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

質問

今excel2003で毎月の客先売上金額、粗利などを6枚のシートで客先ごとに分類し7枚目のシートに合計金額と平均値を出しています。

そこで、全ての合計と平均のある7枚目のシートで
1～12月まであった場合
SUMを利用して反映されているので
まだ、この年度で数字が確定していない月（今でしたら9月以降）の
項目に数字の"0"が入ってしまいます。

この為にAVERAGE関数を使うと12ヶ月分が丸々反映されてしまう為、正確な数字がでません。
このため現在はセルに　合計金額/今年度消化した月間数
（例）
SUM(売上トータル）/5

といった感じで手作業で割り算して平均を出しています。

これを自動的に毎月、手作業で割り算せずに、消化した月数で平均を出す事は出来ないでしょうか？

初歩的な質問かもしれませんが何卒御回答宜しくお願いします。

回答No.10

hige_082 · ベストアンサー

NO.4・5・9です
CHOOSE(インデクス，値１，値２，値３・・・・・・・・）は
インデクスの数番目の値を求める関数
この辺の説明が、まずかったようですね

＞当社の場合５月決算なので×年度で５月が初月です
で
＞=SUM(売上トータル）/CHOOSE(MONTH(TODAY()),2,3,4,5,6,7,8,9,10,11,12,1)
は、ちょっと違います
MONTH(TODAY()の部分は理解されてるようで、今月は９月なので「９」が値として帰ってきます
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)となり、帰ってくる値は
９はインデクスなので、９の次の数字が、値１となり
値１＝２、値２＝３・・・・値８＝９、値９＝１０、値１０＝１１・・・
インデクスは９なので、帰ってくる値は値９、即ち値９＝１０が帰ってきます
=SUM(売上トータル）/１０　と成ってしまいます
そこで
=SUM(売上トータル）/４　としたいということは
９月＝４ということは、値９＝４　にならなければ、成らないので
CHOOSE(9,,,,,,,,,4,,,)と基準を決めてしまいます
８月＝値８＝３、７月＝値７＝２
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")　「９」が帰ってきます
では、日付部分を"h20.1.8"や"平成5年10月15日"に変更して、「１」や「１０」がちゃんと帰ってくるのか、それともエラーになるのか
これをすることによって、CHOOSEのインデクスには１～１２が入ることが分かると思います
これで９月に「４」という値が帰ってくるのかの確認が出来ます
=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)
この作業をすることによって、応用しやすくなると思います
以上長文失礼しました

補足

すいません。基準を決めなければならないというのはわかったのですが、その基準を入れる場所が私の力不足でわかりません。

そこさえ教えていただいたらいけるような気がします。
宜しくお願いします。

回答No.12

n-jun

ANo.11さんへ

質問は既に移動しています。

CHOOSE関数でインデックスを可変にした場合の値の変更
http://okwave.jp/qa4312563.html

回答No.11

hige_082

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))

いくつか作ってみましたが、すべてやっていることは同じです
理解できるものがあればどうぞ
理解できないなら、使用しない方が良いでしょう
後々困ると思うので

回答No.9

hige_082

NO.4・5です
解説をしておけば良かったですね
＞このため現在はセルに　合計金額/今年度消化した月間数
との事でしたので、そのまま式にしました
私は
7枚目のシートに
１月合計　1000円
２月合計　1000円
３月合計　1000円
４月合計　1000円
　　　・
　　　・
１１月合計　0円
１２月合計　0円
売上トータル　9000円　　
月平均　1000円
のような表の月平均の関数を質問している、と、解釈しました

関数の解説すると
１月を１とするなら←この説明が良くなかったようで、すみません
=SUM(売上トータル）/MONTH(TODAY())
TODAYは今日の日付を求める関数＝2008/9/4と成ります
MONTHは日付から月を求める関数＝MONTH("2008/9/4")＝9と成ります
例として
月1000円の売上があるとします
月の売上　×　９月　＝　９０００円
上の関数から　MONTH(TODAY())　＝　９
=SUM(売上トータル）/MONTH(TODAY())　に当てはめると
＝９０００円　／　９月　＝　平均１０００円

×年度で４月を１とするなら
４月を１ヶ月目とするなら
=SUM(売上トータル）/CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9)
CHOOSE(インデクス，値１，値２，値３・・・・・・・・）は
インデクスの数番目の値を求める関数
インデクスが１なら値１、３なら値３
CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9)
の場合は１月＝１０、４月＝１、９月＝６と成ります
例
月の売上が1000円で、今日が９月だとすると
＝６０００円　／　９月は６　＝　平均１０００円

結果を数えるのではなく、今日が何月かを求めて分母を決めています
そうすることによって、売上が０円の月があっても対処できるのではと考えました

説明が下手で、すみませんでした

補足

丁寧なご説明有難うございます。

hige_082様の解釈で全く相違御座いません。本日、CHOOSE関数を使い実行してみました。

当社の場合５月決算なので×年度で５月が初月ですので

=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とでてきました。

説明いただいてる部分で
＞ｘ年度で4月を1とするなら

の解釈が間違っているきがしたので色々HPを見て調べてみましたが
正解がわかりませんでした。

引き続きお手間おかけするのも心苦しいですが、hige_082さんの解釈で私の思っている事は出来上がります。
お手間おかけ致しますが最後までご指導いただけますようお願い申し上げます。

回答No.8

n-jun

ANo.7です。

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

補足

御回答有難う御座いました。

=IF(COUNTBLANK(前期入力!B95:B96)=0,SUM(前期入力!B95:B96),"")

という形で試してみました。
（7枚目の集計シートの月間売上種類別合計部分です。）
ちなみに前期入力とあるシートのB95～B96の合計値が種類別合計です。
B95はx月度種類別売上の合計が入っており
B95＝SUM(B97:B137)といった形で売上が合計されています。
未入力の月間の場合はなぜか0を反映しています

しかし、全く集計された7枚目のシートの未入力部分に空欄が返りません。この数式は1枚目から6枚目の合計数値を出しているセルにいれるべきなのでしょうか？

お手間おかけ致しますが宜しくお願いします。

回答No.7

n-jun

ANo.6です。

入力した範囲の平均値を出したいのか、数式の結果の範囲で平均値を出したいのか。。。？
補足等のSUM関数もよく分からないですし、その範囲もまちまちですし。
合計を求めたいのか、平均を求めたいのか、そして範囲はどこなのか。。。？

どこにどのようなデータがあり、それをどこでどのような数式を用いて
どんな結果が求められ、そしてどうしたいのか。
って所でしょうか。

問題は
>合計金額/今年度消化した月間数
この”今年度消化した月間数”ですよね？
６枚のシートから月間数を求めるのか、７枚目のシートに集約された
データから判断できるのかは、その情報で決まりますよ。

仮に７枚目のシートのＡ１～Ｌ１（12ヶ月分）に各月のデータが集約されるように
なっているとします。
未入力時にデータの結果が"0"になるのであれば、"0"でないデータの個数が"今年度消化した月間数"になりますし、
集計で結果が"0"になる場合もあるのなら未入力時をＩＦ文で判断させれば良いと思います。

補足

色々とご指摘有難う御座います。

おっしゃるとおり7枚目のシートに12か月分の各月のデータが集約されるようになっております。
集計結果が”0”になる場合もございますので未入力時をIF文で判断させたいと考えております。

この場合のIF文はISBRANKを使用するのでしょうか？
こうすればいいよ、というのをご指導いただけると助かります。

重ね重ねお時間取らせて申し訳御座いませんが宜しくお願いします。

回答No.6

n-jun

ANo.3です。

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

たぶん私の受取り方が変なのかも知れないです。

お礼

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

またこの件でお知恵を授かれれば幸いです。
誠にありがとう御座いました。

補足

質問者のns400です。
まさにお話されている通り未入力の月の表示を0にする事が出来れば
答えは簡単だと思います。

1枚目から7枚目まで基本使ってる関数はSUMのみです。
SUM(シート名!A10:A35)等の関数で、入力されてない場合は
セルを空欄にする関数が有るのでしょうか？

貴重なお時間を何度もとらせてしまい申し訳ないのですが、ご指導賜りますよう御願申し上げます。

回答No.5

hige_082

ほら、言ってるそばから、自分が間違ってる
笑ってやってください

年度で４月を１とするなら
=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関数を使ったことが無いので原因不明ですが

＞１月を１とするなら
=SUM(売上トータル）/MONTH(TODAY())

とあったので

1月の売上平均の欄に書き換えてみました。
＜書き換え前＞
=SUM(前期入力!A6:A7)　　　　
この際の合計は940,200　　でした。
この数字が正解です。

＜書き換え後＞
=SUM(前期入力!A6:A7)/MONTH(TODAY())
この際の合計値が104,677

となってしまいます。

ですから、合計欄の変更の
>年度で４月を１とするなら
=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の定義付けが必要なのでしょうか？

色々ご面倒おかけ致しますが宜しくお願いします。

回答No.4

hige_082

質問の仕方が悪い、回答は正しい

１月を１とするなら
=SUM(売上トータル）/MONTH(TODAY())
年度で４月を１とするなら
=CHOOSE(MONTH(TODAY()),10,11,12,1,2,3,4,5,6,7,8,9)

たぶんこうゆう事でしょ

回答No.3

n-jun

７枚目のSheetに全ての合計値があり、その平均値を出したいけど"0"があると
AVERAGE関数では正確な値がでないので。。。
と言う質問に受け止めたのですが。違いますか？

従ってAVERAGE関数の代わりに、SUM(売上トータル）/5　としたいけど、
5が可変（"0"でない月数）にしたいと言う事では？
ですので、5の代わりにCOUNTIF(データの範囲,"<>0")　を入れては？
と言う回答なのですが。
（"0"でないデータの個数を求める）

質問の受取り方が違っているのかな？？？？？

補足

色々懇切丁寧なご指摘有難う御座います。
説明不足で申し訳御座いません。

入力済み項目でも0が発生する事があります。
説明不足で申し訳ありませんでした。

これを回避する方法はございますか？

お手数かけますがご教授宜しくお願いします。

回答No.2

n-jun

ANo.1です。

＞COUNTIFは全く使っていません。
回答がまずかったですかね。
COUNTIFを使って欲しかったのですが。

本来AVERAGE関数で求めたかったデータの範囲
AVERAGE(データの範囲)
の代わりに
SUM(データの範囲)/COUNTIF(データの範囲,"<>0")
として頂きたかったのです。

ただし、データ範囲の値が全て"0"になっている場合はエラーになりますので、
=IF(COUNTIF(データの範囲,"<>0")=0,"",SUM(データの範囲)/COUNTIF(データの範囲,"<>0"))
とかの方がいいでしょうか。

補足

ありがとうございます。早速実行したのですが、この方法ですと実際に売上が0の項目も消えてしまいます。

いい手だと思ったのですが残念です。
他に手立てはないものでしょうか？