エクセルでこんな計算できますか？

質問

計算したいデーターは以下のとおりです。

A列に0～2000付近までの数字（長さ）
B列にその長さの時の実績値
この2つで1Bt分

C・D列以降も二列ずつ同じ様にデーターが並んでいます。
（大体1000～2500程度の縦長さ。横には30Bt分くらい）

長さ列の頭0～100・終わり0～100をはずした長さに対する実績の全てのBtの平均値・標準偏差の数値を出したいのですがデータやその他のシート（グラフなど）が重いため
出来るだけ軽く出来ればと思っています。
またこれを数十個のブックに対して行わなくてはならないので、一つ一つのブックに対しての実行時の操作は比較的簡単な物である方が望ましい。

現在会社にてこれができないため大変困っております。
なんとか助けてもらえないでしょうか？

回答No.3

imogasi · ベストアンサー

Ａ列、Ｂ列に数値データが入って入っているとします。
列挿入して空白のＣ列を作ります。
５０件データがあるとします。トップ１０とボトム１０は
順位では１－１０、４１－５０ですから
Ｃ１に=AND(RANK(A1,A1:A50)>10,RANK(A1,A1:A50)<41)
と入れます。そしてＣ５０まで複写します。
ＦａｌｓｅとＴｒｕｅが合計２０個と３０個入ります。
このＦａｌｓｅは計算においては０、Ｔｒｕｅは１と考えてください。（Ｆａｌｓｅの行、Ｔｒｕｅの行で＝Ｃ１２＊１とかをやって見て納得してください。）
Ｄ１等どこでも=SUM(B1:B50*C1:C50)といれ、Ｓｈｉｆｔ
キーとＣｔｒｌキーを押しながら、Ｅｎｔｅｒキーを押します。配列数式と言うものを利用します。
Ｂ列とＣ列を対応行を掛け算します。Ｆａｌｓｅの行は０を掛けているから、０になり、足し算されないのと同じに
なります。その縦列の和が求まるのです。
http://pc21.nikkeibp.co.jp/pc21/special/hr/hr1.shtmlなどで勉強すること。
結果は件数－２０で割れば平均が出ます。
Ｃ列は配列数式の一端をわかりやすくするため、わざわざ
出したもので、本当は不要です。
取りあえずＳｕｍを出し、件数で割る、ランク順位は人間の目視割りだし（それも総件数から引き算して）回りくどいやり方になっていますが、後刻どなたか配列数式かＳｕｍＰｒｏｄｕｃｔを使ったスマートな回答が出るでしょう。
補足要求した手前、取りあえず平均について載せます。

お礼

どうもありがとうございました。
配列式か～・・・
なんとなく平均の方はできそうです！
でもこのやりかたでは複数行の標準偏差に対しては・・・？
また明日「教えて！」にのしてみます。
どうもありがとうございました。

回答No.4

papi-pu

手作業でなさるなら、
A,B列のデータのある範囲を選択
ソートする　←このとき、A列とB列だけが、A列にあわせて並び変わるはずです。
上位100個と下位100個を選択して平均値と標準偏差を求める
というのはいかがでしょうか。

VBAを使えばもっと自動化できそうですけど、今は思いつきません^^;

お礼

どうもありがとうございました。
しかし私のでーたーはトップ100が上位100とは限らない
（0.1とか1.8とか小数点たんいのものもあるんで・・・）
のでこのやりかたでは・・・
しかしごしんせつにありがとうございました。

回答No.2

papi-pu

No.1さんも書いているようにBtの意味がわからないので、何の平均値、標準偏差を求めたいのかわかりませんが、
平均値を出すためにはAverage関数
標準偏差を出すためにはStdeva関数があります。

詳しい使い方はヘルプを見てください。
関数を使う分には、セルに関数を書いて、後はコピーするだけなので簡単だと思いますが。

もうちょっと、何をしたいのか書いていただかないと、説明できません^^;

補足

No1さん同様わかりにくい文でご迷惑おかけしました。
わたしが聞きたいのはNo1さんご指摘の通り
＞「２００行以上ある２列ペアーのデータで、左列トップ１００とボトム１００を除外した右列平均を出す関数式はどうする」
ということなのです。AV・標準偏差の関数自体はわかるのですが左列に条件をつけ右列を計算させるという条件式がうまくできないのです。
なにかよいアイデアがありましたらご教授下さい。

回答No.1

imogasi

>A列に0～2000付近
0～2000付近とは１－２０００行辺りまでデータ数があると言うことか。
入力する数値が１から２０００辺りまでの大きさの数値が
入っているのか。なぜそれを述べる必要があるのか。
>までの数字（長さ）
長さとは「何かの」物体の測定した長さの数値か。
商品のサイズ長か。
＞この2つで1Bt分、Ｂｔはなんの意味か。
＞大体1000～2500程度の縦長さ。
「縦長さ」とは物体の縦の長さか。「縦」をなぜ述べる
必要あるのか。
＞横には30Bt分くらい
Ａ・Ｂ、Ｃ・Ｄ、Ｆ・Ｇ、・・と３０組（６０列）ぐらい
データがあるのか。
＞長さ列の頭0～100・終わり0～100をはずした
各列でＴＯＰ１００とＢｏｔｔｕｍ１００を除外するのか。
＞長さに対する実績の
実績とは。販売実績などか。
●結果として、質問文や説明は長く仕事密着の言葉がでてくるが、不要で
「２００行以上ある２列ペアーのデータで、左列トップ１００とボトム１００を除外した右列平均を出す関数式はどうする」で良いのでは。あるいは「右列トップ１００・・・」かも知れない。
●ペアーが３０以上あって「しんどい」のを解決は次ぎの問題。

補足

わかりにくい文で大変申し訳ありません。結論として私が教えていただきたいのはご指摘の通り
＞「２００行以上ある２列ペアーのデータで、左列トップ１００とボトム１００を除外した右列平均を出す関数式はどうする」
ということであります。
なにか良い方法がありましたら力を貸していただけませんか？