Somme par Catégorie ou Groupe – Excel et Google Sheets

Ce tutoriel montre comment calculer des sous-totaux par groupe à l’aide de la fonction SOMME.SI.ENS dans Excel et Google Sheets.

 

Tableau de Sous-Totaux par Catégorie ou par Groupe

Ce premier exemple nécessite que vous ayez accès à la fonction UNIQUE. Cette fonction est disponible dans les versions les plus récentes d’Excel, Excel 365 ou Google Sheets. Si vous n’avez pas accès à cette fonction, passez à la section suivante.

Nous pouvons utiliser la fonction UNIQUE et la fonction SOMME.SI.ENS pour calculer automatiquement le sous-total du nombre de produits par groupe de produits. Voici la formule finale qui utilise la fonction SOMME.SI.ENS :

=SOMME.SI.ENS(C3:C11;B3:B11;E3)

Cependant, nous avons d’abord ajouté la fonction UNIQUE à la cellule E3 :

=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 la colonne Catégorie de Produits.

Il s’agit d’une fonction de tableau dynamique dans laquelle il n’est pas nécessaire de définir la taille de la liste de résultats, qui se réduit et s’agrandit automatiquement au fur et à 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 qu’elle l’est dans Google Sheets.

Tableau de Sous-Totaux par Catégorie ou Groupe – Sans la Fonction UNIQUE.

Si vous utilisez une version d’Excel sans la fonction UNIQUE, vous pouvez combiner la fonction INDEX et la fonction EQUIV avec fonction NB.SI pour créer une formule de tableau permettant de produire une liste de valeurs uniques à partir d’une plage de cellules :

=INDEX($B$3:$B$11;EQUIV(0;NB.SI($E$2:E2;$B$3:$B$11);0))

Pour que cette formule fonctionne, les références aux cellules fixes doivent être écrites avec soin, la fonction NB.SI faisant référence à la plage $E$2:E2, qui est la plage allant de E2 à la cellule située au-dessus de la cellule contenant la formule.

La formule doit également être saisie comme une formule de tableau en appuyant sur CTRL + MAJ + ENTRÉE après l’avoir écrite. Cette formule est une formule de tableau à une cellule, qui peut ensuite être copiée-collée dans les cellules E4, E5, etc. Ne saisissez pas cette formule en tant que formule de tableau pour l’ensemble de la plage E3:E5 en une seule action.

De la même manière que dans l’exemple précédent, une fonction SOMME.SI.ENS est ensuite utilisée pour effectuer un sous-total du nombre de produits par groupe de produits:

=SOMME.SI.ENS(C3:C11;B3:B11;E3)

Somme par Catégorie ou Groupe – Sous-Totaux dans le Tableau de Données

Nous pouvons également ajouter des sous-totaux directement dans un tableau de données à l’aide des fonctions SI et SOMME.SI.ENS.

=SI(B5=B4;"";SOMME.SI.ENS($C$3:$C$11;$B$3:$B$11;B5))

Cet exemple utilise une fonction SOMME.SI.ENS imbriquée dans une fonction SI. Décomposons l’exemple en plusieurs étapes :

Nous commençons par totaliser le nombre de produits qui correspondent à la catégorie de produits concernée :

=SOMME.SI.ENS(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 que les sous-totaux s’affichent correctement.

=SI(B5=B4;"";SOMME.SI.ENS($C$3:$C$11;$B$3:$B$11;B5))

La fonction SI compare la valeur du groupe de produits de chaque ligne de données avec la ligne de données qui la précède et, si elles ont la même valeur, elle produit une cellule vide («  »).

Si les valeurs des groupes de produits sont différentes, la somme est affichée. De cette façon, la somme de chaque groupe de produits n’est affichée 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, de sorte que la colonne Sous-total par groupe affiche chaque sous-total plusieurs fois. Pour mettre les données dans le format que nous voulons, nous pouvons sélectionner le tableau de données et cliquer sur « Trier de A à Z ».

Verrouillage des Références de Cellule

Pour faciliter la lecture de nos formules, nous avons présenté certaines d’entre elles sans les références de cellule verrouillées :

=SI(B3=B2;"";SOMME.SI.ENS(C3:C11;B3:B11;B3))

Mais ces formules ne fonctionneront pas correctement lorsqu’elles seront 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 :

=SI(B3=B2;"";SOMME.SI.ENS($C$3:$C$11;$B$3:$B$11;B3))

Pour en savoir plus, lisez notre article sur le verrouillage des références de cellule.

Utilisation des Tableaux Croisés Dynamiques pour Afficher les Sous-Totaux

Afin de supprimer l’obligation de trier préalablement les données par groupe de produits, nous pouvons utiliser la puissance des tableaux croisés dynamiques pour résumer les données. 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 Groupe dans Google Sheets

Ces formules fonctionnent de la même manière dans Google Sheets et dans Excel. Toutefois, la fonction UNIQUE est sensible à la casse dans Google Sheets.