Apa itu PivotTable? Digunakan untuk apa? Berikut adalah tutorial membuat komponen dashboard dengan PivotTable. Table yang dihasilkan bisa jadi sebuah kpi untuk dashboard sales.
PivotTable bisa disebut sebagai alat untuk analisa data yang paling penting di Microsoft Excel. Ia pada dasarnya adalah alat untuk menghasilkan laporan summary dari suatu table atau database. Karena itu menjadi komponen utama dari sebuah dashboard excel. Kita sudah tahu, bahwa dashboard adalah cara mudah untuk menampilkan kpi sebuah bisnis. Pemakaian PivotTable ini sangat mudah, hanya dengan beberapa klik, kita bisa membuat dan menampilkan belasan bentuk format laporan sesuai yang kita inginkan.
Cara membuat PivotTable di Excel untuk membuat laporan KPI sales sederhana, dapat dilihat di Youtube kami di bawah.
Sebagai ilustrasi, misalnya kita memiliki sebuah table transaksi penjualan, dengan kolom tanggal transaksi, produk, kategori produk, nilai penjualan, dan sales-rep. Dengan Pivot Table, kita bisa membuat berbagai konfigurasi laporan untuk kpi sales dashboard seperti berikut:
- Nilai penjualan bulanan, kuartalan, dan tahunan
- Nilai penjualan per produk
- Nilai penjualan per sales untuk setiap produk atau kategori produk
- Jumlah transaksi total
- Jumlah transaksi per produk
- dan seterusnya.
Bahkan kita bisa melakukan beberapa model agregasi data dan menambahkan kalkulasi/rumus di dalamnya. Tergantung pada analysis data diinginkan. Pivot Table juga menyediakan berbagai macam layout yang bisa dipilih sebagai format laporan dan mengatur muncul tidaknya beberapa komponen. Dari sisi sumber data, Pivot Table dapat mengakses database yang terletak secara internal maupun eksternal sebuah workbook Excel. Struktur datanya bisa bersumber pada satu table, atau dari beberapa table secara sekaligus, dengan memanfaatkan fitur relationship. Teknologi ini mirip dengan yang dipakai pada Relational Database Management System (RDBMS). Karena itu maka dashbord excel yang berbasis PivotTable menjadi fleksibel dan powerful.
Berikut adalah tutorial singkat cara membuat Pivot Table di Microsoft Excel untuk membuat laporan sales sederhana. Untuk mengikuti tutorial ini, silahkan mendownload file contoh data pivot table untuk latihan di halaman Download File Latihan.
Pada file latihan tersebut, terdapat Worksheet ORDER_TBL yang berisi daftar transaksi penjualan produk miniatur kendaraaan. Perhatikan, dari kiri ke kanan, terdapat kolom berikut: Order Number, Order Data, Product Code, Product Name, Product Line, Buy Price, Qty, Sales, SalesRep, dan Country. Kolom-kolom inilah yang nantinya dijadikan paramater baris-kolom dari sebuah laporan Pivot Table.
Table ORDER_TBL Berisi Transaksi Penjualan Produk Miniatur Kendaraan
Langkah berikutnya adalah membuat Pivot Table, dengan cara:
- Klik sembarang cell di dalam table Excel tersebut
- Kemudian klik Tab Insert pada Ribbon
- Klik tombol PivotTable
Akan muncul dialog box Pivot Table, isilah dengan paramater berikut
- Table/Range : ORDER_TBL
- Pilih New Worksheet
- Klik OK
Pada worksheet baru, lokasi laporan PivotTable yang kosong akan muncul di A3:C20.
Sebelum mulai membuat laporan, silahkan perhatikan dulu layout halaman PivotTable di Microsoft Excel yang ada.
Berikut adalah konteks menu PivotTable Analyze pada ribbon Excel
Secara singkat, fungsinya adalah sebagai berikut:
- PivotTable Name, untuk memberi nama PivotTable. Berilah nama yang deskriptif agar tidak bingung jika digabung dengan PivotTable lain pada saat membuat dashboard Excel.
- Active Field, untuk menunjukan kolom yang sedang aktif dan setting. Bagian setting yang paling sering dipakai adalah bagian Number Format, misalnya untuk format tanggal, angka, dan currency.
- Group, untuk pengelompokan data pada satu kolom, misalnya tanggal menjadi bulan, kwartal, tahun tahun.
- Filter, untuk memasang fitur Slicer dan TimeLine yaitu semacam dialog box berisi pilihan filter agar PivotTable menjadi interaktif.
- Data, untuk mengatur sumber data PivotTable dan me-refresh jika ada tambahan data baru.
- Calculation, berisi OLAP Tools dan membuat calculated field (kolom dengan rumus).
- Tools, berisi tombol untuk membuat PivotChart, yaitu chart yang menjadikan PivotTable sebagai sumber data.
- Show, berisi tombol untuk menampilkan daftar kolom, tombol-tombol pada laporan PivotTable, dan header kolom.
Berikut adalah konteks menu Pivot Table Design pada ribbon Excel
Ribbon ini berisi pengaturan desain seperti Layout dan Style (warna, heading, baris, dan seterusnya)
Di sebelah kanan, akan muncul daftar kolom (PivotTable Fields) data sumber yang nanti dipilih untuk dimasukan jadi parameter laporan PivotTable. Sedangkan bagian bawahnya terdapat tempat parameter laporan, yang terdiri dari Rows, Column, Values, dan Filters.
Laporan Penjualan per Bulan
Model laporan penjualan bulanan yang akan kita buat kira-kira sebagai berikut:
Kotak Rows akan berisi tanggal, sedangkan kotak Values akan berisi Sales.
Sesuai desain di atas, maka kolom Order Date bisa kita drag (tarik) ke kotak Rows
Kemudian kita dapat menyesuaikan pengelompokan kolom Order Date ini, misalnya kita pilih secara Tahunan dan Bulanan.
- Klik kanan, kemudian pilih Group
- Pilih Months dan Years
Setelah itu, kita masukan nilai Sales ke parameter Value
PivotTable Excel akan secara otomatis memasukan agregasi SUM pada Values. Kita bisa mengganti rumus pivot excel yang dipakai jika perlu, misalnya dengan COUNT untuk menghitung jumlah transaksi. Tersedia juga rumus MAX, MIN di PivotTable.
Berikutnya kita ubah format angkanya, agar mudah dibaca, dengan menambahkan tanda ribuan dan presisi 2 desimal.
- Klik kanan kolom Sum of Sales
- Pilih Value Field Settings
- Pilih Number Format
- Sesuaikan format yang diinginkan, yaitu angka dengan tanda ribuan dan 2 desimal.
Hasil akhirnya adalah sebagai berikut.
Anda dapat melihat beberapa contoh laporan dengan Pivot Table yang dapat dilihat di Youtube kami di bawah ini. Contoh tersebut bisa jadi model analysis data sederhana yang bisa jadi kpi untuk sales performance di bisnis anda.
- Penjualan per Product Line
- Perbandingan penjualan per Product Line dalam 3 tahun terakhir
- Distribusi penjualan per Product Line dalam format persen
- Performa SalesRep dalam 3 tahun terakhir
- Mencari top-5 SalesRep dalam 3 tahun terakhir dengan fitur Sort dan Filter
- Melihat jumlah transaksi setiap SalesRep dengan merubah cara kalkulasi di Pivot Table
Demikianlah tutorial singkat untuk pengenalan PivotTable, semoga bermanfaat.
Tutorial lain tentang Dashboard Excel dapat dilihat di Tutorial Excel Membuat Dashboard.
