Belajar Array Formula Dari Nol Hingga Mahir

Mau jadi jago spreadsheet tanpa harus ngetik ratusan rumus manual? Array formula itu senjata rahasia. Sekali paham, kamu bisa bikin perhitungan kompleks yang otomatis nyebar ke beberapa sel, ngolah baris-baris data sekaligus, dan bikin laporan dinamis yang enak dilihat. Artikel ini bakal ngajarin kamu dari nol sampai mahir: konsep, perbedaan Excel & Google Sheets, banyak contoh nyata, trik debugging, best practice, dan latihan supaya beneran paham. Santai bacanya, cocok buat anak remaja yang pengen cepet jago — langsung praktik aja. --- ## Ringkasan singkat sebelum mulai * **Array formula** = rumus yang bekerja pada kumpulan nilai (array) sekaligus, bukan hanya satu sel. * Di **Excel lama** ada legacy array (masukin pakai `Ctrl+Shift+Enter`). * Di **Excel modern** (Office 365 / Excel 2021+) ada **dynamic arrays** (otomatis spill). * Di **Google Sheets** ada `ARRAYFORMULA()` dan fungsi yang mendukung array. * Contoh fungsi populer: `SUMPRODUCT`, `TRANSPOSE`, `FILTER`, `UNIQUE`, `SEQUENCE`, `MMULT`, `INDEX`+`SEQUENCE`. * Gunakan tabel (Excel Table) dan named ranges untuk kerapihan. --- ## 1. Apa itu Array Formula? Gampangnya gimana sih? Bayangin kamu punya kolom angka A2:A10 dan kolom angka B2:B10, terus mau kalikan tiap pasangan (A2*B2, A3*B3, ...) dan jumlahin hasilnya. Cara tradisional: tambah kolom helper lalu `SUM` di akhir. Dengan array formula kamu bisa lakukan itu dalam satu rumus: Excel/Google Sheets akan mengalikan setiap pasangan di dalam satu operasi dan mengembalikan hasilnya. Contoh sederhana (konsep): ``` {=SUM(A2:A10 * B2:B10)} // legacy Excel (CSE) =SUMPRODUCT(A2:A10, B2:B10) // lebih praktis dan tidak perlu CSE ``` Perhatikan: `SUMPRODUCT` sudah mengimplementasikan operasi array internal sehingga praktis. --- ## 2. Excel lama vs Excel modern vs Google Sheets: bedanya apa? ### Excel lama (pre-Dynamic Arrays) * Ada fitur **array formulas** yang perlu di-enter pakai `Ctrl+Shift+Enter` (CSE). Rumus akan muncul dikurung `{}` otomatis. * Hasil array bisa **mengisi satu sel** (menampilkan agregat seperti SUM) atau **menghasilkan array** ke beberapa sel (jika range target sama ukuran array). * Risk: mudah salah penempatan, sulit debug. ### Excel modern (Office 365/Excel 2021+) * Memperkenalkan **Dynamic Arrays**: fungsi baru seperti `FILTER`, `UNIQUE`, `SORT`, `SEQUENCE`, `RANDARRAY` yang **otomatis spill** (mendistribusikan hasil ke sel bawah/kanan). * Tidak perlu `Ctrl+Shift+Enter`. * Error baru: `#SPILL!` jika sel tujuan terhalang. ### Google Sheets * Tidak pakai CSE. Pakai `ARRAYFORMULA()` untuk membuat rumus yang memproses range dan meng-spill hasil. * Banyak fungsi di Sheets juga otomatis array-aware (mis. `FILTER`, `UNIQUE`) sama seperti Excel modern. --- ## 3. Mental model: bagaimana array bekerja * **Array 1D** = baris atau kolom nilai, mis. `{1,2,3}` (row) atau `{1;2;3}` (column). * **Array 2D** = tabel nilai (baris x kolom). * Operasi antara arrays dilakukan **element-wise** jika ukuran cocok: `A * B` → multiplies masing-masing elemen. * Jika ukuran berbeda, beberapa fungsi melakukan broadcasting (Excel dynamic arrays mendukung beberapa kasus), tapi hati-hati. --- ## 4. Mulai dari yang simpel: SUMPRODUCT & SUM(IF(...)) ### Contoh dataset (salin di sheet): | A (Produk) | B (Qty) | C (Harga) | | ---------- | ------: | --------: | | Pensil | 2 | 3000 | | Buku | 1 | 5500 | | Pulpen | 10 | 2000 | | Penghapus | 3 | 2500 | **Total penjualan** = SUM(B * C) **Excel / Google Sheets** ``` =SUMPRODUCT(B2:B5, C2:C5) ``` Hasil: jumlah produk × harga digabung. **Kenapa SUMPRODUCT?** * Simpel, cepat, tidak perlu kolom helper. * Juga bisa dipakai sebagai `SUMPRODUCT( (Range1=Kriteria) * (Range2) )` untuk SUMIF-like behaviour dengan kondisi kompleks. ### SUMIF dengan beberapa kondisi (alternatif) Misal hanya jumlahkan `Harga*Qty` untuk `Region = "JKT"` (jika ada kolom Region): ``` =SUMPRODUCT((RegionRange="JKT") * (QtyRange) * (HargaRange)) ``` Di Google Sheets pun sama. --- ## 5. ARRAYFORMULA di Google Sheets: contoh praktis Google Sheets punya `ARRAYFORMULA()` untuk membuat rumus yang meng-spill hasil: Contoh: kamu mau membuat kolom `Total` (Qty * Harga) untuk seluruh baris tanpa drag. ``` =ARRAYFORMULA(IF(ROW(A2:A)=1, "Total", IF(A2:A="", "", B2:B * C2:C))) ``` Penjelasan: * `ROW(A2:A)=1` pengecekan header (opsional). * `IF(A2:A="","", ...)` agar tidak menampilkan angka kalau baris kosong. Tip: `ARRAYFORMULA` sering dipakai di sheet yang diperbarui otomatis (data import) agar kolom hasil langsung terisi. --- ## 6. TRANSPOSE dan INDEX: manipulasi bentuk array ### TRANSPOSE Kapan perlu: ubah row → column atau sebaliknya. **Excel & Google Sheets** ``` =TRANSPOSE(B2:F2) // mengubah baris jadi kolom ``` ### INDEX + SEQUENCE (Excel modern / Google Sheets) Untuk buat daftar dinamis seperti ambil 10 pertama dari range: ``` =INDEX(A2:A100, SEQUENCE(10)) ``` Di Excel modern `SEQUENCE(10)` menghasilkan array {1;2;...;10} dan `INDEX` mengambil baris sesuai. Contoh: Ambil 5 nilai pertama dari kolom Sales: ``` =TAKE(SortRange, 5) // Excel modern punya TAKE; di Sheets gunakan INDEX+SEQUENCE ``` --- ## 7. Fungsi Dynamic Array populer di Excel modern (dan analognya di Sheets) * `FILTER(range, include, [if_empty])` — ambil baris yang memenuhi kondisi. * `UNIQUE(range)` — ambil nilai unik. * `SORT(range, [sort_index], [order])` — sortir range. * `SEQUENCE(rows, [cols], [start], [step])` — buat deret angka. * `XLOOKUP()` — lookup lebih fleksibel. * `TAKE`, `DROP`, `WRAPROWS`, `WRAPCOLS` (beberapa baru) — manipulasi array. Contoh: ambil semua produk dengan penjualan > 10000: ``` =FILTER(A2:C100, C2:C100 > 10000, "Tidak ada") ``` Hasil: akan spill ke beberapa baris/kolom sesuai jumlah data yang cocok. Google Sheets punya `FILTER` dan `UNIQUE` juga. --- ## 8. Contoh lengkap: buat daftar produk unik + total penjualan per produk (tanpa Pivot) Dataset transaksi (salin): | OrderID | Produk | Qty | Harga | | ------: | ------ | --: | ----: | | 1001 | Pensil | 2 | 3000 | | 1002 | Buku | 1 | 5500 | | 1003 | Pensil | 5 | 3000 | | 1004 | Pulpen | 10 | 2000 | | 1005 | Buku | 4 | 5500 | Kita mau table: Produk unik | TotalQty | TotalRevenue ### Google Sheets (ARRAYFORMULA + UNIQUE + SUMIF) 1. Ambil produk unik: ``` =UNIQUE(B2:B6) ``` 2. TotalQty per produk: ``` =ARRAYFORMULA(IF(D2:D="", "", SUMIF(B2:B6, D2:D, C2:C6))) ``` Asumsi D2:D adalah hasil UNIQUE. 3. TotalRevenue: ``` =ARRAYFORMULA(IF(D2:D="", "", SUMIF(B2:B6, D2:D, C2:C6 * DUMMY))) // tapi SUMIF tidak menerima array multiply ``` Lebih simpel pakai `SUMPRODUCT` dalam helper: ``` =ARRAYFORMULA(IF(D2:D="", "", SUMPRODUCT((B2:B6 = D2:D) * (C2:C6 * E2:E6)))) ``` Tapi `SUMPRODUCT` agak tricky dengan ARRAYFORMULA; alternatif pakai `QUERY` di Sheets: ``` =QUERY(A1:D, "select B, sum(C), sum(C*D) group by B", 1) ``` `QUERY` adalah tool powerful untuk aggregasi. ### Excel (Dynamic Arrays + SUMIFS) 1. Unique: ``` =UNIQUE(B2:B6) ``` 2. TotalQty: ``` =SUMIFS(C2:C6, B2:B6, F2#) // F2# refers to spilled UNIQUE results ``` 3. TotalRevenue: ``` =SUMIFS(D2:D6*C2:C6, B2:B6, F2#) // Excel tidak support array multiply in SUMIFS; gunakan SUMPRODUCT per item ``` Lebih baik gunakan `BYROW` + `LAMBDA` di Excel 365 advanced: ``` =MAP(UNIQUE(B2:B6), LAMBDA(p, SUMIFS(D2:D6, B2:B6, p) * SUMIFS(C2:C6, B2:B6, p))) ``` Tapi itu advanced. Intinya: array formulas + UNIQUE + SUMIFS / SUMPRODUCT memungkinkan bikin ringkasan tanpa pivot. --- ## 9. FILTER: seleksi baris cerdas (contoh nyata) Misal data penjualan per tanggal, mau ambil baris hanya untuk `Region = "JKT"` dan `Qty >= 5`. **Excel modern / Google Sheets** ``` =FILTER(A2:D100, (C2:C100="JKT") * (B2:B100 >= 5), "Tidak ada data") ``` Penjelasan: kondisi dikalikan -> AND. Untuk OR gunakan `+`. Contoh OR: Region JKT atau BDG: ``` =(C2:C100="JKT") + (C2:C100="BDG") ``` --- ## 10. UNIQUE + SORT + INDEX: top N tanpa Pivot Kamu mau ambil top 3 produk berdasarkan revenue. Langkah: 1. Buat table produk + total revenue (lihat sebelumnya). 2. Sort descending dan ambil 3 baris atas. **Excel modern** ``` =SORT( UNIQUEProductsWithRevenue , 2, -1 ) // asumsi kolom 2 = revenue =TAKE( SORT(...), 3 ) ``` **Google Sheets** ``` =QUERY( A1:D, "select B, sum(C*D) group by B order by sum(C*D) desc limit 3", 1) ``` `QUERY` ngasih cara cepat. --- ## 11. Matrix maths: MMULT, TRANSPOSE, SUMPRODUCT Array juga berguna untuk operasi matriks. Contoh: kalikan matriks (2x2) × (2x1) ``` MMULT( { {a,b}; {c,d} } , { {x}; {y} } ) ``` Praktis di Excel/Sheets: Jika A2:B3 berisi matriks 2x2 dan D2:D3 berisi vektor 2x1: ``` =MMULT(A2:B3, D2:D3) ``` Hasil akan spill menjadi dua baris (Excel modern) atau gunakan `Ctrl+Shift+Enter` di legacy Excel. `SUMPRODUCT` juga bisa implementasikan dot product: ``` =SUMPRODUCT(A2:B2, D2:E2) // dot product row ``` --- ## 12. Rumus bersarang (nested arrays) — contoh kasus nyata Skenario: dari data transaksi, ambil 5 produk dengan revenue tertinggi dalam periode tertentu. Pendekatan: * Buat tabel ringkasan `Produk | TotalRevenue` (UNIQUE + SUMIFS/SUMPRODUCT). * Sort descending lalu ambil top N (`TAKE` atau `INDEX+SEQUENCE`). * Atau gunakan `LARGE` + `INDEX/MATCH`. **Excel modern (contoh)** ``` =LET( prod, UNIQUE(B2:B100), revenue, MAP(prod, LAMBDA(p, SUMIFS(D2:D100, B2:B100, p))), sorted, SORT(HSTACK(prod, revenue), 2, -1), TAKE(sorted, 5) ) ``` Keren tapi perlu Excel 365. Untuk cara lebih kompatibel: * buat helper kolom `TotalRevenue` per produk, lalu `SORT` + `TAKE`. --- ## 13. Teknik: menghitung running total (cumulative) dengan array Running total per baris: Jika angka di B2:B10, ingin kolom C2:C10 berisi cumulative sum. **Excel modern** ``` =SCAN(0, B2:B10, LAMBDA(acc, x, acc + x)) // menghasilkan array cumulative ``` `SCAN` tersedia di Excel modern. Di Google Sheets, gunakan formula: ``` =ARRAYFORMULA(IF(B2:B="", "", MMULT( (ROW(B2:B) <= TRANSPOSE(ROW(B2:B))) * 1, N(B2:B) ))) ``` Itu agak rumit; sering praktis pakai helper kolom `=C1 + B2` drag. --- ## 14. Kasus nyata lengkap: memfilter penjualan bulan berjalan & ringkasan otomatis Dataset transaksi (A2:E): | OrderID | Tgl | Region | Produk | Total | | ------: | --- | ------ | -----: | ----: | Tujuan: buat rangkuman otomatis di sisi: `Total Bulan Ini`, `Top Product`, `Trend 6 bulan`. ### Total Bulan Ini (Excel/Sheets) ``` =SUM( FILTER( E2:E1000, TEXT(B2:B1000, "yyyy-mm") = TEXT(TODAY(),"yyyy-mm") ) ) ``` Atau gunakan `SUMIFS`: ``` =SUMIFS(E2:E1000, B2:B1000, ">=" & EOMONTH(TODAY(), -1)+1, B2:B1000, "<=" & EOMONTH(TODAY(), 0)) ``` ### Top Product (Google Sheets) ``` =QUERY(A1:E, "select D, sum(E) where B >= date '" & TEXT(EOMONTH(TODAY(), -5)+1,"yyyy-mm-dd") & "' group by D order by sum(E) desc limit 3", 1) ``` ### Trend 6 bulan (spark of array) Buat range months list: ``` =SEQUENCE(6,1, EOMONTH(TODAY(),-5)+1, 31) // tidak presisi; better to generate month starts ``` Kemudian SUMIFS per month via `MAP` or `BYROW` in Excel. --- ## 15. Debugging: common errors pada array formula * `#SPILL!` (Excel modern): hasil array tidak bisa spill karena ada sel berisi data di tempat tujuan. Solusi: hapus yang menghalangi atau pindahkan formula. * `#VALUE!`: tipe data tidak cocok (teks vs number) atau array operasi gagal. Cek `ISNUMBER`. * `#REF!`: range invalid, terutama kalau referensi dihapus. * Performance: rumus array yang berat (OFFSET/INDIRECT/volatile) pada ribuan baris bikin lemot. Gunakan helper columns atau Power Query untuk dataset besar. Debug tips: * Cek bagian formula step-by-step. Untuk `SUMPRODUCT`, coba buat kolom helper `=B2* C2` dan `SUM` sebagai verifikasi. * Di Google Sheets, gunakan `Ctrl+~` untuk lihat formula? (tidak, `Ctrl+~` toggle show formulas di Excel; di Sheets gunakan View → Show formulas). * Test pada subset data kecil dulu. --- ## 16. Best practice & UX friendly rules * Pakai **Table** di Excel agar range otomatis meluas. * Gunakan **named ranges** untuk readability, mis. `SalesTotal`. * Jangan pakai whole-column references (`A:A`) dalam array berat. * Jika rumus kompleks, tulis penjelasan di cell komentar. * Simpan versi cadangan saat eksperimen dengan rumus besar. * Untuk sharing ke teman yang masih pakai Excel lama, hindari formula eksklusif Excel 365 (SEQUENCE, LET, LAMBDA) atau berikan alternatif. --- ## 17. Performance tips * Gunakan `SUMPRODUCT` di tempat yang cocok (lebih cepat dari array CSE dalam banyak kasus). * Hindari volatile functions (`NOW`, `TODAY` pada banyak cell, `INDIRECT`, `OFFSET`, `RAND`). * Precompute helper columns jika operasi berulang. * Dalam dataset besar (ribuan baris), pertimbangkan Power Query (Excel) atau BigQuery (Google ecosystem) untuk agregasi. --- ## 18. Latihan praktis (soal & solusi) — bikin paham Kerjakan latihan berikut di filemu, lalu cek jawaban di bawah. ### Soal 1 Diberi kolom `Qty` (B2:B21) dan `Harga` (C2:C21). Buat rumus yang menghitung total revenue tanpa kolom helper. ### Solusi 1 ``` =SUMPRODUCT(B2:B21, C2:C21) ``` ### Soal 2 Dari data transaksi A2:E100, buat list produk unik (kolom) dan total revenue masing-masing, tanpa pivot. ### Solusi 2 (Google Sheets) ``` =QUERY(A2:E, "select D, sum(E) group by D", 0) ``` Solusi Excel modern: ``` =LET( prods, UNIQUE(D2:D100), totals, MAP(prods, LAMBDA(p, SUMIFS(E2:E100, D2:D100, p))), HSTACK(prods, totals) ) ``` Atau buat UNIQUE di F2 lalu `=SUMIFS(E2:E100, D2:D100, F2)` drag. ### Soal 3 Buat kolom cumulative sum untuk B2:B100 tanpa helper yg di-drag (pakai SCAN di Excel 365). ### Solusi 3 (Excel 365) ``` =SCAN(0, B2:B100, LAMBDA(a, x, a+x)) ``` --- ## 19. Cheat sheet pola rumus array penting * Dot product: `SUMPRODUCT(range1, range2)` * Conditional sum multi-kondisi: `SUMPRODUCT( (rangeA=valA) * (rangeB=valB) * (values) )` * Filter rows: `FILTER(range, condition)` * Unique list: `UNIQUE(range)` * Sorted unique: `SORT(UNIQUE(range))` * Generate sequence: `SEQUENCE(n)` * Matrix multiply: `MMULT(A, B)` * Transpose: `TRANSPOSE(range)` * Dynamic take: `TAKE(range, n)` (Excel modern) * Rolling sum: `SCAN` / `MMULT` trick / helper column --- ## 20. Advanced peek: LET & LAMBDA (Excel 365) `LET` membuat variabel dalam rumus supaya lebih rapi: ``` =LET( prodList, UNIQUE(B2:B100), totals, MAP(prodList, LAMBDA(p, SUMIFS(E2:E100, B2:B100, p))), HSTACK(prodList, totals) ) ``` `LAMBDA` memungkinkan bikin fungsi custom yang bisa dipakai ulang. Ini advanced tapi powerful untuk modular formula. --- ## 21. Ringkasan motivasi & langkah selanjutnya Array formula itu skill yang level-up banget buat kamu yang mau kerja cepat dengan spreadsheet. Mulai dari `SUMPRODUCT` untuk perhitungan cepat, sampai `FILTER`+`UNIQUE`+`SEQUENCE` untuk membangun laporan dinamis. Kalau kamu pake Excel modern, explore `LET`, `LAMBDA`, `SCAN`, `MAP` buat solusi elegan. Di Google Sheets, `ARRAYFORMULA`, `QUERY`, dan `SPARKLINE` juga bikin hidup lebih mudah. Langkah praktis: ambil satu file nyata (penjualan sekolah, katalog barang, nilai ulangan), coba ganti kolom helper ke array formula satu-per-satu. Latihan kecil tiap hari 15–30 menit bakal bikin kamu cepat mahir. Selamat ngoprek — kalau udah paham pola array, kerja spreadsheet mentok jadi gampang dan sering bikin teman/teman sekelas kagum.