データ Excel パワークエリ 小技集

はじめに

以前、Excelとパワークエリを用いたデータの作成方法の流れのなかでデータクレンジングとリスト形式(縦持ちデータ)に軽く触れた。

今回は集計表及びクロス集計表の例をあげて、データクレンジングとリスト形式(縦持ちデータ)の変換に特化してデータ作成方法を解説する。

集計表

クロス集計表 汚れたデータ

エクセルの表としては、大変結構なものであるが、経営ダッシュボードの作成(含むPowerBi)、ピボットテーブルの作成には使えないデータである。理由は3つ。

①表題にAlt+Enterでセル内で改行していること。

②ひまわり、東京、大阪でセル結合をしていること。

③スミレの後ろに空白文字が入っていること。

こういったデータを汚いデータと呼ぶ。これからこのデータを分析に使えるように、きれいなデータにしていく。

データクレンジング

表を選択する

Excelメニューのデータからテーブルまたは範囲からを左クリック

テーブルの範囲が正しく、先頭行を……にチェックを入れたらOK.を押す

パワークエリエディタが立ち上がり、表は以下のようになる。

セル結合が解除され、nullで置き換わる

nullを埋めるため、Shiftを押しながら左クリックで列の最後まで、つまり表全体を選択する。

エディタの変換のフィルを使って、上の文字列を下へコピーするので下へをクリックする

nullのところが上の文字列が入り表が出来上がる

表をクレンジングするため、ホームの1行目をヘッダーとして使用の右矢印をクリックし、上から2番目を左クリックする

この表ができたら、データのクレンジングを開始する

ホームの行の削除の矢印をクリックして空白行の削除を実行する

この表の場合、とくに空白行はないので、表は以前のままである。

エディタの変換をクリックして、書式の矢印をクリックする。

トリミングで空白文字を削除する

空白文字が取り除かれ、すべて左揃えになった

エディタの変換の書式の矢印をクリックして、クリーンをクリックする。

この状態でクリーンをクリックすると、改行文字が消去される。

表の1列目が2行だったのが1行になった

エディタのホームの1行目をヘッダーとして使用をクリックする。

1行目をヘッダーとして使用するをクリックする

表はこのような形になる

これでクレンジングは終了したので、クロス集計表(横持ちデータ)をリスト形式(縦持ちデータ)へ変換する。

ただし、この表はこれでリスト形式(縦持ちデータ)になっているので、リスト形式(縦持ちデータ)への変換は別の表を使う。

クロス集計表(横持ちデータ)をリスト形式(縦持ちデータ)へ変換

以下のクロス集計表(横持ちデータ)をリスト形式(縦持ちデータ)へ変換する。

データクレンジングの項で述べたように、パワークエリに取り込む

取り込み後の状態

ここで商品A、B、Cをリストの中にいれるため、支店の選択を解除して、商品A、B、Cを選択する。

ShiftキーまたはCtrlキーを押しながら商品を選択する

変換の列のピボット解除を押す

このような状態になったら属性を商品に変更する

属性の文字列をダブルクリックすると入力できるようになるので、商品にかえる。

このようになったら属性を商品にして、リターンキーを押す

属性が商品に代わった

以上でデータクレンジングとクロス集計表(横持ちデータ)をリスト形式(縦持ちデータ)に変換は終了となる。

サイトご利用方法

次のページ・前のページを利用するよりも、グローバルメニュー(ヘッダー部分にある項目)をクリックしていただければ、その項目の全体像が一目でみることができ、クリックすればそのサイトへ飛びます。

google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、グローバルメニューのどれかひとつを入力すると、その検索サイトが上位表示されます。