Fungsi lambda rekursif dalam excel dengan contoh

Tujuan tutorial ini adalah untuk menerangkan konsep fungsi rekursif dan menunjukkan pendekatan generik untuk penciptaan lambdas rekursif dalam Excel. Kami akan meneroka setiap aspek secara mendalam untuk memudahkan anda mengikuti dan menghasilkan semula dalam lembaran kerja anda.

Dengan pengenalan fungsi Lambda, pengiraan rekursif dalam Excel telah menjadi tersedia kepada sesiapa sahaja, bukan hanya pengaturcara VBA. Untuk meletakkannya dengan mudah, anda kini boleh membina formula yang berkelakuan seperti bahasa pengaturcaraan dan membolehkan anda mencapai begitu banyak, dengan begitu sedikit, begitu cepat :)

Perlu diingat bahawa contoh -contoh yang dibincangkan dalam tutorial ini menyiratkan bahawa anda sudah tahu sintaks dan penggunaan asas Lambda. Jika tidak, ia menjadi alasan untuk memulakan dengan keperluan: bagaimana menulis dan menggunakan Lambda dalam Excel.

• Fungsi Lambda Rekursif
• Lambda rekursif untuk membuang watak yang tidak diingini
• Cara Menulis Lambda Rekursif di Excel
• Cara menggunakan lambda rekursif
• Memahami rekursi
• Contoh lambda rekursif
  • Contoh 1. Keluarkan aksara yang tidak diingini dan memangkas ruang tambahan
  • Contoh 2. Gantikan pelbagai aksara dengan watak yang sama
  • Contoh 3. Gantikan pelbagai nilai dengan nilai lain sekaligus
• Rekursif Lambda vs Fungsi Ditetapkan Pengguna VBA

Fungsi lambda rekursif

Untuk memastikan semua orang berada di halaman yang sama, mari kita tentukan fungsi rekursif terlebih dahulu.

Dalam sains komputer, rekursi adalah kaedah menyelesaikan masalah di mana fungsi memanggil secara langsung atau tidak langsung. Fungsi sedemikian dipanggil rekursif . Pada asasnya, fungsi rekursif berfungsi dengan lelaran dan mencari penyelesaian kepada masalah yang lebih besar dengan menyelesaikan masalah yang lebih kecil dari masalah yang sama.

Pada masa ini, Lambda adalah satu -satunya fungsi Excel yang menyokong rekursi, membolehkan anda membuat penyelesaian padat dan elegan untuk masalah yang kompleks tanpa pengekodan.

Dalam VBA, rekursi biasanya dilakukan menggunakan ... seterusnya atau lakukan ... semasa gelung. Lambda biasanya bergantung pada fungsi IF untuk menguji keadaan Boolean dan berulang jika keadaan sama ada benar atau palsu.

Inilah struktur lambda rekursif yang berfungsi sebagai bentuk yang paling mudah:

= Lambda (x, y, ..., 'mengisytiharkan parameter jika (logical_test, ' menguji keadaan mylambda (), 'recurse jika keadaan menilai kepada value_if_false yang benar ' keluar jika keadaan menilai kepada palsu )

Perkara utama adalah untuk menghentikan panggilan rekursif dari berterusan selama -lamanya. Untuk ini, anda harus menyediakan kes yang berakhir (juga dikenali sebagai kes berhenti , atau kes asas ). Sekiranya tiada titik keluar disediakan, formula akan terus meleleh sehingga komputer anda terhempas, hanya bercanda tentu saja, ia akan membuang #num! ralat.

Berbanding dengan fungsi bukan rekursif, lambdas rekursif lebih sukar untuk menulis, menguji dan debug. Ia menyerupai ayam tua dan teka -teki telur yang baik - untuk berfungsi berfungsi dengan betul, ia mesti memanggilnya sendiri; Untuk memanggilnya sendiri, fungsi mesti berfungsi dengan betul :)

Contoh lambda rekursif untuk menghapuskan watak yang tidak diingini

Apabila mengimport data dari sumber luaran, watak -watak sampah mungkin sering menyelinap masuk, dan anda perlu mencari cara untuk membersihkan data anda entah bagaimana.

Ciri ganti semua boleh mengeluarkan semua kejadian watak yang diberikan dengan menggantikannya dengan apa -apa, tetapi ia hanya dapat menangani satu watak pada satu masa.

Lebih cepat dan lebih mudah adalah untuk menyenaraikan semua watak yang tidak diingini dalam beberapa sel dan menghapuskannya dalam satu kejatuhan menggunakan formula. Lambda rekursif adalah apa yang anda perlukan:

=LAMBDA(data, chars, IF(chars"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))

Fungsi Lambda Custom kami dinamakan RemoveChars dan memerlukan dua parameter input:

• Data - Sel atau pelbagai sel yang akan dibersihkan.
• Chars - watak yang tidak diingini untuk dikeluarkan. Boleh disediakan dalam bentuk rentetan teks atau rujukan sel. Dalam sel, watak -watak harus disenaraikan tanpa ruang, melainkan jika anda mahu membasmi ruang juga.

Pada tahap yang tinggi, inilah fungsi yang dilakukannya:

Kitaran fungsi RemoveChars melalui senarai pengecualian ( chars ) dan membersihkan satu aksara pada satu masa. Sebelum setiap panggilan rekursif, fungsi IF menilai baki chars . Jika rentetan tidak kosong (chars ""), fungsi itu memanggilnya sendiri. Sebaik sahaja watak terakhir telah dikendalikan, proses lelaran selesai - formula mengembalikan data dalam bentuk dan keluar semasa.

Logik terbalik juga akan berfungsi: jika rentetan chars kosong (chars = ""), kemudian kembalikan data dan keluar sekarang; Jika tidak, hubungi fungsi RemoveChars :

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))))

Mana -mana pendekatan yang anda pilih, hasilnya akan sama:

Petua. Tugas yang sama dapat dicapai dengan mudah dengan ungkapan biasa. Untuk maklumat lanjut, sila lihat Excel Regex untuk mengeluarkan watak khas.

Cara Menulis Lambda Rekursif di Excel

Saya ingin memulakan dengan penafian :) Tidak ada cara yang didokumenkan untuk membina lambdas rekursif di Excel, yang dapat dijelaskan memandangkan fungsi itu baru. Saya akan berkongsi jalan saya, yang mungkin atau mungkin tidak membantu anda.

Buat formula teras

Umumnya, anda bermula dengan menulis formula teras yang mencontohi tingkah laku yang dikehendaki fungsi lambda anda. Dalam kes kami, matlamat utama adalah untuk menggantikan watak -watak tertentu tanpa apa -apa, dan Excel sudah mempunyai instrumen yang ideal untuk ini - fungsi pengganti:

Pengganti (teks, old_text, new_text, [instance_num])

Untuk menggantikan, kita perlu membekalkan:

• Teks - teks untuk menggantikan watak. Dalam kes kami, ia adalah rentetan teks dalam A2.
• Old_text - watak yang akan diganti. Kita perlu menyemak setiap watak dalam D2, dan ia menjadi alasan untuk bermula dengan yang paling kiri. Fungsi kiri dengan mudah dapat mengambilnya untuk kita:

  LEFT(D2, 1)

• New_text - watak untuk menggantikan old_text dengan. Jelas sekali, ia adalah rentetan kosong ("").
• Instance_num adalah pilihan dan tidak diperlukan dalam kes kami, jadi ia ditinggalkan.

Hasilnya, formula teras kami mengambil bentuk ini:

=SUBSTITUTE(A2, LEFT(D2, 1), "")

Kerana pengganti hanya boleh melakukan satu pengganti pada satu masa, ia perlu dilaksanakan seberapa banyak kali kerana terdapat aksara dalam senarai pengecualian dalam D2. Persoalannya - bagaimana kita memaksanya untuk mengendalikan watak seterusnya? Dan inilah jawapannya:

Dengan setiap lelaran, kami akan melepaskan satu watak dari kiri, iaitu watak yang telah dipandang. Fungsi yang betul dalam kombinasi dengan Len dengan mudah boleh melakukannya:

=RIGHT(D2, LEN(D2) -1)

Sila perhatikan bahawa setiap pengganti berikutnya menggunakan hasil dari pengganti sebelumnya sebagai hujah teks , iaitu ia menjadikan penggantian bukan dalam rentetan asal (A2), tetapi dalam rentetan yang dikembalikan oleh fungsi pengganti sebelumnya (B2):

Tukar formula teras ke fungsi lambda

Seperti yang anda ingat, fungsi tersuai kami sepatutnya dinamakan RemoveChars , dan ia akan mempunyai 2 parameter: data dan chars .

Tugas anda adalah untuk mengarahkan fungsi mengenai cara mengira setiap parameter:

• Data - rentetan untuk menggantikan aksara. Ia disediakan oleh formula pengganti.
• Chars - watak -watak untuk dikeluarkan. Ia disediakan oleh formula yang betul.

Apa yang anda lakukan hanyalah meletakkan kedua -dua formula yang dibincangkan di atas di dalam fungsi RemoveChars yang memisahkannya dengan koma atau watak apa pun yang digunakan untuk memisahkan hujah -hujah fungsi dalam Excel anda (ditentukan oleh pemisah senarai yang ditetapkan dalam tetapan serantau ).

RemoveChars(SUBSTITUTE(A2, LEFT(D2, 1), ""), RIGHT(D2, LEN(D2) -1))

Mengingati bahawa Lambda beroperasi pada parameter dan bukan rujukan sel, langkah seterusnya adalah menukar A2 ke data dan D2 ke chars :

RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))

Fungsi RemoveChars dilakukan. Malangnya, tidak ada cara untuk mengujinya pada ketika ini, dan kita hanya boleh bergantung pada keputusan ujian sebelumnya dan melakukan debugging kemudian jika diperlukan.

Buat fungsi lambda panggilan sendiri secara rekursif

Ini adalah bahagian utama yang menjadikan "formula teoritis" menjadi penyelesaian kerja.

Seperti mana -mana lambda tersuai, anda bermula dengan mengisytiharkan parameter:

=LAMBDA(data, chars,

Seterusnya, anda menilai keadaan tertentu dan bergantung kepada hasilnya sama ada memohon rekursi atau keluar. Mewujudkan titik keluar adalah pertimbangan penting. Jika anda tidak berbuat demikian, formula anda tidak akan berfungsi dengan betul kerana ia tidak akan keluar dari gelung.

Dalam kes kami, fungsi IF memeriksa jika senarai chars tidak kosong (chars ""). Jika benar ( chars tidak kosong), kami memanggil fungsi RemoveChars. Jika palsu ( chars kosong), kami mengembalikan data bentuk dan keluarnya sekarang.

Ini adalah pendekatan generik:

=LAMBDA(data, chars, IF(chars"", RemoveChars(…), data))

Dan ini adalah formula sebenar dalam bentuk penuh:

=LAMBDA(data, chars, IF(chars"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))

Sebagai alternatif, anda boleh menyemak sama ada chars kosong (chars = ""). Jika benar, mengembalikan data dan keluar; Sekiranya Panggilan Salah Pemecatan.

Konsepnya:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(…)))

Formula Lengkap:

=LAMBDA(data, chars, IF(chars="", data, RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))))

Namakan fungsi yang ditentukan oleh Lambda anda

Menamakan Lambdas Rekursif tidak berbeza dengan menamakan rakan sebaya mereka yang tidak berulang:

1. Tekan pintasan Ctrl 3 untuk membuka Pengurus Nama, dan kemudian klik Baru .
2. Dalam kotak dialog nama baru , lakukan perkara berikut:
   • Dalam kotak nama , taipkan nama fungsi: RemoveChars .
   • Tinggalkan skop yang ditetapkan ke buku kerja .
   • Dalam merujuk kepada kotak, tampalkan formula Lambda anda memastikan ia bermula dengan tanda kesamaan.
   • Secara pilihan, masukkan penerangan parameter dalam kotak komen untuk rujukan lanjut.
   • Klik OK untuk menyimpan fungsi baru anda.

     

Cara Menggunakan Lambda Rekursif di Excel

Ia adalah bahagian yang paling mudah :) Sebaik sahaja fungsi Lambda anda mendapat nama, anda boleh menggunakannya seperti fungsi asli lain.

Dari perspektif pengguna akhir, sintaks fungsi tersuai kami semudah ini:

RemoveChars (data, chars)

Sebagai contoh, untuk membersihkan data dalam A2: A10 ( data ), kami menaip aksara yang tidak diingini dalam D2 ( chars ), dan kemudian masukkan formula di bawah dalam B2:

=RemoveChars(A2:A10, D2)

Seperti yang anda tahu, dalam Excel 356, setiap formula adalah formula array yang dinamik secara semula jadi. Oleh itu, mempunyai formula yang dimasukkan dalam hanya satu sel (B2), kami segera mendapatkan semua hasil (tingkah laku ini dipanggil tumpahan).

Jika anda lebih suka tingkah laku tradisional "satu formula - satu sel", kemudian gunakan rujukan sel untuk data (A2) dan mengunci alamat sel chars ($ d $ 2) dengan tanda $ untuk mengelakkannya daripada berubah apabila menyalin formula ke bawah:

=RemoveChars(A2, $D$2)

Formula di atas pergi ke B2, dan kemudian anda menyeretnya melalui B10:

Daripada menyenaraikan watak-watak yang dibebaskan dalam sel, anda boleh membekalkannya terus ke formula sebagai rentetan teks:

=RemoveChars(A2:A10, "_^*/&%")

Nota. Kerana fungsi pengganti yang digunakan dalam formula teras adalah sensitif kes , fungsi tersuai kami merawat huruf besar dan huruf kecil sebagai aksara yang berbeza. Jika anda ingin mengeluarkan watak tertentu, katakan "x", tanpa mengira kes huruf, maka sertakan kedua -dua "x" dan "x" dalam rentetan chars .

Memahami rekursi

Petunjuk untuk memahami lambdas rekursif mengetahui dengan tepat apa yang berlaku dengan setiap lelaran . Dalam contoh kami, terdapat dua perkara seperti itu:

• Hasil daripada pengganti terdahulu menjadi parameter data baru untuk panggilan seterusnya penyingkiran , seolah -olah kita menggunakan fungsi pengganti bersarang.
• Rentetan chars dikurangkan oleh satu aksara. Anda boleh memikirkannya sebagai sejenis undur. Sebaik sahaja rentetan chars menjadi kosong, proses lelaran berhenti, dan formula mengembalikan data dalam bentuknya sekarang sebagai hasil akhir.

Jadual di bawah dapat membantu anda menggambarkan proses rekursi dengan lebih baik:

Lebih banyak contoh fungsi lambda rekursif

Dalam contoh di bawah, kami akan melihat bagaimana anda boleh melanjutkan fungsi Lambda yang sedia ada dengan fungsi baru untuk menyesuaikannya untuk keperluan anda.

Contoh 1. Keluarkan aksara yang tidak diingini dan memangkas ruang tambahan

Selain daripada pelbagai aksara yang tidak relevan, data anda juga mungkin mengandungi ruang yang berlebihan. Untuk menghilangkannya, anda boleh menghilangkannya di dalam trim seperti yang anda lakukan dengan fungsi terbina dalam:

=TRIM(RemoveChars(A2:A10, F2))

Untuk melihat kesannya, sila bandingkan hasil dalam lajur B dan D. Dalam kes yang kedua, bukan sahaja aksara yang tidak diingini dikeluarkan, tetapi juga semua ruang utama dan trailing, sementara ruang dalaman dikurangkan kepada satu watak ruang antara kata -kata:

Sekiranya anda tidak mahu bersarang dengan bersarang setiap kali, anda boleh melakukannya sebagai persediaan satu kali di dalam lambda itu sendiri:

=LAMBDA(data, chars, TRIM(IF(chars"", RemoveTrim(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data)))

Fungsi kami yang lebih baik dinamakan RemoveTrim dan ia berfungsi seperti pesona:

=RemoveTrim(A2:A10, D2)

Contoh 2. Gantikan pelbagai aksara dengan watak yang sama

Dalam senario tertentu, masuk akal untuk menggantikan beberapa watak yang berbeza dengan watak lain yang anda tentukan. Malah, ia adalah fungsi RemoveChars kami sebenarnya - menggantikan aksara yang ditentukan dengan rentetan kosong (""). Walau bagaimanapun, watak penggantian adalah hardcoded sementara kami mahu menentukannya secara langsung dalam formula. Untuk melakukannya, kita hanya perlu menambah satu lagi parameter, katakan new_char , ke fungsi.

Oleh itu, fungsi baru kami, mari namakannya , akan mempunyai sintaks berikut:

Menggantikanechars (data, chars, new_char)

Untuk mengubah RemoveChars menjadi pengganti , terdapat 3 suntingan kecil yang akan dibuat:

• Tentukan parameter 3 ^rd - new_char .
• Gantikan rentetan kosong hardcoded ("") dengan new_char .
• Lulus New_char ke fungsi penggantian sebagai hujah 3 ^rd .

Hasilnya, kami mendapat satu lagi lambda berguna untuk menggantikan pelbagai aksara rekursif:

=LAMBDA(data, chars, new_char, IF(chars"", ReplaceChars(SUBSTITUTE(data, LEFT(chars), new_char), RIGHT(chars, LEN(chars)-1), new_char), data))

Sebagai contoh, jika pembekal anda tiba -tiba mengubah format ID atau SKU mereka, anda boleh menggantikan semua aksara yang tidak sesuai (E1) dengan yang sesuai (E2) menggunakan formula ini:

=ReplaceChars(A2:A6, E1, E2)

Contoh 3. Gantikan pelbagai nilai dengan nilai lain sekaligus

Contoh ini adalah lanjutan logik yang sebelum ini. Kali ini, kita akan menggantikan keseluruhan perkataan (atau rentetan) daripada watak tunggal, dan setiap perkataan akan mempunyai nilai penggantian sendiri.

Kerana nilai lama dan baru akan diletakkan di dalam sel berasingan (seperti yang ditunjukkan dalam tangkapan skrin di bawah), fungsi yang tepat yang kami gunakan dalam contoh sebelumnya tidak akan berfungsi. Untuk gelung melalui pasangan lama/baru, kita perlu memikirkan sesuatu yang lain. Hmm, nampaknya ada fungsi dalam Excel untuk memindahkan bilangan baris dan lajur tertentu dari sel tertentu. Ya, itu diimbangi!

Dengan kaedah utama yang ditubuhkan, bukan masalah besar untuk menulis fungsi penggantian :

PenggantiAll (data, lama, baru)

Untuk data , kami menggunakan fungsi pengganti dalam bentuk asasnya hanya untuk menggantikan nilai lama dengan yang baru:

SUBSTITUTE(data, old, new)

Untuk mendapatkan nilai lama , kami akan mulakan dengan sel paling atas pada senarai lama dan menggerakkan 1 baris ke bawah dengan setiap interaksi:

OFFSET(old, 1, 0)

Untuk mendapatkan nilai baru , kami akan melakukan perkara yang sama tetapi, tentu saja, dalam senarai baru :

OFFSET(new, 1, 0)

Akhirnya, laksanakan strategi keluar yang sudah biasa dengan bantuan IF, dan Lambda Rekursif yang baru anda siap untuk digunakan (jangan lupa namakannya di Pengurus Nama :)

=LAMBDA(data, old, new, IF(old"", ReplaceAll(SUBSTITUTE(data, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0) ), data))

Dengan data sumber dalam A2: A10, nilai lama dalam lajur D bermula dalam D2, dan nilai -nilai baru dalam lajur E bermula di E2, anda boleh melakukan beberapa penggantian dengan formula mudah ini:

=ReplaceAll(A2:A10, D2, E2)

Hasilnya, formula tunggal dalam B2 menggantikan semua singkatan dalam A2: A10 dengan nama penuh yang sepadan:

Rekursif Lambdas vs Fungsi Ditetapkan Pengguna VBA

Pengguna Excel maju dengan latar belakang pengaturcaraan mungkin ingin melihat bagaimana fungsi Lambda rekursif berkorelasi dengan kod VBA yang setanding. Nah, mari kita lihat.

Lambda rekursif untuk membuang pelbagai aksara

Seperti yang anda faham, ini adalah pseudocode yang tidak berfungsi. Kami memasukkannya ke dalam editor VBA untuk mewakili algoritma dalam bentuk yang biasa untuk lebih memahami apa yang sedang berlaku :)

= Lambda (data, chars, 'mengisytiharkan parameter jika (chars "" , ' periksa jika chars tidak kosongkan kosong (pengganti (data, kiri, 1), "" ), 'jika benar, hubungi penyingkiran yang betul (chars, len (chars) -1)), data'

Fungsi yang ditentukan pengguna untuk menghapuskan pelbagai aksara rekursif

Dan ini adalah bagaimana fungsi yang ditentukan oleh pengguna yang sama boleh ditulis dalam VBA:

Fungsi RemoveCharsRecursive ( data sebagai rentetan , chars sebagai rentet​​​​

Fungsi yang ditentukan pengguna untuk mengalih keluar pelbagai aksara yang tidak berulang

Fungsi yang sama juga boleh ditulis menggunakan kaedah bukan rekursif. Dalam kes ini, kami menulis RemoveChars sebagai fungsi yang berasingan dan memanggilnya dari dalam fungsi RemoveCharsNonRecursive apabila rentetan chars tidak kosong.

Fungsi RemoveCharsNonRecursive ( data sebagai rentetan , chars sebagai rentet​​​​​​​

Tugas yang sama dapat dicapai dengan cara yang berbeza. Anda boleh melangkah melalui watak pengecualian dari 1 hingga Len (chars) dan menggantikan chars yang terdapat dalam data dengan rentetan kosong. Fungsi pertengahan digunakan untuk mengekstrak setiap aksara tunggal dari rentetan chars satu demi satu.

Fungsi RemoveCharsNonRecursive2 ( data sebagai rentetan , chars sebagai rentet​

Apakah faedah menggunakan Lambdas berbanding dengan fungsi yang ditentukan oleh pengguna VBA? Pertama sekali, mereka tidak memerlukan buku kerja menyimpan sebagai fail .xlsm yang dibolehkan makro dan menjimatkan masalah untuk membolehkan makro pada setiap pembukaan.

Mudah -mudahan, tutorial ini telah membantu anda mendapatkan wawasan tentang apa yang kelihatan seperti lambda rekursif dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Amalkan buku kerja untuk dimuat turun

Contoh Lambda Rekursif (fail .xlsx) Fungsi yang ditentukan oleh pengguna VBA (fail .xlsm)

Atas ialah kandungan terperinci Fungsi lambda rekursif dalam excel dengan contoh. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!