「取り込んだデータが汚くて、毎回手作業で直している」――そんな経験はありませんか。表記ゆれ、いらない空白、文字列のままの数値や日付。分析を始める前の下準備だけで、ごっそり時間を取られてしまいますよね。
データ活用は前処理が労力の8割を占めるとも言われます。そこを毎回手作業でこなしていると、肝心の分析にたどり着く前に疲れ果ててしまいます。しかもコピペや手修正は、直し忘れや誤操作のもとです。
そこで役立つのが Power Query です。値の置換やデータ型の変換といった整形操作を一度設定すれば、その手順がステップとして記録されます。次回からは元データを差し替えて[更新]を押すだけ。同じクレンジングが自動で走ります。
この記事では、実務でありがちな「汚いデータ」を題材に、整形の基本となる4つの操作を解説します。置換・行削除・データ型変換・条件列の順に、ひとつずつ手を動かしながら覚えていきましょう。
Power Queryのデータ整形とは？
Power Query のデータ整形とは、取り込んだ生データを「分析できる形」に整える一連の処理のことです。具体的には、表記の統一、不要なデータの除去、型の修正、区分の付与などを指します。
大きな特長は、操作そのものではなく 操作の手順が記録される 点です。Excel のシート上で直接セルを直すと、その作業は1回きりで消えてしまいます。一方 Power Query では、行った操作が「適用したステップ」として右側に積み上がっていきます。
そのため、来月また同じ形式のデータが届いても、整形をやり直す必要はありません。元データを入れ替えて更新するだけで、記録済みの手順が頭から順に再実行されます。これが手作業との決定的な違いです。
Power Query の全体像をまず押さえたい方は、Power Queryの使い方｜完全ガイドで基本操作から確認しておくと、この記事の内容がスムーズに理解できます。
この記事で扱うサンプルデータ
今回は、次のような「ありがちな汚い顧客リスト」を整えていきます。手入力やシステム出力が混ざった、実務でよく見かけるタイプのデータです。
| 顧客名 | 区分 | 売上 | 登録日 |
|---|---|---|---|
| 山田 商事 | 法人 | 120000 | 2025/04/01 |
| 鈴木 太郎 | 個人 | 35000 | 2025/4/2 |
| （空白行） | |||
| 佐藤物産 | ほうじん | 80000 | 2025/04/03 |
「法人」と「ほうじん」の表記ゆれ、名前に混ざった全角・半角スペース、空白行、文字列扱いの売上金額。こうした問題を、これから4つの操作で順番に片付けていきます。
前提とエディターの開き方
操作は Power Query エディター の中で行います。Excel でデータ範囲を選び、リボンの[データ]タブから[テーブルまたは範囲から]をクリックすると、エディターが起動します。
Power Query は Excel 2016 以降に標準搭載されています。Excel 2010・2013 では Microsoft 公式の無料アドインを追加すれば利用できます。データの取り込み方そのものに不安がある方は、Power Queryの始め方で取り込み手順から確認してみてください。
操作1: 値の置換で表記ゆれをそろえる
最初に片付けるのは表記ゆれです。サンプルでは区分列に「法人」と「ほうじん」が混在しています。このままでは集計のとき別物として数えられてしまいます。
値の置換を使えば、特定の文字列を別の文字列に一括で書き換えられます。Excel の[置換]機能とよく似ていますが、こちらは手順として記録される点が違います。
置換の手順
- 整えたい列（ここでは「区分」列）の見出しをクリックして選択します
- リボンの[ホーム]タブにある[値の置換]をクリックします
- [検索する値]に
ほうじん、[置換後]に法人と入力します - [OK]をクリックすると、対象の文字列がすべて置き換わります
これで区分が「法人」に統一されました。右側の「適用したステップ」に「置換された値」という行が追加されているはずです。これが記録された手順です。
スペースや記号もまとめて消せる
置換は、いらない記号や文字を削除する用途にも使えます。[置換後]を空欄のままにすれば、検索した文字を「何もない状態」に置き換える、つまり削除できます。
たとえば電話番号のハイフン、金額についた「円」の文字、住所に紛れた余計な記号などを一気に取り除けます。「消したい文字を空文字に置換する」と覚えておくと応用が利きます。
なお、列をまたいだ複雑な分割や結合が必要な場合は、置換ではなく専用の操作が向いています。詳しくはPower Queryで列を分割・結合する方法を参考にしてください。
操作2: 不要な行と空白を削除する
次は、分析の邪魔になる空白行と、セル内に紛れ込んだ余計な空白を取り除きます。サンプルには中ほどに空白行が1行入っていましたね。
空白行を削除する
空白行はワンクリックでまとめて消せます。手順は次のとおりです。
- リボンの[ホーム]タブで[行の削減]をクリックします
- メニューから[行の削除]→[空白行の削除]を選びます
これで、すべての列が空のままになっている行が一括で取り除かれます。手作業で1行ずつ探して削除する必要はありません。
セル内の余分な空白を取り除く
顧客名の「山田 商事」「鈴木 太郎」のように、文字の間や前後に空白が混ざっているケースもよくあります。こうした空白は[トリミング]という機能で整えられます。
- 整えたい列（「顧客名」列）の見出しを選択します
- [変換]タブの[書式]をクリックします
- メニューから[トリミング]を選びます
トリミングは、文字列の 前後 にある余分な空白を削除する機能です。ただし、単語と単語の間にある空白には作用しません。間の空白まで消したい場合は、操作1で紹介した値の置換で「半角スペースを空文字に置換」する方法を併用します。
データの集計まで一気に進めたいときは、整形後にPower Queryのグループ化で集計する方法へ進むと、件数や合計をまとめて出せます。
操作3: データ型を変換する
3つ目はデータ型の変換です。これは見落とされがちですが、とても重要な操作です。
取り込んだ直後のデータは、数値や日付であっても「文字列(テキスト)」として扱われていることがあります。文字列のままだと合計の計算ができなかったり、日付として並べ替えできなかったりします。
文字列を数値・日付に変える
売上列を数値に、登録日列を日付に変換してみましょう。手順はどちらも同じです。
- 型を変えたい列の見出しを選択します
- 列見出しの左にある小さなアイコン(ABCや123のマーク)をクリックします
- 表示されたメニューから適切な型を選びます
売上列なら[整数]、登録日列なら[日付]を選びます。これで売上は計算できる数値に、登録日はきちんとした日付として認識されます。
| 変換前の型 | 変換後に選ぶ型 | 主な用途 |
|---|---|---|
| 文字列(テキスト) | 整数・10進数 | 合計・平均などの計算 |
| 文字列(テキスト) | 日付 | 期間の絞り込み・並べ替え |
| 文字列(テキスト) | 真偽値 | TRUE/FALSEの判定 |
型変換でエラーが出たときは
型を変換したとき、一部のセルに Error と表示されることがあります。これは、その値を指定した型に変換できなかったことを示します。
たとえば数値列に「未定」という文字が混ざっていると、数値に変換できずエラーになります。原因の文字を操作1の置換で先に取り除いてから、もう一度型変換すると解決することが多いです。手順を入れ替えるだけで直せるのも、ステップが記録されている Power Query の強みです。
操作4: 条件列で区分を自動で付ける
最後は条件列です。これは、ある列の値に応じて「もし〜なら〜」と区分を自動で付ける機能です。Excel の IF 関数を、画面の入力だけで設定できると考えるとイメージしやすいですよね。
サンプルの売上列をもとに、「10万円以上なら大口、それ未満なら通常」という区分を新しい列として作ってみます。
条件列の作り方
- リボンの[列の追加]タブをクリックします
- [条件列]をクリックすると、設定画面が開きます
- [新しい列名]に
売上区分と入力します - 条件部分を「列名: 売上」「演算子: 次の値以上」「値: 100000」に設定します
- [出力]に
大口と入力します - 下部の[それ以外の場合]に
通常と入力します - [OK]をクリックすると、判定結果が入った新しい列が追加されます
これで、売上が10万円以上の行には「大口」、それ未満の行には「通常」が自動で入ります。元データが変わっても、更新すれば判定もやり直されます。
条件を増やすこともできる
判定を3段階以上にしたいときは、設定画面の[句の追加]をクリックします。これで条件を上から順に並べられます。
たとえば「30万円以上なら最重要、10万円以上なら大口、それ以外は通常」のように、ランク分けを作れます。条件は上から順に評価されるので、 金額の大きい条件から並べる のがポイントです。
さらに細かい計算式や、関数を使った独自の列を作りたくなったら、M言語に踏み込むと表現の幅が広がります。興味が出てきたらPower QueryのM言語でカスタム列を作る方法をのぞいてみてください。
うまくいかないときの対処法
操作を進めるなかで詰まりやすいポイントを、ここでまとめておきます。
- ステップを間違えた: 右側の「適用したステップ」で該当行の左にある×印をクリックすれば、その操作だけ取り消せます。最初からやり直す必要はありません。
- 置換が効かない: 前後に空白が残っていると別の文字列と判定されます。先にトリミングで空白を取ってから置換すると、うまく一致します。
- 更新したら型がリセットされた: データ取り込みのステップより前に型変換が入っていないか確認しましょう。基本は「取り込み→整形」の順に並べます。
整形がひととおり終わったら、リボンの[ホーム]タブから[閉じて読み込む]をクリックします。整えたデータが Excel のシートに表として出力されます。
まとめ
Power Query のデータ整形は、置換・行削除・データ型変換・条件列の4つを押さえれば、汚いデータの大半をきれいにできます。最後にポイントを振り返っておきましょう。
- 置換: 表記ゆれをそろえる。空文字に置換すれば不要な文字も削除できる
- 行・空白の削除: 空白行はワンクリック、セル内の空白はトリミングで除去
- データ型変換: 文字列の数値や日付を、計算・並べ替えできる正しい型に直す
- 条件列: IF のような分岐で、区分やランクを自動で付与する
なにより大きいのは、これらの操作がすべて手順として記録されることです。一度組んでしまえば、来月も再来月も[更新]ボタンひとつで同じクレンジングが走ります。毎回の手作業から解放されて、本来やりたい分析に集中できるようになります。
まずは手元のよく使うデータで、この4操作を一度試してみてください。基本操作を体系的に押さえたいときは、Power Queryの使い方｜完全ガイドも合わせて読むと、ぐっと理解が深まります。
