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

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

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

1. Выберите Data>Get Data (Получить данные).

   

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. Выберите Data> GetData Source Settings (Получитьданные).>
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: объект Запросы, Объект WorkbookQuery, Свойство Workbook.Queries. Дополнительные сведения см. в справочнике по VBA Для Excel.

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. Выберите Данные>из текста (устаревшая версия). Откроется диалоговое окно Поиск .

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

   Совет Несколько раз проверка предварительной версии выбранной области данных, чтобы подтвердить выбор.

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

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

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

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

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

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

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

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

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

8. На третьей странице выполните следующие действия.

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

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

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

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

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

1. Выберите Данные>из SQL Server ODBC. Откроется диалоговое окно подключения к источнику данных 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. Выберите нужное подключение в списке, а затем нажмите Очистить разрешения.