Excel 複数シートの平均値

質問

競合店の統計表を作成しています。
一つのシートには、競合店の『販売コーナーごとの来客数』が数時間おきにとられたデータが入っている表が６店舗分。
このシートが１日１シートで月単位で１ブックとしています。
店休日があった場合、その日のその店の来客数の部分が現在は０表示になっています。
１日から例えば５日までの店舗ごとの来店客の平均値を求めたいのですが、
店休日を除いた平均値を求めるにはどのような数式で求めることができるでしょうか。
どなたかお教え下さい。

回答No.6

tascany · ベストアンサー

#3です。たいへん失礼いたしました。
で、ゼロを除外して平均を出すユーザー定義関数をいちおうつくってみました。こんなんでもお役に立ちそうなら、おためしください。
下記のコードをVBエディタの標準モジュールに貼り付けたらすぐ使えます。
使い方は、たとえば「＝平均(2,5,A1)」と記述した場合、左から二番目～五番目のシートの、A1の値の平均を求めます。で、値が0または空の場合は集計しません。
※注意点・・・(1)集計範囲に含まれるセルの値がかわっても、自動で再計算してくれません。Ctrl+Alt+F9で再計算できますが、それが面倒ならあらかじめ「＝平均(2,5,A1)+Now()*0」としておいてください。NOWがあるとすぐ計算してくれるようになりますし、「*0」してるので値に影響はありません。(2)たとえばA1:A3というように各シートで複数のセルにまたがる範囲を指定しても、A1の平均しか求めてくれません。ですので、複数シートにまたがる一個の座標の集計専用です。

Function 平均(x, y As Integer, a As Range)
Dim i, j, s, c
i = a.Row
j = a.Column 'セル情報。
s = 0 '合計
c = 0 '0より大きいデータの個数
For counter = x To y '0より大きなデータだけ集計していく。
　If Sheets(counter).Cells(i, j).Value > 0 Then
　　　c = c + 1
　　　s = s + Sheets(counter).Cells(i, j).Value
　End If
Next
平均 = s / c
End Function

お礼

ご丁寧にありがとうございます。
まだ実際に動かしてはいませんが（外なので）、
拝見したところ、有効に利用できそうです。
頑張ってみます。

回答No.5

imogasi

質問のシート内容の質問
（１）シートの様子
（１シート＝１日分）－１月分＝１ブック
１２月１日のシート
コーナー　来店客数（１０時）来店客数（１２時）来店客数（１５時）・・・
Ａ店　　　２３　　　　　　　　３０　　　　　４２
Ｂ店　　　・
Ｃ店　　　・
Ｄ店　　　・
Ｅ店　　　・
Ｆ店　　　・
のような表ですか？
（２）「数時間おき」の回数は、毎日同じ回数ですか
日によってバラバラですか？

回答No.4

BLUEPIXY

店休日が0でなく空白だったら
単純にAVERAGE(１日:５日!A1)で求められるのですが、
なかなか難しいですね。
店休日は、空白にする（値を入力しない）というのはだめですか？

お礼

本当に難しいです。
あちらこちらで調べたりもしましたが、何処にも見当たらずでして。
やはりこれはゼロでは良い方法が無いということなのでしょうね。

回答No.3

tascany

店休日に入ってるゼロのデータを抜き取って空にすると、AVERAGE関数でふつうに範囲指定してやるとそこだけ除外されます。
なお（以下、もしご存じなら、失礼・・・）。範囲指定のさいはSfiftキーを押しながら、たとえばシート１とシート５をクリックしたうえで、平均を求めたい値が入ってるセルを選んでください（フォームはすべて同じですよね・・・？）。こうすると、シート１とシート５とのあいだにあるシート上のセルもすべて指定したことになります。

お礼

ゼロでなく空白の場合には私もそれを考えていたのですが、
出来ればゼロで平均が出る良い方法をどなたかご存知ないかと思いまして…

回答No.2

s_yoshi_6

集計をどのような形でされるのか分かりませんので、もしかしたら求められているものと違うかも知れませんが、そのような場合私だったらこうするという方法を。

1）「月単位の１ブック」の中に「月集計シート（仮にSheet0）」を作る。

2）Sheet0の適当な列を毎日の来客数の合計の表示欄とする（仮にＡ列でA1:A31）。

3）毎日のシートがSheet1～Sheet31まであって、そのB1にその日の来客数の合計が記入されているとした時に、Sheet0のA1に
=IF(ISERROR(INDIRECT("Sheet"&ROW()&"!B1")),"",INDIRECT("Sheet"&ROW()&"!B1"))
と入力してA31までコピー。
（「ROW()」はそのセルの行番号になるので、１行目(A1)ではなく、例えば２行目(A2)にSheet1の参照値を表示したい場合は「ROW()」を「ROW()-1」とする）

4）Sheet0のＢ列を５日おきの平均来客数の記入欄として、適当なセル（仮にB5）に
=SUM(A1:A5)/COUNTIF(A1:A5,">0")
と入力して平均値を出す。

おそらく毎日の集客数の一覧はどこかに作られるんじゃないかと思いましたので、それを利用する方法です。

お礼

考えていただきありがとうございます。
ただ、一読では少々想像することが出来ませんで…
試しに作って比較・検討させていただきます。

回答No.1

tokpy

この場合，ある期間の平均値を求める式は，

　（平均値）＝（来客数の総和）÷（日数）

となるはずです。普通は7日分の平均値なら（日数）＝7ですが，休日が1日含まれるなら，（休日の来客数）＝０を加えても加えなくても，（来客数の総和）は変わらないので，（日数）＝6としてやるだけで，6日分の平均になりますね。
（来客数）＝0の日を休日とみなすなら，0の日の数だけ（日数）から引いてやれば，平均値が出せます。

　（平均値）＝（来客数の総和）÷{（日数）-（来客数0の日の日数）}

面倒ですが，自分で式を書くのが手っ取り早いと思います。答えになっていなかったらごめんなさい。

お礼

そうですね、確かにそれで求められるのは理解出来るのですが…。
効率的で、スマートな方法をちょっと考えています。
欲張りですよね。なかなか上手くいかなくて。