EXCEL関数について

質問

毎月、各曜日毎のとある集計表の作成をしております。
マクロや関数を利用し、毎月の作業簡易化を目指し奮闘中なのですが、壁に当たってしまったためお力添え下さい。

7月であれば1～31までのシートがあり、日にちをシート名にしてあります。
各曜日「A1」セルの値の平均値を出したいのですが、毎月第4週までと第5週までの曜日が異なる上、祝日もありうまく関数が組めません。
各シートを「月曜1、月曜2・・・」と名前を変更し、セルに関数AVERAGE(月曜1!A1,月曜2!A1,・・・)と範囲指定するマクロを作りましたが、上にも書いた通り、毎月第4週までと第5週までの曜日が異なるため、6月の曜日に合わせたセル範囲を7月に適用させるといくつかセル範囲無効によるエラーが表示されます。（#REF!）
範囲指定したシートのうち、存在するシートのみが反映され、無いシートを無視することができれば私にとって理想なのですが、そのような方法はありますか？

回答No.3

hallo-2007 · ベストアンサー

>7月であれば1～31までのシートがあり、日にちをシート名にしてあります。
日ごとにシートが増えて、月ごとにファイルが増える構成でしょうか？
どうしても、後から集計や分析を行うには適切でないデータの構成です。
とりあえずですが
別途、集計のシートを準備して
1行目に　
日付　曜日　情報１　情報2・・・
と横方向に項目を準備
マクロで
各シートの情報が一つのシートにまとめられるようにします。
例えば
Sub ボタン1_Click()
Rows("2:1000").ClearContents
For i = 2 To Worksheets.Count
Range("A" & i).Value = Worksheets(i).Range("B1").Value'日付の情報
Range("B" & i).Value = Worksheets(i).Range("B2").Value'曜日の情報
・・・・
Next
End Sub
とかを実行し見ては如何でしょうか。
出来上がったシートから集計や分析が行えると思います。
将来的には、データの入力方法から分析までを考えたシート構成に変更することをお勧めします。

回答No.2

kagakusuki

今仮に、各日毎のシートのシート名が、
1
2
3
4
・
・
・
30
31
という具合に、数字のみからなる名前となっていて、
各曜日毎のシートのシート名が、
月曜日
火曜日
・
・
・
日曜日
という具合に、シート名の最初の文字が、必ず月、火、水、木、金、土、日の内の何れかの文字であるものとします。

　まず、適当な使用していないシート（ここでは仮に、そのシートは「補助」という名前のシートであるものとします）に、次の様な休日のリストを作成して下さい。（A列の休日の名称は、判り易くするためだけのものですので、必ずしも入力しなくてはならないものではありません）

　　　　　　A列　　　　　　　　　　B列
1行目　　休日の名称　　　　　年月日
2行目　　元日　　　　　　　　2011年1月1日
3行目　　成人の日　　　　　2011年1月10日
4行目　　建国記念の日　　2011年2月11日
5行目　　春分の日　　　　　2011年3月21日
6行目　　昭和の日　　　　　2011年4月29日
7行目　　憲法記念日　　　　2011年5月3日
8行目　　みどりの日　　　　　2011年5月4日
9行目　　こどもの日　　　　　2011年5月5日
10行目　　海の日　　　　　　　2011年7月18日
11行目　　敬老の日　　　　　2011年9月19日
12行目　　秋分の日　　　　　2011年9月23日
13行目　　体育の日　　　　　2011年10月10日
14行目　　文化の日　　　　　2011年11月3日
15行目　　勤労感謝の日　　2011年11月23日
16行目　　天皇誕生日　　　　2011年12月23日
17行目　　社定休日1　　　　2011年○月×日
18行目　　社定休日2　　　　2011年○月×日
19行目　　社定休日3　　　　2011年○月×日
20行目　　社定休日4　　　　2011年○月×日

　上記のリストを作成する際には、次の参考URLのページを参照されると便利です。

【参考URL】
　国立天文台 > 暦計算室 > 暦要項
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/

　国立天文台 > 暦計算室 > 暦要項 > 国民の祝日、日曜表 平成23年（2011）
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/2011/rekiyou111.html

　次に、補助シートのD1セルに

2011年7月

という具合に、集計を行う月を年から入力して下さい。
　次に、補助シートのE1セルに次の数式を入力して下さい。

=IF(ISNUMBER($D$1),DATE(YEAR($D$1),MONTH($D$1),ROWS($1:1)),"")

　次に、補助シートのF1セルに次の数式を入力して下さい。

=IF(ISNUMBER($E1),IF(OR(COUNTIF($B:$B,$E1)>0,MONTH($E1)<>MONTH($D$1),ISERROR(INDIRECT("'"&DAY($E1)&"'!A1"))),"",TEXT($E1,"aaa")),"")

　次に、補助シートのG1セルに次の数式を入力して下さい。

=IF($F1="","",INDIRECT("'"&DAY($E1)&"'!A1"))

　次に、補助シートのE1～G1の範囲をコピーして、補助シートのE2～G31の範囲に貼り付けて下さい。

　次に、月曜日シートの「1シート～31シートの各A1セルの値」の平均値を表示させるセルに、次の数式を入力して下さい。（この数式中のA1と記されている箇所は、この数式を入力するセルのセル番号に合わせて、適時、変更した方が望ましいと思いますが、このままでも一応は正常に動作します）

=SUMIF(補助!$F:$F,MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,1),補助!$G:$G)/COUNTIF(補助!$F:$F,MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,1))

　次に、月曜日シートの「1シート～31シートの各A1セルの値」の平均値を表示させるセルをコピーして、火曜日シート～日曜日シートの「1シート～31シートの各A1セルの値」の平均値を表示させるセルに貼り付けて下さい。

　後は、集計を行う年月に合わせて、補助シートの休日のリストと、D1セルに入力してある年月の値を、適時修正すれば、各曜日毎のA1セルの値の平均値が、自動的に計算されます。

回答No.1

MackyNo1

>各シートを「月曜1、月曜2・・・」と名前を変更し、セルに関数AVERAGE(月曜1!A1,月曜2!A1,・・・)と範囲指定するマクロを作りましたが、上にも書いた通り、毎月第4週までと第5週までの曜日が異なるため、6月の曜日に合わせたセル範囲を7月に適用させるといくつかセル範囲無効によるエラーが表示されます。（#REF!）

シートの名前は「1,2,3,・・・・」とそのままにして、WEEKDAY関数やINDIRECT関数を使用して、その月の集計対象の日付を自動的に判断して対応するセルの集計をするのが簡単なように思えます。

例えば、D1セルに2010/7/1と入力してある場合、7月の第1月曜日のシートのA1セルは以下の数式で取得できます（１～5週のシートを単純に加算）。

=INDIRECT(7-MOD(WEEKDAY(D1)+4,7)&"!A1")

＞範囲指定したシートのうち、存在するシートのみが反映され、無いシートを無視することができれば私にとって理想なのですが、そのような方法はありますか？

上記の数式を利用すれば、同じテンプレートブックを使用して、どこかのセルにその月（あるいはその月の1日の日付）を入力すれば、対応するシートが限定されますので特定のシートを無視する心配は無用です。

ただし、ウィークデイが祝日などの場合はそれを除外する数式を入力する必要があるので、できれば祝日のシートは残しておいた方が簡便な式で対応できます。

ただし、第5週については月によってない場合もありますので、IF関数でその追記の第5週にあたる日が違う月なら加算しないという数式にする必要があります。

日付を入力するセルや集計したい曜日などのレイアウトを提示していただければ、具体的な数式が提示できると思います。