[EXCEL]当年と前年の同一期間内の数値の合計を出す（SUMIFS関数で特定条件のみ足し上げる）

▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索

こんな記事を見かけたので、頭の体操がてら考えてみました。
昼食後、昼寝しながら頭の中で、ですが、このくらいなら、私にも出来そうだったので。

家に帰ってきたので、昼休みの発想が正しいか、確認がてら記事にしています。

お題は２つあって、「集計」と「表示」というエクセルで悩む人の２大要素です。
それぞれ、「今の私だったらこうする」というものを示します。
あくまで「今の私」に考えられるレベル。
「これが正解」ではなく、「こんなやり方もある」ということで。
生成AIに聞いた方が早いんでしょうけど、こうやって考えるのは、結構好きです。

お題１　前年同時期の売り上げ計を出して比較したい

こんな表があるとして・・・

当年と前年の同一期間何内の数値を比較したい、とのこと。
記事では「ピポットテーブルで」とありましたが、この程度ならピポットを使わなくてもいいかな、と。
ピポットは「当たり」を付けるにはいいけれど、出したいものが決まっていれば、「表」にした方がレポート作成などの際に楽＆きれいでしょう。

集計表はこんな感じ。

当年の日付を入れたら、前年の同じ日付も自動で出るように計算式を入れます（入れ間違い防止）。
計算式
=DATE(YEAR(H2)-1,MONTH(H2),DAY(H2))
当年の年から１を引いたものと、当年の月・日から前年の同日の日付を作ります。これは他にもやり方がありそうですが、とりあえずこんな感じで。

後はこんな感じ。

参照元のトレースを表示すると・・・

ごちゃごちゃしちゃいましたね。

J3セルの計算式は以下のとおり。
=SUMIFS(D:D,$A:$A,">="&$H3,$A:$A,"<="&$I3)
「D列の数値を足し上げる」
ただし、足し上げるのは、
「Aの年月日がH３（始期）から（H3以上）」かつ
「Aの年月日がI3（終期）まで（I3以下）」の間。

SUMIFSは複数の条件を追加していけます。
=SUMIFIS（足し上げる列、列1,セル１,列２,セル２）と記述していきます。
「列1,セル１」とは「列１の中にセル１と同じものを足し上げる」ということです。条件が２つあれば、当然、条件１かつ条件２となります。
なお、範囲指定は列でするのが簡単で間違えません（後述）。

セル１やセル２のようにセルを参照する場合は、条件（＜＝）を「”」でくくって「＆」を付けるのがちょっと面倒で忘れやすいところ（ここでいつも悩む）。

なお、
「D:D」は、「金額」欄にコピーした際に、足し上げる欄がずれるように相対参照（＄なし）にしてあります。

一方、「$A:$A」というようにA列が絶対参照（＄付き）になっているのは、「金額」セルにコピーしても「年月日」欄がずれないようにするため。
「$H3」と「$I3」は、「金額」欄にコピーする際に列を動かしたくないけれど、下の「前年」にコピーする際には、「始期」と「終期」を動かしたいので、列は絶対参照（＆付き）、行は相対参照（＄なし）としています。

以上です。
これで、当年と前年の同一期間の比較ができます。
同一月で比較するなら、月を抜き出してという方法もありますが、上の方法でもできます。
上の方法だと、日単位で期間が自由に設定できるので、応用が利きます。

特定の商品について調べたいなら

参照元のトレースを出すと・・・

売上の計算式は
=SUMIFS(E:E,$A:$A,">="&$H9,$A:$A,"<="&$I9,B:B,$G$7)

上の「数量」の計算式に、「品目」が「みかん」という条件を追加するだけです。

具体的には、最後に　B:B,$G$7　を追加するだけ。
B:Bは「品目」、「$G$7」は表左上の「みかん」。
「$G$7」が絶対参照（＄付き）にしているのは、上のセルからコピーしてもずれないようにするため。
（「みかん」の位置が、ここでいいかはともかく）

なお、上の「全体」に入れた「始期」と「終期」の日付が「みかん」の表にも自動で入るようしておけば（それが普通です。今回は忘れました。すみません）、手間が省けますし、期間の誤りもなくなります。

というわけで、前年同期間の比較はこれでできるでしょう。

私の業務だと「売上」を計算することはないけれど、「一定期間の申請数」や「利用者数」などを出す場合は結構あるので、このパターンは以前から使っています。「＞」を「””」でくくり、「＆」でつけるのがキモですが、ちょっと使わないでいると、忘れてしまいます。

なお、何度も書いていますが、
条件が一つしか設定できないSUMIF関数は使ってはいけません。
それは、条件が一つしか設定できないから、だけではなく、
SUMIFS関数と記述方法が違うから。

日常業務では、条件が一つだけ、なんてことは稀。
集計表を出したら即座に「〇〇の条件も追加しておいて」なんてことは日常です（そういう「深掘り」で色々見えてくる）。
そんな時に、いちいちSUMIF関数をSUMIFS関数に直してやり直すのは面倒。
というより、SUMIFとSUMIFS関数の記述が違うから、混乱必須。
間違い発生のリスク大です。
SUMIFS関数は条件が一つでも使えるので、SUMIF関数は不要です。
不要なものを覚える必要はありません。無駄です。
詳細はこちらをどうぞ。

[EXCEL]使ってはいけない関数３選　①VLOOKUP、②SUMIF、③COUNTIF　初心者向けエクセル情報を鵜呑みにするなかれ

条件の追加はピボットテーブルの方が楽かもしれません。
ただし、個人的にはピボットテーブルは美しくないので（＝わかりづらいので）、そのまま人に見せるのは憚られます。あくまで下処理用ととらえています。だったら、SUMIFS関数を使ってきれいな（＝見やすい）表にした方がいいと思います。

ちなみに、SUMIFS関数などは、行番号で範囲指定せず、列で指定します。
楽だし、間違いも減りますから。
詳細はこちら。
[EXCEL]　セルを行番号で範囲指定してはいけない（SUMIFS/COUNTIF/XLOOKUP関数等）

ただし、表タイトルなどでセル結合がされていると列指定がうまくいかないので注意が必要。そもそも、セル結合はご法度です。
詳細はこちら。
[ダメEXCEL]セル結合してはいけないもう一つの理由（SUMIFS/COUNTIFS/XLOOKUP関数等における列指定の際のトラップ）

以上、参考になれば幸いです。内容に誤りあったらご指摘ください。

ご覧になって、何か発見があれば、スキやフォローしていただけると嬉しいです。

（作業１日　１.５H）