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