Excel VBAで、データ範囲の最終行・最終列の取得は、データ分析や自動処理において重要です。しかし、複数の行や列にわたって値が入力されている場合、単純な判定方法では適切な範囲を取得できないことがあります。 例えば、各列の最終行が異なり、指定した1つの列で判断できないようなケースです。 そこで、今回は各列・各行をチェックし、最大の行・列番号を取得する方法の事例です。 内容： アウトプットイメージ VBAサンプル 最終行・最終列の取得の基本 サンプル例 補足説明 活用事例 動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン250…
事務作業の効率化で本ブログが紹介している主なツール・手段について、「主な活用場面」に応じて分類しました。 1. データの活用 Excel：表計算、データ整理、グラフ作成 Power Query：データの取得・変換・統合 Power Pivot：高度なデータ分析・ピボットテーブル拡張 kintone：業務データの共有、ノーコード業務アプリ作成 Microsoft Lists：チームのタスク・情報管理 Access：データベース管理 2. 業務の自動化・効率化 VBA：Excel等での自動化 Power Automate：Windowsやクラウドのワークフロー自動化 3. コミュニケーション・情報…
Power Queryを活用する際、データソースの列名が変更されることでエラーが発生し、クエリの修正が必要になるケースは少なくありません。しかし、Power Queryを十分に習得していない人にとっては、この修正作業が容易ではなく、負担が増えてしまうことがあります。そこで今回は、列名変更の影響を受けずに動作する保守性の高い動的クエリの作成事例を紹介します。特に、データソースの列名が変更される可能性が高い場合に活用できる実践的な方法です。 内容： 動的な列名変更に対応する方法の基本 具体的な設定事例 設定のアウトプットイメージ 設定の補足説明 1．変更された型の削除 2．マージされたクエリ数のス…
Excelで同じレイアウトで作成した2つのシート（一覧表）の差異を確認する際、異なるシートの情報を左右に並べて結合できると、視認性向上や数式によるチェック作業が容易となります。 ExcelのVLOOKUP関数を活用した表結合が、Excelで利用可能なPower Queryのマージ（結合）機能で効率的に実現できます。 内容： アウトプットイメージ 設定手順 基本 設定の全体像 マージの設定手順 マージの補足説明 参考情報 PowerQueryで列ごとの比較結果を追加例 Excelの条件付き書式を使用した比較例 なお、確認のExcelは「Microsoft® Excel® for Microsof…
Excelで2つのシートを比較し、同じセル位置にある値が異なる場合、そのセルに色を付けて違いをわかりやすくする方法です。 内容： アウトプットイメージ 設定手順 シートの準備 条件付き書式の設定 なお、確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2504）です。 アウトプットイメージ Excelの条件付き書式の機能を使って、下図のように同じセル位置にある値が異なる場合、そのセルを黄色にします。 条件付き書式を使った2つのシートの違いを比較イメージ 設定手順 シートの準備 比較したい2つのシートを一つのファイル（ブック内）に準備…
Excelで2 つのブック間の差異をセルごとに表示し、確認する方法を調べた備忘録です。 内容： Inquireアドインが利用できる場合 Inquireアドイン以外の方法 条件付き書式による方法 Power Queryによる方法 VBAによる方法 Inquireアドインが利用できる場合 Excel 用スプレッドシート照会アドインのツールとして、 利用のExcelがOffice Professional Plus および Microsoft 365 Apps for enterprise エディションの場合、「Inquireアドイン」を利用できます。 このアドインは、2 つのブック間の違いをセルご…
OutlookとMicrosoft To Doを活用すると、タスク管理とスケジュール管理の効率化が図れます。今回はTo Doのタスクを予定表に登録し、時間管理をする方法です。 内容： OutlookでTo Doを予定表に登録する方法 注意点 参考：Outlook・To Do・Planner・Teamsの連携イメージ 確認の環境は、執筆時点の「Microsoft 365 Business Standard」でWeb版です。 OutlookでTo Doを予定表に登録する方法 「新しい Outlook」や「Outlook on the web」でTo Doを予定表に登録するときは、「マイ デイ」ウィ…
MicrosoftのOutlookやTeamsを使用して日常業務をする環境で、効率的なタスク管理とスケジュール管理をする方法を調べた備忘録です。 メールやTeamsのチャットなどからタスクや予定を管理するためには、MicrosoftのOutlook、To Do、Planner、Teamsの各機能の違いや連携の仕組によって何ができるのか、どのような方法があるのかを理解する必要があります。 内容： Outlook、To Do、Planner、Teamsの各機能と連携の概要 タスクやスケジュールの管理でできること Outlook：メールからToDo登録、ToDoから予定表に登録など To Do：Ou…
作成したExcelのマクロを他のブックでも利用したいときは、個人用マクロブック（PERSONAL.XLSB）やExcelアドインを利用します。 マクロを含まないExcelファイルでも「個人用マクロブック」等からマクロを常に利用することができるので、自分の業務に関する汎用的なマクロを作成しておくと便利です。 内容： マクロの保存先 活用事例 マクロの保存先 Excelのマクロは、「Excelマクロ有効ブック(*.xlsm)」に保存していることが多いですが、 個人用マクロブック（PERSONAL.XLSB） Excelアドイン（*.xlam） という保存先もあります。これらは、複数のExcelファ…
Excel VBAで、フォームによる簡単ランチャーの作成事例です。 フォームを利用するメリットの一つに、フォームを開いたまま Excelのセル編集や他の作業が可能な「モードレス表示」がある点があります。 VBA業務の属人化を防止のため、できるだけシンプルな構成が望ましいです。レイアウトの自由度はないですが、次の「フォーム」ではなく「インプットボックス」やExcelのワークシート上にボタンを追加する方法で目的が達成できるなら、その方が良いと思います。 shikumika.org 内容： アウトプットイメージ 作成手順 ステップ1: ユーザーフォームを作成 ステップ2: コードを記述 ユーザーフォ…
Excelマクロを使用した業務の自動化で、メニューの中からマクロを選んで実行したいとき、簡易な方法で実現する事例です。 いわゆる簡易ランチャーの作成事例で、フォームを使用しない、シンプルな方法です。 内容： アウトプットイメージ VBAサンプル 補足説明 動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2503）です。 アウトプットイメージ このマクロは、ユーザーに対して「InputBox」を使って選択肢を提示し、入力された値に応じて特定のマクロを呼び出す事例です。 簡易ランチャーの作成事例 VBAサンプル VBAのサンプルは…
Excelマクロを使用して業務を自動化する際、処理の履歴をExcelシートに出力し、実行後に記録を参照できるようにしたいときがあります。 ログ出力をする簡単なサンプルです。 内容： アウトプットイメージ VBAサンプル 動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2503）です。 アウトプットイメージ 下図のように、マクロがあるExcelファイルにシート名「ログ」を作成し、処理結果を示すログを出力します。 この事例では、A列とB列に処理対象と処理内容のログを出力しています（For文で処理を1から5まで繰り返し）。 シート名…
Excelの作業効率化で自分の作業にあったショートカットキーがない時、「クイックアクセスツールバー」を利用すると「ALT＋数字」の独自ショートカットキーを作成できます。 「クイックアクセスツールバー」に登録できる操作はショートカットキーに登録可能で、「新しいウィンドウを開く」を事例に説明します。 内容： アウトプットイメージ クイックアクセスツールバーの基本 参考情報 Excel のキーボード ショートカットの一覧 2つの画面を並べて表示、比較することが多いときの操作例 なお、確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン250…
Windowsの「スナップ機能」は、複数のウィンドウを効率的に整理し、画面スペースを最大限に活用できる便利な機能です。 また、この機能には、開いているすべてのウィンドウを整理するために役立つ「スナップレイアウト」等もあります。ただし、機能を充分に理解していなかったので活用方法の備忘録です。 スナップレイアウト（スナップ ポップアップ） 内容 スナップ機能とは 「スナップ機能」で理解しておきたい点 1．分割線で画面サイズを変更できる 2．スナップのグループで、画面指定できる 3．「Windows」＋「Z」の後、「1」で直近のウィンドウを並べる 確認したwindowsはWindows11 24H2…
Teamsのメッセージを保存・管理する方法として、Plannerで「あとで読む」というプランを作成して情報整理する方法です。 Plannerによる管理の基本は、次のとおりです。今回は、Plannerで「あとで読む」というプランを作成して、通常のタスクと情報整理タスクを分ける事例です。 shikumika.org 内容： Plannerで自分の「新しいプラン」を作成 Teamsのメッセージから「Plannerタスクを作成」 確認のMicrosoft Teamsのクライアントのバージョンは25060.205.3499.6849です。 Plannerは Microsoft 365 の一部です。Mic…
Teamsの利用が増えてくると「情報が多すぎて大事な情報が埋もれる」、「必要な情報を見つけるのが難しい」などが課題になることが多々あります。以前のTeamsには「このメッセージを保存」という機能がありましたが、現在のTeamsにありません。 そのため、代替案としてMicrosoft アプリのPlanner（タスク/To Do）を利用することが推奨されているので、活用方法の備忘録です。 内容： メッセージ保存の推奨はPlanner Plannerを活用したTeamsのメッセージ保存と確認イメージ （1）メッセージから「Plannerタスクを作成」をクリック （2）保存内容を確認し、「タスクの追加…
Excel で選択しているセル（アクティブセル）の行や列のデータを確認しながら作業をする際、選択中のセル（行と列）を強調表示することでミス防止や作業効率を向上させる方法です。 内容： アウトプットイメージ 利用方法 注意点 1．利用できるExcelはMicrosoft 365 2．強調表示は対象のウインドウを選択状態時のみ なお、確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2503）です。 アウトプットイメージ 選択中のセル（行と列）を強調表示のアウトプットイメージは次のとおりです。 「セルにフォーカス」機能で、アクティブセル…
Microsoft WordのIncludeText フィールド機能を活用すると、他の文書の内容を別の文書にリンクとして挿入し、自動的に更新することができます。動作イメージとしてはExcelのセル参照に似たもので、リンク先の文書を編集することで、自動反映される仕組みです。 IncludeText フィールドの設定事例と注意点を備忘録として整理しました。 内容： IncludeText フィールドのアウトプットイメージ IncludeText フィールドの基本 他の文書の内容をリンクとして挿入するIncludeTextの設定方法 1．リンク元（文章2）にブックマークを設定し、名前を控えておく 2…
Microsoft Wordの「ブックマーク」機能を使用すると、ドキュメント内の特定の場所へ簡単にジャンプしたり、他のドキュメントの内容を参照して挿入することができます。 なお、同様の機能である「相互参照」も同一ドキュメント内のジャンプや内容の参照はできます。しかし、他のドキュメント内へのジャンプや内容の参照はできません。他のドキュメントの特定の場所へのジャンプや参照をしたいときは「ブックマーク」機能を使用したリンク等の挿入で実施します。 内容： アウトプットイメージ ブックマークの基本 他のドキュメントを参照するブックマークの設定方法 （1）文書2（ジャンプ先のドキュメント）を開き、場所を指…
Word VBAを使用する際に「このメンバー識別子は既にオブジェクトモジュールの中に存在しています」というエラーの原因と対応例の備忘録です。 内容： エラーの内容 メンバー識別子とは？ 対応例 動作確認のWordは「Microsoft® Word for Microsoft 365 MSO」（バージョン2502）です。 エラーの内容 マクロを実行しようとすると、「コンパイルエラー：このメンバー識別子は既にオブジェクトモジュールの中に存在しています」というエラーです。 「このメンバー識別子は既にオブジェクトモジュールの中に存在しています」のエラー メンバー識別子とは？ メンバー識別子とは、VBA…
Excelで、数値の先頭に「0」を追加して桁数を揃えた文字列を作成する方法です。 電話番号や郵便番号などの先頭に「0」を含むデータをExcelで処理するときは、注意が必要です。文字列の表示形式で扱っていると、編集時に一部のデータが数値に変換され、先頭の0が消えてしまうことがあります。 その結果、VLOOKUPなどの検索が一致しないなどの問題などが発生してしまいます。 先頭に「0」を含む元データを保有しており、簡単に再処理できる場合は問題ありませんが、データ処理が進んでいる場合などはTEXT関数を使った変換も便利です。 なお、Microsoft365のExcelバージョン 2309 以降は、「先…
ExcelのREPT関数は、文字列を指定した回数だけ繰り返す関数です。「■」や「★」を使った簡易なグラフ作成などでで活用できます。ただし、条件付き書式で簡易グラフ（データバーなど）も可能なので、最近は使用場面が減っていました。 今回は用途は限られますが、Excelの表のレイアウト調整で改行文字を規則的に追加する場面でREPT関数も便利だと思ったので備忘録です。 アウトプットイメージ REPT関数で、改行文字：CHAR(10) を繰り返し、元の文字に改行を追加、レイアウト行高さを変更しています。 レイアウト調整にREPT関数で改行文字を繰り返し 補足説明 数式の内容は、以下です。 D2の場合：（…
Power QueryでグループごとのTableから値取得する事例です。 この方法は、複数の「単票形式のExcelシート」を処理したい場合や、複数行ごとの表を一行に変換したい場合などに活用できます。 内容： アウトプットイメージ 事例の前提 具体的な手順 1．Tableから該当する値を取得 2．手順1を繰り返す（値取得をしたい回数） その他の方法（列のピボット解除） 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2403）です。 アウトプットイメージ 列フィールドにあるTableから値を取得し、一覧表を作成します。 グループごと…
Power Queryで複数行ごとの表を1行に変換する事例として、繰り返しの行数が変動する場合の対応事例です。 繰り返しの行数が固定の場合は、【Power Query】複数行ごとの表を1行に変換（一つ下の値を参照）をご覧ください。 内容： アウトプットイメージ PowerQueryの設定イメージ 基本知識 具体的な手順 1．対象のデータをPowerQueryに取り込み 2．同じ1件を識別するため「NO」列の値を「フィル」で埋める 3．1件分のレコードごとの「Table」を作成 4．1件分のレコードごとの「Table」に行の位置を示すインデックス追加 5．余分な列を削除する 6．行位置を含む「T…
Excelの作業時、空白セルがある場合に上のセルと同じ値を一括入力して集計等をしたいことがあります。そのような時、最近のExcelの標準機能として利用できる「PowerQueryのフィル機能」が便利です。 この機能を使うと、特定の列の空白セルを、空白でない最後の値に置き換えることができます。 なお、この方法は元データはそのままで、新たなテーブルが作成されます。元データの変更にあわせて繰り返し処理が必要なケースやデータ量が多い時に便利です。 内容： PowerQueryのフィル機能について 具体的な事例 1．対象のデータをPowerQueryに取り込み 2．空白セルがある列を選択して「フィル」の…
「おすすめ記事」の紹介です。日々の事務作業で手間なことを簡単にできる仕組み（自動化、方法など）、困ったことの解決方法について、いくつかの方法を比較した記事や、特徴を整理、全体像をわかりやすくした記事などです。 データの加工・抽出、管理、集計・可視化の基本 ・データの加工・抽出、管理の操作事例まとめ（主にExcel） ・データの集計・可視化の操作事例まとめ（主にExcel） データの加工・抽出、管理、集計・可視化のその他 データの加工・抽出、管理、集計・可視化において、詳細に比較・確認などの記事です。 ・【Excel】複数のシートをまとめる方法（ツール含む） ・【Excel】複数ブックのデータ（…
Power Queryで複数行ごとの表を1行に変換する事例として、一つ下の行の値を参照する方法です。他にも変換する方法はありますが、Excelのセル参照による変換イメージに近い方法です。 内容： アウトプットイメージ 事例の前提 手順 1．「カスタム列」の追加（一個目） 2．「カスタム列」の追加（その他） 3．データの整理（フィルター、並び替えなど） 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2403）です。 アウトプットイメージ 下図のように、Excelで「3行分に1件分のデータが入力されている表」をPowerQueryで…
Power QueryでもExcelのように一つ上の行の値を参照することは可能です。Power Queryは列（フィールド）を基本に処理しますが、「一つ上の行の値を参照する方法」を理解しておくと作業の効率化が図れます。 内容： アウトプットイメージ 事例の前提 Excel ブックの内容を取り込み、PowerQueryエディターの起動 PowerQueryで一つ上の行の値を参照する基本知識 手順 1．「インデックス列」の追加 2．「カスタム列」の追加で、一つ上の行の値を参照する 3．余分な「インデックス列」の削除 よくある間違い （誤った事例）手順2で、カスタム列の式にステップ名を省略 使用のE…
ExcelのOFFSET関数は、特定のセル範囲から指定した行数と列数の位置にあるセル範囲の参照を返す関数です。この関数を活用し、セルの値に基づいて参照範囲を動的に変更する事例です（MATCH関数も活用）。 内容： 事例の前提 セルの値で参照範囲を変更 MATCH 関数の説明：検索文字が次に出現する位置（行数）の算出 OFFSET 関数の説明：参照範囲の変更例 参考 事例の前提 セルの値で参照範囲を変更できると便利な場面として、下図のような事例です。 この事例では、下図左側のA列の値の出現状況に応じて、参照範囲を変更しています。 具体的には、”部門”という値が次に出現する位置（行数）を求め、その…
前回は、「3行ごとに1件分のデータ」が入力されている表を1行に変換する方法でした。今回は、繰り返しの行数が変動する場合の方法です。 例えば、下図のように基本的な項目パターンは一致するものの、2行や3行のデータ、空白行があるような表を1行に変換する事例です。 Excelで作成された表のデータベース化などを進めようとすると、時折このような表の処理が必要になることがあるので、対応の一例です。 繰り返し行数が変動する場合の複数行を1行に変換イメージ 内容： 事例の前提 対応方法 参考情報 事例の前提 複数行ごとの表を一行に変換の基本は次のとおり。 shikumika.org 今回は、基本的な項目パター…
Excelの表で、1件のデータが複数行ごとに入力されている場合に、集計等の効率化のために1行のデータに変換する方法です。 例えば、下図はExcelの3行分に1件分のデータが入力されており、1行に変換するアウトプットイメージです。 複数行ごとの表を一行に変換するアウトプットイメージ このように、リストのように入力はされているが、同じ列に異なる値が入力された表を変換する事例です。 変換する方法はいくつかありますが、「Excelのみを使う場合」で個人的によく実施する方法です。 内容： 事例の前提 実施手順 1．セルを参照する数式で1行分のアウトプットを作成する 2．数式を対象となる件数分、下方向にコ…
Excelで、複数のシートを一覧表にまとめる方法に、VSTACKやINDIRECT関数、マクロ、PowerQueryなど、いくつかの選択肢があります。本ブログでもいくつか紹介しています。 よく似た方法がいくつかあり、全体像がわかりづらくなったので、選択する視点をまとめてみました。Microsoft365のExcelおよびWindowsの標準で使える方法（ツール含む）で整理しています。 内容： 方法（ツール含む）を選択する視点 リスト形式、単票形式の補足 リスト形式、Book内の複数Sheet 単票形式、フォルダ内の複数BookのSheet 参考 方法（ツール含む）を選択する視点 複数のシートを…
Excelで印刷範囲の設定は、青い線がある「改ページプレビュー」が便利です。 レイアウト調整や印刷切れの確認がしやすいメリットがあります。 最終的に印刷することを想定した資料作成の作業環境は、原則、「改ページプレビュー」が良いと思います。 内容： 改ページプレビューの表示 改ページプレビューの設定例 青い点線をドラッグ 青い点線をドラッグした結果 余分な余白ページをなくす 改ページを解除する方法 改ページの設定で縮小されたときの対応 （参考）セルの文字が隠れる、印刷で文字が切れる場合の対応 改ページプレビューの表示 改ページプレビューについて、Microsoft サポートの説明ページは以下で、…
Power Automate Desktopには「Webページのコンテンツを待機」アクションがあります。 このアクションは、特定のテキストまたはWebページの要素がWebページに表示されるまで（または非表示になるまで）フローを中断します。 他に同様な機能として、Power Automate Desktopには、 アクションの詳細設定にある「ページが読み込まれるまで待機します」 というオプションがあります。 詳細は、以下のページ内で「2．Webフォーム入力などの場合はページが読み込まれるまで待機の有効化」をご覧ください。 shikumika.org このオプションの有効化で必要なフローを作成でき…
ExcelのVLOOKUP関数とXLOOKUP関数で、複数条件による検索方法の事例です。スピルが利用できるExcel（Microsoft365など）では、シンプルな数式で複数条件による検索が可能です。 内容： 複数条件で検索する数式例 数式の説明 複数条件で検索する場合、検索条件にするセルを連結する 補足説明 スピルの説明 ”複数列”を検索範囲にする方法 （参考）XLOOKUP関数の特徴と活用事例 参考： 動的配列数式とスピル配列の動作 - Microsoft サポート 複数条件で検索する数式例 下図は検索対象の表（$B$3:$D$8）で「B列に合致、かつ、C列に合致」の複数条件で検索し、一致…
ExcelのVLOOKUP関数とXLOOKUP関数で複数列を検索範囲にして値を抽出する方法の事例です。1列の検索範囲で複数列の値を抽出したい場合は【Excel】VLOOKUPとXLOOKUPで複数列の値を抽出する方法の比較をご覧ください。 内容： 複数列を検索範囲にした数式例 VSTACKとHSTACKの計算結果例 その他、VSTACKとHSTACKを活用した事例 （参考）XLOOKUP関数の特徴と活用事例 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳…
ExcelのXLOOKUP関数はVLOOKUP関数の改良版で、検索列と結果列の選択がしやすいなど、とても便利な関数です。 活用の考え方は、基本はXLOOKUP関数を使用し、VLOOKUP関数は何かしらの目的があるときだけ利用が良いと思います。なお、VLOOKUP関数が必要な場面としては、XLOOKUP関数が使用できない人とのファイル共有や、テーブル内での利用などです。 VLOOKUP関数と比較した特徴と、活用事例をまとめました。 なお、XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 内容： XLOOKU…
ExcelのXLOOKUP関数は、抽出したい列数が多い場合（連続している場合）にVLOOKUP関数より便利です。抽出したい複数列が連続していない場合は、目的やデータの状況によりますがHSTACK関数と組み合わせる方法も有効です。 内容： VLOOKUPとXLOOKUPで複数列を抽出する方法の基本 離れた位置の複数列を抽出する方法 （参考）VLOOKUP関数の範囲にもHSTACK関数を使用可能 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳細：HSTACK…
ExcelのVLOOKUP関数と、その改良版であるXLOOKUP関数で複数列の値を抽出する方法の比較です。 抽出したい列数が多い場合（連続している場合）はXLOOKUP関数が便利です。なお、抽出したい複数列が連続していない場合は、目的やデータの状況によります。 内容： 連続した複数列の値を抽出する方法 VLOOKUP関数と配列数式の組み合わせ（非推奨） 連続しない複数列の値を抽出する方法 参考情報 複数列の抽出スピードは、XLOOKUP関数が遅い時あり XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 連…
ExcelのVLOOKUP関数やXLOOKUP関数で、単一の表（セル範囲）ではなく、複数シートのセル範囲から値を抽出したい場合はVSTACK関数が便利です。 VSTACK関数は複数のシートにある表を一つの表に結合できる関数です。 内容： アウトプットイメージ VSTACK関数の基本事例 数式の補足説明 （参考）Power Queryの活用 なお、VSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the webで利用可能です。 詳細：VSTACK 関数 - Microsoft サポート アウトプ…
