Mafunso a SQL mu Microsoft Excel

SQL ndi chilankhulo chodziwika bwino chomwe chimagwiritsidwa ntchito pogwira ntchito ndi database (DB). Ngakhale pali ntchito yina yomwe imatchedwa Kufikika kwa ntchito zogwirira ntchito mu Microsoft Office, Excel ikhoza kugwiranso ntchito ndi malo osungirako zinthu popanga mafunso a SQL. Tiyeni tiwone momwe mungapangire pempho lofananalo m'njira zosiyanasiyana.

Onaninso: Momwe mungapangire zosungira mu Excel

Kupanga funso la SQL ku Excel

Chilankhulo chofunsira SQL chimasiyana ndi ma analogues chifukwa pafupifupi makina onse amakono azomwe amachita nawo. Chifukwa chake, sizosadabwitsa konse kuti purosesa wapamwamba wa tebulo ngati Excel, yemwe ali ndi ntchito zina zowonjezera, akudziwanso momwe angagwirire ntchito ndi chilankhulochi. Ogwiritsa ntchito SQL omwe amagwiritsa ntchito Excel akhoza kukonza zambiri zosiyanitsa zambiri za tabular.

Njira 1: gwiritsani ntchito chowonjezera

Koma, choyamba, tiyeni tiwone njira yomwe mungapangire kufunsa kwa SQL kuchokera ku Excel osagwiritsa ntchito zida wamba, koma kugwiritsa ntchito pulogalamu yowonjezera-yachitatu. Chimodzi mwazowonjezera zabwino zomwe zimagwira ntchito iyi ndi XLTools toolkit, yomwe, kuwonjezera pa izi, imapereka ntchito zina zambiri. Zowona, ziyenera kudziwika kuti nthawi yaulere yogwiritsa ntchito chida ndi masiku 14 okha, ndiye kuti muyenera kugula chiphatso.

Tsitsani kuwonjezera XLTools

1. Mukatsitsa fayilo yowonjezera nmkatolaayenera kupitiriza kukhazikitsa. Kuti muyambe kuyambitsa, dinani kawiri batani lakumanzere pa fayilo yoyika. Pambuyo pake, zenera lidzatsegulidwa momwe mungafunikire kutsimikizira mgwirizano wanu ndi chilolezo chogwiritsira ntchito zinthu za Microsoft - NET Chimango 4. Kuti muchite izi, dinani batani basi "Ndikuvomereza" pansi pazenera.



2. Pambuyo pake, woyikirayo amatsitsa mafayilo ofunikira ndikuyamba njira yoyika.



3. Kenako zenera lidzatseguka pomwe muyenera kutsimikizira kuvomereza kwanu kuti muwonjezere zowonjezera. Kuti muchite izi, dinani batani Ikani.



4. Kenako kukhazikitsa njira zowonjezera pakokha kumayamba.



5. Akamaliza, zenera lidzatsegulidwa pomwe azidzati kuti unsembe udamalizidwa bwino. Pa zenera lotchulidwa, dinani batani Tsekani.



6. Zowonjezerazo zidakhazikitsidwa ndipo tsopano mutha kuyendetsa fayilo ya Excel momwe mungafunire kukonzekera funso la SQL. Pamodzi ndi pepala la Excel, zenera limatsegulira kulowa kwa XLTools code. Ngati muli ndi code, muyenera kuyiyika pamalo oyenera ndikudina batani "Zabwino". Ngati mukufuna kugwiritsa ntchito pulogalamu yaulere kwa masiku 14, ndiye dinani batani Chilolezo Choyesa.



7. Mukamasankha layisensi yoyeserera, zenera lina laling'ono limatseguka, pomwe muyenera kufotokoza dzina lanu ndi dzina lanu (mutha kugwiritsa ntchito ma alias) ndi imelo. Pambuyo pake, dinani batani "Yambani nthawi yoyeserera".



8. Kenako, tibwerera pazenera la layisensi. Monga mukuwonera, zomwe mudalowa zikuwonetsedwa kale. Tsopano mukungofunika dinani batani "Zabwino".



9. Mukatha kugwiritsa ntchito pamanja pamtunduwu, tabu yatsopano ipezeka patsamba lanu la Excel - "XLTools". Koma sitiri othamangira kulowa nawo. Tisanapange funso, tifunika kusintha zidutswa zomwe tidzagwiritse ntchito patebulo lotchedwa "anzeru" ndikuwapatsa dzina.
   Kuti muchite izi, sankhani makonda kapena chinthu chilichonse. Kukhala mu tabu "Pofikira" dinani pachizindikiro "Fomati ngati tebulo". Imayikidwa pa riboni m'bokosi la chida. Masitaelo. Pambuyo pake mndandanda wosankha wamitundu yosiyanasiyana umatsegulidwa. Sankhani kalembedwe kamene mukuganiza kuti ndikofunikira. Chisankho chomwe chatchulidwa sichingakhudze mawonekedwe a tebulo mwanjira iliyonse, chifukwa chake khazikitsani chisankho chanu pokhapokha pazokonda kuwonekera.



10. Kutsatira izi, zenera laling'ono limayamba. Zimawonetsera zoyang'anira patebulopo. Monga lamulo, pulogalamuyiyo “imatenga” adilesi yonseyo, ngakhale mutasankha khungu limodzi lokha. Koma zingachitike, sizivuta kuwona zomwe zili m'munda "Fotokozani komwe masamba patebulopo". Komanso samalani ndi chinthu chapafupi Mutu wa Mitu, panali cheke ngati ma mutu anu akhalapo. Kenako dinani batani "Zabwino".



11. Zitatha izi, mtundu wonse womwe udafotokozedwawu udzajambulidwa ngati tebulo, womwe udzakhudze zonse zake (mwachitsanzo, kutambasula) ndi chowonekera. Gome lomasuliralo lidzapatsidwa dzina. Kuti muzindikire ndikusintha mwakufuna kwanu, dinani pazinthu zilizonse za gulu. Gulu lowonjezera la ma tabu likuwoneka pa nthiti - "Kugwira ntchito ndi matebulo". Pitani ku tabu "Wopanga"kuyikidwa mmenemo. Pa nthiti yomwe ili m'bokosi la chida "Katundu" m'munda "Dongosolo la tebulo" dzina la omwe adayikidwa kuti adziwonetsetse kuti pulogalamu yomwe adayipatsayo idawonetsedwa.



12. Ngati angafune, wogwiritsa ntchitoyo amatha kusintha dzinali kukhala lodziwitsa zambiri, kungolowetsa njira yomwe akufuna muvalo ndi kukanikiza fungulo Lowani.



13. Pambuyo pake, tebulo lakonzeka ndipo mutha kupita mwachindunji ku bungwe la pempholo. Pitani ku tabu "XLTools".



14. Pambuyo popita ku riboni mu bokosi la zida "Mafunso a SQL" dinani pachizindikiro Thamanga SQL.



15. Windo lofunsa mafunso la SQL liyamba. Kumalo ake akumanzere, muyenera kuwonetsa pepalalo ndi tebulo pamtengo wazomwe pempho lidzapangidwire.

    Pazenera lakumanja la zenera, lomwe limakhala ambiri, ndi mayankho a SQL palokha. Ndikofunikira kulemba code pulogalamu mmenemo. Mayina amtundu wa tebulo losankhidwa pamenepo adzawonetsedwa kale. Mizati yokonza imasankhidwa pogwiritsa ntchito lamulo Sankhani. Ndikofunikira kusiya mndandanda ndizokhawo zomwe mukufuna kuti lamulo lithe kuzitsatira.

    Kenako, lemba la lamulo lomwe mukufuna kutsatira pazinthu zomwe zidasankhidwa zalembedwa. Ma timu amapangidwa pogwiritsa ntchito akatswiri apadera. Nayi ziganizo zoyambira za SQL:

    • YOLEMBEDWA NDI - kusankha mfundo;
    • Lowani - kuphatikiza matebulo;
    • KULIMA KWA - gulu la mfundo;
    • SUM - chitetezo chamakhalidwe;
    • Zosiyanitsa - Kuchotsa zobwereza.

    Kuphatikiza apo, ogwiritsira ntchito akhoza kugwiritsidwa ntchito popanga funso MAX, Mamin, Avg, COUNT, Kumanzere ndi ena

    M'munsi mwa zenera muyenera kuwonetsa komwe zotsatira zake zidzawonetsedwa. Uwu ukhoza kukhala pepala latsopano la buku (mwa kusankhapo) kapena mtundu winawake patsamba latsopanolo. Pomaliza, muyenera kusunthira ku malo oyenera ndikumatchula koyanjana kwa malowa.

    Pempho litatha ndikupanga mawonekedwe ofananirako, dinani batani Thamanga pansi pazenera. Pambuyo pake, kuchitidwa kolowera kudzachitika.

Phunziro: Ma Mateti Anzeru ku Excel

Njira 2: gwiritsani ntchito zida zopangira Excel

Palinso njira yopangira funso la SQL motsutsana ndi magawo omwe asankhidwa pogwiritsa ntchito zida za Excel zopangidwa.

1. Timayamba pulogalamu ya Excel. Pambuyo pake, pitani ku tabu "Zambiri".



2. Mu bokosi la zida "Kupeza deta yakunja"ili pa riboni, dinani chizindikiro "Kuchokera kwina". Mndandanda wazosankha zina zikutseguka. Sankhani zomwe zili mmenemo "Kuchokera pa wizard yolumikizira deta".



3. Iyamba Wizard yolumikizira deta. Pamndandanda wamitundu ya magawo azidziwitso, sankhani "ODBC DSN". Pambuyo pake, dinani batani "Kenako".



4. Zenera limatseguka Ma Wizards Ogwirizanitsa ndi datamomwe mukufuna kusankha mtundu wa gwero. Sankhani dzina "Mbiri Yofikira ya MS". Kenako dinani batani "Kenako".



5. Windo laling'ono loyang'anira limatseguka, lomwe muyenera kupita kumalo osungirako database mu mdb kapena mtundu wa addb ndikusankha fayilo ya database yomwe mukufuna. Kusuntha pakati pamagalimoto oyendetsa bwino kumachitika m'malo apadera. Disks. Pakati pa zoongolera, kusintha kumachitika pakatikati pazenera lotchedwa "Zolemba". Mafayilo omwe ali mufayilo yaposachedwa amawonetsedwa pazenera lakumanzere ngati ali ndi mdb yowonjezera kapena accdb. Ndi m'dera lino momwe muyenera kusankha fayilo, ndikudina batani "Zabwino".



6. Kutsatira izi, zenera losankha tebulo mu database yomwe idatchulidwa imayambitsidwa. Pakati penipeni, sankhani dzina la tebulo lomwe mukufuna (ngati alipo angapo), kenako dinani batani "Kenako".



7. Pambuyo pake, zenera lolumikizira mafayilo la data limatsegulidwa. Nayi chidziwitso chofunikira chokhudzana ndi kulumikizana komwe tidakonza. Pa zenera ili, dinani batani Zachitika.



8. Windo la Excel lolowetsa deta limakhazikitsidwa patsamba lothandizira. Mmenemo, mungafotokozere mtundu womwe mukufuna kuti deta ifotokozeredwe:
   • Gome;
   • Lipoti la PivotTable;
   • Chidule.

   Sankhani njira yomwe mukufuna. Kutsikira pang'ono kumafunika kuti muwonetsetse momwe tsokalo liyenera kuyikidwira: patsamba latsopano kapena papepala latsopanoli. Pomaliza, ndikothekanso kusankha magwirizano amalo. Mwachisawawa, deta imayikidwa pa pepala lapano. Kona yakumanzere ya chinthu chomwe chalowetsedwa ili mgululi A1.

   Pambuyo posankha zoikamo zonse zatchulidwa, dinani batani "Zabwino".



9. Monga mukuwonera, tebulo kuchokera ku database limasunthidwa ku pepala. Kenako timasunthira ku tabu "Zambiri" ndipo dinani batani Maulalo, yomwe ili pa tepi mu bokosi la chida cha dzina lomweli.



10. Pambuyo pake, zenera lolumikizana ndi bukuli linayambitsidwa. Mmenemo tikuwona dzina la database yomwe idalumikizidwa kale. Ngati pali mitundu yosiyanasiyana yolumikizidwa, sankhani yofunikira ndikusankha. Pambuyo pake, dinani batani "Katundu ..." kumanja kwa zenera.



11. Yenera kulumikiza zenera limayamba. Timasunthira mmenemo tabu "Tanthauzo". M'munda Mawu a Guluyomwe ili pansi pazenera lilipoli, timalemba lamulo la SQL molingana ndi kapangidwe ka chilankhulo ichi, chomwe tidalankhula mwachidule tikamaganizira Njira 1. Kenako dinani batani "Zabwino".



12. Pambuyo pake, dongosolo limangobwerera pawindo lolumikiza buku. Titha kungodina batani "Tsitsimutsani" m'menemo. Pempho limaperekedwa ku database, pambuyo pake database imabweza zotsatira zake pokonzanso ku pepala la Excel, patebulo lomwe tidasinthana kale.

Njira 3: Lumikizani ku SQL Server

Kuphatikiza apo, pogwiritsa ntchito zida za Excel, mutha kulumikizana ndi SQL Server ndikukutumizirani mafunso. Kupanga pempho sikusiyana ndi njira yapita, koma choyambirira, muyenera kukhazikitsa kulumikizana palokha. Tiyeni tiwone momwe angachitire.

1. Timayamba pulogalamu ya Excel ndipo timadutsanso ku tabu "Zambiri". Pambuyo pake, dinani batani "Kuchokera kwina", yomwe imayikidwa pa tepi mu chipangizo chothandizira "Kupeza deta yakunja". Nthawi ino, kuchokera mndandanda wotsika, sankhani "Kuchokera ku SQL Server".



2. Izi zimatsegula zenera cholumikizira seva yosungirako. M'munda "Dzina la seva" sonyezani dzina la seva yomwe tikulumikiza. Gululi gulu Zambiri Za Akaunti muyenera kusankha momwe kulumikizanaku kudzachitikira: kugwiritsa ntchito chitsimikiziro cha Windows kapena kulowa dzina lolowera achinsinsi. Tikhazikitsa kusinthaku malinga ndi lingaliro. Ngati mwasankha njira yachiwiriyo, kuwonjezera pamenepo muyenera kuyika dzina lolowera achinsinsi pazoyenera. Pambuyo poti makonzedwe onse athe, dinani batani "Kenako". Pambuyo pochita izi, kulumikizana ndi seva yomwe ikutchulidwa kumachitika. Njira zina pakupangidwirafunsira kwa database ndikofanana ndi zomwe tidafotokoza kale.

Monga mukuwonera, ku Excel Excel, kufunsa kumatha kukhazikitsidwa pamodzi ndi zida zopangidwira pulogalamu komanso mothandizidwa ndi owonjezera-achipani. Wogwiritsa aliyense akhoza kusankha njira yomwe ili yabwino kwa iye ndipo ndi yoyenera kuthetsa vuto linalake. Ngakhale, zowonjezera za XLTools zowonjezera, pazambiri, zidakali zapamwamba kwambiri kuposa zida zomwe zidamangidwa mu Excel. Choyipa chachikulu cha XLTools ndikuti nthawi yogwiritsidwa ntchito mwaulere imangokhala milungu iwiri yokha.