Comment faire des sous-totaux automatiques avec Excel ?

Comprendre la fonction SOUS.TOTAL dans Excel

La fonction SOUS.TOTAL dans Excel est conçue pour calculer des sous-totaux dans une liste ou une base de données. Elle est plus polyvalente que la simple fonction SOMME, car elle peut ignorer les lignes masquées (par un filtre, par exemple) et/ou les autres sous-totaux déjà présents dans la plage de données. Cela évite les doubles comptages et garantit des résultats précis, même lorsque vous manipulez des données complexes.

Syntaxe de la fonction SOUS.TOTAL

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

=SOUS.TOTAL(no_fonction; référence1; [référence2]; ...)

• no_fonction : Un nombre compris entre 1 et 11 (incluant les lignes masquées manuellement) ou entre 101 et 111 (excluant les lignes masquées par un filtre). Ce nombre spécifie la fonction à utiliser pour le calcul du sous-total (somme, moyenne, etc.).
• référence1 : La première plage de cellules à laquelle appliquer la fonction.
• référence2; ... (facultatif) : D'autres plages de cellules à inclure dans le calcul. Vous pouvez spécifier jusqu'à 254 références.

Les différents codes de fonction

Le premier argument de la fonction SOUS.TOTAL, no_fonction, détermine le type de calcul à effectuer. Voici les codes de fonction les plus couramment utilisés :

• 1 ou 101 : MOYENNE
• 2 ou 102 : NB (compte le nombre de cellules contenant des nombres)
• 3 ou 103 : NBVAL (compte le nombre de cellules non vides)
• 4 ou 104 : MAX
• 5 ou 105 : MIN
• 6 ou 106 : PRODUIT
• 7 ou 107 : ECARTYPE
• 8 ou 108 : ECARTYPEP
• 9 ou 109 : SOMME
• 10 ou 110 : VAR
• 11 ou 111 : VARP

La différence entre les codes 1-11 et 101-111 réside dans la manière dont ils traitent les lignes masquées. Les codes 1-11 incluent les lignes masquées manuellement, tandis que les codes 101-111 les excluent. Les deux ensembles de codes excluent toujours les lignes masquées par un filtre.

Comment utiliser SOUS.TOTAL étape par étape

Voici un guide pas à pas pour utiliser la fonction SOUS.TOTAL dans Excel :

1. Préparez vos données : Assurez-vous que vos données sont organisées dans un tableau avec des en-têtes de colonnes clairs et des données cohérentes.
2. Sélectionnez la plage de données : Sélectionnez la plage de cellules contenant les données pour lesquelles vous souhaitez calculer les sous-totaux.

3. Insérez des sous-totaux automatiques (méthode rapide) : Allez dans l'onglet Données et cliquez sur le bouton Sous-total. Une boîte de dialogue s'ouvrira. Dans la boîte de dialogue "Sous-total", configurez les paramètres suivants :

   • À chaque changement de : Choisissez la colonne qui détermine les groupes pour lesquels vous voulez calculer les sous-totaux (par exemple, "Région").
   • Utiliser la fonction : Choisissez la fonction à utiliser pour le calcul (par exemple, "Somme").
   • Ajouter un sous-total à : Cochez les colonnes contenant les valeurs que vous voulez additionner (par exemple, "Ventes").
   • Cochez "Remplacer les sous-totaux actuels" si vous en avez déjà et que vous voulez les remplacer.
   • Cochez "Saut de page entre les groupes" si vous voulez un saut de page à l'impression entre chaque groupe.
   • Cochez "Résumé sous les données" si vous voulez que le sous-total apparaisse après les données du groupe, ce qui est plus lisible. Cliquez sur "OK".

   Excel insérera automatiquement des lignes de sous-total pour chaque groupe et un total général à la fin du tableau. Il créera également un plan, avec des boutons de niveau sur le côté gauche, qui vous permettent de masquer ou d'afficher les détails des groupes.

4. Utilisez la fonction SOUS.TOTAL manuellement (méthode flexible) : Si vous souhaitez un contrôle plus précis sur l'emplacement et le type de sous-totaux, vous pouvez utiliser la fonction SOUS.TOTAL directement dans une cellule :

   • Sélectionnez la cellule où vous voulez afficher le sous-total.
   • Tapez =SOUS.TOTAL( et Excel vous proposera une liste des fonctions disponibles.
   • Choisissez le code de fonction approprié (par exemple, 9 pour la somme) et tapez un point-virgule (;).
   • Sélectionnez la plage de cellules à inclure dans le calcul du sous-total.
   • Fermez la parenthèse ) et appuyez sur Entrée.

   Par exemple, si vous voulez calculer la somme des ventes dans la plage C2:C10, la formule serait =SOUS.TOTAL(9;C2:C10). Si vous voulez ignorer les lignes masquées par un filtre, utilisez =SOUS.TOTAL(109;C2:C10). Vous pouvez adapter cette formule à différentes colonnes et fonctions pour obtenir les sous-totaux souhaités.

Exemple pratique : Analyse des ventes par région

Imaginons un tableau de ventes avec les colonnes "Région", "Produit", et "Ventes". Vous souhaitez calculer le total des ventes pour chaque région. Voici comment procéder :

1. Sélectionnez votre tableau de données (y compris les en-têtes).
2. Allez dans l'onglet Données et cliquez sur Sous-total.
3. Dans la boîte de dialogue, sélectionnez :
   • "Région" dans le champ "À chaque changement de".
   • "Somme" dans le champ "Utiliser la fonction".
   • "Ventes" dans le champ "Ajouter un sous-total à".
4. Cliquez sur "OK".

Excel insérera automatiquement des sous-totaux pour chaque région, affichant le total des ventes pour chaque région ainsi qu'un total général. Vous pourrez utiliser les boutons de plan pour afficher uniquement les sous-totaux par région ou développer pour voir le détail des ventes par produit dans chaque région.

Vous pouvez ensuite filtrer le tableau par région pour afficher uniquement les données d'une région spécifique. La fonction SOUS.TOTAL recalculera automatiquement le sous-total en fonction des lignes visibles, vous donnant ainsi une vue dynamique des ventes par région.

Astuces et bonnes pratiques

• Utilisez des noms de plages : Au lieu d'utiliser des références de cellules directes (par exemple, C2:C10), vous pouvez définir des noms de plages (par exemple, "Ventes") pour rendre vos formules plus lisibles et plus faciles à maintenir. Pour définir un nom de plage, sélectionnez la plage de cellules, allez dans la zone de nom (à gauche de la barre de formule) et tapez le nom souhaité. Ensuite, vous pouvez utiliser ce nom dans votre formule SOUS.TOTAL : =SOUS.TOTAL(9;Ventes).
• Combinez SOUS.TOTAL avec d'autres fonctions : Vous pouvez combiner la fonction SOUS.TOTAL avec d'autres fonctions Excel pour effectuer des analyses plus complexes. Par exemple, vous pouvez utiliser la fonction SI pour conditionner le calcul du sous-total en fonction d'une certaine condition.
• Créez des tableaux croisés dynamiques : Pour des analyses plus approfondies et interactives, envisagez d'utiliser des tableaux croisés dynamiques. Ils offrent une grande flexibilité pour regrouper, filtrer et synthétiser vos données, et peuvent être une alternative plus puissante à la fonction SOUS.TOTAL pour certaines tâches.
• Attention aux lignes masquées manuellement : Rappelez-vous que les codes de fonction 1-11 incluent les lignes masquées manuellement. Si vous ne voulez pas inclure ces lignes dans vos calculs, utilisez les codes 101-111.
• Vérifiez vos résultats : Après avoir inséré des sous-totaux, vérifiez toujours vos résultats pour vous assurer qu'ils sont corrects et cohérents. Comparez les sous-totaux avec les valeurs attendues et assurez-vous qu'il n'y a pas de doubles comptages ou d'erreurs de calcul.

Erreurs courantes à éviter

• Utiliser la fonction SOMME à la place de SOUS.TOTAL : La fonction SOMME ne tient pas compte des lignes masquées et peut entraîner des doubles comptages si vous avez déjà des sous-totaux dans votre tableau. Utilisez SOUS.TOTAL pour des résultats plus précis.
• Choisir le mauvais code de fonction : Assurez-vous de choisir le code de fonction approprié en fonction du type de calcul que vous voulez effectuer (somme, moyenne, etc.) et de la manière dont vous voulez traiter les lignes masquées.
• Oublier de mettre à jour les sous-totaux après avoir modifié les données : Si vous modifiez les données de votre tableau, vous devrez peut-être recalculer les sous-totaux pour refléter les changements. Vous pouvez le faire en sélectionnant une cellule dans le tableau et en allant dans l'onglet Données et en cliquant sur Sous-total, puis en cliquant sur OK (sans rien modifier). Excel recalculera alors les sous-totaux.
• Ne pas comprendre l'impact des filtres : Les filtres affectent les résultats de la fonction SOUS.TOTAL. Assurez-vous de comprendre comment les filtres influencent vos calculs et ajustez vos filtres en conséquence pour obtenir les résultats souhaités.

Alternatives à la fonction SOUS.TOTAL

Bien que la fonction SOUS.TOTAL soit utile, il existe d'autres méthodes pour analyser et synthétiser des données dans Excel :

• Tableaux croisés dynamiques : Offrent une grande flexibilité pour regrouper, filtrer et synthétiser des données. Idéal pour des analyses complexes et interactives.
• Fonctions SOMME.SI, MOYENNE.SI, NB.SI : Permettent de calculer des sommes, des moyennes ou des nombres en fonction de critères spécifiques.
• Power Query : Un outil puissant pour importer, transformer et nettoyer des données provenant de différentes sources.

Le choix de la méthode dépendra de vos besoins spécifiques et de la complexité de vos données.

Conclusion

La fonction SOUS.TOTAL d'Excel est un outil précieux pour calculer des sous-totaux automatiques et dynamiques dans vos feuilles de calcul. En comprenant sa syntaxe, ses codes de fonction et ses limitations, vous pouvez l'utiliser efficacement pour simplifier vos analyses de données et prendre des décisions éclairées. N'hésitez pas à expérimenter avec différents exemples et astuces pour maîtriser pleinement cette fonction et optimiser votre utilisation d'Excel.