[[20110221130036]]　『プラスマイナスの計算式』（むすたんぐ）　ページの最後に飛ぶ

『プラスマイナスの計算式』（むすたんぐ）

 皆様 こんにちは 下記の件にてお世話になります。

 環境：Excel2007　Windows 7
 ★なのですが、Excel2000 で対処可の手段でお願い致します。

 　A            B       C       D       E
 1 1品名  	数量	品名 	>0	<0	
 2 りんご	2				
 3 りんご	-1				
 4 りんご	-1				
 5 りんご	3	りんご	3	0	
 6 みかん	2				
 7 みかん	1				
 8 みかん	-1				
 9 みかん	1				
10 みかん	-2				
11 みかん	2				
12 みかん	1				
13 みかん	1				
14 みかん	3				
15 みかん	2	みかん	2	0	
16 メロン	1				
17 メロン	-1				
18 メロン	1				
19 メロン	2	メロン	2	0

 C2:=IF(A2<>A3,A2,"")
 D2:=IF($C2="","",SUM(D$1,SUMIF(B2,D$1)))
 E2:=IF($C2="","",SUM(E$1,SUMIF(B2,E$1))) それぞれ：下方へコピー

 E列の負の計算が出来ません。。。
 どこがいけないのでしょうか。
 ダメな原因も教えていただけないでしょうか。。。

 因みに Ｃ列・Ｄ列の数式は、自分が考慮した内容ではございません。。。
 ですので どうして SUM → SUMIF の順（？）なのかも ご説明していただきますと
 大変にありがたいです。（力不足で済みません。理解をしたい。深めたいです。）

 質問していて申し訳ございませんが ひょっと致しましたら お返事が遅れるかもしれ
 ません。（お返事があった場合なのですが・・・ 汗）
 お返事がありましたら、遅れても必ず お返事は入れさせていただきます。

 作業列を使用して … なくべく簡単にしたい！という願望だけはありますが・・・。
 応用力・基礎力がなく… こんな私ですが、どうぞ宜しくお願い致します。

 D列、E列は何を導き出そうとしてらっしゃるのかよくわかりません。
 「りんご」の場合、D5の値は「りんご」の最後の値が入っていますよね。
 であればSUMをする必要は全くないですし、またSUM文の中に文字列(E$1など)を
 入れているのも意味不明です。。

  A列・B列の値を基にD列・E列には何が入れば正解なのかその辺の詳細をご説明いただけませんか？

 (Yujin)

 Yujin さん　御免なさい。大変失礼致しました。

 言い訳・独り言（質問しようとしたら、急に仕事が飛んできた。。。）
 誤って送付してしまいました。済みません。反省致します。

 訂正後の条件は、下記です。
 今後は、よく確認して送付しましたが、不備があれば　お手数をお掛け致しますが
 ご指摘をお願い致します。

 	A	B	C	D	E	F
1	品名  	数量	品名 	>0	<0	
2	りんご	2				
3	りんご	-1				
4	りんご	-1				
5	りんご	3	りんご	5	-2
6	みかん	2				
7	みかん	1				
8	みかん	-1				
9	みかん	1				
10	みかん	-2				
11	みかん	2				
12	みかん	1				
13	みかん	1				
14	みかん	3				
15	みかん	2	みかん	13	-3
16	メロン	1				
17	メロン	-1				
18	メロン	1				
19	メロン	2	メロン	4	-1

 1 B列の数量を手入力 → その入力した数値 → プラスかマイナスを判断
 2 プラス→プラスの列　マイナス →マイナスの列
 3 商品群別に 小計の数値を算出

 再度 見直しましたら、プラスの数値もダメです。。。

  （むすたんぐ）

 2007より前の場合

 D1 =IF($C2="","",SUMPRODUCT(($A$2:$A$19=$C2)*($B$2:$B$19>0)*$B$2:$B$19))

 E1 =IF($C2="","",SUMPRODUCT(($A$2:$A$19=$C2)*($B$2:$B$19<0)*$B$2:$B$19))

 それぞれを下方向へコピー★

 2007以降の場合

 D1 =IF($C2="","",SUMIFS($B$2:$B$19,$A$2:$A$19,$C2,$B$2:$B$19,D$1))

 を縦横へコピー★

 等でいかがでしょう♪

 (MJ12)

 C2セルの式はそのままで
 D2セルに
=IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 で、フィルドラッグでも良いかもですね。

 (HANA)

 HANAさんなるほど★

 これなら簡単でどちらにも使えますね☆彡

 まーだ頭かたいっス(^^ゞ

 (MJ12)

 MJ12 さん お返事ありがとうございます。

 >D1 =IF($C2="","",SUMPRODUCT(($A$2:$A$19=$C2)*($B$2:$B$19>0)*$B$2:$B$19))
 >E1 =IF($C2="","",SUMPRODUCT(($A$2:$A$19=$C2)*($B$2:$B$19<0)*$B$2:$B$19))

 共に 期待値が返りました。ありがとうございます。
 ただ。。。私のように「論理式等を理解していないと」応用が困難かもしれません。。。（ゞ＾＾
 TRUE × TRUE の掛け算した 和だったかしら。。記憶に自信ありません。（苦笑

 > D1 =IF($C2="","",SUMIFS($B$2:$B$19,$A$2:$A$19,$C2,$B$2:$B$19,D$1))
 2007を持っていながら 仕事では2000なので（笑） SUMIFSは、未使用でした。（悲）
 本日初めての使用です。機会を与えていただき感謝致します。
 この関数便利そうですね。一度の使用ですけど。

 色々な数式のご提案をありがとうございました。
 お気を悪くなさらないでください。
 自分のようにスキルが低いと（苦笑） いかにして 自分自身が理解出来て
 そして 応用が出来て… 今後の数式作りに… が課題です。
 これがずーっと 課題で終わりそうなんです。（爆）
 また、見かけて　気が向きましたら 宜しくお願い致します。（むすたんぐ）

 HANA さん お返事ありがとうございます。

 =IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 期待値が返りありがとうございます。

 折角ご提案をいただいておりますが…。理解不能箇所があります。（汗;;;

 SUMIF 関数の引数　検索範囲,検索条件,合計範囲かと思いますが
 この ３つの引数通り（？）の入力ではないのでしょうか。。。【疑問 1】です。

 SUM 関数　文字を無視して数値を合計する関数に認識してますが、
 間違いでしょうか。でも 引数に文字を使用してますよね。。。？？？【疑問2】です。

 一応：私のお気に入りのサイト「よねさんの」のサイトで確認しましたが
 私の理解間違いでしょうか。
 上手く不明な箇所の説明になっているか自身はありませんが、もし分かったら
 教えていただけないでしょうか。いつも本当に済みません。。。
　
（むすたんぐ）

 HANA さん

 	A	B	C	D	E	F	G	H
1	品名  	数量	品名 	>0	<0			
2	りんご	2					2	2
3	りんご	-1					0	0
4	りんご	-1					0	0
5	りんご	3	りんご	0	0		3	3
6	みかん	2					2	2
7	みかん	1					1	1
8	みかん	-1					0	0
9	みかん	1					1	1
10	みかん	-2					0	0
11	みかん	2					2	2
12	みかん	1					1	1
13	みかん	1					1	1
14	みかん	3					3	3
15	みかん	2	みかん	0	0		2	2
16	メロン	1					1	1
17	メロン	-1					0	0
18	メロン	1					1	1
19	メロン	2	メロン	0	0		2	2

 G2:=SUMIF(C3,">0")
 H2:=SUMIF(C3,">0")-SUM(E$1)それぞれ 下方へコピー

 ↑これで良いのか全く分かりません。
 絶対駄目です。両方の値が同じなのですから。。。

 ただ、IFを省いて考慮すると「このようにしか」考慮
 出来ませんでした。
 顔面が「真っ赤」になっています。(苦笑）

 嘘を付くのが苦手ですので 根性をきめて
 記載致しました。笑ってください。。。（むすたんぐ）

 「考慮」をしないでください。その「考慮」が間違っています。
 教わった式をそのまま入力し、下方へコピーしてみてください。
 (r)

 ＞【疑問 1】
 ＞SUMIF 関数の引数　検索範囲,検索条件,合計範囲かと思いますが
 ＞この ３つの引数通り（？）の入力ではないのでしょうか。。。

 SUMIF関数をヘルプで確認してみてください。

 SUMIF(範囲, 条件, [合計範囲])
                   ~~~~~~~~~~最後の所は [ ]の中に書いてあります。
 その下に
 　・範囲　　　必須です。
 　・検索条件　必須です。
 　・合計範囲　オプションです。
 と書いてあると思います。

 3つ目の引数は、検索範囲と合計範囲が違う時に指定すればその範囲が合計されます。
 たとえば　↓の様な表で、
      [A]          [B]
 [1]  名前　　　   値
 [2]　HANA          1
 [3]  むすたんぐ   10
 [4]  HANA　　　　  3
 範囲－A2:A4　　の中から　　条件－HANA　　と一致する　合計範囲－B2:B4 の合計を求めたい。
 こんな時は、三つ使いますね。=SUMIF(A2:A4,"HANA",B2:B4)

 そして、2007のヘルプでは　先頭の方に
 　　たとえば、数値を含む列で、5 より大きい値のみを合計するとします。
 　　次の数式を使用できます。
 　　=SUMIF(B2:B25,">5")
 と、具体例が挙げてあります。

 この式では（↑書いてありますが）
 B2:B5の中で、5より大きい値だけを合計します。

 上の例だと、1+3 = 4 に成りますね。

 ＞【疑問2】
 ＞SUM 関数　文字を無視して数値を合計する関数に認識してますが、
 ＞間違いでしょうか。
 間違っていません。今回はこの特性をあえて利用しています。

 ＞でも 引数に文字を使用してますよね。。。？？？
 これは、『SUM(D$1:D1)』の所の D1は文字なんだけど。。。　という疑問ですよね？

 この結果は、文字を無視して合計するので　0 です。
 一つ下にフィルドラッグすると SUM(D$1:D2) になりますが
 もしも D2に「10」が入っていたら、0+10 = 10 ですね。
 もしも D2が「""」だったら、これは文字なので無視されて 0+0 = 0 です。

 たとえば、D1とD2を足し算するのだから　D1+D2 とやってしまうと
 D1セルは文字列で、文字は足し算出来ないので #VALUE! エラーが出ます。

 問題の数式に戻ってみます。

 ＞=IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 IFから出すと   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~この部分が残ります。 
 C2が"" で無い時に　この部分が計算され　結果が表示されます。

 SUMIF($B$2:$B2,D$1)-SUM(D$1:D1)を詳細にみてみると
 ~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~ それぞれの結果を引き算しています。

 たとえば、D2セルでこの計算が行われた場合

 先に、SUMIF関数の方にだけ注目すると。。

 SUMIF($B$2:$B2,D$1)　は、B2:B2の中で「>0」の数の合計　が求められます。

 D5セルでこの計算が行われた場合
 SUMIF($B$2:$B5,D$1)　は、B2:B5の中で「>0」の数の合計　が求められる事になりますね。
 　今回の例では　2+3 ＝ 5 です。

 D15セルでこの計算が行われた場合 
 SUMIF($B$2:$B15,D$1)　は、B2:B15の中で「>0」の数の合計　が求められる事になりますので
 　今回の例では　2+3+2+1+1+2+1+1+3+2 ＝ 18 です。

 本当は D15セルは「13」になってもらいたいです。

 なぜ 13ではなく、18になるのかを考えると、りんごの「5」も一緒に合計されているからです。
 SUMIF関数で得られた 18 から　りんごの合計 5 を引くと、希望する結果の「13」が得られます。

 D19セルの　メロン　でも考えてみます。
 SUMIF関数の結果は　22 です。本当は「4」になってもらいたいです。
 この差分がどこから出てくるのかというと、　りんごの5 + みかんの13 = 18 ですね。
 22 から、この合計である 18 を引くと、希望する結果の「4」が得られます。

 SUMIF関数の結果はF2セルに =SUMIF($B$2:$B2,D$1) の式を入れて
 下にフィルドラッグして確認してください。

 SUMIF関数で累計は出ますが　いくつ引けば良いのかが問題です。
 りんごが5だったから　D15では5を引かないといけない　とか
 りんごとみかんを足すと18だから　D19では18を引かないといけない。。。

 でも、希望結果図をよく見てください。
 りんごの「5」はD5セルにあります。
 りんごとみかんを足した「18」は　D5+D15 の結果ですね。

 G2セルに =SUM(D$1:D1) を入れて下にフィルドラッグしてみてください。
 　※D列には希望結果の値を入力しておいてください。

 すると、G5セルは「0」
 　　　　G15セルは「5」
 　　　　G19セルは「18」
 と成ります。（ほかのセルにも値が入っていますが。）

 SUMIF($B$2:$B2,D$1)-SUM(D$1:D1)　の式は SUMIF-SUMの形なので
 H2セルに　=F2-G2 を入れてフィルドラッグしてみます。

 すると、H5,H15,H19 セルの値は、D5,D15,D19セルの値（希望する結果）と
 一致します。

 後は、IF関数に入れて　いらない所に値が入らない様にします。
 I2セルに =IF(C2="","",H2) をフィルドラッグ。

 今回F:I列の4列 + D列　を使用しましたが
 それを一つにまとめたのが
=IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 の式になります。

 再度書きますが、IF関数から結局値を返している部分をとり出すと
 　SUMIF($B$2:$B2,D$1)-SUM(D$1:D1)
 という式になります。
 D1セルには >0 が入っているので
 　SUMIF($B$2:$B2,">0")-SUM(D$1:D1)
 と一緒です。

 ＞H2:=SUMIF(C3,">0")-SUM(E$1)
 ではなく、上記の様になる事は、納得出来そうですか？

 (HANA)

 rさん お返事ありがとございます。

 >その「考慮」が間違っています 
 はい！その事は招致しております。済みません。

 >教わった式をそのまま入力し、下方へコピーしてみてください

 了解致しました。ただ、（汗　↓

 =IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 期待値が返りありがとうございます。とお礼を記載した「つもり」でした。
 至らないのは招致しておりしておすます。不快な思いをさせてしまい、済みません。
 全て自分が悪いというのも理解しています。ありがとうございました。

 HANA さん お返事が大変に遅くなり申し訳ございません。。。

 先に私の気持ちを記載させてください。自己中心的かもしれません。。。が…。

 HANA さんからご提案いただいた数式を入力して直ぐに期待値が返りました。
 もうこれで「期待値」が得られたからOKとは思えず（少しでも理解したいので。。。）

 自分なりに（ヘルプなり、サイトなりを確認しました。）でも、全然確認がアマイのです。
 そこが最大の問題点なのです。

 普通ならばご提案いただいた数式を分析してどうして　こういう数式を使うんだろう？と
 考慮すべきだと思うのですが現状残念ながらそのスキルがありません。（大汗;；；

 ただ、数式を提案していただき、表現は悪いのですが、これじゃ～分かりません。と他力本願
 という認識はない…「つもり」です。（汗　でも現状「他力本願」ですね。済みません。。。

 ＞【疑問 1】

 >  ・範囲　　　必須です。
 >　・検索条件　必須です。
 >　・合計範囲　オプションです。

 ヘルプを確認致しました。合計範囲：オプションなのですね、知りませんでした。

 > こんな時は、三つ使いますね。=SUMIF(A2:A4,"HANA",B2:B4)

 このオーソドック（？）の入力しか経験がありませんでした。ここは違和感なく理解出来ます。

 >たとえば、数値を含む列で、5 より大きい値のみを合計するとします。
 >　　次の数式を使用できます。
 >　　=SUMIF(B2:B25,">5")

 実際にやってみました。ここも違和感なく理解出来ます。

 >＞【疑問2

 これは、『SUM(D$1:D1)』の所の D1は文字なんだけど。。。　という疑問ですよね？

 そうなんです。まさしくその通りなのです。

 >もしも D2に「10」が入っていたら、0+10 = 10 ですね。
 >もしも D2が「""」だったら、これは文字なので無視されて 0+0 = 0 です

 ご説明を受けてみて 初めて理解出来ます。（本当に済みません。。。）

 > D1セルは文字列で、文字は足し算出来ないので #VALUE! エラーが出ます。

 これも実際に行ってみました。確かに#VALUE! が表記されました。

 > 先に、SUMIF関数の方にだけ注目すると。。

 >SUMIF関数で得られた 18 から　りんごの合計 5 を引くと、希望する結果の「13」が得られます

 ご説明を受け理解出来ました。(メロンの箇所も説明を受け理解出来ました。）

 >F2：=SUMIF($B$2:$B2,D$1)
 >G2:=SUM(D$1:D1)
 >H2:=F2-G2
 >I2:=IF(C2="","",H2)

 >それを一つにまとめたのが
 >=IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))

 ご説明を受けますと理解出来ますが　自分ではこのように考慮出来ませんでした。

 >＞H2:=SUMIF(C3,">0")-SUM(E$1)
 >ではなく、上記の様になる事は、納得出来そうですか

 残念ながら理解出来ません。。。。というのは、↑の式ですと ゼロ 0 が返りますよね･･･。。。
 絶対に私の理解不足と思います。

 再度読み直します。数式掲示していただいて、且つこんなに丁寧にご説明までしていただいているのに
 本当に申し訳ございません。（むすたんぐ）

 少しお伺いしてみます。

 私が提示した式は
 ＞D2=IF(C2="","",SUMIF($B$2:$B2,D$1)-SUM(D$1:D1))
 ですが、IF関数をのけると
 　""　か SUMIF($B$2:$B2,D$1)-SUM(D$1:D1) の
 　どちらかになる
 と言うのは問題有りませんか？

 D1セルには「">0"」が入っているので
 　　SUMIF($B$2:$B2,">0")-SUM(D$1:D1)
 ですね。

 すると、作業列に入れてみるべきは
 ＞＞H2:=SUMIF(C3,">0")-SUM(E$1)
 ではなく
  　　　=SUMIF($B$2:$B2,">0")-SUM(D$1:D1)
 と言う事になりませんか？

 もしかしたら、配列の数式を確認する場合と混同なさっておられるかもしれません。

 簡単な例を想定すると
 =IF(C2="","",SUM(D$1:D1))
 という式が有った時に、これをIF関数から分けて計算結果を確認したい時は
 （もし、C列がどんな状態でも　SUMの結果がどうなっているのか知りたいと思ったら）
 =SUM(D$1:D1)　をフィルドラッグして確認しますね？

 =SUM(D$1)　をフィルドラッグして確認・・・
 なんてしようと思わないのではないでしょうか。

 だって、=SUM(D$1) ではどのセルの合計を出したいのでしょう？

 それに=IF(C2="","",SUM(D$1:D1))の式を入れてフィルドラッグして
 C2の範囲のすべてに何か入力をした時の結果とは　一致しませんね？

 たとえば4行分フィルドラッグしたときは
 =SUM(D$1)では、=SUM(D$1)
 =IF(C2="","",SUM(D$1:D1))は、=IF(C6="","",SUM(D$1:D5))
 で、C6には何か入力があるので SUM(D$1:D5) の結果が表示されている事に成ります。

 =SUM(D$1:D1)　をフィルドラッグするから
 IF関数の=IF(C2="","",SUM(D$1:D1))
 　　　　　 この部分／~~~~~~~~~~~の戻り値が
 どの様に変化するかを確認する事が出来る様になりますね？

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20110221130036]] 『プラス マイナスの計算式』（むすたんぐ） ページの最後に飛ぶ

[[20110221130036]]　『プラスマイナスの計算式』（むすたんぐ）　ページの最後に飛ぶ