タグ別アーカイブ: セル結合

セル結合により見出しが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」の間に「列」からはじまる文字データが入っています

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

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

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

セル結合された表を有効活用できようにするため、前回行列の入れ替えとフィル機能を組み合わせて、データ変換を行いました

図にすると、下の図のようなイメージになります

セル結合で欠落した箇所を、上の文字で埋めた合わせた形になっています

今回は、更にデータの並びを縦横並びから縦縦並びに変えるピボット解除を組み合わせます

そして、セル結合された「見出しが不完全なデータ」を下のGIFのように見出しが整った形でエクセルシートに読込めるようにします

つまり、欠落したデータを埋めた上で、縦(1Q/4月、5月・)横(商品A、商品B・)の並びを下の図のように縦縦並びにして、値を1列に集約します

それではまず、前回の作業がどこで終了していたかを確認しましょう!

上の画像を確認してください

左から2列のColumn1は、元はセル結合したデータでした

ですので、Power Query Editorに読込んだ際に1Q、列1・・・と不規則なデータになってしまっていました

前回は、この不規則なデータを画像・一番左の列の形にしました

Column1については、前回で既に削除してあります

では、ここから今回の詳細な解説をはじめます

1.1行目をヘッダーとして使用

前回は、行列を入れ替えるために、あえてヘッダーの見出し行をデータの1行目に変換しておきました

1番左の列の見出しは前回、手動で四半期と入力しています

今回は逆に、データの1行目をヘッダーに変換して見出し行を整えます

ちなみに、上の画像の一番左の列のデータ1行目は前回、条件列を出力した時に「Qで終わらないものは空白」で出力した時に空白になってしまっています

では、変換タブに移動し「 1行目をヘッダーとして使用 」をクリックしましょう!

はい、これで見出しが整ったはずなのですがここで問題が1つあります

データの1行目を見出しに持ってきた時に、下の画面のように2つの自動変換が起こっています

一番左の列の見出しが、前述のように元は空白だったので四半期からColumn1に変わってしまっています

そして、月の列のデータの中味も日付形式に変わってしまっています

適用ステップの欄を見てみると、型の変更が追加されているがよくわかります

こちらの2点は、以下の様に変更を行いましょう!

①Column1の名称→四半期に変更

直接、列の名称を変更しましょう

②日付形式→文字型式に変換

こちらは、変更が行われたステップを削除するか、型式の変更を行いましょう

ちなみに、型式の変更で行う場合には、こちらから行えます

2.ピボット解除

それでは前述の縦横並びを、縦縦並びにかえます

四半期と月の列は縦縦なので、こちらの2つの列をカーソルで選択しておき、右クリックしてください

ちなみに複数の列を選択する時には、Shiftキーを押したままで列の選択を行ってください

上の画像のように、右クリックをすると「その他の列のピボット解除」が選択できますので、こちらをクリックしてください

ピボット解除

これで、縦横並びが縦縦並びに変わりました

3.エクセルシートへの読み込み

本題に入る前に、商品の列の名称が「属性」となっているので「商品」に変えておきましょう

これで、エクセルシートに読込む準備が整いましたが、読込先はテーブルにしても、ピボットテーブルにしてもOKです

テーブルで読み込んで、後でピボットテーブルにする方法もあります

では、解説としては一旦はテーブルで読込ます

そして、こちらのデータをピボットテーブルに変えます

これで、元々はセル結合されていて編集・加工が困難だったデータがピボットテーブルにて様々な形式で集計できるようなりました

<まとめ>

2回にわたり、セル結合を含むデータを有効活用する方法を解説しました

1回目では主に以下、3つのテクニックを解説しました

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

ⅱ)行列入れ替え

ⅲ)フィル

2回目の今回は、ピボット解除により「四半期と月が縦」に、そして、「商品が横に並んでいたデータ」を縦と縦の並びに変えました

データが縦と縦に並んだ、見出しが整ったデータすることにより、ピボットテーブルで有効にデータ活用ができる形式になりました

今回の2回の解説は「行列の入れ替え」「ピポット解除」どちらも行っています

この2つの違いを明確に区別して処理が行えるようになると、パワークエリでデータクレンジングする力が飛躍的に向上します

ぜひ、実際のデータで2回の内容にガッツで取り組んでみてください

では今回の解説は以上です

長文に最後までお付き合い頂き、誠にありがとうございました

次回は四則演算から四捨五入まで、様々な集計方法を解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村