Imibuzo ye-SQL kuMicrosoft Excel

I-SQL ulimi oluhlelekile oluhlelekile olusetshenziswa lapho kusetshenziswa imininingwane (i-DB). Noma kunesicelo esehlukile esibizwa ngokuthi Ukufinyelela kwemisebenzi yedatha eMicrosoft Office, i-Excel ingasebenza nemininingwane yolwazi ngokwenza imibuzo ye-SQL. Ake sibheke ukuthi singasakha kanjani isicelo esifanayo ngezindlela ezahlukahlukene.

Bheka futhi: Ungayakha kanjani i-database ku-Excel

Ukwakha umbuzo we-SQL e-Excel

Ulimi lombuzo lwe-SQL luhlukile kuma-analogues wokuthi cishe zonke izinhlelo zesimanje zokulawulwa kwedatha zisebenza nalo. Ngakho-ke, akumangazi ukuthi i-processor yetafula ethuthukile njengo-Excel, enemisebenzi eminingi eyengeziwe, nayo iyazi ukuthi isebenza kanjani ngalolu limi. Abasebenzisi be-SQL abasebenzisa i-Excel bangahlela imininingwane eminingi ehlukanayo ye-tabular.

Indlela yoku-1: sebenzisa isengezo

Kepha okokuqala, ake sibheke inketho lapho ungakha khona umbuzo we-SQL kusuka kwa-Excel ungasebenzisi amathuluzi ajwayelekile, kepha usebenzisa isengezo seqembu lesithathu. Enye yezengezo ezinhle kakhulu ezenza lo msebenzi yi-XLTools toolkit, engeza kulesi sici, inikela ngeminye imisebenzi eminingi. Kuliqiniso, kufanele kuqashelwe ukuthi isikhathi samahhala sokusebenzisa leli thuluzi yizinsuku eziyi-14 kuphela, khona-ke kuzodingeka uthenge ilayisense.

Landa i-XLTools Add-in

1. Ngemuva kokulanda ifayela lokungenisela xltools.exekufanele uqhubeke ukuyifaka. Ukuqala ukufakwa, chofoza kabili inkinobho yegundane kwesobunxele kufayela lokufaka. Ngemuva kwalokho kuzovuleka iwindi lapho uzodinga khona ukuthi uqinisekise isivumelwano sakho nesivumelwano selayisense sokusetshenziswa kwemikhiqizo yeMicrosoft - NET Uhlaka 4. Ukuze wenze lokhu, chofoza inkinobho "Ngiyamukela" phansi kwewindi.



2. Ngemuva kwalokho, isifaki silanda amafayela adingekayo bese siqala inqubo yokuwafaka.



3. Ngemuva kwalokho kuzovulwa iwindi lapho kufanele uqinisekise khona imvume yakho yokufaka lesi sengezo. Ukuze wenze lokhu, chofoza inkinobho Faka.



4. Ngemuva kwalokho inqubo yokufaka ye-isengezo uqobo iqala.



5. Ngemuva kokuphothulwa kwayo, kuzovulwa iwindi lapho kuzobikwa khona ukuthi ukufakwa kuqedwe ngempumelelo. Ewindini elibekiwe, chofoza inkinobho Vala.



6. Isengezo sifakiwe futhi manje usungakwazi ukusebenzisa ifayela le-Excel lapho udinga ukuhlela khona umbuzo we-SQL. Ngokubambisana ne-Excel sheet, kuvulwa iwindi lokungena kwekhodi yelayisense ye-XLTools. Uma unekhodi, udinga ukuyifaka endimeni efanele bese uchofoza inkinobho "Kulungile". Uma ufuna ukusebenzisa inguqulo yamahhala yezinsuku eziyi-14, vele uchofoze inkinobho Ilayisense Yesilingo.



7. Lapho ukhetha ilayisense lokuzama, elinye iwindi elincane liyavula lapho udinga ukukhombisa igama nesibongo sakho (ungasebenzisa i-alias) ne-imeyili. Ngemuva kwalokho chofoza inkinobho "Isikhathi sokuqalisa".



8. Okulandelayo, sibuyela ewindini lelayisense. Njengoba ukwazi ukubona, amanani ozifakile ayabonakala. Manje udinga nje uchofoza inkinobho "Kulungile".



9. Ngemuva kokuthi wenze ukukhwabanisa okungenhla, kuzokuvela ithebhu entsha kwisimo sakho se-Excel - "I-XLTools". Kepha asiphuthumi ukungena kuwo. Ngaphambi kokudala umbuzo, sidinga ukuguqula uhlu lwamatafula esizosebenza kulo ethebula elibizwa ngokuthi “smart” silinikeze igama.
   Ukuze wenze lokhu, khetha uhlu olucacisiwe noma noma iyiphi into yalo. Ukuba kuthebhu "Ikhaya" chofoza isithonjana "Fometha njengetafula". Ibekwe kubambo kubhokisi lamathuluzi. Izitayela. Ngemuva kwalokho uhlu lokukhethwa kwezitayela ezahlukahlukene luyavula. Khetha isitayela ocabanga ukuthi siyadingeka. Inketho ebekiwe ngeke iphazamise ukusebenza kwetafula nganoma iyiphi indlela, ngakho-ke sekela ukukhetha kwakho kuphela ngesisekelo sokuthandwa okubonakalayo.



10. Ukulandela lokhu, kuqala iwindi elincane. Kubonisa izixhumanisi zetafula. Njengomthetho, uhlelo uqobo “luthatha” ikheli eligcwele le-array, noma ngabe ukhetha iseli elilodwa kuphela kulo. Kepha uma kwenzeka, akunankinga ukubheka ulwazi olusensimini "Cacisa indawo yedatha yetafula". Futhi unake into eseduze Ithebula Lokuqondisa, bekunophawu lokuhlola uma izihloko kulungiselelo lakho likhona. Bese uchofoza inkinobho "Kulungile".



11. Ngemuva kwalokho, lonke uhla olubekiwe luzofakwa itafula, elizothinta zombili izakhiwo zalo (ngokwesibonelo, wolule) nesibonisi esibonakalayo. Ithebula elibekiwe lizonikezwa igama. Ukuyibona futhi uyishintshe ngokuthanda kwayo, chofoza kunoma iyiphi ingxenye yamalungu afanayo. Iqembu elingezekile lamathebhu liyavela kubambo - "Ukusebenza ngamatafula". Hambisa kuthebhu "Umqambi"ibekwe kuwo. Kubambo olubekwe ebhokisini lamathuluzi "Izindawo" enkundleni "Igama lethebula" igama le-array ukuthi uhlelo olwabelwe yona luzokhonjiswa.



12. Uma kufiswa, umsebenzisi angashintsha leli gama alinikeze ulwazi olufundisayo, ngokufaka inketho oyifunayo endle kusuka kukhibhodi futhi ngokucindezela ukhiye Ngena.



13. Ngemuva kwalokho, itafula selilungile futhi ungaqhubeka ngqo nenhlangano yesicelo. Hambisa kuthebhu "I-XLTools".



14. Ngemuva kokuya kubambo kubhokisi lamathuluzi "Imibuzo ye-SQL" chofoza isithonjana Qalisa i-SQL.



15. Iwashi lomshini wokwenziwa kwe-SQL liyaqala. Endaweni yalo yangakwesobunxele, kufanele ukhombise ishidi ledokhumenti netafula esihlahleni sedatha lapho isicelo sizokwenzelwe khona.

    Esikhathini esingenhla kwesokudla sewindows, esithatha iningi laso, isihleli sombuzo se-SQL uqobo. Kuyadingeka ukubhala ikhodi yohlelo kuyo. Amagama ekholamu etafuleni elikhethiwe lapho azokhonjiswa ngokuzenzakalelayo. Amakholomu wokusebenza akhethwa kusetshenziswa umyalo KHETHA. Kuyadingeka ukushiya ohlwini kuphela lawo makholomu ofuna umyalo obekiwe ukuthi usebenze.

    Ngokulandelayo, umbhalo womyalo ofuna ukuwusebenzisa ezintweni ezikhethiwe ubhaliwe. Amathimba enziwe ngokusebenzisa opharetha abakhethekile. Nazi izitatimende ze-SQL eziyisisekelo:

    • UKUHLELWA NGU -Amanani wokuhlunga;
    • Joyina - Joyina amatafula;
    • IQEMBU NGE - Ukuhlukaniswa kwamanani;
    • ISUM - Amanani entengo;
    • Hlukanisa - ukususwa okuphindwe kabili.

    Ngaphezu kwalokho, opharetha bangasetshenziswa ukwakha umbuzo I-MAX, IMIN, I-Avg, I-COUNT, Kwesobunxele nabanye

    Engxenyeni engezansi yewindows kufanele ukhombise lapho umphumela wokucubungula uzokhonjiswa khona. Lokhu kungaba ishidi elisha lencwadi (ngokuzenzakalelayo) noma ibanga elithile kushidi lamanje. Endabeni yamuva, udinga ukuhambisa inkinobho uye endaweni efanele bese ucacisa izixhumanisi zalolu banga.

    Ngemuva kokuthi isicelo senziwe futhi izilungiselelo ezihambisanayo zenziwe, chofoza inkinobho Gijima phansi kwewindi. Ngemuva kwalokho, ukusebenza okufakiwe kuzokwenziwa.

Isifundo: Amathebula ama-Smart e-Excel

Indlela 2: Sebenzisa amathuluzi akhelwe ngaphakathi e-Excel

Kukhona futhi indlela yokwenza umbuzo we-SQL ngokumelene nomthombo wedatha okhethiwe usebenzisa amathuluzi akhelwe nge-Excel.

1. Siqala uhlelo lwe-Excel. Ngemuva kwalokho, iya kuthebhu "Idatha".



2. Ebhokisini lamathuluzi "Ukuthola imininingwane yangaphandle"etholakala kubambo, chofoza isithonjana "Okusuka kweminye imithombo". Uhlu lwezinketho ezengeziwe luyavula. Khetha into ekuyo "Kusuka ku-uxhumano lwedatha".



3. Kuqala IWizard yokuxhuma idatha. Uhlu lwezinhlobo zemithombo yedatha, khetha "I-ODBC DSN". Ngemuva kwalokho chofoza inkinobho "Okulandelayo".



4. Kuvulwa iwindi Ama-Wizards wokuxhuma wedathalapho ufuna ukukhetha khona uhlobo lomthombo. Khetha igama "Database database yokufinyelela". Bese uchofoza inkinobho "Okulandelayo".



5. Kuvulwa iwindi elincane lokuzula, lapho kufanele uye khona kwisikhombi sendawo yedatha endaweni ye-mdb noma ifomethi ye-accdb bese ukhetha ifayela ledatha elifunayo. Ukuzulazula phakathi kokushayela okunengqondo kwenziwa emkhakheni okhethekile. Amadiski. Phakathi kwezikhombisi-ndlela, kwenziwa inguquko enkabeni ephakathi yewindows ebizwa "Izinhlu". Amafayela kusikhombisi samanje akhonjiswa kufasitele lesinxele lefasitela uma ene-mdb noma i-accdb. Kukule ndawo lapho udinga ukukhetha igama lefayela, bese uchofoza inkinobho "Kulungile".



6. Ukulandela lokhu, kwetafula lokukhethwa kwetafula kudathabhethi echaziwe yethulwa. Endaweni ephakathi nendawo, khetha igama lethebula olifunayo (uma ngabe liningana), bese uchofoza inkinobho "Okulandelayo".



7. Ngemuva kwalokho, iwindow yefayili yokuxhumeka yedatha iyavula. Nalu ulwazi oluyisisekelo olumayelana nokuxhumeka esilungiselele. Kuleli windi, chofoza inkinobho Kwenziwe.



8. I-Excel yokungenisa idatha yethulwe ekhasini lokusebenzela le-Excel. Kuyo, ungacacisa ukuthi ufuna idatha ivezwe ngaluphi uhlobo:
   • Ithebula;
   • Umbiko we-PivotTable;
   • Ishadi lokufingqa.

   Khetha inketho oyidingayo. Kudingeka okuncane okuncane ukukhombisa ukuthi idatha kufanele ibekwe kuphi: ekhasini elisha noma eshidini lamanje. Endabeni yokugcina, kungenzeka futhi ukukhetha izixhumanisi zendawo. Ngokuzenzakalelayo, idatha ibekwa eshidi lamanje. Ikhona elingaphezulu kwesokunxele sento engeniswayo litholakala esitokisini A1.

   Ngemuva kokuthi zonke izilungiselelo zokungenisa zichaziwe, chofoza inkinobho "Kulungile".



9. Njengoba ubona, itafula elivela database liyiswa kushidi. Ngemuva kwalokho siqhubekela kuthebhu "Idatha" bese uchofoza inkinobho Ukuxhumana, etholakala ethepheni kubhokisi lamathuluzi legama elifanayo.



10. Ngemuva kwalokho, kwethulwa iwindi lokuxhuma ibhuku. Kuyo sibona igama ledatha exhumeke ngaphambili. Uma kunedatha eminingana exhunyiwe, bese ukhetha enye edingekayo bese uyikhetha. Ngemuva kwalokho chofoza inkinobho "Izimpahla ..." ohlangothini lwesokunene lwewindi.



11. Iwindi lezinto zokuxhuma liqala. Sihamba kuyo ithebhu "Ukuchaza". Ensimini Umbhalo Weqembuetholakala ezansi kwewindows yamanje, sibhala umyalo we-SQL ngokuya nge-syntax yalolu limi, esikhulume ngayo ngamafuphi lapho sicubungula Indlela 1. Bese uchofoza inkinobho "Kulungile".



12. Emva kwalokho, uhlelo lubuyela ngokuzenzakalelayo kuwindi lokuxhumeka kwencwadi. Singachofoza inkinobho kuphela "Vuselela" kuwo. Kwenziwa isicelo ku-database, okuyothi emva kwalokho i-database ibuyise imiphumela yokuphindisela emuva ekhasini le-Excel, etafuleni esilihambise ngaphambili.

Indlela Yesithathu: Xhuma ku-SQL Server

Ngaphezu kwalokho, ngamathuluzi e-Excel, ungaxhuma kwi-SQL Server bese uthumela imibuzo kuyo. Ukwakha isicelo akuhlukile kunketho eyedlule, kepha okokuqala, udinga ukusungula uxhumano uqobo. Ake sibheke ukukwenza.

1. Siqala uhlelo lwe-Excel bese sidlulela kuthebhu "Idatha". Ngemuva kwalokho chofoza inkinobho "Okusuka kweminye imithombo", ebekwe etheyiphu kwibhulokhi yamathuluzi "Ukuthola imininingwane yangaphandle". Lesi sikhathi, kusuka ohlwini olwehliswayo, khetha inketho "Kusuka ku-SQL Server".



2. Lokhu kuvula iwindi lokuxhuma kwiseva yedatha. Ensimini "Igama leseva" khombisa igama leseva esixhuma kuyo. Eqenjini lepharamitha Imininingwane ye-Akhawunti udinga ukunquma ukuthi ukuxhumana kuzokwenzeka kanjani: ukusebenzisa ubuqiniso beWindows noma ngokufaka igama lomsebenzisi nephasiwedi. Sisetha switch ngokuya ngesinqumo. Uma ukhethe inketho yesibili, lapho-ke kubuye kuzodingeka ukuthi ufake igama lomsebenzisi nephasiwedi emikhakheni efanele. Ngemuva kokuthi wonke amasethingi aqediwe, chofoza inkinobho "Okulandelayo". Ngemuva kokwenza lesi senzo, ukuxhumana kweseva echaziwe kuyenzeka. Izinyathelo ezengeziwe zokuhlela umbuzo ku-database ziyefana nalezo esizichaze ngendlela eyedlule.

Njengoba ukwazi ukubona, ku-Excel Excel, umbuzo ungahlelelwa ngamathuluzi wohlelo owakhelwe ngaphakathi ngosizo lwenethiwekhi evela eceleni. Umsebenzisi ngamunye angakhetha inketho elula kuye futhi efaneleke kakhulu ekuxazululeni umsebenzi othile. Noma, izici zokungezelelwa kwe-XLTools, ngokuvamile, zithuthuke kakhulu kunamathuluzi we-Excel owakhelwe ngaphakathi. Okubi kakhulu kwe-XLTools ukuthi igama lokusetshenziswa kwamahhala kwe-add-in linqunyelwe kumaviki ekhalenda amabili kuphela.