AVERAGEIFS関数について

質問

どなたか、お尋ねいたします。

AVERAGEIFS関数でA1～E1のセルに示した数値
A1　 B1 　　 C1　　D1　　E1
９　　20　　　21　　22　　31
二つの条件　10以下、30以上の数値を除く平均値　20、21、22　　　答え21.0
ただし、10と30は別の計算式から求めた値とします。
実際は20程の数値から以下、以上を除いた平均値を計算したいと思っています。
AVERAGEIFSで計算式の書き方を教えていただけませんか。
他の方法でも可能なら結構ですので、よろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

>ただし、10と30は別の計算式から求めた値とします。
F1=10,G1=30として

>AVERAGEIFSで計算式の書き方を教えていただけませんか。
=AVERAGEIFS(A1:E1,A1:E1,">"&F1,A1:E1,"<"&G1)

>他の方法でも可能なら結構ですので、よろしくお願いします。
=SUMIFS(A1:E1,A1:E1,">"&F1,A1:E1,"<"&G1)/COUNTIFS(A1:E1,A1:E1,">"&F1,A1:E1,"<"&G1)
または
=(SUMIF(A1:E1,"<"&G1,A1:E1)-SUMIF(A1:E1,"<"&F1,A1:E1))/(COUNTIF(A1:E1,"<"&G1,A1:E1)-COUNTIF(A1:E1,"<"&F1,A1:E1))
または
=SUMPRODUCT((A1:E1)*(A1:E1>F1)*(A1:E1<G1))/SUMPRODUCT((A1:E1>F1)*(A1:E1<G1))

お礼

早々に、ご解答いただきまして有難うございました。
多くの回答を頂き、問題が解決するとともに大変参考になりました。
またの機会がありましたらよろしくお願いいたします。

回答No.7

Nouble

まず書式から
AVERAGEIFS(平均を求める実範囲,
条件1を検査する範囲,検査条件1,
条件2を検査する範囲,検査条件2,
…
…)
(※注:値の前に比較子を書く、検査条件は文字列で与える、127個まで指定可能)

例
AVERAGEIFS(A1:E1,A1:E1,"<E1","A1<")　…　Ng

AVERAGEIFS(A1:E1,A1:E1,"<E1",">A1")　…　OK

恐らくは
最頻値付近の、平均が
欲しい
の、ですよね？

しかし
此の、量の
サンプル数、では
重複出現は、望めません
ので
頻出度に、よる
排除は
又、別途
ご用命、ください

さて、では
違う、方法

全数値を
一列に、並べ
全体要員数の
最大側と、最小側、
1/n個ずつ、排除し
平均を、取る
(メディアン的、排除)
(※注：nはF3に記載されている物とする)

此の時何個排除すべきかは
CEILING(COUNT(A1:E1)/F3,1)
で解る
故に、
最小からｎ個目は
SMALL(A1:E1,CEILING(COUNT(A1:E1)/F3,1))

最大からｎ個目は
LARGE(A1:E1,CEILING(COUNT(A1:E1)/F3,1))
で、解る

実際の平均を求める式は
=AVERAGEIFS(A1:E1,A1:E1,"<"&LARGE(A1:E1,CEILING(COUNT(A1:E1)/F3,1)),A1:E1,">"&SMALL(A1:E1,CEILING(COUNT(A1:E1)/F3,1))
と、なる

最小値から最大値までの幅を歪度を考慮せず
1/n割ずつ、排除する
(ｎσ的、排除)

此の、時
1/n割は、
最大値から、最小値までの、
幅を
ｎで、割り
最小値を
足した、値に
なる

よって、
最小値側の
排除、しきい値は

(MAX(A1:E1)-MIN(A1:E1))/F3+MIN(A1:E1)

最大値側の、しきい値は
(MAX(A1:E1)-MIN(A1:E1))/F3*(F3-1)+MIN(A1:E1)

又は
=MAX(A1:E1)-(MAX(A1:E1)-MIN(A1:E1))/F3

実際の、式は
=AVERAGEIFS(A1:E1,A1:E1,">"&(MAX(A1:E1)-MIN(A1:E1))/F3+MIN(A1:E1),A1:E1,"<"&(MAX(A1:E1)-MIN(A1:E1))/F3*(F3-1)+MIN(A1:E1))
と、なります

お礼

早々にご解答を頂きまして有難うございました。
大変参考になりました。
またの機会がありましたらよろしくお願いいたします。

回答No.6

msMike

=AVERAGEIFS(A1:E1,A1:E1,">10",A1:E1,"<30")

お礼

早々にご解答を頂きまして有難うございました。
大変参考になりました。
またの機会がありましたらよろしくお願いいたします。

回答No.5

imogasi

直接の回答ではないのですが、　参考に。
異常値を除いた平均値－TRIMMEAN関数
というのがあります。
Googleで「最大値 最小値を除いた平均」で照会。
http://www.relief.jp/docs/001518.html　など
ーー
この関数に持ち込んで（データ割合で排除という風に考え方を変えて）、解決できないですか。

回答No.4

bunjii

>ただし、10と30は別の計算式から求めた値とします。
別の計算式が次のように設定されていると考えて良いでしょうか？
A2=MIN(A1:E1)+1 → 10
B2=MAX(A1:E1)-1 → 30
「10以下と30以上を除く」と言うことは「10を超え30未満」と言うことになります。
∴、提示の範囲（A1:E1）を対象のとき次の数式で良いでしょう。
=AVERAGEIFS(A1:E1,A1:E1,">"&A2,A1:E1,"<"&B2)

>実際は20程の数値から以下、以上を除いた平均値を計算したいと思っています。
上記の数式の範囲を変更して応用してください。

>他の方法でも可能なら結構ですので、よろしくお願いします。
意味不明です。
「結構です」には「それでよろしいです」と「必要ないです」の2つの意味があります。
何方でしょう？
他の方法では複数の関数を使うことになり不合理です。
Excel 2003以前の組み込み関数にAVERAGEIFS関数が無いので他の関数を組み合わせて算出していたことがあります。
複雑になる方法は不要かと思います。

お礼

早々にご解答を頂きまして有難うございました。
大変参考になりました。
またの機会がありましたらよろしくお願いいたします。

回答No.2

yumi0215

=IF(A1:E1=10>=30,ROUNDUP(AVERAGE(A1:E1),1),0)
このような関数はいかがでしょう。
小数点以下が必要な場合は、セルの書式設定→数値→小数点以下の桁数指定で表示となりますが。
小数点以下の四捨五入が不要な場合は
=IF(A1:E1=10>30,AVERAGE(A1:E1),0)
となります。

お礼

早々に、ご解答いただきまして有難うございました。
問題が解決し大変参考になりました。
またの機会がありましたらよろしくお願いいたします。

回答No.1

msMike

=SUMPRODUCT((A1:E1>10)*(A1:E1<30)*(A1:E1))/SUMPRODUCT((A1:E1>10)*(A1:E1<30))

お礼

早々にご解答を頂きまして有難うございました。
大変参考になりました。
またの機会がありましたらよろしくお願いいたします。