Rechercher et extraire dans des bases de données Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Les fonctions Excel de base de données

Dans une formation précédente, nous avons abordé les tableaux croisés dynamiques. Et à ce titre, nous avons appris à résumer les données, en recoupant les valeurs redondantes, pour offrir des opérations de synthèse sur les données numériques. Dans la formation suivante, nous avons abordé les filtres sur les tableaux de base de données Excel. Et nous avons vu comment trier les résultats notamment grâce à des zones de critères complexes, avec les filtres avancés.

Les fonctions de bases de données sont à mi-chemin. Elles permettent de résumer et synthétiser les données, en intégrant dans leurs arguments, une plage de cellules pour la zone de critères. Elles permettent de réaliser des opérations sur des chiffres d'affaires par exemple, de dénombrer une quantité de produits correspondant à certains critères, ou encore d'extraire de l'information selon conditions.

Extraire de l'information de base de données
Deux fonctions d'extraction d'information nous intéressent. La fonction Excel RechercheV() ainsi que la fonction BDLire(). Pour pouvoir les étudier, nous avons besoin de données à exploiter. Pour ce faire :

• Télécharger le classeur fonctions-de-bases-de-donnees.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,
• Activer la feuille Catalogue en cliquant sur son onglet, en bas de la fenêtre Excel,

Ce classeur est constitué de quatre feuilles. La feuille Sorties est le résultat de l'importation de données depuis une base de données SqlServer. La feuille Catalogue est le résultat d'une importation depuis une base de données MySql. Dans un tableau de 3786 enregistrements, la feuille Catalogue propose une liste d'articles vendus sur Internet. Nous allons exploiter ce tableau de base de données pour découvrir les deux fonctions Excel d'extraction d'information. Pour plus de confort, nous allons commencer par dupliquer la feuille Catalogue. Pour ce faire :

• Cliquer avec le bouton droit de la souris, sur l'onglet de la feuille Catalogue,
• Dans le menu contextuel, choisir Déplacer ou copier,
• Dans la petite boîte de dialogue qui suit, cocher la case Créer une copie,
• Dans la section Avant la feuille, sélectionner la feuille Arbres,
• Puis, cliquer sur Ok,

Nous venons de créer une copie de la feuille Catalogue, placée avant la feuille Arbres, comme nous l'avons choisi.

• Double cliquer sur l'onglet de la feuille copiée, en bas de la fenêtre Excel,
• La renommer en Articles et valider par la touche Entrée,

Les deux feuilles sont ainsi nommées de façon explicitement différente.

La fonction RechercheV
La fonction Excel RechercheV permet d'extraire une information d'une base de données, en fonction d'une valeur recherchée dans cette même base de données. Sa syntaxe est la suivante :

RechercheV(Valeur_cherchee ;Tableau_de_recherche ;Num_colonne_retour ;Faux)

Les deux premiers arguments sont suffisamment explicites. Il s'agit tout d'abord de l'information qui est recherchée (1^er argument) dans le tableau de base de données (2^ème argument). Le troisième argument est numérique. Il s'agit du numéro de colonne, relatif à la sélection, dans laquelle se situe l'information correspondante à retourner. Enfin, le dernier argument Faux permet d'indiquer à Excel de ne pas tenter de se rapprocher de la valeur, si elle n'est pas trouvée. Contrairement à la fonction BDLire que nous aborderons ensuite, la fonction RechercheV présente une contrainte. L'élément recherché doit nécessairement se trouver dans la première colonne du tableau de recherche. Sachez enfin que son homologue RechercheH, permet d'effectuer les mêmes recherches, mais dans des tableaux transposés à l'horizontale. La fonction RechercheV est traitée de façon avancée par le support sur les recherches dynamiques dans Excel. Cette formation apprend notamment comment déplacer dynamiquement les bornes du tableau de recherche, en fonction de son évolution.

Dans la feuille Catalogue, nous souhaitons pouvoir extraire toutes les informations d'un article en fonction de son identifiant. Pour cela, nous allons préparer la zone d'extraction :

• Copier les champs de titre du tableau, sauf P_modifié, soit B3:F3,
• Les coller à côté, à partir de la cellule I3,
• En I4, saisir par exemple l'identifiant 4277,

Nous disposons donc d'un identifiant pour extraire les informations correspondantes, dans les champs prévus à cet effet dans les cellules J4 à M4. La fonction RechercheV permet de retourner un seul élément en fonction d'un identifiant recherché. Nous allons donc l'écrire quatre fois, pour retourner respectivement la contenance, la référence, le prix et la date de création. Comme tous les calculs sont dynamiques dans Excel, lorsque nous changerons la référence en I4, tous les éléments correspondant à la recherche, se mettront automatiquement à jour. Etant donnée la taille du tableau, nous allons lui attribuer un nom, afin de faciliter le travail :

• Réaliser le raccourci clavier CTRL + Fin pour atteindre la dernière ligne non vide de la feuille,
• Sélectionner la dernière cellule de recherche du tableau, soit G3789,
• Remonter tout en haut de la feuille avec la barre de défilement verticale,
• Tout en maintenant la touche MAJ (Shift) enfoncée, cliquer la première cellule du tableau, soit B3,

Vous sélectionnez ainsi toutes les cellules du tableau, comprises entre la dernière cellule sélectionnée et la première. Ces techniques et bien d'autres sont enseignées par le support de formation sur les trucs et astuces dans Excel.

• Dans la zone Nom, en haut à gauche de la barre de formule, taper tab_articles,
• Valider ce nom en enfonçant la touche Entrée du clavier,

Grâce à ce nom, il sera désormais plus simple de désigner le tableau dans une formule, plutôt que de devoir sélectionner l'ensemble des cellules qui le composent. Le support de formation pour reproduire des calculs Excel partout, démontre un autre intérêt de ces plages nommées, celui de figer une référence dans un calcul.

Pour l'extraction d'informations, commençons par rapatrier la contenance correspondant à l'identifiant saisi en I4 :

• En cellule J4, taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit =recherchev(,
• Sélectionner la cellule I4 pour la valeur cherchée et taper un point-virgule (;),
• En deuxième argument, taper le nom du tableau, soit tab_articles, suivi d'un point-virgule (;),

Vous remarquez qu'il est proposé en info-bulle lors de la saisie de ce deuxième argument. Cela prouve qu'Excel le reconnaît. Vous pouvez double cliquer sur ce nom ou finir la saisie. A l'issue, le tableau entier est mis en surbrillance par les références de la formule, montrant ainsi qu'il est intégralement considéré, par son nom. Terminons la fonction :

• Taper 2 suivi d'un point-virgule (;),
• Puis saisir Faux et fermer la parenthèse de la fonction RechercheV,
• Valider le calcul en enfonçant la touche Entrée du clavier,

Le chiffre 2 en troisième argument de la fonction, indique à Excel que nous souhaitons récupérer l'information sur la contenance, correspondant à l'identifiant de l'article. En effet, la contenance appartient à la deuxième colonne du tableau de recherche. Le dernier argument Faux indique à Excel de ne pas tenter de se rapprocher de la valeur cherchée, si elle n'est pas trouvée.


Vous obtenez l'information Flacon 125ml, sur la contenance correspondant à l'identifiant recherché. La poignée de cette formule ne peut pas être tirée vers la droite, pour reproduire le calcul sur les autres cellules. En effet, le troisième argument est une valeur numérique qu'il faut adapter en fonction de ce que l'on souhaite récupérer, dans le tableau de la base de données.

• En K4, faire le même calcul avec la valeur 3 en troisième argument,
• En L4, faire le même calcul avec la valeur 4 en troisième argument,
• En M4, faire le même calcul avec la valeur 5 en troisième argument,

En effet, la référence se trouve en troisième colonne du tableau de recherche. Le prix se trouve en quatrième colonne et la date, en cinquième colonne.

• Sélectionner la cellule M4 de la date,
• Dans le ruban Accueil, dérouler la liste des formats de la section Nombre,
• Choisir un format Date courte afin d'afficher le résultat extrait au format date,
• Sélectionner la cellule du prix extrait, soit L4,
• Lui appliquer le format monétaire par le raccourci clavier CTRL + M,

La formation Excel sur les raccourcis clavier apprend à gagner du temps grâce à ces combinaisons de touches.

• Sélectionner la cellule I4 pour l'identifiant de la zone d'extraction,
• Changer l'identifiant par la valeur 4693 et valider par Entrée,

Tous les calculs se mettent à jour. Chaque élément correspondant à l'identifiant, est récupéré en dessous de son champ, dans la zone d'extraction. Il s'agit de l'identifiant de la ligne 11 du tableau source. La fonction RechercheV est donc bien dynamique, comme tout calcul dans Excel. En revanche, si vous tapez une référence en I4, par exemple 4662399, en lieu et place de l'identifiant recherché, les quatre fonctions RechercheV renvoient un message d'erreur (#N/A). En effet, comme nous l'avons dit précédemment, l'élément recherché par la fonction RechercheV doit nécessairement se situer en première colonne du tableau de recherche. C'est le cas de l'identifiant de l'article mais pas de sa référence. Nous verrons que la fonction BDLire quant à elle, peut rechercher des éléments situés n'importe où dans la base de données. Enfin, si vous supprimez la valeur de la cellule de l'identifiant en I4, là encore les quatre fonctions RechercheV renvoient cette erreur. Ce problème peut être pallié grâce à une fonction Si, comme dans la formation sur la facturation automatisée dans Excel. Dans ce cas pratique, nous indiquons en effet à Excel, de réaliser la RechercheV seulement s'il y a bien une valeur à rechercher, inscrite dans la cellule.

La fonction BDLire
BDLire est une fonction de base de données qui permet d'extraire de l'information, selon un ou des critères. Comme toutes les fonctions de base de données que nous allons aborder, elle possède exactement la même structure, la même syntaxe. Elle demande trois arguments :

=BDLire(Base_de_donnees ;Champ ;Criteres).

C'est le nom de la fonction qui change et qui induit un résultat différent. La base de données est toujours le tableau source, lignes de titres incluses, dans lequel la recherche doit être effectuée. Le deuxième argument Champ, correspond à l'étiquette de colonne, à partir de laquelle doit être récupérée l'information correspondant au critère. Enfin, le troisième argument Criteres, correspondant à la zone de critères écrite dans des cellules, avec les noms de champs répliqués, comme pour les filtres avancés d'Excel. Pour exploiter la fonction BDLire dans les meilleures conditions, nous allons commencer par nommer la plage de cellules du tableau source.

• Activer la feuille Catalogue en cliquant sur son onglet,
• Comme précédemment, sélectionner tout le tableau, soit B3:GG3789,
• Dans la zone Nom, taper tab_cat et valider par Entrée,

Maintenant que le tableau possède un nom, nous devons construire une zone de critères ainsi qu'une zone d'extraction, dans les cellules de la feuille Excel. Nous souhaitons pouvoir récupérer la contenance, la référence, le prix et la date de création d'un produit en fonction, soit de son identifiant, soit de sa référence article.

• Copier l'étiquette de champ P_id en I3,
• Copier l'étiquette de champ P_ref en J3,

Il s'agit des deux champs de la zone de critères qui serviront de référence pour la recherche d'informations dans la base de données.

• Copier le nom du champ P_contenance en I7,
• Copier P_ref en J7, P_prix en K7 et P_date en L7,

Nous définissons ainsi les quatre champs de la zone d'extraction pour lesquels nous souhaitons récupérer l'information, en fonction, soit de l'identifiant, soit de la référence saisie dans la zone de critères. Comme il y a quatre informations à retourner en fonction du critère spécifié dans la zone de critères, la fonction BDLire doit être écrite quatre fois. Il suffira d'adapter seulement son deuxième argument, selon l'information de champ à extraire.

• Saisir l'identifiant 4277 dans la zone de critères, en I4,
• Taper le symbole = pour débuter la formule d'extraction en I8,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit BDLIRE(,
• Saisir le nom du tableau de recherche tab_cat, suivi d'un point-virgule (;),
• Sélectionner l'étiquette de l'information à retourner depuis le tableau source, soit C3,
• Taper un point-virgule (;) puis sélectionner la zone de critères, soit I3:J4,
• Fermer la parenthèse de la fonction et valider la formule d'extraction par Entrée,

L'information Flacon 125ml s'affiche. Elle correspondant à la contenance de l'identifiant saisi dans la zone de critères pour l'article. C'est le deuxième argument de la fonction BDLire (C3), qui définit l'information qui doit être retournée. Nous avons sélectionné l'ensemble de la zone de critères, P_ref inclus, bien que seul l'identifiant soit renseigné. De cette manière, toute recherche sur une référence sera permise grâce à la reconnaissance par les étiquettes de champ.

• Saisir la fonction BDLire dans les trois autres cellules, J8, K8 et L8 en adaptant le deuxième argument en fonction de l'étiquette de champ à retourner,
• Formater le prix de la cellule K8 au format monétaire (CTRL + M),
• Formater le résultat en L8 au format Date courte grâce au ruban Accueil,

Nous récupérons bien toutes les informations correspondant à l'identifiant saisi dans la zone de critères.

• Dans la zone de critères, remplacer l'identifiant recherché par 4693,

Toutes les informations correspondantes, sont instantanément rapatriées dans la zone d'extraction.

• Supprimer l'identifiant à rechercher dans la zone de critère en I4,
• En J4, taper la référence 4662407 et valider,

Bien que nous ayons modifié le champ de recherche, la fonction BDLire a été capable de retourner toutes les informations correspondant à cette référence. C'est une avancée intéressante par rapport à la fonction RechercheV qui doit nécessairement rechercher dans la première colonne du tableau.


Pour pallier le message d'erreur qui apparaît lorsqu'aucun identifiant ou aucune référence n'est saisie, nous devons poser une condition avant d'effectuer l'extraction. S'il y a bien un élément à rechercher, nous devons indiquer à Excel d'extraire les données avec la fonction BDLire. Dans le cas contraire, nous devons lui demander de laisser vide la cellule d'extraction. C'est la fonction SI d'Excel qui permet de poser des raisonnements afin de réagir différemment selon le contexte. La formation Excel sur les primes sur chiffres d'affaires démontre la puissance de cette fonction. En effet dans ce cas pratique, des primes sont attribuées, si et seulement si, des objectifs sont atteints.

• Sélectionner la cellule I8 pour l'extraction de la contenance,
• Enfoncer la touche F2 du clavier pour activer la saisie de la formule,
• Cliquer juste après le symbole = pour y placer le point d'insertion,
• Saisir le début de formule suivante : SI(ET(I4='';J4='');'';,
• Puis, fermer une seconde parenthèse après la fonction BDLIRE et valider par Entrée,

La formule modifiée résultante est la suivante :

=SI(ET(I4='';J4='');'';BDLIRE(tab_cat ;C3 ;I3:J4))

Grâce à la fonction ET englobée dans la fonction SI, nous vérifions deux critères à la fois. Si I4 et J4 sont vides ensemble, alors la formule écrit une chaîne vide dans la cellule de l'extraction. Dans le cas contraire, elle effectue bien l'extraction de la contenance en fonction de l'identifiant, ou de la référence trouvée, dans la zone de critères. Désormais, si vous supprimez l'identifiant et la référence de la zone de critères, vous obtenez une zone d'extraction vide, sans message d'erreur.

• Selon le même principe, adapter les trois autres fonctions BDLire,

Dénombrer les informations de bases de données
Lorsque des tableaux de base de données ne proposent aucune valeur numérique, il n'est pas possible de réaliser des extractions avec des opérations de synthèse. Mais des fonctions telles que BDNB et BDNBVAL permettent de dénombrer certaines occurrences redondantes, afin de livrer des résultats statistiques, riches en enseignements.

• Activer la feuille Sorties en cliquant sur son onglet,
• Réaliser le raccourci clavier CTRL + Fin pour atteindre la dernière ligne de la feuille,
• Sélectionner la dernière cellule du tableau, soit E998,
• Remonter tout en haut de la feuille avec la barre de défilement verticale,
• Tout en maintenant MAJ enfoncée, sélectionner la première cellule du tableau, soit A1,
• Dans la zone Nom, saisir tab_sorties et valider par Entrée,

Ce tableau, issu d'une base de données SqlServer est très dense. Il compte 998 lignes, soit 997 enregistrements si on exclut la ligne des titres. Ainsi nommé, il sera plus facile à désigner dans les fonctions qui l'utilisent en argument.

L'objectif ici est de pouvoir faire ressortir des valeurs statistiques pour connaître l'offre des idées de sorties par activités et départements. Nous souhaiterions par exemple savoir combien d'hôtels sont présents dans le département de la Drôme et pourquoi pas, plus précisément dans la ville de Valence. Les fonctions de base de données permettent de recouper des critères, posés sur plusieurs champs. Il suffit de les écrire sous les titres de champs, dans une zone de critères. Cette zone de critères est utilisée dans la formule pour retourner le résultat du calcul. Ainsi les fonctions de dénombrement BNNB et BDNBVAL permettent de retourner le nombre d'enregistrements satisfaisant aux critères, en les comptabilisant sur un champ, désigné lui aussi en argument. Voici leur syntaxe :

=BDNBVAL(Base_de_donnees; Champ_ou_compter; Zone_de_critere)
=BDNB(Base_de_donnees; Champ_ou_compter; Zone_de_critere)

La différence entre ces deux fonctions est le résultat qu'elles retournent, selon la nature des données. BNNB compte le nombre de cellules numériques dans le champ, pour les enregistrements satisfaisant aux critères. BNNBVAL compte le nombre de cellules non vides dans le champ, pour les enregistrements satisfaisant aux critères. Nous allons utiliser le champ ID pour compter les enregistrements qui correspondent aux critères. Ce champ n'est jamais vide et ne compte que des valeurs numériques. Donc, l'une ou l'autre fonction peut être utilisée indifféremment ici. Commençons par créer la zone de critères :

• Copier les titres de champs Id, Raison sociale, Activité, Dép et Ville respectivement en G2, H2, I2, J2 et K2 pour la zone de critères,

Dans un premier temps, nous souhaitons connaître le nombre d'hôtels recensés dans le département de la Drôme. Ce résultat statistique doit être consolidé en dessous, en cellule K7.

• En I3, sous l'étiquette de l'activité de la zone de critères, saisir Hôtel,
• En J3, sous l'étiquette du département de la zone de critères, saisir 26-Drome,
• En K7, taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit =bdnbval(,
• Saisir le nom de la base de données de recherche, suivie d'un point-virgule, soit tab_sorties;,
• Sélectionner l'étiquette de champ sur lequel compter les enregistrements, soit A1,
• Taper un point-virgule (;) et sélectionner la zone de critères, soit G2:K3,
• Fermer la parenthèse de la fonction et valider la formule par la touche Entrée,

Le calcul renvoie 32 enregistrements. Cela signifie qu'il y aurait 32 Hôtels recensés dans la Drôme. Nous pouvons nous assurer que ce résultat est correct en le vérifiant par les filtres automatiques.

• Sélectionner n'importe quelle cellule du tableau de base de données, par exemple B3,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer,

Cette action a pour effet d'ajouter les boutons de filtre en en-tête de colonne du tableau de données. Ils permettent de trier les enregistrements, en posant des filtres adaptés à la nature des champs, sur lesquels ils sont posés.

• Cliquer sur le bouton de filtre du champ Activité,
• Dans la zone Rechercher de la liste, taper les premières lettres du mot clé, soit hôt,
• Décocher les cases Chambres d'Hôtes et Chambres/Tables d'Hôtes pour ne conserver que les Hôtels et Hôtels Restaurants,
• Valider ce premier filtre par Ok,
• Cliquer sur le bouton de filtre du champ Dép,
• Dans la zone Rechercher, taper 26,
• Cliquer sur Ok pour conserver la case 26-Drome cochée,

Nous venons de recouper deux filtres automatiques. Le premier est posé sur le champ Activité. Le second est placé sur le champ Dép. Au final nous obtenons les Hôtels et Hôtels Restaurants de la Drôme. La barre d'état, en bas à gauche de la fenêtre Excel, indique qu'il ne reste plus que 32 enregistrements sur les 997 du départ. Il s'agit du même résultat que celui retourné par la fonction BDNBVAL. Pourtant dans la zone de critères, nous avions saisi hôtel pour le champ Activité. Cela signifie que l'opérateur Comme (LIKE en programmation) est utilisé par cette fonction. En réalité il s'agit de l'opérateur Commence par. La fonction comptabilise tous les enregistrements non vides sur le champ ID, pour lesquels le champ Activité commence par le terme hôtel. Tout cela est ensuite recoupé par le second critère sur le département. Cela signifie que si nous tapions hôt au lieu de hôtel, nous pourrions étendre l'offre aux chambres d'hôtes notamment. C'est ce que nous allons vérifier.

• Sélectionner n'importe quelle cellule du tableau de base de données, par exemple B5,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Effacer,

Nous retrouvons ainsi tous les enregistrements non filtrés, du tableau d'origine. Il n'y a pas de chambres d'hôtes dans la Drôme mais il y en a une en Ardèche.

• Dans la zone de critères en J3, remplacer 26-Drome par 07-Ardèche et valider par Entrée,

Le calcul de la fonction BDNBVAL se met automatiquement à jour. Il indique que 31 Hôtels sont proposés dans le département de l'Ardèche.

• En I3, remplacer le critère hôtel par hôt, puis valider par Entrée,

Le résultat ne change pas. La fonction BDNBVAL recense toujours 31 enregistrements. En effet, l'activité Chambres d'hôtes ne commence pas par le terme hôt mais le contient seulement.

• En I3, remplacer le critère hôt par *hôt,

Le symbole de l'étoile permet cette fois d'indiquer à la fonction BDNBVAL, tous les termes contenant l'expression hôt dans le champ Activité. Et en effet, le résultat retourné passe à 32 enregistrements, intégrant la seule chambre d'Hôtes recensée en Ardèche. Il est donc très simple de manipuler les zones de critères des fonctions de bases de données, afin d'obtenir les résultats correspondant aux conditions les plus adaptées et à l'analyse souhaitée. Mais tout comme pour les filtres avancés, il est aussi possible de recouper les critères en les inscrivant les uns sous les autres. Excel considère alors l'opérateur logique de liaison Ou, pour afficher tous les enregistrements correspondant, soit au premier critère, soit au second.


Nous souhaitons connaître à la fois, le nombre d'activités de loisirs dans la Drôme et le nombre de parcs, toujours dans la Drôme. La zone de critères de la fonction BDNBVAL ne considère qu'une ligne de conditions. Nous devons la modifier avant de commencer.

• Sélectionner la cellule de la formule, soit K7,
• Enfoncer la touche F2 pour passer en mode saisie,
• Dans la formule, sélectionner le dernier argument de la plage de critères, soit G2:K3,
• Supprimer cette plage et sélectionner à la place les cellules G2:K4,
• Valider le calcul par la touche Entrée,

Cette fois, la fonction BDNBVAL considère bien une plage de critères sur deux lignes. A ce stade, le résultat retourné, correspond au nombre total d'enregistrements, présents dans le tableau de la base de données. Cela est dû aux critères vides de la seconde ligne, permettant de considérer n'importe quelle idée de sortie du tableau.

• Dans la zone de critères, saisir parc en I3,
• Saisir 26-Drome en J3,
• Saisir Loisir en I4 et 26-Drome en J4,

La fonction BDNBVAL retourne la valeur 21 en K7. Il y aurait donc 21 activités de loisirs et de parcs comme des parcs d'attraction recensés dans la Drôme. Nous allons le vérifier avec les filtres personnalisés traités dans un support de formation Excel.

• Cliquer sur le bouton de Filtre du champ Activité,
• Dans la liste, pointer sur Filtres textuels et cliquer sur Contient,
• Dans la deuxième zone de la boîte de dialogue, saisir Loisir,
• Cocher l'opérateur OU pour la liaison logique des critères,
• Sur la deuxième ligne, choisir de nouveau l'opérateur contient avec la première liste,
• Puis, saisir Parc dans la zone de saisie sur sa droite,
• Valider ce premier filtre par Ok,
• Cliquer sur le bouton de filtre du champ Dép,
• Pointer sur filtres textuels et cliquer sur Contient,
• Dans la deuxième zone de la première ligne, saisir 26,
• Valider ce critère par Ok,

Le filtre personnalisé retourne 26 enregistrements correspondant aux critères recoupés sur ces deux champs. Il s'agit de 5 entrées supplémentaires. Cela est dû au fait que certaines activités de loisirs ne commencent pas par ce mot clé, comme Visite/Loisir. Or, comme nous l'avons vu précédemment, tel qu'il est écrit dans la zone de critères, pour la fonction BDNBVAL, seules les activités commençant par ce mot clé seront considérées.

• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Effacer,
• En I4, modifier le critère loisir par *loisir et valider par Entrée,

Le résultat de synthèse proposé par BDNBVAL se met automatiquement à jour. Elle affiche désormais 26 enregistrements. L'étoile du pavé numérique devant le mot clé, a permis de considérer toutes les activités contenant le mot clé loisir et pas seulement celles commençant par ce mot clé.

Résumer et synthétiser les données de bases de données
Certaines fonctions de base de données permettent de réaliser des opérations de synthèse sur des critères recoupés. Elles affichent alors des valeurs consolidées directement exploitables. Il s'agit par exemple des fonctions BDSOMME et BDMOYENNE. Elles peuvent être utilisées par exemple pour consolider les chiffres d'affaires réalisés sur une période donnée. Comme elles considèrent des valeurs numériques, elles peuvent exploiter les opérateurs de comparaison pour les nombres (<, > et =), dans la zone de critères. N'oublions pas non plus les fonctions BDMAX et BDMIN qui permettent respectivement de retourner la plus grande et la plus petite des valeurs, correspondant aux critères posés sur la base de données. Leur syntaxe est toujours la même que précédemment, exemple pour la somme :

= BDSOMME(Base_de_donnees; Champ_ou_sommer; Zone_de_critere)

• Activer la feuille Ventes Trimestre,
• Sélectionner tout le tableau, soit A5:F29,
• Attribuer le nom tab_ventes à ce tableau grâce à la zone Nom,

Ce tableau représente les chiffres réalisés par les vendeurs d'une entreprise, au cours du premier trimestre de l'activité. Comme chacune des ventes a été inscrite au fur et à mesure, un même vendeur apparaît à plusieurs reprises, mais à une date différente avec un chiffre différent. L'objectif ici est donc de pouvoir recouper les données redondantes, pour synthétiser les valeurs numériques leur correspondant. En d'autres termes, nous souhaiterions connaître la somme des ventes réalisées par un Vendeur, grâce à la fonction BDSOMME. Cette synthèse avec recoupements peut aussi se réaliser avec les tableaux croisés dynamiques, comme le propose le support de formation. Le critère pour l'instant correspond donc au nom du vendeur choisi. Avant d'exploiter les fonctions de synthèse de bases de données, nous devons créer la zone de critères.

• Recopier l'étiquette du champ Mois en H5,
• Dupliquer cette même étiquette en I5,
• Recopier l'étiquette du champ Vendeur en J5,
• Mettre en forme la cellule J9 pour recevoir les résultats des fonctions de bases de données,

Commençons par préparer la fonction BDSOMME :

• Taper le symbole = en J9 pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit BDSOMME(,
• En premier argument, désigner le tableau par son nom, soit tab_ventes,
• Taper un point-virgule (;) et cliquer le champ sur lequel sommer les valeurs, soit F5,
• Taper un point-virgule (;) et sélectionner la zone de critères, soit H5:J6,
• Fermer la parenthèse de la fonction BDSOMME et valider par Entrée,

La fonction BdSomme retourne le total de 38300. Il s'agit de la somme de toutes les valeurs numériques puisqu'à ce stade, aucun critère n'est encore posé. En deuxième argument, nous avons choisi de désigner le champ Ecart (F5). La somme selon critère sera donc réalisée sur les écarts effectués par les vendeurs, par rapport à leurs objectifs.

• Dans la zone de critères, taper le nom Galls en J6,

Instantanément le résultat de la fonction BDSomme se met à jour. Elle propose désormais la somme des écarts de ventes réalisés par le vendeur Galls. Il suffirait de changer ce nom par celui d'un autre vendeur, pour obtenir automatiquement sa synthèse. Nous souhaitons maintenant obtenir, la somme des écarts de ventes réalisés par un vendeur, sur une période donnée. Cela signifie que le critère doit être compris entre deux dates. Dans le cas précédent, nous avons vu comment signifier le OU dans la zone de critères. Il suffisait de les énumérer les uns sous les autres. Mais concernant l'opérateur logique de liaison ET, c'est différent. Les critères doivent être écrits sur la même ligne, respectivement sous le même nom de champ répliqué. C'est d'ailleurs ce que nous avions mis en oeuvre avec les filtres avancés. Nous souhaitons la synthèse des écarts réalisés par un vendeur, pour la deuxième quinzaine de Janvier et la première quinzaine de Février seulement. La période doit donc être comprise entre le 15/01/20017 et le 15/02/2017. En effet, comme il s'agit de champs date ici, l'opérateur *, dédié au texte, ne peut pas être utilisé. Nous devons exploiter les opérateurs de comparaison numériques supérieur et inférieur.

• Dans la zone de critères en J6, remplacer Galls par Céhef,

Instantanément, vous remarquez que ce deuxième vendeur a réalisé de moins bons objectifs globaux que le premier.

• En H6, ajouter le critère suivant pour la période : >=15/01/2017,

A notre grande surprise, bien que nous ayons réduit la période, la somme augmente. En effet, nous avons exclu de la synthèse, l'écart négatif réalisé par ce vendeur le 06/01/2017.

• En I6, ajouter le critère suivant pour borner la période : <=15/02/2017,

La somme obtenue est désormais négative. La synthèse fournie par la fonction BDSomme nous permet donc de constater rapidement, que les résultats de ce vendeur ne sont pas satisfaisants, sur la période choisie. Quoiqu'il en soit, nous avons bien réussi à recouper les critères, afin d'obtenir des résultats consolidés. En l'état, la fonction BDSOMME calcule la somme des écarts réalisés par le vendeur Céhef sur une période de dates comprises entre le 15/01/2017 ET le 15/02/2017.


Pour confirmer ce résultat, nous allons le recouper avec les filtres personnalisés d'Excel.

• Sélectionner n'importe quelle cellule du tableau,
• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer,
• Cliquer sur le bouton de Filtre du champ Vendeur,
• Dans la liste, décocher la case Sélectionner tout et cocher uniquement la case Céhef,
• Cliquer sur le bouton de Filtre du champ Mois,
• Dans la liste, pointer sur Filtres chronologiques et cliquer sur Filtre personnalisé,
• Dans la boîte de dialogue, définir une période postérieure ou égale au 15/01/2017 et antérieure ou égale au 15/02/2017 comme l'illustre la capture ci-dessous,
• Valider ce filtre chronologique par Ok,

En effet, il ne reste plus qu'une ligne de vente réalisée par ce vendeur dans la période définie. Et son écart est bien le même que celui qui avait été synthétisé par la fonction BDSomme.

• Dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Effacer,

Maintenant que le calcul est posé et que la zone de critères est en place, ces fonctions statistiques sont très souples à exploiter. Si vous changez le nom du vendeur dans la zone de critères, vous obtenez automatiquement sa synthèse sur la période définie. C'est très pratique pour comparer et analyser les résultats. Vous pouvez de la même façon modifier les bornes de la période. Sachez que les fonctions BDMoyenne, BDMax et BDMin fonctionnent exactement de la même manière. Pour les tester, dans la formule, il vous suffit de changer le nom BDSomme par le nom de la fonction de votre choix. Vous obtiendrez respectivement la moyenne des écarts réalisés par un vendeur, le plus grand des écarts réalisés et le plus petit.