エクセルでスペースのない氏名にスペースを自動挿入 簡単なメンテナンスで日本人の名字100%対応可能 最長5文字の名字までOK 365・2019・2016・2013・2010・2007対応

今回はエクセルでスペースのない氏名にスペースを自動挿入する方法です。

みなさんは自分の氏名を入力する際に姓と名の間にスペースを入れますか?

自分は入れる派なのですが、入れない方も多いようです。

この記事はエクセルで姓と名の間にスペースのない氏名に、データベースを利用して姓と名の間にスペースを挿入する方法の紹介です。

エクセルでスペースのない氏名(姓名)を姓(名字)と名に分割する(分ける)方法 意外と簡単 「VLOOKUP関数」で名字を確定して名前も取り出す! 365・2019・2016・2013・2010・2007対応
エクセルで、姓と名の間にスペースがある氏名から「姓」と「名」に分割する方法は簡単です。ところが、姓と名の間にスペースや区切り文字がない場合、「...

今回は日本人で最長の名字5文字までに対応しています。

スポンサーリンク

まずは名字データベースの準備から

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

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

「名字リスト」というシートを作成して、名字の一覧を作成します(B列は今回関係ありません)。

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

名字のデータベースリスト範囲「名字」

メンテナンスフリーなデータ範囲の作成

その後、メンテナンスフリーな「名字」データベースにするために、名前の定義に数式を入れて可変式リストにしておきます。

参照範囲を可変式にするためには、「OFFSET関数」と「COUNTA関数」を利用します。

OFFSET関数とは、もともと基準セルから行を設定した数だけ移動し、設定した数だけ列を移動させたセルの値を参照する関数です。

=OFFSET(基準セル,移動行数,移動列数,基準セルからの行数,基準セルからの列数)という式になります。今回は基準セルと、基準セルからの行数の2つを設定することで可変式参照範囲に名前を定義します。

=OFFSET(名字リスト!$A$2,0,0,COUNTA(名字リスト!$A$2:$A$10000),1)

リストの範囲は最大で10,000件としています。

可変式リストの作成は下記の記事を参考にしてください。

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

それでは下記のようなテーブルを作成し、A列に入力されたスペースのない氏名に姓と名の間にスペースを自動挿入するように設定していきます。

テーブルを作成して名字を取り出す
スポンサーリンク

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

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

4文字の名字は3657位の勅使河原さんです。

ちなみに日本人の名字で最も長い名字は5文字だそうです。今回は最長5文字の名字にも対応させます。

それでは本題に入ります。「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関数を用いて住所から都道府県・市・区・町名に...

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列にコピーする際に参照範囲が変更しないようにするためです。

エクセルで条件に合ったセルの合計・平均を計算するSUMIF関数・AVERAGEIF関数の使い方 365・2019・2016・2013・2010・2007対応
今回はエクセルで、ある条件に合ったセルだけの合計・平均を計算・表示する方法です。「SUMIF関数」と「AVERAGEIF関数」を利用します。以...
IFERROR関数とVLOOKUP関数で5文字の名字が存在するか検索

名字が4文字から1文字の判定も同様に

それでは、名字が4文字~1文字までに対応させるためにB2セルの数式をF2セルまでコピーします。

数式をコピーして修正

続いて、各セルの数式の一部を変更していきます。

まずは4文字の名字が存在するかをチェックするセル、C2の数式を編集します。

4文字の名字があるかをチェックする数式に変更

数式は下記の通りです。

=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文字目の欄に表示されました。

2文字目の名字を取り出した

作成した5文字〜1文字の名字判定の数式をコピー

それでは、B2~F2セルの数式をコピーしてB12~F12セルまで貼り付けた結果が下図になります。

「名字データベース」に含まれている各名字が表示されているのがお分かりいただけますでしょうか。

データベースに登録された名字が表示された

「原田和夫」さんは名字として2文字の「原田」と1文字の「原」が表示されていますが、このあとに名字を判定する数式で、文字数の多い名字が選択されるように設定しますので大丈夫です。

スポンサーリンク

IFS関数で姓(名字)の確定

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

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

セル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(文字列, 検索文字列, 置換文字列, 置換対象)

SUBSTIUTE関数の引数

上記の例で説明しますと、文字列「鈴木一郎」から検索文字「鈴木」を見つけ出し、その「鈴木」の代わりに「鈴木」+「 」(半角スペース” ”)に置き換えて表示するという意味です。

「鈴木一郎」が「鈴木 一郎」と置き換えられることになります。

SUBSTTTUTE関数で姓と名の間にスペースを

数式は下記の通りとなります。

=SUBSTITUTE(A2,G2,G2 & " ")

こちらの数式をH12セルまでコピーした結果が下図となります。

姓と名の間にスペースを挿入完成
スポンサーリンク

エラー表示の場合のメンテナンス 名字の追加は簡単

先ほどの画像で、「#N/A」エラーが表示されているセルがありました。そのセルの氏名の「名字」がデータベースに登録されていないことが原因です。

エラー表示されたときのメンテナンスは非常に簡単です。

「名字リスト」に名字を登録するだけです。今回は「勅使河原」さんと「左衛門三郎」さんをリストの末尾に追加します。

名字リストの末尾に名字を追加

たったこれだけで下図のように姓と名の間にスペースを挿入することが可能になります。

エラー表示が出れば名字を追加するだけの簡単メンテナンス

いかがだったでしょうか。日本語の名字はほぼ100%対応できます。

エラー表示された名字

エラーが表示されたら手動になりますが、「名字リスト」にその名字を追加するだけです。

名字を追加

それだけでスペースのない氏名に簡単にスペースを挿入することが可能です。

氏名にスペースが簡単挿入

今方法で100%成功するはずです。もしうまくいかない氏名の方がいれば、コメントいただけたら対応策を考えます。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

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