Excel : obtenir un sous-total pour vos calculs

Le tableur de Microsoft permet la réa­li­sa­tion de tableaux complets et de calculs complexes. Les fonctions d’Excel sont souvent très utiles, mais sont rarement in­fluen­cées par le design et l’apparence de vos tableaux : vous avez masqué certaines cellules de votre aperçu ? Aucun problème ! Les fonctions comp­ta­bi­li­sent même les valeurs filtrées. Cela dit, peut-être ne le souhaitez-vous pas... Pour que vous calculs réa­gis­sent à vos filtres et cellules masquées, la fonction SOUS.TOTAL est des plus pratiques. Elle peut combiner de nom­breuses opé­ra­tions, tout en vous assurant une plus grande maîtrise des cellules d’Excel à intégrer.

Lorsque vous tra­vail­lez avec de gros volumes de données, vous ne souhaitez pas toujours en afficher toutes les plages. En masquant certaines lignes ou en filtrant certaines valeurs, vous pouvez faciliter la com­pré­hen­sion de tableaux complets. En syn­thé­ti­sant les tableaux pour n’afficher que les données es­sen­tielles, leur ma­ni­pu­la­tion peut s’en trouver ex­trê­me­ment sim­pli­fiée. Les valeurs masquées ne sont toutefois pas perdues, elles con­ti­nuent de faire partie de votre classeur et peuvent donc être utilisées pour la réa­li­sa­tion de calculs au sein de fonctions. C’est là un avantage majeur d’Excel.

Cependant, ce qui, en temps normal, peut re­pré­sen­ter un avantage peut également poser problème dans certaines si­tua­tions. Ainsi par exemple, si vous souhaitez que les fonctions Excel réa­gis­sent à l’affichage actuel de votre tableau et ajustent leurs résultats en fonction, les méthodes de calcul ha­bi­tuelles ne sont pas adaptées. Il vous faudrait presque créer un nouveau tableau, dans lequel les valeurs filtrées n’ap­pa­rais­sent pas. La fonction SOUS.TOTAL s’impose comme la solution al­ter­na­tive idéale, car elle peut être con­fi­gu­rée de manière à ne pas prendre en compte les cellules masquées.

SOUS.TOTAL regroupe onze fonctions : les uti­li­sa­teurs sé­lec­tion­nent le mode de calcul à appliquer à la fonction et décident s’il faut, ou non, tenir compte des cellules masquées. Les valeurs tem­po­rai­re­ment masquées par un filtre ne sont pas prises en compte dans les calculs de la fonction. SOUS.TOTAL peut ainsi être affiché sous forme de résultat sup­plé­men­taire, en plus du résultat final. La com­pa­rai­son des deux valeurs obtenues apporte souvent des in­for­ma­tions sup­plé­men­taires.

En théorie, la fonction SOUS.TOTAL ne nécessite que deux in­di­ca­tions. Dans un premier temps, indiquez le calcul à effectuer. Le deuxième argument ainsi que les suivants con­tien­nent des in­for­ma­tions sur la plage de cellules à prendre en compte. Vous devez en saisir au moins une, mais la formule peut compter jusqu’à 254 plages.

Le premier paramètre de la fonction SOUS.TOTAL cor­res­pond aux pro­prié­tés d’une autre fonction. Excel en fournit une liste. Lorsque vous saisissez le nombre cor­res­pon­dant, SOUS.TOTAL exécute le calcul voulu. Chaque fonction doit com­prendre deux nombres. Les valeurs comprises entre 1 et 11 veillent à ce que la fonction SOUS.TOTAL intègre également les valeurs masquées. Lorsque vous saisissez des valeurs comprises entre 101 et 111, la fonction ignore les cellules masquées dans la plage saisie. En voici une vue d’ensemble.

Les cellules masquées sont prises en compte dans le calcul :

• 1 : MOYENNE
• 2 : NB
• 3 : NB2
• 4 : MAX
• 5 : MIN
• 6 : PRODUIT
• 7 : ECARTYPE
• 8 : ECARTYPEP
• 9 : SOMME
• 10 : VAR
• 11 : VAR.P

Les cellules masquées ne sont pas prises en compte dans le calcul :

• 101 : MOYENNE
• 102 : NB
• 103 : NB2
• 104 : MAX
• 105 : MIN
• 106 : PRODUIT
• 107 : ECARTYPE
• 108 : ECARTYPEP
• 109 : SOMME
• 110 : VAR
• 111 : VAR.P

Le parfait exemple de la fonction SOUS.TOTAL est le calcul d’une somme. Outre le résultat final, qui tient compte de toutes les valeurs, cette fonction vous permet de n’ad­di­tion­ner que les valeurs affichées, non affectées par un filtre.

Dans cet exemple, nous prenons les valeurs de deux colonnes pour les ajouter les unes aux autres. Nous avons saisi la valeur 109 comme premier paramètre. La fonction prendra donc uni­que­ment en compte les valeurs affichées.

Il en va de même pour d’autres types de calcul. Vous pouvez ainsi, par exemple, calculer la valeur moyenne.

Vous pouvez également intégrer d’autres fonctions à la fonction SOUS.TOTAL. Le résultat de la fonction peut ainsi di­rec­te­ment être appliqué à d’autres calculs, sans né­ces­si­ter la création de cellules d’aide. Vous pouvez, par exemple, ad­di­tion­ner plusieurs sous-totaux, mais aussi combiner la fonction SOUS.TOTAL avec la fonction SI. Pour ce faire, vous pouvez créer une cellule dans laquelle saisir le mode de calcul souhaité, sous la forme d’un menu déroulant, par exemple. La formule est alors plus vo­lu­mi­neuse, mais, une fois créée, elle n’exigera plus aucune mo­di­fi­ca­tion de votre part. Procédez comme suit, si vous utilisez ré­gu­liè­re­ment les trois formes de calcul SOMME, NB et MOYENNE, par exemple.

Via les filtres au­to­ma­tiques, sé­lec­tion­nez les données à prendre en compte puis, dans la liste dé­rou­lante, dé­fi­nis­sez le mode de calcul à exécuter. Les requêtes SI exé­cu­te­ront alors toujours la bonne fonction SOUS.TOTAL.

Outre la fonction sus­men­tion­née, que vous pouvez fa­ci­le­ment intégrer à vos formules, Excel met à votre dis­po­si­tion un outil du même nom. Pour le trouver, rendez-vous à l’onglet « Données » et cliquez sur le bouton « Sous-total ». Cet outil est par­ti­cu­liè­re­ment utile si vous utilisez une liste de plusieurs colonnes dans Excel. Dans l’une des colonnes se trouve un jeu de données fixes com­pre­nant, pour la plupart, des valeurs non nu­mé­riques (des noms, par exemple). Ces entrées sont affectées à des valeurs nu­mé­riques : des chiffres d’affaires, des scores, des mesures, etc. L’outil vous aide à regrouper les données et à calculer des sous-totaux : toutes les entrées si­mi­laires d’une même colonne sont re­grou­pées, et les valeurs cor­res­pon­dantes de l’autre colonne sont utilisées pour la réa­li­sa­tion de calculs.

Lorsque vous sé­lec­tion­nez la liste et démarrez l’outil Sous-total, Excel ouvre un nouveau menu. Sé­lec­tion­nez-y la colonne à partir de laquelle vous souhaitez créer un groupe. (Pour cela, le titre des colonnes est très important. En effet, la sélection de la plage se fait à partir de ces intitulés, et non à partir de la référence des cellules.) À l’étape suivante, sé­lec­tion­nez le mode de calcul à appliquer. Vous pouvez sé­lec­tion­ner les fonctions qui vous étaient déjà ac­ces­sibles via la fonction SOUS.TOTAL. Pour finir, sé­lec­tion­nez les valeurs à utiliser pour le calcul. Vous pouvez en sé­lec­tion­ner plusieurs. Il est ensuite possible de procéder à trois mo­di­fi­ca­tions plutôt cos­mé­tiques.

• Remplacer les sous-totaux dis­po­nibles : souhaitez-vous afficher la liste de sous-totaux des dif­fé­rents calculs ou uni­que­ment le dernier calcul ? Dé­ter­mi­nez-le ici.
• Ajouter des sauts de page entre les groupes : un saut de page est ajouté après la liste de chaque groupe.
• Afficher les résultats sous les données : le sous-total de chaque groupe est di­rec­te­ment affiché sous le groupe cor­res­pon­dant.

Lorsque vous confirmez vos données, Excel modifie lé­gè­re­ment l’affichage. Un menu sup­plé­men­taire cor­res­pon­dant à l’en­tre­la­ce­ment de votre liste s’affiche à gauche. Vous pouvez afficher ou masquer les groupes d’un simple clic sur les boutons cor­res­pon­dants et ajuster ainsi l’affichage à vos besoins.