Daptar eusi
Ayeuna, Microsoft Excel ngagaduhan ampir lima ratus fungsi lembar kerja anu sayogi ngalangkungan jandela Wizard Fungsi - tombol fx dina rumus bar. Ieu mangrupikeun set anu saé, tapi, sanaos kitu, ampir unggal pangguna engké atanapi engké mendakan kaayaan dimana daptar ieu henteu ngandung fungsi anu diperyogikeun - ngan kusabab éta henteu aya dina Excel.
Nepi ka ayeuna, hiji-hijina jalan pikeun ngajawab masalah ieu macros, nyaéta nulis fungsi-diartikeun-pamaké anjeun sorangan (UDF = User Defined Function) dina Visual Basic, nu merlukeun kaahlian programming luyu jeung, di kali, teu gampang pisan. Nanging, kalayan apdet Office 365 panganyarna, kaayaan parantos robih janten langkung saé - fungsi "wrapper" khusus parantos ditambahkeun kana Excel. lambda. Kalayan bantosanana, tugas nyiptakeun fungsi anjeun ayeuna direngsekeun kalayan gampang sareng éndah.
Hayu urang nempo prinsip pamakéan na dina conto di handap ieu.
Sakumaha anjeun terang paling dipikaresep, Excel gaduh sababaraha fungsi parsing tanggal anu ngamungkinkeun anjeun pikeun nangtukeun jumlah dinten, bulan, minggu, sareng taun kanggo tanggal anu ditangtukeun. Tapi pikeun sababaraha alesan euweuh fungsi anu nangtukeun jumlah kuartal, nu ogé mindeng diperlukeun, katuhu? Hayu urang ngalereskeun shortcoming ieu jeung nyieun kalawan lambda sorangan fungsi anyar pikeun ngajawab masalah ieu.
Lengkah 1. Tulis rumus
Hayu urang mimitian ku kanyataan yén sacara manual dina cara biasa urang nulis rumus dina sél lambar nu Etang naon urang kudu. Dina kasus jumlah saparapat, ieu tiasa dilakukeun, contona, sapertos kieu:
Lengkah 2. Wrapping up di LAMBDA jeung nguji
Ayeuna waktuna pikeun nerapkeun fungsi LAMBDA anyar sareng bungkus rumus kami di jerona. Sintaksis fungsina nyaéta kieu:
=LAMBDA(Variabel1; Variabel2; … VariabelN ; babasan)
dimana ngaran hiji atawa leuwih variabel dibéréndélkeun munggaran, sarta argumen panungtungan salawasna rumus atawa éksprési diitung nu ngagunakeun aranjeunna. Ngaran variabel teu kudu kasampak kawas alamat sél jeung teu kudu ngandung titik.
Dina kasus urang, bakal aya ngan hiji variabel - tanggal nu urang ngitung jumlah saparapat. Hayu urang nelepon variabel pikeun eta, sebutkeun, d. Lajeng wrapping rumus urang dina fungsi lambda sareng ngagentos alamat sél aslina A2 kalayan nami variabel fiktif, urang kéngingkeun:
Punten dicatet yén saatos transformasi sapertos kitu, rumus urang (saleresna, leres!) mimiti ngahasilkeun kasalahan, sabab ayeuna tanggal aslina tina sél A2 henteu ditransferkeun ka éta. Pikeun nguji sareng kayakinan, anjeun tiasa masihan argumen kana éta ku cara nambihanana saatos fungsina lambda dina kurung:
Lengkah 3. Jieun ngaran
Ayeuna keur bagian gampang jeung senang. Urang buka Manajer Ngaran tab rumus (Rumus - Manajer Ngaran) jeung nyieun ngaran anyar jeung tombol nyiptakeun (Nyieun). Jieun sareng lebetkeun nami kanggo fungsi kahareup urang (contona, Nomkvartala), sareng di lapangan link (Rujukan) taliti nyalin tina rumus bar jeung nempelkeun fungsi urang lambda, ngan tanpa argumen panungtungan (A2):
Sagalana. Saatos ngaklik OK fungsi nu dijieun bisa dipaké dina sél mana wae dina sagala lambar workbook ieu:
Paké dina buku séjén
LAMBDA jeung arrays dinamis
fungsi custom dijieun kalawan fungsi lambda hasil ngarojong gawé kalawan arrays dinamis anyar jeung fungsi maranéhanana (saringan, UNIK, kelas) ditambahkeun kana Microsoft Excel dina 2020.
Sebutkeun urang hoyong nyiptakeun fungsi anu ditetepkeun ku pangguna énggal anu bakal ngabandingkeun dua daptar sareng ngabalikeun bédana antara aranjeunna - unsur-unsur tina daptar kahiji anu henteu aya dina kadua. Karya hirup, teu eta? Saméméhna, pikeun ieu aranjeunna dipaké boh fungsi a la VPR (VLOOKUP), atawa PivotTables, atawa Power Query queries. Ayeuna anjeun tiasa ngalakukeun kalayan hiji rumus:
Dina versi Inggris bakal kieu:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Di dieu fungsina COUNTIF ngitung jumlah lumangsungna unggal unsur daptar kahiji dina kadua, lajeng fungsi saringan milih ngan maranéhanana anu teu boga kajadian ieu. Ku wrapping struktur ieu dina lambda jeung nyieun rentang ngaranna dumasar kana eta kalawan ngaran, contona, SEARCH DISTRIBUSI - urang bakal kéngingkeun fungsi anu saé anu ngabalikeun hasil ngabandingkeun dua daptar dina bentuk array dinamis:
Upami data sumber henteu biasa, tapi tabel "pinter", fungsi kami ogé bakal tiasa diatasi tanpa masalah:
Conto anu sanésna nyaéta ngabagi téks sacara dinamis ku cara ngarobih kana XML teras nga-parsing sél ku sél nganggo fungsi FILTER.XML anu nembe kami parsed. Dina raraga teu baranahan rumus kompléks ieu sacara manual unggal waktu, bakal leuwih gampang pikeun mungkus eta dina LAMBDA jeung nyieun rentang dinamis dumasar kana eta, nyaéta fungsi kompak jeung merenah anyar, ngaranna, contona, RAZDTEXT:
Argumen mimiti fungsi ieu bakal sél jeung téks sumber, sarta kadua - karakter separator, sarta eta bakal balik hasilna dina bentuk Asép Sunandar Sunarya dinamis horizontal. Kodeu fungsina bakal kieu:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Daptar conto teu aya watesna - dina kaayaan naon waé dimana anjeun sering kedah ngalebetkeun rumus panjang sareng pajeulit anu sami, fungsi LAMBDA bakal ngagampangkeun kahirupan.
Enumerasi rekursif karakter
Sadaya conto saméméhna geus ditémbongkeun ngan hiji, paling atra, sisi fungsi LAMBDA - pamakéan na salaku "wrapper" pikeun wrapping rumus panjang di dinya jeung simplify input maranéhna. Nyatana, LAMBDA ngagaduhan sisi anu sanés, langkung jero, anu janten ampir janten basa pamrograman anu lengkep.
Kanyataanna nyaéta fitur anu penting dina fungsi LAMBDA nyaéta kamampuan pikeun nerapkeunana rekursi – logika itungan, nalika dina prosés itungan fungsi nelepon sorangan. Tina kabiasaan, éta sigana pikasieuneun, tapi dina program, rekursi mangrupikeun hal anu biasa. Malah dina makro dina Visual Basic, anjeun tiasa nerapkeun éta, sareng ayeuna, sakumaha anu anjeun tingali, parantos sumping ka Excel. Hayu urang coba ngartos téhnik ieu ku conto praktis.
Anggap urang hoyong ngadamel fungsi anu ditetepkeun ku pangguna anu bakal ngaleungitkeun sadaya karakter anu dipasihkeun tina téks sumber. Mangpaat tina fungsi sapertos kitu, kuring pikir, anjeun henteu kedah ngabuktikeun - éta bakal langkung gampang pikeun mupus data input anu littered kalayan bantosanna, leres?
Sanajan kitu, dibandingkeun jeung saméméhna, conto non-recursive, dua kasusah await kami.
- Urang kedah ngadamel nami pikeun fungsi urang sateuacan urang ngamimitian nyerat kode na, sabab di jerona, nami ieu parantos dianggo pikeun nyauran fungsina sorangan.
- Lebetkeun fungsi rekursif sapertos kana sél sareng debugging ku netepkeun argumen dina kurung saatos LAMBDA (sapertos anu urang lakukeun sateuacana) moal jalan. Anjeun kudu nyieun hiji fungsi geuwat "ti scratch" dina Manajer Ngaran (Ngaran Manajer).
Hayu urang sebutkeun fungsi urang, sebutkeun, BERSIH sareng urang hoyong gaduh dua argumen - téks anu dibersihkeun sareng daptar karakter anu teu kalebet salaku string téks:
Hayu urang nyieun, sakumaha urang tadi, dina tab rumus в Pangatur ngaran ngaranna rentang, ngaranna éta CLEAR jeung asupkeun dina widang jajaran konstruksi handap:
=LAMBDA(t;d;IF(d=””;t;CLEAR(GANTIKAN(t;KIRI(d);””);MID(d;2;255))))
Di dieu variabel t nyaéta téks asli anu bakal dibersihkeun, sareng d nyaéta daptar karakter anu bakal dihapus.
Éta sadayana dianggo sapertos kieu:
Iterasi 1
Fragmén SUBSTITUTE(t;LEFT(d);""), sakumaha anu anjeun pikirkeun, ngagentos karakter kahiji ti karakter kénca ti set d anu bakal dihapus dina téks sumber t sareng string téks kosong, nyaéta ngaleungitkeun " A”. Salaku hasil panengah, urang meunang:
Vsh zkz n 125 rubles.
Iterasi 2
Teras fungsina nyauran nyalira sareng salaku input (argumen kahiji) nampi naon anu tinggaleun saatos beberesih dina léngkah anu saacanna, sareng argumen anu kadua nyaéta senar karakter anu dikaluarkeun henteu dimimitian ti anu kahiji, tapi tina karakter anu kadua, nyaéta "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," tanpa awal "A" - ieu dilakukeun ku fungsi MID. Sapertos sateuacanna, fungsina nyandak karakter anu munggaran ti kénca ti sésana (B) sareng ngagentos kana téks anu dipasihkeun ka éta (Zkz n 125 rubles) kalayan senar kosong - kami kéngingkeun hasil panengah:
125 ru.
Iterasi 3
Fungsina nyauran sorangan deui, nampi salaku argumen kahiji naon anu tinggaleun téks pikeun dibersihkeun dina iterasi saméméhna (Bsh zkz n 125 ru.), Sareng salaku argumen anu kadua, set karakter anu teu kalebet dipotong ku hiji karakter deui. kénca, nyaéta "VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.," tanpa awal "B". Lajeng deui nyokot karakter kahiji ti kénca (B) ti set ieu sarta ngaluarkeun tina téks - urang meunang:
sh zkz n 125 ru.
Jeung saterusna - Kuring miharep anjeun meunang ide. Kalawan unggal Iteration, daptar karakter nu bakal dihapus bakal truncated on kénca, sarta kami bakal neangan tur ngaganti karakter hareup ti set kalawan batal a.
Nalika sakabéh karakter béak, urang kudu kaluar loop - peran ieu ngan dipigawé ku fungsi IF (lamun), dimana desain urang dibungkus. Upami teu aya karakter anu kedah dipupus (d = ""), fungsina henteu kedah deui nyauran nyalira, tapi ngan ukur kedah ngabalikeun téks pikeun dibersihkeun (variabel t) dina bentuk ahirna.
Iteration sél rekursif
Nya kitu, anjeun tiasa nerapkeun enumerasi rekursif sél dina rentang anu ditangtukeun. Anggap urang rék nyieun fungsi lambda ngaranna DAFTAR GANTI pikeun ngagantian borongan fragmen dina téks sumber nurutkeun daptar rujukan dibikeun. Hasilna kedah sapertos kieu:
Jelema. dina fungsi urang DAFTAR GANTI bakal aya tilu argumen:
- sél kalayan téks pikeun diolah (alamat sumber)
- sél mimiti kolom kalayan nilai pikeun milarian tina panéangan
- sél mimiti kolom kalawan nilai ngagantian ti lookup nu
Fungsina kedah angkat ti luhur ka handap dina diréktori sareng ngagentos sacara sekuen sadaya pilihan tina kolom kénca Pikeun mendakan kana pilihan saluyu ti kolom katuhu ngagentos. Anjeun tiasa nerapkeun ieu nganggo fungsi lambda rekursif ieu:
Shift ka handap dina unggal iteration dilaksanakeun ku fungsi excel standar Buang (OFFSET), nu dina hal ieu boga tilu argumen - rentang aslina, shift baris (1) jeung shift kolom (0).
Muhun, pas urang ngahontal tungtung diréktori (n = ""), urang kudu mungkas recursion nu - urang eureun nelepon sorangan sarta nembongkeun naon geus akumulasi sanggeus sagala ngagantian dina variabel téks sumber t.
Éta hungkul. Taya macros tricky atanapi query Power Query - sakabeh tugas direngsekeun ku hiji fungsi.
- Kumaha ngagunakeun fungsi array dinamis anyar Excel: FILTER, SORT, UNIC
- Ngaganti sareng ngabersihan téks nganggo fungsi SUBSTITUTE
- Nyiptakeun makro sareng fungsi anu ditetepkeun ku pangguna (UDF) dina VBA