エクセル集計表で対前年実績と当期実績も集計するには?

エクセル集計表,対前年実績,当期実績

エクセルではいろんな集計表を作成します。

集計表ごとに計算式を考えて作らなければいけません。

 

どんな計算式にすればいいかわかる時はいいですが
わからなければネットで検索することになります。

 

ピンポイントで見つかればいいけど
なかなかそう うまくはいきませんよね。

 

ネット検索ってあっという間に時間が経ちますよね(・・;)

 

今日はメルマガ読者で受講者の方からいただいた質問への回答です。
エクセルの集計表で当月実績だけでなく期首からの累計も集計したいです。

 

その際はSUMIFS関数でいいですか?

具体的な計算式を教えてください。

 

 

わたしがよくやるのは四半期ごと、半期ごと、年間で列を設けます。

それを1期分用意します。

 

四半期:SUM関数で3か月分を合計
半期:SUM関数で前半の四半期と後半の四半期を合計
当期:SUM関数で上期と下期を合計
こんな感じです。

来月以降は空欄ですから
このような集計表ならシンプルな計算式でできますよね。

 

ただ集計行が増えるので、少し表がごちゃごちゃします。
たとえば上記のエクセル集計表のように
前月実績、当月実績、当期実績だけ必要という場合。
次のような考え方で作成するとよいでしょう。

当期実績もSUMIFS関数を使って集計

 

エクセルで集計表を作る時
計算式はできるだけシンプルな数式にすることをオススメします。

 

複雑な長い数式や
よく知られていないExcel関数を使った数式は
メンテナンスがやっかいです。

 

エクセルの達人しか修正できないという
属人化を招き、その資料はブラックボックス化してしまいます。

 

ということで

今回の質問では明細表をもとに
条件に合致した集計ができるSUMIFS関数を使います。

 

SUMIFS関数は複数条件を指定できる便利な関数です。

 

今回の場合、期間と店舗名が合致したデータを集計するという計算式になります。

 

エクセル 日付 年月 計算 関数

集計用に売上年月を計算しておくと便利!

明細データには2017年3月までのデータが入っています。

一般的に基準となる日付は上記のように
「年/月/日 時:分」や「年/月/日」となっています。

 

そのままでは集計の条件としてつかいにくいので
売上年月を別項目で追加しておきます。

売上年月:=YEAR(A2)*100+MONTH(A2)

 

これは数値データにしたい場合です。

もし文字データのほうがよければ
売上年月:=TEXT(A2,”yyyymm”)

こんな計算式になります。

 

あとはSUMIFS関数で集計します。

エクセル 売上年月 複数条件 集計 SUMIFS 関数

売上年月と店舗名を条件にして実績を集計

 

=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,$C3,明細リスト!$B$2:$B$201,D$2)

 

上記計算式の内容は以下の通りです。

 

合計対象範囲:明細リストの売上欄

条件範囲1:明細リストの売上年月

条件1:集計表の売上年月

条件範囲2:明細リストの店舗名

条件2:集計表の店舗名

 

今年実績欄も同じ要領で集計出来ますよね。

当期実績はSUMIFS関数の条件を変える!

 

当期実績の計算式は以下の通りです。

 

=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,”>=201604″,明細リスト!$B$2:$B$201,D$2)

 

上の計算式と条件1だけが違います。

 

条件1:201604以上

できれば計算式には固定の数値を使いたくないので
この条件も関数を使って求めれるようにしておいたほうがいいですね。

 

SUMIFS関数はとっても便利な関数です。
複雑な条件でも、このように少し手を加えればシンプルに計算できます。

 

参考になさってください!

 

Excel業務改善コンサルタント 小野 眸(おの ひとみ)。
20年間のシステムエンジニア経験から得た「最適な機能・関数を選択するコツ」と、コンサルタント経験から得た価値あるデータの見極め方を、15年以上の講師経験をもとにわかりやすく解説しています。

「Excel応用力 X データ活用力」の両方を兼ね備えた『ビジネスExcel力』が身に付き、ワンランク上の仕事ができる人材育成に貢献しています。

ブログではExcel業務の改善、Excelスキルアップに関する情報を発信しています。

ご相談やご質問がありましたら、Excel業務改善コンサルタントの小野 眸までご連絡ください。
E-mail:contact★pcgaku.com ★を@に打ち替えてお送りくださいね。

Twitterフォローをお願いします~
facebookのフォローをお願いします~

◆◇Excel実務に特化したExcel解説やスキルアップ情報をメルマガで配信中◇◆
ご購読は無料です。下記からお申し込みください。

ビジネスExcel力を高めExcel業務の効率を30%UP!

       
この記事が役に立ったと思ったらシェアしてね