エクセルの関数を使い横方向の期間集計を行いたいです。
エクセル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ランキング
-
-1234 を (1234) と表記する由...
-
エクセルで現場ごとに機材の移...
-
他部署からもらう データで、 ...
-
Excel 1の位の数字で処理を分岐...
-
Excelのセルの色を変えた行(す...
-
エクセルの数式について教えて...
-
Excelでの記号と特殊文字につい...
-
とびとびの大量セルを選択した...
-
MACにおけるエクセルの入力トラ...
-
ある表にフィルターをかけて出...
-
Lookup関数
-
エクセルの数式バーのフォント...
-
再質問です。マクロの修正箇所...
-
エクセルで「ページレイアウト...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
exselの質問です
-
エクセルでファイルの最終更新...
-
Excel 大小比較演算子による「...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報