「ファイルを開いてコピペ」を、更新ボタン1つにします
月末になると、各支店から売上表が届く。それを一つずつ開いて、集計用のファイルにコピーして貼り付ける。支店が10あれば、10回同じ作業をくり返すことになります。貼り付け位置のずれや、ファイルの取りこぼしも起きがちです。
パワークエリを使うと、この作業は次のように変わります。
| これまで | パワークエリ導入後 | |
| 毎月の作業 | ファイルを開く→コピー→貼り付けをファイルの数だけくり返す | ファイルをフォルダに入れて「すべて更新」を押す |
| ミスの起きやすさ | 貼り付け位置のずれ、取りこぼしが起きる | 毎回同じ手順で処理される |
| ファイルが増えたとき | 作業時間もその分増える | 作業は変わらない |
パワークエリとは
パワークエリ(Power Query)は、データの「取り込み」と「整形」を自動化するExcelの機能です。一度行った操作が手順として記録され、次回からは更新するだけで同じ処理がくり返されます。
マクロのようなコードを書く必要はありません。操作はほとんどマウスで完結します。
| 関数(①) | パワークエリ(②) | マクロ(③) | |
| 得意なこと | 同じブック内の参照 | 複数ファイルの結合と整形 | 保存や印刷を含む一連の手順 |
| 操作 | 式を入力 | マウス中心 | コードを書く |
| 結果の反映 | 即座に反映 | 「更新」を押したとき | 実行したとき |
※Windows版のMicrosoft 365とExcel 2016以降には、標準で入っています(「データ」タブの「データの取得と変換」)。Mac版では使える機能が限られるため、この記事はWindows版を前提にしています。
準備:フォルダとファイルのルールを決める
パワークエリでうまくまとめられるかは、元のファイルの作り方でほぼ決まります。始める前に、次の4つをそろえておきましょう。
- まとめたいファイルだけを、1つのフォルダに入れる:集計用のブックや関係ないファイルは、別の場所に置きます。
- 列の並びと見出しをそろえる:「日付」「日づけ」のような表記の違いも、別の列として扱われます。
- 見出しは1行目、1行だけにする:表の上にタイトルや空白行があると、加工の手間が増えます。
- シート名をそろえる:「売上」など、どのファイルも同じシート名にしておきます。
この記事では、次のようなフォルダを例にします。
売上データ(フォルダ)
├ 東京支店_202610.xlsx
├ 大阪支店_202610.xlsx
└ 福岡支店_202610.xlsx
どのファイルも「売上」シートに、「日付・商品コード・数量・金額」の4列があるとします。支店に配るテンプレートを統一し、入力用のセル以外はシート保護しておくと、列のずれを防げます。
手順:フォルダ内のファイルを1つの表にまとめる
集計用の新しいブックを開いて、次の順に操作します。
- 「データ」タブ→「データの取得」→「ファイルから」→「フォルダーから」を選びます。
- 「売上データ」フォルダを選び、「開く」を押します。
- フォルダ内のファイル一覧が表示されます。下の「結合」→「データの結合と変換」を選びます。
- 「ファイルの結合」画面で、左側から「売上」シートを選び、OKを押します。最初のファイルが「見本」になり、同じ処理がすべてのファイルに適用されます。
- Power Queryエディターが開き、全ファイルをつなげた表が表示されます。左端の「Source.Name」列には、元のファイル名が入っています。
- 必要に応じて、次の章の「便利な加工」を行います。
- 「ホーム」タブの「閉じて読み込む」を押すと、新しいシートに結合した表が出力されます。
これで、これまでファイルの数だけくり返していたコピペが、1回の設定で終わります。
便利な加工:集計しやすい形に整える
Power Queryエディターでの操作は、画面右側の「適用したステップ」に順に記録されます。間違えたときは、そのステップの左の「×」で取り消せます。よく使う加工を4つ紹介します。
1. ファイル名から「支店名」を取り出す
「Source.Name」列には「東京支店_202610.xlsx」のようなファイル名が入っています。この列を選び、「ホーム」タブ→「列の分割」→「区切り記号」で「_」を指定すると、「東京支店」と「202610.xlsx」に分かれます。支店別の集計ができるようになるので、ファイル名の付け方もルールに入れておくと便利です。
2. データの型を指定する
列見出しの左端にあるアイコン(ABCや123)をクリックすると、型を選べます。日付は「日付」、数量や金額は「整数」または「10進数」にしておくと、読み込んだあとの集計や並べ替えが正しく動きます。
3. 空白行や合計行を除く
各支店の表に合計行があると、そのままでは二重に数えてしまいます。日付列の「▼」を押し、「(null)」のチェックを外すと、日付が空の行(空白行や合計行など)をまとめて除外できます。
4. 列名を分かりやすくする
列見出しをダブルクリックすると名前を変えられます。「Source.Name.1」を「支店」にするなど、読み込んだあとに使いやすい名前にしておきましょう。
翌月からの使い方と、集計へのつなぎ方
毎月の作業は2つだけ
- 新しい月のファイルを「売上データ」フォルダに入れる(前月分を残すかどうかは、累計したいかで決めます)
- 集計用ブックで「データ」タブ→「すべて更新」を押す
これだけで、加工の手順も含めてすべてのファイルが読み込み直されます。
フォルダの場所を変えたいとき
「データ」タブ→「データの取得」→「データソース設定」から、参照先のフォルダを変更できます。共有フォルダに移したときや、年度ごとにフォルダを分けるときに使います。
読み込んだ表を集計する
読み込んだ表は、Excelの「テーブル」として出力されます。そのまま次のようにつなげられます。
- ピボットテーブル:支店別・商品別の売上を切り替えながら見る。「すべて更新」でピボットテーブルも一緒に更新されます。
- 関数:決まった形の報告書に数字を出す。SUMIFSやFILTERでこのテーブルを参照します(①関数編を参照)。
パワークエリで「集める」、関数やピボットテーブルで「集計する」と役割を分けるのが、いちばん壊れにくい作り方です。
まとめ
パワークエリを使うと、複数ファイルの転記は「フォルダに入れて更新する」だけになります。
- 最初に、フォルダとファイルのルール(列・見出し・シート名)をそろえる
- 「フォルダーから」→「データの結合と変換」で1つの表にまとめる
- 翌月からは「すべて更新」を押すだけ
まずは、先月分のファイルを使って試してみてください。今月のファイルが届いたら、フォルダに入れて更新するだけです。やってみてください。


コメント