エクセルの関数について : CHOOSE関数

複数範囲のtrimmeanを計算するのに下記のような関数式を使っていますが、
CHOOSE関数を通すと空白セルがあると0で集計されてしまい、正しい値が出ません。
どのようにしたらよいでしょうか?

=TRIMMEAN(CHOOSE({1,2,3},Sheet1!A1:A3,Sheet2!A1:A3,Sheet3!A1:A3),0.1)

たとえばシートの内容が下記のようになっていたとすると、
CHOOSE関数を通すと空欄""が0で置き換えられてしまい、
[欲しい結果]=trimmean([1,1,"",1,1,"",1,1,""],0.1)=trimmean([1,1,1,1,1,1], 0.1)
[計算結果]=trimmean([1,1,0,1,1,0,1,1,0], 0.1)
になってしまい、正しく計算されません。

■セルの値■
[Sheet1]
A1 : 1
A2 : 1
A3 : ""

[Sheet2]
A1 : 1
A2 : 1
A3 : ""

[Sheet2]
A1 : 1
A2 : 1
A3 : ""

No.6 ベストアンサー 回答者： CoalTar 回答日時： 2011/07/02 17:12

例題ならAVERAGEで終わり

たぶん、もっと計算対象があるのでしょう
最低でも20以上の対象があるでしょうから 7行*3列として
=TRIMMEAN(CHOOSE({1,2,3},IF(A1:A7<>"",A1:A7),IF(Sheet2!A1:A7<>"",Sheet2!A1:A7),IF(Sheet3!A1:A7<>"",Sheet3!A1:A7)),0.1)
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)

この回答へのお礼

ありがとうございました。

ご指摘のとおり、実際のデータ数はもっとたくさんあります。
説明不足で申し訳ありません。

回答いただいたように、配列数式を使う方法で解決できそうです。

お礼日時：2011/07/02 20:38

No.8 回答者： KURUMITO 回答日時： 2011/07/02 22:00

回答No7です。

TRIMMEAN関数についてよく理解しないままに答えてしまったようです。
勉強になりました。有難うございました。

No.7 回答者： KURUMITO 回答日時： 2011/07/02 17:30

回答１，２ですがそれぞれのシートでTRIMMEAN関数を使っているのと同じことを回答２では行っているのですがそれは違っているという

ことですか１のデータを並べるのではなくもっと違うデータを使って正しい答えはいくつかを示していただけませんか？

この回答への補足

説明不足で申し訳ありません。

極端な例として、下記データ(10ヶ X 3シート)で説明します。
(計算の概念の説明ですので、質問で問題としている空欄セルは入れていません。)

[元データ]
シート1 : 0,0,0,0,0,0,0,0,0,0
シート2 : 1,1,1,1,1,1,1,1,1,1
シート3 : 3,3,3,3,3,3,3,3,3,3

この30個のデータからTRIMMEAN(上記データ,0.2)計算する場合、
結果は上下10%ずつデータを除外したもののAVERAGEになります。
(上3ヶと下3ヶが除外)

欲しい結果と回答いただいた方法での計算結果を記載しますが、
結果が一致していないのが分かると思います。

[欲しい結果]
シート1 : 0,0,0,0,0,0,0 (0を3ヶ除外)
シート2 : 1,1,1,1,1,1,1,1,1,1 (除外なし)
シート3 : 3,3,3,3,3,3,3 (3を3ヶ除外)
この24ヶのデータのAVERAGE=1.2917

[回答いただいた方法]
シート1 : 0,0,0,0,0,0,0,0 (0を2ヶ除外、各シートの上下1ヶずつ除外)
シート2 : 1,1,1,1,1,1,1,1 (1を2ヶ除外、各シートの上下1ヶずつ除外)
シート3 : 3,3,3,3,3,3,3,3 (3を2ヶ除外、各シートの上下1ヶずつ除外)
この24ヶのデータのAVERAGE=1.3333

また、回答２の方法では、下側10%のデータしか除外されていないと思います。
元々の質問に記載したTRIMEMEANの計算式ですと、上5%、下5%のデータ除外が必要です。

補足日時：2011/07/02 21:08

No.5 回答者： keithin 回答日時： 2011/07/02 17:09

とりあえず単純なチカラワザで

=TRIMMEAN(IF(CHOOSE({1,2,3},ISBLANK(Sheet1!A1:A3),ISBLANK(Sheet2!A1:A3),ISBLANK(Sheet3!A1:A3)),"",CHOOSE({1,2,3},Sheet1!A1:A3,Sheet2!A1:A3,Sheet3!A1:A3)),0.1)
をCtrl+Shift+Enterしてみるなど。

この回答へのお礼

ありがとうございました。
回答いただいたように、配列数式を使う方法で解決できそうです。

お礼日時：2011/07/02 20:36

No.4 回答者： silverfd 回答日時： 2011/07/02 17:04

各シートのA3を、書かれているように""（""だと文字列「""」になるので＝””とする）にしたら、問題なく正解が得られました。

要は、上記は""はただの空白を意味しているのですね。エクセルの仕様として、明示的に文字長0の文字列である""と入力されていない空白は、数値のゼロとみなすようですね。確かに何も入力しなければおっしゃる通りの結論、前述の通り各シートのA3に全て=""と打ち込むと、求めていた数値が得られています。

VBAで対象となるエリアの空白を、全て=""で埋めるのが一番手間がなく簡単な方法だと思います。私ならシートモジュールのイベントプロシージャに仕込んで、データが入力されると即そのように空白が=""に変換されるようにします。Private Sub Worksheet.Changeならあらゆる変更に対して処理を行うので、万が一のミスも起こりえません。

もともとChooseが配列ですから、配列の中の配列でSubstitute使うとか出来るのかどうかもわかりませんしね。もし、VBA禁止の環境なら改めて別の方法を考えます。

この回答へのお礼

""はただの空白を意味しています。
説明不足で申し訳ありません。

空白を数値のゼロとみなす仕様が正直邪魔です。

私の行いたいことの概要としては、一般的なデータに対してVBAから必要な計算式(TRIMMEANなど)をセルに書き込んで様々な統計値を計算することです。例ではデータ数は少ないですが、実際には膨大です(数千行～数万行 X 256列 X複数シート)

元データは決まったファイル/シートに入力されている訳ではないので、イベントプロシージャを使うためにはVBAからVBAを書き込めむ必要がありますが、やったことが無いので可能かどうか調べて見ます。

処理は速いほうがいいので、イベントプロシージャで膨大な数のセルを操作するのが効率的かどうかというのもあります。

質問した時点では、関数のみで解決する方法を想定していました。

お礼日時：2011/07/02 20:33

No.3 回答者： maron--5 回答日時： 2011/07/02 15:26

◆これでいかがでしょう

=TRIMMEAN(CHOOSE({1,2,3},0&Sheet1!A1:A3,0&Sheet2!A1:A3,0&Sheet3!A1:A3)*1,0.1)

この回答へのお礼

ありがとうございます。
いろんな値でやってみましたが、正しい結果が得られません。
"0&"にはどういう意味があるんでしょうか?
セルの値がマイナスのときはエラーになってしまいませんか?

お礼日時：2011/07/02 16:03

No.2 回答者： KURUMITO 回答日時： 2011/07/02 15:17

TRIMMEAN関数を基本に返って解釈すれば次のような式で表すことができますね。

=(SUMIF(Sheet1!A1:A3,">="&MAX(A1:A3)*0.1)+SUMIF(Sheet2!A1:A3,">="&MAX(A1:A3)*0.1)+SUMIF(Sheet3!A1:A3,">="&MAX(A1:A3)*0.1))/(COUNTIF(Sheet1!A1:A3,">"&MAX(A1:A3)*0.1)+COUNTIF(Sheet2!A1:A3,">"&MAX(A1:A3)*0.1)+COUNTIF(Sheet3!A1:A3,">"&MAX(A1:A3)*0.1))

これで正しい答えが得られるでしょう。

この回答へのお礼

ありがとうございます。
ですが、この式では一般的に正しい結果は得られません。
"各シートのデータを上下5%カット"と"データ全体の上下5%カット"は同じではありません。
TRIMMEAN関数の計算の解釈が間違っています。

お礼日時：2011/07/02 16:06

No.1 回答者： KURUMITO 回答日時： 2011/07/02 15:07

次のようにしてはどうでしょう。

=(TRIMMEAN(Sheet1!A1:A3,0.1)+TRIMMEAN(Sheet2!A1:A3,0.1)+TRIMMEAN(Sheet3!A1:A3,0.1))/3

この回答へのお礼

ありがとうございます。
ですが、この式では一般的に正しい結果は得られません。
"各シートのデータから上下5%カット"と"データ全体の上下5%カット"は同じではありません。

お礼日時：2011/07/02 15:48