Voici à quoi ressemblent vos données maintenant :
Vous pouvez télécharger ce fichier au besoin.
Gardez en tête votre objectif initial. Grâce à ces données, vous devez répondre à ces 2 questions :
Quel est l’état des ventes de la société Zarigual en Europe de l’Ouest, par catégorie de vêtements et par trimestre depuis 2019 ?
Est-ce que les pantalons de la collection 2018 se vendent aussi bien que ceux de la collection 2020 ?
Vous constatez que les noms de région, de pays et de produits ne sont pas très explicites. Pour l’instant, vous n’avez que des codes (colonnes A, B et E). Vous demandez donc à votre responsable de vous fournir une table de correspondance, afin de compléter votre liste de données.
Votre responsable vous fournit 2 tables de correspondance (2 onglets réunis dans le même classeur) :
une table pour les pays/régions ;
une table pour les produits.
Enrichissez vos données texte et nombre
Commencez par ajouter à votre tableau les noms des pays et produits. Pour y parvenir, vous pouvez utiliser la fonction RECHERCHEV().
La fonction RECHERCHEV()
Cette fonction permet d’ajouter une donnée venant d’une autre feuille, ou d’un autre classeur Excel. Je vous invite à regarder attentivement le schéma ci-dessous pour comprendre son fonctionnement. Dans cet exemple, les données sont dans des classeurs différents :
Sur ce schéma, vous pouvez constater que la formule se compose de 4 arguments, représentés par les chiffres 1, 2, 3 et 4 sur le schéma :
Argument 1 : la cellule contenant la donnée commune entre les 2 fichiers.
Argument 2 : la plage de cellules dans laquelle rechercher la nouvelle donnée.
Argument 3 : dans la plage Argument 2, c’est le numéro de la colonne contenant la nouvelle donnée.
Argument 4 : indique à Excel si vous souhaitez effectuer une valeur approchante (valeur VRAI) ou une valeur exacte (valeur FAUX).
Cela donne “En recherchant CETTE DONNÉE dans la 1re colonne de CETTE PLAGE, je récupère l’information de CETTE COLONNE, en effectuant une recherche EXACTE”.
Avec la syntaxe de la fonction, cela donne :
=RECHERCHEV(CETTE DONNÉE, CETTE PLAGE, CETTE COLONNE, EXACTE)
En formule, pour trouver le nom du pays, cela donne : =RECHERCHEV(B2;Table_Correspondance!$B$2:$F$40;2;FAUX)
Pour ajouter à votre tableau le nom des pays, vous allez donc :
à droite de la colonne Country_Cod, ajouter une colonne vierge ;
utiliser la fonction RECHERCHEV() pour trouver la correspondance entre le code du pays et le nom du pays.
Voici le résultat que vous devriez obtenir :
Vous pouvez ensuite ajouter à votre tableau toutes les données produit disponibles dans la table de correspondance produit !
La combinaison de fonctions INDEX() et EQUIV()
La fonction RECHERCHEV() fonctionne uniquement quand la donnée commune aux 2 bases se trouve dans la première colonne de la table de correspondance.
Dans le cas contraire, vous allez utiliser une combinaison de 2 fonctions, INDEX() et EQUIV(). Chacune de ces 2 fonctions est simple, et combinées, elles sont très puissantes ! 😀
INDEX() permet de trouver la valeur d’une cellule à l’intérieur d’une plage de données. Par exemple : “Dans CETTE PLAGE, quelle est la valeur de la cellule en LIGNE 2 et en COLONNE 1 ?”
EQUIV() permet de trouver à quelle place se trouve une certaine valeur, dans une plage de cellules. Par exemple : “Dans CETTE COLONNE, au bout de combien de cellules je trouve EXACTEMENT la VALEUR “Bas” ?”.
Pour utiliser cette combinaison :
Ajoutez 2 colonnes à droite du code de région “Sub_Region_Cod” :
La 1e, nommée “EQUIV”, contient la formule =EQUIV(A1;Table_Correspondance!$F$1:$F$40;0).
La 2e, nommée “INDEX”, contient la formule =INDEX(Table_Correspondance!$E$1:$E$40;B1).
Ou comment 2 fonctions simples deviennent très utiles quand elles sont combinées ! 😊
Enrichissez vos données Date
Vos données contiennent des ventes mensuelles, et pour répondre à la 1e question posée par votre responsable, vous devez créer des données trimestrielles.
Dans Excel, les fonctions Date sont nombreuses, mais malheureusement, aucune ne crée de numéro de trimestre directement à partir d’une date. 😕 Pas d’inquiétude, des alternatives existent ! Procédez en deux étapes :
Créez une colonne Trimestres
Un trimestre contenant 3 mois, vous pouvez vous baser sur le numéro du mois pour en déduire le trimestre :
Insérez 5 colonnes à droite du champ “Period”.
La 1ère colonne, nommée “Mois”, trouve le numéro du mois de vente (de 1 à 12), grâce à la fonction MOIS() :
la formule en G1 est =MOIS(F1).
La 2e colonne, nommée “Trim”, divise le numéro du mois par 3. Le but est d’obtenir une valeur supérieure à 1 pour les 3 premiers mois, supérieure à 2 pour les 3 mois suivants, etc. :
la formule est =(G1+2)/3.
La 3e colonne, nommée “Trimestre”, contient un chiffre sans décimale entre 1 et 4, en se basant sur la valeur entière de la colonne précédente, avec la fonction ENT() :
la formule est =ENT(H1).
La 4e colonne, nommée “Année”, extrait l’année du champ “Period” grâce à la fonction ANNEE() :
la formule est =ANNEE(F1).
La 5e colonne, nommée “Année-Trim”, combine l’année et le trimestre pour avoir un champ exploitable dans Excel :
la formule est =CONCATENER(J1;"-T";I1).
Le champ Année-Trim est maintenant parlant et exploitable ! 😊
Calculez l’écart entre la date de création du produit et sa date de vente
Excel vous propose 2 solutions pour calculer un écart entre 2 dates :
calculer une durée en jours calendaires, avec la fonction JOURS() ;
calculer une durée en jours ouvrés, c’est-à-dire en excluant les week-ends (et en option, les jours fériés), avec la fonction NB.JOURS.OUVRES().
Dans les fonctions d’Excel, il existe la fonction JOUR() et la fonction JOURS(), ne les confondez pas ! 😅
La fonction JOUR() renvoie le numéro du jour d’une date (nombre de 1 à 31).
La fonction JOURS() calcule l’écart entre 2 dates.
Les magasins Zarigual étant ouverts tous les jours de la semaine, c’est la fonction JOURS() que vous devez utiliser dans notre cas :
Insérez une colonne “Créé le” à droite de la colonne Produit.
Utilisez la formule =JOURS(F1;M1).
Une durée en jours calendaires ou jours ouvrés, vous savez maintenant comment la calculer ! 😊
À vous de jouer !
Votre tableau de données est quasiment terminé, il ne vous reste plus qu’à le compléter avec certaines données pour répondre aux 2 questions de votre responsable.
Grâce au fichier suivant à télécharger :
Ajoutez à l’onglet “P2C5-À vous de jouer” la colonne “Pays” de l’onglet “Table_Correspondance”, en utilisant la fonction RECHERCHEV().
Ajoutez à l’onglet “P2C5-À vous de jouer” toutes les colonne H à N (colonnes vertes) de l’onglet “Table_Correspondance”, en utilisant les fonctions INDEX() et EQUIV().
Ajoutez une colonne “Écart en jours ouvrés”, pour calculer l’écart entre la date “Créé le” et la date “Period”, sans jours fériés dans la formule.
Corrigé
Vous pouvez consulter le corrigé et regarder la vidéo ci-dessous pour vérifier votre travail.
En résumé
Vous pouvez ajouter à votre tableau des données venant d’autres feuilles ou fichiers, grâce aux fonctions RECHERCHEV() et INDEX() / EQUIV().
Vous pouvez calculer des écarts entre 2 dates, avec les fonctions JOURS() et NB.JOURS.OUVRES()
Votre tableau de données est désormais opérationnel : bravo ! Prochaine étape : réaliser votre tableau de bord dans la deuxième partie de ce cours. Mais avant, je vous propose de tester vos connaissances avec un quiz !
