Rumah di Solo

ikuti Saluran WhatsApp Rumahdisolo.com. Klik WhatsApp
Beranda
Artikel

Belajar Array Formula Dari Nol Hingga Mahir

Pelajari cara membuat rumus bersarang super efektif di Excel! Panduan praktis dari nol hingga mahir. Klik dan tingkatkan skill Excel kamu sekarang!

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):

Berikut hasil konversi tabel kamu ke HTML murni tanpa CSS, dengan `
` pembungkus seperti format sebelumnya:
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): Berikut versi HTML murni tanpa CSS, sesuai tabel yang kamu tulis:
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:
    2. =UNIQUE(B2:B6)
  2. TotalQty:
    3. =SUMIFS(C2:C6, B2:B6, F2#)  // F2# refers to spilled UNIQUE results
  3. TotalRevenue:
    4. =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.
    3. 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): Berikut versi HTML murni tanpa CSS dari tabel yang kamu tulis (hanya header, belum ada data):
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.

Siswi SMK Muhammadiyah 1 sukoharjo yang cerdas, Bersemangat, dan Berintegritas. Profil Lengkap saya