=SUBTOTAL(9,C2:C8) cộng các số trong C2:C8 giống SUM, nhưng có hai điểm khác: hàm bỏ qua mọi công thức SUBTOTAL khác nằm trong vùng, và bỏ qua các dòng bị bộ lọc ẩn đi. Đối số đầu tiên, 9, cho biết cần làm phép tính nào.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Check | Result | |
| 2 | North | Apple | 120 | SUM of C2:C7 | 890 | |
| 3 | North | Pear | 80 | |||
| 4 | North total | 200 | ||||
| 5 | South | Apple | 200 | |||
| 6 | South | Pear | 45 | |||
| 7 | South total | 245 | ||||
| 8 | Grand total | 445 |
Tổng cộng ở C8 chạy trên cả cột, gồm cả các dòng tổng phụ, mà vẫn ra 445: SUBTOTAL bỏ qua C4 và C7 vì hai ô này chứa công thức SUBTOTAL. F2 làm điều tương tự bằng SUM và ra 890, mỗi khoản doanh số bị tính hai lần. Khi mọi dòng tổng đều dùng SUBTOTAL, bạn có thể thêm hoặc di chuyển nhóm mà không phải viết lại công thức tổng cộng.
Các mã hàm của SUBTOTAL
=SUBTOTAL(function_num, ref1, [ref2], ...)
| Phép tính | Bỏ qua dòng bị lọc | Bỏ qua cả dòng ẩn thủ công |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT (số) | 2 | 102 |
| COUNTA (ô không trống) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV.S | 7 | 107 |
| STDEV.P | 8 | 108 |
| SUM | 9 | 109 |
| VAR.S | 10 | 110 |
| VAR.P | 11 | 111 |
Khi bạn gõ =SUBTOTAL(, Excel hiện danh sách này, nên bạn không cần học thuộc. 9 và 109 (SUM), 1 (AVERAGE) và 103 (đếm dòng hiển thị) là những mã được dùng nhiều nhất.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Calculation | Result | |
| 2 | North | Apple | 120 | AVERAGE (1) | 88.33 | |
| 3 | North | Pear | 80 | COUNTA (3) | 6 | |
| 4 | South | Apple | 200 | MAX (4) | 200 | |
| 5 | South | Pear | 45 | MIN (5) | 30 | |
| 6 | East | Apple | 55 | Visible rows (103) | 6 | |
| 7 | East | Plum | 30 | SUM (109) | 530 |
Ở đây không có dòng nào bị ẩn, nên mỗi dòng đều bằng hàm thông thường: trung bình 88,33, 6 dòng, MAX là 200, MIN là 30 và SUM là 530. Sự khác biệt chỉ lộ ra khi có dòng bị ẩn, và đó là nội dung của phần tiếp theo.
SUBTOTAL 9 và 109, và các dòng bị lọc
Bật bộ lọc bằng Data > Filter (Ctrl+Shift+L, Cmd+Shift+F trên Mac), rồi chọn North trong danh sách thả xuống của cột Region. Các dòng thuộc khu vực khác bị ẩn đi:
=SUM(C2:C7)vẫn cộng cả sáu dòng.=SUBTOTAL(9,C2:C7)và=SUBTOTAL(109,C2:C7)chỉ cộng các dòng North đang hiển thị.=SUBTOTAL(103,A2:A7)đếm số dòng còn trên màn hình: 2, đúng bằng con số "2 of 6 records found" trên thanh trạng thái.
Hai nhóm mã chỉ khác nhau với các dòng bạn ẩn thủ công (chọn dòng, chuột phải > Hide). 9 vẫn cộng các dòng đó; 109 thì không. Nếu tổng luôn phải khớp với những gì trên màn hình, dùng 109. Nếu bạn ẩn dòng chỉ cho gọn mắt mà vẫn muốn tính chúng vào tổng, dùng 9.
SUBTOTAL chỉ làm việc theo dòng. Các cột bị ẩn luôn được tính, nên =SUBTOTAL(109,B2:G2) trên một dòng vẫn cộng cả cột ẩn.
Cách nhanh nhất để có SUBTOTAL là bấm nút AutoSum khi đang bật bộ lọc: Excel viết =SUBTOTAL(9,...) thay vì SUM. Data > Subtotal còn làm được nhiều hơn: với danh sách đã sắp xếp theo một cột, nó chèn một dòng tổng dưới mỗi nhóm và một dòng tổng cộng, tất cả đều dùng SUBTOTAL, kèm các nút outline để thu gọn nhóm.
AGGREGATE: SUBTOTAL có thể bỏ qua lỗi
Nếu một ô trong vùng chứa lỗi, SUM và SUBTOTAL đều trả về lỗi đó. AGGREGATE (Excel 2010 trở lên) là SUBTOTAL có thêm một đối số tùy chọn; tùy chọn 6 bỏ qua các giá trị lỗi.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Formula | Result | |
| 2 | North | Apple | 120 | SUBTOTAL | #N/A | |
| 3 | North | Pear | #N/A | AGGREGATE, ignore errors | 450 | |
| 4 | South | Apple | 200 | AGGREGATE, MAX | 200 | |
| 5 | South | Pear | 45 | |||
| 6 | East | Apple | 55 | |||
| 7 | East | Plum | 30 |
C3 chứa #N/A, nên F2 cũng hiện #N/A. F3 bỏ qua lỗi này và cộng năm ô còn lại: 450. Đối số đầu tiên của nó dùng cùng mã với SUBTOTAL (9 là SUM, 4 là MAX). Các tùy chọn khác: 5 bỏ qua dòng ẩn, 7 bỏ qua dòng ẩn và lỗi, 3 bỏ qua dòng ẩn, lỗi và các công thức SUBTOTAL, AGGREGATE lồng bên trong. Thay C3 bằng một con số và F2 sẽ ra cùng tổng với F3.
Luyện tập: tổng cộng trên các tổng phụ
| A | B | C | |
|---|---|---|---|
| 1 | Region | Item | Sales |
| 2 | North | Apple | 120 |
| 3 | North | Pear | 80 |
| 4 | North total | 200 | |
| 5 | South | Apple | 200 |
| 6 | South | Pear | 45 |
| 7 | South | Plum | 60 |
| 8 | South total | 305 | |
| 9 | Grand total |
Đến lượt bạn: Danh sách có một dòng tổng phụ dưới mỗi khu vực. Đặt tổng cộng ở C9 bao trùm C2:C8 mà không tính các dòng tổng phụ hai lần.
Vì sao tổng bằng SUBTOTAL vẫn sai
- Tổng của các nhóm dùng SUM. SUBTOTAL bỏ qua các công thức SUBTOTAL khác trong vùng của nó, chứ không bỏ qua công thức SUM. Một tổng nhóm viết bằng
=SUM(C2:C3)sẽ bị tính thêm lần nữa. Đổi mọi dòng tổng sang SUBTOTAL. - Các dòng bị ẩn thủ công và mã hàm là 9. Dùng 109.
- Dữ liệu nằm theo cột, không theo dòng. Cột ẩn không bao giờ bị bỏ qua.
- Bạn cần một điều kiện, không phải bộ lọc. SUBTOTAL đi theo những gì bộ lọc ẩn đi. Để tính tổng cho North mà không lọc, dùng SUMIF. Để tóm tắt mọi nhóm cùng lúc, pivot table làm được việc đó mà không cần dòng tổng trong dữ liệu.
Câu hỏi thường gặp
SUBTOTAL 9 trong Excel nghĩa là gì?
Đối số đầu tiên chọn phép tính, và 9 là SUM. =SUBTOTAL(9,C2:C8) cộng C2:C8, bỏ qua các dòng bị bộ lọc ẩn và mọi công thức SUBTOTAL khác trong vùng. 1 là AVERAGE, 2 là COUNT, 3 là COUNTA, 4 là MAX, 5 là MIN.
SUBTOTAL 9 và 109 khác nhau thế nào?
Cả hai đều bỏ qua các dòng bị bộ lọc ẩn. 109 còn bỏ qua các dòng bạn ẩn thủ công (chuột phải > Hide), còn 9 vẫn cộng các dòng đó. Dùng 109 khi tổng phải khớp đúng với những gì hiện trên màn hình.
Làm sao để chỉ tính tổng các ô hiển thị sau khi lọc?
Dùng =SUBTOTAL(9,C2:C100) hoặc =SUBTOTAL(109,C2:C100) bên dưới dữ liệu. Khi bạn lọc danh sách, tổng sẽ chỉ còn tính các dòng hiển thị. SUM thông thường vẫn cộng cả các dòng bị ẩn.
Làm sao để đếm số dòng hiển thị trong danh sách đã lọc?
Dùng =SUBTOTAL(103,A2:A100). 103 là COUNTA có bỏ qua dòng ẩn, nên nó đếm các ô có dữ liệu vẫn còn hiện trên màn hình.
Làm sao để tính tổng một vùng có chứa lỗi?
Dùng AGGREGATE với tùy chọn 6, bỏ qua lỗi: =AGGREGATE(9,6,C2:C8). SUM và SUBTOTAL đều trả về lỗi nếu một ô trong vùng chứa #N/A.
