Импорт и обработка данных в Excel для Mac (Power Query)

Excel для Mac включает в себя технологию Power Query (также называемую "Получить и преобразовать"), обеспечивающую более широкие возможности при импорте, обновлении и проверке подлинности источников данных, управлении источниками данных Power Query, очистке учетных данных, изменении расположения файловых источников данных и преобразование данных в таблицу, соответствующую вашим требованиям. Вы также можете создать запрос Power Query с помощью VBA.

Microsoft 365 Устаревшие версии



Примечание: Источник данных базы данных SQL Server можно импортировать только в бета-версии программы предварительной оценки.

Вы можете импортировать данные в Excel с помощью Power Query из различных источников данных: книга Excel, текст/CSV, XML, JSON, база данных SQL Server, список SharePoint Online, OData, пустая таблица и пустой запрос.

Выберите Данные > Получить данные.
Чтобы выбрать нужный источник данных, выберите Получить данные (Power Query).
В диалоговом окне Выбор источника данных выберите один из доступных источников данных.
Подключение к источнику данных. Дополнительные сведения о способах подключения к каждому источнику данных, см. в разделе Импорт данных из источников данных.
Выбор данных для импорта.
Загрузите данные, щелкнув кнопку Загрузить.

Result (Результат)

Импортированные данные появятся на новом листе.

Типовые результаты запроса

Дальнейшие действия

Для формирования и преобразования данных с помощью редактора Power Query, выберите Преобразовать данные. Дополнительные сведения см. в статье Формирование данных с помощью редактора Power Query.

Примечание: Эта функция обычно доступна подписчикам Microsoft 365, использующим версию 16.69 (23010700) или более позднюю версию Excel для Mac. Если вы являетесь подписчиком Microsoft 365, проверьте, установлена ли у вас последняя версия Office.

Последовательность действий

ВыберитеДанные > Получить данные (Power Query).
Чтобы открыть редактор запросов, выберите Запустить редактор Power Query.

Совет: Вы также можете получить доступ к редактору запросов, выбрав Получить данные (Power Query), выбрав источник данных и щелкнув Далее.
Вы можете формировать и преобразовывать данные с помощью редактора запросов, как в Excel для Windows.

Дополнительные сведения см. в разделе справка по Power Query для Excel.
Когда все будет готово, выберите пункт Домашняя страница > Закрыть и загрузить.

Result (Результат)

Недавно импортированные данные появятся на новом листе.

Типовые результаты запроса

Вы можете обновить следующие источники данных: файлы SharePoint, списки SharePoint, папки SharePoint, OData, текстовые/CSV-файлы, книги Excel (.xlsx), файлы XML и JSON, локальные таблицы и диапазоны, а также базу данных Microsoft SQL Server.

Первое обновление

При первой попытке обновить файловые источники данных в запросах книги может потребоваться обновить путь к файлу.

Выберите Данные, стрелку рядом с Получить данные, а затем Параметры источника данных. Появится диалоговое окно Параметры источника данных.
Выберите подключение, а затем выберите Изменить путь к файлу.
В диалоговом окне Путь к файлу выберите новое расположение, а затем выберите Получить данные.
Выберите Закрыть.

Последующие обновления

Чтобы обновить:

Все источники данных в книге — выберите Данные> Обновить все.
Определенный источник данных — щелкните правой кнопкой мыши таблицу запросов на листе и выберите Обновить.
Сводная таблица — выберите ячейку в сводной таблице, а затем выберите Анализ сводной таблицы > Обновить данные.

При первом доступе к Microsoft Office SharePoint Online, SQL Server, OData или другим источникам данных, требующим разрешения, необходимо предоставить соответствующие учетные данные. Также можно очистить учетные данные и ввести новые.

Ввод учетных данных

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

Если требуется вход в систему, появится диалоговое окно Введите учетные данные.

Например:

Учетные данные Microsoft Office SharePoint Online:
Учетные данные SQL Server:

Очистка учетных данных

Выберите Данные > Получить данные > Параметры источника данных.
В диалоговом окне Параметры источника данных выберите нужное подключение.
В нижней части выберите Очистить разрешения.
Подтвердите выбор действия, а затем выберите Удалить.

Хотя разработка в редакторе Power Query недоступна в Excel для Mac, VBA поддерживает разработку Power Query. Перенос модуля кода VBA в файле из Excel для Windows в Excel для Mac — это двухэтапный процесс. Выборка программы приведена в конце этого раздела.

Шаг 1. Excel для Windows

В Excel для Windows разрабатывайте запросы с помощью VBA. Код VBA, использующий следующие сущности в объектной модели Excel, также работает в Excel для Mac: объект запросов, объект WorkbookQuery, свойство Workbook.Queries.Дополнительные сведения см. в справочнике по Excel VBA.
В Excel проверьте, открыт ли редактор Visual Basic, нажав клавиши ALT+F11.
Щелкните модуль правой кнопкой мыши и выберите Экспорт файла. Откроется диалоговое окно Экспорт.
Введите имя файла, убедитесь, что файл имеет расширение .bas, а затем выберите Сохранить.
Загрузите файл VBA в веб-службу, чтобы сделать файл доступным с Mac.

Вы можете использовать Microsoft OneDrive. Дополнительные сведения см. в статье Синхронизация файлов с OneDrive в Mac OS X.

Шаг 2. Excel для Mac

Загрузите файл VBA, сохраненный на этапе "Шаг 1. Excel для Windows" и загруженный в веб-службу, в локальный файл
В Excel для Mac выберите Инструменты > Макрос > Редактор Visual Basic. Появится окноредактора Visual Basic.
Щелкните правой кнопкой мыши объект в окне проекта и выберите Импорт файла. Появится диалоговое окно Импорт файла.
Найдите файл VBA и выберите Открыть.

Выборка кода

Вот базовый код, который вы можете адаптировать и использовать. Это выборка запроса, создающего список со значениями от 1 до 100.

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Откройте книгу Excel.
При получении предупреждения системы безопасности об отключении внешних подключений к данным, выберите Включить содержимое.
Если появится диалоговое окно Предоставить доступ к файлам, выберите Выбрать, а затем выберите Предоставить доступ к папке верхнего уровня, содержащей файлы источников данных.
Выберите Данные > Из текста (прежние версии). Появится диалоговое окно Finder .
Найдите файл .txt или .csv и выберите Открыть. Появится мастер импорта текста.

Совет Несколько раз проверьте панельпредварительного просмотра выбранных данных, чтобы подтвердить свой выбор.
На первой странице сделайте следующее:

Тип файла    Чтобы выбрать тип текстового файла, выберите С разделителями или Фиксированная ширина.

Номер строки    В поле Начать импорт со строки выберите номер строки, чтобы указать первую строку данных для импорта.

Набор символов    В поле Источник файла выберите набор символов, используемый в текстовом файле. В большинстве случаев этот параметр можно оставить по умолчанию.
На второй странице сделайте следующее:

С разделителями

Если вы выбрали С разделителями на первой странице, в разделе Разделители выберите символ-разделитель или установите флажок "Другой", чтобы ввести символ, которого нет в списке.

Выберите Рассматривать последовательные разделители как один, если данные содержат разделитель из более чем одного символа между полями данных или если данные содержат несколько настраиваемых разделителей.

В Text qualifier выберите символ, который заключающий значения в текстовом файле, чаще всего это символ кавычек (").

Фиксированная ширина

Если вы выбрали Фиксированная ширина на первой странице, следуйте инструкциям по созданию, удалению или перемещению линии разрыва в поле Предварительный просмотр выбранных данных .
На третьей странице сделайте следующее:

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

ВыберитеГотово. Откроется диалоговое окно Импорт данных.
Выберите, куда вы хотите добавить данные: на существующий лист, на новый лист или в сводную таблицу.
Нажмите кнопку ОК.

Чтобы проверить, работает ли соединение, введите некоторые данные, а затем выберите Подключения > Обновить.

Выберите Данные > Из ODBC SQL Server. Откроется диалоговое окно подключения к источнику данных SQL Server ODBC.
Введите сервер в поле Имя сервера и, при необходимости, введите базу данных в поле Имя базы данных.

Получите эти сведения от администратора базы данных.
В разделе Проверка подлинности выберите метод из списка: Имя пользователя и пароль, Kerberos, или NTLM.
Введите учетные данные в поля Имя пользователя и Пароль .
Выберите Подключение. Появится диалоговое окно Навигатор.
На панели слева перейдите к нужной таблице и выберите ее.
Подтвердите оператор SQL на панели справа. Вы можете изменить оператор SQL по своему усмотрению.
Для предварительного просмотра данных выберите Выполнить.
Когда все будет готово, выберите Вернуть данные. Откроется диалоговое окно Импорт данных.
Выберите, куда вы хотите добавить данные: на существующий лист, на новый лист или в сводную таблицу.
Чтобы задать свойства соединения на вкладках Использование и Определение диалогового окна Свойства, выберите Свойства. После импорта данных можно также выбрать Данные > Подключения, а затем в диалоговом окне Свойства подключения выбрать Свойства.
Нажмите кнопку ОК.
Чтобы проверить, работает ли подключение, введите некоторые данные, а затем выберите Данные > Обновить все.

Если вы хотите использовать внешний источник, который не является базой данных SQL (например, FileMaker Pro), вы можете использовать драйвер Open Database Connectivity (ODBC), установленный на своем Mac. Информация о драйверах есть на этой веб-странице. После установки драйвера для своего источника данных выполните следующие действия:

Выберите Данные > Из базы данных (Microsoft Query).
Добавьте источник данных для своей базы данных и выберите ОК.
При запросе учетных данных SQL Server введите метод проверки подлинности, имя пользователя и пароль.
Выберите стрелку рядом с сервером слева, чтобы увидеть базы данных.
Выберите стрелку рядом с нужной базой данных.
Выберите нужную таблицу.
Для предварительного просмотра данных выберите Выполнить.
Когда все будет готово, выберите Вернуть данные.
В диалоговом окне Импорт данных выберите, где вы хотите разместить данные: на существующем листе, на новом листе или в сводной таблице.
Нажмите кнопку ОК.
Чтобы проверить, работает ли подключение, введите некоторые данные, а затем выберите Данные > Обновить все.

Если ваши разрешения не работают, вы не можете сначала очистить их, а затем войти в систему.

Выберите Данные > Подключения. Появится диалоговое окно Подключения к книге.
Выберите нужное подключение в списке, а затем нажмите Очистить разрешения.