Bạn có một bảng 14 đơn hàng tháng 10, cuối bảng có ô tổng doanh thu dùng hàm SUM. Bạn lọc khu vực Hà Nội, bảng chỉ còn 5 đơn, nhưng ô tổng vẫn hiện 73.800.000 của cả 14 đơn. Trong khi đó, 5 đơn Hà Nội cộng lại chỉ có 16.800.000.
Tình huống này xảy ra vì hàm SUM cộng mọi ô trong vùng, kể cả những dòng đang bị lọc ẩn.
Để tính tổng khi lọc dữ liệu trong Excel, bạn thay SUM bằng công thức =SUBTOTAL(9,G2:G15). Hàm SUBTOTAL với mã 9 chỉ cộng các dòng đang hiện, nên khi bạn lọc Hà Nội, ô tổng ra đúng 16.800.000. Nếu bạn còn ẩn dòng bằng tay bằng lệnh Hide, bạn dùng mã 109 để bỏ qua cả những dòng đó.
Bài viết hướng dẫn bạn cách lọc dữ liệu bằng AutoFilter và 6 phép tính cho kết quả đúng sau khi lọc: tính tổng, đếm số dòng, tính trung bình, đánh số thứ tự, tính tổng có điều kiện và tính khi có ô lỗi. Phần cuối giới thiệu hàm FILTER và cách lọc dữ liệu trùng nhau. Mọi con số trong bài đều được kiểm tra trên Excel 365 với bảng dữ liệu mẫu bên dưới.
Bài viết dùng bảng dữ liệu mẫu nào?
Bảng mẫu nằm ở vùng A1:G15. Dòng 1 là tiêu đề, các dòng 2 tới 15 là 14 đơn hàng. Các cột gồm STT (A), Ngày (B), Nhân viên (C), Khu vực (D), Sản phẩm (E), Số lượng (F) và Doanh thu (G). Dòng 16 để trống, các công thức tính tổng nằm từ dòng 17 trở xuống. Tổng doanh thu của cả bảng là 73.800.000.
Khu vực | Số đơn | Tổng doanh thu | Các hàng chứa đơn |
|---|---|---|---|
Hà Nội | 5 | 16.800.000 | 2, 5, 9, 12, 14 |
TP.HCM | 5 | 36.100.000 | 3, 6, 8, 10, 13 |
Đà Nẵng | 4 | 20.900.000 | 4, 7, 11, 15 |
Bạn lọc dữ liệu trong Excel bằng AutoFilter như thế nào?
AutoFilter ẩn các dòng không thỏa điều kiện. Dữ liệu không bị xóa, nên bạn bỏ lọc là bảng trở về như cũ.
Bước 1: Bạn bật bộ lọc cho bảng
Bạn bấm chọn một ô bất kỳ trong bảng, ví dụ ô C3.
Bạn vào tab Data, bấm nút Filter trong nhóm Sort & Filter. Cách nhanh hơn là nhấn
Ctrl + Shift + L.Excel thêm một nút mũi tên nhỏ vào ô tiêu đề của từng cột.
Muốn tắt bộ lọc, bạn bấm Filter thêm một lần hoặc nhấn lại Ctrl + Shift + L. Bạn xem thêm các phím tắt hay dùng trong bài Phím tắt Excel thông dụng.

Excel tự nhận vùng lọc là vùng dữ liệu liền mạch quanh ô bạn chọn, nên bạn cần lưu ý 2 điều:
Bảng không có dòng trống ở giữa. Khi kiểm tra, bảng có một dòng trống ở hàng 9 thì Excel chỉ nhận vùng lọc A1:G8, các đơn phía dưới dòng trống không được lọc.
Dòng tổng nằm cách bảng ít nhất một dòng trống. Khi kiểm tra, dòng tổng dùng SUM nằm sát ngay dưới bảng bị Excel đưa vào vùng lọc và bị ẩn mất khi lọc Hà Nội.
Bước 2: Bạn lọc theo giá trị trong một cột
Lọc theo giá trị là cách lọc theo cột hay dùng nhất. Để xem các đơn của Hà Nội, bạn làm như sau:
Bạn bấm nút lọc ở ô tiêu đề Khu vực (ô D1).
Bạn bỏ tick ô (Select All) để bỏ chọn toàn bộ giá trị.
Bạn tick Hà Nội rồi bấm OK.

Sau khi lọc, bảng chỉ còn 5 đơn Hà Nội ở các hàng 2, 5, 9, 12 và 14. Bạn nhận ra bảng đang lọc nhờ 3 dấu hiệu:
Số hàng của các dòng còn lại chuyển sang màu xanh dương.
Số hàng nhảy cóc, ví dụ từ 2 sang 5.
Nút lọc ở cột Khu vực đổi sang biểu tượng cái phễu.
Bước 3: Bạn lọc theo điều kiện số, ngày và chữ
Tùy kiểu dữ liệu của cột, menu lọc hiện Number Filters (cột số), Date Filters (cột ngày) hoặc Text Filters (cột chữ). Bảng sau ghi lại kết quả trên bảng mẫu:
Bạn muốn lọc | Thao tác | Kết quả trên bảng mẫu |
|---|---|---|
Đơn có số lượng từ 10 trở lên | Cột Số lượng > Number Filters > Greater Than Or Equal To..., gõ 10 | Bảng còn 6 đơn, tổng 17.700.000 |
Đơn có doanh thu trên 5.000.000 | Cột Doanh thu > Number Filters > Greater Than..., gõ 5000000 | Bảng còn 5 đơn, tổng 45.000.000 |
Đơn từ ngày 05/10 tới 15/10/2026 | Cột Ngày > Date Filters > Between..., chọn 2 mốc ngày | Bảng còn 7 đơn, tổng 37.000.000 |
Sản phẩm có chữ "in" trong tên | Cột Sản phẩm > Text Filters > Contains..., gõ in | Bảng còn 7 đơn Máy in và Mực in, tổng 45.800.000 |
Bước 4: Bạn lọc theo màu ô
Để lọc các ô đã tô màu, bạn bấm nút lọc của cột đó, chọn Filter by Color, rồi chọn màu cần lọc trong mục Filter by Cell Color. Trong bảng mẫu, 3 ô doanh thu ở hàng 3, 10 và 13 được tô vàng. Lọc theo màu vàng cho ra đúng 3 đơn, tổng doanh thu 30.000.000.
Bước 5: Bạn lọc nhiều cột cùng lúc và bỏ lọc
Bạn có thể đặt điều kiện ở nhiều cột, Excel chỉ giữ lại các dòng thỏa tất cả điều kiện. Ví dụ, bạn lọc Khu vực là Hà Nội, sau đó lọc thêm Số lượng từ 10 trở lên, bảng còn 3 đơn với tổng 7.800.000.
Bỏ lọc tất cả các cột: bạn vào Data > Clear.
Lọc lại sau khi sửa dữ liệu: bạn vào Data > Reapply. Excel không tự lọc lại khi bạn sửa giá trị trong bảng.
Vì sao hàm SUM vẫn cộng cả dòng bị ẩn khi lọc?
Hàm SUM cộng mọi ô trong vùng bạn đưa vào, không phân biệt ô đang hiện hay đang ẩn. Bộ lọc chỉ ẩn dòng chứ không xóa dòng, nên công thức =SUM(G2:G15) vẫn cộng đủ 14 đơn và ra 73.800.000 dù bảng chỉ còn 5 đơn Hà Nội.
Các hàm COUNTA và AVERAGE cũng vậy. Khi lọc Hà Nội, =COUNTA(B2:B15) vẫn ra 14 và =AVERAGE(G2:G15) vẫn ra 5.271.429 như lúc chưa lọc.
Để tính theo các dòng đang hiện, bạn cần hàm SUBTOTAL hoặc AGGREGATE. Hai hàm này kiểm tra từng dòng và bỏ qua các dòng đang bị ẩn.
Phép tính 1: SUBTOTAL tính tổng các dòng đang hiện
Cú pháp: =SUBTOTAL(function_num, ref1, [ref2], ...)
function_num là mã số cho biết SUBTOTAL thực hiện phép tính nào. Mã 9 và mã 109 đều là phép tính tổng.
ref1 là vùng cần tính, ví dụ G2:G15.
ref2, ... là các vùng tính thêm. Bạn chỉ cần khi muốn gộp nhiều vùng vào một kết quả.
Tại ô G18, bạn gõ =SUBTOTAL(9,G2:G15). Khi lọc Hà Nội, công thức ra 16.800.000. Khi đổi sang lọc TP.HCM, kết quả tự đổi thành 36.100.000. Lọc Đà Nẵng cho ra 20.900.000. Khi bỏ lọc, SUBTOTAL ra 73.800.000, bằng đúng kết quả của SUM.

Một số lưu ý khi dùng SUBTOTAL:
SUBTOTAL tự bỏ qua các ô SUBTOTAL khác nằm trong vùng tính. Khi kiểm tra, ô G16 chứa
=SUBTOTAL(9,G2:G15), công thức=SUBTOTAL(9,G2:G16)vẫn ra 73.800.000. Còn=SUM(G2:G16)ra 147.600.000 vì cộng tổng hai lần.SUBTOTAL chỉ bỏ qua dòng ẩn, không bỏ qua cột ẩn.
Nếu bạn chuyển vùng dữ liệu thành Table bằng
Ctrl + Trồi bật Total Row ở tab Table Design, ô tổng của Table dùng sẵn công thức=SUBTOTAL(109,[Doanh thu]).
Hàm SUBTOTAL có 22 mã phép tính
SUBTOTAL có 11 phép tính, mỗi phép tính có 2 mã. Cả hai nhóm mã đều bỏ qua dòng bị ẩn do lọc, chỉ khác nhau ở dòng ẩn bằng tay.
Phép tính | Mã tính cả dòng ẩn tay | Mã bỏ qua dòng ẩn tay | Hàm tương ứng |
|---|---|---|---|
Trung bình | 1 | 101 | AVERAGE |
Đếm ô chứa số | 2 | 102 | COUNT |
Đếm ô không trống | 3 | 103 | COUNTA |
Giá trị lớn nhất | 4 | 104 | MAX |
Giá trị nhỏ nhất | 5 | 105 | MIN |
Tích các giá trị | 6 | 106 | PRODUCT |
Độ lệch chuẩn mẫu | 7 | 107 | STDEV |
Độ lệch chuẩn tổng thể | 8 | 108 | STDEVP |
Tổng | 9 | 109 | SUM |
Phương sai mẫu | 10 | 110 | VAR |
Phương sai tổng thể | 11 | 111 | VARP |
Phép tính 2: SUBTOTAL đếm số dòng đang hiện
Để biết bộ lọc đang giữ lại bao nhiêu đơn, bạn dùng mã 3 (COUNTA, đếm ô không trống):
=SUBTOTAL(3,B2:B15)
Khi lọc Hà Nội, công thức ra 5, còn =COUNTA(B2:B15) vẫn ra 14. Bạn nên chọn cột luôn có dữ liệu ở mọi dòng, ví dụ cột Ngày hoặc cột mã đơn. Nếu cột có ô trống, SUBTOTAL sẽ đếm thiếu đúng số ô trống đó.
Phép tính 3: SUBTOTAL tính trung bình, lớn nhất, nhỏ nhất
Bạn chỉ cần đổi mã số để có các phép tính khác trên dòng đang hiện:
Bạn cần | Công thức | Kết quả khi lọc Hà Nội | Kết quả khi không lọc |
|---|---|---|---|
Doanh thu trung bình mỗi đơn |
| 3.360.000 | 5.271.429 |
Đơn có doanh thu lớn nhất |
| 4.500.000 | 13.500.000 |
Đơn có doanh thu nhỏ nhất |
| 1.600.000 | 1.600.000 |
Tổng số lượng sản phẩm |
| 61 | 173 |
Mã 9 và mã 109 khác nhau thế nào?
Excel có 2 cách làm ẩn dòng. Cách thứ nhất là lọc bằng AutoFilter. Cách thứ hai là ẩn bằng tay: bạn chọn dòng, bấm chuột phải rồi chọn Hide, hoặc nhấn Ctrl + 9. Mã 9 và mã 109 chỉ khác nhau ở dòng ẩn bằng tay.
Để thấy rõ khác biệt, bạn bỏ lọc, rồi ẩn tay hàng 3 và hàng 10. Hai hàng này là 2 đơn Máy in ở TP.HCM, tổng 22.500.000.

Công thức | Không ẩn, không lọc | Ẩn tay hàng 3 và 10 | Lọc Hà Nội |
|---|---|---|---|
| 73.800.000 | 73.800.000 | 73.800.000 |
| 73.800.000 | 73.800.000 | 16.800.000 |
| 73.800.000 | 51.300.000 | 16.800.000 |
Mã 9 vẫn cộng 2 hàng ẩn tay, còn mã 109 bỏ qua 2 hàng đó. Khi lọc, hai mã cho cùng kết quả. Khi kiểm tra trên Excel 365, bạn cần biết thêm 2 hiện tượng:
Khi bảng đang có bộ lọc, nếu bạn ẩn tay thêm một dòng trong vùng lọc, cả mã 9 và mã 109 đều bỏ qua dòng đó. Ví dụ, đang lọc Hà Nội, bạn ẩn tay thêm hàng 9 (đơn Ghế xoay 4.500.000), cả hai công thức đều ra 12.300.000.
Khi bạn lọc lại hoặc bấm Clear, Excel hiện lại các dòng đã ẩn tay trong vùng lọc. Ví dụ, hàng 3 và 10 đang ẩn tay hiện lại ngay khi lọc TP.HCM.
Cách chọn mã: nếu bạn muốn "thấy dòng nào thì tính dòng đó", bạn dùng nhóm mã 101 tới 111. Nếu bạn chỉ ẩn bớt dòng cho bảng gọn nhưng vẫn muốn tính đủ, bạn dùng nhóm mã 1 tới 11.
Phép tính 4: Số thứ tự tự cập nhật khi lọc
Nếu cột STT là số gõ tay từ 1 tới 14, số thứ tự giữ nguyên khi lọc. Lọc Hà Nội, bạn thấy STT là 1, 4, 8, 11, 13, nhìn rất khó theo dõi. Để STT chạy lại từ 1 theo các dòng đang hiện, bạn dùng công thức.
Cách được nhiều người dùng là gõ vào ô A2 công thức =SUBTOTAL(103,$B$2:B2) rồi kéo xuống A15. Mã 103 đếm ô không trống và bỏ qua dòng ẩn. Vùng $B$2:B2 có điểm đầu cố định, khi kéo xuống ô A5 thì vùng tự mở rộng thành $B$2:B5, nên mỗi dòng đếm số dòng đang hiện từ đầu bảng tới chính nó. Cột B phải có dữ liệu ở mọi dòng, nếu không STT sẽ bị lặp số.
Công thức SUBTOTAL(103) làm dòng cuối luôn hiện
Khi kiểm tra trên Excel 365, công thức trên gặp một lỗi khó nhận ra. Lọc Hà Nội, hàng 15 (đơn Đà Nẵng, Máy in 9.000.000) vẫn hiện với STT là 6. Ô tổng =SUBTOTAL(9,G2:G15) vì vậy ra 25.800.000 thay vì 16.800.000. Lỗi lặp lại với mọi điều kiện lọc khác, và công thức =SUBTOTAL(3,$B$2:B2) cũng bị y hệt. Lý do thường được giải thích là Excel coi dòng cuối chứa SUBTOTAL là dòng tổng của bảng nên giữ lại khi lọc.
Hàm AGGREGATE(3,5) sửa được lỗi dòng cuối
Bạn thay công thức ở ô A2 bằng =AGGREGATE(3,5,$B$2:B2), kéo xuống A15, rồi vào Data > Reapply để lọc lại. Trong công thức này, số 3 là phép đếm ô không trống, số 5 là tùy chọn bỏ qua dòng ẩn.

Sau khi đổi, bảng lọc Hà Nội chỉ còn đúng 5 đơn với STT từ 1 tới 5, ô tổng ra 16.800.000. AGGREGATE(3,5) cũng bỏ qua dòng ẩn bằng tay. Khi kiểm tra, ẩn tay hàng 5 thì STT của các dòng bên dưới tự lùi lại 1.
Trong lúc kiểm tra, 2 cách viết khác cũng không bị lỗi dòng cuối: =SUBTOTAL(103,$B$2:B2)*1 và =IF(B2="","",SUBTOTAL(103,$B$2:B2)).
Phép tính 5: SUMIFS chỉ tính dòng đang hiện nhờ cột phụ
SUMIFS và COUNTIFS không bỏ qua dòng ẩn. Giả sử bạn đang lọc TP.HCM và muốn biết tổng doanh thu Máy in của các đơn đang hiện. Công thức =SUMIFS(G2:G15,E2:E15,"Máy in") ra 36.000.000, tức tổng của cả 4 đơn Máy in trong bảng. Con số đúng chỉ là 22.500.000 của 2 đơn Máy in ở TP.HCM.
Cách 1: Bạn thêm cột phụ đánh dấu dòng đang hiện
Bạn đặt tiêu đề "Hiện" ở ô H1, gõ vào ô H2 công thức
=AGGREGATE(3,5,B2), rồi kéo xuống H15. Dòng đang hiện cho kết quả 1, dòng bị ẩn cho kết quả 0.Bạn thêm điều kiện cột H bằng 1 vào SUMIFS:
=SUMIFS(G2:G15,H2:H15,1,E2:E15,"Máy in").Để đếm, bạn dùng
=COUNTIFS(H2:H15,1,E2:E15,"Máy in").

Khi lọc TP.HCM, SUMIFS có cột phụ ra 22.500.000 và COUNTIFS ra 2. Khi đổi sang lọc Hà Nội, hai công thức tự đổi thành 4.500.000 và 1.
Lưu ý: nếu bạn viết cột phụ bằng =SUBTOTAL(103,B2), cột phụ cũng gặp lỗi dòng cuối như phần STT. Khi kiểm tra, hàng 15 vẫn hiện và kết quả khi lọc TP.HCM sai thành 31.500.000. Vì vậy, bạn nên dùng AGGREGATE cho cột phụ.
Cách 2: Bạn dùng SUMPRODUCT kết hợp SUBTOTAL
Nếu không muốn thêm cột phụ, bạn dùng công thức sau:
=SUMPRODUCT(SUBTOTAL(109,OFFSET(G2,ROW(G2:G15)-ROW(G2),0))*(E2:E15="Máy in"))
Trong công thức, OFFSET tách vùng G2:G15 thành 14 ô riêng, SUBTOTAL(109) trả về giá trị của ô đang hiện và 0 cho ô đang ẩn, rồi SUMPRODUCT nhân với điều kiện Máy in và cộng lại. Khi lọc TP.HCM, công thức ra 22.500.000, khớp với cách cột phụ. Công thức này dài và khó sửa, nên với người mới, cách cột phụ dễ kiểm tra hơn.
Phép tính 6: AGGREGATE bỏ qua cả ô lỗi và dòng ẩn
Khi cột cần tính có một ô lỗi như #N/A, cả SUM và SUBTOTAL đều trả về lỗi. Lỗi #N/A thường đến từ hàm tra cứu không tìm thấy giá trị, bạn xem cách xử lý trong bài Hàm XLOOKUP trong Excel. Trong lúc chờ sửa dữ liệu, hàm AGGREGATE giúp bạn vẫn có con số tổng.
Cú pháp: =AGGREGATE(function_num, options, ref1, [ref2], ...)
function_num là mã phép tính từ 1 tới 19. Các mã hay dùng gồm 1 (AVERAGE), 2 (COUNT), 3 (COUNTA), 4 (MAX), 5 (MIN), 9 (SUM), 14 (LARGE) và 15 (SMALL).
options là tùy chọn cho biết AGGREGATE bỏ qua những gì, theo bảng bên dưới.
ref1 là vùng cần tính.
Tùy chọn | AGGREGATE bỏ qua |
|---|---|
0 tới 3 | Các hàm SUBTOTAL, AGGREGATE lồng trong vùng, cộng thêm dòng ẩn (1), giá trị lỗi (2) hoặc cả hai (3) |
4 | AGGREGATE không bỏ qua gì |
5 | Chỉ dòng ẩn |
6 | Chỉ giá trị lỗi |
7 | Dòng ẩn và giá trị lỗi |
Ví dụ bên dưới có 6 dòng doanh thu, trong đó ô B6 bị lỗi #N/A.

Khi chưa lọc, SUM, SUBTOTAL và AGGREGATE(9,5) đều báo #N/A. AGGREGATE(9,6) và AGGREGATE(9,7) bỏ qua ô lỗi và ra 24.600.000. Khi lọc Đà Nẵng, AGGREGATE(9,7) ra 9.500.000, đúng tổng 2 đơn Đà Nẵng. AGGREGATE(9,6) vẫn ra 24.600.000 vì tùy chọn 6 không bỏ qua dòng ẩn.
Lưu ý: bạn vẫn nên sửa ô lỗi tận gốc, ví dụ bọc công thức tra cứu bằng IFERROR.
Hàm FILTER lọc dữ liệu bằng công thức trên Excel 365
Hàm FILTER lấy các dòng thỏa điều kiện và trả kết quả ra một vùng mới, bảng gốc giữ nguyên. FILTER có trên Excel 365 và Excel 2021 trở lên.
Cú pháp: =FILTER(array, include, [if_empty])
array là vùng dữ liệu cần lấy ra, ví dụ B2:G15.
include là điều kiện lọc, ví dụ
D2:D15="Hà Nội".if_empty là nội dung hiện ra khi không có dòng nào thỏa điều kiện. Đối số này không bắt buộc.
Tại ô I2, bạn gõ =FILTER(B2:G15,D2:D15="Hà Nội","Không có"). Excel trả về 5 đơn Hà Nội, kết quả tự tràn xuống vùng I2:N6. Bạn chỉ gõ công thức ở một ô duy nhất.

Bạn kết hợp FILTER với các hàm khác để tính toán ngay trên kết quả lọc:
Tính tổng:
=SUM(FILTER(G2:G15,D2:D15="Hà Nội"))ra 16.800.000.Lọc thỏa cả hai điều kiện: bạn nhân các điều kiện với nhau. Công thức
=FILTER(B2:G15,(D2:D15="TP.HCM")*(G2:G15>=5000000),"Không có")trả về 3 đơn TP.HCM có doanh thu từ 5.000.000, tổng 30.000.000.
Lưu ý khi dùng FILTER:
FILTER không phụ thuộc AutoFilter. Khi bảng gốc đang lọc Đà Nẵng, công thức FILTER Hà Nội vẫn trả về đủ 5 đơn.
FILTER không mang theo định dạng. Cột ngày trong kết quả hiện số seri như 46296, bạn cần định dạng lại vùng đó theo kiểu dd/mm/yyyy để thấy 01/10/2026.
Khi không có dòng nào thỏa điều kiện và bạn bỏ trống if_empty, FILTER báo lỗi #CALC!. Khi bạn điền "Không có", ô kết quả hiện chữ "Không có".
Khi vùng tràn có ô đã chứa dữ liệu, FILTER báo lỗi #SPILL!.
Bạn lọc dữ liệu trùng nhau bằng Remove Duplicates hoặc UNIQUE
Khi làm sạch danh sách khách hàng, bạn hay cần lọc dữ liệu trùng nhau. Excel có 3 cách xử lý nhanh:
Remove Duplicates: bạn chọn vùng dữ liệu, vào Data > Remove Duplicates, chọn cột cần so sánh rồi bấm OK. Với danh sách 6 tên có tên lặp lại, Excel giữ lại 3 tên ở lần xuất hiện đầu tiên và xóa hẳn các dòng trùng.
Hàm UNIQUE (Excel 365):
=UNIQUE(D2:D15)trả về danh sách 3 khu vực không trùng là Hà Nội, TP.HCM, Đà Nẵng.Đánh dấu dòng trùng để lọc: bạn thêm cột phụ
=COUNTIF($A$2:$A$7,A2). Dòng có kết quả lớn hơn 1 là dòng bị trùng.
Nếu bạn cần cộng các giá trị giống nhau, ví dụ tổng doanh thu của từng khu vực mà không cần lọc, bạn dùng hàm SUMIF. Bạn xem cách dùng trong bài 7 ví dụ dùng hàm SUMIF trong Excel.
Những lỗi nào thường gặp khi tính trên dữ liệu đã lọc?
Bạn thấy | Nguyên nhân | Cách sửa |
|---|---|---|
Ô tổng không đổi khi lọc | Ô tổng dùng SUM, SUM cộng cả dòng ẩn | Bạn đổi sang SUBTOTAL(9,...) hoặc SUBTOTAL(109,...) |
Dòng cuối bảng luôn hiện dù không thỏa điều kiện lọc | Cột STT hoặc cột phụ dùng SUBTOTAL ở mọi dòng | Bạn đổi sang AGGREGATE(3,5,...) rồi vào Data > Reapply |
Dòng tổng biến mất khi lọc | Dòng tổng nằm sát bảng nên Excel đưa vào vùng lọc | Bạn chèn một dòng trống giữa bảng và dòng tổng |
SUMIFS, COUNTIFS vẫn tính cả dòng bị lọc ẩn | Hai hàm này không phân biệt dòng ẩn | Bạn thêm cột phụ AGGREGATE(3,5,...) làm điều kiện |
SUBTOTAL báo lỗi #N/A | Vùng tính có ô lỗi ở dòng đang hiện | Bạn dùng AGGREGATE với tùy chọn 6 hoặc 7 |
Người dùng Excel thường hỏi gì về tính tổng khi lọc dữ liệu?
Cách tính tổng trong Excel khi lọc dữ liệu là gì?
Bạn dùng =SUBTOTAL(9,vùng cần cộng) hoặc =SUBTOTAL(109,vùng cần cộng). Hai công thức này chỉ cộng các dòng đang hiện sau khi lọc. Mã 109 bỏ qua thêm các dòng bạn ẩn bằng tay.
Cách đánh số thứ tự trong Excel sau khi lọc như thế nào?
Bạn gõ =AGGREGATE(3,5,$B$2:B2) vào ô STT đầu tiên rồi kéo xuống, trong đó cột B là cột luôn có dữ liệu. Công thức SUBTOTAL(103,...) cũng đánh số được nhưng làm dòng cuối bảng luôn hiện khi lọc.
SUBTOTAL và AGGREGATE khác nhau thế nào?
SUBTOTAL có 11 phép tính và luôn bỏ qua dòng bị lọc ẩn. AGGREGATE có 19 phép tính và cho bạn chọn bỏ qua dòng ẩn, giá trị lỗi hoặc cả hai. Khi cột có ô lỗi, bạn dùng AGGREGATE.
Bạn muốn làm báo cáo Excel nhanh và chính xác hơn?
Khi đã quen với lọc dữ liệu, SUBTOTAL và AGGREGATE, bạn nên học tiếp nhóm hàm điều kiện (SUMIFS, COUNTIFS), nhóm hàm tra cứu (XLOOKUP) và Pivot Table để tổng hợp dữ liệu theo nhiều chiều. Bạn có thể xem thứ tự học các nhóm kỹ năng này trong bài Lộ trình học Excel từ cơ bản đến nâng cao.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.