Excel PowerQuery 1列内の情報が混在している表を整形する

下図のExcel表は、なんかデタラメな感じだ。

B列・C列に空白が多いのも気になるが、なんと言ってもA列が「社員コード」というのはウソだろうと言いたい状態になっている。

これのモデルは、私の会社の人達が作ってるExcelファイル・・・ではなく、れっきとした会計システムから出力される帳票のExcelファイルだ。

今回はこれを、PowerQueryを使って整形する例を扱う。

今回のExcelファイルのダウンロードはこちらから。

表の規則性

今回のは、PowerQuery操作の問題としては、難易度は高くない。前回の方が難しいだろう。

それよりも、Excelのきちんとした表の作り方に馴染んでいない人にとっては、まずこの表をどのように整形すれば良いのかという完成図を思い浮かべることが難しいと思う。

まず、この表のA列も、何も考えず作られているわけではなく一定の規則性に基づいている。

表を再掲するが、次のような規則性だ。

会社の組織というものは、部 ＞ 課 という階層になっているが、

といったものだ。

Excelフォーマット改善とかに慣れがない人は恐らく、そういう規則性を探そうというステップの前段階で、どうして良いか分からないのかなと思う。

表の整形方針

ではその規則性が分かったら、どのような表に整形する完成図を描けば良いのか。

列内の情報の統一

Excelの表作成の原則はデータベース形式であり、1列に含まれる情報は1種類に統一されていなければならない。

今回の表は「社員コード」列に「部」「課」「社員コード」の情報が混在しており、これを混在しないようにしないといけない。

だから「部」「課」にはそれぞれ専用の列が必要だ。

集計行の削除

また、【】記号で挟まれた集計行も、削除しなければならない。
こういう集計行がないと気持ち悪いという人が多いが、それは駄目だ。

こういう集計行があるままだと、他にこういう分析がしたい・ああいう分析がしたいという多様な用途に、まるで柔軟に対応できない。

集計がしたければ、ピボットテーブルなどで別にやるべきであり、元データ(入力作業用)と見せる用データはシート分けするのもExcel使いの原則だ。

以上をまとめると、下図のように整形する完成イメージになる。
「部」「課」の列が追加され、データが隙間なく埋められている。

ファイルを開く～条件列の追加

ファイルを開く

ではまず、今回使用するExcelファイルをこちらからダウンロードして、自PCのどこかに保存する。

リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」

として、同ファイルを開く。

そしてナビゲーター画面の左端からシートをクリックし「データの変換」を押す。

条件列の追加①部

では「部」「課」の列を追加していく。

「社員コード」列が＜ 記号で始まるのが「部」であり、まずその列を追加する。

リボン「列の追加」タブ
→「条件列」

とする。

Excel PowerQuery 計算式や条件を入れたオリジナルの項目列を追加する

2021年8月10日

そして条件指定画面で、次のように指定する。

これで、

「社員コード」列が＜ 記号で始まるときは「社員コード」の値をそのまま持ってきて、それ以外の時は値なし(null)

という「部」の列が追加される。

条件列の追加②課

次は「社員コード」列が[ 記号で始まる「課」の列を追加する。

リボン「列の追加」タブ
→「条件列」

から、次の条件を指定する。

これで「課」の列も追加される。

追加された「部」「課」の列は最後尾にあると思うが、それらを先頭列に移動させておこう。

結果、下図のようになる。

フィル～フィルタリング

フィル

「部」「課」列は、データの一番上以外はnullになっているので、これを埋めよう。

「部」「課」列を両方選択して

リボン「変換」タブ
→「フィル」
→「下」

とする。

Excel PowerQuery データの空白を埋める

2020年8月30日

フィルタリングで不要な行を削除

集計行など、不要な行を削除していく。

不要な行というのをどう判定するかだが、今回の場合は「社員名」列がnullであるという条件で良いだろう。

「社員名」列の右端の□ボタンを押し、フィルタリング画面で「null」のチェックを外して「OK」を押す。

不要な集計行などが消え、下図のようになる。

これでひとまず完成として良い
・・・とは思うが、「部」「課」の最初と最後に付いている記号を削除する仕上げも、しておきたい。

それについては、長くなったので次回扱う。