Googleスプレッドシートマニアのポールです。ビジネスの現場でよくある表計算ソフト（Googleスプレッドシート）の関数の使い所をご紹介します。

予算管理や実績集計で日次データを月次データに効率的にまとめる

月次予算を日次予算に展開したり、日次の実績を月次に集計してレポートを作成したりすることが頻繁に発生します。下記のような月をまたいだデータをイメージしてください。

今回使うサンプルシートを公開しております。

SUMIFSサンプル – Google スプレッドシート

このようなときに、月次でまとめる際にどのような処理をしていますか？

  1. 手計算
  2. ピポッドテーブル
  3. SUMIFS関数

主に上記のような方法があります。この中で私のおすすめは３の SUMIFS関数です。それぞれを簡単に解説すると、

手計算

手計算とは SUM関数の範囲を手動で指定する方法です。下記のようなイメージです。

これでも一見問題ないように見えます。2020年5月だけのデータしかないのであればこれでもよいですが、複数の月にまたがったデータがあるのであればこれは「わざわざ範囲を人間が目で見て選択している」というデメリットがあります。1回限りの分析というのは意外に少ないものです。あとで、データが間違っていたから元データを変更したり、追加したりすることによる追加工数の発生指定した範囲が1行ずれていたりすることでのミスの発生のリスクを内在することになるためおすすめできません。

ピポッドテーブル

これはかなり便利です。手計算のようなミスも防げますし、データの更新にも対応できます。

デメリットとしては、下記の4行目にみえるように余計な空白が入ったりすることと、ピポッドテーブルのデータに外部参照の数字を絡めた計算などが非常にしずらいということがあります。これはピポッドテーブルが基本的にはセルの横幅と縦幅が可変で柔軟性があるためなのですが、ピポッドテーブルで完結しないような計算をしようとすると一度ピポッドテーブルの中身を変えると横幅や縦幅がかわりエラーが起きてしまいます。

 

こんな感じですね。

SUMIFS関数

SUMIFS関数は関数指定こそ少しこつがいりますが、上記のデメリットがすべてありません。データが増えても対応できます。

実際の関数部分はこうなっています。

=SUMIFS(B:B,$A:$A,”>=”&$E2,$A:$A,”<=”&EOMONTH($E2,0))

コピペで6月やrevenueのG列にも展開できます。コツとしては、EOMONTH関数を使っているところで、B列を合計する条件指定として

  • A列がE2の5月1日以上
  • A列がEOMONTH($E2,0)の結果である5月末日以下

と指定しています。

もし、これが複雑だという場合はD列にA列の日付に対応する月初の日を用意してそれを条件にF列の月（月初の日を年月表示したもの）と＝になるかで判定しても大丈夫です。

 

以上、いろいろな方法はあるのですが表計算の使い方がどれだけ効率的にできるかで仕事のスピードが変わってきますので是非トライしてみてください！

The following two tabs change content below.

ポール

株式会社 援軍取締役。広告からSEOなどあらゆるデジタルマーケティングに精通するデータアナリスト。徹底的に現場に根付いた、スピーディーでシンプルなデータ分析環境の構築と施策推進を得意とする。 ＜経歴＞ 株式会社マイネットでモバイルCRMサービス（後にYahoo! JAPANに売却）のマーケティング部長、モバイルアプリ事業を立ち上げ。Googleで広告代理店営業マネージャーとして国内50社以上の広告代理店のコンサルティング。通信テクノロジー業界シニアアカウントマネージャーとして大手企業向けのマーケティング支援に関わる。 twitter : https://twitter.com/paulmakoto