Excel

[アーティクル]
02/07/2024

まとめ

項目	説明
リリース状態	一般提供
製品	Excel Power BI (セマンティックモデル) Power BI (データフロー) ファブリック (データフロー Gen2) Power Apps (データフロー) Dynamics 365 Customer Insights Analysis Services
サポートされている認証の種類	匿名 (オンライン) 基本 (オンライン) 組織アカウント (オンライン)
関数リファレンスドキュメント	Excel.Workbook Excel.CurrentWorkbook

Note

デプロイスケジュールにより、またホスト固有の機能があることにより、ある製品に存在する機能が他の製品にはない場合があります。

前提条件

レガシブック (.xls や .xlsb など) に接続するには、Access Database Engine OLEDB (または ACE) プロバイダーが必要です。このプロバイダーをインストールするには、ダウンロードページに移動し、関連する (32 ビットまたは 64 ビット) バージョンをインストールします。インストールしていない場合は、レガシブックに接続するときに次のエラーが表示されます。

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE はクラウドサービス環境にインストールできません。そのため、クラウドホスト (Power Query Online など) でこのエラーが発生する場合は、ACE がインストールされているゲートウェイを使用して、レガシ Excel ファイルに接続する必要があります。

サポートされる機能

インポート

Power Query Desktop から Excel ブックへの接続

Power Query Desktop から接続するには:

データの取得エクスペリエンスで [Excel ブック] を選択します。 Power Query Desktop でのデータの取得エクスペリエンスは、アプリによって異なります。アプリの Power Query Desktop データの取得エクスペリエンスの詳細については、「データを取得する場所」を参照してください。
読み込む Excel ブックを参照して選びます。その後、 [開く] を選択します。

Excel ブックがオンラインの場合は、Web コネクタを使用してブックに接続します。
ナビゲーターで、目的のブック情報を選び、[読み込み] を選んでデータを読み込むか、[データの変換] を選んで Power Query エディター内でデータの変換を続行します。

Power Query Online から Excel ブックへの接続

Power Query Online から接続するには:

データの取得エクスペリエンスで [Excel ブック] オプションを選択します。 Power Query Online のデータの取得エクスペリエンスに到達する方法は、アプリによって異なります。 Power Query Online のデータの取得エクスペリエンスに到達する方法の詳細については、「データを取得する場所」を参照してください。
表示される [Excel] ダイアログボックスで、Excel ブックのパスを指定します。
必要に応じて、オンプレミスのデータゲートウェイを選んで Excel ブックにアクセスします。
この Excel ブックに今回初めてアクセスした場合は、認証の種類を選び、アカウントにサインインします (必要な場合)。
ナビゲーターで、目的のブック情報、[データの変換] の順に選んで、Power Query エディター内でデータの変換を続行します。

推奨されるテーブル

特に単一のテーブルが含まれていない Excel ブックに接続すると、Power Query ナビゲーターは、選択できるテーブルの推奨リストを作成しようとします。たとえば、A1 から C5 までのデータ、D8 から E10 までのデータ、および C13 から F16 までのデータが含まれる次のブックの例を考えてみましょう。

3 セットのデータを含む Excel ブックのスクリーンショット。

Power Query のデータに接続すると、Power Query ナビゲーターは 2 つのリストを作成します。最初のリストにはワークブックシート全体が含まれ、2 番目のリストには 3 つの推奨テーブルが含まれます。

ナビゲーターでシート全体を選択すると、ワークブックは Excel で表示されるのと同じように表示され、すべての空白セルが null で埋められます。

提案されたテーブルの 1 つを選択すると、Power Query がブックのレイアウトから決定できた個々のテーブルがナビゲーターに表示されます。たとえば、表テーブル3 を選択すると、最初にセル C13 から F16 に表示されていたデータが表示されます。

Note

シートが大幅に変更されると、テーブルが適切に更新されない可能性があります。データを再度インポートし、新しく提案されたテーブルを選択することで、更新を修正できる可能性があります。

トラブルシューティング

数値の精度 (または "数値が変化した理由")

Excel データをインポートする際に、特定の数値が Power Query にインポートされたときに若干変化するように見えることに気付く場合があります。たとえば、Excel で 0.049 を含むセルを選んだ場合、この数値は数式バーに 0.049 と表示されます。しかし、同じセルを Power Query にインポートして選ぶと、プレビューの詳細には 0.049000000000000002 と表示されます (プレビューテーブルでは 0.049 として書式設定されています)。どうなっているのでしょうか?

答えは少し複雑で、Excel が バイナリ浮動小数点表記 と呼ばれるものを使用して数値を格納する方法に関連しています。つまり、Excel が 100% の精度で表現できない特定の数値があります。 .xlsx ファイルを開き、格納されている実際の値を確認すると、.xlsx ファイルには、0.049 が実際には 0.049000000000000002 として格納されていることがわかります。これは Power Query が .xlsx から読み取る値なので、Power Query でセルを選んだときに表示される値になります。 (Power Query での数値の精度の詳細については、「Power Query でのデータ型」の「10 進数」と「固定小数点数」のセクションを参照してください。)

オンライン Excel ブックへの接続

Sharepoint でホストされている Excel ドキュメントに接続する場合は、Power BI Desktop、Excel、データフローの Web コネクタを使用して行うことができます。データフローの Excel コネクタを使用して行うこともできます。ファイルへのリンクを取得するには:

Excel デスクトップでドキュメントを開きます。
[ファイル] メニューの [情報] タブを選び、[パスのコピー] を選びます。
アドレスを [File Path or URL]\(ファイルパスまたは URL\) フィールドにコピーし、アドレスの末尾から ?web=1 を削除します。

レガシ ACE コネクタ

Power Query は、Access データベースエンジン (または ACE) OLEDB プロバイダーを使用して従来のブック (.xls や .xlsb など) を読み取ります。そのため、OpenXML ブック (.xlsx など) をインポートするときには発生しない予期しない動作が、レガシブックをインポートするときに発生する可能性があります。一般的な例を次にいくつか挙げます。

予期しない値の書式

ACE が原因で、レガシ Excel ブックの値が予想よりも低い精度または忠実度でインポートされる場合があります。たとえば、Excel ファイルに 1024.231 という数字が含まれていて、これが "1,024.23" として表示されるように書式設定したとします。 Power Query にインポートした場合、この値は、基になる完全な忠実度の数値 (1024.231) としてではなく、テキスト値 "1,024.23" として表されます。その理由は、この場合、ACE が Power Query に表示するのは基になる値ではなく、Excel に表示されている値にすぎないためです。

予期しない null 値

ACE は、シートを読み込むときに、最初の 8 行を参照して列のデータ型を決定します。最初の 8 行がそれより後の行の典型ではない場合、ACE はその列に正しくない型を適用したり、型と一致しない値に対して null を返したりする可能性があります。たとえば、ある列の最初の 8 行には数値 (1000、1001 など) が含まれていて、それより後の行には数値以外のデータ ("100Y" や "100Z" など) が含まれている場合、ACE は列に数値が含まれているものと見なし、数値以外の値は null として返します。

一貫性のない値の書式設定

ACE は、最新の情報に更新した後でまったく異なる結果を返すことがあります。書式設定のセクションで説明した例を使用すると、値が突然に "1,024.23" ではなく 1024.231 と表示されることがあります。この違いは、レガシブックを Power Query にインポートするときに Excel で開いていることが原因の可能性があります。この問題を解決するには、ブックを閉じます。

欠落または不完全な Excel データ

Power Query で、Excel ワークシートからすべてのデータを抽出できない場合があります。多くの場合、このエラーは、ワークシートに正しくないディメンションがあることが原因で発生します (たとえば、実際のデータが 3 列または 200 行より多く占有する場合に A1:C200 のディメンションがあるなど)。

正しくないディメンションを診断する方法

ワークシートのディメンションを表示するには:

xlsx ファイルの名前を .zip 拡張子に変更します。
エクスプローラーでファイルを開きます。
xl\worksheets に移動します。
問題のあるシートの xml ファイル (たとえば、Sheet1.xml) を zip ファイルから別の場所にコピーします。
ファイルの最初の数行を調べます。ファイルが十分に小さい場合は、テキストエディターで開きます。ファイルが大きすぎてテキストエディターで開けない場合は、コマンドプロンプトから more Sheet1.xml コマンドを実行します。
<dimension .../> タグ (例: <dimension ref="A1:C200" />) を探します。

ファイルに単一のセルをポイントするディメンション属性 (<dimension ref="A1" /> など) がある場合、Power Query はこの属性を使用して、シート上のデータの開始行と列を検索します。

一方、ファイルに複数のセルをポイントするディメンション属性 (<dimension ref="A1:AJ45000"/> など) がある場合、Power Query はこの範囲を使用して、開始行と列および終了行と列を検索します。この範囲にシート上のすべてのデータが含まれていない場合、一部のデータは読み込まれません。

正しくないディメンションを修正する方法

次のいずれかの操作を行って、正しくないディメンションが原因で発生する問題を修正できます。

Excel でドキュメントを開き、再保存します。この操作により、ファイルに格納されている正しくないディメンションが正しい値で上書きされます。
Excel ファイルを生成したツールがディメンションを正しく出力するように修正されていることを確認します。
正しくないディメンションを無視するように M クエリを更新します。 Power Query の 2020 年 12 月のリリース時点で、Excel.Workbook では InferSheetDimensions オプションがサポートされるようになりました。このオプションが true の場合、関数はブックに格納されているディメンションを無視し、代わりにデータを調べることでそれらを決定します。

このオプションを指定する方法の例を次に示します。

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Excel データの読み込みが遅いのは、正しくないディメンションが原因となっている場合もあります。ただし、この場合の速度低下は、ディメンションのサイズが小さすぎるのではなく、必要以上に大きいことが原因です。ディメンションが大きすぎると、Power Query がブックから読み取るデータ量が、実際に必要な量よりはるかに多くなります。

この問題を解決するには、「ワークシートの最後のセルを特定してリセットする」で詳細な手順を確認できます。

マシン上の Excel または SharePoint からデータを取得する場合は、関係するデータの量とブックの複雑さの両方を考慮してください。

SharePoint からとても大きなファイルを取得すると、パフォーマンスが低下することがわかります。ただし、これは問題の一部にすぎません。 SharePoint から取得される Excel ファイルに重要なビジネスロジックがある場合、データを更新するときにこのビジネスロジックを実行する必要が生じ、複雑な計算が発生する可能性があります。データの集計と事前計算を行うか、Excel レイヤーから Power Query レイヤーにより多くのビジネスロジックを移動することを検討してください。

Excel コネクタを使用して CSV ファイルをインポートするときのエラー

CSV ファイルは、Excel で開くことができますが、Excel ファイルではありません。代わりに、Text/CSV コネクタを使用してください。

「Strict Open XML Spreadsheet」ワークブックのインポート時のエラー

Excel の「Strict Open XML Spreadsheet」形式で保存されたワークブックをインポートすると、次のエラーが表示される場合があります。

DataFormat.Error: The specified package is invalid. The main part is missing.

このエラーは、ACE ドライバがホストコンピュータにインストールされていない場合に発生します。「Strict Open XML Spreadsheet」形式で保存されたワークブックは、ACE でのみ読み取ることができます。ただし、このようなワークブックは通常の Open XML ワークブック (.xlsx) と同じファイル拡張子を使用するため、この拡張子を使用して通常の the Access Database Engine OLEDB provider may be required to read this type of file エラーメッセージを表示することはできません。

エラーを解決するには、ACE ドライバーをインストールします。クラウドサービスでエラーが発生している場合は、ACE ドライバーがインストールされているコンピューター上で実行されているゲートウェイを使用する必要があります。

「ファイルには破損したデータが含まれています」エラー

特定の Excel ワークブックをインポートするときに、次のエラーが表示される場合があります。

DataFormat.Error: File contains corrupted data.

通常、このエラーはファイルの形式に問題があることを示します。

ただし、ファイルが Open XML ファイル (.xlsx など) であるように見えても、実際にはファイルを処理するために ACE ドライバーが必要な場合に、このエラーが発生することがあります。 ACE ドライバーを必要とするファイルの処理方法の詳細については、「レガシー ACE コネクタ」セクションを参照してください。

既知の問題と制限事項

Power Query Online は、暗号化された Excel ファイルにアクセスできません。 "Public" または "Non-Business" 以外の秘密の種類でラベル付けされた Excel ファイルは暗号化されるため、Power Query Online からはアクセスできません。
Power Query Online では、パスワード保護された Excel ファイルはサポートしていません。

次の方法で共有