Dynamic arrays membawa perubahan besar pada cara Excel mengeluarkan hasil formula. Kini, saat membuat formula yang menghasilkan daftar, Anda harus memilih apakah hasil akan “spill” atau meluber ke beberapa sel, atau digabung menjadi satu nilai tunggal.
Salah satu fungsi Excel yang sederhana tapi powerful untuk mengatur ini adalah fungsi SUM. Dengan membungkus formula di dalam fungsi SUM, Anda mengendalikan apakah hasilnya menjadi daftar yang meluber atau satu nilai saja.
Memahami Spill dengan Menghilangkan SUM
Secara default, formula dynamic array akan menghasilkan output berupa range yang meluber. Contohnya, jika Anda mengetik formula seperti:
=SORT(A:A)
Excel akan mengurutkan seluruh data di kolom A dan menampilkan hasilnya yang meluber ke bawah sesuai banyaknya data. Titik setelah tanda titik dua (:) adalah operator trim ref yang mengabaikan sel kosong di akhir kolom agar proses hanya berjalan pada data yang ada.
Fungsi-fungsi seperti SORT, FILTER, UNIQUE, dan SEQUENCE menjadi sangat berguna karena hasilnya otomatis terkoneksi dengan data asli. Jika data berubah, hasil formula juga ikut berubah.
Namun, spill memerlukan ruang kosong yang cukup di sel-sel yang akan diluberinya. Bila ada blokir seperti teks, tabel, atau sel termerge di tengah jalur hasil, Excel menampilkan error #SPILL!. Ini sering terjadi ketika Anda mencoba memasukkan formula spill ke dalam Excel table yang tidak mendukung spill.
Jadi, jika SUM tidak dipakai, Excel akan memberikan hasil berupa daftar mentah yang dapat dinamis tapi rawan error jika jalur spill terganggu.
Menggunakan SUM untuk Memaksa Satu Nilai
Jika tujuan Anda bukan daftar dinamis tapi nilai tunggal, fungsi SUM sangat membantu. Contoh formula:
=SUM(FILTER(A:A, A:A>50))
Formula FILTER membuat array dalam memori terlebih dahulu, lalu SUM langsung menjumlahkan semua elemen dan hanya mengembalikan satu hasil total. Cara ini mengubah output dari daftar spill menjadi nilai skalar yang stabil dan mudah dipakai.
Menggunakan SUM juga memungkinkan Anda memakai dynamic array formula di dalam Excel table. Misalnya, untuk menampilkan total penjualan per individu dalam tabel, Anda bisa tulis:
=SUM(FILTER([Sales], [Name]=[@Name]))
Ini memudahkan untuk membandingkan jumlah total tiap baris dengan data regionalnya. Pendekatan ini membuat formula lebih cocok dipakai dalam tabel tanpa memunculkan error spill.
Fungsi Array Aggregator Lainnya
Selain SUM, Anda bisa memakai fungsi aggregator array lain seperti AVERAGE, MAX, MIN, dan COUNT. Mereka sama-sama “mengonsumsi” array input dan menghasilkan satu nilai. Misalnya, mengganti SUM dengan AVERAGE di formula sebelumnya:
=AVERAGE(FILTER([Sales], [Name]=[@Name]))
Formula ini menghasilkan rata-rata penjualan per orang dalam tabel dan menjaga output tetap satu nilai, bukan daftar.
Contoh Tanpa Fungsi Dynamic Array
Operasi array modern juga bisa untuk kalkulasi seperti perkalian matrix tanpa dynamic array function khusus. Misalnya, mengalikan kuantitas di kolom B dengan harga di kolom C:
- Tanpa SUM:
=(B2:B8*C2:C8)
Ini menghasilkan array hasil perkalian per baris yang meluber.
- Dengan SUM:
=SUM((B2:B8*C2:C8))
SUM langsung menjumlahkan seluruh hasil perkalian dan menghasilkan satu nilai total. Ini cara baru menggantikan metode lama yang memerlukan Ctrl+Shift+Enter.
Panduan Memilih Antara Spill dan Single Value
Saat menulis formula dynamic array, pertimbangkan dua hal berikut:
- Jika membutuhkan nilai tunggal, bungkus formula dengan SUM atau aggregator lain untuk hasil yang stabil dan kompatibel dengan tabel Excel.
- Jika ingin daftar dinamis, biarkan formula meluber. Pastikan jalur spill bebas agar tidak muncul error
#SPILL!.
Anda juga dapat menggunakan operator spill (#) untuk referensi range spill yang dinamis. Misalnya jika spill dimulai di B1, rumus:
=SUM(B1#)
akan menjumlah seluruh hasil spill dari B1 ke bawah.
Fungsi SUM bukan hanya soal penjumlahan, tapi alat kendali yang esensial dalam mengarahkan hasil formula dynamic array di Excel. Memahami kapan dan bagaimana menggunakan SUM akan membuat pekerjaan spreadsheet Anda lebih efisien dan bebas masalah.
