мазмуну
Учурда Microsoft Excelде Функция чебери терезеси – баскычы аркылуу жеткиликтүү беш жүзгө жакын иш барагынын функциялары бар fx формула тилкесинде. Бул абдан татыктуу топтом, бирок, ошентсе да, дээрлик ар бир колдонуучу эртеби-кечпи бул тизмеде ага керектүү функция жок болгон жагдайга туш болот – жөн гана Excelде жок болгондуктан.
Буга чейин бул көйгөйдү чечүүнүн бирден-бир жолу макростар болгон, б.а. өзүңүздүн колдонуучу аныктаган функцияңызды (UDF = User Defined Function) Visual Basic программасында жазуу, ал тиешелүү программалоо көндүмдөрүн талап кылат жана кээде такыр оңой эмес. Бирок, акыркы Office 365 жаңыртуулары менен кырдаал жакшы жагына өзгөрдү – Excelге атайын “ороочу” функциясы кошулду. Lambda. Анын жардамы менен, өз функцияларын түзүү милдети азыр жеңил жана кооз чечилет.
Аны колдонуу принцибине төмөнкү мисалда токтололу.
Белгилүү болгондой, Excel программасында берилген дата үчүн күндүн, айдын, жуманын жана жылдын санын аныктоого мүмкүндүк берген бир нече дата талдоо функциялары бар. Бирок эмнегедир кварталдын санын аныктай турган функция жок, ал дагы көп учурда керек, туурабы? Ушул кемчиликти оңдоп, түзөлү Lambda Бул көйгөйдү чечүү үчүн жаңы функция.
1-кадам. Формула жазыңыз
Келгиле, кол менен кадимки жол менен барак уячасына формула жаза турганыбыздан баштайлы, ал бизге керектүү нерсени эсептейт. чейрек саны учурда, бул, мисалы, мындай болушу мүмкүн:
2-кадам. LAMBDA менен бүтүрүү жана тестирлөө
Эми жаңы LAMBDA функциясын колдонууга жана ага биздин формуланы ороп коюуга убакыт келди. Функциянын синтаксиси төмөнкүдөй:
=LAMBDA(Variable1; Variable2; ... VariableN ; билдирүү)
мында бир же бир нече өзгөрмөнүн аттары биринчи тизмектелет, ал эми акыркы аргумент дайыма формула же аларды колдонгон эсептелген туюнтма болуп саналат. Өзгөрмөлөрдүн аттары клетканын даректерине окшош болбошу керек жана чекиттерди камтыбашы керек.
Биздин учурда, бир гана өзгөрмө болот - биз чейрек санын эсептеп датасы. Келгиле, ал үчүн өзгөрмө чакыралы, айталы, d. Андан кийин формулабызды функцияга ороп алабыз Lambda жана түпнуска A2 уячасынын дарегин жасалма өзгөрмө ат менен алмаштырсак, биз:
Көңүл буруңуз, мындай трансформациядан кийин биздин формула (чынында, туура!) ката кетире баштады, анткени азыр A2 уячасынан баштапкы дата ага өткөрүлбөйт. Сыноо жана ишенимдүүлүк үчүн, аргументтерди функциядан кийин кошуу менен ага өткөрө аласыз Lambda кашаанын ичинде:
3-кадам. Ат түзүңүз
Эми жеңил жана кызыктуу бөлүгү үчүн. ачабыз Аты менеджер табулатура формула (Формулалар — Аты-жөнү менеджери) жана баскычы менен жаңы ат түзүңүз түзүү (Түзүү). Келечектеги функциябыздын атын ойлоп табыңыз (мисалы, Номквартала), жана талаада байланыш (Шилтеме) формула тилкесинен кылдат көчүрүп, функциябызды чаптаңыз Lambda, акыркы аргументсиз гана (A2):
Баары. Баскандан кийин OK түзүлгөн функция бул китептин каалаган барагындагы каалаган уячада колдонулушу мүмкүн:
Башка китептерде колдонуңуз
LAMBDA жана динамикалык массивдер
Функция менен түзүлгөн жеке функциялар Lambda жаңы динамикалык массивдер жана алардын функциялары менен иштөөнү ийгиликтүү колдойт (FILTER, UNIQUE, КЛАСС) 2020-жылы Microsoft Excel программасына кошулган.
Келгиле, биз эки тизмени салыштырып, алардын ортосундагы айырманы кайтара турган жаңы колдонуучу аныктаган функцияны түзгүбүз келет дейли – биринчи тизмедеги экинчиде жок элементтер. Жашоонун иши, туурабы? Мурда бул үчүн алар а ла функциясын колдонушкан VPR (КӨРҮҮ), же Пивот таблицалары же Power Query сурамдары. Эми сиз бир формула менен кыла аласыз:
Англисче версиясында мындай болот:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Бул жерде функция COUNTIF биринчи тизменин ар бир элементинин экинчисинде, андан кийин функцияда пайда болуу санын эсептейт FILTER алардын ичинен мындай көрүнүштөр болбогонун гана тандайт. Бул структураны ороп Lambda жана анын негизинде ат менен аталган диапазонду түзүү, мисалы, ИЗДӨӨ БӨЛҮШҮ – биз динамикалык массив түрүндөгү эки тизмени салыштыруунун натыйжасын кайтарган ыңгайлуу функцияны алабыз:
Эгерде булак маалыматтар жөнөкөй эмес, бирок "акылдуу" таблицалар болсо, биздин функциябыз да көйгөйсүз иштей алат:
Дагы бир мисал, текстти 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. ," баштапкы "А" жок - бул MID функциясы менен аткарылат. Мурдагыдай эле, функция калгандардын сол жагынан биринчи символду алат (B) жана аны ага берилген текстте (Zkz n 125 рубль) бош сап менен алмаштырат - биз аралык натыйжа катары алабыз:
125 ру.
Кайталоо 3
Функция өзүн кайра чакырып, биринчи аргумент катары мурунку итерацияда тазалануучу тексттин калган бөлүгүн кабыл алат (Bsh zkz n 125 ru.), ал эми экинчи аргумент катары алынып салынган символдордун жыйындысы дагы бир символ менен кыскартылган. сол, башкача айтканда, «ВГДЕЭГЗИКЛМНОПРСТУФХТСЧШШЧЫЙЮЯ.», баштапкы «Б» жок. Андан кийин ал кайрадан бул топтомдон солдон (B) биринчи символду алат жана аны тексттен алып салат - биз алабыз:
sh zkz n 125 ru.
Жана башкалар - мен сиз идеяны аласыз деп үмүттөнөм. Ар бир итерацияда өчүрүлө турган символдордун тизмеси сол жактан кыскартылат жана биз топтомдон кийинки символду боштук менен издеп, алмаштырабыз.
Бардык символдор түгөнүп калганда, циклден чыгышыбыз керек болот - бул ролду жөн гана функция аткарат IF (ЭГЕР), анда биздин дизайн оролгон. Эгерде өчүрүлүүчү символдор калбаса (d=””), анда функция мындан ары өзүн чакырбай, жөн гана тазалана турган текстти (t өзгөрмө) акыркы түрүндө кайтарышы керек.
Клеткалардын рекурсивдүү итерациясы
Ошо сыяктуу эле, сиз берилген диапазондогу клеткалардын рекурсивдүү саналышын ишке ашыра аласыз. деп аталган ламбда функциясын түзгүбүз келет дейли АЛМАШТЫРУУ ТИЗМЕСИ берилген маалымдама тизмеси боюнча баштапкы тексттеги фрагменттерди дүңүнөн алмаштыруу үчүн. Натыйжа мындай болушу керек:
Ошол. биздин функцияда АЛМАШТЫРУУ ТИЗМЕСИ үч аргумент болот:
- иштетүү үчүн тексти бар клетка (булак дареги)
- издөөдөн издөө үчүн маанилери бар тилкенин биринчи уячасы
- издөөдөн алмаштыруу маанилери менен мамычанын биринчи уячасы
Функция каталогдо жогорудан ылдыйга чейин барып, сол тилкеден бардык опцияларды ырааттуу түрдө алмаштыруу керек Табуу оң тилкеден тиешелүү опцияларга өтүңүз алмаштыруу. Сиз муну төмөнкү рекурсивдүү lambda функциясы менен ишке ашыра аласыз:
Ар бир итерацияда ылдый жылдыруу стандарттуу Excel функциясы тарабынан ишке ашырылат DISPOSAL (OFSET), бул учурда үч аргументи бар – баштапкы диапазон, саптын жылышы (1) жана мамычанын жылышы (0).
Каталогдун аягына жеткенибиз менен (n = “”) биз рекурсияны бүтүрүшүбүз керек – биз өзүбүздү чакырууну токтотуп, t баштапкы тексттин өзгөрмөсүндөгү бардык алмаштыруулардан кийин топтолгон нерсени көрсөтөбүз.
Баары болду. Эч кандай татаал макрос же Power Query сурамдары жок – бүт тапшырма бир функция менен чечилет.
- Excelдин жаңы динамикалык массив функцияларын кантип колдонуу керек: ФИЛЬТР, СОРТ, UNIC
- SUBSTITUTE функциясы менен текстти алмаштыруу жана тазалоо
- VBAда макросторду жана колдонуучу аныктаган функцияларды (UDFs) түзүү