データが届くたびに、表記ゆれの修正や空白の処理といった作業が多いと、本来の集計や分析に充てる時間が不足してしまいます。
こうしたデータクレンジング（データの整理・修正作業）の悩みは、効率的な作業方法の選択肢を知ることで改善できます。
なお、AI活用が進む現在でも本記事の内容は役立ちます。AIに作業を依頼したり、出力結果を確認したりする場面で、「Excelのデータ整理で何ができるか・何が起きているか」を知っていることが、指示の精度と判断の土台になります。
本記事では、当ブログで紹介してきたテクニックを「実務でそのまま使える10のステップ」として整理しました。「データを集める」→「形を整える」→「中身をきれいにする」→「集計・出力・自動化する」という流れで読み進めることで、バラバラのデータを使えるデータに変える手順が一通り揃います。
この記事の使い方： 全ステップをはじめから読む必要はありません。 下の「自分の詰まりポイントを探す」早見表で、今一番時間を取られているステップを確認してから、そこだけ読んでもOKです。
【目的・課題別】解決策の早見表
| こんな状況で困っている | 読むべきSTEP | 使う主なツール |
|---|---|---|
| WebやkintoneのデータをExcelに取り込みたい | STEP 1 | Power Query (API) |
| 複数シート・ブックのデータをまとめたい | STEP 2 | Power Query |
| 横に広がった表を縦のリストに直したい | STEP 3 | Power Query |
| 全角・半角が混在していて集計できない | STEP 4 | Power Query |
| 住所や商品名から一部だけ取り出したい | STEP 5 | 365関数／Power Query |
| 日付がテキストのままで計算できない | STEP 6 | Power Query |
| 名簿照合や突合作業に時間がかかっている | STEP 7 | XLOOKUP／Power Query |
| ピボットテーブルで文字列を集計したい | STEP 8 | Power Pivot |
| 整えたデータをWordやPDFに自動で流し込みたい | STEP 9 | VBA |
| 毎回同じ手順のファイル処理を自動化したい | STEP 10 | VBA／Power Automate |
内容：
10ステップの全体像
データ整理の作業（データクレンジング）は、大きく4つのフェーズに分かれます。「同じ種類のデータを1つに集約してから、一括でクレンジングを適用する」のが、手戻りをなくす最大のコツです。
なぜこの順番なのか： STEP 1〜2でデータを1つに結合してから、STEP 3で縦長に整え、STEP 4以降でクレンジングを適用します。この順番にすることで、「ファイルごとに同じ処理を繰り返す」という無駄がなくなり、1回の設定で全データをまとめて処理できるようになります。
フェーズ1：データの収集・統合
集計対象となるバラバラのデータを、まず1つのテーブルに引き込みます。ここでデータを集約することが、後続のクレンジング処理を1回で済ませる前提になります。
STEP 1：外部データ（Web/kintone）と連携する
スキップ可能なデータの状態： ExcelファイルやCSVファイルのみを扱っており、外部システムとの連携が不要な場合。
手入力やCSVダウンロードを経由せず、WebページやシステムのAPIから直接データをExcelに取り込むことで、転記ミスと作業時間を削減できます。 例えば、Power QueryのWeb接続機能はAPI接続も可能です。「更新」ボタン1つで最新データをExcelに同期する仕組みを作ることができます。
API連携時の注意： API経由の取得にはシステムごとの上限がある場合があります（例えば、kintoneでは「1回あたり500件まで」など）。大量データを扱う場合は、Power Query側でページネーション（オフセット）処理の組み込みが必要になるケースがあります。
🔗 【Power Query】WebページからデータをExcelに読み込む事例（kintoneのAPI連携）- shikumika's diary
STEP 2：バラバラのデータを「一つ」にまとめる
スキップ可能なデータの状態： 集計対象のデータがすでに1つのシートに集約されている場合。
月別・部署別など複数のシートやブックに分かれたデータを、1つのリストに統合します。コピー貼り付けで対応していると、シート追加のたびに同じ作業が発生します。Power Queryの「フォルダから取得」機能を使うと、フォルダにファイルを追加するだけで自動的に全データが結合された状態にできます。ここでデータを1つにまとめておくことで、次のフェーズ以降の表記揺れ修正などを1回の処理で全ファイル分まとめて終わらせることができます。
ブック内の複数シートを結合する
🔗 【Power Query】ブック内にある複数シートの表を一つのシートに結合する方法 - shikumika's diary
複数シートをまとめる方法の比較
Power Query以外の方法（関数・VBA・ツール）との使い分けを整理しています。
🔗 【Excel】複数のシートをまとめる方法（ツール含む）の比較 - shikumika's diary
フェーズ2：構造の整理（正規化）
データが集まったら、クレンジング処理を効率よく適用できるように表の「骨格」を変えます。この変換を先に行うことで、後続のクレンジング対象となる列数を最小化できます。
STEP 3：データの構造を「集計用」に組み替える
スキップ可能なデータの状態： データがすでに「1行1レコード」の縦長リスト形式になっている場合。
月ごとに横に列が増えていく「クロス集計形式（マトリクス表）」は、ピボットテーブルや関数での集計に向きません。また、この状態でSTEP 4以降のクレンジングを行うと、横に広がる列すべてに個別で処理を設定しなければならなくなります。Power Queryの「列のピボット解除」を先に適用して縦長リストに変換してから、STEP 4以降の処理を行うことで、「値」列に対する1回の設定で全データのクレンジングが完了します。
将来的に月や年といった列が増えることを想定する場合、Power Queryでは固定列（顧客名など）を選択して「その他の列のピボット解除」を実行するのが、列追加時のエラーを防ぐ設定のコツです。
マトリクス表を縦長リストに変換する
🔗 【Power Query】列のピボット解除で、マトリクス表をリストに変換 - shikumika's diary
フェーズ3：データの浄化・整形
1つにまとまり、縦長になったリストに対して、エラーの原因となるノイズを取り除き、表記を統一します。フェーズ1〜2を先に完了させているため、ここでの設定は全データに一括で適用されます。
STEP 4：不要な「ノイズ」を取り除く
スキップ可能なデータの状態： データの表記がすでに統一されており、全角・半角の混在や余分な記号・空白がない場合。
表記揺れの解消（全角・半角の統一）
集計エラーの原因となる不要な情報を一括で排除します。「A１」と「A1」が混在しているとVLOOKUPやCOUNTIFで正しく集計できません。Power Queryの変換機能を使うと、列単位で一括処理できます。
また、全角・半角の変換はPower Queryに標準関数がないため、カスタム列やText.Replace関数を用いる必要があります。特定の文字だけ置換したいなど複雑なパターンには、Power Queryの「例からの列」機能が役立ちます。「こう変換したい」というサンプルを1〜2件入力するだけで、Power Queryが変換ルールを自動生成します。
🔗 【Power Query】全角を半角に一括置換（Text.Replaceを使う時の注意点）- shikumika's diary
不要な記号・スペースの除去
文字列に混入した記号やスペースを取り除き、数値として計算できる形に変換します。数字以外の文字が混じっているだけで、SUM関数が正しく動作しないケースがあります。空欄（null）が含まれるデータの処理には注意が必要ですが、Power Queryで列ごとに一括処理できます。
🔗 【Power Query】Excelで文字列から数字だけ抽出する方法 - shikumika's diary
STEP 5：文字列から必要な情報を切り出す
スキップ可能なデータの状態： 1つのセルに1種類の情報しか入っておらず、分割の必要がない場合。
「住所から都道府県だけ取り出す」「商品コードから型番部分だけ抽出する」といった作業です。目的に応じて、Excel 365の関数とPower Queryを使い分けると効率的です。
| 状況 | 適したツール |
|---|---|
| セル内の値をその場で参照・表示したい | 365関数（TEXTBEFORE/AFTER） |
| 区切り文字が複雑・正規表現が必要な場合 | 365関数（REGEXEXTRACT） |
| 大量データを列ごとに一括変換・繰り返し更新する | Power Query |
| Excel 2019以前の環境と共有する | Power Query または従来関数（LEFT/MID） |
TEXTBEFORE・TEXTAFTER関数の活用（Excel 365）
特定の文字の「前」「後ろ」を指定するだけで抽出できます。MID関数とFIND関数を組み合わせていた複雑な数式が、1つの関数で置き換えられます。さらに複雑なパターン（電話番号・メールアドレスの抽出など）には、REGEXEXTRACT関数（正規表現）が有効です。
動作確認環境： Excel 365（Microsoft 365）、Windows 11（2024年確認）
バージョン互換性の注意： TEXTBEFORE・TEXTAFTER関数はExcel 2021以前のバージョンでは使用できません。またREGEXEXTRACTは2024年に導入された機能で、最新のMicrosoft 365またはExcel 2024以降でのみ動作します。古い環境と共有する場合は、Power Queryでの処理を推奨します。
🔗 【Excel】特定の文字列の間の値を抽出する方法（TEXTBEFORE/ TEXTAFTER 関数）- shikumika's diary
🔗 【Excel】特定文字の前と後ろに分割して抽出する数式事例（FIND・TEXTBEFORE・REGEXEXTRACT比較）- shikumika's diary
STEP 6：日付データを「計算できる形」に変える
スキップ可能なデータの状態： 日付がすでにシリアル値（Excelが認識できる日付形式）として入力されている場合。
文字列として入力された日付は、そのままではDATEDIF関数やTODAY関数との計算ができず、ピボットテーブルでのグループ化も正しく動作しません。Excelが認識できる「シリアル値」に変換する必要があります。
表記ゆれがある日付文字の変換
「2024/1/5」「2024-01-05」「2024.1.5」「令和6年1月5日」など複数の形式が混在していても、Power Queryの「型の変更（ロケール使用）」機能を使うと、一括でシリアル値に変換できます。
和暦「元年」変換のコツ： Power Queryで和暦文字列をシリアル値に変換する場合、「元年」という文字列がエラーを引き起こすことがあります。日付型へ変換する前に、値の置換で「元年」を「1年」に置き換えておくと対処できます。
🔗 Excelで表記ゆれがある日付文字を日付データ（シリアル値）に変換 - shikumika's diary
和暦表示への変換（「元年」対応）
集計に使うデータをシリアル値に変換しつつ、出力帳票には和暦で表示したいケースに対応します。
🔗 Excelでシリアル値のまま西暦から「元年」も含む和暦表示に変更 - shikumika's diary
STEP 7：検索と突合を自動化する
スキップ可能なデータの状態： 名簿照合や一致確認の作業がなく、単一テーブル内で集計が完結する場合。
「名簿から情報を探す」「2つのリストの一致・不一致を確認する」作業は、XLOOKUP関数とPower Queryのマージ機能のどちらでも対応できます。目的に応じて使い分けると効率的です。
| 状況 | 適したツール |
|---|---|
| 1件ずつその場で値を引きたい（都度参照） | XLOOKUP関数 |
| 片方にしかないレコードを抽出したい（抜け漏れチェック） | Power Queryのマージ |
| 2つのリストを横に並べて差分を確認したい | Power Queryのマージ |
| 数万行を超える大量データで繰り返し実行する | Power Queryのマージ |
| Excel 2019以前の環境と共有する | VLOOKUPまたはPower Query |
XLOOKUP関数の導入
VLOOKUPでは検索列より左のデータを返せない、列の挿入で参照がずれるといった制約がありました。XLOOKUP関数はこれらの制約を解消しており、エラー時の表示も関数内で指定できるためメンテナンスもしやすくなっています。
動作確認環境： Excel 365（Microsoft 365）（2024年確認） ※ Excel 2019以前のバージョンではXLOOKUP関数は使用できません。
大量データでの注意： 数万行を超えるデータでXLOOKUPを大量に使用すると、再計算が重くなる場合があります。その場合はPower Queryの「マージ（結合）」機能を使う方がパフォーマンスが安定する傾向があります。
🔗 【Excel】XLOOKUP関数の特徴と活用事例 - shikumika's diary
リスト間の抜け漏れチェック・差分確認（Power Query）
「社員リストに出席リストを突合して欠席者を抽出する」「前回と今回の一覧を比較して変更箇所を洗い出す」といった処理は、Power Queryのマージ機能が適しています。結合の種類（右外部・完全外部など）を選ぶことで、一致しないレコードだけを抽出できます。
🔗 【Power Query】データの抜け漏れチェック（リストにないデータの抽出）- shikumika's diary
🔗 【Power Query】データ比較のためにExcelシートを左右で結合する方法 - shikumika's diary
フェーズ4：集計・出力・自動化
きれいになったデータを最大限に活用し、最終成果物の作成と全工程の自動化を行います。
STEP 8：ピボットテーブルを使い倒す
スキップ可能なデータの状態： 集計結果を数値（合計・件数）のみで確認できればよい場合。
整えたデータをいよいよ集計・可視化します。ピボットテーブルは数値の集計が得意ですが、Power Pivot（データモデル）を活用することで文字列もそのまま表示できます。
値フィールドに文字列を表示する
通常のピボットテーブルでは「値」エリアに設定できるのは数値の集計（合計・件数など）に限られます。ピボットテーブル作成時に「このデータをデータモデルに追加する」にチェックを入れることで、文字列を表示するための「メジャー（DAX関数）」が使用できるようになります。MAX関数で単一の文字列を表示する方法と、CONCATENATEX関数で複数の値を結合して表示する方法があり、交点に複数のデータが重なる場合はCONCATENATEXが有効です。
🔗 Excelピボットテーブルの値フィールドに、文字列をそのまま表示する方法 - shikumika's diary
STEP 9：完成したデータを「成果物」に反映する
スキップ可能なデータの状態： 集計結果をExcelのリスト表のまま提出・共有する場合。
整えたリストデータから、個人別・案件別の単票書類（PDFや個別Excelファイル）を生成します。要件に応じて2つのアプローチを使い分けられます。
- Excel完結型： テンプレートシートにVLOOKUP関数を仕込み、VBAでIDを切り替えながら連続印刷・別名保存する
- Word連携型： Wordの「差し込み印刷」機能とVBAを連携させ、より複雑なレイアウトのPDFを一括生成する
リストから単票・PDFを一括作成する
名簿や受注データから、個人別・案件別の書類を自動生成する方法をまとめた事例集です。
🔗 【Excel】リストから単票のシートやPDFを作成する事例一覧 - shikumika's diary
STEP 10：定型処理をワンクリック化する
スキップ可能なデータの状態： 毎回内容が変わるアドホックな作業や、1回きりの処理の場合。
ここまでの工程も含め、毎回同じ手順で行う処理を自動化するには、作業の内容によってツールを使い分けます。
自動化の準備工数とのバランスは必要ですが、一度フローを作成してしまえば、次回以降はボタンひとつで処理を完了できます。
Excel内で完結する処理ではVBAの方がシンプルに書ける場面が多い印象ですが、目的に合わせて選ぶことが重要です。
| 状況 | 適したツール |
|---|---|
| ファイルを開く・保存・コピーなどのファイル操作 | VBA（FileSystemObject） |
| データの取り込み・変換・集計の繰り返し | Power Query |
| Webページへの入力・転記など画面操作の自動化 | Power Automate Desktop |
| 数十件程度のシンプルな繰り返し処理（コードを書きたくない） | Power Automate Desktop |
OneDrive・SharePoint環境での注意： VBAの
FileSystemObjectやDir関数でフォルダ内のファイルを処理する場合、そのフォルダがOneDriveやSharePointと同期されていると、パスがURL形式（https://...）で取得されてしまい、実行時エラー52などが発生することがあります。クラウド連携環境下では、VBA内でURLをローカルパスに変換する処理の組み込みを検討してください。
フォルダ内ファイルのループ処理（VBA）
FileSystemObjectを使うと、フォルダ内の全ファイルに対して順番に同じ処理を実行できます。「特定フォルダ内の全ファイルをCSVとして別名保存する」といったファイル操作タスクが得意分野です。
動作確認環境： Excel 365（Microsoft 365）、Windows 11（2024年確認）
🔗 【Excel VBA】指定フォルダ内のExcelファイルを順番に処理（FileSystemObjectの利用）- shikumika's diary
フォルダ内ファイルのループ処理（Power Automate Desktop）
コードを書かずにGUI操作でフローを組み立てられます。Excelの操作だけでなく、Webページへの入力など画面をまたいだ処理も自動化できる点がVBAとの違いです。
🔗 【Power Automate】ユーザーが選択したフォルダ内のファイルを順番に処理する事例 - shikumika's diary
まとめ
- データ整理（データクレンジング）は「収集・統合 → 構造変換 → 浄化・整形 → 集計・出力・自動化」というETL的な考え方に基づく10ステップで体系的に進められる
- Power Query・Excel関数・VBA・Power Automateの4つのツールを目的（データ変換か、ファイル操作か、画面操作かなど）に合わせて使い分けるのが、手作業を減らすための基本方針になる
- TEXTBEFORE・REGEXEXTRACTなどの最新関数やPower Queryの「例からの列」機能を活用することで、複雑な数式を書かずに高度な変換処理ができる
- 一度フローを組み立ててしまえば、次回以降は「更新」ボタンを押すかマクロを実行するだけで、繰り返しの手作業が完了する
