Περιεχόμενα
Προς το παρόν, το Microsoft Excel έχει σχεδόν πεντακόσιες λειτουργίες φύλλου εργασίας διαθέσιμες μέσω του κουμπιού – παραθύρου Οδηγός λειτουργιών fx στη γραμμή τύπων. Αυτό είναι ένα πολύ αξιοπρεπές σύνολο, αλλά, παρόλα αυτά, σχεδόν κάθε χρήστης αργά ή γρήγορα αντιμετωπίζει μια κατάσταση όπου αυτή η λίστα δεν περιέχει τη λειτουργία που χρειάζεται – απλώς και μόνο επειδή δεν βρίσκεται στο Excel.
Μέχρι τώρα, ο μόνος τρόπος για να λυθεί αυτό το πρόβλημα ήταν οι μακροεντολές, δηλαδή η εγγραφή της δικής σας συνάρτησης καθορισμένης από το χρήστη (UDF = User Defined Function) στη Visual Basic, η οποία απαιτεί κατάλληλες δεξιότητες προγραμματισμού και, μερικές φορές, δεν είναι καθόλου εύκολη. Ωστόσο, με τις πιο πρόσφατες ενημερώσεις του Office 365, η κατάσταση έχει αλλάξει προς το καλύτερο - μια ειδική λειτουργία "wrapper" προστέθηκε στο Excel LAMBDA. Με τη βοήθειά του, το έργο της δημιουργίας των δικών σας λειτουργιών λύνεται πλέον εύκολα και όμορφα.
Ας δούμε την αρχή της χρήσης του στο παρακάτω παράδειγμα.
Όπως πιθανότατα γνωρίζετε, το Excel διαθέτει πολλές συναρτήσεις ανάλυσης ημερομηνιών που σας επιτρέπουν να προσδιορίσετε τον αριθμό της ημέρας, του μήνα, της εβδομάδας και του έτους για μια δεδομένη ημερομηνία. Αλλά για κάποιο λόγο δεν υπάρχει συνάρτηση που να καθορίζει τον αριθμό του τετάρτου, που επίσης χρειάζεται συχνά, σωστά; Ας διορθώσουμε αυτό το μειονέκτημα και ας δημιουργήσουμε με LAMBDA δική σας νέα λειτουργία για την επίλυση αυτού του προβλήματος.
Βήμα 1. Γράψτε τον τύπο
Ας ξεκινήσουμε με το γεγονός ότι χειροκίνητα με τον συνηθισμένο τρόπο θα γράψουμε έναν τύπο σε ένα κελί φύλλου που θα υπολογίζει τι χρειαζόμαστε. Στην περίπτωση του αριθμού τριμήνου, αυτό μπορεί να γίνει, για παράδειγμα, ως εξής:
Βήμα 2. Αναδίπλωση σε LAMBDA και δοκιμή
Τώρα ήρθε η ώρα να εφαρμόσουμε τη νέα συνάρτηση LAMBDA και να τυλίξουμε τη φόρμουλα μας σε αυτήν. Η σύνταξη της συνάρτησης είναι η εξής:
=LAMBDA(Μεταβλητή 1; Μεταβλητή 2. ... ΜεταβλητήN ; Έκφραση)
όπου τα ονόματα μιας ή περισσότερων μεταβλητών αναφέρονται πρώτα και το τελευταίο όρισμα είναι πάντα ένας τύπος ή μια υπολογισμένη έκφραση που τις χρησιμοποιεί. Τα ονόματα των μεταβλητών δεν πρέπει να μοιάζουν με διευθύνσεις κελιών και δεν πρέπει να περιέχουν τελείες.
Στην περίπτωσή μας, θα υπάρχει μόνο μία μεταβλητή - η ημερομηνία για την οποία υπολογίζουμε τον αριθμό τριμήνου. Ας ονομάσουμε τη μεταβλητή για αυτήν, ας πούμε, d. Στη συνέχεια τυλίγοντας τον τύπο μας σε μια συνάρτηση LAMBDA και αντικαθιστώντας τη διεύθυνση του αρχικού κελιού A2 με ένα εικονικό όνομα μεταβλητής, παίρνουμε:
Λάβετε υπόψη ότι μετά από έναν τέτοιο μετασχηματισμό, ο τύπος μας (στην πραγματικότητα, σωστός!) άρχισε να παράγει ένα σφάλμα, επειδή τώρα η αρχική ημερομηνία από το κελί A2 δεν μεταφέρεται σε αυτόν. Για έλεγχο και σιγουριά, μπορείτε να του μεταβιβάσετε ορίσματα προσθέτοντάς τα μετά τη συνάρτηση LAMBDA σε παρένθεση:
Βήμα 3. Δημιουργήστε ένα όνομα
Τώρα για το εύκολο και διασκεδαστικό κομμάτι. Είμαστε ανοιχτά Όνομα Διευθυντής αυτί τύπος (Τύπες — Διαχειριστής ονόματος) και δημιουργήστε ένα νέο όνομα με το κουμπί Δημιουργία (Δημιουργώ). Βρείτε και εισαγάγετε ένα όνομα για τη μελλοντική μας συνάρτηση (για παράδειγμα, Νομκβαρτάλα), και στο χωράφι Σύνδεσμος (Αναφορά) αντιγράψτε προσεκτικά από τη γραμμή τύπων και επικολλήστε τη συνάρτησή μας LAMBDA, μόνο χωρίς το τελευταίο όρισμα (A2):
Τα παντα. Αφού κάνετε κλικ στο OK η συνάρτηση που δημιουργήθηκε μπορεί να χρησιμοποιηθεί σε οποιοδήποτε κελί σε οποιοδήποτε φύλλο αυτού του βιβλίου εργασίας:
Χρήση σε άλλα βιβλία
LAMBDA και δυναμικοί πίνακες
Προσαρμοσμένες συναρτήσεις που δημιουργούνται με μια συνάρτηση LAMBDA υποστηρίζει με επιτυχία την εργασία με νέους δυναμικούς πίνακες και τις λειτουργίες τους (FILTER, UNIK, ΒΑΘΜΟΣ) προστέθηκε στο Microsoft Excel το 2020.
Ας υποθέσουμε ότι θέλουμε να δημιουργήσουμε μια νέα συνάρτηση που ορίζεται από το χρήστη που θα συγκρίνει δύο λίστες και θα επιστρέφει τη διαφορά μεταξύ τους – εκείνα τα στοιχεία από την πρώτη λίστα που δεν βρίσκονται στη δεύτερη. Έργο ζωής, έτσι δεν είναι; Προηγουμένως, για αυτό χρησιμοποιούσαν είτε τις συναρτήσεις a la VPR (VLOOKUP), ή Συγκεντρωτικοί Πίνακες ή ερωτήματα Power Query. Τώρα μπορείτε να κάνετε με έναν τύπο:
Στην αγγλική έκδοση θα είναι:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Εδώ η συνάρτηση COUNTIF μετράει τον αριθμό των εμφανίσεων κάθε στοιχείου της πρώτης λίστας στη δεύτερη και μετά τη συνάρτηση FILTER επιλέγει μόνο όσους από αυτούς δεν είχαν αυτές τις εμφανίσεις. Τυλίγοντας αυτή τη δομή μέσα LAMBDA και δημιουργώντας ένα εύρος με όνομα με βάση αυτό με ένα όνομα, για παράδειγμα, ΔΙΑΝΟΜΗ ΑΝΑΖΗΤΗΣΗΣ – θα λάβουμε μια βολική συνάρτηση που επιστρέφει το αποτέλεσμα της σύγκρισης δύο λιστών με τη μορφή δυναμικού πίνακα:
Εάν τα δεδομένα προέλευσης δεν είναι συνηθισμένοι, αλλά «έξυπνοι» πίνακες, η λειτουργία μας θα αντιμετωπίσει επίσης χωρίς προβλήματα:
Ένα άλλο παράδειγμα είναι ο δυναμικός διαχωρισμός κειμένου μετατρέποντάς το σε XML και στη συνέχεια αναλύοντας το κελί προς κελί χρησιμοποιώντας τη συνάρτηση FILTER.XML που αναλύσαμε πρόσφατα. Για να μην αναπαράγετε αυτή τη σύνθετη φόρμουλα χειροκίνητα κάθε φορά, θα είναι ευκολότερο να την τυλίξετε σε LAMBDA και να δημιουργήσετε μια δυναμική περιοχή βάσει αυτής, δηλαδή μια νέα συμπαγή και βολική λειτουργία, ονομάζοντάς την, για παράδειγμα, RAZDTEXT:
Το πρώτο όρισμα αυτής της συνάρτησης θα είναι το κελί με το κείμενο προέλευσης και το δεύτερο - ο διαχωριστικός χαρακτήρας και θα επιστρέψει το αποτέλεσμα με τη μορφή ενός οριζόντιου δυναμικού πίνακα. Ο κωδικός λειτουργίας θα είναι ο εξής:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Η λίστα των παραδειγμάτων είναι ατελείωτη – σε κάθε περίπτωση όπου πρέπει συχνά να εισάγετε την ίδια μακρά και δυσκίνητη φόρμουλα, η λειτουργία LAMBDA θα κάνει τη ζωή αισθητά πιο εύκολη.
Αναδρομική απαρίθμηση χαρακτήρων
Όλα τα προηγούμενα παραδείγματα έδειξαν μόνο μία, την πιο προφανή, πλευρά της συνάρτησης LAMBDA – τη χρήση της ως «περιτύλιγμα» για την αναδίπλωση μεγάλων τύπων σε αυτήν και την απλοποίηση της εισαγωγής τους. Στην πραγματικότητα, η LAMBDA έχει μια άλλη, πολύ βαθύτερη, πλευρά που τη μετατρέπει σε σχεδόν μια πλήρη γλώσσα προγραμματισμού.
Το γεγονός είναι ότι ένα θεμελιωδώς σημαντικό χαρακτηριστικό των συναρτήσεων LAMBDA είναι η δυνατότητα εφαρμογής τους αναδρομή – λογική των υπολογισμών, όταν στη διαδικασία υπολογισμού η συνάρτηση καλεί τον εαυτό της. Από τη συνήθεια, μπορεί να ακούγεται ανατριχιαστικό, αλλά στον προγραμματισμό, η αναδρομή είναι κάτι συνηθισμένο. Ακόμη και σε μακροεντολές στη Visual Basic, μπορείτε να το εφαρμόσετε και τώρα, όπως μπορείτε να δείτε, ήρθε στο Excel. Ας προσπαθήσουμε να κατανοήσουμε αυτήν την τεχνική με ένα πρακτικό παράδειγμα.
Ας υποθέσουμε ότι θέλουμε να δημιουργήσουμε μια συνάρτηση που καθορίζεται από το χρήστη που θα αφαιρεί όλους τους χαρακτήρες που δίνονται από το κείμενο προέλευσης. Η χρησιμότητα μιας τέτοιας λειτουργίας, νομίζω, δεν χρειάζεται να αποδείξετε - θα ήταν πολύ βολικό να διαγράψετε τα άχρηστα δεδομένα εισόδου με τη βοήθειά της, σωστά;
Ωστόσο, σε σύγκριση με τα προηγούμενα, μη επαναλαμβανόμενα παραδείγματα, μας περιμένουν δύο δυσκολίες.
- Θα πρέπει να βρούμε ένα όνομα για τη συνάρτησή μας πριν αρχίσουμε να γράφουμε τον κώδικά της, γιατί σε αυτήν, αυτό το όνομα θα χρησιμοποιείται ήδη για την κλήση της ίδιας της συνάρτησης.
- Η εισαγωγή μιας τέτοιας αναδρομικής συνάρτησης σε ένα κελί και ο εντοπισμός σφαλμάτων της καθορίζοντας ορίσματα σε αγκύλες μετά το LAMBDA (όπως κάναμε νωρίτερα) δεν θα λειτουργήσει. Θα πρέπει να δημιουργήσετε μια συνάρτηση αμέσως "από την αρχή". Όνομα Διευθυντής (Όνομα διαχειριστή).
Ας ονομάσουμε τη συνάρτησή μας, ας πούμε, CLEAN και θα θέλαμε να έχει δύο ορίσματα – το κείμενο που πρέπει να καθαριστεί και τη λίστα των εξαιρεμένων χαρακτήρων ως συμβολοσειρά κειμένου:
Ας δημιουργήσουμε, όπως κάναμε νωρίτερα, στην καρτέλα τύπος в Διαχειριστής ονόματος με όνομα εύρος, ονομάστε το CLEAR και πληκτρολογήστε στο πεδίο Σειρά παρακάτω κατασκευή:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Εδώ η μεταβλητή t είναι το αρχικό κείμενο που πρέπει να διαγραφεί και d είναι η λίστα των χαρακτήρων που πρέπει να διαγραφούν.
Όλα λειτουργούν ως εξής:
Επανάληψη 1
Το τμήμα SUBSTITUTE(t;LEFT(d);""), όπως μπορείτε να μαντέψετε, αντικαθιστά τον πρώτο χαρακτήρα από τον αριστερό χαρακτήρα από το σύνολο d που πρόκειται να διαγραφεί στο κείμενο προέλευσης t με μια κενή συμβολοσειρά κειμένου, δηλαδή αφαιρεί το " ΕΝΑ". Ως ενδιάμεσο αποτέλεσμα, παίρνουμε:
Vsh zkz n 125 ρούβλια.
Επανάληψη 2
Στη συνέχεια, η συνάρτηση καλεί τον εαυτό της και ως είσοδο (το πρώτο όρισμα) λαμβάνει ό,τι απομένει μετά τον καθαρισμό στο προηγούμενο βήμα, και το δεύτερο όρισμα είναι η συμβολοσειρά των χαρακτήρων που εξαιρέθηκαν που δεν ξεκινούν από τον πρώτο, αλλά από τον δεύτερο χαρακτήρα, π.χ. "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA. », χωρίς το αρχικό «A» – αυτό γίνεται από τη συνάρτηση MID. Όπως και πριν, η συνάρτηση παίρνει τον πρώτο χαρακτήρα από τα αριστερά των υπολοίπων (B) και τον αντικαθιστά στο κείμενο που της δίνεται (Zkz n 125 ρούβλια) με μια κενή συμβολοσειρά - παίρνουμε ως ενδιάμεσο αποτέλεσμα:
125 ru.
Επανάληψη 3
Η συνάρτηση καλεί ξανά τον εαυτό της, λαμβάνοντας ως πρώτο όρισμα ό,τι έχει απομείνει από το κείμενο που πρέπει να διαγραφεί στην προηγούμενη επανάληψη (Bsh zkz n 125 ru.), και ως δεύτερο όρισμα, το σύνολο των χαρακτήρων που έχουν περικοπεί από έναν ακόμη χαρακτήρα σε το αριστερό, δηλαδή «VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.», χωρίς το αρχικό «B». Στη συνέχεια, παίρνει πάλι τον πρώτο χαρακτήρα από τα αριστερά (B) από αυτό το σύνολο και τον αφαιρεί από το κείμενο - παίρνουμε:
sh zkz n 125 ru.
Και ούτω καθεξής - ελπίζω να έχετε την ιδέα. Με κάθε επανάληψη, η λίστα των χαρακτήρων που θα αφαιρεθούν θα περικοπεί στα αριστερά και θα αναζητήσουμε και θα αντικαταστήσουμε τον επόμενο χαρακτήρα από το σύνολο με ένα κενό.
Όταν τελειώσουν όλοι οι χαρακτήρες, θα χρειαστεί να βγούμε από τον βρόχο - αυτός ο ρόλος εκτελείται απλώς από τη συνάρτηση IF (ΑΝ), στο οποίο είναι τυλιγμένο το σχέδιό μας. Εάν δεν έχουν απομείνει χαρακτήρες προς διαγραφή (d=””), τότε η συνάρτηση δεν θα πρέπει πλέον να καλεί τον εαυτό της, αλλά θα πρέπει απλώς να επιστρέψει το κείμενο προς διαγραφή (μεταβλητή t) στην τελική του μορφή.
Αναδρομική επανάληψη κελιών
Ομοίως, μπορείτε να εφαρμόσετε μια αναδρομική απαρίθμηση κελιών σε μια δεδομένη περιοχή. Ας υποθέσουμε ότι θέλουμε να δημιουργήσουμε μια συνάρτηση λάμδα με όνομα ΛΙΣΤΑ ΑΝΤΙΚΑΤΑΣΤΑΣΗΣ για χονδρική αντικατάσταση θραυσμάτων στο αρχικό κείμενο σύμφωνα με μια δεδομένη λίστα αναφοράς. Το αποτέλεσμα πρέπει να μοιάζει με αυτό:
Εκείνοι. στη λειτουργία μας ΛΙΣΤΑ ΑΝΤΙΚΑΤΑΣΤΑΣΗΣ θα υπάρχουν τρία επιχειρήματα:
- κελί με κείμενο προς επεξεργασία (διεύθυνση πηγής)
- το πρώτο κελί μιας στήλης με τιμές για αναζήτηση από την αναζήτηση
- το πρώτο κελί της στήλης με τιμές αντικατάστασης από την αναζήτηση
Η συνάρτηση πρέπει να πηγαίνει από πάνω προς τα κάτω στον κατάλογο και να αντικαθιστά διαδοχικά όλες τις επιλογές από την αριστερή στήλη Να βρω στις αντίστοιχες επιλογές από τη δεξιά στήλη Υποκατάστατο. Μπορείτε να το εφαρμόσετε με την ακόλουθη αναδρομική συνάρτηση λάμδα:
Η μετατόπιση προς τα κάτω σε κάθε επανάληψη υλοποιείται από μια τυπική συνάρτηση excel ΔΙΑΘΕΣΗ (ΑΝΤΙΣΤΑΘΜΙΖΕΤΑΙ), το οποίο σε αυτήν την περίπτωση έχει τρία ορίσματα – το αρχικό εύρος, η μετατόπιση γραμμής (1) και η μετατόπιση στήλης (0).
Λοιπόν, μόλις φτάσουμε στο τέλος του καταλόγου (n = ""), πρέπει να τερματίσουμε την αναδρομή - σταματάμε να καλούμε τον εαυτό μας και εμφανίζουμε ό,τι έχει συσσωρευτεί μετά από όλες τις αντικαταστάσεις στη μεταβλητή κειμένου προέλευσης t.
Αυτό είναι όλο. Χωρίς δύσκολες μακροεντολές ή ερωτήματα Power Query – όλη η εργασία επιλύεται με μία συνάρτηση.
- Πώς να χρησιμοποιήσετε τις νέες λειτουργίες δυναμικού πίνακα του Excel: FILTER, SORT, UNIC
- Αντικατάσταση και καθαρισμός κειμένου με τη λειτουργία SUBSTITUTE
- Δημιουργία μακροεντολών και συναρτήσεων που καθορίζονται από το χρήστη (UDF) σε VBA