Mündəricat
Hal-hazırda Microsoft Excel-də Funksiya Sihirbazı pəncərəsi – düyməsi vasitəsilə əlçatan beş yüzə yaxın iş vərəqi funksiyası var fx düstur çubuğunda. Bu, çox layiqli dəstdir, lakin buna baxmayaraq, demək olar ki, hər bir istifadəçi gec-tez bu siyahıda ehtiyac duyduğu funksiyanı ehtiva etmədiyi bir vəziyyətlə qarşılaşır - sadəcə Excel-də olmadığı üçün.
İndiyə qədər bu problemi həll etməyin yeganə yolu makrolar idi, yəni öz istifadəçi tərəfindən təyin edilmiş funksiyanızı (UDF = İstifadəçi Təyin edilmiş Funksiya) Visual Basic-də yazmaq idi, bu, müvafiq proqramlaşdırma bacarıqları tələb edir və bəzən heç də asan deyil. Bununla belə, ən son Office 365 yeniləmələri ilə vəziyyət yaxşılığa doğru dəyişdi – Excel-ə xüsusi “sarğı” funksiyası əlavə edildi. LAMBDA. Onun köməyi ilə öz funksiyalarınızı yaratmaq vəzifəsi indi asanlıqla və gözəl şəkildə həll olunur.
Aşağıdakı misalda onun istifadə prinsipinə baxaq.
Çox güman ki, bildiyiniz kimi, Excel-də müəyyən bir tarix üçün günün, ayın, həftənin və ilin sayını təyin etməyə imkan verən bir neçə tarix təhlili funksiyası var. Amma nədənsə rübün sayını təyin edən funksiya yoxdur ki, bu da tez-tez ehtiyac duyulur, elə deyilmi? Gəlin bu nöqsanı aradan qaldıraq və yaradaq LAMBDA bu problemi həll etmək üçün yeni funksiyaya sahib olun.
Addım 1. Düsturu yazın
Başlayaq ki, əl ilə adi şəkildə bizə lazım olanı hesablayan bir vərəq hüceyrəsinə bir düstur yazacağıq. Dörddəbir sayı vəziyyətində, bu, məsələn, belə edilə bilər:
Addım 2. LAMBDA-da yekunlaşdırma və sınaq
İndi yeni LAMBDA funksiyasını tətbiq etmək və düsturumuzu ona bükmək vaxtıdır. Funksiya sintaksisi aşağıdakı kimidir:
=LAMBDA(Dəyişən 1; Dəyişən 2; … DəyişənN ; ifadə)
burada bir və ya bir neçə dəyişənin adları ilk sıralanır və sonuncu arqument həmişə onlardan istifadə edən düstur və ya hesablanmış ifadədir. Dəyişən adları xana ünvanlarına bənzəməməli və nöqtələrdən ibarət olmamalıdır.
Bizim vəziyyətimizdə yalnız bir dəyişən olacaq - rübün sayını hesabladığımız tarix. Bunun üçün dəyişəni çağıraq, deyək ki, d. Sonra düsturumuzu bir funksiyaya sarırıq LAMBDA və orijinal A2 xanasının ünvanını uydurma dəyişən adı ilə əvəz edərək, əldə edirik:
Diqqət yetirin ki, belə bir transformasiyadan sonra düsturumuz (əslində, düzgündür!) səhv yaratmağa başladı, çünki indi A2 xanasından orijinal tarix ona köçürülmür. Test və güvən üçün, funksiyadan sonra onları əlavə etməklə ona arqumentlər ötürə bilərsiniz LAMBDA mötərizədə:
Addım 3. Ad yaradın
İndi asan və əyləncəli hissəyə. açırıq Adı meneceri nişanı formula (Formullar — Ad Meneceri) və düymə ilə yeni ad yaradın Yaratmaq (Yarat). Gələcək funksiyamız üçün bir ad tapın və daxil edin (məsələn, Nomkvartala) və sahədə əlaqə (İstinad) düstur çubuğundan diqqətlə kopyalayın və funksiyamızı yapışdırın LAMBDA, yalnız sonuncu arqument olmadan (A2):
Hər şey. Tıkladıqdan sonra OK yaradılmış funksiya bu iş kitabının istənilən vərəqindəki istənilən xanada istifadə edilə bilər:
Digər kitablarda istifadə edin
LAMBDA və dinamik massivlər
Funksiya ilə yaradılmış xüsusi funksiyalar LAMBDA yeni dinamik massivlər və onların funksiyaları ilə işi uğurla dəstəkləyir (FILTER, UNİK, GRADE) 2020-ci ildə Microsoft Excel-ə əlavə edildi.
Deyək ki, biz iki siyahını müqayisə edən və onlar arasındakı fərqi – ikinci siyahıda olmayan birinci siyahıdakı elementləri qaytaran yeni istifadəçi tərəfindən müəyyən edilmiş funksiya yaratmaq istəyirik. Həyatın işi, elə deyilmi? Əvvəllər bunun üçün a la funksiyalarından istifadə edirdilər VPR (BAXIN), və ya Pivot Cədvəllər və ya Power Query sorğuları. İndi bir formula ilə edə bilərsiniz:
İngilis versiyasında belə olacaq:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Burada funksiya COUNTIF birinci siyahının hər bir elementinin ikincidə baş vermə sayını, sonra isə funksiyanı hesablayır FILTER onlardan yalnız bu hadisələri olmayanları seçir. Bu quruluşu bükərək LAMBDA və onun əsasında bir adla adlandırılmış diapazon yaratmaq, məsələn, AXTAR PAYLAŞIM – dinamik massiv şəklində iki siyahının müqayisəsinin nəticəsini qaytaran rahat funksiya alacağıq:
Mənbə məlumatları adi deyil, "ağıllı" cədvəllərdirsə, funksiyamız da problemsiz öhdəsindən gələcəkdir:
Başqa bir misal, mətni XML-ə çevirərək dinamik şəkildə bölmək və sonra bu yaxınlarda təhlil etdiyimiz FILTER.XML funksiyasından istifadə edərək onu xana-xana təhlil etməkdir. Bu mürəkkəb formulun hər dəfə əl ilə təkrarlanmaması üçün onu LAMBDA-ya bükmək və onun əsasında dinamik diapazon yaratmaq, yəni yeni yığcam və rahat funksiya yaratmaq, məsələn, RAZDTEXT:
Bu funksiyanın ilk arqumenti mənbə mətni olan xana, ikincisi isə ayırıcı simvol olacaq və nəticəni üfüqi dinamik massiv şəklində qaytaracaq. Funksiya kodu aşağıdakı kimi olacaq:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML("
Nümunələrin siyahısı sonsuzdur – tez-tez eyni uzun və çətin düsturları daxil etməli olduğunuz istənilən vəziyyətdə LAMBDA funksiyası həyatı nəzərəçarpacaq dərəcədə asanlaşdıracaq.
Simvolların rekursiv sadalanması
Əvvəlki bütün nümunələr LAMBDA funksiyasının yalnız bir, ən bariz tərəfini göstərdi – onun içindəki uzun düsturları bükmək və onların daxil edilməsini sadələşdirmək üçün “sarğı” kimi istifadə edilməsi. Əslində, LAMBDA-nın onu demək olar ki, tam hüquqlu proqramlaşdırma dilinə çevirən başqa, daha dərin tərəfi var.
Fakt budur ki, LAMBDA funksiyalarının əsas əhəmiyyətli xüsusiyyəti onları həyata keçirmək qabiliyyətidir rekursiya – hesablamaların məntiqi, hesablama prosesində funksiya özünü çağırdıqda. Vərdişdən ürpertici görünə bilər, lakin proqramlaşdırmada rekursiya adi bir şeydir. Hətta Visual Basic-də makrolarda da onu həyata keçirə bilərsiniz və indi gördüyünüz kimi Excel-ə gəlib çatmışdır. Bu texnikanı praktik bir nümunə ilə anlamağa çalışaq.
Tutaq ki, mənbə mətndən bütün verilmiş simvolları siləcək istifadəçi tərəfindən müəyyən edilmiş funksiya yaratmaq istəyirik. Belə bir funksiyanın faydalılığını, məncə, sübut etməyə ehtiyac yoxdur - onun köməyi ilə zibillənmiş giriş məlumatlarını təmizləmək çox rahat olardı, elə deyilmi?
Lakin əvvəlki, rekursiv olmayan nümunələrlə müqayisədə bizi iki çətinlik gözləyir.
- Onun kodunu yazmağa başlamazdan əvvəl funksiyamız üçün bir ad tapmalı olacağıq, çünki onda bu ad artıq funksiyanın özünü çağırmaq üçün istifadə olunacaq.
- Belə bir rekursiv funksiyanı xanaya daxil etmək və LAMBDA-dan sonra mötərizədə arqumentləri göstərməklə (əvvəllər etdiyimiz kimi) onu sazlamaq işləməyəcək. Dərhal "sıfırdan" bir funksiya yaratmalı olacaqsınız Adı meneceri (Ad Meneceri).
Gəlin funksiyamızı çağıraq, məsələn, TƏMİZLƏMƏ və biz onun iki arqumentinin olmasını istərdik – təmizlənəcək mətn və mətn sətri kimi xaric edilmiş simvolların siyahısı:
Gəlin, əvvəllər etdiyimiz kimi, tabda yaradaq formula в Ad meneceri adlı diapazon, onu CLEAR adlandırın və sahəyə daxil edin Silsilə aşağıdakı tikinti:
=LAMBDA(t;d;ƏGƏR(d=””;t;CLEAR(ƏVƏZİNƏ(t;SOL(d);"”);ORTA(d;2;255))))
Burada t dəyişəni təmizlənəcək orijinal mətn, d isə silinəcək simvolların siyahısıdır.
Hamısı belə işləyir:
Təkrarlama 1
SUBSTITUTE(t;LEFT(d);”” fraqmenti, təxmin etdiyiniz kimi, mənbə mətndə silinəcək d dəstinin sol simvolundan birinci simvolu t boş mətn sətri ilə əvəz edir, yəni “ A”. Aralıq nəticə olaraq alırıq:
Vsh zkz n 125 rubl.
Təkrarlama 2
Sonra funksiya özünü çağırır və giriş kimi (birinci arqument) əvvəlki addımda təmizləndikdən sonra qalanı alır və ikinci arqument birincidən deyil, ikinci simvoldan, yəni “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA”dan başlayaraq xaric edilmiş simvollar sətridir. ,” ilkin “A” olmadan – bu MID funksiyası ilə edilir. Əvvəllər olduğu kimi, funksiya qalanların (B) solundan birinci simvolu götürür və ona verilmiş mətndə (Zkz n 125 rubl) boş bir sətirlə əvəz edir - ara nəticə olaraq alırıq:
125 ru.
Təkrarlama 3
Funksiya əvvəlki iterasiyada təmizlənəcək mətndən qalanı birinci arqument kimi qəbul edərək özünü yenidən çağırır (Bsh zkz n 125 ru.), İkinci arqument kimi isə xaric edilmiş simvollar dəsti daha bir simvol ilə kəsilir. sol, yəni ilkin “B” olmadan “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”. Sonra yenidən bu dəstdən soldan (B) birinci simvolu götürür və onu mətndən çıxarır – əldə edirik:
sh zkz n 125 ru.
Və s. - Ümid edirəm ki, fikrinizi başa düşəcəksiniz. Hər təkrarlama ilə silinəcək simvolların siyahısı solda kəsiləcək və biz dəstdən növbəti simvolu boşluqla axtarıb əvəz edəcəyik.
Bütün simvollar tükəndikdə, biz dövrədən çıxmalıyıq - bu rolu sadəcə funksiya yerinə yetirir IF (Əgər), dizaynımızın bükülmüş olduğu. Əgər silinəcək simvol qalmayıbsa (d=””), onda funksiya artıq özünü çağırmamalı, sadəcə olaraq təmizlənəcək mətni (t dəyişənini) son formada qaytarmalıdır.
Hüceyrələrin rekursiv iterasiyası
Eynilə, siz verilmiş diapazonda hüceyrələrin rekursiv sadalamasını həyata keçirə bilərsiniz. Fərz edək ki, biz adlı lambda funksiyası yaratmaq istəyirik Əvəz SİYAHISI verilmiş istinad siyahısına uyğun olaraq mənbə mətndəki fraqmentlərin topdansatış dəyişdirilməsi üçün. Nəticə belə görünməlidir:
Bunlar. bizim funksiyamızda Əvəz SİYAHISI üç arqument olacaq:
- emal ediləcək mətni olan hüceyrə (mənbə ünvanı)
- axtarışdan axtarmaq üçün dəyərləri olan sütunun ilk xanası
- sütunun ilk xanası axtarışdan əvəzedici dəyərlərlə
Funksiya kataloqda yuxarıdan aşağıya doğru getməli və sol sütundakı bütün variantları ardıcıllıqla əvəz etməlidir Tapmaq sağ sütundan müvafiq seçimlərə keçin Əvəz. Bunu aşağıdakı rekursiv lambda funksiyası ilə həyata keçirə bilərsiniz:
Hər iterasiyada aşağı sürüşdürmə standart excel funksiyası ilə həyata keçirilir SƏRƏNCAM (OFSET), bu halda üç arqumentə malikdir - orijinal diapazon, sıra sürüşməsi (1) və sütun sürüşməsi (0).
Yaxşı, kataloqun sonuna çatan kimi (n = “”) rekursiyanı bitirməliyik - özümüzə zəng etməyi dayandırırıq və mənbə mətn dəyişəni t-də bütün dəyişdirmələrdən sonra yığılanları göstəririk.
Hamısı budur. Heç bir çətin makro və ya Power Query sorğuları yoxdur – bütün tapşırıq bir funksiya ilə həll olunur.
- Excel-in yeni dinamik massiv funksiyalarından necə istifadə etmək olar: FILTER, SORT, UNIC
- SUBSTITUTE funksiyası ilə mətnin dəyişdirilməsi və təmizlənməsi
- VBA-da makroların və istifadəçi tərəfindən təyin edilmiş funksiyaların (UDF) yaradılması