Contents [show]
Pada masa ini, Microsoft Excel mempunyai hampir lima ratus fungsi lembaran kerja yang tersedia melalui tetingkap Function Wizard – butang fx dalam bar formula. Ini adalah satu set yang sangat baik, tetapi, bagaimanapun, hampir setiap pengguna lambat laun menghadapi situasi di mana senarai ini tidak mengandungi fungsi yang dia perlukan – semata-mata kerana ia tiada dalam Excel.
Sehingga kini, satu-satunya cara untuk menyelesaikan masalah ini ialah makro, iaitu menulis fungsi takrif pengguna anda sendiri (UDF = Fungsi Ditetapkan Pengguna) dalam Visual Basic, yang memerlukan kemahiran pengaturcaraan yang sesuai dan, kadangkala, tidak mudah sama sekali. Walau bagaimanapun, dengan kemas kini Office 365 terkini, keadaan telah berubah menjadi lebih baik – fungsi "pembungkus" khas telah ditambahkan pada Excel LAMBDA. Dengan bantuannya, tugas mencipta fungsi anda sendiri kini diselesaikan dengan mudah dan cantik.
Mari kita lihat prinsip penggunaannya dalam contoh berikut.
Seperti yang anda ketahui, Excel mempunyai beberapa fungsi penghuraian tarikh yang membolehkan anda menentukan bilangan hari, bulan, minggu dan tahun untuk tarikh tertentu. Tetapi entah kenapa tiada fungsi yang menentukan bilangan suku tersebut, yang juga sering diperlukan, bukan? Mari kita perbaiki kelemahan ini dan buat dengan LAMBDA mempunyai fungsi baru untuk menyelesaikan masalah ini.
Langkah 1. Tulis formula
Mari kita mulakan dengan fakta bahawa secara manual dengan cara biasa kita akan menulis formula dalam sel helaian yang mengira apa yang kita perlukan. Dalam kes nombor suku, ini boleh dilakukan, sebagai contoh, seperti ini:
Langkah 2. Membungkus dalam LAMBDA dan menguji
Kini tiba masanya untuk menggunakan fungsi LAMBDA baharu dan membungkus formula kami di dalamnya. Sintaks fungsi adalah seperti berikut:
=LAMBDA(Pembolehubah1; Pembolehubah2; ... PembolehubahN ; ungkapan)
di mana nama satu atau lebih pembolehubah disenaraikan dahulu, dan hujah terakhir sentiasa formula atau ungkapan terkira yang menggunakannya. Nama pembolehubah tidak sepatutnya kelihatan seperti alamat sel dan tidak boleh mengandungi titik.
Dalam kes kami, hanya akan ada satu pembolehubah - tarikh kami mengira nombor suku. Mari kita panggil pembolehubah untuknya, katakan, d. Kemudian bungkus formula kami dalam fungsi LAMBDA dan menggantikan alamat sel asal A2 dengan nama pembolehubah rekaan, kita dapat:
Sila ambil perhatian bahawa selepas transformasi sedemikian, formula kami (sebenarnya, betul!) mula menghasilkan ralat, kerana kini tarikh asal dari sel A2 tidak dipindahkan kepadanya. Untuk ujian dan keyakinan, anda boleh menyampaikan hujah kepadanya dengan menambahkannya selepas fungsi LAMBDA dalam kurungan:
Langkah 3. Cipta nama
Sekarang untuk bahagian yang mudah dan menyeronokkan. Kami buka Pengurus Nama tab formula (Formula — Pengurus Nama) dan buat nama baharu dengan butang Buat (Buat). Buat dan masukkan nama untuk fungsi masa hadapan kita (contohnya, Nomkvartala), dan di lapangan pautan (Rujukan) salin dengan teliti dari bar formula dan tampal fungsi kami LAMBDA, hanya tanpa hujah terakhir (A2):
Semuanya. Selepas klik pada OK fungsi yang dicipta boleh digunakan dalam mana-mana sel pada mana-mana helaian buku kerja ini:
Gunakan dalam buku lain
LAMBDA dan tatasusunan dinamik
Fungsi tersuai dibuat dengan fungsi LAMBDA berjaya menyokong kerja dengan tatasusunan dinamik baharu dan fungsinya (FILTER, UNIK, GRED) ditambahkan pada Microsoft Excel pada tahun 2020.
Katakan kita ingin mencipta fungsi yang ditakrifkan pengguna baharu yang akan membandingkan dua senarai dan mengembalikan perbezaan di antara mereka – elemen tersebut daripada senarai pertama yang tiada dalam senarai kedua. Kerja hidup, bukan? Sebelum ini, untuk ini mereka menggunakan sama ada fungsi ala VPR (TAMPILAN), atau Jadual Pangsi, atau pertanyaan Power Query. Kini anda boleh lakukan dengan satu formula:
Dalam versi bahasa Inggeris ia akan menjadi:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Di sini fungsinya COUNTIF mengira bilangan kejadian setiap elemen senarai pertama dalam kedua, dan kemudian fungsi FILTER memilih hanya mereka yang tidak mengalami kejadian ini. Dengan membungkus struktur ini LAMBDA dan mencipta julat bernama berdasarkannya dengan nama, contohnya, AGIHAN CARIAN – kita akan mendapat fungsi mudah yang mengembalikan hasil membandingkan dua senarai dalam bentuk tatasusunan dinamik:
Jika data sumber tidak biasa, tetapi jadual "pintar", fungsi kami juga akan mengatasi tanpa masalah:
Contoh lain ialah membelah teks secara dinamik dengan menukarkannya kepada XML dan kemudian menghuraikannya sel demi sel menggunakan fungsi FILTER.XML yang kami huraikan baru-baru ini. Untuk tidak menghasilkan semula formula kompleks ini secara manual setiap kali, lebih mudah untuk membungkusnya dalam LAMBDA dan mencipta julat dinamik berdasarkannya, iaitu fungsi padat dan mudah yang baharu, menamakannya, sebagai contoh, RAZDTEXT:
Argumen pertama bagi fungsi ini ialah sel dengan teks sumber, dan yang kedua – aksara pemisah, dan ia akan mengembalikan hasil dalam bentuk tatasusunan dinamik mendatar. Kod fungsi adalah seperti berikut:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Senarai contoh adalah tidak berkesudahan – dalam apa jua keadaan di mana anda sering perlu memasukkan formula panjang dan menyusahkan yang sama, fungsi LAMBDA akan menjadikan hidup lebih mudah.
Penghitungan rekursif aksara
Semua contoh sebelumnya telah menunjukkan hanya satu, bahagian yang paling jelas, fungsi LAMBDA - penggunaannya sebagai "pembungkus" untuk membungkus formula panjang di dalamnya dan memudahkan inputnya. Malah, LAMBDA mempunyai sisi lain, lebih mendalam, yang mengubahnya menjadi hampir bahasa pengaturcaraan yang lengkap.
Hakikatnya ialah ciri asas penting bagi fungsi LAMBDA ialah keupayaan untuk melaksanakannya pengulangan – logik pengiraan, apabila dalam proses pengiraan fungsi memanggil dirinya sendiri. Dari kebiasaan, ia mungkin terdengar menyeramkan, tetapi dalam pengaturcaraan, rekursi adalah perkara biasa. Walaupun dalam makro dalam Visual Basic, anda boleh melaksanakannya, dan kini, seperti yang anda lihat, ia telah datang ke Excel. Mari cuba memahami teknik ini dengan contoh praktikal.
Katakan kita ingin mencipta fungsi yang ditentukan pengguna yang akan mengalih keluar semua aksara yang diberikan daripada teks sumber. Kegunaan fungsi sedemikian, saya fikir, anda tidak perlu membuktikan - ia akan menjadi sangat mudah untuk membersihkan data input yang bersepah dengan bantuannya, bukan?
Walau bagaimanapun, berbanding dengan contoh bukan rekursif sebelumnya, dua kesukaran menanti kita.
- Kami perlu menghasilkan nama untuk fungsi kami sebelum kami mula menulis kodnya, kerana di dalamnya, nama ini sudah pun digunakan untuk memanggil fungsi itu sendiri.
- Memasukkan fungsi rekursif sedemikian ke dalam sel dan menyahpepijatnya dengan menentukan argumen dalam kurungan selepas LAMBDA (seperti yang kita lakukan sebelum ini) tidak akan berfungsi. Anda perlu mencipta fungsi dengan segera "dari awal" masuk Pengurus Nama (Pengurus Nama).
Mari kita panggil fungsi kami, katakan, CLEAN dan kami ingin ia mempunyai dua hujah – teks yang akan dibersihkan dan senarai aksara yang dikecualikan sebagai rentetan teks:
Mari buat, seperti yang kita lakukan sebelum ini, pada tab formula в Pengurus nama nama julat, namakannya CLEAR dan masukkan dalam medan Pertengahan pembinaan berikut:
=LAMBDA(t;d;IF(d=””;t;CLEAR(GANTIKAN(t;KIRI(d);””);MID(d;2;255))))
Di sini pembolehubah t ialah teks asal yang akan dikosongkan, dan d ialah senarai aksara yang akan dipadamkan.
Semuanya berfungsi seperti ini:
Pengulangan 1
Serpihan SUBSTITUTE(t;LEFT(d);””), seperti yang anda mungkin rasa, menggantikan aksara pertama daripada aksara kiri daripada set d untuk dipadamkan dalam teks sumber t dengan rentetan teks kosong, iaitu membuang “ A”. Sebagai hasil perantaraan, kami mendapat:
Vsh zkz n 125 rubel.
Pengulangan 2
Kemudian fungsi memanggil dirinya sendiri dan sebagai input (hujah pertama) menerima apa yang tinggal selepas pembersihan dalam langkah sebelumnya, dan hujah kedua ialah rentetan aksara yang dikecualikan bermula bukan dari yang pertama, tetapi dari aksara kedua, iaitu “BVGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYYA. ,” tanpa awalan “A” – ini dilakukan oleh fungsi MID. Seperti sebelumnya, fungsi itu mengambil aksara pertama dari kiri yang selebihnya (B) dan menggantikannya dalam teks yang diberikan kepadanya (Zkz n 125 rubel) dengan rentetan kosong - kami dapat sebagai hasil perantaraan:
125 ru.
Pengulangan 3
Fungsi ini memanggil dirinya semula, menerima sebagai argumen pertama apa yang tersisa daripada teks yang akan dikosongkan pada lelaran sebelumnya (Bsh zkz n 125 ru.), Dan sebagai hujah kedua, set aksara yang dikecualikan dipotong oleh satu aksara lagi kepada kiri, iaitu “VGDEEGZIKLMNOPRSTUFHTSCHSSHHCHYYYYUYA.,” tanpa “B” awalan. Kemudian ia sekali lagi mengambil aksara pertama dari kiri (B) dari set ini dan mengeluarkannya daripada teks - kita dapat:
sh zkz n 125 ru.
Dan seterusnya - saya harap anda mendapat idea itu. Dengan setiap lelaran, senarai aksara yang akan dialih keluar akan dipotong di sebelah kiri, dan kami akan mencari dan menggantikan aksara seterusnya daripada set dengan kekosongan.
Apabila semua aksara habis, kita perlu keluar dari gelung - peranan ini hanya dilakukan oleh fungsi IF (JIKA), di mana reka bentuk kami dibalut. Jika tiada aksara yang tinggal untuk dipadamkan (d=””), maka fungsi itu seharusnya tidak lagi memanggil dirinya sendiri, tetapi hanya perlu mengembalikan teks untuk dikosongkan (pembolehubah t) dalam bentuk terakhirnya.
Lelaran rekursif sel
Begitu juga, anda boleh melaksanakan penghitungan rekursif sel dalam julat tertentu. Katakan kita ingin mencipta fungsi lambda bernama SENARAI PENGGANTIAN untuk penggantian borong serpihan dalam teks sumber mengikut senarai rujukan yang diberikan. Hasilnya sepatutnya kelihatan seperti ini:
Itu. di majlis kami SENARAI PENGGANTIAN akan ada tiga hujah:
- sel dengan teks untuk diproses (alamat sumber)
- sel pertama lajur dengan nilai untuk dicari dari carian
- sel pertama lajur dengan nilai gantian daripada carian
Fungsi harus pergi dari atas ke bawah dalam direktori dan menggantikan secara berurutan semua pilihan dari lajur kiri Untuk mencari kepada pilihan yang sepadan dari lajur kanan Pengganti. Anda boleh melaksanakan ini dengan fungsi lambda rekursif berikut:
Anjakan ke bawah pada setiap lelaran dilaksanakan oleh fungsi excel standard PELUPUSAN (OFFSET), yang dalam kes ini mempunyai tiga hujah – julat asal, anjakan baris (1) dan anjakan lajur (0).
Nah, sebaik sahaja kami sampai ke penghujung direktori (n = ""), kami mesti menamatkan rekursi - kami berhenti memanggil diri kami sendiri dan memaparkan apa yang telah terkumpul selepas semua penggantian dalam pembolehubah teks sumber t.
Itu sahaja. Tiada makro rumit atau pertanyaan Power Query – keseluruhan tugas diselesaikan dengan satu fungsi.
- Cara menggunakan fungsi tatasusunan dinamik baharu Excel: FILTER, SORT, UNIC
- Mengganti dan membersihkan teks dengan fungsi SUBSTITUTE
- Mencipta makro dan fungsi takrif pengguna (UDF) dalam VBA