Ｅｘｃｅｌ関数での求め方

いろいろ試行錯誤してみましたが、私の知っている関数達では
表しきれないため、皆様のお知恵をお貸し頂きたく思います。


下の画像のように

スタート　90
ゴール　　100
間隔　　　0.1　という条件で

(100-90.1)*1000=990
(100-90.2)*1000=980
(100-90.3)*1000=970
(100-90.4)*1000=960
(100-90.5)*1000=950
・
・
・
(100-n)*1000=X
※1000は固定

というふうに続いていくとすると、
990＋980＋970＋960＋950・・・＋Xの合計が
上限の30000を超えない最大のn(限界値)を関数で表したいのです。

宜しくお願い致します。

No.1 ベストアンサー 回答者： kagakusuki 回答日時： 2013/12/21 19:02

　御質問の例の場合、

n=1　　(100－90.1)×1000＝9900　　計9900
n=2　　(100－90.2)×1000＝9800　　計19700
n=3　　(100－90.3)×1000＝9700　　計29400
n=4　　(100－90.4)×1000＝9600　　計39000

になりますから、答えが3になれば宜しいのですね？

　(「ゴール」-「スタート」－「間隔」×n)×「固定値」＝「計算値」

という関係なのですから、n回目までの「計算値」の累計は

「計算値の累計」＝(「ゴール」-「スタート」)×n－「間隔」×n×(n＋1)／2

になります。
　これをnに関する2次方程式として解を求めますと、

n＝(「ゴール」-「スタート」)／「間隔」－1／2±√(((「ゴール」-「スタート」)／「間隔」－1／2)^2－「計算値の累計」×2／(「固定値」×「間隔」))

になります。
　ですから、今仮に、スタートの値である90をB1セルに、ゴールの値である100をB2セルに、間隔の値である0.1をB3セルに、固定の値である1000をB4セルに、上限の値である30000をB5セルに、それぞれ入力するものとしますと、限界値は次の様な関数で求める事が出来ます。

=INT(($B$2-$B$1)/$B$3-0.5-SQRT((($B$2-$B$1)/$B$3-0.5)^2-2*$B$5/($B$4*$B$3)))

この回答へのお礼

ご教示頂いたコードでできました！

「計算値の累計」＝((「ゴール」-「スタート」)×n－「間隔」×n×(n＋1)／2)×「固定値」
この部分の考え方が私には難しくて発想できませんでしたが、
これから取り組んで理解に努めたいと思います。

しかし、一発でこういう閃きができるのが凄いと感服してしまいました。
この度は誠にありがとうございました。

お礼日時：2013/12/23 15:34

No.2 回答者： tom04 回答日時： 2013/12/21 19:47

こんばんは！

(100-90.1)*1000=990　→　9900
(100-90.2)*1000=980　→　9800
・
・
・
となりますよね？
(100-90.1)*100
(100-90.2)*100
であれば質問のようになりますので、その辺は実際の数式を入れてみてください。

タイトルが
＞Ｅｘｃｅｌ関数での求め方
となっていますので、
要は頭を動かすか？それとも手を動かすか？
のどちらかになると思います。
手を動かす方法の一例です。

↓の画像のように質問通りの作業用の列を作成します。
D1セルに「1」を入力　→　フィル（Excel2007以降であれば画面左上のオートサム（Σ）のアイコンの下にある下向き↓のアイコン）をクリック　→　連続データの作成　→　列を選択　→　停止値に仮に1000と入力しOK
これでD列がに1～1000までの連番が表示されます。
E1セルに
=(B$2-(B$1+B$3*D1))*100
F1セルに
=SUM(E$1:E1)
という数式を入れE1・F1セルを範囲指定　→　F1セルのフィルハンドルでダブルクリック！
これで作業用の列は完成です。

後はB7セルに
=INDEX(D1:D1000,MATCH(MAX(IF(F1:F1000<=B5,F1:F1000)),F:F,0))
これは配列数式になりますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は、
上記数式をドラッグ＆コピー　→　B7セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！

これで画像のような感じになります。
作業列が目障りであれば非表示にしておきます。

※　E1セルの数式が質問通りの1000であれば
=(B$2-(B$1+B$3*D1))*1000
にしてみてください。m(_ _)m

この回答へのお礼

ありがとうございました。
とても丁寧な解説のうえ画像までアップして頂き、
ベストアンサーにしたいところですが
この度はNo.1の方の１発式の方とさせて頂きました。
また宜しくお願いいたします。

お礼日時：2013/12/23 15:46

No.3 回答者： kagakusuki 回答日時： 2013/12/21 21:59

　回答No.1です。

　済みません、御質問内容とは直接関わる箇所では御座いませんが、「計算値の累計」を求める式に一部抜けが御座いました。
　正しい式は、次の様なものとなります。

「計算値の累計」＝((「ゴール」-「スタート」)×n－「間隔」×n×(n＋1)／2)×「固定値」
＝((「ゴール」-「スタート」)－「間隔」×(n＋1)／2)×n×「固定値」

　尚、上記のミスは、あくまで回答文を入力する際の入力ミスによるものに過ぎず、nに関する2次方程式の解や限界値を求める関数等は、回答No.1で述べた通りのもので間違い御座いません。

この回答へのお礼

ありがとうございました。

お礼日時：2013/12/23 15:48