Vos données sont propres, votre maquette est prête à accueillir des données, il est temps de les faire parler ! 😊
Je vous invite à télécharger ce fichier pour suivre les différentes étapes de ce chapitre. Ce fichier sera notre “Base de départ”.
Utilisez les filtres élaborés
Vous cherchez à répondre à la question suivante, sur la région entière :
“Est-ce que les pantalons de la collection 2018 se vendent aussi bien que ceux de la collection 2020 ?”
Pour y répondre, vous allez utiliser des filtres élaborés. Cela vous demande de procéder en plusieurs étapes !
Créez la zone de critères du filtre élaboré
La zone de critères est une plage de cellules contenant les filtres à appliquer au tableau.
Insérez 5 nouvelles lignes au dessus du tableau de données.
En haut à droite du tableau, saisissez les noms des colonnes que vous souhaitez filtrer, ici les colonnes “Produit” et “Jours”, en R2 et S2.
Sous la cellule Produit, écrivez “Pantalon”, et sous “Jours”, écrivez “>730”, afin d’obtenir les ventes de pantalons créés depuis plus de 2 ans (730 = 365 jours x 2).
Filtrez votre tableau de données
Les critères sont prêts, vous pouvez filtrer votre tableau :
Sélectionnez une cellule dans votre tableau de données.
Cliquez dans l’onglet “Données”, groupe “Trier et filtrer”, icône “Avancé”.
Dans la boîte de dialogue qui s’ouvre, vérifiez la plage de données, et sélectionnez la zone de critères R2 à S3.
Cliquez sur OK.
Le tableau de données est maintenant filtré sur tous les pantalons dont la date de création est supérieure à 2 ans.
Vous obtenez 49 lignes en résultat ! 😊
Vous pouvez constater ce nombre d’enregistrements filtrés en bas à gauche de votre écran :
Modifiez les critères de filtres
Les critères de filtre combinent deux filtres :
La base est filtrée sur les produits “Pantalon”.
À l’intérieur de cette sélection, le 2e critère filtre les jours “>730”.
On peut dire que les critères sont additionnés, avec la condition “ET”.
Vous pouvez modifier ces critères, afin d’obtenir une condition “OU”, qui signifie :
je filtre la base sur les lignes des produits “Pantalon”
et j’y ajoute les lignes des jours “>730” (que le produit soit un pantalon ou non)
Pour créer un tel critère, modifiez la zone de filtre comme ceci :
Chaque critère est maintenant sur une ligne différente !
Refaites un filtre élaboré avec ces nouveaux critères, et vous obtenez 734 lignes de résultats ! 😀
Calculez des statistiques simples
Nommez une plage de cellules
Excel vous donne la possibilité de donner un nom à une cellule, voire à une plage de cellules. Cela est très utile pour rendre vos formules plus compréhensibles.
Pour nommer les cellules de la table de données du fichier de départ :
Sélectionnez les cellules du tableau de données.
Dans la zone de Nom (à gauche de la barre de formule), saisissez un nom sans espace ni caractère spécial en dehors du “_”. Dans cet exemple, le nom est “Plage_Nommée”.
Validez en tapant sur la touche Entrée.
À partir de maintenant, dès que vous utilisez ce nom (dans la zone de Nom ou dans une formule Excel), Excel comprend que cela concerne cette plage de cellules !
Dans cet exemple, le nom “Plage_Nommée” a une référence de plage : =Tableau13468911127[#Tout].
Cette référence est égale à toutes les cellules du tableau de données, d’où son nom. Si vous aviez nommé les cellulesD2:D3 en “ma_zone”, sa référence aurait été ='P3C2-Filtres élaborés'!$D$2:$D$3.
Utilisez des fonctions de base de données
Maintenant que vous savez créer une plage de cellules nommée, utilisez-la ! Le but est de calculer des statistiques simples de ventes, afin de répondre à la question “Quel est l’état des ventes de notre société en Europe de l’Est, par catégorie de vêtements et par trimestre depuis 2019 ?”.
Pour utiliser cette fonction, réalisez les étapes suivantes :
Sur la droite du tableau, écrivez les critères : en R2, “Année-Trim” avec en dessous la valeur “2019-T2”, et en S2, “Categ” avec en dessous la valeur “Haut”.
Écrivez la fonction BDSOMME(), en S5, comme ceci : BDSOMME(Plage_Nommée;"Sales";R2:S3)
Grâce à la plage nommée, la formule est très simple à lire.
Vous pouvez modifier manuellement les critères pour que la formule se mette à jour automatiquement.
Calculez des statistiques conditionnelles
Calculez avec des fonctions à une seule condition
Les fonctions BDxxx() fonctionnent bien, mais elles nécessitent des cellules de critères pour fonctionner, ce qui fait qu’il est parfois complexe de les gérer si vous en avez beaucoup.
Excel propose une autre façon d’effectuer des statistiques simples, notamment à travers la fonction SOMME.SI(), très utilisée car simple à lire.
Par exemple, nous souhaitons calculer la somme des ventes de robes dans le fichier. La fonction SOMME.SI() contient alors 3 arguments :
La colonne du tableau contenant les critères, ici les produits (colonne J).
Le nom du critère voulu, ici les robes.
La colonne sur laquelle la somme doit se faire, ici les ventes (colonne I).
La fonction se présente donc sous cette forme : =SOMME.SI(J:J;"Robe";I:I).
Calculez avec des fonctions à conditions multiples
Si vous souhaitez combiner plusieurs critères, sans utiliser de cellules de critères, Excel a créé plusieurs fonctions reconnaissables à leur nom contenant le texte “.ENS”.
La fonction SOMME.SI.ENS
Pour analyser les ventes par trimestre et par catégorie, vous allez utiliser la fonction SOMME.SI.ENS(). Cette fonction fonctionne sur le même modèle que la fonction SOMME.SI(), en ajoutant des critères à volonté. Elle contient un nombre variable d'arguments, en fonction du nombre de critères dont vous avez besoin.
Sa syntaxe est la suivante :
Le 1er argument, Plage_somme, est la colonne sur laquelle faire une somme.
Le 2e argument, “Plage_critères1”, va de pair avec le 3e argument, “Critères1”.
Le 3e argument, “Plage_critères2”, va de pair avec le 4e argument, “Critères2”.
Exploitation de cette formule
Dans notre exemple, cela donne en cellule T4 : =SOMME.SI.ENS(I:I;G:G;"2019-T2";E:E;"Haut").
Exploitation de cette formule dans notre cas pratique
Dans notre exemple, avec 2 critères, le plus simple est de créer un tableau à double entrée, et de créer une formule qui utilise les noms de ligne et de colonne !
Grâce à l’utilisation des références absolues, et aux symboles “$”, vous pouvez faire en sorte que le Critère 1 corresponde aux lignes, et que le Critère 2 corresponde aux colonnes.
Voici ce que cela donne, dans la cellule V4 :
Dans la fenêtre des arguments, cela donne pour la cellule W4 :
Utilisez des fonctions prédéfinies dans Excel
Excel propose des fonctions prédéfinies en fonction de différents domaines d'activité : fonctions financières, statistiques, comptables, immobilières, etc.
À vous de jouer !
Téléchargez ce fichier et réalisez les opérations suivantes :
Comptez le nombre de lignes en France qui contiennent des ventes de chaussettes, grâce à un filtre élaboré.
Créez une plage de données nommée “Ma_BDD”, qui fait référence à tout le tableau de données.
Comptez le nombre de lignes de ventes de pantalons orange vendus en juin 2019, grâce à la fonction BDNBVAL(), en réutilisant le nom “Ma_BDD” que vous venez de créer.
Calculez la somme des ventes de robes vertes, grâce à la fonction SOMME.SI.ENS().
Corrigé
Vous pouvez consulter ce corrigé et regarder la vidéo ci-dessous pour vérifier votre travail.
En résumé
Filtrez votre tableau de données sur plusieurs critères grâce aux filtres élaborés.
Utilisez les fonctions SOMME.SI() et NB.SI() pour calculer des statistiques simples avec un critère.
Utilisez les fonctions SOMME.SI.ENS() et BDSOMME() pour calculer des statistiques sur plusieurs critères simultanés, etc.
Votre tableau contient désormais des formules avancées. Suivez-moi dans le chapitre suivant pour imbriquer des formules en fonction de vos besoins.
