Konten
Saat ini, Microsoft Excel memiliki hampir lima ratus fungsi lembar kerja yang tersedia melalui jendela Fungsi Wizard – tombol fx di bilah rumus. Ini adalah set yang sangat layak, tetapi, bagaimanapun, hampir setiap pengguna cepat atau lambat menghadapi situasi di mana daftar ini tidak berisi fungsi yang dia butuhkan - hanya karena tidak ada di Excel.
Sampai saat ini, satu-satunya cara untuk mengatasi masalah ini adalah makro, yaitu menulis fungsi yang ditentukan pengguna Anda sendiri (UDF = Fungsi yang Ditentukan Pengguna) dalam Visual Basic, yang memerlukan keterampilan pemrograman yang tepat dan terkadang tidak mudah sama sekali. Namun, dengan pembaruan Office 365 terbaru, situasi telah berubah menjadi lebih baik - fungsi "pembungkus" khusus telah ditambahkan ke Excel lAMBDA. Dengan bantuannya, tugas membuat fungsi Anda sendiri sekarang diselesaikan dengan mudah dan indah.
Mari kita lihat prinsip penggunaannya pada contoh berikut.
Seperti yang kemungkinan besar Anda ketahui, Excel memiliki beberapa fungsi penguraian tanggal yang memungkinkan Anda menentukan jumlah hari, bulan, minggu, dan tahun untuk tanggal tertentu. Tapi entah kenapa tidak ada fungsi yang menentukan angka seperempat, yang juga sering dibutuhkan, bukan? Mari perbaiki kekurangan ini dan berkreasi dengan lAMBDA memiliki fungsi baru untuk memecahkan masalah ini.
Langkah 1. Tulis rumusnya
Mari kita mulai dengan fakta bahwa secara manual dengan cara biasa kita akan menulis rumus di sel lembar yang menghitung apa yang kita butuhkan. Dalam hal angka seperempat, ini dapat dilakukan, misalnya, seperti ini:
Langkah 2. Mengakhiri LAMBDA dan menguji
Sekarang saatnya untuk menerapkan fungsi LAMBDA baru dan membungkus rumus kita di dalamnya. Sintaks fungsinya adalah sebagai berikut:
=LAMBDA(Variabel1; Variabel2; ... VariabelN ; Ekspresi)
di mana nama satu atau beberapa variabel dicantumkan terlebih dahulu, dan argumen terakhir selalu berupa rumus atau ekspresi terhitung yang menggunakannya. Nama variabel tidak boleh terlihat seperti alamat sel dan tidak boleh mengandung titik.
Dalam kasus kami, hanya akan ada satu variabel – tanggal kami menghitung angka kuartal. Mari kita panggil variabel untuk itu, katakanlah, d. Kemudian bungkus rumus kita dalam suatu fungsi lAMBDA dan mengganti alamat sel asli A2 dengan nama variabel fiktif, kita mendapatkan:
Harap dicatat bahwa setelah transformasi seperti itu, rumus kami (sebenarnya, benar!) Mulai menghasilkan kesalahan, karena sekarang tanggal asli dari sel A2 tidak ditransfer ke sana. Untuk pengujian dan keyakinan, Anda dapat meneruskan argumen dengan menambahkannya setelah fungsi lAMBDA dalam kurung:
Langkah 3. Buat nama
Sekarang untuk bagian yang mudah dan menyenangkan. Kita buka Manajer nama tab rumus (Rumus — Manajer Nama) dan buat nama baru dengan tombol membuat (Membuat). Munculkan dan masukkan nama untuk fungsi masa depan kita (misalnya, Nomkvartala), dan di lapangan Link (Referensi) salin dengan hati-hati dari bilah rumus dan tempel fungsi kami lAMBDA, hanya tanpa argumen terakhir (A2):
Semuanya. Setelah mengklik OK fungsi yang dibuat dapat digunakan di sel mana pun di lembar mana pun dari buku kerja ini:
Gunakan di buku lain
LAMBDA dan array dinamis
Fungsi khusus dibuat dengan fungsi lAMBDA berhasil mendukung pekerjaan dengan array dinamis baru dan fungsinya (FILTER, UNIK, GRADE) ditambahkan ke Microsoft Excel pada tahun 2020.
Katakanlah kita ingin membuat fungsi baru yang ditentukan pengguna yang akan membandingkan dua daftar dan mengembalikan perbedaan di antara mereka – elemen-elemen dari daftar pertama yang tidak ada di daftar kedua. Pekerjaan hidup, bukan? Sebelumnya, untuk ini mereka menggunakan salah satu fungsi ala VPR (VLOOKUP), atau PivotTable, atau kueri Power Query. Sekarang Anda dapat melakukannya dengan satu rumus:
Dalam versi bahasa Inggris itu akan menjadi:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Disini fungsinya COUNTIF menghitung jumlah kemunculan setiap elemen dari daftar pertama di daftar kedua, dan kemudian fungsinya FILTER memilih hanya mereka yang tidak memiliki kejadian ini. Dengan membungkus struktur ini dalam lAMBDA dan membuat rentang bernama berdasarkan itu dengan nama, misalnya, PENCARIAN DISTRIBUSI – kita akan mendapatkan fungsi praktis yang mengembalikan hasil membandingkan dua daftar dalam bentuk larik dinamis:
Jika data sumber tidak biasa, tetapi tabel "pintar", fungsi kami juga akan mengatasi tanpa masalah:
Contoh lain adalah memisahkan teks secara dinamis dengan mengonversinya menjadi XML dan kemudian menguraikannya sel demi sel menggunakan fungsi FILTER.XML yang baru saja kita uraikan. Agar tidak mereproduksi formula kompleks ini secara manual setiap saat, akan lebih mudah untuk membungkusnya dalam LAMBDA dan membuat rentang dinamis berdasarkan itu, yaitu fungsi baru yang ringkas dan nyaman, menamainya, misalnya, RAZDTEXT:
Argumen pertama dari fungsi ini akan menjadi sel dengan teks sumber, dan yang kedua - karakter pemisah, dan itu akan mengembalikan hasilnya dalam bentuk array dinamis horizontal. Kode fungsi akan menjadi sebagai berikut:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Daftar contoh tidak ada habisnya – dalam situasi apa pun di mana Anda sering kali harus memasukkan rumus panjang dan rumit yang sama, fungsi LAMBDA akan membuat hidup terasa lebih mudah.
Pencacahan karakter secara rekursif
Semua contoh sebelumnya hanya menunjukkan satu, yang paling jelas, sisi fungsi LAMBDA – penggunaannya sebagai “pembungkus” untuk membungkus rumus panjang di dalamnya dan menyederhanakan inputnya. Faktanya, LAMBDA memiliki sisi lain yang jauh lebih dalam yang mengubahnya menjadi bahasa pemrograman yang lengkap.
Faktanya adalah bahwa fitur penting yang mendasar dari fungsi LAMBDA adalah kemampuan untuk mengimplementasikannya dalam pengulangan – logika perhitungan, ketika dalam proses perhitungan fungsi memanggil dirinya sendiri. Dari kebiasaan, mungkin terdengar menyeramkan, tetapi dalam pemrograman, rekursi adalah hal yang biasa. Bahkan dalam makro di Visual Basic, Anda dapat mengimplementasikannya, dan sekarang, seperti yang Anda lihat, ia telah hadir di Excel. Mari kita coba memahami teknik ini dengan contoh praktis.
Misalkan kita ingin membuat fungsi yang ditentukan pengguna yang akan menghapus semua karakter yang diberikan dari teks sumber. Kegunaan fungsi seperti itu, saya pikir, tidak perlu Anda buktikan – akan sangat mudah untuk menghapus data input yang berserakan dengan bantuannya, bukan?
Namun, dibandingkan dengan contoh non-rekursif sebelumnya, dua kesulitan menunggu kita.
- Kita harus membuat nama untuk fungsi kita sebelum kita mulai menulis kodenya, karena di dalamnya, nama ini sudah akan digunakan untuk memanggil fungsi itu sendiri.
- Memasukkan fungsi rekursif seperti itu ke dalam sel dan men-debugnya dengan menentukan argumen dalam tanda kurung setelah LAMBDA (seperti yang kita lakukan sebelumnya) tidak akan berfungsi. Anda harus segera membuat fungsi "dari awal" di Manajer nama (Manajer Nama).
Mari kita panggil fungsi kita, katakanlah, CLEAN dan kita ingin memiliki dua argumen – teks yang akan dibersihkan dan daftar karakter yang dikecualikan sebagai string teks:
Mari kita buat, seperti yang kita lakukan sebelumnya, pada tab rumus в Manajer nama rentang bernama, beri nama CLEAR dan masukkan di bidang Jarak konstruksi berikut:
=LAMBDA(t;d;JIKA(d=””;t;CLEAR(SUBSTITUTE(t;KIRI(d);””);MID(d;2;255))))
Di sini variabel t adalah teks asli yang akan dihapus, dan d adalah daftar karakter yang akan dihapus.
Semuanya bekerja seperti ini:
iterasi 1
Fragmen SUBSTITUTE(t;LEFT(d);”"), seperti yang Anda duga, menggantikan karakter pertama dari karakter kiri dari himpunan d yang akan dihapus dalam teks sumber t dengan string teks kosong, yaitu menghilangkan tanda “ SEBUAH". Sebagai hasil antara, kita mendapatkan:
Vsh zkz n 125 rubel.
iterasi 2
Kemudian fungsi memanggil dirinya sendiri dan sebagai input (argumen pertama) menerima apa yang tersisa setelah dibersihkan pada langkah sebelumnya, dan argumen kedua adalah string karakter yang dikecualikan yang dimulai bukan dari karakter pertama, tetapi dari karakter kedua, yaitu “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” tanpa inisial “A” – ini dilakukan oleh fungsi MID. Seperti sebelumnya, fungsi mengambil karakter pertama dari kiri yang tersisa (B) dan menggantinya dalam teks yang diberikan kepadanya (Zkz n 125 rubel) dengan string kosong – kita dapatkan sebagai hasil antara:
125 ru.
iterasi 3
Fungsi memanggil dirinya lagi, menerima sebagai argumen pertama apa yang tersisa dari teks yang akan dihapus pada iterasi sebelumnya (Bsh zkz n 125 ru.), Dan sebagai argumen kedua, himpunan karakter yang dikecualikan dipotong oleh satu karakter lagi untuk kiri, yaitu "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," tanpa inisial "B". Kemudian lagi mengambil karakter pertama dari kiri (B) dari set ini dan menghapusnya dari teks – kita mendapatkan:
sh zkz n 125 ru.
Dan seterusnya – saya harap Anda mendapatkan idenya. Dengan setiap iterasi, daftar karakter yang akan dihapus akan terpotong di sebelah kiri, dan kami akan mencari dan mengganti karakter berikutnya dari himpunan dengan kekosongan.
Ketika semua karakter habis, kita harus keluar dari loop – peran ini hanya dilakukan oleh fungsi IF (JIKA), di mana desain kami dibungkus. Jika tidak ada karakter yang tersisa untuk dihapus (d=””), maka fungsi seharusnya tidak lagi memanggil dirinya sendiri, tetapi hanya mengembalikan teks yang akan dihapus (variabel t) dalam bentuk akhirnya.
Iterasi sel secara rekursif
Demikian pula, Anda dapat menerapkan enumerasi sel secara rekursif dalam rentang tertentu. Misalkan kita ingin membuat fungsi lambda bernama DAFTAR PENGGANTIAN untuk penggantian grosir fragmen dalam teks sumber sesuai dengan daftar referensi yang diberikan. Hasilnya akan terlihat seperti ini:
Itu. di fungsi kami DAFTAR PENGGANTIAN akan ada tiga argumen:
- sel dengan teks untuk diproses (alamat sumber)
- sel pertama kolom dengan nilai untuk dicari dari pencarian
- sel pertama kolom dengan nilai pengganti dari pencarian
Fungsi harus pergi dari atas ke bawah dalam direktori dan mengganti secara berurutan semua opsi dari kolom kiri Mencari ke opsi yang sesuai dari kolom kanan Pengganti. Anda dapat mengimplementasikan ini dengan fungsi lambda rekursif berikut:
Shift down pada setiap iterasi diimplementasikan oleh fungsi excel standar PEMBUANGAN (MENGIMBANGI), yang dalam hal ini memiliki tiga argumen – rentang asli, pergeseran baris (1) dan pergeseran kolom (0).
Nah, segera setelah kita mencapai akhir direktori (n = “”), kita harus mengakhiri rekursi – kita berhenti memanggil diri kita sendiri dan menampilkan apa yang telah terakumulasi setelah semua penggantian dalam variabel teks sumber t.
Itu saja. Tidak ada makro rumit atau kueri Power Query – seluruh tugas diselesaikan dengan satu fungsi.
- Cara menggunakan fungsi array dinamis baru Excel: FILTER, SORT, UNIC
- Mengganti dan membersihkan teks dengan fungsi SUBSTITUTE
- Membuat makro dan fungsi yang ditentukan pengguna (UDF) di VBA