中小企業の働き方改革をサポート>>

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


下の画像のように

スタート 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(限界値)を関数で表したいのです。

宜しくお願い致します。

「Excel関数での求め方」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (3件)

 御質問の例の場合、



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)))
    • good
    • 0
この回答へのお礼

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

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

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

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

 回答No.1です。



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

「計算値の累計」=((「ゴール」-「スタート」)×n-「間隔」×n×(n+1)/2)×「固定値」
=((「ゴール」-「スタート」)-「間隔」×(n+1)/2)×n×「固定値」

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

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

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

こんばんは!



(100-90.1)*1000=990 → 9900
(100-90.2)*1000=980 → 9800



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

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

↓の画像のように質問通りの作業用の列を作成します。
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
「Excel関数での求め方」の回答画像2
    • good
    • 0
この回答へのお礼

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

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング