お久しぶりです、むらまさです。
久しぶりにこのブログに記事らしいものを投稿しようと思い立ち、いいネタないかなーと考えてぱっと思いついたものを描いていきたいと思います。
複数段組みを持つExcel表は「クソ表」
データ処理などをしていると、よく他人が作った管理表をベースにしてデータ処理する必要に迫られる場面があると思います。
たとえば、よくありがちなのは年度と月が２段になっていて、上段の年の部分が12個分連結して真ん中に表示されているような表を思い浮かべてください。
このような形式の表はいくつかの情報を見やすくまとめるための表なので、手書きならばよく使われるものですが、行列処理を行うコンピュータとの相性は悪いものとなります。
再利用を考えて作りこまれていないので、このような表はデータとは呼べず、愛情をこめて「クソ表」とひそかに呼んでいます。
私が生息しているIT界隈でもこんな状況ですから、おそらくはそうではない一般的な事業会社の方はさぞ苦労されていることかと思います。
そんな「クソ表」をPowerBIで処理する
そして、そんな乱造された「クソ表」を一つに収集・分析などを行う必要があるケースが結構あります。
更新なし・数枚くらいなら人力で頑張ってコピペする…が通用しますが、数十枚かつ毎日更新されるような場合、人力で対応するのは限度があります。
ここでは、そんな「クソ表」をサクッとまとめて処理するテクニックをご紹介します。
ヘッダを処理する
能書きはこの辺にして、さっそく本題に入ります。
サンプルとして、Excelで下のような表が作成されているとします。
はい、絵に描いたような「クソ表」ですね。でもこういうの結構あると思います。
サンプルの適当さはさておいて、よく見かける表だと思います。
データ処理する担当からすれば、「なぜ、”2020年1Q”ってヘッダに表示していないのだ！」と思う表です。
でも、ご安心ください！
PowerQueryがあればこういう表をあなたが扱いやすいデータに落とし込むことができます。
ファイルを読み込む
まずは、PowerQueryで上記ファイルを読み込ませてください。
PowerBIでは、下のようなボタンを押すとダイアログが出てファイルを選ぶことができます。
すると、こういう画面が表示されます。
ここでは素直にSheet1を選択します。
選択すると下のようにプレビュー表示されますので、「データの変換」でデータ加工を始めます。
無事に、PowerQueryに「クソ表」が取り込まれています。
が、このままでは処理できないので小細工を始めます。
ヘッダーを処理する
まずは、縦横変換を行います。
メニューは「変換」→「入れ替え」です。ぽちっとやるとこういう風になります。
行と列が入れ替わっています。
この状態で空白交じりな１列目を選択(３段、４段ある場合には、空白混ざっている列を一通り選択)して「変換」→「フィル」→「下」を選択します。すると、下方向に同じ値をセットしてくれます。
そうしたら、ヘッダを一つにまとめるために、ヘッダが記載されている列を結合させます。
ヘッダの列（この例では左の２列）を選択して「変換」→「列のマージ」をぽちっとやります。
こんな画面が表示されます。素直に結合したいので、区切り記号は「ｰｰなし–」のままとします。
「結合済み」という列にマージされました。
これを再び、縦横変換を行います。
メニューは「変換」→「入れ替え」ですね。
いい感じになりました。
このままだとタイトル部分もデータ扱いなので、列タイトルに昇格させます。
「ホーム」→「１行目をヘッダーとして使用」をぽちっとやるとこういう風になります。
年度と四半期を項目名とした表になりました。
やっと加工のスタートラインに立つことができました！
ピボット崩しを行って行データに変換する
やっと、タイトル回収が終わったところで、この表をデータ処理できる形に変換します。
これで、Excelコピペ生活とはおさらばです。
先ほどの表の「品目」行をコピーします。
そして、「変換」→「列のピボット解除」の右側の▼→「その他の列のピボット解除」を行います。
この表だけを処理するなら、ピボット解除をする列を全部選択して普通に「列のピボット解除」を押せば済むのですが、あなたの扱う表は今後拡張される場合があると思います。その場合、毎回このPowerQueryを編集しなければならないのですが、この「その他…」を選択することで、右方向に毎年表が拡張されても同じQueryで処理できるのです！
こんな風に、「属性」という列で年と四半期が格納されたデータに分解することができました。
列名を「年次」に修正して…
無事、データを取り込むことができました。
PowerBIでやっていますが、Excelならばテーブルとして取り込まれます。
以後は、更新（Excelでは「データ」→「更新」や右クリックでの「更新」）で元の「クソ表」が更新されても更新後のデータを取得することができます。
PowerBI(PowerQuery)を活用して楽々データ処理を！
人が読むのは容易だが、データとして処理するのが面倒な「クソ表」の処理の一例を紹介しました。
日々、人が作った「文章」を「データ」として処理できると、いろいろな無駄な作業を削減して生産性を向上させることができるようになります。
無料で使えるので、PowerBIはとてもおすすめなツールです。
コメント