Skip to main content

Kutools for Office — 하나의 패키지. 다섯 가지 도구. 더 많은 일을 처리하세요.

Excel에서 고유한 값을 연결하는 방법은 무엇입니까?

Author Xiaoyang Last modified

스프레드시트 작업 중 열에서 고유한 값만 병합(연결)하거나, 고유 항목과 해당 레코드를 요약하는 목록을 작성해야 하는 상황을 자주 마주하게 됩니다. 중복 항목을 처리하고 요약 정보를 제시하면 데이터가 깔끔해질 뿐만 아니라 보고서도 더욱 명확하고 통찰력 있게 만들 수 있습니다. Excel에서는 내장 함수를 사용하거나 고급 추가 기능이나 사용자 정의 코드를 활용하여 이러한 목표를 달성할 수 있는 여러 가지 실용적인 방법이 있습니다. 이 튜토리얼에서는 고유한 값을 연결하고 관련 데이터와 함께 고유 항목을 나열하는 다양한 방법을 자세히 살펴봅니다. 다루는 솔루션은 다양한 Excel 버전과 사용자 선호도에 맞춰져 있어 여러분이 자신의 상황에 가장 적합한 접근 방식을 선택하는 데 도움이 됩니다.

열에서 고유한 값만 연결하기

고유한 값을 나열하고 대응되는 값을 연결하기

열에서 고유한 값만 연결하기

Excel 데이터 분석 중 하나의 빈번한 작업은 열의 고유 항목만 단일 셀로 병합하는 것입니다. 이는 요약 보고서를 작성하거나, 목록에서 중복된 값을 피하거나, 추가 처리를 위해 데이터를 준비하는 데 특히 유용할 수 있습니다. 사용하는 방법은 Excel 버전, 데이터셋 크기 및 수식이나 코드에 대한 익숙함에 따라 달라집니다. 다음 방법들은 각기 다른 요구 사항을 충족시키며, 각각의 고려 사항을 강조하고 올바른 실행을 보장하기 위한 실질적인 팁을 제공합니다.

방법 1: TEXTJOIN 및 UNIQUE 함수 사용하기

Excel365 및 Excel2021 사용자의 경우, TEXTJOIN 및 UNIQUE 함수의 도입으로 열의 고유한 값을 결합하는 것이 간단하고 유연해졌습니다.

데이터 열이 연속적이고 선택한 구분 기호를 사용하여 모든 고유 항목을 한 셀에 빠르게 통합하려는 경우 이 솔루션이 가장 적합합니다. 이 방법은 중복 항목을 자동으로 제거하며, 쉽게 감사할 수 있고 필요에 따라 범위나 구분 기호를 변경할 수 있습니다. 하지만 이 방법은 최신 Excel 버전에서만 사용 가능하며, 이전 버전에서는 UNIQUE 함수를 지원하지 않습니다.

결과를 표시하려는 셀에 다음 수식을 입력하세요 (데이터가 A2:A18 셀에 있다고 가정).

=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))

apply TEXTJOIN and UNIQUE functions to concatenate unique values

이 수식에 대한 설명:
  • UNIQUE(A2:A18)은 중복 항목을 필터링하고 A2:A18 범위에서 고유한 값만 반환합니다.
  • TEXTJOIN(", ", TRUE, ...)은 이러한 고유한 값을 쉼표와 공백으로 구분하여 한 셀로 결합합니다. TRUE 인수는 연결 과정에서 빈 셀을 무시하도록 합니다.

유용한 팁과 문제 해결:

  • 사용 중인 Excel 버전이 UNIQUE 및 TEXTJOIN 함수를 지원하는지 확인하세요. #NAME? 오류가 표시되면 이전 버전을 사용 중일 수 있습니다.
  • TEXTJOIN에서 사용하는 구분 기호는 필요에 따라 "; " 또는 "|" 등 원하는 것으로 변경할 수 있습니다.
  • 원래 범위에 데이터를 추가하거나 제거하면 수식이 자동으로 업데이트됩니다.
  • 의도치 않은 여분의 공백이나 구분 기호를 방지하려면 수식에서 구분 기호 인수를 다시 확인하세요.

방법 2: Kutools AI 도우미 사용하기

수식을 작성하지 않고 고유한 값을 연결하는 더 빠르고 완전히 자동화된 방법이 필요할 때, Kutools for Excel의 "AI 도우미" 도구는 모든 기술 수준의 사용자가 시간을 절약할 수 있는 실용적인 솔루션을 제공합니다. 이 방법은 Excel의 고급 수식에 익숙하지 않거나 데이터가 자주 변경되어 반복 작업이 필요한 경우 특히 유용합니다.

Kutools for Excel은 300개 이상의 고급 기능을 제공하여 복잡한 작업을 간소화하고 창의성과 효율성을 향상시킵니다. AI 기능과 통합되어 Kutools는 정확하게 작업을 자동화하며, 데이터 관리를 손쉽게 만듭니다. Kutools for Excel의 상세 정보...무료 체험하기...

Kutools for Excel 설치 후 "Kutools" > "AI 도우미"를 클릭하여 "Kutools AI 도우미" 창을 엽니다.

  1. 단일 셀로 결합하려는 값을 포함하는 셀을 선택하여 의도한 데이터와 일치하는지 확인하세요.
  2. 채팅 상자에 요구 사항을 설명하세요. 예를 들어 다음과 같이 입력할 수 있습니다:
    선택한 범위의 고유한 값을 쉼표로 연결하고 결과를 C2 셀에 결합하십시오.
  3. 눌러 Enter 키를 누르거나 "Send" 버튼을 클릭하세요. AI는 요청을 분석하고 처리 후 "Execute"를 눌러 Kutools가 작업을 수행합니다. 결과는 설명대로 반환됩니다.

참고 및 팁:

  • 모든 AI 기능에 액세스하려면 최신 Kutools 버전을 실행 중인지 확인하세요.
  • 최상의 결과를 위해 텍스트 명령을 구체적으로 작성하세요; 구분 기호와 대상 셀을 언급하세요.
  • Kutools AI는 대규모 범위 또는 다양한 데이터셋에서 반복적으로 수행해야 하는 워크플로에 특히 효율적입니다.

방법 3: 사용자 정의 함수 사용하기

고급 유연성이 필요하거나 사용자 정의 구분 기호가 필요하거나 여러 워크북에서 재사용 가능한 도구를 원하는 사용자는 VBA에서 사용자 정의 함수(UDF)를 코딩하는 것이 자동으로 고유한 값을 연결하는 효과적인 방법입니다. 이 VBA 솔루션은 모든 Excel 버전과 호환되며 새로운 함수의 가용성에 제한받지 않습니다.

  • 워크북에서 매크로를 활성화해야 합니다.
  • 이 VBA 코드를 계속 사용하려면 파일을 "매크로 사용 가능"(.xlsm)로 저장하세요.
  • 새 코드를 실행하기 전에 워크북을 정기적으로 백업하는 것이 좋습니다.

ALT + F11을 길게 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. VBA 창에서 Insert > Module을 클릭한 다음 아래 코드를 복사하여 붙여넣으세요:

VBA 코드: 고유한 값을 단일 셀로 연결하기:

Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
    Dim xCell As Range
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    For Each xCell In xRg
        xDic(xCell.Value) = Empty
    Next
    ConcatUniq = Join$(xDic.Keys, xChar)
    Set xDic = Nothing
End Function

3. 워크시트로 돌아가서 빈 셀(예: C2)에 다음 수식을 입력하세요:

=ConcatUniq(A2:A18,",")

Enter를 눌러 확인합니다. 셀에는 지정된 범위의 모든 고유한 값이 쉼표로 구분되어 표시됩니다.

concatenate unique values with vba code

  • 범위가 다른 경우 A2:A18을 적절히 조정하세요.
  • 다른 구분 기호가 필요한 경우 수식에서 ","를 원하는 기호(예: ";" 또는 |)로 바꾸세요.
  • #NAME? 오류가 발생하면 매크로가 활성화되어 있는지 확인하고 UDF 이름이 정확히 일치하는지 확인하세요.

팁: 이 함수를 다른 워크북에서 재사용하려면 해당 모듈에도 VBA 코드를 복사하세요.

방법 4: 고급 Excel 수식 사용하기 (대체 솔루션)

UNIQUE 함수를 사용할 수 없는 환경(예: Excel 2016 또는 Excel 2019)에서도 IF, COUNTIF 및 TEXTJOIN 함수 배열 수식을 복잡하게 조합하여 고유한 값을 연결할 수 있습니다. 이 방법은 작동하지만 계산 부담이 크므로 작은 데이터셋에 가장 적합합니다.

1. 대상 셀(예: C2)에 다음 배열 수식을 입력하세요 (입력 후 Ctrl+Shift+Enter를 누르세요):

=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))

2. 수식 주변에 중괄호 {}가 표시되면 배열 수식으로 정상적으로 입력된 것입니다. 수식은 쉼표로 구분된 A2:A18 범위의 연결된 고유한 값을 반환합니다.

참고: 이 방법은 데이터에 맞게 범위를 조정해야 합니다. 매우 큰 범위의 경우 계산 시간이 증가할 수 있습니다. 배열 수식에 익숙하지 않다면 위에서 설명한 VBA 또는 추가 기능 솔루션을 고려해보세요.

고유한 값을 나열하고 대응되는 값을 연결하기

데이터 보고서 작성 시 특정 열에서 고유한 값을 추출하는 것뿐만 아니라 다른 열에서 해당 항목을 집계하거나 결합하려는 경우가 많습니다. 예를 들어, 각 판매자의 판매 제품을 모두 통합하거나 동일한 ID와 연결된 모든 항목을 컴파일하는 경우입니다. 올바른 방법을 선택하는 것은 데이터의 복잡성과 자동화, 사용 용이성 또는 호환성을 우선시하는지에 따라 달라집니다.

방법 1: TEXTJOIN 및 UNIQUE 함수 사용하기

Excel 365 또는 Excel 2021을 사용 중이라면 UNIQUE 및 FILTER 함수를 TEXTJOIN과 결합하여 견고하고 완전히 수식 기반의 접근 방식을 사용할 수 있습니다. 이 방법은 하나의 값이 여러 레코드와 관련될 수 있는 데이터를 요약하고, 해당 레코드를 구분 기호로 구분된 목록으로 생성하는 데 적합합니다.

1. 빈 열에 다음 수식을 입력하여 Column A에서 모든 고유한 값을 나열하세요:

=UNIQUE(A2:A17)

List unique values with formula

2. 이제 각 고유 항목에 대해 Column B의 대응되는 값을 연결하려면 고유한 값 옆의 다음 열(예: D2에서 고유 값이 시작된다면 E2)에 다음 수식을 입력하고 필요에 따라 아래로 드래그하세요:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))

List unique and concatenate matched values with formula

이 수식에 대한 설명:
  • UNIQUE(A2:A17)은 Column A에서 고유한 항목의 배열을 생성합니다.
  • FILTER(B2:B17, A2:A17 = D2)는 D2의 각 고유 값에 대해 Column B에서 해당하는 모든 값을 포함하는 배열을 생성합니다.
  • TEXTJOIN(", ", TRUE, ...)은 쉼표로 구분된 대응되는 값을 결합합니다.
  • 다른 구분 기호가 필요한 경우 TEXTJOIN에서 ", "를 그에 따라 변경하세요.
  • 오류를 방지하려면 수식의 범위가 길이가 같고 FILTER가 누락된 일치 항목에 대해 오류를 반환하지 않는지 확인하세요.
  • 이 접근 방식은 데이터가 변경됨에 따라 결과가 자동으로 업데이트되므로 동적 요약 테이블에 적합합니다.

방법 2: Kutools for Excel 사용하기

Kutools for Excel에는 데이터를 고유한 값으로 그룹화하고 선택한 구분 기호로 대응되는 값을 병합하는 데 특화된 "고급 행 병합" 도구가 있습니다. 이는 수식이나 코드 작성이 익숙하지 않은 사용자를 위한 그래픽 솔루션으로, 특히 대량의 데이터세트를 처리하거나 주기적인 보고서나 지속적인 데이터 유지 관리에서 자주 재그룹화가 필요한 경우 가치가 있습니다.

Kutools for Excel은 300개 이상의 고급 기능을 제공하여 복잡한 작업을 간소화하고 창의성과 효율성을 향상시킵니다. AI 기능과 통합되어 Kutools는 정확하게 작업을 자동화하며, 데이터 관리를 손쉽게 만듭니다. Kutools for Excel의 상세 정보...무료 체험하기...

변경하기 전에 원본 데이터를 다른 위치에 복사하여 데이터를 백업하는 것이 좋은 습관입니다. 그런 다음 다음 단계를 따르세요:

  • 정리하려는 데이터 범위를 선택하세요.
  • 이동하세요 "Kutools" > "Merge & Split" > "고급 행 병합"아래 그림과 같이:
    click Advanced Combine Rows feature of kutools
  • 열리는 대화 상자에서:
    • 병합할 중복된 열을 선택하고 "Operation" 열에서 "Primary Key"로 설정하세요.
    • 병합하려는 열(연결할 값)을 선택하고 "Operation" 아래의 드롭다운 목록에서 원하는 구분 기호를 지정하세요.
    • OK를 클릭하여 실행합니다.
    specify the options in the dialog box

결과:

Kutools는 설정에 따라 데이터를 재정렬하고 고유한 항목을 추출하며 모든 관련 값을 연결합니다.
List unique and concatenate matched values with kutools

  • 실수를 했다면 Excel의 실행 취소 기능(Ctrl+Z)을 사용하여 되돌릴 수 있습니다.
  • 이 과정은 수백 또는 수천 개의 레코드를 포함하는 데이터세트에 적용되며 다양한 구분 기호를 지원합니다.

방법 3: VBA 코드 사용하기

VBA 스크립트를 사용하면 데이터를 추출하고 요약하는 방법을 완전히 제어할 수 있습니다. 이 접근 방식은 모든 Excel 버전과 호환되며, 특히 사용자 정의 워크플로, 자동화 또는 UNIQUE 또는 FILTER와 같은 함수를 사용할 수 없는 경우 적합합니다. 데이터 구조가 자주 변경된다면 이 VBA 솔루션은 쉽게 수정할 수 있습니다.

아래 코드를 사용하려면 다음 단계를 따르세요:

1. ALT + F11을 눌러 VBA 편집기를 엽니다.

2. Insert > Module로 이동한 다음 열린 모듈 창에 다음 코드를 붙여넣으세요:

VBA 코드: 고유한 값을 나열하고 대응되는 데이터를 연결하기

Sub test()
    'updateby Extendoffice
    Dim xRg As Range
    Dim xArr As Variant
    Dim xCell As Range
    Dim xTxt As String
    Dim I As Long
    Dim xDic As Object
    Dim xOutputRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "Does not support multiple selections", , "Kutools for Excel"
        Exit Sub
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
    If xOutputRg Is Nothing Then Exit Sub
    xArr = xRg
    Set xDic = CreateObject("Scripting.Dictionary")
    xDic.CompareMode = 1
    For I = 1 To UBound(xArr)
        If Not xDic.Exists(xArr(I, 1)) Then
            xDic.Item(xArr(I, 1)) = xDic.Count + 1
            xArr(xDic.Count, 1) = xArr(I, 1)
            xArr(xDic.Count, 2) = xArr(I, 2)
        Else
            xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
        End If
    Next
    xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub

3. F5를 눌러 스크립트를 실행합니다. 팝업 창에서 데이터 범위를 선택하라는 메시지가 표시됩니다. 첫 번째 열은 고유한 값, 두 번째 열은 대응되는 값으로 정확히 두 열을 선택했는지 확인하세요.

vba code to select data range

4. OK를 클릭하고 결과 테이블이 시작할 첫 번째 셀을 선택하세요.

vba code to select cell to put the result

5. OK를 클릭한 후 코드는 고유한 값과 이를 연결한 데이터만 포함하는 테이블을 생성합니다.

vba code to List unique and concatenate matched values

  • 열 수에 대한 오류가 발생하면 선택 항목에 정확히 두 열이 포함되어 있는지 확인하세요.
  • 쉼표를 다른 기호로 변경해야 하는 경우 xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2) 줄을 필요에 따라 수정하세요.
  • 새 VBA 스크립트를 실행하기 전에 항상 파일을 백업하세요.

요약하자면, Excel에서는 고유한 값을 연결하고 관련 데이터를 통합하는 다양한 접근 방식을 제공합니다. 수식 방법은 최신 Excel에서 빠르고 동적이며, VBA 및 Kutools 솔루션은 더 넓은 호환성과 더 큰 제어력을 제공합니다. 항상 데이터 크기, Excel 버전 및 선호하는 워크플로에 맞는 방법을 선택하세요. 스크립트 기반 또는 배치 작업을 처음 시도할 때는 샘플 데이터로 테스트하거나 작업을 백업하세요. 추가 Excel 가이드와 고급 팁을 보려면 전체 튜토리얼 컬렉션을 탐색하세요.

최고의 오피스 생산성 도구

🤖 Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행   |  코드 생성  |  사용자 정의 수식 생성  |  데이터 분석 및 차트 생성  |  Kutools Functions 호출…
인기 기능: 중복 찾기, 강조 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 또는 셀 병합   |   반올림(수식 없이) ...
슈퍼 LOOKUP: 다중 조건 VLOOKUP  |   다중 값 VLOOKUP  |   다중 시트 조회   |   퍼지 매치 ....
고급 드롭다운 목록: 드롭다운 목록 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭다운 목록 ....
열 관리자: 지정한 수의 열 추가  |  열 이동  |  숨겨진 열의 표시 상태 전환  |  범위 및 열 비교 ...
추천 기능: 그리드 포커스   |  디자인 보기   |   향상된 수식 표시줄   |  통합 문서 & 시트 관리자   |  자동 텍스트 라이브러리   |  날짜 선택기   |  데이터 병합   |  셀 암호화/해독   |  목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵게/이탤릭/취소선 필터 등) ...
15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...)  |  50+ 종류의 차트(간트 차트, ...)  |  40+ 실용적 수식(생일을 기반으로 나이 계산, ...)  |  19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...)  |  12 가지 변환 도구(단어로 변환하기, 통화 변환, ...)  |  7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...)  |  ... 등 다양
Kutools는 사용자가 선호하는 언어로 이용할 수 있습니다 – 영어, 스페인어, 독일어, 프랑스어, 중국어 및40개 이상의 언어 지원!

Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다.  가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
  • 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
  • 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!

모든 Kutools 추가 기능. 한 번에 설치

Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.

Excel Word Outlook Tabs PowerPoint
  • 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
  • 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
  • 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
  • 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
  • 최고의 가성비 — 개별 추가 기능 구매 대비 절약