Tôi vừa viết bài về dùng hàm tóm tắt trong Excel để dễ dàng tổng hợp dữ liệu lớn, nhưng bài đó tính trên toàn bộ trang tính. Làm sao nếu chỉ muốn xem tập hợp con và tóm tắt tập hợp con đó?
Trong Excel, bạn tạo bộ lọc trên cột để ẩn hàng không khớp. Ngoài ra, dùng các hàm đặc biệt để tóm tắt dữ liệu chỉ từ dữ liệu đã lọc.
Bài viết hướng dẫn tạo bộ lọc trong Excel và dùng hàm tích hợp tóm tắt dữ liệu đã lọc.
Tạo bộ lọc đơn giản trong Excel
Excel có bộ lọc đơn giản và nâng cao. Bắt đầu với đơn giản. Khi làm việc với bộ lọc, nên có hàng đầu tiên làm nhãn cột. Không bắt buộc nhưng dễ làm việc hơn.
Ví dụ: dữ liệu giả, muốn lọc cột Thành phố (City). Rất dễ: tab Dữ liệu (Data) → nút Lọc (Filter). Không cần chọn dữ liệu hay click hàng đầu tiên.
Khi nhấp Filter, mỗi cột hàng đầu tiên tự động có nút mũi tên thả xuống bên phải.
Nhấp mũi tên cột City → thấy các tùy chọn:
Đầu tiên: sắp xếp nhanh tất cả hàng theo giá trị cột City. Lưu ý: sắp xếp sẽ di chuyển toàn bộ hàng, không chỉ giá trị cột City – dữ liệu giữ nguyên vẹn.
Hay thêm cột ID ở đầu, đánh số 1 đến số hàng. Sau này sắp xếp theo ID để khôi phục thứ tự gốc nếu cần.
Dữ liệu đã sắp xếp theo City. Chưa ẩn hàng nào. Xem ô kiểm dưới hộp thoại lọc: ví dụ có 3 giá trị duy nhất trong cột City, cả 3 hiện ra.
Bỏ tích 2 thành phố, giữ 1 → chỉ còn 8 hàng hiện, các hàng còn lại ẩn. Dễ nhận ra đang xem dữ liệu lọc: xem số hàng bên trái. Tùy số hàng ẩn, thấy vài đường ngang thêm và số màu xanh.
Muốn lọc thêm cột thứ hai để thu hẹp kết quả: cột C là tổng thành viên mỗi gia đình, chỉ muốn gia đình >2 thành viên.
Nhấp mũi tên cột C → thấy ô kiểm cho từng giá trị duy nhất. Nhưng ta muốn: nhấp Bộ lọc số (Number Filters) → Lớn hơn (Greater Than). Còn nhiều tùy chọn khác.
Hộp thoại mới: nhập giá trị lọc. Có thể thêm nhiều tiêu chí với AND/OR. Ví dụ: giá trị >2 và ≠5.
Kết quả: chỉ 5 hàng – gia đình chỉ ở New Orleans và ≥3 thành viên. Dễ phải không? Xóa lọc cột: nhấp mũi tên → Xóa lọc từ "Tên cột" (Clear Filter From "Column Name").
Đó là bộ lọc đơn giản. Rất dễ dùng, kết quả trực quan. Tiếp theo: bộ lọc nâng cao qua hộp thoại Nâng cao (Advanced).
Tạo bộ lọc nâng cao trong Excel
Muốn lọc phức tạp hơn, dùng hộp thoại Nâng cao (Advanced). Ví dụ: muốn thấy gia đình ở New Orleans >2 thành viên HOẶC gia đình ở Clarksville >3 thành viên VÀ chỉ email kết thúc .EDU. Bộ lọc đơn giản không làm được.
Cần thiết lập trang tính khác: chèn vài hàng trên dữ liệu, copy nhãn tiêu đề chính xác vào hàng đầu tiên như hình.
Cách bộ lọc nâng cao hoạt động: nhập tiêu chí vào các ô cột đầu → nhấp nút Nâng cao (Advanced) dưới Sắp xếp & Lọc (Sort & Filter) tab Dữ liệu.
Nhập gì vào ô tiêu chí? Ví dụ: chỉ muốn New Orleans hoặc Clarksville → gõ vào ô E2 và E3.
Giá trị trên hàng khác nhau = OR. Muốn New Orleans >2 thành viên VÀ Clarksville >3 thành viên: gõ >2 vào C2, >3 vào C3.
Cùng hàng = AND. Cuối: chỉ email kết thúc .EDU → gõ *.edu vào D2 và D3. Dấu * = bất kỳ ký tự nào.
Xong → click bất kỳ đâu trong tập dữ liệu → nhấp Nâng cao. Ô Vùng danh sách (List Range) tự xác định tập dữ liệu (vì click vào trước). Nhấp nút nhỏ bên phải ô Vùng tiêu chí (Criteria range).
Chọn từ A1 đến E3 → nhấp nút lại để về hộp thoại Advanced Filter → OK. Dữ liệu đã lọc!
Chỉ 3 kết quả khớp tất cả tiêu chí. Lưu ý: nhãn vùng tiêu chí phải khớp chính xác nhãn tập dữ liệu.
Có thể tạo truy vấn phức tạp hơn, hãy thử nghiệm. Cuối cùng: áp dụng hàm tổng hợp cho dữ liệu đã lọc.
Tóm tắt dữ liệu đã lọc
Giả sử muốn tổng số thành viên trên dữ liệu đã lọc, làm sao? Xóa lọc bằng nút Xóa (Clear) trên dải lệnh. Không lo, áp dụng lại Advanced Filter dễ dàng: nhấp Advanced → OK.
Cuối tập dữ liệu, thêm ô Tổng (Total) → hàm SUM tổng thành viên. Ví dụ: =SUM(C7:C31).
Tất cả gia đình: 78 thành viên. Áp dụng lại Advanced Filter → xem kết quả.
Sai! Vẫn thấy 78 thay vì 11 chính xác! Tại sao? Hàm SUM không bỏ qua hàng ẩn, vẫn tính tất cả hàng. May mắn có hàm đặc biệt bỏ qua hàng ẩn.
Hàm đầu tiên: SUBTOTAL. Trước khi dùng, xóa lọc → gõ hàm.
Xóa lọc → gõ =SUBTOTAL( → hộp thả xuống hiện các tùy chọn. Chọn loại hàm tổng hợp bằng số. Ví dụ muốn SUM → gõ số 9 hoặc chọn từ hộp thả xuống. Phẩy → chọn vùng ô.
Enter → giá trị 78 như trước. Nhưng áp dụng lọc lại → thấy 11!
Tuyệt vời! Điều chỉnh lọc → giá trị luôn phản ánh hàng đang hiển thị.
Hàm thứ hai: AGGREGATE – hoạt động gần như giống SUBTOTAL. Khác: tham số thứ hai chỉ định bỏ qua hàng ẩn.
Tham số 1: hàm tổng hợp (9 = SUM). Tham số 2: gõ 5 để bỏ qua hàng ẩn. Tham số cuối: vùng ô.
Đọc thêm bài về hàm tóm tắt để hiểu AGGREGATE và MODE, MEDIAN, AVERAGE... chi tiết hơn.
Hy vọng bài viết gives bạn điểm khởi đầu tốt cho tạo và dùng bộ lọc Excel. Hãy bình luận nếu có thắc mắc. Chúc thành công!