趣味プログラミングblog

前回、エクセルの計算誤差について投稿した。

表示が同じでも値が同じとは限らないということがあるので、
シート比較マクロも値とテキストそれぞれで比較する必要があるかも。

それと、範囲が違った場合も相違点を調べるために
具体的にどのセルがどう違うか知りたいので改良することにした。

レンジ比較、値比較、テキスト比較の３すそれぞれに
メッセージ変数を設定して、相違点がある場合に追記していく。

あんまり大量にメッセージだされてもうざいので、
差分を出力するのは２０箇所までにした。

---

UsedRangeが違うシートを比較する処理は
行列を比較してとか考えていたけど、
範囲　合体でぐぐったらUnionメソッドが使えるらしい。
https://www.sejuku.net/blog/88187

Union(rWS.UsedRange,tWS.UsedRange)だとエラーが出るので、
Union(range(rWS.UsedRange.address),range(tWS.UsedRange.address))に変更。

ん、Rangeでも同じことできるならこれでいいじゃん・・・。
range(rWS.UsedRange.Address, tWS.UsedRange.Address)

正式にはApplication.Union。だからunionメソッド。
https://excel-ubara.com/excelvba1/EXCELVBA403.html

一部のセルが重なる場合のアドレスがどうなるか見てみたら、
指定したままのアドレス表示になるのね。。。

案の定、for each r in union(・・・,・・・)で指定したら
重なった箇所は２回処理されてた。

セルのアドレスを辞書型配列に格納して、キーがなければ処理する仕様にした。
テストで作ったプロシージャは以下の通り。。
Sub unionTest()
Debug.Print Union(range("C122:F126"), range("B120:D123")).Address
Dim r As range, cnt As Long
Dim rDic: Set rDic = CreateObject("Scripting.Dictionary")
For Each r In Union(range("C122:F126"), range("B120:D123"))
If rDic.exists(r.Address) = False Then
rDic.Add r.Address, 1
cnt = cnt + 1
End If
Next r
Debug.Print cnt
End Sub

以下、ソース。 ダウンロード

前回の記事で書いた集計マクロについて、さっそくリファクタリングに着手した。
手始めに、都度シートへアクセスしている個所を２次元配列に格納してみた。

真髄先生、こんな簡単なのね。。。
https://excel-ubara.com/excelvba1/EXCELVBA414.html

Cドライブ内の全ファイルの拡張子集計で比較してみた。
[修正前] 170,036行を3.58秒
[修正後] 170,036行を0.50秒

超はえー！！！


よし！、じゃあ今度はIIJの通話時間の集計だ！

比較結果：1408か所違います。

え～～！！！

まずは事実を整理。
・比較結果としてメッセージボックスに表示される文字列は同じ。
・エクセル上で値を比べても同じ。
・VBAでセル同士を比較すると不一致。

試したこと
・typename(range.value)で型を確認。日時データはdate型が、時刻データはdouble型が返ってくる。
・配列から取り出す際にcDateで変換すると差異なしになる。

調べたこと
・エクセルでは、計算誤差が出ないように自動補正機能があるらしい。
・date型とdouble型で丸め処理に違いがあるらしい。

差が出たからなんか困るかというと
matchやlookupでHITしなくなるくらい。

一致しないのは気持ち悪いから
１つ目のデータをサンプルとして「:」を文字に含む数値は日付型に変換することにした。

また、自動試験マクロも値と文字列を別々に判定するように修正した。

以下、ソース。テキストファイルは こちら。

エクセルの集計でよく使うのが列の構成要素とその内訳を出す作業。

正攻法だと、ピボットテーブルを使ったり、対象列を別シートに書き出して
重複削除などがあるけど、どっちも操作がめんどくさいので、
自作した列集計マクロを使ってる。

それを改良する前に本の影響（前回投稿参照）からリファクタリングをすることにした。

---

今回は、リファクタリング対象の２つのマクロの紹介。

sub ◆1列集計dic()
　選択セルの列を対象に要素の個数をカウントして簡易グラフっぽい表示をする。
当初は、ソートして2項目間を比較していたけど、辞書型配列に変更したら速くなった。

sub ◆2列集計dic()
　選択した２つのセルそれぞれの列を対象に要素の個数をカウントする。
当初は、1列集計dicと同じ処理で要素を出して、sumproduct関数で集計してたので激重。
こちらも辞書型配列に変更したら爆速になった。辞書型配列をポインタとして使うことがポイント。

公開用に不要箇所を消していたら、いつの間にかリファクタリングしちゃってる。。。
なお、「If debugFlag Then Call シート比較自動試験」の部分は前回紹介した内容です。

以下ソース。テキストファイルのソースは こちら。

最近、「プログラマが知るべき９７のこと」という書籍を読んで影響を受けた話。

・わかりやすいコメントを書くより、リファクタリングでわかりやすいソースコードを書くこと
・コードに書けないことのみをコメントすること
・このコードで何がしたいかコメントだけでわかること
・美はシンプルさに宿る
・自動テストで設計品質を確保


個人的によく使ってるマクロに「選択列の項目の内訳を出すマクロ」がある。※１
このマクロを改良するためコピペで別プロシージャを作ったものの、
１００行超えるソースのたった１箇所を変えた別プロシージャを作るのは美しくない。

処理の共通化が必要になってくるので、設計変更に着手する前に
出力されるシートが正しいかを自動的にテストするマクロを作ってみた。


※１　「選択列の項目の内訳を出すマクロ」は、「アパッチのWEBアクセスログの集計④」で登場しました。
https://aki.p-kin.net/Entry/6/


【UI設計】使用方法
最初に考えたのは、２つのシートを選択してシート比較ボタンを押す処理。
（office tanakaで「ActiveWindow.SelectedSheets」の使い方を知った）


何回か試験しているうちに気づいた。いちいちシート２つ選ぶのめんどくさいと。
なので、予め決めておいた名前のシートがあればそれと比較する仕様にした。

【UI設計】結果表示
リファクタリング目的なので、値が同じならOKと割り切り。

２つのシートで値の入った範囲が違えば不一致で終了。
範囲が同じで値の不一致があっても10以上なら数だけ表示。
10以内なら場所と値を表示する。


ちなみに、ファイル出力するマクロだったら
コマンドプロンプトのfcコマンドで比較できる。都度打つのはめんどくさいので、
コマンドをクリップボードに送るバッチファイルで楽をする。


以下ソース。
ソースのテキストファイルは こちらから。

VBScriptを作ってると、デバッグ機能が欲しくなる。
開発環境がなくてメモ帳（otbedit)ベースなので、できることはメッセージボックスで表示。

けど、ループ中にメッセージボックス入れようものなら、
毎回メッセージボックスが出てたまらん。

てな理由からログファイルを出力するVBScriptを作ってみた。

記事を書くときに調べてみたら、有償ソフトでデバッグ環境あんのね。。。
無償トライアル版もあるらしい。今度使ってみよう。
https://www.depthbomb.net/?p=2708#toc6

---

気を取り直して、デバッグログを出力する仕様について

VBAだとdebug.print(Text)の形式だったので、
DebugText(Text)でログファイルにTextを吐き出す仕様にした。

出力先は定数で設定可能に。して、
該当のフォルダやファイルがなければ
call makeDirEX(Path)で自動的に作成。

実行中のスクリプトファイル名と開始日時を取得して
Textと合わせて書き込む。
ファイル名取得はここを参考に。てか１行かよ！
https://bayashita.com/p/entry/show/174


以下、ソース。