Công thức chung
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Tóm lược
Để trích xuất danh sách các giá trị duy nhất từ một tập hợp dữ liệu, được lọc theo số lượng hoặc số lần xuất hiện, bạn có thể sử dụng UNIQUE với FILTER và áp dụng tiêu chí với hàm COUNTIF. Trong ví dụ được hiển thị, công thức trong D5 là:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
xuất ra 3 giá trị duy nhất xuất hiện nhiều lần trong dải ô được đặt tên là "dữ liệu" (B5: B16).
Lưu ý: Trong ví dụ này, chúng tôi đang trích xuất một danh sách các giá trị duy nhất xuất hiện nhiều lần. Nói cách khác, chúng tôi đang tạo một danh sách các bản sao :) Ngôn ngữ hơi khó hiểu.
Giải trình
Ví dụ này sử dụng hàm UNIQUE cùng với hàm FILTER. Bạn có thể xem một ví dụ cơ bản hơn ở đây.
Mẹo trong trường hợp này là áp dụng tiêu chí cho hàm FILTER để chỉ cho phép các giá trị dựa trên số lần xuất hiện. Làm việc từ trong ra ngoài, điều này được thực hiện với COUNTIF và hàm FILTER ở đây:
FILTER(data,COUNTIF(data,data)>1)
Kết quả từ COUNTIF là một mảng các số lượng như sau:
(3;1;3;3;2;1;1;3;1;2;3;3)
được kiểm tra với so sánh logic> 1 để mang lại một mảng hoặc các giá trị TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Chú ý TRUE tương ứng với các giá trị trong dữ liệu xuất hiện nhiều hơn một lần. Mảng này được trả về FILTER dưới dạng đối số bao gồm , được sử dụng để lọc dữ liệu. Kết quả là FILTER trả về một mảng khác:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Mảng này được trả về trực tiếp cho hàm UNIQUE dưới dạng đối số mảng . Lưu ý trong số 12 giá trị ban đầu, chỉ có 8 giá trị tồn tại.
UNIQUE sau đó loại bỏ các bản sao và trả về mảng cuối cùng:
("red";"green";"blue")
Nếu các giá trị trong B5: B16 cchange, đầu ra sẽ cập nhật ngay lập tức.
Đếm> 2
Công thức trong F5, liệt kê các màu xuất hiện ít nhất 2 lần trong dữ liệu nguồn, là:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dải nguồn động
Vì dữ liệu (B5: B15) là một phạm vi được đặt tên thông thường, nó sẽ không thay đổi kích thước nếu dữ liệu được thêm vào hoặc xóa. Để sử dụng phạm vi động sẽ tự động thay đổi kích thước khi cần, bạn có thể sử dụng Bảng Excel hoặc tạo phạm vi được đặt tên động bằng công thức.
