こんにちは、Excellent仕事術のガッツ鶴岡です
これまで、セル結合を含むデータは自動で編集・加工ができず、手動で活用するしかないものだと思われてきました
実際、セル結合を含むデータは、ピボットテーブルに変換してのデータ抽出は行えません
何故なら、ピボットテーブルにするには、元データに見出しがあり、そして各行に見出しに応じた値や文字列が並んでいる必要があります
セル結合が行われいる箇所があると、見出しが欠けている箇所がでてきてしまいます
そして、セル結合している箇所を一括で修正する方法は無いのです
ところが、Power Query(パワークエリ）の登場により、セル結合を含むデータの有効活用が可能になりました
データの行列を入れ替えた上で、空白の箇所に対して、上下にあるデータを埋めるという作業がPower Queryだと簡単に行えるのです
今回はこの「セル結合を含むデータの有効化」については、2回にわたって解説を行います
もしかしたら、セル結合を含むデータを扱う機会はそんなに頻度がないかもしれません
ただ、今回の解説を通じて「データクレンジング」を行う力は格段に向上するはずですので、普段の業務でデータクレンジングをしている方にはお勧めの記事です！
今回の解説では、主に３つの新たなテクニックを使用します
記事の最後には限定公開の動画も添付しますので、参考にしてみてください！
目次
新たなテクニック
ⅰ）ヘッダーを1行目として使用
見出しに含まれているデータ行を、後で列に入れ替えできるように、見出しから外します
ⅱ）行列入れ替え
ⅰ）で見出しからデータの1行目に変換したデータ行を、更に列に入れ替えます
ⅲ）フィル
空白を上（下）にあるデータで埋めます（nullとは空白を意味します）
今回使用する３つのテクニック（ ヘッダーを1行目として使用 、 行列入れ替え 、 フィル ）を確認したところで、今回の解説で使用するデータの概要を確認しましょう！
四半期と記載された行の１Q、２Qの箇所にて、複数のセルがセル結合されています
こちらのデータを下のGIFのように、ピボットテーブルなどで有効活用できるような形に変換します！
上のデータのサンプルファイルは以下からダウンロードできます
ちなみに、上のGIFで見出しの部分にて１Q、２Qの横に「列１・・・」となっている箇所があるので、今回解説するデータとして紹介したデータと違うと感じる方がいらっしゃるかと思います
これは、普通の範囲を「テーブル」にした時に、自動で挿入されてしまう文字データになります
では、詳細な解説を始めたいと思います
１．Power Query Editorを開く
データタブの「テーブルまたは範囲から」をクリックして、Power Query Editorを開きます
２．ヘッダーを1行目として使用
下の画像のようにPower Query Editorが開いたら、変換タブに移動しましょう
変換タブにて「1行目をヘッダーとして使用」の横にある▼マークをクリックすると「ヘッダーを1行目として使用」が出てきます
こちらの「 ヘッダーを1行目として使用」 をクリックして下さい
すると、見出しにあったデータ（左から四半期ではじまる）がデータの1行目に移動します
この移動により３．の行列入れ替えにて、左から「四半期」からはじまるデータを列に入れ替えることができるようになります
３．行列入れ替え
では、２．と同じく変換タブにて「入れ替え」をクリックしましょう
すると、下の画像のように行列が入れ替えてあるはずです
上の画像の「四半期」の列には「１Q」と「２Q」の間に「列」からはじまる文字データが入っています
この「列」からはじまる文字データは空白にして、前述の「フィル」テクニックを活用できるようにします
空白への変換処理に当たっては「条件列」のテクニックを活用します
４．条件列の追加（空白への変換）
では「列の追加」タブに移動しましょう
こちらのタブの中から上の画像の「条件列」をクリックしましょう
こちらの機能にて「Qで終わるデータのみ出力を行う」という条件列の作成を行います
つまり、下の画像の「Q」で終わる文字データ以外は空白にした列を新規に作成します
条件列の追加画面では以下のように指定を行います
①新しい列名⇒四半期
②列名⇒Column1
③演算子⇒指定の値で終わる
④値⇒Q
⑤⇒Column1
ここで④⑤については注意点があります
④については、「Q」の文字については元のデータにあわせて「半角」「全角」を指定してください
⑤については、 値」を入力して指定する方法と「列の選択」を指定する方法があります
今回は「Column１」の内容で、Qで終わる場合にはそのまま列の情報を出力するので「列の選択」を指定します
上記のように条件列を指定すると、下の画像のような列がPower Query Editorの一番右に出力されいます
５．フィル（空白）を埋める
ちなみに上の画像のnullとは空白という意味です
このnullを前述した「フィル」機能で埋める作業を行います
まず、変換タブに移動します
フィルの文字の右横にある▼マークをクリックしてください
ここで上の文字を下方向に埋めるか、下の文字を上方向に埋めるかの選択を行えます
今回は、空白の文字の上にある「１Q」「２Q」を下の空白に埋めます
四半期の列にある空白が埋められたら、下の画像のColumn1はもう使用しませんので、削除しておきましょう
削除は、右クリックを行ってから「削除」を指定して行います
そして、条件列として出力した「四半期」の列は一番左に移動しておきましょう
＜まとめ＞
今回はセル結合を含むデータの有効活用する作業の1回目の解説でした
Power Query Editorに読込んだデータの行列を入れ替えた後に、セル結合によって空白になった部分を「フィル機能」によって埋める作業までを行いました
1回目だけでも様々なテクニックを解説しましたので、今回の内容をマスターすればデータクレンジングの実践的な力はかなり向上すると思います
ぜひサンプルファイルをもとにして、Power Query Editorでの変換作業に慣れていきましょう！
次回は、いよいよエクセルシートに変換したデータを読み込みます！
長文に最後までお付き合い頂き、誠にありがとうございました
YOUTUBEの限定公開の動画も添付しますので参考になれば幸いです！
コメントを残す