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

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

제 아내가 병원에서 수간호사로 일하는 자리로 이동했어요.

아내가 일하는 병원의 수술실에는 두 명의 수간호사가 있어요.

한 분은 인적 자원 관리자이고, 다른 한 분은 수술실 업무 관리자라고 해요.

아내는 수술실 업무 관리자로 일하고 있는데, 인적 관리를 담당하는 수간호사가 좀 어리석은 면이 있는 것 같아요.

수술실의 특성상 간호사들은 자주 오버타임을 해야 하는 것 같아요.

기존의 인적 자원 관리 수간호사는 A4 용지에 50명의 간호사 목록을 작성하고, 일일이 수기로 기입한 뒤에 다시 엑셀로 옮기는 번거로운 작업을 하고 있더라고요. (아빠가 일본인 같아요...?)

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

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

근무 형태를 선택하고 퇴근 시간을 입력하면 오버타임 시간을 계산

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

근무 형태를 선택하고 퇴근 시간을 입력하면 오버타임 시간을 계산해 봐, 남편아!

문제는 그 조직의 간호사들이 3교대 근무를 하는 거라는 거예요. 수술실에서는 오버타임이 아주 자주 발생한다는 거죠.

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

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

사실 StartTime만 준비해도 충분했는데, Ds+1D라는 추가적인 정보가 제공되었어요. 이는 데이 근무자가 데이+이브닝+나이트+데이의 장시간 수술 참여로 인해 24시간 이상의 오버타임을 할 가능성을 대비한 것이에요.

다른 시트에서는 근무 형태를 선택하기 위해 엑셀의 드롭다운 목록을 사용했어요. Constant 시트의 값들을 가져와 계산에 활용할 수 있도록 설정했어요.

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

드롭다운 표시

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

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

엑셀 상대참조 절대참조 만들기

엑셀에서 상대 참조와 절대 참조를 설정하는 방법은 A2 셀을 절대 참조로 만들기 위해 $ 기호를 사용합니다. 엑셀의 절대 참조 단축키는 F4입니다.

만약 A2 셀을 절대 참조로 설정하고 C10까지의 범위를 선택한다면 $A$2:$C$10이 됩니다.

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

overtime 초과 근무시간 계산.

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

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

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

하지만, 3교대 근무자들의 초과 근무 시간을 계산할 때도 앞서 언급한 공식을 그대로 사용합니다.

문제는 일반적인 직장인의 초과 근무 시간은 대부분 자정을 넘지 않는다는 가정하에 계산되는 것입니다.

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

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

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

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

IF 함수의 조건문

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

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

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

IF 함수의 조건문

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

엑셀 vlookup함수 사용법

규정 시간은 엑셀의 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개를 추가로 붙여 넣으면 일주일치의 오버타임 시트가 완성됩니다.

오버타임 시트