☰

여러 행의 쉼표로 구분된 값에서 열의 고유 목록 가져오기

tag-icon • tag-icon microsoft-excel
여러 행의 쉼표로 구분된 값에서 열의 고유 목록 가져오기

EXCEL의 열 목록에서 다른 행의 쉼표로 구분된 고유 값을 어떻게 얻을 수 있습니까?

내 데이터

Dallas, New York, Austin, Tokyo
Dallas, New York, Austin, Tokyo
London, Tokyo
Tokyo, Istanbul

예상 결과:

Dallas
New York
Austin
Tokyo
London
Istanbul

답변1

열의 데이터 포함ㅏ, 다음 짧은 VBA 매크로를 실행하세요.

Sub Sundar()
    Dim s As String, c As Collection, k As Long

    Set c = New Collection
    k = 1

    s = Replace(Application.WorksheetFunction.TextJoin(",", True, Range("A:A")), " ", "")
    arr = Split(s, ",")

    On Error Resume Next
    For Each a In arr
        c.Add a, CStr(a)
        If Err.Number = 0 Then
            Cells(k, 2).Value = a
            k = k + 1
        Else
            Err.Number = 0
        End If
    Next a
    On Error GoTo 0
End Sub

여기에 이미지 설명을 입력하세요

답변2

나는 vba를 사용하겠지만, 공식을 사용하여 이를 수행할 수 있음을 증명합니다.

=INDEX(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,$A$2:$A$5),",",REPT(" ",99)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))-1)*99+1,99)),AGGREGATE(15,7,ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))/(COUNTIFS($B$1:B1,TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,$A$2:$A$5),",",REPT(" ",99)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(TEXTJOIN(",",,$A$2:$A$5))-LEN(SUBSTITUTE(,",",""))+1))-1)*99+1,99)))=0),1))

이것은 모든 문자열을 결합한 다음 ,성장하는 목록에 이미 존재하지 않는 첫 번째 항목을 전달하여 순환에 따라 분할된 모든 항목의 배열을 생성합니다.

몇 가지 주의 사항:

  1. 이를 위해서는 Office 365 이상이 필요합니다.
  2. 이는 배열 유형 수식이므로 너무 많으면 계산 속도가 느려집니다.
  3. 유지 관리가 정말 어렵습니다.
  4. 그 위에는 최소한 하나의 셀이 있어야 하며, 주소 위의 해당 셀은 $B$1:B1무엇이 절대적이고 무엇이 아닌지에 대한 주의를 대체해야 합니다.

여기에 이미지 설명을 입력하세요

답변3

수동으로

  1. 데이터 > 텍스트를 열로 > 구분 > 다음 > 쉼표 > 마침
  2. 별도의 열에 있는 모든 데이터를 하나의 열에 복사합니다.
  3. 열 > 데이터 > 중복 제거를 선택합니다.

자동으로

  1. 시트를 열어보세요
  2. Alt+F11
  3. 삽입 > 모듈
  4. 다음 코드를 붙여넣으세요.
하위 목록_고유()
  범위로 rngData를 흐리게 표시
  범위로 희미한 c
  내가 오랫동안 어둡게
  Dim arr() 문자열로
  Dim dict를 객체로 설정: Set dict = CreateObject("Scripting.Dictionary")
  변형으로서의 희미한 키

  rngData = Range("A14:A17") 설정
  rngData의 각 c에 대해
    arr = 분할(c.값, ",")
    i = 0에서 UBound(arr)로
      dict(Trim(arr(i))) = 1
    다음
  다음
  나는 = 1
  dict.Keys의 각 키에 대해
    rngData(1).Offset(rngData.Rows.Count + i).Value = 키
    나는 = 나는 + 1
  다음

서브 끝
  1. 코드에서 Range("A14:A17")데이터가 있는 실제 범위 주소로 변경하세요.
  2. 때리다F5

답변4

귀하의 답변을 바탕으로 신청서를 만들었습니다. 이것이 가장 쉬운 해결책입니다. 텍스트 영역에 데이터를 복사하여 붙여넣고 실행 버튼을 클릭하세요.

여기에서 애플리케이션의 스크린샷을 볼 수 있습니다.

콤보 상자를 사용하여 구분 기호를 변경할 수도 있습니다. 질문이 있으면 알려주세요.

여기에는 지루한 방법이 있습니다. VBA를 사용합니다.

Sub Macro1()
Dim countries As String
Dim arrayofcountries
Dim con As Integer

con = 0

For i = 2 To 5
countries = Cells(i, 1).Value

If (countries = "") Then
    ''Do nothing

Else
    arrayofcountries= Split(countries , ",")
    For Z = LBound(arrayofcountries) To UBound(arrayofcountries)

                        Cells(i + con, 3).Value = arrayofcountries(Z)
                        con = con + 1

    Next Z
 
End If

con = con - 1
Next i

End Sub

Excel로 중복 항목을 제거하면 됩니다.

관련 정보