배열로 Or 조건 처리

1. 문제

왼쪽에 담당자별 지역별 현황이 있는데, 
이것을 오른쪽 표와 같이 담당자별로 지역별로 집계를 하려고 하는데, 특이한 점은 지역을 묶어서 집계한다는 것입니다.

 
이런 경우에 일반적으로 사용하는 것이 피벗 테이블이지만,
위와 같이 수원, 용인 경우의 합계를 구하려면 두 개의 값을 더할 수밖에 없습니다.

 
이와 같은 경우에 수원 또는 용인인 경우라는 조건을 넣어서 건수를 집계하고자 하는 것입니다.
 

2. 해법 1 : TextSplit 함수 이용

가. Sum + CountIfs 이용

TextSplit 함수는 Microsoft 365에서만 사용 가능한 함수이기 때문에 이보다 낮은 버전이라면 사용할 수 없는 제한은 있지만 이와 같은 경우에 매우 쉽게 처리할 수 있습니다.
 
다시 말해 수원, 용인을 TextSplit 함수를 이용하면 수원과 용인으로 분리한 결과를 반환합니다.
 
TextSplit 함수의 구문은
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
인데,
여기서, text, col_delimiter만 알면 됩니다.
text는 문자열, col_delimiter은 열 구분자인데 여기서는 쉼표(,)입니다.
 
따라서, i22셀에 =textsplit(i21, ",")라고 입력하면
#분산! 에러가 발생하는데 이것은 옆 셀에 수식이 있어서 그런 것이므로 나머지 셀을 모두 지우면 됩니다.

 
이제 CountIfs 함수와 연결하면
=COUNTIFS($C:$C,$G22,$E:$E,TEXTSPLIT(I$21,","))이 됩니다.
CountIfs 함수는 And 조건이지만, TextSplit을 사용하면 지역명은 Or 조건이 됩니다.

 
따라서, 0,0이라고 값이 나왔습니다.
i22셀의 채우기 핸들을 아래로 끌면 홍길동은 1,1, 김주바리는 6,2가 나왔습니다.

 
원데이터를 살펴보니 홍길동은 수원 건이 1, 용인 건이 1건 맞고,
김주바리의 경우도 수원 건이 6, 용인 건이 2건 맞습니다.
 
따라서, 이제 Sum을 하면 됩니다.
=SUM(COUNTIFS($C:$C,$G22,$E:$E,TEXTSPLIT(I$21,",")))

 
1+1=2, 6+2=8 맞습니다.
 

나. SumProduct 이용

=SUMPRODUCT(($C:$C=$G22)*($E:$E=H$21))
SumProduct는 배열을 입력하는데, (담당자=성명)*(지역=지역명)으로 입력하면 참 개의 합이 구해집니다.

 
 

3. 해법 2 : Substitute 함수 이용

 

가. Sum + CountIfs 이용

Susttitute 함수를 이용해 쉼표를 공백 50개로 나누면
첫 번째 문자는 1부터 50개 사이에 있고,
첫 번째 쉼표 이후의 두 번째 문자는 51에서 100 사이, 다시 말해 51에서 50개의 범위 내에 있으면,
세 번째 문제는 101부터 50개의 범위 내에 있습니다.
 
=SUBSTITUTE(K21,",",REPT(" ",50))의 결과 다시 말해 쉼표를 50개의 공백으로 바꾸면 아래와 같이 안양 다음에 공백이 50개 있고, 과천 다음에 공백 50개가 있고, 의왕이 나오게 됩니다.

 
이것을 1부터 50개, 51부터 50개, 101부터 50개 추출하도록 Mid함수를 이용해 표현하면
=MID(SUBSTITUTE(K21,",",REPT(" ",50)),{0,1,2}*50+1,50)

 
안양, 과천, 의왕이 표시되는데, 앞뒤에 공백이 있습니다.
따라서, Trim 함수로 좌, 우 공백을 제거하면 됩니다.
=TRIM(MID(SUBSTITUTE(K21,",",REPT(" ",50)),{0,1,2}*50+1,50))
 
화면에 보이는 것은 아래와 같은데,

 
위 수식을 마우스로 끌어서 범위로 잡고 F9키를 누르면
{"안양","과천","의왕"}이라고 원하는 값으로 표시됩니다.

Esc키를 눌러 원래 수식으로 돌립니다.
 
그리고, TextSplit함수를 이용할 때와 똑같이 Sum+CountIfs함수를 연결하면 아래와 같습니다.
=SUM(COUNTIFS($C:$C,$G22,$E:$E,TRIM(MID(SUBSTITUTE(K$21,",",REPT(" ",50)),{0,1,2}*50+1,50))))

 
 

나. SumProduct 이용

=SUMPRODUCT(($C:$C=$G22)*($E:$E=TRIM(MID(SUBSTITUTE(J$21,",",REPT(" ",50)),{0,1,2}*50+1,50))))
TextSplit함수를 이용할 때보다 엄청 복잡합니다.
이래서 새로운 함수를 사용해야 하는 것이겠지요?
 

 

두가지 조건 or(완성).xlsx

0.02MB