Téléchargez l'exemple de classeur
Ce didacticiel montrera comment calculer des sous-totaux par groupe à l'aide de la fonction SUMIFS dans Excel et Google Sheets.
Tableau des sous-totaux par catégorie ou groupe
Tout d'abord, nous montrerons comment créer un tableau récapitulatif de sous-total dynamique à partir d'une plage de données dans Excel 365 ou plus ou Google Sheets.
Nous utilisons la fonction UNIQUE et la fonction SUMIFS pour sous-totaliser automatiquement le Nombre de produits par Groupe de produits:
| 1 | =SUMIFS(C3:C11,B3:B11,E3) |
Pour créer ce tableau de sous-total, nous utilisons l'application standard de la fonction SUMIFS pour additionner les Nombre de produits qui correspondent à chacun Groupe de produits. Cependant, avant que cela ne soit possible, nous devons créer une liste de Groupes de produits. Les utilisateurs de Microsoft Excel 365 et de Google Sheets ont accès à la fonction UNIQUE pour créer une liste dynamique de valeurs uniques à partir d'une plage de cellules. Dans cet exemple, nous ajoutons la formule suivante à la cellule E3 :
| 1 | =UNIQUE(B3:B11) |
Lorsque cette formule est saisie, une liste est automatiquement créée sous la cellule pour afficher toutes les valeurs uniques trouvées dans le Groupe de produits plage de données. Dans cet exemple, la liste s'est étendue pour couvrir E3:E5 pour afficher les 3 uniques Groupe de produits valeurs.
Il s'agit d'une fonction de tableau dynamique où la taille de la liste des résultats n'a pas besoin d'être définie, et elle se rétrécira et augmentera automatiquement à mesure que les valeurs des données d'entrée changent.
Notez que dans Excel 365, la fonction UNIQUE n'est pas sensible à la casse, mais dans Google Sheets, elle l'est. Considérez la liste {“A”; "une"; « B »; "c"}. La sortie de la fonction UNIQUE dépend du programme :
- {"UNE"; « B »; "c"} dans Excel 365
- {"UNE"; "une"; « B »; "c"} dans Google Sheets
Si vous utilisez une version d'Excel antérieure à Excel 365, vous devrez adopter une approche différente. Ceci est discuté dans la section suivante.
Tableau de sous-total par catégorie ou groupe - Pré Excel 365
Si vous utilisez une version d'Excel antérieure à Excel 365, la fonction UNIQUE n'est pas disponible. Pour reproduire le même comportement, vous pouvez combiner la fonction INDEX et la fonction MATCH avec une fonction COUNTIF pour créer une formule matricielle afin de produire une liste de valeurs uniques à partir d'une plage de cellules :
| 1 | {=INDICE($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Pour que cette formule fonctionne, les références de cellules fixes doivent être écrites avec soin, la fonction COUNTIF faisant référence à la plage $E$2:E2, qui est la plage allant de E2 jusqu'à la cellule au-dessus de la cellule contenant la formule.
La formule doit également être saisie sous forme de formule matricielle en appuyant sur CTRL + MAJ + ENTRÉE après avoir été écrite. Cette formule est une Formule matricielle à 1 cellule, qui peut ensuite être copié-collé dans les cellules E4, E5, etc. N'entrez pas cela comme une formule matricielle pour toute la plage E3:E5 en une seule action.
De la même manière que dans l'exemple précédent, une fonction SUMIFS est ensuite utilisée pour sous-totaliser le Nombre de produits par Groupe de produits:
| 1 | =SUMIFS(C3:C11,B3:B11,E3) |
Somme par catégorie ou par groupe - Sous-totaux dans les tableaux de données
Comme alternative à la méthode du tableau récapitulatif présentée ci-dessus, nous pouvons ajouter des sous-totaux directement dans un tableau de données. Nous allons le démontrer en utilisant les fonctions SI avec la fonction SUMIFS pour ajouter un Sous-total par groupe à la table de données d'origine.
| 1 | =SI(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
Cet exemple utilise une fonction SUMIFS imbriquée dans une fonction SI. Décomposons l'exemple en étapes :
Pour ajouter des statistiques récapitulatives directement dans un tableau de données, nous pouvons utiliser la fonction SUMIFS. Nous commençons par totaliser les Nombre de produits qui correspondent aux Groupe de produits:
| 1 | =SUMIFS(C3:C11,B3:B11,B3) |
Cette formule produit une valeur de sous-total pour chaque ligne de données. Pour afficher les sous-totaux uniquement dans la première ligne de données de chaque Groupe de produits, nous utilisons la fonction SI. Notez que les données doivent déjà être triées par Groupe de produits pour s'assurer que les sous-totaux sont affichés correctement.
| 1 | =SI(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
La fonction SI compare chaque ligne de données Groupe de produits value avec la ligne de données au-dessus, et s'ils ont la même valeur, il génère une cellule vide ("").
Si la Groupe de produits les valeurs sont différentes, la somme est affichée. De cette façon, chaque Groupe de produits sum n'est affiché qu'une seule fois (sur la ligne de sa première instance).
Tri des ensembles de données par groupe
Si les données ne sont pas déjà triées, nous pouvons toujours utiliser la même formule pour le sous-total.
L'ensemble de données ci-dessus n'est pas trié par Groupe de produits, alors le Sous-total par groupe La colonne affiche chaque sous-total plusieurs fois. Pour obtenir les données dans le format souhaité, nous pouvons sélectionner le tableau de données et cliquer sur « Trier de A à Z ».
Verrouillage des références de cellule
Pour rendre nos formules plus faciles à lire, nous avons montré certaines des formules sans références de cellules verrouillées :
| 1 | =SI(B3=B2,"",SUMIFS(C3:C11,B3:B11,B3)) |
Mais ces formules ne fonctionneront pas correctement si elles sont copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :
| 1 | =SI(B3=B2,"",SUMIFS($C$3:$C$11,$B$3:$B$11,B3)) |
Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.
Utilisation de tableaux croisés dynamiques pour afficher les sous-totaux
Afin de supprimer l'obligation de pré-trier les données par Groupe de produits, nous pouvons utiliser la puissance des tableaux croisés dynamiques pour résumer les données à la place. Les tableaux croisés dynamiques calculent automatiquement les sous-totaux et affichent les totaux et les sous-totaux dans plusieurs formats différents.
Somme par catégorie ou par groupe dans Google Sheets
Ces formules fonctionnent de la même manière dans Google Sheets que dans Excel. Cependant, la fonction UNIQUE est sensible à la casse dans Google Sheets.
