EXCELでこんな計算できますか？

質問

２０００行以上ある２列ペアのデータで、
左列トップ１００とボトム１００を除外した
全てのペアの（一ペアずつではなく全てのペア合算の）
右列の平均・右列の標準偏差を出すにはどうすれば
よいのでしょうか？

回答No.4

maruru01

>Ａ・Ｂ列２列で1ペアのデータが複数列
>（Ｃ・Ｄ列　Ｅ・Ｆ列・・・合計30ペアほど）あります。
>その全て合算の平均・標準偏差がほしいのです。
こういうことでしょうか。
データが1行目のA・B列～CU・CV列まであるとすると、平均は、

=AVERAGE(IF((MOD(COLUMN($A$1:$CV$1),2)=1)*($A$1:$CV$1>SMALL(IF(MOD(COLUMN($A$1:$CV$1),2)=1,$A$1:$CV$1),10))*($A$1:$CV$1<LARGE(IF(MOD(COLUMN($A$1:$CV$1),2)=1,$A$1:$CV$1),10)),$A$1:$CV$1))

と入力して、[Ctrl]＋[Shift]＋[Enter]で決定して、配列数式にします。
標準偏差は、上式で
「AVERAGE」→「STDEV」
に変更します。

お礼

どうもありがとうございました。
何とかやってみたいと思います。

回答No.3

ki-aaa

こんにちは

どうしても、関数でなくては、いけないですか。

マクロでやれば、そんなに難しいことでもないような気がします。

マクロの記録でやるとすれば
１　A,B列をIU,IVにコピー貼り付けをする。
２　IU,IV列を、IU列をキーとして、並び替えをする。
３　IU,IV列の下から、１００行を削除する
４　IU,IV列の上から、１００行を削除する
５　IU,IV列の平均、標準偏差を算出する
６　IU,IV列を、削除する。

マクロの記録を終える。

できたマクロを繰り返し使用して、全ペア処理出来るようにする。

こちらで、マクロを組むこともできますが、そのときは、データの配置をもっと具体的に書かないと(一行目は、見出しに使っているとか、計算結果はどこに入れるかとか)、また、質問を立てるようになります。

がんばってください。

お礼

ご指導ありがとうございます。
マクロでやることも視野に入れ再度考えてみます。

回答No.2

maruru01

こんにちは。maruru01です。

データがA、B列の1～1000行にあるとします。
平均は、

=AVERAGE(IF(($A$1:$A$1000<LARGE($A$1:$A$1000,100))*($A$1:$A$1000>SMALL($A$1:$A$1000,100)),$B$1:$B$1000))

と入力して、[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
（数式の両端に「{}」が付いて、配列数式になります。）
標準偏差は、上式で、
「AVERAGE」→「STDEV」
に変更して、同様に配列数式として入力して下さい。

なお、100位が2つ以上とか、99位が3つ以上などの場合は、同点のデータを全て除外して計算されます。
つまり、総データ数が1000点で、トップ100とボトム100を除外した点数は800より少なくなる可能性があるということです。

補足

ご返答ありがとうございました。
私の質問文が悪く不具合全てお伝えできていなかったようです。
Ａ・Ｂ列２列で1ペアのデータが複数列（Ｃ・Ｄ列　Ｅ・Ｆ列・・・合計30ペアほど）あります。
その全て合算の平均・標準偏差がほしいのです。
ご迷惑をおかけし大変申し訳ありません。
なにか良い知恵があれば再度ご教授ください。

回答No.1

grumpy_the_dwarf

A列とB列できちんとペアになって1行目から並んでいるものとします。

最初の100行と最後の100行を除外するには、計算範囲をを101行目か
らcount(A:A)-100行目までにすればいいので、
indirect("B101:B"&count(A:A)-100
という範囲になり、
=average(indirect("B101:B"&count(A:A)-100) とか
=stdev(indirect("B101:B"&count(A:A)-100) なんていう結果になり
ますね。

補足

ご教授ありがとうございます。
私の文章が悪かったんですが、トップ100・ボトム100はずしというのはデータ的に行では判断させることができません。（Ａ列は長さのデータで長さ100ｍｍはずしということで１行に1ｍｍとは限らない）
平均のほうは各列ペアの合計・個数をだし、ペア全体の合計÷ペア全体の個数でいけるのですが標準偏差は・・・？
私の文章が悪く大変ご迷惑をおかけします。
なにか良い方法があれば再度ご教授ください。