엑셀 중복값을 찾거나 제거 또는 추출하는등의 수식을 작성하기에는 문서 서식이 사용자의 환경에 따라 사용해야 하는 함수가 달라지거나 추가, 또는 수식으로 작성된 계산식을 수정해야 하는 경우도 있습니다.
이번 글에서는 엑셀 중복값을 추출하는 수식으로 “중복값 제거 후 조건을 만족하는 고유 개수는 몇개인ㄴ가?”라는 질문으로 수식을 작성해 보도록 하겠습니다.
엑셀 중복값과 관련하여 다양한 형태의 서식에서 적용할 수 있는 수식을 참고하여 활용한다면 여러분들의 업무 효율이 증대될수 있을것입니다.
조건을 만족하는 고유값 개수는 몇개인가?
위 예제 이미지에는 A열 날짜, B열 품명, C열 금액 데이터를 입력했을때 E열의 날짜에 해당하는 품명의 고유 개수는 몇개인가를 계산하여 결과값으로 반환하고자 합니다.
중복 값 제외 고유 개수와 같은 키워드로 인터넷에 검색을 해보면 Sumproduct함수로 수식을 작성해서 고유 개수를 결과값으로 반환하는 수식으로 대부분 찾을 수 있습니다.
위와 같은 예제 데이터에서 F3셀에 E3셀에 작성된 날짜의 품명을 중복된 개수가 아닌 고유 개수는 A-2회, B-1회, D-1회로 A, B, D 3종의 품명을 고유 개수로 결과값을 가져올 수 있습니다.
SUMPRODUCT함수 수식.
Sumproduct함수로 수식을 작성하면 다음과 같이 작성할 수 있습니다.
| =SUMPRODUCT(($A$3:$A$11=$E3)*1/COUNTIFS($B$3:$B$11,$B$3:$B$11,$A$3:$A$11,$A$3:$A$11)) |
그러나 Sumproduct함수로 수식을 작성했을 경우 범위 안에 빈 공란이 있을 경우 #DIV/0!와 같은 오류로 비어있는 셀을 참조하는 수식의 오류값을 반환하게 됩니다.
따라서 참조 범위 안에 비어 있는 셀이 있는 경우에는 Sumproduct함수 수식으로는 조건을 만족하는 고유 개수를 결과값으로 가져올 수 없게 됩니다.
INDEX MATCH함수 수식.
Sumproduct함수 수식과는 다르게 Index match함수로 고유 개수를 결과값으로 반환하는 수식을 작성하게 되면 참조 범위 안에서 비어 있는 셀이 있더라도 조건을 만족하는 고유 개수를 결과값으로 반환할 수 있습니다.
| =COUNT(INDEX(MATCH(ROW($B$3:$B$10001)-2,INDEX(MATCH($B$3:$B$10001,$B$3:$B$10001,)*($A$3:$A$10001=E3),),),)) |
위 수식으로 작성하고 A,B,C열 데이터값 일부를 삭제하더라도 원하는 중복값을 1개로 하는 고유 품명 개수를 결과값으로 반환할 수 있습니다.
글을 마치며…[행아아빠 생각]
중복된 데이터를 1개로 고유 개수를 결과값으로 반환 하는 함수는 Sumproduct함수와 Index, match함수 조합으로 계산 수식을 작성 할 수 있습니다.
본문에서는 Sumproduct함수로 수식을 작성하는 경우 비어 있는 셀이 있는 경우에는 오류값을 반환한다고 설명했지만 반듯이 그런것만은 아닐것이고, 참조 범위안에서 계산되어야 하는 비어 있는 셀들의 개수를 계산식에 넣을 수 있으면 될것입니다.
하지만 이렇게 수식을 작성하는 경우 수식이 다소 복잡해 질 수 있기 때문에 Index, Match함수 조합으로 수식을 작성하는 방법도 함께 알아두면 좋을것입니다.
Sumproduct함수와 Index, Match함수는 굉장히 다양한 형태에서 유용하게 사용할 수 있는 함수로 기본 수식 구문에서 조금만 응용해서 수식을 작성할 수 있다면 다양한 형태의 서식에서 원하는 계산 수식을 사용할 수 있을것입니다.
