今回は、総務省の資料「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の中から、NGとして例示されているサンプルデータを拝借し、PowerQueryでデータ整形します。
使う機能は以下です。
- 列のピボット
- 列のピボット解除
- 行列の入れ替え
- 値の置換
- フィル
- 抽出
- 書式 大文字・小文字
- 書式 プレフィックス・サフィックス
- 書式 トリミング
- 書式 クリーン
- 列の分割
- 列のマージ
PowerQueryでデータをキレイにしてみよう！
NGサンプル① 数値データに、⽂字列が含まれている
修正前は、数値データに、「円」、「▲(マイナス表記)」、「，（カンマ）」が文字列として入力されています。そのため、SUM関数を使って合計値を求めようとするとエラーとなってしまいます。
よし！PowerQueryの機能「抽出」と「値の置換」を使って、修正後のデータになるように整形してみよう！！
抽出
抽出とは、テキストの値(文字データ)から文字を抽出する機能です。文字の数と区切り記号を使う方法があります。
データを見ると、「円」を区切り記号として、「円」よりも前のテキストを抽出できそうです。
対象列を選択し、リボンの「変換」から、「抽出」→「区切り記号の前のテキスト」をクリックすると、
「区切り記号の前のテキスト」ダイアログが開くので、「区切り記号」に「円」と入力し、「OK]をクリック。
「円」よりも前のテキストが抽出されました。
(※この時点ではデータ型は数値ではなくテキストです）
値の置換「テキスト → テキスト」
次に、「前回差分」列の「▲」を「-」(マイナス)に置き換えます。
「前回差分」列を選択し、リボンの「ホーム」から「値の置換」をクリックします。
値の置換ダイアログが表示されるので、
「検索する値」→「▲」
「置換後」→「-」（半角のマイナス）
と入力し、「OK」をクリック。
「▲」が「-(マイナス)」に置き換わりました。
最後に、「単価」「前回差分」「生産台数」のデータ型を「整数」に変更します。
ヘッダー名の左端「ABC」をクリックすると変更できます。
これでデータ整形完了です。
整形後のデータを使えば集計ができます。
NGサンプル② 1セルに複数のデータが入力されている
修正前のデータは、1つのセルに2つのデータが含まれています。
これもPowerQueryを使って修正後のデータに整形してみよう。
ポイントは、元データに含まれる「(」です。「(」を起点として列の分割を行います。
列の分割
列の分割は、テキストの値を複数の列に分割する機能です。
- 区切り記号による分割
- 文字数による分割
- 範囲
と3つの分割方法があります。
今回は「（ 」を区切り記号として分割したいので、「区切り記号による分割」を使います。
対象の列を選択した状態で、リボンの「変換」→「列の分割」→「区切り記号による分割」をクリック。
「区切り記号による分割」ダイアログが開くので、
①「区切り記号を選択するか入力してください」→「カスタム」を選択し「（」を入力。
②一番左の区切り記号を選択
③OKをクリック
「（」を起点に列が分割されました。
この時点で、「（」よりも前の売上金額部分のデータ型が「テキスト」から「整数」に自動的に変更し、「変更された型」というステップが生成されています。
ハイ、ハーイ！(挙手)
ってことで自動生成された「変更された型」のステップは削除します。
ステップの削除は簡単だな～。
分割されたヘッダー名をそれぞれダブルクリックし、「売上金額」と「費用総額」に名前を変更します。
次に、「費用総額」列の「）」を取り除きます。やり方は、サンプル①で使用した「抽出」、「値の置換」、どちらを使用してもOKです。
今回は、値の置換を使ってみるか。
値の置換「テキスト → 空白」
「費用総額」列を選択し、リボンの「ホーム」から「値の置換」をクリックすると値の置換ダイアログが表示されるので、
①「検索する値」→「）」
②「置換後」→なにも入力しない
③「OK」をクリック。
「）」が取り除かれました。
最後に、「企業等数」「売上金額」「費用総額」のデータ型を数値に変換します。
まとめてデータ型を変更することで、ステップ数が1つで済みます。
NGサンプル③ セル結合している
セル結合を解除し、解除後のセルにもデータが入力されている状態にしたい場合、PowerQueryを使えば簡単に整形できます。使う機能は「フィル」です。
フィル
フィルとは、選択列の「null」を、隣接する上の値（または下の値）で埋める機能です。
「null」ってなんだ？「空白」とは違うの？
「null」と「空白」は違うよ。
データには、目に見えるものと見えないものがあります。
見えないデータには
- 空白
- スペース
- 改行
- null
の4種類があります。
引用画像：ノンプロ研PowerQuery講座資料より
見えないデータって色々あるんだな～。
フィルは、見えないデータの中の「null」に対して有効です。
セル結合を含むデータをテーブル化→クエリ取込すると、2行目以下は「null」になっています。
次に対象列を選択し、リボンの「変換」→「フィル」→「下」をクリック。
1行目の「東京都」が下の行にコピーされました。
NGサンプル④ スペースが含まれている
体裁を整えるためにスペースを含める。あるある！
PowerQueryを使えば、スペースも簡単に取り除くことができます。
サンプルデータは、データ先頭にスペースが含まれているので、「トリミング」機能を使うと便利です。
トリミング
トリミングは、選択されている列の各セルから先頭と末尾の空白文字を削除します。
対象列を選択し、リボンの「変換」→「書式」→「トリミング」をクリック。
不要なスペースが取り除かれました。
NGサンプル⑤ 改行が含まれている
改行が含まれるデータもよくあるよね～。
改行も、PowerQueryの「クリーン」機能を使えば、簡単に取り除くことができます。
クリーン
クリーンは、選択されている列に含まれる印刷不可能な文字を削除する機能です。
「改行」はまさに印刷不可能な文字。
対象列を選択し、リボンの「変換」→「書式」→「クリーン」をクリック。
改行が取り除かれました。
たしかヘッダー名の「出荷本数」にも改行がふくまれてたよね？
「クリーン」は列に対して行う処理だから、行と列を入れ替えて、クリーンを行えばいいのかな？
行列の入れ替え
行列の入れ替えを行うにあたり、注意すべき重要ポイントが！行列の入れ替えは、1列目と1行目が入れ替わります。つまり、ヘッダーに使用している行を1行目に下げる必要があります。
リボンの「変換」→「1行目をヘッダーとして使用」→「ヘッダーを1行目として使用」をクリック。
ヘッダーが1行目に下りました。
次に、リボンの「変換」→「入れ替え」をクリックします。
行列が入れ替わりました。
次に、1列目を選択→リボンの「変換」→「書式」→「クリーン」をクリック。
「出荷本数」から改行が削除されました。
再度行列を入れ替え、
リボン「変換」→「1行目をヘッダーとして使用」をクリック。
データ型を確認して完成です。
まとめと感想
以上、PowerQueryの便利機能を使ってデータ整形してみたをお送りしました。
データ整形の際、もっとも印象に残ったのが、「見えないデータ」には種類があることです。
見えないデータ、今まで意識したことなかったな～
目には見えないけど存在するのは非常に厄介ですが、PowerQueryを学習することで、見えないデータにも意識が向かうようになりました。
今回総務省資料「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の中から、NGとして例示されているデータを拝借しました。こちらの資料はExcelでデータ作成するときはこうしましょうというルールを非常にわかりやすく提示してくれているものです。
このルールを全Excelユーザーが守ってくれたら、日本の生産性は爆上げするでしょう。
たしかに、そもそもの元データがキレイに作られていたらいいよな～。
しかし！そうは問屋がおろさないのが現実でしょう。最近は、
Excel？そんなもん古いだろ。今はキントーンとかノーコードアプリがあんだぜ？
など、Excelに疎い人間ほど、Excelを馬鹿にします！
結局、会社側も教わる側も「わかりやすくて簡単に使えるシステム」を求めているんです。Excelスキルとは…とむなしくなることもあるでしょう。
しかし！
PowerQueryの学習を始めて３週間が経過した今、
きったないExcelデータであっても
けっこうPowerQueryでなんとかなるんやないか！？
と思えてきました(*´▽｀*) PowerQueryすごい！
次回はもう少し複雑なデータ整形に挑んでみます！
つづく…