ARRAYFORMULA(IF) di Google Sheets: Panduan Keuangan

Sintaksnya langsung membungkus: =ARRAYFORMULA(IF(logical_array, value_if_true, value_if_false)). Setiap argumen secara otomatis meluas ke seluruh baris yang diisi data. Satu perbedaan perilaku ini - ekspansi otomatis - yang membuatnya layak dipahami secara mendalam sebelum Anda membangun model dengan lebih dari 2 tab.

Pola Dasar ARRAYFORMULA(IF)

Mulailah dengan penjaga baris kosong (blank-row guard). Tanpanya, ARRAYFORMULA mengevaluasi setiap sel di kolom hingga baris ke-50.000 pada beberapa konfigurasi Sheets, dan Anda akan mendapat 50.000 label yang memenuhi kolom. Ini merusak setiap SUMIFS, COUNTIFS, dan pivot yang mereferensikannya di hilir.

=ARRAYFORMULA(
  IF('P&L'!B2:B="", "",
    IF('P&L'!B2:B="Revenue", 'P&L'!C2:C * Assumptions!$B$3, 0)
  )
)

IF luar menjaga baris kosong. IF dalam menangani logika yang sesungguhnya. Pola dua lapis ini adalah fondasi dari hampir setiap ARRAYFORMULA(IF) yang akan Anda tulis dalam model keuangan.

Berikut contoh yang lebih realistis: model contribution margin pada level SKU yang menandai produk dengan gross margin di bawah ambang batas yang telah disetujui manajemen sebesar 38,5%:

=ARRAYFORMULA(
  IF('P&L'!A2:A="", "",
    IF(('P&L'!C2:C - 'P&L'!D2:D) / 'P&L'!C2:C < Assumptions!$B$7,
      "Below Floor", "OK"
    )
  )
)

Di sini Assumptions!$B$7 menyimpan ambang batas 38,5%. SKU yang ditandai "Below Floor" masuk ke COUNTIFS untuk laporan margin bridge kepada direksi. Referensinya tetap aktif - ubah angka ambang batas di tab Assumptions dan flag akan otomatis diperbarui.

Perbandingan: ARRAYFORMULA(IF) vs. Pendekatan Lainnya

Sebelum masuk ke pola yang lebih kompleks, penting untuk memahami kapan harus menggunakan ARRAYFORMULA(IF) dibandingkan alternatif lain. Tabel berikut merangkum perbedaan praktisnya:

Kesimpulan praktis: gunakan ARRAYFORMULA(IF) untuk logika 2 hingga 4 tier yang perlu meluas otomatis ke seluruh kolom. Untuk lebih dari 4 tier, pertimbangkan pendekatan lookup berbasis tabel.

Menyusun Logika Bertingkat Tanpa Pusing

Menurut dokumentasi resmi Google Sheets untuk fungsi IF, batas nested IF adalah 64 level, meskipun keterbacaan sudah runtuh jauh sebelum itu. Untuk struktur bertingkat seperti level komisi, klasifikasi wilayah, atau tranche pencairan, Anda menumpuk IF di dalam IF:

=ARRAYFORMULA(
  IF('Sales'!C2:C="","",
    IF('Sales'!C2:C >= Assumptions!$B$3, "Tier 1",
      IF('Sales'!C2:C >= Assumptions!$B$4, "Tier 2",
        IF('Sales'!C2:C >= Assumptions!$B$5, "Tier 3",
          "Below Minimum"
        )
      )
    )
  )
)

Di mana $B$3 = Rp 21 miliar, $B$4 = Rp 8,5 miliar, $B$5 = Rp 2,5 miliar merupakan kuota target yang disimpan di tab Assumptions agar CFO dapat menyesuaikannya tanpa menyentuh logika formula sama sekali.

IFS akan memberikan sintaks yang lebih bersih di sini - satu fungsi dengan beberapa pasang kondisi-hasil. Namun berdasarkan dokumentasi Google Workspace untuk fungsi IFS, IFS tidak dapat meluas secara array di dalam ARRAYFORMULA per Juli 2026. Begitu pula SWITCH. Jika Anda membutuhkan logika multi-tier lebih dari 4 level, pilihan praktisnya adalah nested IF atau lookup ke tabel referensi menggunakan ARRAYFORMULA(VLOOKUP()) atau ARRAYFORMULA(INDEX(MATCH())). Pendekatan lookup lebih skalabel untuk 8 tier ke atas dan menjaga tab Assumptions sebagai satu-satunya sumber kebenaran.

Menangani Error di Dalam ARRAYFORMULA(IF)

Pembagian, lookup, dan parsing teks semuanya menghasilkan error ketika pembagi bernilai nol, target lookup tidak ditemukan, atau sel sumber kosong. Di dalam ARRAYFORMULA, satu baris yang bermasalah akan menyebarkan error secara visual ke seluruh kolom. Bungkus operasi yang berpotensi gagal dengan IFERROR:

=ARRAYFORMULA(
  IF('P&L'!B2:B="", "",
    IFERROR(
      ('P&L'!C2:C - 'P&L'!D2:D) / 'P&L'!C2:C,
      0
    )
  )
)

Posisi sangat penting. IFERROR ditempatkan di dalam ARRAYFORMULA, bukan di luarnya. Membungkus seluruh ARRAYFORMULA dengan IFERROR akan mengubah semua error menjadi 0, termasuk bug logika formula yang sebenarnya perlu Anda lihat saat proses pembangunan model. Bungkus hanya operasi spesifik yang berpotensi gagal.

Untuk perhitungan margin: nilai pendapatan Rp 0 pada bulan-bulan sebelum produk diluncurkan adalah sumber pembagian-oleh-nol yang umum. IFERROR(expr, 0) menanganinya dengan bersih, tetapi pertimbangkan apakah margin 0% secara makna berbeda dari "tidak ada data" untuk output Anda. Dalam model 36 bulan dengan 200 SKU, perbedaan itu akan terasa dalam trailing average dan analisis kohort.

Apa yang Tidak Bisa Dilakukan ARRAYFORMULA(IF)

Beberapa hal tidak berfungsi di dalam ARRAYFORMULA meski Anda mungkin mengharapkannya:

IFS dan SWITCH tidak meluas secara array. Formula hanya mengevaluasi baris pertama dan mengembalikan nilai tunggal (scalar), bukan kolom. Ini dikonfirmasi dalam dokumentasi ARRAYFORMULA Google Workspace, yang mencatat bahwa hanya fungsi tertentu yang mendukung ekspansi array penuh.

VLOOKUP dengan approximate match (range_lookup = TRUE) berperilaku tidak dapat diprediksi dalam konteks array. Gunakan INDEX(MATCH()) atau VLOOKUP dengan exact match.

Output multi-kolom dari satu cabang IF - ARRAYFORMULA mengembalikan satu kolom per formula. Jika logika kondisional Anda perlu mengisi kolom Revenue, COGS, dan Gross Margin secara terpisah, itu berarti 3 formula yang berbeda.

Fungsi volatile di dalam cabang IF - RAND(), NOW(), dan TODAY() di dalam ARRAYFORMULA akan menghitung ulang setiap kali ada perubahan pada sheet. Dalam model dengan 7.200 sel yang terhubung ke array formula, ini akan terasa sebagai keterlambatan yang nyata.

Performa pada Skala Besar

Model P&L 36 bulan dengan 200 SKU berarti sekitar 7.200 sel per kolom formula. Angka itu masih aman jika berdiri sendiri. Kalkulasi ulang mulai melambat ketika Anda merantai beberapa output ARRAYFORMULA: formula A memberi makan formula B yang memberi makan formula C. Array yang dirantai melipatgandakan biaya kalkulasi ulang.

Dua solusi praktis: evaluasi satu kali di kolom pembantu (helper column) lalu referensikan hasilnya ke hilir, dan bekukan periode yang sudah tutup dengan Paste Special lalu Values ketika bulan berakhir dan logikanya tidak akan berubah lagi.

Siklus iterasi pada ARRAYFORMULA(IF) - tulis, periksa kebocoran blank-row, debug error, sesuaikan logika, ulangi - adalah tempat waktu pertengahan kuartal habis terbuang. Coba ModelMonkey gratis selama 14 hari - tersedia untuk Google Sheets dan Excel.