엑셀 여러 열 고유값 추출 공식
2007지원 버전 자세히 보기
여러 열의 중복값을 제외해 고유값을 세로로 추출하는 공식입니다.
인수 설명
01Excel 2024·M365 공식
=UNIQUE ( TOCOL ( 범위, 1 ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02Excel 2007 이후 호환 공식
{=IFERROR ( INDIRECT ( TEXT ( MIN ( IF ( ( 범위<>"" ) * ( COUNTIF ( 머릿글범위, 범위 )=0 ), ROW ( 범위 )*100000+COLUMN ( 범위 ), 1048577*100000 ) ), "R0C00000" ), 0 )&"", "" )}인수 설명 자세히 보기
동작 원리
01Excel 2024·M365 공식
TOCOL 함수는 여러 열을 하나의 세로 배열로 펼치고 UNIQUE 함수는 중복값을 제거합니다.
Excel 2024·M365 공식의 예제 시트에서 E2:E8에 고유 지역이 표시되는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
TOCOL 함수가 여러 열을 세로로 펼칩니다
TOCOL 함수는 A2:C8을 행 기준으로 읽어 하나의 세로 배열로 만들고, 두 번째 인수 1에 따라 빈 셀을 제외합니다.
=TOCOL ( A2:C8, 1 )
= {"서울";"부산";"대전";"부산";"인천";"서울";"대구";"부산";"광주";"대전";"제주";"인천";"대구";"서울";"광주";"대전";"제주";"부산"}
빈 셀 세 개를 제외하고 행 기준으로 펼친 18개 값입니다. UNIQUE 함수가 중복값을 제거합니다
UNIQUE 함수는 펼쳐진 배열에서 처음 나타난 값만 남겨 일곱 지역을 반환합니다.
=UNIQUE ( TOCOL ( A2:C8, 1 ) )
= {"서울";"부산";"대전";"인천";"대구";"광주";"제주"}
E2:E8에 스필되는 일곱 고유값입니다. 02Excel 2007 이후 호환 공식
COUNTIF 함수로 이미 추출한 값을 제외한 뒤 MIN 함수가 첫 좌표를 고르고, TEXT 함수와 INDIRECT 함수가 해당 셀 값을 반환합니다.
Excel 2007 이후 호환 공식의 예제 시트에서 E2 셀이 서울을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
COUNTIF 함수가 추출할 셀을 판정합니다
빈 셀이 아니고 E1부터 직전 결과까지 한 번도 나오지 않은 셀은 1, 나머지는 0으로 계산합니다. E2 셀에서는 아직 추출한 지역이 없으므로 빈 셀 세 개만 0이 됩니다.
=( A2:C8<>"" ) * ( COUNTIF ( $E$1:E1, A2:C8 )=0 )
= {1,1,1;1,1,1;1,1,1;0,1,1;1,1,1;1,0,1;1,1,0}
A2:C8의 각 셀에 대응하는 7행×3열 판정 배열입니다. IF 함수와 MIN 함수가 첫 좌표를 고릅니다
조건을 만족하는 셀은 행 번호에 100,000을 곱한 뒤 열 번호를 더해 좌표 코드로 바꿉니다. 따라서 A3은 3×100,000+1인 300001이고, 100열 이상도 다음 행과 충돌하지 않습니다. 조건을 만족하지 않는 셀에는 워크시트의 최대 좌표 코드 104857616384보다 큰 104857700000을 넣습니다.
=MIN ( IF ( ( A2:C8<>"" ) * ( COUNTIF ( $E$1:E1, A2:C8 )=0 ), ROW ( A2:C8 )*100000+COLUMN ( A2:C8 ), 1048577*100000 ) )
= 200001
가장 앞선 대상 셀 A2의 좌표 코드입니다. TEXT 함수가 좌표를 R1C1 주소로 바꿉니다
TEXT 함수는 좌표 코드의 뒤 다섯 자리를 열 번호로 분리하고 앞부분을 행 번호로 사용해 R1C1 형식의 주소를 만듭니다.
=TEXT ( 200001, "R0C00000" )
= "R2C00001"
A2 셀을 가리키는 R1C1 주소입니다. INDIRECT 함수가 셀 값을 반환합니다
INDIRECT 함수는 두 번째 인수 0에 따라 R1C1 주소의 셀을 참조해 서울을 반환합니다. 더 이상 추출할 값이 없으면 대체값이 워크시트 밖 주소가 되고 IFERROR 함수가 빈 문자열을 반환합니다.
=IFERROR ( INDIRECT ( "R2C00001", 0 )&"", "" )
= "서울"
E2 셀의 첫 번째 고유값입니다. 
"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 버전을 사용중이시라면 엔터키로 수식을 입력해보세요.