Cara menggunakan fungsi SCAN di Microsoft Excel untuk menghitung total berjalan

Selama bertahun-tahun, saya mengandalkan total kumulatif di Microsoft Excel, tetapi metode tradisionalnya rentan dan tidak dapat diandalkan. Sejak Microsoft memperkenalkan fungsi SCAN, saya beralih ke pendekatan baru yang fleksibel, dapat memperbaiki diri sendiri, dan tidak rusak saat data saya bertambah.

Mengapa metode lama bermasalah

Sebelum fungsi SCAN diperkenalkan, jika saya ingin total berjalan, saya akan menggunakan trik absolut-relatif.

Dalam spreadsheet ini, kolom B berisi keuntungan bulanan, dan saya ingin menampilkan total berjalan di kolom C.

Di sel C2, saya akan mengetik:

=SUM($B$2:B2)

Kemudian, saya akan mengklik dua kali gagang isian untuk menerapkan rumus ke baris yang tersisa.

Secara sekilas, ini terlihat bekerja dengan baik. Namun, bayangkan jika di kemudian hari saya menyadari bahwa saya melewatkan satu bulan. Ketika saya memasukkan baris baru, Excel gagal mengisi rumus ke dalam baris kosong baru itu, meninggalkan celah yang harus diatasi secara manual.

Juga, jika saya memajukan tanggal di kolom A hingga akhir tahun dan menyeret formula di kolom C ke bawah, kecuali saya mengubah formula secara drastis untuk memasukkan pernyataan IF, saya akan mendapatkan total yang berulang dan membingungkan.

Dalam contoh di atas, datanya berada dalam rentang biasa. Namun, jika saya memformat data saya sebagai tabel Excel, semuanya menjadi lebih rumit. Referensi terstruktur tidak memiliki cara bawaan untuk menempatkan awal sebuah rentang, jadi saya harus menimpa logika tabel dengan referensi sel biasa atau membuat formula baru yang memanfaatkan fungsi INDEX.

=SUM(INDEX([Profit],1):[@Profit])

Solusinya: Fungsi SCAN

Fungsi SCAN berada di satu sel dan menghasilkan hasilnya ke bawah kolom, memastikan total saya selalu benar secara struktural dan matematis.

Sintaks SCAN

Fungsi SCAN memiliki tiga argumen:

=SCAN([initial_value],array,lambda)

di mana :

• initial_value adalah titik awal untuk total berjalan.
• array adalah array yang akan dipindai untuk membuat total berjalan.
• lambda adalah fungsi kustom yang dipanggil untuk memindai array.

Struktur LAMBDA

Argumen ketiga, lambda, menerima tiga parameter:

=LAMBDA(accumulator,value,body)

di mana :

• akumulator adalah nilai yang dijumlahkan dan dikembalikan sebagai hasil.
• value adalah nilai saat ini dalam array.
• body adalah perhitungan yang diterapkan pada setiap elemen dalam array.

Poin penting yang perlu diperhatikan sebelum Anda memulai

Sekilas, SCAN terlihat lebih rumit dibandingkan SUM sederhana. Namun ada perbedaan antara formula yang sulit diketik dan yang sulit dikelola. SCAN membutuhkan sedikit pembelajaran di awal, tetapi setelah terpasang, ini adalah alat yang bisa diatur sekali dan dilupakan yang melindungi data Anda dari kesalahan manual yang sering terjadi pada total berjalan tradisional.

Meskipun fungsi SCAN sangat bagus untuk memproses total berjalan, ada satu catatan: fungsi ini tidak bekerja di dalam tabel Excel. Ini karena tabel mengharuskan setiap baris memiliki rumus atau nilai yang independen, sementara fungsi SCAN menyebarkan hasil ke beberapa baris. Ini berarti fungsi ini harus digunakan di rentang biasa. Meski begitu, tetap lebih disarankan untuk memformat data sumber sebagai tabel Excel karena alasan yang akan saya jelaskan segera.

Contoh 1: Menghitung total berjalan dasar

Dalam spreadsheet ini, kolom Bulan dan Laba diformat sebagai tabel Excel (diberi nama T_Profits), dan kolom Total Berjalan berada di kisaran biasa di kolom D. Kolom kosong C berfungsi sebagai penyangga agar tabel tidak 'mengambil' kolom eksternal.

Berikut rumus yang akan saya ketikkan di sel D2:

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))

di mana :

• initial_value adalah 0 karena baris 2 berisi entri pertama dalam dataset saya (saya tidak membawa angka dari 2023).
• T_Profits[Profit] adalah daftar nilai keuntungan pada kolom Profit di tabel T_Profits yang akan dipindainya.
• LAMBDA(a,b,a+b) menganggap a sebagai total sejauh ini, b sebagai nilai di kolom keuntungan pada baris saat ini, dan a+b sebagai perhitungan yang perlu dilakukan.

Manfaat menggunakan metode ini dibandingkan dengan pendekatan lama menjadi jelas ketika saya perlu menyisipkan baris di tengah dataset—ia menangani perubahan struktur ini, siap untuk saya masukkan total bulanan di sel kosong di kolom B.

Saya juga tidak perlu mendahului kolom A atau menarik formula ke bawah, karena ketika saya mengetik bulan baru di sel A22, seluruh struktur otomatis menyesuaikan penambahan ini.

Lebih dari itu, jika saya perlu menghapus satu baris dari tengah dataset, sistem ini menangani perubahan tersebut tanpa kesulitan.

Contoh 2: Melacak rata-rata bergerak

Untuk menghitung rata-rata bergerak yang diperbarui saat saya menambahkan data, saya menggabungkan fungsi SCAN dan SEQUENCE. Sementara total berjalan pada contoh di atas menambahkan nilai, rata-rata bergerak membagi total dengan jumlah entri yang telah diproses sejauh ini.

Untuk melakukan ini, di sel D2, saya akan memasukkan:

Laporan How-To Geek

Laporan How-To Geek

Dengan berlangganan, Anda setuju untuk menerima buletin dan email pemasaran, serta menerima Ketentuan Penggunaan dan Kebijakan Privasi Valnet. Anda dapat berhenti berlangganan kapan saja.

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))/SEQUENCE(ROWS(T_Profits[Profit]))

di mana :

• Bagian SCAN dari rumus menghitung total berjalan persis seperti yang saya lakukan di Contoh 1.
• Bagian SEQUENCE membuat daftar angka dinamis (1, 2, 3...) yang sesuai dengan indeks baris. Misalnya, pada baris kelima, sequence mengembalikan angka 5.
• Dengan membagi (/) total berjalan dengan nomor urut, Excel mengembalikan rata-rata dari semua nilai dari awal hingga baris saat ini.

Contoh 3: Reset tahun hingga saat ini

Dalam contoh sejauh ini, fungsi SCAN digunakan dengan anggapan bahwa saya ingin membawa total berjalan selama 2024 dan 2025. Namun, misalkan saya ingin perhitungan terkumpul hingga Desember 2024 dan kemudian direset begitu saya mencapai Januari 2025.

Dengan metode tradisional, saya harus memutus rantai jumlah saya secara manual setiap 12 bulan. Namun dengan SCAN, saya bisa membangun saklar reset langsung ke dalam LAMBDA.

Berikut rumus yang perlu saya gunakan:

=SCAN(0,SEQUENCE(ROWS(T_Profits[Profit])),LAMBDA(a,i,IF(MONTH(INDEX(T_Profits[Month],i))=1,INDEX(T_Profits[Profit],i),a+INDEX(T_Profits[Profit],i))))

di mana :

• SEQUENCE(ROWS(T_Profits[Profit])) membuat daftar angka (1, 2, 3...) yang mewakili setiap baris.
• Di LAMBDA, i mewakili nomor baris saat ini.
• INDEX(T_Profits[Month],i) melihat tanggal di baris saat ini. Jika itu Januari (1), saklar reset berubah, mengembalikan hanya keuntungan saat ini (INDEX(T_Profits[Profit],i)). Jika itu bulan lain, maka akan melakukan perhitungan a+profit seperti biasa.

Langkah terakhir: Membersihkan hasil

Meskipun rumus-rumus di atas sudah bekerja dengan baik sebagaimana adanya, sentuhan akhir itulah yang membuat spreadsheet saya menjadi yang terbaik:

• Aturan "satu sel": Ingatlah bahwa karena ini adalah rumus keluaran tunggal (spill formulas), mereka hanya perlu diketik di sel atas saja. Jika saya perlu mengubah logikanya (misalnya dari total berjalan ke rata-rata berjalan), saya hanya perlu mengedit sel tunggal itu saja.
• Jika saya pernah melihat kesalahan #SPILL! saat mengetik rumus saya dan menekan Enter, itu sering kali karena ada sesuatu yang menghalangi array dinamis untuk tumpah. Dalam situasi ini, begitu saya membersihkan rumus manual lama atau teks yang tersisa di kolom, hasil SCAN langsung mengalir ke bawah.

Sementara fungsi SCAN merupakan terobosan untuk total berjalan, itu hanyalah salah satu dari serangkaian fungsi revolusioner yang telah mengubah cara kerja Excel—terima kasih pada array dinamis. Fungsi-fungsi seperti FILTER, SORT, dan UNIQUE pada dasarnya telah menggantikan banyak fungsi lama yang dulu sering saya gunakan. Alih-alih harus menangani dataset baris demi baris, alat baru ini memungkinkan satu sel untuk mengerjakan tugas berat di seluruh rentang. Dengan memprioritaskan perilaku spill ini, saya membangun spreadsheet yang kebal terhadap kesalahan manual klasik yang dulu sering mengganggu alur kerja saya.