Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
同じIDの平均額を計算したい
投稿日時: 19/03/01 16:02:05
投稿者: EXman

いつも大変お世話になっております。
エクセルにて、商品の平均金額算出を行いたいのですが、
どのように行ったらよいかご教示頂けますと幸いです。
関数初心者です。
 
どうぞよろしくお願いします。
 
 
【データ例:】
 
A        B        C        D
商品    金額    数量    平均
A        1200    10
A        1500    5
B        1100    7
C        1700    8
C         500    2
B         300    20
B        1900    12
D        2200    3
A        5600    16
D        4800    20
 
 
商品Aの平均金額を計算し終えたら、
Aの記載のあるD列にはすべて平均値の計算結果の数値を表示。
 
A列の商品ID出現の数、金額、数量の規則性はありません。

回答
投稿日時: 19/03/01 17:11:39
投稿者: WinArrow
投稿者のウェブサイトに移動

 
説明文で理解できないところがあります
 
>商品Aの平均金額を計算し終えたら
これは、どのようなことですか?
具体的に、何をするのか?
 
>Aの記載のあるD列にはすべて平均値の計算結果の数値を表示。
 
これについても、理解できません。
何をしたらよいのでしょうか?

回答
投稿日時: 19/03/01 17:23:09
投稿者: Suzu

何の平均を出したいのか 、相加平均 相乗平均なのか。。
  
説明が不足していますが、
  
金額*数量 を、商品毎に合計。 その値を、 商品毎の数量で除した値。
  
  
Aなら、
(1200*10+1500*5+560)/(10+5+16)
  
  
だとして、
D2 に
=SUMPRODUCT(N($A$2:$A$11=$A2),$B$2:$B$11,$C$2:$C$11)/SUMPRODUCT(N($A$2:$A$11=$A2),$C$2:$C$11)
を入れて、crl + shift + Enter で配列関数 として確定。
  
さらに、D2を下にオートフィル。

投稿日時: 19/03/01 17:25:31
投稿者: EXman

WinArrow さんの引用:

説明文で理解できないところがあります
 
>商品Aの平均金額を計算し終えたら
これは、どのようなことですか?
具体的に、何をするのか?
 
>Aの記載のあるD列にはすべて平均値の計算結果の数値を表示。
 
これについても、理解できません。
何をしたらよいのでしょうか?

 
 
下手な説明ですみません。
 
A列にある、A(ID)であればA(ID)すべて、
B(ID)であればB(ID)のすべての金額を合計した後に、
 
各IDのC列の数量を合計した後、各IDの合計金額を各IDの合計数量で割った数値を
各々のIDのDの列に表示。
というような内容なのですが、
お伝えしきれているかわかりませんが、
今一度ご確認よろしくお願いします。

回答
投稿日時: 19/03/01 18:39:40
投稿者: WinArrow
投稿者のウェブサイトに移動

なぜ、D列セルに計算結果を表示させるのかしら
=B2/C2
ならば理解できるが・・・
  
商品ID=A
という行は複数あります、その各々に同じ結果が表示されることになりますが・・・・
無駄というか、見難くないですか?
  
商品ID毎にまとめた方がよいのでは?
  
例えば
「データ」タブの「統合」を使うと、
 商品ID毎の金額合計、数量合計を、抽出できます。
  
操作の例
セルE1にカーソルを置いて
「統合」をクリック、
 集計の方法:合計
 統合元範囲:A1〜C11を選択し、追加
 統合の基準:左側
 「OK」
  
E列に商品ID
 F列に金額合計
 G列に数量合計
 
 
若し、元データが変更される可能性があるならば、
金額合計セル、数量合計セルに
SUMIF関数 を入力すれば、リンクすることができます。
 

セルF2
=SUMIF($A2:$A$11,E2,$B$2:$B$11)
セルG2
=SUMIF($A2:$A$11,E2,$C$2:$C$11)
 
下へフィルドラッグします。
 
あとは
金額合計÷数量合計 →平均が求められますよね・・・

回答
投稿日時: 19/03/02 03:59:01
投稿者: コナミ

AVERAGEIFではできませんか?

回答
投稿日時: 19/03/02 07:55:43
投稿者: WinArrow
投稿者のウェブサイトに移動

コナミ さんの引用:
AVERAGEIFではできませんか?

 
これが使えるとよいと思いましたが、
第3引数の平均対象範囲の指定を
どのように指定するとよいでしょうか?

回答
投稿日時: 19/03/02 08:02:12
投稿者: コナミ

あぁ、表のレイアウトを見誤ってましたが、作業列で行ごとの合計をだせば後はAVERAGEIFで大丈夫ですよね。

回答
投稿日時: 19/03/02 10:27:34
投稿者: 半平太

>作業列で行ごとの合計をだせば後はAVERAGEIFで大丈夫ですよね。
どうやるのは私には分からなかったです。(各行合計が各1個換算での計算になりませんか?)
 
D2セル =SUMPRODUCT((A$2:A$11=A2)*B$2:B$11,C$2:C$11)/SUMIF(A:A,A2,C:C)
 
下にコピー

回答
投稿日時: 19/03/02 20:58:27
投稿者: WinArrow
投稿者のウェブサイトに移動

Suzuさんの
>Aなら、
>(1200*10+1500*5+560)/(10+5+16)
 
この説明通りだとしたら
  
↓の数詞委になると思います。
 
SUMIF関数を使った例
  
セルD2に
=SUMIF($A$2:$A$11,A2,$B$2:$B$11)/SUMIF($A$2:$A$11,A2,$C$2:$C$11)
下へフィルドラッグ
  
ただ、同じ結果が重複しているので、見ずらいとおもいます。

回答
投稿日時: 19/03/03 10:41:58
投稿者: Suzu

引用:
>Aなら、
>(1200*10+1500*5+560)/(10+5+16)

 
上記の数式が違いましたね。
 
(1200*10+1500*5+560*16)/(10+5+16) = 109100/31 = 3519.…
 
でした。
 
当方は上記の計算を、SUMIFと配列関数でいけるかなと思ったのですが 組むことができず
SUMPRODUCT と N を使った次第です。
 
ところで、WinArrow さん の式だと、(1200+1500+560)/(10+5+16) = 267.… になります。
考え方が違ったのですかね?

回答
投稿日時: 19/03/03 13:26:03
投稿者: WinArrow
投稿者のウェブサイトに移動

Suzuさん、
 
大変、申し訳ございません。
早とちりしたうえ、混乱していました。
 
Suzuさんの解釈は
Σ(金額x数量)/Σ(数量)
だと思うのですが、
 
質問者さんの説明の中に

引用:
A列にある、A(ID)であればA(ID)すべて、
B(ID)であればB(ID)のすべての金額を合計した後に、
  
各IDのC列の数量を合計した後、各IDの合計金額を各IDの合計数量で割った数値を
各々のIDのDの列に表示。
というような内容なのですが、
お伝えしきれているかわかりませんが、

と書かれていました。
これを次のように解釈しました。
Σ(金額)/Σ(数量)
 
 

投稿日時: 19/03/04 09:45:24
投稿者: EXman

WinArrow さんの引用:
なぜ、D列セルに計算結果を表示させるのかしら
=B2/C2
ならば理解できるが・・・
  
商品ID=A
という行は複数あります、その各々に同じ結果が表示されることになりますが・・・・
無駄というか、見難くないですか?
  
商品ID毎にまとめた方がよいのでは?
  
例えば
「データ」タブの「統合」を使うと、
 商品ID毎の金額合計、数量合計を、抽出できます。
  
操作の例
セルE1にカーソルを置いて
「統合」をクリック、
 集計の方法:合計
 統合元範囲:A1〜C11を選択し、追加
 統合の基準:左側
 「OK」
  
E列に商品ID
 F列に金額合計
 G列に数量合計
 
 
若し、元データが変更される可能性があるならば、
金額合計セル、数量合計セルに
SUMIF関数 を入力すれば、リンクすることができます。
 

セルF2
=SUMIF($A2:$A$11,E2,$B$2:$B$11)
セルG2
=SUMIF($A2:$A$11,E2,$C$2:$C$11)
 
下へフィルドラッグします。
 
あとは
金額合計÷数量合計 →平均が求められますよね・・・

 
 
大変お世話になっております。
SUMIFの基本的な部分を理解する事が出来ました。
とても参考になります。
ありがとうございます。

投稿日時: 19/03/04 09:46:34
投稿者: EXman

コナミ さんの引用:
AVERAGEIFではできませんか?

 
 
 
大変お世話になっております。
AVERAGEIF、今回はSUFIFで対応しました。今後参考にさせて頂きます。
ありがとうございます。

投稿日時: 19/03/04 09:50:07
投稿者: EXman

EXman さんの引用:
いつも大変お世話になっております。
エクセルにて、商品の平均金額算出を行いたいのですが、
どのように行ったらよいかご教示頂けますと幸いです。
関数初心者です。
 
どうぞよろしくお願いします。
 
 
【データ例:】
 
A        B        C        D
商品    金額    数量    平均
A        1200    10
A        1500    5
B        1100    7
C        1700    8
C         500    2
B         300    20
B        1900    12
D        2200    3
A        5600    16
D        4800    20
 
 
商品Aの平均金額を計算し終えたら、
Aの記載のあるD列にはすべて平均値の計算結果の数値を表示。
 
A列の商品ID出現の数、金額、数量の規則性はありません。

 
いつも皆様大変お世話になっております。
色々ご教示頂き大変助かりました。
今回はSUMIFとSUMPRODUCTを使用して解決できました。
少しづつ進歩しよう思いますので、今後も何かありましたら、
どうぞよろしくお願いします。