Excel（エクセル）活用コミュのデータステップの変更

すみません随分調べたのですが、
良い方法が見つからないので教えてください。

図のようX,Yのデータで、
特定の関数に近似できず、
かつ、Xの刻み幅(ステップ)が一定でないデータが得られたときに、

これを、
一定幅のXでグラフを作り変えたいのです。
たとえば、上の例で、Ｘが10きざみのデータに変えたいのです。

与えたXより大きい最小のXと、それに対応するYと
与えたXより大きい最大のXと、それに対応するYで
で線形近似した値が取れればよいと思うのですが、
エクセルでうまくやる方法はありませんか？

よろしくおねがいいたします。

XP　2003です。

コメント(19)

最初
全て
最新の40件

[2] mixiユーザー 05月02日 13:51

うが!間違えた。。。

＊＊＊＊＊＊＊
ようは近似式を簡単に求められるようなデータに捏造したいということですよね。

（もしそうなら、、もしそうならの話ですよ！）
自分の学生や部下がこんなことをしたら、小一時間説教して、
統計学の講義を３回ほどどこかで再受講させて、
「SPSS」の使い方を覚えさせますが。。

まじめにデータ解釈を教えるのは面倒なので、
散布図から近似式を作り出して、
X=１０、２０、３０、、、、などの値を代入してYの値を求めてください。

それか、先輩に処理の仕方を聞いたほうが良いでしょう。

[3] mixiユーザー 05月02日 16:46

＞HOF さん
ご回答ありがとうございます。
図のようなデータが100程度ありまして、それらの積み上げのグラフを書きたいのですが、それぞれのｘが各データごとに異なっております。
多項式近似でも、ちょっと近時できないデータもありまして、
また、手作業にも限界がありまして、ご提案された方法は少し難しそうです。
ご回答していただいて大変もうしけありませんが、何か他の方法がありましたら教えていただけると幸いです。

[4] mixiユーザー 05月02日 17:16

後から読み返すと、捏造だなんていやな言葉使ってますね。。。。。＾＾；

解析の方針はtai_kiさんのほうでよくよく考えて、

> 与えたXより大きい最小のXと、それに対応するYと
> 与えたXより大きい最大のXと、それに対応するYで
（↑下のほうは、「与えたXより小さい最大のX」のことですよね）

上のような方法を求めているのだと察しますが、
Excelでこのような解析をあまりやらないので、
こちらの解決策は他の方に下駄を預けます。

んで、ageの意味もこめて、
解析方針の足しになればと思い、以下を続けますね。

もし測定範囲の両端近辺が「不要」であり、
またその１００個のデータがどれも類似した傾向のグラフならば、
自分は「範囲を限定する」という条件の下、
最小二乗法で直線式に落とし込んではいかがでしょうか。
（このサンプルで言うと、X=１５～７９）

[5] mixiユーザー 05月02日 17:22

match関数で照合の型を1にすれば下側の値の位置がわかります。
その1行下が上側の値の位置です。

あとは下側が(x, y) 上が(x', y') 当該位置が (x", y")とすると、

y" = (y' - y)(x" - x)/(x' - x) + y

でできそうですねー

[9] mixiユーザー 05月02日 17:37

求める Y = IF(ISERROR(MATCH(x", 範囲, 0)), y", MATCH(x", 範囲, 0))

y" = (y' - y)(x" - x)/(x' - x) + y

x = offset(始点, match(x", 範囲, 1), 0)
y = offset(始点, match(x", 範囲, 1), 1)

x' = offset(始点, match(x", 範囲, 1)+1, 0)
y' = offset(始点, match(x", 範囲, 1)+1, 1)

始点はここでは$A$1
範囲は$A$2:$A$???です。

[10] mixiユーザー 05月02日 17:44

こんな感じですかねぇ > 5

[11] mixiユーザー 05月02日 17:49

間違えまくって何回も訂正したのだが、まだ間違ってる。

求める Y = IF(ISERROR(MATCH(x", 範囲, 0)), y", OFFSET(始点, MATCH(x", 範囲, 0), 1))

y" = (y' - y)(x" - x)/(x' - x) + y

x = offset(始点, match(x", 範囲, 1), 0)
y = offset(始点, match(x", 範囲, 1), 1)

x' = offset(始点, match(x", 範囲, 1)+1, 0)
y' = offset(始点, match(x", 範囲, 1)+1, 1)

始点はここでは$A$1
範囲は$A$2:$A$???です。

こうかな。できたのが図。

[13] mixiユーザー 05月02日 17:53

上（> 10）の写真の数式は、、

x=INDEX(A2:B10,MATCH(A14,$A$2:$A$10,1),1)
y=INDEX(A2:B10,MATCH(A14,$A$2:$A$10,1),2)

x'=INDEX(A2:B10,MATCH(A14,$A$2:$A$10,2)+1,1)
y'=INDEX(A2:B10,MATCH(A14,$A$2:$A$10,2)+1,2)

で、
y" とx"は > 5　の数式を利用

[14] mixiユーザー 05月02日 17:54

ふぁいるはここ

http://narak.jp/tmp/30655681.xls.gz

[15] mixiユーザー 05月02日 17:55

> 11

>範囲は$A$2:$A$???です。

???ってなんですか?

[16] mixiユーザー 05月02日 17:56

だって、一番下がいくつまであるかわからんしー

[17] mixiユーザー 05月02日 17:58

ああ、グラフを見るに、このデータはA26までみたいですね。
そのへんは、てきぎへんこうしてちょー

[18] mixiユーザー 05月02日 17:59

あっ、そうか。

[19] mixiユーザー 05月02日 18:26

＞kenic さん
ありがとうございます。
できました！
あきらめてperlのハッシュでせっせと書こうと思っていたところだったの、本当に助かりました。
本当にありがとうございました。