Intro
Ceci est le challenge n°29 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 de récupérer la liste des adresses mails dont la syntaxe est cohérente parmi toutes les adresses mails de la colonne B.
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.
Ce que tu dois faire
Avec une seule formule propagée dans la cellule F5 : l'objectif est de créer pour chaque personne de la colonne B une adresse mail professionnelle en prenant en compte plusieurs règles :
- tous les accents doivent être supprimés
- rajouter un point (".") entre chaque prénom et/ou nom
- rajouter le suffixe "@hariko.com"
- (et obtenir le résultat final en minuscules bien sûr !)
(cf. résultats dans la colonne H en vert)
Attention aux contraintes :
1) La plage à indiquer est B5:B20.
2) Toutes les lettres comprenant des accents indiquées dans la consigne doivent être modifiées par les mêmes lettres sans accent.
3) Toutes les lettres accentuées de la consigne.
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 135 caractères.
Maintenant, à toi de jouer !
La formule étape par étape pour obtenir ces résultats
#1 Extraire (optionnel)
Tester en B8 l'utilisation de REDUCE afin de remplacer un caractère "é" par "1" depuis la colonne B.
=REDUCE(B8;"é";LAMBDA(a;b;SUBSTITUE(a;b;1)))
#2 Remplacer
Tester les remplacements des caractères "é" , "ï" , "Ô" par "a" , "b" , "1".
=REDUCE(B8;{"é";"ï";"Ô"};LAMBDA(a;b;SUBSTITUE(a;b;1)))
#3 Remplacer (bis)
Remplacer l'ensemble des caractères spéciaux et accents via une insertion fractionnée et par binôme en argument de la fonction REDUCE.
=REDUCE(B8;FRACTIONNER.TEXTE(àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/);LAMBDA(a;b;SUBSTITUE(a;b;1)))
#4 Remplacer (ter)
Finaliser le remplacement des caractères spéciaux via l'insertion d'une fonction de texte.
=REDUCE(B8;FRACTIONNER.TEXTE("àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/";;"/");LAMBDA(a;b;SUBSTITUE(a;GAUCHE(b);1)))
#5 Finaliser
Intégrer les caractères en majuscule dans le remplacement.
=REDUCE(MINUSCULE(B8);FRACTIONNER.TEXTE("àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/";;"/");LAMBDA(a;b;SUBSTITUE(a;GAUCHE(b);1)))
#6 Extraire
Ne remplacer le caractère de gauche que par le caractère de droite en ajoutant la fonction de texte correspondante.
=REDUCE(MINUSCULE(B8);FRACTIONNER.TEXTE("àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/";;"/");LAMBDA(a;b;SUBSTITUE(a;GAUCHE(b);DROITE(b))))
#7 Propager
Créer un tableau composé de deux colonnes contenant respectivement le prénom et le nom.
=MAP(B5:B20;LAMBDA(c;REDUCE(MINUSCULE(c);FRACTIONNER.TEXTE("àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/";;"/");LAMBDA(a;b;SUBSTITUE(a;GAUCHE(b);DROITE(b))))))
La formule finale en 177 caractères
=MAP(B5:B20;LAMBDA(c;REDUCE(MINUSCULE(c);FRACTIONNER.TEXTE("àa/âa/äa/çc/ée/èe/êe/ëe/îi/ïi/ôo/öo/ùu/ûu/üu/ ./";;"/");LAMBDA(a;b;SUBSTITUE(a;GAUCHE(b);DROITE(b))))))&"@hariko.com"
La formule finale en 135 caractères
=REDUCE(MINUSCULE(B5:B20);LIGNE(1:20);LAMBDA(a;n;SUBSTITUE(a;STXT("àâäçéèêëîïôöùûü ";n;1);STXT("aaaceeeeiioouuu.";n;1))))&"@hariko.com"
Les fonctions et symboles importants utilisés
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.
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.
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.
Solpedinn propose des formations tous niveaux à Excel et aux outils d’analyse de données (Google Sheet, Power Query, Power BI…), toujours dans la bonne humeur avec une pédagogie au top du top. N’hésite pas à nous contacter pour en savoir plus.
