Faire des calculs sur la base

Utilisez les fonctions de totalisation

Le calcul le plus simple que l’on puisse faire avec une collection de données est une totalisation par critères. Vous souhaitez, par exemple, déterminer le nombre de films par genres (donc le cumul de tous les contenus de la colonne Nb). Par nombre de films, nous entendons nombre total de disques et pas nombre de titres différents. Pour cela, vous devez d’abord trier votre base sur ce critère. Sélectionnez une cellule de la colonne D, activez le menu Données et cliquez sur A..Z. Cliquez sur le bouton Sous-Total. Dans la fenêtre qui s’affiche, déroulez la liste A chaque changement de… et choisissez Genre. Dans la zone Ajouter un sous-total à, cochez la case Nb (nombre de films) et décochez toutes les autres cases. Validez par OK. Observez la gauche de votre écran : vous pouvez y voir deux traits verticaux et, au-dessus, trois petits boutons 1, 2 et 3. Cela signifie qu’Excel a activé le mode Plan. Cliquez sur le bouton 2 : le tableur dissimule toutes les fiches pour n’en afficher que les totaux, le résultat que vous cherchiez. Cliquez maintenant sur 1 : seul le total général est affiché. Enfin, si vous cliquez sur 3, toutes les fiches sont visibles : c’est l’affichage normal de votre base.

Créez un champ calculé

Vous voulez maintenant obtenir la valeur monétaire de votre stock, donc le total de la multiplication, pour chaque titre, de son prix par le nombre (Nb) de disques. Or, il n’existe pas de champ Valeur dans notre liste. Ce serait en effet une redondance de mettre dans une base un champ dont le contenu se déduit d’autres champs. Vous allez donc utiliser la colonne I, jusque-là inemployée (rappelez-vous : pas de colonne vide dans une base). En I1, saisissez Valeur, le nom du champ calculé. En I2, tapez la formule =B2*E2. Placez le pointeur de cellules en bas à droite de I2 (le pointeur se transforme en une croix noire) et double-cliquez : la formule est répétée sur toutes les cellules de la colonne I. Il ne vous reste plus qu’à refaire l’étape 01 mais en choisissant, cette fois, de totaliser le champ Valeur (qui a été automatiquement ajouté à votre base). Au besoin, masquez les colonnes E, F et G pour faciliter la lecture des résultats.

Ajoutez un critère arbitraire

Parfois, il est nécessaire d’effectuer des totalisations sur un critère qui ne peut pas être déduit directement. Par exemple, vous voulez compter le nombre de titres par durée de film. Impossible d’utiliser la fonction Sous-total sur le champ Durée car, ce dernier, exprimé en minutes, présente une valeur différente pour presque tous les films. Vous obtiendriez donc un total trop morcelé et inexploitable. Créez un champ calculé, que vous appellerez CatDurée (Catégorie Durée), et dans lequel vous ferez figurer un code, par exemple 1 pour les films de moins d’une heure, 2 pour les films entre 61 et 90 minutes, 3 pour les films entre 91 et 120 minutes et 4 pour les films plus longs. Ces seuils sont arbitraires, vous pouvez les modifier à loisir. Pour obtenir ce code directement en fonction de la durée, placez la formule =SI(G2<60;1;SI(G2<90;2;SI(G2<120;3;4))) en I2, recopiez-la sur toute la colonne, puis utilisez la fonction Sous-Total sur le champ CatDurée.

Faites des statistiques sur votre base

Les totaux évoqués aux étapes précédentes s’appliquent à la totalité de la base. Si vous voulez effectuer des calculs sur une partie de votre liste, vous devez utiliser les fonctions statistiques de base de données. Ces fonctions, dont le nom commence par NB (NBSOMME, NBMOYENNE…) diffèrent de leurs homologues classiques (SOMME, MOYENNE…) par un point capital : elles ne s’appliquent qu’aux cellules filtrées via une zone de critères, comme celle que nous avons vue plus haut (voir étape 02, paragraphe Utilisez un champ de critères simple). En voici un exemple : vous voulez connaître le prix moyen des comédies dans votre base. Commencez par donner un nom à toute votre base. Ce n’est pas indispensable, mais cela rend les formules plus faciles à lire. Dans la feuille principale (Titres), cliquez dans une cellule quelconque de la base, tapez Ctrl + * (Ctrl étoile) pour la sélectionner entièrement et donnez-lui le nom MesFilms. Puis insérez une feuille supplémentaire dans votre classeur (ou utilisez celle que vous avez ajoutée pour y saisir les listes de saisie). Tapez Genre dans une cellule et Comédie juste en dessous. Donnez à ces deux cellules le nom PrixMoyenParGenre (sans espaces). Éventuellement, saisissez en dessous un libellé explicatif, par exemple Prix moyen pour ce genre.Enfin, dans la cellule destinée à recevoir le résultat, il faudra saisir la formule =BDMOYENNE(MesFilms;”Prix”;PrixMoyen-ParGenre). Le premier argument (MesFilms) est l’étendue du champ sur lequel porte le calcul. Le second (“Prix”, n’oubliez pas les guillemets), est le champ à calculer. Enfin, le dernier argument (PrixMoyenParGenre) désigne la zone de critères. Maintenant, pour connaître la moyenne des films du genre Musical, tapez ce mot à la place de Comédie : le résultat est immédiatement mis à jour. Si un prix change dans la base, la formule en tient compte immédiatement. Nous vous conseillons de contrôler la saisie dans la cellule où vous tapez les noms de genre (pour cela, reportez-vous au paragraphe Imposez la saisie dans une liste, à l’étape 01). En utilisant des zones de critères complexes, comme celles que nous avons vues plus haut, vous pouvez effectuer n’importe quel calcul. Excel vous propose de nombreuses autres fonctions de ce type, par exemple BDMAX (maximum), BDMIN (minimum), BDNB (nombre de fiches), BDECARTYPE (écart type, pour des calculs statistiques).

Créez un tableau croisé dynamique

Avec les tableaux croisés dynamiques (TCD), une fonction déjà présente dans les précédentes versions du tableur, mais très améliorée avec Excel 2007, vous disposez d’un outil extraordinairement efficace pour faire parler vos chiffres. Pour créer un tel tableau, sélectionnez la feuille contenant votre base, activez le menu Insertion et cliquez sur Tableau croisé dynamique. Ne modifiez pas les champs de la boîte de dialogue et cliquez sur OK. Vous obtenez le formulaire vierge de création d’un TCD. La zone supérieure contient tous les noms de champ de votre base. Nous allons dresser un état du nombre de films par année de sortie. Faites glisser le bouton Année vers la zone Étiquettes de lignes. Enfin, faites glisser le champ Nb vers la zone Valeurs. Votre TCD affiche maintenant un total du champ Nb (donc le nombre total de disques) pour chaque année.

Ajoutez une seconde dimension

Vous voulez maintenant obtenir un état par année, mais aussi par genres de films. Dans le formulaire du TCD, à droite de l’écran, faites glisser le bouton Genre vers la zone Étiquettes de colonnes. Votre TCD est immédiatement recalculé.

Réorganisez le tableau

Sur le même principe, vous pouvez ajouter des champs dans les zones Étiquettes de ligne ou Étiquettes de colonnes ou les déplacer de champ en champ afin de modifier les critères de regroupement. Sur l’écran ci-dessous, nous avons permuté les champs insérés au paragraphe précédent. Vous pouvez placer plusieurs champs dans une même zone : vous obtenez alors un tri à plusieurs niveaux, par exemple par années puis, dans chaque année, par réalisateurs.

Posez un filtre

Vous ne souhaitez afficher que les films sortis après 1990 ? Dans le tableau de valeurs, à gauche de l’écran, cliquez sur le petit triangle placé à gauche de Étiquettes de ligne et choisissez Filtres s’appliquant aux étiquettes, Supérieur ou égal à puis validez par OK. En choisissant Filtres s’appliquant aux valeurs, vous pourriez, par exemple, n’afficher que les années pour lesquelles il y a plus de 60 films dans votre stock.

Ajoutez des graphiques

Avec cette nouveauté dExcel 2007, vous pouvez rendre vos chiffres plus explicites. Revenez dans la feuille principale (Titres), activez le menu Insertion. Ne cliquez pas sur Tableau croisé dynamique mais sur le petit triangle qui jouxte ce bouton, puis choisissez Graphique croisé dynamique. Cliquez sur OK et choisissez vos champs de données comme avec les TCD dans les étapes précédentes.

🔴 Pour ne manquer aucune actualité de 01net, suivez-nous sur Google Actualités et WhatsApp.

Étienne Oehmichen et Stéphane Darget