왼쪽 자료를 기준으로 오른쪽 양식으로 집계하는 것을 해보겠습니다.
1. 구문
CountIfs함수의 구문은 아래와 같고,
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
SumProduct함수의 구문은 아래와 같습니다.
=SUMPRODUCT(array1, [array2], [array3], ...)
2. 위 경우에 적용
가. 첫 번째 표 계산하기
(1) SumProduct 함수를 이용
SumProduct함수는 array가 연속되므로
배열함수의 경우와 같이 and는 *, or는 + 기호를 사용하여 조건을 걸 수 있습니다.
따라서, H2셀의 수식은
=SUMPRODUCT(($C$2:$E$5=H$2)*($B$2:$B$5=$G3))과 같이 작성할 수 있습니다.
($C$2:$E$5=H$2)는 C2셀에서 E5셀까지(셀 병합이 돼서 C에서 E가 된 것임) 중 LG전자인 경우 True, 1을 반환합니다.
($C$2:$E$5=H$2)를 마우스로 끌어서 범위를 지정한 다음 F9키를 누르면 LG전자가 첫 번째와 10번째에 있기 때문에 첫 번째와 10번째만 True가 반환됩니다.
*는 And 조건이므로 앞의 조건과 뒤의 조건이 모두 충족돼야 합니다.
($B$2:$B$5=$G3)는 B2셀에서 B5셀 중 의장인 경우 True, 1을 반환합니다.
($B$2:$B$5=$G3)를 마우스로 끌어서 범위를 지정한 다음 F9키를 누르면 첫 번째와 세 번째만 True가 반환됩니다.
이제 SumProduct 안의 것을 모두 선택한 후 F9키를 누르면
첫 번째만 True와 True이기 때문에 True가 반환되고, 나머지는 False가 반환됩니다.
따라서, SumProduct 함수의 결과로 1이 반환되는 것입니다. 원래대로 수식으로 돌리기 위해 Esc키를 누릅니다.
그리고, H2셀은 H$2로 한 것은 오른쪽으로 이동할 때 열이 변해야 하기 때문에 H에는 $를 붙이지 않고,
내려갈 때는 행이 고정돼야 하기 때문에 2에는 $를 붙인 것이고,
$G3로 한 것은 오른쪽으로 이동하더라도 G열은 바뀌면 안 되기 때문에 $를 붙여 고정한 것이고,
내려갈 때는 행이 변하도록 $를 붙이지 않은 것입니다.
이제 H2셀의 채우기 핸들을 오른쪽으로 끈 다음, 아래로 끌면 i4셀까지 수식이 모두 채워지고, 값이 모두 1로 구해집니다.
(2) CountIfs 함수를 이용
조건은 같으므로 H3의 수식을 복사한 후 H8셀에 붙여 넣고, 수식을 수정하는데, 함수명을 CountIfs로 수정하고, CountIfs함수의 경우는 Criteria_range와 Criteria를 컴마로 구분해야 하므로
=COUNTIFS($C$2:$E$5,H$2,$B$2:$B$5,$G8)로 수정하면 1이란 값이 나오지 않고, #VALUE! 에러가 발생합니다.
이리저리 해보니 조건 범위가 앞은 12개이고, 뒤는 4개여서 그런 것입니다.
$C$2:$E$5를 $C$2:$C$5로 수정하니 원하는 값 1이 나옵니다.
H8셀의 채우기 핸들을 오른쪽으로 끌로 i9셀까지 끌어 수식을 복사합니다.
나. 두 번째 표 계산하기
두 번째 표는 소속뿐만 아니라 이름까지 있어서 소속만을 비교해야 합니다.
(1) SumProduct 함수 이용하기
조건 범위와 조건의 주소가 다르므로 수정하면
H15셀의 수식 =SUMPRODUCT(($C$14:$E$17=H$14)*($B$14:$B$17=$G15))이 됩니다.
그런데 결괏값이 1이 아니라 0입니다.
따라서, 이름(소속)에서 소속만 찾는 것이므로 *를 이용해서 조건을 *기호를 사용해서 "*"&H$14&"*"로 수정하고, 조건 범위도 $C$14:$C$17로 C열만 지정해서 수식을
=SUMPRODUCT(($C$14:$C$17="*"&H$14&"*")*($B$14:$B$17=$G15))로 만들어도
결괏값이 여전히 0입니다.
SumProduct함수의 경우는 * 기호를 적용할 수 없습니다.
(2) CountIfs 함수 이용하기
=COUNTIFS($C$14:$C$17,"*"&H$19&"*",$B$14:$B$17,$G20)로 수식을 작성하니 *기호가 적용되어 LG전자가 들어간 경우 True가 되어 결괏값 1이 반환됩니다.
이번에는 수식 계산 과정을 알아보기 위해 수식 탭 > 수식 분석 그룹에서 수식 계산 명령을 누른 후 계산 버튼을 3차례 누르면 "*"&H$19&"*"가 "*LG전자*"이 됩니다.
조건 범위1이 조건1이고, 조건 범위2가 조건2인 경우의 수를 세는 것이 CountIfs함수인데,
*(LG전자만 일치하면 앞뒤는 어떤 문자가 와도 됨)가 적용되어 SumProduct함수와는 달리 결괏값이 제대로 반환됩니다.
=C14:C17="*LG전자*"라고 C14셀에서 C17셀 중 LG전자가 있는지 살펴보면 모두 False가 되는데 CountIfs는 True를 반환합니다. 이것이 SumProduct함수와 CountIfs함수의 차이점입니다.
H20셀의 채우기 핸들을 오른쪽, 아래로 끌면 수식이 모두 채워집니다.
'Excel' 카테고리의 다른 글
| 조건부 서식과 색 기준 필터(2) (2) | 2023.03.01 |
|---|---|
| 조건부 서식과 색 기준 필터(1) (0) | 2023.02.28 |
| 숫자로만 된 날짜+시간을 날짜, 시간 형식으로 바꾸기 (0) | 2023.02.26 |
| 색 기준 필터, 고급 필터, sum+if 배열 함수 (0) | 2023.02.23 |
| 두 가지이상 조건을 만족하는 값을 구해주는 ~Ifs 함수 (0) | 2023.02.22 |