shikumika’s diary

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

【Power Query】空白セルがある場合に「上のセルと同じ値」を一括入力

Excelの作業時、空白セルがある場合に上のセルと同じ値を一括入力して集計等をしたいことがあります。そのような時、最近のExcelの標準機能として利用できる「PowerQueryのフィル機能」が便利です。

この機能を使うと、特定の列の空白セルを、空白でない最後の値に置き換えることができます。 

なお、この方法は元データはそのままで、新たなテーブルが作成されます。元データの変更にあわせて繰り返し処理が必要なケースやデータ量が多い時に便利です

内容:

PowerQueryのフィル機能について

この機能を活用したアウトプットイメージは次のとおりです。

下図左側のExcelファイル(空白セルあり)を右側(空白セルは、「上のセルと同じ値」の入力)に変換したテーブルを作成します。

Power Queryのフィル機能を活用したイメージ

空白セルがある場合に「上のセルと同じ値」を一括入力

フィル機能の詳細は以下のとおりです。

learn.microsoft.com

具体的な事例

冒頭のアウトプットイメージ左側のExcelファイル(空白セルあり)を前提に以下説明します。

1.対象のデータをPowerQueryに取り込み

Excelの「データ」タブから「テーブルまたは範囲から」をクリック、対象のデータをPowerQueryに取り込む。

取り込んだデータがPowerQueryエディターで次のように表示される。

設定によるが「変更された型」までのステップが自動作成されている

PowerQueryエディターにデータ取り込み後イメージ

なお、PowerQueryに取り込み方法が不明な場合は、【Power Queryの初歩】簡単な事例説明(使いながら覚える最初のポイント) を参考にする。

2.空白セルがある列を選択して「フィル」の実施

前手順でPowerQueryエディターにデータ取り込み後、「変換」タブの「フィル」をクリックし、「下へ」を選択する。なお、埋める方向は下方向か上方向で選択可能。

この事例で、「大分類」という列を選択後、フィルを下方向で実施した画面は次のとおり。

「下方向へコピー済みというステップが追加され、空白セルが上の値で埋められている

「大分類」という列を選択後、フィルを下方向で実施後の画面

なお、空白セルがある場合に「上のセルと同じ値」を入力したい列が複数ある場合は、2列を選択肢して「フィル」の実施、もしくは1列ずつ実施する。

以下は1列ずつ「フィル」を実施した結果だが、ステップは「下方向へコピー済み」の一つに統合されている。2列を選択肢して「フィル」の実施と同じステップができている。

ステップは「下方向へコピー済み」の一つに統合されている

1列ずつ2列分の「フィル」を実施した結果

これで、「閉じて読み込む」をクリックすると冒頭のアウトプットイメージとなる。

 

以上、Excelで空白セルがある場合に「上のセルと同じ値」を一括入力する方法でした。PowerQueryのフィル機能を使えば、このような操作を簡単に、効率的に行うことが可能です。