중복된 텍스트 제외하고 고유 텍스트 개수 세기

네이버 지식인에서 알게 된 좋은 수식을 소개합니다.

이런 식으로 A1:D2 범위 내에 사람 이름이 중복된 것 포함해서 여러개 있습니다.

이때 중복된 이름은 제외하고 모두 몇 명의 이름이 있는지 알고 싶을 때 쓰는 수식입니다.

수식은 위와 같습니다. 

일단, 이 수식의 기본적인 구조는, 각 사람의 이름이 나올 때마다 그 자리에 전체 범위에서 그 사람의 이름이 나오는 횟수를 분모로, 1을 분자로 한 값을 배정하여, 나중에 그 모든 숫자를 합하는 것입니다.

간단한 예를 들어서, 다음과 같이 이름이 6개가 배열되어 있다고 합시다.

홍길동,장길산,홍길동,홍길동,홍길동,장길산

그럼 이름 대신에 그 자리에 다음 숫자를 배당합니다.

0.25, 0.5, 0.25, 0.25, 0.25, 0.5

홍길동은 총 4번 나오니 1/4해서 0.25를 홍길동이 나오는 자리마다 배정합니다.

장길산은 총 2번 나오니 1/2해서 0.5를 장길산이 나오는 자리마다 배정합니다.

이제 모든 숫자를 더하면 0.25+ 0.5+ 0.25+ 0.25+ 0.25+ 0.5 = 2 

그래서 중복된 이름을 제외하고 모두 2명의 이름이 있다는 결과가 나옵니다.

이제 수식을 살펴보겠습니다.

=SUMPRODUCT((A1:D2<>"")/(1-(A1:D2<>"")+COUNTIF(A1:D2,A1:D2)))

(엑셀에서 수식-수식분석-수식계산을 하시면 단계별로 수식이 어떻게 계산되는지 볼 수 있으므로 수식을 이해하는데 도움이 됩니다. 

밑줄이 쳐진 부분은 이번 단계에서 계산이 될 예정이라는 것을 의미하고

이탤릭으로 표시된 부분은 바로 이전 단계에서 계산이 된 결과라는 것을 보여줍니다)

이 부분은 A1:D2 범위에서 공백이 아닌, 즉 이름이 있는 셀을 골라내는 것입니다.

그래서 이름이 들어있으면 TRUE, 공백이면 FALSE가 배정됩니다. 나중에 TRUE는 1로, FALSE는 0으로 계산됩니다.

이부분도 마찬가지로 이름이 있는 셀을 골라냅니다

위와 마찬가지로 이름이 들어있으면 TRUE, 공백이면 FALSE가 배정됩니다. 

1에서 위의 결과값을 뺍니다. 

위의 작업을 하고 나면  TRUE이면(이름이 있으면) 0을 배정하고 FALSE이면 (공백이면) 1이 배정됩니다

방금 한 작업 (1에서 빼는 작업)을 하는 이유는, 나중에 이 부분이 분모가 되기때문에, 분모에 0을 만들지 않기 위해서입니다. 공백인 부분은 분자가 0이 될 것이기에, 분모도 0이 되면 에러가 납니다. 그래서

1-(A1:D2<>"") 

이 수식은 분모에는 0을 만들지 않기 위해서 추가한 것입니다.

countif함수는 평소라면 

countif(범위,조건) 처럼 사용해야합니다. 둘 다 범위를 인수로 주면 오류가 나서 결과값이 0이 됩니다.

하지만 지금은 sumproduct함수내에 들어있기 때문에, 인수 둘 다 범위를 주게 되면 범위끼리 비교해서, 두번째 범위에의 각 위치에 있는 셀값이 첫번째 범위안에 들어있는 개수를 각 위치에 배당한 배열(범위)을 반환합니다.

즉, 위에서처럼 A1:D2를 countif함수에 두 개의 인수로 다 준 수식 countif(A1:D2,A1:D2)의 결과는 다음과 같은 의미입니다.

{홍길동의 개수, 임꺽정의 개수, 임꺽정의 개수, 공백; 임꺽정의 개수, 홍길동의 개수, 공백, 임꺽정의 개수}

(가운데에 세미콜론 ";" 이 들어가 있는 것은 A1:D2의 범위가 가로로 4열이기때문입니다. 다섯번째 값부터는 다음 행으로 넘어가는 범위라는 것을 알려줍니다)

분모를 0으로 만들지 않기 위해 위에서 계산했던 1-(A1:D2<>"") 수식의 결과와 방금 전에 계산한 각 이름의 개수가 들어있는 범위를 서로 더하면 "2,4,4,1;4,2,1,4"의 값을 가진 범위가 됩니다.

이제 분자와 분모를 연결하여 계산을 하게 됩니다. TRUE는 1, FALSE는 0으로 계산이 된다고 했지요

 즉, "1/2, 1/4, 1/4, 0/1; 1/4, 1/2, 0/1, 1/4" 의 결과가 되어 2라는 답을 도출할 수 있습니다.

다시 한번 위 수식을 말로 요약하면, 

홍길동은 총 2번 나오니 1/2을 배정하고, 임꺽정은 총 4번 나오니 1/4를 배정한 뒤, 해당 이름이 나오는 횟수만큼 그 수를 더해주면 홍길동은 1/2+1/2로 1이 되고, 임꺽정은 1/4+1/4+1/4+1/4로 1이 되어, 중복된 이름을 모두 1로 계산하게 됩니다.