Fonctions de calculs dédiées aux bases de données sur Excel

1 Ressource disponible pour cette formation

• Télécharger le fichier Excel "Fonctions-de-bases-de-données.xlsx"

Bonjour et bienvenue sur ce nouveau tutoriel vidéo gratuit qui va vous apprendre à vous servir des fonctions de calculs dédiées aux bases de données le tout sur Microsoft Excel.

Ce tutoriel a été enregistré sur la version 2010 d’Excel mais est adaptable sur toutes les autres versions existantes.

Je vous rappelle que vous pouvez télécharger le fichier sur lequel jetravaille en cliquant sur le boutoncorrespondant dans l’encart Ressourcessitué sous cette vidéo

Beaucoup d’entre vous utilisentExcel comme une base de donnéesou souhaitent faire des traitementsspéciaux à l’aide de donnéesprovenant de bases et extraites dans Excel

Heureusement pour nous,Excel contient des fonctions quivont nous permettre d’effectuer des calculs surnos enregistrements en spécifiant descritères.

Pour les connaisseurs,les fonctions BD sont une équivalence au langageSQLqui permetde sélectionner des donnéeset d’effectuer des calculs de base commedes moyennes, des sommesou trouver les valeurs minimales etmaximales par exemple.

C’est ce que nous verrons dans cettevidéo avec les fonctions BDMOYENNEBDSOMME, BDMIN et BDMAX.

Nous travaillerons sur un fichier qui contient une liste de clients fictifschaque ligne correspond à un enregistrementet comprend des informations de base surle client ainsi que le nombre decommandes effectuéeset le montant total qu’ils ont dépensé.

Le but de cette formation sera d’effectuer des calculs simples sur cette plage de cellulesNous calculerons notammentla moyenne, la somme, la valeur maximaleet minimale des commandes en fonctionde critères comme le pays du clientou sa ville.

Ainsi nous pourrons avoir des informations plus détaillées quant à la performance de notre entreprisesuivant le pays d’origine de nosclientsavant de commencer avec les fonctions BD, je vais vous montrer comment marchent les critères ou du moins vous expliquer théoriquementdonc pour ce faire, j’ai créé untableau qui s’appelle « Critères » donc une plagede cellules qui s’appelle « Critères »où la première ligne que j’ai mis en surbrillancecontiendra le nom de la colonneet les lignes d’en dessouscontiendront des données que l’on voudra avoirdonc pour les critères, comment ça marche ?

Là j’ai mis deux lignes pour vous expliqueret deux colonnes pour avoir des explications surles logiques booléennes ETet la logique booléenne OU.

Si on metdes critères, par exemple, sur le payset qu’on marque « France »et on va marquer la « Belgique ».

Ce que vont nous faire les fonctions BDc’est qu’elles vont aller sélectionner les donnéesen prenant comme critère comme colonnela colonne payset là il va nous prendre soit la Francesoit la Belgiquedonc c’est une logique booléenne OUsi par exemple on voulait juste la France et la Belgiqueon aurait mis »Pays », « Pays »et là on aurait marqué « France », « Belgique » comme çaon peut aussi cumuler les critèresavec d’autres types de colonnes.

Si on veut sélectionner par exemplele pays « France » avecun nombre de commandes donc « Nb Commandes »supérieurà dix commandes par exempleon pourrait tapersupérieur à 10donc là qu’est-ce qu’il va nous faire ?Il va nous sélectionnertous les enregistrements qui auront comme pays « France »ETun nombre de commandes supérieur à 10donc logique booléenne ET.

Si là on marque »Belgique »et un nombre de commandessupérieurà 50 par exempleil va nous sélectionner toutes les lignesde « France »ET ayant un nombre de commandes supérieur à 10OUde « Belgique »ET ayantun nombre de commandes supérieur à 50.

Voilà à peu près la théorie du fonctionnementdes critèresdonc on vapour l’instant mettre un critèretrès simplesur le payset on va mettre « France » par exemplecomme ça on sélectionnera toutesur tous nos calculs le critère « Pays »avec juste les commandes qui viennent de France.

Donc voilà pourutiliser une fonctionbase de donnéeson tape « BD » donc là dans notre cas on va taper « BDMOYENNE »et on ouvre une parenthèseet là on voit qu’il y a trois arguments dans cette fonctionje vais vous expliquer une bonne fois pour toutescomment marchentces trois arguments car ils seront utilisés dans toutes les fonctions BD sur Excel.

Tout d’abord nous avons comme premier argumentbase de donnéesdonc une base de données représente une plage de cellules dans laquelleles lignes sont des enregistrementsles colonnes sont des champs différentset où la première ligne contient les libellés de chaque champ.

Alors moi ce que j’ai fait dans ce fichierau préalablec’est que j’ai nommé la plage de cellules qui contient toutes mes donnéeset je l’ai appelée « Tableau ».

Vous pouvez faire ça c’est un plusfacile que d’aller sélectionnerà chaque fois notre plage de cellules vu quel’on va toujours utiliser la même plage de cellulesdonc làon tape juste »Tableau »point virgule, on passe au deuxième argument : l’argument « champ »le champ indique la colonne qui contientla valeur à calculer.

On peutsoit noter le nom de la colonnesoit indiquer le numéro de lacolonnedonc 1 pour la première colonne, 2 pour la deuxième et ainsi de suitedans mon cas, je vais sélectionnerma cellule A4 quicorrespond exactementau nomde la colonne dans ma plage de cellules.

Si ce n’est pas le cas chez vous, si vous n’avez pas marqué « Nb Commandes »mais vous avez juste marqué »Commandes » ou « Nombre »je vous conseille vivementde le rentrer en dur dans lafonction donc en ouvrantun guillemetet en tapant le nom exactde la colonnedirectement dans la fonction.

Je ne vais pas utiliser ça, je vais utiliserune référence à la cellule C4point virguleon passe au dernier argumentl’argument « critères » donc l’argument le plus importantdonc les critères, comme je vous ai expliqué tout à l’heure, ça correspond à une plage de cellules quicontiendra les conditions spécifiéesdonc la plage doit absolument comprendreau moins un libellé de colonne et audessous de celle-ciune condition à effectuer sur cettecolonneles critères sont assez variés, comme on l’a vuils peuvent spécifier une correspondance de texte ou un nombrepour comparer des valeurs numériquesetc.

Donc nous, pour l’instantvu qu’on aun seul critère, on va sélectionnerde F4 à F5pour avoir « Pays » et « France »on ferme notre parenthèse, on appuye sur Entréeet là il nous calcule quoi ?il nous calcule que le nombre moyen de commandespour la Franceet de 23,51515152on va faire pareil pour le montanton va taper « égal » »BDMOYENNE »entre parenthèsesnotre base de données qui, chez moi s’appelle « Tableau »le champqui correspond à la cellule C5donc à « Montant »et comme critèreon va sélectionner de F4 à F5pour avoir « Pays » et « France »et là on voit quoi ? On voit que, en France, le montant moyen de nos commandes est à216 euros 66 centimesdonc là pour l’instanton se dit que ces données sont justes mais on va quand même aller les vérifierdonc on revient dans notreplage de cellules « Données »et on va, j’ai mis des filtres aupréalable sur chacune de mescolonnes, on va directement chercher la Franceon va taper « France » icidonc là il nous sort tous les enregistrementsqui correspondentà nos clients qui viennent de Franceet là si on prend toutes lescellules qui correspondent au nombre decommandes, on voit quoi en dessous ? On voit queen moyenne ils ont bien fait 23,51515152commandeset que pour les montantson est bien à 216,66€ de moyennedonc notre formule marche bienc’est déjà un bon signe !

Ensuite, on peut faire exactement pareilmais avec la somme de toutes lescommandes qui ont été passées enFrancedonc là on va utiliser »BDSOMME »notre base de données : « Tableau »notre champcellule C8et notre critèreça va être cette plage de cellules là.

Voilàet là on voit quoi ? On voit qu’on a passé 776 commandes en Franceet le montant de commandes »BDSOMME »base de données : « Tableau »le champ : cellule C9et les critères toujours F4:F5et là le montant, il n’est pas encore en format monétaire alors on va le passerau format monétaireet là on voit qu’en France on a eu 7149,88€ de commandes.

Mais si on changeici « France » et qu’on passe en Belgiquedans mon exemple, les noms des pays sont en anglais donc « Belgium »là, les fonctions BDMOYENNE et BDSOMME se mettent à jour automatiquementet nous calculent qu’en Belgique, le nombre moyen de commandesc’est 46le montant moyen est de 360 euroset qu’en cumulé, on a eu que 46 commandesdoncil y a eu apparemment qu’une seule personne qui a commandé de Belgiqueet en montant moyen, en montant cumulé pardon360 euros donc en Belgique notre entreprisemarche un petit peu moins qu’en Francece qui est assez dommage !

Donc là on va remettre « France » pour l’exemplepour les valeurs maximales et minimales et bien c’est toujours pareil, c’est BDlà, ça va être « BDMAX »toujours notre base de donnéestoujours notre champtoujours nos critèreset voilà !

On a 48, la commande maximalec’est 48 euroset le montantle nombre de commandes maximal est de 48 euros pardonle montant max : « BDMAX »base de données : toujours notreplage « Tableau »notre celluleet nos critèreset là, la valeur maximalede commandesc’est 444,64€.

Je pense que vous avez compris leprincipe pour les valeurs minimales ça vaêtre « BDMIN »voilà BDMINon va pas les taper tout de suiteparce que ce que l’on va faire maintenant, c’est que l’on va s’amuser àajouter des critères pour vous montrer un petit peu comment ça marche.

Donc si par exemple à la placed’abord juste la France si on veut la Franceou la Belgiquealors il suffit juste de taper « Belgium » icile problème de cette fonction, c’est qu’il va falloirmettre à jour de partoutnotreplage de données donc là ça va jusqu’àF6et là on voit que le nombre de commandesest passé de 25 tirs à 24commandesen France et en Belgique, donc la Belgiqueplombe un petit peu le résultat, si on peut dire !

Et on peut faire pareil après de partoutmais je ne vais pas m’amuser à faire ça car on va encore ajouter de nouveaux critèressi par exemple cette fois on veut avoirpar exemple le nombre moyen de commandes et lemontant moyendes commandes effectuées en France maisselon les villesbien cette fois on vaajouter en deuxième colonne dans noscritèresles villeson va taper « Ville »donc ça correspond bienencore une fois, au nomde la colonnequi est dans notre plage de cellules sourceet là on peut noter la ville qu’on veuton va mettre par exemple « Paris »et là on va, encore une fois, mettre à journotre plage de cellules cette foispour prendre »Pays », « Ville », « France », « Paris »on va faire pareilpour le montant des commandeset là on va voir que pour Parisle nombre moyen de commandes est de 25,375et le montant moyen des commandes est de 170€ environsi on change et que l’on met « Lyon »ça va changer totalement donc on voit qu’à Paris, le montant des commandes est beaucoup plus élevécela nous donne des indications supplémentairesset là, on peut encoreajouter un nouveau critèrevu que dans ma basesi on refait un filtresur la Franceon voit qu’il n’y a pas que Paris et Lyon, il y a aussi des commandes qui ont été passées à Grenobledes commandes qui ont été passées à Strasbourgsi on veut voir justeParis et Lyonon va mettre, encore une fois, comme pays la Franceet comme ville »Paris » et « Lyon »donc là, encore une fois, on met à jour notretableau de critèrespour que ça fasse F4G6.

Je vais juste modifierle 6 iciet là on voit qu’à Lyon et Parisle nombre moyen de commandes est de 25 et le montant moyendes commandes est de 167,38€.

Je pense que vous avez compris leprincipe de l’utilisation des critèresparce qu’après ça marche exactement pareil pourles moyennes, pour les sommespour les valeurs maximales et pour les valeurs minimales.

Voilà donc a vu dans cette formation comment se servir pleinement de ces fonctions de calculs simples sur les bases de données, cela peut nous donner des indications assez pertinentes nous dans notre cas, sur la performance de notre entreprise en fonction de l’origine des commandes donc le pays d’origine, voire la ville d’origine des commandes et j’espère que vous allez pouvoir l’utiliser à bon escient pour gagner un maximum de productivité avec vos entreprises.

Voilà ce tutoriel touche à sa fin je vous remercie de l’avoir suivi et à bientôt !