Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

VBA

PoweShellからVBAを定期的に実行する設定

VBA

猫も杓子もローコード・ノーコードでRPAな昨今、自分もできる範囲でRPAやってみようと思い手始めに毎朝のルーチンワークに手を付けてみました。 例題 仕組構成 VBA処理内容 PowerShell起動方法 PowerShellコード Move-Itemコマンド PowerShellでExcelマクロ…

3つのテーブルをADODBでJOINするSQL記載方法

表の結合。便利ですよね。 Excelではvlookup等の関数を使用して結合することが多いです。 今回は関数ではなくVBAでSQLを使用して表の結合を行いましたが、その際に2つではなく3つの表の結合をする際にちょっと詰まってしまったので備忘録。 例題 エラー症状 …

Excelで使用するSQL文をA5M2で作成・管理する方法

ExcelでADODBを使用するときはVBEでSQL文を書きます。 が、正直めんどくさいんですよね、VBEでSQL文書くの。 そこでA5M2の登場です。 A5M2ではSQL文を入力補完機能で入力補助を受けながら書くことが出来ます。 今回はSQL文をA5M2で作成・管理しつつ、Excelか…

正規表現検索が出来るユーザー定義関数を作った

エクセルの神髄さんのExcelクイズをきっかけに発生したやりとりをヒントに、正規表現検索が出来るユーザー定義関数を作成してみました。 「エクセル頭の体操」実務編コメントから特定形式「(yyyy年mm月)」の年月だけを取り出してください。 pic.twitter.co…

「思い立ったら即SQL」ユーザーフォームからSQL実行

例えば、 「血液型って何種類あるんだよ~」 となった時にピボットテーブルや重複の削除機能を利用するのが普通かと思います。 しかしそんなあなたにSQLでの集計をお勧めしたい! しかも一々コードを書く必要はありません! ショートカットキーでフォームを…

VBAでSQLを使用してシートの統合とクロス集計を行う(行合計・総合計編)

VBAでSQLを使用したクロス集計表を作成する当シリーズ。 www.excellovers.com 前回で終了予定でしたが、前回の最後にちょろっと言っていた箇所が対応できたので予定外のシリーズ追加です。 では前回何を言っていたかというと 列、行方向の合計がない これの…

VBAでSQLを使用してシートの統合とクロス集計を行う(クロス集計編)

SQLでシートの統合とクロス集計を行う今回のシリーズ。 「男性」「女性」2つのシートを統合する 統合したデータを元にクロス集計を行う 完成したデータをシートに貼付け 前々回はシートの統合、前回グループ化を行いました。 www.excellovers.com www.excell…

VBAでSQLを使用してシートの統合とクロス集計を行う(グループ化編)

SQLでシートの統合とクロス集計を行う今回のシリーズ。 「男性」「女性」2つのシートを統合する 統合したデータを元にクロス集計を行う 完成したデータをシートに貼付け 前回はシートの統合を行いました。 www.excellovers.com 今回はクロス集計について書く…

VBAでSQLを使用してシートの統合とクロス集計を行う(シートの統合編)

今までにもVBAからSQLを使用してSelect文で集計は行ってきた。 今回始めて表の統合とクロス集計をSQLで行ったので備忘録。 お題目 コード ADODBの使用準備 UNIONクエリ ALLオプション 実行結果 まとめ お題目 お題はこのようなデータを使用します。 ※データ…

VBAのFormat関数で思わぬ変換に遭遇した

VBA

先日このような記事を公開した www.excellovers.com この記事で作成した関数を組んでいる途中に想定外の事態が発生したので備忘録 思わぬ変換 Format関数 想定外の変換 原因追求 仮説検証 まとめ 思わぬ変換 ちなみにFormat関数はどのようなのか。 Format関…

10進数から他の進数に変換する関数を作った

VBA

問題解決のための「アルゴリズム×数学」で変換方法を知った 問題解決のための「アルゴリズム×数学」が基礎からしっかり身につく本作者:米田 優峻技術評論社Amazon この計算方法を使えば10進数からあらゆる進数に変換できるようだ。 おもしろそうなのでユーザ…

続 Worksheetは最大何枚まで追加できるのか

以前にこのような記事を作成しました。 www.excellovers.com あれから3年。 PC環境を新しくすることが出来ました。 以前のワークシート上限記録を塗り替えてやる! PC環境 マクロでシートを続々追加 実験結果 原因 ワークシートの最大枚数は PC環境 今回新…

Excel VBAのオブジェクト型変数の生成から破棄まで 2.見直し編

VBA

前回はオブジェクト変数についての自分の勘違いしていた内容をまとめてみました。 www.excellovers.com 今回は色々な方にアドバイスを頂いて自分の中で理解できた内容をまとめてみようと思います。 検証用コード オブジェクト型変数の生成 メモリのスタック…

Excel VBAのオブジェクト型変数の生成から破棄まで 1.勘違い編

VBA

以前にこのようなツイをしたところ、諸先輩方から色々なアドバイスを頂きました。 某書の写経です。このコードでCollectionに追加した「Studentクラス」の中身が更新されて、「Studentクラス」を破棄した時にCollectionに追加した「Studentクラス」は影響を…

ユーザー定義関数をセルからは参照できないようにする(VBEからは参照できる)方法

先日、ユーザー定義関数を作成してモヤモヤしたことをつぶやいたところ様々な貴重なご意見を頂いたので備忘録。 ユーザー定義関数を別モジュールからは呼べるけど、セルからは呼べない状態にすることって出来ましたっけ?— Kou Excelが大好きだ! (@LoverExc…

オートフィルタがかかっている列を探す自作アドインに即時検索を追加した

以前にオートフィルタがかかっている列を検索するアドインを作成した。 www.excellovers.com 我ながらとても便利な機能で、使う時は立て続けに使いまくっている。 で、今日は久々に使いまくる機会があったのだがちょっと改良したくなるポイントがあったので…

Excel機能どれだけ知ってるか部員に聞いみました

社内でExcel研修を行おうと検討していて、現在の部員のExcelスキルを把握するために、Excel機能をどれだけ知っているかアンケートを取ってみました。 質問は全部は載せていませんし、色々とボカしますが傾向というか雰囲気を感じ頂ければ。 アンケート対象者…

R1C1形式でも絶対参照が出来るんです

先日、別の人が作ったマクロの調整依頼を受けてコードを眺めていたときに見慣れないコードがあって、どゆこと?と思ったら実は目から鱗だったお話。 R1C1形式 R1C1形式の絶対参照 R1C1形式 R1C1形式はRに行方向、Cに列方向の位置を指定してセルを指定する方…

CopyはWorkhsheetの表示状態によってエラーになる

雛形シートを非表示にしておいて、必要時にコピーして利用するようにすることがあるんだけど、今回エラーに遭遇してしまったので備忘録。 コピーはシートの表示状態に左右される Visibleプロパティ xlSheetVeryHidden時のコピー対応法 雑感 コピーはシートの…

Excel アドインで「有効なアドイン一覧でチェックが入っているのに、アドインが読み込まれない」場合の対応

VBA

先日タイトル通りの出来事に遭遇した。 どうにも自力では原因がつかめず、twitterフォロワーの皆様に助けを求めたところ無事に解決いたしましたので備忘録。 アドインを読み込まない チェックを入れる セキュリティ対応 余談 まとめ アドインを読み込まない …

集計表をダブルクリックすると内訳を表示するようにしてみた(コード編)

以前にこの様な記事を公開した。 www.excellovers.com どうやら実現できてヒャッハーと報告しただけで終わっていたようなので、今更ながらコードの紹介。 完成品 コード 集計表があるシートのイベント 標準モジュール シートイベント 標準モジュール(DoubleC…

数式の参照先ごとにフォント色分けを一括設定

Excel資料の見た目について「外資系金融のExcel作成術」という本で学んだ内容を何点か実践している。 外資系金融のExcel作成術: 表の見せ方&財務モデルの組み方作者:慎泰俊発売日: 2014/04/04メディア: 単行本 そのうちの1つにフォントの色について記載があ…

「参照先のトレースの一覧表示」に機能追加・不具合改修を行った

VBA

先日このようなアドオンを開発してみた。 www.excellovers.com 実戦投入して何点かいじりたい箇所を発見したので、さっそく手を付けてみた。 改修箇所 ユーザーフォームの改修 完成品動作 コード まとめ 改修箇所 機能追加 検索起点セルの追加 参照元検索の…

「参照先のトレース」が不満で、選択したセルを参照しているセルを一覧で表示できるようにした

VBA

「参照先のトレース」という機能をご存知だろうか? この機能を使用するとこのように選択したセルを参照しているセルがどこにあるか、矢印で可視化してくれる便利な機能である。 が、一つ好きじゃない点がある。 この部分である。 「参照先のトレース」はア…

表データを操作する6つの方法(データフィルタ編)

シリーズ前回ではシートのデータを各機能に取り込み、そのまま別シートに貼り付ける動作を体感してみた。 www.excellovers.com 今回は取り込んだデータに絞り込みをかけた上で、別シートに貼り付ける動作を体感してみた。 6つの方法 時間計測関数 改 処理時…

Recordsetに高速でデータを追加する方法

VBA

最近このような記事を公開した。 www.excellovers.com その際にtwitterのフォロワーさんからアドバイスを頂いたので備忘録。 アドバイス コード差異箇所 高速化前 高速化後 AddNew 配列で高速化 Fieldsプロパティ AddNew 配列 処理速度 まとめ 全コード 高速…

SQLite For ExcelでSelect文のデータ件数を取得する方法

SQLite For ExcelにはADODB.RecordsetにあるようなCountプロパティがない。 ところがちょっとした工夫でデータ件数を取得する方法を見かけたので備忘録。 従来の対応 サブクエリ化 なぜレコード件数を取得するかと言うと、SQLiteから取得したデータを配列に…

表データを操作する5つの方法 + 1

前回は5つの方法で表データを操作してみた。 www.excellovers.com 次の抽出に進む前にもう1つ集計方法を追加してみる。 SQLite For Excel コード 処理時間 まとめ SQLite For Excel 当blogではおなじみSQLite For Excelを候補に加えます。 今回の事例のよ…

表データを操作する5つの方法を体感してみた

Excelと言えば表である。 VBAから表のデータをまとめて操作する方法としては配列が挙げられる。 ところが最近空のRecordsetを使用する方法があることを知って勉強しようと思った矢先に、ArrayListなる機能もあると知ってなにがどう違ってどう良いんだ~とな…

マウスカーソルを移動させるだけでセルに色を塗れるようにした

前回はtwitterのフォロワーさんから頂いたアドバイスを組み込んでみました。 www.excellovers.com 今回は別のフォロワーさんから頂いたアドバイスを組み込んでみようと思います。 淡々としたチェック作業ならシングルクリックやキー押下でセル色の設定・解除…