2021/06/03

設備の稼働状態記録とデータ集計

• １．背景
• ２．システム概要
• 　２－１．稼働状態入力
• 　２－２．稼働状態集計
• 　２－３．システムの制限など
• ３．プログラムの流れ
• 　３－１．週の変更を行った場合
• 　３－２．設備の変更を行った場合
• 　３－３．データの集計を行う場合
• 　３－４．今回システムのシートとモジュールの関係
• ４．システムの環境設定
• 　４－１．ワークブックモジュール（ThisWorkbook）
• 　　４－１－１．Workbook_Openイベント
• 　　４－１－２．データの保存
• 　４－２．標準モジュール１（Module1）
• 　　４－２－１．プロジェクト内の変数・定数宣言
• 　　４－２－２．セル範囲への名前付け
• 　　４－２－３．データシートの存在チェックと作成
• 　　４－２－４．条件付き書式の設定
• 　　４－２－５．設備名のコンボボックス作成
• 　　４－２－６．集計種類のコンボボックス作成
• ５．入力シート・データシートの動作
• 　５－１．入力シートの設定（Sheet1）
• 　５－２．シートモジュール１（Sheet1）
• 　　５－２－１．シートレベル変数の宣言
• 　　５－２－２．CommandButtonのクリックイベント
• 　　５－２－３．コンボボックスの操作イベント
• 　　５－２－４．スクロールバーの操作イベント
• 　５－３．標準モジュール２（Module2）
• 　　５－３－１．入力シートへの稼働状態番号の記入
• 　　５－３－２．データの書き込み（保存）
• 　　５－３－３．データの読み込み
• ６．集計シートの動作、計算・出力
• 　６－１．集計シートの設定（Sheet2）
• 　６－２．標準モジュール３（Module3）
• 　　６－２－１．集計ボタン押下時
• 　　６－２－２．データを取得し、日ごとに集計
• 　　６－２－３．日単位のデータを出力
• 　　６－２－４．週単位に再計算し出力
• 　　６－２－５．月単位に再計算し出力
• 　　６－２－６．出力データをクリア
• ７．最後に

１．背景

設備の管理として、定期検査結果や故障記録を保管することは重要ですが、それ同等に稼働状態記録も大切だと思います。
稼働状態を詳しく記録し、それを分析することで「貴重な設備資産をどれだけ活用しているか」「仕事の進め方に無駄は無いか」「設備の更新時期はいつ頃か」などの情報が得られます。

最近では設備の状態を自動的に吸上げるシステムも良く見かけ、既存設備に取り付けるだけで分単位・秒単位のデータが取れるものもあります。しかし、電源を落とした時にしか出来ない準備や修理を機械が見分けられるわけもなく、そこは実際に作業をしている人でないと仕訳けられない部分です。

今回は、作業者が１時間単位で稼働状態を記録していくシステムを紹介します。記録したデータから日単位・週単位・月単位で稼働状態を集計することも可能ですので、上述した分析をすることで「設備管理」の一助になると思います。

２．システム概要

本システム（サンプルファイル）には、図２－１の「稼働状態入力シート（Sheet1）」と図２－５の「稼働状態集計シート（Sheet2）」の２つがあります。システムを起動すると、まず「稼働状態入力シート（Sheet1）」がアクティブになります。

２－１．稼働状態入力

稼働状態を入力するには、まず設備名をコンボボックス内から選択します（図２－１の①）。

図２－１

次に図２－２のようにスクロールバーを操作して、稼働状態を「入力する週」を選択②します。週はスクロールバー下の日付で確認します。今回「日曜～土曜」で一週間としています。

図２－２

希望の日にちを含む週が選択できたら、縦軸の日付と横軸の時間を見ながら「同じ種類の記録」をしたい範囲をセル選択③します。今回の時間軸は、１日を午前０時～夜中の２４時の２４分割（１セル＝１時間）としました。
選択は、Ctrlキーを使って複数範囲を指定することも可能です。また稼働状態が入力されるのは黒枠内ですが、もし枠からはみ出してセル選択しても、その部分は無視されます。

図２－３

セル選択した後、上部の状態ボタン（今回は５種類）のどれかをクリック④すると、セル範囲の部分に値（図２－４の場合は「１」）が入力され、枠内に設定してある「条件付き書式」により背景が彩色されます。

図２－４

なおボタンをクリックした時点では、まだデータとしては保存されていません。データ保存のタイミングとしては、「コンボボックスの設備名を変更しようとした時」「表示週を変更した時」「集計シートに移動した時」「ファイルを閉じようとした時」にトリガーが働き、データが保存されます。

また状態ボタンの一番右の「停止」は、「設備が止まっている」状態を示しています。何も色のついていない部分は「停止」となります。（既定値＝停止状態）
なお既にデータが入っているセルを選択し、異なる状態ボタンをクリックすれば「異なる状態でデータが上書き」されます。その時に「停止」ボタンをクリックすれば、彩色が消え「停止状態」として上書きされます。

２－２．稼働状態集計

Sheet2は「集計シート」になります。集計する条件は、図２－５の様に⑥～⑨の４種類です。
⑥の種別には、今回「日単位」「週単位」「月単位」の３種類を考えました。集計開始日⑦から集計終了日⑧までのデータをどの様にまとめるかの選択です。

「日単位」は文字通り日毎（入力シートの横一行分）に「それぞれの状態が何時間ずつあったか」を集計します。
「週単位」は、今回「日曜日から土曜日まで」を一週間として集計します。なお例えば、指定した開始日が月曜日だった場合には、日曜日分は集計対象には含めない計算にしています。終了日が土曜以外の場合も同様に対象外です。また「週を何曜日からにするか」は、内部定数の設定により変更できます。
「月単位」は「１日から月の最終日まで」を一か月として集計します。開始日が１日で無い場合、また終了日が最終日で無い場合は、週単位の場合と同様に「月の残りの日は集計対象外」として扱います。

集計対象の設備は⑨で選択します。これは、入力シートのコンボボックスと同じ並びになっています。

図２－５

集計条件を入力後、集計ボタン（図２－６の⑩）をクリックすると、テキストデータ⑪がシートに貼り付けられます。
あらかじめグラフ設定⑫をしておけば、集計データが得られると共にグラフによる可視化が可能です。なお、グラフの範囲を自動的に調整する機能は今回対象外ですので、手動での再設定が必要です。

図２－６

２－３．システムの制限など

記録可能な日数：約２０年（設定により、約２７００年まで可：ワークシートに記録するため Excelの行数＝記録日数）
登録可能な設備数：２５３設備（設備数＝シート数）まではＯＫと思います。それ以上は端末の環境で左右されるようです。

本システムは、データも含めて１つのファイル内に収めています。ですので、データが多くなるとファイル容量も大きくなるため、動作が遅くなることは考えられます。

３．プログラムの流れ

流れの前に、本システムの構造について説明します。
表示されているシートは「入力シート（Sheet1）」「集計シート（Sheet2）」の２つですが、通常非表示としている「データシート（設備名がシート名になっている）」が登録設備数だけ存在します。

データシートは図３－１の右側のように細工のないワークシートで、行方向を日付、列方向を時間に見立てています。
日付については、先頭行（１行目）＝基準日 を「プログラム内の定数」として固定し、その基準日からの日数で行位置を計算しています。また、列方向の時間は、１列目を０時～１時、２列目を１時～２時、というように、１日を２４時間に分けています。これは入力シートの黒枠内と同じです。

図３－１

また入力シートでは「状態ボタンをクリック」することで、ユーザーのセル選択範囲に対して、各々の状態ボタンの番号（1,2,3・・・）が、セルに記入されます。そして入力シートの黒枠内には「番号ごとに条件付き書式を設定」しているため、その「状態の番号に対応した色」で彩色されることになります。
なお、データ保存の際には「状態の番号」のみがデータシートに保存されます。

３－１．週の変更を行った場合

入力シート上で、スクロールバーを操作して「週の変更」を行った場合のプログラムの流れが図３－２です。

図３－２

スクロールバーの値が変更されると「ScrollBar_Changeイベント」が発生します。通常でしたら、そのイベントを受けてすぐに日付値を変更するのですが、今回は「日付値を変更する前」に「現在表示されている条件（設備名・日付）」を読み取り、「表示データをデータシートに保存」をします。
データを保存した後、通常通り「表示日付を変更」し、その変更した日付と表示されている設備名を元にして新たなデータを読み込み、入力シートに貼り付けます。

３－２．設備の変更を行った場合

入力シート上で、コンボボックスを操作して「設備の変更」を行った場合のプログラムの流れが図３－３です。

図３－３

「週の変更（図３－２）」とほぼ同じなのですが、設備の場合は「コンボボックスのみで設備名を表示」しているため「Changeイベントが発生した時には、コンボボックスの値が新しい設備名に変わっていて、元の設備名を取得できない」のが、週の変更の場合と異なるところです。

そこで今回は、埋め込みコントロールで使える特別なイベント（GotFocus、LostFocus）の内「GotFocusイベント」を使用して「ユーザーが設備を変更する直前」を取得することで、「コンボボックスの値が変更される前にデータを保存」することにしました。
ですので「コンボボックスの設備を変更した時点では、１つ前に表示されていたデータは既に保存され」ていますので、Changeイベントの時には、新しいデータを読み込んで「入力シートに貼付け」るだけになります。

なお、Excelのバージョン等で「GotFocusイベント」が取得できない場合は、どこかのセルに「設備名」を記録しておき、週の変更の場合と同様にデータの保存を行う必要があります。但し、コンボボックスの値を消したり（Clearメソッド）、初期表示を変えたり（ListIndexプロパティ）した時にはChengeイベントが発生してしまいますので、「データ保存の誤作動」を起こさないように注意が必要です。

３－３．データの集計を行う場合

集計シート（Sheet2）では、「集計の種類」「集計の開始日」「集計の終了日」「設備」の４項目を選択・入力します。その内の「設備」からシートが特定でき、また「集計の開始日」「集計の終了日」から、取得するデータの行位置が計算できます。そのシート名・行位置から取得したデータを日単位で集計し、「日単位データ」とします。

次に「集計の種類（日単位・週単位・月単位）」により、解析を分岐させます。まず「日単位」の場合には「日単位データ」がそのまま使えますので、そのまま既定の位置（プログラム内では「DataOutputRange」と言う名前を付けたセルを左上とする範囲）に貼り付けます。
「週単位」・「月単位」の場合は、それぞれ曜日・月の初日をキーにして日単位データを更に集計し、週単位・月単位にデータをまとめます。まとめ終わったら、日単位の場合と同様にデータを貼り付けます。

図３－４

３－４．今回システムのシートとモジュールの関係

今回システムは、２つのシート上の細工（Sheet1、Sheet2）、ブックモジュール、１つのシートモジュール（Sheet1）、３つの標準モジュール（Module1～3）で組み立てています。それらは、図３－５のように３つの機能に分けられます。

図３－５

今回のプログラム等を説明する順番ですが、今回はこの機能順で説明するのが分かり易いのではと考え、図３－５の①～⑦の順で進めていきます。

４．システムの環境設定

４－１．①ワークブックモジュール（ThisWorkbook）

４－１－１．Workbook_Openイベント

システムを起動（＝ブックを開く）すると最初に呼び出されるのが、図４－１の「 Workbook_Openイベント」です。

1. '==========　⇩(1)　システム起動時の設定　============
2. Private Sub Workbook_Open()
4. 　StatS = Array("稼働", "準備", "故障", "修理") 　　　 '稼働状態一覧
5. 　StatN = Array(1, 2, 3, 4) 　　　　　　　　　　　　 '稼働状態の値一覧
6. 　StatC = Array(RGB(255, 0, 0), RGB(128, 0, 0), RGB(0, 255, 0), RGB(0, 0, 255)) 　 '稼働状態の色一覧
7. 　StatM = Array("旋盤", "ボール盤") 　　　　　　　 '設備一覧
9. 　Call AreaSet
10. 　Call DataSheetCheck
11. 　Call ColorFormat
12. 　Call MachineList(Sheet1.ComboBox1)
13. 　Call MachineList(Sheet2.ComboBox1)
14. 　Call CalcTypeList(Sheet2.ComboBox2)
15. 　Call DataClear
17. 　Sheet1.Activate
18. End Sub

図４－１

４～７行目は、システム内で使用する共通変数へ値を代入しています。変数宣言は、標準モジュール側（図４－５）で行っています。
「StatS（Status String の略のつもり）」は、設備の稼働状態の一覧です。項目を配列として与えています。
「StatN（Status Number の略のつもり）」は、設備状態を表す番号です。これも配列として与えます。
「StatC（Status Color の略のつもり）」は、設備状態を表す色です。これも配列です。
この変数「StatS」「StatN」「StatC」は、入力シートの条件付き書式の設定、及び集計シート内の計算のタイトル設定に使用しています。
なお、Array関数で配列を作成しているので、配列のインデックスはゼロからスタートします。

「StatS」「StatN」は、見た目は入力シートのボタンの名前と同じなので、変数StatSなどに結びついているように見えますが個別設定です。入力シートのボタン作成まで起動時に自動で行うのは可能ですが、そもそも「設備稼働状態の項目は、そんなに変化するもので無い」と判断し、別個対応としました。

また、設備稼働状態には「停止」という項目があるのですが、これは変数StatS等には盛り込みませんでした。というのは「停止」に例えば番号ゼロを割り当ててしまうと、入力シートで「0が入っているセルは停止」「何も入っていないセルも停止」ということになってしまいます。
また「停止に色を設定」してしまうと、例えば白色で塗ったとしても「セル間の枠線が消える」ことになります。試しでやってみたのですが、枠線が見えないと「時間・日付」が分かり難くなってしまうため、「停止は別扱いとし、色は設定しない」こととしました。
但し「設定し忘れ」なのか「停止に指定」したのかが、管理者側からすれば分かり難くなってしまいます。もし停止の値をゼロにし、色を白色に設定するのであれば、入力シートの枠内には「罫線」を引いて時間・日付が分かり易くするような工夫が必要です。また、セルにゼロ値を入れていない場合には、ユーザーに「忘れていませんか？」などのコメントを出すような機能も必要になりそうです。

また、集計の計算過程で「Frequency関数」を使用しています。Frequency関数はデータを分配するものですが、「StatN」をその分配範囲の配列としても使っています。ですので「StatNは必ず昇順」で並べておく必要があります。

７行目の「StatM（Status Machine のつもり）」は、設備名を配列として与えています。この値は、ユーザーが操作するコンボボックスのリストにするだけでなく、非表示のデータシートの「シート名」にしますので、ダブリは不可です。

９～１５行目は、標準モジュール１のプロシージャを呼び出しています。

９行目の「Call AreaSet」では図４－６を呼び出し、２箇所の「セル範囲への名前付け」を行っています。１箇所目は、入力シートの入力枠内で、ユーザーのセル選択範囲が枠内か否かの判断と、条件付き書式の適用先として使用しています。
２箇所目は、集計シートのデータ出力先の「左上角セルの位置」を設定しています。
両方とも定数として設定することも可能ですが、セル位置と共にシート位置の情報も必要なため、今回名前付けの形で設定しました。

１０行目の「Call DataSheetCheck」は、図４－７を呼び出しています。「DataSheetCheck」内では、図４－１の７行目の変数StatM（＝設備一覧）を１つずつチェックし、もしそのシート名のワークシートが無ければ新規作成しています。つまり、このプロシージャを実行することで「必ず、設備名のシートが存在する」ことになります。
逆に「変数StatMから設備名を削除しても、シートはそのまま残される」ことになりますので、不要になった場合は手動でのシート削除が必要です。

１１行目の「Call ColorFormat」は、図４－８を呼出し、入力シートの枠内を適用先として条件付き書式の設定をしています。設定には変数StatNと変数StatCを使用し、記入される数値（StatN）に対応した背景色（StatC）が設定されるようにします。

１２～１３行目の「Call MachineList(・・・)」は図４－１０を呼び出し、設備リストのコンボボックスの設定を行っています。
今回システムでは、設備を選択するコンボボックスは２か所（Sheet1のComboBox1 と Sheet2のComboBox2 ）あります。サブプロシージャを２種類作る方法も考えられますが、内容はほとんど一緒ですので、異なる部分（対象とするコンボボックスオブジェクト）だけを引数にして共通のプロシージャとしました。

１４行目の「Call CalcTypeList(Sheet2.ComboBox2)」は、図４－１１を呼び出しています。「CalcTypeList」で設定する対象は、集計シートの「日単位・週単位・月単位」を選択するコンボボックスで、今回システムで使うのは１箇所のみです。ですので、１２～１３行目の様にわざわざコンボボックスオブジェクトを引数として渡すことは無いのですが、引数でオブジェクトを渡すと「どこのリストを作成しているのか」が分かり易くなる気がするので、このような形にしました。

１５行目の「Call DataClear」は図６－１９を呼び出し、集計後の貼り付けたデータを消去しています。
１７行目の「Sheet1.Activate」は、起動時に「入力シート」を前面に持ってきて、すぐに入力できるようにしています。

なお、９～１４行目までの設定は「毎回実行しなくても、ブックに残る」ので、初回設定後はコメントアウトしておくことも可能です。但し、設備を増減・設備名変更などをした時には実行は必須です（設備名のシートが無い場合は、エラーが出ます）。

４－１－２．データの保存

今回システムでは「データ保存」というボタンは設定していません。一生懸命入力しても、ボタンを１回クリックし忘れただけで苦労が水の泡になるのでは、良いシステムとは言えない気がするからです。
そこで今回は、何かの操作をした時に自動的にデータ保存することを考えました。（もちろん、データ保存のボタンと自動保存を併用してもOKだと思います）

まず、考えられる自動保存のタイミングは図４－２のようになります。最も確実なのは、④の「枠内のセルに数値を入れた時」です。これでしたら他のイベントは不要になりますが、セルに１つ数値が入るたびに保存がかかりますので、動作が遅くなる可能性があります。
ですので、④は使用せずに⑤と⑥を使って「設備・日付を変更するときにはデータ保存」することにしました。

図４－２

また入力したデータを集計に確実に反映するために、シート移動時（Sheet1 → Sheet2）③にもデータ保存します。あとはデータ入力後にすぐにファイルを閉じる時ですが、ファイル保存してもしなくても「ブック終了イベント①」は必ず通過しますので、②は省略し、①でデータ保存をすることにしました。

ブックモジュールでは図４－３に①を、図４－４に③を記述をします。なお⑤⑥については、Sheet1のシートモジュール（図５－１０、図５－１１）に記述します。

図４－２の③に対応するのが、シート移動のイベントで図４－３になります。

20. '==========　⇩(2)　シートの移動時　============
21. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
22. 　Dim OldWeek As Date 　　　　 '←現在の週初日
23. 　Dim OldMachine As String 　　 '←現在の設備名
25. 　If EventsOff = True Then Exit Sub
27. 　If Sh.CodeName = Sheet2.CodeName Then
28. 　　OldWeek = Sheet1.Range("B4").Value
29. 　　OldMachine = Sheet1.ComboBox1.Text
31. 　　Call WeekWrite(OldWeek, OldMachine)
32. 　End If
33. End Sub

図４－３

引数のShは「移動先のシートオブジェクト」ですので、２７行目の「If Sh.CodeName = Sheet2.CodeName Then」で「（通常、ユーザーにはSheet1とSheet2しか見えないので）Sheet1→Sheet2に移動したとき」に２８～３１行目を実行します。
まず２８行目の「OldWeek = Sheet1.Range("B4").Value」で、日付の先頭の値（＝週の初日）を変数OldWeekに代入します。
続いて２９行目の「OldMachine = Sheet1.ComboBox1.Text」で、コンボボックスに表示されている設備名を変数OldMachineに代入します。

日付と設備名を取り込んだのち、３１行目の「Call WeekWrite(OldWeek, OldMachine)」を実行させます。
「WeekWrite（図５－１３）」は、設備名と日付に従って入力シート上のデータをデータシートに保存する機能です。

なおシステム起動時に、図４－７のDataSheetCheckプロシージャ内で、マクロ操作としてシート移動をしている部分があります。例えば管理者が設備を増やす設定にマクロを書き換えた後システムを起動すると、
「新規シート作成」→「新シートがActive」→「新シートを非表示」→「表示シートの一番右側のシート（Sheet2）がActive」
になり「Sheet2へ移動」が発生し、データ保存（Call WeekWrite）が実行されてしまいます。

「マクロを切ってファイルを立ち上げ、コンボボックスの設備を変更」のような事をされない限り大丈夫だとは思うのですが、システム起動時の不安定な状態の時に大切なデータの書き換えを行うのはリスクが高く、何が起きてもおかしくありません。
そのため意識的にイベント発生を抑えている場合は、２５行目の「If EventsOff = True Then Exit Sub」でプロシージャ実行をスルーさせています。

次に、図４－２の①に対応するのが「Workbook_BeforeCloseイベント」で、図４－４になります。

35. '==========　⇩(3)　ブックを閉じる時　============
36. Private Sub Workbook_BeforeClose(Cancel As Boolean)
37. 　Dim OldWeek As Date 　　　　 '←現在の週初日
38. 　Dim OldMachine As String 　　 '←現在の設備名
40. 　OldWeek = Sheet1.Range("B4").Value
41. 　OldMachine = Sheet1.ComboBox1.Text
43. 　Call WeekWrite(OldWeek, OldMachine)
45. End Sub

図４－４

４０行目で日付を取得し、４１行目で設備名を取得するのは、図４－３の２８～２９行目と同等です。その値を使って、４３行目の「Call WeekWrite(OldWeek, OldMachine)」でデータ保存をしています。

４－２．②標準モジュール１（Module1）

４－２－１．プロジェクト内の変数・定数宣言

標準モジュールの宣言部（先頭部分）では、システム内で使用する定数・変数の宣言をします（図４－５）。

47. '==========　⇩(4)　定数・変数の宣言　============
48. Public Const StartD As Date = "2021/5/2"
49. Public StatS As Variant 　　　 '稼働状態一覧
50. Public StatN As Variant 　　　 '稼働状態の値一覧
51. Public StatC As Variant 　　　 '稼働状態の色一覧
52. Public StatM As Variant 　　　 '設備一覧
54. Public EventsOff As Boolean 　 'イベントプロシージャをスルーする為のフラグ

図４－５

４８行目の「Const StartD As Date = "2021/5/2"」は、「システムが（この場合）2021年5月2日から始まる」ことを意味しますが、更に重要なのは「データシートの１行目」を"2021/5/2"とする　という事です。（定数「StartD」はStart Day のつもり）
ですので、この定数値を途中で変更するということは、既存のデータシートのデータを全て移動する　ということを意味します。

４９～５１行目は「稼働状態の一覧」で、値の代入は図４－１の４～６行目で行っています。それぞれ一次元の配列にしています。なお設定が面倒で無かったら、一方が３要素の二次元配列を使用しても良いと思います。また、ワークシート上のセル範囲に設定条件を記しておき、それを読み取ることで二次元配列とすることも可能かもしれません。

４９行目の「StatS（Status String の略のつもり）」は、設備の稼働状態の一覧で、状態の項目（String型）を配列として格納する変数です。
５０行目の「StatN（Status Number の略のつもり）」は、設備状態を表す番号で、数値（正の整数）を配列として格納する変数です。Frequency関数の分配範囲の配列としても使用するため、昇順（小さい数字順）に並べる必要があります。
５１行目の「StatC（Status Color の略のつもり）」は、設備状態を表す色で、色のRGB値（Long型）を配列として格納する変数です。
なお、配列を代入するために「Variant型」で宣言しています。

５２行目の「StatM（Status Machine のつもり）」は、設備名（String型）を配列として格納する変数です。この値は、データシートのシート名になりますので、重複はNGです。

５４行目の「EventsOff」は、コンボボックス等のコントロールを設定している最中に、どうしても発生してしまうイベントをスルーする為のフラグです。Boolean型で宣言していますので、既定値はFalseとなりますので、Trueを事前に設定することでイベントをスルーさせるようにしています。

４－２－２．セル範囲への名前付け

図４－１の９行目から呼び出される「AreaSet」が、図４－６です。

56. '==========　⇩(5)　セル範囲への名前付け　============
57. Sub AreaSet()
58. 　On Error Resume Next
59. 　　ActiveWorkbook.Names("kadouTable").Delete
60. 　　ActiveWorkbook.Names("DataOutputRange").Delete
62. 　　ActiveWorkbook.Names.Add Name:="kadouTable", RefersTo:="=Sheet1!$D$4:$AA$10"
63. 　　ActiveWorkbook.Names.Add Name:="DataOutputRange", RefersTo:="=Sheet2!$A$4"
64. 　On Error GoTo 0
66. End Sub

図４－６

名前付けのメインコードは、６２～６３行目です。
６２行目の「ActiveWorkbook.Names.Add Name:="kadouTable", RefersTo:="=Sheet1!$D$4:$AA$10"」は、入力シート（Sheet1）の黒枠内（$D$4:$AA$10）に名前「kadouTable」を付けるものです。この「kadouTable」という名前は、稼働状態に応じた彩色を行う条件付き書式の設定、データの読み込み・書き込みに使用しています。

６３行目の「ActiveWorkbook.Names.Add Name:="DataOutputRange", RefersTo:="=Sheet2!$A$4"」は、、集計シート（Sheet2）の集計データ貼り付け範囲の左上角のセル位置（$A$4）に名前「DataOutputRange」を付けるものです。この「DataOutputRange」は、集計データの貼り付け・消去に使用しています。

改造などで、入力枠や集計データ貼付け位置が変更になった場合は、この６２～６３行目のセル範囲位置の値を変更する必要があります。

５９行目の「ActiveWorkbook.Names("kadouTable").Delete」、６０行目の「ActiveWorkbook.Names("DataOutputRange").Delete」は、６２～６３行目で名前を設定する前に「もし同じ名前があったら、削除しておく」というものです。同じ名前が既に存在する場合には、新しい範囲に名前設定が出来ないためです。
但し、もし「kadouTable」「DataOutputRange」という名前が無かった場合には、Delete出来ませんのでエラーが発生してしまいます。ですので５８行目の「On Error Resume Next」でエラーをスルーし、６４行目の「On Error GoTo 0」で元に戻しています。

２つのセル範囲に名前を付けるもう一つの方法として、「Set kadouTable = Sheet1.Range("D4:AA10")」などとRange変数に値を設定するやり方もあります。ただし、この方法の場合には「ブックに名前が残らない」ので、システム起動の都度実行する必要があります。

４－２－３．データシートの存在チェックと作成

図４－１の１０行目から呼び出される「DataSheetCheck」が、図４－７です。

68. '==========　⇩(6)　データシートの存在チェックと作成　============
69. Sub DataSheetCheck()
70. 　Dim ShName As Variant 　 '←1つ1つの設備名（＝ｼｰﾄ名）
71. 　Dim NewSh As Worksheet 　 '←新たに作成するｼｰﾄ
72. 　Dim OldSh As Worksheet 　 '←元のActiveなｼｰﾄ
74. 　Application.ScreenUpdating = False
76. 　　EventsOff = True
77. 　　Set OldSh = ActiveSheet
79. 　　For Each ShName In StatM
80. 　　　On Error Resume Next
81. 　　　　If ThisWorkbook.Sheets(ShName) Is Nothing Then
82. 　　　　　Set NewSh = Sheets.Add(After:=Sheets(Sheets.Count))
83. 　　　　　NewSh.Name = ShName
84. 　　　　　NewSh.Visible = False 　 '←ｻﾝﾌﾟﾙﾌｧｲﾙでは､ｺﾒﾝﾄｱｳﾄしています
85. 　　　　End If
86. 　　　On Error GoTo 0
87. 　　Next ShName
89. 　　OldSh.Activate
90. 　　EventsOff = False
92. 　Application.ScreenUpdating = True
93. End Sub

図４－７

ここでのメインのコードは、７９～８７行目です。設備一覧（配列StatM）に従って、その名前のシートが存在するか否かを調べ、無かったら新規にシートを作成しています。
まず７９行目の「For Each ShName In StatM」では、変数StatMの設備一覧（図４－１の７行目で格納されたもの）を「For Each」で１つずつ取り出し、変数ShNameに代入します。

そして８１行目の「If ThisWorkbook.Sheets(ShName) Is Nothing Then」で、「そういう設備名のついたシートが無かった（Is Nothing）ら」８２行目以下を実行していく･･･ように見えますが、実際には違います。「そういう設備名のついたシートが無かった」ら、「インデックスが有効範囲にありません」という実行時エラーが発生します。そこでその直前の８０行目に「On Error Resume Next」を置き、「エラーが出た時には次に進む」ことで「８２行目以下を実行する」ことになります。
つまり「そういう設備名のついたシートが有った」ときには「ThisWorkbook.Sheets(ShName) Is Nothing」がFalseになり、８２行目以下がスルーされているのです。

「そういう設備名のついたシートが無かった時」には、８２行目の「Set NewSh = Sheets.Add(After:=Sheets(Sheets.Count))」が実行され、既存シートの一番右（Sheets(Sheets.Count)）の後ろ（After:=）に新シートを追加（Add）します。そして追加したシートのオブジェクトを「変数 NewSh」としています。

８３行目の「NewSh.Name = ShName」では、存在有無を検査した「設備名 ShName」を新シートの名前（NewSh.Name）にします。
また、８４行目の「NewSh.Visible = False」で、そのシートを「非表示（Visible = False）」にします。イタズラをしなければ非表示にしなくても良いのですが、シート数が増えるとシステムが使い難くなってしまうと思い非表示としました。
ちなみにサンプルファイルでは８４行目はコメントアウトしています。

８６行目の「On Error GoTo 0」は、存在しない設備名のシートの処理が完了した後、元の正しい状態に戻すものです。
少し戻って、７７行目の「Set OldSh = ActiveSheet」は、このプロシージャを実行する前にアクティブになっていたシートを変数OldShに記憶させ、全ての設備名の処理が完了した８９行目の「OldSh.Activate」で「元のアクティブなシートへ戻し」ています。
これは「一番右側にシート追加」→「新シートがActive」→「新シートを非表示」→「表示状態のシートの内、一番右側のシートがActive」の順番で進みますので、最初のActiveシートと最後のActiveシートが異なる可能性があるのです。

但し、図４－１の１７行目の「Sheet1.Activate」があるため、結局はSheet1がActiveになり、８９行目の「OldSh.Activate」は不要となります。しかし一つのプロシージャ（DataSheetCheck）に、「設備のシートを追加する機能」と「Sheet1をActiveにする機能」という別次元の機能を同時に盛り込むことは、間違いの元となるため避けた方が良いと考え、本プロシージャでは「入口と出口でActiveなシートは同じ」となるようにしました。

７４行目の「Application.ScreenUpdating = False」と９２行目の「Application.ScreenUpdating = True」で囲んでいるのは、シートの作成・移動でバタバタがユーザーに見えない様にするためです。
また出来上がったデータシートは、必ずしも配列StatMの順番で並んでいる訳ではありません。途中で増やした設備は既存の設備よりも右側に作成されるためです。

４－２－４．条件付き書式の設定

図４－１の１１行目から呼び出される「ColorFormat」が図４－８です。

95. '==========　⇩(7)　条件付き書式の設定　============
96. Sub ColorFormat()
97. 　Dim i As Long 　 '←ｶｳﾝﾀ変数(稼働状態の要素数)
99. 　Range("kadouTable").FormatConditions.Delete
101. 　For i = 0 To UBound(StatS, 1)
102. 　　With Range("kadouTable").FormatConditions.Add(Type:=xlExpression, _
103. 　　　　　Formula1:="=" & Range("kadouTable")(1).Address(False, False) & "=" & StatN(i))
104. 　　　With .Interior
105. 　　　　.Color = StatC(i)
106. 　　　End With
107. 　　End With
108. 　Next i
110. End Sub

図４－８

まず９９行目の「Range("kadouTable").FormatConditions.Delete」で、古い条件付き書式を削除しています。この際、セル範囲の名前「kadouTable」が存在しなければ実行時エラー（定義エラー）が発生しますが、そこに条件付き書式が仮に無くてもエラーは発生しません。ですので、９９行目を「On Error Resume Next」と「On Error GoTo 0」で囲む必要はありません。

１０１～１０８行目で、条件付き書式を設定しています。
１０１行目の「For i = 0 To UBound(StatS, 1)」では、For～Nextで稼働状態の要素数分だけ変数ｉを回しています。ここではStatSを使用していますが、StatS・StatN・StatCとも全て同じ要素数のはず（異なるとエラーが出ます）なので、どれを使用しても良いです。

１０２～１０３行目のコード「With Range("kadouTable").FormatConditions.Add(Type:=xlExpression, Formula1:="=" & Range("kadouTable")(1).Address(False, False) & "=" & StatN(i))」の内、前半の「Range("kadouTable").FormatConditions.Add」が条件付き書式の作成のメソッドです。そしてその後ろのカッコ内が条件付き書式の細かい仕様になります。

まず「Type:=xlExpression（演算）」としていますが、Typeに指定できる他のものは図４－９のように多種あります。

定数 値 内容 xlcellvalue 1 セルの値 xlexpression 2 演算 xlColorScale 3 カラー スケール xlDataBar 4 DataBar xlTop10 5 上から 10 個の値 xlIconSet 6 アイコン セット xlUniqueValues 8 一意の値

定数 値 内容 xlTextString 9 テキスト文字列 xlBlanksCondition 10 空白の条件 xlTimePeriod 11 期間 xlAboveAverageCondition 12 平均以上の条件 xlNoBlanksCondition 13 空白の条件なし xlerrorscondition 16 エラー条件 xlnoerrorscondition 17 エラー条件なし

図４－９

４－２－５．設備名のコンボボックス作成

図４－１の１２行目、及び１３行目から呼び出される「MachineList」が図４－１０です。このプロシージャは、引数として「コンボボックスのオブジェクト」を受け取り、プロシージャ内では「Sh_Combo」として扱います。

112. '==========　⇩(8)　設備名のコンボボックス作成　============
113. Sub MachineList(Sh_Combo As Object)
114. 　Dim i As Long 　　 '←ｶｳﾝﾀ変数(設備の数)
116. 　EventsOff = True
117. 　　Sh_Combo.Clear
118. 　EventsOff = False
120. 　For i = 0 To UBound(StatM)
121. 　　Sh_Combo.AddItem StatM(i)
122. 　Next i
124. 　Sh_Combo.ListIndex = 0
126. End Sub

図４－１０

このプロシージャは、指定されたコンボボックスに対して「設備の一覧をリスト化」するものです。今回システムでは、入力シート及び集計シートの２箇所で設備のコンボボックスを使用しています。
使用している場所は異なっても、設備の一覧は同じですので、設定する「コンボボックス自体」を引数として受け取ることで、１つのプロシージャにまとめた形にしています。

１１７行目の「Sh_Combo.Clear」では、まずコンボボックスのリストを空にしています。
但し、空にしたことで「コンボボックスの選択値を変更した」と捉えられ、コンボボックスの「Changeイベントが発生」してしまいます。
Chnageイベントが発生してしまうと、入力シートでは「コンボボックスで選択した設備のデータ」を使ってデータシートからデータを読み込んできますので、空の状態のコンボボックスでは当然エラーが発生することになります。
ですので、１１７行目の前に１１６行目で「EventsOff = True」とし、「Changeイベントをスルー」させています。

コンボボックスのリストが空になったら、新たにリストを作っていきます。
１２０行目の「For i = 0 To UBound(StatM)」で、設備一覧（StatM）の要素数だけ変数ｉを回します。配列StatMのインデックスはゼロ始まりですので、ｉもゼロからスタートさせています。
そして、１２１行目の「Sh_Combo.AddItem StatM(i)」で、１つずつ設備名をリストに追加していきます。

リストが全て完成したら、１２４行目の「Sh_Combo.ListIndex = 0」で、リストの一番上の設備名を表示させます。このコードを実行しないと、初期段階ではコンボボックスには設備名が表示されておらず（.ListIndex = -1 の状態）、ユーザーがリストを選択して初めて設備名が表示されることになります。
しかし、それ以上に困ったことになるのは、コンボボックスは空の状態なのに、入力シートの枠内に表示されているのは「１つ前にブックが閉じられる時に開いていた設備のデータ」となっていることです。この状態でデータを入れデータを保存しようとすると、保存先の設備がわからないためエラーが発生してしまいます。

ですので１２４行目を実行は重要で、かつ直前で「EventsOff = True」設定をしないことで、正常にコンボボックスのChangeイベントが発生し、「コンボボックスの表示 ＝ 枠内のデータ」の整合性が取れることになるのです。

４－２－６．集計種類のコンボボックス作成

図４－１の１４行目から呼び出される「CalcTypeList」が図４－１１です。このプロシージャは、引数として「コンボボックスのオブジェクト」を受け取り、プロシージャ内では「Sh_Combo」として扱います。

128. '==========　⇩(9)　集計種類のコンボボックス作成　============
129. Sub CalcTypeList(Sh_Combo As Object)
131. 　Sh_Combo.Clear
133. 　Sh_Combo.AddItem "日単位"
134. 　Sh_Combo.AddItem "週単位"
135. 　Sh_Combo.AddItem "月単位"
137. 　Sh_Combo.ListIndex = 0
139. End Sub

図４－１１

今回システム内で「集計種類のコンボボックス」は１箇所しか使用していませんので、わざわざ引数でコンボボックスオブジェクトを渡す必要は無いのですが、図４－１０と構造がほぼ同じなので、引数も同じ形にしています。

１３１行目の「Sh_Combo.Clear」で、コンボボックスのリストを一旦空にします。
１３３行目の「Sh_Combo.AddItem "日単位"」でリストに「日単位」の文字列をリストに追加します。続けて１３４行目で「週単位」を追加し、１行目で「月単位」を追加します。
変数を起こして、Array("日単位","週単位","月単位")を代入し、For～Nextで回しながらリストを作る方法もありますが、一回リスト作成したら二度と実行しない可能性もあるため、最も簡単な構造としました。

最後に１３７行目の「Sh_Combo.ListIndex = 0」で、一番目の項目（この場合は「日単位」）を表示させます。

５．入力シート・データシートの動作

５－１．③入力シートの設定（Sheet1）

「入力シート（Sheet1）」上の各コントロール、数式・文字列などの配置は、図５－１のようにしました。

図５－１

稼働状態を記入する範囲（D4：AA10）は黒の枠線で囲み、その原点部分にComboBoxの設備リストを配置し、その下に日付切り替え用のScrollBarを配置します。ユーザー操作の前後の状態を取得するため、ComboBox・ScrollBarは「ActiveXコントロール」を使用しています。

また縦軸には１週間の日付を並べていますが、基準としている「先頭の日曜の日付（B4セル）」をマクロ側から書き換える形とし、月曜～土曜の分は「=B4+1」等と上のセルの値に対して１を足す　という数式を入れることで１週間を自動計算するようにしています。

また横軸には１時～２４時までの時刻を記入し、その上に５つのCommandButtonを配置しており、設備の稼働状態をボタンで入力させるものです。このCommandButtonは、今回フォームコントロールを使用していますが、ActiveXコントロールでも可能です。ボタン表面の文字列は、今回手動で設定しています。

５つのCommandButtonには、図５－２の右側のように「マクロ設定」をしています。ボタンを左側から１，２、･･･とすると、１のボタンにはSheet1のマクロ「Button1」を、２のボタンにはSheet1のマクロ「Button2」を設定する　という具合です。

図５－２

また、ScrollBarの必須の設定としては「Min値をゼロ（既定はゼロ）」にする設定です（図５－２の左側）。これは、図４－５の４８行目で定数StartDに日付値を設定していますが、「StartDの日付 ＝ データシートの１行目」として全てを計算しているためです。マイナスを選べることになると、ワークシートの１行目より上を参照することになり、エラーが発生します。

「Max値」は今回「1000」に設定していますが、このScrollBarの値は「週」を表していますので、1000週つまり約20年間使用できることを意味します。このMax値は最大「2,147,483,647」まで入りますが、ワークシートの行数最大が「1,048,576」ですので、Max値は実質「149,796」が限界ということになります。
なお、Max値をあまり大きくすると、スクロールバーのスクロールボックス（中央の四角い部分）の操作が難しくなってしまうため注意が必要です。

なお、ComboBoxのプロパティは既定値のままでＯＫです。

５－２．④シートモジュール１（Sheet1）

５－２－１．シートレベル変数の宣言

Sheet1モジュールの宣言部では、図５－３のシートレベル変数の宣言をしています。

141. '==========　⇩(10))　シートレベル変数の宣言　============
142. Dim ComboNo As Long 　　 '←ｺﾝﾎﾞﾎﾞｯｸｽの現状の選択番号

図５－３

この変数「ComboNo」は、入力シート（Sheet1）の設備リスト（ComboBox1）にFocusを当てた（＝操作しようとした）時に、表示されていた設備名の番号（ListIndex）を保管しておくものです。これは、図５－４のような不具合を防ぐものです。

図５－４

コンボボックスの一番上のTextBox部には、項目を絞り込むためにユーザーが自由に文字を入れることが可能です。この機能は、リストのどれかが表示されている時に、それを編集しようとする（図５－４の左側）ときにも有効です。
ここで編集をすると、編集した瞬間にリストと異なるものを選択していることになり、Changeイベントが発生します。今回のシステムで言えば「『編集した設備名』のデータシートから、データを読み込む」動作をすることになります。
当然ながら『編集した設備名』のデータシートは無いわけですから、図５－４の右側のように実行時エラーが発生します。

これを防ぐために、以下の手順でコンボボックスを復元します。
１）ユーザーがコンボボックスにFocusを当てた時（まだ編集をしていない段階）、その段階でのコンボボックスの表示値（ListIndex）を一旦変数ComboNoに代入しておく。
２）コンボボックスの値を編集した瞬間にChangeイベントが発生しますので、その中でListIndex値を調べその値が「－１（＝表示値を変更した）」であれば、保管していた変数ComboNoをListIndexに与える。
３）コンボボックスの値が編集前に戻る

この変数「ComboNo」は、図５－１０の１７４行目、１９１行目で使用します。

当然ながら、設備名をコンボボックスのリストの中から選べば、ListIndexが－１になることはありませんので、正常なデータ読み込みを行うことになります。

５－２－２．CommandButtonのクリックイベント

入力シート上の稼働状態の各ボタンをクリックすると、各ボタンに登録されている図５－５のマクロ（例えば「稼働」としているボタン１には、マクロ「Button1」が登録されている）が呼び出されます。

144. '==========　⇩(11)　稼働ボタンのクリック時　============
145. Sub Button1()
146. 　Call Cellwrite(1)
147. End Sub
149. '==========　⇩(12)　準備ボタンのクリック時　============
150. Sub Button2()
151. 　Call Cellwrite(2)
152. End Sub
154. '==========　⇩(13)　故障ボタンのクリック時　============
155. Sub Button3()
156. 　Call Cellwrite(3)
157. End Sub
159. '==========　⇩(14)　修理ボタンのクリック時　============
160. Sub Button4()
161. 　Call Cellwrite(4)
162. End Sub
164. '==========　⇩(15)　停止ボタンのクリック時　============
165. Sub Button5()
166. 　Call Cellwrite(0)
167. End Sub

図５－５

各ボタンからは、全て同じプロシージャ「Cellwrite（図５－１２）」を呼び出します。Cellwriteには引数を与えます。それは図５－６のように、配列StatSに対応する配列StatNの値になります。
ただし、StatSには無いボタン「停止」がありますので、それには「0（ゼロ）」を割り当てます。


図５－６

図５－１２で説明しますが、Cellwrite内では「ゼロが来た時には、値を消す」処理をします。

なお、今回のサンプルファイルでは「フォームコントロール」のボタンを使ったため、上記のようなマクロになりました。一方「ActiveXコントロール」のボタンを使用した場合には、そのボタンのClickイベントを利用するので図５－７のようなコードになります（代表で、ボタン１をActiveXコントロールで作った場合）。

• Private Sub CommandButton1_Click()
• 　Call Cellwrite(1)
• End Sub

図５－７

システムとしての動作（ボタンをクリックすると、セル範囲選択した部分に稼働状態が記入される）はフォームコントロールの時と同じですが、クリックした後の見掛けは図５－８のように異なります。

図５－８

セル範囲選択（図５－８の①）をし、その後でボタンをクリックするのですが、
・フォームコントロールの場合②：選択範囲はそのまま保持
・ActiveXコントロールの場合③：ボタンにFocusが移るために、選択範囲が見えなくなる
ことになります。ActiveXの場合でも「Selection」範囲は①の選択範囲を保持しているのですが、見掛けは違います。
どちらが良いか考えた結果「自分が選択した範囲が残っていた方が、ユーザーにとっては親切なのでは」と考え、今回はフォームコントロールを使用しました。

なお、ComboBox・ScrollBarは、ActiveXでないと実現できない機能があるため、ActiveXで作りました。
そのため、ActiveXコントロールのComboBox・ScrollBarを操作（設備名の変更、日付の変更）した直後は、コントロールにFocusが有るため、どこのセル範囲を選択しているかユーザーには見えません。しかし見えないだけなので、稼働状態のボタンをクリックすれば数値が入力（＝セルが彩色）されてしまいます。
これを防ぐには、各ActiveXコントロールにGotFocus、LostFocusを使って「ActiveXコントロールにFocusが無い時にのみ稼働状態ボタンが起動できる」ようにするしか方法が無いかもしれません。ちょっと面倒そうなので、今回は省略しています。

５－２－３．コンボボックスの操作イベント

まず、コンボボックスを操作する時に発生するイベントは図５－９のようになります。
この中の「GotFocusイベント」は、埋め込みコントロールでの特別なイベント（GotFocus、LostFocus）の１つです。


図５－９

まずコンボボックスにFocusを当てるには２つの方法があります。
１つ目はコンボボックスの「▼印（Arrow Buttonというらしい）」をクリックした時（図５－９の①）。
もう１つはコンボボックスのテキストボックス部をクリックした時（図５－９の②）です。

①の場合は最初に「DropButtonClickイベント」が発生しますが、そのあと「GotFocusイベント」が発生します。②の場合は最初に「GotFocusイベント」が発生します。つまり、コンボボックスを「操作しようとした行為」を検出するには「GotFocusイベント」を使えば良いことになります。
なお、①の方法の場合は、コンボボックスのリストがスダレ状に下に並ぶことになります。また、②の方法の場合は「テキストボックスを編集するのと同様」にコンボボックスのテキストボックス部にカーソルが入ります。

コンボボックスの値を変更するには、Focusの当て方①②で少し異なります。
コンボボックスの「▼印」をクリックした場合①は、スダレ状のリストから設備名を探してマウスでクリックする方法③、または上下矢印キーでカーソル（青反転部）を上下に移動させる方法④です。
また、テキストボックス部をクリックした場合②は、スダレ上のリストが現れないため、上下矢印キーで項目を移動させる④ことになります。
図５－９のように、それぞれ通過するイベントは異なりますが、値が変わった時には「必ずChangeイベントが発生」します。

上記のように、どの方法・手順でコンボボックスを操作しても、以下の２つのイベントを取得すれば良さそうです。
　・コンボボックスに触れた（＝操作しようとした）・・・・・・・・・GotFocusイベント
　・コンボボックスの値を変更した（＝異なる設備名を選んだ）・・・・Changeイベント
この２つのイベントが図５－１０です。

169. '==========　⇩(16)　コンボボックスにFocusした時　============
170. Private Sub ComboBox1_GotFocus()
171. 　Dim OldWeek As Date 　　　　 '←現状の日付
172. 　Dim OldMachine As String 　　 '←現状の設備名
174. 　ComboNo = ComboBox1.ListIndex
176. 　OldWeek = Sheet1.Range("B4").Value
177. 　OldMachine = Me.ComboBox1.Text
179. 　Call WeekWrite(OldWeek, OldMachine)
181. End Sub
183. '==========　⇩(17)　コンボボックスの値を変更した時　============
184. Private Sub ComboBox1_Change()
185. 　Dim NowWeek As Date 　　　 '←現状の日付
186. 　Dim NowMachine As String 　　'←現状の設備名
188. 　If EventsOff = True Then Exit Sub
190. 　If Me.ComboBox1.ListIndex = -1 Then
191. 　　Me.ComboBox1.ListIndex = ComboNo
192. 　　Exit Sub
193. 　End If
195. 　NowWeek = Sheet1.Range("B4").Value
196. 　NowMachine = Me.ComboBox1.Text
198. 　Call WeekRead(NowWeek, NowMachine)
200. End Sub

図５－１０

まずGotFocusイベント（１７０～１８１行目）です。
このイベントが発生するのは、ユーザーが「設備名を変更しようとしている」時です。Changeイベント（１８４～２００行目）が発生した時には「既にコンボボックスの値は新しい設備名に変更されている」ので、「変更前の設備名を取得するにはGotFocusで取得する」しかありません。

１７６行目の「OldWeek = Sheet1.Range("B4").Value」で「表示週の初日」の日付を、変数OldWeekに代入します。
１７７行目の「OldMachine = Me.ComboBox1.Text」で「現在の設備名（コンボボックスの表示項目）」を変数OldMachineに代入します。なお、この１７７行目のコードは「OldMachine = Me.ComboBox1.List (Me.ComboBox1.ListIndex)」または「OldMachine = Me.ComboBox1.List (ComboNo)」と書くこともできます。

そして１７９行目の「Call WeekWrite(OldWeek, OldMachine)」で「WeekWrite（図５－１３）」を呼び出し、現在の入力シートのデータをデータシートへ書き込みます。その書込み先のシートは「OldMachine」の名前のシートです。
また行位置は、日付「OldWeek」からStartD（図４－５の４８行目で定数設定）を引いた行になります。入力シート側のデータは１週間分ですので「行方向は７行分」、２４時間なので「列方向は２４列分」になります。

前後しましたが、１７４行目の「ComboNo = ComboBox1.ListIndex」で、コンボボックスを変更される前のデータ位置（一番上のデータをゼロとする行位置）を変数ComboNoに代入しています。この変数値は、コンボボックスの値がユーザーによって編集された時に、値を戻すためのみに使います。

次にChangeイベント（１８４～２００行目）です。
１８８行目の「If EventsOff = True Then Exit Sub」は、図４－１０でコンボボックスのリストを作っている最中には、このChangeイベントをスルーさせるものです。

１９０～１９３行目はコンボボックスの項目をユーザーが編集した時に、元に戻している部分です。
ユーザーがコンボボックスの項目を編集した時には、本来のリストとは異なるものになりますので、リストに付けられた番号ではない「－１」がListIndexの値になります。ですので１９０行目の「If Me.ComboBox1.ListIndex = -1 Then」は、「ユーザーが項目を編集した時」ということになります。
その場合は、１９１行目の「Me.ComboBox1.ListIndex = ComboNo」で、あらかじめ保管しておいた「編集前のリスト番号」をListIndexに与え、編集前の状態に戻しています。
そして１９２行目の「Exit Sub」でChangeイベントプロシージャを抜けます。
（この「Exit Sub」は無くても、無駄なデータ呼出しが１回増えるだけで、結果的には変わり無いようです。）

１９５行目の「NowWeek = Sheet1.Range("B4").Value」で「表示週の初日」の日付を、変数NowWeekに代入します。
１９６行目の「NowMachine = Me.ComboBox1.Text」で「現在の設備名（コンボボックスの表示項目）」を変数NowMachineに代入します。
そして１９８行目の「Call WeekRead(NowWeek, NowMachine)」で「WeekRead（図５－１４）」を呼び出し、データシートのデータを読み込み、入力シートへデータを上書きします。その読み込みのシートは「NowMachine」の名前のシートです。
また行位置は、日付「NowWeek」からStartD（図４－５の４８行目で定数設定）を引いた行になります。「行方向には７行分」を、また２４時間なので「列方向は２４列分」のデータを読み込んでいます。

５－２－４．スクロールバーの操作イベント

入力シートのスクロールバーの値を変更した時に発生するChangeイベントが図５－１１です。

202. '==========　⇩(18)　スクロールバーの値を変更した時　============
203. Private Sub ScrollBar1_Change()
204. 　Dim OldWeek As Date 　　　 '←変更前の日付
205. 　Dim NewWeek As Date 　　　 '←変更後の日付
206. 　Dim MachineName As String 　　'←現状の設備名
208. 　OldWeek = Sheet1.Range("B4").Value
209. 　MachineName = Me.ComboBox1.Text
211. 　Call WeekWrite(OldWeek, MachineName)
213. 　NewWeek = StartD + Me.ScrollBar1.Value * 7
214. 　Sheet1.Range("B4") = NewWeek
216. 　Call WeekRead(NewWeek, MachineName)
218. End Sub

図５－１１

２０８行目の「OldWeek = Sheet1.Range("B4").Value」で「表示週の初日」の日付を、変数OldWeekに代入します。
２０９行目の「MachineName = Me.ComboBox1.Text」で「現在の設備名（コンボボックスの表示項目）」を変数MachineNameに代入します。
その２つの変数OldWeek・MachineNameを使って、２１１行目の「Call WeekWrite(OldWeek, MachineName)」で入力シートのデータをデータシートへ書き込んでいます。

データを保存（書き込み）した後、２１３行目の「NewWeek = StartD + Me.ScrollBar1.Value * 7」で、スクロールバーの値（Me.ScrollBar1.Value）とシステム初日（StartD）から、変更後の週の初日を計算し、変数NewWeekに代入します。
そして２１４行目の「Sheet1.Range("B4") = NewWeek」で、入力シート上の日付軸の初日（Range("B4")）のセルに初日の値を書き込みます。入力シート上の他の日付セルには、初日の日付を基準とした数式が入っていますので、自動的に週全体の日付が新しくなります。

２１６行目の「Call WeekRead(NewWeek, MachineName)」ではデータを読み込みます。読み込み元は、データを書き込んだシートと同じ「MachineName」シートで、行位置は「NewWeek」から計算される位置となります。

なお、スクロールバーの値を変更しなくても、スクロールバーのスクロールボックス（左右に移動させるボックス）をマウスでクリックするだけで、（値が変更されなくても）Changeイベントが発生します。
この時にも図５－１１が実行される訳ですが、変更前の日付と変更後の日付が一緒ですので、入力シートのデータをデータシートに書き込み、またその同じデータを読み込んで入力シートに貼り付けることになります。
見掛け上は何も起こっていないように見えますが、入出力が発生しています。

５－３．⑤標準モジュール２（Module2）

５－３－１．入力シートへの稼働状態番号の記入

稼働状態を入力する各ボタンに登録されたマクロ（図５－５）から呼び出される「Cellwrite」が図５－１２です。
Cellwriteは引数Statを受け取ります。これは稼働状態を表す各ボタンに割り当てられた数値です。

220. '==========　⇩(19)　入力シートへの稼働状態番号の記入　============
221. Sub Cellwrite(stat As Integer)
222. 　Dim r As Range 　　　 '←セル範囲選択の1つ1つのセル
224. 　For Each r In Selection
225. 　　If Not Intersect(r, Range("kadouTable")) Is Nothing Then
227. 　　　If stat = 0 Then
228. 　　　　r.Value = ""
229. 　　　Else
230. 　　　　r.Value = stat
231. 　　　End If
233. 　　End If
234. 　Next r
236. End Sub

図５－１２

２２４行目の「For Each r In Selection」で、ユーザーが選択したセル範囲（Selection）から１セル１セルを取り出し、そのセルを「ｒ」としています。
そして２２５行目の「If Not Intersect(r, Range("kadouTable")) Is Nothing Then」で、そのｒのセル範囲が「黒枠内（Range("kadouTable")）」か否かを確認し、黒枠内であれば２２７～２３１行目を実行します。

２２７行目の「If stat = 0 Then」では、引数statがゼロ（＝稼働状態が「停止」）だったら、２２８行目の「r.Value = ""」で、セル範囲ｒを空白セルにしています。
「停止（stat=0）」以外のボタンの場合は、２３０行目の「r.Value = stat」で、セル範囲ｒに「引数（＝ボタンに対応した数値）」を書き込みます。

５－３－２．データの書き込み（保存）

図４－３の３１行目、図４－４の４３行目、図５－１０の１７９行目、図５－１１の２１１行目から呼び出される「WeekWrite」プロシージャが、図５－１３です。
引数として、書き込む日付（d）と書き込む設備名（m）を受取ります。

238. '==========　⇩(20)　データの書き込み（保存）　============
239. Sub WeekWrite(d As Date, m As String)
240. 　Dim buf As Variant 　　　 '←データの配列
242. 　buf = Range("kadouTable")
243. 　Sheets(m).Cells(d - StartD + 1, 1).Resize(7, 24) = buf
245. End Sub

図５－１３

２４２行目の「buf = Range("kadouTable")」では、入力シートの黒枠内「Range("kadouTable")」のデータを配列の形で一旦変数bufに代入します。
２４３行目の「Sheets(m).Cells(d - StartD + 1, 1).Resize(7, 24) = buf」では、貼り付け位置を計算しデータを書き込みます。
貼り付け位置の左上角のセルは「Cells(d - StartD + 1, 1)」です。データシートは「システム初日（StartD）が１行目」としていますので、週初日の日付「d」からシステム初日「StartD」を引いた数値が行位置になります。
そのセル位置を基準に黒枠内のサイズ「Resize(7, 24)」にセル範囲を広げ、貼り付け範囲としています。
また、貼り付け先のデータシートは設備名が付いたシートとしていますので、「Sheets(m)」となります。

今回システムでは、この書き込みプロシージャ（図５－１３）を呼び出す前に「週初日の取得」や「設備名の取得」をした形にしたため、図５－１３がやたら簡単なコードになってしまいました。逆に言うと、システムの改造などをする場合に多くの場所に手を入れなければならないシステムになってしまいました。
改善の余地が結構あるな　と反省しています。

５－３－３．データの読み込み

図５－１０の１９８行目、図５－１１の２１６行目から呼び出される「WeekRead」プロシージャが図５－１４です。引数として、読み込む日付（d）と読み込む設備名（m）を受取ります。

247. '==========　⇩(21)　データの読み込み　============
248. Sub WeekRead(d As Date, m As String)
249. 　Dim buf As Variant 　　　 '←データの配列
251. 　buf = Sheets(m).Cells(d - StartD + 1, 1).Resize(7, 24)
252. 　Range("kadouTable") = buf
254. End Sub

図５－１４

このプロシージャは、書き込みプロシージャ（図５－１３）とほぼ逆の動きをしています。
２５１行目の「buf = Sheets(m).Cells(d - StartD + 1, 1).Resize(7, 24)」で、データシート「Sheets(m)」からデータを読み取り、配列として変数bufに代入します。
その後の２５２行目「Range("kadouTable") = buf」で、入力シートの黒枠内にデータを貼り付けます。

６．集計シートの動作、計算・出力

６－１．⑥集計シートの設定（Sheet2）

集計シート（Sheet2）の配置・設定は図６－１のようにしました。

図６－１

上部に「集計種別（ComboBox2）」、「集計開始日（TextBox1）」、「集計終了日（TextBox2）」、「設備リスト（ComboBox1）」を並べ、一番右側にボタンを配置しマクロ「CalcStart」を登録しています。
ボタン以外はActiveXコントロールで作っています。

また、図４－６の６３行目で設定している、「DataOutputRange」と名前をつけたセル（今回はA4セル）は、貼り付けデータの文字が配置したコントロールに被らないように、少し下に離した場所にしました。
なおグラフを図６－１の右側に貼り付けていますが、データの量に連動するような仕掛けは今回していません。

６－２．⑦標準モジュール３（Module3）

６－２－１．集計ボタン押下時

集計シート（Sheet2）の「集計ボタン」をクリックした時に呼び出されるマクロが図６－２です。

256. '==========　⇩(22)　集計ボタン押下時　============
257. Sub CalcStart()
258. 　Dim SDay As Date 　　　　 '←集計開始日
259. 　Dim EDay As Date 　　　　 '←集計終了日
260. 　Dim Machine As String 　　　 '←集計する設備名
262. 　On Error Resume Next
263. 　　SDay = Sheet2.TextBox1.Value
264. 　　EDay = Sheet2.TextBox2.Value
266. 　　If Not Err.Number = 0 Then
267. 　　　MsgBox "日付形式が間違っています"
268. 　　　Exit Sub
269. 　　End If
270. 　On Error GoTo 0
272. 　If SDay ≷ EDay Then
273. 　　MsgBox "開始と終了が逆転しています"
274. 　　Exit Sub
275. 　End If
277. 　If SDay < StartD Then
278. 　　SDay = StartD
279. 　　If EDay < StartD Then
280. 　　　MsgBox "開始日～終了日のデータは存在しません"
281. 　　　Exit Sub
282. 　　End If
283. 　　MsgBox "開始日をデータの存在する日からに再設定します"
284. 　End If
287. 　If Sheet2.ComboBox1.ListIndex = -1 Then
288. 　　MsgBox "設備名が存在しません"
289. 　　Sheet2.ComboBox1.ListIndex = 0
290. 　　Exit Sub
291. 　End If
292. 　Machine = Sheet2.ComboBox1.Text
294. 　Select Case Sheet2.ComboBox2.ListIndex
295. 　　Case 0
296. 　　　Call Calc_Day(Machine, SDay, EDay)
297. 　　Case 1
298. 　　　Call Calc_Week(Machine, SDay, EDay)
299. 　　Case 2
300. 　　　Call Calc_Month(Machine, SDay, EDay)
301. 　　Case Else
302. 　　　MsgBox "集計種別が存在しません"
303. 　　　Sheet2.ComboBox2.ListIndex = 0
304. 　End Select
306. End Sub

図６－２

２６２～２７０行目では日付を取得し、取得した値が日付で無かった場合にはエラーを出しています。
まず、２６３行目の「SDay = Sheet2.TextBox1.Value」、２６４行目の「Eday = Sheet2.TextBox2.Value」で「集計開始日」・「集計終了日」を取得します。取得値を代入する先のSDay・EDayは、２５８～２５９行目でDate型として宣言していますので、もしTextBoxの値が日付型で無かった時には「値を代入する時点でエラー」が発生します。

ですので２６２行目の「On Error Resume Next」でエラーをスルーさせて、値を代入し終わった２６６行目の「If Not Err.Number = 0 Then」でエラー有無を調べます。
エラーがあった（Not Err.Number = 0）ということは、SDayかEDayが「日付では無い」ことになりますので、２６７行目の「MsgBox "日付形式が間違っています"」でコメントを出し、２６８行目の「Exit Sub」で処理を抜け出します。

２６２～２７０行目の処理を通過できればSDay・EDayは日付型です。しかしSDayとEDayの前後関係が逆転している可能性があります。ですので２７２行目の「If SDay ≷ EDay Then」で「日付が前後逆転」している場合は、２７３行目の「MsgBox "開始と終了が逆転しています"」でコメントを出し、２７４行目の「Exit Sub」で処理を抜け出します。

２７７～２８４行目では、ユーザーが設定した集計開始日・集計終了日をシステムの初日（StartD）と比較しています。
システム初日に対する集計開始日（SDay）と集計終了日（EDay）の関係は、図６－３の左側の３種類です。

図６－３

StartDより前にはデータがありませんので、データを取得できるのは「図６－３の右側の青く色を塗った部分」のみになります。この関係を分岐していきます。

まず２７７行目の「If SDay < StartD Then」で集計開始日がシステム初日よりも前の場合（図６－３の①②）は、２７８行目の「SDay = StartD」で「集計開始日をシステム初日に移動」させます。
次に２７９行目の「If EDay < StartD Then」で集計終了日もシステム初日より前の場合（図６－３の①）には、２８０行目でコメントを出し、２８１行目の「Exit Sub」で処理を抜け出します。
２７９行目のIf文が成立しない場合（図６－３の②）は、SDayのみ移動しEDayはそのままとし、２８３行目でコメントを出して処理を進めていきます。

２８７～２９１行目では「設備名のコンボボックスが正しく選択されているか」を調べています。正しく選ばれていればListIndex値がゼロ以上になりますが、編集されていると「－１」となります。
ですので２８７行目の「If Sheet2.ComboBox1.ListIndex = -1 Then」でListIndex値を調べ、－１ならば２８８行目の「MsgBox "設備名が存在しません"」でコメントを出し、２８９行目の「Sheet2.ComboBox1.ListIndex = 0」でリストの一番上を表示させています。
図５－１０では、GotFocusで現状の設備名を一時保管し復元させているのですが、ここではそこまで必要が無いだろうと思い、単純にListIndex = 0 としました。
コンボボックスの表示名を復元した後は、２９０行目の「Exit Sub」で処理を抜け出します。
コンボボックスを正しく操作した場合は２９２行目の「Machine = Sheet2.ComboBox1.Text」で、選択した設備名を取得し、変数Machineに代入します。

２９４～３０４行目では、集計種別により処理を分岐させています。
集計種別リストは図４－１１で作っていますので、「日単位（ListIndex = 0）」「週単位（ListIndex = 1）」「月単位（ListIndex = 2）」と並んでいます。ですので２９４行目の「Select Case Sheet2.ComboBox2.ListIndex」で、ListIndex値で仕訳けています。

日単位・週単位・月単位で処理が大きく異なるため、今回はそれぞれ独立したプロシージャにしています。日単位の場合はCalc_Day（２９６行目）、週単位の場合はCalc_Week（２９８行目）、月単位の場合はCalc_Month（３００行目）を呼出しています。引数としては、どの処理も「設備名」「集計開始日」「集計終了日」の３つを渡します。

但し、集計種別のコンボボックスの項目を編集されてしまった場合は、処理が出来ません。編集すると「ListIndex = -1」となりますので、３０１行目の「Case Else」で引っ掛けて３０２行目の「MsgBox "集計種別が存在しません"」でコメントを出し、３０３行目の「Sheet2.ComboBox2.ListIndex = 0」でコンボボックスのリストの一番上（今回は、日単位）に戻しています。
その後はプロシージャが終了するだけですので、処理としては何もしません。

６－２－２．データを取得し、日ごとに集計

日単位・週単位・月単位の処理・出力をする前に、まずはデータを取得する必要があります。そして二次処理（週単位・月単位の処理）をする前の一時処理（日単位の処理）も必要ですので、それを図６－４の関数「Calc_Data」で行います。

308. '==========　⇩(23)　データ取得と日毎集計　============
309. Function Calc_Data(m As String, SDay As Date, Eday As Date) As Variant
310. 　Dim buf1 As Variant 　　　　 '←生データの配列
311. 　Dim buf2 As Variant 　　　　 '←出力データ形式の配列
312. 　Dim uniBuf1 As Variant 　　　 '←１日単位の生データ
313. 　Dim FQcount As Variant 　　　 '←１日単位の集計
314. 　Dim i As Long 　　　　　　　 '←カウンタ変数（行方向）
315. 　Dim j As Long 　　　　　　　 '←カウンタ変数（列方向）
317. 　buf1 = Sheets(m).Range("A1").Offset(SDay - StartD, 0).Resize(Eday - SDay + 1, 24)
319. 　ReDim buf2(0 To Eday - SDay + 1, 0 To UBound(StatS, 1) + 2)
321. 　For j = 0 To UBound(StatS, 1)
322. 　　buf2(0, j + 1) = StatS(j)
323. 　Next j
325. 　buf2(0, 0) = "日付"
326. 　buf2(0, UBound(buf2, 2)) = "停止"
328. 　For i = 1 To UBound(buf2, 1)
329. 　　buf2(i, 0) = SDay + (i - 1)
331. 　　If UBound(buf1, 1) = 1 Then
332. 　　　uniBuf1 = buf1
333. 　　Else
334. 　　　uniBuf1 = WorksheetFunction.Index(buf1, i)
335. 　　End If
337. 　　FQcount = WorksheetFunction.Frequency(uniBuf1, StatN)
339. 　　For j = 1 To UBound(StatS, 1) + 1
340. 　　　buf2(i, j) = FQcount(j, 1)
341. 　　Next j
343. 　　buf2(i, UBound(buf2, 2)) = 24 - WorksheetFunction.Count(uniBuf1)
345. 　Next i
347. 　Calc_Data = buf2
348. End Function

図６－４

まず３１７行目の「buf1 = Sheets(m).Range("A1").Offset(SDay - StartD, 0).Resize(Eday - SDay + 1, 24)」で、データシートから対象の範囲のデータを取得し、変数buf1に代入します。ワークシートの左上角のA1セル（Range("A1")）を基準に、Offsetで下方向に移動させていますが、図５－１４の２５１行目のように「Sheets(m).Cells(SDay - StartD + 1, 1).Resize(Eday - SDay + 1, 24)」と、最初から移動させた基準セルを用いてもOKです。

３１９行目の「ReDim buf2(0 To Eday - SDay + 1, 0 To UBound(StatS, 1) + 2)」は、変数buf2のサイズを関数Calc_Dataの戻り値のサイズになるように調整しています。図６－５は、そのサイズを表しています。

図６－５

まず縦方向は、ゼロ行目にタイトルを記入し、１行目以降に日付ごとのデータを並べていきますので、必要行数は「ゼロ」～「Eday - SDay + 1」となります。
次に横方向は、ゼロ列目に日付を記入し、１列目以降に稼働状態（StatS）を並べていきます。ただし「停止」はStatSに含まれていませんので、必要列数は「ゼロ」～「UBound(StatS, 1) + 2」となります。
これを式にすると、３１９行目のReDim式になります。

３２１～３２３行目では「稼働状態のタイトル」を記入しています。
３２１行目の「For j = 0 To UBound(StatS, 1)」で、稼働状態StatSの要素数だけ、カウンタ変数ｊを回します。
３２２行目の「buf2(0, j + 1) = StatS(j)」で、「０行目」に稼働状態をタイトルとして記入しますが「０列目は日付」ですので「ｊ＋１」としています。

３２５行目の「buf2(0, 0) = "日付"」で「０行、０列」に日付の文字列を記入します。
３２６行目の「buf2(0, UBound(buf2, 2)) = "停止"」で「０行、最終列」に停止の文字列を記入します。これで、０行目のタイトルが完成です。

３２８～３４５行目のFor～Nextで、１行目以降にデータを入れていきます。回す回数は、３２８行目の「For i = 1 To UBound(buf2, 1)」のように「UBound(buf2, 1)」回です。buf1はワークシートのセル範囲を配列に格納しているため、インデックスは１から始まっています。
３２９行目の「buf2(i, 0) = SDay + (i - 1)」は、０列目に日付を入れています。集計開始日SDayを基準にカウンタ変数ｉで日数を加えています。

３３１～３３５行目は、二次元配列のbuf1データから、１つの日付分のデータ「１行分」だけを切り出し、「一次元配列」のデータにしています。
メインコードは３３４行目の「uniBuf1 = WorksheetFunction.Index(buf1, i)」で、ワークシート関数Indexを使い「配列buf1のｉ行目だけを切り出し」て、変数uniBuf1に代入（一次元配列）しています。

しかし、例えば「5月4日～5月4日」のような「１日のみ」のデータを集計しようとすると、buf1は一応二次元配列ではあるのですがIndex関数が上手く働かず、変数uniBuf1にデータが入ってくれません（Emptyのまま）。ですので３３１行目の「If UBound(buf1, 1) = 1 Then」で、データが１行の場合には、３３２行目で「uniBuf1 = buf1」と、直接buf1をuniBuf1に入れています。

３３７行目の「FQcount = WorksheetFunction.Frequency(uniBuf1, StatN)」では、１行（＝１日）のみの配列データuniBuf1をワークシート関数のFrequencyを使って、図６－６のように「配列内の各データが何個あるか」を計算させています。


図６－６

「Frequency(uniBuf1, StatN)」は、配列uniBuf1内のデータの頻度分布を区間配列StatNに従って出力する　というものです。今回は配列uniBuf1内には１～４の整数、区間配列StatNも１～４の整数ですので、うまく数の計算が出来ていますが、区間配列という言葉から分かる様に「〇を超えて△以下」という範囲でのカウントをしています。
ですので、もし配列uniBuf1内に「2.1」というような値があった場合は、区間３の所に分類されることになりますし、今回だと「４」が最高ですが、「４超」という区間もあるため、Frequencyの結果である「FQcount」は、FQcount(1)からFQcount(5)までの５要素となることに注意が必要です。

３３９～３４１行目では、配列buf2にFrequencyで数えた各稼働状態の個数を入れています。
３３９行目の「For j = 1 To UBound(StatS, 1) + 1」で、カウンタ変数ｊを稼働状態StatSの要素数分（今回は４要素）だけ回し、３４０行目の「buf2(i, j) = FQcount(j, 1)」で「停止」を除いた項目に数値を入れています。

なお「停止」についてはFrequencyでは数えていないので、日単位のデータ配列uniBuf1内のデータの個数を全体「24」から引いた値となります。ですので３４３行目の「buf2(i, UBound(buf2, 2)) = 24 - WorksheetFunction.Count(uniBuf1)」で計算し、配列buf2の最後の列に値を入れます。

この処理を各日付に対して繰り返し行い、３４７行目の「Calc_Data = buf2」で関数Calc_Dataの戻り値にします。

６－２－３．日単位のデータを出力

図６－２の２９６行目から呼び出される「Calc_Day」が、図６－７です。
引数として「m（設備名）」「SDay（集計開始日）」「EDay（集計終了日）」を受け取ります。

350. '==========　⇩(24)　日単位のデータを出力　============
351. Sub Calc_Day(m As String, SDay As Date, Eday As Date)
352. 　Dim dayData As Variant 　　　　 '←日ごとの集計データ配列
354. 　dayData = Calc_Data(m, SDay, Eday)
356. 　Call DataClear
357. 　Range("DataOutputRange").Resize(UBound(dayData, 1) + 1, UBound(dayData, 2) + 1) = dayData
358. End Sub

図６－７

３５４行目の「dayData = Calc_Data(m, SDay, Eday)」では、受け取った引数をそのまま関数Calc_Data（図６－４）に渡して「日ごとの集計データ」を変数dayDataに格納します。

日単位の集計の形は、関数Calc_Dataの出力の形で既に完成しているため、３５７行目の「Range("DataOutputRange").Resize(UBound(dayData, 1) + 1, UBound(dayData, 2) + 1) = dayData」で貼り付けるだけです。
貼り付け先は、名前「DataOutputRange」で設定されたセル位置を基準にし、配列dayDataの縦横サイズから貼り付けサイズをResizeで調整します。なお配列dayDataのインデックスは行・列ともにゼロから始まっているため、行・列ともサイズは「＋１」しています。

なお貼り付ける前に、３５６行目の「Call DataClear」で、図６－１９の「DataClearプロシージャ」を呼出し、既に集計結果が貼り付けられていた場合には、消去してから書き込んでいます。

６－２－４．週単位に再計算し出力

図６－２の２９８行目から呼び出される「Calc_Week」が、図６－８です。
引数として「m（設備名）」「SDay（集計開始日）」「EDay（集計終了日）」を受け取ります。

360. '==========　⇩(25)　週単位に再計算し出力　============
361. Sub Calc_Week(m As String, SDay As Date, Eday As Date)
363. 　Const WeekStart As Integer = 1 　 '←集計の為の週初めを表す数値(日曜=1)
365. 　Dim dayData As Variant 　　　 '←日ごとの集計データ配列
366. 　Dim WeekCount As Long 　　　 '←SDayとEDayの間の週の数
367. 　Dim buf1 As Variant 　　　　 '←１週間分の積算データの配列
368. 　Dim buf2 As Variant 　　　　 '←週単位データの配列(含タイトル)
369. 　Dim MoveF As Integer 　　　　 '←指定開始日と週初日の差
370. 　Dim i As Long 　　　　　　　 '←カウンタ変数(日単位データの行方向)
371. 　Dim j As Long 　　　　　　　 '←カウンタ変数(列方向)
372. 　Dim k As Long 　　　　　　　 '←カウンタ変数(週単位データの行方向)
374. 　dayData = Calc_Data(m, SDay, Eday)
376. 　If Weekday(SDay) ≷= WeekStart Then
377. 　　MoveF = Weekday(SDay) - WeekStart
378. 　Else
379. 　　MoveF = Weekday(SDay) + 7 - WeekStart
380. 　End If
382. 　WeekCount = Int(((SDay - MoveF) - Eday - 1) / 7) * -1
384. 　ReDim buf2(0 To WeekCount, 0 To UBound(dayData, 2))
386. 　buf2(0, 0) = "週初日"
387. 　For j = 1 To UBound(dayData, 2)
388. 　　buf2(0, j) = dayData(0, j)
389. 　Next j
391. 　i = UBound(dayData, 1) + 1
392. 　For k = UBound(buf2, 1) To 1 Step -1
394. 　　ReDim buf1(1 To UBound(dayData, 2))
396. 　　Do
397. 　　　If i <= 1 Then Exit Do
398. 　　　i = i - 1
399. 　　　For j = 1 To UBound(dayData, 2)
400. 　　　　buf1(j) = buf1(j) + dayData(i, j)
401. 　　　Next j
403. 　　　DoEvents: DoEvents
404. 　　Loop Until Weekday(dayData(i, 0)) = WeekStart
406. 　　buf2(k, 0) = dayData(i, 0)
407. 　　For j = 1 To UBound(buf2, 2)
408. 　　　buf2(k, j) = buf1(j)
409. 　　Next j
410. 　Next k
412. 　buf2(1, 0) = buf2(1, 0) - MoveF
414. 　Call DataClear
415. 　Range("DataOutputRange").Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2
417. End Sub

図６－８

まず３６３行目の「Const WeekStart As Integer = 1」で、週単位の集計に使用する「週初めを何曜日とするか」の設定を行っています。ここでは「１」を指定していますので「日曜日」を週初めとしています。
別な曜日に設定する場合は、曜日を表す数値（月曜＝２、火曜＝３、・・・、土曜＝７）にして下さい。

３７４行目の「dayData = Calc_Data(m, SDay, Eday)」では、日ごとの集計データを取得し、配列dayDataに入れています。これは日単位の時と同じです。

ここで、日単位のデータを週単位に集計し直す方法を、図６－９のように考えました。
ユーザーが集計開始日・終了日の日付を、週の先頭・週の最終に設定してくれるかどうかは分かりませんので、ユーザーが指定した開始日から終了日までの期間を含んだ週単位の枠とします。但し、ユーザーが日付を指定していない期間もデータを含めるのは変なので、データとしてはユーザー指定の日付期間とします。

図６－９

なお、日単位データには０列目に「日付」が入っているのと同様に、週データにも０列目に「週の初日の日付」を入れることにしました。ここで週の初日同士のデータは「７日間隔」になっていないと、グラフ化などの時におかしくなると考え、ユーザー指定の開始日では無い「週の初日」が書き込まれる可能性もあります。

この「週の初日」の計算のため、また「日単位のデータは、何週分のデータ」かを計算するために、週初め（定数WeekStartの値）とSDayの曜日値の差（ここでは、変数MoveFとしました）を求める必要があります。
まず、週初めが日曜の場合を図６－１０で考えます。日曜日の週の値は１です。

図６－１０

図６－１０を見ると、差（変数MoveF）は「SDayの週の値」－「日曜の週の値＝１（定数WeekStart＝１）」で求まります。
しかし、例えば火曜日が週初めとした場合（床屋さん等は、このような週の感覚かもしれません）は、図６－１１のように少し複雑になります。

図６－１１

図６－１１を見ると差（変数MoveF）は、
「SDayの曜日の値 ＞＝ 週初めの曜日の値（定数WeekStart値）」の時：「SDayの週の値 － 定数WeekStart値」
「SDayの曜日の値 ＜ 週初めの曜日の値（定数WeekStart値）」の時：「SDayの週の値 － 定数WeekStart値 + 7」
とSDayの曜日によって分岐させる必要があります。

この差（変数MoveF）を求める手法をコードにしたのが３７６～３８０行目です。
３７６行目の「If Weekday(SDay) ≷= WeekStart Then」で、「SDayの曜日の値 ＞＝ 週初めの曜日の値」を判断し、成立していれば３７７行目の「MoveF = Weekday(SDay) - WeekStart」で「正数」の差を求めます。
また、それ以外（SDayの曜日の値 ＜ 週初めの曜日の値）の時には、３７９行目の「MoveF = Weekday(SDay) + 7 - WeekStart」で「正数」の差を求めます。
別な考え方として、３７７行目をなにしろ計算して「結果がマイナスだったら７を足す」というのでもＯＫです。

次に、週単位のデータを入れる入れ物のサイズを決めるため、集計開始日から終了日までが「何週間あるか」を計算します。
図で示すと、図６－１２のようになります。

図６－１２

つまり、日数（開始日は、開始曜日に移動した日付）を７日で割った商を「切り上げ」した数値になります。コードで表現すると、３８２行目の「WeekCount = Int(((SDay - MoveF) - Eday - 1) / 7) * -1」になります。
「(SDay - MoveF)」と「Eday」が逆転しているので、「((SDay - MoveF) - Eday - 1) / 7」の計算結果はマイナス値になります。そのマイナス値に対して今回「Int関数」を使っている訳ですが、「Int関数」は「小さい方の整数にする」という仕様のため、「マイナス値をIntで整数化し、さらに『－１』を掛ける」と「切り上げ」をしたことになります。

図６－１２の下側の16日間で確認してみます。まず「 -16／7＝-2.286 」で、そのマイナス値に対してInt関数で処理すると「Int(-2.286)= -3」となり、-3に-1を掛けることで「3（週間）」という計算結果が得られることになり、その値が変数WeekCountに代入されます。

３８４行目の「ReDim buf2(0 To WeekCount, 0 To UBound(dayData, 2))」では、週単位集計の入れ物であるbuf2のサイズを変更しています。
行方向はゼロから３８２行目で求めたWeekCountになります。週単位集計もゼロ行目はタイトル、ゼロ列目は「週の初日」を記入します。
また列方向は、日単位の配列と同じですので、ゼロから「UBound(dayData, 2)」となります。

３８６～３８９行目では、配列buf2のゼロ行目にタイトルを記入しています。月単位のbuf2は図６－１３の様なフォーマットになっています。

図６－１３

日単位と異なるのはゼロ行ゼロ列の部分で、まず３８６行目の「buf2(0, 0) = "週初日"」とタイトルを記入します。
続いて３８７～３８９行目で、日単位と同じタイトルを書き込みます。この際は、カウンタ変数ｊを１から開始させます。

３９１～４１０行目で週ごとに集計をして配列buf2にデータを書きこんでいます。
ここで、処理をしていく順番について考えます。図６－１４は、日単位データを先頭から処理していく場合です。

図６－１４

今回の場合、日単位のデータを週単位でどんどん積み上げて、「週末が来たら」積み上げたデータを週単位データに移動する　というロジックが必要になります。
今まで、週初日については「定数値WeekStart（３６３行目で宣言済み）」があり、それを使って「週初日との差」を求めたり、週の数を計算したりしてきました。それなのに、データを週単位で積み上げるためには別の「週末」の計算も必要　というのは、あまり上手い方法とは思えません。

そこで、逆に一番後ろ側から処理していくことを考えたのが図６－１５です。

図６－１５

この方法だと「週末」は全く気にせず、「週初め」が来るまで積み上げて、「週初め」が来たら週単位データに移動する　ことで成立します。週初めは「定数WeekStart値」をそのまま使えば良いのです。

ということで、より簡単に処理が可能な「後方からの処理方法」を今回採用しました。
３９１行目の「i = UBound(dayData, 1) + 1」は、日単位データの「最終行＋１」行目をスタート位置にしています。わざと「＋１」させているのは、同じ週内のデータ積算（３９６～４０４行目のDo～Loop内）で、処理を行う前の３９８行目で「i = i - 1」させている為です。

３９２～４１０行目のFor k=･･･～Next では、週単位データの入れ物の行位置を移動させています。
３９２行目の「For k = UBound(buf2, 1) To 1 Step -1」で、カウンタ変数ｋを日データ処理と同様に「最後尾から上へ」移動させるために、「Step -1」で逆に動かしています。

３９４行目の「ReDim buf1(1 To UBound(dayData, 2))」では、週内の積算データを記憶しておくための配列buf1のサイズを決めています。なおこのコードは、３９２行目の「For k =･･･」の内側、且つ３９６行目のDo～Loopの外側にありますので、週ごとの処理が終了するたびに「初期化」が必要です。そのためReDimにはPreserveキーワードは付けません。

３９６～４０４行目のDo～Loop内は、日単位データ（配列dayData）を週単位で積算していく工程です。Doを回す条件は、４０４行目の「Loop Until Weekday(dayData(i, 0)) = WeekStart」で、「積算をした日の曜日が、週の初日（WeekStart）であれば抜け出す」ことにしています。この条件はLoop側に付けていますので「週初日のデータを積算した後」で抜け出すことになります。

３９７行目は、最後に説明します。
３９８行目の「i = i - 1」は、日単位データの行位置を示すカウンタ変数ｉの値を１つデクリメントしています。３９１行目では変数ｉに「最終行＋１」を代入していますので、一番最初にこの行が実行されることで変数ｉは「最終行」を指し示した上で３９９～４０１行目の処理を実行することになります。

３９９～４０１行目のFor～Nextは、停止を含めた各稼働状態ごとにデータを配列buf1に積算しています。
３９９行目の「For j = 1 To UBound(dayData, 2)」でカウンタ変数ｊを列方向に移動させ、４００行目の「buf1(j) = buf1(j) + dayData(i, j)」で、配列buf1の各要素に日単位データ（配列dayData）を積算させています。

４０３行目の「DoEvents: DoEvents」は、万一無限ループになってしまった場合に、ESCキーなどを押す事でプログラムを停止できるようにしています。

３９７行目に戻ります。Do～Loopを脱出条件は４０４行目の「Loop Until Weekday(dayData(i, 0)) = WeekStart」と「週初日になったら」ですが、日単位データが週初日から始まっていない場合は「４０４行目の条件を満たさない」ままタイトル行を読み込んでしまうことになります。
そこで３９７行目の「If i <= 1 Then Exit Do」で、「行位置を示す変数ｉが１になったら（３９８行目でｉをデクリメントするので、タイトル行を読み込む直前）、Do～Loopを抜け出します。

Do～Loopを抜け出した後、４０６行目の「buf2(k, 0) = dayData(i, 0)」で「日付」を入れます。この段階での変数ｉは、Do～Loopを抜けて来たばかりなので「（３９７行目のExit Do で抜けた時を除いて）変数ｉは週の初日のデータ行」です。ですので、代入する値である「dayData(i, 0)」は「週の初日の日付」となります。

次に４０７～４０９行目のFor～Nextは積算したデータを配列buf2に入れています。
４０７行目の「For j = 1 To UBound(buf2, 2)」で、稼働状態を列方向に移動しながら、４０８行目の「buf2(k, j) = buf1(j)」で週ごとの積算データを対応する配列buf2の中に代入しています。

ここまでが３９２～４１０行目のFor k=･･･～Nextで週単位の配列にデータを埋めるコードです。最後に「３９７行目のExit Do で抜けた時（＝日単位データが週初日から始まっていない時）」に、４０６行目で暫定的に「日単位データの配列の一番上の日付」を入れたのですが、それを「週の初めの日付」に修正する必要があります。
ですので４１２行目の「buf2(1, 0) = buf2(1, 0) - MoveF」で、３７６～３８０行目で計算した「MoveF（SDayとWeekStartとの差）」分だけ日付を戻しています。
（開始日が週初日だった場合には、MoveF = 0 なので、同じ値を上書きしてしまうことになります。）

これで週単位のデータが完成したため、４１４行目の「Call DataClear」で元の貼り付けデータを消去し、４１５行目の「Range("DataOutputRange").Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2」で、セル名「DataOutputRange」を左上角セルとするセル範囲に、データを貼り付けます。

なお、３９１行目の「i = UBound(dayData, 1) + 1」で「最終行＋１」行目に移動させていることに違和感がある場合は図６－１６のように「i = UBound(dayData, 1)」と「最終行」に移動した行位置からスタートすることも可能です。
ただし、その場合には図６－１６の赤字部分（カウンタ変数ｉをデクリメントする位置、及び変数の行位置を示す引数値、タイトル行に達した時の不等号）が異なりますが、自分の分かり易い方を使って下さい。

• 　i = UBound(dayData, 1) ' + 1
• 　For k = UBound(buf2, 1) To 1 Step -1
• 　　ReDim buf1(1 To UBound(dayData, 2))
• 　　Do
• 　　　If i < 1 Then Exit Do
• '　　　i = i - 1
• 　　　For j = 1 To UBound(dayData, 2)
• 　　　　buf1(j) = buf1(j) + dayData(i, j)
• 　　　Next j
• 　　　i = i - 1
• 　　　DoEvents: DoEvents
• 　　Loop Until Weekday(dayData(i + 1, 0)) = WeekStart
• 　　buf2(k, 0) = dayData(i + 1, 0)
• 　　For j = 1 To UBound(buf2, 2)
• 　　　buf2(k, j) = buf1(j)
• 　　Next j
• 　Next k

図６－１６

６－２－５．月単位に再計算し出力

図６－２の３００行目から呼び出される「Calc_Month」が、図６－１７です。
引数として「m（設備名）」「SDay（集計開始日）」「EDay（集計終了日）」を受け取ります。

419. '==========　⇩(26)　月単位に再計算し出力　============
420. Sub Calc_Month(m As String, SDay As Date, Eday As Date)
421. 　Dim dayData As Variant 　　　　 '←日ごとの集計データ配列
422. 　Dim MonthCount As Long 　　　 '←SDayとEDayの間の月の数
423. 　Dim buf1 As Variant 　　　　 '←単月分の積算データの配列
424. 　Dim buf2 As Variant 　　　　 '←月単位データの配列(含タイトル)
425. 　Dim i As Long 　　　　　　　 '←カウンタ変数(日単位データの行方向)
426. 　Dim j As Long 　　　　　　　 '←カウンタ変数(列方向)
427. 　Dim k As Long 　　　　　　　 '←カウンタ変数(月単位データの行方向)
429. 　dayData = Calc_Data(m, SDay, Eday)
431. 　MonthCount = DateDiff("m", SDay, Eday) + 1
433. 　ReDim buf2(0 To MonthCount, 0 To UBound(dayData, 2))
435. 　buf2(0, 0) = "年月"
436. 　For j = 1 To UBound(dayData, 2)
437. 　　buf2(0, j) = dayData(0, j)
438. 　Next j
440. 　i = UBound(dayData, 1) + 1
441. 　For k = UBound(buf2, 1) To 1 Step -1
443. 　　ReDim buf1(1 To UBound(dayData, 2))
445. 　　Do
447. 　　　If i <= 1 Then Exit Do
449. 　　　i = i - 1
451. 　　　For j = 1 To UBound(dayData, 2)
452. 　　　　buf1(j) = buf1(j) + dayData(i, j)
453. 　　　Next j
455. 　　　DoEvents: DoEvents
456. 　　Loop Until Day(dayData(i, 0)) = 1
458. 　　buf2(k, 0) = Format(dayData(i, 0), "YYYY/MM")
459. 　　For j = 1 To UBound(buf2, 2)
460. 　　　buf2(k, j) = buf1(j)
461. 　　Next j
463. 　Next k
465. 　Call DataClear
466. 　Range("DataOutputRange").Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2
468. End Sub

図６－１７

月単位の集計では、週単位集計の時に設定したような「週初日を表す定数（図６－８の３６３行目）」は設けていません。月の始まりは「１日（〇月〇日の日）」と固定しています。

まず、４２９行目の「dayData = Calc_Data(m, SDay, Eday)」で日単位データを取得するのは、日単位集計・週単位集計の場合と同じです。
４３１行目の「MonthCount = DateDiff("m", SDay, Eday) + 1」では、DateDiff関数を使って「集計開始日（SDay）から終了日（EDay）までの月数」を計算しています。なおDateDiffでは、例えば「開始日が2/1」「終了日が3/1」の場合は「１（か月）」が返ってきます。これは「2/1の午前0時」から「3/1の午前0時」までの期間を計算しているためです。
しかし今回システムでは、終了は「3/1の24時」までのつもりなので、２か月分の月の枠が必要となりますので「＋１」をしています。

４３３行目の「ReDim buf2(0 To MonthCount, 0 To UBound(dayData, 2))」では、月単位データの配列（buf2）のサイズを変更しています。buf2は、図６－１８のようになっており、ゼロ列目は「年月」にしています。

図６－１８

４３５～４３８行目でタイトル行に文字列を入れています。
まず、４３５行目の「buf2(0, 0) = "年月"」で、ゼロ行（タイトル行）ゼロ列目に「年月」の文字列を記入します。
次に、４３６行目の「For j = 1 To UBound(dayData, 2)」で「１列目～最終列」までをForで回して、４３７行目の「buf2(0, j) = dayData(0, j)」で「日単位データのタイトル文字列を月単位データにコピー」しています。

４４０行目以下の流れは、ほぼ週単位集計の場合と同じです。但し、Do～Loopを抜け出る条件が「週初日の値 → １（日）」になるのと、ゼロ列目の日付は「年／月」ですので、日単位データの日付から「年／月」を取り出して貼り付けます。

まず、４４０行目の「i = UBound(dayData, 1) + 1」で、日単位データの「最終行＋１」にカーソルを持っていきます。
４４１行目の「For k = UBound(buf2, 1) To 1 Step -1」で、月単位の配列を下の方から埋めていくように、カウンタ変数ｋをセットします。
４４３行目の「ReDim buf1(1 To UBound(dayData, 2))」で、単月分の積算データ配列（buf1）のサイズを決めます。２回目以降では、配列の初期化の意味も含んでいます。

４４５～４５６行目のDo～Loop内で、稼働状態ごとに単月分の積算を行っていきます。
４４７行目の「If i <= 1 Then Exit Do」は週単位の時と同様に、日単位データの先頭が１日付で無い場合にDo～Loopを終了させています。
４４９行目の「i = i - 1」は、カーソルを１つずつ上に移動させています。
４５１行目の「For j = 1 To UBound(dayData, 2)」で稼働状態を１つずつ移動させながら、４５２行目の「buf1(j) = buf1(j) + dayData(i, j)」で積算を行っています。
４５６行目の「Loop Until Day(dayData(i, 0)) = 1」にDo～Loopの脱出条件が設定されており、「日単位データの日付が１日付けが来たらDo～Loopを終了」します。

Do～Loopを抜けたら４５８行目の「buf2(k, 0) = Format(dayData(i, 0), "YYYY/MM")」で、日単位データの日付を「年／月」の形式にして、月単位データのゼロ列目に書き込みます。
次に４５９行目の「For j = 1 To UBound(buf2, 2)」でカウンタ変数ｊを回しながら、４６０行目の「buf2(k, j) = buf1(j)」で月単位に積算したデータを月単位データに書き込みます。

これで月単位のデータが完成したため、４６５行目の「Call DataClear」で元の貼り付けデータを消去し、４６６行目の「Range("DataOutputRange").Resize(UBound(buf2, 1) + 1, UBound(buf2, 2) + 1) = buf2」で、セル名「DataOutputRange」を左上角セルとするセル範囲に、データを貼り付けます。

６－２－６．出力データをクリア

図４－１の１５行目、図６－７の３５６行目、図６－８の４１４行目、図６－１７の４６５行目から呼び出される「DataClear」が図６－１９です。
集計シート（Sheet2）に出力されたデータを消去する機能です。

470. '==========　⇩(27)　既存の出力データをクリア　============
471. Sub DataClear()
472. 　Range("DataOutputRange").CurrentRegion = ""
473. End Sub

図６－１９

４７２行目の「Range("DataOutputRange").CurrentRegion = ""」は、「DataOutputRange」と名前を付けたセル（今回は、図４－６の６３行目で「Sheet2のA4セル」に設定）を基準としたCurrentRegion範囲（データの無い行・列で囲まれた範囲）を消去します。
なお、システム起動直後のように、既にデータが無い状態でこのコードを実行した時には、基準のA4セルに対してのみ長さゼロの文字列が書き込まれます。
また「Range("DataOutputRange").CurrentRegion.ClearContents」でも同じ結果が得られます。

７．最後に

その昔、今回システムに近い形の「設備稼働状態システム」を作った時には、１セルを１データにしてデータベースに登録・呼出しをしていました。その為あっと言う間に１００万レコードを超えてしまい、検索スピードをアップするためにインデックスを付けたり、ＳＱＬ文を工夫したりと苦労した記憶があります。
今回は１日で１行、しかも設備でもシートを分けたため、ある程度データが溜まってもそれほど遅くならないのでは　と楽観視しています。

しかしシステム内にデータを抱えている為ファイル自体は徐々に重くなっていきますし、また複数の人が同時に作業出来ないという不便さを感じる方もいるかもしれません。その時には、データファイルを別ファイルとして都度呼び出したりする改造も必要になってくると思います。
しかし何が何でも全てExcelで作ろうとせず、「他に適したソフトが無いか」という視点も大切だと思います。例えばデータを扱うと言えばデータベースですが、一般的にＳＱＬ文にはなじみが無いと思います。しかし、やってみるとExcelとは異なった面白さと便利さに気づかれると思います。

また、集計で出力するデータの単位は「時間」です。「稼働率」などを求める場合は「％」でしょうから「２４で割る」ことも考えましたが、会社や設備の種類によって「２４時間をベース」とするのか「人員が居る時間のみをベース」と見るかで変わることがあるため、基本単位である時間のみにしました。
都合により出力データの横に計算式を作ったりして加工して下さい。


設備の稼働状態記録とデータ集計（it-059.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。