ウォーターサーバーとコーヒーマシンが一体化した画期的マシン >>

列に、「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

A 回答 (4件)

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
「Excel のクロス集計計算式」の回答画像4
    • good
    • 3
この回答へのお礼

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

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

こんばんは!



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

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
「Excel のクロス集計計算式」の回答画像3
    • good
    • 3
この回答へのお礼

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

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

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



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

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




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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルの表の集計をVBAでやりたいのです。

エクセルのsheet1にこのような表があります。
A B C D E
1 名称 金額
2 あああああ 10000
3 あああああ 12000
4 あああああ 9000
5 いいいいい 9500
6 いいいいい 11000
7 ううううう 15000
8 えええええ 12000
9 おおおおお 10000
10 おおおおお 14000
11 かかかかか 13000
12 ききききき 800

以下多数

この表で、それぞれ同じ名称の個数と合計金額を求めたいのです。
たとえば「ああああ」なら個数 3、合計 31000 と、D4とE4に、
「いいいい」なら個数 2、合計 25000 と、D6とE6に入れたいのです。
実際の表はデータ件数が1万件を越えますのでいちいち手で入れるわけにはいきません。
VBAでやるにはどう記述したらいいでしょうか?
どうかお助けください。よろしくお願いします。

エクセルのsheet1にこのような表があります。
A B C D E
1 名称 金額
2 あああああ 10000
3 あああああ 12000
4 あああああ 9000
5 いいいいい 9500
6 いいいいい 11000
7 ううううう 15000
8 えええええ 12000
9 おおおおお 10000
10 おおおおお 14000
11 かかかかか 13000
12 ききききき 800

以下多数

この表で、それぞれ同じ名称の個数と合計金額を求めたいのです。
たとえば「ああ...続きを読む

Aベストアンサー

A列は名称でB列は金額でよろしいんですよねというか
その前提で書きましたが。
デバッグはしてません。またオーバーフロー等あると思うのでそのあたりはご自分で。

Sub 集計()
Dim i, MyTotal As Double, MyCount As Double
MyTotal = Sheets(1).Range("A2")(1, 2)
MyCount = 1
For i = 2 To 65535
If Sheets(1).Range("A2")(i) = "" Then Exit For
If Sheets(1).Range("A2")(i) = Sheets(1).Range("A2")(i - 1) Then
MyTotal = MyTotal + Sheets(1).Range("A2")(i, 2)
MyCount = MyCount + 1
Else
Sheets(1).Range("A2")(i - 1, 4).Formula = MyCount
Sheets(1).Range("A2")(i - 1, 5).Formula = MyTotal
MyTotal = Sheets(1).Range("A2")(i, 2)
MyCount = 1
End If
Next i
Sheets(1).Range("A2")(i - 1, 4).Formula = MyCount
Sheets(1).Range("A2")(i - 1, 5).Formula = MyTotal
End Sub

A列は名称でB列は金額でよろしいんですよねというか
その前提で書きましたが。
デバッグはしてません。またオーバーフロー等あると思うのでそのあたりはご自分で。

Sub 集計()
Dim i, MyTotal As Double, MyCount As Double
MyTotal = Sheets(1).Range("A2")(1, 2)
MyCount = 1
For i = 2 To 65535
If Sheets(1).Range("A2")(i) = "" Then Exit For
If Sheets(1).Range("A2")(i) = Sheets(1).Range("A2")(i - 1) Then
MyTotal = MyTotal + Sheets(1).Ran...続きを読む

Qエクセルの関数でアンケートのクロス集計をしたいです

以下、表(1)のような表があるとき、表(2)のように算出できる関数を教えてください。
※表(1)、(2)の画像を添付しました。


【表(1)】
A列 →回答者ID
B~F列 →回答者の属性(性別、部署等を数値化)
J~R列以降 →I1行の質問番号ごとに回答した選択肢番号

【表(2)】
属性ごとの回答を集計したもの。
(属1-1の場合、B2~G2に選択肢番号を設定しています)


以上の場合で、例えば、
表(1)の属1(B列)で1を選び、かつ、質問1(J列)で1を選んだ回答者の数について、
表(2)の表に入れる式(この場合、表(2)の属1-1)が知りたいです。
実際(2)は、シートを分けて作成したいと考えています。


また、こちらの都合で、今回、ピボットテーブルやマクロ、VBAでなく、
関数で作成する必要があり困っています。面倒なことになり大変恐縮ですが、
ぜひお知恵を拝借願えればありがたいです。

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

Aベストアンサー

属性1-1の表計算式(属性番号はC1セルにあるものとして)
=SUMPRODUCT((Sheet1!$B$2:$B$11=$C$1)*(OFFSET(Sheet1!$I$2,0,$A3,10)=B$2))

=SUMPRODUCT((属性範囲=$C$1)*(回答範囲=B$2))

Sheet1の属性範囲と回答範囲に名前を定義するとわかりやすくなるとおもう

属性1-2以降の表に関しては属性範囲と属性番号が異なるので
(Sheet1!$B$2:$B$11=$C$1)

この部分の計算式の変更が必要

Qexcel vba 複数項目の集計

EXCEL2003 VBAにてマクロを作成しているのですが、作成日程が迫っているにも関わらず、
すぐ行き詰ってしまいます。どうかご指導お願い致します。

以下のような事をしたいのですが、できる限り高速で最も効率の良い方法を教えていただきたいと
思っております。よろしくお願い致します。

あるSheetに以下のようにデータが登録されていて、
A       B        C
りんご    赤       200
なし     黄色      100
りんご    緑       100
メロン    緑       10
なし     黄色      200
りんご    緑       500

これを集計すると、
りんご  赤  200
りんご  緑  600
なし   黄色 300
メロン  緑  10
と、結果を返したいと考えています。

数字を合計するのはA列とB列がともに一致した時のみです。

最初は単純に何回もForNextで処理しようと思ったのですが、行数が
多くなると繰り返し回数も多くなり、時間もかかってしまうので、
別の方法でもっといい方法があればと思い、質問させていただきました。
宜しくお願いします。

EXCEL2003 VBAにてマクロを作成しているのですが、作成日程が迫っているにも関わらず、
すぐ行き詰ってしまいます。どうかご指導お願い致します。

以下のような事をしたいのですが、できる限り高速で最も効率の良い方法を教えていただきたいと
思っております。よろしくお願い致します。

あるSheetに以下のようにデータが登録されていて、
A       B        C
りんご    赤       200
なし     黄色      100
りんご    緑       100
メロン    緑   ...続きを読む

Aベストアンサー

自前で検索するのをやめて Scripting.Dictionaryオブジェクトに任せるなら

Sub Test()
  Dim dicName As New Dictionary
  Dim r As Range
  Dim dicCor As Dictionary
  Dim sName, sColor, obj
  Dim nn As Integer

  ' データの集計
  For Each r In Range("A2", Range("A65536").End(xlUp))
    ' りんご、なし、メロンなどを取得
    sName = r.Value
    ' 色を取得
    sColor = r.Offset(, 1).Value
    ' 値段を取得
    nn = r.Offset(, 2).Value
    ' dicNameに登録済みか検査
    If dicName.Exists(sName) = False Then
      ' 未登録なら 色、値段を登録
      Set dicCor = New Dictionary
      dicCor.Add sColor, nn
      dicName.Add sName, dicCor
    Else
      ' 登録済みの場合 色情報を検査
      Set dicCor = dicName(sName)
      If dicCor.Exists(sColor) = False Then
        ' 色情報が未登録なら 新規登録
        dicCor.Add sColor, nn
      Else
        ' 色情報があるなら 値段を更新
        nn = dicCor(sColor) + nn
        dicCor(sColor) = nn
      End If
      ' 色情報を更新
      Set dicName(sName) = dicCor
    End If
  Next

  ' 出来上がったデータを表示
  For Each sName In dicName
    Set dicCor = dicName(sName)
    For Each sColor In dicCor
      n = dicCor(sColor)
      ' セルに転記するなら Rangeなどに置き換えましょう
      Debug.Print sName, sColor, nn
    Next
  Next
End Sub
といった具合で ・・・

自前で検索するのをやめて Scripting.Dictionaryオブジェクトに任せるなら

Sub Test()
  Dim dicName As New Dictionary
  Dim r As Range
  Dim dicCor As Dictionary
  Dim sName, sColor, obj
  Dim nn As Integer

  ' データの集計
  For Each r In Range("A2", Range("A65536").End(xlUp))
    ' りんご、なし、メロンなどを取得
    sName = r.Value
    ' 色を取得
    sColor = r.Offset(, 1).Value
    ' 値段を取得
    nn = r.Offset(, 2).Value
    ...続きを読む

Qエクセルで、列内が同じ名前を集計する方法は?

エクセルで以下のような表を作成したとします。

      A列   B    C    D
 1行   名前   項目   金額
 2    すずき  あいう  100
 3    すずき  かきく  200
 4    すずき  さしす  450
 5    さとう  あいう  150
 6    さとう  さしす  200
 7    はやし  あいう  250 
 8    はやし  かきく  170
 9    はやし  たちつ  980

B列の名前が同じ者の、金額の合計をD列に出したいのです。
D4に すずきの合計 750、D6にさとうの合計 350、
D9にはやしの合計 1150  というふうに。
全部で100人分位、データ行数は15000ほどあるのですが
これをさっとできるようにはどのようにしたら良いのでしょうか?

Aベストアンサー

次の方法は如何でしょうか。
D2に=IF(COUNTIF(A:A,A2)=COUNTIF($A$2:A2,A2),SUMIF(A:A,A2,C:C),"")を入力→D2を選択→フィルハンドル(D2セル枠右下の■)をダブルクリック
但し、C列途中に空白がある場合、その直前までしかコピーされませんので、空白セル前のセルを下方向にコピー後、同様にフィルハンドルをダブルクリックし下さい。

Q仕切り価格の意味を教えて下さい。

経理の方と話をしていると「仕切り価格」という言葉が出ました。

調べても上手く理解出来ません。
頭の悪い私にでも分かるように具体例を挙げて教えて頂けると大変助かります。


よろしくお願い申し上げます。

Aベストアンサー

売るときの値段です。

定価10,000円だとしたら、
大量に仕入れてくれるA社には仕切り60%、6,000円で販売、
そこそこ仕入れてくれるB社には仕切り80%、8,000円で販売、
一般の方には、仕切り無し、つまりは定価の10,000円で販売。

ということになります。

もちろん仕切りが仕入れ値を割ってはいけません、損しますから。
そんな感じでゆるく考えてください。

Qエクセル クロス集計からのデータ抽出、集計方法

下記の様なエクセルでクロス集計された結果についてデータを抽出、集計する方法を教えてください。

- a b c d e
A 1 0 0 5 0
B 1 0 0 0 0
C 0 3 0 0 0
D 0 0 0 1 1

実際のデータでは、0は空欄になっています。

上記のエクセルデータを下記の様に集計したいです。

データを含む項目
A a, d
B a
C b
D d, e

列、行ともに100以上データがあり、効率的にデータを集計する方法を教えて頂きたいです。
宜しくお願い致します。

Aベストアンサー

[No.4補足]へのコメント、
》 2の式を入れましたが上手く行きませんでした。
》 #VALUE!となってしまいます。
失礼しました。
ステップ2に書いた式の直後に次の一文を書き漏らしていました。
⇒【お断り】上式は必ず配列数式として入力のこと
ご存知かも知れませんが、入力法が、下記の通り、通常とは異なります。

1.該当式をコピーして指定のセルに入力(でも、まだ Enterキーを叩かない!)
 ̄ ̄(#VALUE!となっていたら、そのセルを選択して、F2キーをツンして直ぐに)
2.Ctrl+Shift+Enter を「エイヤッ!」と叩き付け
3.[数式バー]を眺めて、下記のように、入力した式が { }で囲まれていることを確認

{=IF(COLUMN(A1)>$H2,"",INDEX($A$1:$F$1,SMALL(IF($B2:$F2>0,COLUMN($B2:$F2),""),COLUMN(A1))))}

【お断り】決して、上以外の方法で { }で囲んではなりませぬ。


人気Q&Aランキング