- 締切済み
【再投稿】Qエクセルで複雑な条件で平均値を求めたい
皆様初めまして。 エクセルを使って、以下の平均値を求めたいのですが、計算式の組み合わせが よく分からず困っております。 AVERAGEIFとCOUNTIFをうまく組み合わせればいいのではないかと 思っておりますが、条件が複雑でうまくいきませんでした。 よい方法をご存じの方がいらっしゃれば、アドバイスをお願いします。 <希望> ・各担当者が担当した各業務ごとの対応時間を合計し、平均値を求める ・F列の(1)と(2)は以下の業務をひとまとめとし、担当者が対応した業務項目と それにかかった対応時間を合計し、平均値として計算する。 (1)取次ぎ、転送、案内、相談、指示、折り返し (2)保留、見積り、修理、キャンセル ●業務種別 取次、転送、案内、相談、指示、折り返し、保留、見積り、修理、キャンセル I2で求める値は以下の選択範囲の中から 田中さんが対応した「取次ぎ」の対応時間を合計し 平均値を求める。 <選択範囲> (氏名 )B2~B21 (対応時間)C2~C21 (項目 )D2~D21 I8で求める値は以下の選択範囲の中から 田中さんが対応したH2~H7の項目の全てを合計し 平均値を求める。 <選択範囲> (氏名 )B2~B21 (対応時間)C2~C21 (項目 )D2~D21 説明が分かりにくいですが、よろしくお願いします。
- axsmax-taka
- お礼率42% (3/7)
- Excel(エクセル)
- 回答数6
- ありがとう数4
- みんなの回答 (6)
- 専門家の回答
みんなの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
もし、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行目の「合計」欄の関数に関しては特に変更点が御座いませんので割愛致します。
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答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),"")) に、それぞれ変更して下さい。
- bunjii
- ベストアンサー率43% (3589/8248)
>田中さんが対応した項目全ての対応時間合計値をI14に計算する。 単純にSUMIF関数で算出できます。 =SUMIF($B2:$B21,I1,$C2:$C21) 0を表示したくない場合は次のようにIF関数で対応してください。 =IF(COUNTIF($B2:$B21,I1),SUMIF($B2:$B21,I1,$C2:$C21),"")
- kagakusuki
- ベストアンサー率51% (2610/5101)
>追加でもう一つ質問があります。 >H14に「合計」欄を追加したと仮定します。 >田中さんが対応した項目全ての対応時間合計値をI14に計算する。 I14セルに次の関数を入力してから、I14セルをコピーして、J14~L14のセル範囲に貼り付けて下さい。 =IF(I$1="","",SUMIF($B:$B,I$1,$C:$C))
お礼
kagakusuki様 ご指示いただきました計算式にて検証し、正常に動作しました。 ありがとうございました。 また機会がありましたらよろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
もし、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列の値を入力し直すだけで対応する事が出来ます。
- bunjii
- ベストアンサー率43% (3589/8248)
>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に計算する。 上記計算式もご教授願います。 よろしくお願いします。
関連するQ&A
- エクセルで複雑な条件で平均値を求めたい
皆様初めまして。 エクセルを使って、以下の平均値を求めたいのですが、計算式の組み合わせが よく分からず困っております。 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列 説明が分かりにくいですが、よろしくお願いします。
- 締切済み
- Excel(エクセル)
- エクセル2002 条件に合う人だけの平均計算式
エクセル2002で、当てはまる苗字の人の点数の平均点を出したいです。 例) A列 名前の項目 名前はランダムに複数ある B列 各自の点数 例えばA列に 田中 という名前が10個あり、それがばらばらに不規則に並んでいる。 田中の合計点は800点で10項目なので、田中の点数は80点、という風にしたいです。 どのような式が良いでしょうか?ご指導お願い致します。
- ベストアンサー
- オフィス系ソフト
- 条件を付けて平均を求めたい!(Excel)
列 A B C 商品名 ランク 金額 1みかん A 1000 行 2みかん C 300 3みかん B 600 4みかん C 5 合計 1900 6 平均A 7 平均B 8 平均C 説明をする為に適当に作ったので、何故こんな物の平均を?と思うかもしれませんが、そこはあまり気にせずご回答頂ければ幸いです。 例えば、こんな表があったとします。(勿論、データ量はもっと沢山。。。) 各ランクごとの平均を求めたいのですが、どんな関数を使い、どんな範囲選択をすれば宜しいのでしょうか? 条件を付けAVERAGEで求めれば良いことはなんとなく分かるのですが・・・ 範囲の設定や数式の順番が全然分かりません。 よろしくお願いします。 ※金額欄(C列)・・・ 実際に未入力のセルがあるので、未入力のセルを0(ゼロ)として考えてくれる数式をお願いします
- ベストアンサー
- その他(業務ソフトウェア)
- エクセルでの複数条件での平均値算出
お世話になっております。 エクセルで、複数条件に一致した場合のみの平均値を算出する関数が判らずに困っております。 数値の他に「○」「×」「担当」「管理」「その他」とあり、どの様な関数を組めば良いのか、さっぱり判りません。 例としては次のとおりです。 A B C D 1該 点数 A君 B君 2○ 3.5 担当 管理 3○ 3.9 担当 その他 4× 4.2 管理 担当 5○ 4.1 管理 その他 6× 3.9 その他 担当 7 △ □ 上記の表があったします。 条件1:A列の該当が「○」 条件2:C列が「担当」もしくは「管理」 条件1と2を同時に満たした場合のみ、B列の点数を平均する 個々の平均を算出する式はC7(△)・D7(□)のセルでのみ処理を行う 上記なのですが、式が長くなっても構いません。 どなたかお判りになる方、なんとか教えて頂けませんでしょうか。
- ベストアンサー
- オフィス系ソフト
- Excelで平均値のだしかたをおしえてください。
1時間ごとに何件あったかを表を作ったのですが。 1日分で1時間の平均を出したいと思います。 セルA1(縦に40人)『社員名』B1『働いた合計の時間』(これは1時間単位)C1『1日の合計件数』D1『平均値(今回出したいもの)』E1~S1『7時~21時まで1時間ごとに件数をいれる』こういった表を作りたいのですが この場合平均値はどういった数式でだしていったらいいんでしょうか?初心者なものですみませんが 教えてください。お願いします。説明がわかりにくかったらすみません。
- ベストアンサー
- オフィス系ソフト
- Excel_条件に合う範囲の平均値の求め方
Excel関数についての質問です。 以下のようなデータが合った場合、条件に合う範囲のデータのみ 抜き出して平均値を求める方法を教えてください。 ■やりたいこと マイナスが続いている範囲を見つけ出し、マイナスが続いている 範囲のみの平均を算出する。 以下で言うとB4~B7の平均を自動で計算してくれる 関数を考えています。 A列:時間が入っていますが、これ自体も他の値を時間に換算したもの。 (式が入っています。) B列:データが入っており、プラスの領域とマイナスの領域を交互に繰り返す。 A B 1 Time DATA 2 0 0.05 3 6 0.04 4 8 -0.02 5 11 -0.01 6 15 -0.03 7 17 -0.05 8 21 0.05 9 24 0.06 10 27 -0.03 ・・・(以下プラスとマイナスが交互に続きます。) DABERAGEなどの様に、条件に合うセルの平均ではなく、 ある範囲のセルの平均を出したいと思いますが 中々良い案がありません。 よろしくお願いします。
- ベストアンサー
- SE・インフラ・Webエンジニア
- 平均の出し方
Excel初心者です。 下記のような平均を出す関数を教えてください。 日毎に3列使い、日々の処理数を集計しています。 A列、B列、D列、E列は数値又は空欄。 日計:C列はsum(A:B) 日計:F列はsum(D:E) 月合計計:Q列はsum(C,F・・・) Z列に処理平均を出したい。 C列とF列の値がゼロの時は平均の対象外にする。 average(C4,F4)とすると、数式が入っているせいか、結果が1.5になってしまいます。 ここにゼロや数式が入っているセルを除いた平均を出し、Z4に3と出るようにしたいです。 範囲が10個以上あるので、範囲指定もうまくできません。 A B C D E F ・・・ Q Z 1 9/1 9/2 9月 2 午前 午後 終日 午前 午後 終日 月合計 処理平均 3 4 1 5 1 3 4 9 4.5 4 2 1 3 3 3 5 1 5 6 6 6 6 6 1 7 5 5 12 6 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル 複数の条件を満たした場合の集計方法
列Aに月(1~12)、列Bに担当者(Aさん・Bさん)、列Cに作業内容(通常業務・特別業務)、列Dに料金(任意の数値)、列Eに合計金額(1行目からの合計)とある場合に、たとえば9月に担当者”Aさん”が通常業務で作業をした場合の合計のような、月ごと、担当者ごと、作業内容ごとの集計がしたいのですが、どのような式で記述すればよいのでしょうか? お願いいたします。
- ベストアンサー
- オフィス系ソフト
- 条件を満たしたものの、平均価格を求めたいです。
Excel2003を使っています。 業務上で必要な集計を出したいのですが、関数は言うほど理解できていないので、とにかく出来るだけシンプルな方法はないか探しています。 例) A B C 1 間取 戸数 合計価格 2 4LDK 11 39,230 3 4LDK 11 25,480 4 3LDK 10 25,370 5 3LDK 10 26,570 6 4LDK 9 27,810 7 3LDK 8 19,170 上記のような表があって、たとえば3LDKだけをカウントして平均価格出したいのです。 (ちなみは、合計価格は別に一覧表があってそちらから反映させてます) 3LDKだけをカウントして合計を出すのはどうにか出来たのですが(SUMIFを使いました)この合計から平均が出せません。 (AVERAGEを使えばいいのはなんとなくわかるのですが…。) どなたかご指導お願いします。
- ベストアンサー
- オフィス系ソフト
お礼
bunjii 様 ご指示いただきました計算式にて検証し、正常に動作しました。 ありがとうございました。 また機会がありましたらよろしくお願いします。