La fonction SOUS-TOTAL dans Google Sheets est principalement utilisée pour travailler avec des données filtrées et regroupées. Elle remplace les fonctions MOYENNE, NB, NBVAL, MAX, MIN, PRODUIT, ECARTYPE, ECARTYPEP, SOMME, VARP et VAR lorsque vous souhaitez inclure uniquement les lignes visibles dans les agrégations. Ces onze fonctions d’agrégation ne peuvent pas distinguer les lignes visibles des lignes masquées, mais SOUS-TOTAL le peut.
Comment une seule fonction peut-elle remplacer 11 autres fonctions ? En utilisant des codes de fonction.
Puisque SOUS-TOTAL peut gérer 11 fonctions d’agrégation, vous pouvez l’utiliser dans des tableaux de bord dynamiques pour passer d’une somme à une moyenne ou à d’autres agrégations à l’aide d’une liste déroulante.
La fonction SOUS-TOTAL dans Google Sheets possède une autre fonctionnalité qui peut être très utile si vous manipulez un volume important de données. Supposons que vous ayez une colonne avec plusieurs lignes de données et plusieurs totaux de groupe utilisant la fonction SOMME. Ajouter un total général à la dernière ligne de cette colonne peut prendre du temps car vous devez vous rendre à chaque total partiel et les additionner à l’aide de l’opérateur +. Cependant, SOUS-TOTAL rend cette tâche simple. Il s’agit d’une fonctionnalité que j’ai beaucoup utilisée.
Une autre caractéristique de SOUS-TOTAL est que nous pouvons l’utiliser avec des fonctions LAMBDA pour créer une colonne d’aide virtuelle, que nous pouvons ensuite utiliser dans d’autres fonctions comme NB.SI et RECHERCHE pour les faire fonctionner avec les lignes visibles.
Les quatre objectifs de la fonction SOUS-TOTAL en un coup d’œil :
- Calculer les sous-totaux et les totaux généraux dans une liste.
- Travailler avec des données filtrées et regroupées.
- Passer d’une fonction à une autre dans des tableaux de bord dynamiques.
- Créer une colonne d’aide virtuelle pour traiter les lignes filtrées dans d’autres fonctions à l’aide de fonctions LAMBDA.
Syntaxe et arguments
Syntaxe de la fonction SOUS-TOTAL dans Google Sheets:
SOUS-TOTAL(code_fonction, plage1, [plage2, …])
Arguments :
- code_fonction : Un code numérique qui spécifie le type de calcul que vous souhaitez effectuer.
- plage1 : La première plage sur laquelle calculer un sous-total.
- plage2, … : (facultatif) Plages supplémentaires sur lesquelles calculer les sous-totaux.
Comment utiliser la fonction SOUS-TOTAL pour calculer des sous-totaux et un total général dans une colonne
Nous insérons généralement des sous-totaux lorsque nous avons plusieurs catégories triées dans un tableau. Par exemple, supposons que vous ayez un relevé de facturation trié par noms de clients dans la colonne B. Il y a trois clients nommés A, B et C. Pour calculer le montant total de chaque facture du client dans la colonne C, vous pouvez utiliser la fonction SOUS-TOTAL au lieu de SOMME.
=SOUSTOTAL(9,C2:C5) // montant total de la facture pour le client A
=SOUSTOTAL(9,C8:C12) // montant total de la facture pour le client B
=SOUSTOTAL(9,C15:C19) // montant total de la facture pour le client C
L’avantage d’utiliser la fonction SOUS-TOTAL au lieu de SOMME dans cet exemple est que la fonction SOUS-TOTAL exclura les résultats des formules SOUS-TOTAL en C6, C14 et C21 lors du calcul du total général (total des clients A, B et C) en C23.
=SOUSTOTAL(9,C2:C21)
Si vous utilisez la fonction SOMME, vous devrez utiliser l’une des deux fonctions suivantes pour calculer le total général, ce qui peut être source d’erreurs et fastidieux si vous avez un volume important de données :
=SOMME(C6,C14,C21)
=C6+C14+C21
Fonction SOUS-TOTAL dans Google Sheets pour calculer des sous-totaux pour les lignes visibles
Dans ce test, nous utiliserons les filtres, les tranches, le clic droit masquer et le regroupement pour voir comment la fonction SOUS-TOTAL fonctionne avec les données visibles.
Nous utiliserons les codes de fonction 2 et 102 pour le test. Ce sont les numéros de fonction représentant la fonction NB.
Dans la cellule C6, insérez la formule suivante :
=SOUSTOTAL(2,B2:B5)
Dans la cellule C7, insérez la formule suivante :
=SOUSTOTAL(102,B2:B5)
Et insérez la formule NB suivante dans la cellule C8 :
=NB(B2:B5)
Toutes ces formules renverront 4 si toutes les cellules de la plage B2:B5 ne sont pas vides et non vides.
Maintenant, voyons comment ces formules réagissent aux lignes visibles.
- Test « Créer un filtre »
Sélectionnez la plage A1:A5, puis allez dans Données > Filtre. Cela placera un filtre déroulant dans la cellule A1.
Cliquez sur l’icône du menu déroulant du filtre dans la cellule A1, décochez « C » et cliquez sur OK.
Les fonctions SOUS-TOTAL dans les cellules C6 et C7 renverront 3, tandis que la fonction NB dans la cellule C8 renverra toujours 4.
- Test « Ajouter une tranche »
Lors de l’utilisation des tranches, les fonctions SOUS-TOTAL et NB fonctionneront de manière similaire au test « Créer un filtre » ci-dessus.
Pour créer une tranche, sélectionnez la plage A2:B5, puis allez dans « Données > Ajouter une tranche ».
Dans la boîte de dialogue de la tranche dans le volet latéral, sous « Colonne », sélectionnez « Équipe ».
Ensuite, cliquez sur le menu déroulant du filtre sur la tranche, décochez « C » et cliquez sur OK.
Les fonctions SOUS-TOTAL dans les cellules C6 et C7 renverront 3, tandis que la fonction NB dans la cellule C8 renverra toujours 4.
- Test « Clic droit et masquer la ligne »
Ici, vous verrez la différence entre le code de fonction 2 et le code de fonction 102.
Cliquez avec le bouton droit de la souris sur la ligne 4 et cliquez sur « Masquer la ligne ».
La formule SOUS-TOTAL dans la cellule C6, qui utilise le numéro de fonction 2, et la formule NB dans la cellule C8 n’auront aucun effet, tandis que la formule SOUS-TOTAL dans la cellule C7, qui utilise le code de fonction 102, ne comptera que les lignes visibles.
- Test « Regrouper les lignes »
Les valeurs dans les lignes regroupées sont incluses ou exclues dans la fonction SOUS-TOTAL de la même manière que les valeurs dans les lignes masquées par un clic droit. Les codes de fonction 1 à 11 les incluront dans le calcul, tandis que les codes de fonction 101 à 111 les excluront.
Pour tester cela, sélectionnez les numéros de ligne 2 et 3 pour les regrouper. Pour ce faire, cliquez sur la ligne 2, maintenez la touche Maj enfoncée et cliquez sur la ligne 3. Puis, cliquez sur Affichage > Groupes > Groupes de lignes 2 – 3. Cliquez sur le bouton moins pour réduire le groupe.
La formule =SOUS-TOTAL(102, B2:B5) renverra le nombre de lignes visibles, tandis que la formule =SOUS-TOTAL(2, B2:B5) renverra le nombre de toutes les lignes.
La fonction SOUS-TOTAL dans Google Sheets pour passer d’une fonction à une autre dans des tableaux de bord dynamiques
Avant d’aborder la partie centrale de ce sujet, créons d’abord un menu déroulant de validation des données dans la cellule E2.
Tout d’abord, entrez tous les noms de fonctions prises en charge par SOUS-TOTAL dans la plage I1:I11 afin que nous puissions rapidement créer un menu déroulant.
Sélectionnez la cellule E2, puis allez dans Insertion > Validation des données. Dans la boîte de dialogue de validation des données, sous Critères, sélectionnez « Liste à partir d’une plage ». Dans le champ suivant, sélectionnez la plage de données I1:I11, puis cliquez sur OK.
Maintenant, passons aux données d’exemple pour le test.
Nous avons les dates des procès-verbaux de réunion dans la colonne A et le nombre de participants dans la colonne B.
Pour obtenir le total, la moyenne, le minimum, le maximum et le compte des participants sans utiliser la fonction SOUS-TOTAL dans Google Sheets, nous aurions besoin d’utiliser cinq formules :
=SOMME(B2:B)
=MOYENNE(B2:B)
=MIN(B2:B)
=MAX(B2:B)
=NB(B2:B)
Comment passons-nous dynamiquement d’une fonction à une autre en utilisant la fonction SOUS-TOTAL dans Google Sheets ?
Nous avons un menu déroulant dans la cellule E2 qui contient les noms de toutes les fonctions que nous voulons utiliser.
Maintenant, utilisez la fonction SWITCH suivante pour renvoyer le code de fonction en fonction de la fonction sélectionnée dans la cellule E2 :
=SWITCH(E2, »MOYENNE », 1, « NB », 2, « NBVAL », 3, « MAX », 4, « MIN », 5, « PRODUIT », 6, « ECARTYPE », 7, « ECARTYPEP », 8, « SOMME », 9, « VAR », 10, »VARP »,11)
Maintenant, utilisez cette formule comme argument code_fonction dans la fonction SOUS-TOTAL et la plage1 est B2:B :
=SOUSTOTAL(SWITCH(E2, »MOYENNE », 1, « NB », 2, « NBVAL », 3, « MAX », 4, « MIN », 5, « PRODUIT », 6, « ECARTYPE », 7, « ECARTYPEP », 8, « SOMME », 9, « VAR », 10, »VARP »,11),B2:B)
Cela permettra de passer dynamiquement d’une fonction à une autre en fonction du nom de fonction sélectionné dans la cellule E2.
Formule matricielle SOUS-TOTAL dans Google Sheets
Avons-nous déjà besoin d’étendre la fonction SOUS-TOTAL ou toute autre fonction d’agrégation ? Oui. Par exemple, lorsque nous voulons sous-totaliser plusieurs colonnes, nous pouvons avoir besoin d’utiliser plusieurs formules SOUS-TOTAL.
Dans l’exemple suivant, j’ai copié la formule SOUS-TOTAL en B9 en C9, D9 et E9.
En utilisant la fonction BYCOL, nous pouvons étendre la formule SOUS-TOTAL dans la cellule B9 aux cellules C9, D9 et E9.
=BYCOL(B3:E8,LAMBDA(col,SOUSTOTAL(109,col)))
La formule ci-dessus utilise la fonction BYCOL pour appliquer la fonction SOUS-TOTAL à chaque colonne de la plage B3:E8.
La fonction BYCOL prend deux arguments :
- Le premier argument est la plage de cellules sur laquelle appliquer la fonction. Dans ce cas, la plage est B3:E8.
- Le deuxième argument est une fonction qui prend une colonne en entrée et renvoie une valeur. Dans ce cas, la fonction est LAMBDA(col,SOUSTOTAL(109,col)).
La fonction BYCOL appliquera la fonction SOUS-TOTAL à chaque colonne de la plage B3:E8 et retournera un tableau horizontal des résultats.
Nous pouvons utiliser cette fonctionnalité de la fonction SOUS-TOTAL pour créer une colonne d’aide virtuelle. Voici comment.
Colonne d’aide virtuelle utilisant la fonction SOUS-TOTAL dans Google Sheets
Je vais d’abord expliquer l’objectif de la création d’une colonne d’aide virtuelle en utilisant la fonction SOUS-TOTAL dans Google Sheets. Ensuite, nous en créerons une.
Dans l’exemple ci-dessus, comment obtenons-nous le décompte des valeurs dans Q1 qui sont supérieures à 400 ?
Vous utiliseriez la formule NB.SI suivante :
=NB.SI(B3:B8, »>400″)
Cependant, cette formule ne renverra pas le résultat correct si les lignes de la plage sont masquées.
Pour que la formule réagisse aux lignes masquées, nous pouvons utiliser une colonne d’aide SOUS-TOTAL. Nous devons étendre une formule SOUS-TOTAL vers le bas pour cela, pas vers le bas, nous utiliserons donc la fonction lambda MAP.
Les données pour Q1 sont dans la plage B3:B8, donc la colonne d’aide virtuelle doit être basée sur celle-ci. Utilisez la fonction numéro 3 ou 103, qui est égale au NBVAL, en pointant vers la première cellule de cette plage, qui est B3.
=SOUSTOTAL(3,B3:B9)
Etendez cette formule vers le bas en utilisant la fonction lambda MAP comme ceci :
=MAP(B3:B8, LAMBDA(row,SOUSTOTAL(3,row)))
Cette formule renverra 1 dans les lignes visibles et 0 dans les lignes masquées ainsi que dans les lignes vides.
Maintenant, utilisons ceci comme colonne d’aide virtuelle dans la formule NB.SI ci-dessus :
=NB.SI(B3:B8, »>400″,MAP(B3:B8, LAMBDA(row,SOUSTOTAL(3,row))),1)
Cela comptera les valeurs dans les lignes visibles dans Q1 qui sont supérieures à 400.
Note : La fonction lambda BYROW peut être utilisée à la place de la fonction lambda MAP pour créer une colonne d’aide virtuelle SOUS-TOTAL.
Conclusion
Comme vous l’avez vu, la fonction SOUS-TOTAL est l’une des fonctions les plus polyvalentes de Google Sheets. Elle est conçue pour fonctionner avec des ensembles de données verticales. Vous pouvez donc l’utiliser pour insérer des sous-totaux dans des colonnes, exclure ou inclure des lignes masquées et calculer un total général.
Cependant, la capacité de la fonction SOUS-TOTAL est limitée lorsqu’il s’agit d’ensembles de données horizontaux. Vous pouvez toujours l’utiliser pour calculer des sous-totaux et un total général dans une ligne, mais elle ne répondra pas aux colonnes masquées.
