Lai izgūtu datus no ārējiem avotiem, programmu Microsoft Query var izmantot. Izmantojot Microsoft Query datu izgūšanai no uzņēmuma datu bāzēm un failiem, jums nav atkārtoti jāieraksta dati, kurus vēlaties analizēt programmā Excel. Varat arī automātiski atsvaidzināt Excel atskaites un kopsavilkumus no sākotnējās avota datu bāzes, kad datu bāze tiek atjaunināta ar jaunu informāciju.
Papildinformācija par Microsoft Query
Izmantojot Microsoft Query, varat izveidot savienojumu ar ārējiem datu avotiem, atlasīt datus no šiem ārējiem avotiem, importēt datus darblapā un atsvaidzināt datus pēc nepieciešamības, lai darblapas dati tiktu sinhronizēti ar datiem ārējos avotos.
Datu bāzu tipi, kuriem varat piekļūt Datus var izgūt no vairāku tipu datu bāzēm, ieskaitot Microsoft Office Access, Microsoft SQL Server un Microsoft SQL Server OLAP pakalpojumus. Datus var arī izgūt no Excel darbgrāmatām un teksta failiem.
Microsoft Office nodrošina draiverus, kurus var izmantot, lai izgūtu datus no šādiem datu avotiem:
- Microsoft SQL Server analītiskie pakalpojumi (SSAS) (OLAP nodrošinātājs)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- Paradokss
- Teksta failu datu bāzes
Varat izmantot arī ODBC draiverus vai citu ražotāju datu avotu draiverus, lai izgūtu informāciju no datu avotiem, kas šeit nav uzskaitīti, ieskaitot citu veidu OLAP datu bāzes. Informāciju par šeit neesoša ODBC draivera vai datu avota draivera instalēšanu skatiet datu bāzes dokumentācijā vai sazinieties ar datu bāzes piegādātāju.
Datu atlasīšana no datu bāzes Datus no datu bāzes izgūstat, izveidojot vaicājumu, kas ir jautājums, ko uzdodat par ārējā datu bāzē glabātiem datiem. Piemēram, ja dati tiek glabāti Access datu bāzē, iespējams, vēlaties uzzināt konkrēta produkta pārdošanas rādītājus pēc reģiona. Daļu datu varat izgūt, atlasot tikai analizējamā produkta un reģiona datus.
Izmantojot Microsoft Query, varat atlasīt vajadzīgās datu kolonnas un importēt tikai šos datus programmā Excel.
Darblapas atjaunināšana ar vienu darbību Ja Excel darbgrāmatā ir ārējie dati, ikreiz, kad datu bāzē tiek veiktas izmaiņas, varat atsvaidzināt datus, lai atjauninātu analīzi — bez nepieciešamības atkārtoti izveidot kopsavilkuma atskaites un diagrammas. Piemēram, varat izveidot ikmēneša pārdošanas kopsavilkumu un atsvaidzināt to katru mēnesi, kad pienāk jaunie pārdošanas rādītāji.
Kā Microsoft Query izmanto datu avotus Kad konkrētai datu bāzei esat iestatījis datu avotu, to var izmantot, kad vien vēlaties izveidot vaicājumu, lai atlasītu un izgūtu datus no šīs datu bāzes, bez nepieciešamības atkārtoti ierakstīt visu savienojuma informāciju. Microsoft Query izmanto datu avotu, lai izveidotu savienojumu ar ārējo datu bāzi un parādītu, kādi dati ir pieejami. Kad esat izveidojis vaicājumu un atgriezis datus programmā Excel, programma Microsoft Query nodrošina Excel darbgrāmatā gan vaicājuma, gan datu avota informāciju, lai jūs varētu atkārtoti izveidot savienojumu ar datu bāzi, kad vēlaties atsvaidzināt datus.
Izmantojot Microsoft Query datu importēšanai Lai importētu ārējos datus programmā Excel, izmantojot Microsoft Query, izpildiet šīs pamatdarbības, kuras katra ir sīkāk aprakstīta nākamajās sadaļās.
Savienojuma izveide ar datu avotu
Kas ir datu avots? Datu avots ir saglabāta informācijas kopa, kas ļauj programmai Excel un Microsoft Query izveidot savienojumu ar ārēju datu bāzi. Kad izmantojat Microsoft Query datu avota iestatīšanai, jūs piešķirat datu avotam nosaukumu un pēc tam norādāt datu bāzes vai servera nosaukumu un atrašanās vietu, datu bāzes tipu, kā arī jūsu pieteikšanās un paroles informāciju. Informācijā ir iekļauts arī OBDC draivera vai datu avota draivera nosaukums, kas ir programma, kas veido savienojumus ar noteikta veida datu bāzi.
Lai iestatītu datu avotu, izmantojot Microsoft Query:
Cilnes Dati grupā Iegūt ārējos datus noklikšķiniet uz No citiem avotiem un pēc tam uz No Microsoft Query.
Piezīme
Programma Excel 365 ir pārvietojusi Microsoft Query uz mantoto vedņu izvēļņu grupu. Šī izvēlne pēc noklusējuma netiek rādīta. Lai iespējotu, dodieties uz Fails, Opcijas, Dati un iespējojiet sadaļā Mantoto datu importēšanas vedņu rādīšana .
Veiciet vienu no šīm darbībām:
- Lai norādītu datu avotu datu bāzei, teksta failam vai Excel darbgrāmatai, noklikšķiniet uz cilnes Datu bāzes .
- Lai norādītu OLAP kuba datu avotu, noklikšķiniet uz cilnes OLAP kubi . Šī cilne ir pieejama tikai tad, ja programmu Microsoft Query palaidāt no programmas Excel.
Veiciet dubultklikšķi uz <Jauns datu avots>.
- vai -
Noklikšķiniet uz <Jauns datu avots> un pēc tam uz Labi.
Tiek parādīts dialoglodziņš Jauna datu avota izveide .1. darbībā ierakstiet nosaukumu, lai identificētu datu avotu.
2. darbībā noklikšķiniet uz draivera datu bāzes tipam, ko izmantojat kā datu avotu.
Piezīme
- Ja ārējo datu bāzi, kurai vēlaties piekļūt, neatbalsta ODBC draiveri, kas ir instalēti kopā ar Microsoft Query, jums ir jāiegādājas un jāinstalē ar Microsoft Office saderīgs ODBC draiveris no trešās puses piegādātāja, piemēram, datu bāzes ražotāja. Sazinieties ar datu bāzes piegādātāju, lai saņemtu instalēšanas norādījumus.
- OLAP datu bāzēm nav nepieciešami ODBC draiveri. Instalējot Microsoft Query, draiveri tiek instalēti datu bāzēm, kas izveidotas, izmantojot Microsoft SQL Server analītiskie pakalpojumi (SSAS). Lai izveidotu savienojumu ar citām OLAP datu bāzēm, ir jāinstalē datu avota draiveris un klienta programmatūra.
Noklikšķiniet uz Izveidot savienojumu un pēc tam norādiet informāciju, kas nepieciešama, lai izveidotu savienojumu ar datu avotu. Datu bāzēm, Excel darbgrāmatām un teksta failiem norādītā informācija ir atkarīga no atlasītā datu avota veida. Iespējams, jums tiks lūgts ievadīt pieteikšanās vārdu, paroli, izmantotās datu bāzes versiju, datu bāzes atrašanās vietu vai citu datu bāzes tipam specifisku informāciju.
Svarīgi!
- Lietojiet stipras paroles, kurās ir gan lielie, gan mazie burti, skaitļi un simboli. Vājās parolēs šo elementu nav. Stipra parole: Y6dh!et5. Vāja parole: Māja27. Parolei jābūt vismaz 8 rakstzīmes garai. Labāka ir ieejas frāze no 14 vai vairāk rakstzīmēm.
- Ir ļoti svarīgi atcerēties savu paroli. Ja esat aizmirsis paroli, Microsoft to nevar izgūt. Glabājiet pierakstītās paroles drošā vietā atsevišķi no informācijas, kuru tās palīdz aizsargāt.
Kad esat ievadījis nepieciešamo informāciju, noklikšķiniet uz Labi vai Pabeigt , lai atgrieztos dialoglodziņā Jauna datu avota izveide .
Ja datu bāzē ir tabulas un vēlaties, lai noteiktā tabula tiktu automātiski parādīta vaicājuma vednī, noklikšķiniet uz 4. darbības lodziņa un pēc tam uz vajadzīgās tabulas.
Ja nevēlaties ievadīt savu pieteikšanās vārdu un paroli, izmantojot datu avotu, datu avota definīcijā atzīmējiet izvēles rūtiņu Saglabāt manu lietotāja ID un paroli . Saglabātā parole netiek šifrēta. Ja šī izvēles rūtiņa nav pieejama, sazinieties ar datu bāzes administratoru, lai noskaidrotu, vai šo opciju var padarīt pieejamu.
Piezīme
Izvairieties no pieteikšanās informācijas saglabāšanas, veidojot savienojumu ar datu avotiem. Šī informācija var tikt glabāta kā vienkāršs teksts, un ļaunprātīgs lietotājs var piekļūt šai informācijai, tādējādi apdraudot datu avota drošību.
Kad šīs darbības ir paveiktas, dialoglodziņā Datu avota izvēle tiek parādīts datu avota nosaukums.
Vaicājuma definēšana, izmantojot Vaicājumu vedni
Vaicājumu vedņa izmantošana lielākajai daļai vaicājumu Vaicājumu vednis atvieglo datu atlasīšanu un apkopošanu no dažādām tabulām un laukiem datu bāzē. Izmantojot vaicājumu vedni, varat atlasīt tabulas un laukus, kurus vēlaties iekļaut. Iekšējais savienojums (vaicājuma darbība, kas norāda, ka rindas no divām tabulām tiek apvienotas, pamatojoties uz identiskām lauku vērtībām) tiek izveidots automātiski, kad vednis atpazīst primārās atslēgas lauku vienā tabulā un lauku ar tādu pašu nosaukumu otrā tabulā.
Varat arī izmantot vedni, lai kārtotu rezultātu kopu un veiktu vienkāršu filtrēšanu. Vedņa pēdējā darbībā varat izvēlēties atgriezt datus programmā Excel vai precizēt vaicājumu programmā Microsoft Query. Kad vaicājums ir izveidots, varat to izpildīt programmā Excel vai Microsoft Query.
Lai startētu vaicājumu vedni, veiciet tālāk norādītās darbības.
- Cilnes Dati grupā Iegūt ārējos datus noklikšķiniet uz No citiem avotiem un pēc tam uz No Microsoft Query.
- Dialoglodziņā Datu avota izvēle pārliecinieties, vai ir atzīmēta izvēles rūtiņa Vaicājumu izveidei/rediģēšanai izmantot vaicājumu vedni .
- Veiciet dubultklikšķi uz datu avota, kuru vēlaties izmantot.
- vai -
Noklikšķiniet uz datu avota, kuru vēlaties izmantot, un pēc tam uz Labi.
Darbs tieši programmā Microsoft Query citu veidu vaicājumiem Ja vēlaties izveidot sarežģītāku vaicājumu, nekā ļauj vaicājumu vednis, varat strādāt tieši programmā Microsoft Query. Varat izmantot Microsoft Query, lai skatītu un mainītu vaicājumus, ko sākāt veidot vaicājumu vednī, vai varat izveidot jaunus vaicājumus, neizmantojot vedni. Strādājiet tieši programmā Microsoft Query, ja vēlaties izveidot vaicājumus, kas veic šādas darbības:
- Atlasīt konkrētus datus no lauka Lielā datu bāzē, iespējams, vēlēsities izvēlēties kādu no lauka datiem un izlaist datus, kas nav nepieciešami. Piemēram, ja nepieciešami dati par diviem produktiem laukā, kurā ir informācija par daudziem produktiem, varat izmantot kritērijus, lai atlasītu datus tikai par diviem nepieciešamajiem produktiem.
- Datu izgūšana pēc atšķirīgiem kritērijiem ikreiz, kad izpildāt vaicājumu Ja ir jāizveido viena un tā pati Excel atskaite vai kopsavilkums par vairākiem apgabaliem tajos pašos ārējos datos, piemēram, atsevišķa pārdošanas atskaite katram reģionam, varat izveidot parametru vaicājumu. Izpildot parametru vaicājumu, tiek prasīts norādīt vērtību, ko izmantot kā kritēriju, vaicājumā atlasot ierakstus. Piemēram, parametru vaicājums var prasīt ievadīt konkrētu reģionu, un šo vaicājumu var izmantot atkārtoti, lai izveidotu katru reģionālo pārdošanas atskaiti.
- Datu apvienošana dažādos veidos Vaicājumu vedņa izveidotie iekšējie savienojumi ir visbiežāk izmantotais savienojuma tips, kas tiek izmantots vaicājumu izveidē. Tomēr dažreiz var būt nepieciešams izmantot citu savienojuma veidu. Piemēram, ja jums ir tabula ar produktu pārdošanas informāciju un klientu informācijas tabula, iekšējais savienojums (tips, ko izveidojis vaicājuma vednis) neļaus izgūt klientu ierakstus klientiem, kuri nav veikuši pirkumu. Izmantojot Microsoft Query, varat savienot šīs tabulas, lai izgūtu visus klientu ierakstus, kā arī to klientu pārdošanas datus, kuri ir veikuši pirkumus.
Lai startētu Microsoft Query, veiciet tālāk norādītās darbības.
- Cilnes Dati grupā Iegūt ārējos datus noklikšķiniet uz No citiem avotiem un pēc tam uz No Microsoft Query.
- Dialoglodziņā Datu avota izvēle pārliecinieties, vai notīrīta izvēles rūtiņa Vaicājumu izveidei/rediģēšanai izmantot vaicājumu vedni .
- Veiciet dubultklikšķi uz datu avota, kuru vēlaties izmantot.
- vai -
Noklikšķiniet uz datu avota, kuru vēlaties izmantot, un pēc tam uz Labi.
Vaicājumu atkārtota izmantošana un koplietošana Gan vaicājumu vednī, gan programmā Microsoft Query vaicājumus var saglabāt kā .dqy failu, ko var modificēt, atkārtoti izmantot un koplietot. Programmā Excel var tieši atvērt .dqy failus, kas ļauj jums vai citiem lietotājiem no tā paša vaicājuma izveidot papildu ārējo datu diapazonus.
Lai atvērtu saglabātu vaicājumu no programmas Excel:
- Cilnes Dati grupā Iegūt ārējos datus noklikšķiniet uz No citiem avotiem un pēc tam uz No Microsoft Query. Tiek parādīts dialoglodziņš Datu avota izvēle .
- Dialoglodziņā Datu avota izvēle noklikšķiniet uz cilnes Vaicājumi .
- Veiciet dubultklikšķi uz saglabātā vaicājuma, kuru vēlaties atvērt. Vaicājums tiek parādīts programmā Microsoft Query.
Ja vēlaties atvērt saglabātu vaicājumu, bet Microsoft Query jau ir atvērts, noklikšķiniet uz Microsoft vaicājuma izvēlnes Fails un pēc tam noklikšķiniet uz Atvērt.
Ja veicat dubultklikšķi uz .dqy faila, programma Excel tiek atvērta, izpilda vaicājumu un pēc tam ievieto rezultātus jaunā darblapā.
Ja vēlaties kopīgot Excel kopsavilkumu vai atskaiti, kuras pamatā ir ārējie dati, varat citiem lietotājiem piešķirt darbgrāmatu, kurā iekļauts ārējs datu diapazons, vai izveidot veidni. Izmantojot veidni, varat saglabāt kopsavilkumu vai atskaiti, nesaglabājot ārējos datus, lai fails būtu mazāks. Ārējie dati tiek izgūti, lietotājam atverot atskaites veidni.
Darbs ar datiem programmā Excel
Pēc vaicājuma izveides vaicājumu vednī vai Microsoft Query datus var atgriezt Excel darblapā. Pēc tam dati kļūst par ārēju datu diapazonu vai rakurstabulas atskaiti, kuru var formatēt un atsvaidzināt.
Izgūto datu formatēšana Programmā Excel varat izmantot rīkus, piemēram, diagrammas vai automātiskās starpsummas, lai parādītu un apkopotu Microsoft Query izgūtos datus. Varat formatēt datus, un jūsu formatējums tiek saglabāts, atsvaidzinot ārējos datus. Lauku nosaukumu vietā varat izmantot savas kolonnu etiķetes un automātiski pievienot rindu numurus.
Programma Excel var automātiski formatēt jaunus datus, kurus ierakstāt diapazona beigās, lai tie atbilstu iepriekšējām rindām. Programma Excel var arī automātiski kopēt iepriekšējās rindās atkārtotas formulas un paplašināt tās papildu rindās.
Piezīme
Lai formātus un formulas varētu paplašināt uz jaunām diapazona rindām, tām jābūt vismaz trijās no piecām iepriekšējām rindām.
Šo opciju var ieslēgt (vai izslēgt vēlreiz) jebkurā laikā.
- Noklikšķiniet uz Faila>opcijas>papildu.
- Sadaļā Rediģēšanas opcijas atzīmējiet izvēles rūtiņu Paplašināt datu diapazona formātus un formulas . Lai atkal izslēgtu automātisko datu diapazona formatēšanu, notīriet šo izvēles rūtiņu.
Ārējo datu atsvaidzināšana Atsvaidzinot ārējos datus, tiek izpildīts vaicājums, lai izgūtu jaunos vai mainītos datus, kas atbilst specifikācijām. Vaicājumu var atsvaidzināt gan programmā Microsoft Query, gan programmā Excel. Programma Excel nodrošina vairākas vaicājumu atsvaidzināšanas opcijas, tostarp datu atsvaidzināšanu, kad atverat darbgrāmatu, un automātisku to atsvaidzināšanu noteiktos intervālos. Varat turpināt strādāt programmā Excel, kamēr dati tiek atsvaidzināti, un varat arī pārbaudīt statusu, kamēr dati tiek atsvaidzināti. Papildinformāciju skatiet sadaļā Ārējo datu savienojuma atsvaidzināšana programmā Excel.