スポンサーリンク
はじめに:今回の課題
下の図1の表では、C列以降の列には数字を入れるけど、何年目まで列が続くかは分からず流動的とする。
そして、3行目・5行目は横方向の数字が全て0だけど、そういう無駄な行は全部削除して出力する処理を、PowerQueryで組みたいと思う。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-7.png)
その場合、これまでにも当ブログで取り上げた手順だと、PowerQueryの「列のピボット解除」「列のピボット」機能を両方使って変換する手順(詳細は後述)が良いと思うけど、そうすると下図のように、元々0だったセルが空欄になってしまう。
今回は、図1のような数値カラムが流動的な表について、数値が全て0の行は削除して、なおかつ元々0だったセルが空欄にならず0を埋める手順を取り上げたい。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-8.png)
PowerQueryでExcelファイルを読み込む
ファイルの読み込み・変換
ではまず、基本の一歩として、PowerQueryで元データのExcelファイルを開く。
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」
とし、元データのExcelファイルを開こう。
![](https://excel-excellent-technics.com/wp-content/uploads/2020/07/2020070501.png)
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-9.png)
次の画面で「データの変換」ボタンを押す。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-10.png)
エディタの設定
PowerQueryのエディタ画面になるけど、ぜひ次の設定を済ませておこう。
エディタのリボン「ファイル」タブ→「オプションと設定」→「クエリのオプション」と進む。
「データの読み込み」→「型の検出」メニューで、「非構造化ソースの列の型とヘッダーを検出しない」にチェックを入れておく。
![](https://excel-excellent-technics.com/wp-content/uploads/2021/08/001.png)
![](https://excel-excellent-technics.com/wp-content/uploads/2021/08/004.png)
ヘッダ設定
1行目(見出し)が「Column1」などとなっていると思うが、
リボン「ホーム」タブ
→「1行目をヘッダーとして使用」
で、ヘッダを正しく設定する。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-11.png)
ピボット解除~リスト作成
ピボット解除
今回の例では、項目「資産№」「資産名」が固定で、それ以外の数値項目が可変なのだけど、固定項目「資産№」「資産名」の列を選んだ上で
リボン「変換」タブ
→その他の列のピボット解除
とする。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-12.png)
すると、横軸にあった数値項目が「属性」という縦項目に変換される。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-15.png)
グループ化
この「属性」項目を選択した状態で、
リボン「ホーム」タブ
→グループ化
とする。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-16.png)
次の画面で、名前はデフォルトの「カウント」のままで良いので、「操作」からは「すべての行」を選び「OK」を押す。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-17.png)
インデックス追加
今後、色々と処理をしていくと、この「属性」項目について並び順が狂うことがあり得る。
それを防ぐためにインデックス番号を付けるので、リボン「列の追加」→「インデックス列」としよう。
開始番号は0でも1でも良い。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-36.png)
インデックスが追加されたら、グループ化により色々なカラムを畳み込んだ項目「カウント」の右の□ボタンを押す。
項目「属性」は既に表の中にあるので、それのチェックは外し、それ以外の項目には全てチェックを入れて「OK」を押す。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-43.png)
グループ化で隠れていた項目が展開される。
ステップの名称が「展開されたカウント」などとなっていると思うが、後で再利用する箇所なので、ステップ名をクリックしてF2キーを押し「展開」と名称変更しておく。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-45-1024x304.png)
リストに変換
「属性」列を選択した状態で、リボン「変換」タブ→「リストに変換」とする。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-44.png)
すると、項目「属性」が、「リスト」というものに変形される。私も「リスト」の扱いは全然わかってないですけどね。
この「リスト」に変形すると、その後の操作はかなり制限される。
ひとまず「重複の削除」をしておこう。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-47.png)
すると、ステップの名前が「削除された重複」となってるはず。
これも後で再利用する箇所で、もっと分かりやすくしときたいので、ステップ名をクリックしてF2キーを押し、「属性リスト」と名前を変更することにする。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-48.png)
リストへの変換後
前の処理に時計を戻す
名前を変更した「属性リスト」を右クリックして「後にステップの挿入」としよう。
リストってそもそも、こうでもしないと後に処理を続けられない奇妙なもので。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-50.png)
「カスタム1」とかいうステップが追加されたと思うが、このステップ名を「展開時に戻る」とでも変更しよう。
で、ちょっと前に「展開」というステップを作ったけど、これを数式欄に入れる。
これで、リストを作った事実はそのままに、ステップ「展開」の時点に時計を戻せたようなことになるというか。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-51.png)
不要データの削除
列「インデックス」は、列「属性」の並び順を制御するためのものだが、もう不要になったので削除しておく。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-52.png)
では、元々の目的として、数値が全て0という行は削除したいのだった。
「値」列についてオートフィルタで、0を外すフィルタリングをして「OK」を押そう。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-22.png)
列のピボット
大元のデータに合わせて、数値項目を横軸に置いた形式に戻すことにする。
数値項目「属性」の列を選択した状態で、リボン「変換」タブ→「列のピボット」を押そう。
先程は横軸を縦軸にするため「列のピボット解除」をしたのだけど、それと逆の操作だ。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-53.png)
次の画面で、値列というやつについてそのまま「値」を選んで「OK」を押す。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-24.png)
すると、「○年目」というのが横軸にきて、全て0の行が削除されてくれはするのだが、元々0だったところにnullというのが入ってしまっている。
縦軸だったものを強引に横軸にした結果、こういうことになったわけだ。
これを、nullではなく0を埋めたいというのが今回のそもそもの課題だ。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-26-1024x91.png)
リストを活用して置換する
では、問題となっているnull値が入っている列ならどこでも良いので、その列を選択して
リボン「ホーム」タブ
→値の置換
としよう。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-27.png)
次の画面で、「検索する値」には「null」と手入力し、「置換後」には0を入力して「OK」を押す。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-28.png)
数式欄を見ると
= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue, {“5年目”} )
というようになっているはずで、 {” “} で囲まれた項目を対象に置換したということだ。
ではこの項目を、先ほど数値項目についてリストを作ったとき、ステップ名称とした「属性リスト」に書き換えよう。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-29-1024x348.png)
変更後の式は
= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue,属性リスト)
となり、全ての数値項目についてnull値は0に置換されたはずだ。
つまり、リスト「属性リスト」に含まれている数値項目の全てが置換対象になったということだ。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-31.png)
「閉じて読み込む」ボタンを押して、Excelシートに結果出力しよう。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-34.png)
では、大元のデータの方に、元々なかった「6年目」の列を追加してみる。
ついでに、3行目には0でないデータも追加し保存する。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-32.png)
PowerQueryで出力した表の方を、Ctrl + Alt + F5 キーで更新したら、6年目も追加され、0も埋められた状態で更新されるはずだ。
![](https://excel-excellent-technics.com/wp-content/uploads/2022/03/image-35.png)
今回厄介だったのは、数値項目がどこまで続くのか流動的で可変だということで、PowerQueryではそういう場合は大体、コードを自分で書き足さないといけない。
もう少し分かりやすくなってほしいものだが。
スポンサーリンク