複数条件での値参照をする関数

質問

Excel2007を使っています。
以下のような表があります。
店番　　月度　　個数
111　　　　　4　　　　1
111　　　　　5　　　　0
113　　　　　4　　　　0
112　　　　　4　　　　1
113　　　　　5　　　　2

別のシートに以下の表があります。
店番　　4月個数　　5月個数
111
112
113

この4月個数の列と、5月個数の列に関数で値を参照させたいと思います。
店番をA1セルとすると、B2セルには店番が111の4月の個数を表示させたいです。

B2に
=IF(ISERROR(VLOOKUP(A2,元!A:B,2,0)),"",IF(VLOOKUP(A2,元!A:C,2,0)<>4,"",VLOOKUP(A2,元!A:C,3,0)))
C2に
=IF(ISERROR(VLOOKUP(A1,元!A:B,2,0)),"",IF(VLOOKUP(A1,元!A:C,2,0)<>5,"",VLOOKUP(A1,元!A:C,3,0)))
このような式を入れると、4月か5月どちらか上に書いた方しかデータが表示されませんでした。

更新ボタンやトリガーを使わずにできるといいので、ピボットやVBAではなく関数でやりたいと思っているのですが、どのようにすれば良いか分かりません。
使える関数があれば、ぜひ教えて下さい。

回答No.3

mu2011 · ベストアンサー

Excel2007からSUMIF関数が複数条件は可能な様に機能拡張しています。
=SUMIFS(個数列,店番列,店番,月度列,月数)となりますので、=SUMIFS(sheet1!$C:$C,sheet1!$A:$A,$A2,sheet1!$B:$B,B$1)で如何でしょうか。
仮に元表をsheet1、別表をsheet2として、１行目の書式を0"月個数"としています。

お礼

知りませんでした！！
この式でもできました（≧▽≦）
これはとても分かりやすいですーーー。
しかも色々応用が効きそうです。
SUMIFなんてメジャーすぎて、拡張してるとか考えもしませんでした。
というか、地味にsがついていて違う関数なんですね。

回答No.4

hallo-2007

No1の方が紹介されています。SUMPRODUCT関数になると思いますが、
SUMPRODUCT関数の様な配列関数は、この様なシートに右フィル、下フィルして多用すると、入力の作業など含めて重くなります。
＞更新ボタンやトリガーを使わずにできるといい
関数を使用することになりますが、重くなることあります。
＞ピボット
トリガーすることになりますが、通常の作業に影響がない。

もしシートが月順に並んでいるようであれば、配列関数ではなく
SUMIFとMATCH関数やOFFSET関数で組み合わせることもできるので
負担を減らすことが可能です。

お礼

確かに容量もおおきくなりますよね…。
今のところは件数が少ないので大丈夫ですが、多くなったらダメかもしれませんね…。
月度は微妙に前後したり、実際のデータは個数の他にもいくつか項目があるうえ、月によって項目数が違ったりしています。
あまりにも重くなったら、更新ボタン押したくないなどわがまま言わずにVBAなどに頼ってみようと思います。
アドバイスありがとうございました！

回答No.2

sytkn

ANo.1さんの補足です。ANo.1さん、式お借りします。

=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$B$2:$B$6=「B$1」この部分),Sheet1!$C$2:$C$6)

別シートでは　B$1が「4月個数」
元シートでは　$B$2:$B$6　データ　4
で内容が違う為、表示が出ません。

別シートの　B1を「4」にしてセルの書式の設定で
ユーザー定義　G/標準"月個数"にするといいですよ～

Sheet1もシート名を「元」に変更されているようなので変更してくださいね～

お礼

おっしゃる通りでした！
アドバイスありがとうございます！！！

＞別シートの　B1を「4」にしてセルの書式の設定で
＞ユーザー定義　G/標準"月個数"にするといいですよ～
これはいい方法ですね！！！これだと全部フィルコピーできますね。
アイディアが素晴らしいデス（＊＾－＾＊）

回答No.1

jo-zen

SUMPRODUCT関数を使えば大丈夫です。

　別シートのＢ２セルに

　　=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$B$2:$B$6=B$1),Sheet1!$C$2:$C$6)

と入力してください。Ｂ３，Ｂ４,Ｃ２～Ｃ４へはそのままコピーしてあげればＯＫです。

お礼

ありがとうございます！！！！
できました(T-T)
前半部分が見つかれば1になるというのがポイントなんですね。
TRUEは1、FALSEは0というのを利用しているんですね…。すごい…。