Excel

まとめ

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

注意

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

前提条件

レガシ ブック (.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 から接続するには:

  1. コネクタの選択で [Excel] オプションを選びます。

  2. 読み込む Excel ブックを参照して選びます。 その後、 [開く] を選択します。

    エクスプローラーから Excel ブックを選びます。

    Excel ブックがオンラインの場合は、Web コネクタを使用してブックに接続します。

  3. ナビゲーター で、目的のブック情報を選び、 [読み込み] を選んでデータを読み込むか、 [データの変換] を選んで Power Query エディター内でデータの変換を続行します。

    Power Query Desktop のナビゲーターにインポートされた Excel ブック。

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

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

  1. コネクタの選択で [Excel] オプションを選びます。

  2. 表示される [Excel] ダイアログ ボックスで、Excel ブックのパスを指定します。

    Excel ブックにアクセスするための接続情報。

  3. 必要に応じて、オンプレミスのデータ ゲートウェイを選んで Excel ブックにアクセスします。

  4. この Excel ブックに今回初めてアクセスした場合は、認証の種類を選び、アカウントにサインインします (必要な場合)。

  5. ナビゲーター で、目的のブック情報、 [データの変換] の順に選んで、Power Query エディター内でデータの変換を続行します。

    Power Query Online のナビゲーターにインポートされた Excel ブック。

トラブルシューティング

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

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 コネクタを使用して行うこともできます。 ファイルへのリンクを取得するには:

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

レガシ ACE コネクタ

Power Query は、レガシ ブック (.xls や .xlsb など) を読み取り、Access Database Engine (または ACE) OLEDB プロバイダーを使用します。 そのため、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 のディメンションがあるなど)。

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

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

  1. xlsx ファイルの名前を .zip 拡張子に変更します。
  2. エクスプローラーでファイルを開きます。
  3. xl\worksheets に移動します。
  4. 問題のあるシートの xml ファイル (たとえば、Sheet1.xml) を zip ファイルから別の場所にコピーします。
  5. ファイルの最初の数行を調べます。 ファイルが十分に小さい場合は、テキスト エディターで開きます。 ファイルが大きすぎてテキスト エディターで開けない場合は、コマンド プロンプトから more Sheet1.xml コマンドを実行します。
  6. <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 データの読み込み時のパフォーマンスの低下

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

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

SharePoint からデータを読み込むときのパフォーマンスの低下

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

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

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

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