Intro
Ceci est le défi n°27 d'une série de challenges Excel créés spécialement pour découvrir modern Excel et booster vos compétences afin d'atteindre le niveau d'expert !
Rappel : qu'est-ce que le "modern Excel" ?
C'est une version du logiciel disponible avec Microsoft 365 proposant beaucoup de nouveautés.
Par exemple, on trouve de nombreuses nouvelles fonctions (RECHERCHEX,FILTRE, UNIQUE, MAP, etc.) très utiles ainsi qu'un mode de fonctionnement dynamique des résultats depuis une formule source (en savoir plus sur la propagation des formules Excel ici).
Ces nouveautés, intégrées au reste du logiciel, rendent Excel beaucoup plus pratique et efficace qu'auparavant.
Pour en savoir plus sur le modern Excel, tu peux cliquer ici.
Objectif du challenge
Dans ce challenge, l’objectif est d'afficher le matricule correspondant à chaque personne d'une liste.
Tu peux télécharger le fichier correspondant en bas de cette page. Pour t'aider tu peux aussi regarder la vidéo sur Youtube.
Ce challenge doit être réussi en utilisant une seule formule dans une seule cellule : la cellule F5.
Le défi en plus : battre notre champion-maison, Martin qui a réussi ce challenge en utilisant 120 caractères !
Ce que tu dois faire
Avec une seule formule dans la cellule F5 : Créer un matricule correspondant à chaque prénom-nom de la colonne B en :
- gardant les prénoms tels quels (majuscules au début)
- ne gardant que les initiales de chaque nom complétées par un point (".") (il peut y avoir plusieurs noms donc plusieurs initiales) (cf. résultats dans la colonne H).
Attention aux contraintes : la plage à indiquer est B5:B20
Important : pour booster tes compétences dans le modern Excel, tu dois réussir ce challenge avec une seule formule saisie dans une seule cellule, sans étendre ta formule initiale vers le bas.
Ceci te permettra de pratiquer la propagation de résultats depuis une formule initiale.
La cellule à compléter est la cellule F5, qui doit produire sans être étendue autant de résultats que nécessaires.
Tu veux te challenger encore plus ? Essaye de faire autant, voire encore moins, que le record à battre d'une formule de 120 caractères.
Maintenant, à toi de jouer !
La formule étape par étape pour obtenir ces résultats
#1 Fractionner
Construire une première référence en partant de la liste de noms complets en colonne B permettant de dissocier le prénom et le nom.
=FRACTIONNER.TEXTE(B5;" ")
#2 Attribuer
Stocker cette première référence fractionnée pour l'attribuer en qualité de variable sous un nom "n".
=LET(n;FRACTIONNER.TEXTE(B5;" ");n)
#3 Identifier
Utiliser la variable pour identifier les caractères correspondants aux noms (en majuscule) et aux prénoms (en nom propre et minuscule).
=LET(n;FRACTIONNER.TEXTE(B5;" ");EXACT(n;NOMPROPRE(n)))
#4 Raccourcir
Ne conserver que les prénoms en toute lettre et raccourcir les noms en initiales.
=LET(n;FRACTIONNER.TEXTE(B5;" ");SI(EXACT(n;NOMPROPRE(n));n;GAUCHE(n)&"."))
#5 Joindre
Fusionner les chaînes de caractères ainsi obtenues pour les réunir sur une seule et unique colonne.
=LET(n;FRACTIONNER.TEXTE(B5;" ");JOINDRE.TEXTE(" ";;SI(EXACT(n;NOMPROPRE(n));n;GAUCHE(n)&".")))
#6 Identifier
Rendre la formule dynamique et propager la fonction sur toute la colonne à l'aide de la fonction MAP.
=MAP(B5:B20;LAMBDA(c;LET(n;FRACTIONNER.TEXTE(c;" ");JOINDRE.TEXTE(" ";;SI(EXACT(n;NOMPROPRE(n));n;GAUCHE(n)&".")))))
La formule finale en 116 caractères
=MAP(B5:B20;LAMBDA(c;LET(n;FRACTIONNER.TEXTE(c;" ");JOINDRE.TEXTE(" ";;SI(EXACT(n;NOMPROPRE(n));n;GAUCHE(n)&".")))))
La formule finale en 109 caractères
=REDUCE(B5:B20;MAJUSCULE(FRACTIONNER.TEXTE(CONCAT(B5:B20&" ");" "));LAMBDA(x;y;SUBSTITUE(x;y;GAUCHE(y)&".")))
Les fonctions et symboles utilisés
MAJUSCULE
Permet de mettre en majuscule une chaîne de caractères.
REDUCE
Pour réduire un tableau à une valeur cumulée en appliquant un LAMBDA à chaque valeur et en renvoyant la valeur totale dans l'accumulateur.
FRACTIONNER.TEXTE
Permet de séparer des textes d'une même cellule, sur plusieurs colonne, en le coupant au niveau d'un délimitateur défini.
LET
Permet de créer une ou plusieurs variables pour simplifier une formule.
MAP
Pour renvoyer un tableau après avoir appliqué une fonction LAMBDA à chaque valeur du tableau.
LAMBDA
Pour créer vos propres fonctions personnalisées à partir de variables définies par vos soins.
JOINDRE.TEXTE
Pour gérer les erreurs renvoyées lors d'un calcul en les remplaçant.
Le modern Excel
Le modern Excel est l'expression utilisée pour désigner l'apparition d'une ensemble de nouveautés dans Excel depuis 2020 et contenues en majorité dans la version Microsoft 365 du logiciel.
Ces nouveautés sont principalement :
A - L'intégration au logiciel de plus de 40 nouvelles fonctions
o RECHERCHEX
o FILTRE
o UNIQUE
o FRACTIONNER.TEXTE
o Etc.
B - La propagation possible des résultats sur plusieurs cellules depuis une formule et formule initiales
Ces nouveautés permettent de faciliter énormément le fonctionnement d'Excel et notamment la prise en compte de résultats dynamiques (récupérés avec Power Query par exemple). C'est assez complexe à comprendre, mais les challenges du modern Excel que tu trouveras ici te permettront de découvrir l'immense potentiel de toutes ces nouveautés.
Chaque semaine, Solpedinn te propose un nouveau challenge pour booster tes compétences Excel ! N'hésite pas à tester les autres challenges.
