Contents
Ɗaya daga cikin mafi yawan kallon bidiyo akan tashar YouTube shine bidiyo game da Fill Fill a Microsoft Excel. Mahimmancin wannan kayan aiki shine idan kuna buƙatar canza bayanan tushen ku ko ta yaya, to kawai kuna buƙatar fara buga sakamakon da kuke son samu a cikin ginshiƙi kusa. Bayan sel da yawa da aka buga da hannu (yawanci 2-3 sun isa), Excel zai “fahimci” dabaru na canje-canjen da kuke buƙata kuma ya ci gaba da abin da kuka buga ta atomatik, yana kammala duk ayyukan da kuke so:
A quintessence na yadda ya dace. Maɓallin sihirin "yi daidai" da muke ƙauna sosai, daidai?
A gaskiya ma, akwai analog na irin wannan kayan aiki a cikin Query Query - a can ake kira shi Shafi daga misalai (Shafi daga Misalai). A zahiri, wannan ɗan ƙaramin hankali ne na wucin gadi wanda aka gina shi cikin Query Query wanda zai iya koyo da sauri daga bayanan ku sannan ya canza shi. Bari mu ɗan duba iyawar sa a cikin yanayi da yawa masu amfani domin mu fahimci inda zai yi mana amfani a cikin ayyuka na gaske.
Misali 1. Manne/yanke rubutu
Bari mu ce muna da irin wannan tebur "mai wayo" a cikin Excel tare da bayanai akan ma'aikata:
Load da shi cikin Tambayoyin Wuta a daidaitaccen hanya - tare da maɓallin Daga Tebura/Range tab data (Bayanai - Daga Tebura/Range).
A ce muna buƙatar ƙara ginshiƙi tare da sunayen ƙarshe da baƙaƙe ga kowane ma'aikaci (Ivanov SV na ma'aikaci na farko, da dai sauransu). Don magance wannan matsala, zaka iya amfani da ɗayan hanyoyi biyu:
- danna dama akan taken shafi tare da bayanan tushen kuma zaɓi umarnin Ƙara shafi daga misalai (Ƙara shafi daga misalai);
- zaɓi ɗaya ko fiye ginshiƙai tare da bayanai kuma akan shafin Ƙara ginshiƙi zabi tawagar Shafi daga misalai. Anan, a cikin jerin abubuwan da aka saukar, zaku iya tantance ko duka ko ginshiƙan da aka zaɓa kawai suna buƙatar tantancewa.
Sa'an nan duk abin da yake mai sauƙi - a cikin ginshiƙi wanda ya bayyana a hannun dama, za mu fara shigar da misalai na sakamakon da ake so, kuma basirar wucin gadi da aka gina a cikin Ƙarfin Ƙarfafa yana ƙoƙarin fahimtar tunanin mu na canji kuma ya ci gaba da kansa:
Af, za ka iya shigar da daidai zažužžukan a cikin kowane sel na wannan ginshiƙi, watau ba dole ba daga sama zuwa sama da kuma a jere. Hakanan, zaka iya ƙara ko cire ginshiƙai cikin sauƙi daga bincike daga baya ta amfani da akwatunan rajistan shiga a cikin mashaya take.
Kula da dabarar a saman taga - wannan shine abin da mai wayo Power Query ke haifar don samun sakamakon da muke buƙata. Wannan, ta hanyar, shine babban bambanci tsakanin wannan kayan aiki da Cika kai tsaye a cikin Excel. Cika kai tsaye yana aiki kamar “akwatin baƙar fata” - ba sa nuna mana dabaru na canje-canje, amma kawai suna ba da sakamakon da aka shirya kuma muna ɗaukar su a banza. Anan komai a bayyane yake kuma koyaushe zaku iya fahimtar ainihin abin da ke faruwa tare da bayanan.
Idan kun ga cewa Tambayoyin Wutar "ta kama ra'ayin", to, zaku iya danna maɓallin a amince OK ko gajeriyar hanyar keyboard Ctrl+Shigar – Za a ƙirƙiri ginshiƙi na al'ada tare da dabarar da Query Query ya ƙirƙira. Af, daga baya za'a iya gyara shi cikin sauƙi azaman ginshiƙin ƙirƙira da hannu (tare da umarnin Ƙara ginshiƙi - Shagon Al'ada) ta danna gunkin gear da ke hannun dama na sunan mataki:
Misali na 2: Hali kamar a cikin jimloli
Idan ka danna-dama akan taken shafi tare da rubutu kuma zaɓi umarnin Sake Kama (canza), sannan zaku iya ganin umarni guda uku da ke da alhakin canza rajista:
Dace da sanyi, amma a cikin wannan jeri, alal misali, ni da kaina koyaushe na rasa ƙarin zaɓi guda ɗaya - yanayin kamar a cikin jimloli, lokacin da babban girman (babban birni) ba ya zama harafin farko a kowace kalma, amma harafin farko a cikin tantanin halitta, kuma sauran rubutun lokacin da aka nuna Wannan a cikin ƙananan haruffa (kananan).
Wannan fasalin da ya ɓace yana da sauƙin aiwatarwa tare da basirar wucin gadi ginshiƙai daga misalai - kawai shigar da zaɓuɓɓuka biyu don Neman Ƙarfin don ci gaba cikin ruhi ɗaya:
A matsayin dabara anan, Query Query yana amfani da tarin ayyuka Rubutu.Upper и Rubutu. Ƙasashe, canza rubutu zuwa babba da ƙarami, bi da bi, da ayyuka Rubutu.Fara и Rubutu.Mid - kwatankwacin ayyukan Excel LEFT da PSTR, masu iya cire kirtani daga rubutu daga hagu da kuma daga tsakiya.
Misali 3. Karɓar kalmomi
Wani lokaci, lokacin sarrafa bayanan da aka karɓa, ya zama dole don sake tsara kalmomi a cikin sel a cikin jerin da aka bayar. Tabbas, zaku iya raba ginshiƙi zuwa ginshiƙan kalmomi daban ta mai raba sannan ku manne shi a cikin ƙayyadadden tsari (kada ku manta da ƙara sarari), amma tare da taimakon kayan aiki. Shafi daga misalai komai zai yi sauki:
Misali 4: Lambobi kawai
Wani aiki mai mahimmanci shine cire lambobi (lambobi) kawai daga abinda ke cikin tantanin halitta. Kamar a baya, bayan loda bayanai a cikin Tambayoyin Wutar Lantarki, je zuwa shafin Ƙara ginshiƙi - Shagon daga misalai sannan a cika sel biyu da hannu domin shirin ya fahimci ainihin abin da muke so mu samu:
Wasan bingo!
Bugu da ƙari, yana da daraja duba saman taga don tabbatar da cewa Tambaya ta samar da dabarar daidai - a wannan yanayin yana ƙunshe da aiki. Rubutu. Zaɓi, wanda, kamar yadda zaku iya tsammani, yana fitar da haruffan da aka bayar daga rubutun tushen bisa ga jeri. Daga baya, wannan jeri, ba shakka, ana iya gyara shi cikin sauƙi a mashigin dabara idan ya cancanta.
Misali 5: Rubutu kawai
Hakazalika da misalin da ya gabata, zaku iya cirewa da akasin haka - kawai rubutu, share duk lambobi, alamomin rubutu, da sauransu.
A wannan yanayin, ana amfani da aikin da ya riga ya sabawa ma'ana - Text.Remove, wanda ke cire haruffa daga asalin kirtani bisa ga jerin da aka ba.
Misali 6: Ciro bayanai daga porridge na haruffa
Tambayoyin wutar lantarki kuma na iya taimakawa a cikin lokuta masu wahala, lokacin da kuke buƙatar cire bayanai masu amfani daga porridge alphanumeric a cikin tantanin halitta, alal misali, sami lambar asusun daga bayanin dalilin biyan kuɗi akan bayanin banki:
Lura cewa dabarar juyar da Query ɗin da aka samar na iya zama mai rikitarwa:
Don sauƙin karantawa da fahimta, ana iya jujjuya shi zuwa tsari mai hankali sosai ta amfani da sabis na kan layi kyauta. Mai tsara Query Query:
Abu mai amfani sosai - girmamawa ga masu halitta!
Misali 7: Canza kwanan wata
kayan aiki Shafi daga misalai ana iya amfani da ginshiƙan kwanan wata ko kwanan wata kuma. Lokacin da ka shigar da lambobi na farko na kwanan wata, Tambayar Wuta zai taimaka nuna jerin duk zaɓuɓɓukan juyawa masu yuwuwa:
Don haka zaka iya canza ainihin kwanan watan zuwa kowane tsari mai ban mamaki, kamar "ranar-wata-shekara":
Misali 8: Rarraba
Idan muka yi amfani da kayan aiki Shafi daga misalai zuwa ginshiƙi mai bayanan lamba, yana aiki daban. A ce muna da sakamakon gwajin ma'aikata da aka ɗora a cikin Tambayoyin Wuta (maki-daki a cikin kewayon 0-100) kuma muna amfani da matakan digiri masu zuwa:
- Masters - waɗanda suka ci fiye da 90
- Masana - sun ci daga 70 zuwa 90
- Masu amfani - daga 30 zuwa 70
- Masu farawa – waɗanda suka ci ƙasa da 30
Idan muka ƙara ginshiƙi daga misalan zuwa jeri kuma mu fara tsara waɗannan gradations da hannu, to nan ba da jimawa ba Power Query zai ɗauki ra'ayinmu kuma ya ƙara ginshiƙi tare da dabara, inda masu aiki suka shiga cikin juna. if za a aiwatar da dabaru, kama da abin da muke buƙata:
Bugu da ƙari, ba za ku iya danna halin da ake ciki ba har zuwa ƙarshe, amma danna kan OK sa'an nan kuma gyara ƙimar ƙofa a cikin dabarar - yana da sauri ta wannan hanyar:
karshe
Lallai kayan aiki Shafi daga misalai ba "kwayar sihiri" ba ce kuma, ba dade ko ba dade, za a sami yanayi mara kyau ko kuma musamman waɗanda aka yi watsi da su na "gona tari" a cikin bayanan, lokacin da Query Query zai gaza kuma ba zai iya aiwatar da abin da muke so ba. daidai gare mu. Duk da haka, a matsayin kayan aiki na kayan aiki, yana da kyau sosai. Ƙari ga haka, ta hanyar nazarin dabarun da ya ƙirƙira, za ku iya faɗaɗa ilimin ku game da ayyukan Harshen M, wanda koyaushe zai kasance da amfani a nan gaba.
- Fassarar Rubutu tare da Kalmomi na yau da kullun (RegExp) a cikin Query Query
- Binciken rubutu mai ban mamaki a cikin Query Query
- Flash Fill a cikin Microsoft Excel