エクセル 関数を使った横方向の期間集計、範囲指定した横方向の集計

エクセルの関数を使い横方向の期間集計を行いたいです。
エクセル２０１３使用

月ごとに商品の売れた個数を確認出来るよう集計用シートを作成しました。
現在のシートで開始日と終了日を指定して、特定の期間にどのくらい売れたかを確認出来るようにするのが目的です。

ブックは1つで構成。
2017年8月～2019年5月までの各タブ＋集計タブ

2017年8月～各月ごとにタブで納品書をまとめてあります。
1つの納品書は全33行で構成されており、
商品は15品目まで入れられるようになっていて、横に続く形で番号、ブランド名、商品名、商品コード、サイズ、数量、原単価、原価金額、売単価、売値金額、掛率となっています。

過去今までの売れ行き動向が分かるように、
「集計」タブを作り納品書に記載のある数量をsumifs関数で計算し、
仕入合計、売掛金合計、出荷コスト、などを集計しています。　（参考程度に画像添付しました）

60%　販売名 　下代 　上代　卸(60%)　2017.8　2017.9　2017.10　2017.11
商品コード
CL001 商品１　¥600 ¥3,200 ¥1,920　　　1　　　　0　　　　6　　　　6
CL002 商品２　¥720 ¥2,800 ¥1,680　　　10　　　6　　　　0　　　　32

集計シートの4行目、F列～AB列に各タブと名前一致するように横に「2017.8」、、、と入力してあります。
集計シートのA列、8行目～54行目に商品コードが縦に入力してあります。

表一番端のA4セルには任意の掛率が入力できるようになっており、
集計シート、A4セルの掛率、A列の商品コード、F列の年月と
納品書のD列商品コード、F列数量、k列掛率を条件対象に月ごとに各商品が何個売れたか計算しています。

例えば
F8セル　=SUMIFS(INDIRECT("'"&F$4&"'!F:F"),INDIRECT("'"&F$4&"'!D:D"),$A26,INDIRECT("'"&F$4&"'!K:K"),$A$4)

集計シートのAC列には「合計」を設けており、過去今まで売れた総数が各商品コード別（行別）に＝SUMで計算されています。

ここに追加でC２とD2に開始日と終了日用のセル設け、そこに記入した期間だけ計算できるようにしたいです。
通常ですとsumifs関数などで簡単に求められると思いますが、
添付ファイルにあるように日付を文字列「2017.8」、、、で表記してしまっているので上手く動いてくれません。
書式設定を日付表記「2017年8月」、、へ変更するとsumifs関数は動いてくれるようになりましたが、
下表のsumifsが別タブとリンクしており、各タブをそれに合わせて変更していてもsumifs関数の方がエラーになってしまいます。


質問は、、、

◎「2017.8」、、を「2017年8月」、、へ表示変更した後、
各タブ名も「2017年8月」、、に変更した場合でもsumifsを正常に動かす方法はありますか？

↑こちらを解決してしまうのが一番近いかと感じております。
ちなみに、「2017.8」→「2017年8月」に形式変更へ日付表示するとセルの内容が「2017/8/1」と自動変換され　+/1　追加されてしまいます。
それだとタブの名前と合わないためにsumifsが上手く動かないのかなと思っていますがどうですか？
タブは月ごとの名前なので、「2017年8月1日」や「2017/8/1」にはしたくありません。

◎現状の状態で動くように自分なりに考えた関数がありますが、どれもエラーになります。
SUMPRODUCT関数も現状の状態だと年月を認識しないようで、途中で断念しております。
こちらに少し手を加えて適切に直すことは出来ますか？

=SUMIFS(F8:AA8,4:4,">="&C2,4:4,"<="&D2)
=SUM(OFFSET(ADDRESS(4,MATCH(C2,4:4,0),1),,,0,MATCH(D2,4:4,0)&"-"&MATCH(C2,4:4,0)))
=SUM(OFFSET(ADDRESS(4,MATCH(C2,4:4,0),1),1,0):OFFSET(ADDRESS(4,MATCH(D2,4:4,0),1),1,0))
=SUM(OFFSET(MATCH(C2,4:4,0),1,0):OFFSET(MATCH(D2,4:4,0),1,0))

◎その他良い方法ありますか？


分かりずらいと思いますが、エクセルファイル添付してしまった方がよいですか？
分かる方どうぞよろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2019/06/24 16:39

こんにちは

以前に、「日付はシリアル値にしておいた方が何かと便利ですよ」と回答した者です。

例えば、集計対象データがF列からAA列（？）までとして、日付がシリアル値である場合なら、C2とD2の間の日に該当する、8行目の数値の集計は
　=SUMPRODUCT(F8:AA8*(F4:AA4>=C2)*(F4:AA4<=D2))
といった計算式で求められるはずです。


ご提示のシートの日付データは文字列とのことですので、「文字列を一旦日付に変換」して比較すれば同様のことが可能になります。
（文字列のままで日付の比較ができれば良いのですが、あまりうまい方法がありませんので…）

例えば、C2セルの文字列「2019.5」を日付「2019.5.1」に変換するには
　=DATEVALUE(SUBSTITUTE(C2,".","/")&"/1")
などで可能ですが、SUMPRODUCT関数の中で、セル範囲に対するSUBSTITUTE関数が動作しないようです。
それなので、もう少しプリミティブな方法での変換となってしまいましたが、最初の式の中で日付に相当する部分を修正してみると…

=SUMPRODUCT(F8:AA8*(DATEVALUE(REPLACE(F4:AA4,FIND(".",F4:AA4),1,"/")&"/1")>=DATEVALUE(SUBSTITUTE(C2,".","/")&"/1"))*(DATEVALUE(REPLACE(F4:AA4,FIND(".",F4:AA4),1,"/")&"/1")<=DATEVALUE(SUBSTITUTE(D2,".","/")&"/1")))

といった式になることでしょう。（意味合いは最初の式と同じです）

※ ご質問の内容を把握できているのかどうかがよくわかりません。
　 半分は雰囲気で回答していますので、内容的にお門違いかも知れません。
　 その際はスルーしてください。

この回答へのお礼

前回は質問が適切でなかったので、同じ内容ですが再度質問しました。
今回も具体的な回答ありがとうございます！

素晴らしいです、私の求めていたように動作してくれました！

さらに、作って頂いた関数が参考になり、
日付をシリアル値に変更、各シートを「2017年8月」、、に変更、F列からAA列の各セルの計算を

F8　=SUMIFS(INDIRECT("'"&F$4&"'!F:F"),INDIRECT("'"&F$4&"'!D:D"),$A26,INDIRECT("'"&F$4&"'!K:K"),$A$4)

から

F8　=SUMIFS(INDIRECT("'"&YEAR(F$4)&"年"&MONTH(F$4)&"月"&"'!F:F"),INDIRECT("'"&YEAR(F$4)&"年"&MONTH(F$4)&"月"&"'!D:D"),$A8,INDIRECT("'"&YEAR(F$4)&"年"&MONTH(F$4)&"月"&"'!K:K"),$A$4)

にしてみたら、それもうまく動いてくれました。
これなら=SUMPRODUCT(F8:AA8*(F4:AA4>=C2)*(F4:AA4<=D2))で期間計算も簡単に求められそうです。


不思議なんですが
シート名が月別、=sumifsでシートを指定して集計ってよくあることかと思うんですが、
シート名のシリアル値設定とかってないんですね、、、

何はともあれ、ありがとうございました。

お礼日時：2019/06/24 20:25