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

À l’aide de formules

Les filtres élaborés dans le chapitre précédent permettent de n’afficher que les lignes correspondant à un ou plusieurs critères. Outre l’affichage de ces détails, vous souhaiterez probablement obtenir des calculs statistiques sur les informations filtrées.

Une règle importante est de toujours placer vos calculs au-dessus de vos listes de données. L’ajout de nouvelles données ne posera ainsi pas de problèmes. De plus, si vous placez vos calculs à droite de vos listes, les lignes contenant vos formules de calculs peuvent se retrouver masquées par le filtre.

Lorsque nous allons appliquer des filtres successifs, nous souhaitons visualiser les éléments suivants :

Les formules de calcul "TOUTES DEPENSES" ne doivent pas être impactées par les filtres, deux fonctions de calcul vont être utilisées :

Pour les formules de calcul "DEPENSES FILTREES" qui doivent être impactées par les filtres, une seule fonction va être nécessaire : la fonction mathématique SOUS.TOTAL.

La syntaxe de la fonction SOUS.TOTAL est la suivante :

=SOUS.TOTAL(no_fonction,réf1,[réf2],...])

Les arguments réf1, réf2 représentent les cellules ou plages de cellules (254 maxi) pour lesquelles vous souhaitez obtenir un sous-total.

L’argument no_fonction représente un numéro compris entre 1 et 11 ou 101 et 111. Les valeurs de l’argument no_fonction sont détaillées ci-après.

* Les valeurs...

Quelles fonctionnalités et fonctions utiliser pour des synthèses simples ?

Présentation

Excel met à votre disposition de nombreuses fonctions pour effectuer vos calculs statistiques. Les premières fonctions à maîtriser sont celles qui permettent de concevoir des calculs en fonction d’un critère. Les résultats de ce type de calcul dépendent de la valeur de chacune des cellules d’une plage appelée plage de critères.

Notre premier tableau de synthèse va être construit à partir du classeur Facturation.xlsx qui intègre toutes les factures d’interventions d’une entreprise sur une année.

Notre objectif est d’obtenir différentes statistiques par type de travaux, type de clients et techniciens.

 Concevez tout d’abord dans la feuille STAT les tableaux présentés ci-dessous.

Nous disposons de trois fonctions pour effectuer ces calculs :

Nombre de factures : fonction NB.SI

Syntaxe : =NB.SI(PlageCritère;Critère)

Montant total : fonction SOMME.SI

Syntaxe : =SOMME.SI(PlageCritère;Critère;PlageSomme)

Montant moyen : fonction MOYENNE.SI

Syntaxe : =MOYENNE.SI(PlageCritère;Critère;PlageMoyenne)

L’argument PlageCritère correspond à la plage de cellules sur lesquelles le critère doit être vérifié.

L’argument Critère est un nombre, une expression, une référence de cellule ou une chaîne de texte entre guillemets qui détermine sur quelles cellules appliquer le calcul de synthèse.

L’argument PlageSomme ou PlageMoyenne représente la plage de cellules sur lesquelles la somme ou la moyenne doivent être calculées.

Lorsque vous effectuez des calculs sur des bases de données importantes, nous vous conseillons de nommer vos plages et d’utiliser les noms dans les formules plutôt que de sélectionner des ensembles de cellules lors de la création de vos formules. Cette méthode sera d’autant plus efficace si vos données et vos données statistiques se trouvent dans des feuilles de calcul différentes.

Avant de saisir les formules, nous allons donc créer les noms qui vont être utilisés....

Principe

Le calcul matriciel va vous permettre d’obtenir des résultats là où d’autres fonctions nécessiteraient d’effectuer des calculs intermédiaires. Généralement, ceci peut vous éviter d’avoir à insérer des colonnes supplémentaires dans vos tableaux. Un calcul matriciel utilise une seule plage ou plusieurs plages de données de tailles égales appelées matrices.

Appliquée à des plages de données contenant de nombreuses lignes, cette technique de calcul requiert des quantités de ressources importantes, ce qui peut parfois augmenter les temps de calcul d’Excel. Si vous vous rendez compte que ces temps de calculs sont pénalisants pour votre travail, pensez à désactiver le mode de calcul d’Excel et à utiliser le mode de calcul manuel.

Une formule matricielle doit impérativement être validée non pas par Entrée mais par la combinaison de touches CtrlShiftEntrée. Une fois la formule validée, Excel place la formule entre accolades. Lorsque vous modifiez une formule matricielle, vous devez à nouveau la valider par CtrlShiftEntrée. Oublier d’utiliser cette combinaison engendrera un message d’erreur.

Nous vous proposons de mettre en pratique quelques exemples afin d’illustrer cette technique de calcul. Une fois le principe acquis, vous pourrez l’adapter à vos propres cas concrets.

Chacune des feuilles de ce classeur contient les exercices proposés.

Appliquer cette technique à quelques exemples concrets

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

Vous disposez d’une base de données de 18 lignes comportant les sexes et les âges de différentes personnes (feuille Ex1). À partir de cette liste, vous souhaitez connaître le nombre d’hommes âgés de plus de 30 ans.

Votre formule doit intégrer deux critères reliés par un ET :

• Le sexe doit être égal à la cellule E2.

• L’âge doit être supérieur à la cellule E3.

Il est bien sûr possible dans ce cas d’utiliser la fonction NB.SI.ENS pour aboutir au résultat escompté...

Intérêt

Les tableaux que vous utilisez comportent très souvent des informations réparties sur plusieurs feuilles de calcul, par exemple vous pouvez avoir une feuille de calcul par commercial, service, secteur, famille de produit… Chacune des feuilles est bien sûr composée de données et de formules et généralement toutes ces feuilles ont la même structure.

Obtenir des synthèses à partir de nombreuses feuilles peut très vite devenir fastidieux si vous n’utilisez pas les bonnes techniques. Vous pourrez toujours aboutir à un résultat correct mais souvent au prix de formules lourdes et compliquées à mettre à jour si vos données varient. L’ajout de nouvelles feuilles de calcul entrant dans la synthèse ne doit pas engendrer de contraintes de mise à jour complexes.

Une fois les fonctionnalités abordées maîtrisées, cette partie vous permettra de mettre en pratique des techniques de consolidation fiables.

Synthèses multifeuilles

Comme évoqué au début de ce chapitre, dans vos tableaux, privilégiez l’affichage des totaux en tête de tableau et non en pied. Vous n’aurez ainsi pas besoin de descendre de x lignes pour visualiser les synthèses et l’ajout de nouvelles lignes de données ne perturbera pas vos affichages.

Ce classeur comporte neuf feuilles de calcul identiques, chacune relative aux ventes effectuées par une agence de votre entreprise. Une dixième feuille va devoir synthétiser les données de l’ensemble des agences.

La feuille des ventes de votre agence d’Aix :

Toutes les feuilles ont la même structure.

Voici l’ensemble des onglets du classeur :

Totaliser les données de plusieurs feuilles à l’aide de formules

Notre premier travail va consister à totaliser les valeurs contenues dans les cellules D3 de chacune des feuilles des agences.

Il est possible d’effectuer cette somme manuellement en additionnant les cellules de chacune des feuilles. Sur neuf feuilles, ceci est encore possible mais si le nombre de feuilles...