エクセルでスペースのない氏名(姓名)を姓(名字)と名に分割する(分ける)方法 意外と簡単 「VLOOKUP関数」で名字を確定して名前も取り出す! 365・2019・2016・2013・2010・2007対応

エクセルで、姓と名の間にスペースがある氏名から「姓」と「名」に分割する方法は簡単です。

エクセルでスペースで区切られた氏名を姓と名に分ける(分割する)3つの方法 フラッシュフィルが超便利!365・2019・2016・2013対応
エクセルでスペースで区切られた氏名から「姓」「名」を分ける(分割する)方法は簡単です。今回は3つの方法を紹介いたしますが、一番のオススメはその三の方法...

ところが、姓と名の間にスペースや区切り文字がない場合、「姓」と「名」に分割するのは非常に難しい作業となります。

さすがのエクセルも、人名から名字を自動で取り出す機能はありません。

今回は、下図のように「スペースのない氏名(姓名)」から、「姓」と「名」に分割する方法のご紹介です。

スペースのない氏名から「姓」と「名」に分割する
スポンサーリンク

名字のデータベースを活用して名字(姓)を確定

まずは、名字のデータベースを用意します。

全国・都道府県別ランキング – 全国名字ランキング」から名字のデータを今回はサンプルとして500位まで参考にさせていただきました。

名字リスト

そして、名字のデータベース範囲(A2~A501セル)に「名字」という名前を定義します。

Excel(エクセル)プルダウンリスト(ドロップダウンリスト)のリストを可変式リストにすればリストの項目が増えても再設定不要! リストを名前の定義とテーブルにする2つの方法 365・2019・2016・2013・2010・2007対応
データの入力規則で、「元の値」をリストにして、プルダウンリスト(ドロップダウンリスト)を利用している方は多いと思います。「元の値」のリストは、Exce...

これで準備は完了です。

名字のデータベースを作成しておく
スポンサーリンク

氏名から名字だけを確定させるには「VLOOKUP関数」で

氏名から名字だけを抜き出す(取り出す)には、問題があります。ほとんどの名字は2文字ですが、3文字や1文字の名字の方もたくさんいらっしゃいます。

4文字の名字は3657位の勅使河原さんです。今回ご紹介する記事を応用すれば4文字以上の名字の対応も可能となりますが、今回の記事では3文字の名字までとしています。

それでは本題に入ります。「VLOOKUP関数」と「文字列関数」を使用しますので、不安な方はこちらの記事を先にご覧ください。

超簡単!エクセルのVLOOKUP関数で成績一覧から個人の成績票を作成 VLOOKUP関数の裏技も紹介! 365・2019・2016・2013・2010・2007対応
今回は、成績一覧データから、個人の成績票を「VLOOKUP関数」で作成する方法です。下記の成績一覧データから、個人の成績票を作成します(ちなみに名前は...
エクセルの関数で文字列を自由自在に分割! LEFT関数・MID関数・RIGHT関数・LEN関数・FIND関数 365・2019・2016・2013・2010・2007対応
エクセルの文字列操作関数はたくさんあります。今回は、LEFT関数・MID関数・RIGHT関数・LEN関数・FIND関数を用いて住所から都道府県・市・区・町名に...

氏名の前から3文字が名字として存在するかをチェック

まずは、姓名から左3文字を抜き出し、その3文字が先ほどの「名字」データベースに存在するかどうかを確認します。

そしてその3文字が「名字」データベースに存在するならば「3文字」の列(B列)に表示させ、なければ空白にします。

数式は下記のようになります。

=IFERROR(VLOOKUP(LEFT($A2,3),名字,1,FALSE),"")

数式の簡単な説明ですが、A列に格納されている姓名から左3文字を「LEFT関数」で取り出し、その3文字が名前で定義したデータベース「名字」に含まれているかどうかを「VLOOKUP関数」で検索します。もし、存在すればその3文字を表示し、なければ(エラーになれば)「IFERROR関数」で空白(””)にするという意味です。

ちなみに「LEFT($A2,3)」の部分で列のみ絶対参照にしているのは、参照する氏名(姓名)がA列で、数式をC列D列にコピーする際に範囲が変更しないようにするためです。

先頭3文字が名字として存在するかを検索し、存在すれば表示する

セルB2の数式をオートフィルでコピーした結果が下図になります。

見事に「佐々木」さんだけが表示されているのがお分かりいただけるでしょうか。ちなみにE列とF列はすでに姓名に分割されていますが、こちらの数式については後述します。

3文字の名字「佐々木」だけがB列に表示されている

氏名の前から2文字が名字として存在するかをチェック

続いて、氏名の前から2文字が名字として存在するかをチェックします。

B2セルの数式をC2セルにコピーして、「LEFT関数」の部分を2文字にするだけです。

「$A」のみ絶対参照にしたのはそのためです。

=IFERROR(VLOOKUP(LEFT($A2,2),名字,1,FALSE),"")
先頭2文字が名字として存在するかを検索し、存在すれば表示する

C2セルの数式をC9セルまでコピーします。すると、下図のように先頭の2文字が名字として存在すればその行のみ表示されています。

2文字の名字だけがC列に表示された

氏名の前から1文字が名字として存在するかをチェック

最後に氏名の先頭から1文字が名字として存在するかをチェックします。

数式はもうお分かりでしょうか。C2セルの数式をD2セルにコピーし、「LEFT関数」の文字数を「1」にするだけです。

 =IFERROR(VLOOKUP(LEFT($A2,1),名字,1,FALSE),"")
先頭1文字が名字として存在するかを検索し、存在すれば表示する

D2セルの数式をD9セルまでコピーした結果が下図です。

1文字の名字だけがC列に表示された

ちなみに文字数を3文字目から検索した理由は、逆にするとミスが起こるからです。上図のように「原田」さんは「原」でも名字になりますし、「原田」でも名字になります。

そういったエラーを回避するために、文字数の多い3文字目から検索をしています。

「原田」さんの行には「原田」と「原」の2つの名字が表示されていますが、この後の「IFS関数」で対応しますので大丈夫です。

スポンサーリンク

「IFS関数」で姓(名字)を確定させる

姓(名字)を確定させるには、「IFS関数」を利用します。

Excel(エクセル)2019の新機能! 追加された新関数IFS(イフエス)の使い方 IF関数よりもシンプルに条件分岐が可能!
Excel(エクセル)2019で追加された機能Excel2019になっていろいろな機能が追加されています。CONCATIFSMAXIF...

「姓」を表示させるE2セルには、下記の数式が入力されています。

=IFS(B2<>"",B2,C2<>"",C2,D2<>"",D2)

上記の数式の意味は、順にB2セルが空白でなければB2セルの値を表示、C2セルが空白でなければC2セルの値を表示、D2セルが空白でなければD2セルを表示する、という意味です。

ですから先ほどの「原田」さんは「原」ではなく「原田」が先に条件に一致して表示されるということになります。

下図をご確認ください。

「原田」さんは「原」ではなく「原田」が表示される
スポンサーリンク

ここまでくれば名(名前)を取り出すのは簡単 「RIGHT関数」と「LEN関数」で

いよいよ最後です。「姓」(名字)が確定していれば「名」を取り出すのは簡単です。F2セルの数式はこちらです。

=RIGHT(A2,LEN(A2)-LEN(E2))

意味は「姓名」(氏名)の格納されたセルから、「姓名」の文字列の数に「姓」の文字数を引いた文字数を右から取得するという意味です。

氏名の右から「名」を「RIGHT関数」で取得

以上です。そしてB列からD列を非表示した完成図が下記のようになります。

スペースのない氏名から「姓」と「名」に分割する方法の紹介でした。意外と簡単に作成することができることがお分かりいただけたのではないでしょうか。

もし4文字または5文字以上の名字にも対応させる必要があれば、列を増やしていき、「IFS関数」の分岐を増やすことで対応可能です。ポイントは長い名字から検索していくことだけです。

また、エラーが表示されたら「名字」データに「姓」を追加していくだけですので、メンテナンスも簡単です。

こちらは、日本語の名字で最大の5文字に対応する記事です。

エクセルでスペースのない氏名にスペースを自動挿入 簡単なメンテナンスで日本人の名字100%対応可能 最長5文字の名字までOK 外国人も5文字以上でもOK 365・2019・2016・2013・2010・2007対応
今回はエクセルでスペースのない氏名にスペースを自動挿入する方法です。みなさんは自分の氏名を入力する際に姓と名の間にスペースを入れますか?自分は...

ご不明な点がありましたら、コメントいただけたら回答致します。

こちらnoteにて、完成版ファイルをアップしています。ぜひご利用ください。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言をフォローする
リーダーの独り言

コメント

タイトルとURLをコピーしました