Datu importēšana un piemērošana programmā Excel darbam ar Mac (Power Query)

Datu avotu importēšana

Izmantojot Power Query, programmā Excel varat importēt datus no dažādiem datu avotiem: Excel darbgrāmatas, teksta/CSV, XML, JSON, SQL Server datu bāzes, SharePoint Online saraksta, OData, tukšas tabulas un tukša vaicājuma.

1. Atlasiet Data>Get Data.

   

2. Lai atlasītu vajadzīgo datu avotu, atlasiet Iegūt datus (Power Query).

3. Dialoglodziņā Datu avota izvēle atlasiet kādu no pieejamajiem datu avotiem.

   

4. Izveidojiet savienojumu ar datu avotu. Papildinformāciju par savienojuma izveidi ar katru datu avotu skatiet sadaļā Datu importēšana no datu avotiem.

5. Izvēlieties importējamos datus.

6. Ielādējiet datus, noklikšķinot uz pogas Ielādēt .

Rezultāts

Importētie dati tiek parādīti jaunā lapā.

Nākamās darbības

Lai veidotu un transformētu datus, izmantojot Power Query redaktoru, atlasiet Transformēt datus. Papildinformāciju skatiet sadaļā Datu formēšana, izmantojot Power Query redaktoru.

Datu piemērošana, izmantojot Power Query redaktors

Procedūra

1. Atlasiet Data>Get Data (Power Query).

2. Lai atvērtu Vaicājumu redaktoru, atlasiet Palaist Power Query redaktors.

   

   Padoms

   Varat arī piekļūt Vaicājumu redaktoram, atlasot Iegūt datus (Power Query), izvēloties datu avotu un pēc tam noklikšķinot uz Tālāk.

3. Veidojiet un transformējiet datus, izmantojot Vaicājumu redaktoru, kā to darītu programmā Excel darbam ar Windows.

   

   Papildinformāciju skatiet sadaļā Power Query programmai Excel palīdzība.

4. Kad esat pabeidzis, atlasiet Sākums>Aizvērt & Ielādēt.

Rezultāts

Tikko importētie dati tiek parādīti jaunā lapā.

Datu avotu atsvaidzināšana

Varat atsvaidzināt šādus datu avotus: SharePoint failus, SharePoint sarakstus, SharePoint mapes, OData, teksta/CSV failus, Excel darbgrāmatas (.xlsx), XML un JSON failus, lokālās tabulas un diapazonus, Microsoft SQL Server datu bāzi un mapes.

Atsvaidzināt pirmo reizi

Pirmoreiz mēģinot darbgrāmatas vaicājumos atsvaidzināt failu datu avotus, iespējams, būs jāatjaunina faila ceļš.

1. Atlasiet Dati, bultiņu blakus Iegūt datus un pēc tam Datu avota iestatījumus. Tiek atvērts dialoglodziņš Datu avota iestatījumi .
2. Atlasiet savienojumu un pēc tam atlasiet Mainīt faila ceļu.
3. Dialoglodziņā Faila ceļš atlasiet jaunu atrašanās vietu un pēc tam atlasiet Iegūt datus.
4. Atlasiet Aizvērt.

Atsvaidzināt turpmākās reizes

Lai atsvaidzinātu, veiciet tālāk norādītās darbības.

• Visi datu avoti darbgrāmatā, atlasiet Atsvaidzināt>visu.
• Konkrēts datu avots, ar peles labo pogu noklikšķiniet uz vaicājuma tabulas lapā un pēc tam atlasiet Atsvaidzināt.
• Rakurstabulā, atlasiet rakurstabulas šūnu un pēc tam atlasiet Rakurstabula Analizēt>un atsvaidzināt datus.

Akreditācijas datu ievadīšana un notīrīšana

Pirmo reizi piekļūstot SharePoint, SQL Server, OData vai citiem datu avotiem, kuriem nepieciešama atļauja, ir jānorāda atbilstošie akreditācijas dati. Iespējams, vēlēsities arī notīrīt akreditācijas datus, lai ievadītu jaunus.

Ievadīt akreditācijas datus

Pirmoreiz atsvaidzinot vaicājumu, iespējams, saņemsit uzaicinājumu pieteikties. Atlasiet autentifikācijas metodi un norādiet pieteikšanās akreditācijas datus, lai izveidotu savienojumu ar datu avotu un turpinātu atsvaidzināšanu.

Ja nepieciešama pieteikšanās, tiek parādīts dialoglodziņš Akreditācijas datu ievadīšana .

Piemērs.

• SharePoint akreditācijas dati:

  

• SQL Server akreditācijas dati:

  

Notīrīt akreditācijas datus

1. Atlasiet Dati>Iegūt datu>datu avota iestatījumus.
2. Dialoglodziņā Datu avota iestatījumiatlasiet vēlamo savienojumu.
3. Apakšdaļā atlasiet Notīrīt atļaujas.
4. Apstipriniet, ko vēlaties darīt, un pēc tam atlasiet Dzēst.

Power Query VBA koda autorēšana un pārsūtīšana

Lai gan autorēšana Power Query redaktorā nav pieejama programmā Excel darbam ar Mac, VBA atbalsta Power Query autorēšanu. VBA koda moduļa pārsūtīšana failā no Excel for Windows uz Excel darbam ar Mac ir divpakāpju process. Programmas paraugs ir sniegts šīs sadaļas beigās.

Pirmais solis: Excel darbam ar Windows

1. Operētājsistēmā Excel Windows izstrādājiet vaicājumus, izmantojot VBA. VBA kods, kas Excel objekta modelī izmanto šādas entītijas, darbojas arī programmā Excel darbam ar Mac: Objekts Queries, objekts WorkbookQuery, rekvizīts Workbook.Queries. Papildinformāciju skatiet Excel VBA atsaucē.

2. Programmā Excel pārliecinieties, vai ir atvērts Visual Basic redaktors, nospiežot taustiņu kombināciju ALT+F11.

3. Ar peles labo pogu noklikšķiniet uz moduļa un pēc tam atlasiet Eksportēt failu. Tiek atvērts eksportēšanas dialoglodziņš.

4. Ievadiet faila nosaukumu, pārliecinieties, vai faila paplašinājums ir .bas, un pēc tam atlasiet Saglabāt.

5. Augšupielādējiet VBA failu tiešsaistes pakalpojumā, lai padarītu failu pieejamu no Mac datora.

   Varat izmantot Microsoft OneDrive. Papildinformāciju skatiet sadaļā Failu sinhronizēšana ar OneDrive operētājsistēmā Mac OS X.

Otrā darbība: Excel darbam ar Mac

1. Lejupielādējiet VBA failu lokālajā failā, VBA failā, kuru saglabājāt pirmajā darbībā: Excel darbam ar Windows un augšupielādējāt tiešsaistes pakalpojumā.
2. Programmā Excel darbam ar Mac atlasiet Rīki>Makro>Visual Basic redaktors. Tiek parādīts Visual Basic redaktora logs.
3. Projekta logā ar peles labo pogu noklikšķiniet uz objekta un pēc tam atlasiet Importēt failu. Tiek atvērts dialoglodziņš Faila importēšana .
4. Atrodiet VBA failu un pēc tam atlasiet Atvērt.

Koda paraugs

Šeit ir daži pamata kodi, kurus varat pielāgot un izmantot. Šis ir vaicājuma paraugs, kurā tiek izveidots saraksts ar vērtībām no 1 līdz 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 vai CSV failu importēšana un atsvaidzināšana

1. Atveriet Excel darbgrāmatu.

2. Ja tiek parādīts drošības brīdinājums par atspējotiem ārējiem datu savienojumiem, atlasiet Iespējot saturu.

3. Ja tiek parādīts dialoglodziņš Piešķirt failu piekļuvi , atlasiet Atlasīt un pēc tam atlasiet Piešķirt piekļuvi augšējā līmeņa mapei, kurā ir datu avota faili.

4. Atlasīt datus>no teksta (mantota). Tiek parādīts dialoglodziņš Finder .

5. Atrodiet .txt vai .csv failu un pēc tam atlasiet Atvērt. Tiek atvērts Teksta importēšanas vednis .

   Padoms Atkārtoti pārbaudiet atlasīto datu rūts priekšskatījumu , lai apstiprinātu savu izvēli.

6. Pirmajā lapā veiciet tālāk norādītās darbības.
   Faila tips Lai izvēlētos teksta faila tipu, atlasiet Norobežots vai Fiksēts platums.

   Rindas numurs Rindā Sākt importēšanu no rindas atlasiet rindas numuru, lai norādītu pirmo importējamo datu rindu.

   Rakstzīmju kopaSadaļā Faila izcelsme atlasiet rakstzīmju kopu, kas tiek izmantota teksta failā. Vairākumā gadījumu šo iestatījumu var atstāt noklusējuma iestatījumu.

7. Otrajā lapā veiciet tālāk norādītās darbības.

   Norobežots

   Ja pirmajā lapā izvēlējāties Norobežots , sadaļā Norobežotāji atlasiet norobežotāja rakstzīmi vai izmantojiet izvēles rūtiņu Citi, lai ievadītu rakstzīmi, kas nav norādīta sarakstā.

   Atlasiet Secīgus norobežotājus uzskatīt par vienu , ja jūsu datos starp datu laukiem ir vairāk nekā vienas rakstzīmes norobežotājs vai ja datos ir vairāki pielāgoti norobežotāji.

   Teksta ierobežotājā atlasiet rakstzīmi, kas ietver teksta failā ietvertās vērtības, kas visbiežāk ir pēdiņas (") rakstzīme.
   Fiksēts platums

   Ja pirmajā lappusē izvēlējāties Fiksēts platums , lodziņā Atlasīto datu priekšskatījums izvēlaties, izpildiet norādījumus, lai izveidotu, dzēstu vai pārvietotu pārtraukuma līniju.

8. Trešajā lapā veiciet tālāk norādītās darbības.

   Katrai kolonnai sadaļā Atlasīto datu priekšskatījums atlasiet to un pēc tam mainiet uz citu kolonnas formātu, ja vēlaties. Varat papildus iestatīt datumu formātu un atlasīt Papildu , lai mainītu skaitlisko datu iestatījumus. Varat arī konvertēt datus pēc to importēšanas.

   Atlasiet Pabeigt. Tiek atvērts dialoglodziņš Datu importēšana .

9. Izvēlieties datu pievienošanas vietu: esošā lapā, jaunā lapā vai rakurstabulā.

10. Atlasiet Labi.
    Lai pārliecinātos, vai savienojums darbojas, ievadiet datus un pēc tam atlasiet Savienojumi>atsvaidzināt.

Savienojuma izveide ar SQL datu bāzi (ODBC)

1. Atlasiet datus>no SQL SQL Server ODBC. Tiek parādīts dialoglodziņš Savienojuma izveide ar SQL SQL Server ODBC datu avotu.

   

2. Lodziņā Servera nosaukums ievadiet servera vārdu un, ja vēlaties, lodziņā Datu bāzes nosaukums ievadiet arī datu bāzi.

   Saņemiet šo informāciju no datu bāzes administratora.

3. Sadaļā Autentifikācija sarakstā atlasiet metodi: Lietotājvārds/parole, Kerberos vai NTLM.

4. Ievadiet akreditācijas datus lodziņā Lietotājvārds un parole .

5. Atlasiet Savienojuma izveidošana. Tiek parādīts dialoglodziņš Navigators .

6. Kreisajā rūtī naviģējiet uz vajadzīgo tabulu un atlasiet to.

7. Apstipriniet SQL priekšrakstu labajā rūtī. SQL priekšrakstu varat mainīt pēc saviem ieskatiem.

8. Lai priekšskatītu datus, atlasiet Palaist.

9. Kad esat gatavs, atlasiet Atgriezt datus. Tiek atvērts dialoglodziņš Datu importēšana .

   

10. Izvēlieties datu pievienošanas vietu: esošā lapā, jaunā lapā vai rakurstabulā.

11. Lai iestatītu savienojuma rekvizītus, dialoglodziņa Rekvizīti cilnēs Lietojums un Definīcija atlasiet Rekvizīti. Pēc datu importēšanas var arī atlasīt Datu> savienojumi un pēc tam dialoglodziņā Savienojuma rekvizīti atlasītRekvizīti.

12. Atlasiet Labi.

13. Lai pārliecinātos, vai savienojums darbojas, ievadiet dažus datus un pēc tam atlasiet Atsvaidzināt datus>visu.

Savienojuma izveide ar citu datu bāzi (Microsoft Query)

Ja vēlaties izmantot ārēju avotu, kas nav SQL datu bāze (piemēram, FileMaker Pro), varat izmantot Mac datorā instalētu atvērto datu bāzu savienojamības (Open Database Connectivity — ODBC) draiveri. Informācija par draiveriem ir pieejama šajā tīmekļa lapā. Kad jūsu datu avota draiveris ir instalēts, veiciet tālāk norādītās darbības.

1. Atlasīt datus>no datu bāzes (Microsoft Query).

2. Pievienojiet datu avotu datu bāzei un pēc tam atlasiet Labi.

3. SQL Server akreditācijas datu uzvednē ievadiet autentifikācijas metodi, lietotājvārdu un paroli.

   

4. Kreisajā pusē atlasiet bultiņu blakus serverim, lai skatītu datu bāzes.

5. Atlasiet bultiņu blakus vēlamajai datu bāzei.

6. Atlasiet nepieciešamo tabulu.

7. Lai priekšskatītu datus, atlasiet Palaist.

8. Kad esat gatavs, atlasiet Atgriezt datus.

9. Dialoglodziņā Datu importēšana izvēlieties, kur datiem jāatrodas: esošā lapā, jaunā lapā vai rakurstabulā.

10. Atlasiet Labi.

11. Lai pārliecinātos, vai savienojums darbojas, ievadiet dažus datus un pēc tam atlasiet Atsvaidzināt datus>visu.

Notīrīt pieteikšanās akreditācijas datus

Ja atļaujas nedarbojas, iespējams, vispirms tās nenotīrīt un pēc tam pieteikties.

1. Atlasiet Datu>savienojumi. Tiek parādīts dialoglodziņš Darbgrāmatas savienojumi .

2. Sarakstā atlasiet vēlamo savienojumu un pēc tam atlasiet Notīrīt atļaujas.