excelの質問です

質問

excelで次のようなことを行いたいと思っています。

１）１００個の要素をもつ１つ目のデータとして１，２，３，４，５・・・・があるとします。
２）その入力データのそれぞれを合計で全てを割ります
　　1/(1+2+3+・・・)　　　 2/(1+2+3+・・・)　　　 3/(1+2+3+・・・) ・・・・
３）１００個の要素をもつ２つ目のデータとして101、１02,103・・・・があるとし、
　　１つ目のデータと同じように処理を行います
４）２つのデータの要素の１つ目、２つ目、３つ目・・・でそれぞれの差をとります
　　{101 / (101 + 102 + 103・・・) } - {1/(1+2+3+・・・)}
　　{102 / (101 + 102 + 103・・・) } - {2/(1+2+3+・・・)}
：
　　　　　　　　　　　　　　　　：　　

３）それらをそれぞれ２乗します
　　{101 / (101 + 102 + 103・・・) } - {1/(1+2+3+・・・)}　^ 2
　　{102 / (101 + 102 + 103・・・) } - {2/(1+2+3+・・・)} ^ 2
：
　　　　　　　　　　　　　　　：　　
４）その合計をとります
　　{101 / (101 + 102 + 103・・・) } - {1/(1+2+3+・・・)}^ 2
+
　　{102 / (101 + 102 + 103・・・) } - {2/(1+2+3+・・・)}^ 2
：
　　　　　　　　　　　　　　　：　　

このことを１つ目のデータ、２つ目のデータの２つのデータ列と結果の１つのセルだけ表したいのです。
回答お願いします。

回答No.4

keithin · ベストアンサー

そうですね・・・
５０要素のデータが５０列，Ａ列からＡＸ列まであるとして，その右側ＢＡ列からＣＸ列，ＤＡ列からＥＸ列，ＦＡ列からＧＸ列まで利用可能です。１００行から１０００行まで程度のデータ量でしたら，計算負荷もどうという事はありません。

仮にＡ列が基準データ（１，２，３の列），Ｂ列から実測値のようなものだとして，Ｂ列を計算する数式をＢＡ列に１列作成すれば残り４８列は単純にコピーするだけのお話です。
またシート２，３，４に，立体的に展開していくような方法もありそうです。中間処理は裏シートに回し，キモになる計算結果だけ表に戻すような細工をしてみるのでも使いやすいかもしれません。

何て言うか，「やりたくない」と思っている時には「こんなに大変な理由」を次々挙げてやらずに済ませようとするのはとても容易です。
その全く裏返しで，「こうすれば良い」と思っている時には「そんなのどうとでもなりますよ」と言ってのけてしまうのもまた容易なお話という事なんですね。

とは言え現実的には，最初はジミチ－で確実な計算積み上げと「正体不明の専用関数」を併用し，十分にプロセスが検証できた後に例えば１００行目以降の追加データに対しては専用関数だけで合理化するといった具合に進行していくと思います。

お礼

回答ありがとうございます。
＞またシート２，３，４に，立体的に展開していくような方法もありそうです。中間処理は裏
＞シートに回し，キモになる計算結果だけ表に戻すような細工をしてみるのでも使いやすいか
＞もしれません。
教えてくださったとおり中間処理を裏に回すとページ全体が見やすく簡単に拡張できそうなので早速やってみたいと思います。

＞最初はジミチ－で確実な計算積み上げと「正体不明の専用関数」を併用し，十分にプロセス
＞が検証できた後に例えば１００行目以降の追加データに対しては専用関数だけで合理化する
＞といった具合に進行していくと思います。

何度も繰り返してしっかりと確かめて使ってみようと思います。
回答ありがとうございました！

回答No.3

keithin

=SUMXMY2(A1:A100/SUM(A:A),B1:B100/SUM(B:B))
ですね。

＃余談ですが
こちらでも昨日のご相談でSUMPRODUCT()^2では合計の二乗であって二乗和(SUMSQ)と違いますよと投稿しようとして，既にクローズされていました
SUMSQ/SUM^2
が確かに一番簡単です。無理すると
=SUMSQ(INDEX(A1:A100/SUM(A:A),))
などのようになってしまいました。SUMSQ関数が少しバグってるぽいです。

＃余談ですが
＞結果の１つのセルだけ表したい
まさに昨日のやり取りがそうでしたが，そうやって無理に詰め込むと，検算も出来ず間違いの気付きもない愚かな（というのが言い過ぎなら危険な）アプローチです。
列を追加し計算を積み上げるジミチーな計算手順を強く推奨します。

お礼

完結でわかりやすい回答ありがとうございます。
前回の回答ですが確かに間違っていたようです。
ご指摘ありがとうございます。

＞まさに昨日のやり取りがそうでしたが，そうやって無理に詰め込むと，
＞検算も出来ず間違いの気付きもない愚かな
＞（というのが言い過ぎなら危険な）アプローチです。
＞列を追加し計算を積み上げるジミチーな計算手順を強く推奨します。

このことなのですが目的としては５０個の要素を持つ入力データが100
以上あり、いちいち計算していたら膨大な量になってしまう上に、
どんどんデータを追加することを考えているため面倒なことになると思い今回のような
質問をさせていただきました。

なにかよい方法はないでしょうか？

回答No.2

kagakusuki

今仮に、１つ目のデータがA1～A100に、2つ目のデータがB1～B100に並んでいるものとした場合、次の様な関数で求める事が出来ます。

=SUMPRODUCT(($A$1:$A$100/SUM($A$1:$A$100)-$B$1:$B$100/SUM($B$1:$B$100))^2)

　只、統計関数等を使用した、もっとスマートな方法があるかも知れませんので、他の回答が投稿されるまで、様子を見られる事を御勧め致します。

お礼

前回の質問とともに回答していただきありがとうございます。
さっそく入力してみたところ期待通りのもとができました！
せっかく答えをいただいたのですが助言していただいた通り
しばらく待ってみたいと思います。

回答No.1

kagakusuki

すいません、こちらの質問をされた方だと思いますが、

　QNo.6742157　excelの質問です
　　http://okwave.jp/qa/q6742157.html

　上記の御質問に対して、計算の負荷が少ない方法を回答しようとしたのですが、投稿前に質問が締め切られてしまい、投稿する事が出来なかったため、失礼ながら、こちらに、上記の御質問に対する回答を投稿させて下さい。

　入力データがA列にあるものとした場合、各数値の２乗を、合計値の２乗で割ったものの合計を求める関数の一例は、次の様なものがあります。

=SUMSQ(A:A)/SUM(A:A)^2