엑셀 if 조건문 함수 Vlookup 사용법 병원 3교대 근무자 수술실 간호사 초과 근무시간 계산 구하기, excel 유효성검사 드롭다운 만들기, 상대참조 절대참조

엑셀 if 조건문 함수 Vlookup 사용법 병원 3교대 근무자 수술실 간호사 초과 근무시간 계산 구하기, excel 유효성검사 드롭다운 만들기, 상대참조 절대참조

내 아내가 수술실에서 수간호사로 일하는 자리로 이동했어.아내가 일하는 병원의 수술실에는 두 명의 수간호사가 있어.한 명은 인적 자원 관리자, 다른 한 명은 수술실 업무 관리자야.아내는 수술실 업무 관리자로 일하고 있는데, 인적 관리를 담당하는 수간호사가 약간 어리석은 듯한 모습이야.수술실의 특성상 간호사들은 자주 오버타임을 하는 것 같아.

기존의 인적 자원 관리 수간호사는 A4 용지에 50명의 간호사 목록을 넣고, 일일이 수기로 기입하고 다시 엑셀로 옮기는 매우 어리석은 일을 하고 있대. (아빠가 일본인인가...?)

A4 용지에 50명의 목록을 넣으면서, 얼마나 작게 글을 써야 할까요? 그리고 그 작은 글씨를 나이가 든 수간호사가 매번 노안을 극복하며 한 글자씩 읽어서 엑셀로 옮기는 일이라니...

아내가 너무 답답해하던 참에, 그녀가 나에게 미션을 주었어.

근무 형태를 선택하고 퇴근 시간을 입력하면 오버타임 시간을 계산해 봐, 남편아! 문제는 그 조직의 간호사들이 3교대 근무를 하는 거라는 것이고, 수술실에서는 오버타임이 아주 자주 발생한다는 거야.

가장 심하면 데이, 이브닝, 그리고 나이트까지 근무하는 것 같아, 이 부분까지 고려해서 오버타임 시간을 구해야 해.

엑셀을 사용하여 초과 근무 시간을 한 줄로 간단히 계산하려면, 많은 조건문과 변수를 고려해야 해서, Constants라는 시트에 변수로 사용할 셀을 정의해 놓았어.

사실 StartTime을 포함하여 준비한 것은 EndTime만으로도 충분했습니다. 그러나 Ds+1D라는 추가적인 정보가 제공되었습니다. 이는 데이 근무자가 데이+이브닝+나이트+데이의 장시간 수술 참여로 인해 24시간 이상의 오버타임을 할 가능성을 대비한 것입니다.

다른 시트에서는 근무 형태를 선택하기 위해 Excel의 드롭다운 목록을 사용하였습니다. Constant 시트의 값들을 가져와 계산에 활용하도록 구성되었습니다.

엑셀에서 드롭다운 목록을 만들기 위해서는 데이터 - 유효성 검사 - 설정 - 제한 대상[목록]으로 이동하고, 드롭다운 표시를 선택한 뒤, 원본을 선택하여 확인을 누르면 됩니다.

Constant 시트에서 사용할 값을 참조하기 위해서는 영역으로 설정하여야 합니다. 이 영역을 절대 참조로 지정해야 계산 시트에서 행을 드래그해도 참조 열과 행이 변경되지 않습니다.

상대 참조는 열과 행을 드래그 확장하면 함께 증가하게 되지만, 엑셀의 절대 참조는 지정된 영역만을 고정적으로 참조하게 됩니다.

엑셀에서 상대 참조와 절대 참조를 만들기 위해 A2 셀을 절대 참조로 만들려면 $를 붙여줍니다. 엑셀의 절대 참조 단축키는 F4입니다.

$A$2 이렇게 만들어진 Range를 C10까지 선택하여 절대 참조로 만들면 $A$2:$C$10이 됩니다.

이렇게 설정한 참조 영역은 행이나 열이 추가되어도 엑셀이 자동으로 영역의 좌표를 조정해 주지만, 해당 영역을 참조하는 측에서는 행이나 열이 변경되지 않습니다.

오버타임 초과 근무시간을 계산할 때, 평범한 직장인의 초과 근무시간을 구하는 계산식은 간단합니다.

초과 근무시간 = 실제 퇴근시간 - 규정 퇴근시간

이 식만 있으면 충분합니다.

물론, 3교대 근무자들의 초과 근무 시간도 앞서 언급한 공식을 그대로 따릅니다.

문제는 정상적인 직장인의 초과 근무 시간은 대체로 자정을 넘기지 않는다는 가정하에 그렇다는 점입니다.

예를 들어, 18:00에 퇴근해야 할 직장인이 야근으로 인해 새벽 1시에 퇴근했다면, 엑셀의 시간 서식으로 계산하면 음수 값이 출력될 수 있는 문제가 발생합니다.

일반적인 직장인이 자정을 넘어서까지 야근을 한다는 것은 거의 불법에 가까운 상황이지만 현실에서는 매우 흔한 문제입니다.

하지만 이러한 불법적인 근무를 하는 회사에서는 초과 근무 시간을 계산하여 수당으로 지급하지 않는 경우도 많습니다.

하지만 대기업 수준의 종합병원에서는 의료진이 초과 근무를 하면 반드시 초과 근무 수당을 지급해야 하므로, 초과 근무 시간을 계산해야 합니다. 또한, 이브닝 근무자들은 조금만 초과 근무를 해도 자정을 넘어서 퇴근하므로 반드시 자정을 넘은 시간의 초과 시간을 구해야 합니다.

따라서 이러한 경우에는 IF 함수의 조건문이 필요합니다.

주어진 조건에서는 퇴근 시간만 입력받기 때문에 근무자가 실제로 자정을 넘긴 시점에 퇴근하는지를 알 수 없습니다.

하지만 현실적으로 직장에서 규정된 퇴근 시간보다 이른 시간에 퇴근한다는 것은 이미 오버타임 근무를 한 가능성이 높다는 것입니다. 따라서 실제 퇴근 시간이 규정 퇴근 시간보다 빠른지 늦은 지를 판별하여 계산하면 됩니다.

일반적인 경우에는 실제 퇴근 시간이 규정 퇴근 시간 이후로 되기 때문에, 엑셀의 조건식에서는 실제 퇴근 시간(D)이 규정 퇴근 시간(C) 보다 늦은 지를 확인합니다.

규정 시간은 엑셀의 VLOOKUP 함수를 사용하여 다음과 같이 구합니다:

=VLOOKUP(B3, Constants!$A$2:$C$10, 3, FALSE)

VLOOKUP 함수는 다음과 같은 구성요소를 사용합니다: 찾는 값, 참조 영역, 불러올 열, 논리 값.

위의 예시에서는 B 열을 찾는 값으로 사용하고, 참조 영역은 Constant 시트의 $A$2부터 $C$10 범위입니다. 3열의 값을 불러올 것이며, 논리 값은 False로 설정되어 정확히 일치하는 값만 가져옵니다.

False로 설정할 경우, 값이 정확히 일치해야 하며, 엑셀의 서식이 텍스트일 경우에는 철자가 완전히 일치해야 합니다.

엑셀의 IF 함수 조건문은 다음과 같은 구성을 가집니다:

IF(조건식, 참일 때 값, 거짓일 때 값)

D 시간이 C 시간보다 늦다면, D - C로 문제없이 계산할 수 있습니다.

하지만 C 시간이 D보다 늦다면, D - C로 음수 시간이 되며, 엑셀은 ########로 표시합니다.

그러므로 위의 조건문에서 거짓인 경우에는 자정에서 규정 퇴근 시간을 빼고, 여기에 실제 퇴근 시간을 더하여 오버타임을 구할 수 있습니다.

문제는 엑셀의 셀 서식에서 시간 서식은 24시를 표현할 수 없고 0시로 표시됩니다.

따라서 엑셀의 시간 계산 함수인 TIME 함수를 사용할 때, 24시간을 변환하는 대신 23:59:59를 기준으로 정규 퇴근 시간을 빼고, 1분을 더해준 다음에 실제 퇴근 시간을 더해주면 정확한 초과 근무 시간을 구할 수 있습니다.

IF문을 이중으로 중복해서 사용한 이유는, 앞의 조건문은 Duty를 선택하지 않아 규정 시간 값이 비어 있을 때 결과 셀이 잘못된 값을 출력하는 것을 방지하기 위해서입니다. 따라서 엑셀의 IF 함수를 중첩하여 사용했습니다.

실제 계산을 하는 조건식은 다음과 같습니다:

IF(D3 > C3, D3 - C3, TIME(23, 59, 59) - C3 + TIME(0, 0, 1) + D3)

초과 근무 수당은 일반적으로 통상임금의 시급으로 계산되기 때문에, 보고서에는 시간의 소수점 형태로 표기되어야 합니다.

사실, 해당 셀에서는 한 줄로 처리할 수 있지만, 예외 상황 하나를 대비하여 열을 추가하여 초과 근무 시간과 분을 출력하고, 이를 다시 소수점 형태의 시간으로 변환하는 셀을 추가로 만들었습니다.

=IF(ISBLANK(D3), "", IF(B3 <> "Ds+1D", HOUR(E3) + MINUTE(E3) / 60, (HOUR(E3) + MINUTE(E3) / 60) + 24))

위의 식은 엑셀의 중첩 함수로 이중 조건문을 사용하여, 실제 퇴근 시간 셀이 비어 있을 경우 결과 셀도 비어 있도록 만듭니다.

근무 시간을 소수점 형태의 시간으로 표현해야 하므로, 계산된 초과 근무 시간의 분 값을 60으로 나누어 더해주는 것이 핵심입니다.

HOUR(E3) + MINUTE(E3) / 60

그러나, 우리는 이 엑셀을 만들 때 예상치 못한 예외 상황을 미리 고려했습니다.

바로 24시간 이상 근무하는 경우입니다!

이를 위해, 근무 형태가 "Ds+1D"가 아닌 경우 정상적인 근무 시간을 소수점 형태로 계산하여 결과 값을 출력하고, "Ds+1D"일 경우에는 계산된 값에 24를 더하도록 설정했습니다.

자 이제 다 끝났다.

이제는 엑셀 화면이 보기 좋게 정돈하면 됩니다..

규정 퇴근 시간과 OT는 계산식을 간략화하기 위해 참조용으로 만든 셀이므로 표시할 필요가 없습니다.

불불필요한 열을 선택한 후 마우스 오른쪽 버튼을 클릭하여 숨기는 처리를 하였습니다.

화면이 한결 깔끔해졌습니다.

이제 OT 셀에 시간 단위를 붙여보겠습니다.

엑셀에서 단위를 붙이는 방법은 다음과 같습니다.

시간을 소수점 1자리로 표시하려면 "표시 형식 - 사용자 지정"에서 형식을 0.0으로 설정하면 됩니다.

엑셀 단위를 붙이고 싶다면 큰따옴표로 감싸서 붙여주면 됩니다.

소수점 2자리로 시간을 표시하기 위해 형식을 "0.00" 시간으로 지정하였습니다.

한 주의 일정을 입력하도록 하기 위해 Duty 열부터 OT 열까지 영역을 선택한 후 복사하여 6개를 추가로 붙여 넣으면 일주일치의 오버타임 시트가 완성됩니다.