エクセルで複雑な条件で平均値を求めたい

質問

皆様初めまして。
エクセルを使って、以下の平均値を求めたいのですが、計算式の組み合わせが
よく分からず困っております。
AVERAGEIFとCOUNTIFをうまく組み合わせればいいのではないかと
思っておりますが、条件が複雑でうまくいきませんでした。

よい方法をご存じの方がいらっしゃれば、アドバイスをお願いします。

（例）添付画像の一覧表を元に説明します。
「I」2列は以下の範囲より「田中さん」の「取次ぎ」項目だけを合計し
その値の平均値を求める。
（項目）　　「D」2列～「D」21列
（氏名）　　「B」2列～「B」21列
（対応時間）「C」2列～「C」21列

「I」8列は以下範囲より「H」2列～「H」7列の
各対応時間の合計値を平均値として計算する。

（項目）　　「D」2列～「D」21列
（氏名）　　「B」2列～「B」21列
（対応時間）「C」2列～「C」21列

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

回答No.1

kagakusuki

>「I」8列は以下範囲より「H」2列～「H」7列の各対応時間の合計値を平均値として計算する。

と書いておきながら、（項目）、（氏名）、（対応時間）の何れもI列にもH列にも入力されている事にはなっていないのでは、何が何やら解かりません。
　又、画像で確認しようにも、画像の文字が全て潰れているため、どこにどの様なデータが入力されているのか良く解りません。

　ですので、どこにどんなデータが存在するのかという事を、こちらで仮に決めた上での話を致しますので、もしデータと入力セルの関係が間違っていた場合には、質問者様の方で適時修正願います。

　今仮に、元データの「氏名」がB2:B21のセル範囲に入力されていて、元データの「対応時間」がC2:C21のセル範囲に入力されていて、元データの「項目」がD2:D21のセル範囲に入力されていて、H2以下に各担当者の氏名が並んでいて、I1:L1のセル範囲に「取次ぎ」等を始めとする各項目名が入力されているものとします。
　又、I列～L列の2行目以下には、H列に氏名が入力されている各担当者の1行目に入力されている項目のみに関する平均対応時間を表示させるものとします。

　その場合、まずI2セルに次の関数を入力してから、I2セルをコピーして、I列～L列の2行目以下に貼り付けて下さい。

=IF(OR($H2="",I$1=""),"",IFERROR(SUMIFS($C:$C,$B:$B,$H2,$D:$D,I$1)/COUNTIFS($B:$B,$H2,$D:$D,I$1),0))