Импортиране и оформяне на данни в 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. Заредете данните, като щракнете върху бутона Зареждане .

Result

Импортираните данни се показват в нов лист.

Следващи стъпки

За да оформяте и трансформирате данни с помощта на Редактор на 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. Когато сте готови, изберете Начало > Затвори & Зареждане.

Result

Ново импортираните данни се показват в нов лист.

Можете да обновите следните източници на данни: файлове на 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. В долната част изберете Изчистване на permissions.

4. Потвърдете, че искате да направите това, след което изберете Изтрий.

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

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

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

2. В Excel се уверете, че редакторът на Visual Basic е отворен, като натиснете 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. Щракнете с десния бутон върху обект в прозореца на 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

1. Отворете работната книга на Excel.

2. Ако получите предупреждение на защитата за забраняване на връзки с външни данни, изберете Разрешаване на съдържание.

3. Ако се появи диалоговият прозорец Предоставяне на достъп до файл , изберете Избор и след това изберете Предоставяне на достъп до папката от най-горно ниво, съдържаща файловете с източници на данни.

4. Изберете > данни от текст (наследен).. Появява се диалоговият прозорец Finder .

5. Намерете .txt или .csv файл и изберете Отвори. Показва се съветникът за импортиране на текст .
   
   Съвет     Проверявайте неколкократно визуализацията на избрания екран с данни, за да потвърдите избора си.

6. На първата страница направете следното:

   Тип на файла    За да изберете типа на текстовия файл, изберете С разделители или Фиксирана ширина.
   
   Номер на ред    В Започни импортирането от ред изберете номер на ред, за да зададете първия ред с данни, които искате да импортирате.
   
   Набор знаци     В Произход на файла изберете набора знаци, който се използва в текстовия файл. В повечето случаи можете да оставите тази настройка по подразбиране.

7. На втората страница направете следното:
   
   С разделители
   
   Ако сте избрали С разделители на първата страница, под Разделители изберете знака за разделител или използвайте квадратчето за отметка Други, за да въведете този, който не е в списъка.
   
   Изберете Третирай последователните разделители като един , ако данните съдържат разделител на повече от един знак между полетата за данни или ако вашите данни съдържат няколко разделители по избор.
   
   В Текстов ограничител изберете знака, който огражда стойностите във вашия текстов файл, който най-често е знакът за кавичка (").

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

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

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

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

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

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

2. Въведете сървъра в полето Име на сървъра и ако желаете, въведете базата данни в полето Име на базата данни .
   
   Получаване на тази информация от администратора на базата данни.

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

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

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

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

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

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

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

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

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

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

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

Ако искате да използвате външен източник, който не е 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. Изберете желаната връзка в списъка и след това изберете Изчистване на разрешенията.