Rob giữ số liệu thống kê về từng công việc anh ấy hoàn thành mỗi ngày. Ví dụ: anh ta theo dõi số giờ và phút chính xác dành cho mỗi công việc bằng cách nhập thời gian bắt đầu (cột B) và thời gian kết thúc (cột C) cho mỗi công việc theo định dạng: 05/11/20 11:25 sáng. Trong một cột riêng biệt (cột G) anh ta có ngày cho mỗi ngày trong tháng. Ở bên phải của mỗi ngày (trong cột H), anh ấy muốn hiển thị số công việc anh ấy đã hoàn thành vào mỗi ngày trong số calendar. Rob tự hỏi anh ấy sẽ sử dụng công thức nào để tính ra con số này.

Nhiệm vụ này không hề dễ dàng như lúc đầu. Lý do là vì cách lưu trữ thời gian bắt đầu và kết thúc.

Cột B chứa cả ngày và giờ bắt đầu trong cùng một cột, ở định dạng “05/11/20 11:25 sáng” và cột C chứa cả ngày và giờ kết thúc ở cùng một định dạng. Bạn có thể nghĩ rằng bạn có thể sử dụng hàm COUNTIF trong cột G, theo cách này:

=COUNTIF(C$2:C$1000,G2)

Tuy nhiên, điều này sẽ không hoạt động. Lý do rất đơn giản – cột G (trong trường hợp này là ô G2) chứa ngày tháng, không có thời gian. Ví dụ: nó có thể chứa ngày 05/11/20. Khi điều này được so sánh với ô C2, có thể chứa 05/11/20 11:25 sáng, chúng không giống nhau. Hãy nhớ rằng Excel đang thực hiện so sánh dựa trên số sê-ri ngày và giờ được lưu trữ trong mỗi ô. Với các giá trị ví dụ vừa được đề cập, ô G2 sẽ chứa số sê-ri là 39217 và ô C2 sẽ chứa số sê-ri là 39217.47569. Vì hai giá trị này không giống nhau, COUNTIF không được tính là bằng nhau.

Rõ ràng, một giải pháp sẽ là thêm một cột khác chỉ chứa ngày kết thúc cho mỗi công việc mà không có thời gian. Sau đó, bạn có thể sử dụng hàm COUNTIF trong công thức của mình vì bạn đang so sánh “táo với táo”, có thể nói như vậy. Nhưng có một giải pháp không yêu cầu sử dụng kết quả trung gian trong một cột mới. Cách tiếp cận này sử dụng hàm COUNTIFS, áp dụng hai tiêu chí cho tất cả các ô trong một phạm vi:

=COUNTIFS(C$2:C$1000,">=" & G2,C$2:C$1000,"<" & (G2+1))

Công thức này sử dụng hai tiêu chí, xem hiệu quả xem bất kỳ giá trị nào trong các ô trong cột C lớn hơn hoặc bằng giá trị trong G2 nhưng nhỏ hơn G2 + 1 hay không. Nói cách khác, nó đếm mọi thứ xảy ra trong ngày được chỉ định trong G2.

Một cách tiếp cận khác là đảm bảo rằng giá trị số nguyên của bất kỳ thứ gì trong cột C được so sánh với ngày được lưu trữ trong cột G. Điều này có thể được thực hiện bằng cách sử dụng hàm SUMPRODUCT, theo cách này:

=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)

Số nguyên của mỗi giá trị trong phạm vi C2: C1000 được so sánh với ngày trong G2, cho ra một mảng giá trị Đúng và Sai. Nhân mỗi giá trị này với 1 sẽ biến các giá trị Đúng và Sai thành giá trị 1 và 0 tương ứng. Sau đó, công thức tính tổng các sản phẩm này, cho ra số lượng mong muốn.

Nếu muốn, bạn cũng có thể sử dụng công thức sau, công thức này về cơ bản thực hiện cùng một tác vụ bằng cách sử dụng SUMPRODUCT:

=SUMPRODUCT(--(INT(C$2:C$1000)=G2))

Một trong những lợi ích của việc sử dụng hàm COUNTIFS hoặc hàm SUMPRODUCT để xác định kết quả là bạn không cần sử dụng công thức mảng. Tuy nhiên, một số người thích sử dụng công thức mảng. Nếu bạn muốn sử dụng chúng, thì bạn có thể sử dụng một trong hai cách sau:

=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))

=SUM(IF(ROUNDDOWN(C$2:C$1000,0)=G2,1,0))

Hãy nhớ rằng công thức mảng phải được nhập vào một ô bằng Ctrl + Shift + Enter.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10422) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: