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.

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

• Import

Подключение книге 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,04900000000000000000000000000000000000002. Это значение 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 не отображает базовое значение в Power Query, а только значение, отображаемое в Excel.

Непредвиденные значения 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.

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

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

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

Эта ошибка возникает, когда драйвер ACE не установлен на хост-компьютере. Книги, сохраненные в формате "Строгая электронная таблица Open XML", могут читаться только 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 устаревшей версии ACE.

Известные проблемы и ограничения

• Power Query Online не может получить доступ к зашифрованным файлам Excel. Так как файлы Excel, помеченные типами конфиденциальности, отличными от "Public" или "Non-Business", шифруются, они недоступны через Power Query Online.
• Power Query Online не поддерживает файлы Excel, защищенные паролем.