【Excel VBA】サンプルコードまとめ

Excel VBAの作成時に参考にするコード例や、エラーの発生と対応例をこちらにまとめていきます。 コードは一つの目的に対して、様々な処理内容や記述方法もあるので、基本パターンとしているものを中心に整理しています。 内容： ファイル操作関連 一覧の取得 複数ブックの繰り返し処理 複数シートの繰り返し処理 ファイルやフォルダーの作成、コピー テーブル、クエリ、ピボットテーブルでの処理例 分岐や繰り返しの処理例 数回に1回の定期的な処理 処理速度や操作性の向上 エラー対応例 Word VBA その他の事例 参考： データの加工・抽出、管理 ファイル操作関連 一覧の取得 ・指定フォルダ内の全てのサブ…

【Excel VBA】処理の進捗状況は表示して他の画面更新は停止

Excel VBAは、処理の進捗状況を表示しながら他の画面更新を停止することが可能です。 公式な情報は探せませんでしたが、ScreenUpdatingプロパティを使用して画面更新を停止した場合でも、ステータス バーの情報は更新される仕様のようです。 以前から仕様に変更はなさそうですが、動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2310）です。 なお、ステータス バーは画面左下の位置で、テキスト情報が表示されます。 ステータスバー VBAサンプル マクロの速度を向上させるために画面更新は停止し、処理が適切に実行されているこ…

【Excel VBA】開始時の実行確認と終了時のメッセージを表示する方法

Excel VBAでマクロを実行するとき、処理の開始と終了をメッセージで確認できると、ミスの防止や操作性の向上に役立ちます。 例えば、 マクロの実行ボタンを誤ってクリックし、意図しないデータ変更が発生してしまう。 処理がすぐに終了するマクロや、処理前後の違いが把握しにくいマクロでは、処理が終了したのかどうかの確認が手間になる。 マクロの終了時、次のアクションや確認事項を明示することで手順を誘導し、操作性を向上させたい。 などの対応には、画面にメッセージを表示することが有効です。 以下は、開始時の実行確認と終了時のメッセージを表示するシンプルなサンプルです。 VBAサンプル 開始時は、処理を実行…

【Power Automate】「Microsoft Edgeを制御することができませんでした」のエラー発生と対応例

Power Automate Desktopで、WEBページのデータ処理を自動化するため処理を実行しようとするとエラーが発生。エラーが発生しない時もあり、エラー内容と対応例の備忘録。 内容： エラーの内容 対応内容 エラーの内容 Power Automate Desktop（PAD）で、Microsoft Edgeを起動し、ログイン画面にテキスト入力し、ログインするだけのシンプルなフローで次のエラーが発生。 ただし、エラーが発生しない時もある。 工ラ-の詳細Microsoft Edge を制御することができませんでした。Edge was launched but could not get t…

【OneNote】タスクチェックリストを全体で検索、一覧化に「ノート シール」

OneNoteは、タスクの項目をチェックリストにしたり、重要な情報をタグ付けした情報整理に便利な「ノート シール」という機能があります。 このシールを活用することで、ノートの分類とは別にカテゴリ分けできるので必要な情報を効率的に見つけることが可能になります。 特に、「ノート シールを検索」で以下が個人的には便利です。 ノートを横断してチェックリスト検索する（チェックリストで完了していないものだけを抽出も可） タグ付けした情報で検索 検索結果を一覧化できる「概要ページを作成する」の機能 ノート シールに関する操作方法は簡単ですが、以下に参考情報を記載します。 「ノート シール」の付与 suppo…

Webページの保存はOneNote Webclipperが便利

インターネットで情報収集時、気になったウェブページを保存する方法として、OneNoteも便利です。以前にOneNote Webclipperを使った時、ページ全体をスクリーンショットでしか保存できないと勘違いしてEvernoteを利用していました。 OneNoteも保存時に「記事」を選択することで、Web ページを編集可能なテキストとしてノートブックに保存できるので、個人的な情報整理はOneNoteだけ利用で効率的に可能です。 support.microsoft.com OneNote Webclipperで、ページ全体や特定の領域をスクリーンショットとして画像保存や、ページ体のテキストや特定…

業務フローの作成にBPMNの活用（わかりやすい表記法）

わかりやすく、活用しやすい業務フローを作成するため、BPMN（ビジネスプロセスモデリング表記法）について情報収集した備忘録です。 内容： BPMNとは BPMNの具体例 BPMNの作成方法 参考資料 ISO19510 BPMNの背景や検証に関する参考資料 JIS X0121 BPMNとは BPMNは「Business Process Modeling and Notation」の略で、「ビジネスプロセスモデリング表記法」と言われるもので、業務フローのために使用する記号や図などが決まっている。 国際標準（ISO19510）にもなっていて「わかりやすい業務フローの表記方法」と理解。 業務を可視化し…

【Excel】PDFに変換時、変換結果の表示有無を設定する方法

Excelで、ファイルをPDFに変換時、自動的に変換結果を表示、非表示にする設定があります。変換の都度、処理結果が表示されると確認作業が効率的になります。一方で、定型作業で表示が煩わしい場合もあり、以下の手順で表示の有無を設定できます。 手順 Excelのメニュータブから「ファイル」をクリックし「エクスポート」を選択、「PDF/XPSドキュメントの作成」→「PDF/XPSの作成」をクリックすると、次の「PDFまたはXPS形式で発行」ダイアログボックスが表示されます。 「PDFまたはXPS形式で発行」ダイアログボックス 「発行後にファイルを開く」のチェックボックスをONにすると、PDF変換時に変…

【Excel】図形間の全体比率を維持したまま拡大・縮小する方法

ExcelやPowerPointなどで、複数の図形をレイアウトした後、全体的に拡大・縮小したいとき、「グループ化」を活用することで図形間の全体比率を維持したままサイズ変更ができます。 内容： 拡大・縮小の手順 参考（レイアウトが崩れた事例） 拡大・縮小の手順 次のような複数の図形でレイアウトされた図を事例に説明します。 複数のテキストボックスと線で作成した図 それぞれの各テキストボックスは、次のとおり単独のオブジェクトです。 複数のテキストボックスと線で作成した図（選択状態） 複数の図形を選択後に右クリックからグループ化を選択する。 複数の図形のグループ化 次のように、一つのオブジェクトとなる…

【Excel】業務フローを効率的に作成する操作テクニック

Excelで業務フローを効率的に作成するための操作テクニックのまとめ。 内容： 図形の配置を整列 図形の既定を設定 図形のコピー コネクタのカギ線のズレを修正 フロー図の線の重なりを簡易的に見やすくする方法 作成後、多くの図形の書式を変更 Excelの標準機能で書式貼り付け VBAで同じタイプの図形を選択 図形の配置を整列 図形のレイアウト調整を容易にするため、作成したい図形の大きさに応じてセルの幅を設定しておきます。次のように方眼紙等にしておくと印刷物を想定した位置調整も可能です。 「Altキー」や「Shiftキー」を押しながら図形をドラッグするとセル位置での調整が効率的です。 shikum…

【Excel】同じ図形を続けて作成するときのポイント

Excelで、同じ図形（オートシェイプなど）を連続して作成する際に、知っておきたい機能の備忘録。個人的には、CTRLキー、SHIFTキー、ALTキーの活用が特に有用だと感じています。 内容： 描画モードのロック Ctrlキー、Shiftキー、Altキーの活用 Ctrl（図形のコピー） Ctrl＋Shift（図形のコピー＋縦方向か横方向の位置固定） Ctrl＋Alt（図形のコピー＋セルの枠線にあわせて配置） Ctrl＋Shift＋Alt（図形のコピー＋縦方向か横方向の位置固定で枠線にあわせて配置） CTRL＋D（近くにコピー） 描画モードのロック Excelの「描画モード」をロックすることで、同…

【Power Query】ヘルパークエリを理解するポイント

PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」は、慣れるまでその役割が不明な機能です。 ヘルパークエリを理解するポイントについて記事をまとめました。 内容： 各クエリの依存関係を理解 「パラメーター1」と「サンプル ファイルの変換」は削除できる ヘルパークエリをすべて削除して同じ結果を得るクエリに修正 「パラメーター1」と「サンプル ファイルの変換」の理解 その他、参考知識 各クエリの依存関係を理解 まず、ヘルパークエリの各クエリがどのような関係にあるのか依存関係を把握します。 shikumika.org 「パラメーター1」と「サンプル ファイルの変換」は削…

【Power Query】クエリから関数を作成すると、関数にクエリの編集が自動反映される

Power Queryは、クエリからカスタム関数を作成することができます。クエリから関数を作成すると、関数にクエリの編集が自動反映されるというメリットがあります。 どのような動作をするのか調べた備忘録です。 内容： 事例の前提 確認内容 「ファイルの変換」の更新を停止する 「サンプル ファイルの変換」クエリでカスタム関数の作成 参考 事例の前提 ヘルパークエリの理解も兼ねて、事例の前提とするクエリは今回も同じで、以下です。 shikumika.org 確認内容 「ファイルの変換」の更新を停止する ヘルパークエリの関数である「ファイルの変換」は「サンプル ファイルの変換」と連動 しています（詳細…

【Power Query】ヘルパークエリの「ファイルの変換」は「サンプル ファイルの変換」と連動

前回、【Power Query】ヘルパークエリの「パラメーター1」と「サンプル ファイルの変換」を削除をしても、アウトプットであるクエリに影響がないことを記載しました。 今回は、「パラメーター1」と「サンプル ファイルの変換」の役割を調べた備忘録です。 内容： 事例の前提 結果 説明 連動を示す表示 連動の動作 参考 事例の前提 事例の前提とするヘルパークエリは今回も同じで、以下です。 shikumika.org 結果 先に結果を説明すると、次のとおり、「③ファイルの変換」は「④サンプル ファイルの変換」と連動しています。 ただし、上記の「【Power Query】クエリの依存関係を把握（ヘル…

【Power Query】ヘルパークエリの「パラメーター1」と「サンプル ファイルの変換」を削除

PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」の動作を理解するために、「パラメーター1」と「サンプル ファイルの変換」を削除した場合の動作を調べた備忘録です。 内容： 事例の前提 「パラメーター1」と「サンプル ファイルの変換」の削除事例 「サンプル ファイルの変換」の削除 「パラメーター1」の削除 削除後のクエリ 「パラメーター1」と「サンプル ファイルの変換」の役割 参考（ヘルパークエリをすべて削除） 事例の前提 事例の前提とするヘルパークエリは以下です。 shikumika.org このヘルパークエリでは、⑤売上サンプルファイルが、Excelのシートに…

【Power Query】ヘルパークエリを削除して同じ結果を得るクエリに修正

Power Queryで、ファイルを結合する時に自動作成される「ヘルパークエリ」について、各クエリの役割や動作を理解するための備忘録。 ヘルパークエリを削除して同じ結果を得るクエリに修正してみました。 内容： ヘルパークエリを削除する前提知識 事例の題材（ヘルパークエリ） クエリの修正手順 「③ファイルの変換」への依存をなくす 「カスタム関数の呼び出し1」ステップの詳細 「カスタム関数の呼び出し1」ステップの修正 「②サンプル ファイルの変換」への依存をなくす 「展開されたテーブル列1」ステップの詳細 「展開されたテーブル列1」にかわるステップの挿入 「展開されたテーブル列1」の削除 ヘルパー…

【Power Query】もう一つのクエリで参照されているため削除できませんの解決

Power Queryで、余分なクエリを削除しようとすると、「クエリ"〇〇"はもう一つのクエリで参照されているため削除できません:”△△”。」というエラーメッセージが出ることがあります。 これは、削除しようとしているクエリが他のクエリから参照されているために発生します。 慣れていないと戸惑うことがあるので、解決方法の備忘録です。 エラーの発生例 例えば、ファイルを結合する時に自動作成される「ヘルパークエリ」で「パラメータ1」を右クリックし、「削除」をクリックしたとします。 「クエリの削除」の操作例 すると、次のようなメッセージが表示され、クエリを削除することができません。 クエリ “パラメータ…

【Power Query】クエリの依存関係を把握（ヘルパークエリでの事例）

Power Queryで、複数のクエリが連携している場合、各クエリがどのクエリに依存しているかを視覚的に把握したい時があります。 そのための機能として、Power Queryには「クエリの依存関係」という機能があります。 ファイルを結合するクエリで作成された「ヘルパークエリ」を事例に、クエリの依存関係を把握する方法の備忘録です。 なお、ヘルパークエリは、Power Queryで複数のクエリが自動作成されるために、各役割や関連性の理解が難しい機能です。以下はヘルパークエリの理解にも役立ちます。 内容： 事例の題材（ヘルパークエリ） 「クエリの依存関係」の表示 「クエリの依存関係」の説明（事例のヘ…

データの集計・可視化の操作事例まとめ（主にExcel）

広く利用されているExcel（Power Query含む）などのツールを使って、「データの集計・可視化」を効率的に行う事例をこちらにまとめていきます。 なお、★マークのついた操作は、個人的に便利と感じている操作です。 内容： データの集計 PowerQueryでのカウント集計 ピボットテーブルで比較結果を見やすいレイアウトの集計 アンケートデータの集計 データの可視化 年度別の順位推移表のようなマトリクス表の作成 Excelピボットテーブルでのテクニック 参考： データの加工・抽出、管理 PowerQueryやVBAの記事 データの集計 PowerQueryでのカウント集計 ・ExcelのCO…

【Power Query】エディターの [クエリ] ウィンドウでカテゴリに整理

PowerQueryエディターの [クエリ] ウィンドウでは、グループ機能を使用してクエリをカテゴリ別に整理することが可能です。 これは、クエリをフォルダのような構成で管理できますが、動作内容や影響が不明だったので調べた備忘録です。 内容： グループを使用してクエリをカテゴリに整理 クエリをカテゴリに整理の動作内容や影響 概要 調べた内容の詳細 グループを他のグループに移動するループへの移動 グループを解除 グループを作成 「その他のクエリ」の補足 グループを使用してクエリをカテゴリに整理 PowerQueryのメニューからファイルを結合するクエリを作成すると、次のようなフォルダが存在するヘル…

【Power Query】ヘルパークエリがある場合のデータソース変更方法

PowerQueryの利用場面で、データソースを変更したいことは比較的多く、【Power Query】データソースの変更方法が基本的な操作です。 他にもデータソースを変更する方法はありますが、ファイルを結合するクエリでヘルパークエリが存在する場合、データソースの変更には次の注意点があります。 ヘルパークエリが存在する場合は「サンプル ファイル」にもソースの情報が含まれる。 修正は、「データ ソースの設定」から基本的に実施する。（もしくは「データ ソース設定」） 上記2の理由は、次の2つ方法でデータソースの修正範囲が異なるためです。理解すると当たり前の挙動ですが、慣れていないと注意が必要です。 …

【Excel】図形や線の書式設定の既定を変更する方法

Excelで図形や線の書式を統一したいとき、作成の段階から同じ書式（色や太さなど）にしておきたいことがあります。簡単に、図形や線の書式設定の既定を変更できるので、同じ書式の図や線を作成するときは設定しておくと便利です。 基本の設定 図形や線について「既定にしたい書式」の設定後、右クリックで表示されるメニューで既定の書式を設定します。 なお、メニューの表示は次のとおりです。 図形の場合：「既定の図形に設定」ただし、テキストボックスの場合は、「既定のテキストボックスに設定」 線の場合：「既定の線に設定」 図形（テキストボックス）の設定例 補足説明と注意点など 既定の書式は、対象のExcelファイル…

データの加工・抽出、管理の操作事例まとめ（主にExcel）

データの加工・抽出、管理の操作はビジネスで重要なスキルですが、様々なツールやテクニックがあります。広く利用されているExcel（Power Query含む）などの便利なツールを使って、データ操作を効率的に行う事例をこちらにまとめていきます。 なお、★マークのついた操作は、個人的に便利と感じている操作です。 ファイルの結合・抽出 複数シートの表（リスト）を一つのシートに結合する方法 複数シートの値（個票）を一つのシートに結合する方法 指定フォルダ内のExcelファイルを順番に処理 その他データの加工 データを繰り返し取得 表記ゆれの修正など 個別データの処理テクニック データの管理 参考： ファ…

【Excel】図形の書式を繰り返し貼り付ける方法（Ctrl+Shift+Vの活用）

Excelで図形の書式を繰り返し貼り付けする方法は、Ctrl+Shift+Vも活用すると便利です。ある図形に適用した書式を別の図形に適用したいとき、「書式のコピー／貼り付け」を利用するケースは多いと思います。 そして、繰り返し書式を貼り付ける場合、次のような方法があります。使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 マウス操作でする方法「書式のコピー／貼り付け」をダブルクリックして、「貼り付けの繰り返し状態」して、繰り返し図形を選択していく。 ショートカットキーを利用する方法Ctrl+Shift+C（書式のコ…

【Excel】「コネクタ：カギ線」のズレをまっすぐにする方法

Excelの「コネクタ：カギ線」は、業務フロー図の作成などに便利です。しかし、直線となるように配置した図形であっても、まっすぐにならず、微妙なズレがある線となります（下図の左側）。 「コネクタ：カギ線」のズレと解消イメージ コネクタの微妙なズレの解消方法 直接コネクタに変更せず、「コネクタ：カギ線」のままで、ズレを解消してまっすぐに修正するには、「図形の書式設定」のサイズで高さを”0”に設定します（上図の右側）。 なお、「図形の書式設定」の設定イメージは次のとおりです。 「図形の書式設定」の設定イメージ 注意点 上記の手順で、「コネクタ：カギ線」のズレをまっすぐにできますが、図のレイアウト等が…

【Excel VBA】選択しているオートシェイプの図形と同じタイプを一括で選択（複数タイプ可）

前回、VBAで選択しているオブジェクトと同じタイプを一括で選択する方法を記載しました。この方法は、 一括選択したいオブジェクトのタイプが一つのみで、複数タイプに対応していない オートシェイプの図形の詳細な分類に対応していない という仕様だったので、上記課題に対応したVBAの備忘録。 事例の前提 VBAで選択しているオブジェクトと同じタイプを一括で選択する方法ですが、修正前の詳細な前提、背景は以下のとおりです。 shikumika.org VBAサンプル Sub 複数選択して同じタイプの図形選択() 'AutoShapeTypeで判定' Dim 図形 As Shape Dim 選択した全ての図形…

Excelで値の貼り付け（書式なし）がCtrl+Shift+Vで可能になった

Excelで値のみ貼り付け（書式なし貼り付け）のショートカットキーはなく、Ctrl+Shift+Vがこれまでは利用できませんでした。 そのため、テキストの内容だけをプレーンテキストとして貼り付けることが手間でしたが、Microsoft365のExcelでバージョン 2308 (ビルド 16731.20170)以降で利用可能になっていました。 2023年8月28日に公開されたバージョンです。 learn.microsoft.com 値のみ貼り付けは、Excelでテキストを扱う際によく利用します。 上記バージョン以降は、［Ctrl］＋［Shift］＋［V］キーで値のみ貼り付けが可能です。 なお、上…

【Excel VBA】選択しているオブジェクトと同じタイプを一括で選択

Excelで図形を操作するときに、同じタイプのオブジェクトを一括で選択したい場合があります。例えば、シート内にあるすべての「コネクタ: カギ線」だけを選択したり、「テキスト ボックス」だけを選択して色を変更したいときなどです。 しかし、Excelにはそのような機能はありません。 シート内の全てのオブジェクトを選択することは可能ですが、オブジェクトのタイプを絞り込んで一括で選択することはできません。 そのため、一つずつ選択を繰り返すしか方法がなく、オブジェクトが多い場合は手間です。 そこで、VBAを使って、現在選択しているオブジェクトと同じタイプのオブジェクトを全て選択するコードの備忘録です。 …

【Excel】フロー図の線の重なりを簡易的に見やすくする方法

Excelで業務フロー図の作成時など、線が交差する箇所を見やすくしたい時があります。しかし、Excelには「飛び越し点」（飛び越し線）の表示機能がありません。 線と半円をつなげて「飛び越し点」を作成する方法もありますが、フロー図の修正や線の重なりが多い場合は少し手間です。 そこで、線が交差する箇所を簡易的に見やすくする方法として、線の背景（輪郭）を白色にする方法の備忘録。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 飛び越し点について 飛び越し点は、下図の右側のように線の交差箇所を一方の線がもう一方の線…

【Excel】XLOOKUPのバイナリ検索とVLOOKUPの近似一致の速度比較

ExcelのXLOOKUP関数のバイナリ検索と、VLOOKUP関数の近似一致による検索で、IF文を用いて「完全一致のみを抽出」をした場合の速度を比較した事例です。 「XLOOKUP関数やVLOOKUP関数で検索の型を完全一致」で実施した場合の比較は、【Excel】VLOOKUP関数とXLOOKUP関数の速度比較の事例 のとおり、複数列以上の抽出時に処理時間に差があります。 以下は、バイナリ検索や近似一致を用いて「1列のみ抽出」をした時の処理時間です。 一つの事例ですが、結果は次のとおりでした。 XLOOKUP関数のバイナリ検索と、VLOOKUP関数の近似一致による検索で、処理時間は同程度だった…

【Power Query】現在のExcelファイルのシートを直接取得する関数はない

Power Queryで、現在のExcelファイルのシートの内容を取得したい時があります。 しかし、テーブル等を介さず、直接取得する関数はありませんでした。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 Excel ブックの内容を返す関数には Excel.CurrentWorkbook（現在の Excel ブックの内容を返す。シート内容の取得不可） Excel.Workbook（指定したExcel ブックの内容を返す。シート内容の取得可能） があります。 まず、現在の Excel ブックの内容を返すExc…

【Power Query】ブック内にある複数シートの表を一つのシートに結合する方法

Power Queryで、ブック内にある複数シートの表を一つのシートに結合する方法の備忘録。Power Queryで作成する「クエリ」と「結合したいシート」も同じブック内にしたい場合と、クエリ自体は別ブックでもよい場合で若干手順が異なる。 内容： アウトプットイメージ 実施手順 「クエリ」と「結合したいシート」も同じブック内にしたい場合 結合したいデータ範囲のクエリを作成する。 Power Queryエディターで「クエリの追加」をクリック 結合したいテーブル（クエリ名）を「追加するテーブル」に設定 クエリ自体は別ブックでもよい場合 「データ」タブから結合したいシートがあるブックを開く シートで…

【Power Query】List.FirstNやTable.FirstNのcountOrConditionの理解

Power QueryのList.FirstNとTable.FirstN、LastNなどの関数は、リストやテーブルから最初（LastNの場合は末尾）のN個の要素を取得するために使用します。 これらの関数の第二引数countOrConditionは、数値または条件を指定できます。 数値を指定した場合は、単純に最初（LastNの場合は末尾）のN個の要素が返されます。条件を指定した場合、「検索開始時から検索条件が満たされる要素が連続している間」が対象となります。検索条件を満たさない要素が見つかったら、それ以降の要素は無視されます。 例えば、List.FirstNの説明ページは以下です。 learn.…

【Excel】VLOOKUP関数とXLOOKUP関数の速度比較の事例

最近のVLOOKUP関数は、かなり高速に改善されている印象です。過去、データを昇順にして二分探索する方法と通常の完全一致での検索では実感の速度に大きな違いがあったように思いますが、実感するほどの差が少なくなったと感じます。 一方、XLOOKUP関数は検索列と結果列の選択がしやすいなど、VLOOKUP関数と比較して便利な関数ですが、大量データの場合、VLOOKUP関数より速度が少し遅い印象です。 今回、5万件のデータ処理で時間が遅く感じたので、VLOOKUP関数とXLOOKUP関数の処理時間を調べてみました。 データ処理の内容やPC環境等もあるので、一つの事例の結果としてですが、XLOOKUP関…

【Excel】「データ」タブの重複削除とPowerQueryで重複削除の違い

Excelで重複しているデータを削除するとき、「データ」タブの重複削除とPower Queryでの重複削除では、残すデータの仕様に違いがあります。 具体的には、Excelの「データ」タブの重複削除は、リスト内で最初に出現する値が保持されるとなっています。 support.microsoft.com 一方、Power Queryの「重複削除」では、一連の重複の最初のインスタンスが選択されるという保証はないとなっており、リスト内で最初に出現する値が保持されるとは限らないようです。 learn.microsoft.com この回避策として、 (Table.Buffer を使用して) データをバッファ…

【Power Query】VLOOKUPの近似一致と同様処理：区間分け（ビニング）の方法

PowerQueryで、ExcelのVLOOKUP関数の「検索の型をTRUE」（近似一致）にする方法の備忘録。Excelでは、「年齢」を「年代」に変換など、区間分け（ビニング）にVLOOKUP関数は便利です。 PowerQueryでも同様の処理を実現したい場合があり、その手順は次のとおりです。 内容： 事例の前提 アウトプットイメージ 実施手順 カスタム関数を使用する方法 「空のクエリ」の作成 カスタム関数の作成 カスタム関数の名称変更 カスタム関数の呼び出し カスタム関数の設定 カスタム列で実施する方法 カスタム列の追加 事例の前提 ExcelのVLOOKUP関数で実施する次の事例をPowe…

【Excel】「年齢」を「年代」に変換など、区間分け（ビニング）の方法

データ分析時、数値データを適当な範囲（○以上△未満）で区切り、カテゴリーに変換して全体的な傾向を見たい時があります。例えば、「年齢」を「年代」に変換して年代ごとの度数分布表を作成するケースなどです。 このような場合は、ExcelのVLOOKUP関数、またはFLOOR関数を使用します。 VLOOKUP関数を使用する方法は、区間の範囲（○以上△未満）が等間隔でない場合にも可能な方法です。FLOOR関数は区間の範囲が等間隔の場合に可能な方法です。 アウトプットイメージ 実施方法 VLOOKUP関数を使用した方法 区間分けの基準となる表の作成 VLOOKUP関数の使用 FLOOR関数を使用した方法（区…

【Power Query】Excelバージョンによる取込可能なデータソースの違い

ExcelのPowerQueryは、バージョンによって取込可能なデータソースが異なります。例えば、PDFをExcelにインポートする際にはPowerQueryが便利ですが、Office 2016やOffice 2019では利用できず、Microsoft 365のExcelで利用可能です。 JSONの取り込みは、Office 2019やMicrosoft 365のExcelで可能などの違いがあります。 ExcelのバージョンによるPowerQueryデータソースの違いについて以下に一覧があったので備忘録。 support.microsoft.com なお、PDFをExcelにインポートの便利な機…

【Excel】複数回答の選択肢がセル内でカンマ区切りのデータ集計

Excelで、セル内にカンマで区切られたデータ（アンケートの複数回答など）を集計したいとき、Microsoft365のExcelなどにあるTEXTSPLIT関数が便利です。 TEXTSPLIT関数が使える場合、カンマなどの区切文字でセル内の文字列を分割できます。 具体的なアウトプットイメージは次のとおりです。 アウトプットイメージ 次のようにC1:C10に、カンマ区切のデータが入力されています。 TEXTSPLIT関数で文字列を分割しています。 TEXTSPLIT関数で文字列を分割イメージ 上記事例で数式は、次のとおり。 =TEXTSPLIT(分割対象のセル,",") 事例の補足 一つのセルに…

【Excel】複数回答の選択肢が列ごとにあるアンケートデータの集計

Excelでアンケートデータを分析する際など、ピボットテーブル機能を用いて分析するために、複数の列にあるデータを行方向のデータに変換したい時があります。マトリクス表をリストに変換する操作、つまり列のピボット解除です。 前提として、最近のExcelに標準で含まれているPower Queryには、「列のピボット解除」という機能があります。繰り返しの業務や大量のデータなどはPower Queryがお勧めです。 shikumika.org ただし、一時的であったり、関数等でしたい場面もあります。 もし、Microsoft365のExcelなどでVSTACK関数、HSTACK関数が使える場合、数式だけで…

【Power Query】列のピボット解除で、マトリクス表をリストに変換

Power Query（パワークエリ）には、マトリクス表をリストに変換できる「列のピボット解除」という機能があります。 「列のピボット解除」には、「その他の列のピボット解除」と「選択した列のみをピボット解除」を含めて3種類あり、それぞれの処理の違いについての備忘録です。 内容： アウトプットイメージ 実施方法 列のピボット解除 その他の列のピボット解除 選択した列のみピボット解除 それぞれの方法の違い アウトプットイメージ マトリクス表をリストに変換 実施方法 Power Query（パワークエリ）の基本操作が不明な場合は、【Power Queryの初歩】簡単な事例説明（使いながら覚える最初の…

【Excel】外部参照で「値の更新」が出てファイル選択が求められた事例

Excelの外部参照は、他のExcelファイルのデータを参照するための便利な機能です。しかし、OneDrive上にあるファイルを参照する場合、予期せぬ挙動でエラーとなったので備忘録。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 発生した事象 具体的には、次のような数式で外部参照（リンク）されたセルがあり、OneDriveに同期していた。リンク先の値も取得できていた。 例） ='C:\Users\〇〇\OneDrive\test\[サンプル.xlsx]日報'!$C$2 この状態で、リンクされたファイル（この事例では、サンプル.x…

【Excel VBA】Setでワークブックを変数に割り当て（オートメーションエラーの発生事例）

ExcelのVBAを使って作業を自動化する際、ワークブックを変数に割り当てることはよくあります。このとき、変数が不要になったらSetステートメントでNothingを実施しておかないと、次のような"オートメーションエラー"が発生することがあります。 実行時エラー '-2147221080(800401a8)': オートメーション エラーです。 オートメーションエラーは、重いデータ処理や、メモリ不足等が原因にもなるようなので、エラーの一例として示します。 次のように、モジュールレベルの変数でワークブックを割り当て、最後にSetステートメントでNothingを実施していないケースでは注意が必要でした…

DXに関する資料まとめ（リンク集）

DX（デジタルトランスフォーメーション）の取り組みについての情報収集で、公的資料等を中心にリンク先をまとめてみました。 内容: DXの定義 デジタルガバナンス・コード 中堅・中小企業等向け「デジタルガバナンス・コード」実践の手引きについて 「『デジタルガバナンス・コード』実践の手引き」（本体） AI導入ガイドブック DX推進指標について DX認定制度について その他、関連情報 DXの定義 経済産業省では「デジタルガバナンス・コード2.0（旧 DX推進ガイドライン）」で、次のように定義しています。 企業がビジネス環境の激しい変化に対応し、データとデジタル技術を活用して、顧客や社会のニーズを基に、…

【Excel】複数シートの表を一つのシートに結合する方法（VSTACK関数の活用）

Microsoft365のExcelには、複数シートの表を一つのシートに結合する際に便利なVSTACK関数があります。 リスト形式のデータ（列ごとに同じ種類のデータが入力された表）を一つの表にまとめるような関数です。もちろん、複数の表が一つのシートにある場合の結合でも活用できます。 なお、リスト形式ではなく単票形式の表を一覧にしたい場合は、【Excel】複数シートのデータを抽出して一つのシートにまとめる方法（INDIRECT関数の活用） をご覧ください。 内容： 基本 具体的な事例 補足説明 数式で指定する表範囲について 他の方法について 基本 VSTACK関数は、次の構文となる。 =VSTA…

【Excel VBA】セルのアクティブ化でエラーが発生する原因と対応

ExcelのVBAで、セルのアクティブ化や選択をしようとすると、セルが存在するにも関わらず「実行時エラー '1004': Range クラスの Select メソッドが失敗しました」「Range クラスのActivate メソッドが失敗しました」というエラーが発生する時があります。 しかも、元々動作していたが、コードの他の部分を修正した後に発生ということもあります。 エラーが発生する原因と対応 セルが存在しているにも関わらずエラーとなる場合、次の状態になってる可能性があります。 Activate メソッドの場合 「セルは現在の選択範囲内にある必要がある」が満たされていない。learn.micr…

【Excel VBA】処理が止まらない時に強制停止する方法（Breakキーがない場合）

VBAの実行時、ループ処理が終わらないなど、処理が止まらず困った時に備えて、強制停止の方法を知っておくことは重要です。 基本の方法 基本の方法は、「Ctrl + BREAK」 キーを選択です。 もしくはEscキーの選択や、「Ctrl + Pause」キーなどで、VBAの実行を強制的に停止することができると言われています。 しかし、キーボードには「Break」キーがない場合などもあるので、その場合メーカー、キーボードに応じたショートカットキーを押す必要がある。 なお、私の場合（DELLのノートパソコン）では、「Fn + Ctrl + B」キー だった。 その他、後述のスクリーンキーボードを使用す…

【Excel】数式と計算結果を並べて表示する方法

Excelの計算結果をチェックする目的で、数式と計算結果を並べて表示や印刷したい場合、数式を文字列として表示するFORMULATEXT関数が便利です。 Excelの数式メニューにある「数式の表示」や、ショートカットキー「Ctrl+Shift+`」で、数式表示の有無を切り替えは可能だが、シート内の全てが対象で、数式か計算結果の一方だけの表示となる。 一部のセルだけ、計算結果ではなく数式をそのまま表示したいケース、数式と計算を並べて同時に表示したいケースでFORMULATEXT関数が有効です。 support.microsoft.com 具体的なアウトプットイメージは次のとおりです。 FORMUL…

【Excel】特定の文字列の間の値を抽出する方法（TEXTBEFORE/ TEXTAFTER 関数）

Microsoft365のExcelで、特定の文字列の間の値を抽出する方法は、TEXTBEFORE/ TEXTAFTER 関数が便利です。この関数は、従来のLEFT関数やFIND関数などの組み合わせよりも、シンプルに文字列を抽出できます。 ただし、この関数が使えないバージョンのExcelでは「#NAME?」のエラーや、「_xlfn.」の数式で再計算されないといった問題が発生するので、関係者のバージョンが異なる場合には注意が必要です。 TEXTBEFORE/ TEXTAFTER 関数が使えるMicrosoft365のExcelの場合、 =TEXTAFTER(TEXTBEFORE(元の文字列,開始…

【Excel】 セルを効率的に選択・削除する方法（数式のみ、数式以外、数値のみ）

Excelのシートから、 ・数式のみを選択したい。 ・数式以外のデータを削除したい。 ・数値のみを削除したい。 といった選択や削除をしたい時は、「検索と選択」のメニューにある機能を使用すると対象をまとめて選択できます。その後、必要ならそのままDeleteで削除できます。 Excelのバージョン Microsoft® Excel® for Microsoft 365 MSOでは次のとおりです。 まず、「検索と選択」のメニューで、次のように、数式はそのまま「数式」、数式以外は「定数」、数値は「条件を選択してジャンプ」をクリックします。 「検索と選択」のメニュー 数式や定数（数式以外）は以上で選択可…

【Power Automate】処理速度は編集画面での実行が遅く、フロー一覧での実行が早い

Power Automate Desktopの実行は、編集画面とフロー一覧で処理速度が違う。ループ処理など処理回数が多い場合、編集画面から実行すると、実行遅延等の時間含めて処理時間が長くなる。編集画面での実行で処理速度が遅いと感じたら、フローの一覧表示から実行するだけで、かなり改善する。

【Power Automate】2次元配列のDataTableでFor eachの実施例

Power Automateで、2次元配列のDataTableをFor eachループによる処理サンプル。 For each アクションを使用してデータテーブルをループする場合、反復処理の対象となる変数はデータ行となります。そのため、個々の値を抽出する場合は、データ行を再度For each アクションを使用してループするなどが必要。なお、使用のツールは「Power Automate Desktop」です。 内容： データ型の詳細 サンプルの前提 サンプル（DataTableでFor eachの実施） フローのテキスト情報 事例の補足説明 データ型の詳細 learn.microsoft.com …

【Power Automate】ユーザーが選択したフォルダ内のファイルを順番に処理する事例

Power Automate Desktopで、ユーザーが指定フォルダを選択し、当該フォルダ内のExcelファイルを順番に処理する事例。 複数ブックの値を抽出する次のサンプル（フォルダは固定で指定）をベースにフォルダーの選択ダイアログを表示させるフローに修正事例。

【Power Automate】Excelの表から値抽出（2次元配列のDataTableから取得）

Power Automate Desktopの「Excel ワークシートから読み取る」のステップで、値をDateTable（データテーブル）で取得した場合など、データテーブルから値抽出の基本。 基本 例えば、次のようにExcelDataという変数に、データテーブル型で5行5列の2次元配列が格納されているとする。 Datatable（データテーブル）のイメージ このとき2行3列目（縦方向の番号1、列はColumn3）に該当する値を抽出する場合は、 %ExcelData[1][2]% と変数に角括弧を追加して行列に相当する数値を指定する。 数値は、行列の開始番号が0であることに注意する。 その他デ…

【Excel】セルの参照元ファイルを効率的に開く、ジャンプする方法

Excelのシートで、他のブックの値を参照しているセルで、元データのファイルを効率的に開く方法の備忘録。例えば、セルに ='C:\aaa\[test.xlsx]Sheet1'!$A$1 という数式が入力されていて、参照元の値が表示されているとします。そのとき、元データのファイルを確認したい場合に便利な方法です。 方法は簡単で、セルを選択した後に Ctrl＋[ キーを押すだけです。すると、すばやく元データにジャンプします（ファイルが開いていないときは、ファイルも起動します）。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 これは、…

【Excel VBA】指定フォルダ内の全てのサブフォルダを一覧表示するコード例

VBAで、指定フォルダ内にある全てのサブフォルダを一覧表示するコード例。何かの処理目的で「フォルダ情報だけ一覧にしたい」「全てのフォルダを対象に処理をしたい」といったケースで、再帰プロシージャの基本サンプルです。 事例の前提 Excelファイル内に「フォルダ一覧」というシートがあり、シートのデータを全て削除後、リストを作成する。 VBAサンプル Option Explicit Dim fs As Object 'FileSystemObjectのオブジェクト変数を宣言' Dim 対象フォルダ As String Dim サブフォルダ As Object Dim 出力シート As Workshe…

【Excel VBA】他のブックのセルの値を取得するユーザー定義関数の検討結果

ExcelのINDIRECT関数は、同じブック内にある複数のシートの値を抽出する場合には有効ですが、他のブックの値を抽出する場合はブックを事前に開いておく必要があります。なるので、Excelの数式として利用できるユーザー定義関数で対応できないかを検討した備忘録。

【Excel VBA】PDF変換時にビューアーが起動しないようにする方法

Excel VBAで、ExcelシートをPDFファイルに変換するとき、デフォルトではPDFファイルが保存された後にビューアーが起動してファイルが表示されます。便利な機能ですが、複数のPDFファイルに連続で変換する場合などは、ビューアーの起動は余分です。

【Excel VBA】シートを複数選択する方法（For 文などのループで）

Excel VBAで、シートのインデックス番号で、複数選択する方法。For 文などのループで、選択したシートを一つずつ処理ではなく、一旦該当するシートを全て選択して処理したいときがあります。このようなケースでは、Worksheet.Select メソッドのパラメーターでReplaceをFalse に指定すると、現在の選択範囲を拡張するように選択できます。

【Excel VBA】指定フォルダ内のExcelファイルを順番に処理（FileSystemObjectの利用）

ExcelのVBAで、ユーザーが指定フォルダを選択し、フォルダ内のExcelファイルを順番に処理するサンプル。前回のDir関数を利用した方法と同様の処理をFileSystemObjectを利用して実現するサンプルです。 前回のDir関数の方法は、サブフォルダ内の処理ができないなどのデメリットもあるので、より機能が多いFileSystemObject の方法を基本パターンとした方がよい印象。 事例の前提 ユーザーが指定フォルダを選択し、フォルダ内のExcelファイルを順番に処理するサンプル。もう少し詳しい前提を確認したい場合は、次の前回記事を確認ください。 shikumika.org VBAサン…

【Excel VBA】指定フォルダ内のExcelファイルを順番に処理（Dir関数の利用）

ExcelのVBAで、ユーザーが指定フォルダを選択し、フォルダ内のExcelファイルを順番に処理するサンプル。Dir関数を利用した方法で、後日紹介するFileSystemObject の方法を基本とした方がよい印象 Excel操作で処理が完結するような業務は、VBAでの処理が便利なので基本的な事例の備忘録。 事例の前提 事例の前提は次のとおり。 ユーザーが処理対象のフォルダ、出力先のフォルダを指定する。 指定した処理対象のフォルダ内にあるExcelファイルを順番に起動する。 指定した処理を実行し、出力先フォルダに出力する。 なお、事例の処理内容は、操作イメージ用でCSVに変換するだけ。この処理…

【Power Automate】ワークシートに含まれる使用可能なすべての値（データ取得事例と注意点）

Power Automate Desktopには「Excelワークシートから読み取る」というアクションがあり、セルの値を読み取り、転記などの自動化が可能です。このアクションの設定値に「ワークシートに含まれる使用可能なすべての値」を取得というのがあり、どのようにデータ取得されるのかの事例と、セル結合時には注意が必要な点の備忘録。

【Power Automate】Excel ワークシートから最初の空の列や行を取得する（最小値は2）

Power Automateを使用して「Excelワークシートから最初の空の列や行を取得」アクションを実行する際、新しいシートでも最初の空の行や列は、2行目や2列目となるようなので備忘録。 1行目や1列目とならないので注意が必要ということ。 なお、使用のツールは「Power Automate Desktop」（version 2.34.187.23206）です。 具体的な状況 Power Automate DesktopでExcelで新規ブックの作成。 新規ブックの作成 このシートを対象に、次のように「Excelワークシートから最初の空の列や行を取得」アクションを追加。 「Excelワークシー…

【Power Automate】Excelのセルの値を取得（複数ブックの値抽出サンプル）

Power Automate のサンプルフローとして、特定フォルダにあるExcelファイルからセルの値を取得し、一覧にする事例。使用のツールは、Windows 10 および Windows 11 ユーザーであれば、無償で利用することができる「Microsoft Automate Desktop」です。

WEBページの共有時、リンク先ページの特定の場所にスクロール、強調表示する方法

他者にWEBページの情報を共有する時、WEBページ内の特定文章の場所も示して共有したい時があります（リンク先URL、根拠となる文章部分など）。Microsoft Edge、Google Chromeなどに以前からある機能ですが、次のようにすることで、リンク先ページの特定の場所までスクロールするリンクで、文章も強調表示して共有可能です。

【Excel】 複数ブックのデータ（単票）を一覧表にまとめる方法の比較

Excelで複数のブックのデータ（単票）を一覧表にまとめる方法にはいくつかの選択肢があります。Excel関数、VBA、Power Queryなどを活用したこれまでの備忘録を中心にデータの状況と対応例をまとめてみました。 個人的な結論は、VBAを使った方法が汎用性も高く、効率的な印象。 次いでPowerQueryで「列のピボット解除」による方法。 あとは、レイアウトやデータ量、繰り返し頻度などで方法は変わる。 内容： Excel関数だけで処理したい INDIRECT関数を使用する方法 VBAを使用したくない場合 Power Queryの列のピボット解除でセルの値を抽出する方法 Power Que…

【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出

前回はPower Query（パワークエリ）のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法を紹介しました。今回は列のピボット解除などを活用して、同様にセルの値を抽出する方法の備忘録。 個人的な結論として、カスタム関数の利用と比較すると、手順の難易度が低いという印象。 なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 内容： 前提 参考にした情報 手順 データ取得のフォルダを指定し、「データの結合と変換」のクリック テーブルの展開前の状態まで、ステップを削除 「カスタム列」で行位置を追加 一旦余分…

【Power Query】カスタム関数の作成事例：単票形式のExcelシートからセルの値を抽出

Power Query（パワークエリ）のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法の備忘録。 カスタム関数が利用できると、データ抽出等の作業効率化も可能。単票形式で紙印刷のレイアウトはPower Queryでの抽出も手間となることが多いのでカスタム関数を使った手順の整理。 内容： カスタム関数で実現すること、前提 参考にした情報 手順 「空のクエリ」の作成 数式の入力 関数に変換されるので、クエリの名前もわかりやすい名前に変更しておく カスタム関数の動作確認で、空のクエリを再度追加 値の抽出結果 補足説明 その他の活用例など カスタム関数で実現すること、…

【Power Query】動的な列名に対応するため、列を数値で指定する方法

Power Queryで、元データの列名が変わるなど動的な列名に対応するため、列番号を示す数字で列を指定したい場合があります。Table.ColumnNames関数を利用すると、列名ではなく数値で列指定が可能で、方法の備忘録。 これにより、クエリ更新時に列名が変わる場合でも、柔軟に対応できるクエリ設定が可能です。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」。 列を数値で指定したい背景 Power Queryの数式に列名が含まれる場合、列名変更があると再設定が必要。 元データの列名に表記ゆれ等がある動的な列名に対応するため、列番号を…

【Excel VBA】複数ブックのデータ（単票）をセル位置変更があっても一覧表にまとめた事例

ExcelのVBAで、複数ブックのデータ（単票）をセル位置変更があっても一覧表にする事例。 前回の「【Excel VBA】複数ブックのデータ（単票）を一覧表にまとめる事例」は、同じフォーマットのExcelファイルが前提でした。 しかし、様式変更による項目追加などでフォーマットが変更され、抽出したいセル位置も変更となることはあります。 このような場合でも一覧表にまとめた事例として、「メモ」の情報を活用したVBA備忘録。もちろん、データ収集の段階で集計しやすい回収方法に変更できることが理想的ですが、解決手段の選択肢として紹介。 なお、今回の方法は、フォーマットを発行する側で、記入者に「メモ」を削除…

【Excel】シートの保護で特定セルの編集は許可、メモの編集は不可にする

Excelで入力用の様式を作成し、記入依頼、回収する場合は、レイアウト等を変更されないようにシートを保護することが有効です。保護する内容もいくつか設定可能です。例えば、特定セルだけ編集できるようにし、「メモ」は編集・削除できないようにできます。 以下は、シート保護の設定例として、特定セルの編集は許可、「メモ」の編集・削除は不可にする設定で手順の備忘録。 設定後のイメージ シート保護のイメージ図 手順 次の手順を参考に、次項よりポイントを絞って説明。 support.microsoft.com 編集を許可したいセルを選択して、右クリック、セルの書式設定 編集を許可したいセルを選択します。同じ設定…

【Excel VBA】複数ブックのデータ（単票）を一覧表にまとめる事例

ExcelのVBAで、複数ブックのデータ（単票）を一覧表にまとめる事例です。Power Queryでの集計が難しい「神エクセル」と言われるような、紙印刷のレイアウトを前提にしたExcelシートをVBAで一覧表にした備忘録。 同じフォーマットで入力されたExcelファイルを大量に回収し、集計するときに活用できるサンプルです。 事例の前提 次のように、同じレイアウト（単票形式）のExcelファイルに入力されているデータを一覧表にまとめる。 事例の前提 VBAの汎用性を持たせるため、抽出するセルの場所は、Excelの標準機能にある「メモ」を活用し、次のようにする。 「設定シート」で一覧表にまとめた時…

【Excel VBA】複数シートのデータを抽出して一つのシートにまとめる事例

ExcelのVBAで、複数シートのデータを抽出して一つのシートにまとめる事例の備忘録。前回、INDIRECT関数を活用して「複数シートのデータを抽出して一つにまとめる方法」を記載しました。この方法は、シートの枚数が多い場合や、繰り返しシートの更新がある場合などに手間なので、VBAを活用した方法です。 サンプルの前提 前回の事例で示したように、次のとおりExcelファイル内に、同じフォーマットで入力されたデータが複数枚あり、特定のセルの情報を抽出した一覧表を作成する。シートは新規に作成する。 同一ファイル内に同じフォーマットのファイルが複数ある VBAの汎用性を持たせるため、VBAを実行する前に…

【Excel】複数シートのデータを抽出して一つのシートにまとめる方法（INDIRECT関数の活用）

Excelで、同じフォーマットで入力されたデータが複数のシートにあるデータの集計作業では、INDIRECT関数の活用が有効な手段の一つです。 INDIRECT関数とは、セルに記述された「テキスト情報」が参照するセルの値を返す便利な関数です。 具体的な手順は以下の通りです。 集計前のデータ状態 次のようにExcelファイル内に、同じフォーマットで入力されたデータが複数枚あり、特定のセルの情報を抽出した一覧表を作成したい。 同一ファイル内に同じフォーマットのファイルが複数あり 複数シートのデータの抽出結果 抽出結果を表示するシートを追加し、INDIRECT関数を利用して、次のように記載する。 =I…

【Excel VBA】複数ブックのシートを一つのブックにまとめるサンプル

Excelのデータ集計業務は、Power Queryを使うことで効率化が図れる場面が多くあります。 しかし、Power Queryでは扱いにくいデータ操作もあり、自動化を考慮するとVBAの活用が効率的となることもあります。 特に、いわゆる「神エクセル」と言われるような、紙印刷のレイアウトを前提にしたExcelシートの集計は、Power Queryでも集計が難しい。 データ収集の段階で集計しやすい回収方法に変更できることが理想ですが、「紙印刷のレイアウトのExcelシートに記入依頼、回収、転記・集計する」という場面も、まだ多い状況。このような状況なので、VBAを使用した効率化の備忘録。 今回は、…

【Excel】XLOOKUP関数は、テーブル内で使用できない

XLOOKUP関数は、VLOOKUP関数と比較して便利な関数です。しかし、テーブル内で使用すると「#スピル!」となってエラー。XLOOKUP関数など、複数のセルに結果が戻る数式は使用できないようなので備忘録。 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」。 テーブル内でXLOOKUP関数を使用した状況は次のとおりで、「#スピル!」となってエラー。 テーブル内でXLOOKUP関数を使用するとエラー発生 確認すると、スピルした配列数式はテーブルで使用できないとのこと。 support.microsoft.com なお、スピルとは、以下で「数式…

【Power Queryの初歩】簡単な事例説明（起こりそうなエラーと対応の事例）

Power Query（パワークエリ）の初心者の方が知っておくとよいエラーと対応の事例。簡単な事例ですが、通常の編集作業で起こりそうなエラーと対応方法の備忘録。Power Queryでのエラー対応の基本として紹介。 次の事例は、クエリの結果を変更したいケースで、編集画面でステップを挿入すると、後の工程でエラーが発生するというものです。通常の編集作業でよくあることで、エラーメッセージを確認すれば、対応可能だと思います。 なお、すぐに慣れると思いますが、エラー対応の基本は次のとおり。 エラーメッセージと、発生しているステップを確認する エラーが発生しているステップと、その前のステップのプレビューを…

【Power Query】既定ではクエリ名を変更すると出力のテーブル名は変わる

Power Queryを初めて操作してから慣れるまでは、設定変更の影響でエラーが発生しないかが気になるところです。そこで、クエリ名の変更でテーブル名とシート名への影響を調べた備忘録です。 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」。 Power Queryエディターで、クエリ名の変更による変化は、次のとおりだった。 既定ではクエリ名を変更すると出力のテーブル名が変更される。 出力されたテーブル名を変更後、クエリ名を変更した場合はテーブル名は変更されない。 読み込みしているExcelのシート名は変わらない。 つまり、後続の処理でテーブル名…

【Power Queryの初歩】簡単な事例説明（使いながら覚える最初のポイント）

Power Query（パーワークエリ）は最近のExcelの標準機能として利用できます。Excelの機能ということで、使いながら覚えようとしたとき、最初に戸惑うことがいくつかあります。以下、簡単な事例で、初めてPower Queryを操作するときに知っておくと良いと思ったことの備忘録。

【Power Query】データソースの管理方法（ファイルの状況と対応例）

Power Query（パワークエリ）を活用する際、データソースをどこに配置し、どのように接続すると効率的かで悩みます。せっかく設定してもデータソースの保存場所や名前変更で、データソースエラーとなり、その都度の設定が必要になったり、部外や社外の人と結果だけを共有したい時はどのようにすればいいかなど。 そこで、これまでのPower Queryを利用してみての備忘録を中心に、ファイルの状況と対応例をまとめてみました。データソースの管理方法についての個人的な考えです。 内容： データソース変更の基本 データソースのファイル名変更が多い（名前に年月名などがあり、バラつきがある） データソースの保存場所…

• #Power Query

【Excel VBA】テーブルに別ファイルのデータをインポート

Excelのテーブルに「別ファイルのシートにあるデータ」をインポートし、テーブルのデータを更新するVBAサンプルの備忘録。 作成の背景は、Power Queryで同じファイル内のシート（テーブル）をデータソースとする管理をしたいが、元のデータ自体は別ファイルにあるため、テーブルのデータ更新を効率化。 同じファイル内のシートをデータソースにする理由は、データソースを含めたファイル共有を容易にしたかったため。 サンプルの前提 Excelのファイルに、シート名「元」があり、テーブル名「元データ」でデータが入力されている。 「元データ」テーブルについてテーブル状態を維持したまま、データ削除し、別のEx…

• #Excel VBA

【Excel VBA】置換リストで都度確認しながらWord文書の文字列を置換

前回の 【Excel VBA】Excelの置換リストでWord文書の文字列を一括置換 をベースに、置換対象を都度確認しながら実行するVBAのサンプル。置換リストで一致したキーワードについて、都度、置換するかのメッセージを表示し、確認しながら置換を実行します。 「置換リストで都度確認しながらWord文書の文字列を置換」のVBAサンプル サンプルは次のとおり。 Sub 置換リストで都度確認しながらWord文書の文字列を置換() Dim wrd As Object Set wrd = CreateObject("Word.Application") Dim 置換リスト As Variant Dim …

• #Excel VBA

【Power Query】データソースを相対パスにして動的に変更する方法

Power Query（パワークエリ）は、データの変換や統合に便利なツールです。しかし、データソースのパスが固定（絶対パス）の指定で、ファイルの場所が移動すると設定変更が手間です。データソースを相対パスにして動的に変更できると便利なので、最もシンプルだと思う手順の備忘録。Excelのシートからファイルパスを可変な変数として取得し、Power Queryを実行します。

【Word】VBA実行後も「元に戻す」は可能だが、変更履歴の記録が便利

WordのVBAは、ExcelのVBAと違い、VBA実行後に「元に戻す」（Ctrl＋Z、Undo）が可能。ただし、既定の戻れる最大数は100のようで、VBA実行前にはファイルやフォルダのバックアップを取る習慣は大事。加えて、加えて、Wordの場合は、変更履歴の記録をONにするとVBAでの変更も記録されて便利

【Excel】 VBA実行後に 「元に戻す」はできないので事前対処

ExcelのVBA実行後、「元に戻す」（Ctrl＋Z、undo）はできません。VBAの実行で誤った操作や予期せぬ結果が発生して困ることがあるので、事前に対処方法を考えておくことが重要です。VBAでファイルコピーする方法などバックアップ方法の備忘録。

【Excel VBA】Excelの置換リストでWord文書の文字列を一括置換

ExcelのVBAで、置換リストからWord文書の文字列を一括置換する方法のサンプル。前回のWordのVBAを使用した「Word文書の文字列をExcelの置換リストで一括置換する方法」と同様のことをExcelのVBAで実施するサンプルを作成してみました。置換リストの内容に応じてVBAの内容も変更したいなど、Excelファイルの単位でVBAを管理できるメリットもあります。

【Word VBA】Word文書の文字列をExcelの置換リストで一括置換

WordのVBAで、Excelで作成の置換リストを選択し、Word文書内の文字列を一括で置換する方法の備忘録。執筆ルールに従った修正業務を効率化するため、Excelの置換リストで一括置換するVBAサンプルを作成してみました。

• #word

【Word VBA】Excelシートの最終行取得（xlUpで実行時エラー 424の対応）

WordのVBAで、Excelのシートにある情報を使った繰り返し処理等をするために、最終行取得をしようとすると「実行時エラー424」となったので解決方法の備忘録。なお、使用のバージョンは「Microsoft® Word for Microsoft 365 MSO」。

• #word

【Excel】OneDriveのファイルURLをローカルパスに変換（数式で文字列の置換）

Excelの数式で、CELL(“filename”,A1)やVBAのThisWorkbook.Pathを使用したとき、OneDriveに同期したフォルダではローカルパスが取得できず、VBA等が適切に動作しないことがあります。Excelの数式を利用して、OneDriveのファイルURLをローカルパスに変換してみたので備忘録。

【Excel】LAMBDA関数とTEXTBEFORE関数の使い方（フルパス等の取得事例）

Microsoft365のExcelでは、新しい関数の中に、・VBAを使用せず、カスタム関数を作成できるLAMBDA 関数 ・指定した文字の前を抽出できるTEXTBEFORE 関数・指定した文字の後を抽出できるTEXTAFTER 関数があります。 これら新しい関数の使い方の備忘録として、Excelファイルの保存場所（フォルダ・フルパス等）を取得する関数を作成したメモ。 結論としては、次の印象。 LAMBDA関数は、複雑な数式では有用。 TEXTBEFORE/ TEXTAFTER 関数は、従来のLEFT関数やFIND関数などの組み合わせより、シンプルに文字列を抽出可能。 使用例 この事例の前提知…

【PowerPoint】アニメーションを印刷しない方法（疑似的な解決策）

PowerPointの配付資料で、映写用で作成したアニメーションは非表示にしたい場面での擬似的な解決策の備忘録（特定のオブジェクトを印刷したくないも同様）。

• #PowerPoint

【PowerPoint】配付資料で穴埋め問題の作成方法は答えを図形で隠す

PowerPointの配付資料で、対象者の関心を引くために答えを伏せるなど、穴埋め問題にしたい時の備忘録。資料作成時の基本は、次の順序です。 1．答えを先に書いておく 2．図形（白い図形など含む）で答えを隠す 3．アニメーションの「終了効果」で、図形を消す

【Excel】ファイルの保存場所変更に備えた準備（ファイルパスの自動更新）

リンクの設定等があるExcelファイルの場合、保存場所の変更に注意が必要です。特に、VBAやPower Queryなどで固定のファイルパスに依存した構築を行うと、Excelに不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。 ファイルの保存場所やファイル名が変わる可能性が高い場合は、事前に保存場所変更に備えた構築が必要です。 そのため、Excelファイル自体の移動があっても、データソース等との相対的な位置関係で動作できるようにしておくことも有効です。 相対的な位置関係の基準になる場所は、Excelファイル自体の保存場所にすると便利なケースが多いので、Excelの…

【Excel】VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプル

Excelのテーブル機能やPower Queryを含むファイルを他者に渡すことが妥当でないケースが時折あります。そこで、VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録です。

【Excel】フォーマットは同じで、 「ファイル名」が異なるデータのインポート効率化

Excelの標準機能にあるPowerQuery（パワークエリ）を活用して、フォーマットは同じで、「ファイル名」が異なるデータのインポート業務を効率化する方法を調べた備忘録。 背景は、同一フォーマットで、ファイル名が取得時期や対象によって異なる次のようなファイルで、繰り返し発生する集計業務の効率化。 例） 売上データ_2023年4月.xlsx 売上データ_2023年5月.xlsx 売上データ_2023年6月.xlsx ・・・・ このようなファイル名が違うデータについて、毎月集計のためにコピーと貼り付けを繰り返しているようなケース。ファイル名を固定にする方法もあるが、データを識別するために固有なフ…

• #Power Query

【Excel】パワークエリで、複数のファイルを 1 つに結合する方法

Excelの標準機能にあるPowerQuery（パワークエリ）は、同一フォーマットで入力された複数ファイルを一つにまとめたい時に便利です。 毎月の売上データや、部門別の実績データといった複数のファイルを一つに結合する場面などに活用できます。これまでであれば、手作業で一つずつコピーと貼り付けを繰り返すか、VBAを利用して自動化するといった手間な業務がExcelの標準機能で効率化できます。 Microsoftの説明は以下ですが、アウトプットイメージと手順の補足です。使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。 support.micros…

【Power Query】データソースの変更方法

PoweqQuery（パワークエリ）は、毎月更新されるようなファイル（データソース）から、必要な情報の抽出を容易にできるツールで、とても便利です。 しかし、操作になれていないと、Excelであれば簡単なことの実現方法が分からずに困ることがあります。 例えば、データソースのエラーなどは、Excelの数式でいえば参照しているシートが無くなったことと同様ですが、どのようにエラー解消するかが直ぐにわからず利用を躊躇します。 データソースのエラー解消する方法は次のとおりですが、意図的にデータソースを変更する方法の備忘録。 shikumika.org データソースの変更方法 データソースの変更は、「データ…

【Power Query】データソースのエラーを解消する方法（DataSource.Error）

PoweqQuery（パワークエリ）で、データソースのファイル名や、ファイルの場所が変更になると、次のようなデータソースのエラーが発生します。 DataSource.Error: ファイル ‘〇〇.xlsx' が見つかりませんでした。 例えば、データソースとなるファイル名に「売上データ_〇月分」といった取得月の情報が含まれているケースでは、設定時と同じファイル名に修正するか、データソースの再設定が必要となります。 しかし、PoweqQueryに慣れていない場合、エラーメッセージに戸惑うことになるので、DataSource.Errorでデータソースを再設定する方法の備忘録。 （ファイル内の項目名…

【Excel】VBAのメンテナンス性向上のため設定情報をテキストボックスに記載

VBAの利用で、業務の属人化によるリスクが指摘されることがよくあります。そのため、誰かとVBAを含むExcelファイルを共有するような場合、業務変更が生じても少しExcelに詳しければ修正ができるようにと、初期値や設定情報をExcelのシートに記載などの対応をしています。 しかし、初期値や設定情報をExcelのシートのセルに記載する場合、セル位置を変更したり、意図せず変更されるとコードの修正が必要になります。 シート内のテキストボックスであれば、セル位置の変更に対応できそうだったので、試行の備忘録。 フォームコントロールやActiveXコントロール だと、VBAを知らない人の設定変更が難しいと…

【Power Query】空白ではない列の個数を行ごとにカウントする方法

Power Queryを使用して「ExcelのCOUNTA関数」のように、各行ごとに空白ではない列の個数をカウントする方法の備忘録。 ExcelのCOUNTA関数は、特定の範囲内の空白でないセルの数を数えるために使用しますが、Power Queryで同様な関数はList.NonNullCount関数。 アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTA関数」であれば次のような数式。 例） =COUNTA(B2:F2) 具体的なアウトプットイメージは次のとおり。 PowerQueryで行ごとにB列からF列で、空白でない列の個数を行ごとにカウントする。 作成方法 「カスタム列」の…