タグ別アーカイブ: データクリーニング

「例からの列」にて変換パターン自作~上級11回目~

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

今回は、新たなタイプの変換処理について解説します

これまでの変換処理は、予めメニュ―に設定された内容での変換処理でした

 今回紹介する変換処理は予め、ユーザー自身で変換パターン(及び作成パターン)を作成します

パターン作成➡パターン反映の流れ

 上のGIF画像では、左の2列から「例1」の列で3つの変換パターンが例示により作成されています

・「氏名」⇒氏名から「姓」を抜き取り

・「個数」⇒整数を抜き取り

・「姓」と「整数」から【「姓」さんは「整数」個販売】のテキスト作成

この機能を利用すれば、様々な処理に応用できそうですね!

この機能について、2回に分けて解説を行って行きます

まずは、今回の変換処理のポイントを2点だけ解説します

A.変換パターン作成及びパターン反映画面

「列の追加タブ」⇒「例からの列」メニューから処理を行いますが、通常の処理と画面構成が違います

新たな列が、テーブルから離れた場所に追加されてからの変換処理になります

B.例としてのパターン作成

 変換パターンの例示は、1度で済む場合と2度以上の例示が必要になるケースとに分かれます

 1度目に例示する場合も、2度目に例示する場合も、例示する場所はどこでも大丈夫です

 それでは、次の画像のデータをテーブル化し、Power Queryエディターで開いたところから解説を始めます

1.「例からの列」

 前述のように「列の追加タブ」の「例からの列」を指定するのですが、2種類の変換タイプを選択できます

 今回は、複数の列を扱うので「すべての列から」を選択して、すべての列を変換対象にします

すると前述のように、新たな列が画面右に設定されます

2.記号で文字を変更するパターン

ここから、具体的な処理の解説になります

下の図の「氏名」列から姓だけを切り取った列を作成します

右側に出来た「列1」の1番上に「吉永」と入力します

この時に、「吉永」と最初から入力する必要はありません

下の画像では分かりにくいですが、カーソルを当てると入力候補が出てきます

該当の箇所にカーソルを置き直し、エンターキーを押すと下の図のように反映されます

この時、既に列の上の名前が変わり、左の「氏名」の列の内容がコピーされています

では、上の画像の状態から名前を消去して変換パターンの例示を行うと、次の画像のように列全体に姓の文字だけが残ります

ちなみに、例示に基づいて変換が行われると「変換式」が左上に作成されます

では、逆に姓ではなく名前だけ残したら、この「変換式」はどうなるでしょう?

つまり、空白の後を切り取っている内容になっています

以降の解説でも、この変換式は活用します

では、変換が終わったので、元のテーブルに変換した内容を追加します

ショートカットキー:Ctrl+Enterで追加処理は行えます

<まとめ>

今回は、まずは記号で文字を変換するパターンを解説しました

 「列の追加タブ」から「例からの列」をクリックした後の画面が通常のPower Queryエディターの画面と違いました

 テーブルと離れた箇所に列が追加され、追加された列の上で変換処理(作成処理)を行いました

 変換処理(作成処理)が終了したら、ショートカットキー:Ctrl+Enterにて元のテーブルに新たに作成した列を追加しました

 では、次回は冒頭に解説したように、2度の変換パターンを例示するパターンも合わせて解説します

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


にほんブログ村

リストをUNIQUE関数でより便利に

予め用意したリストデータを基にして、簡単に選択が行えるようにするリスト機能はとても便利です

 ただ、リストデータが重複している時にはデータをクリーニングしなくてはなりません

リストにA,A,B,Bとかでたり、A,B,A,Bみたいに出たらおかしいですよね

そこが面倒なところです

 ところが、UNIQUE関数を組み合わせると、データクリーニングの必要はなくなります

以下、ポイントの解説です

1.UNIQUE関数

1つセルに入力すると複数の値を出力する新しいタイプの関数の一つです

指定した範囲のデータから重複を排除します

関数の指定範囲が可変になるため、事前に指定範囲をテーブル化しておくのがポイントです

2.出力データの再利用

新タイプの関数の特徴の1つに、出力データの再利用があります

=セル+#、これだけで再利用できます

今回のリストの範囲はこの仕組みを利用します

<まとめ>

1つのセルに入力すると複数の値を出力する新しいタイプの関数は、使い道がタップリです

様々な機能と組み合わせると、使い道はいくらでも広がります

ぜひ、色々と研究してみましょう!

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

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

にほんブログ村

セル分割他・変換処理~中級編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へ

にほんブログ村