Hevitra ato Anatiny
Amin'izao fotoana izao, ny Microsoft Excel dia manana fiasa efa ho dimanjato azo alaina amin'ny alàlan'ny fikandrana Function Wizard - bokotra fx ao amin'ny formula bar. Ity dia andiany tena mendrika, saingy, na izany aza, saika ny mpampiasa rehetra na ho ela na ho haingana dia miatrika toe-javatra izay tsy ahitana ity lisitra ity ny fiasa ilainy - satria tsy ao amin'ny Excel fotsiny.
Mandraka ankehitriny, ny hany fomba hamahana ity olana ity dia ny macros, izany hoe ny fanoratana ny fiasanao manokana (UDF = User Defined Function) amin'ny Visual Basic, izay mitaky fahaiza-manao fandaharana sahaza ary, indraindray, tsy mora mihitsy. Na izany aza, miaraka amin'ny fanavaozana farany amin'ny Office 365, niova ho tsara kokoa ny toe-javatra - nisy fiasa manokana "wrapper" nampiana tao amin'ny Excel LAMBDA. Miaraka amin'ny fanampiany, voavaha mora sy tsara tarehy izao ny asa famoronana ny asanao manokana.
Andeha hojerentsika ny fitsipiky ny fampiasana azy amin’ity ohatra manaraka ity.
Araka ny mety ho fantatrao, Excel dia manana fiasa fanapariahana daty maromaro izay ahafahanao mamaritra ny isan'ny andro, volana, herinandro ary taona ho an'ny daty iray. Saingy noho ny antony tsy misy fiasa izay mamaritra ny isan'ny ampahefatra, izay ilaina koa matetika, sa tsy izany? Andao amboary ity lesoka ity ary mamorona miaraka LAMBDA asa vaovao manokana hamahana ity olana ity.
Dingana 1. Soraty ny formula
Andeha isika hanomboka amin'ny zava-misy fa amin'ny fomba mahazatra dia hanoratra raikipohy ao anaty sela misy takelaka izay manisa izay ilaintsika. Raha ny laharana fahefatra dia azo atao izany, ohatra, toy izao:
Dingana 2. Famonosana ao amin'ny LAMBDA ary fitsapana
Fotoana izao hampiharana ny fiasa LAMBDA vaovao ary hamehezana ny raikipohy ao anatiny. Ny syntax function dia toy izao manaraka izao:
=LAMBDA(Variable1; Variable2; ... VariableN ; maneho Hevitra)
izay misy ny anaran'ny variables iray na maromaro voatanisa voalohany, ary ny tohan-kevitra farany dia raikipohy na fomba fiteny kajy mampiasa azy ireo foana. Ny anarana miovaova dia tsy tokony hitovy amin'ny adiresin'ny sela ary tsy tokony hisy teboka.
Amin'ny tranga misy antsika dia tsy hisy afa-tsy fari-piainana iray ihany - ny daty hanaovana kajy ny isan'ny ampahefany. Andeha hiantso ny fari-piainana ho azy, lazao hoe, d. Avy eo dia mametaka ny formulay amin'ny asa iray LAMBDA ary manolo ny adiresin'ny sela voalohany A2 miaraka amin'ny anarana miovaova noforonina, dia mahazo:
Mariho fa taorian'ny fiovana toy izany, ny formulay (raha ny marina, marina!) dia nanomboka namokatra fahadisoana, satria ankehitriny ny daty tany am-boalohany avy amin'ny sela A2 dia tsy nafindra ho azy. Ho an'ny fitsapana sy fahatokisana dia azonao atao ny mampita hevitra aminy amin'ny fampidirana azy ireo aorian'ny asa LAMBDA ao anaty fononteny:
Dingana 3. Mamorona anarana
Ankehitriny ho an'ny ampahany mora sy mahafinaritra. Misokatra izahay Name Manager vakizoro raikipohy (Formulas - Name Manager) ary mamorona anarana vaovao miaraka amin'ny bokotra mamorona (Mamorona). Mivoaha ary asio anarana ho an'ny asa ho avy (ohatra, Nomkvartala), ary any an-tsaha Link (Reference) Adikao tsara avy ao amin'ny bara formula ary apetaho ny fiasantsika LAMBDA, raha tsy misy ny hevitra farany (A2):
Ny zava-drehetra. Rehefa avy manindry OK Ny asa noforonina dia azo ampiasaina amin'ny sela rehetra amin'ny takelaka rehetra amin'ity boky ity:
Ampiasao amin'ny boky hafa
LAMBDA sy dynamic arrays
Fampiasa manokana noforonina miaraka amin'ny fiasa iray LAMBDA manohana amim-pahombiazana ny asa miaraka amin'ireo array dynamique vaovao sy ny asany (FILTER, UNIK, Grade) nampidirina tao amin'ny Microsoft Excel tamin'ny 2020.
Andeha atao hoe te hamorona fiasa vaovao voafaritry ny mpampiasa izay hampitaha lisitra roa ary hamerina ny fahasamihafana misy eo amin'izy ireo - ireo singa avy amin'ny lisitra voalohany izay tsy ao amin'ny faharoa. Asan'ny fiainana, sa tsy izany? Teo aloha, ho an'izany dia nampiasa na functions a la VPR (VLOOKUP), na PivotTables, na fanontaniana Power Query. Afaka manao amin'ny formula iray ianao izao:
Amin'ny dikan-teny anglisy dia ho:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Eto ny asa COUNTIF manisa ny isan'ny fisehoan'ny singa tsirairay amin'ny lisitra voalohany amin'ny faharoa, ary avy eo ny fiasa FILTER tsy mifidy afa-tsy ireo izay tsy nanana ireo fisehoan-javatra ireo. Amin'ny famenoana ity rafitra ity LAMBDA ary mamorona sehatra voatonona mifototra amin'izany miaraka amin'ny anarana, ohatra, FIZARANA FITADY - Hahazo fiasa mety isika izay mamerina ny vokatry ny fampitahana lisitra roa amin'ny endrika dynamique:
Raha tsy mahazatra ny angon-drakitra loharano, fa tabilao "smart", ny asantsika dia hiatrika tsy misy olana koa:
Ohatra iray hafa ny manasaraka lahatsoratra amin'ny fomba mavitrika amin'ny alàlan'ny famadihana azy ho XML ary avy eo mametaka azy amin'ny sela isaky ny sela amin'ny fampiasana ny fiasa FILTER.XML izay nodinihinay vao haingana. Mba tsy hamerenana amin'ny tanana isak'izay io raikipohy be pitsiny io, dia ho mora kokoa ny famonosana azy amin'ny LAMBDA ary hamorona sehatra mavitrika mifototra amin'izany, izany hoe fiasa vaovao mirindra sy mety, manome anarana azy, ohatra, RAZDTEXT:
Ny tohan-kevitra voalohany amin'ity fiasa ity dia ny sela misy ny lahatsoratra loharano, ary ny faharoa - ny toetran'ny mpizara, ary hamerina ny vokatra amin'ny endrika laharan-tariby marindrano. Ny code function dia ho toy izao manaraka izao:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Ny lisitr'ireo ohatra dia tsy misy fiafarana - amin'ny toe-javatra rehetra izay tsy maintsy idiranao matetika amin'ny endrika lava sy manahirana, ny fiasa LAMBDA dia hanamora ny fiainana.
Fanisana anarana miverimberina
Ny ohatra rehetra teo aloha dia tsy nampiseho afa-tsy iray, ny tena miharihary indrindra, amin'ny fiasan'ny LAMBDA - ny fampiasana azy ho toy ny "wrapper" amin'ny famenoana ny formulas lava ao anatiny sy ny fanatsorana ny fampidirana azy ireo. Raha ny marina, ny LAMBDA dia manana lafiny iray hafa, lalina kokoa, izay mamadika azy ho fiteny fandaharana saika feno.
Ny zava-misy dia ny singa iray manan-danja amin'ny fiasa LAMBDA dia ny fahafahana mampihatra azy ireo recursion – lojikan'ny kajikajy, rehefa ao anatin'ny dingan'ny kajy dia miantso ny tenany ny asa. Avy amin'ny fahazarana dia mety ho mampatahotra izany, fa amin'ny fandaharana dia zavatra mahazatra ny recursion. Na dia amin'ny macro ao amin'ny Visual Basic aza dia azonao atao ny mampihatra izany, ary ankehitriny, araka ny hitanao, dia tonga amin'ny Excel. Andao hiezaka hahatakatra io teknika io miaraka amin'ny ohatra azo ampiharina.
Eritrereto hoe te hamorona fiasa voafaritry ny mpampiasa izay hanaisotra ireo tarehintsoratra nomena rehetra ao amin'ny lahatsoratra loharano. Ny maha-ilaina ny fiasa toy izany, heveriko fa tsy mila manaporofo ianao - mety tsara ny manadio ny angon-drakitra misy fako miaraka amin'ny fanampiany, sa tsy izany?
Na izany aza, raha oharina amin'ireo ohatra teo aloha, tsy miverimberina, olana roa no miandry antsika.
- Tsy maintsy mamorona anarana ho an'ny asantsika isika alohan'ny hanombohantsika manoratra ny kaody, satria ao io anarana io dia efa hampiasaina hiantsoana ilay asa.
- Tsy mandeha ny fampidirana asa miverimberina toy izany ao anaty sela iray ary mametaka azy amin'ny alàlan'ny famaritana ny tohan-kevitra ao anaty fononteny aorian'ny LAMBDA (tahaka ny nataontsika teo aloha). Tsy maintsy mamorona asa avy hatrany ianao "avy amin'ny scratch" in Name Manager (Mpitantana anarana).
Andao hiantso ny asantsika, lazao hoe, CLEAN ary tianay ny hanana hevitra roa - ny lahatsoratra hodiovina ary ny lisitry ny tarehintsoratra voahilika ho tady lahatsoratra:
Andao hamorona, toy ny nataontsika teo aloha, eo amin'ny tabilao raikipohy в Mpitantana anarana nomena anarana, anarana hoe CLEAR ary midira ao amin'ny saha Range fanorenana manaraka:
=LAMBDA(t;d;RIKA(d=””;t;MAZAVA(MANONO(t;ankavia(d);””);MID(d;2;255))))
Eto ny variable t dia ny lahatsoratra tany am-boalohany hofafana, ary ny d dia ny lisitry ny litera hofafana.
Izany rehetra izany dia miasa toy izao:
Famerenana 1
Ny sombintsombiny SUBSTITUTE(t;LEFT(d);””), araka ny mety ho eritreretinao, dia manolo ny tarehintsoratra voalohany avy amin'ny tarehintsoratra ankavia avy amin'ny set d ho voafafa ao amin'ny lahatsoratra loharano t miaraka amin'ny tady lahatsoratra foana, izany hoe manala ny “ A”. Vokatr'izany dia mahazo:
Vsh zkz n 125 roubles.
Famerenana 2
Avy eo ilay asa dia miantso ny tenany ary amin'ny maha-input (ny argument voalohany) dia mandray izay tavela aorian'ny fanadiovana amin'ny dingana teo aloha, ary ny tohan-kevitra faharoa dia ny tadin'ny tarehin-tsoratra voahilika tsy manomboka amin'ny voalohany, fa avy amin'ny toetra faharoa, izany hoe "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. , "tsy misy ny "A" voalohany - izany dia ataon'ny asa MID. Toy ny teo aloha, ny fiasa dia maka ny endri-tsoratra voalohany amin'ny ankavia amin'ireo sisa (B) ary manolo izany ao amin'ny lahatsoratra nomena azy (Zkz n 125 roubles) miaraka amin'ny tady tsy misy na inona na inona - dia mahazo valiny manelanelana isika:
125 ru.
Famerenana 3
Ny asa dia miantso ny tenany indray, mandray ho toy ny hevitra voalohany izay sisa tavela amin'ny lahatsoratra ho voafafa amin'ny teo aloha iteration (Bsh zkz n 125 ru.), Ary ho toy ny hevitra faharoa, ny fitambaran'ny tarehin-tsoratra voahilika notapahin'ny tarehin-tsoratra iray hafa ho. ny ankavia, izany hoe “VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.,” tsy misy ny “B” voalohany. Avy eo dia alainy indray ny toetra voalohany amin'ny ankavia (B) amin'ity andiany ity ary esorina amin'ny lahatsoratra - mahazo:
sh zkz n 125 ru.
Ary ny sisa - manantena aho fa azonao ny hevitra. Isaky ny miverina, ny lisitr'ireo tarehintsoratra hesorina dia hotapahana eo ankavia, ary hikaroka sy hanolo ny endri-tsoratra manaraka avy amin'ny napetraka miaraka amin'ny banga.
Rehefa lany ny endri-tsoratra rehetra dia mila miala amin'ny loop isika - io andraikitra io dia tanterahan'ny asa fotsiny IF (RAHA), izay nofonosinay ny famolavolanay. Raha tsy misy tarehintsoratra tavela hofafana (d=””), dia tsy tokony hiantso ny tenany intsony ilay asa, fa tokony hamerina fotsiny ny lahatsoratra ho voafafa (variable t) amin'ny endriny farany.
Famerenana ny sela
Toy izany koa, azonao atao ny mametraka fanisana sela miverimberina amin'ny faritra iray. Eritrereto hoe te hamorona asa lambda antsoina hoe Lisitry ny fanoloana ho an'ny fanoloana ambongadiny ny sombiny ao amin'ny lahatsoratra loharano araka ny lisitry ny fanondroana nomena. Ny vokatra dia tokony ho toy izao:
Ireo. amin'ny hetsika ataontsika Lisitry ny fanoloana hisy hevitra telo:
- sela misy lahatsoratra hokarakaraina (adiresy loharano)
- ny sela voalohany amin'ny tsanganana misy soatoavina hikaroka avy amin'ny fitadiavana
- ny sela voalohany amin'ny tsanganana misy sanda fanoloana avy amin'ny fitadiavana
Ny fiasa dia tokony handeha avy any ambony ka hatrany ambany ao amin'ny lahatahiry ary manolo ny safidy rehetra avy amin'ny tsanganana havia Mahita mankany amin'ny safidy mifanandrify amin'ny tsanganana havanana mpisolo toerana. Azonao atao ny manatanteraka izany amin'ny alàlan'ny fiasa lambda miverimberina:
Ny fampidinana midina isaky ny mandeha tsirairay dia ampiharina amin'ny alàlan'ny fiasa excel mahazatra fanariana (OFFSET), izay amin'ity tranga ity dia manana hevitra telo - ny isan-karazany tany am-boalohany, ny row shift (1) ary ny tsanganana shift (0).
Eny ary, raha vantany vao tonga any amin'ny faran'ny lahatahiry (n = "") isika dia tsy maintsy mamarana ny fiverenana - mijanona tsy miantso ny tenantsika isika ary mampiseho ny zavatra voaangona taorian'ny fanoloana rehetra tao amin'ny fari-pitsipika loharano t.
Izay ihany. Tsy misy macro saro-pady na fanontaniana momba ny Power Query - voavahan'ny asa iray ny asa manontolo.
- Ahoana ny fampiasana ny fiasa dynamic array vaovao an'ny Excel: FILTER, SORT, UNIC
- Fanoloana sy fanadiovana lahatsoratra amin'ny fiasa SUBSTITUTE
- Mamorona macros sy asa voafaritry ny mpampiasa (UDF) amin'ny VBA