Indlela yokuthumela isithombe nge-imeyili

I-SQL iyilimi oluthandwayo lohlelo olusetshenziselwa ukusebenzisana nolwazi (DB). Nakuba kunesicelo esihlukile semisebenzi yedatha kwi-Microsoft Office suite - Ukufinyelela, kodwa i-Excel ingasebenza futhi nedatha, yenza imibuzo ye-SQL. Ake sithole ukuthi singakha kanjani isicelo esinjalo ngezindlela ezihlukahlukene.

Bheka futhi: Indlela yokudala idatha ku-Excel

Ukudala umbuzo we-SQL ku-Excel

Ulimi lwemibuzo ye-SQL luhluke emifanekisweni yokuthi cishe zonke izinhlelo zesimanje zokuphathwa kwedatha zisebenza nayo. Ngakho-ke, akumangalisi neze ukuthi isisombululo esinjalo se-tabular njenge-Excel, enemisebenzi eminingi eyengeziwe, ingasebenza futhi nalolu limi. Abasebenzisi abanekhono ekusebenziseni i-SQL usebenzisa i-Excel bangahlela idatha ehlukile ehlukile ye-tabular.

Indlela 1: Sebenzisa ama-Add-ons

Kodwa okokuqala, ake sicabangele ukhetho lapho ungakha umbuzo we-SQL kusuka ku-Excel ngaphandle kokusebenzisa ithuluzi elijwayelekile, kodwa usebenzisa ukungena kweqembu langaphandle. Enye yezengezo ezinhle kakhulu ezenza lo msebenzi yi-toolkit ye-XLTools, okungeziwe, kuhlanganise nalesi sici, inikeza ezinye izinto eziningi. Noma kunjalo, kufanele kuqashelwe ukuthi isikhathi samahhala sokusebenzisa ithuluzi izinsuku ezingu-14 kuphela, bese kufanele uthenge ilayisense.

Landa i-XLTools Add-on

  1. Ngemuva kokuthi ulande ifayela lokungeza xltools.exekufanele iqhubeke nokufakwa kwayo. Ukuze usebenzise lesi sitsha, chofoza kabili inkinobho yegundane lesokunxele kufayela lokufaka. Ngemuva kwalokho, iwindi izolungiswa lapho kuzodingeka khona ukuqinisekisa isivumelwano sakho nesivumelwano selayisense sokusebenzisa imikhiqizo ye-Microsoft - NET Framework 4. Ukwenza lokhu, chofoza nje inkinobho "Yamukela" phansi kwefasitela.

  2. Emva kwalokho, isifaki sizolanda amafayela adingekayo futhi siqala inqubo yokufaka.

  3. Okulandelayo, iwindi livuleka lapho kufanele uqinisekise imvume yakho ukufaka lokhu okungenayo. Ukwenza lokhu, chofoza inkinobho. "Faka".

  4. Khona-ke uqala inqubo yokufaka ngqo i-add-in ngokwayo.

  5. Ngemva kokuqedela, iwindi lizovuleka lapho lizobikwa khona ukuthi ukufakwa kuphelile ngempumelelo. Kuwindi ecacisiwe, chofoza nje inkinobho "Vala".

  6. I-add-in efakiwe futhi manje ungasebenzisa ifayela le-Excel lapho kudingeka khona ukuhlela umbuzo we-SQL. Kanye nepheshana le-Excel, iwindi livula ukufaka ikhodi yelayisensi ye-XLTools. Uma unayo ikhodi, udinga ukuyifaka ensimini efanele bese uchofoza inkinobho "Kulungile". Uma ufuna ukusebenzisa inguqulo yamahhala ezinsukwini ezingu-14, kufanele nje uchofoze inkinobho. "Ilayisense Yokulinga".

  7. Uma ukhetha ilayisense yokuhlola, elinye iwindi elincane livula lapho udinga ukucacisa igama lakho lokuqala nelokugcina (ungasebenzisa i-pseudonym) ne-imeyili. Emva kwalokho, chofoza inkinobho "Qalisa Isikhathi Sokuhlola".

  8. Okulandelayo sibuyela ewindini lelayisense. Njengoba ubona, amanani owafakile asevele abonisiwe. Manje nje udinga ukucindezela inkinobho. "Kulungile".

  9. Ngemuva kokuthi wenze izinyathelo ezingenhla, ithebhu entsha izovela kukhophi yakho ye-Excel - "XLTools". Kodwa hhayi ngokusheshisa ukungena kuwo. Ngaphambi kokudala umbuzo, udinga ukuguqula uhlu lwetafula, esizosebenza ngalo, ekubhekeni etafuleni okuthiwa "smart" bese ulinika igama.
    Ukuze wenze lokhu, khetha uhlu olucacisiwe noma noma yiziphi izakhi zalo. Ukuba ngaphakathi kuthebhu "Ikhaya" chofoza kusithonjana "Fometha njengetafula". Ifakwa kuthempini ematheni amathuluzi. "Izitayela". Emva kwalokho uhlu lwezitayela ezahlukene luvuliwe. Khetha isitayela osibona kufanelekile. Lokhu kukhetha ngeke kuthinte ukusebenza kwetafula, ngakho-ke basekela ukhetho lwakho kuphela ngesisekelo sokuthandwa okubukwayo.

  10. Ngemva kwalokhu, iwindi elincane liqalisiwe. Ikhombisa izixhumanisi zetafula. Njengomthetho, uhlelo ngokwalo "lukhetha" ikheli eligcwele le-array, noma ngabe ukhethe iseli elilodwa kuphela kulo. Kodwa uma kungenjalo akuphazamisi ukuhlola imininingwane esensimini "Cacisa indawo yedatha yetafula". Udinga futhi ukunaka mayelana nento "Ithebula ngezihloko", kwakukhona umkhawulo, uma izinhloko ezigciniwe zakho zikhona. Bese uchofoza inkinobho "Kulungile".

  11. Ngemuva kwaloko, lonke uhla olucacisiwe luzofomathwa njengetafula, oluzophazamisa kokubili izakhiwo zalo (isibonelo, ukwelula) nokuboniswa okubukwayo. Itafula elichaziwe lizobizwa ngegama. Ukuze uyiqaphele bese uyishintsha ngentando, sichofoza kunoma iyiphi ingxenye ye-array. Iqembu elengeziwe lamathebhu livela ebhebheni - "Ukusebenzisana namatafula". Hambisa kuthebhu "Umakhi"kufakwe kuwo. On the tape ebhodini amathuluzi "Izakhiwo" ensimini "Ithebula Igama" igama lendawo, okuyinto uhlelo olunikezwe yona ngokuzenzakalelayo, izoboniswa.

  12. Uma ngabe ufisa, umsebenzisi angashintsha leli gama libe olunye ulwazi ngokungena ekukhethweni oyifunayo ensimini kusukela kwikhibhodi bese ucindezela ukhiye Ngena.

  13. Emva kwalokho, itafula lilungele futhi ungaya ngqo enhlanganweni yesicelo. Hambisa kuthebhu "XLTools".

  14. Ngemuva kokuguqulwa kwitheyipu ematheni amathuluzi "Imibuzo ye-SQL" chofoza kusithonjana Run SQL.

  15. Iwindi le-SQL lokuqalisa umbuzo liqala. Endaweni yayo yesobunxele, cacisa ishidi lwedokhumenti kanye netafula emthini wedatha lapho umbuzo uzokwakhiwa khona.

    Esikhathini esincane sokungena kwefasitela, okuyinto ehlala kuyo yonke iningi, ngumhleli we-SQL umbuzo ngokwayo. Kuyo kudingeka ubhale ikhodi yohlelo. Amakholomu amagama etafuleni akhethiwe ayobe esevele eboniswa ngokuzenzekelayo. Ukukhethwa kwamakholomu okucubungula kwenziwa ngomyalo Khetha. Udinga ukushiya kuhlu kuphela lezo kholomu ofuna umyalo ocacisiwe ukuthi usebenze.

    Okulandelayo, bhala umbhalo womyalo ofuna ukuwenza ezintweni ezikhethiwe. Imiyalo ikhiwa besebenzisa opharetha akhethekile. Nazi izitatimende eziyisisekelo ze-SQL:

    • UKUHLELA - ukuhlunga amanani;
    • JOIN - faka amatafula;
    • I-GROUP BY - ukuqoqwa kwamanani;
    • SUM - ukufingqwa kwamanani;
    • Ukuhlukana - susa okuphindwe kabili.

    Ngaphezu kwalokho, ekwakheni kombuzo, ungasebenzisa opharetha MAX, MIN, Avg, COUNT, BHEKA kanye nabanye

    Engxenyeni engezansi kwewindi, kufanele ukhombise lapho umphumela wokucubungula uzoboniswa khona. Lokhu kungaba ishidi elisha lencwadi (ngokuzenzakalelayo) noma ibanga elithile eshadini samanje. Esikhathini esilandelayo, udinga ukuhlela kabusha ushintsho endaweni efanele futhi ucacise izixhumanisi zalolu hlu.

    Ngemuva kokuthi isicelo senziwe futhi izilungiselelo ezihambelanayo zenziwe, chofoza inkinobho. Qalisa phansi kwefasitela. Emva kwalokho, ukusebenza okufakiwe kuzokwenziwa.

Isifundo: Amatafula ahlakaniphile ku-Excel

Indlela 2: Sebenzisa amathuluzi we-Excel angene ngaphakathi

Kukhona indlela yokwakha umbuzo we-SQL womthombo wedatha okhethiwe usebenzisa amathuluzi we-Excel akhethiwe.

  1. Qalisa uhlelo lwe-Excel. Ngemva kwalokho kuthutha kuthebhu "Idatha".

  2. Ebhokisini lamathuluzi "Ukuthola Idatha Yangaphandle"okuyinto itholakala ku-tape, chofoza isithonjana "Kusuka kwezinye izinto". Uhla lwezinketho ezengeziwe. Khetha into kuwo "Kusukela kuDatha Connection Wizard".

  3. Iqala I-Data Connection Wizard. Kuhlu lwemithombo yomthombo wedatha, khetha "I-ODBC DSN". Emva kwalokho chofoza inkinobho "Okulandelayo".

  4. Iwindi ivula Ama-Data Connection Wizards, lapho okudingeka ukhethe uhlobo lomthombo. Khetha igama "MS Access Database". Bese uchofoza inkinobho. "Okulandelayo".

  5. Iwindi elincane lokuzulazula livula lapho okufanele uhambe khona kwisiqondisi sendawo yedatha ku-mdb noma ukufaka ifomethi bese ukhetha ifayili yedatha edingekayo. Ukuhamba phakathi kokushayela okunengqondo kwenziwa ensimini ekhethekile. "Disks". Kukhona ushintsho phakathi kwamarekhodi endaweni ephakathi kwewindi ebizwa ngokuthi "Izingqikithi". Esikhathini sokungakwesokunxele sefasitela, amafayela akhona kuhla lwemibhalo yamanje ayaboniswa uma anesandiso se-mdb noma i-accdb. Kule ndawo okudingeka ukhethe igama lefayela, bese uchofoza inkinobho "Kulungile".

  6. Ukulandela lokhu, iwindi lokukhetha ithebula ku-database ecacisiwe iqalisiwe. Endaweni ephakathi, khetha igama letafula elifunayo (uma kuneziningana), bese uchofoza inkinobho "Okulandelayo".

  7. Emva kwalokho, ukulondoloza iwindi lefayela lefayela lokuxhuma livula. Nansi ulwazi oluyisisekelo lokuxhuma esikulungiselele. Kulo iwindi, chofoza nje inkinobho. "Yenzelwe".

  8. Ekhasini le-Excel, iwindi lokungenisa idatha lisunguliwe. Kungenzeka ukukhombisa ukuthi iyiphi ifomu ofuna ukuthi idatha ihanjiswe ngayo:
    • Ithebula;
    • Umbiko we-Pivot Table;
    • Ishadi lokufingqa.

    Khetha inketho oyifunayo. Ngaphansi nje udinga ukucacisa ngqo ukuthi ungabeka yini idatha: kushidi elisha noma kushidi langamanje. Esikhathini esilandelayo, kungenzeka futhi ukhethe izixhumanisi zendawo. Ngokuzenzakalelayo, idatha ifakwa eshadini samanje. Ikhoneni lesobunxele eliphezulu lezinto ezifakiwe lifakwa esitokisini. A1.

    Ngemuva kokuthi zonke izilungiselelo zokungeniswa zicacisiwe, chofoza inkinobho "Kulungile".

  9. Njengoba ubona, ithebula kusukela ku-database ishukunyiselwa eshidi. Bese uthuthela kuthebhu "Idatha" bese uchofoza inkinobho "Ukuxhumana"okuyinto efakwe kutheyipu ebhodini lamathuluzi enegama elifanayo.

  10. Ngemuva kwalokho, ukuxhuma encwadini kuqalisiwe. Kuyo sibona igama le-database exhunyiwe ngaphambili. Uma kunamininingwane yolwazi ehlangene, khetha okuyidingayo bese ukhetha. Emva kwalokho chofoza inkinobho "Izakhiwo ..." ngakwesokudla kwes window.

  11. Iwindi lezakhiwo zokuxhumana liqala. Hambisa kuthebhu "Incazelo". Ensimini "Yala umyalezo", phansi kwefasitela lamanje, bhala umyalo we-SQL ngokuhambisana nesi-syntax yolimi, esiwakhulume ngokufushane ngenkathi sicabangela Indlela 1. Bese uchofoza inkinobho "Kulungile".

  12. Ngemuva kwalokho, ukubuyela ngokuzenzakalelayo kwiwindi yokuxhumana kwencwadi kwenziwa. Singachofoza kuphela inkinobho "Qala kabusha" kuyo. I-database ifinyelelwe ngombuzo, ngemuva kwalokho i-database ibuyisela imiphumela yokubuyisela emuva ku-Excel sheet, etafuleni ngaphambilini edluliselwe kithi.

Indlela 3: Xhuma ku-SQL Server

Ngaphezu kwalokho, ngokusebenzisa amathuluzi we-Excel, kungenzeka ukuxhuma ku-SQL Server bese uthumela izicelo kuyo. Ukwakha umbuzo awufani nalokho okwedlule, kodwa okokuqala, udinga ukusungula uxhumano ngokwaso. Ake sibone ukuthi singayenza kanjani.

  1. Run Excel bese uya kuthebhu "Idatha". Emva kwalokho chofoza inkinobho "Kusuka kwezinye izinto"okuyinto efakwe kwi-tape ebhodini lamathuluzi "Ukuthola Idatha Yangaphandle". Lesi sikhathi, ohlwini oluvela, khetha ukhetho "Kusuka ku-SQL Server".

  2. Ukuxhuma kwisiphakeli sedatha kuvulwa. Ensimini "Igama leSeva" ucacise igama leseva esixhuma kuyo. Eqenjini lemingcele "Ulwazi lwe-Akhawunti" udinga ukunquma ukuthi uxhumano luzokwenzeka kanjani: ukusebenzisa i-Windows ukuqinisekiswa noma ngokufaka igama lomsebenzisi nephasiwedi. Sichaza inkinobho ngokusho kwesinqumo. Uma ukhethe ukhetho lwesibili, khona-ke ngaphezu kwezinkambu ezihambelanayo kuzodingeka ufake igama lomsebenzisi nephasiwedi. Ngemuva kokuthi zonke izilungiselelo zenziwe, chofoza inkinobho. "Okulandelayo". Ngemva kokwenza lesi senzo, ukuxhuma kwiseva ecacisiwe kwenzeka. Izenzo ezengeziwe zokuhlela umbuzo wesisekelo zifana nalabo ezichazwe ngendlela eyedlule.

Njengoba ubona, ku-Excel, imibuzo ye-SQL ingahle ihlelwe, mhlawumbe ngamathuluzi akhiwe ngaphakathi kohlelo, noma ngokusebenzisa ukungena kweqembu langaphandle. Umsebenzisi ngamunye angakhetha ukhetho oluhle kakhulu kuye futhi lungcono kakhulu ekuxazululeni umsebenzi othile. Nakuba, amandla okufaka i-XLTools, ngokujwayelekile, namanje ahamba phambili kakhulu kunamathuluzi we-Excel akhiwe. Ukungalungi okuyinhloko kwe-XLTools yukuthi isikhathi sokusetshenziswa mahhala kwesengezo singalinganiselwe kwamasonto amabili kuphela ekhalenda.

Okuthunyelwe Popular

https://termotools.com zu.termotools.com © Imeyili 2024