Excel 에서 여러 열에서 고유한 값을 추출하는 방법은 무엇인가요？
Excel 에서 여러 열에 걸쳐 데이터를 자주 다루다 보면 동일한 열 내나 서로 다른 열 사이에서 일부 값이 중복되는 상황에 직면할 수 있습니다. 많은 보고서 작성 또는 데이터 분석 작업에서는 전체 선택 범위 내에서 한 번만 나타나는 고유한 값을 식별하고 추출해야 할 필요가 있습니다. 이를 수동으로 수행하면 시간이 많이 소요되고 특히 대규모 데이터셋이나 복잡한 테이블을 다룰 때 오류가 발생하기 쉽습니다. 다행히도 Excel 은 이러한 고유한 값을 효율적으로 추출할 수 있는 다양한 방법을 제공합니다。
이 가이드에서는 Excel 버전과 선호도에 따라 사용할 수 있는 여러 솔루션을 소개합니다。 예를 들어 모든 버전에 적합한 수식， 최신 버전용 동적 배열 수식， 간편한 결과를 위한 KUTOOLS AI Aide， 시각적 통합을 위한 PivotTable， 복잡한 시나리오에서 자동화된 추출을 위한 VBA 코드 등이 있습니다。
수식을 사용하여 여러 열에서 고유한 값을 추출하기
때로는 Excel 의 기본 제공 함수를 사용하여 이러한 추출을 수행하고자 할 수 있습니다. 이 섹션에서는 두 가지 접근 방식—모든 Excel 버전에 적합한 배열 수식과 Excel 365 및 Excel 2021 과 같은 최신 버전에서 사용 가능한 동적 배열 수식—을 사용하는 방법을 자세히 설명합니다。 이러한 방법은 직접적인 수식 기반 솔루션이 필요하거나 데이터 변경 시 자주 업데이트되어야 하거나 외부 애드인이나 코드를 피해야 하는 경우에 이상적입니다。
모든 Excel 버전에서 사용 가능한 배열 수식으로 여러 열에서 고유한 값 추출하기
모든 Excel 버전과 호환되도록 배열 수식을 사용하면 동적 배열을 지원하지 않는 Excel 에서도 여러 열에서 고유한 값을 추출할 수 있습니다。 이 방법은INDIRECT, TEXT, MIN, IF, COUNTIF, ROW, COLUMN함수를 조합하여 다양한 데이터 구조에 유연하게 대응할 수 있습니다。
데이터가A2:C9범위에 있다고 가정해 보겠습니다。E2셀부터 고유한 값을 추출하려면 다음 절차를 따르세요：
1.E2셀(또는 목록 배치 영역의 첫 번째 셀)을 클릭하고 다음 배열 수식을 입력하세요：
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>「」)*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&「」
- A2:C9는 고유한 값을 추출하려는 데이터 범위입니다。
- E1:E1은 첫 번째 출력 셀 바로 위의 셀을 가리키며， 이미 출력된 항목을 추적하는 데 필요합니다。
- $2:$9는 데이터의 행 참조이며，$A:$C는 열 참조입니다。 워크시트 레이아웃에 맞게 필요에 따라 조정하세요。
2.수식을 입력한 후 단순히 Enter 를 누르는 대신Ctrl + Shift + Enter를 동시에 눌러 배열 수식으로 확인하세요。 올바르게 입력되면 수식 표시줄에 수식 주위에 중괄호 {}가 나타납니다。 그런 다음E2셀에서 아래쪽으로 채우기 핸들을 드래그하세요。 더 이상 추출할 고유한 값이 없음을 나타내는 빈 셀이 나타날 때까지 계속 드래그합니다。 이 과정을 통해 모든 고유한 값이 대상 열에 표시됩니다。
- $A$2:$C$9： 고유한 값을 검사할 전체 셀 범위를 지정합니다。
- IF(($A$2:$C$9<>「」)*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>「」은 빈 셀을 무시하도록 보장합니다。
- COUNTIF($E$1:E1,$A$2:$C$9)=0은 아직 추출되지 않은 새 값만 포함되도록 합니다。
- 두 조건이 모두 참이면 해당 출력은 고유한 인덱스 번호를 생성하기 위해 셀의 행과 열을 기반으로 계산됩니다。
- 어느 한 조건이라도 거짓이면 수식은 실수로 선택되는 것을 방지하기 위해 매우 큰 숫자()7^8)를 반환합니다。
- MIN(...)： 가장 낮은 인덱스 번호를 식별하여 데이터 내 다음 사용 가능한 고유한 값의 위치를 효과적으로 찾습니다。
- TEXT(...,"R0C00")： R1C1 스타일을 사용하여 인덱스를 유효한 셀 참조로 변환합니다。
- INDIRECT(...)： 위에서 생성된 셀 참조를 데이터 범위의 값으로 변환합니다。
- &「」： 수식 결과가 텍스트로 처리되도록 강제하여 서식 관련 문제가 발생하지 않도록 보장합니다。
Excel 365, Excel 2021 및 최신 버전에서 수식을 사용하여 여러 열에서 고유한 값 추출하기
Excel 365 또는 Excel 2021, 혹은 최신 버전을 사용하는 경우 동적 배열 함수를 사용할 수 있어 여러 열에서 고유한 값을 추출하는 것이 더 간단하고 직관적입니다。UNIQUE및TOCOL함수를 사용하면 여러 열의 데이터를 결합하고 중복을 한 번에 제거할 수 있어 지속적으로 업데이트되거나 대규모 데이터셋을 다루는 경우 특히 유용합니다。
이 방법을 사용하려면 빈 셀(예：E2， 또는 결과를 표시하고자 하는 위치)을 선택하고 다음 수식을 입력한 후Enter를 누르세요：
=UNIQUE(TOCOL(A2:C9,1))Enter 를 누르면A2:C9범위의 모든 고유한 값이 수식 아래 셀로 자동으로 확장됩니다。 이 기능은 특히 효율적이며 원본 데이터가 변경될 때마다 출력이 동적으로 업데이트되어 수동 새로 고침 단계를 생략할 수 있습니다。
- TOCOL(A2:C9,1)： 여러 열의 값 범위를 하나의 열로 변환하면서 빈 셀을 자동으로 제거합니다。
- UNIQUE(...)： 각 값을 한 번만 추출하여 중복이 제거된 깔끔한 목록을 제공합니다。
KUTOOLS AI Aide 를 사용하여 여러 열에서 고유한 값 추출하기
더 간소화된 접근 방식을 원하시고 수작업 노력을 줄이고 싶으시다면 Kutools for Excel 의 KUTOOLS AI Aide 가 여러 열에서 고유한 값을 쉽게 추출하는 데 도움을 줄 수 있습니다. 이 방법은 수식에 익숙하지 않거나 수식 오류 위험을 피하고 싶은 경우 특히 유용합니다. KUTOOLS AI Aide 는 사용자의 지시를 해석하여 데이터를 자동으로 처리하므로 초보자와 몇 번의 클릭만으로 빠른 해결책을 원하는 사용자 모두에게 이상적입니다。
설치 후KUTOOLS AI>AI 도우미를 클릭하여 「KUTOOLS AI Aide」 창을 엽니다：
- 채팅창에 다음과 같이 요청을 입력하세요：「빈 셀을 무시하고 범위 A2:C9에서 고유한 값을 추출하여 결과를 E2부터 표시하세요:」
- "전송「을 클릭하거나 Enter를 누른 후 AI가 요청을 분석하면 」실행"을 클릭하여 실행하세요。 결과는 지정한 위치에 워크시트에 즉시 표시됩니다。
팁：이 솔루션은 데이터 추출 워크플로우가 다양하거나 자연어 처리 기능을 원할 때 매우 유용합니다。 원본 데이터가 완벽하게 일관되지 않은 경우 공백 셀이 포함되거나 필터링될 수 있으므로 AI 요청 세부 사항에 따라 목록 추출를 반드시 다시 확인하세요。
PivotTable 를 사용하여 여러 열에서 고유한 값 추출하기
PivotTable 는 시각적 도구로 작업하는 것을 선호하거나 고유 항목을 요약하거나 발생 횟수 계산과 같은 추가 분석을 원할 때 고유한 값을 추출하는 또 다른 편리한 방법입니다。 이 접근 방식은 수식이 필요 없어 간단하지만， 특히 관련 열의 머리글이 서로 다를 경우 약간의 설정 단계와 데이터 재정렬이 필요합니다。
PivotTable 를 사용하여 고유한 값을 추출하는 권장 절차는 다음과 같습니다：
1.데이터 왼쪽에 즉시 새 빈 열을 삽입합니다. 예를 들어 데이터가 B 열부터 시작하면 새 A 열을 삽입하세요。 이 조정은 범위 통합을 정확하게 보장하는 데 도움이 됩니다。
2.데이터셋 내 임의의 셀을 선택하고Alt + D를 누른 다음 빠르게P를 눌러 「피벗테이블 및 피벗차트 마법사」를 실행합니다。 마법사의 첫 번째 단계에서 “여러 통합 범위"를 선택하세요。 이를 통해 여러 열의 값을 하나의 요약된 필드로 결합할 수 있습니다。
3.다음을 클릭한 후 “내게 단일 페이지 필드 만들기"를 선택합니다。 이 단계는 고유한 값 추출을 위해 모든 데이터를 하나의 그룹으로 구성합니다。
4.다음 단계에서 전체 데이터 범위(새로운 빈 열 포함)를 선택하고추가버튼을 클릭하여 선택 항목을 「모든 범위」 목록에 추가한 후다음을 클릭합니다。
5.마법사의 마지막 단계에서 PivotTable 를 배치할 위치(새 워크시트 또는 기존 시트)를 선택한 다음마침을 클릭하여 PivotTable 보고서를 생성합니다。
6.새로 생성된 PivotTable 에서 「보고서에 추가할 필드 선택」 섹션의 모든 필드 선택을 취소하여 기본 보기에서 제거합니다。
7.마지막으로 「값」 필드를행영역으로 끌어다 놓습니다. 그러면 PivotTable 이 원본 다중 열 범위에서 고유한 모든 값을 단일 열로 깔끔하게 정리하여 표시합니다。
제한 사항：데이터를 사전에 정리해야 하며 원본 데이터셋이 업데이트되면 새로운 고유 값을 보기 위해 PivotTable 을 새로 고쳐야 합니다。
VBA 코드를 사용하여 여러 열에서 고유한 값을 추출하기
자동화된 추출이 필요하거나 크고 비정형적인 데이터셋을 처리해야 하는 경우 VBA(Visual Basic for Applications) 코드를 사용하면 빠르고 재사용 가능한 솔루션을 제공할 수 있습니다. 이 방법은 Excel VBA 편집기에 대한 기본적인 이해가 있거나 반복 작업에서 수작업을 최소화하고 싶은 사용자에게 이상적입니다. 또한 VBA 는 배열 수식보다 대용량 데이터를 더 효율적으로 처리할 수 있습니다。
1.Alt + F11을 눌러 VBA 편집기를 엽니다。 나타나는 「Microsoft Visual Basic for Applications」 창에서삽입>모듈을 클릭하여 새 모듈을 추가합니다。
2.새 모듈에 아래 코드를 붙여넣습니다：
VBA： 여러 열에서 고유한 값 추출하기
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3.F5를 눌러 코드를 실행합니다。 대화상자가 나타나면 데이터 범위을 선택하라는 메시지가 표시됩니다。 공백 셀이 포함된 모든 관련 열을 선택하세요。
4.확인을 클릭한 후 고유한 값을 출력할 위치를 묻는 또 다른 프롬프트가 나타납니다。 결과를 나열할 위쪽 셀을 지정하세요(예：E2).
5.확인을 클릭하면 매크로가 자동으로 실행됩니다。 지정한 위치부터 모든 고유한 값이 표시됩니다。
- 수식 사용 시#VALUE!또는#SPILL!와 같은 오류가 발생하면 범위를 확인하고 출력 영역이 비어 있는지 확인하세요。
- 데이터 범위의 숨겨진 행이나 병합됨를 항상 확인하세요。 이는 고유한 값 추출의 정확성에 영향을 줄 수 있습니다。
- 배열 및 동적 배열 수식은 변경 사항이 있을 때 자동으로 업데이트되지만， 고급 필터 및 PivotTable 솔루션은 수동으로 새로 고치거나 재실행해야 할 수도 있습니다。
- 반복적인 작업의 경우 일관성과 속도를 위해 VBA 를 사용하여 추출을 자동화하는 것을 고려해 보세요。
- 특히 복잡한 워크북에서는 대량 추출 또는 자동화 루틴을 적용하기 전에 반드시 데이터를 백업하세요。
관련 기사 더보기：
- 목록에서 고유값 및 구분값의 개수 계산하기
- 중복 항목이 포함된 긴 값 목록이 있고, 한 번만 나타나는 고유값 또는 열 내 전체 구분값의 개수를 계산하고자 한다고 가정해 보겠습니다(왼쪽 스크린샷 참조). 이 문서에서는 Excel 에서 고유값과 구분값을 효율적으로 계산하는 방법을 설명합니다。
- Excel 에서 조건에 따라 고유한 값 추출하기
- 예를 들어 열 A 의 특정 조건에 따라 열 B 에서 고유한 이름만 추출하여 스크린샷과 같은 결과를 얻고자 한다고 가정해 보겠습니다。 이 튜토리얼에서는 고유한 값을 추출할 때 조건을 적용하는 방법을 보여줍니다。
- Excel 에서 고유한 값만 허용하기
- 워크시트 열에서 고유한 항목만 허용하고 중복 입력 방지 값을 원하신다면, 이 문서에서는 Excel 에서 고유성 규칙을 적용하는 실용적인 기법을 소개합니다。
- Excel 에서 조건에 따라 고유한 값 합계 계산하기
- 예를 들어 인접한 열의 이름을 기준으로 「주문」 열에서 고유한 값만 합계를 계산해야 할 수도 있습니다(스크린샷 참조)。 이 문서에서는 고유성과 조건부 계산을 결합하는 방법을 논의합니다。
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약