【再投稿】Qエクセルで複雑な条件で平均値を求めたい

質問

皆様初めまして。
エクセルを使って、以下の平均値を求めたいのですが、計算式の組み合わせが
よく分からず困っております。

AVERAGEIFとCOUNTIFをうまく組み合わせればいいのではないかと
思っておりますが、条件が複雑でうまくいきませんでした。
よい方法をご存じの方がいらっしゃれば、アドバイスをお願いします。

＜希望＞
・各担当者が担当した各業務ごとの対応時間を合計し、平均値を求める
・F列の(1)と(2)は以下の業務をひとまとめとし、担当者が対応した業務項目と
　それにかかった対応時間を合計し、平均値として計算する。

(1)取次ぎ、転送、案内、相談、指示、折り返し
(2)保留、見積り、修理、キャンセル

●業務種別
取次、転送、案内、相談、指示、折り返し、保留、見積り、修理、キャンセル

I2で求める値は以下の選択範囲の中から
田中さんが対応した「取次ぎ」の対応時間を合計し
平均値を求める。

＜選択範囲＞
（氏名　　）B2～B21
（対応時間）C2～C21
（項目　　）D2～D21

I8で求める値は以下の選択範囲の中から
田中さんが対応したH2～H7の項目の全てを合計し
平均値を求める。

＜選択範囲＞
（氏名　　）B2～B21
（対応時間）C2～C21
（項目　　）D2～D21

説明が分かりにくいですが、よろしくお願いします。

回答No.6

kagakusuki

もし、H列の「項目」欄の値が「平均」である場合と、そうでない場合において、それぞれ別の関数を使うという場合には、以下の様にして下さい。

　まず、I2セルに次の様な関数を入力して下さい。

=IF(OR($H2="",I$1=""),"",IFERROR(AVERAGEIFS($C:$C,$B:$B,I$1,$D:$D,$H2),"-"))

　次に、I2セルをコピーして、I2～L7のセル範囲とI9～L12のセル範囲に貼り付けて下さい。
　次に、I8セルに次の様な関数を入力して下さい。

=IF(OR($H8="",I$1=""),"",IFERROR(SUMPRODUCT(SUMIFS($C:$C,$B:$B,I$1,$D:$D,INDEX($H$1:$H8,MATCH("*?",$F$1:$F8,-1)):$H8)*1)/SUMPRODUCT(COUNTIFS($B:$B,I$1,$D:$D,INDEX($H$1:$H8,MATCH("*?",$F$1:$F8,-1)):$H8)*1),"-"))

　次に、I8セルをコピーして、J8～L8のセル範囲とI13～L13のセル範囲に貼り付けて下さい。

　尚、14行目の「合計」欄の関数に関しては特に変更点が御座いませんので割愛致します。

回答No.5

kagakusuki

回答No.2、3です。

　もし、対応時間を合計が0の場合に、データ無しという意味で「-」などを表示させたいという場合には、I2セルに入力する関数を

=IF(OR($H2="",I$1=""),"",IFERROR(SUMPRODUCT(SUMIFS($C:$C,$B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1)/SUMPRODUCT(COUNTIFS($B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1),"-"))

に、I14セルに入力する関数を

=IF(I$1="","",IF(SUM(I$2:I$13),SUMIF($B:$B,I$1,$C:$C),"-"))

に、それぞれ変更して下さい。
　又、「-」すら表示させたくないという場合には、I2セルに入力する関数を

=IF(OR($H2="",I$1=""),"",IFERROR(SUMPRODUCT(SUMIFS($C:$C,$B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1)/SUMPRODUCT(COUNTIFS($B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1),""))

に、I14セルに入力する関数を

=IF(I$1="","",IF(SUM(I$2:I$13),SUMIF($B:$B,I$1,$C:$C),""))

に、それぞれ変更して下さい。

回答No.4

bunjii

>田中さんが対応した項目全ての対応時間合計値をI14に計算する。
単純にSUMIF関数で算出できます。
=SUMIF($B2:$B21,I1,$C2:$C21)
0を表示したくない場合は次のようにIF関数で対応してください。
=IF(COUNTIF($B2:$B21,I1),SUMIF($B2:$B21,I1,$C2:$C21),"")

お礼

bunjii 様

ご指示いただきました計算式にて検証し、正常に動作しました。
ありがとうございました。
また機会がありましたらよろしくお願いします。

回答No.3

kagakusuki

>追加でもう一つ質問があります。
>H14に「合計」欄を追加したと仮定します。
>田中さんが対応した項目全ての対応時間合計値をI14に計算する。

　I14セルに次の関数を入力してから、I14セルをコピーして、J14～L14のセル範囲に貼り付けて下さい。

=IF(I$1="","",SUMIF($B:$B,I$1,$C:$C))

お礼

kagakusuki様

ご指示いただきました計算式にて検証し、正常に動作しました。
ありがとうございました。
また機会がありましたらよろしくお願いします。

回答No.2

kagakusuki

もし、F2～F8のセル範囲と、F9～F13のセル範囲が、それぞれ結合されたセルとなっていて、尚且つそれらの結合セルに入力されている値が1や2の様な数値データではなく、(1)や(2)の様な文字列データとなっている場合には、次の様な方法を使う事が出来ます。

　まず、I2セルに次の様な関数を入力して下さい。
　(配列式ではないため、Ctrl+Shift+Enterの操作は必要なく、単純に入力するだけでOKです)

=IF(OR($H2="",I$1=""),"",IFERROR(SUMPRODUCT(SUMIFS($C:$C,$B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1)/SUMPRODUCT(COUNTIFS($B:$B,I$1,$D:$D,IF($H2="平均",INDEX($H$1:$H2,MATCH("*?",$F$1:$F2,-1)):$H2,$H2))*1),0))

　次に、I2セルをコピーして、I2～L13のセル範囲に貼り付けて下さい。

　尚、この関数の場合は、H列の「項目」欄の値が「平均」である場合も、それ以外の業務種別である場合も共通の関数で処理する事が出来ますので、もし後になってから、H列の(1)や(2)のグループ分けが変更になった場合にも、F列とH列の値を入力し直すだけで対応する事が出来ます。

回答No.1

bunjii

>I2で求める値は以下の選択範囲の中から田中さんが対応した「取次ぎ」の対応時間を合計し平均値を求める。
=IFERROR(AVERAGEIFS($C$2:$C$21,$B$2:$B$21,I$1,$D$2:$D$21,$H2),"")

>I8で求める値は以下の選択範囲の中から田中さんが対応したH2～H7の項目の全てを合計し平均値を求める。
=IFERROR(SUMPRODUCT(($B$2:$B$21=I$1)*$C$2:$C$21*($D$2:$D$21=TRANSPOSE($H$2:$H$7)))/SUMPRODUCT(($B$2:$B$21=I$1)*($D$2:$D$21=TRANSPOSE($H2:$H7))),"")
この数式は配列演算のためCtrl+Shift+Enterで確定します。
貼付画像はExcel 2013で検証したものです。
Excel 2007以降のバージョンで再現できると思います。

お礼

bunjii さん

ご指示いただきました計算式にて検証し、正常に動作しました。
ありがとうございました。

追加でもう一つ質問があります。
H14に「合計」欄を追加したと仮定します。
田中さんが対応した項目全ての対応時間合計値を
I14に計算する。

上記計算式もご教授願います。
よろしくお願いします。