Réaliser des calculs sur des ensembles de données

Réaliser une synthèse simple à l’aide de formules

La manière la plus directe de produire une synthèse dans Excel consiste à utiliser des formules dans la feuille de calcul. Cette approche permet de construire rapidement des indicateurs simples, comme un nombre de valeurs ou un total, à condition d’identifier correctement les cellules ou les plages à prendre en compte.

Avant de commencer les calculs, il convient de choisir un emplacement qui ne gênera ni la lecture du tableau ni les manipulations ultérieures. En effet, lorsque la liste est filtrée, triée ou enrichie de nouvelles lignes, des calculs placés au milieu des données peuvent devenir difficiles à lire ou plus délicats à maintenir. Il est donc préférable de regrouper les indicateurs de synthèse dans une zone distincte, par exemple au-dessus du tableau.

Pour cet exemple vous pouvez utiliser le fichier SuiviDépenses.xlsx et accéder à la feuille Dépenses. Les cellules C2 à D4 serviront de zone de calcul pour saisir les formules correspondant aux différents objectifs présentés dans la suite de la section. En regroupant ces formules au-dessus du tableau, leur lecture est facilitée et elles ne seront pas perturbées par les opérations de tri ou de filtrage, ni par l’ajout de nouvelles données.

Nombre de toutes les dépenses

L’objectif...

Synthèses à un critère

Excel propose trois fonctions principales pour réaliser un calcul à condition de respecter un critère : NB.SI, SOMME.SI et MOYENNE.SI. Elles permettent respectivement de compter, d’additionner ou de calculer une moyenne uniquement pour les données répondant à la condition définie.

Ces trois fonctions ont la même syntaxe :

Fonction(PlageConcernée;Critère;[Fonction])

PlageConcernée correspond à la plage de cellules sur laquelle l’opération comptage, somme ou moyenne doit être effectuée, Critère correspond à la condition à respecter pour que la valeur de la cellule soit prise en compte dans le calcul, Fonction correspond au type de calcul à effectuer.

Elle contient des valeurs dont la somme des valeurs au-dessus est affichée en ligne 8.

Remarquez les différences des comportements des fonctions d’addition.

En A10, nous souhaitons calculer le nombre de valeurs inférieures à 200 de la plage de cellules A2:A7.

=NB.SI(A2:A7;"<"&200) 

En B10, nous souhaitons calculer la somme des valeurs inférieures à 200 de la plage...

Principe

Le calcul matriciel permet d’effectuer, en une seule formule, des opérations portant sur plusieurs valeurs à la fois. Au lieu de traiter chaque cellule séparément ou de créer des colonnes de calcul intermédiaires, Excel peut ainsi combiner directement plusieurs plages de données dans un même calcul. Cette approche est particulièrement utile lorsque le résultat dépend de plusieurs conditions appliquées simultanément. Dans cette section, vous allez voir comment il permet de compter, d’additionner ou de retrouver une information à partir de plusieurs critères, sur des ensembles de données parfois importants. 

Exemples d’application

Nombre d’hommes âgés de plus de 40 ans

 Activez la feuille Ex1.

Elle contient un tableau recensant les membres du personnel et leur âge. Vous souhaitez savoir combien d’hommes sont âgés de plus de 40 ans.

 En cellule F2, créez une liste déroulante permettant de sélectionner le genre. Complétez la boîte de dialogue Validation des données en vous basant sur la capture d’écran suivante puis terminez par OK.

La liste déroulante Sexe

 Dans la liste déroulante en F2, sélectionnez le genre H puis dans la cellule F3 saisissez 40.

Le calcul du nombre de personnes de sexe masculin de plus de 40 ans est une somme qu’Excel réalise en balayant chaque ligne et si sexe = H et âge >40 il compte 1, sinon il compte 0. Puis pour la ligne suivante si sexe = H et âge >40,  il ajoute 1, et ainsi de suite. En fin de tableau Excel donne la valeur du compteur.

Cette somme est un produit matriciel qui associe deux matrices, le tableau et les critères.

Cette somme se décompose ainsi : (B2:B18=F2) et : (C2:C18>=F3).

La formule produit matriciel devient : =SOMME((B2:B18=F2)*(C2:C18>=F3)), qui, saisie sous forme matricielle s’affiche :

{=SOMME((B2:B18=F2)*(C2:C18>=F3))} 

Les formules matricielles doivent être validées par la combinaison de touches CtrlShiftEntrée. Les accolades apparaissent automatiquement lors...

Intérêt

Lorsqu’un même type d’information est réparti sur plusieurs feuilles, il peut être utile de le regrouper dans une synthèse unique. C’est le rôle de la consolidation de données : réunir et combiner, dans une même feuille, des résultats provenant de plusieurs tableaux de structure identique ou comparable. Elle permet, par exemple, d’obtenir un tableau unique à partir duquel pourront être calculés des totaux globaux, ou des synthèses par période, par exemple.

Synthèses multi-feuilles

Réaliser une synthèse à partir de plusieurs feuilles à l’aide de formules

 Ouvrez le fichier Consolidation.xlsx. Il comprend neuf feuilles identiques, classées par ordre alphabétique, une par agence, plus une feuille de synthèse.

Les feuilles du fichier Consolidation

Toutes les agences disposent d’un tableau de données ayant la même structure. Il répertorie pour chaque produit les quantités vendues et le montant HT associé.

La feuille de l’agence d’Aix

L’objectif est de consolider les données des différentes agences dans un tableau de synthèse situé dans la feuille SYNTHESE.

Tableau pour consolider les données dans la feuille SYNTHESE

Calcul du chiffre d’affaires et de la marge totale

Dans la cellule B3, nous allons saisir une formule permettant de calculer le CA total de l’entreprise.

Il s’agit de la somme des valeurs trouvées dans les cellules D3 de chaque agence.

Cela revient à effectuer le calcul :

=AIX!D3+AMBERIEU!D3+ANNECY!D3+BESANCON!D3+BREST!D3+CAEN!D3+ 
GRENOBLE!D3+ROUEN!D3+STRASBOURG!D3. 

C’est très lourd, sachant qu’une agence va être créée à Marseille (dans un mois), et une autre à Lyon dans quelques mois, et Toulouse en début d’année prochaine. Cela veut dire que pour chaque nouvelle agence, il va falloir modifier les formules. Ce n’est pas optimal !

Utilisez la syntaxe suivante qui permet de grouper les feuilles :

=SOMME(AIX:STRASBOURG!D3) 

Cela signifie que les cellules D3 de toutes les feuilles comprises physiquement entre la feuille AIX et la feuille STRASBOURG seront additionnées....