엑셀 출퇴근 시간 구하기 공식
지원 버전 자세히 보기
출퇴근 기록에서 첫 출근과 마지막 퇴근 시간을 계산하는 공식입니다.
인수 설명
01Microsoft 365·Excel 2019 이후 공식
=IF ( COUNTIFS ( 날짜범위, 날짜, 이름범위, 이름, 출퇴근범위, "출근" )=0, "기록없음", MINIFS ( 시간범위, 날짜범위, 날짜, 이름범위, 이름, 출퇴근범위, "출근" ) )
=MAXIFS ( 시간범위, 날짜범위, 날짜, 이름범위, 이름, 출퇴근범위, "퇴근" )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02이전 버전 배열수식
{=MIN ( IF ( --( 날짜범위=날짜 ) * ( 이름범위=이름 ) * ( 출퇴근범위="출근" ), 시간범위, "" ) )}
{=MAX ( IF ( --( 날짜범위=날짜 ) * ( 이름범위=이름 ) * ( 출퇴근범위="퇴근" ), 시간범위, "" ) )}
인수 설명 자세히 보기
동작 원리
01Microsoft 365·Excel 2019 이후 공식
COUNTIFS 함수로 출근 기록 존재 여부를 확인하고, MINIFS 함수는 첫 출근 시간을, MAXIFS 함수는 마지막 퇴근 시간을 계산합니다.
Microsoft 365·Excel 2019 이후 공식의 예제 시트에서 G2 셀이 0(00:00)을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
COUNTIFS 함수가 출근 기록 개수를 확인합니다
COUNTIFS 함수는 조회 날짜, 이름, 출근 구분을 모두 만족하는 기록을 셉니다. 시간값이 0인 자정 출근도 기록 개수에는 포함되므로 기록 없음으로 잘못 판정하지 않습니다.
=COUNTIFS ( A2:A8, E2, B2:B8, F2, C2:C8, "출근" )
= 2
시간값 0과 0.375의 출근 기록 두 건입니다. MINIFS 함수가 첫 출근 시간을 찾습니다
MINIFS 함수는 세 조건을 만족하는 출근 시간 중 가장 이른 값을 계산합니다.
=MINIFS ( D2:D8, A2:A8, E2, B2:B8, F2, C2:C8, "출근" )
= 0
두 출근 기록 중 가장 이른 00:00의 일련값입니다. IF 함수가 기록 여부에 따라 결과를 반환합니다
출근 기록 개수 2가 0이 아니므로 IF 함수는 MINIFS 함수가 계산한 0을 반환합니다. 이 값은 자정 00:00을 뜻합니다. 조건을 만족하는 기록이 없으면 "기록없음"을 반환합니다.
=IF ( COUNTIFS ( A2:A8, E2, B2:B8, F2, C2:C8, "출근" )=0, "기록없음", MINIFS ( D2:D8, A2:A8, E2, B2:B8, F2, C2:C8, "출근" ) )
= 0
G2 셀에 표시되는 자정 출근의 시간 일련값입니다. 02이전 버전 배열수식
IF 함수는 조건을 만족하는 시간만 배열로 남기고, MIN 함수와 MAX 함수는 각각 첫 출근과 마지막 퇴근 시간을 계산합니다.
이전 버전 배열수식의 예제 시트에서 G2 셀이 0(00:00)을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
IF 함수가 조건에 맞는 출근 시간만 남깁니다
날짜, 이름, 출근 구분을 모두 만족하는 행에서는 시간값을 반환하고 나머지 행에서는 빈 문자열을 반환합니다.
=IF ( --( A2:A8=E2 ) * ( B2:B8=F2 ) * ( C2:C8="출근" ), D2:D8, "" )
= {0;"";0.375;"";"";"";""}
00:00과 09:00에 해당하는 시간 일련값만 남은 배열입니다. MIN 함수가 배열의 최솟값을 반환합니다
MIN 함수는 남은 두 시간값 중 0을 반환합니다. H2 셀은 같은 흐름으로 퇴근 시간값 0.25와 0.5만 남긴 뒤 MAX 함수가 0.5를 반환합니다.
{=MIN ( IF ( --( A2:A8=E2 ) * ( B2:B8=F2 ) * ( C2:C8="출근" ), D2:D8, "" ) )}
= 0
시간 형식으로 표시하면 00:00인 값입니다. 댓글 2
