Tambayar SQL a Microsoft Excel

SQL babban yare ne na shirye-shiryen da ake amfani da shi lokacin aiki tare da bayanan bayanai (DB). Kodayake akwai wani takamaiman aikace-aikacen da ake kira Access don ayyukan bayanai a cikin Microsoft Office, Excel kuma yana iya aiki tare da bayanan bayanai ta hanyar yin tambayoyin SQL. Bari mu gano yadda ake samar da irin wannan fatawar ta hanyoyi daban-daban.

Dubi kuma: Yadda za a ƙirƙiri bayanai a cikin Excel

Irƙirar tambayar SQL a cikin Excel

Harshen tambayar SQL ya bambanta da analogues a cikin wannan kusan dukkanin tsarin sarrafa kayan adana bayanai na zamani suna aiki da shi. Saboda haka, ba abin mamaki bane cewa irin wannan babban tebur mai aikin haɓaka tebur kamar Excel, wanda ke da ƙarin ƙarin ayyuka, shima yasan yadda ake aiki da wannan yaren. Masu amfani da SQL ta amfani da Excel na iya tsara bayanai daban-daban na kayan tarihi daban-daban.

Hanyar 1: yi amfani da ƙari

Amma da farko, bari mu bincika zaɓi yayin da zaku iya ƙirƙirar tambarin SQL daga Excel ba amfani da kayan aikin yau da kullun ba, amma amfani da ƙari na ɓangare na uku. Ofaya daga cikin mafi kyawun abubuwan ƙarawa wanda ke yin wannan aikin shine kayan aikin XLTools, wanda, ban da wannan fasalin, yana ba da rundunar sauran ayyukan. Gaskiya ne, ya kamata a lura cewa lokacin kyauta don amfani da kayan aiki shine kwanaki 14 kawai, sannan kuma dole ne ku sayi lasisi.

Zazzage Lara XLTools

1. Bayan kun saukar da fayil ɗin da ke ciki xltools.execi gaba don shigar da shi. Don fara mai sakawa, danna maɓallin linzamin kwamfuta na hagu sau biyu akan fayil ɗin shigarwa. Bayan haka, za a buɗe wata taga wanda za ku buƙaci tabbatar da yarjejeniyarku da yarjejeniyar lasisin don amfanin samfuran Microsoft - Tsarin NET 4. Don yin wannan, kawai danna maɓallin. Na yarda " a kasan taga.



2. Bayan haka, mai sakawa yana saukar da fayilolin da ake buƙata kuma yana fara aiwatar da shigar da su.



3. Daga nan sai taga zai bude wacce a ciki zaku tabbatar da yardar ku dan sanya wannan kara. Don yin wannan, danna maballin Sanya.



4. Sannan tsarin shigarwa na ƙara-cikin kansa zai fara.



5. Bayan an kammala shi, za a buɗe wata taga wacce za a ba da labarin cewa an gama aikin cikin nasara. A cikin taga da aka ƙayyade, danna kan maballin Rufe.



6. An shigar da ƙara kuma a yanzu zaku iya gudanar da fayil ɗin Excel wanda kuke buƙatar tsara tambayar SQL. Tare tare da takardar Excel, taga yana buɗewa don shigar da lambar lasisi XLTools. Idan kuna da lamba, kuna buƙatar shigar da shi a cikin filin da ya dace kuma danna maɓallin "Ok". Idan kana son yin amfani da sigar kyauta na kwanaki 14, to, kawai danna maballin Lasisin gwaji.



7. Lokacin da zabar lasisi na gwaji, wani karamin taga yana buɗewa, inda kana buƙatar tantance sunanka da sunan mahaifi (zaka iya amfani da wani yare) da imel. Bayan haka, danna maɓallin "Fara lokacin gwaji".



8. Bayan haka, za mu koma zuwa ga lasin lasisi. Kamar yadda kake gani, dabi'un da ka shigar an riga an nuna su. Yanzu kawai kuna buƙatar danna maballin "Ok".



9. Bayan kun yi amfani da abubuwan da ke sama, wani sabon shafin zai bayyana a cikin misali - "XLTools". Amma ba mu cikin sauri mu shiga ciki ba. Kafin ƙirƙirar tambaya, muna buƙatar canza teburin jerin abin da zamu iya aiki a cikin teburin da ake kira "smart" kuma mu ba shi suna.
   Don yin wannan, zaɓi zaɓin abin da aka ƙayyade ko kowane bangare daga ciki. Kasancewa a cikin shafin "Gida" danna alamar "Kaya kamar tebur". An sanya shi a kan kintinkiri a cikin akwatin kayan aiki. Salo. Bayan wannan jerin jerin zaɓi na launuka daban-daban suna buɗe. Zaɓi salon da kuke ganin ya zama dole. Zaɓin da aka ƙaddara ba zai tasiri aikin tebur ta kowace hanya ba, saboda haka ka zaɓa zaɓinka ka'idodi kan abubuwan zaɓi na gani.



10. Bayan wannan, ƙaramin taga ya fara. Yana nuna daidaitawar teburin. A matsayinka na mai mulkin, shirin da kansa ya 'dauki cikakkiyar adireshin hanyoyin, koda kun zabi sel guda daya kacal. Amma kawai a yanayin, ba shi da matsala a duba bayanan da ke filin "Nuna wurin data teburin". Hakanan kula da kusancin abu Tebur Shugaban, akwai alamar tambari idan kawunan kalmominku sun kasance da gaske. Saika danna maballin "Ok".



11. Bayan hakan, za a tsara duk kewayon da aka ƙayyade azaman tebur, wanda zai shafi duka kaddarorinsa (alal misali, shimfiɗa shimfiɗa) da nunin gani. Teburin da aka ƙayyade za a ba shi suna. Don gane shi da canza shi yadda ake so, danna kowane bangare na tsarin. Grouparin rukuni na shafuka sun bayyana a kan kintinkiri - "Yin aiki tare da Tables". Matsa zuwa shafin "Mai zane"sanya shi a ciki. A kan kintinkiri a cikin akwatin kayan aiki "Bayanai" a fagen "Sunan tebur" sunan shirin da shirin sanya shi ta atomatik za a nuna.



12. Idan ana so, mai amfani zai iya canza wannan sunan zuwa wani mai karin bayani, kawai ta hanyar shigar da zabin da ake so a filin daga maballin kuma danna maɓallin. Shigar.



13. Bayan haka, teburin ya shirya kuma zaku iya ci gaba kai tsaye ga ƙungiyar buƙatar. Matsa zuwa shafin "XLTools".



14. Bayan sun je kintinkiri a cikin akwatin kayan aiki "Tambayoyin SQL" danna alamar Run SQL.



15. Ana fara aiwatar da aikin binciken SQL na farawa. A cikin yankin hagu, ya kamata ka nuna takardar daftarin aiki da tebur akan bishiyar bayanai wacce za'a kawo buƙatarta.

    A cikin ɓangaren dama na taga, wanda ya fi yawancin sa, shine editan tambaya na SQL kanta. Wajibi ne a rubuta lambar shirin a ciki. Sunayen shafi akan tebur da aka zaɓa a can tuni za'a nuna su ta atomatik. An zaɓi ginshiƙai don aiki ta amfani da umarnin KUDI. Wajibi ne a bar cikin jerin layukan waɗanda kawai kuke son umarnin da aka ƙayyade don aiwatarwa.

    Bayan haka, rubutun umarnin da kake son amfani da abubuwan da aka zaɓa an rubuta su. An hada rukuni ta amfani da masu aiki na musamman. Ga maganganun SQL na asali:

    • AMSA BY - rarrabe dabi'u;
    • Shiga - haɗuwa cikin tebur;
    • GASKIYA A - hada darajar abubuwa;
    • SAURARA - tarin abubuwan daraja;
    • Bambanta - cire kwastomomi.

    Bugu da kari, ana iya amfani da injin don yin abin nema MAX, MIN, Avg, KUDI, Hagu da sauransu

    A cikin ƙananan ɓangaren taga ya kamata ka nuna inda za a nuna sakamakon aiki. Wannan na iya zama sabon takaddar littafin (ta tsohuwa) ko takamaiman yanki akan takardar ta yanzu. A ƙarshen magana, kuna buƙatar matsar da canjin zuwa matsayin da ya dace kuma ƙayyade abubuwan daidaita wannan kewayon.

    Bayan an yi buƙatar kuma an saita saiti mai dacewa, danna maɓallin Gudu a kasan taga. Bayan wannan, shigar da aikin za a yi.

Darasi: Smart Tables in Excel

Hanyar 2: amfani da kayan aikin Excel masu ginanniyar kayan aiki

Hakanan akwai wata hanyar da za a kirkiro wata hanyar SQL a kan zaɓaɓɓen hanyar bayanai ta amfani da kayan aikin ginanniyar Excel.

1. Mun fara shirin Excel. Bayan haka, matsa zuwa shafin "Bayanai".



2. A cikin akwatin kayan aiki "Samun bayanan waje"located a kan kintinkiri, danna kan icon "Daga sauran kafofin". Lissafin ƙarin zaɓuɓɓuka yana buɗe. Zaɓi abu a ciki "Daga mayewar bayanan bayanai".



3. Ya fara Mayen Haɗin Bayanai. A cikin jerin nau'ikan hanyoyin samun bayanai, zaɓi "ODBC DSN". Bayan haka, danna maɓallin "Gaba".



4. Window yana buɗewa Wizards Haɗin Bayaniwanda a ciki kake so ka zaɓi irin tushen. Zaɓi suna "Hanyar bayanai ta MS". Saika danna maballin "Gaba".



5. Smallan ƙaramin kewayawa yana buɗewa, wanda ya kamata ka je wajan maɓallin wurin bayanai a cikin mdb ko tsarin accdb kuma zaɓi fayil ɗin bayanan da ake so. Kewaya tsakanin tafiyarwa mai ma'ana ana yi a filin musamman. Disks. Tsakanin kundin adireshi, ana yin canji a tsakiyar yankin da taga ake kira "Katalogas". Fayiloli a cikin jagorar na yanzu ana nuna su ta cikin ɓangaren hagu na taga idan suna da mdb tsawo ko accdb. Yana cikin wannan yanki cewa kuna buƙatar zaɓi sunan fayil, sannan danna kan maɓallin "Ok".



6. Bayan wannan, an ƙaddamar da taga zaɓi na tebur a cikin ƙayyadaddun bayanan bayanan. A cikin yankin tsakiyar, zaɓi sunan tebur da ake so (idan akwai da yawa), sannan danna kan maɓallin "Gaba".



7. Bayan haka, taga fayil ɗin adana fayil ɗin yana buɗewa. Anan ne ainihin bayanin game da haɗin da muka tsara. A wannan taga, kawai danna maballin Anyi.



8. An ƙaddamar da taga shigo da bayanai na Excel akan takardar aikin Excel. A ciki, zaka iya tantance wacce tsari kake son gabatar da bayanan:
   • Tebur;
   • Rahoton PivotTable;
   • Takaitaccen bayani.

   Zaɓi zaɓin da kuke buƙata. Ana buƙatar ƙaramin ƙananan don nuna inda yakamata a sanya bayanan: akan sabon takaddar ko akan takaddar yanzu. A ƙarshen magana, yana kuma yiwuwa a zaɓi masu tsara wurin. Ta hanyar tsoho, ana sanya bayanai akan takardar ta yanzu. Kasan hagu na hagu na abinda aka shigo da shi yana cikin tantanin A1.

   Bayan an kayyade duk saitin shigowa, danna maɓallin "Ok".



9. Kamar yadda kake gani, tebur daga ma'aunin bayanan ya koma cikin takardar. Sannan mun matsa zuwa shafin "Bayanai" kuma danna maballin Haɗin kai, wanda yake akan tef ɗin a cikin akwatin kayan aiki na wannan sunan.



10. Bayan wannan, an buɗe taga don haɗa littafin. A ciki ne muke ganin sunan bayanan da aka haɗa a baya. Idan akwai tarin bayanan bayanan da yawa da aka hade, sannan sai a zabi wanda ake bukata sannan a zabi shi. Bayan haka, danna maɓallin "Bayanai ..." a gefen dama ta taga.



11. Fuskokin kayan haɗin yana farawa. Mun motsa a ciki zuwa shafin "Ma'anar". A fagen Rubutun .ungiyarwanda yake kasan tushe na yanzu, muna rubuta umarnin SQL daidai da yadda ake amfani da wannan yare, wanda muka yi magana a takaice lokacin da muke la'akari Hanyar 1. Saika danna maballin "Ok".



12. Bayan wannan, tsarin yana komawa ta atomatik zuwa taga haɗin littafin. Zamu iya danna maballin "Ka sake" a ciki. An gabatar da bukatar ne a cikin bayanan, wanda bayan bayanan na dawo da sakamakon aiwatar da shi zuwa takardar Excel, zuwa teburin da muka gabata.

Hanyar 3: Haɗa zuwa SQL Server

Bugu da kari, ta hanyar kayan aikin Excel, zaku iya haɗi zuwa SQL Server kuma ku aika da tambayoyin. Gina buƙata ba ta bambanta da zaɓin da ya gabata ba, amma da farko, kuna buƙatar kafa haɗin da kansa. Bari mu ga yadda ake yi.

1. Mun fara shirin Excel kuma mun wuce zuwa shafin "Bayanai". Bayan haka, danna maɓallin "Daga sauran kafofin", wanda aka sanya a kan tef a cikin toshe kayan aiki "Samun bayanan waje". Wannan lokacin, daga cikin jerin zaɓi, zaɓi zaɓi "Daga SQL Server".



2. Wannan yana buɗe taga don haɗawa da sabar cibiyar yanar gizo. A fagen "Sunan uwar garke" nuna sunan sabar wanda muke haɗawa. A rukunin sigogi Bayanin Asusun kuna buƙatar yanke shawarar yadda haɗin zai kasance: ta amfani da amincin Windows ko ta shigar da sunan mai amfani da kalmar wucewa. Mun sanya sauyawa bisa ga shawarar. Idan kun zaɓi zaɓi na biyu, to ban da ƙari kuma zaku sami shigar da sunan mai amfani da kalmar wucewa a cikin filayen da suka dace. Bayan an gama dukkan saitunan, danna maballin "Gaba". Bayan aiwatar da wannan aikin, haɗin haɗi zuwa sabawar da aka saba. Stepsarin matakai don tsara abin tambaya zuwa ga bayanai sun yi kama da waɗanda muka bayyana a cikin hanyar da ta gabata.

Kamar yadda kake gani, a cikin Excel Excel, za a iya tsara tambaya duka tare da ginannun kayan aikin shirin kuma tare da taimakon partyangare na uku. Kowane mai amfani zai iya zaɓar zaɓi wanda ya fi dacewa da shi kuma ya fi dacewa don warware takamaiman aiki. Kodayake, fasalulluka na Larin XLTools, a gabaɗaya, har yanzu sun ɗan ɗan ci gaba fiye da kayan aikin-ginancin Excel. Babban hasara na XLTools shine cewa ajalin amfani da ƙari zai iyakance ne ga makonni biyu kawai.