[Excel] 월 별 일당이 다르고 근무 기간이 여러 달에 걸친 경우 총 급여 (단기 근무 급여 계산)

네이버 지식인에 재밌는 질문이 있어 열심히 작성해서 답변했는데, 결국 제가 엉터리 답변을 했더군요.

이리저리 머리 굴리다 거의 자포자기하고 있었는데, 우주신 등급의 답변자께서 등장하셔서 제가 한 번에 이해하기도 버거운 스킬을 구사하며 간단히 해결을 해주셨어요. 우주신의 스킬을 내재화시킬 수 있도록 정리해보았습니다.

[엑셀 함수 질문] 특정 날짜 기간의 금액 산출

질문 정리

월별로 일당이 다르고, 작업 기간이 여러 달에 걸쳐 있습니다.

즉 근무일수 X 월별 일당을 계산해서, 총금액을 산출해야 합니다. 입력 변수는 시작일과 종료일이에요.

아래와 같은 시트고, F열의 금액을 산출하면 되는 것입니다.

저의 접근 방법

월별 근무 일수를 계산하고 월별 일당을 찾아서 서로 곱하고 더해주고자 했습니다.

즉, 아래와 같이 월 별로 계산해서 더해주려고 했습니다.

첫 달 급여 = (첫 달 마지막 날 - 시작일 + 1) * 첫 달 일당

마지막 달 급여 = (종료일 - 전 달 마지막 날) * 마지막 달 일당

 

달의 마지막 날을 찾는 게 생소했는데 딱 맞는 함수가 있었습니다.

EOMONTH

이번에 새로 배웠는데, 특정일이 속한 달의 마지막 날을 찾는 함수를 사용했습니다. 

End of month를 뜻하는 함수명을 가지고 있습니다.

EOMONTH(start_date, months)

• start_date : 궁금해하는 달중 어떤 날이어도 괜찮습니다. 날짜 형식으로 입력이 필요합니다.
• months : 0을 입력하면 궁금해하는 달에 해당하며, 숫자가 커지면 그다음(1), 다음다음(2) 달이 됩니다. 마이너스로 입력하면 전달(-1), 전전달(-2)이 됩니다.

예를 들어, =eomonth(today(),0)라고 수식을 입력하면 이번 달의 마지막 날이 계산됩니다.

조금 더 응용하면, 특정 월의 첫째 날도 계산할 수 있습니다. 예를 들어 이번 달의 첫째 날을 계산하고자 하면 다음과 같습니다.

=eomontsh(today(),-1)+1

지난달의 마지막 날에 1을 더해주면 이번 달의 첫째 날이 되는 거죠!!

 

월별 일당을 찾는 건 vlookup으로 간단히 해결이 됩니다.

 

그러나! 제 접근 방법의 문제점이.. ㅠ_ㅠ

답변까지 하고 보니, 근무가 두 달에만 걸쳐있는 게 아닌 여러 달에 걸쳐있는 경우가 있었습니다.

석 달에 걸쳤다면, 두 번째 달에 대한 조건을 더해줘야 하는데 누락된 거죠.

그리고 근무가 넉 달이라면 두 번째 세 번째 달에 대한 조건을 달아줘야 하였습니다. 경우의 수를 모두 수식에 넣으려면 수식이 매우 복잡해질 것 같아, 손을 들고 말았습니다.

 

우주신의 솔루션!

제가 머리 아파했던 것과 비교해 수식이 너무 간결했습니다. 그리고 이해를 못했습니다... 도대체 어떻게 이런 수식이 정확한 결과를 얻어내는 건지.. 빌 게이츠도 놀랄 것 같다는 생각이 들었는데, 뭐 제 내공이 부족한 것일 수도 있고요..

 

우주신의 답변

=SUM(LOOKUP(ROW(INDIRECT(B3&":"&C3)),$H$3:$H$6,INDEX($I$3:$J$6,,MATCH(D3,$I$2:$J$2,))))

 

수정 답변

수식이 좀 복잡한 것 같아 아래와 같이 수정하였습니다. 제 양식에 맞게끔 구조적 참조 적용 등 수식 수정을 하였습니다.

=SUM(VLOOKUP(ROW(INDIRECT([@시작]&":"&[@종료])),일당표,[@TEAM]+1,1))

제가 새로 작성한 시트입니다. 앞서 보여드린 시트 계산 결과와 달리 정확합니다!

함수가 여러 개 쓰였기에 좀 더 잘 보이도록 아래와 같이 정리해 보았습니다.

sum(
	vlookup(
    	row(indirect(시작일셀&":"&종료일셀),
        일당표,
        팀번호+1,1)
        )
    )

indirect 부분

날짜는 서식이 정해지면 [년-월-일] 이런 식으로 보이지만, 특정 범위를 가지는 일반 숫자이기도 합니다. (예, 2020-07-16 = 44028)

시작일에 해당하는 숫자와 종료일에 해당하는 숫자를 참조 범위로 형성되도록 indirect를 사용한 사례입니다.

즉 [시작일:종료일]은 단순한 텍스트였지만, indirect함수의 인수로 사용되면서 참조 범위가 되었습니다. 계산 과정은 다음과 같습니다.

괄호 안의 실행 결과는, "44021:44053" 이었습니다. indirect("44021:44053")의 실행 결과는 $44021:$44053 입니다. 즉, 고정된 행범위 입니다.

 

row 부분

시작일과 종료일의 범위에 해당하는 행의 번호를 배열(array)로 반환해주는 수식입니다. indirect로 반환된 행 범위에 row함수를 쓴 결과 다음과 같은 배열이 반환되었습니다. 시작일과 종료일을 포함 사이의 모든 날짜를 배열로 만들었습니다. 배열의 값들은 다음과 같이 {중괄호}안에 세미콜론[;]으로 나열됩니다.

{44021;44022; ~ 생략 ~ ;44052;44053}

 

vlookup 부분

row 함수로 만든 날짜 배열에 해당하는 일당을 찾기 위한 파트입니다. [lookup_value/찾는 값]는 보통 하나이지만, 이번과 같이 배열을 사용할 수도 있습니다. 단, 배열을 계산 해주는 함수를 상위에 사용해야지만 정상적인 계산이 됩니다. 이번 사례에서는 sum을 사용했습니다. ※ VLOOKUP은 이전 포스팅도 참고해주세요. 2020/06/09 - [MS 오피스 팁] - [Excel] 엑셀 vlookup 자동 열번호 (match 그리고 표)

• lookup_value : 근무한 날짜 모두에 해당하는 배열
• table_array : 일당표
• col_index_num : 자동 열번호(이전 포스팅 참고)로 팀명에 매칭되는 값을 사용할 수 있지만, 수식을 짧게 하기 위해 팀 번호를 이용해 열번호를 매칭 시키도록 했습니다. 바로 [팀번호 + 1]과 같습니다.
• [range_lookup] : 이번 예제는 여기가 중요합니다. 근무일은 일당표에 모두 적혀있지않고 월 별로 되어있습니다. 이에 0(정확히 일치)가 아닌 1(유사 일치)를 반드시 사용해야합니다.

계산 결과는 근무일 하루하루에 대한 일당입니다.

{2000;2000;2000; ~생략~ ;3500;3500;3500}

sum 부분

근무일 하나하나에 해당하는 일당이 vlookup으로 찾아져 배열로 반환되었습니다. 배열의 값들을 단순히 합해주면 이번 예제의 최종 목표가 달성이 됩니다.

 

전체 계산되는 모습은 다음과 같습니다.

 

 

이상입니다.