特定の条件で計算式を組みたいのですが、教えて頂けますでしょうか？

以下の条件を計算式で打ち出す事は可能でしょうか？
50を入力したときに別のセルに1.50が出る
100を入力したときに別のセルに1.50が出る（100以下の数値入力時は全て1.50）
200を入力したときに別のセルに2.30が出る（₊0.8）
300を入力したときに別のセルに3.00が出る（₊0.7）
400を入力したときに別のセルに3.60が出る（₊0.6）
500を入力したときに別のセルに4.10が出る（₊0.5）
600を入力したときに別のセルに4.50が出る（₊0.4）
700を入力したときに別のセルに4.80が出る（₊0.3）
800を入力したときに別のセルに5.0が出る（₊0.2）
900を入力したときに別のセルに5.1が出る（₊0.1）
1000を入力したときに別のセルに5.1が出る（900以上の数値入力時は全て5.1）

上記以外の数字を入力した場合は、ちょうどその間の数字が出る
例：150という値を入力したときには別のセルに1.90が出る
例：220という値を入力したときには別のセルに2.44が出る

という様なものです。

非常に分かりづらく拙い説明で恐縮ですが、ご教授頂けますと幸いです。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2022/10/28 21:57

こんばんは

100以下と、900以上は一定値で、100～900の間は補間して値を求めたいということと解釈しました。

ご提示の値の変化率が1次関数なので、2次曲線の解を求めて補間すれば簡単な数式にできるので簡単です。
ただし、曲線補間になるので、ご提示の直線補間とは少し異なる値になってしまいます。
（100、200、300・・の場合の値は、ぴったりと一致しますけれど・・）

こちらの方法の方が、グラフ的にはもっともらしい考え方だとは思いますけれど・・
（こちらであればなめらかな曲線グラフ。ご提示の方法だと折れ線グラフ）
ちなみに、その場合は
　150の場合の値は 1.9125
　220の場合の値は 2.448
となります。
二次式の解は、
　Y=-5E-6*X*X+95E-4*X+0.6
といった感じです。


どうしても折れ線グラフ的に補間を行いたければ、No1様の回答のような表を作成しておいて、どの値の間に入るかを検索して直線補間をするしかなさそうですね。

この回答へのお礼

ご回答有難う御座います。
99以下の数値も1.5にするにはどうしたら良いでしょうか。

お礼日時：2022/10/29 10:24

No.6 回答者： ママチャリ 回答日時： 2022/10/29 09:19

No.1さんの考え方が簡単だと思います。

その応用です。

下記のような表を作ります。
A列を完全一致でVLOOKUP。あったらB列を取得。
見つからない（エラー）の場合は、再度、近似値でVLOOKUPし、C列を取得。

A　　B　　C
0　　 1.5　1.5
100　1.5　1.9
200　2.3　2.65
300　3　　3.3
400　3.6　3.85
500　4.1　4.3
　　　・
　　　・
　　　・

この回答へのお礼

ご回答有難う御座いました。

お礼日時：2022/10/29 13:12

No.5 回答者： goomania 回答日時： 2022/10/29 08:11

様々な解決方法があると思いますが、出来るだけシンプルになるよう考えてみました。

作業用のセルを使用してもよいという前提ですが、添付画像をご覧ください。
入力セルをＡ１セルとし、Ｃ１セルに入力値に対する出力値を表示するものとします。

B1セルに、
=ROUNDDOWN(A1,-2)
という数式を記述しています。
さらに、C1セルに
=IFS(A1<=100,1.5,A1>=900,5.1,TRUE,0.6+(1900-B1)*B1/200000+(0.9-B1/1000)*(A1-B1)/100)
という数式を記述しています。
これで、C1セルにご希望の結果が表示されると思います。
数式の意味を説明するととても長くなります。
なにを計算しようとしているのか解析してみてください。

なお、作業セルをしたくない場合は、C1セルに記述した数式のB1の部分全てをROUNDDOWN(A1,-2)に置換すればよいわけですが、数式が長くなり、判りずらいものになってしまいます。

この回答へのお礼

ご回答有難う御座います。
数式の通り入れてみたのですが出ませんでした。
何かが足らなかったのでしょうか。

お礼日時：2022/10/29 10:13

No.4 回答者： googoo900 回答日時： 2022/10/29 00:17

添付図参照です。

INDEX関数とMATCH関数とIF関数を使います。
E列に数値を入力すると、K列に答えがでます。

添付図では計算させる部分は3列作っていますが、必要に応じて調整ください。
A列は必要ありませんが、F列に表示される数字がA列のNo.に対応しています。(B～F列は必要です)
F～J列は作業列です。邪魔にならないところに移動してもよいです。


◆手順
１．添付図のような表を作成します。
２．F2セルに『=MATCH($E2,$B$2:$B$12,1)』を入力します。
３．G2セルに『=INDEX($B$2:$B$12,$F2)』を入力します。
４．H2セルに『=INDEX($B$2:$B$12,$F2+1)』を入力します。
５．I2セルに『=INDEX($C$2:$C$12,$F2)』を入力します。
６．J2セルに『=INDEX($C$2:$C$12,$F2+1)』を入力します。
７．K2セルに『=IF($E2<100,$C$2,IF($E2>900,$C$12,FORECAST($E2,$I2:$J2,$G2:$H2)))』を入力します。
８．F2～K2セルを下方向に必要なだけコピペします。


数式を組み合わせることで作業列を減らすことはできると思いますが、理解しやすいような式にしています。必要に応じて式の統合を行ってください。

この回答へのお礼

ご回答有難う御座いました。

お礼日時：2022/10/29 13:12

No.2 回答者： presidio 回答日時： 2022/10/28 20:43

#1さんのだと，120とかの補間ができませんね。

私は次のようにして，#1さんと同様にtableを使い，補間もできるようにしました。
=LOOKUP(C5,_table1)+(C5-@INDIRECT("tables!"&ADDRESS(LOOKUP(C5,_table2),_Tcolumn)))/(@INDIRECT("tables!"&ADDRESS(LOOKUP(C5,_table2)+1,_Tcolumn))-@INDIRECT("tables!"&ADDRESS(LOOKUP(C5,_table2),_Tcolumn)))*(@INDIRECT("tables!"&ADDRESS(LOOKUP(C5,_table2)+1,_Ccolumn))-LOOKUP(C5,_table1))
（全体像は省略します）。ちょっと敷居が高いのでif文だけでやるのが手っ取り早いでしょう。
先ず100以下を
＝if(A2<100,　1.5,　次のif文)
で処理し（A2が参照セルとして），「次のif文」に次の文を入れて200以下を処理します。
if(A2<200,　A2*0.008+0.7　,　次の次のif文)
同様にして「次の次のif文」で300以下を処理し，同様のことを繰り返します。かなり長い式となりますが，手っ取り早い方法でしょう。

この回答へのお礼

ご回答有難う御座いました。

お礼日時：2022/10/29 13:12

No.1 回答者： banzaiA 回答日時： 2022/10/28 19:55

まず別に段階表を作成しておく。

例えば
A1 B1
数値 判定
0 1.50
100 1.50
200 2.30
300 3.00
400 3.60
500 4.10
600 4.50
700 4.80
800 5.00
900 5.10
1000 5.10

などと。
数値を入力するセルD1 として
判定を出力するセルに
=VLOOKUP(D1,$A$2:$B$12,2,TRUE)
ではだめですかね。

この回答へのお礼

ご回答有難う御座いました。

お礼日時：2022/10/29 13:12