Tabel Ringkasan dengan ARRAY Dinamis

Tabel ringkasan memadatkan daftar besar baris mentah menjadi blok kecil yang mudah dibaca: satu baris untuk setiap kategori dengan total di sampingnya. Bayangkan catatan penjualan dengan ratusan baris berubah menjadi tabel rapi yang menampilkan setiap wilayah dan total pendapatannya.

Cara lama adalah tabel pivot manual yang harus Anda segarkan. Cara modern menggunakan rumus array dinamis yang memperbarui dirinya sendiri begitu data Anda berubah. Tanpa tombol dan tanpa penyegaran.

Dalam pelajaran ini Anda akan menggabungkan tiga alat canggih: UNIQUE untuk mencantumkan kategori, SUMIFS untuk menjumlahkan masing-masing kategori, dan FILTER untuk mengambil baris yang cocok. Bersama-sama, semuanya membentuk ringkasan yang terus diperbarui.

Bayangkan lembar bernama Sales dengan tiga kolom: Wilayah di kolom A, Produk di kolom B, dan Jumlah di kolom C, yang mengisi baris 2 hingga 200.

Tujuan kita adalah membuat ringkasan yang menampilkan setiap wilayah unik beserta total penjualannya. Tantangan pertama adalah mendapatkan daftar wilayah yang rapi tanpa mengetiknya satu per satu, karena wilayah baru mungkin muncul kemudian.

• A2:A200 berisi banyak nama wilayah yang berulang seperti East, West, East, North.
• Kita hanya menginginkan: East, West, North, masing-masing tercantum sekali.

Daftar unik tersebut menjadi dasar seluruh ringkasan.

Fungsi UNIQUE menerima sebuah rentang dan mengembalikan setiap nilai hanya sekali. Fungsi ini meluapkan hasil, artinya satu rumus mengisi sebanyak sel yang diperlukan sesuai jumlah nilai yang berbeda.

Ketikkan rumus ini di sel E2 dan daftar wilayah akan muncul secara otomatis di bawahnya:

Jika wilayah baru ditambahkan ke data nanti, daftar yang meluap akan bertambah sendiri. Anda tidak perlu mengubah rumusnya.

Sekarang kita memerlukan total nilai untuk setiap wilayah di kolom E. SUMIFS menjumlahkan nilai dari satu rentang hanya jika rentang lain cocok dengan suatu kondisi.

Strukturnya adalah SUMIFS(sum_range, criteria_range, criteria). Letakkan rumus ini di F2, di samping wilayah pertama:

Referensi E2# adalah bagian pentingnya. Tanda # berarti seluruh rentang yang meluap dari E2. Jadi, satu rumus ini menjumlahkan setiap wilayah yang dihasilkan oleh UNIQUE.

Referensi luapan E2# selalu menunjuk ke seluruh blok yang dihasilkan sebuah rumus, seberapa pun ukurannya. Inilah yang membuat ringkasan menjadi dinamis.

Saat UNIQUE menemukan 3 wilayah, E2# memiliki tinggi 3 sel dan SUMIFS mengembalikan 3 total. Saat data bertambah menjadi 5 wilayah, kedua rentang tersebut meluas bersama-sama tanpa perubahan apa pun.

• E2 = hanya sel paling atas.
• E2# = seluruh array yang meluap, dimulai dari E2.

Biasakan diri Anda dengan tanda #; tanda ini adalah inti rumus dasbor.

Ringkasan lebih mudah dibaca jika totalnya diurutkan. Bungkus daftar wilayah dengan SORT agar kategori muncul menurut abjad, atau urutkan seluruh tabel berdasarkan total.

Untuk mencantumkan wilayah menurut abjad di E2:

Karena total di F masih merujuk ke E2#, pengurutan wilayah akan otomatis menyelaraskan kembali totalnya. Kedua kolom tetap bergerak bersama.

Terkadang Anda memerlukan baris sumber untuk satu kategori, bukan hanya totalnya. FILTER mengembalikan setiap baris yang memenuhi suatu kondisi dan meluapkannya.

Untuk menampilkan semua baris penjualan saat Wilayah sama dengan nilai di sel H1:

Jika H1 berisi East, Anda mendapatkan setiap baris East. Ubah H1 menjadi West dan blok tersebut akan langsung menulis ulang isinya. Inilah dasar tampilan penelusuran mendalam dalam dasbor.

FILTER menghasilkan galat #CALC! saat tidak ada yang cocok. Agar tetap rapi, berikan argumen ketiga opsional sebagai pesan cadangan.

Argumen ketiga ditampilkan saat tidak ada hasil yang cocok:

Sekarang wilayah tanpa penjualan menampilkan catatan yang informatif, bukan galat. Selalu tambahkan cadangan ini pada dasbor agar pilihan yang tidak disengaja tidak merusak tata letak.

Ringkasan sering kali menampilkan jumlah pesanan setiap wilayah, bukan hanya nilainya. COUNTIFS menghitung baris yang cocok dengan suatu kondisi, seperti SUMIFS tetapi tanpa rentang penjumlahan.

Letakkan rumus ini di kolom G, di samping total:

Sekarang ringkasan tiga kolom Anda berisi Wilayah, Total Penjualan, dan Jumlah Pesanan, semuanya digerakkan oleh satu daftar wilayah yang meluap di E2#. Semuanya diperbarui bersama-sama.

Berikut seluruh rumusnya yang ditempatkan berdampingan:

• E2: =SORT(UNIQUE(Sales!A2:A200)) mencantumkan wilayah.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) menjumlahkan setiap wilayah.
• G2: =COUNTIFS(Sales!A2:A200, E2#) menghitung setiap wilayah.

Hanya rumus E2 yang diketik pada baris pertama; F dan G meluap berdasarkan referensi #. Tambahkan penjualan baru di mana saja dalam Sales dan ketiga kolom akan diperbarui tanpa mengeklik apa pun.

Ringkasan berbasis rumus memiliki beberapa keunggulan nyata dibandingkan mengetik nilai atau menyegarkan tabel pivot:

• Selalu diperbarui: perhitungannya dilakukan kembali saat data berubah.
• Ukuran otomatis: kategori baru muncul secara otomatis melalui UNIQUE dan referensi #.
• Transparan: siapa pun dapat membaca logikanya di dalam sel.

Kekurangannya adalah rentang luapan memerlukan ruang kosong untuk berkembang; kita akan membahas luapan yang terhalang dalam pelajaran berikutnya. Untuk sekarang, sisakan ruang di bawah rumus Anda.

Uji pemahaman Anda tentang cara membuat tabel ringkasan yang memperbarui dirinya sendiri.

Anda telah membuat tabel ringkasan yang memelihara dirinya sendiri:

• UNIQUE mencantumkan setiap kategori sekali dan meluapkan hasilnya.
• SORT mengurutkan daftar tersebut agar mudah dibaca.
• SUMIFS dan COUNTIFS menjumlahkan dan menghitung setiap kategori menggunakan referensi luapan E2#.
• FILTER mengambil baris yang cocok untuk penelusuran mendalam, dengan pesan cadangan jika tidak ada yang cocok.

Karena setiap rumus menggunakan daftar yang meluap sebagai acuan, penambahan data baru akan memperbarui seluruh ringkasan tanpa langkah manual. Selanjutnya Anda akan membuat ulang laporan bergaya tabel pivot sepenuhnya dengan rumus.