エクセル・VBAの計算誤差
2022年05月31日
前回の記事で書いた集計マクロについて、さっそくリファクタリングに着手した。
手始めに、都度シートへアクセスしている個所を２次元配列に格納してみた。
真髄先生、こんな簡単なのね。。。
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しなくなるくらい。
一致しないのは気持ち悪いから
１つ目のデータをサンプルとして「:」を文字に含む数値は日付型に変換することにした。
また、自動試験マクロも値と文字列を別々に判定するように修正した。
以下、ソース。テキストファイルは こちら。
手始めに、都度シートへアクセスしている個所を２次元配列に格納してみた。
真髄先生、こんな簡単なのね。。。
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しなくなるくらい。
一致しないのは気持ち悪いから
１つ目のデータをサンプルとして「:」を文字に含む数値は日付型に変換することにした。
また、自動試験マクロも値と文字列を別々に判定するように修正した。
以下、ソース。テキストファイルは こちら。
PR
お気に入りの集計マクロ
2022年05月27日
エクセルの集計でよく使うのが列の構成要素とその内訳を出す作業。
正攻法だと、ピボットテーブルを使ったり、対象列を別シートに書き出して
重複削除などがあるけど、どっちも操作がめんどくさいので、
自作した列集計マクロを使ってる。
それを改良する前に本の影響（前回投稿参照）からリファクタリングをすることにした。
今回は、リファクタリング対象の２つのマクロの紹介。
sub ◆1列集計dic()
選択セルの列を対象に要素の個数をカウントして簡易グラフっぽい表示をする。
当初は、ソートして2項目間を比較していたけど、辞書型配列に変更したら速くなった。
sub ◆2列集計dic()
選択した２つのセルそれぞれの列を対象に要素の個数をカウントする。
当初は、1列集計dicと同じ処理で要素を出して、sumproduct関数で集計してたので激重。
こちらも辞書型配列に変更したら爆速になった。辞書型配列をポインタとして使うことがポイント。
公開用に不要箇所を消していたら、いつの間にかリファクタリングしちゃってる。。。
なお、「If debugFlag Then Call シート比較自動試験」の部分は前回紹介した内容です。
以下ソース。テキストファイルのソースは こちら。
正攻法だと、ピボットテーブルを使ったり、対象列を別シートに書き出して
重複削除などがあるけど、どっちも操作がめんどくさいので、
自作した列集計マクロを使ってる。
それを改良する前に本の影響（前回投稿参照）からリファクタリングをすることにした。
今回は、リファクタリング対象の２つのマクロの紹介。
sub ◆1列集計dic()
選択セルの列を対象に要素の個数をカウントして簡易グラフっぽい表示をする。
当初は、ソートして2項目間を比較していたけど、辞書型配列に変更したら速くなった。
sub ◆2列集計dic()
選択した２つのセルそれぞれの列を対象に要素の個数をカウントする。
当初は、1列集計dicと同じ処理で要素を出して、sumproduct関数で集計してたので激重。
こちらも辞書型配列に変更したら爆速になった。辞書型配列をポインタとして使うことがポイント。
公開用に不要箇所を消していたら、いつの間にかリファクタリングしちゃってる。。。
なお、「If debugFlag Then Call シート比較自動試験」の部分は前回紹介した内容です。
以下ソース。テキストファイルのソースは こちら。
リファクタリング用シート比較自動試験マクロ
2022年05月27日
最近、「プログラマが知るべき９７のこと」という書籍を読んで影響を受けた話。
・わかりやすいコメントを書くより、リファクタリングでわかりやすいソースコードを書くこと
・コードに書けないことのみをコメントすること
・このコードで何がしたいかコメントだけでわかること
・美はシンプルさに宿る
・自動テストで設計品質を確保
個人的によく使ってるマクロに「選択列の項目の内訳を出すマクロ」がある。※１
このマクロを改良するためコピペで別プロシージャを作ったものの、
１００行超えるソースのたった１箇所を変えた別プロシージャを作るのは美しくない。
処理の共通化が必要になってくるので、設計変更に着手する前に
出力されるシートが正しいかを自動的にテストするマクロを作ってみた。
※１ 「選択列の項目の内訳を出すマクロ」は、「アパッチのWEBアクセスログの集計④」で登場しました。
https://aki.p-kin.net/Entry/6/
【UI設計】使用方法
最初に考えたのは、２つのシートを選択してシート比較ボタンを押す処理。
（office tanakaで「ActiveWindow.SelectedSheets」の使い方を知った）
何回か試験しているうちに気づいた。いちいちシート２つ選ぶのめんどくさいと。
なので、予め決めておいた名前のシートがあればそれと比較する仕様にした。
【UI設計】結果表示
リファクタリング目的なので、値が同じならOKと割り切り。
２つのシートで値の入った範囲が違えば不一致で終了。
範囲が同じで値の不一致があっても10以上なら数だけ表示。
10以内なら場所と値を表示する。
ちなみに、ファイル出力するマクロだったら
コマンドプロンプトのfcコマンドで比較できる。都度打つのはめんどくさいので、
コマンドをクリップボードに送るバッチファイルで楽をする。
以下ソース。
ソースのテキストファイルは こちらから。
・わかりやすいコメントを書くより、リファクタリングでわかりやすいソースコードを書くこと
・コードに書けないことのみをコメントすること
・このコードで何がしたいかコメントだけでわかること
・美はシンプルさに宿る
・自動テストで設計品質を確保
個人的によく使ってるマクロに「選択列の項目の内訳を出すマクロ」がある。※１
このマクロを改良するためコピペで別プロシージャを作ったものの、
１００行超えるソースのたった１箇所を変えた別プロシージャを作るのは美しくない。
処理の共通化が必要になってくるので、設計変更に着手する前に
出力されるシートが正しいかを自動的にテストするマクロを作ってみた。
※１ 「選択列の項目の内訳を出すマクロ」は、「アパッチのWEBアクセスログの集計④」で登場しました。
https://aki.p-kin.net/Entry/6/
【UI設計】使用方法
最初に考えたのは、２つのシートを選択してシート比較ボタンを押す処理。
（office tanakaで「ActiveWindow.SelectedSheets」の使い方を知った）
何回か試験しているうちに気づいた。いちいちシート２つ選ぶのめんどくさいと。
なので、予め決めておいた名前のシートがあればそれと比較する仕様にした。
【UI設計】結果表示
リファクタリング目的なので、値が同じならOKと割り切り。
２つのシートで値の入った範囲が違えば不一致で終了。
範囲が同じで値の不一致があっても10以上なら数だけ表示。
10以内なら場所と値を表示する。
ちなみに、ファイル出力するマクロだったら
コマンドプロンプトのfcコマンドで比較できる。都度打つのはめんどくさいので、
コマンドをクリップボードに送るバッチファイルで楽をする。
以下ソース。
ソースのテキストファイルは こちらから。
デバッグログを出力するVBScript
2022年05月22日
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
以下、ソース。
開発環境がなくてメモ帳（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
以下、ソース。
空フォルダを消すクリーナバッチを改良
2022年04月30日
昨日からバッチファイルの勉強してて、書籍を１つ読破。
前にネットからまるパクリした空のフォルダをまとめて削除するバッチの
ソースを見返してみたけど理解できず。
本とネットを駆使してようやく理解できた。
●空フォルダ削除
for /f %%A in ('dir /ad /b') do rd %%A
※ フォルダ名にスペースが入ってると消してくれない
%%Aがループで使用する変数。
('dir /ad /b')がディレクトリ内の全フォルダを取得するコマンド。
今回、フォルダを対象とするので、
dirコマンドで一覧を取得。/ad：フォルダのみを対象に /b：フォルダ名だけ取得
for文の中で文字列として受け取るので /f
取得したフォルダ名が%%Aに入ってるので、rdコマンドで削除。
実はオプションなしでrdコマンドを打つと空フォルダしか消さない。
空じゃないフォルダも消すならrd /s /q とサブフォルダを対象にして
確認メッセージをスキップするオプションを指定する必要がある。
（win10環境だと/sでいけた。書籍は古いのでdeltreeで消すって書いてあった。）
※で書いた弱点克服のために試行錯誤。
"usebackq"オプションをつけてみたけどうまくいかず。rd "%%A"もだめ。
そして気づいた。そもそも全然違うことに。
改めて調べてみたらここがわかりやすい。
https://www.javadrive.jp/command/bat/index9.html#section1
カレントディレクトリにあるディレクトリの分だけ繰り返す
FOR /D %%変数 IN (セット) DO コマンド
for /d %%A in (*) do rm %%A
バッチファイルのあるディレクトリの空フォルダを消す正しいコマンド。
スペースの入ったフォルダも対象にするには""でくくればOK。やっと弱点克服。
●最終的なコマンド
for /d %%A in (*) do rm "%%A"
どのファイルを消したかとか見たければ、最後にpauseを付ける。
するとコマンドプロンプトウィンドウが表示されたままになる。
以下、ソースと結果（%0でバッチファイル名を取得できる）
こんな簡単に結果を見れるならVBScriptよりも使いやすいよなぁ・・・。
前にネットからまるパクリした空のフォルダをまとめて削除するバッチの
ソースを見返してみたけど理解できず。
本とネットを駆使してようやく理解できた。
●空フォルダ削除
for /f %%A in ('dir /ad /b') do rd %%A
※ フォルダ名にスペースが入ってると消してくれない
%%Aがループで使用する変数。
('dir /ad /b')がディレクトリ内の全フォルダを取得するコマンド。
今回、フォルダを対象とするので、
dirコマンドで一覧を取得。/ad：フォルダのみを対象に /b：フォルダ名だけ取得
for文の中で文字列として受け取るので /f
取得したフォルダ名が%%Aに入ってるので、rdコマンドで削除。
実はオプションなしでrdコマンドを打つと空フォルダしか消さない。
空じゃないフォルダも消すならrd /s /q とサブフォルダを対象にして
確認メッセージをスキップするオプションを指定する必要がある。
（win10環境だと/sでいけた。書籍は古いのでdeltreeで消すって書いてあった。）
※で書いた弱点克服のために試行錯誤。
"usebackq"オプションをつけてみたけどうまくいかず。rd "%%A"もだめ。
そして気づいた。そもそも全然違うことに。
改めて調べてみたらここがわかりやすい。
https://www.javadrive.jp/command/bat/index9.html#section1
カレントディレクトリにあるディレクトリの分だけ繰り返す
FOR /D %%変数 IN (セット) DO コマンド
for /d %%A in (*) do rm %%A
バッチファイルのあるディレクトリの空フォルダを消す正しいコマンド。
スペースの入ったフォルダも対象にするには""でくくればOK。やっと弱点克服。
●最終的なコマンド
for /d %%A in (*) do rm "%%A"
どのファイルを消したかとか見たければ、最後にpauseを付ける。
するとコマンドプロンプトウィンドウが表示されたままになる。
以下、ソースと結果（%0でバッチファイル名を取得できる）
こんな簡単に結果を見れるならVBScriptよりも使いやすいよなぁ・・・。