タグ: 関数

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

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

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

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

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

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

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

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

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

それを1期分用意します。 四半期：SUM関数で3か月分を合計 半期：SUM関数で前半の四半期と後半の四半期を合計 当期：SUM関数で上期と下期を合計   こんな感じです。

来月以降は空欄ですから
このような集計表ならシンプルな計算式でできますよね。 ただ集計行が増えるので、少し表がごちゃごちゃします。 たとえば上記のエクセル集計表のように
前月実績、当月実績、当期実績だけ必要という場合。 次のような考え方で作成するとよいでしょう。

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

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

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

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

ということで

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

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

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

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

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

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

 

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

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

 

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

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

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

 

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

 

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

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

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

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

条件範囲１：明細リストの売上年月

条件１：集計表の売上年月

条件範囲２：明細リストの店舗名

条件２：集計表の店舗名

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

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

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

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

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

条件１：201604以上

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

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

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