Mundarija
Ayni paytda Microsoft Excel-da "Funksiya ustasi" oynasi - tugmasi orqali foydalanish mumkin bo'lgan deyarli besh yuzga yaqin ish varag'i funksiyalari mavjud fx formulalar qatorida. Bu juda munosib to'plam, ammo shunga qaramay, deyarli har bir foydalanuvchi ertami-kechmi ushbu ro'yxatda unga kerak bo'lgan funksiya mavjud bo'lmagan vaziyatga duch keladi - shunchaki Excelda bo'lmagani uchun.
Hozirgacha bu muammoni hal qilishning yagona yo'li makroslar edi, ya'ni o'z foydalanuvchi tomonidan belgilangan funksiyani (UDF = User Defined Function) Visual Basic dasturida yozish edi, bu tegishli dasturlash ko'nikmalarini talab qiladi va ba'zida umuman oson emas. Biroq, Office 365-ning so'nggi yangilanishlari bilan vaziyat yaxshi tomonga o'zgardi - Excelga maxsus "o'rash" funksiyasi qo'shildi. LAMBDA. Uning yordami bilan o'z funktsiyalaringizni yaratish vazifasi endi oson va chiroyli tarzda hal qilinadi.
Quyidagi misolda uni qo'llash tamoyilini ko'rib chiqamiz.
Sizga ma'lumki, Excelda sana uchun kun, oy, hafta va yil sonini aniqlash imkonini beruvchi bir nechta sanani tahlil qilish funksiyalari mavjud. Lekin negadir chorak sonini aniqlaydigan funksiya yo'q, bu ham tez-tez kerak, to'g'rimi? Keling, bu kamchilikni tuzatamiz va yaratamiz LAMBDA ushbu muammoni hal qilish uchun yangi funktsiyaga ega.
1-qadam. Formulani yozing
Keling, odatdagidek qo'lda biz kerakli narsani hisoblaydigan varaq katakchasiga formula yozamiz. Chorak raqami bo'lsa, buni, masalan, shunday qilish mumkin:
2-qadam. LAMBDA va sinovdan o'tkazish
Endi yangi LAMBDA funksiyasini qo'llash va formulamizni unga o'rash vaqti keldi. Funktsiya sintaksisi quyidagicha:
=LAMBDA(O'zgaruvchi 1; O'zgaruvchi 2; … OʻzgaruvchiN ; Fikr)
bu erda bir yoki bir nechta o'zgaruvchilarning nomlari birinchi bo'lib ro'yxatga olinadi va oxirgi argument har doim formula yoki ulardan foydalanadigan hisoblangan ifodadir. O'zgaruvchilar nomlari hujayra manzillariga o'xshamasligi va nuqtalardan iborat bo'lmasligi kerak.
Bizning holatda, faqat bitta o'zgaruvchi bo'ladi - biz chorak sonini hisoblagan sana. Buning uchun o'zgaruvchini chaqiramiz, deylik, d. Keyin formulamizni funktsiyaga o'rash LAMBDA va asl A2 katak manzilini xayoliy o'zgaruvchi nomi bilan almashtirib, biz quyidagilarni olamiz:
E'tibor bering, bunday o'zgartirishdan so'ng bizning formulamiz (aslida, to'g'ri!) xatoga yo'l qo'ya boshladi, chunki endi A2 katakchasidagi asl sana unga o'tkazilmaydi. Sinov va ishonch uchun siz funktsiyadan keyin ularni qo'shish orqali unga argumentlarni yuborishingiz mumkin LAMBDA qavs ichida:
3-qadam. Ism yarating
Endi oson va qiziqarli qismga. Biz ochamiz Ism menejeri tab formula (Formulalar - ism menejeri) va tugma yordamida yangi nom yarating yaratish (Yaratmoq). Kelajakdagi funksiyamiz uchun nom toping va kiriting (masalan, Nomkvartala) va dalada aloqa (Malumot) formulalar qatoridan diqqat bilan nusxa oling va funksiyamizni joylashtiring LAMBDA, faqat oxirgi argumentsiz (A2):
Hamma narsa. Bosgandan keyin OK yaratilgan funksiyadan ushbu kitobning istalgan varaqidagi istalgan katakda foydalanish mumkin:
Boshqa kitoblarda foydalaning
LAMBDA va dinamik massivlar
Funktsiya bilan yaratilgan maxsus funktsiyalar LAMBDA yangi dinamik massivlar va ularning funktsiyalari bilan ishlashni muvaffaqiyatli qo'llab-quvvatlash (Filtrni, UNIK, sinf) 2020 yilda Microsoft Excel-ga qo'shildi.
Aytaylik, biz ikkita ro'yxatni solishtiradigan va ular orasidagi farqni - ikkinchi ro'yxatda bo'lmagan birinchi ro'yxatdagi elementlarni qaytaradigan yangi foydalanuvchi tomonidan belgilangan funksiya yaratmoqchimiz. Hayotning ishi, shunday emasmi? Ilgari buning uchun ular a la funksiyasidan foydalanganlar VPR (KO'RISH), yoki PivotTables yoki Power Query soʻrovlari. Endi siz bitta formula bilan qilishingiz mumkin:
Inglizcha versiyada u quyidagicha bo'ladi:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Mana funktsiya COUNTIF birinchi ro'yxatning har bir elementining ikkinchi ro'yxatdagi takrorlanish sonini, keyin esa funksiyani hisoblaydi Filtrni ulardan faqat ushbu hodisalarga ega bo'lmaganlarni tanlaydi. Ushbu tuzilmani o'rash orqali LAMBDA va unga asoslangan nom bilan nomlangan diapazon yaratish, masalan, QIDIRISH TARQATISH - dinamik massiv ko'rinishida ikkita ro'yxatni taqqoslash natijasini qaytaradigan qulay funktsiyani olamiz:
Agar manba ma'lumotlari oddiy emas, balki "aqlli" jadvallar bo'lsa, bizning funktsiyamiz ham muammosiz ishlaydi:
Yana bir misol, biz yaqinda tahlil qilgan FILTER.XML funksiyasidan foydalanib, matnni XML ga aylantirish orqali dinamik ravishda ajratish va keyin uni katakchaga ajratishdir. Ushbu murakkab formulani har safar qo'lda takrorlamaslik uchun uni LAMBDA-ga o'rash va unga asoslangan dinamik diapazonni yaratish, ya'ni yangi ixcham va qulay funktsiyani yaratish, masalan, RAZDTEXT:
Ushbu funktsiyaning birinchi argumenti manba matni bo'lgan katak, ikkinchisi esa ajratuvchi belgi bo'lib, natijani gorizontal dinamik massiv ko'rinishida qaytaradi. Funktsiya kodi quyidagicha bo'ladi:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML("
Misollar ro'yxati cheksizdir - tez-tez bir xil uzoq va noqulay formulani kiritishingiz kerak bo'lgan har qanday vaziyatda LAMBDA funktsiyasi hayotni sezilarli darajada osonlashtiradi.
Belgilarni rekursiv sanash
Oldingi barcha misollar LAMBDA funksiyasining faqat bitta, eng ravshan tomonini ko'rsatdi - undan uzun formulalarni o'rash va ularni kiritishni soddalashtirish uchun "o'rash" sifatida foydalanish. Darhaqiqat, LAMBDA-ning boshqa, ancha chuqurroq tomoni bor, bu uni deyarli to'liq dasturlash tiliga aylantiradi.
Gap shundaki, LAMBDA funktsiyalarining tubdan muhim xususiyati ularni amalga oshirish qobiliyatidir rekursiya – hisob-kitoblar mantig‘i, hisoblash jarayonida funksiya o‘zini chaqirganda. Odatga ko'ra, bu dahshatli tuyulishi mumkin, ammo dasturlashda rekursiya odatiy holdir. Hatto Visual Basic-dagi makroslarda ham siz uni amalga oshirishingiz mumkin va endi, ko'rib turganingizdek, u Excelga keldi. Keling, ushbu texnikani amaliy misol bilan tushunishga harakat qilaylik.
Aytaylik, biz barcha berilgan belgilarni manba matnidan olib tashlaydigan foydalanuvchi tomonidan belgilangan funksiya yaratmoqchimiz. Menimcha, bunday funktsiyaning foydaliligini isbotlashning hojati yo'q - uning yordami bilan kirlangan ma'lumotlarni tozalash juda qulay bo'lar edi, shunday emasmi?
Biroq, oldingi, rekursiv bo'lmagan misollar bilan solishtirganda, bizni ikkita qiyinchilik kutmoqda.
- Funksiyamizning kodini yozishni boshlashdan oldin uning nomini topishimiz kerak bo'ladi, chunki unda bu nom allaqachon funksiyaning o'zini chaqirish uchun ishlatiladi.
- Hujayraga bunday rekursiv funksiyani kiritish va LAMBDA dan keyin qavs ichida argumentlarni ko'rsatish orqali uni tuzatish (avvalgi kabi) ishlamaydi. Siz darhol "noldan" funktsiyani yaratishingiz kerak bo'ladi Ism menejeri (Ism menejeri).
Keling, funksiyamizni CLEAN deb nomlaymiz va biz uning ikkita argumentiga ega bo'lishini xohlaymiz - tozalanadigan matn va matn qatori sifatida chiqarib tashlangan belgilar ro'yxati:
Keling, avvalroq qilganimizdek, yorliqda yarataylik formula в Ism menejeri nomli diapazon, uni CLEAR deb nomlang va maydonga kiriting Diapazon quyidagi qurilish:
=LAMBDA(t;d;AGAR(d=””;t;TOZLASH(O'RNISH(t;CHAP(d);”");MID(d;2;255))))
Bu erda t o'zgaruvchisi tozalanishi kerak bo'lgan asl matn, d esa o'chirilishi kerak bo'lgan belgilar ro'yxati.
Hammasi shunday ishlaydi:
takrorlash 1
SUBSTITUTE(t;LEFT(d);”” fragmenti, siz taxmin qilganingizdek, dastlabki matnda o‘chiriladigan d to‘plamining chap belgisidagi birinchi belgini t bo‘sh matn qatoriga almashtiradi, ya’ni “” ni olib tashlaydi. A”. Oraliq natija sifatida biz quyidagilarni olamiz:
Vsh zkz n 125 rubl.
takrorlash 2
Keyin funktsiya o'zini chaqiradi va kirish sifatida (birinchi argument) oldingi bosqichda tozalashdan keyin qolgan narsalarni oladi va ikkinchi argument birinchidan emas, balki ikkinchi belgidan boshlanadigan chiqarib tashlangan belgilar qatoridir, ya'ni “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," boshlang'ich "A"siz - bu MID funktsiyasi tomonidan amalga oshiriladi. Avvalgidek, funktsiya qolganlarning chap tomonidagi birinchi belgini oladi (B) va uni unga berilgan matnda (Zkz n 125 rubl) bo'sh satr bilan almashtiradi - biz oraliq natija sifatida olamiz:
125 rubl.
takrorlash 3
Funktsiya o'zini qayta chaqiradi va birinchi argument sifatida oldingi iteratsiyada tozalanishi kerak bo'lgan matnning qolgan qismini oladi (Bsh zkz n 125 ru.), va ikkinchi argument sifatida chiqarib tashlangan belgilar to'plami yana bitta belgi bilan qisqartiriladi. chap, ya'ni "VGDEEGZIKLMNOPRSTUFHTSCHSHCHYYYYUYA.", boshlang'ich "B"siz. Keyin yana ushbu to'plamning chap tomonidagi birinchi belgini (B) oladi va uni matndan olib tashlaydi - biz olamiz:
sh zkz n 125 ru.
Va hokazo - umid qilamanki, siz bu fikrni olasiz. Har bir iteratsiya bilan o'chiriladigan belgilar ro'yxati chap tomondan qisqartiriladi va biz to'plamdan keyingi belgini bo'sh joy bilan qidiramiz va almashtiramiz.
Barcha belgilar tugagach, biz tsikldan chiqishimiz kerak bo'ladi - bu rol faqat funktsiya tomonidan amalga oshiriladi IF (Agar), unda bizning dizaynimiz o'ralgan. Agar o'chirish uchun belgilar qolmagan bo'lsa (d=””), u holda funktsiya endi o'zini chaqirmasligi kerak, faqat tozalanadigan matnni (t o'zgaruvchisi) yakuniy ko'rinishida qaytarishi kerak.
Hujayralarning rekursiv iteratsiyasi
Xuddi shunday, siz ma'lum diapazondagi hujayralarni rekursiv sanab o'tishingiz mumkin. Biz nomli lambda funksiyasini yaratmoqchimiz deylik ALSHIRISh RO'YXATI berilgan ma'lumotnomalar ro'yxati bo'yicha manba matnidagi parchalarni ulgurji almashtirish uchun. Natija quyidagicha ko'rinishi kerak:
Bular. bizning funktsiyamizda ALSHIRISh RO'YXATI uchta argument bo'ladi:
- qayta ishlash uchun matnli hujayra (manba manzili)
- Qidiruvdan qidirish uchun qiymatlari bo'lgan ustunning birinchi katagi
- ustunning birinchi katakchasi qidiruvdan olingan qiymatlarni almashtiradi
Funktsiya katalogda yuqoridan pastgacha borishi va chap ustundagi barcha variantlarni ketma-ket almashtirishi kerak Topmoq o'ng ustundan mos keladigan variantlarga O'zgartirish. Buni quyidagi rekursiv lambda funktsiyasi bilan amalga oshirishingiz mumkin:
Har bir iteratsiyada pastga siljish standart Excel funktsiyasi tomonidan amalga oshiriladi ISHLASH (OFSET), bu holda uchta argument mavjud - asl diapazon, qatorni siljitish (1) va ustunni siljitish (0).
Xo'sh, biz katalogning oxiriga yetganimizdan so'ng (n = "") rekursiyani tugatishimiz kerak - biz o'zimizga qo'ng'iroq qilishni to'xtatamiz va t manba matnidagi barcha almashtirishlardan keyin to'plangan narsalarni ko'rsatamiz.
Ana xolos. Hech qanday qiyin makroslar yoki Power Query so'rovlari yo'q - butun vazifa bitta funktsiya tomonidan hal qilinadi.
- Excelning yangi dinamik massiv funksiyalaridan qanday foydalanish mumkin: FILTER, SORT, UNIC
- SUBSTITUTE funksiyasi bilan matnni almashtirish va tozalash
- VBA-da makrolar va foydalanuvchi tomonidan belgilangan funktsiyalarni (UDF) yaratish