Contents
A halin yanzu, Microsoft Excel yana da ayyuka kusan ɗari biyar na aikin da ake samu ta taga Wizard Aiki - maballin fx a cikin dabara bar. Wannan saiti ne mai kyau, amma, duk da haka, kusan kowane mai amfani ba dade ko ba dade ya ci karo da yanayin da wannan jerin ba ya ƙunshi aikin da yake buƙata - kawai saboda ba a cikin Excel ba.
Har ya zuwa yanzu, hanya daya tilo don magance wannan matsalar ita ce macro, watau rubuta aikin da aka ayyana mai amfani (UDF = User Defined Function) a cikin Visual Basic, wanda ke buƙatar ƙwarewar shirye-shiryen da ta dace kuma, a wasu lokuta, ba ta da sauƙi ko kaɗan. Koyaya, tare da sabbin sabuntawa na Office 365, yanayin ya canza don mafi kyau - an ƙara aikin “nannade” na musamman zuwa Excel. LAMBDA. Tare da taimakonsa, aikin ƙirƙirar ayyukan ku yanzu an warware shi cikin sauƙi da kyau.
Bari mu kalli ka’idar amfani da shi a misali mai zuwa.
Kamar yadda wataƙila kun sani, Excel yana da ayyuka da yawa na tantance kwanan wata waɗanda ke ba ku damar tantance adadin rana, wata, sati, da shekara don kwanan wata. Amma saboda wasu dalilai babu wani aiki da ke ƙayyade adadin kwata, wanda kuma ake buƙata sau da yawa, daidai? Bari mu gyara wannan shortcoming da haifar da LAMBDA kansa sabon aikin don magance wannan matsala.
Mataki 1. Rubuta dabarar
Bari mu fara da gaskiyar cewa da hannu a cikin hanyar da aka saba za mu rubuta wata dabara a cikin tantanin halitta wanda ke lissafin abin da muke buƙata. A cikin yanayin lambar kwata, ana iya yin haka, misali, kamar haka:
Mataki 2. Kunnawa cikin LAMBDA da gwaji
Yanzu lokaci ya yi da za a yi amfani da sabon aikin LAMBDA kuma ku nannade tsarin mu a ciki. Haɗin aikin shine kamar haka:
=LAMBDA(Mai canzawa1; Mai canzawa2; … Mai canzawaN ; Zancen)
inda aka fara jera sunayen mabanbanta ɗaya ko fiye da haka, kuma hujja ta ƙarshe ko da yaushe wata dabara ce ko ƙididdigan magana mai amfani da su. Mabambantan sunaye kada su yi kama da adiresoshin tantanin halitta kuma kada su ƙunshi ɗigo.
A cikin yanayinmu, za a sami canji ɗaya kawai - kwanan wata da muke ƙididdige lambar kwata. Bari mu kira mabambanta don shi, a ce, d. Sa'an nan kuma kunsa tsarin mu a cikin aiki LAMBDA kuma musanya adireshin asalin tantanin halitta A2 tare da suna mai ƙima, muna samun:
Lura cewa bayan irin wannan canji, tsarin mu (a gaskiya, daidai!) ya fara haifar da kuskure, saboda yanzu ba a canza asalin kwanan wata daga cell A2 zuwa gare shi ba. Don gwaji da amincewa, zaku iya ba da hujja gare shi ta ƙara su bayan aikin LAMBDA cikin baka:
Mataki 3. Ƙirƙiri suna
Yanzu ga sashi mai sauƙi da nishaɗi. Mun bude Mai sarrafa suna tab tsari (Formulas - Name Manager) kuma ƙirƙirar sabon suna tare da maɓallin Create (ƙirƙira). Fito da shigar da suna don aikin mu na gaba (misali, Nomkvartala), kuma a cikin filin link (Magana) a hankali kwafi daga ma'aunin dabara kuma liƙa aikinmu LAMBDA, kawai ba tare da hujja ta ƙarshe ba (A2):
Komai. Bayan danna kan OK Ana iya amfani da aikin da aka ƙirƙira a kowace tantanin halitta akan kowace takardar wannan littafin aikin:
Yi amfani da wasu littattafai
LAMBDA da tsararraki masu ƙarfi
Ayyukan al'ada waɗanda aka ƙirƙira tare da aiki LAMBDA nasarar tallafawa aiki tare da sabbin tsararraki masu ƙarfi da ayyukansu (tace, UNIK, saAn ƙara zuwa Microsoft Excel a cikin 2020.
Bari mu ce muna son ƙirƙirar sabon aikin da aka ayyana mai amfani wanda zai kwatanta lissafin biyu kuma ya dawo da bambanci tsakanin su - waɗannan abubuwan daga jerin farko waɗanda ba a cikin na biyu ba. Aikin rayuwa, ko ba haka ba? A baya can, don wannan sun yi amfani da ko dai ayyuka a la VPR (VLOOKUP), ko PivotTables, ko Query Query queries. Yanzu zaku iya yin da dabara ɗaya:
A cikin harshen Ingilishi zai kasance:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Anan aikin COUNTIF yana ƙidaya adadin abubuwan da suka faru na kowane kashi na lissafin farko a cikin na biyu, sannan aikin tace yana zaɓar waɗanda ba su sami waɗannan abubuwan ba. Ta hanyar kunsa wannan tsari a ciki LAMBDA da ƙirƙirar kewayon mai suna bisa shi tare da suna, misali, NEMAN RABO - za mu sami aiki mai dacewa wanda zai dawo da sakamakon kwatanta lissafin guda biyu a cikin nau'i mai tsauri:
Idan bayanan tushen ba na yau da kullun ba ne, amma tebur na "masu wayo", aikinmu kuma zai iya jurewa ba tare da matsaloli ba:
Wani misali kuma shine raba rubutu a hankali ta hanyar canza shi zuwa XML sannan kuma ana sarrafa shi ta tantanin halitta ta hanyar amfani da aikin FILTER.XML da muka tantance kwanan nan. Domin kada a sake haifar da wannan hadadden dabarar da hannu kowane lokaci, zai zama da sauƙi a nannade shi a cikin LAMBDA kuma ƙirƙirar kewayon ƙarfi dangane da shi, watau sabon aiki mai ƙarfi da dacewa, sanya masa suna, misali, RAZDTEXT:
Hujja ta farko na wannan aikin zai zama tantanin halitta tare da rubutun tushe, kuma na biyu - hali mai rarraba, kuma zai dawo da sakamakon a cikin nau'i mai tsauri a kwance. Lambar aikin za ta kasance kamar haka:
=LAMBDA(t;d; SANARWA (FILTER.XML("
Jerin misalan ba shi da iyaka - a kowane yanayi inda sau da yawa dole ne ku shigar da tsari mai tsawo da wahala, aikin LAMBDA zai sa rayuwa ta fi sauƙi.
Matsakaicin ƙididdiga na haruffa
Duk misalan da suka gabata sun nuna ɗaya kawai, mafi bayyane, gefen aikin LAMBDA - amfani da shi azaman “nannade” don naɗe dogayen dabaru a ciki da sauƙaƙe shigar su. A haƙiƙa, LAMBDA yana da wani, mafi zurfi, gefen da ke juya shi zuwa kusan cikakkiyar yaren shirye-shirye.
Gaskiyar ita ce, muhimmin mahimmancin fasalin ayyukan LAMBDA shine ikon aiwatar da su a ciki maimaitawa - dabaru na lissafi, lokacin da ake aiwatar da lissafin aikin yana kiran kansa. Daga al'ada, yana iya yin sauti mai ban tsoro, amma a cikin shirye-shirye, sake dawowa abu ne na kowa. Ko da a cikin macros a cikin Visual Basic, zaku iya aiwatar da shi, kuma yanzu, kamar yadda kuke gani, ya zo Excel. Bari mu yi ƙoƙari mu fahimci wannan fasaha tare da misali mai amfani.
A ce muna so mu ƙirƙiri aikin da aka ayyana mai amfani wanda zai cire duk haruffan da aka bayar daga rubutun tushe. Amfanin irin wannan aikin, ina tsammanin, ba kwa buƙatar tabbatarwa - zai zama dacewa sosai don share bayanan shigar da zuriyar tare da taimakonsa, daidai?
Koyaya, idan aka kwatanta da na baya, misalan marasa maimaitawa, matsaloli biyu suna jiran mu.
- Sai mun fito da sunan aikinmu kafin mu fara rubuta code dinsa, domin a cikinsa, wannan sunan za a rika kiransa da kansa.
- Shigar da irin wannan recursive aiki a cikin tantanin halitta da kuma gyara shi ta hanyar tantance mahawara a maƙallan bayan LAMBDA (kamar yadda muka yi a baya) ba zai yi aiki ba. Dole ne ku ƙirƙiri aiki nan da nan "daga karce" a ciki Mai sarrafa suna (Name Manager).
Bari mu kira aikin mu, a ce, TSAFTA kuma muna son ya sami mahawara guda biyu - rubutun da za a tsaftace da jerin haruffan da aka cire a matsayin saƙon rubutu:
Bari mu ƙirƙira, kamar yadda muka yi a baya, akan shafin tsari в Manajan suna mai suna kewayon, sanya masa suna CLEAR kuma shiga cikin filin range gini mai zuwa:
=LAMBDA(t;d;IF(d=””;t;CLEAR(MAGANIN (t; HAGU (d);””);MID (d;2;255)))))
Anan m t shine ainihin rubutun da za a share, kuma d shine jerin haruffan da za a goge.
Duk yana aiki kamar haka:
Kashewa 1
Rukunin SUBSTITUTE (t; LEFT(d);””), kamar yadda zaku iya tsammani, yana maye gurbin harafin farko daga harafin hagu daga saitin d da za a goge a cikin rubutun tushen t tare da fankon rubutu, watau yana cire “ A". A matsayin sakamako na tsaka-tsaki, muna samun:
Kudinsa 125 rubles.
Kashewa 2
Sai aikin ya kira kansa kuma a matsayin shigarwa (hujja ta farko) tana karɓar abin da ya rage bayan tsaftacewa a mataki na baya, kuma hujja ta biyu ita ce zaren haruffan da aka cire ba daga farko ba, amma daga hali na biyu, watau "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," ba tare da "A" na farko ba - ana yin wannan ta aikin MID. Kamar yadda yake a baya, aikin yana ɗaukar hali na farko daga hagu na sauran (B) kuma ya maye gurbin shi a cikin rubutun da aka ba shi (Zkz n 125 rubles) tare da kirtani mara kyau - muna samun sakamako mai tsaka-tsaki:
125 ru.
Kashewa 3
Aikin yana sake kiran kansa, yana karɓar azaman hujja ta farko abin da ya rage na rubutun da za a share shi a cikin juzu'i na baya (Bsh zkz n 125 ru.), Kuma a matsayin hujja ta biyu, saitin haruffan da ba a haɗa su ta hanyar wani ƙarin harafi guda ɗaya zuwa ba. hagu, watau “VGDEEGZIKLMNOPRSTUFHTSCHSHSHYYYYUYA.” Ba tare da “B” na farko ba. Sa'an nan kuma ta sake ɗaukar harafin farko daga hagu (B) daga wannan saitin kuma ya cire shi daga rubutun - muna samun:
sh zkz n 125 ru.
Da sauransu - Ina fatan kun sami ra'ayin. Tare da kowane juzu'i, jerin haruffan da za a cire za a yanke su a hagu, kuma za mu nemo da maye gurbin na gaba daga saitin tare da fanko.
Lokacin da duk haruffan suka ƙare, za mu buƙaci fita madauki - wannan rawar ana yin ta ne kawai ta aikin IF (IF), wanda aka nannade zanenmu. Idan babu haruffan da aka bari don gogewa (d=”), to aikin bai kamata ya sake kiran kansa ba, amma yakamata kawai ya dawo da rubutun don sharewa (variable t) a sigarsa ta ƙarshe.
Maimaituwar sel
Hakazalika, zaku iya aiwatar da ƙididdige ƙididdiga na sel a cikin kewayon da aka bayar. A ce muna son ƙirƙirar aikin lambda mai suna JERIN MAJIYA don maye gurbin jumloli na gutsuttsura a cikin rubutun tushe bisa ga lissafin da aka bayar. Sakamakon yakamata yayi kama da haka:
Wadancan. a aikin mu JERIN MAJIYA za a sami dalilai guda uku:
- cell tare da rubutu don aiwatarwa (adireshin tushe)
- tantanin halitta na farko na ginshiƙi mai ƙima don bincika daga bincike
- tantanin halitta na farko na ginshiƙi tare da ƙimar maye gurbin daga dubawa
Ya kamata aikin ya tafi daga sama zuwa kasa a cikin kundin adireshi kuma ya maye gurbin duk zaɓuɓɓukan daga ginshiƙi na hagu Don nemowa zuwa zaɓuɓɓukan da suka dace daga shafi na dama Canje. Kuna iya aiwatar da wannan tare da aikin lambda mai maimaitawa:
Sauƙaƙewa a kowane juzu'i ana aiwatar da shi ta daidaitaccen aikin excel JUYAWA (KASHE), wanda a cikin wannan yanayin yana da gardama guda uku - kewayon asali, motsin layi (1) da motsi shafi (0).
Da kyau, da zaran mun isa ƙarshen kundin adireshin (n = ""), dole ne mu kawo karshen maimaitawa - mu daina kiran kanmu kuma mu nuna abin da ya tara bayan duk masu maye gurbin a cikin madaidaicin rubutun tushe t.
Shi ke nan. Babu macros masu wayo ko tambayoyin Tambayoyin Wutar Wuta - ana warware dukkan aikin ta hanyar aiki ɗaya.
- Yadda ake amfani da sabbin ayyukan tsararru na Excel: FILTER, SORT, UNIC
- Sauya da tsaftace rubutu tare da aikin SUBSTITUTE
- Ƙirƙirar macros da ayyuka masu amfani (UDFs) a cikin VBA