カテゴリー別アーカイブ: 上級

行削除とヘッダー行の調整~上級編1回目~

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

 Power Queryを使用していると、無駄な行を削除したり、ヘッダー行を整えるなどの行の調整が必要になるケースがあります

 特に、PDFWEBからデータを取得した際や、エディター画面で行列を入れ替えた際に、行の調整が必要になることが多いです

行の調整が必要となるケースを、実際の画像で2点ほど紹介します

 次の画像は、PDFファイルからデータを取得した際、テーブルデータの上に「無駄な行」が実際に入っていた時の画像です

黄色の印をつけた3行は削除する必要があります 

 次に、2点目の事例ですが、下の画像のように、データを取得した際に「見出し行」の位置がずれていたりすることもあります

 本来は、黄色の印をつけた行に「前期」「今期」「前期比」の行がなくてはなりません 

 この回では前述した2点のようなケースに対応できるように、取得したデータの行削除を行ったり、見出し行を整えるテクニックを解説します

 この行調整のテクニックを取得することで、より実践的にPower Queryを活用できるようになります

尚、サンプルデータには、次のファイルデータの2ページ目を使用します

アイコン

サンプルPDFファイル 49.80 KB 475 downloads

...

データを取得

「データの取得」から「ファイルから」⇒「PDFから」を指定します

次に開いた画面で、該当のPDFファイルを指定します

PDFファイル指定

PDF内の取得データを指定

前述の1.を行うと、次のようなナビゲーション画面が開きます

ナビゲーション画面

今回の解説では「Page002」を指定して、ナビゲーション画面下の「データの変換」ボタンを押します

行の削除

今回は、テーブルデータの上、3行を削除するので、ホームタブの「行の削除」から「上位の行の削除」を実行します

「上位の行の削除」をクリックすると、削除する行数を指定する画面が出てきます

上位の行の削除

ここで「3」と入力してOKボタンを押すと、テーブルデータの上3行が削除されています

見出し行の調整

3.の上位3行を削除した状態だと、エクセルに読込んだ際は前述のように見出し行が抜けた状態になります

ですので、「変換」タブに移動し「1行目をヘッダーとして使用」を実行します

実行すれば、下のGIFのように1行目が見出しに設定されます

ここまで行調整したデータをエクセルシート上に読込みを行うと、次の画像のように、きちんと見出しが設定されています

<まとめ>

 今回はPower Queryでデータを取得した際に、エディター画面で行調整する方法を2つ解説しました

1つ目は、行数を指定して行を削除する方法

2つ目は、データの1行目を見出しとして設定する方法

以上の2つを習得すれば、より実践的にPower Queryを活用できるようになります

ではまた次回、ガッツで頑張りましょう!

次回はPower Queryを本領を発揮したテーマです

 Power Queryのテクニックを複数駆使して、セル結合を含む表を有効活用できるようにします

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


にほんブログ村

セル結合により見出しが2行の表をデータ活用1~上級編2回目~

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

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

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

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

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

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

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

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

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

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

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

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

目次

新たなテクニック

1.Power Query Editorを開く

2.ヘッダーを1行目として使用

3.行列入れ替え

4.条件列の追加(空白への変換)

5.フィル(空白)を埋める

<まとめ>

新たなテクニック

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

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

ⅱ)行列入れ替え

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

ⅲ)フィル

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

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

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

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

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

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

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

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

1.Power Query Editorを開く

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

2.ヘッダーを1行目として使用

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

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

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

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

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

3.行列入れ替え

では、2.と同じく変換タブにて「入れ替え」をクリックしましょう

すると、下の画像のように行列が入れ替えてあるはずです

上の画像の「四半期」の列には「1Q」と「2Q」の間に「列」からはじまる文字データが入っています

この「列」からはじまる文字データは空白にして、前述の「フィル」テクニックを活用できるようにします

空白への変換処理に当たっては「条件列」のテクニックを活用します

4.条件列の追加(空白への変換)

では「列の追加」タブに移動しましょう

こちらのタブの中から上の画像の「条件列」をクリックしましょう

こちらの機能にて「Qで終わるデータのみ出力を行う」という条件列の作成を行います

つまり、下の画像の「Q」で終わる文字データ以外は空白にした列を新規に作成します

条件列の追加画面では以下のように指定を行います

①新しい列名⇒四半期

②列名⇒Column1

③演算子⇒指定の値で終わる

④値⇒Q

⑤⇒Column1

ここで④⑤については注意点があります

④については、「Q」の文字については元のデータにあわせて「半角」「全角」を指定してください

⑤については、 値」を入力して指定する方法と「列の選択」を指定する方法があります

今回は「Column1」の内容で、Qで終わる場合にはそのまま列の情報を出力するので「列の選択」を指定します

上記のように条件列を指定すると、下の画像のような列がPower Query Editorの一番右に出力されいます

5.フィル(空白)を埋める

ちなみに上の画像のnullとは空白という意味です

このnullを前述した「フィル」機能で埋める作業を行います

まず、変換タブに移動します

フィルの文字の右横にある▼マークをクリックしてください

ここで上の文字を下方向に埋めるか、下の文字を上方向に埋めるかの選択を行えます

今回は、空白の文字の上にある「1Q」「2Q」を下の空白に埋めます

四半期の列にある空白が埋められたら、下の画像のColumn1はもう使用しませんので、削除しておきましょう

削除は、右クリックを行ってから「削除」を指定して行います

そして、条件列として出力した「四半期」の列は一番左に移動しておきましょう

<まとめ>

今回はセル結合を含むデータの有効活用する作業の1回目の解説でした

Power Query Editorに読込んだデータの行列を入れ替えた後に、セル結合によって空白になった部分を「フィル機能」によって埋める作業までを行いました

1回目だけでも様々なテクニックを解説しましたので、今回の内容をマスターすればデータクレンジングの実践的な力はかなり向上すると思います

ぜひサンプルファイルをもとにして、Power Query Editorでの変換作業に慣れていきましょう!

次回は、いよいよエクセルシートに変換したデータを読み込みます!

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

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

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

にほんブログ村

セル結合により見出しが2行の表をデータ活用2~上級編3回目~

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.1行目をヘッダーとして使用

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

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

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

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

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

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

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

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

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

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

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

①Column1の名称→四半期に変更

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

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

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

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

2.ピボット解除

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

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

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

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

ピボット解除

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

3.エクセルシートへの読み込み

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

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

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

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

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

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

<まとめ>

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

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

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

ⅱ)行列入れ替え

ⅲ)フィル

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

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

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

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

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

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

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

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

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

にほんブログ村

各種集計/合計から四捨五入まで~上級編4回目

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

エクセルは表計算ソフトなので、エクセル作業に集計処理はつきものです

必要な関数を調べて入力したり、ピボットテーブルにして処理したりする作業はちょっとガッツが必要なものです

Power Queryでは、様々な切り口による集計処理を直感的なクリック操作で行えるのが、大きなメリットです

Power Queryエディター内の「変換タブ」と「列の追加タブ」では、集計処理のためのメニューが豊富に揃っています

集計処理メニュー

今回の解説では、上の画像の左のメニューから順にポイントを絞って解説します

尚、三角関数と情報メニューについては、解説を今回は割愛させて頂きます(指数メニューについても概要だけの解説になります)

今回、解説する上で分かりにくい点が1点あります

変換タブ列の追加タブには、統計以外は同じメニューがあります

違う点は、集計した列を追加するかどうかだけです

今回の解説では、極力、変換タブに絞って解説を行います

目次

1.統計処理メニュー

2.標準メニュー

3.指数メニュー

4.数字を丸める

<まとめ>

1.統計処理メニュー

こちらのメニューは列単位での集計処理メニューになります

「列単位での集計処理」という意味は、下の画像を例にとれば「販売個数の列」を行方向に一括で集計するという意味です

ですから、集計した値は1つだけ出力されます

統計ボタンの右下にある▼マークをクリックすると、様々な集計切り口が出てきます

統計

今回は、合計だけ出力してみます

集計する列をカーソルで指定し「合計」を押します

合計処理

すると、列にある値を合計した値「1011」が出力されました

ちなみに、Power Queryコラムでも書きましたが、こちらの統計処理メニューには、「個別の値のアカウント」という集計方法があります

こちらは、列内にある重複しないリスト数を集計するという優れた機能です

従来のエクセルの使い方であれば、複数回のステップが必要な集計処理でした

「個別の値のアカウント」について興味のある方は、こちらの記事もぜひご参照ください

2.標準メニュー

こちらのメニューでは2つの集計方法を紹介します

集計単位は指定した列の各行単位になります

①四則演算

今回は四則演算の内、「割り算」についてのみ解説します

実際に集計する前に、集計する列をコピーしておいて、集計結果を集計前と比較できるようにします

この列のコピー処理がPower Queryエディターでは、どうやるのかが分かりにくいので注意が必要です

このコピー処理は「列の追加」タブの「重複する列」で行います

では、コピーした列の列名を「販売個数ー割り算」に変更した上で、「割り算」による集計を行います

まず、集計する列をカーソルで選択した上で、「除算」を押します

その後に、除算する値を入力するダイアログが出てきます

ここでは、「2」を入力しました

結果、列全体が「2で割り算された値」に置き換わっています

②パーセンテージ

指定された列の値を、指定したパーセンテージで乗じた値に置き換えます

例えば、①で2で割り算をした列に、1%を指定した場合は、以下のように出力されます

この1%を指定する時に、旧エクセルでは「0.01」もしくは「1%」と入力指定をしていました

このPower Queryの「パーセンテージ」集計方法を使えば、下の図のように「1」と指定すれば済むのが嬉しいです

これで標準メニューについての解説を終わりますが、列の各行に対して1つの値を割り算するなどの集計方法ではなく、

「複数列を組み合わせて、四則演算をする場合にはどう集計するの?」

という疑問を持たれた方もいらっしゃると思います

この場合には、過去の記事にて複数列での集計方法を紹介しています

この場合には複数列をカーソルで指定し、変換タブではなく「列の追加」タブにある標準メニューを選択します

複数列の計算

上のGIF画像では、「販売単価」と「販売個数の合計」の2つの列を乗じて新しい列に集計結果を出力しています

3.指数メニュー

こちらのメニューについては、詳細な解説は割愛させて頂きます

この指数メニューでは、下の図の様な集計が行えます

4.数字を丸める

このメニューは従来のエクセルの使い方をすれば、ROUNDUPなどの関数で集計していたものでした

Power Queryでは、クリック処理で集計を進めることができます

丸め

丸めのメニューには3つの集計方法があります

・切り上げ

・切り捨て

・四捨五入

今回の解説では、下の図の2.の解説で2で割り算をした列を「切り上げ」処理をしてみます

下のGIF画像のように、クリックひとつで切り上げ処理ができました

<まとめ>

 今回は、4つの集計メニューについて解説を行いました

・統計 ⇒合計値などの集計

・標準 ⇒四則演算などの集計

・指数 ⇒平方根などの集計

・丸め ⇒切り上げや四捨五入の集計

上記の処理はいずれも、直感的なクリック処理にて集計をすすめられるので、とても便利です

 但し、Power Queryエディタ―独自の表記の問題で、変換タブと列の追加タブでメニューが重複しています

そちらについては少し分かりずらいので、違いを意識して処理を進めていきましょう!

 後、列コピーをするのに右で処理するのではなく「列の追加」タブの「重複する列」というボタンを使用するのも、少し分かりずらいですね

「列の追加」タブで処理することを覚えておきましょう!

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

次回からエラーへの対処方法を解説します

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

にほんブログ村

エラー修正_処理ステップエラー~上級編5回目

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

Power Queryは従来のエクセルとは、使用の仕方が違います

ですのでエラーが発生した時の対処については、最初は戸惑います

 但し、Power Queryで発生するエラーは大きく分けて「処理のステップ」に関わるものと「データ自体」に関わるものの2つしかありません

エラーメッセージの内容を確認しながら、修正処理を進めていきましょう!

 「処理のステップ」に関わるエラーの中でも、発生頻度が多いのはファイルの保存先変更などに伴う「データソース取得のステップ」に関するものです

今回は、ファイルの保存先を変更した場合のエラーへの対処について解説します

1.エラーの発見

クエリが参照しているファイルが見つからない時にクエリの更新処理を行うと、次のようなメッセージが出ます

上の画像は、クエリが参照しているファイルの保存フォルダを変更したために発生しています

このメッセージが出た場合には、エラーメッセージが出ているクエリの編集をPower Queryエディターで行います(今回はダウンロードは完了していません、のメッセージが出ているクエリで行います)

2.データソースの変更

Power Queryエディターを開くと、次の様なメッセージが出ているはずです

この場合には、画面上の右にある「データソース設定」ボタンをクリックします

データソース設定

次は、画面の左下にある「ソースの変更」をクリックするのですが、ボタンが小さくて分かりずらいので注意が必要です

後はデータソースの変更を、下のGIFのように進めてください

3.更新処理

データソースを変更しても、Power Queryエディター上にエラーメッセージが出ている場合には「プレビューの更新」を押してみてください

Power Queryエディター上でエラーメッセージが消えたら、エクセルシートに修正したデータを読み込んで、更に「データ更新」を行いましょう

<まとめ>

 今回はエラーメッセージが出る頻度が高い「データソース変更の処理ステップ」に関わるエラー修正を解説しました

 Power Queryは従来のエクセルとは使用の仕方が違うので、エラー修正が難点です

 エラーメッセージを詳細に確認しながら、修正を進めていきましょう!

 ちなみに、今回使用したエクセルのバージョンは下の画像の通りです

次回は「データ自体」のエラーに関する処理について解説します

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

にほんブログ村

途中のステップを削除した場合のエラー修正処理~上級編6回目

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

 Power Queryは便利だけど、通常のエクセルとは使用方法が違うので苦労する点も多いですよね

 特にPower Queryエディターは、これまでのエクセルとは画面自体が違うので「理解に苦しむ」人もいらっしゃると思います

 Power Queryエディターの画面右にある「ステップの適用」は通常のエクセルでいえば、ショートカットキー:Ctrl+Zに相当する箇所なのです

 こちらの「ステップの適用」は最初は難解に感じる人もいらっしゃるかもしれません

 今回は、「ステップの適用」にて途中のステップを削除した場合の修正方法について解説します

 今回の解説を通じて、Power Queryへの理解の一助になれば幸いです

1.ステップを削除する前の状態の説明

今回は、以前の回で使用したデータを使用します

この回では、下のGIFのように販売単価と販売個数の合計を乗算して、新たな列を作成しました

その後に、新しくできた列の名前を下の画像のように「乗算」から「売上金額」に変更しました

今回は、更に売上金額の列を10万円以上でフィルダーをかけます

すると、適用のステップには次の画像のように1つのステップが追加されます

では、この状態から本題の解説に入ります

2.1つ前のステップを削除

1.で追加してステップの1つ前を削除します

すると、次の画像のようにエラーメッセージが発生します

1.でフィルターを10万以上した際には、あくまで「売上金額」の列に対してフィルターをしています

そのフィルターをした「売上金額」の列が削除されてしまったのでエラーになっています

3.エラーの修正

このエラーを修正するには、2つの方法があります

1つは、フィルターされた行を削除し、列名を変えるところからやり直す方法です

2つ目については、解説を始める前にまず「フィルターされた行のステップ」の1つ前のステップ「挿入された乗算のステップ」をクリックしてみます

上のGIFのように1つ前のステップを選択するとエラーメッセージは消えました

これは、あくまで次のステップである「最後尾のステップ」からエラーになっているということです

ここからが2つめの修正方法の解説です

解説するのはエラーになっていないステップから修正をかける方法です

エラーが起こっていない、「最後尾のステップの1つ前」のステップにカーソルを置いたまま、列名を1.でフィルターをかけた時と同じ「売上金額」に変更します

すると、下のGIF画像のようにエラーが起きていた最後尾のステップ「フィルターされた行」をクリックしてもエラーメッセージは出ません

<まとめ>

 今回は、途中のステップを削除した場合のエラー修正について解説しました。このような場合には、必ずしもエラーが起きたステップを削除した後に「やり直し」処理が必要なわけでありません

 エラーが起きていないステップから再度修正をかける方法もあります

 とはいえ、極力は途中のステップはなるべく削除しないで済むようにはしておきましょう

 尚、解説を端折ってしまいましたが、途中のステップを削除すると必ずエラーが発生するわけではありません

 あくまで、途中のステップを削除することで前後の処理の流れに矛盾が起こる場合にエラーが発生しますので、その点だけ最後に追記させて頂きます

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

次回はデータ自体がエラーになっている場合について解説します

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

にほんブログ村

エラー修正_データ自体のエラー~上級編7回目

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

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

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

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

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

目次

1.エラー内容の確認

2.修正作業

<まとめ>

1.エラー内容の確認

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

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

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

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

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

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

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

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

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

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

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

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

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

2.修正作業

①元のデータを直す

この方法が1番簡単で確実です

元のデータを直して、クエリを更新すればエラーが解決です

②データの削除

①のように直すべき値が分からず、応急処置を行うときにこの方法が必要になります

 1.にて矢印をクリックして開いたクエリーの一覧から、元々、下記の画像のようなメッセージが出ていたクエリーを開きます

すると、下の画像のようにエラーの箇所が表示されています 

ですので、次にホームタブから「行の削除」の▼マークをクリックします

 すると、下の画像のように「エラーの削除」というボタンがクリックできるようになります

 「エラーの削除」をクリックする前に、下のGIF画像のように対象となるエラーが発生している列をカーソルで選択してから「エラーの削除」をクリックしましょう!

 そうすれば、再度エクセルシートに読込処理を行うと下の画像のようにエラーメッセージが出力されないようになっています

但し、1行少なくなってはいます

③値の置換

次の画像のメニューからエラーになっている値を置き換えできます

まず、変換タブの「値の置換」の右横の▼マークをクリックします

すると「エラーの置換」が選択できます

次にエラーが出ている列を選択します(複数列の指定も可です)

次に前述の「エラーの置換」をクリックします

すると「エラー」をどの値と置き換えるかを聞かれます

今回は「0」を指定します

OKボタンを押すとエラーの値が「0」に変わります

<まとめ>

 今回は表示されたエラーメッセージを辿っていき、Power Queryエディター上で「データ自体」のエラーの発生原因を調査しました

 そして「データ自体」のエラー発生個所が分かった後には、3つの修正方法があることを解説しました

理想は大元の「データ自体」を修正することです

 但し、どうしてもその方法では対処できない場合にはPower Queryエディターのホームタブで「エラーの削除」処理を行います

今回は2回にわたってエラー修正の方法を解説しましたが、Power Queryエディターの使い方も詳細に身に付く内容だったと思います

 特にエディターの左画面でクエリーの一覧を確認・選択する方法を覚えておくと何かと便利です

今回を機に習得して頂けますと嬉しいです

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

次回は、ユーザーが独自作成するカスタム関数について解説します

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

にほんブログ村

カスタム関数を自身で登録して使い回す~上級編8回目

 資料を作成する度に、毎回毎回、同じ数式を入力していませんか?Power Queryではカスタム関数という仕組みにより、事前登録したカスタム関数を呼び出して使い回していくことができます

今回とあわせて2回に分けて、カスタム関数についての解説を行います

 私が昔、企画の仕事をしている時は、会議の前日は必ず数値資料を作成していました

 「前期比」「利益率」「単価」などを資料に盛り込むわけですが、毎回決まった数式を表に入力していました

毎回、同じ数式を入力するので体で覚えてしまったことをよく覚えています

 この記事を見て下さっている読者の方の中にも似たような経験のある方がいらっしゃるのでないでしょうか?

 カスタム関数をマスターして、「同じ数式の再入力」は自身で事前作成した「カスタム関数」の呼出し処理だけで代用できるようにしましょう!

 本格的な解説に入る前に、「今回のポイント」と「今回使用するデータと、作成するカスタム関数」について解説を行います

1.今回のポイント

中学数学の方程式

中学時代にこんな問題を数学の時間に出された経験はありませんか?

Y=aX+bの式に(X=2,Y=5)を代入するといった問題です

今回のカスタム関数を作成する時にも、同じような考え方をします

ただ、今回は代入するのが「列の名前」になります

空のクエリの作成

今回はA.の方程式を、空のクエリを作成してから指定します

「空のクエリ」はこれまでのPower Queryの解説では出てこなかった方法です

 更に空のクエリを作成した後、Power Queryエディター画面から詳細エディター画面を開き、下の画像の「let」と「in」の箇所に方程式を指定します

2.今回の使用データと作成内容

 今回のポイントを2点解説したので、次に今回使用するデータと作成するカスタム関数について解説します

アイコン

上級編8-9回_演習 19.39 KB 39 downloads

...

今回使用するデータは、次の画像にあるデータになります

 今回作成するカスタム関数は、黄色く塗られた列の値を使用して、下の画像の利益を計算する関数になります

ちなみに、上の画像の「利益」の列には以下の数式が入力されています

 使用するデータと作成するカスタム関数について解説したところで、本格的な解説に入ります

.空のクエリ作成

データタブの「データの取得」から「その他のデータソースから」を開きます

開いた一覧の一番下に「空のクエリ」があります

 上の画像の「空のクエリ」をクリックすると、Power Queryエディターが開きます

次にPower Queryエディターのホームタブから「詳細エディター」を開きます

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

上の画像の「Let」の下と「in」の下に、ポイントAで解説した方程式を入力していきます

<まとめ>

 今回は、カスタム関数を作成するにあたってのポイントを2点解説した後、ポイントAで解説した、方程式を入力する画面を開くところまでを解説しました

 詳細エディターの画面は少しややこしいところもありますが、方程式の作成の仕方をイメージしてもらえればOKです

では次回は、カスタム関数を完成させます!

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

にほんブログ村

カスタム関数を自身で登録して使い回す2~上級編9回目

 今回はPower Queryでカスタム関数を作成した後、実際に作成した関数を呼び出す方法を解説します

まずは、前回の内容の「振り返り」から行いたいと思います

前回は、カスタム関数を作成する際の「ポイント」と「作成画面」について解説しました

 解説の対象となる、これから完成しようとしてる「カスタム関数」は次の画像の「販売単価」「製造原価」「販売個数」から「利益」を算出する関数です

 上の画像の数式バーにあるように、利益を算出するのに数式を入力していたところを、カスタム関数を呼び出すだけで「利益」を自動算出できるようにします

カスタム関数のポイントとしては、2点を解説しました

1点目は、中学時代に習った「方程式を思い出す」です

カスタム関数はこの方程式と同じ様な考えで作成します

そして、代入するのは「列の名前」になるのが特徴です

2点目は、カスタム関数の作成画面についてです

前回は、上の画面を開いたところまでを行いました

 では、この「詳細エディター」画面にてカスタム関数を設定するところから解説をはじめます

目次

1.関数の設定

2.カスタム関数の呼出し

<まとめ>

1.関数の設定

今回は、次の数式を詳細エディター上に関数として設定します

利益=(販売単価-製造原価)x 販売個数

ここで、詳細エディターへの設定方法を分かり易くするために、前回のポイントで解説した方程式を使って解説します

前回のポイントで、方程式の事例として解説したのは次の方程式です

Y=aX + b

上の方程式を詳細エディターに設定したとすると、次の画像のようなイメージになります

詳細エディター設定例

letの箇所は2段の設定になります

1段目:Y = (a,X,b) =>

2段目:ax + b

1段目で「変動する数」として使用する記号を、=を挟んで右辺と左辺に分けて設定した後に、「=>」の2文字で2段目に繋ぎます

そして、2段目では方程式の右辺を設定します

inの箇所はYを設定するだけです

方程式を例に取って、詳細エディターの設定イメージをお伝えしたところで、本題の関数を設定します

利益=(販売単価-製造原価)x 販売個数

上の数式の「変動する数」はそれぞれ、以下のように設定するものとします

・利益 ➡profit

・販売単価 ➡price

・製造原価 ➡cost

・販売個数 ➡quantity

では、上の「変動する数」を詳細エディターにあてはめると下の図のようになります

画面下に以下の文字が出ていたら、右下の「完了」を押します

すると、下のような画像に切り替わります

画面には「パラメーターの入力」と出ていますが、こちらは無視していいです

ここからは通常のクエリを作成した時と同じように、エクセルシートに読込処理を行うわけですが、画面右にてクエリー名を「利益」としておきます

読込処理を行った後は、通常のクエリーと同じ様に画面右に表示されています

マークは通常のクエリとは異なっています

ちなみに、後でこの「利益」の名前を使用して「関数呼び出し」を行います

これで、カスタム関数は完成です

2.カスタム関数の呼出し

では、利益額を差し込む表をテーブル化してPower Queryエディターを開きます

Power Queryエディターが開いたら、「列の追加」タブにて「カスタム関数の呼出し」をクリックします

「カスタム関数の呼出し」をクリックすると次のようなダイアログが開きます

カスタム関数の呼出し

このダイアログの上にある「新しい列名」は「利益」に変えておきます

次に下のGIF画像のように、カスタム関数「利益」を呼出します

カスタム関数を呼び出し

後は、詳細エディターで設定した「変動する数」に「列名」を代入します

ちなみに、列名を代入するには、下の画像の▼マークをクリックしておく必要があります

「列名」を代入して、ダイアログボックス右下の「OK」ボタンをクリックすると下の画像のように「利益」の列が作成されています

これで、関数の呼出しも終了したので、エクセルシートにPower Queryエディターのデータを呼出します

事前に計算しておいた利益額とも一致しているようです

<まとめ>

 今回は、2回にわたって「カスタム関数」について解説を行いました

2回目の回では、カスタム関数を詳細エディタに設定した後に、別のクエリ画面からカスタム関数を呼び出して新たな列を設定しました

 要領としては、中学数学の方程式に「列名」を代入するイメージになります

 但し、詳細エディターで「let」「in」それぞれの段に設定する式については1度では正確に覚えられないので、参照用のメモ等を作成しておくと効率がいいです

参考までに今回使用したデータと完成版を添付しておきます

アイコン

上級編8-9回目 27.37 KB 22 downloads

...

 ちなみに、1度作成したカスタム関数を修正する場合には、まずは、下の画像のように、クエリの編集をクリックします

次にPower Queryエディターが開いたら、下のGIF画像のように▼マークをクリックすれば修正できます

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

次回は条件付きのカスタム列を作成する方法を解説します

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

にほんブログ村

IF式を組み合わせて列作成~上級編10回目

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

 Power Queryは関数やVBAの知識がなくても、直感的にクリックしていくことで処理を進めていけるのが魅力です

 ただ、どうしてもIF文をネスト(組み合わせ)し、複数の条件式を組み合わせた新たな列を挿入する必要がでる時もあります

 そんな時のためにPower Queryには「列の追加」タブに「カスタム列」というメニューがきちんとあります

 今回は、下の図の売上金額の集計表をもとにして、IF式を組み合わせて新たな列を作成する方法を解説します

売上金額

アイコン

上級10回目_演習 16.89 KB 54 downloads

...
 

上の図の表「売上金額」を元にして、来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

来期の売上予測のパターンの1つ目は消極的な予測です

・地域がアメリカ ➡ 10%増加

・それ以外 ➡ 5%増加

2つ目は積極的な予測です

・地域がアメリカ ➡ 15%増加

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加

・上記2つ以外 ➡ 5%増加

1つ目は1つのIF文により、2つの計算式を作成します

2つ目は2つのIF文により、3つの計算式を作成します

 しかも、2つ目のパターンの3番目の計算式は「且つ」なので「AND」を組み合わせます

作成する計算式を説明したところで、本格的な解説に入ります

消極的パターン

解説は、Power Queryエディターを開いたところから始めます

主な解説内容は、次の①~③があります

①はIF文を作成するメニューの場所

②IF文を実際に作成する画面の詳細

③②を行う上での注意点

では、まずは①から解説します

① IF文を作成するメニューの場所

まず「列の追加」タブの「カスタム列」をクリックします

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

②IFを実際に作成する画面の詳細

まず、この「カスタム列」画面の解説を3か所に分けて行います

・新しい列名➡IF文を使用して新たに作成する列の名称を記入

・カスタム列の式➡「=」から右にIF文を記入

・<<挿入➡上の「使用できる列」で指定された「列」を式の中に挿入

3番目の「<<挿入」の使い方については、以下、詳細に解説します

まずは上の「使用できる列」の中から該当する「列」をカーソルで選択します

次に画面右下の挿入ボタンを押すと、下のGIF画像のように前述の「カスタム列の式」の欄に選択された列が挿入されます

この「カスタム列画面」の3か所について解説したところで、実際にIF文を記入していきます

IF文の構文は以下の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

内容はVBAでIF文を書く場合と、ほぼ一緒です

最後に「End IF」を付けないところだけが違います

実際の消極パターンを書いた画面は、次の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

上の構文に、消極的パターンを当てはめて数式が書いてあります

if [地域]=”アメリカ” then [売上金額]*1.1 else [売上金額]*1.05】

③②を行う上での注意点

ここで1点、注意点があります

上の画像の下に「トークン Eof・・・」なるエラーが出ています

これは、式の構文の中に大文字が入っているからです

式の構文は、下のテキストのように、全て小文字で記入するようにしてください

IF、THEN、ELSE ➡ if、then、else

では、正しく数式を記入したところで下のOKボタンを押します

そうすると、Power Queryエディター画面に新たな列が挿入されます

では、念の為に新たに挿入された列が正しいかどうかをPower Queryエディター上で確認します

まず「売上金額」と「予測式_消極」の列をカーソルで入れ替えます

次に上の両列をカーソルで選択したまま「列のタブ」の「標準」から除算をクリックします

すると、次の様に「(左側)予測式_消去」÷「(右側)売上金額」の結果が出力されます

「標準」機能の計算は、列の位置が左にあるものから自動的に計算されてしまうので、列の位置を入れ替えることを事前に行いました

さて、出力結果は次の通り、正確に出力されていたので「積極パターン」の解説に移ります

積極パターン

まず、積極パターンの内容を振り返りましょう

・地域がアメリカ ➡ 15%増加(A

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加(B)

・上記2つ以外 ➡ 5%増加(C)

では上記のA~Cを、「カスタム列を作成する画面」に当てはめると次のような式になります

まず、前述のA( 地域がアメリカ ➡ 15%増加 )はの式のように、1.の消極パターンと同様の書き方です

【if [地域]=”アメリカ” then [売上金額]*1.15】

次にBの箇所を書き始めるところが1.の消極パターンと違います

「else」の後にそのまま2つ目のIF文を「AND」を交えて、の式のように書きます

【else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1】

そして最後にCを「else」の後に書きます

else [売上金額]*1.05

上の3つの数式をつなげると次のようになります

if [地域]=”アメリカ” then [売上金額]*1.15 else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1 else [売上金額]*1.05

「カスタム列を設定する画面」の下のOKボタンを押すと、次の様に出力されます

1.の消極パターンでも行ったように、検算を行うと次のようになりました

 Bの 条件【製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加】もしっかり反映されています

 上のPower Queryエディタの内容をエクセルシートに読込処理を行うと、次の画像のようになります

<まとめ>

今回は、IF文を使用した列を追加する方法を解説しました

IF文の構文の書き方はVBAとほぼ同じになっており、次の通りとなっています

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

 IF文を更に組み合わせる場合には、上記の「else」の後にまたIF文を追加すればOKです

 尚、構文を間違えていたり、前述した大文字で記入を行ったりすると 「トークン Eof・・・」なるエラーが出てしまいますので注意が必要です

 ちなみに、正確に数式を記入できている場合には、以下のメッセージが画面下にでます!

今回の解説は以上です

この記事の内容をマスターしたら、M言語を使用した「カスタム列」にもぜひチャレンジしてみてください!

https://analytic-vba.com/power-query/m-code/mcode-begin-custom/
https://analytic-vba.com/power-query/m-code/mcode-begin-custom/

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

参考までに、今回の解説で使用したデータと完成したデータを添付しておきます

アイコン

上級10回目 24.44 KB 12 downloads

...

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

にほんブログ村