エクセル関数：複数の条件の合う数値の合計

質問

可能かどうかもわかりませんが。。。

納品の量を管理するためにエクセルを利用したいと考えております。

Sheet1
　　A　　　B 　　 C 　　 　D 　 E 　 ............
1　出荷先 納品日　りんご みかん いちご
-----------------------------------------
2倉庫　　12.01　　10　　　　0　　　　5
3八百屋　12.02 　0　　　 　4　　　　3
4お店ア　12.05 　3　　　　 2　　　　8
5顧客A　 12.03 　3　　　　 5　　　　0
6顧客B　 12.05 　0　　　　 15　　　 0
7お店イ　12.02 　2　　　　 2　　　　2
.
.
.
.

＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞

Sheet2
A　　　 B 　　 C 　　 D 　............
1納品日　りんご みかん いちご
-----------------------------------------
212.01
312.02
412.03
512.04
612.05
.
.
.

Sheet1に入力した内容を、Sheet2で管理できないものかと考えております。
例：Sheet2のセルB2に「12月1日」の「りんご」の納品予定数を表示できないものでしょうか。即ち、Sheet1より「12月5日」の「みかん」の納品数は「2」+「15」で17と関数で計算する方法はないものでしょうか。

VLOOKUPやSUMIFSなどをが使えるのかとも考え、色々と調べてみましたが、どうしてもうまくいきません。

どうぞ宜しくお願い致します。

回答No.6

bunjii · ベストアンサー

>商品や日付が増えてくるので、範囲固定ではなく設定できればいいのですが。
>もちろん必要な「＄」は残し、どうにかしてオートフィル用の計算式ができないかどうか挑戦してみます。
元データのテーブルのデータ数が多いと計算途中で使われるシステムメモリー（RAM）が不足して正しい値を算出できなくなります。
ExcelのバージョンとWindows OSの種別によって限界があります。
特に配列の計算では膨大な計算を行わなければならないため長時間を要することもあります。
従って、現在のデータの列数に空欄列を1～2列加え、行数も同様に1～2行加えた範囲に止めた数式を使ってください。
元データ側（Sheet1）の行数や列数を増やす時は空欄行または空欄列の前に行または列を挿入することで対処します。
集計側（Sheet2）では数式の参照範囲が増加したとき自動的に変更されます。
集計側の行または列を増やすときはオートフィルで数式をコピーすれば良いでしょう。
=IF(OR(A2="",B1=""),"",SUMPRODUCT((Sheet1!$C$2:$G$10)*(Sheet1!$B$2:$B$10=$A2)*(Sheet1!$C$1:$G$1=B$1)))
G列の前に1列挿入した結果は次の数式になります。
=IF(OR(A2="",B1=""),"",SUMPRODUCT((Sheet1!$C$2:$H$10)*(Sheet1!$B$2:$B$10=$A2)*(Sheet1!$C$1:$H$1=B$1)))
更に10行目の上に1行挿入すると次のようになります。
=IF(OR(A2="",B1=""),"",SUMPRODUCT((Sheet1!$C$2:$H$11)*(Sheet1!$B$2:$B$11=$A2)*(Sheet1!$C$1:$H$1=B$1)))
これらの対策でより効率的な運用ができるでしょう。
知識を積んで応用力を高めてください。

お礼

色々と勉強になりますコメント、ありがとうございます！

SUMPRODUCT、少し学んでみます！

回答No.5

tom04

No.3です。

Sheet1とSheet2の「商品」の並びが異なるというコトですので、
もう一度画像をアップします。
Sheet2の1行目（商品）は手入力するとします。

画像ではSheet2のB2セルに
=IF(OR($A2="",B$1=""),"",SUMIF(Sheet1!$B:$B,$A2,OFFSET(Sheet1!$A:$A,,MATCH(B$1,Sheet1!$1:$1,0)-1,,1)))
という数式を入れ、列・行方向にフィルハンドルでコピーしています。

これで列方向の並びがバラバラでもちゃんと表示されると思います。

※　Sheet2の1行目項目がSheet1にない場合はエラーとなります。m(_ _)m

お礼

恐れ入ります。

SUMPRODUCTについてちょっと勉強してました。いやはや、すごい計算式、ありがとうございます。
エクセルを仕事で使っていた時期は独学である程度学んだのですが、ここ数年、エクセルから離れてまして。。。
計算式が出来た時の喜びは一入ですね。
今回も、出来るだろうと色々と右往左往、昔使ったLOOKUPなんて引っ張ってきて色々考えましたが、私には無理でした。

お礼申し上げます！

回答No.4

bunjii

>VLOOKUPやSUMIFSなどをが使えるのかとも考え、色々と調べてみましたが、どうしてもうまくいきません。
SUMPRODUCT関数が最も適しています。
貼付画像はExcel 2013で検証した結果です。
Sheet1のB列とSheet2のA列は日付のシリアル値です。（形式は[$-411]m.d）
Sheet2!B2=SUMPRODUCT((Sheet1!$C$2:$E$7)*(Sheet1!$B$2:$B$7=$A2)*(Sheet1!$C$1:$E$1=B$1))
右と下にオートフィルでコピーしました。
実情に合わせてセル範囲を変更してください。
尚、エラーチェックは行っていませんので必要なときはExcelのバージョンに対応した方法で処理してください。

お礼

ありがとうございます。

SUMPRODUCT関数、知りませんでした。。。勉強不足でした。

商品や日付が増えてくるので、範囲固定ではなく設定できればいいのですが。もちろん必要な「＄」は残し、どうにかしてオートフィル用の計算式ができないかどうか挑戦してみます。

Sheet2!B2=IF($A2="","",SUMPRODUCT((Sheet1!$C$2:$IV$65536)*(Sheet1!B:B=$A2)*(Sheet1!$C$1:$IV$1=B$1)))

みたいになるのかなぁ。。。

回答No.3

tom04

こんにちは！

Sheet1とSheet2の商品の列の並びが同じであれば
No.1さんが回答されているようにSUMIF関数だけで対応できます。

質問では「日付」の列が「12.01」のようになっていますが、
シリアル値なのでしょうか？
それはさておいて・・・
↓の画像のように上側がSheet1・下側がSheet2とします。
両Sheetとも日付の列は同じ入力方法（シリアル値でなくても同じデータがある）とします。

Sheet2のB2セルに
=IF($A2="","",SUMIF(Sheet1!$B:$B,$A2,Sheet1!C:C))
という数式を入れ列・行方向にフィルハンドルでコピー！

これで画像のような感じになります。m(_ _)m

お礼

ご回答ありがとうございます。

おっしゃる通りです。列の並びが同じであれば、ご回答頂いた式でオートフィルすれば。。。

言葉不足で申し訳ございません、ただ、仕事の能率アップで、列の並びをSheet2で変えることが出来ればと思っております。

butter1122さんにもお返事させていただきましたが：
行１の「商品」が多いため、Sheet2の「行１」のとある商品を検索の"条件"として計算する方法はないのかと思った次第です。

例えば、私の例ですとSheet1の行1は列Cから、りんご、みかん、いちご、のように並んでいますが、Sheet2で同様に並べないでもどうにか計算できないものでしょうか。

Sheet1、Sheet2、もちろん商品の記載は同じになりますが、列の「並び方」は２つのシートで変えることが出来れば仕事の能率が上がるので、上記のように、商品を検索の"条件"として計算する方法はないのかと思った次第です。

Sheet1は表記の通りで、Sheet2の行1を列Ｂからりんご、いちご、みかん、とした場合、オートフィルを使用できず（縦のオートフィルは使えるのでしょうが）、列ごとに関数を割り当てないとなりません。

回答No.2

butter1122

すみません、先ほど回答した者ですが補足です。

数式の中のＡ5　の部分は集計したい日付が入力してあるセルです(^^♪

お礼

はい、了解しました。
ありがとうございます。

回答No.1

butter1122

それだとＳＵＭＩＦでいけると思いますが・・・

例えばsheet1のA1からA7に日付が入っていたとして、
B1からB7にみかんの数字が入っていたとします。

=SUMIF(Sheet1!A1:A7,Sheet1!A5,Sheet1!B1:B7)

となります。
SUMIFSはセルの個数なので数字の合計値ではありません(^^♪

ここだけできてもまた疑問は出てくるでしょうからパソコン教室にでも通うことをお勧めします(#^.^#)

お礼

早速、ご回答ありがとうございます。

説明不足で申し訳ございません。列１の「商品」が多いため、Sheet2の「列１」のとある商品を検索の"条件"として計算する方法はないのかと思った次第です。

例えば、私の例ですとSheet1の列1は行Cから、りんご、みかん、いちご、のように並んでいますが、Sheet2で同様に並べないでもどうにか計算できないものでしょうか。

Sheet1、Sheet2、もちろん商品の記載は同じになりますが、行の「並び方」は２つのシートで変えることが出来れば仕事の能率が上がるので、上記のように、商品を検索の"条件"として計算する方法はないのかと思った次第です。

Sheet1は表記の通りで、Sheet2の列1を行Ｂからりんご、いちご、みかん、とした場合、オートフィルを使用できず（縦のオートフィルは使えるのでしょうが）、行ごとに関数を割り当てないとなりません。

お手数をおかけし、言葉が足らずに申し訳ございませんでした。

補足

列と行を間違えました。。。

説明不足で申し訳ございません。行１の「商品」が多いため、Sheet2の「行１」のとある商品を検索の"条件"として計算する方法はないのかと思った次第です。

例えば、私の例ですとSheet1の行1は列Cから、りんご、みかん、いちご、のように並んでいますが、Sheet2で同様に並べないでもどうにか計算できないものでしょうか。

Sheet1、Sheet2、もちろん商品の記載は同じになりますが、列の「並び方」は２つのシートで変えることが出来れば仕事の能率が上がるので、上記のように、商品を検索の"条件"として計算する方法はないのかと思った次第です。

Sheet1は表記の通りで、Sheet2の行1を列Ｂからりんご、いちご、みかん、とした場合、オートフィルを使用できず（縦のオートフィルは使えるのでしょうが）、列ごとに関数を割り当てないとなりません。

お手数をおかけし、言葉が足らずに申し訳ございませんでした。