Excel のクロス集計計算式

列に、「A.B.C.D.E」の項目と「1～11」の項目(点数)を集計してます。(約300行)
点数の「11」は無回答の場合入力してます。
この様な表で、それぞれA,B,C毎の平均点を出すにはどの様な数式で算出できますか？
※「11」は無回答なので、カウントしたくない。
以上、ご教示の程、よろしくお願いいたします

質問者からの補足コメント

• Aの平均点は○点、Bの平均点は○点と、
  それぞれ、A,B,C,D,Eごとに平均点を出したいです。

    補足日時：2018/07/06 09:11

No.1 回答者： runix2007 回答日時： 2018/07/05 21:26

11以外の和/(全データのセル個数 - 11の入ってるセルの個数) が求める式

=SUMIF(セル範囲,"<>11")/( COUNT(セル範囲)-COUNTIF(セル範囲,"=11"))

No.2 回答者： runix2007 回答日時： 2018/07/06 14:18

>A,B,C,D,Eごとに平均点を出したいです。

だったら、そうなるように式の範囲を設定すればいいでしょ
補足欄に書くのはやめてください

No.3 回答者： tom04 回答日時： 2018/07/06 23:31

こんばんは！

具体的な配置が判らないので、勝手にやってみました。
↓の画像のような配置の場合の一例です。

I2セルに
=AVERAGE(IF((OFFSET(A$2:A$500,,MATCH(H2,B$1:F$1,0),,1)<>"")*(OFFSET(A$2:A$500,,MATCH(H2,B$1:F$1,0))<11),OFFSET(A$2:A$500,,MATCH(H2,B$1:F$1,0))))

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで下へコピーしています。m(_ _)m

この回答へのお礼

ご回答、ありがとうございます。
かなり、解決に近づいた感じですが、難しい数式ですね…
エクセル初心者なので、ものにできるかな…(^o^;)
配列は「1」行ににA～Eがランダムに複数入力されて、(およそ200
列まで)
「2」行にに1～11の数字が入力されてるイメージです(「1」行と同じ列数)
もしこの配列でもアドバイス頂けると非常に助かります
よろしくお願いいたしますm(._.)m

お礼日時：2018/07/07 00:23

No.4 ベストアンサー 回答者： tom04 回答日時： 2018/07/07 00:55

No.3です。

＞1」行ににA～Eがランダムに複数入力されて、(およそ200列まで)
＞「2」行にに1～11の数字が入力されてるイメージです(「1」行と同じ列数)

↓のような感じになっているのですかね？

画像ではB5セルに
=SUMIFS($2:$2,$1:$1,A5,$2:$2,"<11")/COUNTIFS($1:$1,A5,$2:$2,"<11")
（今回は配列数式ではありません）

という数式を入れフィルハンドルで下へコピーしています。

※　細かい検証はしていませんので
仮に2行目に空白セルがあった場合にちゃんとお望みの結果になるかどうか判りません。m(_ _)m

この回答へのお礼

本当にありがとうございます。
めちゃくちゃ助かりました
現在、並べ替えして、11回答を除いたピボットテーブルを使って算出してたのが、自動で算出出来そうです
アンケート集計なので、年間通期の値も出せそうです
ありがとうございましたm(__)m

お礼日時：2018/07/07 14:59