Excel : obtenir un sous-total pour vos calculs

Le tableur de Microsoft permet la réalisation de tableaux complets et de calculs complexes. Les fonctions d’Excel sont souvent très utiles, mais sont rarement influencées par le design et l’apparence de vos tableaux : vous avez masqué certaines cellules de votre aperçu ? Aucun problème ! Les fonctions comptabilisent même les valeurs filtrées. Cela dit, peut-être ne le souhaitez-vous pas... Pour que vous calculs réagissent à vos filtres et cellules masquées, la fonction SOUS.TOTAL est des plus pratiques. Elle peut combiner de nombreuses opérations, tout en vous assurant une plus grande maîtrise des cellules d’Excel à intégrer.

Lorsque vous travaillez 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 compréhension de tableaux complets. En synthétisant les tableaux pour n’afficher que les données essentielles, leur manipulation peut s’en trouver extrêmement simplifiée. Les valeurs masquées ne sont toutefois pas perdues, elles continuent de faire partie de votre classeur et peuvent donc être utilisées pour la réalisation de calculs au sein de fonctions. C’est là un avantage majeur d’Excel.

Cependant, ce qui, en temps normal, peut représenter un avantage peut également poser problème dans certaines situations. Ainsi par exemple, si vous souhaitez que les fonctions Excel réagissent à l’affichage actuel de votre tableau et ajustent leurs résultats en fonction, les méthodes de calcul habituelles ne sont pas adaptées. Il vous faudrait presque créer un nouveau tableau, dans lequel les valeurs filtrées n’apparaissent pas. La fonction SOUS.TOTAL s’impose comme la solution alternative idéale, car elle peut être configurée de manière à ne pas prendre en compte les cellules masquées.

SOUS.TOTAL regroupe onze fonctions : les utilisateurs sélectionnent le mode de calcul à appliquer à la fonction et décident s’il faut, ou non, tenir compte des cellules masquées. Les valeurs temporairement 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 supplémentaire, en plus du résultat final. La comparaison des deux valeurs obtenues apporte souvent des informations supplémentaires.

En théorie, la fonction SOUS.TOTAL ne nécessite que deux indications. Dans un premier temps, indiquez le calcul à effectuer. Le deuxième argument ainsi que les suivants contiennent des informations 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 correspond aux propriétés d’une autre fonction. Excel en fournit une liste. Lorsque vous saisissez le nombre correspondant, SOUS.TOTAL exécute le calcul voulu. Chaque fonction doit comprendre 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’additionner 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 uniquement 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 directement être appliqué à d’autres calculs, sans nécessiter la création de cellules d’aide. Vous pouvez, par exemple, additionner 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 volumineuse, mais, une fois créée, elle n’exigera plus aucune modification de votre part. Procédez comme suit, si vous utilisez régulièrement les trois formes de calcul SOMME, NB et MOYENNE, par exemple.

Via les filtres automatiques, sélectionnez les données à prendre en compte puis, dans la liste déroulante, définissez le mode de calcul à exécuter. Les requêtes SI exécuteront alors toujours la bonne fonction SOUS.TOTAL.

Outre la fonction susmentionnée, que vous pouvez facilement intégrer à vos formules, Excel met à votre disposition 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 particulièrement utile si vous utilisez une liste de plusieurs colonnes dans Excel. Dans l’une des colonnes se trouve un jeu de données fixes comprenant, pour la plupart, des valeurs non numériques (des noms, par exemple). Ces entrées sont affectées à des valeurs numé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 similaires d’une même colonne sont regroupées, et les valeurs correspondantes de l’autre colonne sont utilisées pour la réalisation de calculs.

Lorsque vous sélectionnez la liste et démarrez l’outil Sous-total, Excel ouvre un nouveau menu. Sélectionnez-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électionnez le mode de calcul à appliquer. Vous pouvez sélectionner les fonctions qui vous étaient déjà accessibles via la fonction SOUS.TOTAL. Pour finir, sélectionnez les valeurs à utiliser pour le calcul. Vous pouvez en sélectionner plusieurs. Il est ensuite possible de procéder à trois modifications plutôt cosmétiques.

• Remplacer les sous-totaux disponibles : souhaitez-vous afficher la liste de sous-totaux des différents calculs ou uniquement le dernier calcul ? Déterminez-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 directement affiché sous le groupe correspondant.

Lorsque vous confirmez vos données, Excel modifie légèrement l’affichage. Un menu supplémentaire correspondant à l’entrelacement de votre liste s’affiche à gauche. Vous pouvez afficher ou masquer les groupes d’un simple clic sur les boutons correspondants et ajuster ainsi l’affichage à vos besoins.