[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『月ごとの平均値計算の方法』（笛吹き）
初歩的な質問だとは思いますが、教えてください。
４月から翌年３月まで１年間の数値が入る表のなかで自動で平均値が出るようにしたいのですが、どのようにすればよいでしょうか？
仮にＡ２～Ｌ２のセルに４月～１２ヶ月分の数値が入っていればその１２ヶ月の平均を出す計算式は
「=SUM(Ａ2:Ｌ2)/12」
とかになると思うのですが、今回お伺いしたいのが、入力された月までの月数に応じて平均値を自動計算するような計算式を知りたいと思っています。
４月、５月と入力されていれば、２ヶ月分なので合計を２で割り平均を出す。４月から８月まで数値が入力されていれば、４か月分なので自動で４で割り平均を出すと言うように入力されている月の数を認識して平均値を計算させたいと思っています。
Ａ Ｂ Ｃ Ｄ
------------------------------------------- 1 ４月 ５月 ６月 ７月
2 2 4
3
4
だと２ヶ月分の入力があるので２か月分の平均を出す。
Ａ Ｂ Ｃ Ｄ
------------------------------------------- 1 ４月 ５月 ６月 ７月
2 2 4 1
3
4
だと３ヶ月分の入力があるので３か月分の平均を出す。
このような表の自動計算方法はあるでしょうか？
エクセルは２０００です。
宜しくお願い致します。
例えばＭ１セル等に
=IF(COUNT(A2:L2),SUM(A2:L2)/COUNT(A2:L2),"")
等でいかがですか？
これでもよさそう
=IF(COUNT(A2:L2),AVERAGE(A2:L2),"")
(MJ12)
追記：M1セルよりM2セルが妥当ですよね(^_^;)
でも問題が出てしまいました。
先ほどの表なのですが、各月Ａ２～Ｌ２（4月、5月、6月・・・・）に入る数値は別の表での合計数が自動で入るようにしてあるので、まだ入力のされていない月には「０．０」と表示されている状態になっています。
教えていただいた式だと、まだ未入力の「０．０」と入った月も数値が入ったとみなされてしまい、7月ぐらいまでしか入力してなくても8月以降も「０．０」と入力されている為に合計を一年分である１２で割った数字となってしまっています。
「0.0」を認識せずに計算を行って欲しいのですが、万が一「０．０」の数値の月が出た場合その月は省かれて計算されてしまうのでそれも困ってしまいます。
何か良い方法はありますでしょうか？
（笛吹き）
=IF(COUNT(・・・・)=0,"",SUM(・・・・)) として表示されないようにするのは。 (NB)
=IF(SUM(A2:L2)>0,SUM(A2:L2)/(COUNT(A2:L2)-COUNTIF(A2:L2,0)),"")
0.0って数値ですよね？
数値であれば上記でできると思います★
でも0.0と表示させなくていいのであれば、NBさんのような数式を
該当セルに使用した方がスッキリしていいと思いますよ♪
(MJ12)
>各月Ａ２～Ｌ２（4月、5月、6月・・・・）に入る数値は別の表での合計数が自動で入るようにしてあるので、 >まだ入力のされていない月には「０．０」と表示されている状態になっています。
>「0.0」を認識せずに計算を行って欲しいのですが、 >万が一「０．０」の数値の月が出た場合その月は省かれて計算されてしまうのでそれも困ってしまいます。
1 ４月 ５月 ６月 ７月 2 2 4 0.0 0.0
この表で、5月まで2か月の平均を出すのか、6月を0として3か月の平均をだすのか、 あるいは7月も0.0として4か月の平均をだすのか、 どのように判断するのですか? (tora)
>万が一「０．０」の数値の月が出た場合その月は省かれて計算されてしまうのでそれも困ってしまいます。
この部分…よく読んでませんでした(・・;)
万が一0.0と表示されると、多分割る数に入るのでは…
(MJ12)
＞(NB)さん
ありがとうございます。 でも何処にこの式を適用してよいか分からずでした・・・。
すみません初心者過ぎて・・・。
＞ (MJ12)さん
数式を入力したのですが、仮に４月「０．８」５月「０．５」６月「０．８」７月「１．０」の数値が入っている場合、合計が３．１になりこれを４で割ると「0.775」なのですが、計算式から算出された数値は「0.75」でした。 別の列にも「1.8」「1.0」「1.5」「0.5」の数値「4.8」の平均が「1.19」と微妙に平均値より少ない数値が出てしまいました。セルの書式設定などが悪いのでしょうか？
＞(tora)さん
この表の場合は５月まで入力されてるので２か月分と判断するようにしたいのですが、その判断方法もどうすれば良いか分からない状態です。
＞ (MJ12)さん
やはりそうですか、0.0が入る限りは割り数に入りますか・・・。
（笛吹き）
A2～L2セルにはどんな数式が入ってますか？
その数式を参照先が０の場合は非表示にする…という方法を
使用すると今の数式で使えると思います。
NBさんも同じことを言ってらっしゃるのでは？と思いますが(^_^;)
それと、計算結果ですが、自分で試したみたところ…
0.775 1.2 と表示されますが…
笛吹きさんの 0.0には ２つの意味があるんですよね？
入力されて0.0の場合と 何も入力されず0.0の場合…
ですから、後者の方を表示しないようにしてはいかがですか？
(MJ12)
(ＭＪ１２）さんへ
実は実際に使用している式では、４月から翌年３月までの１２ヶ月間の数値を「Ｅ１１セル～Ｐ１１セル」に入力している為、先ほど教えていただいた式のＡ２Ｌ２を、下記のようにＥ１１とＰ１１に変えて入力したのですが、それが駄目だったのでしょうか？
=IF(SUM(E11:P11)>0,SUM(E11:P11)/(COUNT(E11:P11)-COUNTIF(E11:P11,0)),"")
ためしにＡ２～Ｌ２に数値を入力しＭＪ１２さんに教わった式を入力したところ、見事に数値が計算されました。
Ａ２～Ｌ２ではなくＥ１１～Ｐ１１に変えたいのですが、入れ替えるだけでは駄目なのでしょうか？
Ｅ１１～Ｐ１１には別のセルで出た数字が表示される式？が入ってます「＝Ｍ１０」などです。
（笛吹き）
初心者ですので、本当にご迷惑をおかけしております。
＞入力されて0.0の場合と 何も入力されず0.0の場合…
＞ ですから、後者の方を表示しないようにしてはいかがですか？
何も入力されずに０．０を表示しない方法とはどのような方法でしょうか？
（笛吹き）
入れ替えても問題ありませんよ！！
=M10 等が入っている…
=M10 では、M10に何も入力されていない場合でも 0 と表示されるはずです…
が、 =if(M10="","",M10) とすると何も表示されないはずです。。。
IF分を使用して、入力されてない箇所は表示しないようにしてみて下さい！！
(MJ12)
色々と有難うございます。
教えていただきました「=if(M10="","",M10)」の式で確かに表示がなくなりました。
・・・でそのＭ１０のセルですが、そのセルには月の平均を出す式「=(M24+N24+O24+P24)/4」が入っているので何も入力してなくても「０．０」の数値が入っています。
ですので、この月の平均を出しているセルの式「=(M24+N24+O24+P24)/4」に教えていただいた、入力がない場合は表示しない「=if(M10="","",M10)」の式を合わせることが出来れば解決するのかな？と思っているのですがどうでしょうか？
その通りですね★
M10セルに
=IF(COUNT(M24:P24),AVERAGE(M24:P24),"")
を入れてみて下さい♪
自分…今日はココまでしか出てこれません(^_^;)
(MJ12)
（笛吹き）
教えていただいた式を色々と組み合わせた結果完成いたしました。
ありがとうございました。
そうですか★それは良かったです！！
頑張って下さい♪
(MJ12)
もうひとつ教えていただいてもよろしいでしょうか？
作成した表からグラフを「折れ線作成」しているのですが、参照しているセルには
「=IF(COUNT(M23)=0,"",SUM(M23))」の式が入っています。
上記式が入っている表を作成する中で、まだ数値の入っていない所（月）、例えば今ですと１１月と１２月は空白なのですが、計算式が入っている為に「０」と認識？してしまい、作成した「折れ線グラフ」が１１月から下がってしまいます。
棒グラフにすれば、「０」だと表示されないので良いのですが、どうしても「折れ線グラフ」で流れを見たいのです。
せっかく４月～１０月まで順調に流れがわかっても、突然１１月でがくんと折れ線が落ちてしまうので
成績が落ちたように勘違いしてしまいます。
説明不足かもしれませんが、何とかする方法はありますでしょうか？
（笛吹き）
それもＩＦ関数で０を表示しなければいい話ですね…
それと =IF(COUNT(M23)=0,"",SUM(M23)) この数式ですが…
余計な関数が二つ入っていると思います。。。参照先が１セルだけですから
このような場合に =IF(M23="","",M23) としてはいかがでしょう？
COUNTもSUMも必要ありませんよ★
(MJ12)
また説明不足でした。順を追って内容をお話いたします。
①まず一ヶ月を４週に分けて、各週ごとの数値を入力します。
４月の場合はセル「Ｍ３８～Ｐ３８」です。
Ｍ３８には「=IF(COUNT(M30:M37)=0,"",SUM(M30:M37))」の数式が入っております。
②第一週から第４週までの数値（Ｍ３８～Ｐ３８）の平均値を求めるためにセル「Ｑ３８」に計算式
「=IF(COUNT(M38:P38),AVERAGE(M38:P38),"")」の入力を行いました。
③算出した月の平均値をセル「Ｅ１１」に作成した表に表示させる為に「Ｅ１１」セルに「＝Ｑ３８」を入力いたしました。
④セル「Ｅ１１」から３項目１２か月分でセル「Ｐ１３」までの表を折れ線グラフに作成。
作成した折れ線グラフを表示すると、経過していない月も「０」として表示されてしまう。
入力月で折れ線グラフが終わるようにしたいが、計算式が入っているので「０」を認識？してしまい年度末まで折れ線グラフが０で続いてしまいます。
この作成方法ではどうしても仕方ないのでしょうか？
宜しくお願い致します。
（笛吹き）
意味が分かりました(^^ゞ
５月のセルが何処にあるか分からないので
例えば…R38が５月のセルだとして S38・６月…と並んでるとします。
F11に
=IF(R38="",IF(E11="","",E11),R38)
を3月までコピペ！！こんな感じでグラフは大丈夫だと思います。
セルの箇所は違うでしょうから,ご自身で変更してみて下さい★
(MJ12)
いつもありがとうございます。
試してみました。
しかしやり方が悪かったのか、やはり未入力部分に対して「０」で折れ線グラフが繋がってしまいました。
セルＥ１１に4月分「=IF(Q24="",IF(E11="","",E11),Q24)」
セルＦ１１に5月分「=IF(V24="",IF(F11="","",F11),V24)」
セルＧ１１に6月分「=IF(AA28="",IF(G12="","",G12),AA28)」
と入力しました。
セルＥ１１には「１．０」と表示
セルＦ１１には「０．８」と表示
セルＧ１１は未入力なので「０．０」と表示されています。
この状態を折れ線グラフにしたのですが、5月と6月がつながってしまいました。
仮に表をつくり、4月・5月分を入力し6月以降に何も数値が入っていない場合にグラフを表示すると
折れ線グラフは4月5月で終わると思うのですが、今回は6月以降も計算式が入っている為に、6月以降は０と認識？され、5月から6月にかけて折れ線グラフが繋がってしまう状態が発生しています。
式が入ってる限り仕方ないのでしょうか？
（笛吹き）
参照先のセル位置が変わってますね(^_^;)
4月分は =Q38 ではありませんでしたか？それと御掲示の数式では循環参照に
なるはずです！！
ではとりあえず、Q24 に変更ということで…
E11（4月分）には
=IF(Q24="","",Q24)
F11（5月分）には
=IF(V24="",IF(E11="","",E11),V24)
後は5月分の要領で3月分まで入れると前月表示分がそのまま反映されるはずです！！
6月分のセルは AA28 になってますが AA24 では？
と思いましたので表示はしてません(^^ゞ
(MJ12)
ありがとうございます。
参照先が変わってましたね。最初が間違っていました、すみません。
それに6月分のセルも間違ってました。おっしゃるとおりＡＡ２４でした（笑）
対象セルに式を入力したところ、確かに前月表示分が表示されました。
この状態だと、数値の入力をしていない6月以降も最終入力月の5月分「0.8」を引き継いで表示するので
グラフ上も「0.8」の位置で横棒になってしまいます。
前月分の数値を反映せずに、数値未入力から先の月はグラフ上に表示させない方法はありますでしょうか？
何度も何度も本当にすみません。 （笛吹き）
>数値の入力をしていない6月以降も最終入力月の5月分「0.8」を引き継いで表示するので >グラフ上も「0.8」の位置で横棒になってしまいます。
はい！横棒になります…そうなるようにしてました(・・;) 違ったんですね。。。
これって数式がどうのこうの…ではなくグラフの問題ですね。。。
要するに入力した月までのグラフを表示したい…という話ですよね？
であれば、とりあえず…E11～P11 までの数式を
4月分 =IF(Q24="","",Q24) 5月分 =IF(V24="","",V24) ・・・とこの要領で最後まで
そしてグラフの設定です！！
グラフのシートのプロットエリアで右クリック⇒データの選択 をクリック…
データソースの選択画面に 『非表示および空白のセル』 をクリック
空白セルの表示方法： で空白を選び
非表示の行と列のデータを表示する…に☑が入っていたら外して下さい！！
そしてＯＫで今度こそ完了。。。のはず(^_^;)
(MJ12)
何度も本当にありがとうございます。
こちらで使っているエクセルのバージョンが古いからでしょうか・・・・。
教えていただいた項目が見つけられません（泣）
エクセルのバージョンは２０００です。
プロットエリアで右クリックを行うと
○プロットエリアの書式設定
○グラフの種類
○元データ
○グラフのオプション
○グラフの場所
○３－Ｄグラフ
○グラフウィンドウ
○クリア
と表示されます。
「元データ」や「グラフのオプション」などを見てみたのですが、
見当たりませんでした。
もしご存知なら教えていただいてよろしいでしょうか？
（笛吹き）
こちらが参考になると思います！！
試してみて下さい★
(MJ12)
別のＰＣに新しいエクセル２００７で良かったでしょうか？が入っていましたので試したところ、お伺いした項目が見つかりました。
教わったとおりに行ってみましたが、残念なことに変化がありませんでした。
その後、教えていただいたＨＰを参考にエクセル２０００で再トライしてみましたが、駄目でした。
こちらのやり方が悪かったのかもしれません・・・。
やはりあきらめるしかないのでしょうか？
（笛吹き）
変化ありませんでした？自分は2007ですが、
非表示の部分はグラフに反映されませんでしたよ！！
数式をみて非表示になってるか再確認してみて下さい★
何度も修正してるので、もしかしたらそのままになってませんか？
(MJ12)
そうですか・・・やはりこちらのやり方が悪いんでしょうね・・・。
教えていただいたとおり
表には以下の数式を入れております。
4月分の場所 セルＥ１１には「=IF(Q24="","",Q24)」
5月分の場所 セルＦ１１には「=IF(Q24="","",Q24)」
といった数式が入っています。 表の中には未入力部分には何の数値も出なくなってます。
間違っている場所が無いか確認してみます。
（笛吹き）
5月分の場所は
=IF(V24="","",V24)
でしたよね(^^ゞ やはり何処か間違ってるような気がしてなりません(^_^;)
エクセル2000での確認はコチラでは出来ないのが残念です(/_;)
(MJ12)
試しに別の場所に、同じような表を作ってみて、グラフのオプション等を試したのですが、
やはり未入力の０部分にも折れ線グラフが繋がってしまいました。
こちらの入力で何か根本的にやり方の間違いをしているのかもしれません。
初心者なのでミスを見つけることも出来ないので辛いです。
ＭＪ１２さんがこちらのファイルを見ればすぐに直せるでしょうね。
あ～知識が欲しい！
色々触ってると、知識は地道についていくと思います♪
と言ってる自分もまだ勉強中です(^_^;)
もう一度オサライしないといけませんね★
今日はココまでです(^^ゞ
(MJ12)
私事ですみませんが明日一日外出しておりますので
また来週にご教授くださいませ。
宜しくお願い致します。
（笛吹き）
>例えば今ですと１１月と１２月は空白なのですが、計算式が入っている為に「０」と認識？してしまい 多分正解です グラフの作成範囲に名前の定義で作成した範囲を使用すると良いのかな？ [挿入] メニューの [名前] →[定義] [名前] ボックスに 範囲と入力 [参照範囲] ボックスに =OFFSET(基準,0,0,1,幅) 基準：グラフの最初のセル （例$E$11) 幅：表示したい月数を入力したセルアドレス(例$Z$1) Z1の値によりグラフが拡がると思います。 方法は異なるが 参考：可変範囲のグラフ http://www2.odn.ne.jp/excel/waza/graph.html#SEC13
教えていただいたのを試みているのですが、初心者ゆえにイマイチ分からず（笑）
=OFFSET(テスト!$E$11,0,0,1,テスト!$P$13)
と定義に入力いたしました。
そこからどうすれば良いのかわからず立ち往生です。
「参考」を参照しながら「系列」等に入力しましたが上手く行きませんでした。
どのようにすれば良いでしょうか？
（笛吹き）
計算式を入れていないグラフを作成した後、前回教えていただいた「空白セルの・・・」
等の作業を行うと、折れ線グラフ上で数値の入力されている部分と入力されていない部分が
繋がっていないグラフが出来上がりました。
しかし何かしらの計算式を入力するとやはり未入力部分も繋がる折れ線グラフが出来てしまいました。
何かしらの計算式を何処に入れたのですか？
2007もあるとのことで…少し上で説明した通りで
できると思うのですが(^_^;)
非表示の箇所は反映されないように…０は入力後に０という結果もあるという事でしたので
０値が表示されるとグラフには反映されますよ★
(MJ12)
ありがとうございます。
もう一度見直してみます。
どこかで何かを間違ってるのだと思います。
また分からないことがあったときは宜しくお願い致します。
（笛吹き）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.