[Tip] 대소문자 구분해서 카운트하기

   countif()함수의 한계

위 이미지를 살펴보면 A열에 'abc'가 2개, 'ABC'가 1개가 있습니다. 소문자 'abc'만 카운트 하고 싶은데 대문자 'ABC'까지 같이 카운트 되는 상황이죠. 이는countif()함수에서는 대소문자를 구분해서 카운트하는 기능이 없기 때문입니다. 당연히 같은 기능을 하면서 조건만 추가된 countifs()문 역시 제대로 대소문자를 구분해서 계산하지 못합니다.

 

 

   sumproduct(), exact() 함수를 이용한 대소문자 구분하여 카운트하는 함수

=sumproduct(exact("abc", a:a))

 

   함수 설명

exact() 함수와 sumproduct() 함수를 이용해서 대소문자를 구분한 검색이 가능합니다. sumproduct()도 익숙한 함수는 아닌데 exact()함수는 더 생소합니다. exact()함수는 두 문자열이 동일한지 비교해주는 함수입니다. 대소문자까지 일치하는지 비교해주죠. "abc"="ABC"라는 수식은 TRUE라고 계산됩니다. 하지만 exact()함수에서는 FALSE라고 계산되죠. 대소문자까지 비교했기 때문입니다.

이처럼 exact()함수는 단일 값들을 서로 비교해주는 함수입니다. 그래서 비교대상에 단일값 뿐 아니라 범위(배열)를 넣어도 범위를 집계하지 않고 범위 내 단일값들과 비교하도록 작동됩니다. =exact("abc",A:A) 이런 식으로 각각의 A열 값들에 대해 "abc"라는 키워드에 매칭되는지 비교하는 함수를 작성할 수 있습니다. 여기에 arrayformula()함수를 결합하여 배열로 출력할 수 있습니다.

그럼 이제 마지막으로 원하는 것은 저 리스트에서 True인 값의 수를 계산하는 방법입니다. countif(), filter(), query() 등 다양한 방법이 있겠지만 sumproduct()를 이용하면 가장 직관적으로 함수를 작성할 수 있습니다.

 

sumproduct()함수는 각 행에 있는 숫자들을 곱한 후 열단위로 합을 계산해주는 함수입니다. 대표적으로 "각 상품의 (가격 * 판매수량)의 합"을 통해 총판매액을 구할 때 사용됩니다. sumproduct()는 작동과정에서 내부 인자를 배열 형태로 변환해서 계산해줍니다. 그래서 "=exact("abc", A:A)"함수에 arrayformula()함수를 적용하지 않고 내부인자로 넣어도 됩니다.

 

이 때, sumproduct()함수에는 인자 하나, 즉, 배열 하나만 들어갔기 때문에 결과적으로 배열에서 한 열에 대해서만 총합이 계산됩니다.

 

구글 스프레드시트에서는 TRUE, FALSE가 각각 1, 0으로 참/거짓형에서 숫자형으로 강제 형변환이 이루어집니다. sumproduct()는 결과적으로 TRUE로 계산된 모든 1들을 더하여 총 개수를 계산하게 됩니다.

 

참고로 MS오피스 엑셀에서는 TRUE, FALSE에 대해 강제 형변환이 이루어지지 않아 의도했던 결과가 나오지 않습니다. exact()함수 앞에 --를 붙여주면 TRUE, FALSE를 각각 1, 0으로 변환해줍니다. 그래서 엑셀에선 아래와 같이 써야 의도했던 결과가 나옵니다.

=sumproduct(--exact("abc", a:a))