Công thức chung
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Tóm lược
Để chỉ trích xuất các giá trị duy nhất từ danh sách hoặc cột, bạn có thể sử dụng công thức mảng dựa trên INDEX, MATCH và COUNTIF. Trong ví dụ được hiển thị, công thức trong D5, được sao chép xuống, là:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
trong đó "danh sách" là phạm vi được đặt tên B5: B11.
Lưu ý: đây là công thức mảng và phải được nhập bằng cách sử dụng control + shift + enter.
Giải trình
Cốt lõi của công thức này là tra cứu cơ bản với INDEX:
=INDEX(list,row)
Nói cách khác, cung cấp cho INDEX danh sách và một số hàng và INDEX sẽ truy xuất một giá trị để thêm vào danh sách duy nhất.
Công việc khó khăn là tìm ra số ROW để cung cấp INDEX, để chúng tôi chỉ nhận được các giá trị duy nhất. Điều này được thực hiện với MATCH và COUNTIF, và thủ thuật chính là ở đây:
COUNTIF($D$4:D4,list)
Tại đây, COUNTIF đếm số lần các mục đã có trong danh sách duy nhất xuất hiện trong danh sách chính, sử dụng tham chiếu mở rộng cho phạm vi, $ D $ 4: D4.
Tham chiếu mở rộng là tuyệt đối ở một mặt, tương đối ở mặt khác. Trong trường hợp này, khi công thức được sao chép xuống, tham chiếu sẽ mở rộng để bao gồm nhiều hàng hơn trong danh sách duy nhất.
Lưu ý rằng tham chiếu bắt đầu ở D4, một hàng phía trên mục nhập duy nhất đầu tiên, trong danh sách duy nhất. Điều này là cố ý - chúng tôi muốn đếm các mục * đã có * trong danh sách duy nhất và chúng tôi không thể bao gồm ô hiện tại mà không tạo tham chiếu vòng tròn. Vì vậy, chúng ta bắt đầu ở hàng trên.
Quan trọng: hãy chắc chắn rằng tiêu đề cho danh sách duy nhất không xuất hiện trong danh sách chính.
Đối với các tiêu chí trong COUNTIF, chúng tôi đang sử dụng chính danh sách chính. Khi được cung cấp nhiều tiêu chí, COUNTIF sẽ trả về nhiều kết quả trong một mảng. Tại mỗi hàng mới, chúng ta có một mảng khác nhau như sau:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Lưu ý: COUNTIF xử lý nhiều tiêu chí với mối quan hệ "HOẶC" (tức là COUNTIF (dải ô, ("đỏ", "xanh lam", "xanh lục")) tính màu đỏ, xanh lam hoặc xanh lục.
Bây giờ chúng ta có các mảng mà chúng ta cần tìm vị trí (số hàng). Đối với điều này, chúng tôi sử dụng MATCH, được thiết lập cho đối sánh chính xác, để tìm các giá trị bằng không. Nếu chúng ta đặt các mảng được tạo bởi COUNTIF ở trên vào MATCH, đây là những gì chúng ta nhận được:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH định vị các mục bằng cách tìm kiếm số lượng 0 (tức là tìm kiếm các mục chưa xuất hiện trong danh sách duy nhất). Điều này hoạt động, vì MATCH luôn trả về kết quả phù hợp đầu tiên khi có bản sao.
Cuối cùng, các vị trí được đưa vào INDEX dưới dạng số hàng và INDEX trả về tên tại vị trí đó.
Phiên bản không phải mảng với LOOKUP
Bạn có thể xây dựng một công thức không phải mảng để trích xuất các mục duy nhất bằng cách sử dụng hàm LOOKUP linh hoạt:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Cấu trúc công thức tương tự như công thức INDEX MATCH ở trên, nhưng LOOKUP có thể xử lý nguyên bản hoạt động của mảng.
- COUNTIF trả về số lượng của từng giá trị từ "danh sách" trong phạm vi mở rộng $ D $ 4: D4
- So sánh với 0 tạo ra một mảng các giá trị TRUE và FALSE
- Số 1 được chia cho mảng, tạo ra mảng 1s và lỗi # DIV / 0
- Mảng này trở thành lookup_vector bên trong LOOKUP
- Giá trị tra cứu của 2 lớn hơn bất kỳ giá trị nào trong lookup_vector
- LOOKUP sẽ khớp với giá trị không lỗi cuối cùng trong mảng tra cứu
- LOOKUP trả về giá trị tương ứng trong result_vector, phạm vi được đặt tên là "danh sách"
Trích xuất các mục chỉ xuất hiện một lần
Công thức LOOKUP ở trên rất dễ mở rộng với logic boolean. Để trích xuất danh sách các mục duy nhất chỉ xuất hiện một lần trong dữ liệu nguồn, bạn có thể sử dụng công thức như sau:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Sự bổ sung duy nhất là biểu thức COUNTIF thứ hai:
COUNTIF(list,list)=1
Ở đây, COUNTIF trả về một mảng số lượng mục như thế này:
(2;2;2;2;2;1;2)
được so sánh với 1, dẫn đến một mảng các giá trị TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
hoạt động như một "bộ lọc" để hạn chế đầu ra cho các mục chỉ xuất hiện một lần trong dữ liệu nguồn.
