'배열 수식'에 해당되는 글 1건

  1. 2007.07.06 엑셀에서 중복되지 않는 항목 개수 구하기 (2)

아래와 같은 자료가 있다고 합시다. '품명'이 A1이라고 치죠. 이때 중복되지 않는 '종류'가 몇 개인지 궁금하다면? 즉, 중복 레코드를 카운트하지 않고 고유한(unique) 개수를 알고 싶다면?

품명 종류 가격 원산지
캐맛있는배 10000 서울시구석탱이
둘이먹다하나죽는사과 사과 5000 경기도사과동
저를먹어주세요 바나나 7000 아프리카울랄라시
맛있는바나나 바나나 6000 제주도제주시
정말맛있는딸기 딸기 5000 전남광양
자이로딸기 딸기 6000 미국NY
니뽄삘딸기 딸기 4000 일본서귀포
캔으로먹는사과 사과 19000 캐나다
악마같은사과 사과 9000 독일브레멘
혼자만드센 딸기 8000 독일브뤼셀

뭐 항목(레코드 또는 행)이 적다면 육안으로도 가능하겠지만; 만약 항목이 천개가 넘어간다면? 게다가 앞으로도 항목은 추가될 가능성이 언제라도 있고 그때마다 필터 따위의 수작업 없이 중복되지 않는 종류를 세고 싶다면?

여러 방법이 가능합니다.

위에서도 말했지만 고급필터를 이용하는 방법이나 피벗 테이블을 이용하는 방법도 있습니다만, 수작업이 필요한 방법은 제외하도록 하죠. 가라(-_-)로 엑셀을 사용하는 프로그래머(바로 저 같은;)라면 VBA를 떠올릴 겁니다. 하지만 VBA도 제외하도록 하죠.

내장함수만으로 이 문제를 해결하는 방법이 있습니다. 하지만 배열수식을 이용해야 합니다. 이것만은 피할 수 없죠. 보니까 오피스2007의 도움말에도 여러가지 방법이 소개되어 있더군요. 가장 쉬운 방법으로 frequency 함수를 이용하는 방법이 있지만 이 방법은 숫자 자료에만 사용할 수 있다는 한계가 있습니다. 물론 이를 응용한 방법들 또한 소개되고 있습니다. 도움말에서 소개하는 중복 값이 있는 범위에서 고유 값 개수 구하기를 보면 아시겠지만, 이 방법은 다소 복잡한 감이 있습니다. 원리는 간단하지만 수식이 복잡해진다는 의미에서요.

이보다 훨씬 간단한 방법이 있습니다. 한 네이버 카페에서 발견한 방법입니다. 위의 자료를 예로 든다면 만약 그냥 counta 함수를 사용한다면 중복된 '종류'를 포함하여 10이 나올 것입니다. 하지만 자료 범위 외의 아무 셀에나 =SUM(1/COUNTIF(B2:B11,B2:B11))라고 배열수식을 입력해보세요. 배열수식을 입력하기 위해서는 앞의 수식을 적고 ctrl + shift + 엔터를 쳐야 합니다. 그러면 간단하게 원하던 결과인 4가 나오죠.

사실 원리는 간단합니다. 중복되는 항목의 경우 countif 함수의 결과로 1보다 큰 값(x라고 합시다)이 나오게 되는데요, 이 x가 x개 있으므로 (1/x) * x = 1로 계산하게 만드는 방법입니다.

궁금하신 분들은 해당 셀에서 수식>수식 계산(오피스 2007 기준) 해보면 어떻게 계산이 되는지 볼 수 있습니다. 제가 아는 한 가장 간단한 방법이 아닐까 싶습니다. 필터도 피벗도 VBA도 다 필요없습니다. 얼마나 간단합니까.



Posted by 필유