Ringkasan
Untuk membuat hitungan berjalan dalam Tabel Excel, Anda dapat menggunakan fungsi INDEX dengan referensi terstruktur untuk membuat rentang yang diperluas. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Saat disalin ke bawah kolom, rumus ini akan mengembalikan hitungan berjalan untuk setiap warna di kolom Warna.
Di beberapa versi Excel, ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Intinya, rumus ini telah menggunakan INDEX untuk membuat referensi yang diperluas seperti ini:
INDEX((Color),1):(@Color) // expanding range
Di sisi kiri titik dua (:), fungsi INDEX mengembalikan referensi ke sel pertama di kolom kolom.
INDEX((Color),1) // first cell in color
Ini berfungsi karena, fungsi INDEX mengembalikan referensi ke sel pertama, bukan nilai sebenarnya. Di sisi kanan titik dua, kami mendapatkan referensi ke baris saat ini dari kolom warna seperti ini:
(@Color) // current row of Color
Ini adalah sintaks referensi terstruktur standar untuk "baris ini". Digabungkan dengan titik dua, kedua referensi ini membuat rentang yang meluas saat rumus disalin ke bawah tabel. Jadi, kami menukar referensi ini ke dalam fungsi SUM, kami memiliki:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Setiap ekspresi di atas menghasilkan larik nilai TRUE / FALSE, dan negatif ganda (-) digunakan untuk mengonversi nilai ini menjadi 1 dan 0. Jadi, di baris terakhir, kita berakhir dengan:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Rumus lainnya hanya menggabungkan warna dari baris saat ini ke hitungan yang dikembalikan oleh SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Rentang perluasan sederhana?
Mengapa tidak menggunakan rentang perluasan sederhana seperti ini?
SUM(--($B$5:B5=(@Color)))
Untuk beberapa alasan, jenis referensi campuran ini menjadi rusak dalam Tabel Excel saat baris ditambahkan. Menggunakan INDEX dengan referensi terstruktur menyelesaikan masalah.
