Nếu bạn làm việc với chuỗi dữ liệu lớn, thì không chỉ việc tính toán dữ liệu, mà việc tìm thông tin liên quan cũng rất khó khăn. Nhưng Excel đã giúp việc thu nhỏ vùng tìm kiếm trở nên dễ dàng hơn bằng công cụ lọc dữ liệu đơn giản nhưng rất hữu ích. Trong bài viết này, UniTrain sẽ giúp bạn hiểu thêm về cách tạo bộ lọc trong Excel.
Đếm hàng đã lọc
Bạn đã áp dụng một bộ lọc cho nội dung của một trong các trang tính của mình. Bạn muốn xác định số hàng hiển thị trong dữ liệu được lọc, nhưng khi bạn cố gắng đếm chúng bằng cách sử dụng hàm COUNT, Excel sẽ trả về tổng số hàng, bao gồm cả những hàng không được hiển thị trong danh sách đã lọc.
Đây thực sự là hành vi bình thường — hàm COUNT trả về tất cả các hàng trong một dải ô, cho dù chúng có hiển thị hay không. Nếu bạn muốn xác định số hàng đáp ứng tiêu chí của bộ lọc của mình, bạn có thể sử dụng nhiều kỹ thuật khác nhau.
Nếu bạn không cần tính số lượng của mình trong một ô, thì bạn có thể chỉ cần dựa vào Excel để thông báo cho bạn về số lượng. Khi bạn áp dụng bộ lọc, thanh trạng thái Excel chứa số lượng hàng được bộ lọc hiển thị. Số lượng này sẽ biến mất ngay khi bạn bắt đầu chỉnh sửa thông tin khác trong trang tính của mình, nhưng nó sẽ xuất hiện lại nếu bạn áp dụng lại bộ lọc.
Bạn cũng có thể chọn tất cả các ô hiển thị trong một cột cụ thể và chỉ cần xem trong vùng “tổng” của thanh trạng thái. Theo mặc định, Excel hiển thị tổng của bất kỳ ô nào bạn đã chọn, nhưng bạn có thể nhấp chuột phải vào tổng này và thay vào đó, hướng dẫn Excel hiển thị số lượng các ô đã chọn.
Nếu bạn muốn sử dụng công thức để xác định số hàng, bạn có thể sử dụng hàm COUNTIF. Tất cả những gì bạn cần làm là đảm bảo rằng tiêu chí được chỉ định trong hàm giống với tiêu chí mà bạn đã sử dụng trong bộ lọc của mình. Tất nhiên, hạn chế của điều này là nếu bạn thay đổi tiêu chí bộ lọc của mình, bạn cũng sẽ cần thay đổi tiêu chí COUNTIF để có được số lượng chính xác.
Bạn cũng có thể sử dụng hàm SUBTOTAL để xác định số lượng hàng. Ví dụ: nếu thông tin được lọc của bạn ở A2: A500, bạn có thể sử dụng công thức này để hiển thị số hàng được hiển thị bởi bộ lọc:
=SUBTOTAL(2, A2:A500)
Tham số đầu tiên, 2, cho biết rằng bạn muốn Excel sử dụng hàm COUNT để xác định kết quả công thức. Nếu bạn thay đổi tham số này thành 3 thì SUBTOTAL sẽ sử dụng COUNTA để thay thế. Dù bằng cách nào, SUBTOTAL chỉ đếm những hàng được bộ lọc hiển thị.
Lọc các cột
Bạn biết rằng bạn có thể sử dụng khả năng lọc của Excel để chọn các hàng trong bảng dữ liệu được hiển thị, nhưng tự hỏi liệu có cách nào để lọc theo cột hay không. Nói cách khác, bây giờ bạn có thể lọc theo chiều dọc, nhưng bạn muốn lọc dữ liệu của mình theo chiều ngang.
Câu trả lời ngắn gọn là không có cách nào cả. Excel, khi được sử dụng để lưu trữ dữ liệu, tuân theo một mô hình cơ sở dữ liệu tương đương các hàng với bản ghi và cột với các trường trong mỗi bản ghi. Lọc cổ điển được thực hiện bằng cách kiểm tra các giá trị trong các cột (trường) và từ đó xác định hàng (bản ghi) nào sẽ được hiển thị dựa trên những gì được tìm thấy ở đó.
Cách dễ nhất để lọc dữ liệu của bạn theo cách bạn muốn là chuyển đổi dữ liệu trước khi lọc. Sao chép tất cả dữ liệu của bạn (chọn nó và nhấn Ctrl + C), sau đó trên một trang tính khác, hãy sử dụng tùy chọn Transpose của Paste Special. Bạn có thể hiển thị hộp thoại này bằng cách chọn Edit | Paste Special.
Sau khi dữ liệu của bạn được hoán vị (các hàng trở thành cột và cột trở thành hàng), bạn có thể sử dụng các công cụ lọc tích hợp sẵn của Excel như bình thường.
Nếu bạn không muốn chuyển đổi dữ liệu của mình, bạn sẽ cần sử dụng các giải pháp ít tự động hơn. Ví dụ: bạn có thể tự tìm ra những cột nào bạn muốn ẩn (“lọc ra”) và sau đó thực sự ẩn những cột đó. Khi bạn hoàn tất việc ẩn tất cả các cột bạn muốn ẩn, bạn có thể xác định dạng xem tùy chỉnh (như được mô tả trong các Mẹo Excel khác ) để lưu các trang tính xuất hiện.
Cuối cùng, bạn luôn có thể tạo PivotTable từ dữ liệu của mình. Sau đó, trong PivotTable, bạn có thể sử dụng các công cụ lọc để xác định dữ liệu nào được hiển thị trong bảng. Các công cụ ở đó sẽ hoạt động trên cả trường hàng và trường cột.
Lọc bình luận
Bạn có một trang tính có các nhận xét được đưa vào nhiều nơi khác nhau. Bạn tự hỏi liệu có thể lọc các hàng trong bảng dữ liệu để chỉ những hàng bao gồm các chú thích trong một cột cụ thể mới được hiển thị hay không.
Khả năng lọc của Excel không cung cấp một cách để bạn có thể tự động kiểm tra sự hiện diện của các nhận xét, nhưng có một số cách bạn có thể tiếp cận một giải pháp. Một giải pháp khả thi là làm theo các bước chung sau:
- Tạo một bản sao của cột chứa các nhận xét cần lọc.
- Chọn cột trùng lặp.
- Nhấn F5 để hiển thị tab Đi tới của hộp thoại Find và Replace.
- Nhấp vào Special. Excel sẽ hiển thị hộp thoại Go To Special.
- Nhấp vào nút radio Nhận xét và sau đó nhấn Enter. Chỉ những ô có chứa bình luận mới được chọn.
- Nhập bất kỳ số, ký tự hoặc cụm từ nào chưa có trong cột.
- Nhấn Ctrl + Enter. Tất cả các ô đã chọn (những ô có nhận xét) bây giờ sẽ chứa những gì bạn đã nhập ở bước 6.
- Sử dụng AutoFilter để chỉ hiển thị những hàng chứa bất cứ thứ gì bạn đã nhập ở bước 6.
Nếu muốn, bạn có thể tạo một hàm do người dùng xác định sẽ cho bạn biết liệu một ô cụ thể có nhận xét được liên kết với nó hay không. Sau đây là một cách đơn giản để xác định như vậy:
Ẩn đồ họa khi lọc
Bạn có một trang tính có đồ họa trên đầu các ô giải thích những gì có trong các ô. Đồ họa sắp xếp với các ô rất tốt, nhưng khi bạn áp dụng bộ lọc cho các ô, đồ họa sẽ tập hợp lại ở đầu các ô có thể nhìn thấy được. Bạn tự hỏi liệu có cách nào để ẩn đồ họa khi lọc dữ liệu trong ô không.
Câu trả lời liên quan đến cách bạn thiết lập các thuộc tính cho đồ họa. Bạn cần đảm bảo rằng đồ họa được đặt để thay đổi kích thước khi chiều cao hàng thay đổi. Đây là những gì bạn làm:
1. Chọn đồ họa có thuộc tính bạn muốn sửa đổi.
2. Bấm chuột phải vào đồ họa. Excel hiển thị menu Context.
3. Chọn Format Picture từ menu lối tắt. Excel sẽ hiển thị hộp thoại Format Picture.
4. Đảm bảo rằng tab Properties được chọn.
Tab Properties của hộp thoại Format Picture.
5. Đảm bảo rằng hộp kiểm Move and Size with Cells được chọn.
6. Bấm OK.
Đây là bước 5 thực hiện thủ thuật ở đây. Vì đồ họa của bạn được sắp xếp đúng cách khi bạn sắp xếp trang tính, rất có thể bạn đã chọn hộp kiểm Move nhưng Don’t Size with Cells. Đây là nguyên nhân khiến đồ họa bị dồn lại — chúng không thể thay đổi kích thước khi lọc ẩn các hàng mà chúng được liên kết.
Tính toán lại khi lọc
1. Chọn Options từ menu Tools. Excel sẽ hiển thị hộp thoại Options.
2. Đảm bảo rằng tab Calculation được hiển thị.
Tab Calculation của hộp thoại Options.
3. Chọn tùy chọn Manual.
4. Bấm OK.
Khi hoạt động ở chế độ này, Excel không tự động tính toán lại. Thay vào đó, nó sẽ đợi bạn nhấn F9 để cho biết rằng bạn đã sẵn sàng thực hiện tính toán lại. Tất nhiên, hạn chế của phương pháp này là bạn cần phải nhớ tính toán lại trang tính của mình sau khi bộ lọc cuối cùng của bạn được áp dụng.
UniTrain lược dịch
Xem thêm
Combo 3 Khóa Học Kỹ Năng Excel Nâng Cao
Excel Các loại biểu đồ thông dụng trong Excel