複数条件（前方一致含む）の合計の出し方について

再びお世話になります。
シート１の対象年月と商品が一致した売上をシート２で集計したいのですが、上手く集計が取れなかったのでご教示ください。

【表】シート１のA1からC5にデータが入力されているとして。
　　　（CSVで出力の為、すべて文字列状態）
　　A列　　　　　　B列　　　　　　C列
１　日付　　　　　　商品　　　　　売上金額
２　20150401　　　卵　　　　　　100
３　20150409　　　卵　　　　　　110
４　20150502　　　パン　　　　　200
５　20160102　　　野菜　　　　　50
６　20160110　　　野菜　　　　　80


シート2で集計します。
【作業日が2017/04/16として、前年度の前月と同月の売上を集計します】
・A1=作業日当日日付、B1=前年前月とC1=前年同月はDATE関数でA1を元に年月を出しています。
・D列はシート１のA列を、E列でTEXT関数で日付表示にしてからD列でDATE関数でこの表記に変えていますので、5000行程続いています。（TEXT関数だと形式が一致しないようだったので…）

　　A列　　　　　　B列　　　　　　C列　　　　　D列（日付作業列）
１　(作業日日付)　　2015/03/16 2015/04/16 2015/04/01　　
２　卵.............................................................2015/04/09
３　パン..........................................................2015/05/02
４　野菜

このような状態で、
B2には、B1とD列の前7文字が一致（年月が一致）　かつ　A2とシート1のB列の表示が一致（商品が一致）したものの売上合計を表示。
いきなり全体の式を組むのは私には無理なので、少しずつ計算していこうとしたのですが、
B2に「=SUMIF(D:D,LEFT($B$1,7)&"*",シート1!C:C)」と入れてみたものの計算が反映せず行き詰ってしまいまいた…。
皆さまのお知恵をお借りしたく、宜しくお願いいたします。

No.5 ベストアンサー 回答者： yuji3690 回答日時： 2017/04/16 20:35

まず、やりたい事を順番にまとめて、それに応じた適切な作業列を設けましょう。

やりたい事について
①作業日の日付（シート2!A1）から前年前月（シート2!B1）と前年同月（シート2!C1）を表示させる。
②シート2のB列・C列に「シート1のA列の月が、シート2の1行目に表示した月と一致するシート1のデータから、シート1のB列がシート2のA列と一致するデータの、シート1のC列に表示された金額を合計したもの」を表示させる。

作業列例について
①については特に作業列は必要ありませんが、検索したいのは年月の分かる6桁の数字なので、
シート2!B2=TEXT(DATE(YEAR(A1)-1,MONTH(A1)-1,1),"yyyymm")
シート2!C2=TEXT(DATE(YEAR(A1)-1,MONTH(A1),1),"yyyymm")
としましょう。
日にちは関係ないので、年月をA1とから算出し、日は1日にしています。
質問者の例の状態では、B2=201503、C2=201504、とそれぞれ表示されるはずです。

②についてですが、
合計を計算するためには、シート1のC列を文字列ではなく数値で表示させる必要があります。
よってシート1のD列を作業列とし、
シート1!D2=C2*1
これをデータの数だけ↓にコピーしましょう。
*1とすることで、C2が数値として利用できるデータが文字列として入っている場合、数値として扱って計算結果を表示してくれます。
*1なので、値としては同じですね。+0としてもかまいません。

これで準備は整いました。
シート2のD列で表示させたデータは特に必要ないというわけですね（汗
あとは結果を表示させるだけです。
シート2!B2=SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,B$1&"*",Sheet1!$B:$B,$A2)
これを表の分だけ（例の場合C4まで）コピーしましょう。

あくまでシート1のデータが全て文字列として入力されていた場合です。
結果が合わない場合は、おそらく文字列として入力されていると思い込んでいるだけ、
といった可能性もあります。（CSVについては良く知りませんので）

この回答へのお礼

引き続きのご回答ありがとうございます。
行き詰っていたせいで分かりにくい表記の質問になってしまっていましたが、分かりやすく整理して頂き、頭の中がすっきりしました。
②はシート１に他にも多くの項目が入っているため、シート2のD列を入れ替えで使ってみました。
詳しく解説頂きありがとうございます！解決しましたm(__)m

お礼日時：2017/04/16 23:17

No.4 回答者： WindFaller 回答日時： 2017/04/16 17:27

>（CSVで出力の為、すべて文字列状態）

まず、特に、加工した状態でなければ、文字列状態とはいいませんが……。
また、書式やプログラムで、そんな面倒なことをしてはいないはずです。
Sheet1 のCSVは×インポート◯オープンしたものになるのですが、たぶん、単に開いただけのものだと思います。（インポートは、データ加工してから表に入れること）

私は前の経緯を読んでいないのですが、少し内容的にわかりにくく感じています。

Sheet1 CSVを、
>E列でTEXT関数で日付表示にしてから
>D列でDATE関数でこの表記に変えていますので、5000行程続いています。
Text関数から、日付値（シリアル値）に変更出来ますが、DATE関数は使いません。

その日付値をどう処理するかで決まるはずです。
SUMIF でも出来ますが、文字列にすべて置き換えていくのも問題ですね。
正統派としたら、シリアル値の日付表示にするところだと思います。
-------------------------
そうすると、#2様のおっしゃっているように、
「SUMIF」ではなく、「SUMIFS」関数を選ばなくてはなりません。
ということは、また最初から降り出しに戻らなくてはなりません。

1.
Sheet1 D列は、タイトルは[日付SR(仮)]
=TEXT(A2,"##!/##!/##")*1 　これで、シリアル値になります。
　　↓
とします。シリアル番号そのものが出てきてしまいます。表示が気になるなら、書式を設定してください。

2.
Sheet2:B2～（右へ）
=SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,$A2,Sheet1!$D:$D,">=" &DATE(YEAR(B$1),MONTH(B$1),1),Sheet1!$D:$D,"<=" &DATE(YEAR(B$1),MONTH(B$1)+1,0))

なお、表示が遅いようでしたら、C:C や D:D を使わずに、C$2:C$10000 と範囲を限定した方がよいかもしれません。Excel2003までは、そのスタイルは問題なかったようですが、仕様が変わったそうです。

'-----------------
>　　A列　　　　　　B列　　　　　　C列　　　　　D列（日付作業列）
１　(作業日日付)　　2015/03/16 2015/04/16 2015/05/16 ←５月になるはず　　

1行目は、書式で、YYYY年M月としたほうがよいでしょう。

No.3 回答者： B_one 回答日時： 2017/04/16 14:14

ちょっと意味不明な点があります。

（CSVで出力の為、すべて文字列状態）とありますが、シート1のC列はCSVファイルを読み込んでも、EXCELシートでは数値に変換されているはずですが。

シート2のA列の品名は手入力したものですよね？

D列はシート1のA列(文字列の日付)を通常の日付に変換したものなので、シート2のD列ではなく、シート1のD列に表示すべきものではありませんか?

E列に日付作業列をもうけなくてもD列に次の式で直接変換できます。

=DATEVALUE(LEFT(Sheet1!A2,4)&"/"&MID(Sheet1!A2,5,2)&"/"&MID(Sheet1!A2,7,2))

なお、該当するセルの書式設定はyyyy/mm/ddとします。

上の式を使って日付をシート1のD列に表示するようにしたとすると画像のようになります。

複数条件での合計はSUMIF関数を使うのですが、今回の場合hSUMIFS関数を使ってみたのですがうまくいきませんでした。

なので、SUMPRODUCTを使っています。

シート2のB2に下の式を入力し、B列とC列にコピーして下さい。

=SUMPRODUCT((Sheet1!$B$2:$B$6=$A2)*(YEAR(Sheet1!$D$2:$D$6)=YEAR(B$1))*(MONTH(Sheet1!$D$2:$D$6)=MONTH(B$1))*Sheet1!$C$2:$C$6)

なお、式中の＄6の部分は＄5000のように、データーの列数によって適宜書き換えて下さい。

この回答へのお礼

ご回答ありがとうございます。
売上数字については自分の思い込みで、数値になっておりました。
シート１については、実は他にCZ列あたりまで項目があるという事と、毎月データの更新で1000行程が増えるという事情があるので、触らないようにしてシート２で作業列を作りました。
教えて頂いた通りのやり方で、シート１のD列を変えた場合だとうまく集計がとれました。シート2に作業列を持ったままの場合ももう少し頑張ってみます！

お礼日時：2017/04/16 22:19

No.2 回答者： soixante 回答日時： 2017/04/16 11:38

シート2のB1セルは、日付に見えていますが、シリアル値となっていますのでその式ではダメかと思います。

シリアル値に対して、LEFT対応では意味がありません。

良く分からないのですが、元データ（全売上）はシート２なのですか？　5000行もあるっていうのなら。
SUMIF関数じゃなくて、普通に　SUMIFS関数（複数条件を満たすものの合計）を使ってはどうですか。

この回答へのお礼

ご回答ありがとうございます。
行き詰って頭が混乱しており、分かりにくい表記になってしまってすみません。。
元データはすべてシート１です。日付に見えてシリアル値というお言葉に、あ…、と思いました。確かにLEFTでは意味がなかったですね…。

お礼日時：2017/04/16 22:10

No.1 回答者： ママチャリ 回答日時： 2017/04/16 11:34

致命的な障害が1点あります。

シート１はすべて文字列とのことですが、C列の売上金額も文字なのですよね？SUM系の関数は、文字を相手にしてくれないので、このままでは集計することができません。売上金額だけでも数字にすべきです。

売上金額が数字であれば、次の数式で集計できるはずです。

【シート2！B2セル】=SUMIFS(シート1!C:C,シート1!A:A,TEXT($B$1,"yyyymm")&"*",シート1!B:B,$A2)

この回答へのお礼

ご回答ありがとうございます。
売上数字は文字列だと思い込んでいただけでした…。
教えて頂いた式は上記のサンプル表では問題なく動いたのですが、集計したい表に入れると上手く集計が取れませんでした。もう一度日付のあたりを見直します！

お礼日時：2017/04/16 22:06