すみませんＥＸＣＥＬ再質問になります。

質問

頂いた数式を基に作業をしていたのですが、思ったより問題が
出ました。申し訳ないのですが再質問させてください。

Ａ点、Ｂ点を直線にする数式は他に方法は無いものでしょうか。
先日教えて頂いた数式ですが、
>例えばＡ１＝１０、Ａ９＝９０として
>Ａ２セルに　=A$1+(A$9-A$1)*(ROW()-ROW(A$1))/(ROW(A$9)-ROW(A$1))
>Ａ８まで式を複写
と教えて頂いたのですが、引き続き
Ａ１０＝５０、Ａ１８＝１００の場合
=A$10+(A$18-A$10)*(ROW()-ROW(A$10))/(ROW(A$18)-ROW(A$10))
になり行の部分を手入力で変更しなければなりません。
（行数が最大５００行ほどのブックもあり厳しいです）

また、以下の方法も教えて頂いたのですが、
=(A4-A1)*2/3+A1
=(A4-A1)/3+A1
セルの区間に変更があると　2/3　や　/3　の部分を
手入力で変更しなければなりません。

入力ミスや変更作業時間の可能性を少しでも
無くしたいので最小限の変更にしたいです。
マクロも含め手段はないでしょうか。

よろしくお願い致します。

回答No.4

Wendy02

こんばんは。

#2の回答者です。

#2の数式をそのまま、マクロに置き換えてみました。理由は、出した数値を、そのままでは、数式には入れられないからです。それを、そのまま使うと循環参照を起こしてしまいます。

2点間の数値の一次関数(Liner Function)の数式を作ります。
数式自体は、MsgBox だけですが、Debug.Print を外せば、数式は残ります。なお、計算式の結果とマクロの違いが出るのは、マクロ側が、通貨型のデータを使っているからで、マクロ側のほうが正確です。

ただ、直接を取るだけのためなら、このようなことはまったく必要などありません。単に、[線形近似]を取ってあげればよいのですが、数式の場合は、LINEST関数の場合は、配列を必要としますから、穴埋めの数値が必要になってしまいます。

標準モジュールに登録すると良いです。
'-----------------------------
Sub MakingLinerFunc()
'1次関数のグラフ用のデータ作成マクロ
'該当の列にカーソルを置いてから実行してください。

　　Dim myCol As Range
　　Dim rng As Range
　　Dim dblMin As Double
　　Dim dblMax As Double
　　Dim lMin As Long
　　Dim lMax As Long
　　Dim Alpha As Currency
　　Dim Delta As Currency
　　Dim Func As String
　　Const vbMyError As Integer = 513
　　On Error GoTo ErrHandler
　　If StrComp(TypeName(Selection), "range", 1) = 0 Then
　　　　Set myCol = Selection.EntireColumn
　　Else
　　　　Exit Sub
　　End If
　　'数値のカウント
　　If Application.CountA(myCol) <> 2 Then
　　　　Err.Raise vbMyError
　　End If
　　'必要データの収集
　　dblMin = Application.Min(myCol)
　　dblMax = Application.Max(myCol)
　　lMin = Application.Match(dblMin, myCol, 0)
　　lMax = Application.Match(dblMax, myCol, 0)
　　Alpha = (dblMax - dblMin) / (lMax - lMin)
　　Delta = dblMin - Alpha * lMin
　　Application.ScreenUpdating = False
　　With ActiveSheet
　　　　Set rng = .Range(.Cells(1, myCol.Column), .Cells(65536, _
　　　　myCol.Column).End(xlUp))
　　　　rng.SpecialCells(xlCellTypeBlanks).FormulaLocal = "=" & Alpha & "*ROW()+" & _
　　　　Delta
　　End With
　　Application.ScreenUpdating = True
ErrHandler:
　　If Err.Number = 6 Then
　　　　MsgBox "この列には、要素となる数値が足りないようです。", 48
　　ElseIf Err.Number = vbMyError Then
　　　　MsgBox "対象となる数値は、その列に2つです、多くても少なくてもいけません。", 48
　　ElseIf Err.Number > 0 Then
　　　　MsgBox Err.Number & " :" & Err.Description, 48
　　Else
　　　　Func = "この数列は、 = " & Alpha & "x" & String(CLng(Delta > 0) ^ 2, "+") & CStr(Delta)
　　　　MsgBox Func & "　の数式です。", 64
　　　　'Debug.Print Func
　　End If　　
　　Set rng = Nothing
　　Set myCol = Nothing
End Sub

回答No.3

noname#79209

セル参照のセル番地の行暗号や列番号の前に「$」をつけると、
式をコピーしても、行や列の番地が変化しません（絶対番地指定と言います）。
式を作る場合、コピーのことも勘案しながらながら「$」をつけたり外したりすることが大切です。

例えば、構成比を出したいときに、
　　Ａ　　　Ｂ　　　Ｃ
１　項目名　データ　構成比
２　項目１　１０
３　耗目２　２０
４　項目３　３０
５　合計　　６０
C2に
「=B2/B5」として下にコピーしてしまうと、
C3とC4は「=B3/B6」「=B3/B7」と分母の参照も一緒に変化してしまいます。
ここで、「=B2/B$2」と分母の行番号に「$」付けておけば、分母のセル参照は変化しません。

> セルの区間に変更があると　2/3　や　/3　の部分を手入力で変更しなければなりません。
「2/3」や「/3」は計算の対象になるセル数が変化しても追従させる事なのだと思いますが、
この数値は、COUNT関数などで自動的に計算できると思われますが、具体的な計算式は、
ご質問の例からだけでは判りません（表のイメージが書かれていないので）。
例えば、
=(A4-A1)/3+A1
の「3」がA2～A4の3つのセルの意味なら、
=(A4-A$1)/COUNT(A$2:A4)+A$1
とすれば可能ですが「あくまで可能性」ですので、質問者さんの表のデザインによっては大きく変わってきます。

とにかく、
１．絶対参照と相対参照の意味を充分理解されることと、
２．関数を勉強されて、
３．提示された数式をそのま鵜呑みにせず、自分で式の意味を解析する
などをして、
セルの変化にも対応できる数式を作成できるスキルを身につけて下さい。

回答No.2

Wendy02

こんにちは。

それは、一次関数ではありませんか。

つまり言い換えれば、
y = αx + b　の数式になりますね。

α　= yの増加分/x 増加分　

ですから、
例えば、A1 =10 で、A20 =200 ですと、

αの傾きは、(200-10)/(20-1) =10
で、x=1　で、y =10 なら、

b は、10-10 =0
という計算ですから、

y = 10 x
という関数式が取れます。

それをExcelに代入する場合は、
= 10 * ROW()

>Ａ１０＝５０、Ａ１８＝１００の場合

αの傾き
=(A18-A10)/(18-10)
=6.25

bの定数は、

=50-6.25*10
=-12.5

したがって、
y= 6.25x- 12.5

という数式が取れます。

数式が取れれば、後は、Excelに代入すればよいわけです。

= 6.25*ROW() -12.5

これが、面倒なら、最初から、折れ線グラフで、数式を取り出しても同じように出るはずです。

回答No.1

hallo-2007

例えば　
C1に　１（スタートの行番号）　D1に１０
C2に　９（ゴールの行番号）　　D2に９０　　入れて
B1に　=TREND(D$1:D$2,C$1:C$2,ROW(A1),D$1)　　たっぷり下フィルしておいておく。
それぞれのシートOrブックの　C1,C2　D1,D2に数値入れるだけですがいかがでしょうか？