Excelで氏名を姓と名に分ける:関数・フラッシュフィルとスペースのそろえ方

名簿の「山田 太郎」を、姓と名の列に分ける方法を説明します。全角と半角のスペースが混ざった名簿の整え方から、関数とフラッシュフィルの使い分け、スペースのない氏名の扱いまで扱います。

目次(6項目)

🧹 最初にスペースをそろえる

名簿には、全角スペース、半角スペース、2つ続いたスペースが混ざっていることがあります。先に半角1つへそろえると、後の式が単純になります。

=TRIM(SUBSTITUTE(A2," "," "))

全角スペースを半角に置き換え、TRIMで前後の空白と連続した空白を整えます。ここではB列に入れて、以降の式はB列を使います。

スペースをそろえた結果
A列(元の氏名)B列(そろえた結果)
山田 太郎山田 太郎
佐々木 花子佐々木 花子
長谷川 一郎長谷川 一郎

スペースのない行は、次の式で見つけられます。TRUEになった行は、次の手順で分けられません。

=ISERROR(FIND(" ",B2))

✏️ 関数で分ける

姓と名を取り出す式
取り出すもの式「佐々木 花子」の結果
姓=LEFT(B2,FIND(" ",B2)-1)佐々木
名=MID(B2,FIND(" ",B2)+1,LEN(B2))花子

FINDでスペースの位置を探し、その前を姓、後ろを名として取り出します。MIDの第3引数は「残り全部」の意味で、十分に長い値として LEN(B2) を指定しています。

スペースのない行でエラーを出したくないときは、IFERRORで囲みます。

=IFERROR(LEFT(B2,FIND(" ",B2)-1),B2)

スペースがなければ氏名全体を姓の列に入れます。後で確認しやすいよう、前の手順の判定列と一緒に使います。

Excel 2024 / Microsoft 365 では、TEXTBEFORE・TEXTAFTERで短く書けます。

=TEXTBEFORE(B2," ") =TEXTAFTER(B2," ")

⚡ フラッシュフィルで分ける

Excel 2013以降では、例を1つ入力するだけで、規則を読み取って残りを埋める機能が使えます。

  1. 氏名の隣の列の先頭に、姓を入力します(例:山田)。
  2. 下のセルに移り、Ctrl+E を押します。
  3. 結果を上から確認します。名の列も同じように作ります。
関数とフラッシュフィルの使い分け
関数フラッシュフィル
結果数式値
元の氏名を直したとき自動で変わる変わらない(やり直す)
新しい行式をコピーするやり直す
向いている場面毎月使う名簿1回だけの整理
⚠️ 結果は確認してから使う
フラッシュフィルは例から規則を推測します。スペースの有無がそろっていない名簿では、間違った分け方になることがあります。行数の多い名簿では、エラーが出ないまま誤った値が入るので、特に確認が必要です。

Ctrl+E で何も起きないときは、「ファイル」→「オプション」→「詳細設定」の「フラッシュ フィル オプション」に関する設定を確認してください。

🚧 スペースのない氏名

「山田太郎」のようにスペースがない氏名を、規則だけで正しく分けることはできません。姓が1文字とは限らず、「林」「長谷川」「東海林」のように文字数がさまざまだからです。

姓の一覧がある場合は、最も長く一致する姓を探す方法で候補を作れます。D2:D100に姓の一覧を、文字数の少ない順に並べておきます。空白のセルがあると正しく動かないので、範囲は一覧の行数に合わせます。

=LOOKUP(2,1/(LEFT(A2,LEN($D$2:$D$100))=$D$2:$D$100),$D$2:$D$100)

一覧の中で、氏名の先頭と一致する最後の姓を返します。文字数の少ない順に並べるので、最も長い姓が選ばれます。一致しなければ #N/A になります。

これは候補を作る方法です。「東海林太郎」と「東 海林太郎」のように、一覧だけでは決められない場合があります。本人の情報と照らして、人が確認してください。

🔤 ふりがなを付ける

五十音順に並べるには、ふりがなの列があると便利です。Excelで入力した氏名なら、PHONETIC関数で取り出せます。

=PHONETIC(A2)

他のシステムから貼り付けた名簿では、漢字のまま返ることがあります。カタカナ・ひらがな・半角への変換と合わせて、PHONETIC関数の記事で説明しています。

📌 名簿を整えるときの注意

  • 元の列を残す:分けた後も元の氏名の列を消さず、確認できるようにします。
  • 姓名を戻す:=C2&" "&D2 のように結合すると、スペースのそろった氏名になります。
  • 同姓同名:氏名だけでの照合は避け、社員番号などの番号で照合します。
  • 異体字:「高」と「髙」、「崎」と「﨑」は別の文字として扱われます。置き換えるときは、本人の表記を確認してください。
  • 個人情報:試すときは、架空の名簿か、コピーしたファイルを使います。

🔗 関連記事