근무표 유형 변경하기(365 이전 버전용)

1. 문제

근무표 문제.xlsx

0.01MB

 

 

아래와 같이 직원별, 일자별 근무 현황이 있는데,

 

근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 합니다.

 

원 데이터는 다른 시트에 있는건데 이해와 설명의 편의를 위해 한 개 시트에 모았습니다.

 

2. 해결 방안

365 버전용 함수를 이용하는 방법과 이전 버전용 함수를 이용하는 두 가지 방법에 대해 알아보겠습니다.

 

가. 365 이전 버전

(1) d1, d2, n1, n2 유형에 따라 일자별 근무자 및 괄호안에 숫자 표시

 

일자와 유형 2가지 조건을 만족해야 하므로

두가지 조건을 * 연산자로 연결하면

=($B$3:$H$8=K$2)*($B$2:$H$2=$J3)이 되는데,

#분산!라고 표시되고, 6*7의 크기로 테두리가 쳐집니다.

 

365 버전이라 이렇게 보이는 것이고,

이전 버번이라면 1만 표시될 겁니다.

 

따라서, 365 버전의 경우 테두리 안에 있는 데이터를 모두 지우면

 

d1이면서 1일인 것만 1(참)이고, 나머지는 0(거짓)으로 표시됩니다.

 

이제 조건에 맞는 경우 왼쪽 근무표의 사원1 등을 표시하고, 괄호 안에 d1  등의 1을 넣으면 됩니다.

 

따라서, 수식은

=IF(($B$3:$H$8=K$2)*($B$2:$H$2=$J3),$A$3:$A$8&"("&RIGHT(K$2,1)&")","")

이 되고, 결과는 일치하는 셀만 사원1(1)이고, 나머지는 빈 칸입니다.

 

이제 TextJoin 함수로 문자열을 쉼표(,)로 연결하는데 공백인 것은 제외하기 위해 두 번째 인수를 True로 하면

 

수식은

=TEXTJOIN(",",TRUE,IF(($B$3:$H$8=K$2)*($B$2:$H$2=$J3),$A$3:$A$8&"("&RIGHT(K$2,1)&")",""))

이 됩니다.

 

이제 사원2, 1일의 데이터 d2를 d1으로 수정하면

1일 d1에 해당하는 사원이 사원1과 사원2 두명이므로 사원1(1),사원2(1)로 표시됩니다.

 

다시 d2로 돌리고,

K3셀의 수식을 복사해서 N7셀까지 붙여넣으면

아래와 같이 됩니다.

 

(2) 시간외 근무자와 괄호안에 시간외 유형에 따른 숫자 표시

 

(가) Vlookup 함수를 이용하는 방법

시간외 근무자를 추출하는 것은 B3셀에서 H8셀 중 '시간외'란 글자가 들어있는 셀의 왼쪽 사원번호를 가져오면 되므로 간단한데,

=TEXTJOIN(",",TRUE,IF(($B$2:$H$2=J3)*(LEFT($B$3:$H$8,3)="시간외"),$A$3:$A$8&"("&$B$3:$H$8&")",""))

 

괄호안에 시간외 근무유형에 따라 1, 2, 2, 3, 4로 표시하는 것은 Vlookup을 사용할 수도 없지만 수식을 다시 한번 사용해야 하니 복잡해집니다.

=IFERROR(TEXTJOIN(",",TRUE,IF(($B$2:$H$2=J3)*(LEFT($B$3:$H$8,3)="시간외"),$A$3:$A$8,""))&"("&VLOOKUP(TEXTJOIN(",",TRUE,IF(($B$2:$H$2=J3)*(LEFT($B$3:$H$8,3)="시간외"),$B$3:$H$8,"")),$S$3:$T$7,2,0)&")","")

 

그렇지만 원하는 결과는 나옵니다.

 

(나) Substitute 함수를 이용하는 방법

시간외1은 1, 시간외2는 2식으로 Substitute 함수를 사용할 수 있습니다.

그런데 한 번에 하나씩만 바꿀 수밖에 없어 지저분합니다.

 

=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,IF(($B$2:$H$2=J3)*(LEFT($B$3:$H$8,3)="시간외"),$A$3:$A$8&"("&$B$3:$H$8&")","")),"시간외1",1),"시간외2",2),"시간외3",2),"시간외4",3),"시간외5",4),"")

 

(다) Choose 함수를 이용하는 방법

시간외 다음의 숫자가 1,2,3,4,5이므로 Choose 함수를 사용해서 1,2,3,4,5에 해당하는 값을 반환할 수 있으면

수식은 아래와 같습니다.

 

=IFERROR(TEXTJOIN(",",TRUE,IF(($B$2:$H$2=J4)*(LEFT($B$3:$H$8,3)="시간외"),$A$3:$A$8&"("&CHOOSE(RIGHT($B$3:$H$8,1),1,2,2,3,4)&")","")),"")

 

(라) 시간외근무자가 2몀이상인 경우

2일 사원5의 휴를 시간외4로 수정하면

Vlookup은 시간외4와 시간외1을 찾다보니 에러가 발생하고,

 

Substitute와 Choose를 이용한 수식은 문제없이

사원5(3),사원6(1)이 반환됩니다.

근무표 문제의 답(365 이전 버전용).xlsx

0.01MB