Les feuilles de calcul contenant beaucoup de données peuvent souvent sembler encombrées et difficiles à comprendre. Heureusement, Microsoft Excel fournit une puissante fonctionnalité de sous-total qui vous permet de résumer rapidement différents groupes de données et de créer un plan pour vos feuilles de calcul. Veuillez cliquer sur les liens suivants pour connaître les détails.
Qu’est-ce que le sous-total dans Excel ?
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 former le total général.
Dans Microsoft Excel, la fonctionnalité Sous-total n’est pas limitée au total de sous-ensembles de valeurs au sein d’un ensemble de données. Il vous permet de regrouper et de résumer vos données à l’aide de SOMME, NB, MOYENNE, MIN, MAX et d’autres fonctions. De plus, il crée une hiérarchie de groupes, connue sous le nom de plan, 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 :
Pour ajouter rapidement des sous-totaux dans Excel, procédez comme suit.
1 . Organiser les données sources
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.
Ainsi, avant d’ajouter des sous-totaux, assurez-vous de trier la colonne par laquelle vous souhaitez regrouper vos données. Le moyen le plus simple de le faire est de cliquer sur le bouton Filtrer dans l’onglet Données, puis de cliquer sur la flèche de filtre et de choisir de trier de A à Z ou de Z à A :
2 . Ajouter des sous-totaux
Sélectionnez n’importe quelle cellule de votre jeu de données, accédez à l’ onglet Données / groupe Plan , puis cliquez sur Sous-total.
Conseil. Si vous souhaitez ajouter des sous-totaux uniquement pour 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 – par quelle colonne regrouper, quelle fonction récapitulative utiliser et quelles colonnes sous-totaliser :
■ Dans la zone A chaque changement de, sélectionnez la colonne contenant les données que vous souhaitez regrouper.
■ Dans la zone Utiliser la fonction, sélectionnez l’une des fonctions suivantes :
– Somme – additionne les nombres.
– Count – compte les cellules non vides (cela insère 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.
– Compter les nombres – compter les cellules qui contiennent des nombres (cela insèrera des formules de sous-total avec la fonction NB ).
– ECARTYPE.STANDARD – calcule l’écart type d’une population sur la base d’un échantillon de nombres.
– StDevA – renvoie l’écart type basé sur une population entière de nombres.
– Var – estimer la variance d’une population sur la base d’un échantillon de nombres.
– Varp – estimez la variance d’une population sur la base d’une population entière de nombres.
■ Sous Ajouter un sous-total à , cochez la case de chaque colonne que vous souhaitez sous-totaliser.
Dans cet exemple, nous regroupons les données par la colonne Region et utilisons la fonction SOMME pour totaliser les nombres dans les colonnes Sales et Profit.
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 récapitulative au-dessus de la ligne de détails, décochez la case Récapitulatif sous les données . Pour afficher une ligne récapitulative sous la ligne des 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 actuels 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 les sous-totaux insérés dans votre feuille de calcul, ils seront recalculés automatiquement lorsque vous modifierez les données source.
Conseil. Si les sous – totaux et grand total ne sont pas recalculées, assurez – vous de définir votre classeur aux formules calculer automatiquement (Fichier / Options de / Formules/ Options de calcul / Calcul Cahier / Automatique).
Choses que vous devez savoir sur la fonctionnalité de sous-total d’Excel
Excel Subtotal est très puissant et polyvalent, et en même temps, c’est une fonctionnalité très spécifique en termes de calcul des données. Ci-dessous, vous trouverez les explications détaillées des spécificités de Subtotal.
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 des valeurs dans les lignes masquées manuellement, c’est-à-dire les lignes qui ont été masquées en utilisant la commande Masquer les lignes de l’ onglet Accueil / groupe Cellules / Format / Masquer et afficher , ou en cliquant avec le bouton droit 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 dans le premier argument (function_num) qui appartient à l’un des ensembles suivants :
■ 1 – 11 ignore les cellules filtrées, mais inclut les lignes masquées manuellement.
■ 101 – 111 ignorent toutes les lignes masquées (filtrées et masquées manuellement).
La fonction de sous-total Excel insère des formules avec le numéro de fonction 1-11.
Dans l’exemple ci-dessus, l’insertion de sous-totaux avec la fonction Somme crée cette formule : SUBTOTAL(9, C2:C7). Où 9 représente la fonction SOMME et C2:C5 est le premier groupe de cellules à sous-totaliser.
Si vous filtrez, par exemple, les citrons et les oranges, ils seront automatiquement supprimés des sous-totaux. Cependant, si vous masquez ces lignes manuellement, elles seront incluses dans les sous-totaux. L’image ci-dessous illustre la différence :
Pour exclure les lignes masquées manuellement 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 SUBTOTAL( 9 ,C2:C7) par SUBTOTAL( 109 ,C2:C7) :
Pour plus d’informations sur l’utilisation des formules de sous-total dans Excel, veuillez consulter le didacticiel sur la fonction SOUS – TOTAL.
2 . Les totaux généraux sont calculés à partir des données d’origine
La fonctionnalité de sous-total Excel 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 avec la fonction Moyenne calcule la moyenne générale en tant que moyenne arithmétique de toutes les valeurs d’origine dans les cellules C2:C19, 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 travaillez probablement avec un tableau Excel . Étant donné que la fonction Sous-total ne peut pas être utilisée avec les tableaux Excel, vous devez d’abord convertir votre tableau en une plage ordinaire. Veuillez consulter ce didacticiel pour connaître les étapes détaillées : Comment convertir un tableau Excel en plage.
Comment ajouter plusieurs sous-totaux dans Excel (sous-totaux imbriqués)
L’exemple précédent a montré comment insérer un niveau de sous-totaux. Et maintenant, allons plus loin et ajoutons des sous-totaux pour les groupes internes au sein des groupes externes correspondants. Plus précisément, nous allons d’abord regrouper nos exemples de données par région, puis les décomposer par élément.
1 . Trier les données sur plusieurs colonnes
Lors de l’insertion de sous-totaux imbriqués dans Excel, il est important de trier les données dans toutes les colonnes par lesquelles vous souhaitez regrouper vos sous-totaux. Pour ce faire, allez à données onglet Trier & filtrer groupe, cliquez sur le Tri bouton , et ajouter deux ou plusieurs niveaux de tri:
Par conséquent, 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 n’importe quelle cellule de 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 aurez des sous-totaux de ventes et de bénéfices par région :
3 . Insérer des niveaux imbriqués de sous-totaux
Une fois les sous-totaux externes en place, cliquez à nouveau sur Données / Sous- totaux pour ajouter un niveau de sous-total interne :
■ Dans la zone À chaque changement de, sélectionnez la deuxième colonne par laquelle vous souhaitez regrouper vos données.
■ Dans la zone Utiliser la fonction, sélectionnez la fonction récapitulative souhaitée.
■ Sous Ajouter un sous-total à , sélectionnez la ou les colonnes pour lesquelles vous souhaitez calculer des sous-totaux. Il peut s’agir des mêmes colonnes que dans les sous-totaux externes ou de colonnes différentes.
Enfin, décochez la case Remplacer les sous – totaux actuels. C’est le point clé qui empêche d’écraser 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 regroupera les données par la colonne Article et résumera les valeurs dans les colonnes Ventes et Profit :
En conséquence, Excel calculera les totaux pour chaque élément dans chaque région, comme indiqué dans la capture d’écran ci-dessous :
Par souci d’espace, le groupe Région Est est développé pour afficher les sous-totaux d’ articles imbriqués , et 3 autres groupes de régions sont réduits (la section suivante explique comment procéder : Afficher ou masquer les détails du sous-total ).
Ajouter différents sous-totaux pour la même colonne
Lorsque vous utilisez des sous-totaux dans Excel, vous n’êtes pas limité à insérer un seul sous-total par colonne. En fait, vous pouvez résumer les données dans la même colonne avec autant de fonctions différentes que vous le souhaitez.
Par exemple, dans notre exemple de tableau, en plus des totaux par région, nous pourrions afficher une moyenne pour les colonnes Ventes et Profit :
Pour obtenir un résultat similaire à ce que vous voyez dans la capture d’écran ci-dessus, effectuez les étapes décrites dans Comment ajouter plusieurs sous-totaux dans Excel . N’oubliez pas de décocher la case Remplacer les sous – totaux actuels chaque fois que vous ajoutez le deuxième et tous les niveaux de sous-totaux suivants.
Comment utiliser les sous-totaux dans Excel
Maintenant que vous savez comment faire des sous-totaux dans Excel pour obtenir instantanément un résumé pour différents groupes de données, les conseils suivants vous aideront à obtenir la fonctionnalité de sous-total Excel sous votre contrôle total.
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 de plan 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 nombre affiche à la fois les sous-totaux et les valeurs individuelles.
■ Les nombres intermédiaires montrent des 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 plusieurs nombres intermédiaires dans le plan.
Dans notre exemple de feuille de calcul, cliquez sur le numéro 2 pour afficher le premier regroupement par région :
Ou, cliquez sur le numéro 3 pour afficher les sous-totaux imbriqués par article :
Pour afficher ou masquer des lignes de données pour des sous – totaux individuels , utilisez les symboles et .
Ou cliquez sur les boutons Afficher les détails et Masquer les détails sous l’onglet Données, dans le groupe Plan.
Copier uniquement les lignes de sous-total
Comme vous le voyez, l’utilisation du sous-total dans Excel est facile… jusqu’à ce qu’il s’agisse de copier uniquement les sous-totaux ailleurs.
La façon la plus évidente qui me vient à l’esprit – afficher les sous-totaux souhaités, puis copier ces lignes vers un autre emplacement – ne fonctionnera pas ! Excel copiera et collera 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 :
- Affichez uniquement les lignes de sous-total que vous souhaitez copier à l’aide de nombres hiérarchiques ou de symboles plus et moins.
- Sélectionnez n’importe quelle cellule de sous-total, puis appuyez sur Ctrl + A pour sélectionner toutes les cellules.
- Une fois les sous-totaux sélectionnés, accédez à l’onglet Accueil / Groupe d’ édition , puis cliquez sur Rechercher et sélectionner / Sélectionner les cellules.
- Dans la boîte de dialogue Aller à spécial, sélectionnez Cellules visibles uniquement, puis cliquez sur OK.
- Dans votre feuille de calcul actuelle, appuyez sur Ctrl + C pour copier les cellules de sous-total sélectionnées.
- Ouvrez une autre feuille ou un autre classeur et appuyez sur Ctrl + V pour coller les sous-totaux.
En conséquence, vous n’avez que le résumé des données copié dans une autre feuille de calcul. Veuillez noter que cette méthode copie les valeurs du sous – total et non les formules :
Conseil. Vous pouvez utiliser la même astuce pour modifier la mise en forme de toutes les lignes de sous-total d’un seul coup.
Comment modifier les sous-totaux
Pour modifier rapidement les sous-totaux existants, procédez comme suit :
- Sélectionnez n’importe quelle cellule de sous-total.
- Accédez à l’ onglet Données et cliquez sur Sous-total .
- Dans la boîte de dialogue Sous – total , apportez les modifications souhaitées concernant la colonne clé, la fonction récapitulative et les valeurs à sous-totaliser.
- Assurez-vous que la case Remplacer les sous – totaux actuels est sélectionnée.
- Cliquez sur OK.
Noter. Si plusieurs sous-totaux ont été ajoutés pour le même ensemble de données, il n’est pas possible de les modifier. Le seul moyen consiste à supprimer tous les sous-totaux existants, puis à les insérer à nouveau.
Comment supprimer les sous-totaux dans Excel
Pour supprimer des sous-totaux, procédez comme suit :
- Sélectionnez n’importe quelle cellule dans la plage des sous-totaux.
- Accédez à l’onglet Données / 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 fonction Excel Sous-total qui insère automatiquement des sous-totaux, il existe un moyen “manuel” d’ajouter des sous-totaux dans Excel – en utilisant la fonction SOUS – TOTAL. Il offre encore plus de polyvalence, et je vais vous montrer quelques astuces utiles dans notre prochain tutoriel.