エクセルの関数（AVERAGE）について質問です。

質問

エクセルの関数（AVERAGE）について質問です。

エクセル2007を使ってこのような、表を作りました。

　　　　A　　　　　B　　　　　C　　　　　　D
１　　入院日　　　退院日　　在院日数　　　区分
２　2010/3/2　　2010/5/8　　68　　　　　(1)
３　2010/4/18　 2010/5/9　　22　　　　　(5)
４　　　　　　　　　　…

と、100行分まで表があります。

Cの『在院日数』は「退院日－入院日＋１」で計算します。
計算式は『=IF(B1="","",B1-A1+1)』といれてみました。
在院日数を求めるのはこれでうまくいくのですが、
問題は、この在院日数の平均を求めることがうまくいかないのです。

以前、エクセル2003を使っていたときは
普通に『=AVERAGE(C2:C101)』でうまくいっていたと思うのですが
エクセル2007にしてからは
空白のセルも、計算式が入っているせいか、数値があると認識してしまうらしく
80行までしか入力されていない時は、81から100行までの分を0と考え
1行目から100行目までの値を足し、100でで割られた値が出てきてしまいます。
どうしたら、空白のセルは計算せずに平均値を出すことが出来ますか？

それと、もう一つ質問があり
区分では、(1)～(8)を入力するのですが
この、区分の(1)と(3)だけを選んで平均値を出したい
ということもしたいのですが
どうしたらよいでしょうか？
(1)だけなら、AVERAGEIFを使えばと思うのですが
(1)と(3)という時は、どういう風にしたらいいのか教えて下さい。

回答No.1

tom04 · ベストアンサー

こんにちは！
Excel2007に関しては手元にないので詳しく判りませんが、

AVERAGE関数は空白セルや文字列が入っているセルは無視され、数値だけのセルで平均が出ると思いますので、
質問文にあるように普通にAVERAGE関数で問題ないと思うのですが・・・

この件に関しては理由が判らないのでごめんなさい。

次に区分を選んでの平均の方法の一例です。
当方使用のExcel2003の場合です。

↓の画像でG5セルに
=(SUMIF(D2:D100,F5,C2:C101)+SUMIF(D2:D100,F6,C2:C101))/(COUNTIF(D2:D101,F5)+COUNTIF(D2:D101,F6))

という数式を入れています。
これでF5・F6セルに平均を出したい区分を入力すればその平均が表示されると思います。
尚、F5・F6セルの一つだけでもその区分の平均が表示されますが、
両方とも空白の場合はエラーになります。

そして余計にお世話かもしれませんが、G1セルには普通に
=AVERAGE(C2:C101)

という数式を入れています。

そして↓のURLは参考にならないでしょうか？
http://www.becoolusers.com/excel/averageifs.html

この程度ですが
参考にならなかったらごめんなさいね。m(__)m

回答No.8

esupuresso

以下の表より説明
セル【C2】に　=IF(OR(A2="",B2=""),"",B2-A2+1)　の式を入力後【C7】まで数式のコピーをします
セル【G8】に　=AVERAGEA(AVERAGEIF(D2:D7,G7,C2:C7),AVERAGEIF(D2:D7,H7,C2:C7))　の式を入力

セル【G7】及び【H7】は手入力で変更することができ、区分別平均を出す事ができます

回答No.7

imogasi

エクセル２００７でテストできないが
だまされたと思って、下記を改めてやってください。
２００７になって、仕様変更されるような個所ではないと思うから。
例データ
A列　　B列　　　C列
1 1 1
3 2 2
5
2 1 2
3
C列は式　=IF(B1="","",A1-B1+1)　と入れて下方向に式複写
それ以外のセルに　=AVERAGE(C1:C5)　1.666666667
その下に　=5/3　　1.666666667
になるかどうか。なるようなら、質問者の予想外の点に問題があると思う。
思っていたように式が杯って居ないとか。
ーー
ツールーオプションーウインドウオプションーゼロ値は関数の解説書に注意が載っているが
空白ではないケースなので問題外だろう。
セルの値が０なのにゼロ値を空白に表示しておれば、質問のようになるが。
B列未入力データ行のC列は、質問者の意に反して、０になっていませんか。

回答No.6

Cupper

ああ、ごめんなさい。
AVERAGEIFS関数 の条件設定は and でしたね。
まえの投稿は無視してください。

回答No.5

Cupper

おかしいですね。本当に空白になるようにしていますか？
では、AVERAGEIF関数 で 「値がゼロより大きい」 を条件に平均値を出してみてはどうですか。

　＝AVERAGEIF(条件の範囲，条件，平均の範囲)

こう使います

　＝AVERAGEIF(C2:C101,">0",C2:C101)

などとしてみてください。（未検証）

＞(1)だけなら、AVERAGEIFを使えばと思うのですが
＞(1)と(3)という時は、どういう風にしたらいいのか教えて下さい。

AVERAGEIFS関数

　＝AVERAGEIFS(平均範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)

こっちはこう使います。

　＝AVERAGEIFS(C2:C101,D2:D101,"(1)",D2:D101,"(3)")

などとしてみてください。（これも未検証）

※これらの関数は Excel2007 で追加されたものです。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

あら、最期の数式の範囲がずれている

=SUM(SUMIF(D2:D101,{"(1)","(3)"},C2:C101)/SUM(COUNTIF(D2:D101,{"(1)","(3)"})))
1か所だけど修正、配列数式。
{"(1)","(3)"}の部分をセル範囲で参照にするには、配列として確定させるために
[Ctrl]+[Shft] ＋[Enter] で確定させる必要があります

回答No.3

某HN クロメート(Chromate)（@CoalTar）

>空白のセルも、計算式が入っているせいか、数値があると認識してしまうらしく
2007でも考えにくいですね。

81行め以降も数式が正しいか[Ctrl]+[Shift]+[@]
(もう一度押すと戻る)
確認しましょう

[Excelのオプション]から
詳細設定の[レ]ゼロ値のセルにゼロを表示する
にチェックが入っているか？

[F9]再計算してみるとか

質問文の数式は
＞計算式は『=IF(B1="","",B1-A1+1)』といれてみました
ではなく
C2セルに =IF(B2="","",B2-A2+1)
下へオートフィル ですね

まだ、何か見落としがあるのかなあ。

=SUM(SUMIF(D2:D100,{"(1)","(3)"},C2:C101)/SUM(COUNTIF(D2:D101,{"(1)","(3)"})))

回答No.2

KURUMITO

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

=SUM(C2:C200)/COUNTIF(C2:C200,">0")

また（１）と（３）だけの平均ですが

=(SUMIF(D2:D200,"(1)",C2:C200)+SUMIF(D2:D200,"(3)",C2:C200))/(COUNTIF(D2:D200,"(1)")+COUNTIF(D2:D200,"(3)"))