Công thức Excel: Đếm ngày duy nhất
Công thức Excel: Đếm ngày duy nhất

Công thức chung
=COUNT(UNIQUE(date))
Tóm lược

Để đếm các ngày duy nhất (trong ví dụ là “ngày giao dịch”), bạn có thể sử dụng Hàm UNIQUE với COUNT hàmhoặc một công thức dựa trên Hàm COUNTIF. Trong ví dụ được hiển thị, công thức trong ô G8 là:

=COUNT(UNIQUE(date))

Ở đâu ngàyphạm vi được đặt tên B5: B16.

Giải trình

Theo truyền thống, việc đếm các mục duy nhất bằng công thức Excel là một vấn đề phức tạp, bởi vì không có một hàm duy nhất dành riêng. Tuy nhiên, điều đó đã thay đổi khi mảng động đã được thêm vào Excel 365, cùng với một số chức năng mới, bao gồm cả UNIQUE.

Lưu ý: Trong các phiên bản Excel cũ hơn, bạn có thể đếm các mục duy nhất bằng hàm COUNTIF hoặc hàm FREQUENCY, như được giải thích bên dưới.

Trong ví dụ được hiển thị, mỗi hàng trong bảng đại diện cho một giao dịch chứng khoán. Một số ngày, nhiều hơn một giao dịch được thực hiện. Mục tiêu là đếm số ngày giao dịch – số ngày duy nhất mà một số loại giao dịch đã xảy ra. Công thức trong ô G8 là:

=COUNT(UNIQUE(date))

Làm việc từ trong ra ngoài, Hàm UNIQUE được sử dụng để trích xuất danh sách các ngày duy nhất từ ​​phạm vi được đặt tên là “date”:

UNIQUE(date) // extract unique values

Kết quả là một mảng có 5 số như sau:

{44105;44109;44111;44113;44116}

Mỗi số đại diện cho một Ngày Excel, không có định dạng ngày tháng. 5 ngày là 1-Tháng 10-20, 5-Tháng 10-20, 7-Tháng 10-20, 9-Tháng 10-20 và 12-Tháng 10-20.

Mảng này được phân phối trực tiếp đến COUNT hàm:

=COUNT({44105;44109;44111;44113;44116}) // returns 5

trả về tổng số các giá trị số, 5, là kết quả cuối cùng.

Lưu ý: Hàm COUNT đếm các giá trị số, trong khi Hàm COUNTA sẽ đếm cả giá trị số và văn bản. Tùy thuộc vào tình huống, nó có thể hợp lý để sử dụng cái này hoặc cái kia. Trong trường hợp này, vì ngày là số, chúng tôi sử dụng COUNT.

Với COUNTIF

Trong phiên bản Excel cũ hơn, bạn có thể sử dụng Hàm COUNTIF để đếm các ngày duy nhất với công thức như sau:

=SUMPRODUCT(1/COUNTIF(date,date))

Làm việc từ trong ra ngoài, COUNTIF trả về một mảng có số lượng cho mỗi ngày trong danh sách:

COUNTIF(date,date) // returns {2;2;3;3;3;2;2;2;2;3;3;3}

Tại thời điểm này, chúng tôi có:

=SUMPRODUCT(1/{2;2;3;3;3;2;2;2;2;3;3;3})

Sau khi chia 1 cho mảng này, chúng ta có một mảng các giá trị phân số:

{0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333}

Mảng này được phân phối trực tiếp Hàm SUMPRODUCT. SUMPRODUCT sau đó tính tổng các mục trong mảng và trả về tổng, 5.

Với tần số

Nếu bạn đang làm việc với một tập hợp dữ liệu lớn, bạn có thể gặp vấn đề về hiệu suất với công thức COUNTIF ở trên. Trong trường hợp đó, bạn có thể chuyển sang công thức mảng dựa vào Hàm FREQUENCY:

{=SUM(--(FREQUENCY(date,date)>0))}

Lưu ý: Đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.

Công thức này sẽ tính toán nhanh hơn phiên bản COUNTIF ở trên, nhưng nó sẽ chỉ hoạt động với các giá trị số. Để biết thêm chi tiết, xem bài báo này.

Tập tin đính kèm

https://exceljet.net/formula/count-unique-dates