shikumika’s diary

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

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

2023/07/31 06:30

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

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

2023/07/29 10:30

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

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

2023/07/28 06:30

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

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

2023/07/26 06:30

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

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

2023/07/24 12:30

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

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

2023/07/22 10:30

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

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

2023/07/21 06:30

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

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

2023/07/19 12:30

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

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

2023/07/18 06:30

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

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

2023/07/17 13:00

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

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

2023/07/14 06:30

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

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

2023/07/12 06:30

【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 …

2023/07/10 06:30

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

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

2023/07/08 17:30

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

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

2023/07/07 06:30

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

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

2023/07/05 06:30

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

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

2023/07/03 06:30

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

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

2023/07/01 13:00