Cara Menggabungkan karakter diantara 2 kolom atau lebih di Excel

Di saat kita membuat laporan di MS excel ada kalanya kita dihadapkan pada  kasus atau keadaan untuk menggabungkan karakter atau angka diantara 2 kolom atau lebih,mungkin kasus yang pernah saya alami ini bisa bermanfaat untuk anda, berikut saya berikan contoh bagaimana cara menggabungkan karakter atau angka di Excel :

Pada contoh data diatas saya mempunyai data dengan 6 kolom dimana kolom ke 6 atau kolom F adalah hasil penggabungan karakter dan angka dari Kolom 2 - 5 atau kolom B,C,D,E..

untuk menggabungkan nya Gunakan Rumus =CONCATENATE( lalu klik atau tunjuk kolom yang diinginkan pisahkan dengan tanda koma (,) untuk format waktu  komputer bahasa inggris atau titik koma (;) untuk format waktu komputer bahasa indonesia.

untuk penggabungan karakter atau angka dengan karakter yang diinginkan  gunakan tanda ( " " ).

Lihat contoh diatas saya ingin menggabungkan Kolom B,C,D,E dan ditambah kata "Ok " 

maka rumus nya adalah =CONCATENATE(B6,C6,D6,E6," ok") maka hasil nya akan seperti gambar dibawah ini :

Terimakasih..
Semoga bermanfaat..

Read more

Cara ambil data berdasarkan nilai atau record yang sama di MS Excel

Dalam pekerjaan kantor yang berhubungan dengan pembuatan laporan tentunya kita harus bisa membuat laporan dengan Baik, cepat dan akurat tentunya.Pada kesempatan kali ini saya akan menjelaskan bagaimana cara membuat laporan di excel dengan memanfaatkan fungsi VLOOKUP untuk mendapatkan nilai suatu data atau kolom berdasarkan nilai atau record yang sama. Biasanya kalau di kantor saya fungsi VLOOKUP ini sangat berguna untuk rekonsiliasi data ( mencocokan data ) dalam laporan. Untuk lebih jelasnya langsung saja saya berikan contoh sebagai berikut :

perhatikan contoh berikut saya mempunyai file excel dengan 2 Sheet ( MASTER & LGN ) seperti gambar di bawah :

Pada contoh ini saya ingin pada Sheet MASTER Kolom Nama Langganan dan kolom Limit Terisi otomatis data nya sesuai dengan data yang ada pada Sheet LGN Berdasarkan Kode langganan yang sama.

Note : dalam kasus penggunaan rumus VLOOKUP untuk proses ambil data atau rekonsiliasi data ini sarat nya yaitu harus ada nilai yang sama diantara kedua data nya ( bisa berbeda File nya, atau sheet nya saja ).

Untuk memulai langsung saja saya akan ketik rumus berikut di Kolom Nama Langganan pada Sheet MASTER :

Pada dasarnya rumus ini sifatnya Baku / mutlak anda hanya tinggal merubah baris dan Sheet nya saja,untuk lebih jelasnya berikut penjelasan rumus nya :

saya jelaskan dulu dalam bentuk gambar agar mudah dimengerti :-) berikut penjelasan nya :

1. Setiap anda menggunakan rumus ini hanya yang saya beri warna Merah Dan hijau saja yang perlu anda rubah sesuai dengan kondisi/yang anda inginkan

2. Untuk Rumus yang diberi warna Merah merupakan bagian dari Sheet MASTER  dan rumus yang diberi warna Hijau merupakan bagian dari Sheet LGN

3. Setiap Penyebutan baris harus diawali dengan tanda $ ( Absolute Reference) fungsi nya agar duplikasi Cell /baris tidak akan mengakibatkan perubahan alamat Cell.contoh Baris B4 saya rubah menjadi  $B4

4. Sarat utama untuk menggunakan rumus ini adalah diantara kedua file nya / ke dua sheet nya harus mempunyai nilai yang sama / record yang sama.pada contoh ini pada Sheet MASTER dan Sheet LGN  terdapat nilai / record yang sama pada kolom Kode Langganan

5. Untuk rumus yang diberi warna merah  $B4 merupakan bagian dari  Sheet MASTER maksud nya adalah pada sheet kerja Excel nilai / record pertama dari Kode Langganan  terdapat pada Kolom B dan baris ke 4 ( lihat gambar diatas ).

6.Untuk rumus yang diberi warna Hijau LGN!$A$2:$D$100,2,FALSE)) merupakan bagian dari  Sheet LGN  Maksudnya adalah:

 LGN! ( merupakan Sheet LGN ). 

$A$2 ( Kolom A dan baris 2 di Sheet LGN..lihat gambar diatas ).

$D$100,2,FALSE )) ( Kolom kerja terakhir pada Sheet LGN adalah Kolom D, angka 100 adalah baris terakhir di Sheet LGN ( pada contoh record memang sampai baris /cell 15 tapi lebih baik ditulis lebih saja agar pasti terekon semua data nya).

dan angka ,2, merupakan penyebutan kolom yang akan di ambil data / record nya di Sheet LGN, lihat gambar diatas..disebutkan kolom 2.karena kolom 1 merupakan baris recon data antara Sheet MASTER dan Sheet LGN. ( jadi setiap data / record pada baris yang dijadikan sumber data rekon kolom dihitung no 1 )

Berikut rumus jelasnya :

Terimakasih..
Semoga bermanfaat.. :-)

    

Read more

CARA MELINDUNGI DOKUMEN EXCEL AGAR TIDAK BISA DI RUBAH ATAU DI EDIT

Di dalam mengerjakan laporan kepada user sering kali kita dihadapkan pada situasi yang mengaharuskan kita memproteksi / melindungi dokumen/file agar hanya bisa di baca dan tidak bisa di edit ( di rubah ). terutama saat mengerjakan laporan menggunakan Microsoft Excel, untuk mencegah orang lain bisa merubah isi dari laporan yang kita buat menggunakan MS Excel ikuti langkah berikut :

1.Pertama buka Laporan / file Excel anda, sebagai contoh saya mempunyai dokumen / File sebagai berikut :

Note : di dalam laporan atau contoh file diatas saya mempunyai file dengan 2 Sheet saja yaitu MASTER & LGN ( Perhatikan yang dilingkari ).

untuk Sheet LGN saya ingin user hanya bisa membacanya dan tidak bisa  merubah / edit isi data nya. 

2.Akses Menu seperti gambar : ( saya menggunakan MS Excel 2003,jika kalian menggunakan versi Excel 2007 / 2010 cara tetap sama hanya penempatan nya saja yang berbeda ).

3.Lalu akan muncul Menu berikut:

pada kolom Allow all users of this worksheet to : pilih / ceklist hal apa yang anda ijinkan pada user,
contoh jika anda mengijinkan penerima laporan /file bisa menambah baris maka ceklis / centang Pada insert rows..dll..
Kemudian Pada Kolom Password to unprotect sheet, ketik password yang anda inginkan kemudian Klik Tombol OK . .Akan muncul Confirn Password ketikan kembali Password anda. klik tombol OK dan selesai..


Terimakasih..
selamat mencoba..

Read more

Cara Membuat Grafik di Excel

Di dalam pekerjaan atau tugas pembuatan laporan, peran Grafik atau bagan sangat dibutuhkan dalam menjelaskan data tanpa bertele-tele,karena Grafik merupakan salah satu model penyajian data dalam bentuk visual yang memudahkan seseorang untuk mengetahui & memahami data yang di jelaskan.

di dalam Program Microsoft Excel terdapat fasiltas untuk membuat grafik atau bagan dengan mudah,berikut saya berikan contoh bagaimana membuat grafik atau bagan di Excel.

sebagai contoh kita gunakan data berikut untuk membuat bagan :

di dalam contoh data diatas saya ingin mengetahui grafik penjualan harian selama 5 hari kerja,mengalami kenaikan, pernurunan atau stabil.

untuk memulai membuat grafik ikuti langkah berikut :

blok atau drag bagian yang akan di buat grafik atau bagan nya ( jangan di blok atau drag semua bagian tabel karena akan membuat grafik atau bagan menjadi acak /sulit dibaca,pilih saja bagian yang memang di perlukan ) lihat contoh pada tabel yang saya blok.

langkah berikut nya klik menu berikut :

atau bisa klik gambar Chart di toolbar.

selanjut nya akan muncul menu berikut :

pilih bentuk Grafik atau Bagan yang anda inginkan di kolom Chart type

untuk contoh saya pilih Chart type Column ( lihat gambar diatas )

klik tombol Next akan muncul menu berikut :

Pilih opsi Rows atau Kolom pada kolom Series in

untuk contoh ini saya pilih opsi Columns karena saya ingin menapilkan Grafik berdasarkan kolom Nama Cabang.

klik tombol Next akan muncul menu berikut :

pilih tab Legend ( lihat gambar yang diberi tanda merah )

pilih penempatan keterangan sesuai keinginan anda,pada contoh ini saya menempatkan keterangan di bawah,dengan memilih opsi Bottom pada kolom Placement.

langkah berikut nya masih di menu yang sama pilih tab Titles   untuk membuat Judul Grafik atau bagan :

Ketik pada kolom Chart title untuk memberikan judul grafik atau bagan anda ( lihat contoh gambar )

klik tombol Next akan muncul menu berikut :

Pilih opsi As new Sheet untuk memudahkan anda bekerja pada Sheet baru.

Klik Tombol Finish. 

Selamat mencoba &  Semoga bermanfaat..

terimakasih

Fayol

Cianjur 2015

Read more

CARA CEK DOUBLE INPUT DATA DI EXCEL

kali ini saya akan menjelaskan tentang bagaimana cara nya cek inputan data atau record yang double pada Microsoft Excel,
banyak contoh laporan atau tugas yang pada pengerjaan nya terdapat salah input terutama double input data,untuk mengatasi nya anda tidak usah repot cek manual sendiri, bisa pusing 7 keliling trus belum tentu benar data nya..hehe..
pada contoh tugas berikut saya akan berikan contoh cek data double pada perhitungan lemburan dengan menggunakan rumus sehingga data yang double atau salah bisa di cek dengan otomatis..langsung aja kita bahas contoh kasus berikut :

pada contoh data diatas diberlakukan syarat sebagai berikut :
1.data pada kolom NIK & Nama diperbolehkan double ( Ganda / sama  ) selama data di kolom Tanggal berbeda.
2.data pada kolom Tanggal  tidak boleh double ( Ganda / sama )

jadi pada contoh diatas kita harus memastikan bahwa tidak ada data pada kolom Tanggal yang Double ( sama ).
untuk memulai langsung saja kita fokus pada kolom keterangan / Kolom H, ketik rumus berikut hanya dibaris pertama atau data no 1 seperti contoh diatas.

Keterangan : D7 & D8 = Kolom Tanggal / Kolom D

                      C7 & C8 = Kolom Nama / Kolom D

rumus ini bisa di jabarkan sebagai berikut : jika  D7  ( Baris 1 ) = D8 ( Baris 2 ) dan C7 ( Baris 1 ) = C8 ( Baris 2 ) maka nilai yang diberikan adalah Nilai = "DOUBLE" Namun jika D7 & D8  tidak sama yang diberikan adalah nilai "OK"

Note : untuk baris selanjut nya anda tinggal copy dan paste saja  rumus nya

** pada contoh data diatas perhatikan data / record yang saya beri warna kuning,pada keterangan muncul keterangan "OK" Karena memiliki Nilai Pada Kolom Tanggal / D yang berbeda.

** pada contoh data diatas perhatikan data / record yang saya beri warna Unggu,pada keterangan muncul keterangan "DOUBLE" Karena memiliki Nilai Pada Kolom Tanggal / D yang Sama.

untuk lebih jelasnya silakan anda praktekan sendiri dengan contoh data dan rumus diatas,.

Selamat mencoba...

Note : jangan Lupa beli software nya yang asli ya..jangan bajakan..hehe... ( Microsoft Office Home & Student 2010 Key Card - 1PC/1User )

Terimakasih

fayol

Read more

Memberi tanda atau warna pada baris Excel untuk kondisi tertentu

Pada kesempatan kali ini  saya akan menjelaskan tentang bagaimana membuat tanda pada baris di Ms.excel,

tanda ini bisa berupa Warna tertentu, jenis huruf yang berbeda dll..dengan kondisi tertentu

saya menggunakan Microsoft  Excel 2003 untuk mengerjakan nya,jika kamu menggunakan versi Excel yang lebih baru seperti Ms.Excel 2007 atau excel 2010 kamu tinggal mencari letak menu nya saja,karena pada dasarnya semua versi Ms Excel Menggunakan perintah / cara yang sama hanya penyajian menu nya saja yang berbeda.

untuk lebih jelasnya langsung saja kita praktek dengan contoh data yang saya punya berikut ini :

 saya mempunyai sebuah data Sales dan penagihan ( seperti contoh gambar diatas ),untuk memudahkan saya menganalisa datanya, saya ingin untuk kolom sales yang total angka  nya sama dengan atau lebih besar dari Rp.200,000,000 berwarna Hijau, dan untuk data penagihan yang total nya Kurang dari Rp.100,000,000 berwarna kuning,

Langkah pertama yang harus anda lakukan adalah blok terlebih dahulu kolom yang akan di beri kondisi,sebagai contoh saya akan blok kolom sales terlebih dahulu, setelah di blok lalu klik Menu Format ---> lalu klik Conditional Formating, lihat gambar :

Setelah di klik akan muncul menu berikut :

Langkah Pertama setelah muncul Menu Conditional Formating adalah :

1. Tentukan Condition, pada kasus ini karena saya ingin melihat baris total yang = > dari Rp.200,000,000 berwarna hijau,maka saya pilih condition nya greater than ( = / lebih besar dari )
untuk kasus lain seperti ingin memberi tanda pada nilai yang kurang dari Rp.100,000,000 makapilih Condition nya Less Than ( Kurang dari )..dan masih ada beberapa kondisi lain nya ( sesuai kebutuhan )

2.Masukan Nilai Kondisi, pada kasus ini saya ketik 200,000,000 ( lihat gambar diatas )

3.Klik Tombol Format,

4.Setelah muncul Menu Format Cells  Pilih Tab Patterns  untuk Kondisi lain seperti merubah Huruf dll..kamu tinggal pilih Tab Font ( sesuaikan dengan keinginan ) ( lihat Gambar diatas )

5.Pilih Warna Sesuai Keinginan Kamu ( pada kasus ini saya pilih Hijau ) --> lalu klik OK
  
Selesai sudah Proses yang harus dilakukan, kurang lebih data nya akan terlihat seperti gambar berikut :

Selamat Mencoba dan mengembangkan nya lagi...

Terimakasih semoga bermanfaat..

Read more

Cara Membuat Data Pivot menggunakan Microsoft Excel

Pada Postingan Kali ini saya akan membahas bagaimana cara membuat data Pivot di MS Excel ,Sebelum kita mulai latihan untuk membuat data Pivot kita bahas dulu sebenarnya apa sih data Pivot itu?

Data Pivot / Pivot Table merupakan suatu fasilitas yang terdapat di dalam Microsoft Excel, dimana data Ditampilkan dengan tabel interaktif yang sangat cepat dalam menganalisa, dengan Pivot Table kita dapat mongkombinasikan dan membandingkan sejumlah data baik sedikit maupun banyak, Dalam Pivot Table kita dapat memilih suatu baris data ataupun kolom yang hendak dijadikan referensi dalam melihat jumlah total suatu data, Data Pivot Bersifat Baku / Laporan Otomatis yang terhubung dengan data sumbernya jadi data Pivot tidak bisa di edit / di rubah isi nya,kecuali anda copy & paste special dulu ( by value lalu by Format ) ke halaman baru. atau anda bisa merubah data melalui sumber data nya / table aslinya ( sheet,tempat data asli ).

untuk lebih jelas nya mari kita Latihan untuk membuat data Pivot dengan contoh data berikut ini :

langkah pertama yang perlu anda lakukan adalah membuat format laporan seperti contoh di atas menjadi bentuk data ( seperti format data di dalam database ) yaitu pada setiap kolom,baris harus terisi data,merge cell harus anda hilang kan dulu.

yang harus anda perhatikan dalam langkah ini :

1.judul Laporan harus di hapus ( judul bisa di buat setelah data pivot jadi )

2 tidak boleh ada merge Cells ( perhatikan contoh data dibawah, total penjualan dihapus )

3.Baris Yang kosong harus di isi nilai ( data ),perhatikan contoh gambar dibawah yang diberi warna kuning 
4.Setiap baris yang berisi data wajib mempunyai nama kolom    

** Text Tebal ( hitam) adalah sumber data nya (sub), text yang di beri warna kuning hasil dari copy paste

Perhatikan Contoh berikut setelah data di rubah format nya menjadi Bentuk Data : 

setelah data dirubah menjadi format seperti contoh di atas, langkah berikut nya adalah:
klik menu data lalu pilih Pivot Table and pivotchart Reports.. Lihat Gambar :

Langkah Selajutnya klik next ( pilihan opsi tidak perlu di rubah ) Lihat gambar berikut :

Langkah berikutnya Klik Next ( range terisi otomatis ) lihat gambar :

langkah berikut nya klik Finish..lihat gambar :

Setelah klik finish akan muncul lembar kerja seperti gambar di bawah

** untuk bagian sebelah kiri ( Drop Data items Here ) merupakan lembar kerja untuk membuat data pivot

** untuk bagian sebelah kanan ( Pivot Table Field List ) merupakan sumber data ( ditampilkan sesuai nama kolom sebelumnya )

untuk memulai membuat data pivot anda cukup drag nama kolom di dalam ( Pivot Table Field List ) ke dalam lembar kerja ( Drop Rows Fields Here ) / ( Drop Data items Here ) / ( Drop colums fields Here ) tergantung kreasi anda dalam menyajikan data..

kali ini saya akan membuat contoh data dengan men drag kolom Nama Cabang,No Dokumen,Tanggal Dokumen,Nama Salesman,Kode Langganan dan nama Langganan  ke bagian ( Drop Rows Fields Here ),untuk total penjualan saya drag ke bagian ( Drop Data items Here ). karena di dalam ( Drop Data items Here ) data angka bisa terjumlahkan ( sum ) otomatis..perlu di perhatikan setiap kali anda mendrag kolom akan muncul sub total, lihat contoh gambar di bawah,jika anda tidak membutuhkan total untuk data kolom yang di drag,cukup klik kanan pada total lalu pilih Hide.lihat gambar di bawah :

sekarang data pivot sudah jadi anda tinggal menambahkan judul laporan,menganalisa,mongkombinasikan dan membandingkan data sesuai dengan keinginan anda

untuk kasus berikut saya ingin Menampilkan Data Dengan nama Cabang cianjur saja dan tanggal Dokumen 11/02/2014, anda tinggal klik tanda panah yang dibulati berwarna merah, perhatikan contoh gambar di bawah ini

maka hasil nya akan seperti ini :

selamat mencoba ya
semoga artikel ini bermanfaat

terimaksih
fayol

Read more