今回の記事では、以下の2つのテーマについて詳しく解説していきます。
テーブルに変換せずにデータを取り込む方法
テーブル変換後も、元のデータの見やすさを保つ方法 それでは早速、始めていきましょう！
PowerQuery 表データクレンジング
今回使用するファイルとデータ内容
今日使用するファイルは、こちらの売上報告書です。
開くとこのようなデータが記載されています。
内容は以下の通りです。
支店名: 北海道から九州まで入っています。
商品のカテゴリー: OA機器、事務用品、ソフトウェアなど・・・
商品名: プリンター、シュレッターなど・・・
数量、単価、売上: 売上は計算式として入力されています。
支店によって販売している商品や数量が異なるデータです。
この売上報告書のタイトル「売上報告」はセルを統合して中央に表示されています。
PowerQueryへの取り込み：テーブル変換の場合
このデータをPowerQueryに取り込んでみます。
メニューの【データ】タブから【データの取得と変換】グループにある【テーブルまたは範囲から】をクリックします。
【テーブルの作成】ポップアップが表示され、取り込む範囲が合っているか確認します。
【先頭行をテーブルの見出しとして使用する】というチェックボックスがありますが、今回は【売上報告書】がタイトル行であり、列の項目名ではないため、チェックを入れずにそのまま「OK」をクリックします。
すると、この様な、PowerQueryエディタが立ち上がります。
これでPowerQueryエディターが立ち上がります。
今回は特に加工せず、一度エディター画面を閉じ、変更を破棄して終了します。
すると、元データがテーブルに変換され、メニュー項目に「テーブルデザイン」タブが増えているのが確認できます。
しかし、このテーブルの見た目が「あまり良くない」「元データは元のままにしたい」という方もいらっしゃるでしょう。次はその方法を見ていきましょう。
テーブルにしないデータ取り込み：範囲に名前を付ける方法
売上報告書のコピーデータ（売上報告2シート）を使って、テーブルにしない方法を説明します。
通常のデータは「範囲」と呼ばれます。この範囲に名前を付けることで、PowerQueryに範囲のまま取り込むことができます。
- まず、範囲の対象を指定します。今回は「売上報告」から「中国支店」までを選択します。
- 左上のボックスに付けたい名前（例：「売上報告」）を入力し、Enterキーを押します。
これにより、選択した範囲に名前が付けられます。この名前をクリックすると、いつでもその範囲が指定されるようになります。
この名前を付けた範囲をPowerQueryに取り込みます。
- 【データー】タブから【テーブルまたは範囲から】をクリックします。
PowerQueryエディターが立ち上がります。
この右側の【クエリの設定】を確認すると、名前ボックスで入力した、【売上報告】になっている事が分かります。
ここでも加工せずに一旦閉じ、変更を破棄します。
これで元データがテーブルに変わっていないことが確認できます。PowerQueryには取り込めていますが、元データはテーブルに変換されない状態を保てました。又、powerQueryエディタを起動します。
今回行いたい加工は、【支店名】と【カテゴリー名】の空欄を埋めてデータベースの形にすることです。
自動登録されたステップの削除とヘッダー設定
PowerQueryでは【昇格されたヘッダー】と【変更された型】というステップが自動的に登録されています。しかし、今回のデータでは【数量】【単価】【売上】の行を項目名としたいので、これらの自動ステップは削除します。
すると、この様になります。1行目の【売上報告】の行は必要ないので、削除していきます。
ホームメニューの【行の削除】から【上位の行の削除】を選択します。これは、上から何行分を削除するか？という事です。クリックして選択します。
先頭から削除する行数として【1】と入力し、【OK】をクリックします。
これで上1行が削除されます。
次に、現在の1行目を項目ヘッダーに設定します。
ホームタブの【1行目をヘッダーとして使用】をクリックします。
ヘッダーに【数量】【単価】【売上】が設定されます。空欄だった項目は【Column1】【Column2】などと自動で振られるので、ダブルクリックやF2キーで【支店名】【カテゴリー】【商品名】と入力し直します。
F2キーで編集モードになります。
フィル機能で空欄を埋める方法
【表示】タブから【列の分布】をクリックします。
下に【null】と表示されます。これは空欄であることを意味します。
空欄を埋める
【支店名】列を選択し、メニューの【変換】タブにある【フィル】ボタンをクリックし、【下】を選択します。
この【フィル】機能は、上にあるデータをコピーして下の空欄に貼り付けていく機能です。次のデータが見つかるまでコピーを続けます。これで【北海道支店】などのデータが下にきれいに埋まります。
空白スペースの落とし穴と置換処理
同様に【カテゴリー】列でもフィル【下】を実行しますが、一部のセルが埋まらないことがあります。
これは、本来空欄であるべきセルに半角スペースが入っているためです。PowerQueryはスペースを【データがある】と認識するため、フィル機能が働きません。
下の方を見てみても、【null】とは入力されてません。
この問題を解決するには、空白スペースを【Null】値に置換する必要があります。
【カテゴリー】列を選択し、【変換】タブの【値の置換】をクリックします。
【検索する値】に半角スペースを入力し、【置換後の値】には何も入力せず【OK】をクリックします。
ここで何も入れないと、スペースは消えますが【null】としては認識されません。
そのため、もう一度【値の置換】を開き、【置換後の値】に【null】と直接半角で入力します。
【カテゴリー】の列をコントロールキーを押しながら同時に選択し、【フィル】の【下】を実行します。これで両方の列がきれいになります。
ワークシートへの読み込みとピボットテーブルの作成
加工が完了したデータをワークシートに読み込みます。
ホームタブの【閉じて読み込む】の黒い三角ボタンをクリックし、【閉じて次に読み込む】を選択します。
【データのインポート】画面で、【テーブルとして】をチェックし、【既存のワークシート】を選択します。今回は既存のデータの隣（例：H3セル）に読み込みます。
これで、加工されたデータがテーブルとしてワークシートに読み込まれます。
ピボットテーブルへの変換
次に、このクエリを元にピボットテーブルを作成します。
【データ】タブから【クエリと接続】をクリックし、登録されているクエリを表示させます。
【売上報告】にカーソルを持って行くと、クエリの詳細が表示されるポップアップの一番下にある【…】をクリックし
【読み込み先】を選択します。
【データのインポート】画面で、【テーブル】の代わりに【ピボットテーブル】をクリックします。
既存のワークシートの読み込み先（例：H3セル）を指定し、【OK】をクリックします。
【データ損失の可能性】というメッセージが出ますが、これは元のテーブルがピボットテーブルに置き換わることを意味します。問題ないので【OK】をクリックします。
ピボットテーブルのフィールドが表示されます。
ピボットテーブルの編集
ピボットテーブルのフィールドが表示されるので、集計したい項目を設定します。
- 【金額】を【値】に入れます。
- 【支店名】を【行】に入れます。
これにより、支店ごとの売上がピボットテーブルで集計されます。
数値の書式設定と並べ替え
数値を見やすくするため、3桁ごとのカンマを設定します。
- ピボットテーブルのフィールドにある【値】の【売上】の横の矢印をクリックし、【値フィールドの設定】を選択します。
- 【表示形式】をクリックし、【数値】を選んで【桁区切り（,）を使用する】にチェックを入れ【OK】をクリックします。
ピポットテーブルの詳細は下記の記事をご覧ください。
次に、売上を大きい順に並べ替えます。
ピボットテーブル内で右クリックし、【並べ替え】から【降順】を選択します。
すると、表が降順に並び替えられます。
レポートレイアウトの変更
項目名【行ラベル】を【支店名】に修正します。
ピボットテーブルを選択した状態で、上のメニューの【デザイン】タブをクリックします。
【レポートのレイアウト】から【表形式で表示】を選択します。
これで【支店名】と正しく表示され、見やすい集計結果が得られました。
範囲データの更新：手動での範囲拡張が必要な場合
現在の【範囲】データに新しいデータを追加した場合、それが自動的に反映されるか確認してみましょう。
データの一番下に【四国支店】のデータを追加します。
【データ】タブの【すべて更新】をクリックします。
しかし、更新しても【四国支店】はピボットテーブルに表示されません。
これは、PowerQueryに読み込まれている大元のデータの【範囲】が、最初に名前を付けた【九州支店まで】に固定されているためです。新しいデータを追加しても、その範囲が自動的に広がるわけではありません。
この問題を解決するには、手動で範囲を広げる必要があります。
【数式】タブの【名前の管理】をクリックします。
【売上報告】を選択し、参照範囲の行番号を新しいデータの最終行（例：47行目から54行目）に修正し、【閉じる】をクリックします。
これで範囲が広げられたので、再度【データ】タブの【すべて更新】をクリックします。 すると、【四国支店】がピボットテーブルに追加されたのが確認できます。
このことから、範囲のままPowerQueryに取り込むことはできますが、データを追加した際には手動で範囲を広げる必要があるという点に注意が必要です。
テーブルの見た目を範囲のようにする工夫
【データを追加したら自動的に範囲が広がるようにしたいけど、範囲のように見やすくしたい】という要望もあるかと思います。そこで、テーブルとして持ちながら、見た目を範囲に近づける方法をご紹介します。
（【売上報告3】シートを使います）
まず、この範囲のデータをテーブルに変換します。範囲を指定し、Ctrl + Tキーを押すか、【挿入】タブの【テーブル】をクリックします。
【テーブルの作成】ポップアップで範囲を確認し、【先頭行をテーブルの見出しとして使用する】のチェックを外して【OK】をクリックします。
これでテーブルに変換されます。テーブルかどうかは、メニューバーに【テーブルデザイン】タブがあることで確認できます。
テーブルに変換すると、以下のような変化があります。
- 自動的に【列1】【列2】などの項目名が割り振られます。
- セルが統合されていた部分【売上報告】は分解され、データは一番左のセルに移動します。
- 1行おきに水色の縞模様が入るなどのデザインが適用されます。
テーブルの見た目を範囲に近づける設定
これを範囲のような見栄えにするには、以下の設定を行います。
- 見出し行の非表示
- テーブルを選択した状態で、【テーブルデザイン】タブをクリックします。
- 【見出し行】のチェックボックスを外します。これで項目名が非表示になります。
- 縞模様の削除
- 【テーブルデザイン】タブの【クイックスタイル】にある矢印をクリックします。
- 一番左上にある【なし】を選択するか、一番下にある【クリア】をクリックします。
これで、範囲の状態とかなり近い見た目になります。
テーブル機能の確認：自動更新の検証
この見た目を範囲に近づけたテーブルが、実際にデータを追加したときに自動で更新されるか確認しましょう。
このデータはまだPowerQueryに取り込んでいないので、先ほどと同じ加工をこれから行います。
【データ】タブから【テーブルまたは範囲から】をクリックします。
PowerQueryエディターが立ち上がったら、
自動ステップを削除し、1行目をヘッダーとして使用します。
すると、この様になりますので、ヘッダーに、支店名・カテゴリー・商品を入力します。
【カテゴリー】列の半角スペースを【Null】に置換し、その後、【支店名】と【カテゴリー】列をフィル【下】で埋めます。
データが整ったら、ワークシートに読み込みます。
- ホームタブの【閉じて読み込む】から【閉じて次に読み込む】を選択します。
- 今回は直接ピボットテーブルにしたいので、【ピボットテーブル】を選択し、既存のワークシート（例：H3セル）に読み込みます。
ピボットテーブルのフィールドで【売上】を【値】に、【支店名】を【行】に設定します。
数値のカンマ設定と降順並べ替えを行い、レポートレイアウトを表形式に変更します。
データの追加と自動更新の確認
いよいよ、新しいデータを追加して、それがちゃんと反映されるかを確認します。
- 【四国支店】のデータをテーブルの一番下に追加します。
- 【データ】タブの【更新】をクリックします。
すると、ピボットテーブルに【四国支店】が追加されたのが確認できます。 これは、テーブルであるため、PowerQueryが自動的に範囲を認識し、拡張してくれたためです。
念のため、【四国支店】の数値を大きくして、降順の並べ替えも自動で反映されるかを確認します。
- 【四国支店】の【数量】を大幅に増やします（例：16本を1600本に）。
- 【データ】タブの【更新】をクリックします。
ピボットテーブルを見ると、【四国支店】が一番上に移動し、数字も更新されているのが確認できます。
- パワークエリの何がすごいのですか？
-
Power Queryのすごいところは、データを出力後でも、新規データの更新が簡単なところです。 例えば、年度が替わり新しい「年度」のExcelファイルがフォルダ内に追加されたときに、関数を組みなおさなくても、自動でテーブルのデータが更新されます。 新規のデータを追加した場合でも、データの整形は不要です。
- パワークエリで使われる言語は？
-
Power Queryには、データを取得するためのグラフィカルインターフェイスと、専用のPower Queryエディターが付属しています。 Power Queryでは、M式言語と呼ばれるスクリプト言語が使用されます。 M言語は、Power Queryのデータ変換言語であり、クエリはすべてM言語で記述されます。
まとめ
今回の動画は以上となります。いかがでしたでしょうか？
- PowerQueryでデータを取り込む際、範囲のまま取り込むことも、テーブルとして取り込むことも可能です。
- 範囲のまま取り込む場合は、データの追加時に手動で範囲を広げる必要があります。
- テーブルとして取り込むと、データの追加時に自動で範囲が拡張される利点があります。
- テーブルの見た目を範囲に近づけるには、見出し行の非表示や縞模様のクリア、そして【選択範囲内で中央】の書式設定を活用することで、見栄えと機能性を両立できます。
PowerQueryで実務が少しでも楽に、効果的にできるための参考にしていただければ幸いです。
■__________________________________■
メルマガ登録でお渡しする
【10大】
特典をプレゼント中↓↓↓
✅Powerquery教科書（PDF版）
✅MOS教科書（PDF版）
✅Ctrlキーのショートカット集
✅現金出納帳フォーマット
✅家計簿フォーマット
✅備品管理表フォーマット
✅提出物が揃ったら自動で完成（提出物確認）
✅備品注文書（チェックボックスで管理）
✅ガントチャートフォーマット
✅目次自動作成ツール
＼ 無料メルマガに登録してプレゼントの受け取りはここから／
コメント