Dynamic array di Excel merevolusi cara kita membuat rumus, tidak perlu lagi menekan Ctrl+Shift+Enter atau menyalin rumus ke cell-cell lain. Cukup satu rumus dan Excel akan menumpahkannya ke cell-cell yang sesuai.
Artikel ini akan membahas pengertian, fitur utama, perbedaan dengan rumus array tradisional, serta penggunaan rumus dan fungsi-fungsi populer yang mendukung dynamic array.
File: dynamic-array-excel.xlsx.
Apa Itu Dynamic Array di Excel?
Dynamic array adalah fitur yang memungkinkan rumus Excel menggunakan atau menghasilkan lebih dari satu nilai sekaligus. Jika mengembalikan lebih dari satu nilai, hasil tersebut akan menumpah ke bawah atau ke samping sesuai bentuk array.
Fitur ini menggantikan formula array tradisional yang membutuhkan Ctrl+Shift+Enter (CSE). Dengan dynamic array, cukup tekan Enter, dan Excel akan secara otomatis menangani sisanya.
Dynamic array dapat ditemui di Excel 365, Excel Web, serta Excel 2021 ke atas. Excel lainnya tidak mendukung fitur ini.
3 Konsep Kunci dalam Dynamic Array
3 hal mandatori untuk memahami dynamic array:
- Rumus array: Rumus yang menghasilkan lebih dari satu nilai.
- Rentang tumpahan (spilled range): Sekumpulan cell hasil kembalian dari satu rumus dynamic array. Misal: B2#, menunjukkan seluruh tumpahan rumus di cell B2.
- Penanganan kesalahan tumpahan: Jika cell ke mana hasil tumpahan sudah terisi, Excel akan menampilkan error #SPILL!.
Perbedaan Dynamic Array VS Rumus Array Tradisional
| Fitur | Rumus Array Tradisional | Rumus Dynamic Array |
|---|---|---|
| Versi Excel | Excel 2019 ke bawah | Excel Web, Excel 2021 ke atas, dan Excel 365. |
| Input Rumus | Ctrl+Shift+Enter | Hanya Enter. |
| Hasil tumpahan | Manual. | Otomatis. |
| Update data | Butuh eksekusi ulang rumus. | Real-time dan otomatis. |
| Fungsi yang didukung | Terbatas | Hampir semua fungsi Excel. |
Dasar Rumus Dynamic Array Excel
Rumus Dynamic Array Dasar
Dalam bentuk paling sederhana, rumus dynamic array mengambil hanya sebagian atau seluruh cell-cell dari rentang sumber.
=A2:A6
Rumus Perhitungan Sederhana
Rumus-rumus berikut melakukan operasi aritmetika terhadap setiap angka di range A2:A6.
=A2:A6*10=A2:A6/2=A2:A6^2
Mengalikan 10 dengan setiap angka di A2:A6 disebut sebagai array broadcasting.
Array Broadcasting
Array broadcasting adalah sistem yang memperluas nilai tunggal atau array dengan dimensi lebih kecil agar sesuai dengan array dengan dimensi lebih besar.
Rumus berikut menggabungkan 3 baris dan 5 kolom menjadi array 5×3—di setiap nilai vertikal dikombinasikan dengan horisontal.
=A1:A3&B2:B6
Rumus dengan Dua Array
Rumus berikut menjumlahkan elemen dari baris-baris yang sama di dua rentang berbeda.
=A2:A6+B2:B6
Menggunakan Array Konstan
Rumus array dinamis juga berlaku untuk array konstan.
Rumus mengalikan array statis dengan angka 10.
={1; 2; 3; 4; 5}*10
Cara Menggunakan Rumus Dynamic Array
Array Dinamis dengan Fungsi IF
Setiap elemen A4:A8 dibandingkan dengan B1 menggunakan IF; hasilnya Ya jika lebih besar, dan Tidak jika tidak.
=IF(A4:A8>B1; "Ya"; "Tidak")
FILTER dan SORT
Fungsi berikut menyaring produk yang terjual lebih dari 15.000 dan mengurutkan hasilnya.
=SORT(FILTER(A2:A8; B2:B8>15000))
Fungsi INDEX dan SEQUENCE: Mengurutkan Data
Rumus berikut menampilkan nama penjual diurutkan dari penjualan tertinggi. Fungsi ini menggabungkan urutan otomatis (SEQUENCE) dengan urutan data (SORTBY).
=INDEX(A2:A6; SORTBY(SEQUENCE(COUNTA(B2:B6)); -B2:B6))
Fungsi SUM dan Dynamic Array
Di atas, SORT dan FILTER menerima array hasil fungsi lain.
Dengan memanfaatkan array kembalian, kita dapat menjumlahkan, mencari rata-rata, atau bentuk olahan lain.
Rumus berikut mencari 3 besar penjualan dan menjumlahkannya.
=SUM(LARGE(B2:B6; SEQUENCE(3)))
Kompatibilitas dengan Dynamic Array
Banyak fungsi seperti SUM, AVERAGE, MIN, dan lain-lainnya menerima dan mengolah array dari rumus dinamis.
Namun, tidak semua fungsi mendukung dynamic array sebagai input. Contoh: SUMIF.
SUMIF(range; criteria; [sum_range])
Argumen range dan sum_range harus berupa referensi range nyata, bukan array hasil dari fungsi lain.
Beberapa sintaks tidak menyatakan secara langsung. Contoh:
SUM(number1; [number2];...)
Jika demikian, cara terbaik adalah mencobanya langsung di dalam rumus.
Penting juga untuk mengetahui alternatif dari setiap rumus. Contoh: Gunakan SUM dan IF sebagai alternatif SUMIF.
Kesimpulan
Dynamic array membuat formula di Excel lebih intuitif, efisien, dan fleksibel. Dengan satu rumus, kita bisa memproses dan menampilkan banyak data sekaligus.
Fitur seperti array broadcasting, FILTER, SORT, SEQUENCE, dan tumpahan otomatis membuka kemungkinan baru untuk analisis data yang lebih canggih—tanpa perlu VBA atau add-in.