excel ifで自動出力させたい

Question

画像の目的は合計額を出すためのものです。

単価は固定です。
B列各行*C列各行がD列各行に入ってます。
数量は手入力です。

毎回、数量への手入力がしんどいので開始レベルと目標レベルを入力して数量を自動で出力させるようにしたいのです。

右の開始レベルは最小１からで
目標レベルは最大３０まで入力します。

画像のものだと8～20なので画像の数量の通りになります。
この、各数量を自動で出力させるようにしたいです。

今まで数量を手入力していたのを
レベルの２か所を入力するだけで数量を自動算出させようというものです。

０１～１０の行の数量は下記のifを入れています。

=IF(G1<10,10-G1,0)

開始レベルが４なら数量は6になります。

１１～１４以降のifが思いつかないのですが
分かる方いましたら教えていただけますと助かります。

Comments

[asahina_dev]

意図不明、もっと詳細を書くべき、　あとPHPタグは不要ですよねこれ

[MG1986]

毎回、数量への手入力がしんどいので開始レベルと目標レベルを入力して数量を自動で出力させるようにしたいのです。

と何がしたいのか記載してあったのですが訂正しました。

[asahina_dev]

とりあえずいまはいっている計算式をだすのじゃ

[asahina_dev]

て１１～がか・・・　ルールがわからない以上誰にも回答ができないと思いますよ
横：「開始レベル　目標レベル」
縦：「TL（個数）」

のマトリクス表を作成してだせばルールはおのずと出てきます。
つくって LOOKUP関数 をつかったほうがいいきがするが

Answer 1

数量の計算について

例えば画像のように８～２０の場合、
「１～１０」は８・９・１０の３つ、「１１～１４」では１１・１２・１３・１４の４つ、、という値が数量に入るのではないかと推測しましたが、どうでしょう？

「１～１０」が２という時点で違っているのですが、これは単純に引き算のミスと推測しました。
（もしくは開始レベルの値は含まない？）
・・・はずしていたらすみません。。

以下、上記を前提としてアドバイスを進めます。

１～１０に入れた式ですが、前述のとおり８・９・１０で３個という値を求めたいのであれば
10－G1+1としなければなりません。

また、開始レベルに対する判定はできていますが、目標値に対する判定が足りないようです。
目標値が１０未満の時にも１０からの減算になってしまうと思います。

やるべきこと

こんがらがってしまうような条件も、ひとつずつ整理すると作るべきものも明らかになってきます。

①各行の最小値より目標レベルが小さい場合は０
②各行の最大値より開始レベルが大きい場合は０
③各行の最大値と目標レベルを比較して、小さい値をＡとする
④各行の最小値と開始レベルを比較して、大きい値をＢとする
⑤Ａ－Ｂが各行の数量となる

また、各行の最小・最大をH,I列に出しておくと、以下のように式を共通化することができます。
'H1:「１～１０」の最小値(1)
'I1:「１～１０」の最大値(10)
'G$1:開始レベル
'G$2:目標レベル

=IF(G$2<H1,0,IF(G$1>I1,0,IF(I1<G$2,I1,G$2)-IF(H1>G$1,H1,G$1)+1))

---

Ｈ列 I列
1 10
11 14
15 15
16 18
19 20

---

Answer 2

数式から推測すると、「１～１０」の行の数量(C2セル)には、既に"開始レベル"個（８個）の品物がある時に、いくつ足せば１０に達するかを書く、ということかと思われます。

これが正しいとすると
A1セルは、TLではなく「個数の範囲」、F1セルは開始レベルではなく「最初から有った品物の個数」、F2セルは目標レベルではなく「品物の総数（この個数に達するところまで計算する）」というように意味が伝わる項目名になっているべきです。そういう項目名が書けないのは、作業内容の理解が不足しているからだと思います。

３行目以降の（「１１～１４」以降）の数値を計算する式ですが、質問に書かれたデータでは行ごとに個別に書くしかありませんから、すこし修正を加えます。
どのようにするかというと、A列は個数の上限の数値だけを書くようにします。具体的には、A2からA6を以下のような数値にします。A7からA16のセルも数値にしてください（A7セルは、質問の表では"２１"という文字列になっていますが、それを 21 という数値にするということです)
そして、３行目は、A3が１４、A2が10なので、「１０個より大きくて、１４個以下」についての行と理解してください。（１１～１４という意味は、変わっていません）

A2 : 10
A3 : 14
A4 : 15
A5 : 18
A6 : 20

この修正が終わったら、C3セルに以下の式を入力してください。

=IF(($G$1+SUM($C$2:$C3) < $G$2), IF($G$2>=$A3, $A3-$A2,$G$2-$A2) , 0)

この式の意味：
最初のIFは、「最初から有った品物の個数」と、上側の行にある個数の合計を求めて、それが「品物の総数」を超えていなければ個数を計算（式の中のIF文）し、超えていれば０にする。
式の中のIFは、「品物の総数」がこの行の個数の上限（A3セル：値は14）を超えていれば前の行の上限(A2セル：値は10)との差(14-10=4)、超えていなければ個数の上限と前の行の上限との差を求めています。

あとは、C3セルをコピーして、C4～C16セルにコピーすればOKです。
式の中に＄を多く使っているのは、コピーした時に自動的に変化する部分（$がついていない）と変化させない部分($がついている）があるからです。
このような式の書き方を覚えると、式を１回書いて、残りはコピーで済むので作業が楽になりますよ。

ちなみに、C3セルをコピーして、C4セルにコピーすると、C4セルには次のような式が入ります。
C3セルの式と比べて、$がついていない行の番号が１つ増えている事に気づくかと思います。

=IF(($G$1+SUM($C$2:$C4) < $G$2), IF($G$2>=$A4, $A4-$A3,$G$2-$A3) , 0)

試してみてください。

Comments

[asahina_dev]

C4にその式がはいると循環参照エラーになるはずですが

[coco_bauer]

おっしゃる通りですね。　C3セルに入れる正しい式は、”=IF(($G$1+SUM($C$2:$C２) < $G$2), IF($G$2>=$A3, $A3-$A2,$G$2-$A2) , 0)”　です。　訂正いたします。