Excelが大好きだ!

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


スポンサードリンク

Timevalueは一日未満

時間の計算ってあまりやらないうえに色々とトラップがあるので、余計な時間がかかることがあります。 今日はTimevalue関数で起きたトラップ(自分が知らんだけ)をご紹介。 1日以上増えない 1日未満のみ VALUE関数 1日以上増えない こんな計算をしたんですが、…

ExcelからコピペでObsidianにテーブルとして貼り付けるためのマクロ

Personal Knowlegde ManagementツールのObsidianが楽しくて、ここ数日はExcelをそっちのけで色々といじっています。 obsidian.md 今回はObsidianを使っている中で、マクロを作成することでちょっと楽できるネタが有りましたのでご紹介。 テーブルにならない …

SQLで列多段Pivotクロス集計を行う方法

以前にADODB-SQLでクロス集計を行う方法を色々ご紹介した。 www.excellovers.com 今回新たにクロス集計のネタに遭遇したので、調べた結果を残しておく。 やりたいこと SQLでクロス集計 行に複数項目のPivotのためのSQL 列に複数項目は出来ない 「+」で項目…

最終版とアピールしてファイルの誤編集を防ぐ

先日社内のファイルに今まで見たことのないアラートが表示されているのを見つけました。 今まで知らなかったので備忘録。 勝手に更新しないで 最終版の設定方法 「最終版」の機能 まとめ 勝手に更新しないで そのアラートとはこちらです。 ファイルを開いた…

人生2度目のExcel研修を終えて

昨年に引き続き、社内Excel研修の講師を担当した。 昨年度の時のblogを見返して思うところがあったので、今年度も残してみることにした。 昨年度開催時の振り返り記事。 www.excellovers.com ルーチン化 研修テーマ 資料作成 開催までの心境 研修当日 やって…

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に列方向の位置を指定してセルを指定する方…