Lọc dữ liệu trong Excel. Bộ lọc nâng cao trong Excel và ví dụ về khả năng của nó

Lọc dữ liệu trong Excel sẽ cho phép bạn hiển thị thông tin mà người dùng quan tâm tại một thời điểm cụ thể. Nó đơn giản hóa rất nhiều quá trình làm việc với các bảng lớn. Bạn sẽ có thể kiểm soát cả dữ liệu sẽ được hiển thị trong cột và những gì bị loại khỏi danh sách.

Làm thế nào để thêm

Nếu bạn đã chuẩn bị thông tin thông qua tab “Chèn” – “Bảng” hoặc tab “Trang chủ” – "Định dạng dưới dạng bảng", thì tùy chọn lọc sẽ được bật theo mặc định. Nút bắt buộc được hiển thị dưới dạng mũi tên, nằm ở ô trên cùng bên phải.

Nếu bạn chỉ điền dữ liệu vào các khối rồi định dạng chúng dưới dạng bảng, bạn cần bật bộ lọc. Để thực hiện việc này, hãy chọn toàn bộ phạm vi ô, bao gồm cả hàng có tiêu đề, vì nút chúng ta cần sẽ được thêm vào hàng trên cùng. Nhưng nếu bạn chọn các khối bắt đầu từ ô có dữ liệu thì hàng đầu tiên sẽ không liên quan đến thông tin đã lọc. Sau đó chuyển đến tab Dữ liệu và nhấp vào nút Bộ lọc.

Trong ví dụ, nút có mũi tên nằm trong tiêu đề và điều này đúng - tất cả dữ liệu nằm bên dưới sẽ được lọc.

Nếu bạn quan tâm đến câu hỏi về cách tạo bảng trong Excel, hãy theo liên kết và đọc bài viết về chủ đề này.

Làm thế nào nó hoạt động

Bây giờ hãy xem cách bộ lọc hoạt động trong Excel. Ví dụ: hãy sử dụng dữ liệu sau. Chúng tôi có ba cột: "Tên sản phẩm", “Danh mục” và “Giá”, chúng tôi sẽ áp dụng nhiều bộ lọc khác nhau cho chúng.

Nhấp vào mũi tên ở ô trên cùng của cột mong muốn. Tại đây bạn sẽ thấy danh sách dữ liệu không lặp lại từ tất cả các ô nằm trong cột này. Sẽ có một dấu kiểm bên cạnh mỗi giá trị. Bỏ chọn các hộp cho các giá trị bạn muốn loại trừ khỏi danh sách.

Ví dụ: chúng ta chỉ để lại các loại trái cây trong “Danh mục”. Bỏ chọn hộp “rau” và nhấp vào “OK”.

Đối với những cột trong bảng được áp dụng bộ lọc, biểu tượng tương ứng sẽ xuất hiện ở ô trên cùng.

Làm thế nào để xóa

Nếu cần bỏ bộ lọc dữ liệu trong Excel thì nhấn vào ô tương ứng biểu tượng và chọn từ menu "Xóa bộ lọc khỏi (tên cột)".

Bạn có thể lọc thông tin trong Excel theo nhiều cách khác nhau. Có bộ lọc văn bản và số. Chúng được áp dụng tương ứng nếu các ô cột chứa văn bản hoặc số.

Sử dụng bộ lọc

Số

Áp dụng “Số…” cho cột “Giá”. Bấm vào nút ở ô trên cùng và chọn mục tương ứng từ menu. Từ danh sách thả xuống, bạn có thể chọn điều kiện bạn muốn áp dụng cho dữ liệu. Ví dụ: hãy hiển thị tất cả các sản phẩm có giá dưới "25". Chọn "ít hơn".

Nhập giá trị được yêu cầu vào trường thích hợp. Bạn có thể áp dụng nhiều điều kiện để lọc bằng logic AND và OR. Khi sử dụng “AND”, phải đáp ứng cả hai điều kiện; khi sử dụng “OR”, phải đáp ứng một trong các điều kiện đã chỉ định. Ví dụ: bạn có thể đặt: “ít hơn” – “25” – “Và” – “nhiều hơn” – “55”. Vì vậy, chúng tôi sẽ loại trừ các sản phẩm có giá nằm trong khoảng từ 25 đến 55.

Trong ví dụ tôi đã làm như thế này. Tất cả dữ liệu có “Giá” dưới 25 đều được hiển thị ở đây.

Chữ

"Bộ lọc văn bản" trong bảng ví dụ, có thể được áp dụng cho cột "Tên sản phẩm". Nhấp vào nút có mũi tên ở trên cùng và chọn mục cùng tên từ menu. Ví dụ: trong danh sách thả xuống mở ra, hãy sử dụng “bắt đầu bằng”.

Hãy để trong bảng các sản phẩm bắt đầu bằng "ka". Trong cửa sổ tiếp theo, trong trường chúng tôi viết: “ka*”. Nhấp vào "OK".

“*” trong một từ thay thế một chuỗi ký tự. Ví dụ: nếu bạn đặt điều kiện “chứa” - “s*l”, các từ sẽ vẫn giữ nguyên: bàn, ghế, chim ưng, v.v. "?" sẽ thay thế bất kỳ dấu hiệu nào. Ví dụ: “b?ton” - ổ bánh mì, nụ, bê tông. Nếu cần để lại từ gồm 5 chữ cái thì viết “?????” .

Đây là cách tôi để lại những thứ tôi cần "Tên sản phẩm".

Theo màu tế bào

Bộ lọc có thể được cấu hình theo màu văn bản hoặc màu ô.

Hãy làm nó "Lọc theo màu sắc"ô cột "Tên sản phẩm". Bấm vào nút mũi tên và chọn mục cùng tên từ menu. Hãy chọn màu đỏ.

Kết quả là chỉ còn lại các sản phẩm màu đỏ, hay đúng hơn là tất cả các ô được tô màu đã chọn.

Theo màu văn bản

Bây giờ trong ví dụ được sử dụng, chỉ có trái cây màu đỏ được hiển thị.

Nếu bạn muốn hiển thị tất cả các ô trong bảng, nhưng trước tiên là màu đỏ, sau đó là xanh lục, xanh lam, v.v., hãy sử dụng tính năng sắp xếp trong Excel. Bằng cách nhấp vào liên kết, bạn có thể đọc một bài viết về chủ đề này.

Hãy xem cách bạn có thể tìm kiếm thông tin trong MS Excel bằng bộ lọc.

Để thực hiện việc này, hãy mở Excel và vẽ một bảng nhỏ trong đó.

Chọn bất kỳ ô nào trong hàng tiêu đề, sau đó đi tới " Dữ liệu" và nhấp vào nút " Lọc»:

Trong hàng có tiêu đề của bảng của chúng ta, các “mũi tên” sẽ xuất hiện ở mỗi cột.

Xin lưu ý rằng nếu bảng của bạn không có hàng có tiêu đề thì Excel sẽ tự động chèn bộ lọc vào hàng đầu tiên có dữ liệu:

Giai đoạn chuẩn bị đã hoàn tất. Bạn có thể bắt đầu tìm kiếm thông tin.

Lọc thông tin cơ bản

Áp dụng bộ lọc cho bảng

Nhấn vào biểu tượng ở cột Người quản lý. Menu sau sẽ mở ra:

Trong menu này, bằng cách sử dụng các hộp kiểm, bạn có thể đánh dấu các phần tử mà bạn muốn lọc dữ liệu.

Nếu có nhiều giá trị trong bảng thì hãy sử dụng chuỗi tìm kiếm. Để làm điều này, hãy bắt đầu nhập một phần của từ mà bạn cần tìm. Danh sách các tùy chọn sẽ được tự động rút ngắn.

Nhược điểm của phương pháp này là bạn chỉ có thể đánh dấu một giá trị hoặc một số giá trị chứa cụm từ tìm kiếm, nhưng không thể tìm thấy các giá trị hoàn toàn khác nhau. Ví dụ: bạn sẽ không thể tìm thấy ngay những người quản lý có tên là Sergey và Alexander theo cách này, nhưng bạn có thể tìm thấy tất cả các giá trị có chứa “Serg”: Sergey, Sergeev, Sergienko, v.v.

Ví dụ: bạn chỉ cần đánh dấu 2 giá trị trong số vài chục giá trị. Việc bỏ chọn thủ công từng mục ngoại trừ những mục cần thiết khá tốn thời gian. Để tăng tốc quá trình này, hãy bỏ chọn hộp kiểm “(Chọn tất cả)”. Điều này sẽ xóa tất cả các hộp kiểm khác. Bây giờ bạn chỉ có thể đánh dấu những mục mà bạn cần.

MS Excel hỗ trợ nhiều bộ lọc, tức là lọc trên nhiều cột cùng một lúc.

Ví dụ: bạn cần tìm tất cả các đơn đặt hàng của người quản lý Ivanov ngày 18 tháng 1 năm 2014.

Để bắt đầu, hãy nhấp vào cột “Người quản lý” và chọn Ivanov.

Bây giờ hãy nhấp vào cột "Ngày giao hàng", bỏ chọn "(Chọn tất cả)" và chọn 18/01/2014 hoặc nhập 18 vào thanh tìm kiếm và nhấp vào "OK".

Bảng sẽ trông như thế này:

Tương tự như vậy, bạn có thể tiếp tục lọc dữ liệu theo các cột “Mô tả”, “Số lượng”, v.v.

Lưu ý rằng trong các cột đã được lọc, biểu tượng sẽ thay đổi thành .

Bằng cách này, bạn sẽ luôn biết dữ liệu được lọc theo cột nào.

Hủy bộ lọc

Để xóa tất cả bộ lọc cùng một lúc, hãy đi tới " Dữ liệu" và nhấp vào nút " Thông thoáng».

Nếu bạn chỉ cần xóa bộ lọc khỏi một cột, để lại các bộ lọc ở các cột khác, hãy nhấp vào biểu tượng cho cột này, ví dụ: “Ngày giao hàng” và nhấp vào mục “Xóa bộ lọc khỏi<Название столбца>»:

hoặc

Nếu bạn cần loại bỏ hoàn toàn các bộ lọc trong bảng, hãy đi tới “ Dữ liệu" và nhấp vào nút " Lọc" Nó sẽ ngừng tô sáng, các biểu tượng sẽ biến mất khỏi thanh tiêu đề và tất cả dữ liệu sẽ được hiển thị trong bảng.

Trước

Sau đó

Cài đặt bộ lọc bổ sung

Tùy thuộc vào loại nội dung của các cột, bộ lọc có các tùy chọn bổ sung.

Bộ lọc văn bản

Nhấp vào biểu tượng cột “Trình quản lý”, di chuột qua “Bộ lọc văn bản”, đợi menu xuất hiện và chọn bất kỳ tiêu chí lựa chọn nào hoặc mục “Bộ lọc tùy chỉnh…”. Cửa sổ sau sẽ xuất hiện:

• 1. Điều khoản "bằng" hoặc "không công bằng" giả sử rằng biểu thức mong muốn khớp với nội dung của ô một trăm phần trăm. Tiêu chuẩn "bằng" trong bảng chỉ để lại những hàng chứa giá trị đã chọn. Theo đó, tiêu chí "không công bằng"để lại tất cả các giá trị ngoại trừ giá trị đã chọn. Để đơn giản hóa tác vụ, bạn có thể chọn giá trị mong muốn từ danh sách thả xuống:

• 2. Điều khoản "hơn" Và "ít hơn" giả sử bảng sẽ giữ lại các giá trị bắt đầu bằng một chữ cái sớm hơn hoặc muộn hơn trong bảng chữ cái. Ví dụ: nếu bạn chọn giá trị “Ivanov” cho tùy chọn “thêm”, thì chỉ những ô bắt đầu bằng chữ “Y” mới còn lại trong bảng (Kartov, Yoghurtov, v.v.). Và với tùy chọn “ít hơn” - các giá trị bắt đầu bằng chữ cái “Z” (Zakharov, Bukin).
• 3. Sự khác biệt duy nhất về điều kiện "nhiều hơn hoặc bằng" Và "ít hơn hoặc bằng" từ đoạn trước trong đó bộ lọc bao gồm giá trị đã chọn.
• 4. Nếu bạn cần tìm tất cả các giá trị bắt đầu bằng "Cây liễu", sau đó sử dụng điều kiện "bắt đầu với", và nếu bạn muốn biết có bao nhiêu giá trị trong bảng kết thúc bằng "rovich", sau đó chọn tùy chọn "kết thúc bằng".
• 5. Theo đó, điều kiện "không bắt đầu bằng" Và "không kết thúc bằng" giả sử rằng bạn không cần hiển thị các giá trị chứa cụm từ tìm kiếm trong bảng.
• 6. Khi chọn điều kiện "chứa" hoặc "không chứa" Bạn có thể chỉ định bất kỳ cụm từ hoặc tổ hợp chữ cái nào mà bạn muốn bao gồm hoặc loại trừ khỏi bộ lọc. Sự khác biệt giữa điểm này với điểm 1, 4 và 5 là cụm từ tìm kiếm có thể nằm ở bất kỳ đâu trong ô. Ví dụ: đặt “Willow” làm bộ lọc, kết quả sẽ là “Alexey Ivanov”, “Sergey Ivarovsky”, “đường cong”, v.v.

Bộ lọc số

Hầu hết các điều kiện đều giống như đối với bộ lọc văn bản. Chúng ta hãy chỉ xem xét những cái mới.

• 1. Điều kiện "giữa". Khi bạn chọn điều kiện này, các tiêu chí cần thiết sẽ ngay lập tức được đặt trong cửa sổ xuất hiện, giúp công việc của bạn dễ dàng hơn:

Tất cả những gì bạn phải làm là nhập các giá trị từ bàn phím hoặc chọn chúng từ danh sách.

Lọc theo ngày

Những điều kiện này không yêu cầu giải thích đặc biệt vì ý nghĩa của chúng rất dễ hiểu từ tên gọi. Điều duy nhất đáng chú ý là trong cửa sổ tiêu chuẩn để chọn điều kiện lọc, nút Lịch xuất hiện để giúp bạn nhập ngày dễ dàng hơn.

Và thêm một chút về bộ lọc

Có một cách khác để lọc dữ liệu. Hãy biến đổi bảng của chúng tôi một chút:

Như bạn có thể thấy, chúng tôi đã tô màu nó.

Ví dụ: bây giờ chúng ta cần tìm tất cả các dòng có Krasotkin. Nhấp chuột phải vào ô có người này và chọn “Bộ lọc” từ menu xuất hiện. Menu mới có một số tùy chọn mới. Trong ví dụ này chúng ta cần mục "Lọc theo giá trị...".

Nếu bạn chọn điều kiện "Lọc theo màu sắc...", khi đó bảng sẽ chứa các hàng có ô cùng màu với ô hiện hoạt (tô màu vàng).

Nếu bạn bấm vào “Lọc theo màu phông chữ…”, thì chỉ những ô có phông chữ màu đỏ hoặc đen sẽ vẫn còn trong bảng của chúng ta, tùy thuộc vào ô nào hiện đang hoạt động.

Mục bộ lọc cuối cùng chỉ áp dụng được nếu bảng sử dụng định dạng có điều kiện bằng biểu tượng.

Quản trị trang web trang mạng bày tỏ lòng biết ơn tới tác giả Alexander Tsarevđể chuẩn bị nguyên liệu.

Lọc dữ liệu Excel bao gồm hai bộ lọc: AutoFilter và Advanced Filter. Giả sử bạn có một tập dữ liệu lớn, nhưng từ toàn bộ mảng, bạn cần xem xét hoặc chọn dữ liệu liên quan đến một ngày cụ thể, một người cụ thể, v.v. Có các bộ lọc cho việc này. Đối với những người lần đầu tiên sử dụng công cụ này, bộ lọc sẽ không xóa mà ẩn các bản ghi không đáp ứng các điều kiện lọc mà bạn đặt cho chúng.

Đầu tiên là bộ lọc tự động, được thiết kế cho các thao tác đơn giản nhất - đánh dấu các bản ghi có giá trị cụ thể (ví dụ: chỉ chọn các bản ghi liên quan đến LeBron James), dữ liệu nằm trong một phạm vi nhất định (hoặc trên mức trung bình hoặc top 10) hoặc các ô /phông chữ có màu nhất định (Nhân tiện, nó rất tiện lợi). Theo đó, nó rất dễ sử dụng. Bạn chỉ cần chọn dữ liệu muốn xem được lọc. Sau đó là lệnh “Dữ liệu” / “Bộ lọc”. Một hộp danh sách sẽ xuất hiện trên mỗi ô trên cùng của bảng trên cùng, từng lệnh đã dễ hiểu, rất đơn giản để thành thạo và tôi hy vọng không cần phải giải thích thêm, chỉ là các sắc thái của việc sử dụng bộ lọc tự động:

1) Chỉ hoạt động với phạm vi không bị phá vỡ. Không thể lọc hai danh sách khác nhau trên một trang tính nữa.

2) Dòng trên cùng của bảng được tự động gán làm tiêu đề và không liên quan đến việc lọc.

3) Bạn có thể áp dụng bất kỳ bộ lọc nào trong các cột khác nhau, nhưng hãy nhớ rằng tùy thuộc vào thứ tự áp dụng bộ lọc, một số điều kiện có thể không được áp dụng, bởi vì các bộ lọc trước đó đã ẩn các mục được yêu cầu. Không có vấn đề gì ở đây, dù sao thì những mục này cũng đã bị ẩn, nhưng nếu bạn muốn sử dụng một số bộ bộ lọc, thì tốt hơn là nên bắt đầu với những điều kiện có ít ứng dụng nhất.

Ứng dụng thực tế trong công việc: ví dụ bạn làm việc qua danh sách này để tìm lỗi hoặc kiểm tra dữ liệu. Sau khi áp dụng bộ lọc tự động, bạn có thể lần lượt xem toàn bộ bảng, đánh dấu tuần tự dữ liệu đã được xem. Các nút “Xóa” và “Áp dụng lại” xác định hình thức của bảng sau khi áp dụng các điều kiện. Sau đó, sau khi làm việc xong với bảng, bạn có thể đưa phông chữ trở lại dạng ban đầu mà không cần thay đổi dữ liệu. Nhân tiện, một số người bối rối vì tất cả các bản ghi trong bảng đều biến mất sau khi áp dụng bất kỳ điều kiện nào. Chà, hãy xem xét kỹ hơn, bạn đã đặt ra các điều kiện theo đó không có bản ghi nào thỏa mãn các điều kiện này. Việc bảng được lọc là khi số hàng của bảng được tô sáng màu xanh lam.

Bây giờ, hãy chuyển sang bộ lọc nâng cao. Nó khác với bộ lọc tự động ở chỗ nó có nhiều cài đặt chi tiết hơn nhưng cũng có nhiều lựa chọn hơn khi lọc dữ liệu. Đặc biệt:

1) Đặt càng nhiều điều kiện càng cần thiết.

2) Cho phép bạn chọn các ô có dữ liệu duy nhất (không lặp lại). Điều này thường cần thiết khi làm việc với dữ liệu và tùy chọn này sẽ giải quyết vấn đề một cách hoàn hảo.

3) Cho phép bạn sao chép kết quả lọc sang một vị trí riêng mà không cần chạm vào mảng chính.

Vì vậy, điểm khác biệt chính khi làm việc với bộ lọc này là trước tiên chúng ta cần chuẩn bị một bảng điều kiện. Thật dễ dàng để làm. Các tiêu đề của bảng chính được sao chép và dán vào một nơi thuận tiện cho chúng ta (tôi đề nghị phía trên bảng chính). Bảng này phải có nhiều hàng đến mức sau khi xác định điều kiện, bạn sẽ không thể vào được bảng chính.

Ví dụ về điều kiện:

1) ‘L*’ – các ô bắt đầu bằng L

2) ‘>5’ - dữ liệu lớn hơn 5

Nếu bạn xóa các hàng khỏi một bảng đã lọc, chúng sẽ bị xóa mà không mang theo các hàng lân cận. Những thứ kia. nếu bảng được lọc và hiển thị các hàng 26-29 và 31-25, việc chọn tất cả các hàng và xóa chúng sẽ không xóa được hàng 30. Điều này rất tiện lợi. Cá nhân tôi thường sử dụng điều này khi viết macro. Điều này mang lại lợi ích gì - chúng ta thường nhận được các bảng cần được đưa vào dạng hoạt động, tức là. loại bỏ, ví dụ, các dòng trống. Những gì chúng tôi làm là áp dụng bộ lọc cho bảng, chỉ hiển thị những hàng mà chúng tôi không cần, sau đó xóa toàn bộ bảng, bao gồm cả tiêu đề. Các hàng và tiêu đề không cần thiết sẽ bị xóa, trong khi bảng không có khoảng trắng và tạo thành một phạm vi duy nhất. Và một dòng tiêu đề có thể được thêm vào bằng các thao tác sao chép đơn giản từ khu vực đã chuẩn bị trước đó. Tại sao điều này lại quan trọng khi viết macro? Không biết dữ liệu không mong muốn bắt đầu từ hàng nào và không rõ bắt đầu xóa từ hàng nào; xóa toàn bộ bảng giúp giải quyết nhanh chóng vấn đề này.

Lọc trong Excel - Đây là việc lựa chọn dữ liệu theo những đặc điểm nhất định.Lọc trong Exceltrong những điều kiện nhất định được thực hiện bằng cách sử dụng chức năng " Lọc trong Excel ". Nếu có ảnh trong các ô của bảng thì xem bài viết "Chèn ảnh vào ô trong Excel" về cách sửa ảnh trong ô không bị xê dịch trong quá trình lọc.
Cách lọc trong Excel.
Đầu tiên, chọn cột mà bạn cần lọc dữ liệu bảng. Sau đó, trên tab “Trang chủ”, nhấp vào nút “Sắp xếp và lọc” và chọn chức năng “Bộ lọc”. Hoặc chuyển đến tab “Dữ liệu” và nhấp vào nút “Bộ lọc”.
Nút bộ lọc đã xuất hiện ở ô trên cùng của cột đã chọn. Nhấp vào nút này và chọn “Bộ lọc số”, sau đó chọn “ít hơn”. Trong cửa sổ xuất hiện, đối diện với ô “nhỏ hơn”, viết số nhỏ hơn mà chúng ta cần chọn dữ liệu, ví dụ: 7. Nhấp vào “OK”.

Bạn có thể lọc dữ liệu theo màu ô. Các ô có thể được tô màu theo cách thủ công hoặc định dạng có điều kiện. Cách tô màu các ô có định dạng có điều kiện theo số, theo từ, theo ngày, v.v., "Định dạng có điều kiện trong Excel".
Bấm vào màu hồng. Nhấp vào "OK". Hóa ra như thế này.