Công thức chung
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Tóm lược
Để dữ liệu tổng phụ theo nhóm hoặc nhãn, trực tiếp trong bảng, bạn có thể sử dụng công thức dựa trên hàm SUMIF.
Trong ví dụ được hiển thị, công thức trong D5 là:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Lưu ý: dữ liệu phải được sắp xếp theo cột nhóm để có kết quả hợp lý.
Giải trình
Khung của công thức này dựa trên IF, kiểm tra từng giá trị trong cột B để xem nó có giống với giá trị trong "ô ở trên" hay không. Khi các giá trị khớp, công thức không trả về (""). Khi các giá trị khác nhau, hàm IF gọi SUMIF:
SUMIF(B:B,B5,C:C)
Trong mỗi hàng mà SUMIF được kích hoạt bởi IF, SUMIF tính tổng của tất cả các hàng phù hợp trong cột C (C: C). Tiêu chí được sử dụng bởi SUMIF là giá trị hàng hiện tại của cột B (B5), được kiểm tra dựa trên tất cả cột B (B: B).
Các tham chiếu cột đầy đủ như thế này rất thú vị và trang nhã, vì bạn không phải lo lắng về nơi dữ liệu bắt đầu và kết thúc, nhưng bạn cần đảm bảo rằng không có dữ liệu thừa phía trên hoặc bên dưới bảng có thể bị SUMIF bắt.
Hiệu suất
Có vẻ như việc sử dụng tham chiếu cột đầy đủ là một ý tưởng tồi, vì các phiên bản Excel hiện tại chứa hơn 1 triệu hàng. Tuy nhiên, thử nghiệm đã chỉ ra rằng Excel chỉ đánh giá dữ liệu trong "phạm vi đã sử dụng" (A1 đến địa chỉ giao điểm của cột được sử dụng cuối cùng và hàng được sử dụng cuối cùng) với loại công thức này.
Charles Williams tại Fast Excel có một bài viết hay về chủ đề này, với một tập hợp đầy đủ các kết quả về thời gian.
Tại sao về Pivot Tables?
Ví dụ này có nghĩa là hiển thị cách các tham chiếu cột đầy đủ hoạt động và cách chúng có thể được sử dụng trực tiếp trong bảng dữ liệu. Bảng tổng hợp vẫn là một cách tuyệt vời để nhóm và tóm tắt dữ liệu.
