【Excel】複数条件のAVERAGEで平均値を求める方法
- Excelの関数「AVERAGE」を使用して、複数条件の平均値を求める方法について解説します。
- 特定の条件に該当する要素の平均値を求めるためには、「SUMIFS」関数を使用し、その結果を「AVERAGE」関数に入れる必要があります。
- ただし、この式をコピーして他のセルに貼り付ける際に、正常に動作しないことがあります。原因はセル範囲の絶対参照の設定にあります。
- ベストアンサー
【Excel】複数条件のAVERAGE
ある特定したいくつかの要素の合計を、セル「I5」にだすとします。 たとえば表2のH2の「年」、同表のH3の「店舗」、同表のG5以下の「種目」からそれぞれ具体的な条件を、たとえば表1のA列より「2016年」、同表のE列より「店舗(1)」、同表のB列より「アスパラガス」を探し出すようにする場合関数式は以下のような式となりますが、 =SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3) これを合計ではなく平均値で出す場合、「SUMIFS」のみ「AVERAGE」に入れ替え =AVERAGE($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3) という式を「I5]に入れると確かに平均値はあっていたのですが、この式をコピー(セルのコピー)しても、正常に反映されず、I5の回答がそのままコピーされてしまいます。 何が原因なのでしょうか?
- frau
- お礼率54% (2371/4378)
- Excel(エクセル)
- 回答数2
- ありがとう数2
- みんなの回答 (2)
- 専門家の回答
質問者が選んだベストアンサー
>確認のために手動計算してみましたところ、合っていました。 SUMIFS関数で合計を算出した範囲の平均値であれば合っているはずはないのですが平均対象のセルを再確認してください。 A列が2016年でG列がアスパラガスは対象データが無いようです。 >>AVERAGEIFS >に差し替えたのですが、#DIV/0!になってしまいます。 前述のように条件に合う計算対象が1つもないため「0で除算したときのエラー」になったのです。 >外側というのは何に対しての外側でしょうか?どう設定すればよいかわかりません。 =AVERAGEIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3) ↓ =IFERROR(AVERAGEIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3),"") >つまり、列全体とした場合上の式は =AVERAGEIFS(D:D,A:A,$H$2,B:B,$G5,E:E,I$3) ↓ =IFERROR(AVERAGEIFS($D:$D,$A:$A,$H$2,$B:$B,$G5,$E:$E,H$3),"") H3とI3は結合しているのでI$3にはデータが有りません。 $E:$EをH$3と比較してください。
その他の回答 (1)
- miso_kasu
- ベストアンサー率60% (6/10)
>これを合計ではなく平均値で出す場合、「SUMIFS」のみ「AVERAGE」に入れ替え 「AVERAGE」ではなく「AVERAGEIFS」にしてください。 但し、集計する対象が無いときは「0で除算」のエラーになります。 >という式を「I5]に入れると確かに平均値はあっていたのですが、 手動で計算してみましたか? AVERAGE関数はカッコ内にカンマ区切りで列記されたセルの値や実数が全て計算対象になりますのでAVERAGEIFS関数と異なった結果になります。 >この式をコピー(セルのコピー)しても、正常に反映されず、I5の回答がそのままコピーされてしまいます。 I5をI6へコピーすると同じ結果になると思います。 >何が原因なのでしょうか? AVERAGEとAVERAGEIFSでは求める対象が異なります。 前述のように「AVERAGE」を「AVERAGEIFS」に修正してください。 エラーを避けるにはIFERROR関数を外側に付けてください。
お礼
=AVERAGEIFS($D$2:$D$707,$A$2:$A$707,$H$2,$B$2:$B$707,$G5,$E$2:$E$707,I$3) ですが、行は707までに増やしました。 参照される側のセルを増やすたびに、式を変えていかなければなりません。 列全体を選択させるにはたとえば =SUMIF(B:B,G6,D:D) のようになるらしいですが、「B:B」と「D:D」には絶対値の記号「$」がつけられてません。 「B:B」はB列全部という意味だと思いますが、このように示せば絶対値の「$」マークは不要なのでしょうか? つまり、列全体とした場合上の式は =AVERAGEIFS(D:D,A:A,$H$2,B:B,$G5,E:E,I$3) こうなるのでしょうか?
補足
ご回答ありがとうございます。 > 手動で計算してみましたか? 確認のために手動計算してみましたところ、合っていました。 >AVERAGEIFS に差し替えたのですが、#DIV/0!になってしまいます。 #DIV/0!は数式や関数が0(ゼロ)または空白のセルで割られていることを伝えるメッセージだそうですが、これは >集計する対象が無いときは「0で除算」のエラー のことだと思いますが、 > エラーを避けるにはIFERROR関数を外側に付けてください。 外側というのは何に対しての外側でしょうか?どう設定すればよいかわかりません。
関連するQ&A
- 【Excel】SUMIFSのコピーが連番になる
条件が複数の場合の関数のSUMIFSについて、作り方は一応わかったのですが、コピーするとどうしてもセルが連番になってしまいます。 条件が一つの場合はSUMIFを使います。 たとえば添付画像の「種目」別の合計をだしたい場合、 =SUMIF(B:B,G6,D:D) という式になりますが、これを複数の条件とする場合にはSUMIFSを使うようですが、各「年」ごと、各「種目」の合計をさらに「店舗」ごと個別計算し、その結果を表2に示す場合、 =SUMIFS(D2:D596,A2:A596,H2,B2:B596,G5,E2:E596,H3) という式でよいかと思いますが、これを「種目」すべてにコピーすると、上の式のセルがすべて連番になってしまうのです。 たとえば =SUMIFS(D3:D597,A3:A597,H3,B3:B597,G6,E3:E597,H4) このようにです。 冒頭に述べた =SUMIF(B:B,G6,D:D) の場合、たとえば下のセルにコピーすると =SUMIF(B:B,G7,D:D) というようにG列だけ変化します。 連番になってしまうと、参照元がまったく別のものになってしまいます。 「合計対象範囲」「条件範囲」はセルの範囲を指定するという説明です。 https://www.becoolusers.com/excel/sumifs.html しかしこれは合計結果がひとつづつの場合ですが、この添付画像のような複数の項目の、複数の条件の場合、同式の内容のみコピーさせるにはどうしたらいいのか困っております。
- ベストアンサー
- Excel(エクセル)
- [EXCEL]AVERAGEでブランクを飛ばした計算式
みなさん、こんばんは。 [EXCEL]AVERAGEでブランクを飛ばした計算式を教えてください。 よろしくお願いします。 まず A列1~13に 10 20 30 40 50 60 (空白/ブランク) 10 20 30 40 50 60 と数字が入っています。 B列6行に =IF(A6="","",AVERAGE(A1:A6))と式を入れ、この式をB列13まで延ばすと、 B列6~13は 35 38 36 34 32 30 35 となります。 私の希望は7行目のブランクを無視して数字があるところだけを指定範囲(今回は範囲は6行)計算したいのです。 結果としてB列6~13は 35 35 35 35 35 35 35 となるのが希望です。 B列6、13は、連続していますので、そのままの平均なので35ですが、 他はブランクがありますので、そのブランク1つ分を計算範囲に入れず、 範囲先頭部分をひとつ増やして計算させたいのです。 B列8ならA7を除く、A2~A6,A8の平均(AVERAGE(A2:A6,A8)) B列10ならA7を除く、A4~A6,A8~A10の平均(AVERAGE(A4:A6,A8:A10)) という感じにです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 【Excel】SUMIFSの式を絶対値にするには
添付した画像のセルB2には以下のとおりのSUMIFSの式が入っているとします。 =SUMIFS(E:E,B:B,Q2,C:C,Q3,G:G,R2) セルC2にこの式をコピーしたいのですが、 =SUMIFS(F:F,C:C,R2,D:D,R3,H:H,S2) このようになり、合計対象範囲から条件まで、すべてズレてしまいます。 逆にB2の下のセルB3にB2の式をコピーすると条件それぞれの位置がこのように =SUMIFS(E:E,B:B,Q3,C:C,Q4,G:G,R3) ズレます。 表マトリクスの数値の合計をだすために、どの位置にセルB2の式をコピーしても正確な結果がでるようにセルB2を絶対値としたいのです。セルB2に入れる正確な式を教えてください。 また式だけではなく絶対値となる理由もご説明していただけると幸いです。
- 締切済み
- Excel(エクセル)
- Excel2007でのAverage
例えばA列のA1からA30間での数値平均を出したい場合、 あるセルに=AVERAGE(A1:A30)と書けばよいのは分かるのですが このA列に何も数字が書いてない場合(0除算のエラー)になり セルには#####と表示されてしまいます。 このA列に何も数字が書いてない場合、0と表示させるには どんな式を書けばよいのか教えてください。
- ベストアンサー
- オフィス系ソフト
- Excel関数で一致した条件を合計する方法
Excelの関数を使って、E2:E4のセルにF列の「A」~「D」に 一致した場合、G列の「8」または「9」を加算する方法を教えて下さい。 例えば、スギの場合だと...... スギの1日目が「A」の時、F1:G4の表をもとに、「8」になり スギの2日目が「C」の時「7」になり スギの3日目が「A」の時「8」になり、 E2の合計は「8+7+8」と加算されるということです。 スギの合計は23、 マツの合計は25、 サクラの合計は28というふうになればいいのですが・・・。 どなたか教えて下さい。お願いします。 A B C D E F G 1 1日 2日 3日 合計 A 8 2 スギ A C A B 9 3 マツ C D A C 7 4 サクラ B B D D 10
- ベストアンサー
- オフィス系ソフト
- 複数の条件での合計を出したいのです
エクセルの関数を使い 条件セル E2の値 条件の検索範囲の列Dから抜き出し その行のG列-H列-I列(G列の空白の時の値)の合計を出したい つまり E2=2の時 D列 G列 H列 I列 1行 1 2 2 2行 2 3 1 1 3行 2 2 2 答えは 3-1-2-2 こんな事を実現したいのですが 頭がグルグルしてしまってます ご指南ください。
- ベストアンサー
- その他(プログラミング・開発)
- 複数の条件を満たすレコードの合計を求める
出荷日 品名 数量 単価 合計 担当者 部門 5/2 もも 1 100 100 A 14 5/3 珈琲 2 150 300 B 15 5/4 ばなな 3 100 300 C 14 1行から100行位の表があってそのつど入力していき、(5月分の表としてありますので、100行全て埋まる訳ではありません)別のシートに A列 B列 C列 D列 1 A担当者 B担当者 C担当者 214部門 100 0 300 315部門 0 300 0 という表になるようにB2セルに次の式を入れました =sumproduct((sheet1!$G$2:$G$100=$A2)*(Sheet1!$F$2:$F$100=$B1)*sheet!$E$2:$E$100)) で入れるとNAME?になります。100と入れているところを今現在入力されている行数を入れると合計が表示されます上の表ではG2:G3と入れると合計が出ますが、毎日毎日データーが更新されているのでそのつど関数を入れなおせません。 どなたか、良い案を教えてください 長くなってすみません
- ベストアンサー
- オフィス系ソフト
- excel2003で複数条件
A列にA~Gのアルファベットかブランク、B列に数字(0~5)かブランクが入っています。 A列にアルファベット、B列に0が入っている行を抽出し、 A列とB列がAと0、BとO、CとO・・・の行がそれぞれABCごとに何個あるかカウントしたいのですが、 どのような論理式を作ればよいでしょうか? なおexcel2003を使用しておりますのでcountifとsumproductは使うことはできますが、countifsを使うことはできません。 よろしくお願い致します。。
- ベストアンサー
- Excel(エクセル)
- Excel複数条件 日付について
Excelで複数条件である文字列をカウントをしたいと思っています。 sheet1 A B C D E G H 1 10/1 10/2 10/3 10/4・・・11/1 11/2 2 鈴木 出勤 出勤 3 佐藤 出勤 出勤 出勤 4 田中 出勤 出勤 sheet2 A B C 1 10月 11月 2 鈴木 1 1 3 佐藤 1 2 4 田中 2 0 sheet1の出勤した日をsheet2の月別に出勤の文字列をカウントしたいのですが 現在は10月のセルに=COUNTIF(sheet1!B2:E2,"出勤")、11月のセルに=COUNTIF(sheet1!G2:H2,"出勤")と入力しています。 これを名前と月を条件に出勤のカウントをとれる関数はありますでしょうか。 宜しくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- エクセルで以下の件を教えてください!
添付の写真のように、A~F列のデータを、H~K列で集計しています。(利益率は集計してませんが。。。) この集計にさらに『在庫金額合計』の項目を、L列に作成したいと考えています。 購入はしたが、販売はできていないという、在庫の集計をしたいのです。 この表で言うと、B3とB5とB9の購入金額合計を、それぞれ月別(購入月別)に自動で計算できるようにしたいです。 販売日以降の列が空欄の場合は、プラスして計算するみたいな関数は、どう作ればよいでしょうか? 参考までにI~K列の関数は、以下のような感じです。 I2セル:=SUMIFS(B:B,A:A,">="&H2,A:A,"<"&EDATE(H2,1)) J2セル:=SUMIFS(D:D,C:C,">="&H2,C:C,"<"&EDATE(H2,1)) K2セル:=SUMIFS(E:E,C:C,">="&H2,C:C,"<"&EDATE(H2,1)) なるべく同じような関数を使って表を作成したいです。(分かりやすいとの理由からですので、絶対という訳ではなく、別の方法でもかまいません!) ご教授ください。宜しくお願い致します。
- ベストアンサー
- Excel(エクセル)
お礼
おかげさまで目的が叶いました。ありがとうございました!