1. 문제
아래와 같이 중복된 숫자가 있으면 중복을 제거해야 할 경우가 생깁니다. 이와 관련해서 다양한 경우를 다루고자 합니다.
2. 중복된 숫자의 개수 세기
=COUNTIF(A2:A9,A2:A9)라고 하면
A2셀에서 A9셀까지의 범위에서 같은 숫자의 개수를 구해줍니다.
Microsoft 365라 아래와 같이 보이는 것이지 낮은 버전이라면 B2셀에서 B9셀까지 선택한 후 수식을 입력하고 Ctrl + Shift + Enter 키를 눌러야 할 겁니다.
A10셀에 2를 추가하고, 수식을 A10셀까지 수정하면 2라는 숫자의 개수가 모두 3으로 변경됩니다.
엑셀이 구 버전이라면 수식을 모두 지우고 새로 수식을 입력해야 할 수도 있습니다. 그렇다면 수식을 먼저 복사해서 다른데 붙여넣고 작업하는 것이 안전합니다.
2. 순수한 숫자의 개수 세기
위의 경우 1, 2, 4, 5, 7, 8과 같이 중복된 수라도 한번만 세려면
B열의 수식을 1/로 수정해서
=1/COUNTIF(A2:A10,A2:A10)이라고 하면 1/중복 개수가 되므로 1이면 1이지만, 2라면 1/2=0.5가 되고, 1/3=0.333333이 됩니다.
따라서, 위 수식을 sum 하면 0.5+0.5 = 1, 0.333333*3 = 1과 같이 고유한 숫자의 개수가 구해집니다.
=SUM(1/COUNTIF(A2:A10,A2:A10))을 하니 개별적으로 표시되던 것이 합계값 하나로 6이라고 표시됩니다. 1,2,4,5,7,8이므로 6 맞습니다.
3. 순수한 숫자만 표시하기
순수한 숫자 1, 2, 4, 5, 7, 8을 구해보겠습니다.
가. 중복 숫자는 한 번만 표시하고, 두번째부터는 공란으로 표시하기
D2셀에 =IF(COUNTIF($A$2:A2,A2)=1,A2,””) 이라고 입력합니다.
아래로 내려가면서 해당 셀의 개수를 세서 1보다 크면 빈 셀로 만들려고 하는 것입니다. 첫 셀인 A2셀을 절대 참조로 하는 것이 중요합니다. 왜냐하면 항상 A2셀부터 현재 셀(상대 참조)까지의 현재 셀의 개수를 세려고 하는 것이기 때문입니다.
D2셀의 채우기 핸들(D2셀 오른쪽 아래 네모)을 더블 클릭하면 D10셀까지 수식이 채워지는데(복사),
1, 2, “”, 4, 5, “”, 7, 8, “”라고 숫자가 중복되면 빈 셀로 표시되므로 이 숫자 들을 결합하면 됩니다.
나. 숫자 결합해서 표시하기
(1) TextJoin 함수
TextJoin함수는 Delimiter를 지정해서 텍스트를 결합할 수 있기 때문에 Concat함수보다 훨씬 편리합니다.
=TEXTJOIN(“, “,,D2:D10)라고,
구분자로 ,를 지정하고, 빈셀을 무시하도록 두번째 인수는 입력하지 않고 통과하고, 세번째 인수로 결합할 텍스트의 범위를 지정하면 원하는대로 1, 2, 4, 5, 7, 8이 구해집니다.
(2) Concat 함수
Concat 함수는 구분자를 지정할 수 없고, 결합할 텍스트만 지정할 수 있어서
=CONCAT(D2:D10)라고 하면 124578이라고 숫자가 단순히 결합된 결과만을 반환합니다.
(대체 해법)
어렵지만 할 수 없는 것은 아닙니다.
먼저 숫자가 있을 경우는 숫자 뒤에 공백을 한 칸 추가한 다음
Concat으로 연결한 다음 Substitute 함수를 이용해 공백 한 칸을 쉼표 + 공백 한칸으로 대체하면 됩니다.
=SUBSTITUTE(CONCAT(IF(D2:D10<>””,D2:D10&” “,””)),” “,”, “)라고 입력하면
결괏값을 보니 필요없이 마지막에도 ,가 들어가 있습니다.
따라서, concat한 후 trim을 해서 빈 공백을 제거해줘야 합니다.
수정된 수식은
=SUBSTITUTE(TRIM(CONCAT(IF(D2:D10<>””,D2:D10&” “,””))),” “,”, “) 입니다.
원하는대로 마지막에 쉼표 없이 1, 2, 4, 5, 7, 8이 구해졌습니다.