Мундариҷа
Дар айни замон, Microsoft Excel дорои қариб панҷсад вазифаи варақаи корӣ мебошад, ки тавассути равзанаи Устоди Функсия – тугма дастрас аст fx дар сатри формула. Ин маҷмӯи хеле хуб аст, аммо, бо вуҷуди ин, қариб ҳар як корбар дер ё зуд ба вазъият дучор мешавад, ки дар ин рӯйхат функсияи ба ӯ лозимиро дар бар намегирад - танҳо аз сабаби он ки он дар Excel нест.
То кунун ягона роҳи ҳалли ин мушкилот макросҳо буд, яъне навиштани функсияи аз ҷониби корбар муайяншуда (UDF = Функсияи корбар муайяншуда) дар Visual Basic, ки малакаҳои мувофиқи барномасозиро талаб мекунад ва баъзан умуман осон нест. Бо вуҷуди ин, бо навсозиҳои охирини Office 365, вазъ ба таври беҳтар тағйир ёфт - ба Excel функсияи махсуси "печанда" илова карда шуд ЛАМБДА. Бо ёрии он, вазифаи эҷоди функсияҳои шахсии шумо ҳоло ба осонӣ ва зебо ҳал карда мешавад.
Принсипи истифодаи онро дар мисоли зерин дида мебароем.
Тавре ки шумо эҳтимол медонед, Excel дорои якчанд функсияҳои таҳлили сана мебошад, ки ба шумо имкон медиҳад шумораи рӯз, моҳ, ҳафта ва солро барои санаи додашуда муайян кунед. Аммо бо кадом сабабҳо ягон функсияе нест, ки шумораи кварталро муайян кунад, ки он ҳам аксар вақт лозим аст, дуруст? Биёед ин камбудро ислох кунем ва бо ЛАМБДА Функсияи нави худро барои ҳалли ин мушкилот.
Қадами 1. Формуларо нависед
Биёед аз он оғоз кунем, ки ба таври дастӣ ба таври муқаррарӣ мо формуларо дар чашмаки варақ менависем, ки он чизеро, ки ба мо лозим аст, ҳисоб мекунад. Дар мавриди рақами семоҳа, ин метавонад анҷом дода шавад, масалан, ин тавр:
Қадами 2. Пур кардани LAMBDA ва санҷиш
Ҳоло вақти он расидааст, ки функсияи нави LAMBDA-ро истифода барем ва формулаи моро дар он печонед. Синтаксиси функсия чунин аст:
=ЛАМБДА(Тағйирёбанда 1; Тағйирёбанда 2; … Тағйирёбанда Н ; Эзоҳ)
ки дар он номҳои як ё якчанд тағирёбанда аввал номбар шудаанд ва далели охирин ҳамеша формула ё ифодаи ҳисобшуда мебошад, ки онҳоро истифода мебарад. Номҳои тағирёбанда набояд ба суроғаҳои чашмак монанд бошанд ва дорои нуқтаҳо бошанд.
Дар ҳолати мо, танҳо як тағирёбанда хоҳад буд - санае, ки мо рақами семоҳаро ҳисоб мекунем. Биёед тағирёбандаро барои он даъват кунем, бигӯем, d. Сипас формулаи моро дар функсия печонед ЛАМБДА ва суроғаи ячейкаи аслии A2 бо номи тағирёбандаи сохта иваз карда, мо мегирем:
Лутфан таваҷҷӯҳ намоед, ки пас аз чунин табдил, формулаи мо (воқеан, дуруст!) ба хатогӣ шурӯъ кард, зеро ҳоло санаи аслӣ аз ячейкаи A2 ба он интиқол дода намешавад. Барои санҷиш ва эътимод, шумо метавонед далелҳоро ба он тавассути илова кардани онҳо пас аз функсия интиқол диҳед ЛАМБДА дар қавс:
Қадами 3. Эҷоди ном
Акнун барои қисми осон ва шавқовар. Мо мекушоем Менеҷери ном ҷадвали формула (Формулаҳо - Менеҷери ном) ва бо тугма номи нав эҷод кунед сохтан (Эҷод кардан). Пешниҳод кунед ва номеро барои функсияи ояндаи мо ворид кунед (масалан, Номквартала) ва дар сахро Link (Истинод) Бодиққат аз сатри формула нусхабардорӣ кунед ва функсияи моро часбонед ЛАМБДА, танҳо бе далели охирин (A2):
Ҳама чиз. Пас аз пахш кардан OK Функсияи сохташударо дар ҳама ячейкаҳои ҳар варақи ин китоби корӣ истифода бурдан мумкин аст:
Дар китобҳои дигар истифода баред
LAMBDA ва массивҳои динамикӣ
Функсияҳои фармоишӣ бо функсия сохта шудаанд ЛАМБДА бомуваффақият дастгирӣ кардани кор бо массивҳои нави динамикӣ ва вазифаҳои онҳо (филтр, УНИК, синфи) дар соли 2020 ба Microsoft Excel илова карда шуд.
Фарз мекунем, ки мо мехоҳем як функсияи нави аз ҷониби корбар муайяншуда эҷод кунем, ки ду рӯйхатро муқоиса карда, фарқияти байни онҳоро баргардонад - он унсурҳои аз рӯйхати аввал, ки дар дуюм нестанд. Кори ҳаёт, ҳамин тавр не? Пештар, барои ин онҳо ё функсияҳоро истифода мебурданд VPR (ИНТИЗОР), ё Ҷадвалҳои ҷамъоварӣ ё дархостҳои Power Query. Акнун шумо метавонед бо як формула кор кунед:
Дар версияи англисӣ он хоҳад буд:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Дар ин ҷо функсия COUNTIF шумораи рӯйдодҳои ҳар як унсури рӯйхати якумро дар дуюм ва баъд функсияро ҳисоб мекунад филтр танҳо онҳоеро интихоб мекунад, ки ин ҳодисаҳоро надоштанд. Бо печонидани ин сохтор дар ЛАМБДА ва сохтани диапазони номбаршуда дар асоси он бо ном, масалан, ТАҚСИМИ Ҷустуҷӯ – мо функсияи қулай мегирем, ки натиҷаи муқоисаи ду рӯйхатро дар шакли массиви динамикӣ бармегардонад:
Агар маълумоти манбаъ оддӣ нест, балки ҷадвалҳои "ақл" бошад, функсияи мо низ бе мушкилот мубориза мебарад:
Мисоли дигар ба таври динамикӣ тақсим кардани матн тавассути табдил додани он ба XML ва сипас бо истифода аз функсияи FILTER.XML, ки мо ба наздикӣ таҳлил кардем, ячейка ба ячейка ҷудо кардан аст. Барои он ки ин формулаи мураккаб ҳар дафъа дастӣ дубора тавлид нашавад, онро дар LAMBDA печондан ва дар асоси он диапазони динамикӣ, яъне функсияи нави паймон ва қулай эҷод кардан осонтар мешавад, ки онро масалан RAZDTEXT номгузорӣ мекунад:
Аргументи якуми ин функсия чашмак бо матни ибтидоӣ ва дуюмаш аломати ҷудокунанда хоҳад буд ва он натиҷаро дар шакли массиви динамикии уфуқӣ бармегардонад. Рамзи функсия чунин хоҳад буд:
=ЛАМБДА(t;d; TRANSPOSE(FILTER.XML("
Рӯйхати мисолҳо беохир аст - дар ҳама ҳолатҳое, ки шумо бояд як формулаи дароз ва душворро ворид кунед, функсияи LAMBDA ҳаётро ба таври назаррас осонтар мекунад.
Рӯйхати рекурсивии аломатҳо
Ҳама мисолҳои қаблӣ танҳо як паҳлӯи равшантарини функсияи LAMBDA-ро нишон доданд - истифодаи он ҳамчун "печанда" барои печонидани формулаҳои дароз дар он ва содда кардани вуруди онҳо. Дарвоқеъ, LAMBDA паҳлӯи дигаре дорад, ки хеле амиқтар аст, ки онро қариб ба забони мукаммали барномасозӣ табдил медиҳад.
Далели он аст, ки як хусусияти муҳими функсияҳои LAMBDA қобилияти татбиқи онҳо дар он аст рекурсия – мантиқи ҳисобҳо, вақте ки дар ҷараёни ҳисобкунӣ функсия худашро даъват мекунад. Аз рӯи одат, он метавонад даҳшатнок садо диҳад, аммо дар барномасозӣ, рекурсия як чизи маъмулист. Ҳатто дар макросҳои Visual Basic, шумо метавонед онро амалӣ кунед ва ҳоло, тавре ки мебинед, он ба Excel омадааст. Биёед кӯшиш кунем, ки ин техникаро бо мисоли амалӣ фаҳмем.
Фарз мекунем, ки мо мехоҳем функсияи аз ҷониби корбар муайяншуда эҷод кунем, ки ҳамаи аломатҳои додашударо аз матни сарчашма хориҷ кунад. Манфиатнокии чунин функсия, ба фикрам, ба шумо исбот кардан лозим нест - бо ёрии он тоза кардани маълумотҳои воридшуда хеле қулай мебуд, дуруст?
Аммо нисбат ба мисолхои пештараи гайрирекурсивй моро ду душворй интизор аст.
- Пеш аз оғози навиштани коди он мо бояд номеро барои функсияи худ пайдо кунем, зеро дар он ин ном аллакай барои даъват кардани худи функсия истифода мешавад.
- Ворид кардани чунин функсияи рекурсивӣ ба ячейка ва ислоҳи он тавассути нишон додани аргументҳо дар қавс пас аз LAMBDA (чунон ки мо қаблан карда будем) кор намекунад. Шумо бояд функсияро фавран "аз сифр" эҷод кунед Менеҷери ном (Мудири ном).
Биёед функсияи худро, гӯем, CLEAN меномем ва мо мехоҳем, ки он ду аргумент дошта бошад - матни тоза кардашаванда ва рӯйхати аломатҳои хориҷшуда ҳамчун сатри матн:
Биёед, тавре ки қаблан карда будем, дар ҷадвал эҷод кунем формула в Менеҷери ном диапазони номбаршуда, онро CLEAR номида, ба майдон ворид кунед диапазон сохтмони зерин:
=ЛАМБДА(t;d;АГАР(d=””;t;ТОЗА(ҶАВОБ (t;ЧАП(д);"”);МИИД(д;2;255))))
Дар ин ҷо тағирёбандаи t матни аслӣ аст, ки бояд тоза карда шавад ва d рӯйхати аломатҳои ҳазфшаванда аст.
Ҳамааш чунин кор мекунад:
Такрори 1
Фрагменти SUBSTITUTE(t;LEFT(d);””), тавре ки шумо тахмин карда метавонед, аломати аввалро аз аломати чапи маҷмӯи d, ки дар матни сарчашма t ҳазф карда мешавад, бо сатри матнии холӣ иваз мекунад, яъне “ А». Дар натиҷаи мобайнӣ мо ба даст меорем:
Vsh zkz n 125 рубл.
Такрори 2
Сипас, функсия худро даъват мекунад ва ҳамчун вуруд (аргументи аввал) он чизеро, ки пас аз тозакунӣ дар қадами қаблӣ боқӣ мондааст, мегирад ва аргументи дуюм сатри аломатҳои хориҷшуда аст, ки на аз аломати якум, балки аз аломати дуюм оғоз мешавад, яъне “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHYYYYYA. ," бе "А" ибтидоӣ - ин аз ҷониби функсияи MID анҷом дода мешавад. Мисли пештара, функсия аломати якумро аз тарафи чапи боқимонда (B) мегирад ва онро дар матни ба он додашуда (Zkz n 125 рубл) бо сатри холӣ иваз мекунад - мо ҳамчун натиҷаи мобайнӣ мегирем:
125 руб.
Такрори 3
Функсия худро боз даъват мекунад ва ҳамчун аргументи аввал он чизеро, ки аз матн дар итератсияи қаблӣ тоза карда мешавад, мегирад (Bsh zkz n 125 ru.), Ва ҳамчун далели дуюм, маҷмӯи аломатҳои хориҷшуда бо як аломати дигар бурида мешавад. чап, яъне «ВГДЕЕГЗИКЛМНОПРСТУФХТСЧШЩЙЙЙЮЯ.», бе «Б» ибтидой. Он гоҳ бори дигар аломати аввалро аз чап (B) аз ин маҷмӯа мегирад ва онро аз матн хориҷ мекунад - мо мегирем:
ш зкз н 125 руб.
Ва ғайра - ман умедворам, ки шумо идеяро пайдо мекунед. Ҳангоми ҳар як такрор, рӯйхати аломатҳои ҳазфшуда аз тарафи чап бурида мешавад ва мо аломати навбатиро аз маҷмӯи бо холӣ ҷустуҷӯ ва иваз мекунем.
Вақте ки ҳамаи аломатҳо тамом мешаванд, мо бояд аз ҳалқа берун шавем - ин нақш танҳо аз ҷониби функсия иҷро карда мешавад IF (АГАР), ки дар он тарҳи мо печонида шудааст. Агар барои нест кардани аломатҳо боқӣ намонанд (d=””), он гоҳ функсия дигар бояд худро даъват накунад, балки бояд матни тозашавандаро (тағйирёбандаи t) дар шакли ниҳоии худ баргардонад.
Итератсияи рекурсивии ҳуҷайраҳо
Ба ҳамин монанд, шумо метавонед як рӯйхати рекурсивии ҳуҷайраҳоро дар диапазони додашуда амалӣ кунед. Фарз мекунем, ки мо мехоҳем функсияи ламбдаро бо номи худ созем Рӯйхати Иваз барои яклухт иваз кардани порчаҳои матни сарчашма мувофиқи рӯйхати истинодҳои додашуда. Натиҷа бояд чунин бошад:
Онхое. дар вазифаи мо Рӯйхати Иваз се далел хоҳад буд:
- ячейка бо матн барои коркард (суроғаи манбаъ)
- чашмаки якуми сутун бо арзишҳо барои ҷустуҷӯ аз ҷустуҷӯ
- чашмаки якуми сутун бо арзишҳои иваз аз ҷустуҷӯ
Функсия бояд дар директория аз боло ба поён равад ва ҳама имконоти сутуни чапро пай дар пай иваз кунад Ёфтан ба имконоти мувофиқ аз сутуни рост Тағирдиҳӣ. Шумо метавонед инро бо функсияи лямбда рекурсивии зерин амалӣ кунед:
Гузариш ба поён дар ҳар як такрор аз ҷониби функсияи стандартии Excel амалӣ карда мешавад ИСТИФОДА (ОФСЕТ), ки дар ин ҳолат се далел дорад - диапазони аслӣ, ивази сатр (1) ва ивази сутун (0).
Хуб, вақте ки мо ба охири директория мерасем (n = ""), мо бояд рекурсияро хотима диҳем - мо занг заданро қатъ мекунем ва пас аз ҳама ивазкунӣ дар тағирёбандаи матни сарчашма t ҷамъшударо нишон медиҳем.
Ҳамааш ҳамин. Ҳеҷ макросҳои душвор ё дархостҳои Power Query нест - тамоми вазифа бо як функсия ҳал карда мешавад.
- Чӣ тавр истифода бурдани функсияҳои нави массиви динамикии Excel: FILTER, SORT, UNIC
- Иваз ва тоза кардани матн бо функсияи SUBSTITUTE
- Эҷоди макросҳо ва функсияҳои аз ҷониби корбар муайяншуда (UDFs) дар VBA