Để đếm số ngày duy nhất trong một khoảng ngày, chúng ta có thể nghĩ ngay đến hàm COUNTUNIQUE. Nhưng đó không phải là hàm đúng. Tại sao lại như vậy?
Chúng ta không thể dựa vào COUNTUNIQUE để đếm số ngày duy nhất trong một khoảng ngày. Bởi vì nó không thể xử lý các điều kiện. Vậy có những lựa chọn nào khác?
Trong bài hướng dẫn này, bạn sẽ tìm hiểu về năm công thức để đếm số ngày duy nhất trong một khoảng ngày. Chúng thuộc các loại khác nhau như:
- Một công thức cơ sở dữ liệu (cũng là một công thức kết hợp).
- Ba công thức kết hợp.
- Một công thức độc lập.
Công thức cơ sở dữ liệu dựa trên hàm cơ sở dữ liệu DCOUNT và hàm phân loại bộ lọc gọi là UNIQUE. Trong khi công thức độc lập dựa trên COUNTUNIQUEIFS.
Ngoài ra, còn ba công thức kết hợp khác mà chúng ta cũng có thể sử dụng để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Dưới đây là 5 công thức để ‘đếm duy nhất’ một khoảng ngày trong Google Sheets.
Ví dụ về khoảng ngày và tiêu chí kiểm tra
Các ngày cần đếm nằm trong A3:A17. Tiêu chí (khoảng ngày) nằm trong C2 và D2.
Những gì tôi muốn là đếm số ngày duy nhất giữa các ngày 01/11/2020 và 10/11/2020.
Xin lưu ý rằng tất cả các ngày trong ví dụ của tôi đều trong định dạng DD/MM/YY.
Kết quả của số ngày duy nhất sẽ là 6. Những ngày đó được đánh dấu trong hình ảnh để giúp bạn dễ hiểu.
Hãy đi vào các công thức một cách từng bước. Tôi sẽ bắt đầu với công thức kết hợp cơ sở dữ liệu.
Sử dụng DCOUNT để đếm số ngày duy nhất trong một khoảng ngày
Đầu tiên, tôi sẽ không sử dụng các tiêu chí nằm trong ô C2 và D2. Tôi sẽ cố định chúng vào công thức.
Cú pháp hàm: DCOUNT(database, field, criteria)
Công thức:
=dcount(unique(A2:A17), 1, {{"Date";">="&date(2020,11,1)},{"Date";"<="&date(2020,11,10)}})Trong công thức trên, phần công thức unique(A2:A17) được gọi là đối số ‘database’ trong DCOUNT. UNIQUE trả về một khoảng ngày duy nhất như trong bảng dưới đây.
| Date |
|---|
| 01/11/2020 |
| 02/11/2020 |
| 03/11/2020 |
| 04/11/2020 |
| 05/11/2020 |
| 06/11/2020 |
| 07/11/2020 |
| 08/11/2020 |
| 09/11/2020 |
| 10/11/2020 |
| 11/11/2020 |
| 12/11/2020 |
| 13/11/2020 |
| 14/11/2020 |
| 15/11/2020 |
| 16/11/2020 |
| 17/11/2020 |
Số ‘field’ là 1. Đó là vị trí tương đối của cột ngày trong ‘database’. Như bạn có thể biết, chỉ có một cột trong dữ liệu mẫu của chúng tôi.
Bây giờ chúng ta cần áp dụng phần ‘tiêu chí’.
Nếu bạn đã đọc qua hướng dẫn của tôi có tiêu đề – Hướng dẫn cuối cùng cách sử dụng Tiêu chí trong các chức năng Cơ sở dữ liệu trong Google Sheets – bạn có thể hiểu việc sử dụng tiêu chí ngày hoặc bất kỳ tiêu chí nào khác như một mảng trong các chức năng Cơ sở dữ liệu trong Google Sheets.
Ở đây, mảng tiêu chí đó là:
{{"Date";">="&date(2020,11,1)},{"Date";"<="&date(2020,11,10)}}Theo cách này, chúng ta có thể sử dụng DCOUNT để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Tôi muốn sử dụng tiêu chí bên ngoài công thức, nằm trong ô C2 và D2. Làm cách nào để sử dụng chúng?
Đối với việc đó, bạn cần chèn nhãn ‘field’, tức là “date” vào cả ô C1 và D1. Vì đó là nhãn ‘field’ trong A2 và chúng ta phải sử dụng loại nhãn trong tiêu chí.
Sau đó, thay đổi ngày C2 từ 1/11/20 thành >=1/11/20 và ngày D2 từ 10/11/20 thành <=10/11/20.
Sau đó, bạn có thể sử dụng công thức DCOUNT như dưới đây.
=dcount(unique(A2:A17), 1, G1:H2)Đơn giản, phải không?
COUNTUNIQUEIFS để đếm số ngày duy nhất trong một khoảng ngày
Đây là lựa chọn thứ hai của tôi để đếm số ngày duy nhất trong một khoảng ngày. Không giống như hàm Countunique, nó có thể xử lý tiêu chí.
Cú pháp: COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
Ở công thức Countuniqueifs dưới đây, tiêu chí (ngày) được cố định (được chèn trực tiếp vào công thức, không sử dụng tham chiếu ô).
=COUNTUNIQUEIFS(A3:A17, A3:A17, ">="&date(2020,11,1), A3:A17, "<="&date(2020,11,10))Tương tự như DCOUNT, chúng ta có thể làm cho công thức này đơn giản hơn bằng cách chỉ định tiêu chí là các biến (tham chiếu ô).
=COUNTUNIQUEIFS(A3:A17, A3:A17, ">="&C2, A3:A17, "<="&D2)
Lưu ý: Một số gợi ý để lựa chọn giữa DCOUNT và COUNTUNIQUEIFS.
Nếu các ngày trong cột A chứa một hàng tiêu đề (xem ô A2), thì bạn có thể sử dụng DCOUNT vì đó là một hàm cơ sở dữ liệu.
Tôi muốn nói rằng khoảng ngày phải có các đặc điểm của một cơ sở dữ liệu.
Nếu nó chỉ là một khoảng, hãy sử dụng Countuniqueifs.
3 Công thức kết hợp (Mẹo bổ sung)
Combo Formula 1 – Query
Ngoài hai công thức trên, chúng ta còn có thể sử dụng ba công thức khác nhau để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Chúng bao gồm Query, Filter và Countifs.
Tôi sẽ bắt đầu bằng Query. Trong Query cũng chúng ta nên sử dụng Unique trong ‘Dữ liệu’ của Query.
Cú pháp Query: QUERY(data, query, [headers])
Công thức:
=query(unique(A3:A17), "Select Count(Col1) where Col1>=date '2020-11-1' and Col1<=date '2020-11-10' label Count(Col1)''")Tiêu chí ngày được cố định trong Query. Nếu chúng ta tham chiếu các ô C2 và D2, thì công thức sẽ như sau.
=query(unique(A3:A17), "Select Count(Col1) where Col1>='"&TEXT(C2,"yyyy-mm-dd")&"' and Col1<='"&TEXT(D2,"yyyy-mm-dd")&"' label Count(Col1)''")Đây bạn nên lưu ý một điều quan trọng.
Dù định dạng ngày trong bảng của bạn là gì, trong công thức, bạn nên tuân theo cùng định dạng ngày mà tôi đã sử dụng trong các công thức truy vấn ở trên.
Đọc thêm tại đây: Cách sử dụng Tiêu chí ngày trong hàm Truy vấn trong Google Sheets
Combo Formula 2 – Filter
So với Query, FILTER có vẻ thực tế hơn để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Cú pháp: FILTER(range, condition1, [condition2, …])
Ở đây, chúng ta có thể lọc một khoảng ngày duy nhất hoặc lọc các ngày theo tiêu chí và sau đó làm cho chúng duy nhất. Tôi đang làm theo cái sau.
=unique(filter(A3:A17, A3:A17>=date(2020,11,1), A3:A17<=date(2020,11,10)))Kết quả:
| Unique Dates |
|---|
| 01/11/2020 |
| 02/11/2020 |
| 03/11/2020 |
| 04/11/2020 |
| 05/11/2020 |
| 06/11/2020 |
Bằng cách bao bọc đầu ra này bằng Count, chúng ta có thể có được kết quả yêu cầu.
Làm cách nào để làm điều đó?
Bao gói cả hai công thức Unique + Filter trên với Count. Bạn có thể xem công thức dưới đây.
=count(unique(filter(A3:A17, A3:A17>=date(2020,11,1), A3:A17<=date(2020,11,10))))Trong cả hai công thức kết hợp này, chúng ta có thể tham chiếu các ô thay vì chèn tiêu chí trực tiếp vào công thức. Đây là công thức đó.
Thay thế >=date(2020,11,1) bằng >=C2 và thay thế <=date(2020,11,10) bằng <=D2. Đơn giản thế thôi!
Combo Formula 3 – Countifs
Đây là công thức cuối cùng để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Chúng ta có thể sử dụng Countif để đếm có điều kiện.
Vì nó không có khả năng ‘duy nhất’ (làm cho một khoảng trở nên duy nhất), chúng ta có thể nhờ vào chức năng Unique ở đây trong Countif.
Cú pháp: COUNTIF(range, criterion)
Công thức:
=countifs(unique(A3:A17), ">="&date(2020,11,1), unique(A3:A17), "<="&date(2020,11,10))Ở đây, để thay thế tiêu chí được cố định, thay >=date(2020,11,1) bằng >=C2 và thay <=date(2020,11,10) bằng <=D2.
Trên đây là 5 công thức để đếm số ngày duy nhất trong một khoảng ngày trong Google Sheets.
Cách này, chúng ta có thể đếm số ngày duy nhất từ một ngày bắt đầu và ngày kết thúc như trong một tháng, trong một năm, hoặc trong một khoảng thời gian cụ thể như q1, q2 hoặc q3.
Cảm ơn bạn đã theo dõi. Chúc bạn thú vị!
Tài liệu tham khảo:
