Téléchargez l'exemple de classeur
Ce didacticiel montre comment utiliser le Fonction Excel SOUS-TOTAL dans Excel pour calculer des statistiques récapitulatives.
Présentation de la fonction SOUS-TOTAL
La fonction SOUS-TOTAL Calcule une statistique récapitulative pour une série de données. Les statistiques disponibles incluent, mais sans s'y limiter, la moyenne, l'écart type, le nombre, le min et le max. Voir la liste complète ci-dessous dans la section des entrées de fonction :
Pour utiliser la fonction de feuille de calcul Excel SOUS-TOTAL, sélectionnez une cellule et tapez :
(Remarquez comment les entrées de formule apparaissent)
SOUS-TOTAL Fonction Syntaxe et entrées :
| 1 | =SOUS-TOTAL(num_fonction,REF1) |
num_fonction - Un nombre représentant l'opération à effectuer.
REF1 - Plages ou références contenant des données à calculer.
Qu'est-ce que la fonction SOUS-TOTAL ?
Le SOUS-TOTAL est l'une des fonctions uniques des feuilles de calcul car il peut faire la différence entre les cellules masquées et les cellules non masquées. Cela peut s'avérer très utile lorsque vous traitez des plages filtrées ou lorsque vous devez configurer des calculs en fonction de différentes sélections d'utilisateurs. Puisqu'il sait également ignorer d'autres fonctions SOUS-TOTAL de ses calculs, nous pouvons également l'utiliser dans des données résumées volumineuses sans craindre un double comptage.
Résumé de base avec SOUS-TOTAL
Supposons que vous disposiez d'un tableau des ventes de produits triées et que vous souhaitiez créer des totaux pour chaque produit, ainsi qu'un total global. Vous pouvez utiliser un tableau croisé dynamique ou insérer des formules. Considérez cette disposition :
J'ai placé des fonctions SOUS-TOTAL dans les cellules B5 et B8 qui ressemblent à
| 1 | =SOUS-TOTAL(9, B2:B4) |
À partir de la syntaxe, vous pouvez utiliser une variété de nombres pour le premier argument. Dans notre cas particulier, nous utilisons 9 pour indiquer que nous voulons faire une somme.
Concentrons-nous sur la cellule B9. Il a cette formule, qui inclut toute la plage de données de la colonne B, mais n'inclut pas les autres sous-totaux.
| 1 | =SOUS-TOTAL(9, B2:B8) |
REMARQUE : Si vous ne souhaitez pas écrire vous-même toutes les formules récapitulatives, vous pouvez accéder au ruban Données et utiliser l'assistant Plan - Sous-total. Il insérera automatiquement des lignes et placera les formules pour vous.
Différence dans les premiers arguments
Dans le premier exemple, nous avons utilisé un 9 pour indiquer que nous voulions faire une somme. La différence entre l'utilisation de 9 et 109 serait la façon dont nous voulons que la fonction gère les lignes cachées. Si vous utilisez les désignations 1XX, la fonction n'inclura pas les lignes qui ont été manuellement masquées ou filtrées.
Voici notre table d'avant. Nous avons déplacé les fonctions afin que nous puissions voir la différence entre les arguments 9 et 109. Avec tout visible, les résultats sont les mêmes.
Si nous appliquons un filtre pour filtrer la valeur de 6 dans la colonne B, les deux fonctions restent les mêmes.
Si nous masquons manuellement les lignes, nous voyons la différence. La fonction 109 a pu ignorer la ligne cachée alors que la fonction 9 ne l'a pas fait.
Modifier l'opération mathématique avec SOUS-TOTAL
Vous souhaiterez peut-être parfois donner à votre utilisateur la possibilité de modifier le type de calculs effectués. Par exemple, veulent-ils obtenir la somme ou la moyenne. Étant donné que SUBTOTAL contrôle l'opération mathématique par un numéro d'argument, vous pouvez l'écrire dans une seule formule. Voici notre configuration :
Nous avons créé une liste déroulante dans D2 où l'utilisateur peut sélectionner "Somme" ou "Moyenne". La formule en E2 est :
| 1 | =SOUS-TOTAL(SI(D2="Moyenne",1,SI(D2="Somme",9)),B2:B4) |
Ici, la fonction SI va déterminer quel argument numérique donner au SOUS-TOTAL. Si A5 est « Moyen », alors il produira un 1 et SOUS-TOTAL donnera la moyenne de B2:B4. Ou, si A5 est égal à "Somme", alors le SI renvoie un 9, et nous obtenons un résultat différent.
Vous pouvez étendre cette capacité en utilisant une table de recherche pour répertorier encore plus de types d'opérations que vous souhaitez effectuer. Votre table de recherche pourrait ressembler à ceci
Ensuite, vous pouvez changer la formule dans E2 pour qu'elle soit
| 1 | =SOUS-TOTAL(RECHERCHEV(A5, Table de consultation, 2, 0), B2:B4) |
Formules conditionnelles avec SOUS-TOTAL
Bien que SUBTOTAL puisse effectuer de nombreuses opérations, il ne peut pas vérifier les critères par lui-même. Cependant, nous pouvons l'utiliser dans une colonne d'assistance pour effectuer cette opération. Lorsque vous avez une colonne de données dont vous savez qu'elle toujours contiennent une donnée, vous pouvez utiliser la capacité de SOUS-TOTAL pour détecter les lignes cachées.
Voici la table avec laquelle nous allons travailler dans cet exemple. Finalement, nous aimerions pouvoir additionner les valeurs pour "Apple", mais aussi laisser l'utilisateur filtrer la colonne Qté.
Tout d'abord, créez une colonne d'aide qui hébergera la fonction SOUS-TOTAL. En C2, la formule est :
| 1 | =SOUS-TOTAL(103, A2) |
N'oubliez pas que 103 signifie que nous voulons faire un COUNTA. Je recommande d'utiliser COUNTA car vous pouvez alors remplir votre cellule de référence de A2 avec Soit des chiffres ou du texte. Vous aurez maintenant un tableau qui ressemble à ceci :
Cela ne semble pas utile au début car toutes les valeurs sont juste 1. Cependant, si nous masquons la ligne 3, ce "1" dans C3 deviendra 0 car il pointe vers une ligne cachée. Bien qu'il soit impossible d'avoir une image montrant la valeur de la cellule cachée spécifique, vous pouvez la vérifier en masquant la ligne, puis en écrivant une formule de base comme celle-ci pour vérifier.
| 1 | =C3 |
Maintenant que nous avons une colonne dont la valeur changera selon qu'elle est cachée ou non, nous sommes prêts à écrire l'équation finale. Notre SUMIFS ressemblera à ceci
Dans cette formule, nous allons seulement additionner les valeurs de la colonne B lorsque la colonne A est égale à "Apple", et la valeur dans la colonne C est 1 (aka, la ligne n'est pas masquée). Disons que notre utilisateur veut filtrer le 600, car il semble anormalement élevé. Nous pouvons voir que notre formule donne un résultat correct.
Avec cette capacité, vous pouvez appliquer un contrôle à un COUNTIFS, SUMIFS ou même un SUMPRODUCT. Vous ajoutez la possibilité de laisser vos utilisateurs contrôler certains trancheurs de table, et vous êtes prêt à créer un tableau de bord impressionnant.
SOUS-TOTAL dans Google Sheets
La fonction SOUS-TOTAL fonctionne exactement de la même manière dans Google Sheets que dans Excel :
SOUS-TOTAL Exemples en VBA
Vous pouvez également utiliser la fonction SOUS-TOTAL dans VBA. Taper:application.worksheetfunction.subtotal(function_num,reh1)
Exécuter les instructions VBA suivantes
| 1234567891011121314151617 | Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Plage("D7") = Application.WorksheetFunction.Subtotal(1, Plage("D2:D5"))Plage("D8") = Application.WorksheetFunction.Subtotal(2, Plage("D2:D5"))Plage("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))Plage("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Plage("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
produira les résultats suivants
Pour les arguments de la fonction (num_fonction, etc.), vous pouvez soit les saisir directement dans la fonction, soit définir des variables à utiliser à la place.
Revenir à la liste de toutes les fonctions dans Excel
