Untuk model tiga laporan keuangan atau DCF sindikasi bank dengan tab yang saling terhubung, keandalan ini bukan sekadar fitur tambahan. Ini adalah perbedaan antara model yang diaudit dengan bersih dan model yang runtuh di slide ketiga presentasi dewan direksi.
Cara Kerja ARRAYFORMULA
Formula standar di B2 hanya mengevaluasi B2. Salin ke B2:B5001 dan Anda punya 5.000 sel individual, masing-masing merupakan potensi sumber penyimpangan jika seseorang mengedit baris 847 tanpa menyadarinya.
ARRAYFORMULA membungkus formula tunggal tersebut dan menyebarkannya ke seluruh rentang. Hasilnya: satu formula, satu sumber kebenaran, satu sel untuk diperbarui.
// Pendekatan standar - 5.000 sel, 5.000 titik kegagalan
B2: =IF(A2="Pendapatan", C2*Assumptions!$B$4, 0)
... disalin ke B5001
// ARRAYFORMULA - satu sel, seluruh kolom
B2: =ARRAYFORMULA(IF(A2:A="Pendapatan", C2:C*Assumptions!$B$4, 0))
Rentang terbuka A2:A berarti formula secara otomatis mencakup setiap baris baru yang ditambahkan di bawah. Ini krusial ketika sumber data Anda adalah feed GL langsung atau ekspor bulanan yang terus bertambah setiap tutup periode.
ARRAYFORMULA Lintas Tab dalam Model Google Sheets Multi-Tab
Nilai sesungguhnya ARRAYFORMULA dalam model keuangan yang serius terlihat pada lookup lintas tab. Bayangkan Anda perlu contribution margin per SKU di tab Analisis Retur, yang menarik data dari tab P&L dan tab Assumptions secara bersamaan.
// Returns Analysis!D2 - laba kotor per lini produk, seluruh kolom dalam satu formula
=ARRAYFORMULA(
SUMIFS('P&L'!E:E, 'P&L'!B:B, 'Returns Analysis'!A2:A, 'P&L'!C:C, ">=" & Assumptions!$B$3)
- SUMIFS('P&L'!F:F, 'P&L'!B:B, 'Returns Analysis'!A2:A, 'P&L'!C:C, ">=" & Assumptions!$B$3)
)
Formula ini menarik pendapatan dan COGS dari tab P&L, memfilter berdasarkan lini produk dan batas tanggal dari Assumptions, lalu mengembalikan seluruh kolom angka laba kotor dalam satu formula. Jika tab P&L Anda mendapat 12 SKU baru pada kuartal berikutnya, formula langsung mengambilnya secara otomatis.
Untuk sensitivitas runway terhadap kecepatan penambahan karyawan, pola yang sama berlaku:
// Headcount!G2 - kumulatif burn di setiap skenario staffing
=ARRAYFORMULA(
MMULT(
Scenarios!$C$2:$E$13, // 12 bulan × 3 skenario
TRANSPOSE(Assumptions!$D$5:$D$7) // loaded cost per role
) + SUMIF('Fixed Costs'!A:A, "Overhead", 'Fixed Costs'!C:C)
)
Fungsi yang Kompatibel dan yang Tidak
Tidak semua fungsi merespons ARRAYFORMULA. Tabel berikut adalah yang seharusnya saya miliki dari awal, sebelum menghabiskan dua jam mencoba membungkus VLOOKUP di dalamnya.
| Fungsi | Kompatibel dengan ARRAYFORMULA | Catatan |
|---|---|---|
IF | ✅ Ya | Penggunaan utama |
SUMIFS | ✅ Ya | Mengembalikan array of sums |
IFERROR | ✅ Ya | Membungkus seluruh rentang |
TEXT, VALUE, LEN | ✅ Ya | Fungsi teks/matematika standar |
VLOOKUP | ⚠️ Parsial | Bekerja tapi sering melewatkan baris terakhir; gunakan INDEX/MATCH |
INDEX/MATCH | ✅ Ya | Pilihan utama untuk array lookup |
UNIQUE | ❌ Tidak | Sudah array-native; nesting menyebabkan error |
FILTER | ❌ Tidak | Sama, ini adalah array function |
SORT | ❌ Tidak | Sama |
QUERY | ❌ Tidak | Tidak kompatibel |
Perilaku ini didokumentasikan dalam Google Sheets Function List (support.google.com/docs/table/25273, diakses Juli 2026): fungsi yang digambarkan sebagai "returning an array" sudah beroperasi dalam konteks array dan tidak memerlukan, serta tidak akan menerima, pembungkus ARRAYFORMULA. Pola yang sama dikonfirmasi dalam Google Workspace Developer Documentation untuk Apps Script dan Sheets API (developers.google.com/workspace, diakses Juli 2026), yang secara eksplisit memisahkan "array functions" dari "functions that accept array arguments."
Performa ARRAYFORMULA pada Skala Besar di Google Sheets
Google Sheets memiliki batas 10 juta sel. Tab detail GL dengan 50.000 baris dan 20 kolom klasifikasi berbasis ARRAYFORMULA masih jauh di bawah batas itu, tetapi waktu penghitungan ulang adalah kendala yang sesungguhnya.
Dari pengalaman langsung, ARRAYFORMULA yang terstruktur dengan baik di 50.000 baris dengan 2-3 referensi lintas tab memerlukan 3-8 detik untuk recalculate pada keseluruhan sheet. Formula individual sebanyak 50.000 sel seringkali membutuhkan 45-90 detik, dan terkadang membuat tab crash sepenuhnya.
Keuntungan performa datang dari beberapa pilihan struktural:
Gunakan rentang kolom terbuka secukupnya. A2:A memang praktis, tetapi memaksa Sheets mengevaluasi seluruh kolom setiap recalculate. Jika dataset Anda terbatas, misalnya 5.000 baris di tab aktual kuartalan, gunakan A2:A5001 secara eksplisit.
Hindari nesting ARRAYFORMULA di dalam ARRAYFORMULA. Satu pembungkus luar sudah cukup. Nesting bersifat redundan dan memperlambat evaluasi.
Simpan formula dalam satu sel, bukan dibungkus di sekitar kolom pembantu. Kolom pembantu yang menjadi input ARRAYFORMULA lain tidak masalah. Tetapi menggunakan ARRAYFORMULA untuk menghasilkan satu kolom, lalu membungkusnya dalam ARRAYFORMULA kedua di kolom lain, menggandakan biaya recalculation tanpa manfaat apa pun.
Pola yang Paling Sering Merusak Model
Kesalahan senilai Rp 23 miliar dalam laporan kuartal ketiga yang pernah saya lihat berasal dari ini: SUMIFS tanpa ARRAYFORMULA di tab ringkasan, yang mencari nilai dari tab detail di mana seseorang telah mengetik manual di 3 baris di tengah kolom. Rentang formula berhenti tepat di baris sebelum entri manual tersebut. Tidak ada yang menyadarinya sampai perhitungan covenant bank menghasilkan angka yang salah.
ARRAYFORMULA tidak sepenuhnya mencegah override manual, tetapi membuat pelanggarannya langsung terlihat. Ketika formula satu sel mengatur seluruh kolom, entri manual di kolom tersebut memunculkan error konflik (#REF! atau penimpaan yang secara diam-diam merusak pola formula). Itu terlihat jelas. Penyimpangan diam-diam dari copy-paste 5.000 baris tidak.
Kapan Menggunakan ARRAYFORMULA vs. QUERY vs. Fungsi Array Native
Pilihan ini bergantung pada apa yang Anda lakukan dengan data tersebut.
ARRAYFORMULA adalah alat yang tepat ketika Anda perlu menerapkan formula kalkulasi atau klasifikasi ke setiap baris dalam kolom: kategorisasi pendapatan, loaded cost per baris headcount, atau penanda varians periode ke periode.
QUERY (khusus Google Sheets) lebih baik untuk agregasi dan filter di mana Anda sebaliknya akan menulis tumpukan SUMIFS/COUNTIFS bersarang. Sintaksnya mirip SQL dan menangani pengelompokan dengan rapi, tetapi lebih lambat pada rentang besar dan tidak kompatibel dengan ARRAYFORMULA.
Fungsi array native (FILTER, UNIQUE, SORT, SEQUENCE) dibuat khusus untuk tugasnya dan lebih cepat. Jika Anda perlu mengekstrak daftar unik cost center dari GL 20.000 baris, UNIQUE('GL Detail'!B2:B) mengalahkan apa pun yang bisa Anda bangun dengan ARRAYFORMULA.
Pembagian praktis untuk model tiga laporan keuangan: ARRAYFORMULA mengatur kolom klasifikasi dan kalkulasi di tingkat baris, fungsi native menangani summary lookup dan daftar unik, QUERY menangani agregasi ad-hoc yang sebaliknya akan Anda kerjakan di pivot.
Untuk menulis pola ARRAYFORMULA lintas tab yang kompleks dengan cepat, ModelMonkey menyusunnya di sidebar berdasarkan deskripsi bahasa sederhana tentang kebutuhan Anda. Berguna ketika Anda sedang bekerja tiga tab di dalam model LBO 12 tab dan tidak ingin mengurai offset kolom secara manual hanya untuk menyusun formula dari awal.
FAQ: ARRAYFORMULA di Google Sheets
Apakah ARRAYFORMULA memperlambat Google Sheets?
Tidak, justru sebaliknya dalam sebagian besar kasus nyata. Satu ARRAYFORMULA di 50.000 baris umumnya recalculate dalam 3-8 detik. Jumlah formula individual yang setara, yaitu 50.000 sel terpisah, sering membutuhkan 45-90 detik dan terkadang menyebabkan tab crash. Pengecualiannya adalah ARRAYFORMULA dengan rentang kolom terbuka (A2:A) di dalam workbook dengan banyak tab aktif; dalam skenario itu, membatasi rentang ke batas baris aktual (misalnya A2:A5001) akan memangkas waktu recalculate secara signifikan.
Apa perbedaan ARRAYFORMULA dan FILTER di Google Sheets?
ARRAYFORMULA adalah pembungkus yang memperluas formula kalkulasi standar agar beroperasi di seluruh rentang sekaligus. FILTER adalah fungsi array native yang mengekstrak subset baris berdasarkan kondisi tertentu dan sudah beroperasi dalam konteks array secara default. Keduanya tidak kompatibel satu sama lain: membungkus FILTER di dalam ARRAYFORMULA akan menghasilkan error. Gunakan ARRAYFORMULA untuk kalkulasi per baris (IF, SUMIFS, operasi aritmetika), dan gunakan FILTER ketika Anda perlu menarik subset baris berdasarkan kriteria tertentu.
Bagaimana cara menggabungkan ARRAYFORMULA dengan SUMIFS lintas tab?
Tempatkan ARRAYFORMULA sebagai pembungkus terluar, lalu gunakan rentang kolom terbuka di argumen sum_range dan criteria_range di dalam SUMIFS. Contoh untuk menjumlahkan pendapatan per lini produk dari tab P&L:
=ARRAYFORMULA(
SUMIFS('P&L'!C:C, 'P&L'!B:B, 'Summary'!A2:A)
)
Pola ini mengembalikan seluruh kolom hasil dalam satu formula. Pastikan semua rentang dalam SUMIFS memiliki dimensi yang konsisten (semuanya kolom terbuka atau semuanya rentang eksplisit dengan jumlah baris sama) agar tidak menghasilkan error #VALUE!.
Apakah ARRAYFORMULA bisa digunakan bersama INDEX/MATCH untuk lookup multi-kriteria?
Ya, dan ini adalah kombinasi yang direkomendasikan sebagai pengganti VLOOKUP dalam konteks array. VLOOKUP di dalam ARRAYFORMULA sering melewatkan baris terakhir dan berperilaku tidak konsisten pada rentang besar. Sebagai gantinya, gunakan pola INDEX/MATCH dengan operator * untuk multi-kriteria:
=ARRAYFORMULA(
INDEX('Master'!C:C,
MATCH(1, ('Master'!A:A=Summary!A2:A) * ('Master'!B:B=Summary!B2:B), 0)
)
)
Pada dataset di atas 10.000 baris, pertimbangkan untuk membatasi rentang pencarian ke batas baris aktual agar waktu recalculate tetap terkendali.