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

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

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

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

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

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

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

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

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

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

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

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

記事の最後には限定公開の動画も添付しますので、参考にしてみてください！

目次

新たなテクニック

１．Power Query Editorを開く

２．ヘッダーを1行目として使用

３．行列入れ替え

４．条件列の追加（空白への変換）

５．フィル（空白）を埋める

＜まとめ＞

新たなテクニック

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

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

ⅱ）行列入れ替え

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

ⅲ）フィル

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

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

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

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

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

上級2回目サンプルファイル

1 ファイル 10.36 KB

ダウンロード

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

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

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

１．Power Query Editorを開く

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

２．ヘッダーを1行目として使用

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

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

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

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

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

続きを読む 見出しがセル結合により2行になってしまっている表をデータ活用１～上級編2回目～ →

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

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

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

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

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

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

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

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

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

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

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

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

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

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

１．1行目をヘッダーとして使用

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

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

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

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

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

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

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

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

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

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

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

①Column１の名称→四半期に変更

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

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

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

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

２．ピボット解除

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

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

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

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

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

３．エクセルシートへの読み込み

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

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

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

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

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

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

＜まとめ＞

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

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

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

ⅱ）行列入れ替え

ⅲ）フィル

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

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

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

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

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

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

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

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

にほんブログ村

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

　今回は、上級編の5回目で事前にアナウンスしたようにPower Queryのエラーのうち、「データ自体」のエラーの修正方法について解説します

　解説に使うデータは5回目と同じく、中級編の2回目と同じデータを使用します

　中級編の1回目では上の２つの表を組み合わせて、下のテーブルを作成していました！

商品台帳の販売単価と売上台帳の販売個数を乗じて、「売上金額」を新たな列として追加してあります

目次

１．エラー内容の確認

２．修正作業

＜まとめ＞

１．エラー内容の確認

下のクエリのようにエラーが発見された、というところから解説をスタートします

次に、上の画像の「１つのエラーです。」をクリックします

　すると、Power Queryエディターが開き、下の画像のようにエラー箇所が表示されます

今回のエラーは、販売単価と売上金額にてエラーが発生しています

　売上金額は販売個数と「商品台帳.販売単価」を乗じているので、大元の商品台帳のクエリにて「データ自体」の「狂い」生じている可能性があります

　ですので、エラーが発生している可能性があるクエリの中味を確認するため、エディターの矢印マークをクリックします

　すると、下の図のようにクエリーの一覧が表示されます

では、「狂い」生じている可能性がある商品台帳クエリーをクリックします

すると上の画像のようにやはり「狂い」が生じている箇所がありました

では、「Error」の箇所をクリックしてみます

すると、やはり本来は数字が入っているべきところに「!!!」が入っていました

　エラーの中味を確認したら、画面右の「適用したステップ」に追加されたステップを削除して元の画面に戻ります

エラーの箇所が特定できたところで、ここから２つの修正方法を解説します

続きを読む エラー修正_データ自体のエラー～上級編7回目 →