みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルの関数について : 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 : ""

投稿日時 - 2011-07-02 14:46:00

QNo.6849163

すぐに回答ほしいです

質問者が選んだベストアンサー

例題なら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 17:12:41

お礼

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

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

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

投稿日時 - 2011-07-02 20:38:22

ANo.6

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

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

投稿日時 - 2011-07-02 22:00:05

ANo.7

回答1,2ですがそれぞれのシートでTRIMMEAN関数を使っているのと同じことを回答2では行っているのですがそれは違っているということですか1のデータを並べるのではなくもっと違うデータを使って正しい答えはいくつかを示していただけませんか?

投稿日時 - 2011-07-02 17:30:53

補足

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

極端な例として、下記データ(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

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

投稿日時 - 2011-07-02 21:08:20

ANo.5

とりあえず単純なチカラワザで
=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 17:09:53

お礼

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

投稿日時 - 2011-07-02 20:36:28

ANo.4

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

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

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

投稿日時 - 2011-07-02 17:04:47

お礼

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

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

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

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

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

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

投稿日時 - 2011-07-02 20:33:11

ANo.3

◆これでいかがでしょう
=TRIMMEAN(CHOOSE({1,2,3},0&Sheet1!A1:A3,0&Sheet2!A1:A3,0&Sheet3!A1:A3)*1,0.1)

投稿日時 - 2011-07-02 15:26:39

お礼

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

投稿日時 - 2011-07-02 16:03:25

ANo.2

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))

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

投稿日時 - 2011-07-02 15:17:21

お礼

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

投稿日時 - 2011-07-02 16:06:52

ANo.1

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

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

投稿日時 - 2011-07-02 15:07:58

お礼

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

投稿日時 - 2011-07-02 15:48:08

あなたにオススメの質問