Excel Power Query データ収集・整形 自動化入門

マクロも VBA も必要なし。標準機能でここまでできる!

「CSV や Web 上のデータを取得して、集計・抽出・並べ替え・不要データの削除などの加工を行う」といった面倒なデータの収集・整形作業を自動化しませんか?
Excel に［データの取得と変換］メニューとして標準で搭載されている Power Query (パワークエリ) の使い方を、基本からやさしく解説します。

・業務システムのレポートファイルから請求金額を集計
・採用情報のデータ整形
・論文検索で表示された不規則なリストの整形
・ゲートの出退勤データからタイムシートを作成
等々、あなたの業務をラクラク効率化できます!

毎月届く CSV や業務システムから出力したレポートを、毎回同じ手順で並べ替え、不要な行を消し、集計し直している人に向けた一冊です。『Excel Power Query データ収集・整形 自動化入門』 (E-Trainer.jp 著、秀和システム、2021 年 9 月刊) は、Excel に［データの取得と変換］メニューとして標準で搭載されている Power Query (パワークエリ) の使い方を、基本からやさしく解説した入門書です。版元の紹介文は「マクロも VBA も必要なし。標準機能でここまでできる!」と掲げ、CSV や Web 上のデータを取得して集計、抽出、並べ替え、不要データの削除といった加工を行う作業を自動化できると案内しています。

全 8 章と巻末資料の構成、Power Query の基本から表の操作まで

本文は全 8 章と巻末資料です。第 1 章「Power Query の基本」は、Power Query が必要な理由、マクロや VBA との違い、Power Query エディターの画面構成、M 言語と数式バーの使い方を経て、業務に使用する際に考慮すべき事項までを扱います。第 2 章「クエリの作成と管理」は、開いているブックのテーブル、ブック内のシートやフォルダー内のブックの一括取得、CSV ファイル、Web からの取り込みに加え、クエリの保存と接続専用への切り替え、複製、別のクエリを参照するクエリ、ファイルの位置を変えても動作する設定を取り上げます。第 3 章「列の操作」と第 4 章「行の操作」では列の分割や結合、データ型の変更、空白行や重複データ、エラーデータの削除、複数条件のフィルターを、第 5 章「文字の操作」ではスペースやセル内改行の削除、数字の桁数をそろえる処理、区切り文字による抽出、セル結合を解除して 1 行 1 データに整形する方法を扱います。第 6 章「計算の基本」は偶数丸めと Excel の ROUND 関数と同等の計算の違い、日付と時刻の計算、カレンダーの作成、第 7 章「集計と条件分岐」はグループごとの合計や平均、条件列、列のピボット、if then else による条件分岐を扱います。第 8 章「表の操作」はピボットの解除、表をデータベース形式に整形する方法、外部結合や内部結合、反結合によるテーブルの結合と行方向の結合を取り上げ、入退室情報からタイムシートを作成する節で終わります。巻末資料は Power Query エディターのメニュー一覧、Power Pivot、Power View、Power Map の概要、M 関数の参照方法の 3 本です。

手順を記録して何度でも再実行できる仕組み

コピーと貼り付け、並べ替え、行の削除を手で繰り返す作業は、翌月に同じ形のファイルが届けば、また最初からやり直しになります。Power Query は、データの取り込み元と、そこに加えた変換を 1 つずつの手順として記録し、元のデータが差し替わっても更新の操作で同じ手順を再実行できます。データを取り出し、形を整え、使う場所へ読み込むという流れは、データ基盤の分野で 抽出、変換、ロード と呼ばれる処理と同じ考え方で、Power Query ではその小さな仕組みを Excel の中で組み立てます。VBA のマクロがセルを 1 つずつ動かす命令を書くのに対し、Power Query は表全体にどんな変換を加えるかを積み重ねていくため、手順の途中を見直したり、取り込み元だけを入れ替えたりしやすい点も違います。

もう 1 つの要点は、集計しやすい形にデータを整えることです。月ごとの値が横に並ぶ一覧表や、セルが結合された見出しを持つ表は、人が読むには分かりやすくても、グループごとの合計や条件による抽出には向きません。1 行に 1 つのデータが並び、列ごとに意味が決まった形に直しておけば、その後の集計や結合が素直に書けます。第 5 章のセル結合の解除や第 8 章のピボットの解除、データベース形式への整形の節は、この下ごしらえに当たります。メニューの操作で手順を組み立てられるのが Power Query の特長ですが、本書は第 1 章で M 言語と数式バーの使い方も取り上げています。

Excel の定型作業を減らしたい人に向く理由と前提

前提になるのは、表の入力や並べ替え、フィルターといった Excel の基本操作です。版元はプログラミングの知識ではなく、標準機能の使い方として本書を案内しています。版元の紹介文が挙げる業務システムのレポートファイルからの請求金額の集計や、不規則なリストの整形のように、具体的な業務の形を手がかりに読み進められます。一方で、Power Query をすでに使いこなし、M 言語で関数を書く段階にいる人にとっては、入門書としての位置づけが目的に合わない場合があります。また 2021 年 9 月刊で、画面やメニューの配置は刊行時点のものです。

毎月の締めや定例の報告のたびに、同じ形の CSV やレポートを手で整えている人に向けて、本書は取り込みから列と行の整形、集計、表の結合までを一度組み立て、更新の操作で繰り返す手順を解説しています。