Momwe mungatumizire chithunzi ndi imelo

SQL ndichinenero chodziwika bwino chomwe chimagwiritsidwa ntchito pogwira ntchito ndi zolemba (DB). Ngakhale pali ntchito yapadera yochitira masitepe ku Microsoft Office suite - Access, koma Excel ingagwiritsenso ntchito ndi database, kupanga SQL mafunso. Tiyeni tione momwe tingakhalire pempholi m'njira zosiyanasiyana.

Onaninso: Kodi mungapange bwanji deta ku Excel

Kupanga funso la SQL ku Excel

SQL yankho lachilankhulo likusiyana ndi zofanana ndi momwe pafupifupi makono onse amakono ogwiritsira ntchito mabungwe ogwirira ntchito akugwira nawo ntchito. Choncho, sizosadabwitsa kuti pulosesa yotereyi monga Excel, yomwe ili ndi ntchito zina zambiri, ingagwirenso ntchito ndi chinenero ichi. Ogwiritsira ntchito SQL pogwiritsa ntchito Excel akhoza kupanga deta zosiyanasiyana zosiyana siyana.

Njira 1: Gwiritsani ntchito Zoonjezera

Koma choyamba, tiyeni tikambirane zomwe mungachite pamene mutha kupanga SQL funso kuchokera ku Excel popanda kugwiritsa ntchito chida, koma kugwiritsa ntchito wina wowonjezera. Chimodzi mwa zopindulitsa zabwino zomwe zikugwira ntchitoyi ndiwotchi ya XLTools, yomwe, kuphatikizapo mbali iyi, imapereka ntchito zina zambiri. Komabe, ziyenera kukumbukira kuti nthawi yaulere yogwiritsa ntchito chidacho ndi masiku 14 okha, ndiyeno muyenera kugula laisensi.

Koperani XLTools Add-on

1. Mutatha kulandila fayilo yowonjezera xltools.exeayenera kupitiriza ndi kukhazikitsa kwake. Kuti muthe kuyimitsa, dinani kawiri pa batani lamanzere pa fayilo yowonjezera. Pambuyo pake, mawindo adzayambanso kumene muyenera kuonetsetsa mgwirizano wanu ndi mgwirizano wa chilolezo chogwiritsira ntchito mankhwala a Microsoft - NET Framework 4. Kuti muchite izi, dinani pa batani "Landirani" pansi pazenera.



2. Pambuyo pake, osungira amatsitsa mafayilo oyenerera ndikuyamba njira yowakhazikitsa.



3. Kenaka, zenera zikutsegulira kumene muyenera kutsimikizira kuvomereza kwanu kukhazikitsa izi zowonjezera. Kuti muchite izi, dinani pa batani. "Sakani".



4. Kenaka imayambitsa ndondomeko yowunikira mwachindunji yowonjezeramo.



5. Pambuyo pomalizidwa, zenera lidzatsegulidwa kumene lidzanenedwa kuti kukonza kumeneku kunatha. Muwindo lofotokozedwa, dinani pa batani "Yandikirani".



6. Zowonjezeramo zaikidwa ndipo tsopano mutha kuyendetsa fayilo ya Excel imene mukufunika kukonza SQL. Pamodzi ndi pepala la Excel, zenera likuyamba kulowa m'khodi layisensi ya XLTools. Ngati muli ndi kachidindo, muyenera kulowa mu malo oyenera ndipo dinani pa batani "Chabwino". Ngati mukufuna kugwiritsa ntchito mawonekedwe aulere kwa masiku 14, ndiye kuti mukungoyankha pa batani. "License Yoyesa".



7. Mukasankha chilolezo choyesera, vindo lina laling'ono limatsegula pamene mukufunikira kufotokoza dzina lanu loyamba ndi lachilendo (mungagwiritse ntchito pseudonym) ndi imelo. Pambuyo pake, dinani pa batani "Yambani Panthawi Yoyesera".



8. Kenako timabwerera kuwindo la license. Monga momwe mukuonera, malingaliro omwe mwalowamo ayamba kale. Tsopano mukungofuna kukanikiza batani. "Chabwino".



9. Mukamaliza kuchita izi, tabu yatsopano idzawonekera mukhosi yanu ya Excel - "XLTools". Koma osati mofulumira kuti mupite mmenemo. Musanayambe funso, muyenera kutembenuza tebulo, yomwe tidzakagwiritsire ntchito, kupita ku tebulo yomwe imatchedwa "yochenjera" ndikuipatsa dzina.
   Kuti muchite izi, sankhani ndondomeko yeniyeni kapena zinthu zake zonse. Kukhala mu tab "Kunyumba" dinani pazithunzi "Pangani monga tebulo". Imaikidwa pa tepiyi mu zida za zipangizo. "Masitala". Pambuyo pake, mndandanda wa mitundu yosiyanasiyana imatsegulidwa. Sankhani sewero limene mukuwona kuti likuyenera. Chisankho ichi sichidzakhudza momwe ntchitoyi ikugwiritsidwira ntchito, choncho tsatirani chisankho chanu pokhapokha pazomwe mumawonetsera maonekedwe.



10. Pambuyo pake, mawindo ochepa amayamba. Ikuwonetseratu makonzedwe a gome. Monga lamulo, pulogalamuyo yokha "imatenga" maadiresi athunthu, ngakhale mutasankha selo imodzi yokha mmenemo. Koma ngati sangasokoneze ndi kufufuza zomwe zili m'munda "Tchulani malo a deta ya deta". Muyeneranso kumvetsera za chinthu "Mndandanda ndi mutu", panali chongerezi, ngati mutu wa gulu lanu ulipo. Kenaka dinani pa batani "Chabwino".



11. Pambuyo pake, zonsezi zidzakonzedwa ngati tebulo, zomwe zidzakhudza zonsezi (mwachitsanzo, kutambasula) ndi maonekedwe. Gome lolongosoledwa lidzatchulidwa. Kuti tizindikire ndikusintha pazomwe tikufuna, timangodutsa pazinthu zilizonse. Gulu lina la ma tabo likuwonekera pa nsalu - "Kugwira ntchito ndi matebulo". Pitani ku tabu "Wopanga"anaikidwa mmenemo. Pa tepiyi mu chida cha zipangizo "Zolemba" kumunda "Dzina lamasamba" dzina la mndandanda, umene pulogalamuyo inapatsidwa mwadzidzidzi, idzasonyezedwa.



12. Ngati mukufuna, wogwiritsa ntchito akhoza kusintha dzinali kuti adziwe zambiri mwa kungowonjezera njira yomwe mukufuna kuimanga kuchokera ku makiyi ndi kukanikiza fungulo Lowani.



13. Pambuyo pake, tebulo ili wokonzeka ndipo mukhoza kupita ku bungwe la pempholi. Pitani ku tabu "XLTools".



14. Pambuyo pa kusintha kwa tepiyi muzitsulo "SQL mafunso" dinani pazithunzi Thamangani SQL.



15. SQL yowunikira mafunso yowonekera ikuyamba. Kumanzere kwake, tchulani pepala la chikalata ndi tebulo pa mtengo wa deta limene funso lidzapangidwe.

    Pazenera pazenera, zomwe zimakhala zambiri, ndi SQL query editor yokha. M'menemo muyenera kulemba makalata. Mayina a mndandanda wa tebulo losankhidwa kumeneko adzawonetsedwa kale. Kusankhidwa kwa ndondomeko zokonzekera kwachitika ndi lamulo SANKHANI. Muyenera kuchoka mndandanda mndandanda womwewo womwe mukufuna kuti lamulo loti lichitike.

    Kenaka, lembani mawu a lamulo limene mukufuna kugwiritsa ntchito ku zinthu zosankhidwa. Malamulo amalembedwa pogwiritsa ntchito ochita ntchito yapadera. Nazi mfundo zofunika za SQL:

    • ZOKHALA - kusankha mfundo;
    • ONANI - kujowina matebulo;
    • GROUP BY - gulu la makhalidwe;
    • SUM - kufotokoza kwa makhalidwe;
    • Kusiyanitsa - chotsani zowerengera.

    Kuwonjezera apo, pomangapo funsolo, mungagwiritse ntchito ogwira ntchito MAX, MIN, Av, COUNT, MALAMULO ndi ena

    Pansi pazenera, muyenera kufotokoza ndendende kumene zotsatira zotsatila zidzawonetsedwa. Izi zikhoza kukhala pepala latsopano la buku (mwachisawawa) kapena mtundu wina pa tsamba lomwe lili pano. Pachifukwa chotsatirachi, muyenera kuyambiranso kusinthana ku malo oyenerera ndikuwonetseratu zochitikazo.

    Pambuyo pempholi ndipangidwe, dinani pakani. Thamangani pansi pazenera. Pambuyo pake, ntchito yolowera idzachitidwa.

PHUNZIRO: Magome Opusa mu Excel

Njira 2: Gwiritsani Ntchito Zida Zowongedwa

Palinso njira yothetsera funso la SQL pazomwe mwasankhidwa pogwiritsa ntchito zida zowonjezera za Excel.

1. Kuthamanga pulogalamu ya Excel. Pambuyo pake kusamukira ku tabu "Deta".



2. M'kati mwa zipangizo "Kutenga Deta Zakunja"yomwe ili pa tepi, dinani pazithunzi "Kuchokera kuzinthu zina". Mndandanda wa zina zomwe mungachite. Sankhani chinthu mmenemo "Kuchokera ku Data Connection Wizard".



3. Iyamba Wothandizira Dongosolo la Deta. Pa mndandanda wa mitundu yopezera deta, sankhani "ODBC DSN". Pambuyo pake dinani pa batani "Kenako".



4. Window ikutsegula Zida Zogwirizanitsa Deta, momwe muyenera kusankha mtundu wa magwero. Sankhani dzina "MS Access Database". Kenaka dinani pa batani. "Kenako".



5. Fayilo laling'ono loyendayenda likutsegula momwe muyenera kupita ku malo a malo a deta yanu mu mdb kapena kuika maonekedwe ndikusankha fayilo yofunika. Kuyenda pakati pa zoyendetsa bwino kumagwira ntchito yapadera. "Disks". Pakati pa zojambulajambula, kusintha kumapangidwa pakati pawindo lotchedwa "Makanema". Kumanzere kumanzere kwawindo, maofesi omwe ali m'ndandanda yamakono akuwonetsedwa ngati ali ndi extension or mdd. M'dera lino muyenera kusankha dzina la fayilo, kenako dinani pa batani "Chabwino".



6. Pambuyo pake, mawindo akusankha tebulo m'data lachinsinsi adayambitsidwa. Pakatikatikati, sankhani dzina la tebulo lofunidwa (ngati pali angapo), ndiyeno dinani batani "Kenako".



7. Pambuyo pake, kusunga fayilo yowonjezera deta yowatsegula. Pano pali mfundo zofunika zogwirizanitsa zomwe takonza. Muwindo ili, dinani pa batani. "Wachita".



8. Pa pepala la Excel, fayilo yowulutsira deta imayambika. N'zotheka kusonyeza mtundu umene mukufuna kuti deta iwonedwe:
   • Tchati;
   • Pivot Table Report;
   • Tchati chachidule.

   Sankhani zomwe mukufuna. Pansi pansi muyenera kufotokoza ndendende kumene mungaike deta: pa pepala latsopano kapena pa pepala lomwe liripo. Pachifukwa chotsatirachi, ndizotheka kusankha malo omwe ali. Mwachinsinsi, deta imayikidwa pa pepala laposachedwapa. Kangodya kumanzere kwa chinthu chololedwa chimayikidwa mu selo. A1.

   Pambuyo pokonza zonse zoitanitsa, tambani pa batani "Chabwino".



9. Monga momwe mukuonera, tebulo kuchokera ku databata imasunthira ku pepala. Kenaka pita ku tabu "Deta" ndipo dinani pa batani "Connections"yomwe imayikidwa pa tepiyi mu zida zogwiritsa ntchito dzina lomwelo.



10. Pambuyo pake, kugwirizana kwa bukuli kunayambika. M'menemo tikuwona dzina lasanjanichi yolumikizidwa kale. Ngati pali zolemba zambiri, sankhani zomwe mukufuna ndikuzisankha. Pambuyo pake dinani pa batani "Zolemba ..." kumanja kwawindo.



11. Kugwirizana kwazenera zenera kuyambira. Pitani ku tab "Tanthauzo". Kumunda "Lamulo lolemba", pansi pawindo lamakono, lembani lamulo la SQL molingana ndi chilankhulidwe cha chinenerocho, chomwe tachikamba mwachidule tikamaganizira Njira 1. Kenaka dinani pa batani "Chabwino".



12. Pambuyo pake, pokhapokha kubwereranso kuzenera zowonjezera. Tikhoza kungodinkhani pa batani "Tsitsirani" mmenemo. Mndandanda wazomwewu umapezeka ndi funso, pambuyo pake deta ikubwezeretsanso zotsatira za kubwezeretsanso kubwerera ku Excel pepala, ku tebulo lomwe latitumizidwa ndi ife.

Njira 3: Gwiritsani ntchito SQL Server

Kuwonjezera apo, kupyolera mu zida za Excel, n'zotheka kulumikizana ndi SQL Server ndi kutumiza zopempha. Kumanga funso sikusiyana ndi njira yapitayi, koma choyamba, muyenera kukhazikitsa mgwirizano wokha. Tiyeni tiwone momwe tingachitire izo.

1. Thamani Excel ndikupita ku tabu "Deta". Pambuyo pake dinani pa batani "Kuchokera kuzinthu zina"yomwe imayikidwa pa tepi mu zida za zipangizo "Kutenga Deta Zakunja". Nthawi ino, kuchokera pandandanda yomwe ikuwonekera, sankhani kusankha "Kuchokera ku SQL Server".



2. Kugwirizana kwa seva ya deta lamanja kutsegulidwa. Kumunda "Dzina la Seva" tchulani dzina la seva imene tikugwirizanitsa. Mu gulu la magawo "Zambiri za Akaunti" muyenera kusankha momwe kulumikizako kudzachitikire: kugwiritsa ntchito mawindo ovomerezeka kapena polemba dzina ndi dzina lanu. Timavomereza kusuta mogwirizana ndi chisankhocho. Ngati mwasankha njira yachiwiri, ndiye kuwonjezera pa masanjidwe oyenera muyenera kulowa dzina ndi dzina lanu. Pambuyo pokonza zonse zomwe zachitika, dinani pa batani. "Kenako". Pambuyo pochita izi, kugwirizana kwa seva yeniyeni imapezeka. Zochita zina zokonza funso lachinsinsi zili zofanana ndi zomwe zafotokozedwa kale.

Monga mukuonera, mu Excel, SQL funso lingakonzedwe monga zowonjezera zida za pulojekiti, ndipo mothandizidwa ndi owonjezera chipani. Wosuta aliyense akhoza kusankha njira yomwe ili yabwino kwambiri kwa iye ndipo ali woyenera kuthetsa ntchito inayake. Ngakhale, zokhoza za XLTools zowonjezeramo, zowonjezera, zakhala zikupambana kwambiri kuposa zida zowonjezera zowonjezera. Chosavuta chachikulu cha XLTools ndi chakuti nthawi yaufulu yogwiritsira ntchito yowonjezerapo ili yochepa kwa milungu iwiri yokha ya kalendala.