☰

異なる行のカンマで区切られた値から列内の一意のリストを取得します。

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. 少なくとも 1 つのセルを上に配置する必要があります。また、アドレスの上のセルは、$B$1:B1絶対的なものとそうでないものに注意を払う代わりになるはずです。

ここに画像の説明を入力してください

答え3

手動で

  1. データ > テキストを列に > 区切り > 次へ > カンマ > 完了
  2. 別々の列からすべてのデータを1つの列にコピーします
  3. 列を選択 > データ > 重複を削除

自動的に

  1. シートを開く
  2. Alt+F11
  3. 挿入 > モジュール
  4. 次のコードを貼り付けます:
サブリスト_unique()
  Dim rngData を範囲として
  範囲として暗黙の c
  暗く長く
  arr() を文字列として暗くする
  Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
  Dim キーをバリアントとして

  rngData = Range("A14:A17") を設定します。
  rngData内の各cについて
    arr = Split(c.Value, ",")
    i = 0 の場合、UBound(arr)
      dict(トリム(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 で重複を削除するだけです。

関連情報