Werrej
Bħalissa, Microsoft Excel għandha kważi ħames mitt funzjoni tal-worksheet disponibbli permezz tat-tieqa tal-Wizard tal-Funzjoni - buttuna fx fil-bar tal-formula. Dan huwa sett deċenti ħafna, iżda, madankollu, kważi kull utent illum jew għada jiltaqa 'ma' sitwazzjoni fejn din il-lista ma jkunx fiha l-funzjoni li għandu bżonn - sempliċiment għax mhix f'Excel.
Sa issa, l-uniku mod biex tissolva din il-problema kien il-macros, jiġifieri l-kitba tal-funzjoni tiegħek stess definita mill-utent (UDF = User Defined Function) fil-Visual Basic, li teħtieġ ħiliet ta’ programmar xierqa u, xi drabi, mhix faċli xejn. Madankollu, bl-aħħar aġġornamenti tal-Office 365, is-sitwazzjoni nbidlet għall-aħjar - funzjoni speċjali ta '"wrapper" ġiet miżjuda ma' Excel LAMBDA. Bl-għajnuna tagħha, il-kompitu tal-ħolqien tal-funzjonijiet tiegħek issa huwa solvut faċilment u sabiħ.
Ejja nħarsu lejn il-prinċipju tal-użu tiegħu fl-eżempju li ġej.
Kif x'aktarx taf, Excel għandu diversi funzjonijiet ta 'parsing tad-data li jippermettulek tiddetermina n-numru tal-ġurnata, xahar, ġimgħa u sena għal data partikolari. Iżda għal xi raġuni m'hemm l-ebda funzjoni li tiddetermina n-numru tal-kwart, li huwa wkoll ta 'spiss meħtieġ, hux? Ejja nirranġaw dan in-nuqqas u noħolqu bihom LAMBDA funzjoni ġdida stess biex issolvi din il-problema.
Pass 1. Ikteb il-formula
Nibdew bil-fatt li manwalment bil-mod tas-soltu se niktbu formula f'ċellula tal-folja li tikkalkula dak li għandna bżonn. Fil-każ tan-numru ta' kwart, dan jista' jsir, pereżempju, bħal dan:
Pass 2. Tgeżwir f'LAMBDA u ttestjar
Issa wasal iż-żmien li tapplika l-funzjoni l-ġdida LAMBDA u nagħlaq il-formula tagħna fiha. Is-sintassi tal-funzjoni hija kif ġej:
=LAMBDA(Varjabbli1; Varjabbli2; ... VarjabbliN ; espressjoni)
fejn l-ismijiet ta 'wieħed jew aktar varjabbli huma elenkati l-ewwel, u l-aħħar argument huwa dejjem formula jew espressjoni kkalkulata li tużahom. Ismijiet varjabbli m'għandhomx jidhru bħall-indirizzi taċ-ċelluli u m'għandux ikun fihom tikek.
Fil-każ tagħna, se jkun hemm varjabbli waħda biss - id-data li għaliha nikkalkulaw in-numru tat-trimestru. Ejja nsejħu l-varjabbli għaliha, ngħidu aħna, d. Imbagħad tgeżwir formula tagħna f'funzjoni LAMBDA u billi tissostitwixxi l-indirizz taċ-ċellula oriġinali A2 b'isem varjabbli fittizju, irridu:
Jekk jogħġbok innota li wara trasformazzjoni bħal din, il-formula tagħna (fil-fatt, korretta!) bdiet tipproduċi żball, għax issa d-data oriġinali miċ-ċellula A2 mhix trasferita lilha. Għall-ittestjar u l-kunfidenza, tista 'tgħaddilu argumenti billi żżidhom wara l-funzjoni LAMBDA fil-parentesi:
Pass 3. Oħloq isem
Issa għall-parti faċli u divertenti. Niftħu Maniġer tal-Ismijiet tab formula (Formuli — Maniġer tal-Ismijiet) u oħloq isem ġdid bil-buttuna Oħloq (Oħloq). Oħroġ u daħħal isem għall-funzjoni futura tagħna (pereżempju, Nomkvartala), u fil-qasam link (Referenza) kopja bir-reqqa mill-bar tal-formula u paste l-funzjoni tagħna LAMBDA, biss mingħajr l-aħħar argument (A2):
Kollox. Wara li tikklikkja fuq OK il-funzjoni maħluqa tista 'tintuża fi kwalunkwe ċellula fuq kwalunkwe folja ta' dan il-ktieb tax-xogħol:
Uża fi kotba oħra
LAMBDA u arrays dinamiċi
Funzjonijiet personalizzati maħluqa b'funzjoni LAMBDA jappoġġjaw b'suċċess ix-xogħol b'arrays dinamiċi ġodda u l-funzjonijiet tagħhom (Iffiltra, UNIK, GRAD) miżjuda mal-Microsoft Excel fl-2020.
Ejja ngħidu li rridu noħolqu funzjoni ġdida definita mill-utent li tqabbel żewġ listi u tirritorna d-differenza bejniethom - dawk l-elementi mill-ewwel lista li mhumiex fit-tieni. Ix-xogħol tal-ħajja, hux? Preċedentement, għal dan użaw jew funzjonijiet a la VPR (LOOKUP), jew PivotTables, jew mistoqsijiet dwar Power Query. Issa tista 'tagħmel b'formula waħda:
Fil-verżjoni bl-Ingliż se jkun:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hawnhekk il-funzjoni COUNTIF jgħodd in-numru ta 'okkorrenzi ta' kull element tal-ewwel lista fit-tieni, u mbagħad il-funzjoni Iffiltra jagħżel biss dawk minnhom li ma kellhomx dawn l-okkorrenzi. Billi tgeżwer din l-istruttura fi LAMBDA u toħloq firxa imsemmija bbażata fuqha b'isem, pereżempju, DISTRIBUZZJONI TA' FITXA – se nġibu funzjoni konvenjenti li tirritorna r-riżultat tat-tqabbil ta’ żewġ listi fil-forma ta’ firxa dinamika:
Jekk id-dejta tas-sors mhix tabelli ordinarji, iżda "intelliġenti", il-funzjoni tagħna tlaħħaq ukoll mingħajr problemi:
Eżempju ieħor huwa t-test li jaqsam b'mod dinamiku billi taqlebh f'XML u mbagħad jiġi analizzat ċellula b'ċellula bl-użu tal-funzjoni FILTER.XML li analizzajna dan l-aħħar. Sabiex ma tirriproduċix din il-formula kumplessa manwalment kull darba, ikun aktar faċli li tkebbebha f'LAMBDA u toħloq firxa dinamika bbażata fuqha, jiġifieri funzjoni kompatta u konvenjenti ġdida, li tissemma, pereżempju, RAZDTEXT:
L-ewwel argument ta 'din il-funzjoni se jkun iċ-ċellula bit-test sors, u t-tieni - il-karattru separatur, u se jirritorna r-riżultat fil-forma ta' firxa dinamika orizzontali. Il-kodiċi tal-funzjoni se jkun kif ġej:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Il-lista ta 'eżempji hija bla tarf - fi kwalunkwe sitwazzjoni fejn spiss ikollok tidħol l-istess formula twila u ingombranti, il-funzjoni LAMBDA se tagħmel il-ħajja notevolment aktar faċli.
Enumerazzjoni rikorsiva ta' karattri
L-eżempji preċedenti kollha wrew naħa waħda biss, l-aktar ovvja, tal-funzjoni LAMBDA - l-użu tagħha bħala "tgeżwir" għat-tgeżwir ta 'formuli twal fiha u tissimplifika l-input tagħhom. Fil-fatt, LAMBDA għandha naħa oħra, ħafna aktar profonda, li tibdelha f'lingwa ta' programmar kważi sħiħa.
Il-fatt hu li karatteristika fundamentalment importanti tal-funzjonijiet LAMBDA hija l-abbiltà li timplimentahom rikursjoni – loġika tal-kalkoli, meta fil-proċess tal-kalkolu l-funzjoni ssejjaħ lilha nnifisha. Mill-vizzju, jista 'ħoss creepy, iżda fl-ipprogrammar, ir-rikorsjoni hija ħaġa komuni. Anke fil-macros fil-Visual Basic, tista 'timplimentah, u issa, kif tistgħu taraw, waslet għal Excel. Ejja nippruvaw nifhmu din it-teknika b'eżempju prattiku.
Ejja ngħidu li rridu noħolqu funzjoni definita mill-utent li tneħħi l-karattri kollha mogħtija mit-test sors. L-utilità ta 'tali funzjoni, naħseb, m'għandekx bżonn tipprova - ikun konvenjenti ħafna li tnaddaf id-dejta tal-input mifruxa bl-għajnuna tagħha, hux?
Madankollu, meta mqabbla mal-eżempji preċedenti, mhux rikorsivi, jistennewna żewġ diffikultajiet.
- Ikollna noħorġu b'isem għall-funzjoni tagħna qabel ma nibdew niktbu l-kodiċi tagħha, għax fiha, dan l-isem diġà se jintuża biex issejjaħ il-funzjoni nnifisha.
- Id-dħul ta 'tali funzjoni rikorsiv f'ċellola u d-debugging tagħha billi tispeċifika argumenti fil-parentesi wara LAMBDA (kif għamilna qabel) mhux se taħdem. Int ser ikollok toħloq funzjoni immedjatament "mill-bidu" ġewwa Maniġer tal-Ismijiet (Isem Maniġer).
Ejja nsejħu l-funzjoni tagħna, ngħidu aħna, NADIF u nixtiequ li jkollha żewġ argumenti – it-test li jrid jitnaddaf u l-lista ta’ karattri esklużi bħala string ta’ test:
Ejja noħolqu, kif għamilna qabel, fuq it-tab formula в Isem il-maniġer firxa msemmija, semmiha ĊARA u daħħal fil-qasam Medda kostruzzjoni li ġejja:
=LAMBDA(t;d;JEKK(d="”;t;CLEAR(SOSTITUT(t;XELLUG(d););””);MID(d;2;255))))
Hawnhekk il-varjabbli t hija t-test oriġinali li jrid jitneħħa, u d hija l-lista ta' karattri li għandhom jitħassru.
Kollox jaħdem hekk:
Iterazzjoni 1
Il-framment SOSTITUT(t;LEFT(d);””), kif tista’ taħsbu, jissostitwixxi l-ewwel karattru mill-karattru tax-xellug mis-sett d li għandu jitħassar fit-test sors t b’sekwenza ta’ test vojta, jiġifieri tneħħi l-“ A”. Bħala riżultat intermedju, irridu:
Vsh zkz n 125 rublu.
Iterazzjoni 2
Imbagħad il-funzjoni ssejjaħ lilha nnifisha u bħala input (l-ewwel argument) tirċievi dak li jkun fadal wara t-tindif fil-pass preċedenti, u t-tieni argument huwa s-sekwenza ta 'karattri esklużi li jibdew mhux mill-ewwel, iżda mit-tieni karattru, jiġifieri "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," mingħajr l-"A" inizjali - dan isir mill-funzjoni MID. Bħal qabel, il-funzjoni tieħu l-ewwel karattru mix-xellug ta 'dawk li jifdal (B) u tibdilha fit-test mogħti lilha (Zkz n 125 rublu) b'sekwenza vojta - niksbu bħala riżultat intermedju:
125 ru.
Iterazzjoni 3
Il-funzjoni ssejjaħ lilha nnifisha għal darb'oħra, tirċievi bħala l-ewwel argument dak li fadal mit-test li jrid jitnaddaf fl-iterazzjoni preċedenti (Bsh zkz n 125 ru.), U bħala t-tieni argument, is-sett ta 'karattri esklużi maqtugħ b'karattru wieħed aktar għal ix-xellug, jiġifieri “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” mingħajr l-inizjali “B”. Imbagħad jerġa 'jieħu l-ewwel karattru mix-xellug (B) minn dan is-sett u jneħħih mit-test - irridu:
sh zkz n 125 ru.
U hekk - Nispera li tieħu l-idea. Ma 'kull iterazzjoni, il-lista ta' karattri li għandhom jitneħħew se tkun maqtugħa fuq ix-xellug, u aħna se nfittxu u nissostitwixxu l-karattru li jmiss mis-sett b'vojt.
Meta l-karattri kollha jispiċċaw, ikollna bżonn noħorġu mill-linja - dan ir-rwol jitwettaq biss mill-funzjoni IF (JEKK), li fiha d-disinn tagħna huwa mgeżwer. Jekk ma jkun fadal l-ebda karattri x'tħassar (d=""), allura l-funzjoni m'għandhiex tibqa 'ssejjaħ lilha nnifisha, iżda għandha sempliċiment tirritorna t-test li għandu jiġi kklerjat (varjabbli t) fil-forma finali tagħha.
Iterazzjoni rikursiva taċ-ċelloli
Bl-istess mod, tista 'timplimenta enumerazzjoni rikorsiva ta' ċelloli f'firxa partikolari. Ejja ngħidu li rridu noħolqu funzjoni lambda msemmija LISTA TA' SOSTITUZZJONI għas-sostituzzjoni bl-ingrossa ta’ frammenti fit-test sors skont lista ta’ referenza partikolari. Ir-riżultat għandu jidher bħal dan:
Dawk. fil-funzjoni tagħna LISTA TA' SOSTITUZZJONI se jkun hemm tliet argumenti:
- ċellula bit-test għall-proċess (indirizz tas-sors)
- l-ewwel ċellola ta 'kolonna b'valuri biex tfittex mill-tfittxija
- l-ewwel ċellola tal-kolonna b'valuri ta 'sostituzzjoni mill-tfittxija
Il-funzjoni għandha tmur minn fuq għal isfel fid-direttorju u tissostitwixxi b'mod sekwenzjali l-għażliet kollha mill-kolonna tax-xellug Issib għall-għażliet korrispondenti mill-kolonna tal-lemin Sostitut. Tista' timplimenta dan bil-funzjoni lambda rikorsiva li ġejja:
Shift isfel f'kull iterazzjoni hija implimentata minn funzjoni standard excel RIMI (OFFSET), li f'dan il-każ għandu tliet argumenti - il-firxa oriġinali, shift ringiela (1) u shift kolonna (0).
Ukoll, hekk kif naslu fit-tmiem tad-direttorju (n = ""), irridu ntemmu r-rikors - nieqfu nsejħu lilna nfusna u nuru dak li jkun akkumula wara s-sostituzzjonijiet kollha fil-varjabbli tat-test sors t.
Dak kollox. Ebda macros delikati jew mistoqsijiet dwar Power Query - il-kompitu kollu jiġi solvut b'funzjoni waħda.
- Kif tuża l-funzjonijiet l-ġodda tal-firxa dinamika ta 'Excel: FILTER, SORT, UNIC
- Sostituzzjoni u tindif tat-test bil-funzjoni SOSTITUT
- Il-ħolqien ta' macros u funzjonijiet definiti mill-utent (UDFs) f'VBA