Comment additionner des valeurs sans inclure ou en excluant les sous-totaux dans Excel ?
Lorsque vous travaillez avec des ensembles de données dans Excel, il est courant d'organiser et d'analyser vos chiffres en calculant des sous-totaux pour différents groupes ou catégories. Cependant, après avoir ajouté ces sous-totaux, vous pouvez vouloir calculer un total général qui exclut ces valeurs de sous-total, évitant ainsi tout double comptage dans votre somme finale. Le défi est que, lorsque vous ajoutez directement tous les nombres, les sous-totaux sont inclus dans le total global, ce qui entraîne une somme totale plus grande que prévu. Pour éviter cela, vous devez trouver un moyen d'additionner les données originales tout en excluant les lignes de sous-total. Dans cet article, nous présentons plusieurs méthodes pratiques pour résoudre ce problème, afin que vous puissiez calculer efficacement des résultats précis dans différentes situations.
Additionner des valeurs sans sous-totaux avec la fonction SOMME dans Excel
Additionner des valeurs sans sous-totaux avec la fonction SOUS.TOTAL dans Excel
Code VBA - Additionner des valeurs en excluant les lignes de sous-total
Filtrage - Utiliser un filtre et SOUS.TOTAL pour additionner uniquement les lignes visibles (non sous-totaux)
Additionner des valeurs sans sous-totaux avec la fonction SOMME dans Excel
Une approche largement adoptée pour calculer des sous-totaux de groupe consiste à insérer la fonction SOMME à l'intérieur ou en dessous de chaque groupe pertinent. Cependant, lorsque vous souhaitez calculer le total général pour l'ensemble de la liste tout en ignorant ces lignes de sous-total de groupe, une étape supplémentaire est souvent nécessaire. Voici une méthode que vous pouvez utiliser pour y parvenir :
1. Utilisez la fonction SOMME dans chaque groupe pour générer les sous-totaux respectifs, comme le montre cette capture d'écran :
2. Ensuite, pour trouver le total général sans inclure ces lignes de sous-total, entrez la formule suivante dans une cellule vide où vous souhaitez que le résultat apparaisse :
=SOMME(B2:B21)/2
Appuyez sur Entrée pour obtenir le total correct. Cette formule fonctionne car elle suppose que vous avez inséré des sous-totaux avec la fonction SOMME immédiatement après les groupes pertinents, doublant ainsi la somme des données brutes. La division par 2 élimine le total dupliqué. Notez bien que cette méthode convient surtout lorsque l'ensemble de données est structuré de manière à ce que la somme des valeurs originales et des sous-totaux forme exactement deux fois la somme des données originales.
Si votre structure de données est plus complexe, ou si le nombre de lignes de sous-total varie, les méthodes alternatives décrites ci-dessous peuvent offrir une solution plus précise et flexible.
Additionner des valeurs sans sous-totaux avec la fonction SOUS.TOTAL dans Excel
La fonction SOUS.TOTAL dans Excel offre une méthode intégrée pour effectuer des calculs uniquement sur les lignes filtrées ou visibles. Cela la rend particulièrement utile lorsqu'on travaille avec des listes contenant des sous-totaux, car les formules SOUS.TOTAL peuvent être configurées pour ignorer d'autres résultats SOUS.TOTAL dans la plage, évitant ainsi tout double comptage des valeurs.
Pour utiliser la fonction SOUS.TOTAL dans ce contexte, suivez ces étapes :
1. Entrez la fonction SOUS.TOTAL pour chaque groupe comme suit :
=SOUS.TOTAL(9;B2:B10)
Cela applique l'opération SOMME (numéro de fonction 9) à la plage spécifiée et est souvent utilisé dans le cadre de la fonctionnalité Sous-total du menu Données > Sous-total d'Excel.
2. Pour le total général qui exclut les sous-totaux internes, entrez la formule suivante dans votre cellule choisie :
=SOUS.TOTAL(9;B2:B21)
Appuyez sur Entrée pour additionner automatiquement les valeurs de données, en ignorant toutes les fonctions SOUS.TOTAL imbriquées dans la plage. Cette approche convient aux listes qui utilisent la fonction SOUS.TOTAL pour les résumés de groupe et élimine le risque d'ajouter plusieurs fois les sous-totaux.
Notez que la fonction SOUS.TOTAL fonctionne mieux lorsque les cellules de sous-total ont été générées à l'aide de SOUS.TOTAL plutôt que de SOMME. De plus, si vous filtrez ou masquez des lignes, SOUS.TOTAL peut être configuré pour ne faire la somme que des données visibles (non masquées), ce qui est utile dans des situations de rapports dynamiques.
Code VBA - Additionner des valeurs en excluant les lignes de sous-total
Si votre structure de données est complexe ou si vous voulez une méthode automatisée pour additionner uniquement les lignes de données brutes (en excluant les sous-totaux), vous pouvez utiliser une macro VBA simple. Cette approche vous permet d'identifier et d'additionner par programmation uniquement les lignes qui correspondent à vos critères, tels que basés sur un formatage spécifique, la présence de formules, ou d'autres attributs distinctifs.
Par exemple, si vos lignes de sous-total sont calculées avec des formules (telles que SOMME ou SOUS.TOTAL), et que vos lignes de données brutes contiennent uniquement des valeurs statiques, vous pouvez configurer le code VBA pour additionner uniquement les cellules dans une colonne spécifique qui ne contiennent pas de formules. Voici comment procéder :
1. Cliquez sur Outils de développement > Visual Basic pour ouvrir la fenêtre Microsoft Visual Basic for Applications.
2. Dans la nouvelle fenêtre, cliquez sur Insérer > Module et collez le code suivant dans le module :
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Cliquez sur le bouton 
Exécuter pour exécuter le code. Une invite apparaîtra pour vous demander de sélectionner la plage contenant vos données. La macro additionnera ensuite uniquement les cellules de votre sélection qui ne contiennent pas de formules, en ignorant ainsi les lignes typiques de sous-total.
Filtrage - Utiliser un filtre et SOUS.TOTAL pour additionner uniquement les lignes visibles (non sous-totaux)
Si votre ensemble de données vous permet de filtrer les lignes de sous-total (par exemple, il existe une étiquette, un mot-clé ou un modèle pour les identifier), vous pouvez utiliser la fonction Filtre intégrée d'Excel avec la fonction SOUS.TOTAL pour n'additionner que les lignes de données visibles (non filtrées). Cette technique est pratique dans les cas où les sous-totaux comportent un indicateur ou une étiquette cohérente.
1. Cliquez n'importe où dans votre ensemble de données, puis allez dans l'onglet Données et cliquez sur Filtrer pour activer les filtres pour vos colonnes.
2. Cliquez sur la flèche déroulante dans votre colonne d'aide, décochez "Sous-total" pour masquer les lignes de sous-total, et afficher uniquement les données originales.
3. Dans une cellule séparée, entrez la formule suivante pour additionner uniquement les lignes visibles (filtrées).
=SUBTOTAL(9,B2:B21)La fonction SOUS.TOTAL (avec le numéro de fonction 9 pour SOMME) ignorera toutes les lignes masquées, renvoyant ainsi le total uniquement des lignes actuellement visibles (c'est-à-dire les lignes non sous-totaux que vous avez conservées après le filtrage). Cette solution est rapide et dynamique – si vous réappliquez des filtres ou étendez votre ensemble de données, la formule se mettra à jour en conséquence.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels