Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?

Dans ce tutoriel, nous allons plonger dans le monde des fonctions base de données et découvrir comment utiliser des fonctions peu connues, telles que BDMAX, BDLIRE BDSOMME, ainsi que d'autres fonctions similaires pour exploiter facilement vos données.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Les fonctions Excel de base de données

Les fonctions de base de données d'Excel sont un ensemble de fonctions prédéfinies permettant d'effectuer des calculs sur des données stockées dans une table ou une plage de cellules. Elles sont utilisées pour extraire, trier, filtrer et calculer des données spécifiques dans une base de données.

Celles-ci peuvent être utilisées sur toutes sortes de données, que celles-ci soient des données textuelles ou numériques.

Elles sont particulièrement utiles lorsque nous avons besoin de calculer des statistiques sur des données complexes ou quand nous avons besoin d'une plus grande flexibilité dans la gestion des données

En effet, contrairement aux fonctions classiques d’Excel comme les fonctions SOMME, MOYENNE, NB,… qui peuvent être utilisées pour travailler avec toutes sortes de données, les fonctions de base de données sont conçues pour travailler uniquement avec des données stockées dans une table ou une plage de cellules.

Les fonctions de base de données sont également plus complexes que les fonctions classiques, car elles requièrent une syntaxe particulière et l’utilisation d’une table de filtrage pour extraire les données souhaitées, ce qui peut rendre leur utilisation plus difficile pour les débutants. Mais pas de panique, nous allons découvrir le fonctionnement de ces dernières dans le cadre de ce tutoriel.

2. Création de la base de données

Pour utiliser correctement les fonctions de bases de données d’Excel, nous aurons au préalable besoin d’une … base de données !

Il s’agit d’un ensemble de données liées les unes aux autres, généralement organisées au sein d'un tableau dans lequel les lignes représentent les différents enregistrements et les colonnes les champs de la table.

De cette manière, l’accès aux données est simplifié, ce qui nous permet de stocker, consulter, modifier et analyser les données, selon des critères prédéfinis.

Une fois que les données sont saisies dans la feuille de calcul, nous pouvons les transformer en base de données avec l’outil « Tableau d’Excel » que nous avons déjà pu utiliser lors de nombreux autres tutoriels. Bien que cette opération ne soit pas obligatoire, je vous recommande fortement de la mettre en place, ce qui permettra de bénéficier de tous les avantages proposés par cet outil, en particulier la gestion dynamique de la plage des cellules de la base, et la gestion des noms automatiques.

Si vous souhaitez en savoir plus sur les tableaux de données Excel, vous pouvez consulter le cours qui leur est totalement dédié.

Pour convertir notre plage de cellules en tableau de données Excel, nous commençons par sélectionner l’une des cellules de la base, puis nous nous rendons dans l'onglet « Données » afin de cliquer sur le bouton « Créer un tableau ».

Excel nous affiche une petite boîte de dialogue, pour que nous puissions confirmer la plage des cellules récupérée automatiquement, ainsi que le fait que nos colonnes possèdent chacune un en-tête.

Il nous suffit alors de cliquer sur le bouton « OK » pour confirmer la création du tableau de données.

3. Les fonctions de bases de données

   3.1. La syntaxe des fonctions bases de données

Maintenant, découvrons ce qui nous intéresse ici, les fonctions de base de données d’Excel.

La syntaxe commune à toutes les fonctions de base de données d'Excel est la suivante :

 =nom_de_la_fonction(base_de_données; champ; critères) 

Où

• "nom_de_la_fonction" est le nom de la fonction de base de données que vous souhaitez utiliser (par exemple BDLIRE, BDMIN, BDMAX, etc.).
• "base_de_données" correspond à la plage de cellules contenant les données de la base de données. Elle doit inclure les en-têtes de colonnes.
• "champ" est le numéro ou le nom de la colonne que vous souhaitez utiliser pour effectuer le calcul.
• "critères" est la plage de cellules contenant les critères que vous souhaitez appliquer. Vous pouvez spécifier plusieurs critères en utilisant plusieurs rangées.

Bien entendu, cette syntaxe de base peut varier en fonction de la fonction de base de données exacte que nous souhaitons utiliser. Par exemple, certaines fonctions de base de données peuvent nécessiter des paramètres supplémentaires pour spécifier la plage de cellules contenant les en-têtes de colonnes ou pour effectuer des calculs sur plusieurs colonnes.

Malgré tout, cette similitude entre toutes les fonctions de base de données permet de simplifier l’utilisation de celles-ci.

   3.2. Liste des fonctions de bases de données dans Excel

Pour obtenir la liste des fonctions de base de données d’Excel, il suffit de saisir « =BD » pour qu’Excel nous les présente :

4. Exemple concret d'une base de données

Pour illustrer l'utilisation des fonctions de base de données, nous allons prendre un exemple concret avec ce tableau Excel nommé « base » dans lequel nous retrouvons les colonnes "Date, N facture, Commercial, Produit, Quantités, Prix, Montant". Ce tableau synthétise les ventes de fruits et légume d'une entreprise au cours de l’année 2022.

   4.1. Exemple 1 : Utilisation de la fonction BDSOMME

Pour commencer, nous allons souhaiter utiliser la fonction BDSOMME, qui permet d’additionner toutes les valeurs dans une base de données qui répondent à des critères spécifiques.

Dans cet exemple, nous allons utiliser la fonction BDSOMME pour compter le poids total des ventes réalisées par le commercial « Jean ».

La caractéristique principale des fonctions BDSOMME que n’avons fait qu’aborder légèrement jusqu’à maintenant, c’est que la liste des critères à utiliser pour retourner un résultat doit être saisie dans une plage de cellules, en tenant compte des paramètres suivants :

• Chaque ligne correspond à un critère différent (« OU »)
• Chaque colonne correspond à un critère cumulatif (« ET »)

Ainsi, pour le cas qui nous intéresse ici, une seule condition est à retenir : le nom du commercial doit être « Jean ».

Nous allons donc saisir dans la cellule I1 le nom de la colonne, à savoir « Commercial » et juste en dessous, nous saisissons « Jean ».

Cela étant fait, nous allons pouvoir utiliser la fonction BDSOMME pour dénombrer le poids de tous les fruits vendus par Jean.

La formule est alors la suivante :

 =BDSOMME(base[#Tout];"Q";I1:I2) 

Pour insérer automatiquement le nom du tableau dans la formule, nous plaçons le curseur en haut à gauche de la cellule dans laquelle se trouve le premier en-tête, jusqu’à ce que le pointeur de la cellule prenne la forme d’une flèche, et nous cliquons deux fois : une première fois pour sélectionner les données de la base (Excel insère alors « base » dans la formule) et une deuxième fois pour y inclure également les en-têtes de colonne (« base[#Tout] »).

Jean a donc vendu 45240 kg de fruits au cours de l’année 2022.

Il est important de noter que si Jean n’avait réalisé aucune vente, la fonction renverrait une erreur #VALEUR!. Dans ce cas, il faudrait vérifier que les critères sont bien définis et qu'il n'y a pas de fautes de frappe ou d'orthographe dans les étiquettes de colonnes. Nous reviendrons sur les erreurs des fonctions de bases de données un peu plus tard dans ce tutoriel.

   4.2. Exemple 2 : Utilisation de la fonction BDMOYENNE

Maintenant, nous allons souhaiter utiliser la fonction BDMOYENNE, qui permet bien entendu de calculer la moyenne des valeurs dans une colonne d'une base de données qui répondent à des critères spécifiques. Dans cet exemple, nous allons utiliser la fonction BDMOYENNE pour calculer le prix de vente moyen, toujours pour Jean, mais uniquement sur le mois de Janvier.

Étant donné que nous allons appeler un deuxième critère, cumulé avec le premier, nous allons pouvoir ajouter une colonne sur le tableau des critères.

Nous ajoutons donc la colonne « Date » et le paramètre « <=31/01/2022 ».

Puis nous allons utiliser la formule suivante :

 =BDMOYENNE(base[#Tout];"Montant";I1:J2)  

Si nous le souhaitons, nous pouvons modifier la première formule afin de tenir compte de ce deuxième critère, en modifiant simplement la référence de la plage des cellules dans lesquelles se trouvent les critères.

La formule :

 =BDSOMME(base[#Tout];"Q";I1:I2) 

Devient donc :

 =BDSOMME(base[#Tout];"Q";I1:J2) 

Pour rendre cette plage dynamique, nous pouvons nommer la plage, en sélectionnant les cellules I1 à J2, puis en définissant un nom depuis la zone des noms située en haut à gauche d’Excel.

Nous pourrons donc modifier les formules comme ceci :

 =BDSOMME(base[#Tout];"Q";_criteres)
 =BDMOYENNE(base[#Tout];"Montant";_criteres)   

Maintenant, si nous souhaitons obtenir les statistiques du mois de février, nous insérerons une seconde colonne « Date », et dans les filtres, nous saisissons « >=01/02/2022 » et « <=28/02/2022 ».

Ensuite, nous nous rendons dans le menu « Formules » du ruban, afin de cliquer sur le bouton « Gestionnaire des noms ».

Nous recherchons le nom « _criteres », puis nous modifiions la plage des cellules correspondantes. Nous n’oublierons pas de cliquer sur la petite coche verte pour enregistrer ma modification apportée.

Les résultats des formules sont maintenant mis à jour en fonction des critères mis en place.

   4.3. Exemple 3 : Utilisation de la fonction BDMAX

Maintenant, nous allons souhaiter connaître le poids de la vente la plus importante, entre Jean et Émily (toujours sur le mois de février).

Étant donné, que nous allons souhaiter étendre l’analyse sur deux critères additionnés (Jean OU Émily), nous allons devoir ajouter une deuxième ligne sur notre tableau des critères. Sur celle-ci, nous saisissons « Émily », et nous reprenons les dates de la première ligne.

Ensuite, nous actualisons les cellules du nom « _criteres », comme nous l’avons vu juste avant.

Les valeurs des formules précédentes s’actualisent alors pour présenter les correspondances des ventes réalisées par les deux commerciaux.

Puis, nous allons pouvoir utiliser la fonction BDMAX, qui permet de trouver la valeur maximale dans une colonne d'une base de données en fonction des critères spécifiques.

   4.4. Exemple 4 : Utilisation de la fonction BDLIRE

Les formules de base de données que nous venons d’utiliser jusqu’à maintenant nous ont permis d’extraire des valeurs numériques, en fonction de critères prédéfinis.

Mais il est également possible d’aller chercher des valeurs textuelles.

Ici, nous allons souhaiter obtenir le nom du commercial qui entre Jean et Émily a réalisé les 250 kg de ventes de fruits sur le mois de février.

Pour cela, nous avons deux options qui s’offrent à nous :

• Soit nous ajoutons une nouvelle colonne dans le tableau critère pour aller chercher le nom du commercial ayant réalisé cette vente, en prenant alors le risque de modifier les résultats obtenus précédemment,
• Soit nous ajoutons un nouveau tableau de saisi des critères sur la feuille de calcul

Ici, nous allons préférer cette deuxième option, afin de préserver l’intégrité des calculs précédents.

Ce tableau sera alors consisté de trois colonnes : « Q », « Date » et « Date ».

Sous la cellule « Q », nous allons récupérer le résultat obtenu juste avant, et dans les colonnes « Date », nous allons chercher les dates situées sur la première ligne de la plage « _criteres ».

Ensuite, nous pouvons saisir la formule :

 =BDLIRE(base[#Tout];base[[#En-têtes];[Commercial]];I9:K10)  

   4.5. Les règles de filtres supplémentaires

Si vous suivez mes tutoriels sur Excel, vous devez retrouver ici une certaine ressemblance entre les règles découvertes dans le cadre de l’utilisation des fonctions de base de données et la création de filtres élaborés.

Il s’agit en effet du même type de fonctionnement.

Alors, si vous souhaitez en savoir plus sur l’étendu de ces règles, je vous invite à consulter le tutoriel dédié, vous y découvrirez ainsi de nombreuses autres astuces, notamment sur l’utilisation des caractères jokers pour effectuer des analyses sur des portions de textes.

5. Les erreurs retournées par les fonctions de base de données d’Excel

Lorsque nous travaillons avec des fonctions de base de données dans Excel, il est possible de rencontrer des erreurs de formule qui peuvent rendre nos résultats incohérents ou inattendus.

Voici les erreurs les plus courantes que nous pouvons rencontrer et leurs causes possibles :

• #VALEUR! : cette erreur apparaît lorsque la fonction de base de données ne peut pas trouver de correspondance entre les critères spécifiés et les données de la base de données. Cela peut être dû à une faute de frappe, à des critères trop restrictifs ou à une erreur dans les étiquettes de colonnes ou les critères.
• #NOMBRE! : cette erreur se produit lorsque la fonction de base de données remonte plusieurs valeurs correspondant aux critères spécifiés. Cela peut être dû à des critères trop larges ou à une erreur dans les étiquettes de colonnes ou les critères.
• #DIV/0! : cette erreur apparaît lorsque la fonction de base de données tente de diviser un nombre par zéro. Cela peut se produire si vous utilisez une fonction qui calcule une moyenne ou une somme sur une colonne qui contient des zéros.
• #REF! : cette erreur se produit lorsque la fonction de base de données fait référence à une plage de cellules qui a été supprimée ou déplacée.

Pour éviter ces erreurs, il est important de vérifier les étiquettes de colonnes et les critères utilisés pour nous assurer qu'ils sont correctement orthographiés et qu'ils correspondent aux données de la base de données.

Pour éviter toute erreur de frappe, nous pouvons également utiliser la validation de données pour limiter les erreurs de frappe et les erreurs de critères. Nous avions en effet découvert dans un tutoriel précédent comment créer facilement une liste déroulante pour pouvoir sélectionner facilement des éléments sans avoir à la saisir manuellement.