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

  1. Bạn bấm chọn một ô bất kỳ trong bảng, ví dụ ô C3.

  2. 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.

  3. 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.

Bật bộ lọc trong Excel bằng nút Filter ở tab Data hoặc phím tắt Ctrl + Shift + L
Nút Filter nằm trong nhóm Sort & Filter của tab Data, bật xong mỗi tiêu đề cột có một nút lọc

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:

  1. Bạn bấm nút lọc ở ô tiêu đề Khu vực (ô D1).

  2. Bạn bỏ tick ô (Select All) để bỏ chọn toàn bộ giá trị.

  3. Bạn tick Hà Nội rồi bấm OK.

Menu lọc theo cột Khu vực trong Excel: ô Search, danh sách giá trị, Text Filters, Filter by Color
Menu lọc của cột Khu vực: bỏ (Select All), 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.

Khi lọc Hà Nội, SUM vẫn ra 73.800.000 còn SUBTOTAL(9) ra 16.800.000, SUBTOTAL đếm 5 đơn đang hiện
Đang lọc Hà Nội: SUM vẫn cộng cả 14 đơn, SUBTOTAL chỉ tính 5 đơn đang hiện

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 + T rồ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

=SUBTOTAL(1,G2:G15)

3.360.000

5.271.429

Đơn có doanh thu lớn nhất

=SUBTOTAL(4,G2:G15)

4.500.000

13.500.000

Đơn có doanh thu nhỏ nhất

=SUBTOTAL(5,G2:G15)

1.600.000

1.600.000

Tổng số lượng sản phẩm

=SUBTOTAL(9,F2:F15)

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.

Ẩn tay hàng 3 và 10: SUBTOTAL mã 9 ra 73.800.000, SUBTOTAL mã 109 ra 51.300.000
Không lọc, ẩn tay 2 hàng: mã 9 vẫn cộng dòng ẩn, mã 109 bỏ qua dòng ẩn

Công thức

Không ẩn, không lọc

Ẩn tay hàng 3 và 10

Lọc Hà Nội

=SUM(G2:G15)

73.800.000

73.800.000

73.800.000

=SUBTOTAL(9,G2:G15)

73.800.000

73.800.000

16.800.000

=SUBTOTAL(109,G2:G15)

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.

Đánh số thứ tự khi lọc: SUBTOTAL(103) làm dòng cuối luôn hiện, AGGREGATE(3,5) cho STT 1 tới 5 đúng
Công thức STT bằng SUBTOTAL(103) giữ lại dòng cuối, đổi sang AGGREGATE(3,5) thì lọc đúng

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

  1. 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.

  2. 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").

  3. Để đếm, bạn dùng =COUNTIFS(H2:H15,1,E2:E15,"Máy in").

Dùng cột phụ AGGREGATE(3,5,B2) để SUMIFS chỉ cộng các dòng đang hiện sau khi lọc
Cột phụ H đánh dấu dòng đang hiện, SUMIFS thêm điều kiện cột H để chỉ cộng dòng đang hiện

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.

Hàm AGGREGATE tùy chọn 6 và 7 bỏ qua ô lỗi #N/A, trong khi SUM và SUBTOTAL báo lỗi
AGGREGATE với tùy chọn 6 hoặc 7 vẫn cộng được khi cột có ô 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.

Hàm FILTER trong Excel 365 lọc các đơn Hà Nội và các đơn TP.HCM có doanh thu từ 5.000.000
Hàm FILTER trả kết quả lọc ra vùng mới, kết hợp SUM để tính tổng

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.