Table des matières
À l'heure actuelle, Microsoft Excel dispose de près de cinq cents fonctions de feuille de calcul disponibles via la fenêtre de l'assistant de fonction - bouton fx dans la barre de formule. C'est un ensemble très correct, mais, néanmoins, presque chaque utilisateur rencontre tôt ou tard une situation où cette liste ne contient pas la fonction dont il a besoin - simplement parce qu'elle n'est pas dans Excel.
Jusqu'à présent, la seule façon de résoudre ce problème était les macros, c'est-à-dire écrire votre propre fonction définie par l'utilisateur (UDF = User Defined Function) en Visual Basic, ce qui nécessite des compétences en programmation appropriées et n'est parfois pas facile du tout. Cependant, avec les dernières mises à jour d'Office 365, la situation a changé pour le mieux - une fonction spéciale "wrapper" a été ajoutée à Excel LAMBDA. Avec son aide, la tâche de créer vos propres fonctions est maintenant résolue facilement et magnifiquement.
Regardons le principe de son utilisation dans l'exemple suivant.
Comme vous le savez probablement, Excel dispose de plusieurs fonctions d'analyse de date qui vous permettent de déterminer le numéro du jour, du mois, de la semaine et de l'année pour une date donnée. Mais pour une raison quelconque, aucune fonction ne détermine le numéro du trimestre, ce qui est également souvent nécessaire, n'est-ce pas ? Corrigeons ce défaut et créons avec LAMBDA propre nouvelle fonction pour résoudre ce problème.
Étape 1. Écrivez la formule
Commençons par le fait que manuellement, de la manière habituelle, nous écrirons une formule dans une cellule de feuille qui calcule ce dont nous avons besoin. Dans le cas du numéro de trimestre, cela peut être fait, par exemple, comme ceci :
Étape 2. Conclure dans LAMBDA et tester
Il est maintenant temps d'appliquer la nouvelle fonction LAMBDA et d'y intégrer notre formule. La syntaxe de la fonction est la suivante :
=LAMBDA(Variable1; Variable2; … VariableN ; Expression)
où les noms d'une ou plusieurs variables sont répertoriés en premier et le dernier argument est toujours une formule ou une expression calculée qui les utilise. Les noms de variables ne doivent pas ressembler à des adresses de cellules et ne doivent pas contenir de points.
Dans notre cas, il n'y aura qu'une seule variable - la date pour laquelle nous calculons le numéro du trimestre. Appelons la variable correspondante, disons, d. Enveloppant ensuite notre formule dans une fonction LAMBDA et en remplaçant l'adresse de la cellule d'origine A2 par un nom de variable fictif, on obtient :
Veuillez noter qu'après une telle transformation, notre formule (en fait, correcte !) a commencé à produire une erreur, car la date d'origine de la cellule A2 n'y est plus transférée. Pour les tests et la confiance, vous pouvez lui passer des arguments en les ajoutant après la fonction LAMBDA entre parenthèses:
Étape 3. Créez un nom
Passons maintenant à la partie facile et amusante. Nous ouvrons Gestionnaire de noms languette formule (Formules — Gestionnaire de noms) et créez un nouveau nom avec le bouton Création (Créer). Trouvez et entrez un nom pour notre future fonction (par exemple, Nomkvartala), et sur le terrain Lien (Référence) copiez soigneusement depuis la barre de formule et collez notre fonction LAMBDA, seulement sans le dernier argument (A2) :
Tout. Après avoir cliqué sur OK la fonction créée peut être utilisée dans n'importe quelle cellule de n'importe quelle feuille de ce classeur :
Utilisation dans d'autres livres
LAMBDA et tableaux dynamiques
Fonctions personnalisées créées avec une fonction LAMBDA prendre en charge avec succès le travail avec de nouveaux tableaux dynamiques et leurs fonctions (FILTRE, UNIK, QUALITÉ) ajouté à Microsoft Excel en 2020.
Disons que nous voulons créer une nouvelle fonction définie par l'utilisateur qui comparerait deux listes et renverrait la différence entre elles - les éléments de la première liste qui ne sont pas dans la seconde. Le travail de la vie, n'est-ce pas ? Auparavant, pour cela, ils utilisaient soit des fonctions à la VPR (VLOOKUP), ou des tableaux croisés dynamiques ou des requêtes Power Query. Maintenant, vous pouvez faire avec une seule formule :
Dans la version anglaise, ce sera :
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Ici la fonction COUNTIF compte le nombre d'occurrences de chaque élément de la première liste dans la seconde, puis la fonction FILTRE ne sélectionne que ceux d'entre eux qui n'avaient pas ces occurrences. En enveloppant cette structure dans LAMBDA et créer une plage nommée basée sur celle-ci avec un nom, par exemple, RÉPARTITION DE LA RECHERCHE – nous obtiendrons une fonction pratique qui renvoie le résultat de la comparaison de deux listes sous la forme d'un tableau dynamique :
Si les données sources ne sont pas des tables ordinaires, mais des tables "intelligentes", notre fonction s'en sortira également sans problème :
Un autre exemple consiste à fractionner dynamiquement du texte en le convertissant en XML, puis en l'analysant cellule par cellule à l'aide de la fonction FILTER.XML que nous avons récemment analysée. Afin de ne pas reproduire manuellement cette formule complexe à chaque fois, il sera plus facile de l'envelopper dans LAMBDA et de créer une plage dynamique basée sur celle-ci, c'est-à-dire une nouvelle fonction compacte et pratique, en la nommant, par exemple, RAZDTEXT :
Le premier argument de cette fonction sera la cellule avec le texte source, et le second - le caractère séparateur, et il renverra le résultat sous la forme d'un tableau dynamique horizontal. Le code de la fonction sera le suivant :
=LAMBDA(t;d; TRANSPOSE(FILTRE.XML(“
La liste des exemples est interminable – dans toutes les situations où vous devez souvent saisir la même formule longue et fastidieuse, la fonction LAMBDA vous facilitera sensiblement la vie.
Énumération récursive de caractères
Tous les exemples précédents n'ont montré qu'un seul côté, le plus évident, de la fonction LAMBDA - son utilisation en tant que "wrapper" pour y envelopper de longues formules et simplifier leur saisie. En fait, LAMBDA a un autre côté, beaucoup plus profond, qui en fait presque un langage de programmation à part entière.
Le fait est qu'une caractéristique fondamentale des fonctions LAMBDA est la possibilité de les mettre en œuvre dans récursion – la logique des calculs, lorsqu'en cours de calcul la fonction s'appelle elle-même. De l'habitude, cela peut sembler effrayant, mais en programmation, la récursivité est une chose courante. Même dans les macros de Visual Basic, vous pouvez l'implémenter, et maintenant, comme vous pouvez le voir, il est arrivé dans Excel. Essayons de comprendre cette technique avec un exemple pratique.
Supposons que nous voulions créer une fonction définie par l'utilisateur qui supprimerait tous les caractères donnés du texte source. L'utilité d'une telle fonction, je pense, vous n'avez pas besoin de prouver - il serait très pratique d'effacer les données d'entrée jonchées avec son aide, n'est-ce pas ?
Cependant, par rapport aux exemples précédents, non récursifs, deux difficultés nous attendent.
- Nous devrons trouver un nom pour notre fonction avant de commencer à écrire son code, car dans celui-ci, ce nom sera déjà utilisé pour appeler la fonction elle-même.
- Entrer une telle fonction récursive dans une cellule et la déboguer en spécifiant des arguments entre parenthèses après LAMBDA (comme nous l'avons fait précédemment) ne fonctionnera pas. Vous devrez créer une fonction immédiatement "à partir de zéro" dans Gestionnaire de noms (Gestionnaire de noms).
Appelons notre fonction, disons, CLEAN et nous voudrions qu'elle ait deux arguments - le texte à nettoyer et la liste des caractères exclus sous forme de chaîne de texte :
Créons, comme nous l'avons fait précédemment, sur l'onglet formule в Gestionnaire de noms plage nommée, nommez-la CLEAR et entrez dans le champ Catégorie chantier suivant :
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Ici, la variable t est le texte d'origine à effacer et d est la liste des caractères à supprimer.
Tout fonctionne comme ceci :
Iteration 1
Le fragment SUBSTITUTE(t;LEFT(d);””), comme vous pouvez le deviner, remplace le premier caractère du caractère de gauche de l'ensemble d à supprimer dans le texte source t par une chaîne de texte vide, c'est-à-dire supprime le “ UN". Comme résultat intermédiaire, on obtient :
Vsh zkz n 125 roubles.
Iteration 2
Ensuite, la fonction s'appelle elle-même et en entrée (le premier argument) reçoit ce qui reste après le nettoyage à l'étape précédente, et le deuxième argument est la chaîne de caractères exclus commençant non pas par le premier, mais par le deuxième caractère, c'est-à-dire "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," sans le "A" initial - cela est fait par la fonction MID. Comme précédemment, la fonction prend le premier caractère à gauche des autres (B) et le remplace dans le texte qui lui est donné (Zkz n 125 roubles) par une chaîne vide – nous obtenons comme résultat intermédiaire :
125 rou.
Iteration 3
La fonction s'appelle à nouveau, recevant comme premier argument ce qui reste du texte à effacer à l'itération précédente (Bsh zkz n 125 ru.), Et comme deuxième argument, l'ensemble des caractères exclus tronqués d'un caractère de plus pour la gauche, c'est-à-dire « VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA. », sans le « B » initial. Ensuite, il prend à nouveau le premier caractère à partir de la gauche (B) de cet ensemble et le supprime du texte - nous obtenons :
sh zkz n 125 ru.
Et ainsi de suite - j'espère que vous avez compris l'idée. A chaque itération, la liste des caractères à supprimer sera tronquée à gauche, et on cherchera et remplacera le caractère suivant de l'ensemble par un vide.
Lorsque tous les caractères seront épuisés, nous devrons quitter la boucle - ce rôle est simplement joué par la fonction IF (SI), dans lequel notre conception est enveloppée. S'il ne reste plus de caractères à supprimer (d=""), alors la fonction ne doit plus s'appeler elle-même, mais doit simplement retourner le texte à effacer (variable t) dans sa forme finale.
Itération récursive de cellules
De même, vous pouvez implémenter une énumération récursive de cellules dans une plage donnée. Supposons que nous voulions créer une fonction lambda nommée LISTE DE REMPLACEMENT pour le remplacement en gros de fragments dans le texte source selon une liste de référence donnée. Le résultat devrait ressembler à ceci :
Ceux. à notre fonction LISTE DE REMPLACEMENT il y aura trois arguments :
- cellule avec texte à traiter (adresse source)
- la première cellule d'une colonne avec des valeurs à rechercher à partir de la recherche
- la première cellule de la colonne avec les valeurs de remplacement de la recherche
La fonction doit aller de haut en bas dans le répertoire et remplacer séquentiellement toutes les options de la colonne de gauche Trouver aux options correspondantes de la colonne de droite Représentant(e) suppléant(e). Vous pouvez implémenter cela avec la fonction lambda récursive suivante :
Le décalage vers le bas à chaque itération est implémenté par une fonction Excel standard ÉLIMINATION (DÉCALAGE), qui dans ce cas a trois arguments - la plage d'origine, le décalage de ligne (1) et le décalage de colonne (0).
Eh bien, dès que nous atteignons la fin du répertoire (n = ""), nous devons mettre fin à la récursivité - nous arrêtons de nous appeler et affichons ce qui s'est accumulé après tous les remplacements dans la variable de texte source t.
C'est tout. Pas de macros compliquées ni de requêtes Power Query - toute la tâche est résolue par une seule fonction.
- Comment utiliser les nouvelles fonctions de tableau dynamique d'Excel : FILTER, SORT, UNIC
- Remplacer et nettoyer du texte avec la fonction SUBSTITUTE
- Création de macros et de fonctions définies par l'utilisateur (UDF) dans VBA