Excel merupakan program pengolah angka yang sangat canggih. Banyak hal yang dapat kita lakukan menggunakan program ini. Sebelumnya saya sudah menjelaskan sedikit bagaimana mengolah data secara sederhana. Namun, pada kali ini, saatnya untuk mempelajari cara yang lebih advanced.
Dalam blog ini akan dijelaskan tiga hal yaitu:
- Menggunakan Slicers
- Menambahkan Kolom Pada PivotTable
- Menggunakan Rumus untuk Menghitung Pada PivotTable
Slicers
Slicers adalah alat yang sangat berguna untuk mensortir data agar kita hanya membaca data yang kita inginkan saat itu. Alat ini terletak pada ribbon Insert > Slicers atau dengan mengklik salah satu PivotTable dan mengklik ribbon Analyze > Insert Slicer
Apabila kita klik alat ini akan ditampilkan dengan dialogue box sebagai berikut
Dari situ kita bisa mensortir data sesuai dengan apa yang mau kita lihat. Pada kali ini saya memilih untuk menyortir berdasarkan negara, maka saya mencentang opsi Countries pada pada dialogue box tersebut. Hasilnya akan seperti berikut
Nah apabila sudah kita klik maka kita dapat lihat bagaimana pengaruhnya terhadap PivotTable dan PivotChart. Apabila kita ingin menyortir berdasarkan negara, maka hanya negara yang kia pilih saja yang akan muncul di PivotTable dan PivotCharts.
Cara ini juga dapat diterapkan apabila kita ingin menyortir berdasarkan tahun. Dengan cara yang sama, kita akan melihat hasil seperti di bawah ini. Karena kita menggunkan line chart, pastikan kita memilih lebih dari satu tahun ya!
Slicer tidak terbatas hanya untuk satu PivotTable saja, tetapi dapat dihubungkan ke PivotTable lain. Alat yang akan kita gunakan sekarang adalah ReportConnections. Alat ini terletak di Slicer Tools > Options > Report Connections.
Dengan menggunakan alat ini kita dapat memfilter data sesuai slicer pada banyak PivotTable. Sebagai contoh, lihat gambar dibawah ini.
Dalam PivotTable pasti terdapat data kelompok yang urutannya tidak sesuai yang kita inginkan. Nah kita dapat menggunakan alat Sort yang terletak pada ribbon Data > Sort. Kita dapat menyortir data berdasarkan abjad, atau bahkan menggunakan custom list. Nah kita akan menggunakan opsi kedua. Pertama kita harus memblok data yang kita inginkan lalu mengklik alat itu terlebih dahulu. Lalu ikuti langkah-langkah digambar ini. Apabila telah selesai klik Refresh.
PivotTable Lebih Lanjut
PivotTable merupakan alat yang sangat memnatu kita dalam melakukan analisis data sederhana menggunakan Microsoft Excel. PivotTable memungkinkan kita untuk meringkas data yang jumlah sangat banyak menjadi sesuatu yang mudah untuk kita lihat dan olah. Akan tetapi, PivotTable tidaklah sempurna. Ada beberapa hal yang dapat membatasi kita dalam menggunakan PivotTable. Nah apa saja sih? Simak di bawah ini ya!
Menambahkan Kolom Baru
Kita akan mengambil kasus dimana kita harus mencari keuntungan penjualan dari sebuah toko. Data penjualan dan penerimaan keseluruhan dalam bentuk ExcelTable. Untuk menambahkan kolom penjualan kita hanya perlu memasukan kolom baru di ujung kanan. Lalu memasukkan fungsi untuk mencari keuntungan dengan mencari selisih penerimaan dan pengeluaran.
Kasus lain yang dapat kita ambil adalah mencari profit margin dari suatu perusahaan. Secara umum, profit margin dapat dirumuskan sebagai Profit/Revenue. Langkah pertama yang mungkin kita ambil adalah menambahkan kolom baru di Excel Tables kita. Kita sebut kolom tersebut sebagai profit margin dan fungsi yang kita masukkan adalah Profit/Revenue. Lalu kita refresh PivotTable kita dan mari sekalian kita ubah dari desimal ke bentuk persen.
Apabila kita lihat hasil yang dihasilkan akan aneh? Mengapa hal ini bisa terjadi? Karena Excel akan menjumlahkan semua yang ada di kolom Profit Margin sehingga hasilnya akan mencapai ribuan. Tentunya hal ini salah. Nilai yang kita inginkan berkisar dari nol sampai satu. Bagaimana cara membenarkan hal ini? Pertama kita harus hapus dulu kolom profit margin di Excel Table. Lalu refresh kembali PivotTable kita agar kolom margin benar-benar hilang. Kita dapat menggunakan alat yang bernama Calculated Fields yang terletak pada ribbon Pivot Tables > Fields, Items, and Sets > Calculated Fields. Lalu akan muncul dialogue box. Kita hanya perlu memasukan nama sebagai Margin lalu memasukan fungsinya berupa =Profit/Margin. Lalu tekan OK dan PivotTable akan terupdate dengan sendirinya. Kita ubah ke persen dan selesai! Hasilnya akan lebih masuk akal!
Agar data tersebut lebih mudah dilihat dan dianalisis, kita dapat mengubanhya menjadi format mata uang dan menggunakan Conditional Formatting. Conditional Formatting akan memberikan warna seperti progress bar untuk memudahkan membandingkan dengan data lain dalam suatu set data. Caranya adalah dengan mengklik ribbon Home > Conditional Formatting. Untuk data Revenue, kita dapat menggunakan pilihan Data Bars dan untuk Margin kita dapat menggunakan Color Scales.
Menghitung dengan Rumus dalam PivotTable
Kalo dijelasin semuanyaa, kayaknya kita bakalan pusing kan? Biar gampang, kita akan menggunakan suatu kasus. Misalkan kita memiliki data yang sangat banyak dan kita sudah memiliki PivotTable untuk meringkas datanya seperti gambar di bawah ini.
Nah, dari PivotTable di atas, kita diminta untuk menghitung growth dari tahun 2015 ke 2016. Mungkin kita mikir “Loh gaampang kan? Tinggal masukkin fungsi =H7-G7 terus tarik ke bawah”. Eits, tapi tidak semudah itu! Mari lihat apa yang terjadi kalau kita menggunakan cara di atas.
Eh kok jadi gitu? Kok fungsinya beda? Terus kok hasil tarikan ke bawahnya sama semua? Tenang itu sebenarnya adalah salah satu dari keunggulan dari PivotTable itu sendiri! Apabila kita memilih satu sel pada PivotTable, Excel akan menggunakan fungsi =GETPIVOTDATA dan akan mengunci sel yang kita pilih. AKibatnya, apabila PivotTable kita ubah, ,seperti menambahkan kolom gender seperti di bawah, hasil Growth yang kita hitung tadi tidak akan berubah.
Wah berarti ga bisa dong kita bikin Growthnya? Tenang, semuanya ada solusinya kok! Kita dapat mematikan opsi untuk menggunakan fungsi tersebut dengan mematikan opsi Generate GetPivotData pada ribbon PivotTable Tools > Analyze > Options > Generate GetPivotData.
Nah apabila kita matikan opsi tersebut, ketika kita memilih suatu sel, fungsi yang digunakan sama seperti fungsi yang biasa dan tidak dikunci pada sel itu saja. Nah, sekarang kita bisa menghitung Growthnya! Kita akan membuatnya bersama persentase Growthnya juga ya!
Tapi cara ini sebenarnya lebih ke workaround saja ya! Apabila kita menggunakan cara ini, kita tidak akan bisa membuat PivotChart, dan apabila kita mengubah PivotTable, hasil yang kita buat akan jadi kacau.
Nah, seperti masalah lainnya, pasti ada workaround juga untuk masalah ini! Biasanya dan yang paling gampang adalah dengan mengcopas data yang kita inginkan untuk berbagai keperluan lainnya.
Link File Praktikum 6:
https://drive.google.com/drive/folders/1M8k3OWWaZt_27SzyRtc63RNbJuN4rZo4?usp=sharing
Radja's Tech Blog 