Công thức Excel: Giá trị duy nhất theo số lượng
Công thức Excel: Giá trị duy nhất theo số lượng

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 ĐỘC NHẤT với BỘ LỌCvà áp dụng các 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))

kết quả đầu ra 3 giá trị duy nhất xuất hiện nhiều lần trong phạm vi được đặt tên “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 duy nhất các giá trị xuất hiện nhiều lần. Nói cách khác, chúng tôi đang tạo ra 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ể thấy 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ố đếm 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}

Lưu ý 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 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 mảng tranh luận. 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

Bởi vì dữ liệu (B5: B15) là một phạm vi được đặt tên bình 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 một 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 Excelhoặc tạo một phạm vi được đặt tên động với một công thức.

https://exceljet.net/formula/unique-values-by-count