Laporan Bergaya Pivot dengan Formula

Tabel pivot menyusun data dalam tabulasi silang: baris untuk satu kategori, kolom untuk kategori lain, dan total yang mengisi kisi. Contoh klasiknya adalah Wilayah di sisi kiri, Kuartal di bagian atas, dan Penjualan di setiap sel.

Tabel pivot memang sangat berguna, tetapi perlu disegarkan secara manual dan berada dalam blok tetap. Tabel pivot berbasis rumus membangun ulang dirinya secara langsung setiap kali data berubah.

Dalam pelajaran ini Anda akan menyusun tajuk baris, tajuk kolom, dan isi yang terdiri dari rumus SUMIFS untuk menghitung setiap perpotongan secara otomatis.

Kita akan menggunakan lembar bernama Sales dengan kolom berikut: Wilayah di A, Kuartal di B, dan Jumlah di C, pada baris 2 hingga 500.

Laporan yang kita inginkan akan terlihat seperti ini:

• Label baris: setiap Wilayah unik ke bawah di kolom E.
• Label kolom: Q1, Q2, Q3, Q4 melintang pada baris 1 dari F hingga I.
• Isi: total Jumlah untuk setiap pasangan Wilayah dan Kuartal.

Setiap sel isi menjawab satu pertanyaan: berapa banyak penjualan wilayah ini pada kuartal tersebut?

Tajuk baris adalah wilayah yang berbeda. Gunakan UNIQUE bersama SORT agar wilayah tersebut meluap ke bawah kolom E dan tetap terurut.

Letakkan rumus ini di E2:

Sekarang wilayah tersebut mengisi E2 dan sel-sel di bawahnya secara otomatis. Seperti pada tabel ringkasan, daftar ini menjadi acuan yang dituju oleh seluruh kisi.

Tajuk kolom adalah kuartal yang dibentangkan melintang pada sebuah baris. Anda dapat mengetikkan Q1, Q2, Q3, Q4 secara manual, atau meluapkannya secara horizontal dengan membungkus UNIQUE menggunakan TRANSPOSE.

Di F1, rumus ini menempatkan kuartal unik secara melintang di bagian atas:

TRANSPOSE mengubah daftar vertikal menjadi daftar horizontal, sehingga kolom kuartal menjadi baris tajuk. Sekarang kedua sumbu kisi sudah tersedia.

Sekarang isi bagian tabel. Setiap sel memerlukan total untuk wilayah pada barisnya dan kuartal pada kolomnya. SUMIFS dapat menangani dua kondisi dengan mudah.

Di sel isi pertama, F2, tuliskan:

Rumus ini membaca Jumlah saat Wilayah sama dengan label di sebelah kiri dan Kuartal sama dengan tajuk di atas. Ini adalah satu perpotongan dalam tabel pivot.

Tanda dolar memungkinkan satu rumus mengisi seluruh kisi saat disalin. Perhatikan referensi campuran berikut:

• $E2 mengunci kolom ke E tetapi membiarkan baris berubah, sehingga setiap baris membaca wilayahnya sendiri.
• F$1 mengunci baris ke 1 tetapi membiarkan kolom berubah, sehingga setiap kolom membaca kuartalnya sendiri.
• $C$2:$C$500 terkunci sepenuhnya karena rentang data tidak pernah bergeser.

Salin F2 ke seluruh kolom kuartal dan ke bawah seluruh baris wilayah; setiap sel akan menyesuaikan diri dengan tepat.

Setelah F2 ditulis dengan benar, pilih sel tersebut dan seret gagang isian ke kanan melintasi kolom kuartal, lalu ke bawah melintasi baris wilayah. Excel akan menulis ulang bagian relatifnya untuk Anda.

• Sel G2 menjadi Wilayah $E2 dan Kuartal G$1.
• Sel F3 menjadi Wilayah $E3 dan Kuartal F$1.

Hasilnya adalah tabulasi silang lengkap dengan total di setiap perpotongan. Anda tidak memerlukan panduan pivot, dan perhitungannya dilakukan kembali segera setelah data Sales berubah.

Tabel pivot yang lengkap menampilkan total keseluruhan. Tambahkan kolom Total di sebelah kanan dan baris Total di bagian bawah menggunakan SUM biasa pada setiap baris.

Untuk total baris wilayah pertama, letakkan rumus ini di kolom setelah kuartal terakhir:

Untuk total kolom, jumlahkan sel-sel isi pada kuartal tersebut ke bawah sepanjang baris. Total di tepi ini membuat laporan terasa lengkap dan memungkinkan pembaca memeriksa kewajaran angka dengan sekilas pandang.

Jika aplikasi Anda mendukungnya, Anda dapat menghindari penyalinan dengan langsung memasukkan referensi luapan ke dalam SUMIFS. Gunakan tajuk yang meluap sebagai kriteria.

Satu rumus ini menjumlahkan setiap perpotongan wilayah dan kuartal:

Di sini E2# adalah daftar wilayah vertikal dan F1# adalah daftar kuartal horizontal. Excel memasangkannya menjadi kisi lengkap sekaligus. Metode penyeretan lebih kompatibel, tetapi ini adalah versi modern yang lebih rapi.

Laporan menjadi lebih informatif jika menampilkan proporsi, bukan hanya jumlah. Tambahkan kolom yang menyatakan total setiap wilayah sebagai persentase dari total keseluruhan.

Jika total baris wilayah berada di J2 dan total keseluruhan berada di J10, tuliskan:

Mengunci total keseluruhan dengan $J$10 memungkinkan Anda mengisi rumus ke bawah untuk semua wilayah, sementara pembaginya selalu tetap sama. Format kolom tersebut sebagai persentase agar pembaca dapat langsung melihat wilayah mana yang mendominasi.

Beberapa kebiasaan membuat pivot berbasis rumus tetap andal:

• Gunakan rentang luas yang mencakup seluruh baris, seperti baris 2 hingga 500, agar baris baru ikut tercakup.
• Kunci rentang data dengan acuan $ lengkap; hanya referensi tajuk yang boleh berubah.
• Sisakan ruang kosong di bawah dan di sebelah kanan agar tajuk dan total yang meluap memiliki ruang.

Jika dibuat dengan baik, laporan ini tidak memerlukan pemeliharaan sama sekali. Ketikkan penjualan baru dan kisi, total, serta label akan memperbarui dirinya sendiri.

Periksa pemahaman Anda tentang referensi campuran yang menggerakkan pivot berbasis rumus.

Anda telah membuat ulang tabel pivot hanya dengan rumus:

• UNIQUE bersama SORT membuat tajuk baris dalam kolom yang meluap.
• TRANSPOSE membentangkan tajuk kolom melintang pada sebuah baris.
• SUMIFS dengan referensi campuran $E2 dan F$1 mengisi setiap perpotongan, baik dengan menyeret maupun menggunakan referensi luapan seperti E2# dan F1#.
• SUM menambahkan total keseluruhan di tepi.

Seluruh kisi dihitung ulang secara langsung. Selanjutnya Anda akan membuat dasbor interaktif dengan menu tarik-turun yang mengendalikan ukuran-ukuran laporan.