shikumika’s diary

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

【Power Query】単票形式のExcelシートから数式も組み合わせてセルの値を抽出

Power Query(パワークエリ)で、単票形式のExcelシートからセルの値を抽出する方法として、【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出とは異なる方法として、Excelの数式でセルを参照し、値を抽出する方法の備忘録。

単票形式にしている目的やシートを管理できるレベルによりますが、この方法はPower Query(パワークエリ)の設定が少なくなるので、有効な手段の一つです。

内容:

アウトプットイメージ

次のような単票形式(一件一葉の個票)のファイルが複数あり、Power Queryで一覧にしたいという場面です。

一覧にするアウトプットイメージです

単票形式のExcelファイルをパワークエリで一覧化

手順

変換用のシートで1行のデータを作成する

単票形式のデータがある各ファイルに、事前にシートを用意しておき、単票から数式で値参照し、Power Queryで取込が容易な形式に変換する。

各ファイルにシート「データ」を作成し、単票から数式で値参照している

変換用のシートで1行のデータを作成するイメージ

数式を含めた具体的なイメージは次のとおり。

補足説明:図の右側の4行目(赤字部分)は、2行目の数式内容を表示している。

2行目の数式で、単票形式のシートの値を参照している

数式を含めた具体的なイメージ

Power Queryで一覧化する

Power Queryで取込が容易な形式に変換したシートを利用して一覧化する(事例ではシート名「データ」)。

なお、Power Queryで複数のファイルを一つに結合する方法は以下です。

shikumika.org

参考情報

Excelで複数ブックのデータ(単票)を一覧表にまとめる方法にはいくつかの選択肢があるので以下に対応例をまとめました。

shikumika.org

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

shikumika.org

以上、Power Queryで単票形式のExcelシートから数式も組み合わせてセルの値を抽出する方法でした。

PowerQueryだけでは複雑なステップになる処理も、Excelの数式を組み合わせすることで、シンプルな処理が可能になります。