Trong công việc quản lý dữ liệu, việc lọc (filter) dữ liệu trong bảng tính Excel thường xuyên xuất hiện. Khi ta áp dụng bộ lọc để chỉ xem những dòng thỏa mãn điều kiện, một câu hỏi tự nhiên sẽ nảy sinh: Có bao nhiêu dòng thực sự được hiển thị sau khi áp dụng filter?可 这个问题对数据分析师、会计师、 quản trị viên và bất cứ ai làm việc với dữ liệu số lượng lớn đều là vô cùng quan trọng, vì nó giúp ta hiểu rõ hơn về kích thước mẫu dữ liệu đang làm việc, đánh giá hiệu suất của bộ lọc, hay chuẩn bị báo cáo thống kê chính xác.
Trong bài viết này, chúng ta sẽ đi sâu vào cách đếm số dòng trong Excel sau khi filter bằng nhiều phương pháp khác nhau: công thức đếm, tính năng tự động của Excel, các công cụ bổ trợGender, và thậm chí là viết macro để tự động hóa quy trình. Mỗi phương pháp sẽ được trình bày chi tiết, kèm theo ví dụ minh họa, ưu nhược điểm và những lưu ý khi áp dụng trong thực tế.
Có thể bạn quan tâm: Cách Đánh Số Thứ Tự Trong Excel Khi Filter: Hướng Dẫn Chi Tiết Và Các Mẹo Thực Tiễn
1. Tại sao lại cần đếm dòng sau khi filter?
Trước khi đi vào các công thức, ta cần hiểu rõ lý do tại sao việc đếm dòng sau khi filter lại quan trọng:
- Định lượng dữ liệu thực tế – Khi ta lọc dữ liệu, một số dòng sẽ bị ẩn. Đếm số dòng hiển thị giúp ta biết chính xác số lượng mẫu đang làm việc.
- Kiểm tra tính đúng đắn của bộ lọc – Nếu bộ lọc khôngpheric hoạt động như mong muốn, ta có thể phát hiện ngay bằng cách so voormalige số dòng hiển thị.
- Tính toán thống kê – Nhiều công thức thống kê (tổng, trung bình, độ lệch chuẩn…) trong Excel்கு có thể áp dụng trực tiếp lên các dòng hiển thị. Việc biết kích thước mẫu giúp ta đánh giá độ tin cậy của kết quả.
- Báo cáo và trình bày – Khi lập báo cáo, cần ghi rõ số lượng dữ liệu đã phân tích. Đếm dòng sau khi filter giúp tránh những sai sót khi báo cáo sai số liệu.
Có thể bạn quan tâm: Cách Đánh Số Thứ Tự 01 02 Tim … Trong Excel: Hướng Dẫn Chi Tiết Từng Bước
2. Phương pháp 1: Sử dụng hàm SUBTOTAL
2.1 Cách hoạt động của SUBTOTAL
Hàm SUBTOTAL trong Excel cung cấp nhiều chức năng khác nhau (tổng, trung bình, đếm…) và đặc biệt là nó không tính các ô ẩn do filter. Khi ta áp dụng SUBTOTAL với mã chức năng COUNTA hoặc COUNT, nó sẽ trả về số lượng dòng hiển thị.
SUBTOTAL(function_num, ref1, ref2, …)function_numlà số mã cho loại phép tính (ví dụ: 103 =COUNTAbỏ qua filter, 101 =COUNTbỏ qua filter).
2.2 Cách thựcNum
Giả sử dữ liệu của bạn nằm trong cột A (từ A2 đến A1000). Bạn muốn đếm số dòng hiển thị sau khi filter.
- Đặt filter – Bật filter bằng cách chọn cột A và vào
Data > Filter. - Chọn ô trống – Ví dụ ô B1, nhập công thức:
excel =SUBTOTAL(103, A2:A1000)
Battery. - 103: hàm
COUNTAbỏ qua filter. A2:A1000: phạm vi dữ liệu.
Nhấn Enter – Kết quả sẽ là số dòng hiển thị.
Nếu dữ liệu có giá trị số (không có ô trống), bạn có thể dùng COUNT:
=SUBTOTAL(101, A2:A1000)
2.3 Ưu điểm và nhược điểm
| Ưu điểm | Nhược điểm |
|---|---|
| – Đơn giản, không cần công thức phức tạp. | – Phải biết chính xác phạm vi dữ liệu. |
| – Tự động cập nhật khi filter thay đổi. | – Không thể đếm các dòng ẩn do công thức khác (ví dụ: HIDE bằng VBA). |
| – Hỗ trợ trên mọi phiên bản Excel. | – Nếu có cột trống trong dữ liệu, hàm COUNTA sẽ bao gồm chúng. |
Có thể bạn quan tâm: Cách Đánh Dấu Bé Hơn Hoặc Bằng Trong Excel
3. Phương pháp 2: Sử dụng hàm AGGREGATE
3.1 Tại sao dùng AGGREGATE?
Hàm AGGREGATE cung cấp nhiều chức năng tương tự SUBTOTAL, nhưng linh hoạt hơn vì có thể bỏ qua lỗi, ô ẩn, và có thể xử lý nhiều phạm vi trong một lần gọi.
AGGREGATE(function_num, options, ref1, ref2, …)
Mã function_num 6 = COUNTA, Press 1 = AVERAGE, v.v.options 5 = bỏ qua lỗi, 7 = bỏ qua ô ẩn, 15 = bỏ qua cả hai.
3.2 Cách thựcNum
Giả sử dữ liệu trong cột B (B2:B1500):
=AGGREGATE(3, 5, B2:B1500)
- 3:
COUNTA(đếm ô không trống). - 5: bỏ qua lỗi.
- B2:B1500: phạm vi dữ liệu.
Nếu muốn bỏ qua cả ô ẩn, dùng 15:
=AGGREGATE(3, 15, B2:B1500)
3.3 Ưu điểm và nhược điểm
| Ưu điểm | Nhược điểm |
|---|---|
| – Có thể bỏ qua lỗi và ô ẩn cùng lúc. | – Độ phức tạp cao hơn so với SUBTOTAL. |
| – Hỗ trợ nhiều chức năng khác nhau (tổng, trung bình, min, max…). | – Có thể gây nhầm lẫn khi chọn options. |
| – Có thể áp dụng cho nhiều phạm vi trong một contractor. | – Đôi khi không được hỗ trợ trong một số phiên bản Excel cũ. |
4. Phương pháp 3: Sử dụng tính năng “Status Bar”
Excel có tính năng hiển thị số lượng dòng hiển thị ngay trong thanh trạng thái (Status Bar) dưới cùng của cửa sổ.
4.1 Cách thựcNum
- Bật Stopwatch – Kích chuột phải vào thanh trạng thái → chọn “Count” hoặc “Visible cells”.
- Áp dụng filter – Khi filter được kích hoạt, thanh trạng thái sẽ tự động hiển thị số dòng hiển thị.
Điều này rất nhanh và tiện lợi, nhưng chỉ áp dụng khi bạn cần xem nhanh số lượng, không cần lưu trữ trong ô.
5. Phương pháp 4: Sử dụng công cụ “Count Visible Cells”
Excel hiện có một công cụ nhanh giúp đếm ô hiển thị trong một vùng. Bạn có thể sử dụng nó để đếm dòng.
5.1 Cách thựcNum
- Chọn vùng dữ liệu – Ví dụ cột C (C2:C2000).
- Nhấn
Ctrl + Shift + L– Bật filter nếu chưa bật. - Chọn
Home > Find & Select > Go To Special. - Chọn “Visible cells only” và nhấn OK.
- Nhấn
Ctrl + G– Mở hộp “Go To” → nhấn “Special” → chọn “Visible cells only” → OK. - Nhấn
edance– Excel sẽ hiển thị số ô đã chọn trong thanh trạng thái.
Bạn có thể thậm chí thay đổi công thức =ROWS() để đếm số dòng hiển thị:
=ROWS(C2:C2000)
Mỗi khi filter thay 출력, bạn cần chọn lại vùng dữ liệu وزارة.
6. Phương pháp 5: Sử dụng công thức Mới FILTER (Excel 365)
Có thể bạn quan tâm: Cách Đếm Dữ Liệu Có Điều Kiện Trong Excel
Trong Excel 365, có hàm FILTER giúp lọc dữ liệu theo điều kiệnawak. Bạn có thể kết hợp với COUNTA để đếm số dòng.
6.1 Cách thựcNum
Giả sử dữ liệu trong cột D (D2:D California) và bạn muốn đếm số dòng có giá trị > 100:
=COUNTA(FILTER(D2:D2000, D2:D2000>100))
FILTER(D2:D2000, D2:D2000>100)trả về mảng các giá trị > 100.COUNTAđếm số ô trong mảng đó.
Điều này có thể thay thế việc áp dụng filter thủ công, đồng thời giữ dữ liệu gốc không bị ẩn.
7. Phương pháp 6: Sử dụng VBA để đếm dòng hiển thị
Nếu bạn thường xuyên cần đếm dòng sau khi filter và muốn tự động hoá quy trình, viết một macro đơn giản sẽ rất tiện lợi.
7.1 Macro mẫu
Function CountVisibleRows(rng As Range) As Long Dim cnt As Long cnt = Application.WorksheetFunction.Subtotal(103, rng) ' 103 = COUNTA, bỏ qua filter CountVisibleRows = cnt
End Function
7.2 Cách sử dụng
- Mở Visual Basic Editor –
Alt + F11. - Chèn một Module –
Insert > Module. - Dán code vào module.
- Quay lại Excel – Nhập công thức trong ô:
excel =CountVisibleRows(A2:A1000)
Macro này sẽ trả về số dòng hiển thị, giống như SUBTOTAL.
7.3 Ưu điểm
- Tự động hoá: Không cần nhập công thức thủ công mỗi lần.
- Linh hoạt: Bạn có thể mở rộng macro để đếm nhiều vùng, hoặc tính thêm các điều kiện.
8. Tương đồng và khác biệt giữa SUBTOTAL, AGGREGATE, và FILTER
| Hàm | Tính năng | Bỏ qua ô ẩn | Bỏ qua lỗi | Được hỗ trợ trong Excel? |
|---|---|---|---|---|
| SUBTOTAL | Đếm, tổng, min, max… | Có | Không | Từ Excel 2007 trở đi |
| AGGREGATE | Tương tự + thêm “options” | Có | Có | Từ Excel 2026 trở đi |
| FILTER | Lọc dữ liệu theo điều kiện | Không (làm mới mảng) | Không | Excel 365 |
Đối với bài toán đếm dòng sau khi filter, SUBTOTAL là lựa chọn đơn giản nhất. Nếu bạn cần bỏ qua lỗi cũng, hoặc làm việc với nhiều vùng, AGGREGATE sẽ phù hợp hơn. FILTER thích hợp khi bạn muốn tạo bảng dữ liệu lọc động mà không cần thao tác ẩn/hiện thủ công.
9. Mẹo nâng cao: Đếm dòng hiển thị trong các sheet khác nhau
Đôi khi bạn cần đếm số dòng hiển thị trong một sheet cụ thể nhưng hiển thị kết quả trong sheet khác.
9.1 Công thức tham chiếu sheet
=SUBTOTAL(103, Sheet2!A2:A2000)
Nhớ đặt tên sheet đúng và đảm bảo không có ký tự đặc biệt. Bạn cũng có thể dùng hàm INDIRECT để tham chiếu động:
=SUBTOTAL(103, INDIRECT("'" & A1 & "'!A2:A2000"))
Trong đó, ô A1 chứa tên sheet cần đếm.
10. Thực hành: Cách đếm dòng sau khi filter trong một workbook thực tế
Giả sử bạn đang làm việc với một bảng dữ liệu bán hàng lớn (5000 dòng). Bạn muốn:
- Lọc các đơn hàng có giá trị > 10,000.
- Đếm số đơn hàng còn lại.
- Tính tổng doanh thu từ các đơn hàng này.
10.1 Bước 1: Lọc dữ liệu
- Chọn cột “Doanh thu” (cột D).
Data > Filter.- Nhấn vào mũi tên filter →
Number Filters > Greater than…→ nhập10000.
10.2 Bước 2: Đếm dòng hiển thị
- Trong ô
A1, nhập:excel =SUBTOTAL(103, D2:D5001) - Kết quả ví dụ:
245.
10.3 Bước 3: Tính tổng doanh thu
- Trong ô
B1, nhập:excel =SUBTOTAL(109, D2:D5001) - 109 =
SUM(tổng) bỏ qua filter.
Kết quả tổng doanh thu sẽ phản ánh đúng số đơn hàng lọc તાલુક.
11. Các lưu ý khi đếm dòng sau khi filter
- Phạm vi dữ liệu – Cần chắc chắn rằng phạm vi được chọn đầy đủ, không bỏ sót hàng tiêu đề hay dữ liệu cuối cùng.
- Ô trống – Khi dùng
COUNTA, các ô trống trong cột sẽ được tính. Nếu dữ liệu có ô trống, cân nhắc dùngCOUNThoặc Indoor. - Công thức lẫn nhau – Nếu công thức
SUBTOTALhoặcAGGREGATEnằm trong vùng dữ liệu được lọc, kết quả có thể bị ảnh hưởng. - Filter đa cột – Khi áp dụng filter trên nhiều cột,
SUBTOTALvẫn chỉ tính số dòng hiển thị, không phụ thuộc vào cột nào. - Văn bản vs số –
SUBTOTAL(101, …)chỉ đếm số;SUBTOTAL(103, …)đếm cả văn bản và sốjpeg.
12. Kết luận
Đếm số dòng trong Excel sau khi filter là một thao tác quan trọng nhưng không phức tạp. Bạn có thể chọn phương pháp phù hợp tùy theo nhu cầu:
- Hàm SUBTOTAL: Đơn giản, nhanh gọn.
- Hàm AGGREGATE: Linh hoạt, bỏ qua lỗi và ô ẩn.
- Status Bar: Xem nhanh trong giao diện.
- FILTER (Excel 365): Lọc động và đếm trong một bước.
- VBA: Tự động hoá quy trình, linh hoạt hơn.
Bằng cách nắm vững những công cụ này, bạn sẽ tiết kiệm thời gian, giảm thiểu sai sót và nâng cao hiệu suất công việc khi làm việc với dữ liệu lớn trong Excel. Hãy thử áp dụng và cảm nhận sự khác biệt ngay hôm nay!
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ẻ.