Excelが大好きだ!

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


スポンサードリンク

COUNTIF関数は数値に見える文字列は数値で条件一致判定を行う

先日仕事でデータの重複チェックを行おうと思ってCOUNTIF関数でやってみようと思ったら、思いがけない結果にハマってしまったので備忘録。 数値で検索? 解決法 シングルクォーテーションを追加 別の関数 COUNTIF関数を躾ける まとめ 数値で検索? 下記の事…

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進数からあらゆる進数に変換できるようだ。 おもしろそうなのでユーザ…

Excelの各機能を初めて使う時に表示されるメッセージ

先日PCを新調しました。 とういうことでExcelもまっさらな状態で再インストール。 そうしたら色々とメッセージが表示されるんですよね。 なんとなく気になったのでインストール後表示されたメッセージのスクショ集。 スクリーンショット集 まとめ スクリーン…

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

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

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

VBA

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

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

VBA

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

人生初のExcel研修を終えて

先日、人生初となるExcel研修の講師役を努めたので、その時の経緯や感情を忘れる前に備忘録 研修のきっかけ 数年前から一度はExcel研修で講師を努めてみたいとは考えていました。 しかしなかなか機会もなく自分から動くことも有りませんでした。 しかし会社…

キーボードだけで選択範囲を移動させる方法

このようなツイを見かけた。 [Ctrl]+[Shift]+[→]等で対象の行or列選択している状態で、その選択範囲を移動するショートカットってないですか??#助けてExcel#ショートカット教えてください pic.twitter.com/vjBwK84zSH— 初心者@PowerQuery勉強中 (@Excel5…

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

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

知らぬ間に増殖しエラーになる名前定義

ぎゃー! 何だかよく分からないうちに増殖をしている「名前」。 そして知らないうちにエラーとなっている「名前」。 「名前」がどこで増殖しエラーとなっているのか検証しようと思います。 「名前」とは 名前定義 名前の使用方法 名前の増殖 シート自体のコ…

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

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

配列定数に変数を差し込む方法

以前に下記のExcelクイズに解答したことがある。 高校以降では連立方程式を解くのに行列を使います。これを応用しているのがKouさんの回答です。https://t.co/hccsHw5b8o定数配列の一部に変数を差し込むというテクニックも有用そうです。— 遊牧家族/yuuboku …

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

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

A5M2でExcelに接続する方法

高機能かつ軽量なSQLクライアント、A5:SQL Mk-2(以下A5M2) 。 便利ですよね。 a5m2.mmatsubara.com 様々データベースに接続することが出来るA5M2ですが、Excelに接続することも出来ます。 Excelに接続することでA5M2の便利な機能を利用しつつSQL文の作成・…

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

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

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

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

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

VBA

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

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

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

置換しようとしたら全て消えた

先日、置換したんですよ。置換。 じゃぁ全てが消し飛んだんですよ。全てが。 ビックリしますよね。 ワイルドカード *(アスタリスク) ?(疑問符) ~(チルダ) 置換で全てを消さない対策 上記の例では「*」(アスタリスク)を空白に置換しようとしました。 しかし「…

ピボットテーブルで日付がグループ化出来ない時の対処法

ピボットテーブル便利ですね。 でも時々どうしたら良いんだろう?という事態に遭遇することもしばしば。 今回はそんな中から1つ、日付でグループ化出来ない場合の対処法をご紹介します。 日付グループ化出来ない 原因は書式設定 解決方法 1を掛ける 区切り…