趣味プログラミングblog

エクセルで列をソートする機能はない。
しょうがないので、自作で作ることにした。

作る前に同じようなものを作った人がいないかグーグル検索。


列ソートできんじゃん。。。

過去の記事「列集計マクロの改良」に書いた通り、
マクロをがんばって改良したけど、そのマクロがない環境で同じ集計をやることになってとっさに別の方法を発見。

集計に必要な情報を書いた列を追加すればいいじゃん。。。

---

UI設計は、列集計マクロと同様にmsgboxとInputboxを使って
数値の除去の有無と取得する文字数を設定。

ちなみに数値を除去する処理は、当初ワイルドカードを使っていたけど
配列の計算なので圧倒的にforループの方が速かった。

ワイルドカード
https://vbabeginner.net/extract-non-numeric-characters-from-strings/


セルを配列に格納して、計算してからセルに戻す処理。
便利なので使うようになったけど、セルが１つしかないと
変数が配列じゃなくなって型エラーが出る。。。（将来対応だな）

書式については、タイトル行の次のセルの書式に合わせた。


マクロの中であれこれでてしまうと、
どのように出したかが追えなくなるので、
集計用の列を追加する方法は可読性の面でも有利だな。

以下、ソース。ソーステキストは こちら。

３つ前の記事で、列集計マクロについて書いた。
今回はそのマクロの改良について。

---

きっかけとなった改良内容は次の２つ。
１．セルの文字から数字部分を除去すること。
２．左から指定した文字数だけ集計すること。

セルの内容を辞書型配列に格納するときに
上記の１，２を実装した関数を通せばいいだけ。

新しいプロシージャとしてコピペでソースをかけば動くわけだけど、
ソースが二重管理になるのでDRY原則(Don't Repeat Yourself)に反する。

そのまま改良を始めると、ソースがごちゃごちゃになりそうだったので、
リファクタリングを行った。

元々のモジュール名とその挙動は変更せず、
機能追加したモジュールを別途作成し、メインの処理を共通化した。

当初、動作オプション１つずつに別々の引数にしていたけど、
スマートじゃないので、文字列で受け渡すことにした。

調べた限り、VBAでは配列を引数として渡すことができないようなので、
数値型の４引数をカンマ区切りの文字列として渡して
受け側でSplit関数で配列に変換。要素数を指定して取り出すという処理にした。

2次元集計については、出力結果に対して
合計計算とカラースケールの追加、行列の並べ替えをしていたので、
その処理も合わせて追加した。リファクタリングツール様様でした。


面白いことに、作っているときはこれが最善手と思っているけど、
１日経てば全く違う解決策がぱっと浮かぶもんなんだな。

そしてその解決策もそもそも必要なかったという結論になったんだけど
それはまた別の話。

というわけで、以下ソース。 ソーステキストはこちら。

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

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

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

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

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

---

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しなくなるくらい。

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

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

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