Trong Microsoft Excel, một số phương pháp có sẵn để tạo một danh sách duy nhất theo nhiều tiêu chí. Danh sách duy nhất thường được chuẩn bị để loại bỏ các bản sao khỏi bảng. Trong bài viết này, bạn sẽ học cách sử dụng các phương pháp khác nhau để tạo danh sách duy nhất dựa trên một số điều kiện.
Bạn có thể tải xuống sổ làm việc Excel mà chúng tôi đã sử dụng để chuẩn bị bài viết này.
9 phương pháp tạo danh sách duy nhất trong Excel dựa trên tiêu chí
1. Tạo danh sách các hàng duy nhất với nhiều cột tiêu chí
Trong hình sau, bảng bên trái có một số tên môn thể thao ngẫu nhiên, những người tham gia và độ tuổi của họ. Một bảng khác ở bên phải là bảng đầu ra, nơi chúng tôi sẽ trích xuất dữ liệu chỉ dựa trên các giá trị duy nhất. Ví dụ:chúng tôi chỉ muốn biết tên của những người tham gia duy nhất cùng với tuổi của họ.
Chúng tôi sẽ sử dụng DUY NHẤT ở đây hàm trả về các giá trị duy nhất từ một dải ô hoặc mảng. Công thức chung của hàm này là:
=DUY NHẤT (mảng, [by_col], [chính xác_once])
Mặc dù DUY NHẤT hàm chỉ khả dụng trong Excel 365 , chúng tôi cũng sẽ hiển thị các phương pháp thay thế với sự kết hợp của các hàm thông thường khác trong 3 phần cuối trước khi chuyển sang phần kết luận.
Vì vậy, hàm bắt buộc trong Ô F5 sẽ là:
=UNIQUE(B5:C13,FALSE,FALSE) Sau khi nhấn Enter , hàm sẽ trả về một mảng với các tên duy nhất cùng với độ tuổi tương ứng của những người tham gia.
Đọc thêm:Cách tạo danh sách dựa trên tiêu chí trong Excel (4 phương pháp)
2. Nhận danh sách các giá trị duy nhất được sắp xếp theo thứ tự bảng chữ cái
Chúng tôi cũng có thể nhận các giá trị duy nhất được sắp xếp theo thứ tự bảng chữ cái bằng cách sử dụng SORT hàm số. Bằng cách biểu thị thứ tự sắp xếp tăng dần (A đến Z) hoặc giảm dần (Z đến A) tiêu chí, chúng tôi có thể sắp xếp lại tên người tham gia dựa trên tên viết tắt của họ.
Trong đầu ra Ô E5 , công thức bắt buộc với SORT và DUY NHẤT các chức năng sẽ là:
=SORT(UNIQUE(B5:C13,FALSE,FALSE),,1) Sau khi nhấn Enter , hàm sẽ trả về mảng được sắp xếp theo thứ tự bảng chữ cái của A đến Z .
Đọc thêm: Cách tạo danh sách theo thứ tự bảng chữ cái trong Excel (3 cách)
3. Tạo danh sách các giá trị duy nhất được nối thành một ô
Giả sử, chúng tôi muốn trích xuất dữ liệu duy nhất từ hai cột và sau đó nối các giá trị với nhau để trả về các giá trị được nối trong một cột. Trong hình sau, các tên riêng và độ tuổi tương ứng sẽ được hiển thị cùng nhau trong một cột bằng cách sử dụng dấu phân cách cụ thể- Dấu phẩy (,). Và Chúng tôi sẽ sử dụng Ký hiệu và (&) tại đây để nối các giá trị với hai cột khác nhau.
Công thức bắt buộc trong đầu ra Ô F5 sẽ là:
=UNIQUE(B5:B13&", "&C5:C13) Công thức sẽ trả về một mảng các hàng duy nhất trong một cột.
Đọc thêm: Cách tạo danh sách được phân tách bằng dấu phẩy trong Excel (5 phương pháp)
4. Tạo Danh sách Giá trị Duy nhất với Tiêu chí (Công thức LỌC DUY NHẤT)
i. Xác định các giá trị duy nhất dựa trên nhiều tiêu chí VÀ trong Excel
Trong phần này, chúng tôi sẽ thêm một số tiêu chí và trích xuất dữ liệu duy nhất dựa trên các điều kiện đó. Ví dụ, chúng tôi muốn biết tên riêng của những người tham gia chỉ tham gia bơi lội và dưới 25 tuổi. Vì vậy, chúng ta phải kết hợp DUY NHẤT và BỘ LỌC các chức năng ở đây để lọc dữ liệu với các tiêu chí đã cho.
Công thức cần thiết trong đầu ra Ô F5 sẽ là:
=UNIQUE(FILTER(B5:C13,(D5:D13=G9)*(C5:C13<G10)))
Sau khi nhấn Enter , công thức sẽ trả về một mảng với các tên duy nhất và độ tuổi tương ứng dựa trên tiêu chí đã chọn.
ii. Tìm kiếm giá trị duy nhất dựa trên nhiều tiêu chí HOẶC
Giả sử, bây giờ chúng tôi muốn biết tên của những người tham gia duy nhất đã tham gia cả các môn thể thao ngoài trời - bơi lội và đi xe đạp. Vì chúng tôi chỉ chỉ định tiêu chí từ một cột, chúng tôi chỉ cần sử dụng phép cộng số để kết hợp hai điều kiện trong BỘ LỌC chức năng.
Công thức bắt buộc trong Ô F5 bây giờ sẽ là:
=UNIQUE(FILTER(B5:C13,(D5:D13=F11)+(D5:D13=F12)))
Nhấn Enter và bạn sẽ nhận được dữ liệu duy nhất được trích xuất ngay lập tức như được hiển thị trong ảnh chụp màn hình sau.
iii. Nhận danh sách các giá trị duy nhất bỏ qua các ô trống
Tập dữ liệu của chúng tôi có thể chứa một số ô hoặc hàng trống. Vì vậy, nếu chúng ta muốn bỏ qua các hàng trống đó và trích xuất dữ liệu duy nhất từ bảng, chúng ta phải sử dụng toán tử so sánh: Không bằng với (<>) trong công thức.
Công thức bắt buộc trong đầu ra Ô F5 sẽ là:
=UNIQUE(FILTER(B5:C13,D5:D13<>""))
Sau khi nhấn Enter , công thức sẽ trả về mảng sau chỉ có các hàng duy nhất không trống trong bảng đầu ra.
Bài đọc tương tự
- Cách tạo danh sách việc cần làm trong Excel (3 phương pháp dễ dàng)
- Tạo Danh sách Gửi thư trong Excel (2 Phương pháp)
5. Tìm danh sách các giá trị duy nhất trong các cột được chỉ định trong Excel
Giả sử rằng chúng ta chỉ muốn trích xuất dữ liệu duy nhất từ một số cột cụ thể. Không thể chọn các cột riêng biệt đó bằng con trỏ chuột và nhập nó làm đối số cho UNIQUE hàm số. Vì vậy, chúng ta phải kết hợp LỰA CHỌN hoạt động ở đây với UNIQUE hoạt động như CHOOSE hàm sẽ cho phép bạn chọn bất kỳ số lượng cột được chỉ định hoặc phạm vi ô dựa trên số chỉ mục từ danh sách các giá trị.
Từ bảng dữ liệu của chúng tôi, chúng tôi sẽ trích xuất tên của những người tham gia duy nhất và hiển thị kết quả từ Tên (Cột B) và Tên thể thao (Cột D) chỉ cột.
Công thức bắt buộc trong Ô F5 sẽ là:
=UNIQUE(CHOOSE({1,3},B5:B13,C5:C13,D5:D13))
Sau khi nhấn Enter , công thức sẽ trả về các tên riêng và tên của các môn thể thao tương ứng từ hai cột riêng biệt như trong hình bên dưới.
Bên trong CHOOSE hàm, số chỉ mục là 1 và 3 điều này ngụ ý rằng phạm vi ô thứ nhất và thứ 3 phải được chọn từ danh sách các giá trị. Sau đó, hàm UNIQUE chỉ xem xét các cột được chỉ định và trả về dữ liệu duy nhất từ các cột đó trong một mảng.
Đọc thêm:Cách tạo danh sách được đánh số trong Excel (8 phương pháp)
6. Sử dụng hàm IFERROR trong khi tạo danh sách duy nhất
Chúng tôi cũng có thể sử dụng IFERROR chức năng đằng sau UNIQUE chức năng hiển thị thông báo tùy chỉnh nếu tìm thấy bất kỳ lỗi nào trong giá trị trả về. Giả sử, chúng tôi muốn biết tên của những người tham gia duy nhất dưới 21 tuổi. Vì không có trường hợp nào về bất kỳ người tham gia nào dưới 21 tuổi, công thức sẽ trả về # N / A lỗi. Nhưng thay vì hiển thị lỗi này, chúng tôi sẽ sử dụng IFERROR chức năng hiển thị thông báo tùy chỉnh- “Không tìm thấy”.
Vì vậy, công thức bắt buộc trong Ô F5 sẽ là:
=IFERROR(UNIQUE(FILTER(B5:C13,C5:C13<=G10)), "Not Found")
Sau khi nhấn Enter , công thức sẽ trả về thông báo được chỉ định như được hiển thị trong ảnh chụp màn hình sau.
7. Trích xuất một danh sách duy nhất dựa trên tiêu chí (Công thức INDEX-MATCH)
Bây giờ chúng tôi sẽ loại bỏ DUY NHẤT và áp dụng kết hợp INDEX và MATCH chức năng. INDEX hàm trả về một giá trị hoặc tham chiếu của ô tại giao điểm của hàng và cột cụ thể. Và TRẬN ĐẤU hàm trả về vị trí của một mục phù hợp với giá trị được chỉ định trong một thứ tự được chỉ định. Giả sử rằng chúng tôi muốn biết tên riêng của những người tham gia chỉ tham gia môn bơi lội.
📌 Bước 1:
➤ Chọn đầu ra Ô F5 và nhập:
=INDEX(B5:B13, MATCH(0, IF($F$12=$D$5:$D$13,COUNTIF($F$4:$F4, $B$5:$B$13), ""), 0)) ➤ Nhấn Enter .
Bạn sẽ nhận được tên duy nhất đầu tiên làm giá trị trả lại.
📌 Bước 2:
➤ Sử dụng Xử lý điền để kéo ô sang bên phải.
Và bạn cũng sẽ tìm thấy tuổi của người tham gia.
📌 Bước 3:
➤ Từ Ô G5 , hãy điền vào cột cho đến khi # N / A giá trị xuất hiện.
Vì vậy, đây là cách bạn có thể trích xuất dữ liệu duy nhất dựa trên một điều kiện nhất định với INDEX-MATCH công thức.
🔎 Công thức hoạt động như thế nào?
- COUNTIF ($ F $ 4:$ F4, $ B $ 5:$ B $ 13): COUNTIF ở đây đã được sử dụng để lưu trữ và đếm tất cả các ô có sẵn trong phạm vi B5:B13 . Và hàm trả về dưới dạng:
{0; 0; 0; 0; 0; 0; 0; 0; 0}
- IF ($ F $ 12 =$ D $ 5:$ D $ 13, COUNTIF ($ F $ 4:$ F4, $ B $ 5:$ B $ 13), “”): IF hàm tìm kiếm các tiêu chí đã cho trong các ô và trả về dưới dạng:
{0; ””; 0; ””; ””; 0; ””; ””; ””}
- TRẬN ĐẤU hàm trả về số hàng của ô được tìm thấy ở bước trước.
- Cuối cùng, INDEX hàm trích xuất dữ liệu dựa trên các số hàng đó.
8. Chuẩn bị một danh sách duy nhất trong Excel dựa trên nhiều tiêu chí
Trong phần này, chúng ta sẽ tìm hiểu cách INDEX-MATCH công thức hoạt động khi nhiều điều kiện được áp dụng. Ví dụ:chúng tôi muốn biết tên riêng của những người tham gia đã tham gia bơi lội và dưới 25 tuổi.
Công thức bắt buộc trong đầu ra Ô F5 sẽ là:
=IFERROR(INDEX($B$5:$B$13,MATCH(0,COUNTIF(F4:$F$4,$B$5:$B$13)+IF(D5:D13=$G$9,1,0)+IF(C5:C13<$G$10,1,0),0)),"")
Sau khi nhấn Enter và tự động điền vào các ô mới trong cột đầu ra, chúng tôi sẽ nhận được các tên duy nhất như thể hiện trong hình bên dưới.
Trong công thức này, chúng tôi đã chỉ định tiêu chí với hai IF và COUNTIF hàm sẽ xem xét các tiêu chí đó trong khi trích xuất mảng các ô đầu ra. Như được mô tả trong phương pháp trước, INDEX-MATCH công thức sẽ trả về kết quả dựa trên mảng đó. Và IFERROR chức năng ở đây đã được sử dụng để trả về một thông báo tùy chỉnh nếu tìm thấy bất kỳ lỗi nào.
9. Tạo nhiều danh sách duy nhất dọc theo hàng và cột với tiêu chí
Trong phương pháp cuối cùng của chúng tôi, chúng tôi sẽ sử dụng bảng Excel để trích xuất nhiều danh sách dữ liệu duy nhất. Trong hình sau, bảng ở dưới cùng sẽ hiển thị tên riêng của những người tham gia dựa trên các loại thể thao.
Chúng tôi đã đặt tên cho bảng hoặc phạm vi ô là (B5:C13) với Thể thao. Các cột có tiêu đề với tên- SportsName và Name. Bạn phải lưu ý rằng các tiêu đề trong bảng không được chiếm bất kỳ khoảng trống nào.
📌 Bước 1:
➤ Công thức bắt buộc trong đầu ra đầu tiên Ô B16 sẽ là:
=IFERROR(INDEX(Sports,SMALL(IF(Sports[SportsName]=B$15,ROW(Sports)-4),ROW(1:1)),2),"")
Sau khi nhấn Enter , giá trị đầu ra đầu tiên sẽ trả về ngay lập tức.
📌 Bước 2:
➤ Sử dụng Xử lý điền để điền vào cột cho đến khi một ô trống xuất hiện.
📌 Bước 3:
➤ Bây giờ sao chép phạm vi ô từ kết quả đầu ra (B16:B20) trong cột đầu tiên.
➤ Trong Ô C16 , dán các giá trị bằng Công thức (F) tùy chọn.
Bạn sẽ nhận được cột thứ hai với tên riêng của những người tham gia chỉ tham gia đạp xe.
📌 Bước 4:
➤ Tương tự, hãy dán lại các giá trị đã sao chép dưới dạng công thức vào Ô D16 và E16 .
Và bạn sẽ nhận được các tên riêng khác của những người tham gia dựa trên các loại thể thao ngay lập tức. Nhưng ở đây, bạn phải nhớ rằng bạn không thể chỉ sử dụng Fill Handle để tự động điền các ô về phía bên phải từ các giá trị trả về đầu tiên trong Cột B . Sử dụng Tự động điền tùy chọn này sẽ dẫn đến dữ liệu bị thao túng và bạn sẽ không thể trích xuất đầu ra ban đầu.
🔎 Công thức hoạt động như thế nào?
- IF (Sports [SportsName] =B $ 15, ROW (Thể thao) -4): Phần này của hàm tìm kiếm điều kiện đã cho được chỉ định bởi tiêu đề trong Ô B15 và hàm trả về dưới dạng:
{1; FALSE; FALSE; 4; FALSE; FALSE; 7; 8; FALSE}
- NHỎ (IF (Sports [SportsName] =B $ 15, ROW (Thể thao) -4), ROW (1:1)): NHỎ hàm trích xuất số nhỏ nhất từ đầu ra trước đó và cho Ô C16 , đó là ‘1’ .
- INDEX hàm kéo ra tên dựa trên số hàng được chỉ định bởi NHỎ chức năng.
- IFERROR đã được sử dụng để hiển thị các ô trống nếu tìm thấy bất kỳ đầu ra lỗi nào.
- Trong công thức kết hợp này, số ‘4’ trong phần- “ROW (Thể thao) -4” là số hàng của tiêu đề trong bảng dữ liệu chính.
Từ kết luận
Tôi hy vọng tất cả các phương pháp được minh họa ở trên giờ đây sẽ giúp bạn áp dụng chúng trong bảng tính Excel của mình hiệu quả hơn trong khi tạo danh sách các giá trị duy nhất từ bảng dữ liệu. Nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, vui lòng cho tôi biết trong phần bình luận. Hoặc bạn có thể xem các bài viết khác của chúng tôi liên quan đến các hàm Excel trên trang web này.
Bạn cũng có thể thích khám phá
- Cách Tạo Danh sách Dấu đầu dòng trong Excel (9 Phương pháp)
- Tạo danh sách trong một ô trong Excel (3 Phương pháp Nhanh)
- Cách tạo bảng giá trong Excel (Hướng dẫn từng bước)