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

1. 문제

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

 

근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 하는 것에서 앞의 문제는 간단하니 넘어가고 뒷부분의 문제만 365 버전용 함수를 이용해 풀어보겠습니다.

 

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

0.01MB

 

 

2. 해결 방안 1

Scan 함수와 IFs 함수 등을 이용해 줄 별로 결과를 추출하는 수식을 만들어 봤습니다.

​

O2셀 : =TEXTJOIN(",",,SCAN("",OFFSET($B$3:$B$8,,ROWS(C$3:$C3)-1),LAMBDA(a,b,IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0),"")&IFS(b="시간외1","(1)",b="시간외2","(2)",b="시간외3","(2)",b="시간외4","(3)",b="시간외5","(4)",TRUE,""))))

 

가. Scan 함수

(1) 구문

=SCAN ([initial_value], array, lambda(accumulator, value))

​

Scan 함수는 초기값, 배열을 가지고 있고, 초기값과 배열을 Lambda 함수에 전달한 후

계산식을 통해 결과를 반환해 주는 기능을 합니다.

​

(2) 수식 설명

SCAN("",OFFSET(근무표!$B$2:$B$7,,ROWS($B$2:B2)-1),LAMBDA(a,b,IF(.....))))

​

초기값을 비워 두면 d1값이 반환돼서 ""로 했고,

두 번째 인수는 OFFSET(근무표!$B$2:$B$7,,ROWS($B$2:B2)-1)인데, 이것은 B2:B7열을 한 줄씩 내려갈 때마다 옆의 열을 참조하도록 하기 위한 것입니다.

​

Lambda 함수의 인수로 초기값과 Offset으로 이뤄진 열을 a와 b로 넘긴 다음

if 함수로 조건에 따라 결괏값을 반환하는 것입니다.

​

나. IFs 함수

(1) 구문

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

​

여러 가진 조건을 입력하고, 이에 따른 값을 한꺼번에 처리하는 함수로서

모든 것이 일치하지 않을 경우는 ",True, 값"으로 입력합니다.

​

True가 조건문에서 Else의 역할을 합니다.

​

(2) 수식 설명

IFS(b="시간외1","(1)",b="시간외2","(2)",b="시간외3","(1)",b="시간외4","(3)",TRUE,"")

셀 값이 "시간외1"이면 (1)로, "시간외2"면 (2)로 바꾸도록 하는 것입니다.

맨 마지막에 True일 경우, 다시 말해 앞의 조건이 모두 일치하지 않을 경우는 ""으로 하도록 했습니다.

​

다. 사원명 가져오기

IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0),"")

셀 값의 왼쪽 3글자가 "시간외"인 경우 A2셀을 기준으로 행 수에서 2를 뺀 값만 떨어진(Offset) 사원번호를 가져오도록 하는 것입니다.

​

라. TextJoin으로 연결

O4셀의 수식에서 Scan부터 끝에서 두 번째 괄호까지를 선택한 후 F9키를 누르면

아래와 같이 "{"";"";"";"";"사원5(3)";"사원6(1)"}이 반환되므로

TextJoin으로 연결하면 원하는 값이 나옵니다.

​

3. 해결 방안 2

Map, Transpose, ByRow 함수 등을 연결해서 한 번에 여러 줄에 적용되는 수식을 만들어 봤습니다.

​

P3셀 : =BYROW(TRANSPOSE(MAP($B$3:$H$8,LAMBDA(b,IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0),"")&IFS(b="시간외1","(1)",b="시간외2","(2)",b="시간외3","(2)",b="시간외4","(3)",b="시간외5","(4)",TRUE,"")))),LAMBDA(a,TEXTJOIN(",",TRUE,a)))

 

P3셀에 수식을 입력한 후 엔터키를 누르면 #분산! 에러가 발생하는데, 이것은 결괏값이 표시될 영역에 이미 데이터가 있어서 그런 것이므로 P4셀부터 P8셀까지 지우면 정상적으로 결괏값이 표시됩니다.

 

​가. Map 함수

(1) 구문

=MAP (array1, lambda_or_array<#>)

​

Map 함수는 배열을 Lambda 함수에 전달하고 계산한 값을 배열로 반환합니다.

​

(2) 수식 설명

MAP($B$3:$H$8,LAMBDA(b,IF(.....)))

​

B2셀부터 H7셀까지의 근무표를 배열로 Lambda 함수에 b라는 이름으로 전달하고, If함수로 처리한 값을 배열로 반환합니다.

​

if함수 이하의 수식은 해법 1과 같습니다.

​

(3) Map 수식 실행 결과

Map 수식의 결과를 알기 위해

앞부분의 BYROW(TRANSPOSE(를 지우고,

뒷부분에서 ),LAMBDA(a,TEXTJOIN(",",TRUE,a)))를 지우고 엔터 키를 누르면

 

오른쪽에 시간외근무 유형 표가 있어서 데이터가 표시되지 못하므로 시간외근무 유형을​ W열로 이동시킵니다.

 

그러면 Q열부터 V열까지 결과가 표시되는데,

 

O열과 비교하면 V열이 맨 아래 줄에 있어야 하고,

Q열은 4행에 있어야 하므로

행과 열을 바꿔야(Transpose) 합니다.

​

나. Transpose 함수

Transpose 함수는 행/열 전환을 해주는 함수입니다.

​

위 수식의 앞에 Transpose를 붙이고, 맨 뒤에 괄호를 추가하면

O열과 달리 열로만 구분되어 있을 뿐 행으로 된 값은 일치합니다.

​

다. ByRow함수

(1) ByRow 함수의 구문

ByRow 함수의 구문은 Map함수와 유사하게 배열과 배열을 Lambda에서 처리하는 구조로 되어 있는데,

단일 결괏값을 반환하는 것이 다른 점입니다.

=BYROW(array, lambda(row))

​

(2) ByRow 함수를 이용한 수식

​

=BYROW(TRANSPOSE(MAP($B$3:$H$8,LAMBDA(b,IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0),"")&IFS(b="시간외1","(1)",b="시간외2","(2)",b="시간외3","(2)",b="시간외4","(3)",b="시간외5","(4)",TRUE,"")))),LAMBDA(a,TEXTJOIN(",",TRUE,a)))

​

ByRow 다음의 Transpose부터 굵은 글씨로 표시한 부분이 배열이고,

이것을 Lambda함수의 a로 전달한 후

TextJoin(",",True,a)라고 해서

행별로 배열을 쉼표(,)로 결합한 값을 반환하는 것입니다.

 

(3) IFs 함수를 Choose 함수로 대체

시간외 다음의 값이 1, 2, 3, 4, 5이므로 Choose 함수를 사용할 수 있습니다.

 

=BYROW(TRANSPOSE(MAP($B$3:$H$8,LAMBDA(b,IF(LEFT(b,3)="시간외",OFFSET($A$3,ROW(b)-3,0)&"("&CHOOSE(RIGHT(b,1),1,2,2,3,4)&")","")))),LAMBDA(a,TEXTJOIN(",",TRUE,a)))

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

0.01MB