하나라도 일치하는 건 수 세기

1. 문제

지역이 여러 개인데, 이에 해당하는 건수를 모두 세려고 합니다.

화성과 같이 하나만 있다면 CountIfs 함수로 간단하게 셀 수 있는데,

수원이거나 용인인 경우를 구하려면 CountIfs를 두 번 쓰던가 해야 하는데 한 번에 하는 방법을 찾으려고 하는 것입니다.

 

두가지 조건 or(문제).xlsx

0.01MB

 

아래는 CountIfs를 두 번 사용해서 홍길동이 화성이거나 용인인 건수의 계를 구한 것입니다.

 

그리고 또 하나의 문제는 수원,용인 또는 안양,과천,의왕이라는 지역명을 쉼표를 기준으로 구분하는 것입니다.

 

 

2. TextSplit 함수

TextSplit 함수의 구문은 

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])로서

첫 번째 인수는 문자, 두 번째는 열 구분자, 세 번째는 행 구분자, 네 번째는 빈 셀  무시 여부, 다섯 번째는 대/소문자 구분 여부, 여섯 번째는 텍스트를 나눈 결과가 2차원 배열일 때 누락된 값 대신 채울 값으로 네번째 인수가 False여야 합니다.

 

=TEXTSPLIT(I$21,",")

는 열 구분자, 여기서는 쉼표(,)를 기준으로 문자열을 분할한 후 배열 형태로 반환해 주므로

지역명이 "수원,용인"이라면 반환 값은 {"수원";"용인"}이 됩니다.

따라서, 수원 또는 용인인 경우의 처리가 가능합니다.

 

TextSplit 자리에 {"수원","용인"}이라고 수기로 입력해도 결과는 같지만,

일반화할 수가 없죠.

 

G22로 하면 0건이니

G23을 기준으로 구해보면 2건 맞습니다.

 

 

3. Substitute 함수

Substitute 함수는

수식은

TRIM(MID(SUBSTITUTE(I$31,",",REPT(" ",50)),50*{0,1,2}+1,50))

으로(배열 수식이므로 CSE로 입력),

 

SUBSTITUTE(I$31,",",REPT(" ",50))은 쉼표를 한 칸 공백 50개로 대체하는 수식이고,

 

MID(SUBSTITUTE(I$31,",",REPT(" ",50)),50*{0,1,2}+1,50)은 위에서 대체한 문자열을 50개씩 가져오는 것입니다.

위 수식은 배열이 0,1,2로 3개이지만, 지역명이 2개인 경우에는 세 번째 지역명이 공백으로 처리되기 때문에 문제없습니다.

 

그리고, 위 예에서는 쉼표가 최대 2개이므로 {0,1,2}라고 했는데 쉼표 개수가 늘어나면 {0,1 2 3,...} 등으로 수정하면 됩니다.

 

Trim 함수는 위와 같이 값을 가져오면 오른쪽 여백이 있기 때문에 오른쪽 여백을 제거하는 역할입니다.

여기까지 하면 {"수원","용인",""}이 구해집니다.

따라서, TextSplit 함수를 사용하는 것과 동일한 결과를 구할 수 있습니다.

 

4. SumProduct 함수를 Sum + CountIfs 함수로 변환

H22셀 : =SUM(COUNTIFS($C:$C,$G22,$E:$E,TEXTSPLIT(H$21,",")))

 

H32셀 : =SUM(COUNTIFS($C:$C,$G32,$E:$E,TRIM(MID(SUBSTITUTE(H$31,",",REPT(" ",50)),50*{0,1,2}+1,50))))

 

나머지 셀의 수식은 그대로 두었습니다.

 

두가지 조건 or(최종).xlsx

0.02MB