エクセルの関数を使い横方向の期間集計を行いたいです。
エクセル2013使用
月ごとに商品の売れた個数を確認出来るよう集計用シートを作成しました。
現在のシートで開始日と終了日を指定して、特定の期間にどのくらい売れたかを確認出来るようにするのが目的です。
ブックは1つで構成。
2017年8月~2019年5月までの各タブ+集計タブ
2017年8月~各月ごとにタブで納品書をまとめてあります。
1つの納品書は全33行で構成されており、
商品は15品目まで入れられるようになっていて、横に続く形で番号、ブランド名、商品名、商品コード、サイズ、数量、原単価、原価金額、売単価、売値金額、掛率となっています。
過去今までの売れ行き動向が分かるように、
「集計」タブを作り納品書に記載のある数量をsumifs関数で計算し、
仕入合計、売掛金合計、出荷コスト、などを集計しています。 (参考程度に画像添付しました)
60% 販売名 下代 上代 卸(60%) 2017.8 2017.9 2017.10 2017.11
商品コード
CL001 商品1 ¥600 ¥3,200 ¥1,920 1 0 6 6
CL002 商品2 ¥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で計算されています。
ここに追加でC2と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ベストアンサー
- 回答日時:
こんにちは
以前に、「日付はシリアル値にしておいた方が何かと便利ですよ」と回答した者です。
例えば、集計対象データが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でシートを指定して集計ってよくあることかと思うんですが、
シート名のシリアル値設定とかってないんですね、、、
何はともあれ、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) 年齢ごとの商品の販売個数を集計しようとしています。 a列が日付、b列が年齢、c列が販売個数のばあいで 4 2022/09/26 22:35
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) エクセルでSUMIFS関数で条件範囲の部分が#valueになる。 4 2023/04/28 12:42
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) SUMIFS 一部の条件のどちらかを参照する場合を教えてください。 2 2022/04/26 12:48
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】ファイル名の変更に...
-
Aというブックの1というシート...
-
エクセルで 例えば 伊藤と名前...
-
Excelで、決まった行を繰り返し...
-
エクセルの条件付き書式につい...
-
エクセルで「-0.0」と表示さ...
-
【マクロ】毎回、ファイル名が...
-
Excel元に戻す方法を教えてくだ...
-
【マクロ】シート名を取得する...
-
Excel2013のF6キー操作について
-
excel で二つのどちらかを選ぶ
-
Excelでの時間帯の入力
-
Excel 2019 のピボットテーブル...
-
【マクロ】フォルダからエクセ...
-
マクロの有効化するダイヤログ...
-
行数が不規則な一週間ごとの合...
-
エクセルで特定の範囲内から小...
-
ある列、或いは、ある行のセル...
-
シートAで横に並んだ項目→シー...
-
Excelが固まってしまった。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報