Cách lọc các bản ghi duy nhất trong Excel
Trong bài viết này, chúng ta sẽ tìm hiểu Cách Lọc Bản ghi Duy nhất trong Excel.
Trước đó trong Excel 2016 và các phiên bản cũ hơn, chúng tôi đã sử dụng các công thức phức tạp để `liên kết: / count-how-to-count-unique-values-in-excel-with-criteria-2 [trích xuất các giá trị duy nhất từ một dải ô] ‘. Trong phiên bản Excel 365 trực tuyến, MS cung cấp một hàm mảng động mới UNIQUE chỉ trả về các khối duy nhất từ một danh sách nhất định.
Cú pháp của hàm DUY NHẤT
|
=UNIQUE(array,[by_col],[exactly_once]) |
Mảng: Mảng mà bạn muốn trích xuất các giá trị duy nhất:
[by_col]: Đặt giá trị TRUE (1) nếu mảng nằm ngang. Theo mặc định, nó là FALSE cho dữ liệu dọc.
[precision_once]: đặt giá trị TRUE (1) nếu bạn muốn trích xuất các giá trị chỉ xuất hiện một lần trong mảng. Theo mặc định, nó là FALSE (0) để trích xuất tất cả các giá trị duy nhất.
Hãy để chúng tôi có một số ví dụ để hiểu cách hoạt động của hàm UNIQUE này.
Ví dụ về hàm UNIQUE Ví dụ 1: Trích xuất các số duy nhất từ danh sách bằng cách sử dụng hàm UNIQUE của Excel 365.
Ở đây tôi có dữ liệu mẫu. Trong phạm vi A2: A11, tôi có một số số chứa các giá trị trùng lặp. Tôi muốn nhận các giá trị duy nhất từ phạm vi này. Đơn giản là tôi sẽ sử dụng hàm DUY NHẤT của Excel 365.
|
=UNIQUE(A2:A11) |
Công thức này chỉ trả về tất cả các giá trị duy nhất từ phạm vi.
Ngay sau khi bạn nhấn nút enter, mảng kết quả có giá trị duy nhất sẽ tràn xuống các ô. Đây là một mảng động chỉ có sẵn cho Excel 365 trực tuyến.
Ví dụ 2: Trích xuất Giá trị chỉ xuất hiện một lần trong phạm vi.
Trong ví dụ trên, chúng tôi đã nhận các giá trị duy nhất từ phạm vi. Nếu một giá trị xuất hiện một lần, hai lần hoặc nhiều hơn, chúng tôi chỉ có một lập trường của nó. Nhưng nếu chúng ta muốn trích xuất các giá trị chỉ xuất hiện một lần trong phạm vi (các giá trị duy nhất trong phạm vi) thì công thức sẽ là:
|
=UNIQUE(A2:A11,1) |
Ở đây chúng tôi đặt biến chính xác_once thành TRUE. Điều này trả về giá trị duy nhất trong phạm vi / mảng.
Ví dụ 3: Tìm Giá trị Duy nhất trong Dữ liệu Ngang
Trong hai ví dụ trên, mảng là hàng dọc và có các giá trị số. Điều gì sẽ xảy ra nếu chúng ta có các giá trị không phải số và dữ liệu ngang? Vâng, không phải lo lắng. Hàm UNIQUE trong excel hoạt động trên bất kỳ loại giá trị nào, có thể là số, văn bản, boolean, v.v. Và chúng ta cũng có thể sử dụng nó trên thiết lập dữ liệu ngang.
Ở đây tôi có một số tên trong các ô của các cột liền kề. Tôi muốn lấy tất cả các tên mà không lặp lại trong các ô bên dưới.
Công thức sẽ là:
|
=UNIQUE(C2:K2,,1) |
Nó trả về các tên sau khi loại bỏ tất cả các bản sao khỏi mảng trong các cột.
Đối với Excel 2010-2016
Để trích xuất danh sách các giá trị duy nhất từ một danh sách, chúng tôi sẽ sử dụng INDEX, MATCH và COUNTIF. Tôi cũng sẽ sử dụng IFERROR, chỉ để có kết quả rõ ràng, tùy chọn của nó.
Và vâng, nó sẽ là một công thức mảng… Vì vậy, hãy làm cho nó được thực hiện… Công thức chung Trích xuất các giá trị duy nhất trong Excel
|
\{=INDEX(ref_list,MATCH(0,COUNTIF*(expanding_ouput_range,ref_list),0))} |
Ref_list: Danh sách mà bạn muốn trích xuất các giá trị duy nhất Expanding_ouput_range: Bây giờ điều này rất quan trọng. Đây là phạm vi mà bạn muốn xem danh sách đã trích xuất của mình. Phạm vi này phải có một tiêu đề riêng biệt không phải là một phần trong danh sách và công thức của bạn sẽ nằm dưới tiêu đề (nếu tiêu đề ở E1 thì Công thức sẽ ở E2). Bây giờ mở rộng có nghĩa là, khi bạn kéo công thức của mình xuống, nó sẽ mở rộng trên phạm vi đầu ra. Để làm như vậy, bạn cần cung cấp tham chiếu của tiêu đề là $ E $ 1: E1 (Tiêu đề của tôi nằm trong E1). Khi tôi kéo nó xuống, nó sẽ mở rộng. Trong E2, nó sẽ là $ E $ 1: E1. Trong E3, nó sẽ là $ E $ 1: E2. Trong E2, nó sẽ là $ E $ 1: E3, v.v. * Bây giờ chúng ta hãy xem một ví dụ. Nó sẽ làm cho nó rõ ràng.
Trích xuất Giá trị Duy nhất Ví dụ Excel Vì vậy, ở đây tôi có danh sách khách hàng này trong Cột A trong phạm vi A2: A16. Bây giờ trong cột E, tôi muốn chỉ nhận các giá trị duy nhất từ khách hàng. Bây giờ, phạm vi A2: A16 này cũng có thể tăng lên, vì vậy tôi muốn công thức của mình tìm nạp bất kỳ tên khách hàng mới nào từ danh sách, bất cứ khi nào danh sách tăng lên.
Được rồi, Bây giờ để tìm nạp các giá trị duy nhất từ Cột A, hãy viết công thức này trong Ô E2 và nhấn CTRL + SHIFT + ENTER để biến nó thành công thức mảng. *
|
\{=INDEX(A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))} |
A $ 2: A16: Tôi hy vọng danh sách đó sẽ mở rộng và có thể có các giá trị duy nhất mới mà tôi muốn trích xuất. Đó là lý do tại sao tôi đã để nó mở từ dưới lên bằng cách không tham chiếu tuyệt đối về A16. Nó sẽ cho phép nó mở rộng bất cứ khi nào bạn sao chép công thức bên dưới.
Vì vậy, chúng tôi biết `liên kết: / tra cứu-công thức-truy xuất-phù hợp-giá trị-từ-không-liền kề-danh sách [cách hàm INDEX và MATCH hoạt động] ‘. Phần chính ở đây là:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Công thức này sẽ trả về một mảng 1 và 0. Bất cứ khi nào một giá trị trong phạm vi $ E $ 1: E1 được tìm thấy trong danh sách tiêu chí $ A $ 2: A16, giá trị sẽ chuyển đổi thành 1 tại vị trí của nó trong phạm vi $ A $ 2: A16.
Bây giờ sử dụng hàm MATCH, chúng ta đang tìm kiếm các giá trị 0. Đối sánh sẽ trả về vị trí của 0 đầu tiên được tìm thấy trong mảng được trả về bởi hàm COUNTIF. Than INDEX sẽ xem xét A $ 2: A16 để trả về giá trị được tìm thấy tại chỉ mục do hàm MATCH trả về.
Nó có thể hơi khó để nắm bắt nhưng nó hoạt động. Số 0 ở cuối danh sách chỉ ra rằng không còn giá trị duy nhất nào nữa. Nếu cuối cùng bạn không thấy số 0 đó, bạn nên sao chép công thức vào các ô bên dưới.
Bây giờ để tránh #NA trong bạn có thể sử dụng hàm IFERROR của excel.
|
\{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF(E$1:$E1,$A$2:A16),0)),””)} |
Lưu ý: Không đặt các dấu ngoặc nhọn này theo cách thủ công, hãy sử dụng Ctrl + Shift + Enter để lấy kết quả.
Hy vọng bài viết này về Cách lọc các bản ghi duy nhất trong Excel trong Excel là giải thích. Tìm thêm các bài viết về trích xuất giá trị và công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].