[[20100519151234]]　『配列数式で平均値を出す』（COCO）　ページの最後に飛ぶ

『配列数式で平均値を出す』（COCO）

 よろしくお願いいたします。 
 課内のある数字に対しての平均を出そうとしています。
 U45には
 =ROUND(AVERAGE(U25,U27,U29,U31,U33,U35,U37,U39,U41,U43),1)
 と式が入っていました。
 1行おきの配列数式にしようと
 ｛=ROUND(AVERAGE(IF($S$25:$S$40="前",U25:U40,"")),1)｝
 と修正したのですが答えが「3.3」になってしまいます。
 26を7で割りたいのに8で割って平均が出てしまいます。
 （ちなみに配列数式にしないと3.7となります。）
 なんだかわからなくなり頭がパンク状態です。。

 説明がうまく出来ませんがよろしくお願いいたします。

     O   S        U         V       W   
 25 Aさん	前	4		
 26	後			
 27 Bさん	前	4	4	4
 28	後			
 29 Cさん	前	4	4	4
	後			
 31 Dさん		4	4	4

 33 Eさん		3	4	4

 35 Fさん		3	4	4

 37 Gさん		4	4	4

 39 Hさん			3	3
 40

 45 課平均値　前
 46 課平均値　後

 実際の表を見ないと何ともいえませんが、
 憶測で、空白セルを参照してるためではないでしょうか?

 こんな感じで解消できるかな
｛=ROUND(AVERAGE(IF(($S$25:$S$40="前")*(U25:U40<>""),U25:U40,"")),1)｝

 こんにちは。
AVERAGE関数のヘルプには、以下のように書かれています。
・引数として指定した配列またはセル範囲に文字列、論理値、空白セルが含まれている場合、これらは無視されます。
　ただし、数値として 0 (ゼロ) を含むセルは計算の対象となります。

 ＞ =ROUND(AVERAGE(U25,U27,U29,U31,U33,U35,U37,U39,U41,U43),1)
修正前の数式の場合、
U39以降のセルは空白なので、平均の計算からは除かれます。

 ＞｛=ROUND(AVERAGE(IF($S$25:$S$40="前",U25:U40,"")),1)｝
一方、修正後の数式の場合、IF(～)の部分は、
　{4;"";4;"";4;"";4;"";3;"";3;"";4;"";0;""}
のように計算されていますので、U39セルが空白ではなく、0として計算に含まれるのです。
（たとえ参照元が空白セルでも、参照先では、0になります）

 したがって、↑のななしさんのご回答のように、IF関数の条件に、U25:U40が空白でない(U25:U40<>"")という
条件を追加すれば、IF(～)の部分は以下のように、0ではなく""となり、平均計算から除外されます。
　{4;"";4;"";4;"";4;"";3;"";3;"";4;"";"";""}

 ちなみに以下のように微修正すると、同じ数式で、後やV列以降のの平均も計算できます。
  U45 {=ROUND(AVERAGE(IF(($S$25:$S$44=$S45)*(U$25:U$44<>""),U$25:U$44,"")),1)}
  ↑Ctrl+Shit+Enterで確定（配列数式）
（コタ）

 返信遅くなりすみません。
 ななしさん、コタさんありがとうございます。

 >空白セルが含まれている場合、これらは無視されます。
 >ただし、数値として 0 (ゼロ) を含むセルは計算の対象となります。
 　↑これは見ていたのですが
 >（たとえ参照元が空白セルでも、参照先では、0になります）
 　をわかっていませんでした。勉強になりました。

 教えていただいた式の中でわからないのですが

 >｛=ROUND(AVERAGE(IF(($S$25:$S$40="前")*(U25:U40<>""),U25:U40,"")),1)｝
　 の中の「*」と「<>」の意味がわかりません。
 また、コタさんの微修正の式と
 ななしさんの式をV,W・・・方向へフィルコピーするのでは
 V列以降の平均をだす際の意味が違うのでしょうか？

 よろしくお願いいたします。

 ＞ 「*」と「<>」の意味がわかりません。
単純に答えると、「*」：乗算（掛ける「×」）と、「<>」：不等号（「≠」）ということです。

 配列の乗算の場合、それぞれの要素同士の計算になります。

  ($S$25:$S$40="前")*(U25:U40<>"")
 = {1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0}
  *{1,0,1,0,1,0,1,0,1,0,1,0,1,0,0,0} ※Trueを1,Falseを0としています
 = {1,0,1,0,1,0,1,0,1,0,1,0,1,0,0,0}
                                ^    1*0=0

 ＞ V列以降の平均をだす際の意味が違うのでしょうか？
V列以降の方向は同じですね。
  V45 {=ROUND(AVERAGE(IF(($S$25:$S$40="前")*(V25:V40<>""),V25:V40,"")),1)}
  V45 {=ROUND(AVERAGE(IF(($S$25:$S$44=$S45)*(V$25:V$44<>""),V$25:V$44,"")),1)}

 「後」の平均を求める場合に、変わってきます。
  U46 {=ROUND(AVERAGE(IF(($S$25:$S$40="前")*(U26:U41<>""),U26:U41,"")),1)}
  U46 {=ROUND(AVERAGE(IF(($S$25:$S$44=$S46)*(U$25:U$44<>""),U$25:U$44,"")),1)}
（コタ）

コタさん、ありがとうございます。

 >「*」：乗算（掛ける「×」）はわかりましたが
 >「<>」：不等号（「≠」）ということです。
 がいまいちピンときません（泣）

  ($S$25:$S$40="前")*(U25:U40<>"")
  = {1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0}
   *{1,0,1,0,1,0,1,0,1,0,1,0,1,0,0,0} ※Trueを1,Falseを0としています
  = {1,0,1,0,1,0,1,0,1,0,1,0,1,0,0,0}
                                ^    1*0=0
 上記の仕組みはわかりました。（とてもわかりやすくしていただきありがとうございます。）

 ただ、この部分「(U25:U40<>"")」のU25:U40の値が何と不等号になるのでしょうか？
 できればもうちょっと言葉で表現していただけると助かります。

 初歩的な質問ですみませんがよろしくお願いいたします。

 ＞ 「(U25:U40<>"")」のU25:U40の値が何と不等号になるのでしょうか？
そのものずばりで、"" と比較しているのですが、つまりは、"" が何か？が
分からないということでしょうか。
これは、「長さ0の文字列」で、
　未入力セルと同様のもの（*）
です。

 どこかのセルに、="" と入力してみてください。
見た目は未入力の場合と、全く同じでしょう。
 それだけでなく、あるセルが未入力かどうかも、"" で判定できます。
以下のシートを再現してみてください。

  [R/C]  [A]  [B]
   [1]     4    1
   [2]     0    1
   [3]     _    0 ←「_」セルは未入力

  B1 =IF(A1<>"",1,0) →B1:B3フィルコピー

 ・1、2行目は、A列に「4」「0」が入力されているので、B列のIF関数の判定式で
 　A1<>""がTrueとなって（＝A1は""ではない）、結果が1になっています。
 ・ところが3行目は、A3が未入力なので、A3<>""がFalseとなって（＝A3は""である）、
　 結果が0になります。

 同様に、(U25:U40<>"")という式の場合、
　・入力のあるセルはTrue (=1)に、
　・入力のないセルはFalse(=0)に、
なり、
　{1,0,1,0,1,0,1,0,1,0,1,0,1,0,0,0}
という配列になるのです。
（コタ）

 （*）補足
もちろん、未入力セルと"" は、同じものではありません。
単に、未入力セル="" という数式を作ると、Trueという結果が返る、という意味で
「同様」と表現してるだけです。

 わかりましたぁぁぁ・・！！
 感謝・感謝です。

 ちなみに式は
 V45 {=ROUND(AVERAGE(IF(($S$25:$S$44=$S45)*(V$25:V$44<>""),V$25:V$44,"")),1)}
 こちらを使用して解決しました。

 コタさん、本当にありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20100519151234]] 『配列数式で平均値を出す』（COCO） ページの最後に飛ぶ

[[20100519151234]]　『配列数式で平均値を出す』（COCO）　ページの最後に飛ぶ