こんにちは！ 健史(たけふみ)です。
Excelシート上のデータやテキストファイルなどを先頭から順次読み込み処理するバッチ処理のロジックには基本パターンがあります。
順次読み込み処理するロジックは、プログラミングのベースとなるものです。
ファイルを使うバッチの処理パターンは、大きく以下の３つと考えており、殆どのバッチ処理をカバーできます。
1.非コントールブレイク処理
2.コントロールブレイク処理
3.マッチング処理
1).１：１マッチング
2).１：Ｎマッチング
これらを理解し流用することで、一定の品質・生産性・スピードを確保できます。
この記事では、非コントールブレイク処理とコントールブレイク処理を紹介します。
非コントールブレイク処理
「非コントロールブレイク処理」とは私が付けた処理名で、データの先頭から全件を順次読み込み処理する単純な処理です。
その単純な非コントロールブレイク処理を説明することでコントロールブレイク処理が理解しやすくなると考え敢えて説明します。
例えば、先頭から読み込んだ売上ファイルの全売上金額を集計する場合です。
道路を走ることに例えると「どんなことが起きようと真っすぐに進む」イメージです。
フローチャート
ファイルの読み込み処理が2回ありますが、殆どの多くのプログラムで採用されており、専門用語ではプログラムの構造化の観点から採用されるロジックです。
ExcelVBAのプログラム
'変数定義
Dim ix1 As Long
Dim ix2 As Long
Dim wk_uri_kingaku As Long
'売上ファイルのオープン
ix1 = 0
'売上金額ファイルのオープン
ix2 = 0
Sheets(2).Cells.Clear
'初期値設定
wk_uri_kingaku = 0
'売上ファイルの読み込み
ix1 = ix1 + 1
'売上ファイルが終わるまで繰り返す
Do Until Sheets(1).Cells(ix1, 1) = ""
wk_uri_kingaku = wk_uri_kingaku + Sheets(1).Cells(ix1, 2)
ix1 = ix1 + 1
Loop
'売上金額ファイルへの出力
ix2 = ix2 + 1
Sheets(2).Cells(ix2, 1) = wk_uri_kingaku
作成したデータは以下の通りです。
・処理前
・処理後
入力の[売上ファイル]をSheets(1)、出力の[売上金額ファイル]をSheets(2)としています。
売上ファイルのA列は日付、B列は売上金額です。商品コードなどは省略しています。
売上ファイルの終了はSheets(1)のA列が[''](NULL)です。
読み込みは、変数ix1を1ずつアップします。
セルA1であるSheets(1).Cells(1,1) = ''(NULL)の場合は１件もデータがないことになります。
売上金額ファイルは、最初にクリアした状態にする必要があるため、[Sheets(2).Cells.Clear]でクリアしています。
書き込みは、変数ix2を1ずつアップします。
既にオープンしているExcelシートを仮のファイルに見立てて処理していることから、
・読み書きするために必要な添え字[ix1][ix2]に初期値設定すること、及び、書き込むためのシートクリアをファイルオープン
・処理結果をそのまま画面に表示しおくため、ファイルのクローズ処理はない
です。
コントロールブレイク処理
2つのパターンのロジックを紹介します。
処理結果は同じです。
日付順にソートされた売上ファイルを順次読み込み、日付別の売上金額を集計して売上金額ファイルに出力する処理です。
プログラム処理は、
しかし日付が変わったら集計した金額を売上金額ファイルに出力する
です。
現場では「日付がブレイクしたら、集計した金額を売上金額ファイルに出力する」と使(い)います。
道路を走ることに例えると「変化が生じたら進行を中断して寄り道して、寄り道が終わったら戻って元の道を進行する」イメージです。
ファイル読み込みが１回の場合
フローチャート その１
これは繰り返し処理の中で「ファイルの読み込み処理を１回だけしかできない」パターンです。
繰り返し処理の中でファイルの読み込み処理を１回だけしかできない記述方法のプログラミング言語があるために紹介しました。
それは「PL/SQL」という言語です。
もう少し具体的に説明致しますと、その記述方法であれば読み込み命令を記述しなくても読み込み処理され、「読み込まれている」前提で処理内容だけを記述すれば良いのです。
PL/SQLには「複数回の読み込みを行える記述方法」もありますが、プログラム行数が多くなること、そのため標準化の観点から使わないルール、または奨励していない職場があります。
ちなみに上記の記述方法、ルール化や奨励していることが「良くないこと」などと思っていませんので、悪しからずです。
その場合は、この処理ロジックで記述します。
本題の処理ロジックの補足説明です。
初期値でセットするのは、ブレイクキーの[ワーク.売上日付]だけで、金額を集計する[ワーク.売上金額]にはセットしません。
0をセットしても良いのですよ、ですがループ処理のなかで0をセットしているので敢えて行う必要はありません。
繰り返し処理のなかで、最初の1件目の1回だけしか通らない線がありますが、わかりますか。
「ワーク.売上日付 = 0」のときに分岐するオレンジ色の線です。
初期値セットで[ワーク.売上日付]に0をセットしているのですが、これは初回だけ行うスイッチ機能を果たしています。
1件目のときだけは、売上金額を集計していないため売上金額ファイルを出力しないように制御する必要があります。
1件目もキーブレイクした場合の処理を行いますが、「１件の場合だけは売上金額ファイルに出力しない」フローになっています。
2件目以降は、0ではなく読み込んだ日付がセットされていて、該当日付をもつ売上金額がワーク.売上金額に蓄積されているのです。
重要なロジックがメイン処理を終えた後にある「ワーク.売上日付の判断」です。
このロジックでは、メイン処理が終わった後にブレイク後の処理を行わないと最後のキーデータが処理されないのです。
これ、わかりますか？
この判断とブレイク後の処理を記述しないと、最後のデータがワークに残ったままで「無かったこと」になってしまうのです。
ですが「キーブレイクを判断する項目が初期値のまま」ということは、データが1件もなかったことになります。
そのため、最後の判断が必要なのです。
スイッチとして[ワーク.売上日付]を使いましたが、以下のように[ワーク.スイッチ]を使っても同じことです。
「売上日付が'0'～'99999999'、数字の羅列の全パターンが想定される」場合には、スイッチを使います。
プログラム その１
[ワーク.売上日付]をスイッチとして使う処理フローで作成したプログラムです。
Dim ix1 As Long
Dim ix2 As Long
Dim wk_uri_hizuke, wk_uri_kingaku As Long
'売上ファイルのオープン
ix1 = 0
'売上金額ファイルのオープン
ix2 = 0
Sheets(2).Cells.Clear
'初期値クリア
wk_uri_hizuke = 0
'売上ファイルの読み込み
ix1 = ix1 + 1
'売上ファイルが終わるまで繰り返す
Do Until Sheets(1).Cells(ix1, 1) = ""
If Sheets(1).Cells(ix1, 1) <> wk_uri_hizuke Then
'ワーク.日付が0の場合は1件目のデータなので出力しない
If wk_uri_hizuke <> 0 Then
'売上金額ファイルへの出力
ix2 = ix2 + 1
Sheets(2).Cells(ix2, 1) = wk_uri_hizuke
Sheets(2).Cells(ix2, 2) = wk_uri_kingaku
End If
'初期値設定
wk_uri_hizuke = Sheets(1).Cells(ix1, 1)
wk_uri_kingaku = 0
End If
'キーブレイク関係なく、売上ファイルの金額を合計欄に加算するだけ
wk_uri_kingaku = wk_uri_kingaku + Sheets(1).Cells(ix1, 2)
'売上ファイルの読み込み
ix1 = ix1 + 1
Loop
'最後の日付の金額を出力していないのでワーク.日付を判断して出力
' 日付が0の場合はデータが無かったことなので出力しない
If wk_uri_hizuke <> 0 Then
ix2 = ix2 + 1
Sheets(2).Cells(ix2, 1) = wk_uri_hizuke
Sheets(2).Cells(ix2, 2) = wk_uri_kingaku
End If
・処理前
非コントロールブレイク処理と同じ
・処理後
ファイル読み込みが複数回の場合
フローチャート その２
「繰り返し処理の中で、ファイルの読み込み処理を複数回できる」パターンです。
[売上ファイル.売上金額をワーク.売上金額に加算]と[売上ファイルの読み込み]は繰り返し処理の中にある繰り返し処理です。
その１に比べて、シンプルになっています。
プログラム その２
Dim ix1 As Long
Dim ix2 As Long
Dim wk_uri_hizuke, wk_uri_kingaku As Long
'売上ファイルのオープン
ix1 = 0
'売上金額ファイルのオープン
ix2 = 0
Sheets(2).Cells.Clear
'売上ファイルの読み込み
ix1 = ix1 + 1
'売上ファイルが終わるまで繰り返す
Do Until Sheets(1).Cells(ix1, 1) = ""
'初期値設定
wk_uri_hizuke = Sheets(1).Cells(ix1, 1)
wk_uri_kingaku = 0
Do Until Sheets(1).Cells(ix1, 1) <> wk_uri_hizuke
'キーブレイク関係なく、売上ファイルの金額を合計欄に加算するだけ
wk_uri_kingaku = wk_uri_kingaku + Sheets(1).Cells(ix1, 2)
'売上ファイルの読み込み
ix1 = ix1 + 1
Loop
'売上金額ファイルへの出力
ix2 = ix2 + 1
Sheets(2).Cells(ix2, 1) = wk_uri_hizuke
Sheets(2).Cells(ix2, 2) = wk_uri_kingaku
Loop
・処理前
コントロールブレイク処理 その１と同じ
・処理後
コントロールブレイク処理 その１と同じ
コントロールブレイク処理での留意点
初期値セット
必須の処理は以下で、キーブレイク処理の準備作業です。
・ブレイクキーの退避
・集計項目などのリセット、クリア
集計などのループ処理
基本、非コントロールブレイク処理と同じです。
・入力ファイルの項目をワークの集計項目に累積計算処理など
・入力ファイル読み込み
「キーがブレイクしたら・・・」といった判断処理は必要ありません。
ブレイク後の処理
留意点は以下です。
・ワーク上の値を使うこと
ワーク.日付、ワーク.売上金額などの項目だけを使います。
・入力ファイル項目の記述があればミス・バグ・エラー
この処理で入力ファイル項目を使うことはありません。
「ブレイクした時の入力ファイル項目の値も出力する」といった記述があれば必要ですが、そのような要求仕様はまずないでしょう！
コンロトールブレイクを理解していないと「入力ファイルの項目を使ってないけど間違ってないかな？」と不安・疑問に思うところです。
ファイル読み込みが１回の場合の留意点
上記でも書きましたが、「ファイル読み込みが１回の場合」にはメイン処理終了後、データの有無を確認し有ったならば、ブレイク後の処理が必要な場合があります。
今回の例では必要です。
不要なケースは「日付ごとの連番を1,2,3・・・とカウントアップし、出力ファイルにカウントアップした連番を付加して出力する」などです。
キーブレイク時の処理は「連番を付与する項目[ワーク.連番]をリセットする」だけで、非コントロールブレイク処理で「[ワーク.連番]をカウントアップし付加してファイル出力してしまう」からです。
すなわち、キーブレイクで処理する項目を非コントロールブレイク処理で使ってしまい、キーブレイク時には処理された値を使うことがないからです。
最後に
長くなりましたが最後まで目を通して頂き、ありがとうございました！
コントロールブレイクは、基本中の基本であり、かつ、重要な処理ロジックです。
以前は、帳票のプログラムも処理パターンの1つとして考えていました。
例えば
・50行印字したら改ページ
・商品コードが変わったら金額合計を印字して改ページ
ですが今は、帳票もコントロールブレイクの範疇と考えています。
ExcelVBAでサンプルプログラムを紹介しましたが、多くの職場で使われているExcelがあれば動作します。
上記プログラムをコピー＆ペーストして試し、さらに応用し業務効率化の一助になればと思います。
参考記事：
コメント