ランニングデータをまとめてみる 7
週ごとのデータ推移
前回まで月ごとの走行距離を前年と比較できるようなグラフにするまとめ方を記事にしてきました。同じ季節感の中で走行距離に差が出ているかを比較し、日々のトレーニング計画に反映させることに役立てられると思います。
続いて、週ごとにデータをまとめて先週と比較したり、数週間の推移を比較できるようなグラフにまとめていきたいと思います。
これまで作成してきたデータテーブルです。
週集計をするときは、Week列のラベルを使います。
前回までの月データ集計と同様に、sumif関数を用いて集計をします。
すなわち、書式はSumif(範囲,検索条件,合計範囲)でしたので、
Sumif(テーブル１[Week],Weekラベル,テーブル１[Distance])
です。
| Week | Distance |
| 0 | 110.02 |
| 1 | 67.22 |
| 2 | 68.17 |
| 3 | 76.47 |
| 4 | 89.92 |
| 5 | 95.46 |
このような感じで表を作成します。
Excelで上記集計したテーブルをグラフにすると、Weekが固定されて、第5週目以降の走行距離が表示されません。5週間にわたっての推移を表示するようにして最新週を右端で表示できるようにしたいと思います。
まずは、5週間のグラフを作成します。
グラフ表示のために必要なデータを確認します。
現状はこのように選択範囲が固定されています。今週から4週間前までの5週間を選択範囲にするように変えていきます。
週番号はWeeknum関数で求めましたので、
今週は、Weeknum(Today(),21)
Today（）は、今日の日付を求める関数です。
上記集計表の5行分を日付に合わせてずらして選択するということになります。
このように基準のセルから一定値をずらす場合に使える関数は
offset関数(基準セル,行の移動数,列の移動数,行選択範囲,列選択範囲)
グラフの系列、軸ラベルとも範囲選択となりますが、offset関数で範囲を選択することが可能です。
行の移動数をWeeknum関数で求められるWeekラベルとしたいので、基準セルは上記週集計表のWeek列『0』のセル、行の移動数は、Weeknum(Today(),21)-4,列の移動数は0,行選択範囲は5、列選択範囲は1
例えば、今週が第25週目とした場合、行の移動数が25-4で第21週目の行となり、そこから行選択範囲が5行分なので、第25週目の行までが選択範囲となるということになります。
この関数をグラフの軸ラベルの範囲に入力したいのですが、関数を入力できません。
そこで、『名前』に関数で表す範囲を登録します。
メニューバーの数式の定義された名前というグループの中に名前の定義というものがありますので、軸ラベルに入力する値を定義します。
とりあえず名前は『項目』として
参照範囲にoffset(Week列の第0週目のセル,Weeknum(Today(),21),0,5,1)
を入力します。なお、2段目の範囲には、集計表が入力されているシートを選択してください。
走行距離についても同様に名前を定義しておきます。
そして、グラフの軸ラベルの範囲に、
というように、シート名!『名前』を入力します。
系列の範囲についても同様に定義した名前で範囲を設定すれば、グラフが週ごとに変化していきます。
