Paragraf
Saiki, Microsoft Excel duwe meh limang atus fungsi lembar kerja sing kasedhiya liwat tombol Wisaya Fungsi fx ing garis formula. Iki minangka set sing apik banget, nanging, meh saben pangguna cepet utawa mengko nemoni kahanan ing dhaptar iki ora ngemot fungsi sing dibutuhake - mung amarga ora ana ing Excel.
Nganti saiki, siji-sijine cara kanggo ngatasi masalah iki yaiku makro, yaiku nulis fungsi sing ditemtokake pangguna dhewe (UDF = Fungsi sing ditemtokake pangguna) ing Visual Basic, sing mbutuhake katrampilan pemrograman sing cocog lan, kadhangkala, ora gampang. Nanging, kanthi nganyari Office 365 paling anyar, kahanan wis owah dadi luwih apik - fungsi "pambungkus" khusus wis ditambahake menyang Excel. LAMBDA. Kanthi bantuan, tugas nggawe fungsi sampeyan saiki wis ditanggulangi kanthi gampang lan apik.
Ayo dipikirake prinsip panggunaane ing conto ing ngisor iki.
Kaya sing sampeyan ngerti, Excel duwe sawetara fungsi parsing tanggal sing ngidini sampeyan nemtokake jumlah dina, wulan, minggu, lan taun kanggo tanggal tartamtu. Nanging sakperangan alesan ora ana fungsi sing nemtokake jumlah kuartal, sing uga asring dibutuhake, ta? Ayo dadi ndandani shortcoming iki lan nggawe karo LAMBDA fungsi anyar dhewe kanggo ngatasi masalah iki.
Langkah 1. Tulis rumus
Ayo dadi miwiti kanthi manual kanthi cara sing biasa kita bakal nulis rumus ing sel sheet sing ngitung apa sing kita butuhake. Ing kasus nomer seprapat, iki bisa ditindakake, contone, kaya iki:
Langkah 2. Wrapping munggah ing LAMBDA lan testing
Saiki wektune kanggo ngetrapake fungsi LAMBDA anyar lan bungkus rumus kasebut. Sintaks fungsi kaya ing ngisor iki:
=LAMBDA(Variabel1; Variabel2; … VariabelN ; expression)
ing ngendi jeneng siji utawa luwih variabel didhaptar dhisik, lan argumen pungkasan tansah minangka rumus utawa ekspresi sing diwilang sing nggunakake. Jeneng variabel ngirim ora katon kaya alamat sel lan ora ngemot titik.
Ing kasus kita, mung bakal ana siji variabel - tanggal sing kita ngitung nomer seprapat. Ayo diarani variabel kasebut, ucapake, d. Banjur mbungkus rumus kita ing fungsi LAMBDA lan ngganti alamat sel asli A2 kanthi jeneng variabel fiktif, kita entuk:
Wigati dicathet yen sawise transformasi kasebut, rumus kita (nyatane, bener!) Mulai ngasilake kesalahan, amarga saiki tanggal asli saka sel A2 ora ditransfer menyang. Kanggo tes lan kapercayan, sampeyan bisa ngirim argumen kasebut kanthi nambahake sawise fungsi kasebut LAMBDA ing kurung:
Langkah 3. Nggawe jeneng
Saiki kanggo bagean sing gampang lan nyenengake. We mbukak Jeneng Manajer tab rumus (Formula - Jeneng Manager) lan nggawe jeneng anyar karo tombol nggawe (Nggawe). Teka lan ketik jeneng kanggo fungsi mangsa ngarep (contone, Nomkvartala), lan ing lapangan link (Referensi) Nyalin kanthi teliti saka garis rumus lan tempel fungsi kita LAMBDA, mung tanpa argumen pungkasan (A2):
Kabeh. Sawise ngeklik OK fungsi sing digawe bisa digunakake ing sel apa wae ing lembar buku kerja iki:
Gunakake ing buku liyane
LAMBDA lan susunan dinamis
Fungsi khusus digawe kanthi fungsi LAMBDA kasil ndhukung karya karo array dinamis anyar lan fungsi (Nyaring, UNIK, GRADE) ditambahake menyang Microsoft Excel ing 2020.
Contone, kita pengin nggawe fungsi sing ditemtokake pangguna anyar sing bakal mbandhingake rong dhaptar lan ngasilake bedane - unsur kasebut saka dhaptar pisanan sing ora ana ing nomer loro. Karya urip, ta? Sadurunge, iki digunakake salah siji fungsi a la VPR (VLOOKUP), utawa PivotTables, utawa pitakon Power Query. Saiki sampeyan bisa nindakake karo siji rumus:
Ing versi Inggris bakal dadi:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Kene fungsi COUNTIF counts nomer kedadean saben unsur dhaftar pisanan ing kaloro, lan banjur fungsi Nyaring milih mung wong-wong sing ora duwe kedadeyan kasebut. Kanthi mbungkus struktur iki LAMBDA lan nggawe sawetara jeneng adhedhasar jeneng kasebut, contone, SEARCH DISTRIBUSI - kita bakal entuk fungsi sing trep sing ngasilake asil mbandhingake rong dhaptar kanthi bentuk array dinamis:
Yen data sumber ora biasa, nanging tabel "pinter", fungsi kita uga bakal ngatasi masalah:
Conto liyane yaiku pamisah teks kanthi dinamis kanthi ngowahi dadi XML banjur ngurai sel kanthi sel nggunakake fungsi FILTER.XML sing bubar diurai. Supaya ora ngasilake rumus rumit iki kanthi manual saben-saben, bakal luwih gampang kanggo mbungkus ing LAMBDA lan nggawe kisaran dinamis adhedhasar kasebut, yaiku fungsi kompak lan trep anyar, menehi jeneng, contone, RAZDTEXT:
Argumentasi pisanan saka fungsi iki bakal dadi sel kanthi teks sumber, lan nomer loro - karakter pemisah, lan bakal ngasilake asil ing wangun array dinamis horisontal. Kode fungsi bakal kaya ing ngisor iki:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Dhaptar conto iku telas - ing kahanan apa sampeyan kerep kudu ngetik rumus dawa lan cumbersome padha, fungsi LAMBDA bakal nggawe urip luwih gampang.
Enumerasi karakter rekursif
Kabeh conto sadurunge wis ditampilake mung siji, paling ketok, sisih fungsi LAMBDA - nggunakake minangka "wrapper" kanggo mbungkus rumus dawa ing lan simplifying input. Nyatane, LAMBDA duwe sisih liyane, luwih jero, sing ngowahi dadi basa pamrograman sing lengkap.
Kasunyatane yaiku fitur sing paling penting saka fungsi LAMBDA yaiku kemampuan kanggo ngetrapake rekursi – logika petungan, nalika ing proses pitungan fungsi nelpon dhewe. Saka pakulinan, bisa uga katon serem, nanging ing pemrograman, rekursi minangka perkara sing umum. Malah ing makro ing Visual Basic, sampeyan bisa ngleksanakake, lan saiki, kaya sing sampeyan ngerteni, wis teka ing Excel. Ayo nyoba ngerti teknik iki kanthi conto praktis.
Contone, kita pengin nggawe fungsi sing ditemtokake pangguna sing bakal mbusak kabeh karakter sing diwenehake saka teks sumber. Kegunaan fungsi kasebut, aku mikir, sampeyan ora perlu mbuktekake - bakal trep banget kanggo mbusak data input sampah kanthi bantuan, ta?
Nanging, dibandhingake sadurunge, conto non-rekursif, loro kangelan nunggu kita.
- Kita kudu nggawe jeneng kanggo fungsi kita sadurunge miwiti nulis kode, amarga ing kono, jeneng iki wis digunakake kanggo nelpon fungsi kasebut.
- Nglebokake fungsi rekursif kasebut menyang sel lan debugging kanthi nemtokake argumen ing kurung sawise LAMBDA (kaya sing ditindakake sadurunge) ora bakal bisa. Sampeyan kudu nggawe fungsi langsung "saka ngeruk" ing Jeneng Manajer (Nama Manager).
Ayo nelpon fungsi kita, ucapake, CLEAN lan kita pengin duwe rong argumen - teks sing bakal diresiki lan dhaptar karakter sing ora kalebu minangka string teks:
Ayo nggawe, kaya sadurunge, ing tab rumus в Jeneng manager jenenge kisaran, jenenge CLEAR lan ketik ing lapangan Range konstruksi ing ngisor iki:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUT(t;LEFT(d);””);MID(d;2;255))))
Ing kene variabel t minangka teks asli sing bakal dibusak, lan d minangka dhaptar karakter sing bakal dibusak.
Kabeh tumindak kaya iki:
pengulangan 1
Fragmen SUBSTITUTE(t;LEFT(d);””), kaya sing bisa ditebak, ngganti karakter pisanan saka karakter kiwa saka set d sing bakal dibusak ing teks sumber t kanthi string teks kosong, yaiku mbusak “ A”. Minangka asil penengah, kita entuk:
Vsh zkz n 125 rubel.
pengulangan 2
Banjur fungsi kasebut nelpon dhewe lan minangka input (argumentasi pisanan) nampa apa sing ditinggalake sawise ngresiki ing langkah sadurunge, lan argumentasi kapindho yaiku senar karakter sing ora kalebu sing diwiwiti ora saka sing pisanan, nanging saka karakter sing kapindho, yaiku "BVGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYYA. , "tanpa wiwitan "A" - iki ditindakake dening fungsi MID. Kaya sadurunge, fungsi kasebut njupuk karakter pisanan saka sisih kiwa sing isih ana (B) lan ngganti ing teks sing diwenehake (Zkz n 125 rubel) kanthi senar kosong - kita entuk asil penengah:
125 ru.
pengulangan 3
Fungsi kasebut nelpon maneh, nampa minangka argumen pisanan apa sing ditinggalake saka teks sing bakal dibusak ing pengulangan sadurunge (Bsh zkz n 125 ru.), Lan minangka argumen kapindho, set karakter sing ora kalebu dipotong dening siji karakter liyane menyang sisih kiwa, yaiku "VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.," tanpa wiwitan "B". Banjur maneh njupuk karakter pisanan saka kiwa (B) saka set iki lan mbusak saka teks - kita entuk:
sh zkz n 125 ru.
Lan sateruse - muga-muga sampeyan entuk ide kasebut. Kanthi saben pengulangan, dhaptar karakter sing bakal dibusak bakal dipotong ing sisih kiwa, lan kita bakal nelusuri lan ngganti karakter sabanjure saka set kasebut kanthi kekosongan.
Nalika kabeh karakter entek, kita kudu metu saka daur ulang - peran iki mung dileksanakake dening fungsi IF (Yen), kang desain kita kebungkus. Yen ora ana karakter sing kudu dibusak (d = ""), mula fungsi kasebut ora kudu nelpon maneh, nanging mung kudu mbalekake teks sing bakal dibusak (variabel t) ing wangun pungkasan.
Pengulangan sel rekursif
Kajaba iku, sampeyan bisa ngleksanakake enumerasi rekursif sel ing sawetara tartamtu. Upaminipun kita pengin nggawe fungsi lambda jenenge DAFTAR GANTI kanggo panggantos Grosir pecahan ing teks sumber miturut dhaftar referensi diwenehi. Asil kudu katon kaya iki:
Sing. ing fungsi kita DAFTAR GANTI bakal ana telung argumen:
- sel karo teks kanggo diproses (alamat sumber)
- sel pisanan saka kolom kanthi nilai kanggo nggoleki saka panelusuran
- sel pisanan kolom kanthi nilai panggantos saka panelusuran
Fungsi kudu pindhah saka ndhuwur menyang ngisor ing direktori lan ngganti sequentially kabeh opsi saka kolom kiwa Kanggo nemokake menyang opsi sing cocog saka kolom tengen Ganti. Sampeyan bisa ngetrapake iki kanthi fungsi lambda rekursif ing ngisor iki:
Shift mudhun ing saben iterasi dileksanakake dening fungsi excel standar Pembuangan (OFFSET), sing ing kasus iki nduweni telung argumen - kisaran asli, shift baris (1) lan shift kolom (0).
Inggih, sanalika kita tekan mburi direktori (n = ""), kita kudu mungkasi rekursi - kita mandheg nelpon dhéwé lan nampilake apa sing wis diklumpukake sawise kabeh panggantos ing variabel teks sumber t.
Mekaten. Ora ana macro sing angel utawa pitakon Power Query - kabeh tugas ditanggulangi kanthi siji fungsi.
- Cara nggunakake fungsi array dinamis anyar Excel: FILTER, SORT, UNIC
- Ngganti lan ngresiki teks nganggo fungsi SUBSTITUTE
- Nggawe makro lan fungsi sing ditemtokake pangguna (UDF) ing VBA