エクセルのIF関数について

質問

エクセル初心者です。毎月の支払出来高の表を作っています。A列に項目、B列に契約金額、C列に”済”と支払いが完了したら表示するように式が入ってます。D列以降は月々の累計支払いが入っています（例えば100万で契約して1月～12月で支払い）。D列　1月　E列　2月・・・・。
12月の次の列に残高計算する式が入っています。　
そこで質問なのですが、C列に,
IF(BR119=0,"済",IF(BR119<0,"NG","")),IF(D119<0,IF(BR119>D119,""))と入れたのですが、B列の金額の欄には値引き額があってマイナスの金額があるのです。契約金額を上回った時に”NG"と表示させたいのと、完了した時に”済”を表示させたいのですが、マイナスのところがうまくいきません。どうかアドバイスお願いします。

回答No.3

momomo1976

＃１です。

まだ、少しご説明の内容が腑に落ちてないのですが。
支払い残の式がちょっと変かな～。
IF(ISBLANK(E109),"",IF(E109<0,E109-(MIN(G109:BP109)),IF(E109>1,E109-(MAX(G109:BP109)))))
（１）E列（金額）が空白の場合
　　　空白
（２）E列（金額）が空白でなく、かつマイナス（値引）の場合
　　　金額－値引き金額の累計
（３）E列（金額）が空白でなく、かつ１より大きい場合
　　　金額－支払い金額の累計

２点、少し違和感がある部分がありますので
ここを変えられたらいいんじゃないかと思います。
（２）の部分ですが、値引き金額の累計はマイナスであるのに
　　　金額-(-マイナスの金額）でプラスになるから、おかしくなってしまうと。
　　　これは金額-(値引き額の絶対値）になりますね。
　　　単純に、金額+（値引き額の累計）としちゃっても良いと思いますが。後でなぜプラスにしたか分からなくなると困る場合は、絶対値に直してください。

　　　３００円の品を１００円引きで買ったら、
　　　３００－（１００）＝２００円と考えますよね。
　　　イコール、３００＋（－１００）＝２００です。
　　　現在は値引き額がマイナス値であらわされているので、
　　　金額の絶対値をマイナス、またはマイナス値をプラスしないと
　　　正確な金額は計算できません。

（３）E（金額）が１より大きい　の部分
　　　現実にはないと思いますが、金額が１の場合が抜けてしまっているので、E>=0とした方が良いと思います。

回答No.2

noname#79209

申し訳ないですが、状況がキチンと書かれてないので、解りにくいです。
なんとか解読を試みたものの、構成が全く解りません。
1.最長66ヶ月の支払期間があるのか？（残高計算がBR列なのでD～BQで66列）
2.B列に契約金額と書いているのに、値引き（マイナス）もB列だとも書いている。
　2行でワンセットか？
3.「D列以降は月々の累計支払い」と書いているが、月々の累計（積算）額でなく、
　「累計」は言葉の綾で、個々の月での支払額を、支払があっ都度手入力していくのか？
4.式自体の表記がおかしい。（#1さんの指摘）
5.式でD列を参照しているが、D列は第一回目の支払額ではないのか？
　それとも、値引きがあればB列でなくD列に入れるのか
　（値引きをB列に入れるという記述そのものがミスタイプ？）
等、ツッコミどころが満載です。

できるだけ、言葉の表現でなく、エクセルの表を模した形式で具体例を書いて下さい。

補足

説明不足ですみません。所々ミスタイプがありました。
1.残高がBR列なのは数量・金額（12月迄）、数量・金額（1月迄）・・・と2年ぐらいはあるので出来るだけ隅におきたかったのです。

2.　　　　契約内容　　　　　　　　　　　　　　　　　　　　12月迄　　　　1月迄・・・
　　項目　　単位　数量　　単価　　金額　　完了　数量　金額　　数量　金額・・・
　　雑誌　　　　　　100　　1000　　100000　
　　　・
　　　・
　　値引き　　　　　-1　　100000　　-100000
　　合　計　　　　　　　　　　　　　　　　20000000
3.月々の支払額ではなく累計を入れていきます。
4.作りかけの表を入力してしまいました。
5.列の表記が違ってました。
完了の列に"済""NG"を表示させる為にBR列には、IF(ISBLANK(E109),"",IF(E109<0,E109-(MIN(G109:BP109)),IF(E109>1,E109-(MAX(G109:BP109)))))が入ってます。
普通は完了の列は、IF(BR109=0,"済",IF(BR109<0,"NG",""))でいいのですが、契約金額（E列）が値引のマイナスの場合だと支払残が契約金よりだんだん大きくなっていくので、式がわからなかったのです。
よろしくお願いします。

回答No.1

momomo1976

IF関数を作る場合は、順番に考えていくといいですよ。
「場合分け」と一緒です。
今、作られている式は
（１）BR=0の場合は「済」を表示
　　　IF(BR119=0,"済",
（２）BR≠0で、BR<0の場合は「NG」を表示
IF(BR119<0,"NG"
（３）BR≠0で、BR≧0の場合は「""（空白）」を表示
　　　　　　　　　　　　　　,""))
ここまででIFの場合分けが終わってしまうので、
余っている部分「,IF(D119<0,IF(BR119>D119,""))」が出てきてしまい、式としてエラーになります。

BRは何が入っているのかな？イマイチ数値の入り方がよく分からないですけど
考え方としてはこんな感じでいいんじゃないでしょうか
=IF(残高=0,"済",IF(累計支払額>契約金額,"NG","")

補足

見てくださいましてありがとうございます。
言葉足らずですみません。
BRの列には支払い残が入ってます。IF(ISBLANK(E116),"",IF(E116<0,E116-(MIN(G116:BP116)),IF(E116>1,E116-(MAX(G116:BP116)))))　G列からBP列は月ごとの支払い累計が入ってます。
Ａ　　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　　Ｇ　　　Ｈ　　Ｉ　　　Ｊ　・・・　　　ＢＲ
項目　　単位　数量　単価　金額　完了　数量　金額　数量　金額・・・　　　支払残
ＧとＨは１２月迄　ＩとＪは１月迄・・・２年ぐらいの支払いなので余裕をもたせてＢＲ列になってます。
Ｆ列にはIF(BR103=0,"済",IF(BR103<0,"NG",""))を入れています。
値引きのマイナスがなければこの式で問題ないのですが、値引きも月々発生します。通常、月が進むにつれて支払残は減っていきますが、値引きの場合は支払い残が契約の値引金額よりマイナスが減っていくので、大きくなってしまいます。それでＦ列にうまく式がいれられなく悩んでいます。（項目が100行ぐらいあり、最後の行に値引、合計となってます。）契約の値引が-100万だとして12月に-30万の値引、1月に-50万の値引・・・9月に-100万の値引で支払残が0になります。
説明下手ですみませんが、よろしくお願いします。