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

Импорт источников данных

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

1. Выберите "Данные>""Получить данные".

   

2. Чтобы выбрать нужный источник данных, выберите Получить данные (Power Query).

3. В диалоговом окне "Выбор источника данных " выберите один из доступных источников данных.

   

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

5. Выбор данных для импорта.

6. Загрузите данные, нажав кнопку "Загрузить ".

Результат

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

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

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

Формирование данных с помощью редактора Power Query

Процедура

1. Выберите "Данные>"Получить данные (Power Query).

2. Чтобы открыть Редактор запросов, выберите "Запустить Редактор Power Query".

   

   Совет

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

3. Вы можете формировать и преобразовывать данные с помощью редактора запросов, как в Excel для Windows.

   

   Дополнительные сведения см. в разделе справка по Power Query для Excel.

4. Когда все будет готово, нажмите "Главная>","Закрыть" & "Загрузить".

Результат

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

Обновление источников данных

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

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

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

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

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

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

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

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

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

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

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

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

Например:

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

  

• Учетные данные SQL Server:

  

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

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

Создание и передача кода Power Query VBA

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

Шаг 1. Excel для Windows

1. В Excel для Windows разрабатывайте запросы с помощью VBA. Код VBA, использующий следующие сущности в объектной модели Excel, также работает в Excel для Mac: объект queries, объект WorkbookQuery, свойство Workbook.Queries. Дополнительные сведения см. в справочнике по Excel VBA.

2. В Excel проверьте, открыт ли редактор Visual Basic, нажав клавиши ALT+F11.

3. Щелкните модуль правой кнопкой мыши и выберите Экспорт файла. Откроется диалоговое окно Экспорт.

4. Введите имя файла, убедитесь, что файл имеет расширение .bas, а затем выберите Сохранить.

5. Загрузите файл VBA в веб-службу, чтобы сделать файл доступным с Mac.

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

Шаг 2. Excel для Mac

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

Импорт и обновление файлов TXT или CSV.

1. Откройте книгу Excel.

2. Если вы получили предупреждение системы безопасности о том, что подключения к внешним данным отключены, выберите "Включить содержимое".

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

4. Выберите "Данные>из текста" (прежние версии)". Откроется диалоговое окно Finder .

5. Найдите файл .txt или .csv и нажмите кнопку "Открыть".Откроется мастер импорта текста .

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

6. На первой странице сделайте следующее:
   Тип файла Чтобы выбрать тип текстового файла, выберите "С разделителями " или "Фиксированная ширина".

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

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

7. На второй странице сделайте следующее:

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

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

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

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

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

8. На третьей странице сделайте следующее:

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

   Нажмите кнопку Готово. Откроется диалоговое окно Импорт данных.

9. Выберите, куда вы хотите добавить данные: на существующий лист, на новый лист или в сводную таблицу.

10. Нажмите кнопку ОК.
    Чтобы проверить, работает ли соединение, введите некоторые данные, а затем выберите "Обновление подключения>".

Подключение к базе данных SQL (ODBC)

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

   

2. Введите имя сервера в поле "Имя сервера " и базу данных в поле "Имя базы данных " (необязательно).

   Получите эти сведения от администратора базы данных.

3. В разделе Проверка подлинности выберите метод из списка: Имя пользователя и пароль, Kerberos, или NTLM.

4. Введите учетные данные в поля "Имя пользователя " и "Пароль ".

5. Нажмите Подключиться. Откроется диалоговое окно "Навигатор ".

6. На панели слева перейдите к нужной таблице и выберите ее.

7. Подтвердите оператор SQL на панели справа. Вы можете изменить оператор SQL по своему усмотрению.

8. Для предварительного просмотра данных выберите Выполнить.

9. Когда все будет готово, выберите Вернуть данные. Откроется диалоговое окно Импорт данных.

   

10. Выберите, куда вы хотите добавить данные: на существующий лист, на новый лист или в сводную таблицу.

11. Чтобы задать свойства соединения, на вкладках Использование и Определение диалогового окна Свойства выберите Свойства. После импорта данных можно также выбрать"Подключения к данным>", а затем в диалоговом окне "Свойства подключения" выбрать пункт "Свойства".

12. Нажмите кнопку ОК.

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

Подключение к другой базе данных (Microsoft Query)

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

1. Выберите "Данные>из базы данных (Microsoft Query)".

2. Добавьте источник данных для своей базы данных и выберите ОК.

3. При запросе учетных данных SQL Server введите метод проверки подлинности, имя пользователя и пароль.

   

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

5. Выберите стрелку рядом с нужной базой данных.

6. Выберите нужную таблицу.

7. Для предварительного просмотра данных выберите Выполнить.

8. Когда все будет готово, выберите Вернуть данные.

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

10. Нажмите кнопку ОК.

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

Очистите свои учетные данные для входа

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

1. Выберите "Подключенияк данным>". Появится диалоговое окно Подключения к книге.

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