Імпорт і формування даних в Excel для Mac (Power Query)

Excel для Mac включає технологію Power Query (яка також називається Get & Transform) для забезпечення більшої можливості під час імпорту, оновлення та автентифікації джерел даних, керування Power Query джерелами даних, очищення облікових даних, змінення розташування файлових джерел даних і формування даних у таблицю, яка відповідає вашим вимогам. Також можна створити запит Power Query за допомогою VBA.

Microsoft 365 Спадщина

Примітка.: SQL Server джерело даних бази даних можна імпортувати лише в бета-версіях для оцінювачів.

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

Виберіть Дані > Отримати дані.
Щоб вибрати потрібне джерело даних, натисніть кнопку Отримати дані (Power Query).
У діалоговому вікні Вибір джерела даних виберіть одне з доступних джерел даних.
Підключення до джерела даних. Докладні відомості про підключення до кожного джерела даних див. в статті Імпорт даних із джерел даних.
Виберіть дані, які потрібно імпортувати.
Завантажте дані, натиснувши кнопку Завантажити .

Результат

Імпортовані дані відобразяться на новому аркуші.

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

Подальші дії

Щоб формувати й перетворювати дані за допомогою Редактор Power Query, натисніть кнопку Перетворити дані. Докладні відомості див. в статті Дані фігури з Редактор Power Query.

Примітка.: Ця функція зазвичай доступна для абонентів Microsoft 365 під керуванням Версії 16.69 (23010700) або новішої версії Excel для Mac. Якщо ви абонент Microsoft 365, переконайтеся, що маєте найновішу версію Office.

Процедура

Виберіть Дані > Отримати дані (Power Query).
Щоб відкрити Редактор Power Query, натисніть кнопку Запустити Редактор Power Query.

Порада.: Щоб отримати доступ до Редактор Power Query, натисніть кнопку Отримати дані (Power Query), виберіть джерело даних і натисніть кнопку Далі.
Формуйте та перетворюйте дані, використовуючи Редактор Power Query, як у програмі Excel для Windows.

Докладні відомості див. в Power Query довідки Excel.
Завершивши, натисніть кнопку Основне > Закрити & Завантажити.

Результат

Щойно імпортовані дані з'являться на новому аркуші.

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

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

Оновіть перший раз

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

Виберіть пункт Дані, стрілка поруч із кнопкою Отримати дані, а потім – Параметри джерела даних. Відкриється діалогове вікно Параметри джерела даних .
Виберіть підключення та натисніть кнопку Змінити шлях до файлу.
У діалоговому вікні Шлях до файлу виберіть нове розташування, а потім натисніть кнопку Отримати дані.
Натисніть кнопку Закрити.

Оновити наступні часи

Щоб оновити оновлення, виконайте наведені нижче дії.

Усі джерела даних у книзі виберіть пункт Дані > Оновити все.
Конкретне джерело даних, клацніть правою кнопкою миші таблицю запиту на аркуші, а потім виберіть оновити.
Зведена таблиця, виділіть клітинку у зведеній таблиці, а потім виберіть пункт Зведена таблиця Аналізувати > Оновити дані.

Під час першого доступу до SharePoint, SQL Server, OData або інших джерел даних, для яких потрібен дозвіл, потрібно надати відповідні облікові дані. Ви також можете очистити облікові дані, щоб ввести нові.

Введіть облікові дані

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

Якщо потрібно ввійти, відкриється діалогове вікно Введення облікових даних .

Наприклад:

Облікові дані SharePoint:
SQL Server облікові дані:

Очистити облікові дані

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

Хоча створення Редактор Power Query недоступне в Excel для Mac, VBA підтримує створення Power Query. Перенесення модуля коду VBA у файлі з Excel для Windows до Excel для Mac – це двоетапний процес. У кінці цього розділу вам буде надано зразок програми.

Крок 1. Excel для Windows

В Excel Windows розробляйте запити за допомогою VBA. Код VBA, який використовує такі сутності в об'єктної моделі Excel, також працює в Excel для Mac: об'єкт Query, об'єкт WorkbookQuery, властивість Workbook.Queries.Докладні відомості див. в статті Довідник із VBA в Excel.
Переконайтеся, що редактор Visual Basic відкрито в Excel, натиснувши клавіші Alt+F11.
Клацніть модуль правою кнопкою миші та виберіть пункт Експорт файлу. Відкриється діалогове вікно Експорт .
Введіть ім'я файлу, переконайтеся, що розширення файлу – BAS, а потім натисніть кнопку Зберегти.
Завантажте файл VBA до онлайнової служби, щоб зробити файл доступним із комп'ютера Mac.

Ви можете використовувати Microsoft OneDrive. Докладні відомості див. в статті Синхронізація файлів зі службою OneDrive у Mac OS X.

Крок 2. Excel для Mac

Завантажте файл VBA до локального файлу VBA, збереженого в розділі "Крок один: Excel для Windows", і переданий до онлайнової служби.
В Excel для Mac виберіть Знаряддя > Макрос > редактор Visual Basic. Відкриється вікно Редактора Visual Basic .
Клацніть правою кнопкою миші об'єкт у вікні Project і виберіть пункт Імпортувати файл. Відкриється діалогове вікно Імпорт файлу .
Знайдіть файл 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.
Якщо з'явиться попередження системи безпеки про те, що зв'язки із зовнішніми даними вимкнуто, натисніть кнопку Увімкнути вміст.
Якщо з'явиться діалогове вікно Надати доступ до файлу , натисніть кнопку Вибрати, а потім виберіть пункт Надати доступ до папки верхнього рівня з файлами джерел даних.
Виберіть data > From Text (Legacy). Відкриється діалогове вікно Finder (F-inder ).
Знайдіть файл .txt або .csv і натисніть кнопку Відкрити. Відкриється майстер імпорту тексту .

Порада Кілька разів перевірте попередній перегляд вибраної області даних , щоб підтвердити вибір.
На першій сторінці виконайте такі дії:

Тип файлу    Щоб вибрати тип текстового файлу, виберіть пункт З роздільниками або Фіксована ширина.

Номер рядка    У розділі Почати імпорт із рядка виберіть номер рядка, щоб указати перший рядок даних, які потрібно імпортувати.

Набір символів     У розділі File origin (Походження файлу) виберіть набір символів, який використовується в текстовому файлі. У більшості випадків цей параметр можна залишити за замовчуванням.
На другій сторінці виконайте такі дії:

Розділені

Якщо на першій сторінці вибрано розділ Роздільники, у розділі Роздільники встановіть символ роздільника або за допомогою прапорця Інші введіть символ, відсутній у списку.

Установіть прапорець Розглядати послідовні роздільники як один , якщо дані містять роздільник із кількома символами між полями даних або якщо дані містять кілька настроюваних роздільників.

У текстовому обмежувачі виберіть символ, який охоплює значення в текстовому файлі, який найчастіше є символом лапок (").

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

Якщо на першій сторінці вибрано фіксовану ширину , дотримуйтеся вказівок зі створення, видалення або переміщення рядка розриву в полі Попередній перегляд вибраних даних .
На третій сторінці виконайте такі дії:

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

Натисніть кнопку Готово. Відкриється діалогове вікно Імпорт даних .
Виберіть розташування для додавання даних: на наявному аркуші, на новому аркуші або у зведеній таблиці.
Натисніть кнопку OK.

Щоб переконатися, що підключення працює, введіть деякі дані, а потім виберіть Підключення > Оновити.

Виберіть data > FromSQL Server ODBC. Відкриється діалогове вікно Підключення до SQL Server джерела даних ODBC.
Введіть сервер у полі Ім'я сервера та, за потреби, введіть базу даних у полі Ім'я бази даних .

Отримайте ці відомості від адміністратора бази даних.
У розділі Автентифікація виберіть метод зі списку: Ім'я користувача або пароль, Kerberos або NTLM.
Введіть облікові дані в полях Ім'я користувача та Пароль.
Натисніть кнопку Підключитися. Відкриється діалогове вікно Навігатор .
В області ліворуч перейдіть до потрібної таблиці та виберіть її.
Підтвердьте інструкцію SQL в області праворуч. Інструкцію SQL можна змінити належним чином.
Щоб переглянути дані, натисніть кнопку Виконати.
Коли все буде готово, натисніть кнопку Повернути дані. Відкриється діалогове вікно Імпорт даних .
Виберіть розташування для додавання даних: на наявному аркуші, на новому аркуші або у зведеній таблиці.
Щоб установити властивості підключення на вкладках Використання та визначення діалогового вікна Властивості , натисніть кнопку Властивості. Імпортувавши дані, можна також вибрати параметр Дані > Підключення, а потім у діалоговому вікні Властивості підключення натисніть кнопку Властивості.
Натисніть кнопку OK.
Щоб переконатися, що підключення працює, введіть деякі дані, а потім виберіть пункт Дані > Оновити все.

Якщо потрібно використовувати зовнішнє джерело, яке не є базою даних SQL (наприклад, FileMaker Pro), можна використовувати драйвер ODBC, інстальований на комп'ютері Mac. Відомості про драйвери доступні на цій веб-сторінці. Інсталювавши драйвер джерела даних, виконайте такі дії:

Виберіть Data > From Database (Microsoft Query).
Додайте джерело даних для бази даних і натисніть кнопку OK.
У вікні SQL Server введіть метод автентифікації, ім'я користувача та пароль.
Ліворуч клацніть стрілку поруч із сервером, щоб переглянути бази даних.
Клацніть стрілку поруч із потрібною базою даних.
Виберіть потрібну таблицю.
Щоб переглянути дані, натисніть кнопку Виконати.
Коли все буде готово, натисніть кнопку Повернути дані.
У діалоговому вікні Імпорт даних виберіть розташування даних: на наявному аркуші, на новому аркуші або у зведеній таблиці.
Натисніть кнопку OK.
Щоб переконатися, що підключення працює, введіть деякі дані, а потім виберіть пункт Дані > Оновити все.

Якщо ваші дозволи не працюють, не можна спочатку очистити їх, а потім увійти в систему.

Виберіть Data> Connections (Підключення> даних ). Відкриється діалогове вікно Підключення книги .
Виберіть потрібне підключення в списку та натисніть кнопку Очистити дозволи.