・3 min read
Spreadsheet で、ずっと使える実績シート
今回は、実績表を Google Spreadsheet で実現するときの、細かなテクニックを紹介します。実績は手間がかからないような管理が望ましいです。そのため Google Spreadsheet の特性にマッチする使い方、レイアウトがあります。それがこれです。
色別に説明します。
- [赤]集計関係はすべて上に行固定し、データ部分は9行目以下すべてを使うので「A9:A」の表記が使える。
- [橙]今月1日のセル位置をもとめる。当月集計の合計、平均、グラフに使用する。
- [青]当月のグラフ、合計、平均を表示。
- [緑]先月1日のセル位置をもとめる。先月集計の合計、平均、グラフに使用する。
- [紫]全体のグラフを表示する。
今月初日のセル位置のもとめ方
データ行の最下段を拾います。
=rows(B:B)-day(offset(B1,rows(B:B)-1,0,1,1))- 最初の
rows(B:B)で、現在何行まであるのか調査します。 offset(B1,rows(B:B)-1,0,1,1)は、最下段のセルをもとめています。B1セルから最大行数だけ下にいったもの(-1は増分の調整、B1に縦セル分そのまま足したらオーバーフローします)。-day(最下段セル)で、最下段にある日付の日部分だけを取得し、最大行数から引き算します。5/19が最大だとすると、27セル目にあるので、そこから19を引き算、移動分なので+1調整して5/1のセル位置をもとめます。 #なお、キャプチャー画面では式の中で-2となっている部分がありますが、最下段が空欄である、特別な運用をしているためです。通常は-1で使えます。
当月の集計方法
月初のセル位置がもとまれば、当月の集計が可能です。以下の関数を使い分けます。
- SPARKLINE(範囲) = グラフを表示
- SUM(範囲) = 合計を表示
- AVERAGE(範囲) = 平均を表示 たとえばクリック率みたいな割合項目に合計は必要無いので、表示しないようにしておきます。
そして範囲
OFFSET(C$1, $B$5 - 1, 0, ROWS($B:$B) - $B$5 + 1, 1)ですが、以下のようにしてもとめます。- 最初の引数はセル縦位置です。
$B$5 - 1月初日のセル位置をもとめます。 ROWS($B:$B) - $B$5 + 1, 1で、全行数から月初日のセル位置を引き算して、行数を取得します。- 列方向には何もしないので、それぞれ0と1を指定します。 これらの式はコピーしてもセルがずれないよう、$表記にしてあります。横方向に対しては何も考えずにコピ貼りできれいに集計できます。 グラフで使った式をそのまま縦方向にコピ貼りした後、SPARKLINEの関数をSUMなどに置き換えれば完成です。
先月初日のセル位置のもとめ方
B5 - DAY(OFFSET(B1, B5 - 2, 0, 1, 1))B5は当月の月初日のセル位置です。そこから先月末の日(30 or 31 or ??)をもとめて引き算をします。
先月の集計方法
SPARKLINE(OFFSET(C$1, $B$2 - 1, 0, $B$5 - $B$2 + 1, 1))で実現できます。一番上のセルを基準にして、先月初日から当月初日の前まで引っ張る感じです。セル指定のドル表記は有名ですが、これはセル式のコピー貼り付けをしたときに、指定のセル位置が動かないようにするための指定です。C2に入力したセルは、そのまま右方向にコピー貼り付けして使えます。全体のグラフ
こちらは簡単ですね。C9:Cでデータ全体を選択できます。
