Sử dụng bộ lọc nâng cao trong Excel

Bộ lọc Nâng cao của Excel là một trong những tính năng bị đánh giá thấp và ít được sử dụng nhất mà tôi từng gặp

Nếu bạn làm việc với Excel, tôi chắc rằng bạn đã từng sử dụng (hoặc ít nhất là đã nghe nói về bộ lọc excel thông thường). Nó nhanh chóng lọc một tập dữ liệu dựa trên lựa chọn, văn bản được chỉ định, số hoặc các tiêu chí khác như vậy

Trong hướng dẫn này, tôi sẽ chỉ cho bạn một số nội dung thú vị mà bạn có thể thực hiện bằng cách sử dụng bộ lọc nâng cao của Excel

Nhưng trước tiên… Bộ lọc Nâng cao của Excel là gì?

Bộ lọc nâng cao của Excel – như tên gợi ý – là phiên bản nâng cao của bộ lọc thông thường. Bạn có thể sử dụng điều này khi cần sử dụng các tiêu chí phức tạp hơn để lọc tập dữ liệu của mình

Dưới đây là một số khác biệt giữa bộ lọc thông thường và bộ lọc Nâng cao

• Mặc dù bộ lọc dữ liệu thông thường sẽ lọc tập dữ liệu hiện có, nhưng bạn cũng có thể sử dụng bộ lọc nâng cao của Excel để trích xuất tập dữ liệu sang một số vị trí khác
• Bộ lọc Nâng cao của Excel cho phép bạn sử dụng các tiêu chí phức tạp. Ví dụ: nếu bạn có dữ liệu bán hàng, bạn có thể lọc dữ liệu theo tiêu chí trong đó đại diện bán hàng là Bob và khu vực là Bắc hoặc Nam (chúng ta sẽ xem cách thực hiện điều này trong các ví dụ). Hỗ trợ văn phòng có một số lời giải thích tốt về điều này
• Bạn có thể sử dụng Bộ lọc nâng cao của Excel để trích xuất các bản ghi duy nhất từ ​​dữ liệu của mình (thêm về điều này trong giây lát)

BỘ LỌC NÂNG CAO EXCEL (Ví dụ)

Bây giờ, hãy cùng xem một số ví dụ về cách sử dụng Bộ lọc nâng cao trong Excel

Ví dụ 1 – Trích xuất một danh sách duy nhất

Bạn có thể sử dụng Bộ lọc nâng cao của Excel để nhanh chóng trích xuất các bản ghi duy nhất từ ​​một tập dữ liệu (hay nói cách khác là loại bỏ các bản ghi trùng lặp)

Trong Excel 2007 và các phiên bản mới hơn, có một tùy chọn để loại bỏ các bản sao khỏi tập dữ liệu. Nhưng điều đó làm thay đổi tập dữ liệu hiện tại của bạn. Để giữ nguyên dữ liệu gốc, bạn cần tạo một bản sao của dữ liệu rồi sử dụng tùy chọn Remove Duplicates. Bộ lọc nâng cao của Excel sẽ cho phép bạn chọn một vị trí để có một danh sách duy nhất

Hãy xem cách sử dụng bộ lọc nâng cao để có danh sách duy nhất

Giả sử bạn có một tập dữ liệu như hình bên dưới

Như bạn có thể thấy, có các bản ghi trùng lặp trong tập dữ liệu này (được đánh dấu bằng màu cam). Đây có thể là do lỗi nhập dữ liệu hoặc kết quả của việc biên dịch dữ liệu

Trong trường hợp như vậy, bạn có thể sử dụng công cụ Bộ lọc nâng cao của Excel để nhanh chóng lấy danh sách tất cả các bản ghi duy nhất ở một vị trí khác (để dữ liệu gốc của bạn vẫn còn nguyên vẹn)

Dưới đây là các bước để có được tất cả các bản ghi duy nhất

• Chọn toàn bộ tập dữ liệu (bao gồm cả tiêu đề)
• Chuyển đến tab Dữ liệu -> Sắp xếp & Lọc -> Nâng cao. (Bạn cũng có thể sử dụng phím tắt – Alt + A + Q). Thao tác này sẽ mở hộp thoại Bộ lọc nâng cao
• Trong hộp thoại Bộ lọc nâng cao, hãy sử dụng các chi tiết sau
  • Hành động. Chọn tùy chọn 'Sao chép sang vị trí khác'. Điều này sẽ cho phép bạn chỉ định vị trí nơi bạn có thể lấy danh sách các bản ghi duy nhất.
  • Phạm vi danh sách. Đảm bảo rằng nó đề cập đến tập dữ liệu mà bạn muốn tìm các bản ghi duy nhất từ ​​đó. Ngoài ra, hãy đảm bảo bao gồm các tiêu đề trong tập dữ liệu.
  • Phạm vi tiêu chí. Để trống.
  • Sao chép vào. Chỉ định địa chỉ ô mà bạn muốn lấy danh sách các bản ghi duy nhất.
  • Chỉ sao chép các bản ghi duy nhất. Chọn tùy chọn này.
• Nhấp vào OK

Điều này sẽ ngay lập tức cung cấp cho bạn một danh sách tất cả các bản ghi duy nhất

Thận trọng. Khi bạn đang sử dụng Bộ lọc nâng cao để lấy danh sách duy nhất, hãy đảm bảo rằng bạn cũng đã chọn tiêu đề. Nếu không, nó sẽ coi ô đầu tiên là tiêu đề.

Ví dụ 2 – Sử dụng Tiêu chí trong Bộ lọc Nâng cao của Excel

Lấy các bản ghi duy nhất là một trong nhiều điều bạn có thể làm với bộ lọc nâng cao của Excel

Tiện ích chính của nó nằm ở khả năng cho phép sử dụng các tiêu chí phức tạp để lọc dữ liệu

Đây là những gì tôi muốn nói về các tiêu chí phức tạp. Giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn nhanh chóng lấy tất cả các bản ghi có doanh số lớn hơn 5000 và khu vực là Hoa Kỳ

Đây là cách bạn có thể sử dụng Bộ lọc Nâng cao của Excel để lọc các bản ghi dựa trên các tiêu chí đã chỉ định

• Bước đầu tiên khi sử dụng Bộ lọc Nâng cao Excel với các tiêu chí phức tạp là chỉ định các tiêu chí. Để thực hiện việc này, hãy sao chép các tiêu đề và dán vào đâu đó trong trang tính
• Chỉ định tiêu chí mà bạn muốn lọc dữ liệu. Trong ví dụ này, vì chúng tôi muốn lấy tất cả các bản ghi cho Hoa Kỳ có doanh số bán hàng trên 5000, hãy nhập 'US' vào ô bên dưới Khu vực và >5000 vào ô bên dưới Doanh số. Điều này bây giờ sẽ được sử dụng làm đầu vào trong Bộ lọc nâng cao để lấy dữ liệu đã lọc (như được hiển thị trong các bước tiếp theo)
• Chọn toàn bộ tập dữ liệu (bao gồm cả tiêu đề)
• Chuyển đến tab Dữ liệu -> Sắp xếp & Lọc -> Nâng cao. Thao tác này sẽ mở hộp thoại Bộ lọc nâng cao
• Trong hộp thoại Bộ lọc nâng cao, hãy sử dụng các chi tiết sau
  • Hành động. Chọn tùy chọn 'Sao chép sang vị trí khác'. Điều này sẽ cho phép bạn chỉ định vị trí nơi bạn có thể lấy danh sách các bản ghi duy nhất.
  • Phạm vi danh sách. Đảm bảo rằng nó đề cập đến tập dữ liệu mà bạn muốn tìm các bản ghi duy nhất từ ​​đó. Ngoài ra, hãy đảm bảo bao gồm các tiêu đề trong tập dữ liệu.
  • Phạm vi tiêu chí.  Chỉ định tiêu chí mà chúng tôi đã xây dựng ở các bước trên. Trong ví dụ này, nó sẽ là F1. tôi3.
  • Sao chép vào. Chỉ định địa chỉ ô mà bạn muốn lấy danh sách các bản ghi duy nhất.
  • Chỉ sao chép các bản ghi duy nhất. Chọn tùy chọn này.
• Nhấp vào OK

Điều này sẽ ngay lập tức cung cấp cho bạn tất cả các bản ghi trong đó khu vực là Hoa Kỳ và doanh số bán hàng hơn 5000

Ví dụ trên là trường hợp lọc được thực hiện dựa trên hai tiêu chí (Hoa Kỳ và doanh số lớn hơn 5000)

Bộ lọc nâng cao của Excel cho phép bạn tạo nhiều kết hợp tiêu chí khác nhau

Dưới đây là một số ví dụ về cách bạn có thể xây dựng các bộ lọc này

Sử dụng tiêu chí AND

Khi bạn muốn sử dụng tiêu chí AND, bạn cần chỉ định nó bên dưới tiêu đề

Ví dụ

• Để lọc bản ghi khi khu vực là Hoa Kỳ VÀ đại diện bán hàng là Joe
• Để lọc bản ghi khi khu vực là Hoa Kỳ VÀ giá trị bán hàng lớn hơn 5000
• Khi khu vực là Hoa Kỳ VÀ doanh số bán hàng được ghi nhận sau ngày 31-03-2017

Sử dụng tiêu chí OR

Khi bạn muốn sử dụng tiêu chí OR, bạn cần chỉ định các tiêu chí trong cùng một cột

Ví dụ

• Để lọc các bản ghi khi khu vực là Hoa Kỳ HOẶC khu vực là Châu Á
• Để lọc các bản ghi khi Đại diện bán hàng là Bob HOẶC Martha

Đến đây, chắc hẳn bạn đã nhận ra rằng khi chúng ta có các tiêu chí trong cùng một hàng thì đó là tiêu chí AND và khi chúng ta có các tiêu chí đó ở các hàng khác nhau thì đó là tiêu chí OR

Ví dụ 3 – Sử dụng Ký tự WILDCARD trong Bộ lọc nâng cao trong Excel

Bộ lọc Nâng cao của Excel cũng cho phép sử dụng các ký tự đại diện trong khi xây dựng tiêu chí

Có ba ký tự đại diện trong Excel

1. * (dấu hoa thị) – Nó đại diện cho bất kỳ số ký tự nào. Ví dụ: ex* có thể có nghĩa là excel, excels, example, Expert, v.v.
2. ? . Ví dụ: Tr?mp có thể có nghĩa là Trump hoặc Kẻ lang thang
3. ~ (dấu ngã) – Nó được sử dụng để xác định ký tự đại diện (~, *, ?) trong văn bản

Bây giờ, hãy xem cách chúng ta có thể sử dụng các ký tự đại diện này để thực hiện một số bộ lọc nâng cao trong Excel

• Để lọc các bản ghi có tên đại diện bán hàng bắt đầu từ J.  

Lưu ý rằng * đại diện cho bất kỳ số ký tự nào. Vì vậy, bất kỳ đại diện nào có tên bắt đầu bằng J sẽ được lọc theo các tiêu chí này

Tương tự, bạn cũng có thể sử dụng hai ký tự đại diện khác

Ghi chú. Trong trường hợp bạn đang sử dụng Office 365, bạn nên kiểm tra chức năng LỌC. Nó có thể làm được rất nhiều thứ mà bộ lọc nâng cao có thể làm được với một công thức đơn giản

GHI CHÚ

1. Hãy nhớ rằng, các tiêu đề trong tiêu chí phải giống hệt tiêu đề trong tập dữ liệu
2. Lọc nâng cao không thể hoàn tác khi được sao chép sang các vị trí khác
   

Bạn cũng có thể thích các Hướng dẫn Excel sau

• Dynamic Excel Filter – Trích xuất dữ liệu khi bạn nhập
• Lọc các ô có định dạng phông chữ đậm
• Cách lọc các ô có chuỗi văn bản (từ) trùng lặp trong đó
• Cách lọc dữ liệu trong Pivot Table trong Excel
• Hướng dẫn MS về Bộ lọc nâng cao trong Excel
• Cách so sánh hai cột trong Excel
• Bộ lọc tự động VBA Excel
• 20 Hàm và Công thức Excel Nâng cao (dành cho Chuyên gia Excel)

20 suy nghĩ về “Bộ lọc nâng cao của Excel – Hướng dẫn đầy đủ với các ví dụ”

1. ali adl

   Chào
   câu hỏi của tôi. cách sử dụng 'thẻ đại diện' khi chúng ta có 'giữa' trong bộ lọc nâng cao
   câu hỏi. nếu chúng tôi muốn tìm một sản phẩm có số lượng mua từ 2000 đến 5000 đô la hoặc nhân viên bán hàng là David

2. BZ

   Đang tìm cách lọc ra (nghĩa là *loại trừ*) khỏi kết quả bất kỳ giá trị nào trong dữ liệu chứa các chữ cái 'cul' trong một cột cụ thể. Tôi không thấy ở đây làm thế nào để làm điều đó

3. Rama Prasad

   Tôi muốn lọc tập dữ liệu bằng bộ lọc nâng cao trên “ram”. Nhưng kết quả không chỉ bao gồm “ram” mà còn có hàng chứa “raman”. Làm cách nào để tôi chỉ nhận được kết quả cho "ram"?

4. shazia

   lời giải thích của bạn về tùy chọn bộ lọc nâng cao là tuyệt vời
   Cảm ơn bạn rất nhiều

5. Harrison Rhodes

   Xin chào, tôi có hai cột A & B. Tôi cần xem dữ liệu cột A có các số khác nhau ở đâu trong cột B. Vì vậy, nếu cột A có 123 và cột B có hai số khác nhau được liên kết với 123, tôi cần xem những số được đánh dấu theo một cách nào đó. Có ai biết làm thế nào để làm điều này?

6. mak

   bạn chỉ có thể sao chép dữ liệu đã lọc vào trang tính đang hoạt động;

7. đà la ni

   Liệt kê ra Khác nhau giữa bộ lọc và bộ lọc nâng cao

8. Gary

   Tôi đang cố gắng sử dụng các công thức trong tiêu chí bộ lọc nâng cao của mình trong đó cơ sở dữ liệu của tôi nằm trong một sổ làm việc (bên ngoài) riêng biệt từ trang trích xuất của tôi. Nó hoạt động nếu tôi không sử dụng công thức. Điều này có khả thi không và nếu có thì cú pháp là gì?

9. điều hướng

   Tôi có một dữ liệu chứa điểm của sinh viên. Tôi muốn biết tên của những sinh viên đã đạt điểm từ 50 đến 70

10. rajeet kumar

    Bộ lọc nâng cao không hoạt động trong ms office 2007. phải làm gì?

11. natharni

    cảm ơn vì lời giải thích của bạn, tôi có cách sử dụng một số công cụ trong excel

12. Rohit

    Xin chào, tôi có một câu hỏi liên quan đến excel. Tôi muốn lọc nhiều mục trong một cột, vậy tôi có thể làm điều đó mà không cần vào danh sách thả xuống không?

13. vinit

    Xin chào, bộ lọc nâng cao không hoạt động trong sổ làm việc đóng, tôi muốn áp dụng bộ lọc nâng cao trong sổ làm việc đóng, vui lòng trợ giúp

14. Hardeep

    Hướng dẫn của bạn thật tuyệt vời
    Bạn đã giải thích chi tiết và hữu ích nhất
    Vui lòng mô tả là bạn có thể, Nếu có một cột 'Ngày sinh' và tôi chỉ muốn trích xuất các bản ghi có ngày sinh của họ trong Tháng 11
    Cám ơn bạn

    • Ma-thi-ơ

      Giả sử A2. A10 chứa ngày của bạn, với tiêu đề trong A1. Tìm một cặp ô trống, cái này nằm dưới cái kia - Tôi sẽ giả sử là E2 và E3. Để trống ô E2, ô E3 nhập công thức `=MONTH(A2)=11` (cụ thể ô A2 là ô dữ liệu đầu tiên của cột ngày, tháng 11 là tháng thứ 11). Áp dụng bộ lọc nâng cao, sử dụng A1. A10 làm phạm vi danh sách và E2. E3 làm phạm vi tiêu chí

15. Sambit Kumar Mohapatra

    Hướng dẫn hay về Bộ lọc nâng cao

16. Syed Muneer

    anh bạn thật tuyệt vời. Cảm ơn bạn. Tôi chỉ tìm kiếm phím tắt cho tùy chọn tìm kiếm trong bộ lọc A+ mũi tên xuống trên E… nhưng ở đây tôi đã học được một điều rất có giá trị

    Làm cách nào để sử dụng bộ lọc nâng cao trong Excel?

    Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy nhấp vào Nâng cao . Để lọc phạm vi danh sách bằng cách sao chép các hàng phù hợp với tiêu chí của bạn sang một vùng khác của trang tính, hãy bấm vào Sao chép sang vị trí khác, bấm vào hộp Sao chép vào, rồi bấm vào góc trên bên trái của khu vực mà bạn muốn dán các hàng.

    Các tiêu chí chúng ta có thể sử dụng trong bộ lọc nâng cao trong Excel là gì?

    Công thức trong tiêu chí Bộ lọc nâng cao . ô công thức và ô tiêu đề The criteria range should include a minimum of 2 cells: formula cell and heading cell . Ô tiêu đề trong tiêu chí dựa trên công thức phải trống hoặc có tiêu đề khác với bất kỳ tiêu đề nào trong phạm vi danh sách.

    Bộ lọc nâng cao khác với bộ lọc trong Excel như thế nào?

    Mặc dù bộ lọc dữ liệu thông thường sẽ lọc tập dữ liệu hiện có, nhưng bạn cũng có thể sử dụng bộ lọc nâng cao của Excel để trích xuất tập dữ liệu sang một số vị trí khác. Bộ lọc Nâng cao của Excel cho phép bạn sử dụng các tiêu chí phức tạp. Bộ lọc Nâng cao của Excel là một trong những tính năng bị đánh giá thấp và ít được sử dụng nhất mà tôi từng gặp

    Làm cách nào để sử dụng các bản ghi duy nhất của bộ lọc nâng cao chỉ trong Excel?

    Để lọc các giá trị duy nhất, nhấp vào Dữ liệu > Sắp xếp & Lọc > Nâng cao . Để xóa các giá trị trùng lặp, hãy nhấp vào Dữ liệu > Công cụ dữ liệu > Xóa các giá trị trùng lặp. Để đánh dấu các giá trị duy nhất hoặc trùng lặp, hãy sử dụng lệnh Định dạng có Điều kiện trong nhóm Kiểu trên tab Trang chủ.