• 12 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 28/04/2022

Saisissez des formules avancées

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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).

Créez la zone de critères pour le filtre élaboré
Créez la zone de critères pour le filtre élaboré

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.

Pensez à inclure la ligne de titre dans la zone de critères
Pensez à inclure la ligne de titre dans la zone de critères
  • 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 :

Filtre élaboré des pantalons OU des dates >730 jours
Filtre élaboré des pantalons OU des dates >730 jours

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.

Entrez le nom de la plage dans la zone de nom
Entrez le nom de la plage dans la zone de nom

À 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 !

Modifiez une plage nommée via le gestionnaire de noms
Modifiez une plage nommée via le gestionnaire de noms

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’Ouest, 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)

Utilisez BDSOMME() avec les critères en R2:S3
Utilisez BDSOMME() avec les critères en 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 la somme des ventes de robes sur tout le tableau
Calculez la somme des ventes de robes sur tout le tableau

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 :

Arguments de la fonction SOMME.SI.ENS() à 2 critères
Arguments de la fonction SOMME.SI.ENS() à 2 critères

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").

Utilisez SOMME.SI.ENS si vous avez plusieurs critères de filtre
Utilisez SOMME.SI.ENS si vous avez plusieurs critères de filtre
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 :

La formule utilise les en-têtes du tableau comme critère
La formule utilise les en-têtes du tableau comme critère

Dans la fenêtre des arguments, cela donne pour la cellule W4 :

Critères1 est en référence absolue sur la ligne 3, et Critères2 est en référence absolue sur la colonne V
Critères1 est en référence absolue sur la ligne 3, et Critères2 est en référence absolue sur la colonne V

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.

La liste des fonctions, une mine d’idées !
La liste des fonctions, une mine d’idées !

À 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.

Exemple de certificat de réussite
Exemple de certificat de réussite