Excel

Итоги

Предварительные требования

Чтобы подключиться к устаревшей книге (например, .xls или XLSB), требуется поставщик OLEDB (или ACE) Access ядро СУБД. Чтобы установить этот поставщик, перейдите на страницу загрузки и установите соответствующую (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.

Поддерживаемые возможности

• Импорт

Подключение к книге Excel из Power Query Desktop

Чтобы сделать подключение из Power Query Desktop, выполните следующие действия.

1. Выберите файл Excel в функции получения данных. Взаимодействие с данными в Power Query Desktop зависит от приложений. Дополнительные сведения об использовании Power Query Desktop для вашего приложения см. в раздел " Где получить данные".

2. Найдите и выберите книгу Excel, которую вы хотите загрузить. Щелкните Открыть.

   

   Если книга Excel находится в сети, используйте веб-соединитель для подключения к книге.

3. В Навигаторе выберите нужные сведения о книге, а затем выберите "Загрузить", чтобы загрузить данные, или выберите "Преобразовать данные", чтобы продолжить преобразование в Редакторе Power Query.

   

Подключение к книге Excel с использованием Power Query Online

Чтобы сделать подключение из Power Query Online, выполните следующие действия.

1. Выберите параметр книга Excel в интерфейсе извлечения данных. Различные приложения имеют различные способы получения данных в Power Query Online. Дополнительную информацию о том, как получить доступ к функции импорта данных в Power Query Online из вашего приложения, см. в разделе "Где получить данные".

   

2. В появившемся диалоговом окне Excel укажите путь к книге Excel.

   

3. При необходимости выберите локальный шлюз данных для доступа к книге Excel.

4. Если вы впервые обращаетесь к этой книге Excel, выберите тип проверки подлинности и войдите в учетную запись (при необходимости).

5. В Навигаторе выберите информацию о книге, которую вы хотите, а затем воспользуйтесь функцией Преобразовать данные, чтобы продолжить преобразование данных в редакторе Power Query.

   

Предлагаемые таблицы

Если вы подключаетесь к книге Excel, которая не содержит конкретную таблицу, навигатор Power Query пытается создать список рекомендуемых таблиц, которые можно выбрать. Например, рассмотрим следующий пример рабочей книги, содержащей данные от A1 до C5, больше данных от D8 до E10 и больше от C13 до F16.

При подключении к данным в Power Query навигатор Power Query создает два списка. Первый список содержит весь лист книги, а второй список содержит три предлагаемых таблицы.

Если выбрать весь лист в навигаторе, книга отображается, как она появилась в Excel, со всеми пустыми ячейками, заполненными значением NULL.

Если выбрать одну из предлагаемых таблиц, каждая отдельная таблица, которую Power Query смог определить из макета книги, отображается в навигаторе. Например, при выборе таблицы 3 отображаются данные, которые первоначально появились в ячейках C13 на F16.

Устранение неполадок

Числовая точность (или "Почему мои числа изменились?")

При импорте данных Excel можно заметить, что некоторые значения чисел, как представляется, немного изменяются при импорте в Power Query. Например, если выбрать ячейку, содержащую 0.049 в Excel, это число отображается в строке формул как 0,049. Но если вы импортируете ту же ячейку в Power Query и выберите ее, сведения о предварительном просмотре отображаются как 0.0490000000000002 (даже если в таблице предварительной версии она отформатирована как 0,049). В чем причина?

Ответ немного сложный и связан с тем, как Excel сохраняет числа, используя так называемую двоичную плавающую точку. Суть в том, что есть определенные числа, которые Excel не может представить с 100% точностью. Если открыть файл .xlsx и посмотреть фактическое хранящееся значение, обратите внимание, что в файле .xlsx фактически хранится 0.049 как 0.049000000000000002. Это число — значение, считываемое Power Query из .xlsx, и потому оно отображается при выборе ячейки в Power Query. (Дополнительные сведения о числовой точности в Power Query см. в разделах "Десятичное число" и "Фиксированное десятичное число" в разделах Типы данных в Power Query.)

Подключение к книге Excel в Интернете

Если вы хотите подключиться к документу Excel, размещенного в Sharepoint, это можно сделать с помощью веб-соединителя в Power BI Desktop, Excel и потоках данных, а также соединителя Excel в потоках данных. Чтобы получить ссылку на файл, выполните следующие действия.

1. Откройте документ в Excel Desktop.
2. Откройте меню "Файл", перейдите на вкладку "Сведения" и выберите "Путь копирования".
3. Скопируйте адрес в поле "Путь к файлу" или "URL-адрес " и удалите ?web=1 из конца адреса.

Устаревший коннектор ACE

Power Query считывает устаревшие книги (например, .xls и .xlsb) с помощью поставщика OLEDB движка базы данных Access (или ACE). Из-за этой зависимости при импорте устаревших рабочих книг могут возникать непредвиденные поведения, которые не встречаются при импорте рабочих книг OpenXML (например, .xlsx). Ниже приведено несколько типичных примеров.

Непредвиденное форматирование значений

Из-за ACE значения из устаревшей книги Excel могут быть импортированы с меньшей точностью или достоверностью, чем ожидалось. Например, представьте, что файл Excel содержит номер 1024.231, форматированный для отображения как "1024.23". При импорте в Power Query это значение представляется в виде текстового значения "1024.23" вместо базового полного числа (1024.231). Это происходит, так как в этом случае ACE отображает только значение, как оно отображается в Excel в Power Query, а не базовое число.

Неожиданные null-значения

Когда ACE загружает лист, он смотрит на первые восемь строк, чтобы определить типы данных столбцов. Если первые восемь строк не являются репрезентативными для последующих строк, ACE может применить неправильный тип к такому столбцу и возвращать значения NULL для любого значения, которое не соответствует типу. Например, если столбец содержит числа в первых восьми строках (например, 1000, 1001 и т. д.), но имеет нечисловые данные в более поздних строках (например, "100Y" и "100Z"), ACE заключает, что столбец содержит числа, а любые нечисловые значения возвращаются как null.

Несогласованное форматирование значений

В некоторых случаях ACE возвращает совершенно разные результаты при обновлении. Используя пример, описанный в разделе форматирования, вы можете внезапно увидеть значение 1024.231 вместо "1024.23". Это различие может быть вызвано открытием устаревшей книги в Excel при импорте книги в Power Query. Чтобы устранить эту проблему, закройте книгу.

Отсутствующие или неполные данные Excel

Иногда Power Query не может извлечь все данные из листа Excel. Эта ошибка часто вызвана неправильными измерениями листа (например, с измерениямиA1:C200, в которых фактические данные занимают более трех столбцов или 200 строк).

Как диагностировать неправильные измерения

Чтобы просмотреть размеры листа, выполните следующие действия.

1. Переименуйте xlsx-файл с расширением .zip.
2. Откройте файл в Проводнике.
3. Перейдите в xl\worksheets.
4. Скопируйте XML-файл для проблемного листа (например, Sheet1.xml) из ZIP-файла в другое расположение.
5. Проверьте первые несколько строк файла. Если файл достаточно мал, откройте его в текстовом редакторе. Если файл слишком велик, чтобы открыться в текстовом редакторе, выполните следующую команду из командной строки: дополнительные Sheet1.xml.
6. <dimension .../> Найдите тег (например, <dimension ref="A1:C200" />).

Если в файле есть атрибут измерения, указывающий на одну ячейку (например <dimension ref="A1" />), Power Query использует этот атрибут для поиска начальной строки и столбца данных на листе.

Однако если в файле есть атрибут измерения, указывающий на несколько ячеек (например <dimension ref="A1:AJ45000"/>), Power Query использует этот диапазон для поиска начальной строки и столбца , а также конечной строки и столбца. Если этот диапазон не содержит все данные на листе, некоторые данные не загружаются.

Исправление неправильных измерений

Вы можете устранить проблемы, вызванные неправильными измерениями, выполнив одно из следующих действий:

• Откройте и измените документ в Excel. Это действие перезаписывает неправильные размеры, хранящиеся в файле, на правильное значение.

• Убедитесь, что средство, формирующее файл Excel, исправлено для корректного вывода размеров.

• Обновите запрос M, чтобы игнорировать неправильные измерения. По состоянию на выпуск Power Query Excel.Workbook за декабрь 2020 г. теперь поддерживается InferSheetDimensions возможность. Если задано значение true, эта опция заставляет функцию игнорировать размеры, хранящиеся в рабочей книге, и вместо этого определять их, анализируя данные.

  Ниже приведен пример предоставления этого параметра:

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

Низкая скорость или медленная работа при загрузке данных Excel

Неправильные измерения также могут привести к медленной загрузке данных Excel. Однако в этом случае причиной медлительности является то, что размеры значительно больше, чем нужно, а не слишком малы. Слишком большие размеры приводят к тому, что Power Query считывает гораздо больший объем данных из рабочей книги, чем требуется.

Чтобы устранить эту проблему, можно обратиться к Нахождению и сбросу последней ячейки на листе, чтобы получить подробные инструкции.

Низкая производительность при загрузке данных из SharePoint

При получении данных из Excel на компьютере или из SharePoint рассмотрите как объем задействованных данных, так и сложность книги.

Вы можете заметить снижение производительности при получении очень больших файлов из SharePoint. Однако размер файла является лишь одной частью проблемы. Если у вас есть важная бизнес-логика в файле Excel, извлекаемом из SharePoint, эта бизнес-логика может выполняться при обновлении данных, что может привести к сложным вычислениям. Рассмотрите возможность агрегирования и предварительного вычисления данных или частичного переноса бизнес-логики из слоя Excel в слой Power Query.

Ошибки при использовании соединителя Excel для импорта CSV-файлов

Несмотря на то, что CSV-файлы можно открыть в Excel, они не файлы Excel. Вместо этого используйте соединитель Text/CSV.

Ошибка при импорте книг формата "Strict Open XML" таблицы

При импорте рабочих книг, сохраненных в формате "Строгая электронная таблица Open XML", может возникнуть следующая ошибка:

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

Эта ошибка возникает, когда драйвер ACE не установлен на хост-компьютере. Только ACE может считывать книги, сохраненные в формате "Строгая электронная таблица Open XML". Тем не менее, поскольку такие книги используют то же расширение файла, что и обычные книги Open XML (.xlsx), мы не можем использовать расширение для отображения обычного the Access ядро СУБД 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. Так как файлы Excel, помеченные типами конфиденциальности, отличными от "Public" или "Non-Business", шифруются, они недоступны через Power Query Online.
• Power Query Online не поддерживает файлы Excel, защищенные паролем.
• Параметр Excel.WorkbookuseHeaders преобразует числа и даты в текст, используя текущую языковую культуру, поэтому он по-разному ведет себя при выполнении в средах с различной языковой культурой операционной системы. Вместо этого рекомендуется использовать Table.PromoteHeaders .