• ベストアンサー

エクセルで毎月の複数シート合計から平均値を年間途中の分で出したい><

今excel2003で毎月の客先売上金額、粗利などを6枚のシートで客先ごとに分類し7枚目のシートに合計金額と平均値を出しています。 そこで、全ての合計と平均のある7枚目のシートで 1~12月まであった場合 SUMを利用して反映されているので まだ、この年度で数字が確定していない月(今でしたら9月以降)の 項目に数字の"0"が入ってしまいます。 この為にAVERAGE関数を使うと12ヶ月分が丸々反映されてしまう為、正確な数字がでません。 このため現在はセルに 合計金額/今年度消化した月間数 (例) SUM(売上トータル)/5 といった感じで手作業で割り算して平均を出しています。 これを自動的に毎月、手作業で割り算せずに、消化した月数で平均を出す事は出来ないでしょうか? 初歩的な質問かもしれませんが何卒御回答宜しくお願いします。

  • ns400
  • お礼率6% (1/16)

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

  • ベストアンサー
  • hige_082
  • ベストアンサー率50% (379/747)
回答No.10

NO.4・5・9です CHOOSE(インデクス,値1,値2,値3・・・・・・・・)は インデクスの数番目の値を求める関数 この辺の説明が、まずかったようですね >当社の場合5月決算なので×年度で5月が初月です で >=SUM(売上トータル)/CHOOSE(MONTH(TODAY()),2,3,4,5,6,7,8,9,10,11,12,1) は、ちょっと違います MONTH(TODAY()の部分は理解されてるようで、今月は9月なので「9」が値として帰ってきます CHOOSE(MONTH(TODAY()),2,3,4,5,6,7,8,9,10,11,12,1)へあてはめると CHOOSE(9,2,3,4,5,6,7,8,9,10,11,12,1)となり、帰ってくる値は 9はインデクスなので、9の次の数字が、値1となり 値1=2、値2=3・・・・値8=9、値9=10、値10=11・・・ インデクスは9なので、帰ってくる値は値9、即ち値9=10が帰ってきます =SUM(売上トータル)/10 と成ってしまいます そこで =SUM(売上トータル)/4 としたいということは 9月=4ということは、値9=4 にならなければ、成らないので CHOOSE(9,,,,,,,,,4,,,)と基準を決めてしまいます 8月=値8=3、7月=値7=2 CHOOSE(7,,,,,,,2,3,4,,,) のようにしていき、関数を完成すると CHOOSE(MONTH(TODAY()),8,9,10,11,12,1,2,3,4,5,6,7)と成ります 最終的に =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),8,9,10,11,12,1,2,3,4,5,6,7) 参考になりましたでしょうか? 長文ついでに 関数の確認方法も覚えておいた方が良いと思います 例えば、今回、CHOOSE、MONTH、TODAYという関数を使用しました =TODAY() だけをセルに入れてみます 「2008/9/5」という日付が帰ってきます このことから、MONTH(日付)ということが分かると思います =MONTH("2008/9/5") 「9」が帰ってきます では、日付部分を"h20.1.8"や"平成5年10月15日"に変更して、「1」や「10」がちゃんと帰ってくるのか、それともエラーになるのか これをすることによって、CHOOSEのインデクスには1~12が入ることが分かると思います これで9月に「4」という値が帰ってくるのかの確認が出来ます =CHOOSE(9,8,9,10,11,12,1,2,3,4,5,6,7) =CHOOSE(MONTH("2008/9/5"),8,9,10,11,12,1,2,3,4,5,6,7) この作業をすることによって、応用しやすくなると思います 以上長文失礼しました

ns400
質問者

補足

すいません。基準を決めなければならないというのはわかったのですが、その基準を入れる場所が私の力不足でわかりません。 そこさえ教えていただいたらいけるような気がします。 宜しくお願いします。

その他の回答 (11)

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.12

ANo.11さんへ 質問は既に移動しています。 CHOOSE関数でインデックスを可変にした場合の値の変更 http://okwave.jp/qa4312563.html

  • hige_082
  • ベストアンサー率50% (379/747)
回答No.11

NO.10です て言うか =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),8,9,10,11,12,1,2,3,4,5,6,7) これ試してみましたか 理解できないなら =CHOOSE(MONTH(TODAY()),SUM(売上トータル)/8,SUM(売上トータル)/9,SUM(売上トータル)/10,SUM(売上トータル)/11,SUM(売上トータル)/12,SUM(売上トータル)/1,SUM(売上トータル)/2,SUM(売上トータル)/3,SUM(売上トータル)/4,SUM(売上トータル)/5,SUM(売上トータル)/6,SUM(売上トータル)/7) CHOOSE関数が理解できないなら =SUM(売上トータル)/IF(MONTH(TODAY())<=5,MONTH(TODAY())+7,MONTH(TODAY())-5) =IF(MONTH(TODAY())<=5,SUM(売上トータル)/(MONTH(TODAY())+7),SUM(売上トータル)/(MONTH(TODAY())-5)) いくつか作ってみましたが、すべてやっていることは同じです 理解できるものがあればどうぞ 理解できないなら、使用しない方が良いでしょう 後々困ると思うので

  • hige_082
  • ベストアンサー率50% (379/747)
回答No.9

NO.4・5です 解説をしておけば良かったですね >このため現在はセルに 合計金額/今年度消化した月間数 との事でしたので、そのまま式にしました 私は 7枚目のシートに 1月合計 1000円 2月合計 1000円 3月合計 1000円 4月合計 1000円    ・    ・ 11月合計 0円 12月合計 0円 売上トータル 9000円   月平均 1000円 のような表の月平均の関数を質問している、と、解釈しました 関数の解説すると 1月を1とするなら←この説明が良くなかったようで、すみません =SUM(売上トータル)/MONTH(TODAY()) TODAYは今日の日付を求める関数=2008/9/4と成ります MONTHは日付から月を求める関数=MONTH("2008/9/4")=9と成ります 例として 月1000円の売上があるとします 月の売上 × 9月 = 9000円 上の関数から MONTH(TODAY()) = 9 =SUM(売上トータル)/MONTH(TODAY()) に当てはめると =9000円 / 9月 = 平均1000円 ×年度で4月を1とするなら 4月を1ヶ月目とするなら =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) CHOOSE(インデクス,値1,値2,値3・・・・・・・・)は インデクスの数番目の値を求める関数 インデクスが1なら値1、3なら値3 CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) の場合は1月=10、4月=1、9月=6と成ります 例 月の売上が1000円で、今日が9月だとすると =6000円 / 9月は6 = 平均1000円 結果を数えるのではなく、今日が何月かを求めて分母を決めています そうすることによって、売上が0円の月があっても対処できるのではと考えました 説明が下手で、すみませんでした

ns400
質問者

補足

丁寧なご説明有難うございます。 hige_082様の解釈で全く相違御座いません。本日、CHOOSE関数を使い実行してみました。 当社の場合5月決算なので×年度で5月が初月ですので =SUM(売上トータル)/CHOOSE(MONTH(TODAY())2,3,4,5,6,7,8,9,10,11,12,1) としてみました。 エラーはでなかったのですが 平均値とは異なる数値を返してきました。 実際例 (今までの方法) =SUM(売上トータル)/4 答えは622,153が正解です (今回の数式) =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),2,3,4,5,6,7,8,9,10,11,12,1) 答えは248,863とでてきました。 説明いただいてる部分で >x年度で4月を1とするなら の解釈が間違っているきがしたので色々HPを見て調べてみましたが 正解がわかりませんでした。 引き続きお手間おかけするのも心苦しいですが、hige_082さんの解釈で私の思っている事は出来上がります。 お手間おかけ致しますが最後までご指導いただけますようお願い申し上げます。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.8

ANo.7です。 COUNTBLANK関数にて =IF(COUNTBLANK(データ範囲)=0,求めたい結果,"") になると思います。 (未入力セルが0個であれば通常の処理、そうでなければ空白を返す) とか。

ns400
質問者

補足

御回答有難う御座いました。 =IF(COUNTBLANK(前期入力!B95:B96)=0,SUM(前期入力!B95:B96),"") という形で試してみました。 (7枚目の集計シートの月間売上種類別合計部分です。) ちなみに前期入力とあるシートのB95~B96の合計値が種類別合計です。 B95はx月度種類別売上の合計が入っており B95=SUM(B97:B137)といった形で売上が合計されています。 未入力の月間の場合はなぜか0を反映しています しかし、全く集計された7枚目のシートの未入力部分に空欄が返りません。この数式は1枚目から6枚目の合計数値を出しているセルにいれるべきなのでしょうか? お手間おかけ致しますが宜しくお願いします。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.7

ANo.6です。 入力した範囲の平均値を出したいのか、数式の結果の範囲で平均値を出したいのか。。。? 補足等のSUM関数もよく分からないですし、その範囲もまちまちですし。 合計を求めたいのか、平均を求めたいのか、そして範囲はどこなのか。。。? どこにどのようなデータがあり、それをどこでどのような数式を用いて どんな結果が求められ、そしてどうしたいのか。 って所でしょうか。 問題は >合計金額/今年度消化した月間数 この”今年度消化した月間数”ですよね? 6枚のシートから月間数を求めるのか、7枚目のシートに集約された データから判断できるのかは、その情報で決まりますよ。 仮に7枚目のシートのA1~L1(12ヶ月分)に各月のデータが集約されるように なっているとします。 未入力時にデータの結果が"0"になるのであれば、"0"でないデータの個数が"今年度消化した月間数"になりますし、 集計で結果が"0"になる場合もあるのなら未入力時をIF文で判断させれば良いと思います。

ns400
質問者

補足

色々とご指摘有難う御座います。 おっしゃるとおり7枚目のシートに12か月分の各月のデータが集約されるようになっております。 集計結果が”0”になる場合もございますので未入力時をIF文で判断させたいと考えております。 この場合のIF文はISBRANKを使用するのでしょうか? こうすればいいよ、というのをご指導いただけると助かります。 重ね重ねお時間取らせて申し訳御座いませんが宜しくお願いします。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.6

ANo.3です。 7枚目のシート構成が今一つ不明なのですが。 どのセルにどのようなデータ(数式)があるのか不明ですし、合計が"0"になるケースが あるというのであれば、未入力時の月の結果は表示しないような対策が 必要なのかも知れませんけど、詳細が読み取れませんでした。 たぶん私の受取り方が変なのかも知れないです。

ns400
質問者

お礼

こちらこそ、説明不足に不手際で申し訳ございませんでした。 色々とお知恵を拝借いただいたことに感謝しております。 こちらも何分、理解していない部分も多かった為かえって n-jun様にはご迷惑をおかけしてしまいました。 またこの件でお知恵を授かれれば幸いです。 誠にありがとう御座いました。

ns400
質問者

補足

質問者のns400です。 まさにお話されている通り未入力の月の表示を0にする事が出来れば 答えは簡単だと思います。 1枚目から7枚目まで基本使ってる関数はSUMのみです。 SUM(シート名!A10:A35)等の関数で、入力されてない場合は セルを空欄にする関数が有るのでしょうか? 貴重なお時間を何度もとらせてしまい申し訳ないのですが、ご指導賜りますよう御願申し上げます。

  • hige_082
  • ベストアンサー率50% (379/747)
回答No.5

ほら、言ってるそばから、自分が間違ってる 笑ってやってください 年度で4月を1とするなら =CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) を =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) に訂正 ごめん

ns400
質問者

補足

御回答有難う御座います。色々ご指摘頂きました計算式を使ってみたのですが、平均値の計算が全然正確でない数字が出てしまいます。 何分、MONTH関数やTODAY関数を使ったことが無いので原因不明ですが >1月を1とするなら =SUM(売上トータル)/MONTH(TODAY()) とあったので 1月の売上平均の欄に書き換えてみました。 <書き換え前> =SUM(前期入力!A6:A7)     この際の合計は940,200  でした。 この数字が正解です。 <書き換え後> =SUM(前期入力!A6:A7)/MONTH(TODAY()) この際の合計値が104,677 となってしまいます。 ですから、合計欄の変更の >年度で4月を1とするなら =CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) を =SUM(売上トータル)/CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) に訂正 も、正確な数字が出ませんでした。 合計と入力用のシートに なにかMONTHやTODAYの定義付けが必要なのでしょうか? 色々ご面倒おかけ致しますが宜しくお願いします。

  • hige_082
  • ベストアンサー率50% (379/747)
回答No.4

質問の仕方が悪い、回答は正しい 1月を1とするなら =SUM(売上トータル)/MONTH(TODAY()) 年度で4月を1とするなら =CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9) たぶんこうゆう事でしょ

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.3

7枚目のSheetに全ての合計値があり、その平均値を出したいけど"0"があると AVERAGE関数では正確な値がでないので。。。 と言う質問に受け止めたのですが。違いますか? 従ってAVERAGE関数の代わりに、SUM(売上トータル)/5 としたいけど、 5が可変("0"でない月数)にしたいと言う事では? ですので、5の代わりにCOUNTIF(データの範囲,"<>0") を入れては? と言う回答なのですが。 ("0"でないデータの個数を求める) 質問の受取り方が違っているのかな?????

ns400
質問者

補足

色々懇切丁寧なご指摘有難う御座います。 説明不足で申し訳御座いません。 入力済み項目でも0が発生する事があります。 説明不足で申し訳ありませんでした。 これを回避する方法はございますか? お手数かけますがご教授宜しくお願いします。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.2

ANo.1です。 >COUNTIFは全く使っていません。 回答がまずかったですかね。 COUNTIFを使って欲しかったのですが。 本来AVERAGE関数で求めたかったデータの範囲 AVERAGE(データの範囲) の代わりに SUM(データの範囲)/COUNTIF(データの範囲,"<>0") として頂きたかったのです。 ただし、データ範囲の値が全て"0"になっている場合はエラーになりますので、 =IF(COUNTIF(データの範囲,"<>0")=0,"",SUM(データの範囲)/COUNTIF(データの範囲,"<>0")) とかの方がいいでしょうか。

ns400
質問者

補足

ありがとうございます。早速実行したのですが、この方法ですと実際に売上が0の項目も消えてしまいます。 いい手だと思ったのですが残念です。 他に手立てはないものでしょうか?

関連するQ&A

  • エクセルでシートを隔てての合計計算

    エクセルでシートを何枚も使い、 月ごとに数字を打つ作業をしているのですが、 年末で数字を合計したいのです。 しかし、毎月のシートで項目は同じなのですが、打ち込んでいるセルの場所が違う場合、同じ項目の合計をしたいのですが、できますか? たとえば シート1(1月)に項目「いろは」があるとして、 セルはA1とします。 シート2(2月)に同じ項目で「いろは」があるのですが、セルがA5になっている。 そのような感じでシート3(3月)、 シート4(4月)…と続いて12月まであります。 年末(12月)で項目「いろは」の1月~12月の数値を合計させたいのですが、可能でしょうか? 方法があれば教えて頂けないでしょうか。

  • エクセルで前月までの平均を出す方法

    http://oshiete1.goo.ne.jp/kotaeru.php3?q=1933831 上記をふまえた上で・・・ シート1には日ごとの売上 シート2には月ごとの売上がシート1からのSUM関数で表示 そこでやりたいことはというと 前月分までの売上の月平均を出したいのです! =AVERAGE(売上の合計,MONTH(NOW())-1) というところまで考えてみました 「MONTH(NOW())-1」で前月までの数値になりますよね? 問題なのが売上の合計です 1月から12月それぞれの売上合計を シート1の日別売上からSUMで出しています 月平均を出す合計は1月から12月を範囲指定した場合 現在の月も入ってしまいます これを前月までに自動的に計算する方法はないでしょうか? たとえば 今が4月だとして1月から3月までの売上合計・・・ といった具合です 現在ボクが考えられるのは 自動での指定は諦めて月初に前月までの合計をAVRAGE関数に指定する方法 これしかないでしょうか?

  • エクセルで複数のシートの合計を出したい

    エクセルで下記のような3つのシートがあります。 A列には科目、B列には数字が入っています。 sheet1     A   B 1 消耗品費 100 2 交通費  300 3 通信費  200 4 手数料  200 sheet2     A   B 1 交通費  100 2 消耗品費 100 3 手数料  200 4 通信費  200 sheet3     A   B 1 消耗品費 300 2 通信費  100 3 手数料  200 4 交通費  200 sheet1~3の科目ごとの合計をsheet4に出したいのですが、良い関数を教えてください。 A1にいつも「消耗品費」があるとは限りませんので、 =SUM(sheet1:sheet3!B1) という式が入りません。 また、A列に出てくる科目は同じものが2個出てくることはありません。 よろしくお願いします。

  • エクセルの合計と平均

    すみません、エクセルの合計/平均に関する質問です。 例えば、1月から12月までの売り上げの平均値をA2からL2に入力し、M2に1月から12月までの合計の平均を出したいと思います。 ですが、M2に=AVERAGE(A2:L2)と入力すると平均値の平均が出てしまいます。 1月~12月の合計÷365をM2に出したい場合どのようにしたら良いでしょうか。 また、例えば、7月まで入力されている場合、8月、9月の平均を入力していったら、自動的にM2に9月までの平均を出すにはどのようにしたら良いでしょうか。 (例:2月まで入力されている場合。A1、B1には1月の平均、2月の平均が入力されている。M2には、1月の平均×31+2月の平均×28÷59の計算が出るようににしたい) 分かりにくくて申し訳ございませんが、宜しくお願い致します。

  • エクセル 自動で別シートへ並べ替えて合計

    エクセルで請求書と入金確認の管理をしたいと考えています。 まず請求書を発行したら下記のような表に各項目を記入しています。  請求年月   件名   金額   入金年月 2011年4月 ●●●●費 200,000 2011年6月 2011年4月 ▲▲▲▲費 500,000 2011年5月 2011年5月 ■■■■費 300,000 2011年6月 2011年5月 ●●●●費 100,000 2011年7月 2011年6月 ▲▲▲▲費 500,000 2011年7月 2011年6月 ■■■■費 300,000 2011年8月 上記の項目の入力だけで、 ・請求年月ごとの合計金額 ・入金年月ごとの合計金額 が自動的に計算されるようにしたいと考えています。 入金月は取引先の支払いサイトに合わせて 翌月・翌々月払いとバラツキがあります。 タイトルにも記載した通り、別シートに入金年月が自動的に並び替えられて 入金月別の合計が計算されるようなものができると良いのですが、 それぞれの月別の合計が分かれば別シートでなくても作りは何でも構いません。 現状はシートを複製した後に入金年月をオートフィルで並び替え、 月ごとの合計金額をオートSUMで計算しているのですが、 毎月この作業が発生し、なおかつ手作業となりますので 間違いのないよう入力一回だけで済ませる方法をご教授ください。 しばらくこの方法で続けて、 辛抱強く回答をお待ちしますので お時間できましたらお力添えをお願いします。

  • Excelです。複数シート同一セルの合計

    Excelで日計表みたいなものを作っています。 A-1に日付 A-2に醤油ラーメン  B-2に売上金額¥2,530 A-3に味噌ラーメン  B-3に売上金額¥1,830 A-4に塩ラーメン   B-4に売上金額¥2,450 のような表が毎日繰り返されています。3月なら定休日の水曜を除いて26のシートがあります。 この味噌ラーメンの月合計を出すにはどうしたら良いのかお知恵を貸して下さい。B-2の3月の合計ということで・・・ ※架空でラーメンやさんの売り上げにしてますが、本当は違いますのでご了承下さい。

  • エクセル。複数のシートを使っての複雑な集計のやり方

    エクセル。複数のシートを使っての複雑な集計のやり方を教えていただきたいです。 縦は上から150期から155期まで、横は4月から3月まで(12カ月)でそれぞれ売り上げ額が入っています。 その下には各月の粗利益%が入っています。このような表が入った計10シートが同じファイル内にあることとします。 この10シートの集計をしたいのです。 私はよくわからなくって、すべてひとつひとつシートを選んではクリックしながらとても長い数式を入れていました。これでもできたのですが、 いろいろネットで調べたら、サム関数を使ってシートを一気に選択して(コントロールを押しながらシートを選択して)コピー出来る事を知りました。 それはなんとなくわかったのですが、売り上げの下にある、「粗利益〇%」の集計もしたいのですが、いまいち簡単なやり方がわかりません。 粗利益の合計を出すには→各月の売り上げ×その月の粗利益を、売り上げで割ると出るので、 それを地道に、たとえば155期4月の粗利益の集計を出す場合は、(1)シートの4月の売り上げ×粗利益+(2)シートの4月の売り上げ×粗利益+・・・(とこれが10シート続きます)これを、(1)シートの4月売上+(2)シートの4月売上+・・・で割るという数式になります。 10シートなので、相当長い数式になってしまいます。 これができたら他にひとつひとつコピーしていくというかんじでした。 上記のようなサム関数などでシートをまとめて集計するようなやり方ってないのでしょうか? 掛けたものを足してそれをまた割ってという場合は、簡単に集計するのは難しいのでしょうか? もっと簡単なやり方があったら教えていただきたいです。 わかりにくくすみませんが、わかる方、教えていただきたいです。

  • エクセルでシートごとに作成したものを年度合計する

    いつもお世話になります。エクセル初心者です。全員者が作成したものをどうにか新年度用に作成しようとしています。毎月作成したシートごとになっている同じ形式の月ごと(平成23年4月から3月)月報を同じ様式のシートで年間合計表を作るには、毎月の数値を合計表の同じセルに、12か月分の合計数値として入れるのに一番早い方法はどうすればよいのでしょうか? 教えて頂ければ助かります。よろしくお願いします。

  • エクセルの合計で質問です。

    エクセルで合計のことで質問させてください。 教えてください、1~9行目に仕入れ金額が入っていて10行目に1~9をオートSUMで合計したとします。 たとえば9行目と10行目の間に行を挿入して商品名をどんどん追加した場合には現在ではオートSUMが反映されません。 ドラッグすればいいかと思いますが単純作業なだけになんとか自動計算させたいです。 やり方を申し訳ございませんが教えてください。 宜しくお願い致します。

  • エクセルの集計・合計方法教えてほしいのです

    宜しくお願いします 当方は複数の店舗を運営しており毎日の日報を入力しております エクセルにて売上や客数など必要事項を入力しその各シートの数字を 当月で合計したシートをファイル内につくっています 各店ベースでは今どのようになっているかを見極めることはできるのですが、それぞれ(計7店あります)の合計シートをさらに まとめて閲覧できるようにファイルを作りたいのですが すべてのファイルを開けてポチポチSUMしていたのですが 非常に非効率で私がやり方を知らないだけかもと思い ぜひ、その方法を教えていただけたらと思います。 また、日々各ファイルの数字も変化していきますので まとめて閲覧できるファイルも自動的?に変更されるように できればと思いますし、毎月同じファイルを作るのも大変なので ひな形として置きたいとも考えております 長くなりましたがお知恵を拝借させて頂きたいです 宜しくお願い致します。

専門家に質問してみよう