PowerQuery 数値が全て0の行は削除、そして空白セルには0を埋める

Microsoft Excel

スポンサーリンク

はじめに:今回の課題

下の図1の表では、C列以降の列には数字を入れるけど、何年目まで列が続くかは分からず流動的とする。

そして、3行目・5行目は横方向の数字が全て0だけど、そういう無駄な行は全部削除して出力する処理を、PowerQueryで組みたいと思う。

図1 元データ

その場合、これまでにも当ブログで取り上げた手順だと、PowerQueryの「列のピボット解除」「列のピボット」機能を両方使って変換する手順(詳細は後述)が良いと思うけど、そうすると下図のように、元々0だったセルが空欄になってしまう。

今回は、図1のような数値カラムが流動的な表について、数値が全て0の行は削除して、なおかつ元々0だったセルが空欄にならず0を埋める手順を取り上げたい。

PowerQueryでExcelファイルを読み込む

ファイルの読み込み・変換

ではまず、基本の一歩として、PowerQueryで元データのExcelファイルを開く。

リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」

とし、元データのExcelファイルを開こう。

次の画面で「データの変換」ボタンを押す。

PowerQuery エディターの基礎

エディタの設定

PowerQueryのエディタ画面になるけど、ぜひ次の設定を済ませておこう。

 

ヘッダ設定のオプション

エディタのリボン「ファイル」タブ→「オプションと設定」→「クエリのオプション」と進む。

「データの読み込み」→「型の検出」メニューで、「非構造化ソースの列の型とヘッダーを検出しない」にチェックを入れておく。

 

Excel PowerQuery エディタのオススメ設定

ヘッダ設定

1行目(見出し)が「Column1」などとなっていると思うが、

リボン「ホーム」タブ
→「1行目をヘッダーとして使用

で、ヘッダを正しく設定する。

ピボット解除~リスト作成

ピボット解除

今回の例では、項目「資産№」「資産名」が固定で、それ以外の数値項目が可変なのだけど、固定項目「資産№」「資産名」の列を選んだ上で

リボン「変換」タブ
その他の列のピボット解除

とする。

Excel PowerQuery 縦横を入れ替えるワザ

すると、横軸にあった数値項目が「属性」という縦項目に変換される。

グループ化

この「属性」項目を選択した状態で、

リボン「ホーム」タブ
グループ化

とする。

Excel PowerQueryで、選択列の値が変わるごとに縞模様を付ける

次の画面で、名前はデフォルトの「カウント」のままで良いので、「操作」からは「すべての行」を選び「OK」を押す。

インデックス追加

今後、色々と処理をしていくと、この「属性」項目について並び順が狂うことがあり得る。

それを防ぐためにインデックス番号を付けるので、リボン「列の追加」→「インデックス列」としよう。

開始番号は0でも1でも良い。

Excel PowerQuery 行番号(インデックス)を付ける

インデックスが追加されたら、グループ化により色々なカラムを畳み込んだ項目「カウント」の右の□ボタンを押す。

項目「属性」は既に表の中にあるので、それのチェックは外し、それ以外の項目には全てチェックを入れて「OK」を押す。

グループ化で隠れていた項目が展開される。

ステップの名称が「展開されたカウント」などとなっていると思うが、後で再利用する箇所なので、ステップ名をクリックしてF2キーを押し「展開」と名称変更しておく。

リストに変換

「属性」列を選択した状態で、リボン「変換」タブ→「リストに変換」とする。

すると、項目「属性」が、「リスト」というものに変形される。私も「リスト」の扱いは全然わかってないですけどね。

この「リスト」に変形すると、その後の操作はかなり制限される。

ひとまず「重複の削除」をしておこう。

すると、ステップの名前が「削除された重複」となってるはず。

これも後で再利用する箇所で、もっと分かりやすくしときたいので、ステップ名をクリックしてF2キーを押し、「属性リスト」と名前を変更することにする。

リストへの変換後

前の処理に時計を戻す

名前を変更した「属性リスト」を右クリックして「後にステップの挿入」としよう。

リストってそもそも、こうでもしないと後に処理を続けられない奇妙なもので。

Excel PowerQuery ステップの挿入・切り離し

「カスタム1」とかいうステップが追加されたと思うが、このステップ名を「展開時に戻る」とでも変更しよう。

で、ちょっと前に「展開」というステップを作ったけど、これを数式欄に入れる。

これで、リストを作った事実はそのままに、ステップ「展開」の時点に時計を戻せたようなことになるというか。

不要データの削除

列「インデックス」は、列「属性」の並び順を制御するためのものだが、もう不要になったので削除しておく。

では、元々の目的として、数値が全て0という行は削除したいのだった。

「値」列についてオートフィルタで、0を外すフィルタリングをして「OK」を押そう。

列のピボット

大元のデータに合わせて、数値項目を横軸に置いた形式に戻すことにする。

数値項目「属性」の列を選択した状態で、リボン「変換」タブ→「列のピボット」を押そう。

先程は横軸を縦軸にするため「列のピボット解除」をしたのだけど、それと逆の操作だ。

次の画面で、値列というやつについてそのまま「値」を選んで「OK」を押す。

すると、「○年目」というのが横軸にきて、全て0の行が削除されてくれはするのだが、元々0だったところにnullというのが入ってしまっている。

縦軸だったものを強引に横軸にした結果、こういうことになったわけだ。

これを、nullではなく0を埋めたいというのが今回のそもそもの課題だ。

リストを活用して置換する

では、問題となっているnull値が入っている列ならどこでも良いので、その列を選択して

リボン「ホーム」タブ
→値の置換

としよう。

PowerQueryの置換機能

次の画面で、「検索する値」には「null」と手入力し、「置換後」には0を入力して「OK」を押す。

数式欄を見ると

= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue,  {“5年目”}   )

というようになっているはずで、 {” “}   で囲まれた項目を対象に置換したということだ。

ではこの項目を、先ほど数値項目についてリストを作ったとき、ステップ名称とした「属性リスト」に書き換えよう。

変更後の式は

= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue,属性リスト)

となり、全ての数値項目についてnull値は0に置換されたはずだ。

つまり、リスト「属性リスト」に含まれている数値項目の全てが置換対象になったということだ。

「閉じて読み込む」ボタンを押して、Excelシートに結果出力しよう。

では、大元のデータの方に、元々なかった「6年目」の列を追加してみる。

ついでに、3行目には0でないデータも追加し保存する。

PowerQueryで出力した表の方を、Ctrl + Alt + F5 キーで更新したら、6年目も追加され、0も埋められた状態で更新されるはずだ。

今回厄介だったのは、数値項目がどこまで続くのか流動的で可変だということで、PowerQueryではそういう場合は大体、コードを自分で書き足さないといけない。

もう少し分かりやすくなってほしいものだが。

Excel PowerQuery 列のタイトルが可変の表をあれこれ加工してみる

スポンサーリンク