Cách Đếm Số Dòng Được Lọc Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Trong môi trường làm việc với dữ liệu, Excel luôn là công cụ không thể thiếu. Một trong những thao tác thường gặp nhưng lại gây khó khăn cho nhiều người dùng là đếm số dòng (rows) đã được lọc (filtered). Khi dữ liệu lớn, việc biết chính xác bao nhiêu bản ghi đang hiển thị sau khi áp dụng bộ lọc là rất quan trọng để đưa ra quyết định nhanh chóng và chính xác. Bài viết này sẽ cung cấp cho bạn hướng dẫn toàn diện về cách đếm số dòng đã lọc trong Excel, bao gồm các phương pháp sử dụng công thức, hàm, PivotTable, VBA và các mẹo tối ưu hoá quy trình.

Có thể bạn quan tâm: Cách Tính Số Ngày Giữa Hai Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Nội Dung

1. Tại sao cần đếm số dòng đã lọc?
2. Cách 1: Sử dụng thanh trạng thái (Status Bar)
3. Cách 2: Dùng hàm SUBTOTAL
4. Cách 3: Kết hợp hàm AGGREGATE
5. Cách 4: Đếm với hàm COUNTA + FILTER (Excel 365/2026)
6. Cách 5: Sử dụng PivotTable để đếm tự động
7. Cách 6: Đếm bằng VBA (Macro)
8. Mẹo nhanh: Đếm các ô có màu nền (Conditional Formatting)
9. So sánh các phương pháp: Khi nào dùng gì?
10. Kết luận và lời khuyên thực tiễn

Có thể bạn quan tâm: Cách Đếm Ô Trống Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Tại sao cần đếm số dòng đã lọc?

• Báo cáo nhanh: Khi trình bày dữ liệu cho quản lý, thường chỉ cần biết tổng số bản ghi thỏa mãn tiêu chí lọc.
• Kiểm tra dữ liệu: Xác nhận rằng bộ lọc đã hoạt động đúng, không bỏ sót hay lọc sai.
• Tính toán phụ thuộc: Nhiều công thức tính tổng, trung bình,… cần dựa trên số lượng dòng hiện tại.
• Quản lý dự án: Đếm số công việc, nhiệm vụ, đơn hàng… trong một trạng thái cụ thể.
• Tự động hoá: Khi viết macro hoặc báo cáo tự động, việc lấy số lượng dòng lọc là bước đầu quan trọng.

Có thể bạn quan tâm: Mẫu Excel Phân Tích Chi Phí – Lợi Ích (cost Benefit Analysis) – Hướng Dẫn Chi Tiết Từ A Đến Z

Cách 1: Sử dụng thanh trạng thái (Status Bar)

1.1. Cách thực hiện

1. Áp dụng bộ lọc: Chọn dữ liệu → Data → Filter (hoặc Ctrl + Shift + L).
2. Lọc dữ liệu theo tiêu chí mong muốn.
3. Nhìn xuống thanh trạng thái (ở dưới cùng của cửa sổ Excel). Mặc định, Excel sẽ hiển thị thông tin như:
   Count: 152 (đếm số ô chứa dữ liệu) hoặc Count: 152 of 500 (số ô hiển thị so với tổng).

1.2. Tùy chỉnh thanh trạng thái

• Nhấp chuột phải vào thanh trạng thái → chọn hoặc bỏ chọn các mục hiển thị: Count, Sum, Average,…
• Đảm bảo Count đã được bật để hiển thị số dòng đã lọc.

1.3. Ưu và nhược điểm

Cách 2: Dùng hàm SUBTOTAL

Hàm SUBTOTAL là một trong những hàm mạnh mẽ nhất để làm việc với dữ liệu đã lọc. Nó tự động bỏ qua các hàng bị ẩn bởi bộ lọc.

2.1. Cú pháp

SUBTOTAL(function_num, ref1, ref2, …)

• function_num: Mã số xác định hàm thống kê (1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 9 = SUM, …). Khi dùng các số từ 101 trở lên, hàm sẽ bỏ qua các hàng ẩn bằng tay (manual hide) ngoài bộ lọc.
• ref: Phạm vi cần tính.

2.2. Đếm số dòng có dữ liệu (COUNT)

=SUBTOTAL(2, A2:A1000)

• 2 = hàm COUNT (đếm các ô chứa số). Nếu cột chứa cả văn bản, dùng 3 (COUNTA) để đếm mọi ô không rỗng.

2.3. Đếm số dòng không rỗng (COUNTA)

=SUBTOTAL(3, A2:A1000)

2.4. Ví dụ thực tế

Giả sử bảng dữ liệu B2:B2000 chứa tên khách hàng. Bạn muốn đếm số khách hàng hiện đang hiển thị sau khi lọc theo thành phố.

=SUBTOTAL(3, B2:B2000)

Sau khi thay đổi bộ lọc, công thức này sẽ tự động cập nhật số lượng khách hàng hiển thị.

2.5. Lưu ý

• Nếu dùng SUBTOTAL trong một ô nằm trong cùng phạm vi đang tính (ví dụ: công thức trong cột B2:B2000), sẽ gây vòng lặp. Đặt công thức bên ngoài phạm vi.
• Khi muốn bỏ qua cả các hàng ẩn bằng tay, dùng 101‑111 thay cho 1‑11.

Cách 3: Kết hợp hàm AGGREGATE

Hàm AGGREGATE (Excel 2026 trở lên) cung cấp tính năng tương tự SUBTOTAL nhưng linh hoạt hơn, cho phép bỏ qua lỗi và bỏ qua các hàng ẩn.

3.1. Cú pháp

AGGREGATE(function_num, options, array, k)

• function_num: Mã hàm (1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 9 = SUM,…)
• options: Các tùy chọn (0 = không bỏ qua gì, 1 = bỏ qua lỗi, 2 = bỏ qua hidden rows, 3 = bỏ qua cả lỗi và hidden rows)
• array: Dải dữ liệu.
• k: Tham số chỉ dùng cho các hàm thứ tự (LARGE, SMALL, …).

3.2. Đếm số dòng đã lọc (bỏ qua hidden rows)

=AGGREGATE(2, 3, A2:A1000)

• 2 = COUNT
• 3 = bỏ qua lỗi và hidden rows (có nghĩa là bỏ qua cả hàng ẩn bằng bộ lọc và ẩn thủ công)

Nếu chỉ muốn bỏ qua hidden rows (bộ lọc), dùng options = 2.

=AGGREGATE(2, 2, A2:A1000)

3.3. Khi nào dùng AGGREGATE?

• Khi dữ liệu có lỗi #DIV/0!, #N/A, … và bạn muốn chúng không ảnh hưởng tới kết quả.
• Khi bạn muốn kết hợp nhiều tính năng (bỏ qua hidden rows + bỏ qua lỗi) trong một công thức duy nhất.

Cách 4: Đếm với hàm COUNTA + FILTER (Excel 365/2026)

Với Excel 365 và Excel 2026, Microsoft giới thiệu các hàm mảng động như FILTER, COUNTIF, LET, … cho phép tạo công thức ngắn gọn và mạnh mẽ.

4.1. Sử dụng FILTER để tạo mảng dữ liệu đã lọc

Giả sử bảng dữ liệu nằm trong A1:D1000, và bạn muốn lọc theo cột C (trạng thái) = “Hoàn thành”.

=FILTER(A1:D1000, C1:C1000="Hoàn thành")

Công thức trên trả về một mảng các hàng thỏa mãn điều kiện.

4.2. Đếm số dòng trong mảng FILTER

=ROWS(FILTER(A1:D1000, C1:C1000="Hoàn thành"))

• ROWS trả về số hàng của mảng.

Nếu bạn chỉ cần đếm cột B trong các hàng đã lọc (không quan trọng nội dung), dùng:

=COUNTA(FILTER(B1:B1000, C1:C1000="Hoàn thành"))

4.3. Kết hợp nhiều tiêu chí

=ROWS(FILTER(A1:D1000, (C1:C1000="Hoàn thành") (D1:D1000>DATE(2026,1,1))))

• Dấu hoạt động như AND logic.

4.4. Ưu điểm

• Mã ngắn gọn, dễ đọc.
• Không phụ thuộc vào bộ lọc truyền thống; công thức tự động cập nhật khi dữ liệu thay đổi.

4.5. Nhược điểm

Có thể bạn quan tâm: Cách Đếm Giá Trị Độc Nhất (distinct) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

• Chỉ khả dụng trong Excel 365/2026 và các phiên bản hỗ trợ hàm mảng động.
• Khi dữ liệu rất lớn, hàm FILTER có thể gây chậm trễ.

Cách 5: Sử dụng PivotTable để đếm tự động

PivotTable là công cụ mạnh mẽ để tóm tắt và phân tích dữ liệu. Nó có thể đếm số bản ghi dựa trên các tiêu chí lọc một cách nhanh chóng.

5.1. Tạo PivotTable

1. Chọn bất kỳ ô nào trong bảng dữ liệu.
2. Vào Insert → PivotTable.
3. Chọn vị trí (trên sheet mới hoặc hiện tại) → OK.

5.2. Đặt trường

• Rows: Kéo trường cần phân loại (ví dụ: Trạng thái).
• Values: Kéo cùng trường hoặc một trường bất kỳ → Mặc định sẽ là Count of ….

5.3. Áp dụng bộ lọc

• Kéo trường muốn lọc vào Filters hoặc Columns.
• Chọn giá trị cần lọc, PivotTable sẽ tự động cập nhật số lượng bản ghi.

5.4. Lấy giá trị đếm ra công thức

Nếu muốn sử dụng số đếm trong các công thức khác, bạn có thể liên kết (link) ô chứa giá trị Count trong PivotTable (sử dụng GETPIVOTDATA).

=GETPIVOTDATA("Trạng thái",$A$3,"Trạng thái","Hoàn thành")

5.5. Khi nào dùng PivotTable?

• Khi cần phân tích đa chiều (đếm theo nhiều tiêu chí).
• Khi muốn tự động cập nhật khi dữ liệu thay đổi mà không cần viết công thức phức tạp.

Cách 6: Đếm bằng VBA (Macro)

Đối với các báo cáo tự động hoặc khi muốn đưa kết quả vào MessageBox, UserForm, hoặc điền vào một ô cụ thể, VBA là lựa chọn linh hoạt.

6.1. Mã VBA đơn giản

Sub CountFilteredRows() Dim ws As Worksheet Dim rng As Range Dim cnt As Long Set ws = ThisWorkbook.Sheets("Dữ liệu") ' Thay tên sheet phù hợp Set rng = ws.Range("A1").CurrentRegion ' Giả sử dữ liệu bắt đầu ở A1 ' Đếm các hàng không ẩn (đã lọc) cnt = rng.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 ' Trừ tiêu đề MsgBox "Số dòng đã lọc: " & cnt, vbInformation
End Sub

• SpecialCells(xlCellTypeVisible) trả về các ô hiển thị (không ẩn). Khi bộ lọc được áp dụng, các hàng không thỏa mãn sẽ bị ẩn và không được tính.

6.2. Đưa kết quả vào một ô

ws.Range("F1").Value = cnt ' Ghi số đếm vào ô F1

6.3. Đếm với nhiều cột

Nếu muốn chắc chắn rằng cả hàng được tính khi ít nhất một ô trong hàng không ẩn, có thể dùng:

cnt = Application.WorksheetFunction.Subtotal(103, rng.Columns(1)) ' 103 = COUNTA, bỏ qua hidden rows

6.4. Khi nào dùng VBA?

• Khi cần tự động hoá quy trình (ví dụ: mỗi khi nhấn nút, báo cáo tự cập nhật).
• Khi muốn gửi email, lưu vào file dựa trên số lượng dòng đã lọc.
• Khi làm việc trên Workbook có nhiều bảng và muốn một macro tổng hợp.

Mẹo nhanh: Đếm các ô có màu nền (Conditional Formatting)

Đôi khi bộ lọc dựa trên định dạng có điều kiện (Conditional Formatting) và bạn muốn đếm số ô được tô màu (ví dụ: các giá trị vượt ngưỡng).

7.1. Sử dụng hàm GET.CELL (đòi hỏi tạo một tên định nghĩa)

1. Định nghĩa tên:
2. Vào Formulas → Name Manager → New.
3. Đặt tên: CellColor.
4. Trong Refers to nhập: =GET.CELL(63,INDIRECT("rc",FALSE))

5. Lưu lại.

6. Thêm cột phụ (ví dụ: cột Z) và nhập công thức:

=CellColor

• Kéo công thức xuống toàn bộ cột Z. Các ô sẽ trả về số màu nền (index).

• Đếm số màu cụ thể (giả sử màu nền đỏ có index = 3):

=COUNTIF(Z2:Z1000,3)

7.2. Lưu ý

• Hàm GET.CELL là hàm cũ (Excel 4 macro) và không cập nhật tự động khi thay đổi màu; cần tính lại (F9) hoặc dùng VBA để cập nhật.
• Đối với Conditional Formatting dựa trên công thức, bạn có thể tái tạo lại công thức đếm bằng cách sử dụng cùng điều kiện trong hàm COUNTIFS.

So sánh các phương pháp: Khi nào dùng gì?

Kết luận và lời khuyên thực tiễn

Đếm số dòng đã lọc trong Excel không còn là một công việc “khó khăn” nữa, nhờ vào nhiều công cụ và hàm được tích hợp sẵn. Dưới đây là một số lời khuyên cuối cùng để bạn lựa chọn phương pháp phù hợp:

1. Xác định mục tiêu:
2. Nếu chỉ muốn kiểm tra nhanh, sử dụng thanh trạng thái.
3. Nếu cần kết quả trong công thức, ưu tiên SUBTOTAL hoặc AGGREGATE.

4. Nếu làm việc trên Excel 365, hãy khai thác FILTER để có công thức ngắn gọn và mạnh mẽ.

5. Kiểm tra dữ liệu:

6. Dữ liệu có lỗi? Hãy dùng AGGREGATE với tùy chọn bỏ qua lỗi.

7. Dữ liệu lớn (hàng > 100.000)? Tránh dùng FILTER quá lớn; SUBTOTAL hoặc PivotTable sẽ nhanh hơn.

8. Tự động hoá:

9. Khi tạo báo cáo định kỳ, hãy viết một macro VBA để đếm và ghi lại kết quả, giảm thiểu thao tác thủ công.

10. Định dạng có điều kiện:

11. Nếu bộ lọc dựa trên màu sắc, hãy cân nhắc đánh dấu lại bằng cột phụ (sử dụng công thức hoặc VBA) để có thể đếm bằng COUNTIF.

12. Kiểm tra lại:

13. Luôn so sánh kết quả từ hai phương pháp (ví dụ: SUBTOTAL vs. Status Bar) để chắc chắn không có sai sót.

Bước thực hành nhanh (tóm tắt)

1. Áp dụng bộ lọc → Data → Filter.
2. Đếm nhanh → Nhìn thanh trạng thái (hoặc dùng =SUBTOTAL(3, A2:A1000)).
3. Nếu cần công thức nâng cao → Dùng =ROWS(FILTER(A2:D1000, C2:C1000="Hoàn thành")).
4. Lưu công thức ở một ô riêng, để khi thay đổi bộ lọc, giá trị tự cập nhật.

Với những kiến thức trên, bạn đã sẵn sàng đếm và quản lý số lượng dòng đã lọc trong mọi tình huống Excel. Hãy thử áp dụng ngay trong công việc của mình và trải nghiệm sự tiện lợi, hiệu quả mà Excel mang lại!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.