☰

最大 4 つの名前を持つリストで 2 つの名前のケースを識別する方法

tag-icon • tag-icon microsoft-excel worksheet-function
最大 4 つの名前を持つリストで 2 つの名前のケースを識別する方法

この質問は以前の質問から派生したものです:Excel 式 - 姓が複数の部分から成る人の名前の分離式:。

コンテクスト

20,000 件を超える連絡先のリストがあり、その中には「フルネーム」列が 1 つだけあります。名と姓の 2 つの列を作成する必要があります。ただし、名前がすべてその単純なパターンに当てはまるわけではありません。リストには次のような名前が含まれています。

David Batte
Guy-Mael Jacobe de Naurois
Jean-Marc Lioutier
Thibaud Le Seguillon
Mrs. Manuela Junghaehnel
Hussain Mohammed Dipu Kabir

先ほどの質問の結果として、私は特定のパターンに適合する名前のコレクションを分離して、適切な解析ルールを適用することで問題に取り組んでいます。パターンの数が比較的少ないため、リストの大部分の解析を自動化でき、残りの部分をより管理しやすいタスクにすることができます。

この質問

完全な名前リストのかなりの部分には、解析しやすい名と姓だけが含まれています。この質問の目的は、それらの 2 つの名前を識別して、リストの残りの部分から分離できるようにすることです。

答え1

リスト内の名前に、名と姓のように 2 つのコンポーネント名のみが含まれるケースを識別する方法はいくつかあります。その条件は、コンポーネント名を区切るスペースによって定義されます。

方法1

この質問は以前の質問（Excel 式 - 姓が複数の部分から成る人の名前の分離式:) では、コメントで既に、ファーストネームまたはラストネームだけなど、単一の名前のケースを識別する方法について説明しました。リストからそれらの名前を既に削除して、すべてのエントリに少なくとも 2 つの名前があることが保証されている場合は、ここで同様のテクニックを使用して、それを基に構築することができます。

このアプローチでは、スペースを検索し、そこから別のスペースを検索します。2 番目のスペースが見つかった場合、エントリには 2 つ以上のコンポーネント名があります。エントリが A1 にある場合、基本的な式は次のようになります。

=FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)

この結果は、数字 (次のスペースの位置) か、数字がない場合は #VALUE! エラーになります。#VALUE! エラーは簡単に見つけられ、2 つの名前のエントリを識別します。単一の名前のエントリも識別されることに注意してください。そのため、この方法は、すでにそれらのエントリを削除した場合にのみ機能します。

結果をエラー条件でテストし、2 つの名前のケースにテキスト メッセージを表示するか空白を表示することで、視覚的に魅力的にすることができます。上記の数式を IF テストで囲むと、次の数式になります。

=IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)),"Just 2","")

方法2

もう 1 つの方法は、スペースを数えることです。この方法を使用すると、実際に任意の数のスペースを検索できます。つまり、ターゲット カウントを変更するだけで、名前が 1 つだけのエントリも検索できます。この方法では、すべてのスペースを削除して、文字列が何文字減るかを調べることでスペースを数えます。長さの比較によって、スペースの数が生成されます。これを IF テストで囲むと、結果の数値で必要な操作を実行できます。名前が 2 つだけのエントリ (つまり、スペースが 1 つだけのエントリ) にフラグを付ける式は次のようになります。

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,"Just 2","")

答え2

もっと簡単な方法を紹介します。最適ではないかもしれない20000行。

  • データ -> テキストを列に使用して、名前をスペースで分割します。
  • 名前はA、B、C、Dの4つの列に分割されます。
  • フィルターをクリックし、blanks列Cでフィルターします
  • A と B には 2 つの単語名のみが表示されます。それらをコピーします。
  • 名前を結合するには=TEXTJOIN(" ",TRUE,A1:B1)

答え3

2 つの方法を提案したいと思います。1 つは非 VBA で、もう 1 つは VBA です。

方法1:

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

使い方：

  • セルにこの数式を入力してH2ヘルパー値を作成します（名前の一部を識別する）を入力して記入してください。

=LEN(G2)-LEN(SUBSTITUTE(G2," ",""))

  • 2セルに条件を入力しますH1。

  • この配列数式をセルに入力しI2、Ctrl+Shift+Enterそしてそれを埋めてください。

    {=IFERROR(INDEX($G$2:$G$14,SMALL(IF(H$1=$H$2:$H$14,ROW($G$2:$G$14)- MIN(ROW($G$2:$G$14))+1,""), ROW()-1)),"")}

注意

  • セルの条件をまたはH1に変更すると、 2 つ以上の部品名のリストを取得できます。13

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

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

方法2:

20000 行以上を処理する場合、VBA (マクロ) がジョブを完了するための最も速い方法になります。

使い方：

  • 右クリックシートタブとポップアップメニューからコードを表示VB エディターを入手します。

  • コピーペーストこのコードを標準モジュールとして、最後に走るマクロ。

    Sub DeleteMorethan2()

Dim r As Integer, c As Integer, space_count As Integer

For r = 2 To 15
space_count = 0

For c = 1 To Len(Range("A" & r))

   If Mid(Range("A" & r), c, 1) = " " Then space_count = space_count + 1
   If space_count > 1 Then Range("A" & r).ClearContents

  Next
Next

End Sub

次のようになります:

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

注意

  • このコードは動作しておりRows 2 to 15、この部分を編集することでFor r = 2 To 15、より多くの行を含めることができます。
  • 使用されている列は でA、これも編集可能です。

さらに、空白行を削除して名前リストを再作成するための配列数式を提案したいと思います。

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

  • この配列数式をセルに入力しB2、Ctrl+Shift+Enter＆それを埋めてください。

    {=IFERROR(INDEX(A$2:A$14,SMALL(IF(A$2:A$14<>"",ROW(A$2:A$14)-ROW(A$2)+1),ROWS(B$2:B2))),"")}

必要に応じて数式内のセル参照を調整します。

関連情報