タグ別アーカイブ: フィル

セル結合により見出しが2行の表をデータ活用1~上級編2回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

これまで、セル結合を含むデータは自動で編集・加工ができず、手動で活用するしかないものだと思われてきました

実際、セル結合を含むデータは、ピボットテーブルに変換してのデータ抽出は行えません

何故なら、ピボットテーブルにするには、元データに見出しがあり、そして各行に見出しに応じた値や文字列が並んでいる必要があります

セル結合が行われいる箇所があると、見出しが欠けている箇所がでてきてしまいます

そして、セル結合している箇所を一括で修正する方法は無いのです

ところが、Power Query(パワークエリ)の登場により、セル結合を含むデータの有効活用が可能になりました

データの行列を入れ替えた上で、空白の箇所に対して、上下にあるデータを埋めるという作業がPower Queryだと簡単に行えるのです

今回はこの「セル結合を含むデータの有効化」については、2回にわたって解説を行います

もしかしたら、セル結合を含むデータを扱う機会はそんなに頻度がないかもしれません

ただ、今回の解説を通じて「データクレンジング」を行う力は格段に向上するはずですので、普段の業務でデータクレンジングをしている方にはお勧めです!

今回の解説では、主に3つ新たなテクニックを使用します

目次

新たなテクニック

1.Power Query Editorを開く

2.ヘッダーを1行目として使用

3.行列入れ替え

4.条件列の追加(空白への変換)

5.フィル(空白)を埋める

<まとめ>

新たなテクニック

ⅰ)ヘッダーを1行目として使用

見出しに含まれているデータ行を、後で列に入れ替えできるように、見出しから外します

ⅱ)行列入れ替え

ⅰ)で見出しからデータの1行目に変換したデータ行を、更に列に入れ替えます

ⅲ)フィル

空白を上(下)にあるデータで埋めます(nullとは空白を意味します)

今回使用する3つのテクニック( ヘッダーを1行目として使用 、 行列入れ替え 、 フィル )を確認したところで、今回の解説で使用するデータの概要を確認しましょう!

四半期と記載された行の1Q、2Qの箇所にて、複数のセルがセル結合されています

こちらのデータを下のGIFのように、ピボットテーブルなどで有効活用できるような形に変換します!

上のデータのサンプルファイルは以下からダウンロードできます

ちなみに、上のGIFで見出しの部分にて1Q、2Qの横に「列1・・・」となっている箇所があるので、今回解説するデータとして紹介したデータと違うと感じる方がいらっしゃるかと思います

これは、普通の範囲を「テーブル」にした時に、自動で挿入されてしまう文字データになります

では、詳細な解説を始めたいと思います

1.Power Query Editorを開く

データタブの「テーブルまたは範囲から」をクリックして、Power Query Editorを開きます

2.ヘッダーを1行目として使用

下の画像のようにPower Query Editorが開いたら、変換タブに移動しましょう

変換タブにて「1行目をヘッダーとして使用」の横にある▼マークをクリックすると「ヘッダーを1行目として使用」が出てきます

こちらの「 ヘッダーを1行目として使用」 をクリックして下さい

すると、見出しにあったデータ(左から四半期ではじまる)がデータの1行目に移動します

この移動により3.の行列入れ替えにて、左から「四半期」からはじまるデータを列に入れ替えることができるようになります

3.行列入れ替え

では、2.と同じく変換タブにて「入れ替え」をクリックしましょう

すると、下の画像のように行列が入れ替えてあるはずです

上の画像の「四半期」の列には「1Q」と「2Q」の間に「列」からはじまる文字データが入っています

以降は有料ページ/課金サービスへ