Excelのフラッシュフィル・Power Queryで名簿整理を10倍速にする方法

スポンサーリンク

Excelのフラッシュフィル・Power Queryで名簿整理を10倍速にする方法

「氏名を姓と名に分けたいけど、LEFTとFINDの組み合わせを毎回ググっている」「支店ごとの名簿を毎月手作業で結合している」——Excelで名簿を扱う現場では、こんな繰り返し作業に時間を取られがちですよね。

実は、Excelには関数を書かなくても名簿整理が爆速で終わる機能が2つあります。フラッシュフィル（Ctrl+Eで実行できるパターン自動補完機能）とPower Query（取得した処理手順を保存して再利用できるデータ変換ツール）です。

この記事では、姓名分割・メールアドレスからのドメイン抽出・表記ゆれ統一・複数ファイル結合という5つの実例を通じて、関数で力技を続けていたあなたの名簿整理を10倍速にする方法をお伝えします。

  1. 名簿整理が遅いのは「Excelの関数で頑張りすぎ」かもしれない
  2. フラッシュフィルとは？Excelで1回きりの整形を爆速にする機能
    1. 基本動作
    2. ショートカットは Ctrl+E
    3. 対応バージョン
  3. 【実例1】Excelで姓名分割をフラッシュフィルで一発処理
    1. 関数版（従来のやり方）
    2. フラッシュフィルでの手順
  4. 【実例2】Excelでメールアドレスからドメインを抽出する
    1. 関数版
    2. フラッシュフィルでの手順
  5. 【実例3】Excelで表記ゆれを統一する（全角半角・大文字小文字・スペース除去）
    1. 関数版
    2. フラッシュフィルでの手順
  6. Excelのフラッシュフィルが効かないときの対処法
    1. 対処1: 追加のサンプルを入力する
    2. 対処2: 自動フラッシュフィルの設定を確認する
    3. 対処3: 整形対象の隣接列に元データがあるか確認
    4. 対処4: 空白行を挟まない
    5. 対処5: それでも効かないなら Power Query へ
  7. Power Queryとは？Excelで繰り返し処理を自動化する仕組み
    1. 「適用したステップ」で処理を記録
    2. 更新ボタンで同じ処理を再実行
    3. 対応バージョン
  8. 【実例4】複数ファイルの名簿をExcel Power Queryで結合する
    1. 手順1: フォルダーから読み込む
    2. 手順2: ファイル名列を活用する
    3. 手順3: 整形してテーブルに出力
  9. 【実例5】毎月の名簿更新を「更新ボタン1つ」で再実行する
    1. 翌月、フォルダーに新ファイルを追加するだけ
    2. ファイルが入れ替わった場合も自動追従
  10. Excelのフラッシュフィル vs Power Query の使い分けフロー
    1. 一言まとめ
  11. まとめ｜Excelの名簿整理で時間を奪わせない
    1. 次に読みたい記事

名簿整理が遅いのは「Excelの関数で頑張りすぎ」かもしれない

Excelで名簿を整える定番パターンといえば、こんな数式ではないでしょうか。

=LEFT(A2, FIND(" ", A2)-1)
=MID(A2, FIND(" ", A2)+1, LEN(A2))

姓名を分割するだけで2つの数式を覚える必要があり、しかも全角スペースと半角スペースが混ざっているとエラーになります。データが変わるたびに数式の中身を確認するのは大変ですよね。

そこで頼りになるのが、Excelに最初から備わっているフラッシュフィルとPower Queryです。両者の役割を一言でまとめると、こうなります。

機能役割学習コスト
フラッシュフィル1回きりの整形を爆速で終わらせる低（Ctrl+Eだけ）
Power Query繰り返し処理を自動化して再利用する中（GUIで操作）

この使い分けが頭に入っていると、関数で書いていた数式の8割は機能側に任せられます。順番に見ていきましょう。

フラッシュフィルとは？Excelで1回きりの整形を爆速にする機能

フラッシュフィルは、Excel 2013から搭載されたパターン自動補完機能です。隣の列に整形例を1〜2件入力すると、Excelがパターンを推測して残りの行を一気に埋めてくれます。

基本動作

たとえばA列に「山田 太郎」「鈴木 花子」と並んでいるとして、B列の1行目に「山田」と入力します。続けてB2に文字を打ち始めると、Excelが灰色のサジェストで「鈴木」「佐藤」と候補を出してくれます。Enterで確定すれば一括補完が完了します。

ショートカットは Ctrl+E

自動サジェストが出ないときは、整形例を1〜2件だけ入力してからカーソルを次のセルに置き、Ctrl + Eを押すと一気にフラッシュフィルが実行されます。覚えるのはこのショートカット1つだけで大丈夫ですよ。

対応バージョン

  • Excel 2013以降のWindows版で利用可能
  • Excel for Macは2016以降で対応
  • 推奨はMicrosoft 365（最新のパターン推測アルゴリズム）

ここまで読んで「LEFTやMIDで書いていた処理が、それだけで終わるなら使ってみたい」と思いますよね。実例で見ていきましょう。

【実例1】Excelで姓名分割をフラッシュフィルで一発処理

名簿整理の最頻出パターン、姓名分割から始めます。

関数版（従来のやり方）

A列に「氏名」が入力されている前提で、B列に姓、C列に名を分けるなら従来はこう書いてきたはずです。

B2: =LEFT(A2, FIND(" ", A2)-1)
C2: =MID(A2, FIND(" ", A2)+1, LEN(A2))

数式はExcelのLEFT関数の使い方とExcelのFIND関数の使い方の組み合わせです。動作はしますが、全角スペースが混ざるとFINDがエラーを返します。

フラッシュフィルでの手順

  1. B2に「山田」と入力（A2の「山田 太郎」の姓部分だけ手で打つ）
  2. B3にカーソルを移動して Ctrl + E
  3. B列の残り行に「鈴木」「佐藤」と一気に入りました
  4. C列も同じ手順で名だけを抽出（C2に「太郎」と入力 → C3で Ctrl+E）
01 ui flash fill name split
A列（氏名）B列（姓）C列（名）
山田 太郎山田太郎
鈴木 花子鈴木花子
佐藤 一郎佐藤一郎

数式は不要。スペースが全角でも半角でも、フラッシュフィルがパターンを認識して処理してくれます。

02 result flash fill name split

「これだけで終わるの？」と拍子抜けする手軽さですよね。

【実例2】Excelでメールアドレスからドメインを抽出する

次は、メールアドレスから「@より後のドメイン」を抜き出すパターンです。フィルタや集計で「会社ドメインごとに分類したい」シーンで使えます。

関数版

ローカル部: =LEFT(A2, FIND("@", A2)-1)
ドメイン:   =MID(A2, FIND("@", A2)+1, LEN(A2))

ExcelのMID関数とFIND関数の組み合わせです。動きはしますが、毎回引数を考えるのは面倒です。

フラッシュフィルでの手順

A列に「taro.yamada@example.com」のようなメールアドレスが並んでいるとします。

  1. B2に「example.com」と手入力
  2. B3にカーソルを移して Ctrl + E
  3. B列の残り行に「sample.co.jp」「test.org」など、各行のドメインが一気に入る
A列（メール）B列（ドメイン）
taro.yamada@example.comexample.com
hanako.suzuki@sample.co.jpsample.co.jp
ichiro.sato@test.orgtest.org
03 result flash fill email domain

ローカル部（@より前）を取り出したいときも、同じ手順で1件だけ手入力すればOKです。会社ドメインを抽出してフィルタする業務がぐっと楽になりますよ。

【実例3】Excelで表記ゆれを統一する（全角半角・大文字小文字・スペース除去）

3つ目は、名簿で必ず発生する表記ゆれの統一です。フリガナの全角/半角が混在していたり、英数字に余計なスペースが入っていたりすると、並び替えやVLOOKUPで弾かれます。

関数版

スペース除去: =SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "　", "")
大文字統一:   =UPPER(A2)
全角→半角:    =ASC(A2)

ExcelのSUBSTITUTE関数を二重に使う構造で、書き慣れていないと読みにくいですよね。

フラッシュフィルでの手順

たとえばA列に「Yamada Taro」「suzuki hanako」「Sato Ichiro」のように、大文字小文字や余分なスペースが混在しているとします。

  1. B2に「YamadaTaro」と入力（スペース除去 + 名前の頭文字を大文字に揃える）
  2. B3にカーソルを移して Ctrl + E
  3. B列に「SuzukiHanako」「SatoIchiro」が一気に入る

複雑な置換ルールでも、Excelが「サンプルから推測」してくれるので、関数の入れ子を書く必要がありません。

「ここまで自動で読み取ってくれるの？」と驚くポイントだと思います。

Excelのフラッシュフィルが効かないときの対処法

ここまでで「フラッシュフィルは魔法か」と感じた方も多いはずですが、実は効かない場面もあります。代表的なトラブルと対処法を押さえておきましょう。

対処1: 追加のサンプルを入力する

サンプルが1件だけだとパターンを推測しきれず、結果が空白になることがあります。2〜3件目を手入力してから再度 Ctrl + E を押すと、認識精度が一気に上がります。

対処2: 自動フラッシュフィルの設定を確認する

  • [ファイル] → [オプション] → [詳細設定] を開く
  • 「自動フラッシュフィル」のチェックボックスがONになっているか確認
  • OFFになっていると、入力中のサジェストが出ません

対処3: 整形対象の隣接列に元データがあるか確認

フラッシュフィルは「隣接した列の値からパターンを推測」する仕組みです。整形元データから列を1つ空けてしまうと推測できないので、必ず元データの隣の列で実行してください。

対処4: 空白行を挟まない

整形対象範囲の途中に空白行があると、フラッシュフィルがそこでパターン推測を止めてしまいます。空白行は事前にフィルタや並び替えで詰めておきましょう。

対処5: それでも効かないなら Power Query へ

パターンが複雑すぎる、あるいはデータ量が大きすぎる場合は、Power Queryで明示的に処理を組むほうが確実です。次のセクションで見ていきましょう。

「効かないときは Power Query」と覚えておくだけで、行き詰まりが減りますよ。

Power Queryとは？Excelで繰り返し処理を自動化する仕組み

Power Queryは、Excel 2016以降に標準搭載されているデータ取得・変換ツールです。「データを読み込んで、整形して、テーブルに出力する」という一連の処理を「クエリ」として保存できます。

※ クエリ＝Power Queryで記録した、データ取得から変換までの一連の処理

「適用したステップ」で処理を記録

Power Queryエディターでは、列の分割や型変換などの操作を1つずつ「ステップ」として記録します。右側に並ぶ「適用したステップ」を見れば、どんな処理を順番に実行したかが一目でわかります。

※ ステップ＝クエリ内で実行される個別の変換処理（例: 列の分割、値の置換）

各ステップは後から編集・削除・並べ替えが可能です。「あ、ここで型変換を間違えた」と気づいたら、該当ステップだけを修正できます。

更新ボタンで同じ処理を再実行

Power Queryの真価は「再実行」にあります。元のデータが更新されたら、[データ] タブ → [すべて更新] をクリックするだけで、保存したクエリが同じ手順で再実行されます。毎月の名簿整理で、同じ作業を何度も繰り返す必要がなくなります。

対応バージョン

  • Excel 2016以降のWindows版で標準搭載（[データ] タブから利用可能）
  • Excel 2010/2013ではアドインとして提供
  • Excel for Macも段階的に対応中（Microsoft 365推奨）

Power Queryの基礎をもう少し詳しく学びたい方は、Power Query入門記事もあわせてご覧ください。

ここからは、フラッシュフィルでは難しい「複数ファイル結合」の実例を見ていきます。

【実例4】複数ファイルの名簿をExcel Power Queryで結合する

支店別の名簿が東京支店.xlsx、大阪支店.xlsx、福岡支店.xlsxの3ファイルに分かれているとします。これを1つのマスタにまとめる作業を、Power Queryで自動化します。

手順1: フォルダーから読み込む

  1. 3ファイルを同じフォルダー（例: C:UsersyouDocuments名簿）にまとめる
  2. [データ] タブ → [データの取得] → [ファイルから] → [フォルダーから] を選択
  3. 対象フォルダーのパスを指定して [OK]
  4. プレビュー画面で [結合] → [データの結合と変換] をクリック
  5. サンプルファイルとシートを指定（通常は1つ目のファイルの「Sheet1」）
  6. Power Queryエディターが開き、3ファイルが縦結合された状態で表示される

手順2: ファイル名列を活用する

結合結果には自動でSource.Nameという列が追加され、「東京支店.xlsx」「大阪支店.xlsx」のように元ファイル名が入っています。この列を残しておくと「どの支店のデータか」が後からわかって便利ですよ。

手順3: 整形してテーブルに出力

不要な列の削除や型変換を「適用したステップ」として記録したあと、[ホーム] タブ → [閉じて読み込む] でExcelシートにテーブル出力します。

Source.Name氏名部署メール
東京支店.xlsx山田 太郎営業yamada@example.com
大阪支店.xlsx鈴木 花子経理suzuki@example.com
福岡支店.xlsx佐藤 一郎総務sato@example.com

これで毎月「3ファイルを開いてコピペで結合」していた作業から解放されます。

【実例5】毎月の名簿更新を「更新ボタン1つ」で再実行する

Power Queryの真骨頂は、ここからです。

翌月、フォルダーに新ファイルを追加するだけ

来月、同じフォルダーに札幌支店.xlsxを追加したとします。やることはたった1つ。

07 ui power query refresh
  1. [データ] タブ → [すべて更新] をクリック

それだけです。クエリが自動で再実行され、4ファイルすべてが結合された状態でテーブルが更新されます。

ファイルが入れ替わった場合も自動追従

同じファイル名（例: 東京支店.xlsx）の中身が更新された場合も、フォルダー内のファイルを差し替えて [すべて更新] を押すだけで反映されます。

月作業時間（従来）作業時間（Power Query）
1ヶ月目30分（クエリ作成込み）30分（初回構築）
2ヶ月目以降20分 × 毎月30秒（更新ボタンのみ）

初回だけ30分かけてクエリを組めば、2ヶ月目以降は30秒で名簿マスタが更新できます。年間で考えると、数時間〜十数時間の節約になりますよ。

Excelのフラッシュフィル vs Power Query の使い分けフロー

ここまでで「両方便利なのはわかったけれど、どちらを使えばいいの？」と思いますよね。判断フローを表にまとめました。

質問YESならNOなら
Q1. 今回1回だけの作業ですか？フラッシュフィルQ2へ
Q2. データソースは1つのシート内ですか？フラッシュフィル or Power QueryPower Query
Q3. 複数ファイル / フォルダーをまたぎますか？Power Queryフラッシュフィル
Q4. 来月以降も同じ処理を繰り返しますか？Power Queryフラッシュフィル
Q5. パターンが複雑で推測しづらいですか？Power Queryフラッシュフィル

一言まとめ

  • 「1回きり、1シート内、シンプルなパターン」= フラッシュフィル
  • 「繰り返し、複数ファイル、複雑な変換」= Power Query

まずはフラッシュフィルから始めて、繰り返しが必要になった段階で Power Query にステップアップする、という流れが学習効率も高くておすすめです。

まとめ｜Excelの名簿整理で時間を奪わせない

ここまで、ExcelのフラッシュフィルとPower Queryを使った名簿整理の5実例を見てきました。最後にポイントを整理しておきます。

  • フラッシュフィル = Ctrl+Eで1回きりの整形を爆速化
    • 姓名分割、ドメイン抽出、表記ゆれ統一など
    • 効かないときは追加サンプル投入 or Power Queryへ
  • Power Query = クエリで処理を保存して繰り返し再利用
    • フォルダーから複数ファイル一括結合
    • [すべて更新]ボタンで毎月の処理を30秒に短縮
  • 使い分け基準 = 「1回きり vs 繰り返し」「1ファイル vs 複数ファイル」

関数で力技を続けてきた方ほど、フラッシュフィルとPower Queryに乗り換えたときの時短効果が大きいです。明日の業務で、まずは姓名分割をフラッシュフィルで試してみてくださいね。

次に読みたい記事

スポンサーリンク
タイトルとURLをコピーしました