[[20140902113228]]　『ある数値から等差級数を減算』（ＢＦ）　ページの最後に飛ぶ

『ある数値から等差級数を減算』（ＢＦ）

A1-(((B1+(B1+x))*((B1+x)-B1+1))/2)が0未満にならないようなxを関数を使って求めることは出来るでしょうか？

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 数学的には xからみれば２次不等式ですから、解は A1 と B1 の式で得られますが、
 （上記の式なら解なしのケースもあり）

 EXCEL の機能で解を求めたいのであれば、ソルバーかゴールシークになると思います。
http://office.microsoft.com/ja-jp/excel-help/HA010243164.aspx 現在参照不可
http://www4.synapse.ne.jp/yone/excel2010/excel2010_goalseek.html
（Mook） 2014/09/02(火) 14:01

回答ありがとうございます。

どう表現していいのかわからないので勘違いや間違いがあるかもしれません。
A1の値からB1の値を引き1以上であれば
上記の結果からさらにB1+1を引き1以上であれば
上記の結果からさらにB1+2を引き1以上であれば
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
と引けなくなるまで続け、引いた回数と余りを求めたいのですが、纏めると最初のようなA1から等差数列の和を引いたときの項数ということになると思うのですが、ゴールシークを使うには元の数式が入力されたセルが必要ですよね？
その数式がうまく作れないのですがどのような式であればゴールシークで可能でしょうか？

それともこれはマクロを使用しなければ不可能でしょうか？
（ＢＦ） 2014/09/02(火) 15:33

 A1、B1 は整数という制約があるのですか？
 マクロは結局処理をプログラムにすることなので、まずは EXCEL の標準機能での解決を
 考えたほうが良いと思います（汎用性のある処理では無いですし）。

 もしかしてこういうことでしょうか。

 A1 は初期値を記入
 C1=A1-B1*(B1+1)/2
 「データ」⇒「ソルバー」
    目的セル C1
    目標値 最小値
    変化させるセル B1
    制約条件  C1>0

 で求まった、B1以下の整数が所望の回答でしょうか。

（Mook） 2014/09/02(火) 16:05

済みません、書いておくべきでした。
全て整数であり、正の数となります。
ご提案の方法を試してみましたが求める結果は得られませんでした。
自分でマクロを扱えないので、応用の利かないマクロは考えていません。

数字を具体的にして例を書いてみます。

A1=2000
B1=170のとき
2000-170=1830
1830-171=1659
1659-172=1487
　　　・
　　　・
　　　・
255-180=75
75-181=-106
ここで負となるのでその直前11回目までの計算を行い、C1=11,D1=75となるような式を作りたいと思っています。
C1が出来ればD1は出来ると思ったので、質問はC1の式についてでした。

それと関数で可能であった場合、状況に応じてオートフィルで数百件コピーしようと思ってたのですがゴールシークやソルバーでは1件1件しかできないですよね？

（ＢＦ） 2014/09/02(火) 16:53

 初項が異なったのですね。であれば

 A1=2000
 B1=170
 D1=A1-(B1+C1)*(C1-B1+1)/2
 「データ」⇒「ソルバー」
    目的セル C1
    目標値 最大値
    変化させるセル C1
    制約条件  D1>0
 で180が得られます。

 このレベルになれば、マクロで一括処理もありだと思います。
 マクロの記録で
Sub Macro1()
    SolverOk SetCell:="$C$1", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$1"
    SolverSolve
End Subb
 というのが取れますので、セルのアドレスを変えながらループすればできそうな気がします。
http://msdn.microsoft.com/ja-jp/library/office/ff194414%28v=office.15%29.aspx
 のあたりが参考になりそうです。
 ちなみに、VBA ではVBE で参照設定で SOLVER にチェックが必要です。

（Mook） 2014/09/02(火) 17:41

 一応上記をマクロでやる例です。
 Sub Macro1() 
    SolverOk SetCell:="$C$1", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$1"
    SolverSolve UserFinish:=True 
    SolverFinish KeepFinal:=1 
 End Sub

（Mook） 2014/09/02(火) 17:58

 xの2次関数なので、根の公式を使って出来ないですかね？

 (1) C1セル =IF(COUNT(A1:B1)<2,"",IF(A1<B1,"解なし",INT((-2*B1+1+(4*B1^2-4*B1+1+8*A1)^0.5)/2)))
 (2) D1セル =A1-B1*C1-C1*(C1-1)/2

 <結果図>
  行  __A__  _B_  _C_  _D_
   1  2000   170   11   75

（半平太） 2014/09/02(火) 19:09

返事が遅くなり申し訳ないです。
お二人の方法で求めていた答えを出す事ができました。
わかりにくい質問に付き合って頂き、Mookさん、洋平太さん、本当にありがとうございました。

（ＢＦ） 2014/09/03(水) 10:33

コメント返信：

[ 一覧(最新更新順) ]

[[20140902113228]] 『ある数値から等差級数を減算』（ＢＦ） ページの最後に飛ぶ

[[20140902113228]]　『ある数値から等差級数を減算』（ＢＦ）　ページの最後に飛ぶ