Formula generik
=SUMPRODUCT(data*(headers=A1))
Ringkasan
Untuk menjumlahkan nilai dalam lajur dengan mencocokkan tajuk lajur yang sepadan, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di J5 adalah:
=SUMPRODUCT(data*(LEFT(headers)=J4))
di mana "data" adalah julat bernama B5: G14, dan "tajuk" adalah julat bernama B4: G4.
Rumus merangkum lajur di mana tajuk bermula dengan "a" dan mengembalikan 201.
Penjelasan
Pada intinya, formula ini bergantung pada fungsi SUMPRODUCT untuk menjumlahkan nilai dalam lajur yang sesuai dalam julat bernama "data" C5: G14. Sekiranya semua data diberikan kepada SUMPRODUCT dalam satu julat, hasilnya adalah jumlah semua nilai dalam julat:
=SUMPRODUCT(data) // all data, returns 387
Untuk menggunakan penapis dengan memadankan tajuk lajur - lajur dengan tajuk yang diawali dengan "A" - kami menggunakan fungsi KIRI seperti ini:
LEFT(headers)=J4) // must begin with "a"
Ungkapan ini mengembalikan BENAR jika tajuk lajur dimulakan dengan "a", dan SALAH jika tidak. Hasilnya adalah pelbagai:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Anda dapat melihat bahawa nilai 1,2, dan 5 sesuai dengan lajur yang diawali dengan "a".
Di dalam SUMPRODUCT, array ini dikalikan dengan "data". Oleh kerana penyiaran, hasilnya adalah susunan dua dimensi seperti ini:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Sekiranya kita memvisualisasikan susunan ini dalam jadual, mudah dilihat bahawa hanya nilai dalam lajur yang bermula dengan "a" yang bertahan dalam operasi, semua lajur lain adalah sifar. Dengan kata lain, penapis menyimpan nilai minat dan "membatalkan" selebihnya:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Dengan hanya satu array untuk diproses, SUMPRODUCT mengembalikan jumlah semua nilai, 201.
Jumlahkan dengan padanan tepat
Contoh di atas menunjukkan cara menjumlahkan lajur yang bermula dengan satu atau lebih watak tertentu. Untuk menjumlahkan lajur berdasarkan padanan tepat, anda boleh menggunakan formula yang lebih mudah seperti ini:
=SUMPRODUCT(data*(headers=J4))
