Rob은 매일 완료하는 각 작업에 대한 통계를 보관합니다. 예를 들어, 그는 각 작업의 시작 시간 (B 열)과 완료 시간 (C 열)을 05/11/20 11:25 am 형식으로 입력하여 각 작업에 소요 된 정확한 시간과 분을 추적합니다. 별도의 열 (G 열)에는 해당 월의 각 날짜에 대한 날짜가 있습니다. 각 날짜의 오른쪽 (H 열)에는 각 calendar 날짜에 완료 한 작업 수를 표시하려고합니다. Rob은이 숫자를 계산하기 위해 어떤 공식을 사용할지 궁금합니다.

이 작업은 처음에는 생각만큼 쉽지 않습니다. 그 이유는 시작 및 종료 시간이 저장되는 방식 때문입니다.

B 열에는 “05/11/20 11:25 am”형식으로 같은 열에 시작 날짜와 시간이 모두 포함되어 있고 C 열에는 같은 형식으로 종료 날짜와 시간이 모두 포함되어 있습니다. 다음과 같이 G 열에서 COUNTIF 함수를 사용할 수 있다고 생각할 수 있습니다.

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

그러나 이것은 작동하지 않습니다. 그 이유는 간단합니다. G 열 (이 경우 G2 셀)에는 시간이없는 날짜가 포함되어 있습니다. 예를 들어, 날짜가 05/11/20 일 수 있습니다. 이것이 05/11/20 11:25 am을 포함 할 수있는 C2 셀과 비교할 때 동일하지 않습니다. Excel은 각 셀에 저장된 날짜 및 시간 일련 번호를 기반으로 비교를 수행합니다. 방금 언급 한 예제 값이 주어지면 셀 G2에는 일련 번호 39217이 포함되고 셀 C2에는 일련 번호 39217.47569가 포함됩니다. 이 두 값이 같지 않기 때문에 COUNTIF는 두 값을 같은 것으로 간주하지 않습니다.

분명히 한 가지 해결책은 시간없이 각 작업의 종료 날짜 만 포함 된 다른 열을 추가하는 것입니다. 그런 다음 “사과와 사과”를 비교하기 때문에 공식에 COUNTIF 함수를 사용할 수 있습니다. 그러나 새 열에서 중간 결과를 사용할 필요가없는 솔루션이 있습니다. 이 접근 방식은 범위의 모든 셀에 두 가지 기준을 적용하는 COUNTIFS 함수를 사용합니다.

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

이 수식은 두 가지 기준을 사용하여 C 열의 셀에있는 항목이 G2의 값보다 크거나 같지만 G2 + 1보다 작은 지 효과적으로 확인합니다. 즉, G2에 지정된 하루 동안 발생하는 모든 것을 계산합니다.

또 다른 접근 방식은 C 열에있는 정수 값을 G 열에 저장된 날짜와 비교하는 것입니다. 이는 다음과 같은 방식으로 SUMPRODUCT 함수를 사용하여 수행 할 수 있습니다.

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

C2 : C1000 범위에있는 각 값의 정수는 G2의 날짜와 비교되어 True 및 False 값의 배열을 제공합니다. 각각에 1을 곱하면 True와 False 값이 각각 1과 0 값으로 바뀝니다. 그런 다음 공식은 이러한 제품을 합산하여 원하는 개수를 제공합니다.

원하는 경우 SUMPRODUCT를 사용하여 본질적으로 동일한 작업을 수행하는 다음 공식을 사용할 수도 있습니다.

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

COUNTIFS 함수 또는 SUMPRODUCT 함수를 사용하여 결과를 확인할 때의 이점 중 하나는 배열 수식을 사용할 필요가 없다는 것입니다. 그러나 어떤 사람들은 배열 수식을 선호합니다. 이를 사용하려면 다음 중 하나를 사용할 수 있습니다.

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

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

Ctrl + Shift + Enter를 사용하여 배열 수식을 셀에 입력해야합니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (10422)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Counting_Jobs_Completed_On_a_Date [한 날짜에 완료된 작업 계산].