Excelで氏名を姓と名に分ける:関数・フラッシュフィルとスペースのそろえ方
名簿の「山田 太郎」を、姓と名の列に分ける方法を説明します。全角と半角のスペースが混ざった名簿の整え方から、関数とフラッシュフィルの使い分け、スペースのない氏名の扱いまで扱います。
目次(6項目)
🧹 最初にスペースをそろえる
名簿には、全角スペース、半角スペース、2つ続いたスペースが混ざっていることがあります。先に半角1つへそろえると、後の式が単純になります。
全角スペースを半角に置き換え、TRIMで前後の空白と連続した空白を整えます。ここではB列に入れて、以降の式はB列を使います。
| A列(元の氏名) | B列(そろえた結果) |
|---|---|
| 山田 太郎 | 山田 太郎 |
| 佐々木 花子 | 佐々木 花子 |
| 長谷川 一郎 | 長谷川 一郎 |
スペースのない行は、次の式で見つけられます。TRUEになった行は、次の手順で分けられません。
✏️ 関数で分ける
| 取り出すもの | 式 | 「佐々木 花子」の結果 |
|---|---|---|
| 姓 | =LEFT(B2,FIND(" ",B2)-1) | 佐々木 |
| 名 | =MID(B2,FIND(" ",B2)+1,LEN(B2)) | 花子 |
FINDでスペースの位置を探し、その前を姓、後ろを名として取り出します。MIDの第3引数は「残り全部」の意味で、十分に長い値として LEN(B2) を指定しています。
スペースのない行でエラーを出したくないときは、IFERRORで囲みます。
スペースがなければ氏名全体を姓の列に入れます。後で確認しやすいよう、前の手順の判定列と一緒に使います。
Excel 2024 / Microsoft 365 では、TEXTBEFORE・TEXTAFTERで短く書けます。
⚡ フラッシュフィルで分ける
Excel 2013以降では、例を1つ入力するだけで、規則を読み取って残りを埋める機能が使えます。
- 氏名の隣の列の先頭に、姓を入力します(例:山田)。
- 下のセルに移り、Ctrl+E を押します。
- 結果を上から確認します。名の列も同じように作ります。
| 関数 | フラッシュフィル | |
|---|---|---|
| 結果 | 数式 | 値 |
| 元の氏名を直したとき | 自動で変わる | 変わらない(やり直す) |
| 新しい行 | 式をコピーする | やり直す |
| 向いている場面 | 毎月使う名簿 | 1回だけの整理 |
フラッシュフィルは例から規則を推測します。スペースの有無がそろっていない名簿では、間違った分け方になることがあります。行数の多い名簿では、エラーが出ないまま誤った値が入るので、特に確認が必要です。
Ctrl+E で何も起きないときは、「ファイル」→「オプション」→「詳細設定」の「フラッシュ フィル オプション」に関する設定を確認してください。
🚧 スペースのない氏名
「山田太郎」のようにスペースがない氏名を、規則だけで正しく分けることはできません。姓が1文字とは限らず、「林」「長谷川」「東海林」のように文字数がさまざまだからです。
姓の一覧がある場合は、最も長く一致する姓を探す方法で候補を作れます。D2:D100に姓の一覧を、文字数の少ない順に並べておきます。空白のセルがあると正しく動かないので、範囲は一覧の行数に合わせます。
一覧の中で、氏名の先頭と一致する最後の姓を返します。文字数の少ない順に並べるので、最も長い姓が選ばれます。一致しなければ #N/A になります。
これは候補を作る方法です。「東海林太郎」と「東 海林太郎」のように、一覧だけでは決められない場合があります。本人の情報と照らして、人が確認してください。
🔤 ふりがなを付ける
五十音順に並べるには、ふりがなの列があると便利です。Excelで入力した氏名なら、PHONETIC関数で取り出せます。
他のシステムから貼り付けた名簿では、漢字のまま返ることがあります。カタカナ・ひらがな・半角への変換と合わせて、PHONETIC関数の記事で説明しています。
📌 名簿を整えるときの注意
- 元の列を残す:分けた後も元の氏名の列を消さず、確認できるようにします。
- 姓名を戻す:
=C2&" "&D2のように結合すると、スペースのそろった氏名になります。 - 同姓同名:氏名だけでの照合は避け、社員番号などの番号で照合します。
- 異体字:「高」と「髙」、「崎」と「﨑」は別の文字として扱われます。置き換えるときは、本人の表記を確認してください。
- 個人情報:試すときは、架空の名簿か、コピーしたファイルを使います。