Excel(エクセル)でスペースがある氏名リストを『姓』と『名』に分ける方法を【【エクセル】氏名の『姓』と『名』(『苗字』と『名前』)を自動で分ける３つの方法】の記事で紹介しました。
しかし、氏名リストの『姓』と『名』の間にスペースなどの区切り文字が無い場合は分割するのが困難です。
氏名リストの件数が少なければ目視で分割した方が速いですが、データ量が膨大な場合は、今回紹介する方法を参考にしてみてください。
今回は、下の表のように、スペースがない氏名を『姓』と『名』に分ける方法を紹介します。
分解する全体の処理の流れ
スペースがない氏名を『姓』と『名』に分ける手順について、全体の作業の流れをまず説明します。
処理は大きく分けて、以下の5ステップに分かれます。各見出しで手順を紹介してますので参考にしてください。
今回の処理手順
- WEBから『日本人の名字リスト』を取得
- 氏名リストを文字数別に分解
- 手順１のリストを使って、氏名リストの『姓』の候補を抽出
- 氏名リストの『姓』を特定
- 氏名リストの『名』を特定
WEBから『日本人の名字リスト』を取得
氏名リストの『姓』と『名』の間にスペースが無いので、名字リストを使って『姓』を特定していきます。
しかし、残念ながらエクセルには日本人の名字リストは登録されていません。
そのため、WEB(インターネット)から名字リストを取得します。
手順１．「WEB」を開く
【名字由来net】の以下のWEBのURLを開きます。
引用元：名字由来net
https://www.post.japanpost.jp/zipcode/download.html
手順２．「全国・都道府県別ランキング」を開く
「名字ランキング」⇒「全国・都道府県別ランキング」を開きます。
手順３．「１～２０位の名字リスト」をコピー
「１～２０位の名字リスト」を選択して、「Ctrl＋C」でコピーします。
手順４．エクセルに貼り付け
Excelの利用していないワークシートに「Ctrl＋V」のショートカットキーで貼り付けます。
貼り付けた後に、画面右下の『貼り付けのオプション』をクリックし、『貼り付け先の書式に合わせる』をクリックしてください。
そうすると、罫線やリンクなどの貼り付けが消えて、『標準』で貼り付けることが出来ます。
手順５．２１位以下の名字ランキングも貼り付け
手順３、４の作業を繰り返して、名字ランキング２１位以下のランキングもExcelに貼り付けます。今回は、１０００位以下までの名字をエクセルに貼り付けします。分解したい氏名リストの人数に合わせて貼り付けるリスト数を増やしてください。
氏名リストを文字数別に分解
続いて、氏名リストから『姓』の候補を見つけるためにエクセルのデータを加工していきます。
手順１．氏名リストの横に最大文字数分の列を追加
氏名リストの右横に氏名の最大文字数分の列を追加します。下の表では、『小野田和也』の５文字が最大文字数なので、５行を追加し、列タイトルは『5』『4』・・・『１』と数値を入力します。
手順２．LEFT関数を挿入
一番上の氏名の横のセルに以下のLEFT関数を挿入し、氏名の先頭から”列タイトルの数値分の文字数”を抽出します。この関数を別のセルにもコピペできるように、以下の位置に絶対参照の”＄”を挿入してください。
=LEFT($B3,C$2)
「山田太郎」は４文字なので、５文字の抜き取りをLEFT関数で指定しましたが４文字が抜き取られました。
手順３．LEFT関数をコピペ
手順２で作成したLEFT関数をそれ以外のセルにもコピペします。
WEBリストを使って、氏名リストの『姓』の候補を抽出
LEFT関数で先頭の文字を抜き取ったリストと、WEBから取得した名字リストが一致したセルが『姓』の可能性が高いということになります。
この一致を確認するためにVLOOKUP関数を使って「姓」の候補セルを見つけます。
手順１．右横にVLOOKUP関数を挿入
『姓』のセルを見つけるために、右の空いたセルにVLOOKUP関数を挿入します。第１引数「検索値」は、LEFT関数の挿入セルです。
=VLOOKUP(C3,
手順２．第２引数「検索範囲」を指定
続いてVLOOKUP関数の第２引数「検索範囲」を指定します。
まず、WEBの名字リストを貼り付けたワークシートをクリックしてから、名字列の列番号をクリックしてF4キーを押して絶対参照”＄”を付与します。
=VLOOKUP(C3,WEB名字ランキング!$B:$B,
手順３．第３引数「列番号」と第４引数「検索方法」を指定
続いてVLOOKUP関数の第３引数「列番号」と第４引数「検索方法」を指定します。
列番号は「１」、検索方法は「FALSE」を使って、以下のようにVLOOKUP関数を完成させてENTERを押します。
=VLOOKUP(C3,WEB名字ランキング!$B:$B,1,FALSE
手順４．VLOOKUP関数をコピペ
LEFT関数で先頭の文字を抜き取ったリストと、WEBから取得した名字リストが一致したセルが一致している場合は、VLOOKUP関数の結果はエラー値『#N/A』が表示され、一致している場合は「姓」が表示されます。
このVLOOKUP関数をコピペして、それ以外のセルも一致するか確認します。
手順５．VLOOKUP関数のセルを値貼り付け
VLOOKUP関数はもう使いませんので、値に変換します。VLOOKUP関数を挿入したセルを選択してコピーしてから、右クリックメニューの「値貼り付け」で値で貼り付けます。
手順６．『#N/A』を空白に置換
エラー値『#N/A』のセルは不要なので、置換機能を使って削除します。
まず値貼り付けしたセル範囲を選択してから、「Ctrl」キーを押しながら「H」キーを押します。
「検索と置換」画面が表示されるので、「検索する文字列」欄に「#N/A」を入力し、「置換後の文字列」は何も入力しないまま「すべて置換」をクリックします。
手順７．『姓』を確認
エラー値『#N/A』が削除されるので、残ったセルの名字が「姓」となりうるということです。まだこの段階では、１行に２つの「姓」が入力されたセルがありますが、このステップでは気にしなくてOKです。
手順８．LEFT関数を挿入したセルに値貼り付け
手順７で完成したセル範囲をコピーして、LEFT関数が挿入されたセル範囲に貼り付けます。
VLOOKUP関数を挿入したセル範囲は利用しないので、列自体を削除してください。
手順９．不要な列を削除
今回説明用に使った氏名リストでは、「５」「４」の列には何も入力されていないので、列自体を削除します。
そうすると、下の図のように、「姓」の候補リストが完成します。「小野田和也」の「姓」が、「小野田」と「小野」の２つが入力されているなど、「姓」を絞り切れていませんが、この見出しの作業は以上で終了です。
氏名リストの『姓』を特定
続いて、「姓」を特定(確定)させます。
「小野田和也」の「姓」が、「小野田」と「小野」の２つ、「今井壮太」の「姓」が、「今井」と「今」の２つ入力されていますが、基本的に「姓」と「名」を分かりやすく名前が付けられるので、左側が正解の可能性が高いでしょう。
左側が正解という前提で、「姓」を特定(確定)させる手順を紹介していきます。
手順１．「姓」列を追加
確定した「姓」を入力するための『姓』列を追加します。
手順２．IFS関数を挿入
手順１で作成した『姓』列にIFS関数を追加します。
ただし、IFS関数はExcel2016、Excel2019、Excel2021、Office365は利用出来ますが、一部のExcel2016やそれ以前のバージョンでは使うことが出来ません。その場合は、IF関数でも対応出来ますので、IF関数を利用する場合は、下の手順２(２)(ＩＦ関数版)まで読み進めて下さい。
IF関数の『IF』と複数の『S』の単語のとおり、IFS関数は、1つまたは複数の条件が満たされているかどうかを判定し、最初に条件を満たす場合の値を返します。引数で利用している『<>』は、”以外”を表す演算子です。
=IFS(C3<>"",C3,D3<>"",D3,E3<>"",E3)
関数の設定が難しく感じる方は、数式バーの左横の【fx】ボタンを押して、「関数の引数」画面から引数を一つずつ設定した方が簡単に設定することが出来ます。
ENTERで関数を確定すると、最も左側に入力されたセル値が「姓」列に入力されます。
手順２(２)．IF関数を挿入
手順２でIFS関数を利用出来なかった場合は、IF関数で対応しましょう。
最も左側に入力されたセル値が「姓」列に入力されるよう、以下のIF関数を挿入してください。
=IF(C3="",IF(D3="",E3,D3),C3)
ENTERで関数を確定すると、IFS関数の結果と同じく、最も左側に入力されたセル値が「姓」列に入力されます。
IFS関数やIF関数の具体的な使い方を以下の記事でも紹介していますので、ぜひ参考にしてみてください。
手順３．IFS関数(もしくはIF関数)をコピペ
手順２で挿入したIFS関数(もしくはIF関数)を下のセルにコピペすると、「姓」が確定します。
氏名リストの『名』を特定
『姓』が確定したので、残りは『名』を確定すれば作業は完了です。
次は、RIGHT関数を使って、氏名の末尾から必要な文字数を抜き取ります。
手順１．不要な列を削除
IFS関数(もしくはIF関数)が挿入されている列は値貼り付けして、不要な列を削除します。
手順２．RIGHT関数を挿入
以下のRIGHT関数を挿入します。RIGHT関数の中に文字数をカウントすることが出来るLEN関数を挿入して、抜き取る『名』の文字数を自動で計算します。
=RIGHT(B3,LEN(B3)-LEN(C3))
ENTERで関数を確定すると、『太郎』と表示され、『名』が確定されます。
手順３．RIGHT関数をコピペ
手順２で挿入したRIGHT関数を下のセルにコピペすると、全てのセルの「名」が確定します。
手順４．『姓』と『名』の分解が完了
RIGHT関数が挿入されたセルを値貼り付けすれば、すべての氏名の『姓』と『名』の分解が完了です。
