タグ別アーカイブ: 複数セル

セル分割他・変換処理~中級編7回目

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

読者の皆さんは、他の方が作成した表を見て「使いにくい」と思ったことはありませんか?

例えば、下の図のような表です

1セルの中に複数のデータがカンマ” , ”を区切りにして混在しています

しかも、担当者が縦への繰返しで並んでいるのに、担当企業の欄は横に並んでいます

この表を他の目的に活用しようと思ったら、下の図のような「縦並びが複数列で繰り返された表」に変換するしかありません

この変換処理は手で行ったとしたら莫大な時間がかかります

ところが、Power Queryならば一括で変換できてしまうのです!

今回は上の表の変換を事例として、Power Queryを活用したデータクリーニング技術について、2回に分けて解説します

<今回のポイント>

A.セル分割

このセル分割によるデータ変換が今回のメインの解説になります

区切り文字を指定し、分割の仕方を指定するのがポイントです

B.ピボット解除

ピボット解除については前回も解説しました

この機能はとても便利です

今回も横並びを縦並びにするのに活用します

では、演習ファイルをダウンロードして頂いたら解説をはじめます!

今回は、セルの分割までです

アイコン

中級編7回目_演習 17.28 KB 42 downloads

...

1.セル分割

①Power Query Editorを開く

問題となる表の上にカーソルを置き、”テーブルまたは範囲から”をクリックし、Power Query Editorを開く

➁列の分割をクリック

Power Query Editorが開いたら、担当企業を選択した上で、変換タブから”列の分割”の▼マークをクリックします

③区切り記号と分割方法を指定

列の分割の▼マークをクリックすると、”区切り記号による分割”が選択できるようになりますので、またこの文字をクリックします

すると次のような画面が開きます

さて、今回のキーポイントに入る前に、元の表を再度確認しましょう!

カンマ” , ”が不規則に出現し、複数のデータが1セル内に混在しています

このセルを、区切り文字のカンマが出現するごとにデータ分割すればいいのです

ですから、区切り記号は”カンマ”を指定し、分割方法はデフォルトの「区切り記号の出現ごと」のままにし、右画面下のOKボタンを押します

すると、1セルの内容がカンマ” , ”ごとに複数のセルに分割されます!

<まとめ>

今回は、カンマ” , ”で区切られた使いにくい表をPower Query Editor上で分割まで行いました

今までのエクセル上でも同じようなことはできましたが、Power Queryではもっとシンプルに行えるようになっています

次回は使える表への変換をガッツで完成しましょう!

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


にほんブログ村

セル分割他・変換処理2~中級編8回目

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

前回はカンマ” , ”で区切られた「使いにくい表」をPower Query Editor上で「使いやすい形」になるように行方向に分割しました

このままだと、行別にみると2列の行があったり、5列以上の行あったり不規則で使いにくい表のままです

今回はPower Query Editor上で行方向に広がった列の並びを、縦の並びに変換して1列にし「使いやすい表」に変換します!

そして、前回使用したセル分割のテクニックを再び使い、連番までつけてしまいます!

前回の冒頭で1.セル分割2.ピボット解除を解説すると案内しました

前回1.セル分割を行ったので2.以降から解説を開始します

2.ピボット解除

コンマ” , ”で区切られたデータが混在したセルを分割した後は、データは横並びになっています

この並びを縦並びにして1列にします

この並び替えにはピボット解除の技術を使います

①担当者の列を選択

担当者の列をカーソルで選択し、右クリックします

➁その他の列のピボット解除

担当者以外の列が並び替えの対象です

その他の列のピボット解除をクリックします!

すると、並び順が縦の並びの繰返しに変わります

3.読込

いよいよ2.で変換した内容をシートに読込みます

その前に若干、修正を加えましょう!

①再分割

ピボット解除をした時、新たに属性の列ができています

この列を更に2列に分割し、

⇒属性1の列は削除

⇒属性2の列は”NO”として残しましょう

今回の分割処理はピリオド”.”を区切り記号に指定して行います

2列に分割された属性1の列は削除し、

属性2の列は列の名前を変えましょう!

➁クエリ名前の変更

クエリの名前は担当企業としましょう!

③既存シートへの読込

①②が終わったところで読込を行います

今回は読込先を指定するので「閉じて読み込む」の右下▼から「閉じて次に読み込む」をクリックします

更にデータのインポート画面で

既存のワークシートを選択し、読み込むセル位置をE2セルに指定します

これで使いにくい表を使いやすくする表にする処理は完成です!

<まとめ>

今回は”,”や”.”で区切られたセルを分割することで表を使いやすくする技術を学びました

従来のエクセル手法であれば、この分割処理を行うのにワークシート上で大量の手作業が発生していました

Power Queryであれば一括で行えるのでとても便利です!

参考までに今回の解説に使用したサンプルデータを参考までに添付します

では次回はもっとPower Queryを実践的に使いこなせるようになるため、「やり直し」の方法についてガッツで解説します

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村