この記事のもくじ
テーブルやPowerQueryで累積・累計行を作るのって難しい
テーブルやPowerQueryにて累積・累計の行を作ろうとしたことはないでしょうか？
たとえば、下記のように単月の売上と累計の売上をグラフにしたいときなどです。
各月の累計を算出する場合、計算式としては下記になります。
- 最初の行は単月とイコール
- 以降の行は一個上の行の数字を足して、自分の行の数字を足す
が、テーブルだと自動的に全てのセルを同じ式にしてくれちゃうので、上の2.の式を全部に入れると、最初の行で上の行の文字列を取ってきてエラーになります。
PowerQueryも簡単にできる機能はないです。ググッてもかなり難しい方法でやっていて理解不能でした。
ということで簡単にやる方法を、私が一生懸命、一生懸命探しました。そしてね、簡単っぽい方法見つけましたよ(嗚呼！バラ色の珍生風)。
今回は結構力技です。
まずデータの用意
データとして、自販連の2018年新車販売台数をコピペしてきて、テーブルにします。
そのテーブルをクエリに読み込みます。
いらない列を消して必要なデータを残す
日産・エクストレイルの2018年累計を出すこととして、いらない列を消して、エクストレイルの販売台数データだけをフィルタリングします。
ここで、累計も出せれば……と思いますが、データの変換や列の追加を見ても簡単にできるボタンはなし。
困りました。
列・行のデータを入れ替えたりソートしたりが目的のクエリだと、累計するというのは毛色が違って相性が悪いです。
クエリをいったんテーブルに落として力技で累計行を作る
というわけで、作ったクエリー結果をいったんテーブルに落とします。
その後、下記のように累計(下記画像では累積)行を作ります。下記画像のように
=IFERROR([@台数]+D1,[@台数])
と入力すればOKです。これで累計・累積の計算式がテーブルとして下まで展開されます。
ここで、D1セルを普通にクリックするとD1ではなく
テーブル1[[#見出し],[累積]]
となってしまって#VALUE!となりますので注意です。[@台数]を入れるのがコツ。
ちなみにIFERROR関数とは
=IFERROR([@台数]+D1,[@台数])
これは[@台数]+D1が成立しない場合(#VALUE!)、代わりに[@台数]を表示するという関数です。
なので、最初の行だけはD1が数字ではなく項目文字列になって成立しないので、台数だけを出すようになります。
IFERRORはExcel2007くらいから追加された関数で、以前はエラー処理がISERRORという関数とIFを組み合わせるもので面倒でした。
あとはピボットグラフにするなり、クエリとして読み込むなり
ここまで来たら、後は挿入→ピボットグラフで単月販売台数と累計販売台数のグラフを作ることも出来ますし、このテーブルをPowerQueryで再び読み込むと累計・累積の入ったクエリとして編集できます。
順番を入れるのを忘れていたので、クエリでインデックスを追加した後、テーブルとして吐き出しました。
本当は1から始まるインデックスを追加し、サフィックスを入れて1月、2月……にしないといけなかったのですが、まあ、すんません。
これをピボットグラフにします。
グラフにしたものはこちら
グラフにしたら単月と累積で数字差が大きく、2軸グラフにしました。
しかしこれで、日産エクストレイルの2018年単月の販売台数と月ごとの累計が見えるようになりました。
エクストレイルは2018年3月ドーンと売れてあとは厳しいなぁ。2019年5月で6年目だし、そろそろフルモデルチェンジしないと……閑話休題。
こんな感じで1回テーブルを介してExcelのテーブルやPowerQueryで累積・累計行を作る方法でした。
PowerQueryの条件式でExcelの関数式が使えれば苦労しないのですが、現状厳しいのでこういう方法となります。
欠点
最初のクエリの行が増えた場合、再度テーブルに落とした時の累計列が自動で追加されない場合があります。
ちなみに、ピボットテーブルには累計列を作る機能があります。
値に指定している要素の、値フィールドの設定を選択します。
計算の種類タブで計算の種類を累計にし、累計の基準とするフィールドを別途選びます。
何を累計の基準にするのかというと、基本的に日付や時間などになると思います。
これで完了です。設定したピボットテーブルの数字要素が累計になります。
他のExcel関係の話はこちら
コメント