#28 - Lister les adresses mails cohérentes - épisode 1

Intro

Ceci est le challenge n°28 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.

La mission : trouver la formule pour ne lister que les adresses mails cohérentes !

Ce que tu dois faire

Avec une seule formule propagée dans la cellule F5 : 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. 

L'adresse mail doit contenir :

    - un seul et unique symbole "@"

    -  un seul et unique point (".") après le symbole "@"

       ("@xxx.com.fr" ne doit pas fonctionner par exemple)

(cf. résultats dans la colonne H en vert)

Attention à la contrainte : 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 84 caractères.

Maintenant, à toi de jouer !

La formule étape par étape pour obtenir ces résultats

#1 Lister

Créer une première liste à partir de la colonne B et la placer sous forme de variable.

=LET(t;B5:B20;t)

#2 Compter

Compter la position de chaque "@" contenu dans chaque adresse mail de la liste en colonne B.

=LET(t;B5:B20;CHERCHE("@";t))

#3 Compter (bis)

Rechercher également les adresses contenant un "." après le "@".

=LET(t;B5:B20;(CHERCHE("@*.";t)))

#4 Identifier

Classer les adresses correspondantes à ces premiers critères, en les distinguant sous VRAI ou FAUX.

=LET(t;B5:B20;ESTNUM(CHERCHE("@*.";t)))

#5 Exclure

Dans la liste, exclure les adresses comprenant un "." après le "@" dans leur chaine de caractères.

=LET(t;B5:B20;FILTRE(t;ESTNUM(CHERCHE("@*.";t))))

#6 Exclure (bis)

Dans la liste, exclure les adresses comprenant deux "@" dans leur chaine de caractères.

=LET(t;B5:B20;FILTRE(t;ESTNUM(CHERCHE("@*.";t))*ESTERREUR(CHERCHE("@*@";t))))

#7 Exclure (final)

Créer un tableau composé de deux colonnes contenant respectivement le prénom et le nom.

=LET(t;B5:B20;FILTRE(t;ESTNUM(CHERCHE("@*.";t))*ESTERREUR(CHERCHE("@*@";t))*ESTERREUR(CHERCHE("@*.*.";t))))

La formule finale en 101 caractères

=LET(t;B5:B20;FILTRE(t;ESTNUM(CHERCHE("@*.";t))*ESTERR(CHERCHE("@*@";t))*ESTERR(CHERCHE("@*.*.";t))))

La formule finale en 84 caractères

=FILTRE(B5:B20;MAP(B5:B20;LAMBDA(c;NB.SI.ENS(c;"*@*.*";c;"<>*@*@*";c;"<>*@*.*.*"))))

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.

LET

Permet de créer une ou plusieurs variables pour simplifier une formule.

ESTNUM

Permet de savoir si une cellule contient un nombre. Si la valeur est un nombre, la fonction indique VRAI, si ce n'est pas le cas, elle indique FAUX.

Chaque semaine, Solpedinn te propose un nouveau challenge pour booster tes compétences Excel ! N'hésite pas à tester les autres challenges.

TELECHARGER LE FICHIER DU CHALLENGE
ordinateur avec main sur Excel
Logo Solpedinn

Et si vous vous formiez au Modern Excel ?

VOIR LA FORMATION

On vous explique notre manière de faire ?

Logo Solpedinn

Inscrivez-vous à notre newsletter pour recevoir les nouveaux contenus

Message bien reçu ! Notre équipe vous recontactera très vite.
Une erreur est survenue. Merci d'essayer à nouveau.

La correction n'est pas encore prête (on attend vos réponses...)

Voir la formule
Voir la formule
Voir la formule