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