Ringkasan
Untuk membuat kiraan berjalan dalam Jadual Excel, anda boleh menggunakan fungsi INDEX dengan rujukan berstruktur untuk membuat julat yang berkembang. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Apabila disalin ke bawah lajur, formula ini akan mengembalikan kiraan berjalan untuk setiap warna di lajur Warna.
Dalam beberapa versi Excel, ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Pada intinya, formula ini menggunakan INDEX untuk membuat rujukan yang berkembang seperti ini:
INDEX((Color),1):(@Color) // expanding range
Di sebelah kiri kolon (:), fungsi INDEX mengembalikan rujukan ke sel pertama di lajur lajur.
INDEX((Color),1) // first cell in color
Ini berfungsi kerana, fungsi INDEX mengembalikan rujukan ke sel pertama, bukan nilai sebenarnya. Di sebelah kanan kolon, kami mendapat rujukan pada baris lajur warna seperti ini:
(@Color) // current row of Color
Ini adalah sintaks rujukan berstruktur standard untuk "baris ini". Bergabung dengan titik dua, kedua rujukan ini membuat julat yang berkembang ketika formula disalin di bawah jadual. Oleh itu, kami menukar rujukan ini ke fungsi SUM, kami mempunyai:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Setiap ungkapan di atas menghasilkan susunan nilai BENAR / SALAH, dan negatif ganda (-) digunakan untuk menukar nilai ini menjadi 1s dan 0s. Oleh itu, pada baris terakhir, kami berakhir dengan:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Formula selebihnya hanya menggabungkan warna dari baris semasa hingga kiraan yang dikembalikan oleh SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Julat pengembangan sederhana?
Mengapa tidak menggunakan julat pengembangan sederhana seperti ini?
SUM(--($B$5:B5=(@Color)))
Untuk beberapa sebab, rujukan campuran semacam ini menjadi rosak dalam Jadual Excel semasa baris ditambahkan. Menggunakan INDEX dengan rujukan berstruktur menyelesaikan masalah.
