タグ別アーカイブ: エラー

【分析作業用】大量データを1目で把握する

 Power Queryには旧エクセルの「104万行」の壁がありません。ですから大量のデータも手軽に扱えるのが魅力です。ところが、Power Queryエディタ(以降、エディタ)では、旧エクセルのように、ショートカットキーを活用してデータ内を端から端まで散策するようなことができません。ですので、エディタ内ではデータ全体を把握しずらいのです。

しかも行数が1000行以上だと全ての行は表示されないようになっています

これは大量のデータがあっても、素早く動くようにするための工夫ですが、ユーザーにとっては不便です。

 但し「散策」できない点を補完する機能がエディタ内の「表示タブ」にあります。今回は「表示タブ」での「補完機能」について解説します

今回解説する補完機能の内容は次の3つになります

1.データの列数を1目で把握する

2.データの行数を1目で把握する

3.エラーの発生数を1目で把握する

 上の3つは、どれも「表示」タブ内の簡単な操作で把握できる内容になっています

では、今回解説する内容について紹介したところで、詳細な解説をはじめます

1.列数を把握する

「一体、このデータはどこまで右に続くのか?」

列数があまりに多いと、最終列を探す旅にでかけなくてはなりません

前述の表示タブでは、少なくとも「列数」はすぐに把握できます

表示タブの左下に列数が表示されています

こちは、行数についても「1,000行以下」であれば表示されています

2.データの行数を1目で把握する

Power Queryのエディタ内では、1000行以上は表示されないように工夫されています

ですから、フィルタで1000行以上を見ようとしても上の画像のように「値が上限の1000個に到達しました」と表示されます

以降は有料ページ/課金サービスへ

エラー修正_処理ステップエラー~上級編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へ

にほんブログ村

#SPILLエラーについて~新関数に独自のエラー

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

 これまで、1つのセルへの入力で複数セルに出力する新関数について、複数の記事を書いてきました

 例えば、下のGIF画像はFILTER関数を使った検索付きのリストの例です

 C列にFILTER関数を仕込んであり、E列で検索文字「鈴木」が設定されるとA列のリストデータから「鈴木」を含むリストデータを出力します

 この新関数では独特のエラー「#SPILL」が出力される時があります

このエラーの意味について、今日は少し解説したいと思います

SPLLLエラー

見慣れないエラーですが、発生理由はシンプルです

 この新関数は複数のセルに出力を行いますが、出力範囲に何らかの入力がある場合にこのエラーは発生します

 

上の画像で言えば、!の文字が新関数の出力をブロックしてるのです

このブロックしている文字を取り除けば、このエラーは解消されます

 尚、ブロックしている文字のフォントが「白色」で人の目では見えない時にも同じエラーが発生するので注意が必要です

では、今回は以上です

最後までブログ記事を見て下さり、大変感謝しております!

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

にほんブログ村

エラー発生の予防/列のデータ形式変更と削除について~上級19回~

 Power Queryの便利な点は、普段から使い慣れたエクセルをAccessのようなデータベースとして活用できる点です。このページを訪問して頂いた方の中にもAccessは細かなルールが多くて不便と感じた方もいらっしゃったと思います。

 Power Queryは自動で、Accessの不便な点を補ってくれる機能があります。そのかわり、エラーが出やすい箇所があります

このエラーはPower Queryの2つの特徴と深く結びついています

 Power Queryの2つの特徴について解説しながら、「列名変更」に関するエラーの発生を防ぐ方法について解説します

 エラーの発生を防ぐ方法を理解した時には、「データ形式」も含めてPower Queryへの理解が一段と深まっているはずです!

ところで、

エクセルとAccessの違いとは何でしょう??

エクセルはあくまで表計算ソフトです

Accessはデータベースソフトです

 この2つの違いを、別な言い方で表現すると「データの構造化」ということになります

こちらが、Power Queryの特徴の1つ目の話しです

Accessでは、いきなりデータから入力はできないようになっています

 上の画像のように「データを入力する箱」をフィールド毎(エクセルでは列毎)に「フィールド名/見出し」「データ型」を設定しなくてはいけません

つまり、予め入力するデータの箱を「構造化」しておくわけです

 今回の解説では、上の画像についてはこれ以上は深入りしませんが、上で前述した「データの構造化」を意識して、以降の記事を読み進めてみてください!!

今回、解説に使用するデータは次の画像のデータです

 こちらの3列(売上日、商品コード、販売個数)からなるテーブルデータから、下の画像のように商品コードの1列を抽出するクエリを作成しておきます

そして、元のテーブルデータの「販売個数」の列名を変えます

すると、「商品コード」のみを抽出するクエリを更新するとエラーになります

 エラーの中味を見てみると、抽出する「商品コード」とは関係ない列の「名前変更」によりエラーが発生しています

実は、このエラーは前述の「データの構造化」が深く関係しています

では、こちらのエラーを回避する方法を以下、2パターンで解説します

1.ステップ「変更された型」の削除

まずは、前述のクエリの中味をPower Queryエディタ(以降、エディタ)で見てみます

適用したステップは3つあります

こちらの3つのステップを、上から順に各ステップの「数式」を見てみます

①ソース

こちらは、ファイル内の「売上台帳」テーブルをデータソースとして読み込んでいるのが分かります

②変更された型

こちらのステップでエラーが発生しているのが、よく分かります

こちらの数式に含まれる「販売個数」は、既に名前が変更されているのでエラーが発生しています

③削除された他の列

こちらのステップで「商品コード」列のみを抽出しています

 実際には、次の画像のように「商品コード」以外の列を削除してステップが作成されています

①~③のステップの中味を確認したところで、エラー原因となった②のステップを削除してみます

すると、エラーは消えます

仮に元の列名が「販売個数」の列名を、再度変更してもエラーは発生しません

これで、列名の変更でエラーが発生しないクエリに変更できました

ところで、

今回のエラー発生の原因となった「変更された型」のステップとはなんでしょうか?

エディタを開く時には、「変更された型」のステップは自動挿入されています

このステップは記事の冒頭で前述したPower Queryの1つ目の特徴である「データの構造化」と深くかかわっています

試しに下の画像のように、最初からクエリを作成してみます

開いたエディの中味を見ると、「変更された型」が前述のように自動追加されています

そして、各列も自動で「型式」が変更されています

例えば、商品コードの型式は元々は「文字列」でしたが、下の図のように「123」マークの「整数型式」に自動変換されています

下の図のように元のデータにて、数字かどうかを判定する数式「ISNUMBER」で判定するとFALSEになり、「文字列」だったことがよく分かります

記事の冒頭で前述したように、エクセルはあくまで表計算ソフトです

表計算ソフト内にあるデータは、データベースとしては構造化されていません

Power Queryでは、Accessとは違い、自動でエディタ内にて「構造化」の設定を行ってくれているのです

ちなみに、AccessのファイルをPower Queryで読み込むと「変更された型」のステップは発生しません

 AccessファイルをPower Queryで読み込む方法は過去の記事で紹介していますので、興味のある方は試してみてください

話をPower Queryの「構造化」に戻します

「構造化」は元データの全ての列に対して設定を行うので、エディタ内で削除した列も設定の対象です

しかも「構造化」は元の列名に対して行われます

ですので、一見、クエリに関係ない列の「名前の変更」が影響するのです

 但し、「商品コード」が文字列から「整数」に変換されたように、常に正しく「構造化」が行われるわけではないので注意が必要です

自動で変更された型を変更する場合には、下のGIF画像の箇所で調整を行います

下の図のように「文字列」は「テキスト」となっている点に注意してください

上の図の詳細な内容については、一覧でMicrosoft社のHP内で紹介されています

さて、

 エラー発生の対策をしたクエリからは「変更された型」のステップは削除されていますが、こちらのステップを後から追加することもできます

上の画像の黄色の箇所にある、変換タブ内の「データ型の検出」をクリックすると、「変更された型」のステップが追加されます

2.削除の仕方の変更

2.削除の仕方の変更では、1.とは別なアプローチでエラーを解決してみます

まず、エディタ内の画面左側から1.でエラーが発生しなくなったクエリを「複製」してみます

複製したクエリの最終ステップでは、前述のように「商品コード」以外の列を削除しています

画像に alt 属性が指定されていません。ファイル名: image-235.png

こちらのステップを削除して、次のGIF画像のように他の2つの列を1つ1つ削除します

2つの列を削除した後、エクセルシートに読込みます

次に、列名を「TEST2」から「TEST3」に変更すると複製したクエリはエラーになります

このエラーの原因は「列の削除の仕方」にあります

下の図のように、列名変更前の「TEST2」の列名を指定して「列の削除」をおこなっているからです

 こちらのエラーについては下の画像の黄色の箇所のように、列の削除の仕方を「列名」を指定しない方法(他の列の削除)で行えば発生しません

画像に alt 属性が指定されていません。ファイル名: image-235.png

このエラーはPower Queryの「ハードコード」という特徴と深く結びついています

Power Queryでは、エディタ内の操作がステップとして記録されます

このステップ内には、も記録されます

前述のエラーで言えば、「列名を変更する前」の列名です

こちらは、列名が変更になると「ハードコード」した列名と一致しなくなってしまい、エラーになったのです

<まとめ>

 今回は、エディタ内で削除した「列名」を後から修正した場合に発生するエラーの修正方法について2つ解説しました

 1つ目は「変更された型」という自動で追加されるステップを削除してエラーを回避できるようにしました

2つ目は列の削除の仕方を、削除する列名を特定しない方式に変更しました

2つの方法はそれぞれ、Power Queryの2つの特徴と深く結びついています

 1つ目の「変更された型」に関するエラーは「データの構造化」というPower Queryの特徴と結びついています

 エディタでデータを読み込む際に、全ての列を対象にして「見出し」「データ形式」を設定します

ですので、エディタ内で削除した列の「列名変更」がエラーの原因になります

 こちらの「変更された型」については、「変更された型」のステップを削除する方法ともう一つ、対処方法があります

データタブの「データの取得」をクリックすると一番下に「クエリオプション」が出てきます

こちらをクリックすると次の画像の画面が開きます

こちらの画面で、「非構造化ソースの列と型とヘッダーを検出しない」を指定しておくという方法もあります(自動で検出されていた部分が検出されなくなるので注意が必要です)

ここまでで、Power Queryの1つ目の特徴についての「まとめ」を解説しました

次は2つ目の特徴である「ハードコード」についてです

Power Queryではエディタ内の操作が「ステップ」として記録されます

こちらの「ステップ」には値も直接書き込まれます

ですので、前述の2.で列を削除した際には「列名」も値として記録されました

ですので、エディタ内で削除した列の「列名」が変更になると、列名の「不一致」が発生してエラーになったのです

下の画像で言えば、「TEST2」の列名の列を削除するように数式が設定されていましたが、既に「TEST3」に列名が変更になっていたのです

画像に alt 属性が指定されていません。ファイル名: image-248.png

 今回はPower Queryの2つの特徴について、理解を深める機会になったと思います

この2つの特徴を理解しておくと、Power Query自体への理解も深まります

 ぜひ今回を機会に「データの構造化」「ハードコード」について意識した上でPower Queryに取り組んでいきましょう!

では、今回は以上となります

参考までに今回使用したエクセルデータを添付します

アイコン

上級19回 24.18 KB 10 downloads

...

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

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

にほんブログ村