Công thức chung
=SUMPRODUCT(data*(headers=A1))
Tóm lược
Để tính tổng các giá trị trong các cột bằng cách đối sánh các tiêu đề cột phù hợp, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong J5 là:
=SUMPRODUCT(data*(LEFT(headers)=J4))
trong đó "dữ liệu" là dải ô được đặt tên B5: G14 và "tiêu đề" là dải ô được đặt tên B4: G4.
Công thức tính tổng các cột có tiêu đề bắt đầu bằng "a" và trả về 201.
Giải trình
Về cơ bản, công thức này dựa vào hàm SUMPRODUCT để tính tổng các giá trị trong các cột phù hợp trong phạm vi được đặt tên là "dữ liệu" C5: G14. Nếu tất cả dữ liệu được cung cấp cho SUMPRODUCT trong một phạm vi duy nhất, kết quả sẽ là tổng của tất cả các giá trị trong phạm vi:
=SUMPRODUCT(data) // all data, returns 387
Để áp dụng bộ lọc bằng cách đối sánh tiêu đề cột - các cột có tiêu đề bắt đầu bằng "A" - chúng tôi sử dụng hàm LEFT như sau:
LEFT(headers)=J4) // must begin with "a"
Biểu thức này trả về TRUE nếu tiêu đề cột bắt đầu bằng "a" và FALSE nếu không. Kết quả là một mảng:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Bạn có thể thấy rằng các giá trị 1,2 và 5 tương ứng với các cột bắt đầu bằng "a".
Bên trong SUMPRODUCT, mảng này được nhân với "dữ liệu". Do phát sóng, kết quả là một mảng hai chiều như sau:
(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)
Nếu chúng ta hình dung mảng này trong một bảng, sẽ dễ dàng nhận thấy rằng chỉ các giá trị trong các cột bắt đầu bằng "a" mới tồn tại trong thao tác, tất cả các cột khác đều bằng không. Nói cách khác, bộ lọc giữ các giá trị quan tâm và "loại bỏ" phần còn lại:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| số 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| số 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| số 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | số 8 | 0 | 0 | số 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Chỉ với một mảng duy nhất để xử lý, hàm SUMPRODUCT trả về tổng của tất cả các giá trị, 201.
Tính tổng theo đối sánh chính xác
Ví dụ trên cho thấy cách tính tổng các cột bắt đầu bằng một hoặc nhiều ký tự cụ thể. Để tính tổng cột dựa trên kết hợp chính xác, bạn có thể sử dụng công thức đơn giản hơn như sau:
=SUMPRODUCT(data*(headers=J4))
