shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

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

Excelで複数のブックのデータ(単票)を一覧表にまとめる方法にはいくつかの選択肢があります。Excel関数、VBA、Power Queryなどを活用したこれまでの備忘録を中心にデータの状況と対応例をまとめてみました。

個人的な結論は、VBAを使った方法が汎用性も高く、効率的な印象。

次いでPowerQueryで「列のピボット解除」による方法。

あとは、レイアウトやデータ量、繰り返し頻度などで、効率的な方法を選びます。

内容:

Excel関数だけで処理したい場合

INDIRECT関数を使用する方法

標準のExcel関数だけで処理したい場合、INDIRECT関数を使用する方法が有効です。

個人的には、一旦複数のブックを一つのファイルにまとめた方が作業しやすい印象です。また、シートの枚数が多い場合や、繰り返しシートの更新がある場合などはVBAやPowerQueryを使用した方法と比較すると手間です。

shikumika.org

また、INDIRECT関数を使用せず、数式に直接セル番地を入力して値を参照することも可能です。ただし、数式に複数ブックのセル番地を入力することが手間になります。

VBAを使用したくない場合

Excelの標準機能であればPower Queryを活用すると便利です。

Power Queryの列のピボット解除でセルの値を抽出する方法

Power Queryで、全てのデータを読み取り後、必要なセル位置のデータを取得する次の方法が効率的です。ただし、Power Queryに慣れていない場合や、単票のデータ範囲に含まれるセルの個数が多くなる場合、単票のレイアウト変更がある場合などは設定が手間となる可能性が高いです。

shikumika.org

Power QueryとExcelの数式を組み合わせてセルの値を抽出する方法

Power Queryの列のピボット解除が不慣れな場合や、単票形式のレイアウトが異なる場合などはExcelの数式も活用する方法が効率的かもしれません。

shikumika.org

Power Queryのカスタム関数を作成してセルの値を抽出する方法

単票のデータ範囲に含まれるセルの個数が多くなり手間な場合は、必要なセル位置の情報を指定して取得するカスタム関数を活用した方法が効率的です。

(カスタム関数を利用するため、若干難易度が高くなるが、慣れたらこの方法も上記より便利な印象)

shikumika.org

汎用的なVBAを使った方法

VBAの設定は必要だが、汎用的に利用できるサンプル(設定後は、VBAの理解がなくても利用可能な見込)。

Excelのメモ機能を活用して抽出する方法

Excelのメモがあるセルを抽出対象とし、タイトルと併せて一覧化が可能です。

shikumika.org

都度選択したセルから値を抽出する方法

以下は、繰り返し業務ではなく、都度選択して一覧化するケースで便利です。

shikumika.org

抽出したいセル位置が変更してもメモの情報で抽出する方法

抽出したいセル位置が変更しても取得したい場合の方法です。ただし、フォーマットを発行する側で、記入者に「メモ」を削除させない管理ができる立場でないと有効な方法となりません。

shikumika.org

Excel VBAでユーザー定義関数による方法(非常に遅い)

次の方法は処理の参考用で、用途に適さない可能性が高いです。

shikumika.org

その他の方法

Power Automateを利用した方法

Power Automateを利用した場合も同様のことは可能で、Windowsのパソコンであれば利用可能で選択肢となります。ただし、どこまで改善可能なのか試行中で

・データ抽出のスピードは遅くなる。

・メンテナンス(初期設定や修正)は、少し手間となりやすい。

という印象です。そのため、目的次第ですが数十件程度までの処理件数で、シンプルな処理のケースは適している印象です。

shikumika.org

 

また、Pythonの場合は環境構築なども必要なので、今回の選択肢から除外。

以上、Excelで、複数ブックのデータ(単票)を一覧表にまとめる方法の比較でした。

参考(データの加工・抽出、管理):

データの加工・抽出、管理の操作事例について、以下にまとめています。

shikumika.org