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

質問

以前、下記の質問をさせていただきましたが、うまく稼働しないため（私の質問の仕方が悪く…）
http://okwave.jp/qa/q6382052.html
再度実際の表に近い状態で質問させていただきます。
よろしくお願いします。
A列　　B列　　　C列　　　D列　　 E列 　　 F列　　 G列
　 日付　　No.　　　商品名　　金額　　数量 　　区分　　 残高
1　 12/1　　101　　りんご　　 7000　　　10　　　売上　　　0
2　 11/2　　101　　りんご　　 7000　　　10　　　在庫　　　0
3　 10/3　　101　　りんご　 　15000　　 15　　　在庫　　15000

4　 12/4　　205　　いちご　　 8000　　 10 　　売上　　　0
5　 10/4　　205　　いちご　　 8000　　 10 　　 在庫　　　0
6　 11/5　　205　　いちご　　 8000　　 15 　　 在庫　　　8000

7　 10/5　　306 バナナ　 　9000　　　10　　　在庫　　　2000　←在庫のうち一部販売している
8　 11/5　　306　　バナナ　　 7000　　　 8　　　売上　　　0

9　 12/3　　411　　みかん　　 2000　　　10　　　仕入　　　500
10　11/2 　411　　みかん　　 1000 　　　　5 　　在庫　　　0
11 12/5 　411　　 みかん　　1500　　　　8　　　売上　　　0

12 12/3　　523　　 ぶどう 　　2000 　　10 　　仕入　　　0
13　11/2 　523　　　ぶどう　　 2000 　　 10　　　売上　　　0
14　12/5　　523　　マスカット　 1500　 8 　　　仕入　　　0
15　12/1　　523　　マスカット 1500　　　　8　　　　売上　　　0

上記のような表があります。
B列No.（第一条件）、C列商品名（第二条件）が一致して
在庫、仕入がある場合は在庫+仕入-売上、
在庫のみの場合は在庫-売上、
在庫がない場合は在庫-売上
になるようにG列のような数字になるように残高を出したいです。
ちなみに「いちご」のように在庫が複数ある場合がありますが
その場合は、一番最新の日付の在庫として残高を出したいです。
またNo.は同じですが、「ぶどう」「マスカット」のように商品名が同一ではないものもあります。

それぞれの商品によって条件が違うので改行でわかりやすく表示しています。
1つの関数で処理することは可能でしょうか？
1つの関数で処理するのが困難であれば、いくつかの関数に分けて処理する方法でも構いません。
またどんな関数でも構いません。
関数でできる方法があれば教えてください。
実際のデータ量が2000行くらいあるので手作業でするのが大変で…。
よろしくお願いしますｍ（＿　＿）ｍ

回答No.5

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

1. 日付を降順で並べ替える
　(優先順位が4番目になる)

2. データ 並べ替え
最優先 No. 昇順
　2番目 商品名 昇順
　3番目 区分
[オプション]ふりがなを使わない

3. 文字の連結
G2セルに =B2&C2

4. 検索範囲の行番号
H2セルに
=IF(G1=G2,"",MATCH(G2,G$2:G$2000)+1)

5. 残高
I2セルに
=IF($H2="","",SUM(
SUMIF($F2:INDEX($F:$F,$H2),{"在庫","仕入"},D2:INDEX(D:D,$H2)),
-SUMIF($F2:INDEX($F:$F,$H2),"売上",D2:INDEX(D:D,$H2))))

6. 残数
I2セルを右へオートフィル

7. 数式のコピー
G2:J2セルを選択して フィルハンドルをダブルクリック

気分的には 4.の検索範囲の行番号は
=IF(G1=G2,"",ROW()+COUNTIF(G3:G$2500,G2))
にしておきたいし、そこまで計算速度にこだわらないと思うので。

お礼

お礼が遅くなり申し訳ありません。
画像でわかりやすくしていただきありがとうございました。
まさしく表示したい方法でしたので
こちらをいくらか修正しながら作業したら
ほぼ思い通りの結果を得ることができました。
ただ４の関数だけうまく計算できなかったため
最後に書いてある関数を使用したらきちんと計算できました。
いろいろとご丁寧にありがとうございました。
またご質問させていただく際にはよろしくお願いします。

回答No.6

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

＃5です
在庫と仕入が同じ日で最新だと以下の式でダブルが、どちらが優先だろうか？

=IF($F2="売上","",
IF(MAX(($B2=$B$2:$B$2000)*($C2=$C$2:$C$2000)*
($F$2:$F$2000={"在庫","仕入"})*$A$2:$A$2000)<>$A2,"",
SUMPRODUCT(($B2=$B$2:$B$2000)*($C2=$C$2:$C$2000)*
($F$2:$F$2000={"在庫","仕入","売上"})*D$2:D$2000*{1,1,-1})))
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)
右へ下へオートフィル
計算速度無視。質問者の理解無視。単なる自己満足です。

回答No.4

tom04

こんばんは！
横からお邪魔します。
一例です。
データが2000行位あるということなので、極力配列数式は使わないように↓の画像のような感じで
作業列を3列設けています。
（だたし、どうしても配列数式を一つ使ってしまいました）

Sheet1の作業列1のI2セルに
=C2&F2
作業列2のJ2セルに
=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"")
K2セルに
=IF(A2="","",IF(A2=MAX(IF($C$2:$C$2000=C2,$A$2:$A$2000)),1,""))
このK列だけが配列数式になってしまいますので、この画面からK2セルにコピー＆ペーストした後数式バー内で一度クリックしてください。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このI2～K2セルを範囲指定し、K2セルのフィルハンドルで下へずぃ～～～！っとコピー！

そして、Sheet2にも表を作っています。
Sheet2のB1セルに
=IF(COUNT(Sheet1!$J:$J)<COLUMN(Sheet1!A1),"",INDEX(Sheet1!$C:$C,SMALL(Sheet1!$J:$J,COLUMN(Sheet1!A1))))
という数式を入れ、列方向（右方向）にオートフィルでコピー！
これでSheet1の商品名がすべて表示されます。
B2セルには
=IF(B$1="","",SUMIF(Sheet1!$I:$I,B$1&$A6,Sheet1!$D:$D))
として「売上」のB4セルまでコピー
B5セルは単に
=IF(B1="","",B2+B3-B4)
同様に「数量」の表も作っておきます。
B6セルを
=IF(B$1="","",SUMIF(Sheet1!$I:$I,B$1&$A2,Sheet1!$E:$E))
としてB8セルまでコピー
B9セルは
=IF(B1="","",B6+B7-B8)
B2～B9セルを範囲指定し、B9セルのフィルハンドルで列（右）方向へオートフィルでコピーします。
これでやっと準備が整いました。
最後にSheet1のG2セルに
=IF(K2="","",INDEX(Sheet2!$5:$5,,MATCH(C2,Sheet2!$1:$1,0)))
H2セルに
=IF(K2="","",INDEX(Sheet2!$9:$9,,MATCH(C2,Sheet2!$1:$1,0)))
という数式を入れ、G2・H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、Sheet2のデータができていないうちはSheet1の作業列に数式を入れてもエラーになるか何も表示されないとおもいますが、
それは無視して淡々と数式を入れてみてください。

以上、長々と書きましたが
外していたらごめんなさいね。m(__)m

お礼

お礼が遅くなり申し訳ありません。
画像を貼り付けてくださりありがとうございました。
ただ商品数がとても多いため別シートに在庫表を作ることを考えていなかったため
ほかの方の回答を参考にさせていただくことにしました。
ご回答くださったのに申し訳ありません。
ご丁寧にありがとうございました。

回答No.3

kagakusuki

これがもし、在庫の行が無い場合には、仕入の行に集計結果を表示し、在庫の行がある場合には（例え仕入の行があったとしても）必ず最新の在庫の行に表示するのであれば、作業列を必要とせず、1つの関数で処理する事が可能なのですが、仕入と在庫の両方がある場合には、仕入の行に集計結果を表示するという条件では、作業列を使用する方法しか見つける事は出来ませんでした。

　今仮に、表が存在しているシートをSheet1であるとし、Sheet2のA列を作業列として使用するものとします。
　まず、Sheet2のA1セルに次の数式を入力してから、そのセルをコピーして、A2以下に貼り付けて下さい。

=IF(COUNTIF(Sheet1!$A1:$F1,"")>0,"",IF(SUMPRODUCT((OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)=Sheet1!$B1)*(OFFSET(Sheet1!$C$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)=Sheet1!$C1)*(OFFSET(Sheet1!$F$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)="在庫")*(OFFSET(Sheet1!$A$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)>=Sheet1!$A1))=1,Sheet1!$B1&Sheet1!$C1,""))

　次に、Sheet1のG1セルに次の数式を入力してから、そのセルをコピーして、Sheet1のG列とH列のその他のセルに貼り付けて下さい。

=IF(COUNTIF($A1:$F1,"")>0,"",IF(OR($F1="仕入",AND(SUMPRODUCT(($B$2:$B$16=$B1)*($C$2:$C$16=$C1)*($F$2:$F$16="仕入"))=0,$T1<>"")),SUMPRODUCT((OFFSET($B$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)=$B1)*(OFFSET($C$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)=$C1)*OFFSET(D$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)*(LOOKUP(OFFSET($F$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1),{"","仕入","在庫","売上";0,1,0,-1})))+IF(COUNTIF(Sheet2!$A:$A,$B1&$C1)=0,0,INDEX(D:D,MATCH($B1&$C1,Sheet2!$A:$A,0))),"-"))

　以上です。

お礼

お礼が遅くなり申し訳ありません。
こちらの関数で検証させていただきましたが
OFFSET関数が重たいのか計算に少し時間がかかるようでしたので
別の方の回答を参考にさせていただきました。
ご回答くださったのに申し訳ありません。
ご丁寧にありがとうございました。

回答No.2

kagakusuki

幾つか不明な点がありますので追加情報を補足願います。

●残高をどの行に表示すべきなのかが不明です。
　B列とC列が同じデータの中で、F列に「仕入」がある場合には、必ずF列に「仕入」と入力されている行に、残高を表示させればよいのでしょうか?
　F列に「仕入」が無い場合、B列が「101」でC列が「りんご」のデータでは、F列が「在庫」となっている中で最も古い日付の行に、残高が表示されているのに対して、B列が「205」でC列が「いちご」のデータでは、F列が「在庫」となっている中で最も新しい日付の行に、残高が表示されていますが、残高を表示させる行はどのような規則に従って決まるのでしょうか?

●在庫＋仕入－売上の計算が合っていない様ですが、どの様な計算になっているのでしょうか?
　例えば、B列が「101」でC列が「りんご」のデータでは、10/3に数量が15で金額と残高が15000であったものが、途中に売上や仕入れが無かったにも関わらず、11/2にはいきなり数量が10で金額が7000、残高が0になっています。
　しかも、10/3から11/2の間に数量は3分の2になっただけであるのに、金額は15分の7になっていますから、同じ商品でも単価が異なる事になります。
　同じ事が205のいちごでも起きています。
　411のみかんでは、11/2に在庫の数量が5で金額が1000あったところへ、12/3に数量が10で金額が2000の仕入れがあったのですから、合計で数量が15の金額が3000ある筈ですが、12/5に数量が3の金額が1500を売り上げただけで、残高が500になっています。

●
>在庫がない場合は在庫-売上

とありますが、在庫が無いのにも関わらず、売る事が出来るのでしょうか?
　それとも、この部分は、「仕入と売上のみの場合は仕入-売上」の間違いなのでしょうか?

補足

いくつか記載が間違っていたようで申し訳ありません。
一つずつ補足させていただきます。

残高の表示はG列に表示させようと思っています。
「りんご」の残高の表示するところを最新の日付に入力するつもりでしたが
誤って一番古い日付に入力してしまいましたので
「りんご」の残高は11/2の残高欄に15000となります。

残高の計算方法ですがその都度の残高を出したいのではなく
月末等にその月の最終的な残高を出したいので
最後の在庫の行に最終的な残高を入力できたらと思っています。
今気づいてしまったのですが（汗）残高の数量も出したいです。
なのでG列またはH列にそれぞれの残高と在庫の数量が出せればうれしいです。
最終的に在庫（または仕入）と売上が相殺された分の行は削除したいので
できたら0円表示などができればフィルターで削除するのが便利かと思っています。

単価に関しては無視してくださって結構です。
あいまいな表を作ってしまって申し訳ありません。

「みかん」に関しては単純に残高の計算ミスをしているので
仕入行に1500円が正しいです。

最後の在庫がない場合は～、という項目の部分は
単純に記載ミスになります。
「仕入と売上のみの場合は仕入-売上」になります。

よろしくお願いいたします。

また何か不備がございましたら回答いただければ補足させていただきます。
どうぞよろしくお願いします。

回答No.1

KURUMITO

お示しのデータが2行目から下方にあるして、Ｊ2セルには次の式を入力して下方にオートフィルドラッグします。

=B2&C2&F2

お求めのＧ列ではＧ2セルに次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",IF(OR(F2="売上",COUNTIF(J$2:J2,J2)<COUNTIF(J:J,J2)),0,IF(AND(COUNTIF(J:J,B2&C2&"仕入")>0,F2="仕入"),SUMIF(J:J,B2&C2&"在庫",D:D)+SUMIF(J:J,B2&C2&"仕入",D:D)-SUMIF(J:J,B2&C2&"売上",D:D),IF(AND(COUNTIF(J:J,B2&C2&"仕入")=0,F2="在庫"),SUMIF(J:J,B2&C2&"在庫",D:D)-SUMIF(J:J,B2&C2&"売上",D:D),0))))