Panduan Dynamic Arrays di Excel Modern

Mau nge-boost skill Excel biar kerjaan makin cepat dan rapi? Dynamic Arrays di Excel Modern (Office 365 / Excel 2021 ke atas) itu senjata wajib.

Dengan fitur ini kamu bisa bikin rumus yang otomatis me-“spill” ke beberapa sel, bikin daftar unik otomatis, filter data tanpa pivot, membuat top-N dengan sekali klik, dan banyak lagi — tanpa ribet.

Artikel ini bakal jelasin semuanya dari nol sampai trik mahir, plus contoh tabel nyata, rumus siap-copas, troubleshooting, dan latihan.

Santai aja bahasa-nya, cocok buat remaja Indonesia yang pengen paham praktik langsung.

Sekilas: Apa itu Dynamic Arrays?

Dynamic Arrays adalah kemampuan Excel untuk menghasilkan array hasil dari sebuah rumus dan secara otomatis menyebarkannya (spill) ke sel-sel sekitarnya. Sebelumnya, kalau rumus menghasilkan lebih dari 1 nilai, kamu harus pakai `Ctrl+Shift+Enter` (CSE) atau helper columns. Sekarang, fungsi baru seperti `FILTER`, `UNIQUE`, `SORT`, `SEQUENCE`, `RANDARRAY`, `TAKE`, `DROP`, `XLOOKUP`, `MAP`, `BYROW`, `SCAN`, dsb. akan mengeluarkan hasil ke rentang sel yang tepat tanpa langkah ekstra.

Contoh singkat:

=SEQUENCE(5)

Hasil: akan menuliskan 1,2,3,4,5 secara vertikal mulai dari sel tempat kamu ketik rumus.

Mengerti konsep “Spill” (penyebaran hasil)

• Cell formula menulis satu rumus. Jika rumus menghasilkan array lebih dari satu elemen, Excel otomatis akan menyebarkannya ke sel di sebelah atau di bawah — inilah yang disebut spill.
• Spill range punya kotak biru bila kamu memilihnya; sel tempat rumus awal berada (top-left) berisi rumus, sel lain terisi hasil secara otomatis.
• Operator `#` digunakan untuk merujuk ke seluruh spill range. Contoh: kalau hasil `=FILTER(...)` di sel F2 dan spill sampai F2:F10, kamu bisa refer ke seluruh hasil dengan `F2#`.

Masalah umum: kalau ada sesuatu menghalangi sel yang harus di-spill maka muncul error `#SPILL!`. Kita bahas cara atasi di bagian troubleshooting.

Kenalan dengan fungsi-fungsi Dynamic Array yang sering dipakai

Berikut daftar fungsi Dynamic Array paling berguna dengan contoh singkat. Nanti masing-masing akan dibahas lebih detail.

• `FILTER(range, include, [if_empty])` — ambil baris sesuai kondisi.
• `UNIQUE(range, [by_col], [exactly_once])` — ambil nilai unik.
• `SORT(range, [sort_index], [order], [by_col])` — sortir data.
• `SORTBY(range, by_range1, [order1], ...)` — sortir berdasarkan kolom lain.
• `SEQUENCE(rows, [cols], [start], [step])` — buat deret angka.
• `RANDARRAY(rows, [cols], [min], [max], [integer])` — angka acak di array.
• `TAKE(range, rows, [cols])` & `DROP(range, rows, [cols])` — ambil / buang bagian array.
• `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` — lookup modern, array-aware.
• `UNION`/`VSTACK`/`HSTACK`/`WRAPROWS`/`WRAPCOLS` — gabung/melayout arrays (tergantung versi Excel).
• `LET(name, value, calculation)` — definisikan variabel di rumus untuk baca lebih gampang & performa lebih baik.
• `LAMBDA(...)` & `MAP`, `BYROW`, `BYCOL`, `SCAN`, `REDUCE` — fungsi tingkat lanjut untuk olah array per-row/kolom.

Sekarang kita kupas satu-satu dengan contoh nyata.

Contoh dataset awal — kita pakai ini sepanjang artikel

Salin tabel ini ke sheet Excel mulai A1:

prompt taroh sini

Kolom `Total` (G) bisa dihitung dengan `=E2*F2` lalu fill down, atau kita akan tunjukkan cara tanpa helper column nanti.

1) FILTER — ambil baris yang cocok dengan kriteria

excel
=FILTER(A2:G8, C2:C8="JKT", "Tidak ada data")

• `range` = A2:G8 (semua kolom yang mau ditampilkan).
• `include` = kondisi boolean C2:C8="JKT".
• Hasil: semua baris yang region-nya JKT akan otomatis tersusun (spill) di sel tempat kamu menulis rumus.

excel
=FILTER(A2:G8, (C2:C8="JKT") * (E2:E8>=3), "Tidak ada data")

Catatan: `*` berfungsi sebagai AND. Untuk OR gunakan `+`.

Gunakan untuk: membuat view per sales, filter customer, laporan bulanan dengan satu rumus.

2) UNIQUE — daftar nilai unik

Tujuan: bikin daftar produk unik yang ada di data.

excel
=UNIQUE(D2:D8)

Hasil: baris berisi Pensil, Buku, Pulpen, Penghapus (unik).

Varian: `UNIQUE(range, [by_col], [exactly_once])`

• `exactly_once=TRUE` akan mengembalikan nilai yang muncul persis satu kali (menghilangkan yang duplikat banyak).

excel
=SORT(UNIQUE(D2:D8))

Ini berguna untuk membuat dropdown dinamis.

3) SORT & SORTBY — mengurutkan hasil

excel
=SORT(A2:G8, 2, 1)  // sort range by column 2 (Tgl) ascending (1)

4) SEQUENCE — buat deret angka otomatis

excel
=SEQUENCE(12,1,1,1)

excel
=SEQUENCE(1,10,1,1)

Kombinasi `SEQUENCE` dengan `DATE` bisa bikin list tanggal bulanan otomatis, lalu digunakan di fungsi lain.

5) XLOOKUP — lookup modern dan array-aware

`XLOOKUP` menggantikan VLOOKUP/HLOOKUP dengan lebih fleksibel.

Contoh: cari harga produk "Pulpen"

excel
=XLOOKUP("Pulpen", D2:D8, F2:F8, "Tidak ditemukan")

`XLOOKUP` juga bisa mengembalikan array (mis. semua kolom info) jika `return_array` berupa range multi-kolom.

Misal:

excel
=XLOOKUP("Pensil", D2:D8, A2:G8)

Hasilnya akan spill seluruh baris pertama yang cocok.

6) Gabungkan FILTER + UNIQUE + SORT untuk ringkasan cepat (tanpa Pivot)

Goal: Buat ringkasan `Produk | TotalQty | TotalRevenue` tanpa pivot.

Langkah:

1. Dapatkan produk unik:

excel
=UNIQUE(D2:D8) // taruh di J2

2. Hitung TotalQty per produk:

excel
=SUMIFS(E2:E8, D2:D8, J2#)  // J2# merujuk spill range unik

Namun `SUMIFS` tidak array-aware di semua konteks untuk menerima J2#. Jadi metode yang reliable di Excel modern:

excel
=LET(
  prods, UNIQUE(D2:D8),
  totalsQty, MAP(prods, LAMBDA(p, SUMIFS(E2:E8, D2:D8, p))),
  totalsRevenue, MAP(prods, LAMBDA(p, SUMIFS(G2:G8, D2:D8, p))),
  HSTACK(prods, totalsQty, totalsRevenue)
)

Penjelasan:

• `prods` adalah daftar unik.
• `MAP` pakai LAMBDA untuk setiap produk hitung SUMIFS.
• `HSTACK` gabungkan jadi tabel. (Jika tidak ada `HSTACK` di versi Excel kamu, gunakan create columns terpisah atau `INDEX` trick.)

Jika kamu belum pakai LET/LAMBDA, bisa buat helper kolom: unique list di J, lalu di K2 pakai `=SUMIFS(E:E, D:D, J2)` lalu drag.

7) TAKE & DROP — ambil/buang bagian array

Contoh: kamu sudah punya daftar bulanan di A1:A24 (24 bulan), mau ambil 6 bulan terakhir:

excel
=TAKE(A1:A24, -6)  // ambil 6 dari akhir

Atau buang 12 pertama:

excel
=DROP(A1:A24, 12)

Keduanya berguna ketika mengelola time series yang terus bertambah.

8) SCAN, REDUCE, MAP, BYROW — olah array maju ke fungsi LAMBDA

Ini fitur advanced di Excel 365. Contoh penggunaan:

Cumulative sum (running total) pake SCAN

Jika monthly numbers di B2:B13:

excel
=SCAN(0, B2:B13, LAMBDA(acc, x, acc + x))

Hasil: array cumulative yang spill.

Hitung sesuatu per baris dengan MAP

Misal kamu punya dua kolom A dan B, mau buat kolom C = A*B per baris tapi menggunakan LAMBDA:

excel
=MAP(A2:A8, B2:B8, LAMBDA(a,b, a*b))

Hasilnya array produk tiap baris.

MAP/BYROW/BYCOL cocok buat transformasi kompleks tanpa helper column.

9) Contoh nyata: Top 3 produk berdasarkan revenue otomatis

Kita punya data transaksi. Langkah cepat:

Metode QUERY (Sheets) atau Pivot (Excel) — gampang.

Tapi pakai Dynamic Arrays di Excel:

1. Buat ringkasan produk total revenue:

excel
=LET(
  prods, UNIQUE(D2:D1000),
  rev, MAP(prods, LAMBDA(p, SUMIFS(G2:G1000, D2:D1000, p))),
  table, HSTACK(prods, rev),
  sorted, SORT(table, 2, -1),
  TAKE(sorted, 3)

Penjelasan: hasil akan memberi 3 baris teratas, tanpa pivot.

Jika versi Excelmu tidak support `HSTACK`, alternatif: buat UNIQUE di J dan `=SUMIFS(G:G,D:D,J2)` di K lalu sort+take.

10) Membuat Dropdown dinamis (data validation) otomatis memakai spill

1. Buat daftar unik:

excel
=UNIQUE(D2:D100)

2. Nama range: pilih sel hasil UNIQ (misal J2#) lalu di Name Manager beri nama `ProductList`. Atau refer langsung `=J2#` di data validation.
3. Data Validation → List → source `=J2#` (atau `=ProductList`)

Sekarang dropdown otomatis update saat data baru masuk.

11) Menghubungkan Chart dengan Spill range (dynamic chart)

Kalau kamu mau chart otomatis update saat data bertambah:

• Buat range hasil Dynamic Array (misal `Months` di H2#, `Revenue` di I2#).
• Pilih chart → di Select Data → untuk series gunakan formula yang menunjuk ke spill `=Sheet1!I2#` (ketik manual di kotak range kalau perlu) atau gunakan named range yang merujuk ke `I2#`.

Chart akan ikut expand saat spill berubah.

12) Kesalahan umum & cara mengatasinya (Troubleshooting)

`#SPILL!`

Penyebab:

• Ada data yang menghalangi spill range.
• Terdapat merged cell di rentang spill.
• Array yang dihasilkan terlalu besar (melewati limit sheet).

Solusi: hapus isi sel yang menghalangi, unmerge cells, pindahkan rumus, atau cek ukuran array.

`#CALC!`

Penyebab:

• Rumus array error runtime (mis. LAMBDA mengalami masalah).

Solusi: cek input, debug bagian LAMBDA, pecah rumus jadi bagian kecil.

`#VALUE!` atau `#N/A`

Penyebab:

• Operasi antar tipe tidak kompatibel (mis. teks dikalikan angka).
• Lookup tidak menemukan nilai.

Solusi: pakai `IFERROR`, `IFNA`, atau cek `ISNUMBER`, `ISTEXT` terlebih dahulu.

`#NAME?`

Penyebab:

• Fungsi tidak dikenal (versi Excel belum support fungsi baru).

Solusi: update Excel atau gunakan alternatif (QUERY di Sheets, Pivot di Excel).

13) Performance: tips supaya worksheet tetap cepat

• Hindari fungsi volatile (mis. `OFFSET`, `INDIRECT`, banyak `NOW`, `RAND`) di rentang besar.
• Gunakan `LET` untuk menyimpan nilai antara sehingga tidak dihitung berulang. Contoh:

excel
=LET(
  data, A2:A1000,
  cond, data>100,
  SUM(IF(cond, data, 0))
)

• Untuk dataset besar (>10k rows), pertimbangkan Power Query untuk transformasi dan hanya gunakan Dynamic Array untuk ringkasan.
• Batasi `MAP`/`BYROW` yang melakukan operasi berat di ribuan baris; gunakan aggregation (SUMIFS) jika mungkin.

14) Gabungan fungsi yang sering dipakai (pattern & contoh)

Pattern A — FILTER + SORT + UNIQUE (lihat rekap custom)

Ambil 5 produk teratas di region JKT berdasarkan revenue:

excel
=TAKE(
  SORT(
    HSTACK(
      UNIQUE(FILTER(D2:D1000, C2:C1000="JKT")), 
      MAP(UNIQUE(FILTER(D2:D1000, C2:C1000="JKT")), LAMBDA(p, SUMIFS(G2:G1000, D2:D1000, p, C2:C1000, "JKT")))
    ), 
  2, -1),
3)

Panjang tapi powerful.

Pattern B — kriteria berganda dengan FILTER

Ambil transaksi JKT atau BDG dengan total > 10000:

excel
=FILTER(A2:G1000, ((C2:C1000="JKT") + (C2:C1000="BDG")) * (G2:G1000>10000), "Tidak ada")

Pattern C — rolling window dengan TAKE + SEQUENCE

Buat rolling 3-month sum untuk series bulanan di B2:B24:

excel
=MAP(SEQUENCE(ROWS(B2:B24)-2,1,3), LAMBDA(i, SUM(OFFSET(B2, i-3, 0, 3))))

(Ini contoh; OFFSET mungkin volatile, ada juga metode non-volatile dengan MMULT/TRANSPOSE.)

15) Contoh studi kasus lengkap: dashboard mini otomatis

Goal: buat dashboard kecil yang tunjukkan:

• Total Revenue Bulan Ini
• Top 3 Produk Bulan Ini
• Trend 6 bulan (sparkline di cell)
• Tabel transaksi filterable per region

Langkah ringkas:

1. Total Bulan Ini:

excel
=SUM(FILTER(G2:G1000, TEXT(B2:B1000,"yyyy-mm") = TEXT(TODAY(),"yyyy-mm")))

2. Top 3 Produk Bulan Ini:

excel
=LET(
  m, TEXT(TODAY(),"yyyy-mm"),
  trans, FILTER(A2:G1000, TEXT(B2:B1000,"yyyy-mm")=m),
  prods, UNIQUE(INDEX(trans,0,4)),
  revs, MAP(prods, LAMBDA(p, SUMIFS(INDEX(trans,0,7), INDEX(trans,0,4), p))),
  sorted, SORT(HSTACK(prods,revs),2,-1),
  TAKE(sorted,3)
)

3. Trend 6 bulan (summary):
• Buat array months:

excel
=TEXT(SEQUENCE(6,1, EOMONTH(TODAY(),-5)+1, 31),"yyyy-mm")  // atau generate properly month starts

• For each month SUMIFS and output as array then use sparkline chart referencing spill.
• Tabel transaksi per region:

excel
=FILTER(A2:G1000, C2:C1000 = $B$1, "Tidak ada transaksi") // where B1 is cell dengan selected region

Dashboard ini update otomatis saat data baru ditambah.

16) Latihan praktek (soal + solusi singkat)

Soal A

Buat daftar produk unik dan total qty masing-masing tanpa pivot.

• `=UNIQUE(D2:D100)` → spill ke J2.
• `=SUMIFS(E2:E100,D2:D100,J2#)` (atau pakai MAP/LET).

Soal B

Tampilkan baris transaksi untuk produk “Pensil” dan “Pulpen” saja, urut berdasarkan tanggal descending.

excel
=SORT(
  FILTER(A2:G100, (D2:D100="Pensil") + (D2:D100="Pulpen")),
  2, -1
)

Soal C

Hitung cumulative revenue per month (array hasil 6 bulan terakhir).

• Generate months list dengan `SEQUENCE` atau `EDATE` + MAP to SUMIFS per month.
• Atau gunakan `LET` + `MAP` untuk mapping month -> SUMIFS.

17) Perbedaan penting dengan Google Sheets (sedikit catatan)

• Banyak fungsi dynamic array di Excel modern juga ada di Google Sheets (`FILTER`, `UNIQUE`, `SORT`, `SEQUENCE`), tetapi sintaks `ARRAYFORMULA` di Sheets kadangkala diperlukan untuk hasil berskala.
• Excel 365 punya `LET`, `LAMBDA`, dan family `MAP/BYROW/SCAN` yang lebih kaya dibanding Sheets (meskipun Sheets juga berkembang).
• Untuk kompatibilitas, jika kamu share file ke teman yang masih pakai Excel lama, hindari fungsi-fungsi terbaru atau sediakan fallback (helper tables/pivot).

18) Tips UX & best practice (biar sheet enak dipakai orang lain)

• Gunakan tabel (Ctrl+T) untuk data mentah. Banyak fungsi lebih mudah dirujuk jika pakai structured references.
• Tandai cell input (mis. pakai warna background) supaya user tahu mana yang harus diubah.
• Nama range untuk memudahkan baca rumus (`DataTransaksi`, `ListProduk`).
• Gunakan LET untuk memecah rumus panjang supaya mudah dibaca.
• Tambahkan komentar atau small README sheet yang jelaskan bagian-bagian penting.
• Hindari rumus yang bisa bikin lag di dataset besar — gunakan Power Query untuk transformasi besar.

19) Debugging rumus dynamic array — checklist cepat

• Apakah cell hasil spill terhalang? → hapus yang menghalangi.
• Apakah ada merged cell di path spill? → unmerge.
• Apakah fungsi tidak dikenali → cek versi Excel.
• Gunakan `EVALUATE FORMULA` (Formula → Evaluate Formula) untuk breakdown.
• Pisahkan rumus kompleks (pakai LET) sehingga bisa cek bagian per bagian.

20) Kesimpulan & langkah selanjutnya

Dynamic Arrays bikin kerja di Excel jauh lebih efisien: bikin ringkasan tanpa Pivot, dropdown dinamis, chart yang auto update, dan banyak automasi lain. Kunci cepat mahir:

1. Pahami dasar spill & operator `#`.
2. Kuasai `FILTER`, `UNIQUE`, `SORT`, `SEQUENCE`.
3. Pelajari `LET` & `LAMBDA` untuk membuat rumus rapi & cepat.
4. Coba contoh praktis di dataset nyata (toko online, nilai ulangan, stok barang).
5. Kalau lemot, pindahkan transformasi berat ke Power Query.

Praktikkan satu use-case nyata — misalnya bikin dashboard penjualan dari dataset di awal — dan modifikasi rumus sampai dashboard update otomatis tiap kali kamu tambah baris transaksi.

Dalam waktu singkat, kamu bakal ngerasa Excel jadi alat yang nggak cuma kuat, tapi juga asik dipakai.

Selamat ngoprek Dynamic Arrays — kamu bakal heran betapa sedikit waktu yang terbuang setelah ngerti pola-pola ini!