Cách lọc dữ liệu trong Excel

Gần đây tôi đã viết một bài viết về cách sử dụng các hàm tóm tắt trong Excel để dễ dàng tóm tắt lượng lớn dữ liệu, nhưng bài viết đó đã tính đến tất cả dữ liệu trên bảng tính. Điều gì sẽ xảy ra nếu bạn chỉ muốn xem một tập hợp con của dữ liệu và tóm tắt tập hợp con của dữ liệu?

Trong Excel, bạn có thể tạo các bộ lọc trên các cột sẽ ẩn các hàng không khớp với bộ lọc của bạn. Ngoài ra, bạn cũng có thể sử dụng các hàm đặc biệt trong Excel để tóm tắt dữ liệu chỉ sử dụng dữ liệu được lọc.

Trong bài viết này, tôi sẽ hướng dẫn bạn các bước để tạo bộ lọc trong Excel và cũng sử dụng các hàm dựng sẵn để tóm tắt dữ liệu được lọc đó.

Tạo các bộ lọc đơn giản trong Excel

Trong Excel, bạn có thể tạo các bộ lọc đơn giản và các bộ lọc phức tạp. Hãy bắt đầu với các bộ lọc đơn giản. Khi làm việc với các bộ lọc, bạn phải luôn có một hàng ở trên cùng được sử dụng cho nhãn. Đây không phải là một yêu cầu để có hàng này, nhưng nó giúp làm việc với các bộ lọc dễ dàng hơn một chút.

Ở trên, tôi có một số dữ liệu giả mạo và tôi muốn tạo bộ lọc trên Thành phố cột. Trong Excel, điều này thực sự dễ làm. Đi trước và nhấp vào Dữ liệu tab trong ruy-băng và sau đó bấm vào Bộ lọc nút. Bạn không phải chọn dữ liệu trên trang tính hoặc nhấp vào hàng đầu tiên.

Khi bạn nhấp vào Bộ lọc, mỗi cột trong hàng đầu tiên sẽ tự động có một nút thả xuống nhỏ được thêm vào ở bên phải.

Bây giờ hãy tiếp tục và nhấp vào mũi tên thả xuống trong cột Thành phố. Bạn sẽ thấy một vài lựa chọn khác nhau, mà tôi sẽ giải thích bên dưới.

Ở trên cùng, bạn có thể nhanh chóng sắp xếp tất cả các hàng theo các giá trị trong cột Thành phố. Lưu ý rằng khi bạn sắp xếp dữ liệu, nó sẽ di chuyển toàn bộ hàng, không chỉ các giá trị trong cột Thành phố. Điều này sẽ đảm bảo rằng dữ liệu của bạn vẫn còn nguyên vẹn như trước đây.

Ngoài ra, bạn nên thêm một cột ở phía trước được gọi là ID và đánh số từ một đến nhiều hàng bạn có trong bảng tính của mình. Bằng cách này, bạn luôn có thể sắp xếp theo cột ID và lấy lại dữ liệu của mình theo đúng thứ tự ban đầu, nếu điều đó quan trọng với bạn.

Như bạn có thể thấy, tất cả dữ liệu trong bảng tính hiện được sắp xếp dựa trên các giá trị trong cột Thành phố. Cho đến nay, không có hàng nào được ẩn. Bây giờ chúng ta hãy xem các hộp kiểm ở dưới cùng của hộp thoại bộ lọc. Trong ví dụ của tôi, tôi chỉ có ba giá trị duy nhất trong cột Thành phố và ba giá trị đó hiển thị trong danh sách.

Tôi đã đi trước và bỏ chọn hai thành phố và để lại một kiểm tra. Bây giờ tôi chỉ có 8 hàng dữ liệu hiển thị và phần còn lại bị ẩn. Bạn có thể dễ dàng biết bạn đang xem dữ liệu đã lọc nếu bạn kiểm tra số hàng ở phía xa bên trái. Tùy thuộc vào số lượng hàng bị ẩn, bạn sẽ thấy thêm một vài dòng ngang và màu của các số sẽ là màu xanh.

Bây giờ hãy nói rằng tôi muốn lọc trên cột thứ hai để giảm thêm số lượng kết quả. Trong cột C, tôi có tổng số thành viên trong mỗi gia đình và tôi chỉ muốn xem kết quả cho các gia đình có nhiều hơn hai thành viên.

Hãy tiếp tục và nhấp vào mũi tên thả xuống trong Cột C và bạn sẽ thấy các hộp kiểm tương tự cho mỗi giá trị duy nhất trong cột. Tuy nhiên, trong trường hợp này, chúng tôi muốn nhấp vào Bộ lọc số và sau đó bấm vào Lớn hơn. Như bạn có thể thấy, có một loạt các tùy chọn khác nữa.

Một hộp thoại mới sẽ bật lên và ở đây bạn có thể nhập giá trị cho bộ lọc. Bạn cũng có thể thêm nhiều tiêu chí bằng hàm AND hoặc OR. Bạn có thể nói bạn muốn các hàng có giá trị lớn hơn 2 và không bằng 5, chẳng hạn.

Bây giờ tôi chỉ còn 5 hàng dữ liệu: các gia đình chỉ từ New Orleans và có từ 3 thành viên trở lên. Vừa đủ dễ? Lưu ý rằng bạn có thể dễ dàng xóa bộ lọc trên một cột bằng cách nhấp vào menu thả xuống và sau đó nhấp vào Xóa bộ lọc từ tên cột liên kết.

Vì vậy, đó là về nó cho các bộ lọc đơn giản trong Excel. Chúng rất dễ sử dụng và kết quả khá đơn giản. Bây giờ hãy xem các bộ lọc phức tạp bằng cách sử dụng Nâng cao hộp thoại bộ lọc.

Tạo bộ lọc nâng cao trong Excel

Nếu bạn muốn tạo các bộ lọc nâng cao hơn, bạn phải sử dụng Nâng cao hộp thoại lọc. Ví dụ: giả sử tôi muốn thấy tất cả các gia đình sống ở New Orleans có hơn 2 thành viên trong gia đình của họ HOẶC LÀ tất cả các gia đình ở Clarksville với hơn 3 thành viên trong gia đình của họ VÀ chỉ những người có một .EDU địa chỉ email kết thúc. Bây giờ bạn không thể làm điều đó với một bộ lọc đơn giản.

Để làm điều này, chúng ta cần thiết lập bảng Excel một chút khác nhau. Hãy tiếp tục và chèn một vài hàng phía trên bộ dữ liệu của bạn và sao chép chính xác các nhãn tiêu đề vào hàng đầu tiên như được hiển thị bên dưới.

Bây giờ đây là cách các bộ lọc nâng cao hoạt động. Trước tiên, bạn phải nhập tiêu chí của bạn vào các cột ở trên cùng và sau đó nhấp vào Nâng cao nút bên dưới Sắp xếp & Lọc trên Dữ liệu chuyển hướng.

Vì vậy, chính xác những gì chúng ta có thể gõ vào các tế bào? OK, vậy hãy bắt đầu với ví dụ của chúng tôi. Chúng tôi chỉ muốn xem dữ liệu từ New Orleans hoặc Clarksville, vì vậy hãy nhập dữ liệu đó vào các ô E2 và E3.

Khi bạn nhập giá trị trên các hàng khác nhau, nó có nghĩa là HOẶC. Bây giờ chúng tôi muốn các gia đình New Orleans có nhiều hơn hai thành viên và gia đình Clarksville có hơn 3 thành viên. Để làm điều này, gõ vào > 2 trong C2 và > 3 trong C3.

Vì> 2 và New Orleans nằm trên cùng một hàng, nên nó sẽ là toán tử AND. Điều này cũng đúng với hàng 3 ở trên. Cuối cùng, chúng tôi chỉ muốn các gia đình có địa chỉ email kết thúc .EDU. Để làm điều này, chỉ cần gõ vào * .edu vào cả D2 và D3. Biểu tượng * có nghĩa là bất kỳ số lượng ký tự.

Khi bạn làm điều đó, nhấp vào bất cứ nơi nào trong tập dữ liệu của bạn và sau đó nhấp vào Nâng cao nút. Các Danh sách RangTrường điện tử sẽ tự động tìm ra tập dữ liệu của bạn kể từ khi bạn nhấp vào nó trước khi nhấp vào nút Nâng cao. Bây giờ bấm vào nút nhỏ ở bên phải của Phạm vi tiêu chí nút.

Chọn mọi thứ từ A1 đến E3 và sau đó nhấp vào cùng một nút để quay lại hộp thoại Bộ lọc nâng cao. Nhấp vào OK và dữ liệu của bạn sẽ được lọc!

Như bạn có thể thấy, bây giờ tôi chỉ có 3 kết quả phù hợp với tất cả các tiêu chí đó. Lưu ý rằng các nhãn cho phạm vi tiêu chí phải khớp chính xác với các nhãn cho tập dữ liệu để làm việc này.

Rõ ràng bạn có thể tạo ra nhiều truy vấn phức tạp hơn bằng phương pháp này, vì vậy hãy chơi xung quanh nó để có kết quả mong muốn. Cuối cùng, hãy nói về việc áp dụng các hàm tính tổng cho dữ liệu được lọc.

Tóm tắt dữ liệu đã lọc

Bây giờ hãy nói rằng tôi muốn tổng hợp số lượng thành viên gia đình trên dữ liệu được lọc của mình, làm thế nào tôi có thể làm điều đó? Vâng, hãy xóa bộ lọc của chúng tôi bằng cách nhấp vào Thông thoáng nút trong ruy băng. Đừng lo lắng, rất dễ dàng để áp dụng lại bộ lọc nâng cao bằng cách nhấp vào nút Nâng cao và nhấp lại vào OK.

Ở dưới cùng của tập dữ liệu của chúng tôi, hãy thêm một ô được gọi là Toàn bộ và sau đó thêm một hàm tổng để tổng các thành viên trong gia đình. Trong ví dụ của tôi, tôi vừa gõ = SUM (C7: C31).

Vì vậy, nếu tôi nhìn vào tất cả các gia đình, tôi có tổng cộng 78 thành viên. Bây giờ, hãy tiếp tục và áp dụng lại bộ lọc Nâng cao của chúng tôi và xem điều gì sẽ xảy ra.

Rất tiếc! Thay vì hiển thị số chính xác, 11, tôi vẫn thấy tổng số là 78! Tại sao vậy? Chà, hàm SUM không bỏ qua các hàng ẩn, vì vậy nó vẫn đang thực hiện phép tính bằng cách sử dụng tất cả các hàng. May mắn thay, có một vài chức năng bạn có thể sử dụng để bỏ qua các hàng ẩn.

Đầu tiên là ĐĂNG KÝ. Trước khi chúng tôi sử dụng bất kỳ chức năng đặc biệt nào trong số này, bạn sẽ muốn xóa bộ lọc của mình và sau đó nhập vào chức năng.

Khi bộ lọc bị xóa, hãy tiếp tục và nhập = SUBTOTAL ( và bạn sẽ thấy một hộp thả xuống xuất hiện với một loạt các tùy chọn. Sử dụng chức năng này, trước tiên bạn chọn loại hàm tổng mà bạn muốn sử dụng bằng số.

Trong ví dụ của chúng tôi, tôi muốn sử dụng TÓM TẮT, vì vậy tôi sẽ gõ số 9 hoặc chỉ cần nhấp vào nó từ danh sách thả xuống. Sau đó nhập dấu phẩy và chọn phạm vi ô.

Khi bạn nhấn enter, bạn sẽ thấy giá trị của 78 giống như trước đây. Tuy nhiên, nếu bây giờ bạn áp dụng lại bộ lọc, chúng ta sẽ thấy 11!

Xuất sắc! Đó chính xác là những gì chúng ta muốn. Bây giờ bạn có thể điều chỉnh các bộ lọc của mình và giá trị sẽ luôn chỉ phản ánh các hàng hiện đang hiển thị.

Hàm thứ hai hoạt động khá chính xác giống như hàm SUBTOTAL là ĐỒNG Ý. Sự khác biệt duy nhất là có một tham số khác trong hàm AGGREGATE nơi bạn phải xác định rằng bạn muốn bỏ qua các hàng ẩn.

Tham số đầu tiên là hàm tổng mà bạn muốn sử dụng và như với SUBTOTAL, 9 đại diện cho hàm SUM. Tùy chọn thứ hai là nơi bạn phải nhập 5 để bỏ qua các hàng ẩn. Tham số cuối cùng là giống nhau và là phạm vi của các ô.

Bạn cũng có thể đọc bài viết của tôi về các chức năng tóm tắt để tìm hiểu cách sử dụng chức năng AGGREGATE và các chức năng khác như MODE, MEDIAN, AVERAGE, v.v..

Hy vọng, bài viết này cung cấp cho bạn một điểm khởi đầu tốt để tạo và sử dụng các bộ lọc trong Excel. Nếu bạn có bất kỳ câu hỏi, xin vui lòng gửi bình luận. Thưởng thức!