Formula / Google Sheet · October 30, 2022 0

Membuat Rumus Saldo Otomatis dengan ARRAYFORMULA di Google Sheet

Banyak yang bertanya bagaimana cara membuat rumus untuk saldo dengan satu rumus dan akan mengisi otomatis kebawahnya.

Ya, beberapa pertanyaan dari group banyak menanyakan hal ini, yaitu begaimana cara membuat rumus saldo dengan Arrayformula.

Sebelum membahas dengan ARRAYFORMULA, mari kita lihat bagaimana rumus saldo ini jika menggunakan Rumus Excel Statis (Rumus Excel yang Umumnya digunakan).

=SUM($E$2:E2)-SUM($D$2:D2)

Dengan rumus diatas, saldo sudah bisa diketahui. Hanya saja setiap mengisi data baru maka Rumus Saldo harus di drag kebawah agar saldo bisa dihitung secara otomatis, dan ini lah yang ingin di hindari oleh para pengguna Google sheet.

Membuat Rumus Saldo Versi ARRAYFORMULA

Untuk membuat rumus saldo otomatis tentunya dengan arrayformula, disini saya akan buat dalam 2 jenis formula, yang pertama menggunakan SUMIF dengan ARRAYFORMULA dan yang kedua menggunakan rumus Dynamic Array Formula yaitu SCAN. Keduanya sama-sama bisa digunakan untuk membuat rumus otomatis di saldo.

Rumus Saldo Arrayformula dengan SUMIF

Kenapa menggunakan SUMIF tidak SUM saja? Seharusnya untuk menjumlahkan tanpa kriteria bisa menggunakan SUM, tapi kendalanya adalah SUM akan menjumlahkan dari atas sampai bawah. karena itu menggunakan SUMIF agar Data yang dijumlahkan hanya yang barada di baris dimana rumus itu berada dan dibaris sebelumnya.

=ARRAYFORMULA(IF(LEN(B2:B),SUMIF(A2:A,"<="&A2:A,E2:E)-SUMIF(A2:A,"<="&A2:A,D2:D),))

Pada rumus diatas, ARRAYFORMULA menggunakan bantuan kolom A yaitu Nomor untuk menentukan posisi baris yang akan dijumlahkan. Jika pada data tidak ada kolom Nomor, Bisa gunakan rumus ROW untuk menentukan Posisi baris. Untuk contohnya bisa dilihat pada gambar dibawah ini.

=ARRAYFORMULA(IF(LEN(A2:A),SUMIF(ROW(A2:A),"<="&ROW(A2:A),D2:D)-SUMIF(ROW(A2:A),"<="&ROW(A2:A),C2:C),))

Rumus Saldo SCAN (Dynamic Array Formula)

Untuk cara kedua ini, menggunakan rumus baru adopsi dari dari Excel di tahun 2022 sebuah rumus yang diciptakan khusus untuk Dynamic Array sehingga tidak diperlukan lagi ARRAYFORMULA.

Penjelasan Detail tentang rumus SCAN, sudah saya jelaskan pada tutorial Video berikut ini :

Sintaksis rumus SCAN:

=SCAN(initial_value, array_or_range, LAMBDA)

Rumus SCAN membutuhkan 3 parameter atau inputan, yang pertama adalah Initial Value atau Nilai awal, untuk number bisa diisi dengan 0 sedangkan untuk text bisa diisi dengan “” ataupun bisa diisi dengan Nilain selain kosong.

Array or Range adalah Range atau Array yang akan di Baca isinya baris per baris. Untuk isian terkahir ada LAMBDA dimana lambda ini adalah perhitungan yang akan digunakan dalam rumus SCAN ini.

=SCAN(0,D2:D,LAMBDA(S,K,IF(LEN(OFFSET(K,,-2)),S+K-OFFSET(K,,-1),)))

Untuk penjelasan rumus SCAN ini, bisa dilihat pada tutorial dibawah ini