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

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

내 아내가 수술실에서 수간호사로 일하게 되었다. 그곳에는 인적자원 관리자와 수술실 업무관리자라는 두 명의 수간호사가 있었다. 아내의 역할은 업무관리자로서, 그러나 인적관리에는 좀 서툴러 보이는 것 같았다.

수술실은 특성상 간호사들이 자주 오버타임을 해야 하는 모양새였다. 기존의 인적자원관리 수간호사는 무려 50명의 간호사 목록을 A4 용지 한 장에 넣고, 그것을 일일이 수기로 작성한 뒤 다시 엑셀로 옮기는 번거로운 일을 하고 있었다. 이런 일로 아내는 고생을 하게 되었는데, 정말 일본인 조상이 있나 싶을 정도로 작은 글씨로 적어야 했다니까요. 그리고 그 작업을 노안을 겨냥한 노인이 한 자 한 자 들여다보며 엑셀로 옮기려고 한다는 것이 더 어이없는 일이었습니다.

아내는 이러한 상황에 답답함을 느끼며 나에게 도전 과제를 주었습니다.

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

문제는 수간호사들이 3교대로 근무한다는 것과 수술실에서는 오버타임이 매우 빈번하게 발생한다는 것이었습니다.

때로는 데이, 이브닝, 나이트 근무까지 겹쳐서 하는 경우도 있어서, 모든 이러한 요소들을 고려하여 오버타임 시간을 계산해야 했습니다.

엑셀로 초과 근무 시간을 계산하려면 많은 조건문과 변수를 고려해야 했는데, 그래서 저는 Constants라는 시트를 만들어서 변수들을 미리 정의해 두었습니다.

사실 StartTime을 포함해서 준비한 것은 EndTime만 있어도 되지만, 받은 자료에는 StartTime도 포함되어 있었습니다.

Ds에 더하여 Ds+1D가 추가되었는데, 이는 데이 근무자가 데이+이브닝+나이트+데이의 긴 수술 참여로 인해 24시간 이상의 초과 근무를 해야 할 경우를 대비한 것입니다.

다른 시트에서는 근무 형태를 선택할 때 Excel의 드롭다운 메뉴를 사용하여 Constant 시트의 값을 가져와 계산에 활용하도록 구성하였습니다.

excel 유효성검사

Excel에서 드롭다운 메뉴를 만들기 위해서는 데이터 - 유효성 검사 - 설정 - 제한 대상 [목록]을 선택하고 드롭다운 표시를 활성화한 후, 원본을 선택하고 확인 버튼을 누르면 됩니다.

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

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

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

Excel에서 상대 참조와 절대 참조를 만드는 방법은 다음과 같습니다.

예를 들어, A2셀을 절대 참조로 지정하려면 $ 기호를 사용하여 $A$2로 표시합니다. Excel에서 절대 참조의 단축키는 F4입니다.

$A$2

$A$2를 Range로 선택한 뒤 C10까지 확장하여 절대 참조로 만들면 $A$2:$C$10이 됩니다.

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

overtime 초과 근무시간 계산.

초과 근무 시간 계산을 위한 초과된 근무 시간 공식은 일반적인 직장인의 초과 근무 시간을 구하는 것과 간단합니다.

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

이렇게 하면 충분합니다.

물론 3교대 근무자들의 초과 근무 시간도 이전에 언급한 공식을 그대로 사용합니다.

다만, 문제는 정상적인 직장인의 초과 근무 시간은 대체로 자정을 넘지 않는다는 가정하에 계산되는데, 이것이 문제가 됩니다.

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

실제로 자정을 넘어서까지 야근하는 경우는 대부분 불법에 가깝기 때문에 현실적으로는 거의 발생하지 않는 문제입니다.

그러나 이런 불법적인 야근을 하는 직장에서는 초과 근무 시간을 계산하여 수당으로 지급하지 않는 경우도 많습니다.

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

따라서, 이런 상황에서는 IF 함수의 조건문이 사용되어야 합니다.

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

하지만 현실적으로 직장에서 규정된 퇴근 시간보다 이른 시간에 퇴근한다는 것은 이미 오버타임 근무를 한 것으로 간주될 수 있으므로, 실제 퇴근 시간이 규정 퇴근 시간보다 빠른지 여부를 판별하여 계산하면 됩니다.

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

엑셀 vlookup함수 사용법

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

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

위와 같이 구했다.

규정 시간을 구하기 위해 엑셀의 VLOOKUP 함수를 활용했습니다. 사용법은 vlookup(찾는 값, 참고 영역, 불러 올 열, 논리 값)으로 인수를 전달합니다.

예를 들어, 위의 예시에서는 찾는 값으로 B열을, 참고 영역으로 Constants 시트의 $A$2부터 $C$10 범위를 지정하고, 3열의 값을 불러오도록 설정했습니다. 논리 값은 False로 지정하여 정확한 일치를 요구합니다.

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

엑셀의 IF 함수는 조건문을 사용하여 IF(조건식, 참일 때 값, 거짓일 때 값) 형태로 작성합니다.

엑셀 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)

초과 근무 수당은 보통의 임금을 시간 단위로 계산하므로, 보고서에는 소수점 형태의 시간으로 기입되어야 합니다.

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

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

여기서도 엑셀의 중첩 함수인 IF 함수를 이중 조건문으로 사용하였는데, 이번에는 실제 퇴근 시간 셀이 비어 있을 경우 결과 셀도 비어 있도록 만들기 위한 조치입니다.

근무 시간을 소수점 시간으로 표현해야 하므로, 계산된 오버타임의 분 값을 60으로 나눈 후 더해줍니다.

HOUR(E3)+MINUTE(E3)/60

하지만, 우리는 이 엑셀을 만들 때 한 번 있을 수 있는 예외 상황을 미리 고려하였습니다.

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

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

자 이제 다 끝났다.

이제는 엑셀 화면을 정리해서 더 깔끔하게 보이도록 해보겠습니다.

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

불필요한 열을 선택한 후 마우스 오른쪽 버튼을 클릭하여 숨기기로 처리하면 됩니다. 이렇게 하면 화면이 더 깔끔해집니다.

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

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

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

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

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

한편, 1개의 시트에 일주일치를 입력하도록 만들기 위해 Duty 열부터 OT 열까지의 영역을 선택한 후 복사하여 6번 더 붙여 넣어주면 일주일치의 오버타임 시트가 완성됩니다.