タグ別アーカイブ: データ成型

「例からの列」による桁数が規則列への対応~上級12回目~

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

前回は「例からの列」のメニューを使用して、変換パターンを1つ自作しました

 こちらは1つ例を示して、氏名から「姓」だけ、もしくは「名前」だけを分解する変換パターンでした

下の画像が、変換前の氏名でした

そして「例からの列」機能を使い、上の画像を下の様に分解しました

 では、今回は変換内容の2つの例示を通じ、作成したい変換パターンをPower Queryに汲み取らせた上で、変換パターンを確定する方法を紹介します

この「2つの例示を行うパターン」が使いこなせるようになると、列のデータが不規則でも、ノーコードにてデータ成型が一括で行えるようになります

解説に使うデータは前回と同じです

1.桁数が不規則な列を揃える

今回変換するのは、下の画像の「部門」の列です

こちらは部門コードが4~5桁になっており、桁数が不揃いになっています

この部門コードを3桁で統一します

まずは、例示の1回目を行います

1行目の「ABC1」を「ABC」にて例示すると下の図のようになります

やりたいことは列全体を3桁に揃えることでしたが、3桁になったのは例示した行も含めて2行だけでした

・ABC1 ➡ABC

・ABC10 ➡ABC

他の行は次のような状態です(一部省略)

・BBC2 ➡BBC2

・ABB33➡ABB33

全く変化が起こっていませんので、画面左上の変換式をチェックしてみます

要は「1」の前の文字を切り取る変換式が出来ていました

では、次に下のGIF画像のように「1」を含まない行にて、2つ目の例示を行いましょう

2つ目の例示を追加したら、全て3桁に揃いました

では、変換式を確認してみましょう!

「Text.Start([部門],3)」となっています

こちらは、「部門」の列の各行の文字から3文字を左から切り取ったという意味になります

つまり、2つの例示を通じて「3桁」にしたいという意図をPower Queryが汲み取った形になっています

2.計算

あまり使う機会がないかもしれませんが、「列」と「列」の計算もこの「例からの列」で行えます

「単価」と「個数」を乗じた計算結果を2つ例示してみます

今回は、1行目と2行の計算結果、1800x 7 = 「12600」と3800 x 3 = 「11400」を入力してみます

すると、2つの計算結果を汲み取ってくれて、列の全行に計算結果を入力してくれます

<まとめ>

 今回は複数の例示から、変換パターンを汲み取らせて変換するパターンを行いました

 前回の冒頭にも紹介しましたが、複数の列を組み合わせて「文章」も作成することもできます

 但し、大量のデータがある場合には、変換ミスが起こっていても気づかないケースもありうります

なるべく複雑な変換は行わないようにしましょう!

そして、画面左にある「変換式」はなるべくチェックするようにしましょう!

最後に、参考までに今回の解説で使用したデータと完成版を添付します!

次回は、エディタを開かないまま「データソース」を変更する処理を解説します

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


にほんブログ村

【裏技】1列のデータを複数列に展開する

今回は、エクセルの特質を利用した裏技を紹介します!

下の図のように、1列にデータが規則的に並んでいるとします

この1列のデータを見やすいように複数列に展開します

この展開作業には関数やマクロは使用しません。エクセルの特徴を利用するだけです

1.セルの位置を2行だけ展開する

セル位置を、見出しの下に2行だけ記入していきます

「=」から始まる数式で記入しないのがポイントです

2.複数行をカーソルで指定して下にドラッグする

1.でセル位置を記入した2行をドラッグし、下にドラッグします

実は、この時点できちんと各セルの「セル位置」の指定がパターン化されています

3.「セル位置」を「=」で置換

ショートカットキー:Ctrl+Fにて置換え画面を立ち上げましょう!

置換を次のように実行しましょう

「セル位置」⇒「=」

すると2でパターン化された「セル位置」の指定に従って数式が展開されます

ちなみに、指定範囲の書式は「#,###」で指定してあります

この指定を行っておかないと、空欄を指定した数式が0を返します

試しに、参照もとの1列にデータを追加してみましょう!

0ではなく、追加された値を反映します

<まとめ>

 今回は、文字列をパターン化してコピーしたものを、後から=で置き換えることとにより、1列のデータを規則正しく複数列に展開しました

 日常業務で使う場面が多いテクニックだとは思いませんが、「エクセルは工夫次第で様々なことができる」と感じて頂ければ幸いです

長文を最後まで読んで頂きまことにありがとうございました

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

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

にほんブログ村

ピボット解除/縦横並びの変換処理~中級編6回目

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

前回までは複数のクエリ(データ)を組み合わせる技術を解説しました

今回からは、Power Query Editor上でデータを変換する技術を解説していきます

ところで、題名にあるPivotはどう意 味かご存知でしょうか?

ちなみに辞書には軸とか、旋回と書いてありました

この回を終了した時には、”Pivot”の意味が納得できるようになっていると思います

<今回のポイント>

A.集計の目的とデータの並べ方の関係

下の2つのデータを見比べてください

①は縦に繰り返し並んでいます。一方、②は、①の2つの目の列が横に“旋回”して並んでいます

どちらが集計しやすいと思いますでしょうか?

全体の合計値を出す場合、①は1列を範囲にしてSUM関数を入力すれば算出できます

一方、②は見出し以外の全ての行列を範囲に含めてSUM関数を入力するしかありません

①と②のデータで「アルファベット」と「ひらがな」別、つまり2つの切り口を組み合わせて合計値を集計する場合も考えてみましょう

①はフィルターをかけて、ひらがなの列だけみれば合計値を集計できます

②の場合には、目で列を追いかけながら集計するしかありません

ですので、「データはあらゆる切り口でデータ集計が行えるようにする」、ということであれば、縦方向に繰り返し並べていくべきなのです

B.Power Query上での操作

Power Queryでは、Power Query Editor上で、横に旋回しているデータを縦の並びに切り替え、集計を行い易い形にてデータを出力し直すことができます

その際、旋回させる軸を決めるのと同時に、以下のサンプルデータの合計の行列(Total)を削除します

この合計の値を消して、その後はどのようにに合計値を算出していくかについては後述します

では、実際のサンプルデータを基にして解説をはじめます

1.Power Query Editorを立ち上げる

データタブの「テーブルまたは範囲から」をクリックし、Power Query Editorを立ち上げます

この際、8行目のTotalの範囲が入るように気を付けましょう!

こちらは前述のように、Power Query Editor上で削除を行います

2.合計行列を消去する

Power Query Editorが開いたら次の①②の処理を行い、「 B.Power Query上での操作」で前述したようにTotalの行列を削除します

①フィルターからTotal行を消去する

-フィルターをクリックします

-チェックを外して一覧からTotalを消します

➁Total列を消去する

Total列を指定し、削除をクリックします

3.Pivot解除

ではPivot解除機能により、に旋回しているデータをの並びに切り替えます

①軸を指定します

今回は、に展開している日付を並びにします

ですので、一番左にあるカテゴリー列を左クリックして指定します

その後、右クリックすると可能な処理の一覧がでてきます

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

①の一覧から「その他の列のピボット解除」をクリックします

これで、横に展開していたデータが縦並びになりました

4.読込先をピボットテーブルに指定する

Power Query Editorで縦並びにしたデータを「ピボットテーブル」としてエクセルシート上に読込みます

①閉じて次に読みこむを指定

ホームタブにて「閉じて読み込む」の右横▼マークから「閉じて次に読み込む」を指定します

➁ピボットテーブルの読込先を指定

ー「既存のワークシート」内の「元のデータの下」A11セルを指定しましょう!

-ピボットテーブルのフィールドを以下のように指定しましょう!

Power Query Editor上でTotalの行列は削除しましたが、ピボットテーブルでTotal(合計)が集計できるようになりました

逆に、もしPower Query Editor上でTotal行列を削除しなかったら、ピボットテーブル上で合計値が本来の値より2倍になってしまいます

<まとめ>

 このPivot解除を知っておけば、他の方から入手したデータが、横並びになっていなかったとしても、縦の並びに簡単に変えることができます

 データを縦の並びに変えておけば、様々な切り口でデータ集計がおこなえるようになりますので、ぜひ有効活用していきましょう!

 次回はセルの中のデータを複数列に分解する方法と今回解説したピボット解除を組み合わせた変換処理を行います

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

にほんブログ村