Excelで複数条件を満たし残高を計算する関数

質問

A列　　　　　B列　　C列　　　　　D列　　　　　E列　　F列
　　　日付 　　　　No. 商品名　　仕入/販売　 金額 残高
1　　2010/12/1　　101　　りんご　　　仕入　　¥1,000 　　 ¥-
2　　2010/12/2　　101　　りんご　　　販売　　¥1,000 　　 ¥-
3　　2010/12/3　　122　　みかん　　　仕入　　¥1,200 　　 ¥400
4　　2010/12/4　　122　　みかん　　　販売　　¥800 　　　 ¥-
5　　2010/12/5　　135　　バナナ　　　仕入　　¥1,300 　　 ¥800
6　　2010/12/6　　135　　バナナ　　　仕入　　¥300 　　　 ¥-
7　　2010/12/7　　135　　バナナ　　　販売　　¥800 　　　 ¥-
8　　2010/12/8　　148　　ぶどう　　　仕入　　¥2,000 　　 ¥-
9　　2010/12/9　　148　　ぶどう　　　販売　　¥1,500 　　 ¥-
10　2010/12/10　　148　　ぶどう　　　販売　　 ¥500 ¥-

上記のようなEXCELの表があります。
B列No.（第一条件）、C列商品名（第二条件）が一致するものを
仕入から販売を引いてF列のような数字になるように残高を出したいです。
どんな関数でも構いません。
関数でできる方法があれば教えてください。
実際のデータ量が2000行くらいあるので手作業でするのが大変で…。
よろしくお願いしますｍ（＿　＿）ｍ

OS:WindowsXP
Office：2007

回答No.1

MackyNo1 · ベストアンサー

B列とC列の値は対応しているように見えますが、もし違うなら以下のような数式になります.

=IF(COUNTIF($B$2:B2,B2)=1,SUMPRODUCT(($B$2:$B$2000=B2)*($C$2:$C$20=C2)*($D$2:$D$2000="仕入")*$E$2:$E$2000)-SUMPRODUCT(($B$2:$B$2000=B2)*($C$2:$C$2000=C2)*($D$2:$D$2000="販売")*$E$2:$E$2000),"")

B列とC列の値が対応しているなら以下のような数式で表示できます。

=IF(COUNTIF($B$2:B2,B2)=1,SUMPRODUCT(($B$2:$B$2000=B2)*($D$2:$D$2000="仕入")*$E$2:$E$2000)-SUMPRODUCT(($B$2:$B$2000=B2)*($D$2:$D$2000="販売")*$E$2:$E$2000),"")

お礼

素早い回答ありがとうございました。
実際の表にこちらの関数を入れ込んだところだいぶ近い結果を得ることができました。
ただ実際の表がもうちょっと複雑なので修正しているところなのですが
うまく正しい結果が出ません（泣）
私の質問の仕方がいけなかったのかもしれないので
再度実際の表にもっと近い状態にして質問させていただきます。
このたびはありがとうございました。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

＃2です
計算負荷が気になるようでしたら、もう一工夫です
=IF(D2="販売",0,
IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))<>1,0,
SUMPRODUCT((B2:$B$2000=B2)*(C2:$C$2000=C2)*(D2:$D$2000={"仕入","販売"})*{1,-1}*E2:$E$2000)))
下へオートフィル、「販売」の数にもよりますが、負荷は半分以下になると思います。
2000行で試験し、当方では2秒足らずの計算が1秒足らずになりました。

OFFSET関数はSUMPRODUCT関数より凶悪です。
計算に関係のないセルに入力したり、削除するだけで10秒近く待たされます。
kagakusukiさん ぜひ、試験してみてください。

検索キーワードは 「揮発性関数」
INDIRECT関数もその部類入ります。

回答No.5

kagakusuki

回答番号:ANo.4です。
　もし、B列とC列の値は対応していて、且つB列の値が同じ値となっている複数の行の中には、仕入が必ず一回しか現れないと決まっている場合には、次の方法が使用可能になります。

　F2セルに次の数式を入力してから、F2セルをコピーして、F列の他のセルに貼り付けて下さい。

=IF(AND($D1="仕入",SUMIF($B:$B,$B1,$E:$E)<$E1*2),$E1*2-SUMIF($B:$B,$B1,$E:$E),"-")

回答No.4

kagakusuki

>SUMPRODUCT関数などを使いますとパソコンの動きが重くなります。

と仰る方がいますので、SUMPRODUCT関数を使用した割には計算負荷が多少軽くなる数式を紹介させて頂きます。
　データが入力されている最も下の行番号と、仕入れの商品Noが最初に現れる行番号に応じて、計算処理を行う範囲が自動調整されますので、表が2000行以上ある場合にも対応します。

￥－ の表記部分は0として F2セルに次の数式を入力してから、F2セルをコピーして、F列の他のセルに貼り付けて下さい。

=IF(COUNTIF($A2:$E2,"")>0,"",IF($D2="仕入",SUMPRODUCT((OFFSET($B$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)=$B2)*(OFFSET($C$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)=$C2)*OFFSET($E$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)*(1-2*(OFFSET($D$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)<>"仕入"))),"-"))

お礼

とても早い回答ありがとうございました。
実際の表に合わせて細かく関数を修正しながら入力してみたのですが
うまく結果が出ませんでした。
私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。回答いただいたのに申し訳ありません。。。
ありがとうございました。

回答No.3

KURUMITO

SUMPRODUCT関数などを使いますとパソコンの動きが重くなります。
作業列などを使って対応するのがよいでしょう。
例えばデータは2行目から下方にあるとしてＨ2セルには次の式を入力して下方にオートフィルドラッグします。

=B2&C2&D2

答えのＦ列にはＦ2セルに次の式を入力して下方にオートフィルドラッグします。

=IF(OR(B2="",D2="販売",COUNTIF(H$2:H2,H2)>1,SUMIF(H:H,B2&C2&"仕入",E:E)-SUMIF(H:H,B2&C2&"販売",E:E)=0),"",SUMIF(H:H,B2&C2&"仕入",E:E)-SUMIF(H:H,B2&C2&"販売",E:E))

お礼

とても早い回答ありがとうございました。
実際の表に合わせて細かく関数を修正しながら入力してみたのですが
うまく結果が出ませんでした。
私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。回答いただいたのに申し訳ありません。。。
ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

￥－ の表記部分は0として F2セルに
=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))<>1,0,
SUMPRODUCT((B2:$B$2000=B2)*(C2:$C$2000=C2)*(D2:$D$2000={"仕入","販売"})*{1,-1}*E2:$E$2000))

お礼

とても早い回答ありがとうございました。
実際の表が仕入/販売以外にもあったりして
細かく関数を修正しながら入力してみたのですが
うまく結果が出ませんでした。
私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。
ありがとうございました。