Excel для Mac включает в себя технологию Power Query (также называемую "Получить и преобразовать"), обеспечивающую более широкие возможности при импорте, обновлении и проверке подлинности источников данных, управлении источниками данных Power Query, очистке учетных данных, изменении расположения файловых источников данных и преобразование данных в таблицу, соответствующую вашим требованиям. Вы также можете создать запрос Power Query с помощью VBA.
Примечание: Источник данных базы данных 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.Синхронизация файлов с OneDrive в Mac OS X.
Вы можете использовать Microsoft OneDrive. Дополнительные сведения см. в статье
Шаг 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 введите метод проверки подлинности, имя пользователя и пароль.
-
Выберите стрелку рядом с сервером слева, чтобы увидеть базы данных.
-
Выберите стрелку рядом с нужной базой данных.
-
Выберите нужную таблицу.
-
Для предварительного просмотра данных выберите Выполнить.
-
Когда все будет готово, выберите Вернуть данные.
-
В диалоговом окне Импорт данных выберите, где вы хотите разместить данные: на существующем листе, на новом листе или в сводной таблице.
-
Нажмите кнопку ОК.
-
Чтобы проверить, работает ли подключение, введите некоторые данные, а затем выберите Данные > Обновить все.
Если ваши разрешения не работают, вы не можете сначала очистить их, а затем войти в систему.
-
Выберите Данные > Подключения. Появится диалоговое окно Подключения к книге.
-
Выберите нужное подключение в списке, а затем нажмите Очистить разрешения.
См. также
Справка по Power Query для Excel