엑셀 여러 열 고유값 추출 공식
엑셀 여러 열 고유값 추출 목차 바로가기
함수 공식
=INDIRECT(TEXT(MIN(IF(($범위<>"")*(COUNTIF($머릿글:머릿글,$범위)=0),ROW($범위)*100+COLUMN($범위),1024^2)),"R0C00"),0)&""
'배열수식이므로 M365 이전버전 사용자는 Ctrl + Shift + Enter로 입력합니다.
'배열수식이므로 M365 이전버전 사용자는 Ctrl + Shift + Enter로 입력합니다.
인수 설명
| 인수 | 설명 |
| $범위 | 고유값으로 추출할 값이 입력된 범위입니다. 자동채우기 되는 수식이므로 반드시 절대참조 형태로 입력하는 것에 주의합니다. 범위로 여러개의 열을 선택할 수 있으며, 범위는 반드시 연속된 범위여야 합니다. 예) A1:C30 (O) | A1:C30, D1:E30 (X) |
| $머릿글:머릿글 | 고유값을 추출할 범위의 머릿글을 확장범위로 입력합니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 여러개 열 고유값 추출 공식예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 여러 열에서 고유값 추출하기 공식 알아보기
공식 설명
본 공식은 여러 범위에서 중복된 값은 제외하고 고유값만 추출하는 엑셀 함수 공식입니다. 배열수식이므로 M365 이전버전 사용자는 Ctrl + Shift + Enter 으로 입력합니다.
공식을 사용하면 수식을 입력한 셀을 시작으로 아래쪽 방향으로 고유값을 추출합니다. 그리고 수식을 입력한 셀을 시작으로 아래로 한칸씩 범위를 확장하며 고유값이 올바르게 추출되었는지 확인하므로, 수식을 입력하는 셀 위에는 반드시 머릿글이 있어야 합니다. 머릿글은 고유값으로 출력될 값과 달라야합니다. 예를 들어, 고유값으로 출력될 값 중에 '사과'가 있을 경우, 머릿글은 '사과'와 다른 값이여야만 공식이 올바르게 동작합니다.
공식의 동작원리
- 선택한 범위의 값이 빈칸이 아니고 (=값이 존재하고), 머릿글부터 시작한 확장범위를 보았을 때 기존에 출력된 고유값이 아닌 경우 TRUE를 반환합니다.
=(범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0)
- 만약 위 조건의 값이 TRUE일 경우 (=즉, 범위의 값이 빈칸이 아니면서 기존에 고유값으로 출력된 값이 아닐경우), 해당 셀의 행번호*100+열번호를 반환합니다. 그렇지 않을 경우 1,048,576 (1024^2, 행번호 최대값)을 반환합니다.
=IF((범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0),ROW(범위)*100+COLUMN(범위),1024^2)
- IF함수로 반환된 배열에서 가장 작은값을 선택합니다.
=MIN(IF((범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0),ROW(범위)*100+COLUMN(범위),1024^2))
'만약 범위에서 값이 빈칸이 아니면서 이전에 고유값으로 출력된 값이 아닌 값의 셀주소가 A3일 경우, 함수는 [ 1*100+3 = 103 ] 을 반환합니다. - 반환된 숫자는 TEXT 함수를 통해 셀 주소 형식으로 변환됩니다.
=TEXT(MIN(IF((범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0),ROW(범위)*100+COLUMN(범위),1024^2)),"R0C00")
- INDIRECT 함수로 반환된 셀 주소를 참조합니다.
=INDIRECT(TEXT(MIN(IF((범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0),ROW(범위)*100+COLUMN(범위),1024^2)),"R0C00"),0)
- 값이 빈칸일 경우 0 대신 빈칸이 반환되면서 공식이 마무리됩니다.
=INDIRECT(TEXT(MIN(IF((범위<>"")*(COUNTIF($머릿글:머릿글,범위)=0),ROW(범위)*100+COLUMN(범위),1024^2)),"R0C00"),0)&""
- 선택한 범위의 값이 빈칸이 아니고 (=값이 존재하고), 머릿글부터 시작한 확장범위를 보았을 때 기존에 출력된 고유값이 아닌 경우 TRUE를 반환합니다.
"R0C00" 작동원리가 궁금하네요. 수고하셨습니다
다른 시트의 범위를 참조하시려면,
=INDIRECT("시트명!"TEXT(MIN(IF(($범위<>"")*(COUNTIF($머릿글:머릿글,$범위)=0),ROW($범위)*100+COLUMN($범위),1024^2)),"R0C00"),0)&""
으로 수식을 수정해서 사용해보세요
시트 명에 공백이 있을 경우 작은따옴표로 함께 묶어주셔야 합니다.
이렇게 입력해보시겠어요?^^
연속되지 않은 범위에서 추출해야 할 경우, M365 기준 VSTACK 함수를 사용하면 가능합니다. 만약 M365 이전 버전일 경우, 아쉽게도 함수만으로 해결하는 건 불가능해서 파워쿼리 등 다른 기능을 함께 사용해야만 가능합니다.
1. CONCATENATE나 &를 이용하여 A열과B열을 합쳐서 보조표를만든다
ex)사과 서울
2. 만든보조표에서 고유값을 구한다
3. LEFT,RIGHT,FIND,LEN등으로 다시놔눠준다
만약 2021 이후 버전을 사용 중이시라면 함수로 쉽게 가능하겠으나, 2019 이전 버전이라면 함수만으로는 어려울 것 같습니다. 파워쿼리와 중복값제거 기능등을 사용해보는 것을 검토해보세요 :)
2021 버전을 사용중이시라면 엔터키로 수식을 입력해보세요.