• ベストアンサー

Excel平均の算出方法。

まず画像を見て頂けると分かり易いです。 B1とC1で指定すれば、色が変るようにしています。 数字は左が値段、右が個数です。 そして、B1とC1で指定した箇所のみの平均値をO5とP5に表示されるような関数が知りたいのです。 ちなみに、数値の場所は空白や「０」の箇所もあるので、指定が一致しても数値が空白や0の場合は計算しないで飛ばすという風にしてもらえれば助かりますm(_ _ )m 詳しい方がいましたらご伝授頂きたいですm(_ _ )m

この投稿のマルチメディアは削除されているためご覧いただけません。
  • mzakom
  • お礼率38% (129/338)

質問者が選んだベストアンサー

  • ベストアンサー
  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.2

N5には次の式を =SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(B5:K5>0),B5:K5)/SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(B5:K5>0)) O5には次の式を =SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(C5:L5>0),C5:L5)/SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(C5:L5>0))

mzakom
質問者

お礼

ありがとうございましたm(_ _ )m

その他の回答 (6)

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.7

No.6です！ たびたびごめんなさい。 投稿後に気づきました。 （前回投稿の画像の配置として） せっかく配列数式を使用するのであれば R5セルに =AVERAGE(IF(B4:N4=B2,IF(D4:P4=C2,IF(B5:N5<>0,B5:N5)))) S5セルに =AVERAGE(IF(B4:N4=B2,IF(D4:P4=C2,IF(D5:P5<>0,D5:P5)))) でも大丈夫だと思います。 ※　前回同様、配列数式です。 何度も失礼しました。m（_ _）m

mzakom
質問者

お礼

セルの結合はどうしても必要なのでセルを変えることはできませんでした(≧ヘ≦) でも配列数式はとても勉強になりました！！ 丁寧に画像まで用意して頂きありがとうございますm(_ _ )m とても参考になりました！！

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.6

こんばんは！ 手元のExcel2003でやった一例です。 セルの結合があると何かと難しくなりますので、 ↓の画像のような配置だとします。 （画像が小さくて見にくかったらごめんなさい。） R5セルに =SUM(IF(B4:N4=B2,IF(D4:P4=C2,IF(B5:N5<>0,B5:N5))))/SUMPRODUCT((B4:N4=B2)*(D4:P4=C2)*(B5:N5<>0)) S5セルに =SUM(IF(B4:N4=B2,IF(D4:P4=C2,IF(D5:P5<>0,D5:P5))))/SUMPRODUCT((B4:N4=B2)*(D4:P4=C2)*(D5:P5<>0)) どちらも配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！ この画面から各セルにコピー＆ペーストする場合は、 セルに貼り付け後数式バー内で一度クリック！ 編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！ 数式の前後に｛　｝マークが入り配列数式になります。 ※　数式内の範囲指定が2列ずつずれているコトに注意してください。m（_ _）m

画像を拡大する
  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.5

＞最初の図では綺麗にできました。 まず元の図で、回答した通りの数式を使って計算します ＞もしよろしければ画像の場合の対処 元の図の状態から、Ｅ列、Ｉ列、Ｍ列にそれぞれ列番号右クリックで空列を挿入していけば、それで出来ます。 実際に手を動かし操作して、どんな数式になったか確認してみてください。

mzakom
質問者

お礼

色々試行錯誤の結果できました!! とても参考になるアドバイスありがとうございましたm(_ _ )m

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

回答2です。 入力のセルはO5とP5セルでしたね。 N5の式をO5に、O5の式をP5に入力してください。 なお、B1セルにりんご、C1セルに青森のように文章ではなっていますが図ではB2、C2となっていましたのでそのような式になっています。

mzakom
質問者

補足

ありがとうございます。最初の図では綺麗にできました。 申し訳ありませんが、更に列数に一つ間隔が空いた場合はどうすればよろしいのでしょうか。 http://iup.2ch-library.com/i/i0681927-1341645558.png このように間に列を挿入したらできなくなりました。 よろしければこの画像の場合の対処方も教えて頂けないでしょうかm(_ _ )m

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.3

個数は単純に平均すれば求まりますが、値段の方はキチンと合計と個数から求めないと間違います。 個数： =AVERAGEIFS(D5:M5,B4:K4,B2,C4:L4,C2,D5:M5,">0") 値段： =SUMPRODUCT((B4:K4=B2)*(C4:L4=C2),B5:K5,D5:M5)/SUMIFS(D5:M5,B4:K4,B2,C4:L4,C2) それとも実売平均ではなく箇所ごと平均でいいなら、個数と同様に計算してください。

mzakom
質問者

補足

ありがとうございます。最初の図では綺麗にできました。 もしよろしければ画像の場合の対処も教えて頂けると助かりますm(_ _ )m http://iup.2ch-library.com/i/i0681927-1341645558.png

回答No.1

AVERAGEIFS関数を検索してみてください。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. その他MS Office製品

関連するQ&A

  • エクセル特定範囲で設定可能の平均算出について

    指定数字をいれたら平均本数が変わる方法があったら教えてください。 　　　A　　　B　　　C 　　実数　　計算　　　　　　　　 1　　２　　　　　　　　　　　　指定欄 2　　３ 3　 １３ 4　 ２２　　関数 5　 ２３　　関数 6　 ３４　　関数　　 7　 ２３ 指定欄に数字が４といれたらB４はaverage(a1:a4)となり、 指定欄に数字の５と入れたらB４は消え、B５にaverage(a1:a５)と なる方法を教えて欲しいです。

  • エクセル2007で％の平均値を求めたいのですが、うまくできません。

    エクセル2007で％の平均値を求めたいのですが、うまくできません。 先日下記の様な質問をさせて頂いたのですが、もう少し質問したいことがあったのに間違えて解決済にしてしまいました。 どなたか分かる方がいらっしゃいましたら教えてください。 　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ　　　　　Ｈ １　目標　　6.44%　　4.50%　　3.77%　　5.15%　　2.66%　　2.79%　　式を入れたいセル ２　実績　　5.07%　　6.00%　　4.85%　　　　　　　　　　　　　　　　　A2～G2の平均値 A1～G1は、=(F3/G6)みたいな数式が入っていて、％で表示されています。 B2～G2は、最初は空欄で毎月B、C、D、E、Fと一つずつ％が入っていきます。 上記で、H1のセルに、下記の様な結果を表示できる式を入れたいのですが、何かいい方法はないでしょうか？ ・B2～G2が空白の時はB1～G1の平均値 ・B2にだけ数字が入っていてC2～G2が空白の時はB1の数字 ・B2～C2に数字が入っていてD2～G2が空白の時はB1～C1の平均値 ・B2～D2に数字が入っていてE2～G2が空白の時はB1～D1の平均値 ・B2～E2に数字が入っていてF2～G2が空白の時はB1～E1の平均値 ・B2～F2に数字が入っていてG2だけが空白の時はB1～F1の平均値 ・B2～G2全てに数字が入っている時はB1～G1の平均値 という質問に対して、 [H1]=AVERAGE(B1:INDEX(B1:G1,COUNT(B2:G2))) という式を教えて頂いたところ、うまくいきました。 半期分はこれでOKだったのですが、1年分だとHに半期の平均のセルが入るため、うまく表示できません。 ・B～Gが前期、Hが前期の平均 ・I～Nが後期、Oが後期の平均 さらに、Pに全期の平均を入れたい場合はどのようにしたらよいのでしょうか？ [P1]=AVERAGE(B1:INDEX(B1:N1,COUNT(B2:G2,I2:N2))) にすると、Hのセルも入ってしまうため、後期に数字が入ると平均値の表示が1つずつずれてしまいます。 何回も申し訳ないのですが、どうぞよろしくお願いいたします。

  • エクセル2007で％の平均値を求めたいのですが、うまくできません。

    エクセル2007で％の平均値を求めたいのですが、うまくできません。 AVERAGEやIFをいろいろ組み合わせてみたのですが、どうしても分からないので教えてください。 　　Ａ　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ　　　Ｈ １　目標　　6.44%　　4.50%　　3.77%　　5.15%　　2.66%　　2.79%　　式を入れたいセル ２　実績　　5.07%　　6.00%　　4.85%　　　　　　　　　　　　　　　　　A2～G2の平均値 A1～G1は、=(F3/G6)みたいな数式が入っていて、％で表示されています。 B2～G2は、最初は空欄で毎月B、C、D、E、Fと一つずつ％が入っていきます。 上記で、H1のセルに、下記の様な結果を表示できる式を入れたいのですが、何かいい方法はないでしょうか？ ・B2～G2が空白の時はB1～G1の平均値 ・B2にだけ数字が入っていてC2～G2が空白の時はB1の数字 ・B2～C2に数字が入っていてD2～G2が空白の時はB1～C1の平均値 ・B2～D2に数字が入っていてE2～G2が空白の時はB1～D1の平均値 ・B2～E2に数字が入っていてF2～G2が空白の時はB1～E1の平均値 ・B2～F2に数字が入っていてG2だけが空白の時はB1～F1の平均値 ・B2～G2全てに数字が入っている時はB1～G1の平均値 どうかよろしくお願いします。

  • エクセルでの複数条件での平均値算出

    お世話になっております。 エクセルで、複数条件に一致した場合のみの平均値を算出する関数が判らずに困っております。 数値の他に「○」「×」「担当」「管理」「その他」とあり、どの様な関数を組めば良いのか、さっぱり判りません。 例としては次のとおりです。 　Ａ　　　　Ｂ　　　Ｃ　　　　　Ｄ　　　 １該　　点数　　Ａ君　　Ｂ君 ２○　　3.5　　　担当　　管理 ３○　　3.9　　　担当　　その他 ４×　　4.2　　　管理　　担当 ５○　　4.1　　　管理　　その他 ６×　　3.9　　　その他　担当 ７　　　　　　　　△　　　　　□ 上記の表があったします。 条件１：Ａ列の該当が「○」 条件２：Ｃ列が「担当」もしくは「管理」 条件１と２を同時に満たした場合のみ、Ｂ列の点数を平均する 個々の平均を算出する式はＣ７（△）・Ｄ７（□）のセルでのみ処理を行う 上記なのですが、式が長くなっても構いません。 どなたかお判りになる方、なんとか教えて頂けませんでしょうか。

  • Excelで可変範囲内での平均値を出したい

    特定の範囲内で0以上の数値が入っている箇所までの平均値をだしたいのですが 関数でできますでしょうか。 【例】 A　B　C　D　E　F --------------------------- 10　0　2　 0　0　0 この場合、A列～C列までの平均値(4)を出したいです。 また、各数値は関数（SUM(A1:A2)等)で出力されており、可変になります。 エクセル初心者ですみませんが、よろしくお願いいたします。

  • エクセルの空白を求めたいのですが。

    エクセルの空白を求めたいのですが。 例えば、 ・・A　B　C　D　E 1 4 2 3 8 4 5 6 4 7 8 9 2 10 A列の数字入力があるセルから、次のセルの入力値まで の空白の個数を関数で出来ますでしょうか？ A９～A６　は空白が２個となります。 A６～A３　は空白が３個となります。 A列の　各行　には他のセルで計算された値を出しているので、 各行に現れる数値は、その時によって違います。（現れる数値は整数です） わかる方いましたら、宜しくお願いします。

  • エクセルの関数

    エクセルの関数ですが Ｂ２には目標値が入っています。Ｃ２～Ｎ２まで、毎月数値を入れて行きます。今は１月なのでＬ２まで数値が入っています。Ｍ２～Ｎ２は、いまは空欄です。 Ｏ２には、　=SUM(C2:N2)-B2*10　　と関数が入っています。　最後の１０は、月数です　よって２月の数値が決定したら　Ｍ２に数値が入り　Ｏ２には　=SUM(C2:N2)-B2*1１　となります。 これだと、毎月　最後の月数の数字を入れ替えなくてはなりません この　Ｏ２のセルの関数を毎月いじらなくてもよい関数を教えてください 宜しくお願いいたします

  • エクセルで

    エクセルで、 VLOOKUP関数を使って、シート2枚目と数字が一致する物に関して、 抜き出して行こうと思っています。 でも完全一致ではなく例えば、 2170610105186 の数値を左2行右1行消した数字(7061010518)がシート2枚目の一致しているのを 探そうとする場合、関数を合わせることによってセルを抜き出すことはできるでしょうか？ 誰か知っていれば教えてくださーい。

  • エクセルでのIF関数：この場合どうすれば？

    IF関数を使うと思うのですが、このような場合どのように入力すればよろしいのでしょうか？ 特定のセル（たとえばC１のセル）にマイナスの数字もしくは、プラスの数字もしくは空白が入力されており、たとえばB１のセルに関数を入力するとき C１に空白以外の数値が入力されている場合に限り、A1に入力されている数値をB１のセルに入力（反映）させたい場合。 また同時に、C1が空白の場合、B1も０ではなく空白にしたい。 どなたかご教授ください。よろしくお願い致します。

  • 《エクセル2000》SUMPRODUCT関数、この式のどこがいけないのか…

    こんにちは。 Ａ列が１であり、Ｂ列とＣ列に共に数字が入っている（空白でない）ものをカウントしたいと思い、以下の関数を書きました。 =SUMPRODUCT((A1:A50=1)*(B1:B50<>"")*(C1:C50<>"")) ですがこの式ですと、「Ｂ列とＣ列が共に空白なもの」の個数が返って来てしまいます。 「<>」が怪しい気がするのですが、具体的に何がいけないのでしょうか？ また、欲しい数字を出すようにするにはどうすればいいのでしょうか。 よろしくお願いします…

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する