今回はエクセルでスペースのない氏名にスペースを自動挿入する方法です。
みなさんは自分の氏名を入力する際に姓と名の間にスペースを入れますか？
自分は入れる派なのですが、入れない方も多いようです。
この記事はエクセルで姓と名の間にスペースのない氏名に、データベースを利用して姓と名の間にスペースを挿入する方法の紹介です。
今回は日本人で最長の名字5文字までに対応しています。
まずは名字データベースの準備から
まずは、名字のデータベースを用意します。
「全国・都道府県別ランキング – 全国名字ランキング」から名字のデータを今回はサンプルとして500位まで参考にさせていただきました。
「名字リスト」というシートを作成して、名字の一覧を作成します（B列は今回関係ありません）。
次に、名字のデータベース範囲（A2～A501セル）に「名字」という名前を定義します。
メンテナンスフリーなデータ範囲の作成
その後、メンテナンスフリーな「名字」データベースにするために、名前の定義に数式を入れて可変式リストにしておきます。
参照範囲を可変式にするためには、「OFFSET関数」と「COUNTA関数」を利用します。
OFFSET関数とは、もともと基準セルから行を設定した数だけ移動し、設定した数だけ列を移動させたセルの値を参照する関数です。
=OFFSET(基準セル,移動行数,移動列数,基準セルからの行数,基準セルからの列数)という式になります。今回は基準セルと、基準セルからの行数の2つを設定することで可変式参照範囲に名前を定義します。
=OFFSET(名字リスト!$A$2,0,0,COUNTA(名字リスト!$A$2:$A$10000),1)
リストの範囲は最大で10,000件としています。
可変式リストの作成は下記の記事を参考にしてください。
それでは下記のようなテーブルを作成し、A列に入力されたスペースのない氏名に姓と名の間にスペースを自動挿入するように設定していきます。
氏名から名字だけを確定させるには「VLOOKUP関数」で
氏名から名字だけを抜き出す（取り出す）には、問題があります。ほとんどの名字は2文字ですが、3文字や1文字の名字の方もたくさんいらっしゃいます。
4文字の名字は3657位の勅使河原さんです。
ちなみに日本人の名字で最も長い名字は5文字だそうです。今回は最長5文字の名字にも対応させます。
もし5文字以上の外国人の名字でも、今回の記事を参考にしていただくと実現可能です。
それでは本題に入ります。「VLOOKUP関数」と「文字列関数」を使用しますので、不安な方はこちらの記事を先にご覧ください。
IFERROR関数とVLOOKUP関数とLEFT関数で5文字の名字を抽出
最初に、セルB2に下記の数式を入力します。
=IFERROR(VLOOKUP(LEFT($A2,5),名字,1,FALSE),"")
数式の簡単な説明ですが、A列に格納されている姓名から左5文字を「LEFT関数」で取り出し、その5文字が名前で定義したデータベース「名字」に含まれているかどうかを「VLOOKUP関数」で検索します。もし、存在すればその5文字を表示し、見つからなければ（エラーになれば）「IFERROR関数」で空白（””）にするという意味です。
ちなみに「LEFT($A2,5)」の部分で列のみ絶対参照にしているのは、参照する氏名（姓名）がA列で、数式をC列〜F列にコピーする際に参照範囲が変更しないようにするためです。
名字が4文字から1文字の判定も同様に
それでは、名字が4文字～1文字までに対応させるためにB2セルの数式をF2セルまでコピーします。
続いて、各セルの数式の一部を変更していきます。
まずは4文字の名字が存在するかをチェックするセル、C2の数式を編集します。
数式は下記の通りです。
=IFERROR(VLOOKUP(LEFT($A2,4),名字,1,FALSE),"")
続いて、D2セルも下記のように変更します。
=IFERROR(VLOOKUP(LEFT($A2,3),名字,1,FALSE),"")
E2セルは下記の数式です。
=IFERROR(VLOOKUP(LEFT($A2,2),名字,1,FALSE),"")
最後にF2セルです。いずれも、「=IFERROR(VLOOKUP(LEFT($A2,1),名字,1,FALSE),””)」の部分を検索したい文字数に合わせるだけです。
=IFERROR(VLOOKUP(LEFT($A2,1),名字,1,FALSE),"")
すべて数式を変更すると下記のように、「鈴木一郎」さんの名字「鈴木」が2文字目の欄に表示されました。
作成した5文字〜1文字の名字判定の数式をコピー
それでは、B2～F2セルの数式をコピーしてB12～F12セルまで貼り付けた結果が下図になります。
「名字データベース」に含まれている各名字が表示されているのがお分かりいただけますでしょうか。
「原田和夫」さんは名字として2文字の「原田」と1文字の「原」が表示されていますが、このあとに名字を判定する数式で、文字数の多い名字が選択されるように設定しますので大丈夫です。
IFS関数で姓（名字）の確定
姓（名字）を確定させるには、「IFS関数」を利用します。
セルG2には下記の数式を入力します。
=IFS(B2<>"",B2,C2<>"",C2,D2<>"",D2,E2<>"",E2,F2<>"",F2)
上記の数式の意味は、順にB2セルが空白でなければB2セルの値を表示、C2セルが空白でなければC2セルの値を表示、D2セルが空白でなければD2セルを表示する、という意味です。
ですから「原田」さんは「原」という名字でも候補に挙がり、「原田」でも候補に挙がりますが、先に条件に一致した2文字の名字「原田」が表示されるということになります。
文字数の多い名字の候補から選択することで、正確に名字の確定が可能になります。
SUBSTITUTE関数で姓と名の間にスペースを追加
それでは最後に取り出した名字を利用して、姓と名の間にスペースを入れる数式を作成します。
姓と名の間にスペースを挿入するには、今回は「SUBSTITUTE関数」を利用します。「SUBSTITUTE」とは日本語で「置換」という意味です。
SUBSTIUTE関数とは
「SUBSTIUTE関数」は以下のような引数を必要とします。
=SUBSTITUTE（文字列, 検索文字列, 置換文字列, 置換対象）
上記の例で説明しますと、文字列「鈴木一郎」から検索文字「鈴木」を見つけ出し、その「鈴木」の代わりに「鈴木」＋「 」（半角スペース” ”）に置き換えて表示するという意味です。
「鈴木一郎」が「鈴木 一郎」と置き換えられることになります。
数式は下記の通りとなります。
=SUBSTITUTE(A2,G2,G2 & " ")
こちらの数式をH12セルまでコピーした結果が下図となります。
エラー表示の場合のメンテナンス 名字の追加は簡単
先ほどの画像で、「#N/A」エラーが表示されているセルがありました。そのセルの氏名の「名字」がデータベースに登録されていないことが原因です。
エラー表示されたときのメンテナンスは非常に簡単です。
「名字リスト」に名字を登録するだけです。今回は「勅使河原」さんと「左衛門三郎」さんをリストの末尾に追加します。
たったこれだけで下図のように姓と名の間にスペースを挿入することが可能になります。
いかがだったでしょうか。日本語の名字はほぼ100％対応できます。
エラーが表示されたら手動になりますが、「名字リスト」にその名字を追加するだけです。
それだけでスペースのない氏名に簡単にスペースを挿入することが可能です。
今回紹介した方法で、100％成功するはずです。
note上にて、完成版ファイルをアップしています。ぜひご利用ください。
コメント
コメント一覧 （5件）
こんにちは！
スペースのない氏名を分割を購入させて頂きました。
ダウンロードして開いたら、G2（姓）・H2（名）・I2（姓名にスペース）にエラー（＃NAME？）が出ます。
どうすればいいですか？
ニシムラ様
ご購入とコメントありがとうございます。
#N/Aエラーでしたら名字が登録されていないことが原因ですが、#NAME?エラーが発生しましたでしょうか？
特殊な関数は使用していないのですが、もしよろしければ具体的にエラーが出ているセルの番地を教えていただけたら幸いです。
ちなみにこちらでアップしているファイルを再確認致しましたが#NAME?エラーは表示されていませんでした。
何卒よろしくお願いいたします。
こんばんは
早速のご返答ありがとうございます
姓名スペースタグの、G2～G20・H2～H20・I2～I20です。
ダウンロードをして、開いたら（#NAME?エラー）が出ていました。
ちなみに、ブログを見ながら自分でも作ってみましたが、G列の全てに#NAME?エラーがでます。
以上、よろしくお願いします。
リーダーの独り言 様
#NAME?エラーの件ですが、原因がわかりました。
当方の、Excelのバージョンが2016で、IFS関数が使えませんでした。
IF関数に変更したら問題なく表示されました。
お手数をお掛けして、すみませんでした。
今後とも、よろしくお願いいたします。
ニシムラ様
ご返信ありがとうございます。
また返信が遅くなり大変失礼いたしました。
解決したとのこと、本当によかったです。
エクセルでは新しい関数が増えていますので、以前では大変な数式が必要だった処理も簡単にできるようになっています。
いずれにせよ問題が解決してよかったです。
今後とも当サイトとnoteを何卒よろしくお願いいたします。