お役立ちコラム
東京パソコンアカデミーのお役立ちコラムです。
東京パソコンアカデミーのお役立ちコラムです。
目次
パワークエリは「表の整形」に最適なツールです。データベース形式に整えられた表は、並べ替えや抽出などの作業が容易にできます。しかし、すべての表がデータベース形式になっているわけではありません。例えば、表内のセルが結合されていると、正確な並べ替えや抽出が難しくなってしまいます。
今回は、こうした未整形の表をパワークエリで適切な形に整える方法をいくつかご紹介します。
2024年11月のコラム「データクレンジングにパワークエリを使ってみよう」では、データを整える操作について解説しましたが、データを正しく扱うためには、まず表を適切な構造にすることが重要です。
表を整えて、並べ替えや抽出をスムーズに行いたい方に向けて、今回はその手順を説明します。
それでは、まずパワークエリとは何かを解説します。
パワークエリはＥＴＬツールの１つです。データ処理の一環で「Extract(抽出) Transform(変換) Load(取り込み)」の頭文字を集めた略号です。
「様々なところに散らばった情報を集めて、必要な形に整え、共通の場所(Excelブックの中)に取り込む」ということができます。
ETLで整えられたデータは以下のように多岐にわたる用途で活用することができます。
・ERP（基幹システム）など他のシステムへのインポート
・データのパターンやトレンドを分析し、ビジネスインサイトに利用
・ExcelやPower BIにインポートして視覚的にわかりやすいレポートの作成
・ピボットテーブルの作成 など
パワークエリはExcelのアドインとして提供されていましたが、現在はExcelに「データの取得と変換」として標準搭載されました。何かを別に購入する必要がないので、始めやすいのもいいですね。
Microsoftが開発したデータ処理ツールなので、ExcelやPower BIに利用できるのも魅力です。
それではさっそく表を整えるいくつかの操作をお伝えします。
セルの結合を解除する
データベースとして表を使用する場合、各行に1件のデータが入力されていることが重要です。しかし、セルが結合されていると、並べ替えや抽出などの操作を適切に行うことができません。そのため、セルの結合を解除し、表を正しい形式へ整えていきます。
データを取り込みます。
表内任意のセルを選択し、データタブ→データの取得と変換グループ→テーブルまたは範囲からをクリック。
表のセル範囲が正しく認識されていることと、先頭行のテーブルの見出しとして使用するにチェックが入っていることを確認し、OKをクリック。
ExcelのデータがPower Queryエディターで開きます。
ここで、すでに結合セルが解除されています。実は、Power Queryエディターに取り込むと自動的に結合セルが解除され、空欄に「null」が表示されます。
「null」と表示されている２列分を選択。（複数選択の場合は、2列目以降をShiftキーを押しながらクリック。）
変換タブ→任意の列グループ→フィル→下へをクリック。
「null」のセルがその上に入力されていたデータで埋まります。
ホームタブ→閉じるグループ→閉じて読み込むをクリック。
新しいシートに結合セルがない状態でテーブルが作成されました。
これであれば、正しく並べ替えや抽出が行えるようになります。
セルを分割する
1つのセルに複数の情報が含まれている場合、それを個別の列に分割して表示することができます。
この機能を活用すれば、後からデータを整理・調整する際にも便利に使えます。
ここでは、列「バリエーション」のセルの中に、2つの情報が入っている状態です。これを別の行に分割していきます。
データを取り込みます。
表内任意のセルを選択し、データタブ→データの取得と変換グループ→テーブルまたは範囲からをクリック。
表のセル範囲が正しく認識されていることと、先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認し、OKをクリック。
分割したいデータのある列を選択。（ここでは列「バリエーション」を選択。）
変換タブ→テキストの列グループ→列の分割→区切り記号による分割をクリック。
区切り記号による列の分割ダイアログボックスで、区切り記号を選択し（ここではコンマ）、行に分割をするので詳細オプションの分割の方向の「行」を選択してOKをクリック。
列のデータが行方向に分割されて表示される。
ホームタブ→閉じるグループ→閉じて読み込むをクリック
これで、列「バリエーション」の複数のデータが含まれていたセルが個別の行に分かれ、並べ替えや抽出が容易になりました。データベースを扱う際は、「1つのセルには1つのデータ、1行には1件のデータ」という構造にすると、管理しやすくなります。
表の縦横を組み替える
データベースとして表を活用する際、表の構造が統一されていないと、正確な並べ替えや抽出ができません。ここでは、横方向に配置されたデータを縦方向に変更し、整えられた形式へ変換する手順を紹介します。
表内任意のセルを選択し、データタブ→データの取得と変換グループ→テーブルまたは範囲からをクリック。
列「カテゴリー」と「商品名」を選択している状態で、
変換タブ→任意の列グループ→列のピボット解除の▼をクリック→その他の列のピボット解除をクリック。
※「列のピボット解除」とは、指定した列を縦に並べ替え、データベース形式に変換する機能です。
列名の「商品価格」「ペアセット価格」「ファミリーセット価格」が新たな縦1列に並びました。
項目名は「属性」「値」と自動でつきますが、後から項目名の変更も可能です。
この構成なら、同じ列に同種のデータが統一されるため、複数の値の列を用意する必要がなくなり、並べ替えや抽出の操作がより簡単になります。
いかがでしたでしょうか。
パワークエリを活用すると、結合されたセルの解除やデータの分割、縦横の構造変換など、表を整える作業を効率的に行うことができます。データを正しく整理すれば、並べ替えや抽出が容易になり、より活用しやすいデータベースを作ることが可能です。
表の構造を適切に整え、データをスムーズに扱えるようにすることで、業務の効率化や分析の精度向上につながります。ぜひ、パワークエリを活用して、データ整理を快適に進めてみてください！
石田
前回の記事はこちら
データクレンジングにパワークエリを使ってみよう
お近くの教室で無料体験レッスンを受け付けております。
ぜひお気軽にお申し込みください。
