Tutorial Pivot Excel sederhana

Olahan data menjadi bentuk laporan yang dapat disajikan secara Informatif menjadi kebutuhan yang mutlak dalam proses Evaluasi maupun sekedar Monitoring Kinerja. Nyatanya, banyak aplikasi atau software yang sebenarnya telah tersedia dalam PC anda belum mampu  di manfaatkan secara optimal. Sehingga Aplikasi yang diharapkan dapat membantu kegiatan dan proses percepatan pekerjaan, acap kali membuat anda tambah lambat bahkan Galau dan bingung karena kesulitan menggunakannya. Apakah Anda pernah merasakannya?

Ms. Excel merupakan Aplikasi yang terkenal untuk berbagai pengolahan data yang dimiliki Microsoft ini, dan terbukti mampu menghasilkan berbagai macam olahan Laporan baik sederhana sampai kompleks sekalipun. Bahkan jika anda amati, saat ini aplikasi ini semakin user friendly. Salah satu bentuk laporan yang lumayan informatif dan sering digunakan adalah bentuk PIVOT. Nah, berikut tutorial pivot sederhana Ms. Excel yang biasa digunakan, khususnya untuk transaksi sales bulanan.

Pembuatan Data isian Pivot

Buka Program Ms. Excel dan beri nama sesuai keinginan Anda. Kemudian buatlah data seperti dalam gambar berikut dan beri nama sheet “transaksi” seperti dibawah ini :

Dalam transaksi penjualan, seringkali field nama produk, area, harga dan penanggungjawab hanya menggunakan kode-kode tertentu untuk mempermudah aktivitas transaksi itu sendiri. Data yang di entry selain kode-kode tadi, tentunya ada data unit terjual, berapa diskonnya atau apakah ada program tertentu yang sedang dilaksanakan.

Kode-kode tersebut mempunyai relasi data yang memberikan informasi tambahan, seperti jenis produk, harga, dan lain-lain. Contoh jenis produk dalam transaksi ini adalah jenis Food dan non food. Dalam memisahkan kategori tersebut maka anda perlu menyediakan informasi baku yang sejenis dan dapat dikategorikan. Untuk membuat table kategori, maka buatlah sheet baru pada dengan nama “kategori”








Pembuatan tabel kategori

Setelah anda selesai membuat sheet kategori diatas, maka sekarang anda perlu membuat data lanjutan yang akan diolah menjadi informasi. Untuk itu, untuk mengamankan data awal (transaksi asli), maka anda perlu melakukan copy dan paste dari data transaksi (sheet transaksi) ke dalam sheet baru. Buatlah sheet dengan nama “data pivot”. Tambahkan nama Field pada data transaksi yang sudah di copy tadi, sebagai berikut :                                                                                                                                   

Sekarang nama Field telah tersusun, seperti tampilan gambar diatas. Selanjutnya anda akan mengisi kolom yang masih kosong (warna hijau) dengan menggunakan fungi "vlookup" Rumusnya adalah VLOOKUP(lookup_value,table_array,col_index_num,FALSE). Urutan pengisian sebagai berikut:

       1.       Untuk mengisi produk, gunakan perintah : =(vlookup($A2,produk,2,FALSE)) kemudian copy ke baris dibawahnya sampai data baris terakhir

Ket :

-          Lookup_value adalah $A2 >> $A artinya Absolut pada Kolom dan 2 artinya data pada baris 2. Absolut artinya data yang ditarik akan selalu mengacu pada data pada kolom A, walaupun data tersebut akan di copy pada kolom selanjutnya yaitu Field Jenis, Value, Gudang, dan lain-lain. (dengan kata lain Lookup_value adalah data yang menjadi sumber untuk informasi pada kategori yang sama)

-          Table_array adalah produk >> produk disini adalah nama range table yang sudah dinamai pada sheet kategori

-          Col_index adalah 2 >> artinya data yang diambil dari table_array “produk” adalah pada kolom 2. Lihat pada kategori produk, kolom 2 berisi tentang data “produk” : sikat gigi, sabun, dan lain-lain.

-          FALSE adalah data isian harus sesuai (exact Match) dengan data yang tersedia pada lookup_value.

       2.       Untuk mengisi data pada field jenis, gunakan perintah: =(vlookup($A2,produk,3,FALSE)) kemudian copy ke baris dibawahnya sampai data baris terakhir

       3.       Untuk mengisi data pada field value, gunakan perintah: =(vlookup($A2,produk,4,FALSE))*C2 kemudian copy ke baris dibawahnya sampai data baris terakhir.

Ket : fungsi vlookup pada field value, digunakan untuk mengambil harga per produk (yang sudah di entry di kategori produk) sedangkan C2 adalah data Kolom C pada baris 2, untuk mengambil data unit terjual.

       4.       Lakukan perintah pertama dan kedua, untuk mengisi field gudang, kategori dan PIC. Perlu diingat untuk merubah col_index pada masing-masing field. Col_index gudang adalah 5, kategori adalah 7 dan PIC adalah 6. Urutan field kategori dan PIC sengaja tidak sama dengan field yang ada di kategori (artinya urutan field tidak masalah, asalkan col_index sesuai dengan field yang diinginkan).

       5.       Untuk pengisian field cabang sampai national manager, gunakan perintah yang sama dengan perintah pertama. Yang berbeda adalah data lookup_value dan table_array (dicoba sendiri ya, untuk melatih pemahaman anda). Kami beri satu Clue =VLOOKUP($B2,area,2,FALSE)

Data tersebut akan terisi seperti gambar dibawah ini :

Pembuatan Pivot Table

Sampai tahap ini, Anda sudah membuat rangkaian data yang siap diolah untuk menjadi Laporan yang informatif dengan menggunakan Pivot table. Untuk membuat pivot table, lakukan langkah sebagai berikut :

       1.       Mulai dengan klik tab “INSERT”, kemudian klik icon “Pivot Table”

  

           2. Kotak dialog akan muncul untuk langkah selanjutnya yaitu memilih data yang ingin dianalisa menggunakan Pivot Table. Pada Table Range, klik logo panah warna merah untuk proses seleksi data yang diinginkan. Dan anda diminta memilih menempatkan Pivot Table apakah di worksheet baru atau worksheet yang sudah terbuka. Dalam contoh ini, kami akan mensimulasikan apabila dibuka di worksheet baru dengan klik "New Worksheet".

       3.       Pivot Table sudah terbuka dan siap untuk digunakan, seperti tampilan dibawah ini:

       4.       Pada Pivot Table Field, anda dapat memilih field yang akan disertakan dalam tabel dengan cara drag (menyeret) ke kotak dibawahnya. Kotak tersebut yaitu Filter, Columns, Rows dan values. Hal ini memungkinkan Anda memindahkan data-data sehingga Anda dapat mengatur yang terbaik untuk laporan Anda.

       5.       Contoh, anda akan membuat Laporan penjualan per produk Area Manager dengan nama Dudung, maka anda drag field “Area manager” kedalam kotak “Filters” kemudian anda juga ingin mengetahui Jenis produknya maka drag field “Jenis” untuk diletakkan di kotak “Columns” maka Pivot Table akan tersaji seperti diatas. Untuk mengetahui cabang yang ada dibawah area Manager Dudung, maka anda perlu menyeret field “Cabang” ke kotak Rows dan tentu saja “produk” ke kotak Rows untuk mengetahui produk yang terjual. Selanjutnya untuk mengetahui jumlah nilai penjualan, anda hanya perlu menyeret field “Value” ke kotak “values”.

       6.       Untuk jenis dan variasi laporan lainnya anda perlu bereksperimen dengan drag pada filter, columns, rows maupun values.

Selamat mencoba dan berkreasi menggunakan Pivot Table.