Tutoriel Excel : calculs des sous-totaux

Les feuilles de calcul contenant beaucoup de données peuvent souvent paraître encombrées et difficiles à comprendre. Heureusement, Microsoft Excel fournit une puissante fonctionnalité de sous-total qui vous permet de récapituler rapidement différents groupes de données et de créer un plan pour vos feuilles de calcul.

La fonctionnalité Sous-total d'Excel est un excellent moyen d'insérer automatiquement un sous-total Somme / Nombre / Moyenne / Max / Min à votre ensemble de données en appuyant sur un bouton. Les sous-totaux vous permettent de créer un résumé de la liste des groupes. Vous pouvez effectuer de nombreux calculs en fonction de sous-totaux.

Ce tutoriel explique comment utiliser la fonctionnalité de sous-total Excel pour additionner, compter ou moyenner automatiquement différents groupes de cellules. Vous apprendrez également à afficher ou à masquer les détails du sous-total, à ne copier que les lignes de sous-total et à supprimer les sous-totaux.

Presque tous les mois, je tombe dans des cas où je dois utiliser la fonctionnalité Sous-total. C'est une fonctionnalité facile à utiliser qui résume les données par groupes. Vous définissez le groupe et la fonction de synthèse ; Excel fait le reste. Dans ce tutoriel, je vais vous montrer comment utiliser la fonctionnalité sous-total et partager quelques astuces qui vous aideront à tirer le meilleur parti de cette fonctionnalité.

Nous allons travailler avec un simple jeu de données. J'ai utilisé Excel 2016 sur Windows 7, mais les instructions pour les trois versions de ruban sont pratiquement les mêmes. Dans Excel, vous trouverez des sous-totaux dans l’onglet Données. Bien que l'exemple de fichier soit simple, vous verrez rapidement à quel point cet outil peut être pratique avec beaucoup de données !

Table des matières

Qu'est-ce qu'un sous-total dans Excel ?

Les feuilles de calcul avec beaucoup de contenu peuvent parfois sembler écrasantes et même devenir difficiles à lire. Heureusement, Excel peut organiser les données en groupes, ce qui vous permet d'afficher et de masquer facilement différentes sections de votre feuille de calcul. Vous pouvez également récapituler différents groupes à l'aide de la commande Sous.total et créer un plan pour votre feuille de calcul. De manière générale, le sous-total est la somme d'un ensemble de nombres, qui est ensuite ajouté à un ou plusieurs autres ensembles de nombres pour obtenir le total général.

Dans Microsoft Excel, la fonctionnalité Sous-total ne se limite pas à la somme des sous-ensembles de valeurs d'un ensemble de données. Il vous permet de regrouper et de récapituler vos données à l’aide de SOMME, NBVAL, MOYENNE, MIN, MAX et d’autres fonctions. En outre, il crée une hiérarchie de groupes, appelée structure, qui vous permet d'afficher ou de masquer les détails de chaque sous-total ou d'afficher simplement un résumé des sous-totaux et des totaux généraux.

Par exemple, voici à quoi peuvent ressembler vos sous-totaux Excel :

Avant que de commencer :

Si vous compilez des données dans n'importe quel contexte, vous apprenez rapidement que la cohérence est la clé du succès. Toutes les fonctionnalités puissantes du monde ne vous aideront pas si vos données sont incohérentes. La fonctionnalité Sous-total d'Excel n'échappe pas à cette règle. La valeur de chaque groupe doit être identique pour tous les enregistrements de ce groupe. Par exemple, si les utilisateurs entrent dans Recherche et développement, R & D et Atteindre et développement, Excel renverra une ligne de résumé pour chacune de ces valeurs. Excel n'interprète pas les différentes valeurs comme identiques, même si elles le sont. Excel offre un certain nombre de fonctionnalités pour vous aider dans ce domaine ; vous pouvez utiliser la validation des données, les types de données et même des formulaires de saisie de données.

La fonctionnalité Sous-total nécessite que vos valeurs soient regroupées. Cela signifie que vous devez trier vos données en fonction des valeurs du groupe avant de mettre en œuvre la fonctionnalité Sous-total.

Comment insérer des sous-totaux dans Excel

Pour ajouter rapidement des sous-totaux dans Excel, procédez comme suit.

1. Organiser les données source

La fonctionnalité de sous-total Excel nécessite que les données source soient organisées dans un ordre approprié et ne doivent contenir aucune ligne vide. Par conséquent, avant d'ajouter des sous-totaux, veillez à trier la colonne avec laquelle vous souhaitez regrouper vos données. Pour ce faire, cliquez sur le bouton « Filtrer » de l'onglet « Données », puis sur la flèche du filtre et sélectionnez le type de tri « A à Z » ou « Z à A » :

Assurez-vous que chaque colonne d'une plage de données pour laquelle vous voulez calculer des sous-totaux a une étiquette dans la première ligne, contient des faits similaires dans chaque colonne et qu'elle n'inclut pas de lignes ni de colonnes vides.

2. Ajouter des sous-totaux

Sélectionnez une cellule dans votre jeu de données, accédez à l'onglet « Données » puis dans le Groupe de plan, puis cliquez sur « Sous-total ».

Remarque : Si vous souhaitez ajouter des sous-totaux uniquement à une partie de vos données, sélectionnez la plage souhaitée avant de cliquer sur le bouton Sous-total.

3. Définir les options de sous-total

Dans la boîte de dialogue Sous-total, spécifiez les trois éléments principaux : la colonne à regrouper, la fonction de résumé à utiliser et les colonnes à sous-totaliser :

Dans la zone « À chaque changement de », sélectionnez la colonne contenant les données à regrouper, dans cet exemple c’est la colonne « Région »

Dans la zone « Utiliser la fonction », sélectionnez l’une des fonctions suivantes :

• Somme : additionnez les nombres.
• Compter : compte les cellules non vides (ceci insérera des formules de sous-total avec la fonction NBVAL).
• Moyenne : calcule la moyenne des nombres.
• Max : renvoie la plus grande valeur.
• Min : renvoie la plus petite valeur.
• Produit : calcule le produit des cellules.
• Chiffres : compte les cellules qui contiennent des nombres (ceci insérera des formules de sous-total avec la fonction NBVAL).
• EcartType : calcule l'écart type d'une population sur la base d'un échantillon de nombres.
• EcartTypep : renvoie l’écart type basé sur une population entière de nombres.
• Var : estimer la variance d'une population à partir d'un échantillon de nombres.
•  Varp : estimer la variance d'une population sur la base d'une population entière de nombres.

Sous « Ajouter un sous-total à », cochez la case correspondant à chaque colonne à sous-totaliser.

Dans cet exemple, nous regroupons les données en fonction de la colonne « Région » et utilisons la fonction « SOMME » pour totaliser les nombres dans les colonnes « Ventes » et « Gain ».

De plus, vous pouvez sélectionner l’une des options suivantes :

• Pour insérer un saut de page automatique après chaque sous-total, cochez la case « Saut de page entre les groupes ».
• Pour afficher une ligne de résumé au-dessus de la ligne de détails, décochez la case « Synthèse sous les données ». Pour afficher une ligne de résumé sous la ligne de détails, cochez cette case (généralement sélectionnée par défaut).
• Pour écraser les sous-totaux existants, laissez la case « Remplacer les sous-totaux » cochée, sinon, décochez cette case.

Enfin, cliquez sur le bouton OK. Les sous-totaux apparaîtront sous chaque groupe de données et le total général sera ajouté à la fin du tableau.

Une fois que les sous-totaux sont insérés dans votre feuille de calcul, ils sont automatiquement recalculés lorsque vous modifiez les données source.

Pour voir les groupes par niveau, lorsque vous créez des sous-totaux, votre feuille de calcul est divisée en différents niveaux. Vous pouvez basculer entre ces niveaux pour contrôler rapidement la quantité d’informations affichée dans la feuille de calcul en cliquant sur l’image du bouton Niveaux du bouton pour les niveaux 1, 2, 3 situés à gauche de la feuille de calcul. Dans notre exemple, nous basculerons entre les trois niveaux de notre plan. Bien que cet exemple ne contienne que trois niveaux, Excel peut en gérer huit.

Remarque : Si les sous-totaux et le total général ne sont pas recalculés, veillez à configurer votre classeur pour qu'il calcule automatiquement les formules (Fichier > Options > Formules > Options de calcul > Calcul du classeur > Automatique).

3 choses à savoir sur la fonctionnalité de sous-total Excel

La fonctionnalité sous-total d’Excel est très puissante et polyvalente, tout en étant une fonctionnalité très spécifique en termes de calcul des données. Ci-dessous, vous trouverez les explications détaillées sur les spécificités de Sous-total.

1. Seules les lignes visibles sont sous-totalisées

Essentiellement, le Sous-total Excel calcule les valeurs dans les cellules visibles et ignore les lignes filtrées. Cependant, il inclut les valeurs dans les lignes masquées manuellement, c’est-à-dire les lignes qui ont été masquées à l’aide de la commande Masquer les lignes de l’onglet Accueil > groupe de cellules > Format > Masquer et afficher, ou en cliquant avec le bouton droit de la souris sur les lignes, puis en cliquant sur « Masquer ». Les quelques paragraphes suivants expliquent les détails techniques.

L'application de la fonctionnalité Sous-total dans Excel crée automatiquement des formules SOUS.TOTAL qui effectuent un type de calcul spécifique, tel que somme, nombre, moyenne, etc. La fonction est définie par le nombre figurant dans le premier argument (numéro_fonction) appartenant à l'un des ensembles suivants :

-          1 à 11 ignorent les cellules filtrées, mais incluent les lignes masquées manuellement.

-          101 - 111 ignorent toutes les lignes masquées (filtrées et masquées manuellement).

La fonctionnalité Excel Sous-total insère des formules avec le numéro de fonction 1-11.

Dans l'exemple ci-dessous, l'insertion de sous-totaux avec la fonction Somme crée la formule suivante : SOUS.TOTAL (9 ; C2: C5). Où 9 représente la fonction SOMME et C2: C5 est le premier groupe de cellules au sous-total.

Si vous filtrez, par exemple, les citrons et les oranges, ils seront automatiquement supprimés des sous-totaux. Toutefois, si vous masquez ces lignes manuellement, elles seront incluses dans les sous-totaux. L'image ci-dessous illustre la différence :

Pour exclure manuellement les lignes masquées afin que seules les cellules visibles soient calculées, modifiez la formule Sous-total en remplaçant le numéro de fonction 1-11 par le numéro correspondant 101-111.

Dans notre exemple, pour additionner uniquement les cellules visibles à l'exclusion des lignes masquées manuellement, remplacez SOUS.TOTAL (9 ; C2: C5) par SOUS.TOTAL (109 ;C2: C5):

Vous trouverez plus d'informations sur l'utilisation des formules de sous-total dans Excel, dans la partie de la fonction SOUS.TOTAL.

2. Les totaux généraux sont calculés à partir des données d'origine

La fonctionnalité Excel Sous-total calcule les totaux généraux à partir des données d'origine et non à partir des valeurs de sous-total.

Par exemple, l'insertion de sous-totaux à l'aide de la fonction Moyenne calcule la moyenne générale en tant que moyenne arithmétique de toutes les valeurs d'origine des cellules C2: C17, en négligeant les valeurs des lignes de sous-total. Il suffit de comparer les captures d'écran suivantes pour voir la différence :

3. Les sous-totaux ne sont pas disponibles dans les tableaux Excel

Si le bouton Sous-total est grisé sur votre ruban, vous utilisez probablement un tableau Excel. Étant donné que la fonctionnalité Sous-total ne peut pas être utilisée avec des tableaux Excel, vous devez d'abord convertir votre tableau en une plage ordinaire. Pour convertir votre tableau Excel en plage sélectionnez votre tableau puis allez dans l’onglet « Création » et dans le groupe « Outils » cliquez sur « Convertir en plage ».

Remarque : Les sous-totaux sont calculés à l'aide d'une fonction récapitulative, telle que Somme ou Moyenne, à l'aide de la fonction SOUS.TOTAL. Vous pouvez afficher plusieurs types de fonction de résumé pour chaque colonne.

Les totaux généraux sont dérivés des données détaillées et non des valeurs des sous-totaux. Par exemple, si vous utilisez la fonction Récapitulative moyenne, la ligne de total général affiche une moyenne de toutes les lignes de détail de la liste, et non une moyenne des valeurs des lignes de sous-total.

Comment ajouter plusieurs sous-totaux dans Excel (sous-totaux imbriqués)

L'exemple précédent montre comment insérer un niveau de sous-total. Et maintenant, allons plus loin et ajoutons des sous-totaux pour les groupes internes au sein des groupes externes correspondants. Plus spécifiquement, nous allons d'abord regrouper nos données d'échantillon par région, puis les ventiler par objet.

1. Trier les données en plusieurs colonnes

Lors de l'insertion de sous-totaux imbriqués dans Excel, il est important de trier les données de toutes les colonnes avec lesquelles vous souhaitez regrouper vos sous-totaux. Pour ce faire, accédez à l'onglet « Données » puis dans le Groupe « Trier et filtrer », cliquez sur le bouton « Trier » et ajoutez deux niveaux de tri ou plus. En conséquence, les valeurs des deux premières colonnes sont triées par ordre alphabétique :

2. Insérer le premier niveau de sous-totaux

Sélectionnez une cellule dans votre liste de données et ajoutez le premier niveau, externe, des sous-totaux, comme illustré dans l'exemple précédent. En conséquence, vous obtiendrez des sous-totaux Ventes et Bénéfice par région :

3. Insérer des niveaux imbriqués de sous-totaux

Avec les sous-totaux externes en place, cliquez à nouveau sur le bouton « Sous-totaux » dans l’onglet « Donnée » pour ajouter un niveau de sous-total interne :

• Dans la zone « À chaque modification », sélectionnez la deuxième colonne avec laquelle vous souhaitez regrouper vos données.
• Dans la zone « Utiliser la fonction », sélectionnez la fonction de résumé souhaitée.
• Sous « Ajouter un sous-total à », sélectionnez la ou les colonnes pour lesquelles vous souhaitez calculer des sous-totaux. Cela peut être la même colonne (s) que dans les sous-totaux externes ou différentes.
• Enfin, décochez la case « Remplacer les sous-totaux existants ». C'est le point clé qui empêche de remplacer le niveau externe des sous-totaux.

Répétez cette étape pour ajouter d'autres sous-totaux imbriqués, si nécessaire.

Dans cet exemple, le niveau de sous-total interne regroupe les données dans la colonne « Objet » et récapitule les valeurs des colonnes « Ventes » et « Gains » :

Pour obtenir un résultat similaire à ce que vous voyez dans la capture d'écran ci-dessous, suivez les étapes décrites ci-dessus. N'oubliez pas de désactiver la case à cocher Remplacer les sous-totaux actuels chaque fois que vous ajoutez le deuxième niveau et tous les niveaux suivants de sous-totaux.

Comment utiliser les sous-totaux dans Excel

Maintenant que vous savez effectuer des sous-totaux dans Excel pour obtenir instantanément un résumé des différents groupes de données, les conseils suivants vous aideront à maîtriser la fonctionnalité de sous-total d'Excel.

Afficher ou masquer les détails du sous-total

Pour afficher le résumé des données, c'est-à-dire uniquement les sous-totaux et les totaux généraux, cliquez sur l'un des symboles hiérarchiques. Les symboles hiérarchiques qui apparaissent dans le coin supérieur gauche de votre feuille de calcul :

Le numéro 1 affiche uniquement les totaux généraux.

Le dernier chiffre affiche les sous-totaux et les valeurs individuelles.

Les nombres intermédiaires indiquent les groupements. Selon le nombre de sous-totaux que vous avez insérés dans votre feuille de calcul, il peut y avoir un, deux, trois ou plus nombres entre les deux dans le contour.

Dans notre exemple de feuille de calcul, cliquez sur le numéro 2 pour afficher le premier groupe par région :

Ou cliquez sur le numéro 3 pour afficher les sous-totaux imbriqués par élément :

Pour afficher ou masquer les lignes de données de sous-totaux individuels, utilisez les symboles « + » et « - ».

Vous pouvez également cliquer sur les boutons « Afficher les détails » et « Masquer les détails » de l’onglet « Données » du groupe « Plan ».

Copier uniquement les lignes de sous-total

Comme vous le voyez, il est facile d’utiliser Sous-total dans Excel… jusqu’à ce qu’il s’agisse de ne copier que les sous-totaux.

La façon la plus évidente qui vous vient à l’esprit : afficher les sous-totaux souhaités, puis copier ces lignes à un autre emplacement, ne fonctionnera pas ! Excel va copier et coller toutes les lignes, pas seulement les lignes visibles incluses dans la sélection.

Pour copier uniquement les lignes visibles contenant des sous-totaux, procédez comme suit :

1-      Affichez uniquement les lignes de sous-total que vous souhaitez copier en utilisant des numéros de contour ou des symboles plus et moins.

2-      Sélectionnez une cellule de sous-total, puis appuyez sur « Ctrl + A » pour sélectionner toutes les cellules.

3-      Avec les sous-totaux sélectionnés, allez à l'onglet « Accueil » puis dans le Groupe « Edition », puis cliquez sur « Rechercher et sélectionner » et choisissez « Sélectionner les cellules »

4-      Dans la boîte de dialogue « Sélectionner les cellules » pour « Sélectionner », choisissez « Cellules visibles seulement » et cliquez sur OK.

Remarque : Au lieu d'utiliser la fonctionnalité « Sélectionner les cellules », vous pouvez appuyer sur « Alt + ; » pour sélectionner uniquement les cellules visibles.

5-      Dans votre feuille de calcul actuelle, appuyez sur « Ctrl + C » pour copier les cellules de sous-total sélectionnées.

6-      Ouvrez une autre feuille ou un autre classeur et appuyez sur « Ctrl + V » pour coller les sous-totaux.

Terminé ! En conséquence, vous ne faites que copier le résumé des données dans une autre feuille de calcul. Veuillez noter que cette méthode copie les valeurs de sous-total et non les formules.

Remarque : Vous pouvez utiliser la même astuce pour modifier le formatage de toutes les lignes de sous-total en un seul coup.

Comment changer les sous-totaux

Pour modifier rapidement les sous-totaux existants, procédez comme suit :

-          Sélectionnez une cellule de sous-total.

-          Accédez à l'onglet « Données », puis cliquez sur « Sous-total ».

-          Dans la boîte de dialogue « Sous-total », apportez les modifications souhaitées concernant la colonne clé, la fonction de résumé et les valeurs à sous-totaliser.

• Assurez-vous que la case « Remplacer les sous-totaux existants » est sélectionnée.
• Cliquez sur OK.

Remarque : Si plusieurs sous-totaux ont été ajoutés pour le même jeu de données, il est impossible de les modifier. Le seul moyen consiste à supprimer tous les sous-totaux existants, puis à les réinsérer.

Comment supprimer les sous-totaux dans Excel

Pour supprimer les sous-totaux, procédez comme suit :

• Sélectionnez une cellule dans la plage des sous-totaux.
• Accédez à l'onglet « Données » puis dans le groupe « Plan », puis cliquez sur « Sous-total. »
• Dans la boîte de dialogue « Sous-total », cliquez sur le bouton « Supprimer tout ».

Cela dissociera vos données et supprimera tous les sous-totaux existants.

Outre la fonctionnalité de sous-total Excel qui insère automatiquement les sous-totaux, il existe une méthode "manuelle" pour ajouter des sous-totaux dans Excel - à l'aide de la fonction SOUS.TOTAL. Il offre encore plus de polyvalence, et je vais vous montrer quelques astuces utiles dans la partie suivante.

Format des sous-totaux

La mise en forme par défaut n’est pas un problème, mais vous pouvez la modifier pour distinguer les lignes de sous-total des données. Heureusement, il n'est pas nécessaire de formater chaque ligne individuellement. Il y a un moyen plus rapide :

• Cliquez sur 3 dans le volet Sous-total (à gauche) pour masquer les enregistrements de détail.
• Appuyez sur « Alt + ; » pour sélectionner uniquement les cellules visibles.
• Appliquer le format. Vous pouvez choisir une police bleue (légère). (Je ne recommande pas un bleu aussi clair; il contraste simplement avec le noir.)
• Appuyez sur 3 dans le volet latéral pour examiner tous les enregistrements. Vous allez voir que seules les lignes de sous-total sont en bleu.

Vous avez mis en forme toutes les cellules visibles, ce qui signifie que les données existantes et les nouvelles données de toutes les cellules visibles seront en bleu. Si votre feuille contient autre chose que votre jeu de données, ce raccourci ne fonctionnera probablement pas pour vous.

Fonction SOUS.TOTAL

Cette partie explique les spécificités de la fonction SOUS.TOTAL dans Excel et montre comment utiliser des formules de sous-total pour résumer des données dans des cellules visibles.

Vous pouvez penser à une somme de valeurs sélectionnées. Mais la fonction sous.total est capable de beaucoup plus. En plus de sa capacité à renvoyer une somme, il peut également donner à l'utilisateur un résultat moyen, nombre, maximum et plusieurs autres résultats. Dans cette partie, nous allons examiner quelques exemples pour montrer ce que fait SOUS.TOTAL et comment l'utiliser.

Dans les parties précédentes, nous avons présenté un moyen automatique d'insérer des sous-totaux dans Excel à l'aide de la fonctionnalité Sous-total. Aujourd'hui, vous apprendrez à écrire vous-même des formules de sous-total et quels avantages vous en retirent.

SOUS.TOTAL Excel - syntaxe et utilisations

Microsoft Excel définit SOUS.TOTAL comme la fonction qui renvoie un sous-total dans une liste ou une base de données. Dans ce contexte, le "sous-total" ne consiste pas simplement à totaliser des nombres dans une plage définie de cellules. Contrairement aux autres fonctions Excel conçues pour ne faire qu'une chose en particulier, SOUS.TOTAL est incroyablement polyvalente : elle peut effectuer différentes opérations arithmétiques et logiques telles que le comptage de cellules, le calcul de la moyenne, la recherche de la valeur minimale ou maximale, etc.

La fonction SOUS.TOTAL est disponible dans toutes les versions d'Excel 2016, Excel 2013, Excel 2010, Excel 2007 et versions antérieures.

La syntaxe de la fonction Excel SOUS.TOTAL est la suivante :

SOUS.TOTAL (num_fonction ; ref1 ; [ref2] ;…)

Où :

Num_fonction : nombre indiquant la fonction à utiliser pour le sous-total.

Ref1, Ref2,… : une ou plusieurs cellules ou plages jusqu'au sous-total. Le premier argument de référence est requis, les autres (jusqu'à 254) sont facultatifs.

L'argument num_fonction peut appartenir à l'un des ensembles suivants :

• 1 à 11 ignorent les cellules filtrées, mais incluent les lignes masquées manuellement.
• 101 - 111 ignorer toutes les cellules cachées - filtrées et masquées manuellement.

En fait, il n'est pas nécessaire de mémoriser tous les numéros de fonction. Dès que vous commencez à taper une formule de sous-total dans une cellule ou dans la barre de formule, Microsoft Excel affiche une liste des numéros de fonction disponibles pour vous.

Par exemple, voici comment vous pouvez créer une formule de sous-total 9 pour résumer les valeurs des cellules C2 à C8 :

Pour ajouter un numéro de fonction à la formule, double-cliquez dessus, tapez un point-virgule, spécifiez une plage, tapez la parenthèse fermante et appuyez sur « Entrée ». La formule complétée ressemblera à ceci :

= SOUS.TOTAL (9 ; C2: C8)

De la même manière, vous pouvez écrire une formule de sous-total 1 pour obtenir une moyenne, de sous-total 2 pour compter les cellules avec des nombres, de sous-total 3 pour compter les non-blancs, etc. La capture d'écran suivante montre quelques autres formules en action :

Remarque : Lorsque vous utilisez une formule de SOUS.TOTAL avec une fonction de résumé telle que SOMME ou MOYENNE, elle calcule uniquement les cellules avec des nombres ignorant les blancs et les cellules contenant des valeurs non numériques.

Maintenant que vous savez comment créer une formule de sous-total dans Excel, la question principale est la suivante : pourquoi voudrait-on prendre la peine de l’apprendre ? Pourquoi ne pas simplement utiliser une fonction régulière comme SOMME, NBVAL, MAX, etc. ? Vous trouverez la réponse ci-dessous.

3 principales raisons d'utiliser SOUS.TOTAL dans Excel

Utilisez la fonction SOUS.TOTAL dans Excel au lieu de SOMME, NBAL, MAX, etc. pour ignorer les lignes masquées par un filtre ou les lignes masquées manuellement. Par rapport aux fonctions Excel traditionnelles, SOUS.TOTAL vous offre les avantages importants suivants.

1. Calculer les valeurs dans les lignes filtrées

La fonction SOUS.TOTAL d'Excel ignorant les valeurs des lignes filtrées, vous pouvez l'utiliser pour créer un résumé de données dynamique dans lequel les valeurs de sous-total sont recalculées automatiquement en fonction du filtre.

Par exemple, si nous filtrons le tableau pour afficher les ventes uniquement pour la région « Este », la formule Sous-total s'ajustera automatiquement afin que toutes les autres régions soient supprimées du total :

Remarque : Étant donné que les deux ensembles de numéros de fonction (1-11 et 101-111) ignorent les cellules filtrées, vous pouvez utiliser la formule SOUS.TOTAL 9 ou 109 sous-total 109 dans ce cas.

2. Calculer uniquement les cellules visibles

Comme vous vous en souvenez, les formules de sous-total avec les numéros de fonction 101 à 111 ignorent toutes les cellules cachées - filtrées et masquées manuellement. Ainsi, lorsque vous utilisez la fonction de masquage d'Excel pour supprimer des données non pertinentes de la vue, utilisez le numéro de fonction 101-111 pour exclure les valeurs des lignes masquées des sous-totaux.

L'exemple suivant vous aidera à mieux comprendre comment cela fonctionne : Sous-total 9 vs. Sous-total 109.

3. Ignorer les valeurs dans les formules de sous-total imbriquées

Si la plage fournie à votre formule de sous-total Excel contient une autre formule de sous-total, ces sous-totaux imbriqués seront ignorés et les mêmes nombres ne seront pas calculés deux fois. Génial, n'est-ce pas ?

Dans la capture d'écran ci-dessous, la formule Moyenne générale SOUS.TOTAL(1 ;C2: C8) ignore les résultats des formules de sous-total des cellules C6 et C10, comme si vous utilisiez une formule Moyenne avec 2 plages distinctes MOYENNE (C2: C5 ;C7: C8).

Utilisation de SOUS.TOTAL dans Excel - exemples de formules

Lorsque vous rencontrez SOUS.TOTAL pour la première fois, cela peut sembler complexe, difficile et même inutile. Mais une fois que vous aurez abordé le problème, vous vous rendrez compte que ce n’est pas si difficile à maîtriser. Les exemples suivants vous montreront quelques conseils utiles et des idées inspirantes.

Exemple 1. SOUS.TOTAL 9 vs SOUS.TOTAL 109

Comme vous le savez déjà, Excel SOUS.TOTAL accepte 2 ensembles de numéros de fonctions : 1-11 et 101-111. Les deux ensembles ignorent les lignes filtrées, mais les numéros 1 à 11 incluent des lignes masquées manuellement, tandis que 101-111 les exclut. Pour mieux comprendre la différence, considérons l'exemple suivant.

Pour additionner les lignes filtrées, vous pouvez utiliser la formule Sous-total 9 ou Sous-total 109, comme indiqué dans la capture d'écran ci-dessous :

Mais si vous avez masqué manuellement des éléments non pertinents à l'aide de la commande Masquer les rangées de l'onglet Accueil > groupe de cellules > Format > Masquer et afficher, ou en cliquant avec le bouton droit de la souris sur les lignes, puis en cliquant sur Masquer, vous voulez maintenant que les valeurs soient totalisées uniquement Le sous-total 109 est la seule option :

Les autres numéros de fonction fonctionnent de la même manière. Par exemple, pour compter les cellules filtrées non vierges, une formule de sous-total 3 ou de sous-total 103 fera l'affaire. Mais seul le sous-total 103 peut correctement compter les non-blancs visibles s'il y a des lignes cachées dans la plage :

Remarque : La fonction SOUS.TOTAL Excel avec numéro_fonction 101-111 néglige les valeurs dans les lignes masquées, mais pas dans les colonnes masquées. Par exemple, si vous utilisez une formule telle que SOUS.TOTAL (109 ;A1: E1) pour additionner des nombres dans une plage horizontale, masquer une colonne n'affectera pas le sous-total.

Exemple 2. SI + SOUS.TOTAL pour la synthèse dynamique des données

Si vous créez un rapport de synthèse ou un tableau de bord dans lequel vous devez afficher diverses données récapitulatives sans disposer d'espace suffisant pour tout, l'approche suivante peut constituer une solution :

Dans une cellule, créez une liste déroulante contenant les noms de fonctions tels que Total, Max, Min, etc.

Dans une cellule à côté du menu déroulant, entrez une formule SI imbriquée avec les fonctions de sous-total incorporées correspondant aux noms de fonction figurant dans la liste déroulante.

Par exemple, en supposant que les valeurs du sous-total sont dans les cellules C2: C16 et que la liste déroulante dans A17 contient les éléments Total, Moyenne, Max et Min, la formule "dynamique" de sous-total est la suivante :

=SI(A9="Total";SOUS.TOTAL(9;C2:C8);SI(A9="Moyenne";SOUS.TOTAL(1;C2:C8);SI(A9="min";SOUS.TOTAL(5;C2:C8);SI(A9="max";SOUS.TOTAL(4;C2:C8);""))))

Et maintenant, en fonction de la fonction que votre utilisateur sélectionne dans la liste déroulante, la fonction Sous-total correspondante calculera les valeurs dans des lignes filtrées :

Remarque : Si tout à coup la liste déroulante et la cellule de formule disparaissent de votre feuille de calcul, veillez à les sélectionner dans la liste de filtres.

Sous.total Excel ne fonctionne pas - erreurs courantes

Si votre formule de sous-total renvoie une erreur, cela est probablement dû à l'une des raisons suivantes :

#VALEUR! - l'argument numéro_fonction est différent d'un entier compris entre 1 et 11 et entre 101 et 111; ou l'un des arguments de référence contient une référence 3D.

# DIV / 0! - survient si une fonction récapitulative spécifiée doit effectuer une division par zéro (par exemple, calculer un écart moyen ou standard pour une plage de cellules ne contenant pas une seule valeur numérique).

#NOM? - le nom de la fonction de sous.total est mal orthographié - l'erreur la plus facile à corriger :)

Remarque : Si vous ne vous sentez pas encore à l'aise avec la fonction SOUS.TOTAL, vous pouvez utiliser la fonction SOUS.TOTALE intégrée et faire en sorte que les formules soient insérées automatiquement.

Pour aller plus loin : Comment contrôler les sous-totaux dans un tableau croisé dynamique

Le comportement par défaut des tableaux croisés dynamiques consiste à automatiser automatiquement les champs de sous-total lorsqu'ils sont ajoutés aux zones d'étiquette de ligne ou de colonne. Cependant, vous pouvez activer et désactiver les sous-totaux champ par champ.

Lorsque vous commencez à ajouter des champs de libellé de ligne ou de colonne à un tableau croisé dynamique, vous ne verrez aucun sous-total. Par exemple, dans un tableau croisé dynamique de vente de produits par catégorie et région, si on ajoute « Catégorie » en tant qu’étiquette de ligne, Région en tant que champ d’étiquette de colonne et Total Ventes en tant que valeur on va voir des grands totaux, mais pas de sous-totaux.

Toutefois, si nous ajoutons un deuxième champ à la zone Etiquettes de ligne, Produit, un sous-total pour chaque catégorie apparaît sous forme de ligne dans la table. En règle générale, le champ le plus à l'intérieur de la zone d'étiquettes de ligne, dans ce cas, Produit, ne sera pas sous-total, alors que les champs extérieurs, Catégorie dans ce cas, seront sous-totaux.

Il en va de même pour les étiquettes de colonne. Si nous ajoutons une ville sous une région, nous verrons un sous-total pour chaque région apparaître sous forme de nouvelles colonnes dans le tableau croisé dynamique.

Vous pouvez contrôler les sous-totaux qui apparaissent dans le tableau croisé dynamique globalement ou champ par champ. Pour utiliser tous les sous-totaux en même temps, accédez à l'onglet « Création » du ruban Outils de pivot, puis utilisez le menu Sous-total. Vous pouvez désactiver tous les sous-totaux en même temps. Ou, vous pouvez réactiver les sous-totaux en haut ...ou le bas du champ de regroupement.

Les deux commandes activent tous les sous-totaux, mais chacune place les étiquettes de ligne à un emplacement différent. Les étiquettes de colonne ne sont pas affectées. Quelle que soit l'option choisie, vous pouvez ajouter des lignes vides après chaque élément de regroupement à l'aide du menu Lignes vierges.

Vous pouvez également contrôler les sous-totaux individuellement à l'aide de Paramètres de champ. Par exemple, pour désactiver les sous-totaux pour la région, accédez à Paramètres de champ et définissez Sous-totaux de Automatique à Aucun.

Vous pouvez faire la même chose avec le champ Catégorie. Accédez aux paramètres du champ et modifiez les sous-totaux de Automatique à Aucun.

Notez que le menu Sous-totaux du ruban contrôle les mêmes paramètres. Si nous utilisons ce menu pour activer les sous-totaux, puis cochez Paramètres de champ pour Catégorie, les sous-totaux sont à nouveau définis sur Automatique.