お気に入りの集計マクロ
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 シート比較自動試験」の部分は前回紹介した内容です。
以下ソース。テキストファイルのソースは こちら。
PR
リファクタリング用シート比較自動試験マクロ
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コマンドで比較できる。都度打つのはめんどくさいので、
コマンドをクリップボードに送るバッチファイルで楽をする。
以下ソース。
ソースのテキストファイルは こちらから。
フォルダ内のテキストファイルを書き出し
2022年03月31日
前に似たようなものを作ったんだけど、
流用して作ろうとしたら使い勝手が悪かったので改良してみた。
前から気になってたVBAのFIlter関数も使って
シンプルかつ強力に！
機能は、特定フォルダの中のテキストファイルを開いて中身を全部書き出すこと。
なので、フォルダのパスを入力するとテキストの中身を出力する関数として作った。
通常であれば、エクセルを使って不要行の削除とかをするんだけど、
１００万行とかあるので、シートに書き起こす前に
配列のままフィルタリングをする。
昔は、シートありきだったけど成長したなぁ。
いつものように配列のサイズ計算がめんどくさいので、
データの格納段階で無駄にDictionary配列を使う。
重複削除についてもDictionary型のキー格納を使って
エラー無視でぶん回す。
この内容なら、VBScriptに移植もできそうだな。
余談だけど、配列をエクセルファイルに張り付ける際に、
WorksheetFunction.Transpose関数を使うと、
要素数が多すぎると停止するので要注意。
２次元配列に格納しなおして張り付けた方がよさげ。
以下、ソース。
流用して作ろうとしたら使い勝手が悪かったので改良してみた。
前から気になってたVBAのFIlter関数も使って
シンプルかつ強力に！
機能は、特定フォルダの中のテキストファイルを開いて中身を全部書き出すこと。
なので、フォルダのパスを入力するとテキストの中身を出力する関数として作った。
通常であれば、エクセルを使って不要行の削除とかをするんだけど、
１００万行とかあるので、シートに書き起こす前に
配列のままフィルタリングをする。
昔は、シートありきだったけど成長したなぁ。
いつものように配列のサイズ計算がめんどくさいので、
データの格納段階で無駄にDictionary配列を使う。
重複削除についてもDictionary型のキー格納を使って
エラー無視でぶん回す。
この内容なら、VBScriptに移植もできそうだな。
余談だけど、配列をエクセルファイルに張り付ける際に、
WorksheetFunction.Transpose関数を使うと、
要素数が多すぎると停止するので要注意。
２次元配列に格納しなおして張り付けた方がよさげ。
以下、ソース。
マクロの実行ログを保存するツール
2021年10月26日
マクロに限らず、めっちゃ検討して作ったものより
とりあえずで作って改良を重ねたものの方が
便利で使いやすかったりする。
マクロだと、どれが一番使われている？
それを調べるため、ログを出力するマクロを作ってみた。
手順はざっと以下のとおり。
１．ログファイルがなければ作る
２．ログファイルを追記モードで開く
３．テキストを書き込んで閉じる
ほら、めっちゃ簡単！
ログファイルがなければ作るモジュールについては、
フルパス指定で一発で作れるように細工をした。
https://aki.p-kin.net/Entry/96/
フラグでファイルを作ったかわかるようにして、
作った場合は、タイトル行を書き込む仕様にした。
（作っといてあれだけど、集計側マクロとの兼ね合いでいらねーかも）
自作したプロシージャの１行目に以下のログを仕込めば
ログをためることができる。
以下、ソース。
とりあえずで作って改良を重ねたものの方が
便利で使いやすかったりする。
マクロだと、どれが一番使われている？
それを調べるため、ログを出力するマクロを作ってみた。
手順はざっと以下のとおり。
１．ログファイルがなければ作る
２．ログファイルを追記モードで開く
３．テキストを書き込んで閉じる
ほら、めっちゃ簡単！
ログファイルがなければ作るモジュールについては、
フルパス指定で一発で作れるように細工をした。
https://aki.p-kin.net/Entry/96/
フラグでファイルを作ったかわかるようにして、
作った場合は、タイトル行を書き込む仕様にした。
（作っといてあれだけど、集計側マクロとの兼ね合いでいらねーかも）
自作したプロシージャの１行目に以下のログを仕込めば
ログをためることができる。
★WriteLog(プロシージャ名）ちなみに、自分のログは、圧倒的にオートフィルタマクロが１位でした。
以下、ソース。
同じタイプのオートシェイプを選択するマクロ
2021年10月14日
最近夜更かしで寝不足気味。
夜にパソコンやるのよくないと分かってるけど、
ついつい手を伸ばしちゃう。自律ルールを作るかな。
今回もまたオートシェイプの話。
オートシェイプの枠線の太さや塗りつぶしの色の変更を
一括でできると便利だよね。
２つ目以降の変更をF4キーでやってくにしても
数が多いとやっぱりめんどい。
ただ、マクロで実行しちゃうとUnDoが使えないので、
オートシェイプのタイプが同じものを選択するマクロを作ってみた。
汎用性を考えて、複数のシェイプを選択した場合は
いずれかのシェイプタイプと同じなら選択する。
処理の流れは以下のステップ
１．シェイプを選択してなかったらエラーにする。
２．選択中の全シェイプに対してループさせる。
３．シェイプタイプを調べ辞書に格納する。
４．シート内の全シェイプに対してループさせる。
５．辞書に登録済みのシェイプタイプなら選択する。
６．結果をメッセージボックスに出力
今回は簡単なので、以下ソース。
夜にパソコンやるのよくないと分かってるけど、
ついつい手を伸ばしちゃう。自律ルールを作るかな。
今回もまたオートシェイプの話。
オートシェイプの枠線の太さや塗りつぶしの色の変更を
一括でできると便利だよね。
２つ目以降の変更をF4キーでやってくにしても
数が多いとやっぱりめんどい。
ただ、マクロで実行しちゃうとUnDoが使えないので、
オートシェイプのタイプが同じものを選択するマクロを作ってみた。
汎用性を考えて、複数のシェイプを選択した場合は
いずれかのシェイプタイプと同じなら選択する。
処理の流れは以下のステップ
１．シェイプを選択してなかったらエラーにする。
２．選択中の全シェイプに対してループさせる。
３．シェイプタイプを調べ辞書に格納する。
４．シート内の全シェイプに対してループさせる。
５．辞書に登録済みのシェイプタイプなら選択する。
６．結果をメッセージボックスに出力
今回は簡単なので、以下ソース。