Andmeteekonnad Excelis

Siin on põhiasjad, mida saate andmetega töötamise ajal Exceli rongijaamas teha.

• Importima    Andmeid saate importida paljudest välistest andmeallikatest. Need andmeallikad võivad olla teie arvutis, pilves või poolel teel üle kogu maailma. Lisateavet leiate teemast Andmete importimine välistest andmeallikatest.

• Power Query    Andmete kujundamiseks, teisendamiseks ja kombineerimiseks päringute loomiseks saate kasutada Power Query (varem nimega Hangi & Transformatsioon). Saate eksportida oma töö Power Query mallina, et määratleda Power Appsis andmevoo toiming. Saate luua isegi andmetüübi lingitud andmetüüpide täiendamiseks. Lisateavet leiate Power Query Exceli spikrist.

• Turvalisus    Andmete privaatsus, identimisteave ja autentimine on alati pidevaks probleemiks. Lisateavet leiate teemadest Andmeallika sätete ja õiguste haldamine jaPrivaatsustasemete määramine.

• Värskenda    Imporditud andmed nõuavad tavaliselt excelisse muudatuste (nt täienduste, värskenduste ja kustutamiste) toomiseks värskendustoimingut. Lisateavet leiate teemast Välise andmeühenduse värskendamine Excelis.

• Connections/atribuudid    Igal välisel andmeallikal on seostatud mitmesugused ühenduse- ja atribuudiandmed, mis mõnikord nõuavad olenevalt teie asjaoludest muudatusi. Lisateavet leiate teemadest Välisandmevahemike ja nende atribuutide haldamine, Välisandmetega ühenduste loomine, redigeerimine ja haldamine ning Ühenduse atribuudid.

• Pärand    Traditsioonilised meetodid (nt pärandimpordiviisardid ja MSQuery) on endiselt kasutamiseks saadaval. Lisateavet leiate teemadest Andmete importimise ja analüüsi suvandid jaMicrosoft Query kasutamine välisandmete toomiseks.

Järgmistest jaotistest leiate täpsemat teavet selle kohta, mis toimub selle hõivatud aja (Exceli rongijaama) taustal.

Olemas on ühenduse, päringu ja välisandmevahemiku atribuudid. Nii ühenduse kui ka päringu atribuudid sisaldavad traditsioonilist ühenduseteavet. Dialoogiboksi pealkirjas tähendab ühenduse atribuudid, et sellega pole päringut seostatud, kuid päringu atribuudid tähendab, et see on olemas. Välisandmevahemiku atribuudid juhivad andmete paigutust ja vormingut. Kõigil andmeallikatel on dialoogiboks Välisandmete atribuudid , kuid identimisteabega ja värskendamisteabega andmeallikad kasutavad suuremat dialoogiboksi Välisvahemiku andmeatribuudid .

Järgmises teabes on kokkuvõte olulisematest dialoogiboksidest, paanidest, käsuteedest ja vastavatest spikriteemadest.

Dialoogiboks või paan Käsuteed	Vahekaardid ja tunnelid	Põhispikker
Viimatised allikad Andmete > viimatised allikad	(Vahekaarte pole) Tunnels to Connect > Navigator dialog box	Andmeallika sätete ja õiguste haldamine
Ühenduse atribuudid OR-andmeühendusviisard Data > Queries & Connections > Connections tab > (paremklõpsa ühendus) > Properties	Usage tab Definition tab Used In tab	Ühenduse atribuudid
Päringuatribuudid Data > Existing Connections > (paremklõpsa ühendus) > Edit Connection Properties OR Data > Queries & Connections | Päringute vahekaart > (paremklõpsake ühendust) > Atribuudid OR Query > atribuudid OR Data > Refresh All > Connections (when positioned on a loaded query worksheet)	Usage tab Definition tab Used In tab	Ühenduse atribuudid
Päringud & Connections Data > Queries & Connections	Vahekaart Päringud Connections  tab	Ühenduse atribuudid
Olemasolevad Connections Olemasoleva Connections > andmed	menüü Tabelid Connections	Välisandmetega ühenduse loomine
Välisandmete atribuudid OR Välisandmevahemiku atribuudid OR Data > atribuudid (keelatud, kui seda ei paigutata päringutöölehele)	Kasutatakse vahekaardil (dialoogiboksist Ühenduse atribuudid )   Refresh button on the right tunnels to Query Properties	Väliste andmevahemike ja nende atribuutide haldamine
Vahekaart Ühenduse atribuudid > määratlus > ekspordi ühendusfail OR Query > ekspordi ühendusfail	(Vahekaarte pole) Tunnels to File dialog box Data sources folder	Välisandmetega ühenduste loomine, redigeerimine ja haldamine

Exceli töövihiku andmed võivad pärineda kahest erinevast asukohast. Andmed võivad olla talletatud otse töövihikus või välises andmeallikas (nt tekstifailis, andmebaasis või OLAP-kuubis). See väline andmeallikas on töövihikuga ühendatud andmeühenduse kaudu, mis on teabekogum, mis kirjeldab, kuidas välist andmeallikat otsida, sisse logida ja sellele juurde pääseda.

Välisandmetega ühenduse loomise peamine eelis on see, et saate neid andmeid regulaarselt analüüsida, ilma andmeid töövihikusse korduvalt kopeerimata. See on toiming, mis võib olla aeganõudev ja tõrkeohtlik. Pärast välisandmetega ühenduse loomist saate exceli töövihikuid automaatselt värskendada (või värskendada) ka algsest andmeallikast iga kord, kui andmeallikat uue teabega värskendatakse.

Ühendusteave talletatakse töövihikus ja seda saab talletada ka ühendusfailis (nt Office'i andmeühendusfailis (.odc) või andmeallika nimefailis (.dsn).

Välisandmete Excelisse toomiseks on vaja juurdepääsu andmetele. Kui välist andmeallikat, millele soovite juurde pääseda, pole teie kohalikus arvutis, peate võib-olla pöörduma andmebaasi administraatori poole, et saada parool, kasutajaõigused või muu ühendusteave. Kui andmeallikas on andmebaas, veenduge, et andmebaas poleks eksklusiivrežiimis avatud. Kui andmeallikaks on tekstifail või arvutustabel, siis veenduge, et mõnel teisel kasutajal poleks see eksklusiivseks juurdepääsuks avatud.

Paljud andmeallikad nõuavad ka ODBC-draiverit või OLE DB pakkujat, et koordineerida andmevoogu Exceli, ühendusfaili ja andmeallika vahel.

Järgmisel skeemil on ülevaade andmeühenduste põhipunktidest.

1. Ühenduse loomiseks on mitu andmeallikat: analüüsiteenused, SQL Server, Microsoft Access, muud OLAP-i ja relatsioonandmebaasid, arvutustabelid ja tekstifailid.

2. Paljudel andmeallikatel on seostatud ODBC-draiver või OLE DB pakkuja.

3. Ühendusfail määratleb kogu teabe, mida on vaja andmeallikale juurdepääsemiseks ja andmeallikast andmete toomiseks.

4. Ühendusteave kopeeritakse ühendusfailist töövihikusse ja ühenduseteavet saab hõlpsasti redigeerida.

5. Andmed kopeeritakse töövihikusse, et saaksite neid kasutada samamoodi nagu otse töövihikusse salvestatud andmeid.

Ühendusfailide otsimiseks kasutage dialoogiboksi Olemasolevad Connections. (Valige Andmed > Olemasolev Connections.) Selles dialoogiboksis kuvatakse järgmist tüüpi ühendused.

• Connections töövihikus    

  Selles loendis kuvatakse kõik töövihiku praegused ühendused. Loend luuakse juba määratletud ühendustest, mille lõite andmeühendusviisardi dialoogiboksi Andmeallika valimine abil või ühendustest, mille olete varem selle dialoogiboksi kaudu ühenduseks valinud.

• Ühendusfailid teie arvutis    

  See loend luuakse kaustast Minu andmeallikad , mida tavaliselt talletatakse kaustas Dokumendid.

• Võrguühendusfailid    

  Selle loendi saab luua kohaliku võrgu kaustakomplektist, mille asukoha saab Microsoft Office'i rühmapoliitikate juurutamisel või SharePointi teegi juurutamisel kogu võrgus juurutada. 

Samuti saate Excelit kasutada ühendusfailiredaktorina töövihikus või ühendusfailis talletatud väliste andmeallikatega ühenduste loomiseks ja redigeerimiseks. Kui te ei leia soovitud ühendust, saate luua ühenduse, klõpsates dialoogiboksi Andmeallika valimine kuvamiseks nuppu Sirvi veel ja seejärel andmeühendusviisardi käivitamiseks nuppu Uus allikas.

Pärast ühenduse loomist saate kasutada dialoogiboksi Ühenduse atribuudid (Andmete > päringute valimine & Connections >Connections vahekaarti > (paremklõpsake ühendust) > Atribuudid), et reguleerida erinevaid välisandmeallikatega ühenduse loomise sätteid ja kasutada, uuesti kasutada või vahetada ühendusfaile.

Märkus    Mõnikord nimetatakse dialoogiboksi Ühenduse atribuudid dialoogiboksiks Päringu atribuudid, kui sellega on seostatud päring, mis loodi Power Query (varasema nimetusega Hangi & Transformatsioon).

Kui kasutate andmeallikaga ühenduse loomiseks ühendusfaili, kopeerib Excel ühendusteabe ühendusfailist Exceli töövihikusse. Kui teete muudatusi dialoogiboksis Ühenduse atribuudid, redigeerite praeguses Exceli töövihikus talletatud andmeühendusteavet, mitte algset andmeühendusfaili, mida võisite ühenduse loomiseks kasutada (näitab failinime, mis kuvatakse vahekaardi Määratlus atribuudis Ühendusfail). Pärast ühenduseteabe redigeerimist (v.a a atribuudid Ühenduse nimi ja Ühenduse kirjeldus ), eemaldatakse ühendusfaili link ja atribuut Ühendusfail tühjendatakse.

Veendumaks, et ühendusfaili kasutatakse alati andmeallika värskendamisel, klõpsake vahekaardil Määratlus nuppu Proovi alati kasutada seda faili nende andmete värskendamiseks. Selle ruudu märkimine tagab, et ühendusfaili värskendusi kasutavad alati kõik seda ühendusfaili kasutavad töövihikud, millel peab olema ka see atribuudikomplekt.

Dialoogiboksis Connections saate ühendusi hõlpsasti hallata, sh ühendusi luua, redigeerida ja kustutada (Andmete > päringud & Connections >Connections vahekaart > (paremklõpsake ühendust) > Atribuudid).) Selles dialoogiboksis saate teha järgmist: 

• luua, redigeerida, värskendada ja kustutada töövihikuühendusi;

• Kontrollige välisandmete allikat. Seda võite soovida teha juhul, kui ühenduse on määratlenud mõni teine kasutaja.

• vaadata, kus kasutatakse ühendusi praeguses töövihikus;

• diagnoosida välisteandmete ühendamisega seotud tõrketeadeteid;

• suunata ühendus ümber teisele serverile või andmeallikale või asendada olemasoleva ühenduse ühendusfaili;

• hõlpsalt luua ja jagada ühendusfaile teiste kasutajatega.

Ühendusefailid on eriti kasulikud ühenduste ühtseks jagamiseks, ühenduste hõlpsamaks leitavaks muutmiseks, ühenduste turvalisuse parandamiseks ja andmeallikate haldamise hõlbustamiseks. Parim viis ühendusfailide ühiskasutusse andmiseks on paigutada need turvalisse ja usaldusväärsesse kohta (nt võrgukausta või SharePointi teeki), kus kasutajad saavad faili lugeda, kuid faili saavad muuta ainult määratud kasutajad. Lisateavet leiate teemast Andmete jagamine ODC-ga.

ODC-failide kasutamine

Office'i andmeühenduse (ODC) failide (.odc) loomiseks saate luua ühenduse välisandmetega dialoogiboksi Andmeallika valimine kaudu või kasutades uute andmeallikatega ühenduse loomiseks andmeühendusviisardit. ODC-fail kasutab ühendusteabe talletamiseks kohandatud HTML- ja XML-silte. Excelis saate hõlpsalt faili sisu vaadata ja redigeerida.

Saate anda ühendusfaile teistele inimestele ühiskasutusse, et anda neile sama juurdepääs, mis teil on välisele andmeallikale. Teised kasutajad ei pea ühendusfaili avamiseks andmeallikat häälestama, kuid võib juhtuda, et nad peavad oma arvuti välisandmetele juurdepääsuks installima ODBC-draiveri või OLE DB pakkuja.

ODC-failid on soovitatav meetod andmetega ühenduse loomisel ja andmete jagamisel. Muude traditsiooniliste ühendusfailide (DSN, UDL ja päringufailid) hõlpsalt ODC-failiks teisendamiseks avage ühendusfail ja klõpsake siis dialoogiboksi Ühenduse atribuudid vahekaardil Määratlus nuppu Ekspordi ühendusfail.

Päringufailide kasutamine

Päringufailid on tekstifailid, mis sisaldavad andmeallika teavet, sh selle serveri nime, kus andmed asuvad, ja ühenduseteavet, mille esitate andmeallika loomisel. Päringufailid on traditsiooniline viis päringute jagamiseks teiste Exceli kasutajatega.

DQY-päringufailide kasutamine    Microsoft Query abil saate salvestada DQY-faile, mis sisaldavad relatsioonandmebaaside või tekstifailide andmete päringuid. Kui avate need failid Microsoft Querys, saate vaadata päringu tagastatud andmeid ja muuta päringut erinevate tulemite toomiseks. DQY-faili saate salvestada mis tahes loodava päringu jaoks kas päringuviisardi abil või otse Microsoft Querys.

OQY-päringufailide kasutamine    OLAP-andmebaasi andmetega ühenduse loomiseks saate OQY-faile salvestada kas serveris või ühenduseta kuubifail (.cub). Kui loote Microsoft Query mitmemõõtmelise ühenduse viisardi abil OLAP-andmebaasi või kuubi andmeallika, luuakse OQY-fail automaatselt. Kuna OLAP-andmebaasid pole kirjetes ega tabelites korraldatud, ei saa neile andmebaasidele juurdepääsuks päringuid ega DQY-faile luua.

RQY-päringufailide kasutamine    Excel saab avada päringufaile RQY-vormingus, et toetada seda vormingut kasutavaid OLE DB andmeallikadraivereid. Lisateavet leiate draiveri dokumentatsioonist.

QRY-päringufailide kasutamine    Microsoft Query saab avada ja salvestada päringufaile QRY-vormingus kasutamiseks Microsoft Query varasemates versioonides, mis ei saa DQY-faile avada. Kui teil on QRY-vormingus päringufail, mida soovite Excelis kasutada, avage fail microsoft Querys ja salvestage see DQY-failina. DQY-failide salvestamise kohta leiate teavet Microsoft Query spikrist.

IQY-veebipäringufailide kasutamine    Excel saab avada iqy veebipäringufaile andmete toomiseks veebist. Lisateavet leiate teemast Excelisse eksportimine SharePointist.

Välisandmevahemik (päringutabel) on määratletud nimi või tabelinimi, mis määratleb töölehele toodud andmete asukoha. Välisandmetega ühenduse loomisel loob Excel automaatselt välisandmevahemiku. Ainus erand on andmeallikaga ühendatud PivotTable-aruanne, mis ei loo välisandmevahemikku. Excelis saate välisandmevahemikku vormindada ja küljendada või kasutada seda arvutustes nagu muude andmete puhul.

Excel nimetab välisandmevahemiku automaatselt järgmiselt.

• Office'i andmeühenduse (ODC) failide välistele andmevahemikele antakse failinimega sama nimi.

• Andmebaasidest pärinevate välisandmevahemike nimi on päringu nimi. Vaikimisi on Query_from_source selle andmeallika nimi, mida kasutasite päringu loomiseks.

• Tekstifailide välisandmevahemike nimi on tekstifaili nimi.

• Veebipäringute välisandmevahemike nimi on veebilehe nimi, kust andmed alla laaditi.

Kui teie töölehel on samast allikast mitu välisandmevahemikku, nummerdatakse vahemikud. Näiteks MyText, MyText_1, MyText_2 jne.

Välisandmevahemikul on täiendavad atribuudid (mitte segamini ühenduseatribuutidega), mille abil saate andmeid juhtida (nt lahtrivormingu ja veeru laiuse säilitamine). Välisandmevahemiku atribuutide muutmiseks klõpsake menüü Andmed jaotises Connections nuppu Atribuudid ja seejärel tehke muudatused dialoogiboksides Välisandmevahemiku atribuudid või Välisandmete atribuudid.

Erinevate andmeallikatega ühenduse loomiseks saate kasutada mitut andmeobjekti (nt välisandmevahemik ja PivotTable-liigendtabeli aruanne). Kuid andmeallika tüüp, millega saate ühenduse luua, on iga andmeobjekti puhul erinev.

Ühendatud andmeid saate kasutada ja värskendada Exceli teenused. Nagu iga välise andmeallika puhul, peate võib-olla oma juurdepääsu autentima. Lisateavet leiate teemast Välise andmeühenduse värskendamine Excelis. Fvõi lisateavet identimisteabe kohta leiate teemast Exceli teenused autentimissätted.

Järgmises tabelis on kokkuvõte sellest, milliseid andmeallikaid iga Exceli andmeobjekti puhul toetatakse.

Excel andmed ese	Loob Välise andmed Vahemik?		OLE DB	ODBC	Tekst fail	HTML-fail fail	XML fail	SharePoint loend
Tekstiimpordiviisard	Jah		Ei	Ei	Jah	Ei	Ei	Ei
PivotTable-liigendtabeli aruanne (mitte-OLAP)	Ei		Jah	Jah	Jah	Ei	Ei	Jah
PivotTable-liigendtabeli aruanne (OLAP)	Ei		Jah	Ei	Ei	Ei	Ei	Ei
Excel Table	Jah		Jah	Jah	Ei	Ei	Jah	Jah
XML-vastendus	Jah		Ei	Ei	Ei	Ei	Jah	Ei
Veebipäring	Jah		Ei	Ei	Ei	Jah	Jah	Ei
Andmeühendusviisard	Jah		Jah	Jah	Jah	Jah	Jah	Jah
Microsoft Query	Jah		Ei	Jah	Jah	Ei	Ei	Ei

Exceli teenused lahendus Exceli tabelite ja nimega vahemike jaoks

Kui soovite Exceli töövihiku kuvada Exceli teenused, saate andmetega ühenduse luua ja andmeid värskendada, kuid peate kasutama PivotTable-liigendtabeli aruannet. Exceli teenused ei toeta välisandmevahemikke, mis tähendab, et Exceli teenused ei toeta andmeallikaga, veebipäringuga, XML-vastendusega ega Microsoft Queryga ühendatud Exceli tabelit.

Siiski saate selle piirangu lahendamiseks kasutada andmeallikaga ühenduse loomiseks PivotTable-liigendtabelit ning seejärel kujundada ja küljendada PivotTable-liigendtabeli kahemõõtmelise tabelina ilma tasemete, rühmade või vahekokkuvõteteta, et kuvataks kõik soovitud rea- ja veeruväärtused. 

Vaatame andmebaasi mäluraja.

MDAC, OLE DB ja OBC

Esiteks vabandan kõigi akronüümide pärast. Microsoft Data Access Components (MDAC) 2.8 sisaldub Microsoft Windowsis. MDAC-ga saate luua ühenduse mitmesuguste relatsioon- ja mitterelatsiooniliste andmeallikatega ning neid kasutada. Saate luua ühenduse paljude erinevate andmeallikatega, kasutades ODBC(Open Database Connectivity) draivereid või OLE DB pakkujaid, mille on koostanud ja tarninud Microsoft või mille on arendanud mitmed kolmandad osapooled. Microsoft Office'i installimisel lisatakse teie arvutisse täiendavad ODBC-draiverid ja OLE DB pakkujad.

Arvutisse installitud OLE DB pakkujate täieliku loendi kuvamiseks kuvage andmelingi faili dialoogiboks Andmelingi atribuudid ja klõpsake vahekaarti Pakkuja .

Arvutisse installitud ODBC-pakkujate täieliku loendi kuvamiseks kuvage dialoogiboks ODBC-andmebaasi administraator ja klõpsake vahekaarti Draiverid .

Samuti saate kasutada teiste tootjate ODBC-draivereid ja OLE DB pakkujaid, et hankida teavet muudest allikatest kui Microsofti andmeallikatest (sh muud tüüpi ODBC- ja OLE DB-andmebaasidest). ODBC-draiverite ja OLE DB pakkujate installimise kohta leiate teavet vastava andmebaasi dokumentatsioonist või pöörduge vastava andmebaasi pakkuja poole.

ODBC kasutamine andmeallikatega ühenduse loomiseks

ODBC-arhitektuuris loob rakendus (nt Excel) ühenduse ODBC-draiverihalduriga, mis omakorda kasutab andmeallikaga (nt Microsofti SQL Server andmebaasiga) ühenduse loomiseks kindlat ODBC-draiverit (nt Microsoft SQL-i ODBC-draiver).

ODBC-andmeallikatega ühenduse loomiseks tehke järgmist.

1. Veenduge, et andmeallikat sisaldavasse arvutisse oleks installitud sobiv ODBC-draiver.

2. Määratlege andmeallika nimi (DSN), kasutades ühenduseteabe talletamiseks registris või DSN-failis ODBC andmeallika administraatorit või Microsoft Visual Basicu koodis ühendusestringi, et edastada ühendusteave otse ODBC-draiverihaldurile.

   Andmeallika määratlemiseks klõpsake Windowsis nuppu Start ja seejärel nuppu Juhtpaneel. Klõpsake valikut Süsteem ja hooldus ning seejärel valikut Haldusriistad. Klõpsake nuppu Jõudlus ja hooldus ja seejärel valikut Haldusriistad. ja seejärel klõpsake nuppu Andmeallikad (ODBC). Erinevate suvandite kohta lisateabe saamiseks klõpsake igas dialoogiboksis nuppu Spikker .

Seadme andmeallikad

Seadme andmeallikad salvestavad kasutaja määratletud nimega ühenduseteavet kindlas arvutis asuvasse registrisse. Seadme andmeallikaid saate kasutada ainult arvutis, kuhu need on määratud. On kaht tüüpi seadme andmeallikaid – kasutaja ja süsteemi andmeallikad. Kasutaja andmeallikaid saab kasutada ainult praegune kasutaja ja need on nähtavad ainult sellele kasutajale. Süsteemi andmeallikaid saavad kasutada kõik arvuti kasutajad ja need on nähtavad kõigile arvuti kasutajatele.

Seadme andmeallikast on eriti kasu siis, kui soovite lisada turvet, kuna see aitab tagada, et seadme andmeallikat saavad vaadata ainult sisselogitud kasutajad ja kaugkasutaja ei saa seadme andmeallikat teise arvutisse kopeerida.

Faili andmeallikad

Faili andmeallikad (ehk DSN-failid) salvestavad ühenduseteavet tekstifaili, mitte registrisse, ja neid on üldiselt paindlikum kasutada kui seadme andmeallikaid. Näiteks saate kopeerida faili andmeallika mis tahes arvutisse, kus on õige ODBC-draiver, et teie rakendus saaks kõigis arvutites toetuda ühtsele ja täpsele ühenduseteabele. Või saate paigutada faili andmeallika ühte serverisse, jagada seda võrgu mitme arvuti vahel, ja hallata hõlpsalt ühendusteavet ühes kohas.

Faili andmeallikat on võimalik muuta ka jagamatuks. Jagamatu faili andmeallikas asub ühes arvutis ja osutab seadme andmeallikale. Jagamatute faili andmeallikate abil saate faili andmeallikatest juurdepääsu olemasolevatele seadme andmeallikatele.

OLE DB kasutamine andmeallikatega ühenduse loomiseks

OLE DB arhitektuuris nimetatakse andmetele juurdepääsu võimaldavat rakendust andmetarbijaks (nt Excel) ja andmebaasipakkujaks (nt Microsoft OLE DB pakkuja SQL Server jaoks).

Universaalne andmelingifail (.udl) sisaldab ühenduseteavet, mida andmetarbija kasutab andmeallikale juurdepääsuks selle andmeallika OLE DB pakkuja kaudu. Ühenduseteabe loomiseks tehke ühte järgmistest.

• Kasutage andmeühendusviisardis OLE DB pakkuja andmelingi määratlemiseks dialoogiboksi Andmelingi atribuudid . 

• Looge tühi tekstifail UDL-failinimelaiendiga ja seejärel redigeerige faili, mis kuvab dialoogiboksi Andmelingi atribuudid .