Nigute ushobora gukora ikibazo cya sql muri excel

SQL ni imvugo izwi cyane ikoreshwa mugihe ukorana na data base (Ububiko). Nubwo ibikorwa hamwe nububiko muri Microsoft Office Service Hano haribisabwa bitandukanye - kwinjira, ariko gahunda ya Excel irashobora kandi gukorana na base ya SQL. Reka tumenye uburyo muburyo butandukanye ushobora gukora icyifuzo gisa.

Isomo: "Smart" Excel

Uburyo 2: Ukoresheje Ibikoresho Byinshi

Hariho uburyo bwo gukora ikibazo cya sql kumakuru yatoranijwe ukoresheje ibikoresho byubatswe na exel.

1. Koresha gahunda ya Excel. Nyuma yibyo, twimukira kuri tab "data".



2. Muri "Kubona amakuru yo hanze", iherereye kuri lebbon, kanda kuri "andi masoko". Urutonde rwibindi bikorwa bifungura. Hitamo ikintu "uhereye kumakuru yimyitozo ngororamubiri".



3. Umuyoboro wamakuru watangijwe. Kurutonde rwubwoko bwinkomoko yamakuru, hitamo "ODBC DSN". Nyuma yibyo, kanda kuri buto "ikurikira".



4. Idirishya rya Wizard rifungura aho ushaka guhitamo ubwoko bwinkomoko. Hitamo izina "Madamu Access Dudase". Noneho kanda kuri buto "ikurikira".



5. Idirishya ryo kugenda rifungura, aho ugomba guhindura ububiko bwububiko muri format ya MDB cyangwa ACCDB hanyuma uhitemo dosiye yifuzwa. Kugenda hagati ya disiki ya logique bikozwe muri "disiki" idasanzwe. Hano hari inzibacyuho ahantu hagati yidirishya ryitwa "Cataloge". Mubumoso bwibumoso bwidirishya ryerekana dosiye ziri mububiko bwubu niba ifite kwagura MDB cyangwa ACCDB. Ni muri kano gace ukeneye guhitamo izina rya dosiye, hanyuma ukande kuri buto "OK".



6. Gukurikira ibi, idirishya ryo gutoragura imbonerahamwe ryatangijwe mububiko bwagenwe. Mu karere ka hagati, hitamo izina ryimeza wifuza (niba hari benshi muribo), hanyuma ukande kuri buto "ikurikira".



7. Nyuma yibyo, amakuru ahuza dosiye ihuza idirishya rifungura. Ibi bikubiyemo amakuru yibanze yerekeye isano twashizeho. Muri iri idirishya, birahagije gukanda kuri buto ya "Kurangiza".



8. Urupapuro rwingenzi rutangiza amakuru yimodoka. Irashobora guterwa muburyo ushaka amakuru yatanzwe:
   • Ameza;
   • Raporo y'ameza yahujwe;
   • Incamake.

   Hitamo inzira wifuza. Hasi hepfo, ugomba kwerekana aho amakuru agomba gushyirwa: kurupapuro rushya cyangwa kurupapuro rwubu. Mu rubanza rwa nyuma, amahirwe yo guhitamo imikoreshereze y'amacumbi nayo yatanzwe. Mburabuzi, amakuru ashyirwa kurupapuro rwubu. Inguni yo hejuru yibumoso yikintu cyatumijwe mu mahanga cyashyizwe muri selire A1.

   Nyuma ya byose bitumizwa ku mahanga byagenwe, kanda kuri buto "OK".



9. Nkuko tubibona, ameza yo muri data base yimuriwe kurupapuro. Noneho twimukira kuri tab "Data" hanyuma ukande buto "Ihuza", ishyirwa kuri kaseti mugikoresho hamwe nizina rimwe.



10. Nyuma yibyo, idirishya rihuza ryatangiye. Muri yo tubona izina rya base base ryahujwe natwe mbere. Niba ububiko buhujwe aribwo buryo, noneho hitamo icyifuzo ukabigaragaza. Nyuma yibyo, kanda kuri "Indangamuntu ..." kuruhande rwiburyo bwidirishya.



11. Idirishya rihuza ritangira. Turayigenda muri tab "ibisobanuro". Muri "inyandiko yinyandiko", iherereye hepfo yidirishya ryubu, andika itegeko rya SQL ukurikije syntax yuru rurimi, twavuze muri make mugihe dusuzumye uburyo 1. Noneho kanda buto ya "OK".



12. Nyuma yibyo, bihita gusubira kumurongo. Biracyasigaye gusa kanda kuri buto "ivugurura". Ubujurire kuri data base hamwe nibibazo bibaho, nyuma yububiko bwa nyuma yo gusubiza ibisubizo byo gutunganya inyuma kurupapuro rwa Excel, kumeza mbere yoherejwe natwe.

Uburyo 3: Guhuza na SQL seriveri SQL Seriveri

Byongeye kandi, binyuze mubikoresho bya Excel, hari isano na seriveri ya SQL hanyuma wohereze kubisaba. Kubaka ikibazo ntigitandukanye na verisiyo ibanza, ariko mbere ya byose, ugomba kwinjizamo isano ubwayo. Reka turebe uko twabikora.

1. Koresha gahunda ya Excel hanyuma ujye kuri tab. Nyuma yibyo, kanda kuri buto "uhereye kuri buto", zishyirwa kuri kaseti muri "Shakisha amakuru yimyanyabikoresho. Iki gihe, uhereye kurutonde rwurutonde, hitamo amahitamo "kuva kuri seriveri ya SQL seriveri".



2. Ifungura idirishya rihuza kuri seriveri ya base. Muri seriveri izina ryumurima, vuga izina ryiyo seriveri ukora ihuza. Mu itsinda rya "Konti", ugomba guhitamo neza uko wahuza: ukoresheje Windows kwemeza cyangwa winjiza izina ryukoresha nijambobanga. Ndamugaragaza mpindura hakurikijwe icyemezo cyafashwe. Niba wahisemo amahitamo ya kabiri, noneho wongeyeho, imirima ikwiye igomba kwinjiza izina ryukoresha nijambobanga. Nyuma yimiterere yose yafashwe, kanda kuri buto "ikurikira". Nyuma yo gukora iki gikorwa, guhuza seriveri yagenwe. Ibindi bikorwa kumuteguro wibikorwa byububiko bisa nibisobanuye muburyo bwambere.

Nkuko mubibona, muri Excele SQL, icyifuzo kirashobora gutegurwa nkibikoresho byubatswe hamwe ninyongera-ya gatatu yongeyeho. Buri mukoresha arashobora guhitamo amahitamo yoroshye kubwibyo kandi arushijeho gukosora umurimo washyizweho. Nubwo, ibishoboka bya Xltools byongeyeho, muri rusange, biracyari byiza cyane kuruta ibikoresho byashyizwemo. Kubura nyamukuru kwa Xltools nuko igihe cyo gukoresha kubuntu rwinyongera kigarukira kubyumweru bibiri gusa.