Импортиране и оформяне на данни в Excel for 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.

   

   Съвет

   Можете също да получите достъп до Редактор на 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 Server:

  

Изчистване на идентификационни данни

1. Изберете "Данни>", "Получаванена настройки на източник на данни>".
2. В диалоговия прозорец за настройки на източник на данниизберете желаната връзка.
3. В долната част изберете "Изчистване на разрешения".
4. Потвърдете, че това е, което искате да направите, и след това изберете "Изтрий".

Създаване и прехвърляне на VBA код на Power Query

Въпреки че авторството в Редактор на Power Query не е налично в Excel for Mac, VBA поддържа авторство в Power Query. Прехвърлянето на модул с код на VBA във файл от Excel за Windows в Excel for Mac е процес от две стъпки. В края на този раздел за вас е предоставена примерна програма.

Стъпка едно: Excel за Windows

1. В Excel Windows разработвайте заявки с помощта на VBA. Кодът на VBA, който използва следните обекти в обектния модел на Excel работи и в Excel за Mac: Обект Queries, обект WorkbookQuery, свойство Workbook.Queries. За повече информация вж. справката за Excel VBA.

2. Уверете се, че редакторът на Visual Basic е отворен в Excel, като натиснете ALT+F11.

3. Щракнете с десния бутон върху модула и след това изберете Експортиране на файл. Появява се диалоговият прозорец "Експортиране ".

4. Въведете име на файл, уверете се, че разширението на файла е .bas, и след това изберете "Запиши".

5. Качете VBA файла в онлайн услуга, за да направите файла достъпен от Mac.

   Можете да използвате Microsoft OneDrive. За повече информация вж. "Синхронизиране на файлове с OneDrive в Mac OS X".

Стъпка две: Excel for Mac

1. Изтеглете VBA файла в локален файл – VBA файла, който записахте в "Стъпка едно: Excel за Windows" и го качихте в онлайн услуга.
2. В Excel for 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. Във втората страница направете следното:

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

   Ако сте избрали "С разделители " на първата страница, под "Разделители" изберете разделителния знак или използвайте квадратчето за отметка "Други", за да въведете такъв, който не е в списъка.

   Изберете "Третирай последователните разделители като един", ако данните ви съдържат разделител с повече от един знак между полетата за данни или ако вашите данни съдържат няколко потребителски разделителя.

   В текстовия ограничител изберете знака, който огражда стойностите в текстовия файл, който е най-често знакът кавичка (").
   Фиксирана ширина

   Ако сте избрали Фиксирана ширина на първата страница, следвайте инструкциите за създаване, изтриване или преместване на линия за прекъсване в полето " Визуализация на избраните данни ".

8. В третата страница направете следното:

   За всяка колона под " Визуализация на избраните данни" я изберете и след това я променете в друг формат на колона, ако искате. Можете допълнително да зададете формата на датата и да изберете "Разширени ", за да промените настройките за числови данни. Можете също да конвертирате данните, след като ги импортирате.

   Изберете Готово. Появява се диалоговият прозорец за импортиране на данни .

9. Изберете къде искате да бъдат добавени данните: в съществуващия лист, в нов лист или в обобщена таблица.

10. Изберете OK.
    За да се уверите, че връзката работи, въведете данни и след това изберете "Обнови връзките>".

Свързване към SQL база данни (ODBC)

1. Изберете данни>от ODBC на SQL Server. Появява се диалоговият прозорец Свързване към SQL Server ODBC източник на данни.

   

2. Въведете сървъра в полето " Име на сървър " и ако желаете, въведете базата данни в полето "Име на базата данни ".

   Получете тази информация от администратора на бази данни.

3. Под "Удостоверяване" изберете метод от списъка: потребителско име/парола, Kerberos или NTLM.

4. Въведете идентификационните данни в полетата " Потребителско име " и "Парола ".

5. Изберете Свързване. Показва се диалоговият прозорец "Навигатор ".

6. В левия екран отидете до желаната таблица и след това я изберете.

7. Потвърдете SQL командата в десния екран. Можете да промените SQL командата, както сметнете за уместно.

8. За да визуализирате данните, изберете "Изпълнение".

9. Изберете "Връщане на данни", когато сте готови. Появява се диалоговият прозорец за импортиране на данни .

   

10. Изберете къде искате да бъдат добавени данните: в съществуващия лист, в нов лист или в обобщена таблица.

11. За да зададете свойства на връзката в разделите "Използване " и "Дефиниция " на диалоговия прозорец "Свойства", изберете "Свойства". След като импортирате данните, можете също да изберете"Връзки с данни>" и след това в диалоговия прозорец "Свойства на връзката" да изберете "Свойства".

12. Изберете OK.

13. За да се уверите, че връзката работи, въведете данни и след това изберете"Обнови всички".>

Свързване към друга база данни (Microsoft Query)

Ако искате да използвате външен източник, който не е SQL база данни (например FileMaker Pro), можете да използвате драйвер Open Database Connectivity (ODBC), инсталиран на вашия Mac. Информация за драйверите е налична на тази уеб страница. След като драйверът за вашия източник на данни е инсталиран, изпълнете следните стъпки:

1. Избор на данни>от база данни (Microsoft Query).

2. Добавете източника на данни за вашата база данни и след това изберете OK.

3. В подканата за идентификационни данни на SQL Server въведете метода за удостоверяване, потребителското име и паролата.

   

4. Вляво изберете стрелката до сървъра, за да видите базите данни.

5. Изберете стрелката до желаната база данни.

6. Изберете желаната таблица.

7. За да визуализирате данните, изберете "Изпълнение".

8. Изберете "Връщане на данни", когато сте готови.

9. В диалоговия прозорец за импортиране на данни изберете къде искате да бъдат разположени данните: в съществуващия лист, в нов лист или в обобщена таблица.

10. Изберете OK.

11. За да се уверите, че връзката работи, въведете данни и след това изберете"Обнови всички".>

Изчистване на вашите идентификационни данни за влизане

Ако разрешенията ви не работят, може първо да не ги изчиствате, след което да влезете.

1. Изберете "Връзкис данни>". Появява се диалоговият прозорец "Връзки на работна книга ".

2. Изберете желаната връзка в списъка и след това изберете "Изчистване на разрешения".