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

Імпорт джерел даних

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

1. Виберіть "Дані>".

   

2. Щоб вибрати потрібне джерело даних, виберіть Get Data (Power QueryPower Query).

3. У діалоговому вікні " Вибір джерела даних " виберіть одне з доступних джерел даних.

   

4. Підключіться до джерела даних. Щоб дізнатися, як підключитися до кожного джерела даних, див. статтю "Імпорт даних із джерел даних".

5. Виберіть дані, які потрібно імпортувати.

6. Завантажте дані, натиснувши кнопку "Завантажити ".

Результат

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

Наступні кроки

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

Дані фігури за допомогою Редактор Power Query

Процедура

1. Select Data>Get Data (Power QueryPower Query).

2. Щоб відкрити Редактор Power Query, натисніть кнопку "Запустити Редактор Power Query".

   

   Порада.

   Ви також можете перейти до Редактор Power Query, натиснувши кнопку Отримати дані (Power Query), вибравши джерело даних, а потім натиснувши кнопку Далі.

3. Формуйте й перетворюйте дані за допомогою Редактор Power Query так само, як в Excel для Windows.

   

   Докладні відомості див. в Power Query довідці Excel.

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

Результат

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

Оновлення джерел даних

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

Перше оновлення

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

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

Оновлювати наступні рази.

Щоб оновити сторінку, виконайте наведені нижче дії.

• Усі джерела даних у книзі , натисніть кнопку>"Оновити все".
• певного джерела даних, клацніть правою кнопкою миші таблицю запиту на аркуші, а потім натисніть кнопку "Оновити".
• Виберіть зведену таблицю, виділіть клітинку у зведеній таблиці, а потім виберіть "Аналіз зведеної таблиці""Оновити> дані".

Введення та видалення облікових даних

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

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

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

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

Наприклад:

• Облікові дані SharePoint.

  

• SQL ServerSQL Server credentials:

  

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

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

Створення та перенесення коду VBA Power Query

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

Крок перший. Excel для Windows

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

2. Переконайтеся, що редактор Visual Basic відкрито в програмі Excel, натиснувши клавіші Alt+F11.

3. Клацніть модуль правою кнопкою миші та виберіть Експортувати файл. Відобразиться діалогове вікно "Експорт ".

4. Введіть ім'я файлу, переконайтеся, що він має розширення .bas, і натисніть кнопку "Зберегти".

5. Передайте файл VBA до онлайнової служби, щоб мати змогу отримувати до нього доступ із комп'ютера Mac.

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

Крок другий. Excel для Mac

1. Завантажте файл VBA у локальний файл VBA, збережений на кроці першому. Excel для Windows і завантажений до онлайнової служби.
2. В Excel для Mac виберіть "Знаряддя>макросів>"Редактор Visual Basic. Відкриється вікно редактора Visual Basic .
3. Клацніть правою кнопкою миші об'єкт у вікні програми Project, а потім виберіть пункт "Імпортувати файл". Відкриється діалогове вікно "Імпорт файлу ".
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. На другій сторінці виконайте такі дії:

   З розділенням

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

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

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

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

8. На третій сторінці виконайте такі дії:

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

   Натисніть Готово. Відобразиться діалогове вікно "Імпорт даних ".

9. Виберіть, куди потрібно додати дані: на наявному аркуші, на новому аркуші або у зведеній таблиці.

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

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

1. Select Data>From SQL ServerSQL Server ODBC. Відкриється діалогове вікно "Підключення SQL Server до джерела даних ODBC".

   

2. Введіть назву сервера в полі "Ім'я сервера " та, за потреби, базу даних у полі " Ім'я бази даних ".

   Зверніться до адміністратора бази даних.

3. У розділі "Автентифікація" виберіть метод зі списку: ім'я користувача або пароль, Kerberos або протокол NTLM.

4. Введіть облікові дані в полях « Ім'я користувача» та « Пароль ».

5. Виберіть Підключитися. Відкриється діалогове вікно "Навігатор ".

6. В області ліворуч перейдіть до потрібної таблиці та виберіть її.

7. Підтвердьте інструкцію SQL в області праворуч. Ви можете змінити інструкцію SQL на власний розсуд.

8. Щоб переглянути дані, натисніть кнопку "Виконати".

9. Коли все буде готово, виберіть Return Data. Відобразиться діалогове вікно "Імпорт даних ".

   

10. Виберіть, куди потрібно додати дані: на наявному аркуші, на новому аркуші або у зведеній таблиці.

11. Щоб установити властивості підключення, у діалоговому вікні "Властивості" на вкладках "Використання" та "Визначення" натисніть кнопку "Властивості". Імпортувавши дані, ви також можете вибрати елемент"Зв'язкиданих>", а потім у діалоговому вікні "Властивості підключення" натиснути кнопку "Властивості".

12. Натисніть кнопку OK.

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

Підключення до іншої бази даних (Microsoft Query)

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

1. Вибір даних>із бази даних (Microsoft Query).

2. Додайте джерело даних для бази даних і натисніть кнопку OK.

3. У вікні SQL Server облікових даних введіть спосіб автентифікації, ім'я користувача та пароль.

   

4. Ліворуч клацніть стрілку поруч із сервером, щоб переглянути бази даних.

5. Клацніть стрілку поруч із потрібною базою даних.

6. Виберіть потрібну таблицю.

7. Щоб переглянути дані, натисніть кнопку "Виконати".

8. Коли все буде готово, виберіть Return Data.

9. У діалоговому вікні "Імпорт даних " виберіть, де потрібно розташувати дані: на наявному аркуші, на новому аркуші або у зведеній таблиці.

10. Натисніть кнопку OK.

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

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

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

1. Виберіть"Зв'язкиданих>". Відкриється діалогове вікно «Підключення книги ».

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