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

엑셀 if조건문, Vlookup함수를 이용한 병원 수술실 간호사 초과근무시간 구하기

아내가 수술실 수간호사로 자리를 옮겼다.

아내 병원 수술실은 수간호사가 2명이다.

1명은 인적자원 관리자, 1명은 수술실 업무관리자이다.

아내의 롤은 업무관리자인데, 인적관리 수간호사가 좀 댕청한 모양이다.

수술실 특성상 간호사들은 오버타임이 잦은 모양이다.

이걸 기존의 인적자원관리 수간호사가 A4에 50명의 간호사 목록을 몰아넣고, 일일이 수기로 기입하게 해서 그걸 다시 엑셀로 옮기는 댕청한 짓을 하고 있었다고 한다. (조상이 일본인인가...?)

A4에 50명의 목록이 들어가니, 얼마나 깨알같은 글씨로 적어야 할까... 그걸 또 나이 든 수간호사가 노안을 이겨내며 한 자 한 자 들여다보고 엑셀로 옮기는 짓이라니...

답답했던 아내가 내게 미션을 줬다.

근무형태를 선택하고 퇴근시간을 입력하면 OverTime 시간을 계산해라 남편몬!

문제는 이들 조직은 3교대를 하는 간호사라는 것이고, 수술실은 오버타임이 매우 잦다는 것이다.

심하면 데이+이브닝+나이트까지 근무를 하는 모양인데, 이 부분까지 고려해서 overtime 시간을 구해야 한다.

엑셀 초과근무시간 계산을 한 줄로 끝내자니 조건문도 많고, 변수도 있어서 Constants라는 Sheet에 변수로 활용할 셀을 만들어서 정의해두었다.

사실 EndTime만 있어도 되지만, 넘겨받은 자료라 StartTime까지 준비했을 뿐이다.

Ds 외에 Ds+1D가 추가됐는데, 이는 데이 근무자가 혹시 모를 상황으로 데이+이브닝+나이트+데이의 장시간 수술 참여로 24시간 이상 OverTime을 하게 됐을 때를 대비한 것이다.

다른 시트에서는 근무형태를 엑셀 드롭다운 Select로 선택하면 Constant sheet의 값을 가져와서 계산에 활용하도록 했다.

excel 유효성검사

엑셀 드롭다운 만들기는 데이터-유효성 검사-설정-제한 대상[목록], 드롭다운 표시 V, 원본을 선택해주고 확인을 누르면 된다.

원본에서 참조할 값을 Constant 시트에서 영역으로 설정하고 이 영역을 절대 참조로 해야 계산 시트에서 행을 드래그해도 참조 열과 행이 변경되지 않는다.

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

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

A2셀을 절대 참조로 만들려면 $를 붙여준다. 엑셀 절대참조 단축키는 F4

$A$2

이것을 Range로 C10까지 선택할 때 절대참조로 만들면 $A$2:$C$10 이 된다.

이렇게 만들어둔 참조 영역은 행이나 열이 추가되면 엑셀이 자동으로 알아서 영역의 좌표는 변경해주지만, 참조하는 측에서 행이나 열이 밀리진 않는다.

overtime 초과 근무시간 계산.

초과된 근무시간을 계산할 때 평범한 직장인의 초과근무시간을 구하는 계산식은 간단하다.

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

이면 충분하다.

물론 3교대 근무자들의 초과근무시간도 위의 식을 그대로 따른다.

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

18:00시에 퇴근해야 할 직장인이 야근을 하다가 새벽 1시에 퇴근했다면 엑셀 시간 서식으로 계산을 하면 음수 값이 출력되는 문제가 생긴다.

일반 직장인이 야근을 자정 넘어서까지 한다는 것은 거의 불법에 가깝지만 현실에서는 밥 먹도록 일어나는 문제다.

하지만 그런 불법을 저지르는 직장은 어차피 초과근무시간 따위 계산해서 수당으로 지급하지 않는다.

 

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

따라서 이때 사용할 식에는 if 함수 조건문이 들어가야 한다.

퇴근시간만 입력받는다는 조건하에서는 근무자가 실제로 자정을 넘긴 시점에 퇴근하는지 알 수는 없다.

하지만 현실적으로 직장에서 규정된 퇴근시간보다 이른 시간에 퇴근한다는 것은 이미 오버타임 근무를 했을 가능성이 높다는 것이므로, 실제 퇴근시간이 규정 퇴근시간보다 빠른지 느린지를 판별해서 계산하면 된다.

정상적이라면 실제 퇴근시간이 규정 퇴근시간 이후가 되므로 엑셀 조건식에서는 실제 퇴근시간(D)이 규정 퇴근시간(C) 보다 느린지를 체크한다.

엑셀 vlookup함수 사용법을 이용해서 규정시간은

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

위와 같이 구했다.

엑셀 vlookup 함수의 사용방법은 vlookup(찾는 값, 참고 영역, 불러 올 열, 논리 값)으로 인수를 사용한다.

위 예제에서 찾는 값은 B열이고, 참조 영역은 Constant시트의 $A$2에서 $C$10 range이며, 3열의 값을 불러오되, 논리 값이 False일 때는 반드시 일치, True일 때는 대략적으로 일치하면 된다.

False일 때는 값이 정확히 일치해야 하며 엑셀 서식이 Text일 때는 철자가 완전히 일치해야 한다.

엑셀 IF 함수 조건문은

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

이다.

D시간이 C시간보다 늦다면 D-C를 하면 문제가 없다.

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

때문에 위 조건문에서 거짓일 때는 자정에서 규정 퇴근시간을 빼고 여기에 실제 퇴근시간을 더해줌으로써 오버타임을 구할 수 있다.

문제는 엑셀 셀 서식의 시간 서식은 24시를 표현할 수 없고 0시가 된다.

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

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

IF문을 이중으로 중복해서 사용했는데, 앞의 조건문은 Duty를 선택하지 않아서 규정시간 값이 비어 있을 때 결과 셀이 엉뚱한 값을 출력하는 것을 방지하기 위해 엑셀 if함수 중첩을 넣은 것이다.

실제로 계산하는 조건식은 다음과 같다.

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

초과근무수당은 통상임금의 시급으로 계산되기 때문에, 보고서상에는 Hour의 소수점 형태로 기입돼야 한다.

사실 저 셀에서 한 줄로 처리를 해 줄 수 있지만 단 하나의 예외상황을 위해서 열을 하나 더 추가해서 초과근무 시간:분을 출력하고 이 것을 다시 소수점의 Hour로 변환하는 셀을 더 만들었다.

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

역시 엑셀 중첩함수 이중 조건문을 사용했는데, 이번에는 실제 퇴근시간 셀이 비어 있으면 결과 셀도 비어 있도록 만들기 위한 것이다.

근무시간을 소수점의 시간으로 표현해야 하므로 구해진 오버타임의 Minute값을 60으로 나눠서 더해준다는 것이 핵심이다.

HOUR(E3)+MINUTE(E3)/60

그런데, 우리는 이 엑셀을 만들 때 어쩌다 한 번 있을 예외상황을 미리 상정했다.

24시간 이상 근무자의 근무시간!

이것을 위해서, 근무형태가 Ds+1D가 아니면 정상적인 근무시간 소숫점 Hour값을 계산해서 결과값을 출력하게 하고, Ds+1D 일 경우에는 계산한 값에 24를 더하도록 했다.

자 이제 다 끝났다.

문제는 이제 좀 엑셀 화면이 정돈돼 보이게 하는 것이다.

규정 퇴근시간과 OT는 계산식을 간략화하기 위해서 참조를 위해 만든셀이기 때문에 굳이 보일 필요가 없다.

불필요한 열을 Ctrl+클릭으로 선택한 뒤 마우스 오른쪽 버튼을 눌러서 숨기기 처리한다.

한결 깔끔해졌다.

OT셀에 시간 단위를 붙여보자.

엑셀 단위 붙이기는 다음과 같다.

시간을 소수점 1자리로 하고 싶다면 표시 형식-사용자 지정에서 형식을 0.0으로 하면 된다.

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

소숫점 2자리 시간으로 표현하기 위해서 0.00" 시간"으로 형식을 지정해 줬다.

1 시트에 일주일치를 입력하게 하기 위해서 Duty열부터 OT열까지 영역을 잡고 복사해서 6개를 추가로 더 붙여 넣어주면 일주일치 오버타임 시트가 완성된다.