Intro
Ceci est le challenge n°23 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 créer un tableau de X lignes et 2 colonnes en fonction des valeurs du tableau de la plage de cellules B5:C8.
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 G5.
Le défi en plus : battre notre champion-maison, Martin qui a réussi ce challenge en utilisant 105 caractères ou moins !
Ce que tu dois faire
Avec une seule formule dans la cellule G5 : Créer autant de lignes par client que de factures de ce client et numéroter les factures de chaque client de 1 à X (X étant le nombre de factures du client) (cf. résultats dans les colonnes J et K)
Attention à la contrainte suivante : la plage à indiquer "B5:C8"
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 197 caractères.
Maintenant, à toi de jouer !
La formule étape par étape pour obtenir ces résultats
#1 Propager
Créer une première formule afin de rendre la liste des clients de façon propagée.
=LET(a;B5:C8;b;PRENDRE(a;;-1);b)
#2 Tester (optionnel)
Additionner le nombre de factures indiquées dans la colonne C pour afficher la somme dans la colonne G.
=LET(a;B5:C8;b;PRENDRE(a;;-1);MAKEARRAY(4;2;LAMBDA(l;c;"test")))
#3 Cadrer
Le nombre de colonne doit correspondre à 2 tandis que le nombre de ligne doit correspondre à 5.
=LET(a;B5:C8;b;PRENDRE(a;;-1);c;MAKEARRAY(LIGNES(a);MAX(b);LAMBDA(l;c;"test")))
#4 Composer
Ajouter l'ensemble du contenu des plages de référence.
=LET(a;B5:C8;b;PRENDRE(a;;-1);c;MAKEARRAY(LIGNES(a);MAX(b);LAMBDA(l;c;INDEX(a;l;1)&"facture"&c));c)
#5 Dissocier
Le nombre de colonne doit correspondre à 2 tandis que le nombre de ligne doit correspondre à 5.
=LET(a;B5:C8;b;PRENDRE(a;;-1);c;MAKEARRAY(LIGNES(a);MAX(b);LAMBDA(l;c;INDEX(a;l;1)&"facture"&c));c)
#6 Limiter
Propager uniquement la formule sur les deux premières colonnes.
=LET(a;B5:C8;b;PRENDRE(a;;-1);c;MAKEARRAY(LIGNES(a);MAX(b);LAMBDA(l;c;SI(c<3;INDEX(a;l;1)&"|facture"&c;"-")));c)
La formule finale en 197 caractères
=LET(a;B5:C8;b;PRENDRE(a;;-1);c;MAKEARRAY(LIGNES(a);MAX(b);LAMBDA(l;c;INDEX(a;l;1)&"facture"&c));c)
La formule finale en 105 caractères
=LET(b;C5:C8;s;SEQUENCE(;MAX(b));FRACTIONNER.TEXTE(CONCAT(REPT("|"&B5:B8&"_Facture "&s;s<=b));"_";"|";1))
Les fonctions et symboles importants utilisés
MAKEARRAY
Pour générer rapidement un tableau en à partir d'un nombre de lignes et de colonnes personnalisé tout en appliquant une fonction LAMBDA.
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.
PRENDRE
Pour renvoyer un nombre spécifié de lignes ou de colonnes contiguës à partir du début ou de la fin d’un tableau.
LIGNES
Compter le nombre de ligne contenues dans un tableau (rappel).
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.
Quelques fonctions du modern Excel
Le modern Excel doit beaucoup à l'intégration dans le logiciel de beaucoup de nouvelles fonctions.
Parmi elles, tu peux commencer par retenir ces 15 fonctions là :
- RECHERCHEX
- FILTRE
- UNIQUE
- TRIER et TRIERPAR
- SEQUENCE
- CHOISIRCOLS
- ASSEMB.V et ASSEMB.H
- LET
- JOINDRE.TEXTE
- FRACTIONNER.TEXTE
- TEXTE.AVANT et TEXTE.APRES
- MAP
Tu en découvriras beaucoup plus sur toutes ces fonctions et leurs possibilités en te testant au fur et à mesure des challenges.
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.
