Мазмұны
Қазіргі уақытта Microsoft Excel бағдарламасында Функция шебері терезесі – түймесі арқылы қол жетімді бес жүзге жуық жұмыс парағы функциялары бар fx формула жолында. Бұл өте лайықты жиынтық, бірақ соған қарамастан, әрбір дерлік пайдаланушы ерте ме, кеш пе, бұл тізімде оған қажет функция жоқ жағдайға тап болады - бұл Excel бағдарламасында болмағандықтан.
Осы уақытқа дейін бұл мәселені шешудің жалғыз жолы макростар болды, яғни Visual Basic тілінде өзіңіздің пайдаланушы анықтайтын функцияңызды (UDF = User Defined Function) жазу, ол сәйкес бағдарламалау дағдыларын қажет етеді және кейде оңай емес. Дегенмен, соңғы Office 365 жаңартуларымен жағдай жақсы жаққа өзгерді – Excel бағдарламасына арнайы «орау» функциясы қосылды. ЛАМБДА. Оның көмегімен өз функцияларыңызды жасау міндеті енді оңай және әдемі шешілді.
Оны қолдану принципін келесі мысалда қарастырайық.
Өздеріңіз білетіндей, Excel бағдарламасында берілген күн үшін күн, ай, апта және жыл санын анықтауға мүмкіндік беретін бірнеше күнді талдау функциялары бар. Бірақ қандай да бір себептермен тоқсан санын анықтайтын функция жоқ, ол да жиі қажет, солай емес пе? Осы кемшілікті түзетіп, бірге жасайық ЛАМБДА бұл мәселені шешу үшін жаңа функцияға ие.
1-қадам. Формуланы жазыңыз
Кәдімгідей қолмен формуланы парақ ұяшығына жазатынымыздан бастайық, ол бізге қажет нәрсені есептейді. Тоқсандық сан жағдайында мұны, мысалы, келесідей жасауға болады:
2-қадам. LAMBDA жүйесінде қорытындылау және тестілеу
Енді жаңа LAMBDA функциясын қолдану және оған формуламызды орау уақыты келді. Функция синтаксисі келесідей:
=LAMBDA(Айнымалы 1; Айнымалы 2; … АйнымалыN ; Өрнек)
мұнда бір немесе бірнеше айнымалылардың атаулары бірінші болып тізімделеді және соңғы аргумент әрқашан оларды пайдаланатын формула немесе есептелген өрнек болып табылады. Айнымалы атаулар ұяшық адрестеріне ұқсамауы керек және нүктелерден тұруы керек.
Біздің жағдайда тек бір айнымалы болады – тоқсан санын есептейтін күн. Ол үшін айнымалыны шақырайық, айталық, d. Содан кейін формуламызды функцияға орау ЛАМБДА және бастапқы A2 ұяшығының мекенжайын жалған айнымалы атаумен алмастырсақ, біз мынаны аламыз:
Назар аударыңыз, мұндай түрлендіруден кейін біздің формуламыз (шын мәнінде, дұрыс!) қате жасай бастады, өйткені қазір A2 ұяшығынан бастапқы күн оған ауыстырылмайды. Тестілеу және сенімділік үшін функциядан кейін оларды қосу арқылы оған аргументтерді беруге болады ЛАМБДА жақша ішінде:
3-қадам. Атау жасаңыз
Енді жеңіл және қызықты бөлікке. ашамыз Аты менеджері қойындысы формула (Формулалар — атау менеджері) түймесі арқылы жаңа атау жасаңыз жасау (Жасау). Біздің болашақ функциямыз үшін атау ойлап табыңыз және енгізіңіз (мысалы, Номквартала) және далада байланыс (Сілтеме) формула жолынан мұқият көшіріп, функциямызды қойыңыз ЛАМБДА, тек соңғы аргументсіз (A2):
Барлығы. Басқаннан кейін OK жасалған функцияны осы жұмыс кітабының кез келген парағындағы кез келген ұяшықта пайдалануға болады:
Басқа кітаптарда қолданыңыз
LAMBDA және динамикалық массивтер
Функциямен жасалған теңшелетін функциялар ЛАМБДА жаңа динамикалық массивтермен және олардың функцияларымен жұмысты сәтті қолдайды (СҮЗГІ, UNIK, СЫНЫП) 2020 жылы Microsoft Excel бағдарламасына қосылды.
Біз екі тізімді салыстыратын және олардың арасындағы айырмашылықты – екінші тізімде жоқ бірінші тізімдегі элементтерді қайтаратын жаңа пайдаланушы анықтайтын функцияны жасағымыз келеді делік. Өмірдің жұмысы, солай емес пе? Бұрын бұл үшін олар a la функциясын пайдаланған VPR (КӨРУ), немесе Жиынтық кестелер немесе Power Query сұраулары. Енді сіз бір формуламен жасай аласыз:
Ағылшын тіліндегі нұсқасында ол келесідей болады:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Мұнда функция COUNTIF бірінші тізімнің әрбір элементінің екіншіде, содан кейін функцияда орын алу санын санайды СҮЗГІ олардың арасынан мұндай оқиғалар болмағандарды ғана таңдайды. Бұл құрылымды орау арқылы ЛАМБДА және оған негізделген атаумен аталған ауқымды жасау, мысалы, Іздеу таралу – динамикалық массив түріндегі екі тізімді салыстыру нәтижесін қайтаратын ыңғайлы функцияны аламыз:
Егер бастапқы деректер қарапайым емес, бірақ «ақылды» кестелер болса, біздің функциямыз да қиындықсыз жұмыс істейді:
Тағы бір мысал - XML форматына түрлендіру арқылы мәтінді динамикалық түрде бөлу, содан кейін біз жақында талдаған FILTER.XML функциясы арқылы оны ұяшық бойынша талдау. Бұл күрделі формуланы әр уақытта қолмен қайта жасамау үшін оны LAMBDA-ға орап, оның негізінде динамикалық диапазонды жасау оңайырақ болады, яғни жаңа ықшам және ыңғайлы функция, оны атай отырып, мысалы, RAZDTEXT:
Бұл функцияның бірінші аргументі бастапқы мәтіні бар ұяшық, ал екіншісі – бөлгіш таңба болады және ол нәтижені көлденең динамикалық массив түрінде қайтарады. Функция коды келесідей болады:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML("
Мысалдар тізімі шексіз – бірдей ұзақ және қиын формуланы жиі енгізуге тура келетін кез келген жағдайда LAMBDA функциясы өмірді айтарлықтай жеңілдетеді.
Таңбаларды рекурсивті санау
Алдыңғы барлық мысалдар LAMBDA функциясының бір ғана, ең айқын жағын көрсетті – оны ұзын формулаларды орау және енгізуді жеңілдету үшін «орағыш» ретінде пайдалану. Шын мәнінде, LAMBDA-ның оны толыққанды бағдарламалау тіліне айналдыратын тағы бір тереңірек жағы бар.
LAMBDA функцияларының негізгі маңызды ерекшелігі - оларды іске асыру мүмкіндігі рекурсия – есептеулер логикасы, есептеу процесінде функция өзін шақырады. Әдеттегідей, бұл қорқынышты көрінуі мүмкін, бірақ бағдарламалауда рекурсия әдеттегі нәрсе. Тіпті Visual Basic-тегі макростарда сіз оны жүзеге асыра аласыз, енді көріп тұрғаныңыздай, ол Excel бағдарламасына келді. Бұл техниканы практикалық мысалмен түсінуге тырысайық.
Біз бастапқы мәтіннен барлық берілген таңбаларды алып тастайтын пайдаланушы анықтайтын функцияны жасағымыз келеді делік. Мұндай функцияның пайдалылығын, менің ойымша, дәлелдеудің қажеті жоқ – оның көмегімен қоқыссыз кіріс деректерін тазалау өте ыңғайлы болар еді, солай емес пе?
Дегенмен, алдыңғы, рекурсивті емес мысалдармен салыстырғанда, бізді екі қиындық күтіп тұр.
- Біз оның кодын жазуды бастамас бұрын функциямыздың атауын ойлап табуымыз керек, өйткені онда бұл атау функцияның өзін шақыру үшін пайдаланылады.
- Ұяшыққа осындай рекурсивті функцияны енгізу және LAMBDA-дан кейін жақшадағы аргументтерді көрсету арқылы оны түзету (бұрынғыдай) жұмыс істемейді. Сізге бірден «нөлден» функция жасау керек болады Аты менеджері (Аты менеджері).
Функциямызды, айталық, CLEAN деп атаймыз және біз оның екі аргументі болғанын қалаймыз – тазартылатын мәтін және мәтіндік жол ретінде алынып тасталған таңбалар тізімі:
Бұрынғыдай қойындыда жасайық формула в Атау менеджері диапазон деп атаңыз, оны CLEAR деп атаңыз және өріске енгізіңіз диапазон келесі құрылыс:
=LAMBDA(t;d;Егер(d=””;t;ТАЗАЛАУ(t;СОЛ(d);"”);ORTA(d;2;255))))
Мұнда t айнымалысы тазартылатын бастапқы мәтін, ал d - жойылатын таңбалар тізімі.
Мұның бәрі келесідей жұмыс істейді:
Қайталау 1
SUBSTITUTE(t;LEFT(d);””) фрагменті, сіз болжағандай, бастапқы мәтінде жойылатын d жиынының сол жақ таңбасының бірінші таңбасын t бос мәтіндік жолға ауыстырады, яғни “ А”. Аралық нәтиже ретінде біз мыналарды аламыз:
Vsh zkz n 125 рубль.
Қайталау 2
Содан кейін функция өзін шақырады және кіріс ретінде (бірінші аргумент) алдыңғы қадамда тазартудан кейін қалғанды алады, ал екінші аргумент біріншіден емес, екінші таңбадан басталатын алынып тасталған таңбалар тізбегі, яғни «BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,» бастапқы «A» жоқ – бұл MID функциясы арқылы орындалады. Бұрынғыдай, функция қалғандардың сол жағынан бірінші таңбаны алады (B) және оны оған берілген мәтінде (Zkz n 125 рубль) бос жолға ауыстырады – аралық нәтиже ретінде аламыз:
125 ру.
Қайталау 3
Функция өзін қайта шақырады, бірінші аргумент ретінде алдыңғы итерацияда тазартылатын мәтіннің қалған бөлігін алады (Bsh zkz n 125 ru.), ал екінші аргумент ретінде алынып тасталған таңбалар жиынын тағы бір таңбаға қысқартады. сол жақ, яғни «VGDEEGZIKLMNOPRSTUFHTSCHSHCHYYYYUYA.», бастапқы «В» жоқ. Содан кейін ол қайтадан осы жиынның сол жағындағы бірінші таңбаны алады және оны мәтіннен алып тастайды - біз аламыз:
sh zkz n 125 ru.
Және т.б. – сіз идеяны аласыз деп үміттенемін. Әрбір итерацияда жойылатын таңбалар тізімі сол жақтан қысқартылады және біз жиыннан келесі таңбаны іздеп, бос орынмен ауыстырамыз.
Барлық таңбалар таусылғанда, біз циклден шығуымыз керек - бұл рөл тек функция арқылы орындалады IF (Егер), онда біздің дизайн оралған. Егер жойылатын таңбалар қалмаса (d=””), онда функция енді өзін шақырмауы керек, тек тазартылатын мәтінді (t айнымалысы) соңғы түрінде қайтаруы керек.
Ұяшықтардың рекурсивті қайталануы
Сол сияқты, берілген ауқымдағы ұяшықтардың рекурсивті санауын жүзеге асыруға болады. деп аталатын лямбда функциясын жасағымыз келеді делік АУЫСТЫРУ ТІЗІМІ берілген анықтамалық тізімге сәйкес бастапқы мәтіндегі үзінділерді көтерме ауыстыру үшін. Нәтиже келесідей болуы керек:
Анау. біздің функциямызда АУЫСТЫРУ ТІЗІМІ үш аргумент болады:
- өңдеуге арналған мәтіні бар ұяшық (бастапқы мекенжай)
- іздеуден іздеу үшін мәндері бар бағанның бірінші ұяшығы
- іздеуден алынған ауыстыру мәндері бар бағанның бірінші ұяшығы
Функция каталогта жоғарыдан төменге қарай жүріп, сол жақ бағандағы барлық опцияларды ретімен ауыстыруы керек Табу оң жақ бағандағы сәйкес опцияларға өтіңіз Ауыстыру. Мұны келесі рекурсивті лямбда функциясымен жүзеге асыруға болады:
Әрбір иерацияда төмен жылжыту стандартты Excel функциясы арқылы жүзеге асырылады Диспозалы (OFSET), бұл жағдайда үш аргументі бар – бастапқы ауқым, жолды жылжыту (1) және бағанды ауыстыру (0).
Каталогтың соңына жеткен бойда (n = «») біз рекурсияны аяқтауымыз керек – біз өзімізді шақыруды тоқтатамыз және бастапқы мәтін айнымалысында t барлық ауыстырулардан кейін жинақталған нәрсені көрсетеміз.
Бар болғаны. Күрделі макростар немесе Power Query сұраулары жоқ – бүкіл тапсырма бір функция арқылы шешіледі.
- Excel бағдарламасының жаңа динамикалық массив функцияларын пайдалану жолы: FILTER, SORT, UNIC
- SUBSTITUTE функциясымен мәтінді ауыстыру және тазалау
- VBA жүйесінде макростар мен пайдаланушы анықтайтын функцияларды (UDF) жасау