Märkus.: See artikkel on oma töö ära teinud ja aegub peagi. Kuna soovime vältida tõrketeateid selle kohta, et ühte või teist lehte ei leitud, eemaldame lingid, millest oleme teadlikud. Kui olete ise loonud sellele lehele viivaid linke, eemaldage need. Üheskoos aitame tagada veebi korraspüsimise.
Excel 2016 sisaldab nüüd uut võimsat tööriistakomplekti Toomine ja teisendamine. Power Query tehnoloogial põhinevad tööriistad võimaldavad ühendada, kombineerida ja kujundada mitmesugustest allikatest pärit andmeid. Funktsiooni põhiteabe leiate Funktsiooni Toomine ja teisendamine õpetustest.
Meil on hea meel teatada exceli andmete importimise ja kujundamise funktsioonide järgmisest olulisest värskendusest – ühtsest funktsioonist Hangi & transformatsioon. 2017. aasta märtsi Microsoft 365 värskendusega täiustasime Exceli andmete importimise funktsioone ja korraldasime ümber menüü Andmed nupud, et oleks lihtsam kui kunagi varem kasutada kõiki võimalusi & transformatsiooni hankimine.
Ühtne funktsioon Toomine ja teisendamine võimaldab kasutada integreeritud ja tänapäevaseid Exceli andmete importimise ja kujundamise funktsioone. Saate hõlpsalt ühenduse luua mitmesuguste populaarsete andmeallikatega nagu failid, andmebaasid, Azure’i ja veebiteenused jne ning neist andmeid tuua. Ja siis saate neid andmeid tõhusalt kujundada (nt sortida, muuta andmetüüpe, tükeldada veerge, koondada andmeid jne). Uued konnektorid ja hõlpsalt teostatavad teisendusvõimalused, mida lisatakse pidevalt juurde, teevad toomise ja teisendamise funktsioonist hindamatu ja ajasäästliku tööriista, mida oma igapäevatöös kasutada.
Võtke arvesse, et uus lahendus asendab vanad andmete importimise pärandviisardid, mis olid saadaval jaotises Andmed > Välisandmete toomine. Soovi korral pääsete neile siiski juurde. Lugege artiklit Pärandfunktsiooni Välisandmete toomine taastamine
Märkus.: See funktsioon on Excelis saadaval ainult siis, kui teil on Microsoft 365 tellimus. Kui oleteMicrosoft 365 tellija, veenduge, et teil oleks Office'i uusim versioon.
Andmete toomine populaarsetest andmeallikatest (nt CSV-failid või veebisaidid)
Teame, et andmeid importivad kasutajad soovivad, et kõige populaarsemad konnektorid oleks neil lindil hõlpsalt kättesaadavad. Selles värskenduses proovisime seda põhimõtet järgida ning säilitasime lahenduse, millega olete juba harjunud. Nüüd leiate menüü Andmed toomise ja teisendamise jaotisest järgmised kolm populaarsemat konnektorit: Teksti-/CSV-failist, Veebist ja Tabelist/vahemikust.
Teksti-/CSV-failist
Selle konnektori abil saate hõlpsalt importida andmeid tekstifailidest (*TXT), komaeraldusega väärtuste failidest (*CSV) või vormindatud tekstifailidest (*PRN). See tänapäevane konnektor põhineb Power Query tehnoloogial ja vahetab välja pärandviisardi Tekstist.
Kui soovite alustada andmete importimist uue konnektori abil, valige Andmed > Teksti-/CSV-failist, valige lähtefail ja siis klõpsake nuppu OK. Uus ja täiustatud konnektor Teksti-/CSV-failist analüüsib seejärel teie sisendfaili struktuuri ning rakendab automaatselt asjakohased impordisätted. See võimaldab tükeldada veerge eraldaja abil, ülendada esimese rea, et kasutada seda veerupäisena, ja teha palju muud.
Kui eelvaade pole päris selline, nagu sooviksite, saate siin konfigureerida olulisemad impordisätted (nt faili päritolu, eraldaja ja andmetüübi tuvastamise loogika). Vastavalt teie valikutele värskendatakse automaatselt andmete eelvaadet.
Seejärel saate klõpsata nuppu Laadi, et laadida andmed otse töölehele, või klõpsata nupu Laadi kõrval olevat noolt, et laadida andmed andmemudelisse. Kui soovite päringuredaktorisse lisada täiendavaid andmeteisendusi, klõpsake nuppu Redigeeri.
Veebist
Selle konnektori abil saate andmeid koguda HTML-lehtedelt. See on teine uudne konnektor, mis põhineb samuti Power Query tehnoloogial ja asendab pärandviisardi Veebist.
Alustamiseks valige Andmed > Veebist, määrake sihtlehe URL ja siis klõpsake nuppu OK. Dialoogiboksis Navigaator kuvatakse kõigi lehel olevate tabelite loend. Veebilehega saate suhelda kas tabelivaates või veebivaates. Tabelivaates klõpsake esmalt vasakul tabeli nime ja paremal kuvatakse sellest tabelist pärit andmed. Veebivaate korral saate tabelit klõpsata loendi Navigaator või veebivaate eelvaatepaani kaudu. Mõlemas vaates saate valida mitu tabelit
Tabelist/vahemikust
Selle konnektori abil saate luua uue päringu, mis lingitakse Exceli töölehel tabeli või nimega vahemikuga. Seejärel saate andmeid piiritleda ning rakendada neile aknas Päringuredaktor täiendavaid teisendusi.
Andmete toomine muudest allikatest (nt XML-fail või Microsoft SQL Server
Saate luua uue päringu, mis võimaldab importida andmeid ühest andmeallikast (nt Microsoft Accessi andmebaasist või OData-kanalist). Samuti saate andmeid importida korraga mitmest kaustas olevast tekstifailist.
Kui klõpsate nuppu Andmed > Too andmed, kuvatakse saadaolevad andmeallikad rippmenüüs. Lähtesuvandid on korraldatud kategooriatesse ,sh exceli töövihikud, XML-failid või kogu kaust, andmebaasid (nt SQL Server või Oracle), Azure'i teenused (nt HDInsight või Blob Storage), veebiteenused (nt SharePoint Online'i loend või Salesforce) ja mitmesugused muud allikad (nt OData või ODBC).
Saadaolevate konnektorite loend suureneb pidevalt.
Selle värskendusega eemaldasime ka järgmised pärandviisardid, mis olid varem saadaval menüü Andmed jaotises Välisandmete toomine: Accessist, Veebist, Tekstist, SQL Serverist, OData andmekanalist, XML-andmete impordist ja Andmeühendusviisardist. Kui peate mingil põhjusel kasutama mõnda eemaldatud pärandviisardit, lugege teemat Pärandfunktsiooni Välisandmete toomine taastamine.
Vanas välisandmete toomise funktsioonikogumis on ka kaks populaarset ja üsna erilist viisardit, mis jäävad alles ka uude lahendusse: Teenusest Analysis Services ja Rakendusest Microsoft Query. Need leiate vastavalt uue lahenduse jaotistest Andmed > Andmebaasist ja Andmed > Muudest allikatest.
Funktsiooni Toomine ja teisendamine võimaluste kasutamine andmete puhastamiseks ja kujundamiseks
Funktsiooni Toomine ja teisendamine abil saate puhastada ja kujundada oma andmeid, mis on suurte andmemahtude analüüsimisel väga oluline. Saate eemaldada veerge, filtreerida ridu, muuta veeru andmetüüpe, asendada väärtusi ja teha muud. Selleks on spetsiaalne aken Päringuredaktor, mis hõlbustab andmeteisenduste tegemist ja kuvab need.
Aktiveerige lindil menüü Andmed, klõpsake nuppu Too andmed, looge saadaolevate konnektorite abil ühendus soovitud andmeallikaga (nt fail või andmebaas) ja vaadake andmete eelvaadet aknas Navigaator.
Järgmiseks valige tabelid, mida soovite importida, ja klõpsake akna Päringuredaktor avamiseks käsku Redigeeri.
Aknas Päringuredaktor on saadaval hulgaliselt käske, mida saate kasutada. Andmete sortimise ja filtreerimise, andmetüüpide teisendamise, veergude tükeldamise ja ühendamise, andmete koondamise jt funktsioonide abil saate rakendada võimsaid teisendusi.
Kõik andmetele rakendatavad teisendusetapid registreeritakse ja salvestatakse teie päringu osana. Registreeritud teisendusi saab jälgida ja hallata akna Päringuredaktor jaotises RAKENDATUD ETAPID ja need rakendatakse andmetele uuesti iga kord, kui päringut värskendatakse.
Andmete toomine viimati kasutatud allikatest, Office’i andmebaasiühenduse (ODC) failidest, muudest töövihikuühendustest ja tabelitest
Andmete importimise ala üks kõige populaarsemaid stsenaariume on andmete ühendamine ja toomine viimatistest allikatest. Kasutajad loovad sageli enda jaoks kogumi kõige sagedamini kasutatavatest allikatest, mida nad andmete importimiseks kasutavad.
Päringu ühendamiseks ja loomiseks mõnest hiljuti kasutatud andmeallikast valige Andmed > Viimatised allikad. Seejärel saate viimati ühendatud allikaid sirvida, valida soovitud allika ning klõpsata imporditoimingu jätkamiseks käsku Loo ühendus.
Teine levinud stsenaarium on andmete importimine ühendustest või tabelitest, mis on töövihikus juba olemas. Võib-olla soovite luua varem loodud töövihikuühenduse põhjal uue PivotTable-liigendtabeli. Või ehk soovite linkida ja importida andmeid andmeallikatest Office’i andmebaasiühenduse (ODC) faili abil, mille määratles keegi teie ettevõttes.
Tänu sellele värskendusele saate eksportida oma päringute mistahes määratlused ODC-faili ning seejärel anda need ühiskasutusse kõigis töövihikutes või kõigi kolleegidega. Päringumääratluse eksportimiseks ODC-faili paremklõpsake päringut külgpaanil Päringud ja ühendused ning valige Ekspordi ühendusfail. Loodud ODC-fail sisaldab päringu määratlust kõigi sõltuvustega, sh andmeallikaga ühenduse loomise juhiseid, ning andmetele rakendatavaid teisendustoiminguid. Teine võimalus päringumääratluse eksportimiseks on dialoogiboksi Päringu atribuudid kaudu, mille saab avada, kui klõpsata päringu kontekstimenüüs käsku Atribuudid.
Kui saate ODC-faili, saate seda kasutada ning päringumääratluse oma töövihikusse importida. Andmete importimiseks ODC-meetodi kaudu valige Andmed > Olemasolevad ühendused. Kuvatakse dialoogiboks Olemasolevad ühendused ja seejärel saate andmeid importida saadaolevatest ODC-failidest, olemasolevatest töövihikuühendustest või tabelitest.
Kui peate valima ODC-faili, mida kasutab ka mõni muu rakendus (nt Visio või Excel 2013 või mõni varasem versioon), saate seda teha mõne järgmise konnektori abil: SQL Serveri andmebaasist, Microsoft Accessi andmebaasist, OData kanalist, Teenusest Analysis Services, Rakendusest Microsoft Query või pärandkonnektor Andmeühendusviisardist. Oluline on teada, et kui kasutate mõnest muust rakendusest pärit ODC-faile, kasutab see rakendus uuesti ainult ODC-st pärit ühendusjuhiseid, ega sisalda teisendusetappe, mis võisid sisalduda teie algses päringus. Kui peate taastama pärandkonnektorid Accessist, SQL Serverist, OData andmekanalist, lugege teemat Pärandfunktsiooni Välisandmete toomine taastamine
Olemasolevate töövihikupäringute ja -ühenduste haldamine
Kui võtsime kasutusele funktsiooni Get & Transformatsioon Excel 2016, võtsime kasutusele kaks andmeimpordimeetodit: esimene oli uus funktsioon Get & Transformatsioon, mis kasutab päringuid (ja tugineb taustal olevatele töövihikuühendustele värskendamiseks ja töövihiku elementide laadimiseks). Teine oli vanem pärandfunktsioon, mis kasutab töövihiku ühendusi. Töövihiku päringute ja ühenduste haldamine toimus ka eraldi. Töövihikupäringuid hallatakse sihtotstarbelise paani Päringud kaudu, samal ajal kui ühendusi hallatakse dialoogiboksi Töövihiku ühendused kaudu. See põhjustas segadust meie kasutajate seas.
Praeguse värskendusega lahendasime selle probleemi. Nüüd pääsete nii töövihiku päringutele kui ka ühendustele juurde ja saate neid hallata spetsiaalse külgpaani Päringud ja ühendused kaudu. Külgpaani avamiseks valige Andmed > Päringud ja ühenduse.
Kui klõpsate paani ülaosas vahekaarti Päringud, saate sirvida ja hallata töövihikus saadaolevaid päringuid. Kui soovite avada päringu kontekstimenüü, mis võimaldab teha lisatoiminguid, paremklõpsake päringut. Näiteks saate värskendada aluseks olevast allikast pärit päringuandmeid, kopeerida ja kleepida päringu, redigeerida päringuteisendusi päringuredaktori abil ja teha muud.
Lisaks oleme lihtsustanud päringu ja selle aluseks oleva ühenduse haldamist uue dialoogiboksi Päringu atribuudid kaudu. Kui loote nüüd töövihikus uue päringu, luuakse taustal automaatselt uus aluseks olev ühendus, mis seostatakse selle päringuga. Kui päringu eesmärk on luua ühendus andmeallikaga ja rakendada sellele nõutavad andmeteisendused, juhib aluseks olev ühendus kohta, kuhu andmeid laaditakse (nt tööleht või andmemudel), ja päringuandmete värskendussätteid (nt värskendussagedust). Uue dialoogiboksi Päringu atribuudid kaudu saate hõlpsalt ühest kesksest kohast juhtida päringu atribuute (nt päringu nime või teisendusjuhiseid) ning aluseks oleva ühenduse atribuute (nt värskendussätteid). Uue dialoogiboksi Päringu atribuudid avamiseks klõpsake külgpaanil Päringud ja ühendus päringu kontekstimenüüs käsku Atribuudid.
Kui klõpsate külgpani ülaosas vahekaarti Ühendused, saate sirvida ja hallata töövihikus saadaolevaid pärandühendusi, mis on seostatud mõne pärandviisardiga (nt Tekstist, Teenusest Analysis Services või Rakendusest Microsoft Query). Kontekstimenüü avamiseks paremklõpsake ühenduse nime. Seal saate tuttava dialoogiboksi Ühenduse atribuudid kaudu teha lisatoiminguid (nt värskendata allikast pärit ühenduseandmeid, kustutada ühenduse või hallata ühenduse atribuute).
Pärandfunktsiooni Välisandmete toomine taastamine
Uus funktsioon Toomine ja teisendamine pakub võrreldes pärandviisarditega paremaid konnektoreid ja teisendusfunktsioone. Siiski võib juhtuda, et soovite andmete importimiseks mõnikord kasutada ka mõnda vana viisardit. Võib-olla soovite salvestada töövihiku osana andmeallika sisselogimisandmeid. (Võtke arvesse, et see meetod ei vasta tänapäevastele turbestandarditele ja seda ei toetata uues funktsioonis Toomine ja teisendamine).
Pärandkonnektoritele juurdepääsuks saate kohandada linti või tuua ja kuvada andmete importimise pärandviisardid Exceli suvandite dialoogiboksi uute ja hõlpsate konfiguratsioonisätete kaudu otse rippmenüüsse Andmed > Too andmed.
Avage Fail > Suvandid > Andmed > Pärandandmete impordiviisardite kuvamine ja valige kasutatavad pärandviisardid:
Avage rippmenüü Andmed > Too andmed ja käivitage spetsiaalsest pärandviisardite kategooriast üks varem valitud andmete importimise pärandviisard:
Funktsiooni Toomine ja teisendamine lisateave
Kui soovite lisateavet & transformatsiooni hankimise võimaluste kohta, on hea alustada Microsofti Power Query Exceli spikrist.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
