Comment calculer uniquement la somme des cellules filtrées ou visibles dans Excel?

L’article d’aujourd’hui de cette formation Excel va vous montrer comment utiliser la fonction SOMME pour qu’elle prenne en compte les cellules filtrées lors du filtrage des données d’un tableau. Vous allez aussi apprendre à créer votre propre fonction somme en utilisant du code VBA pour accomplir la même tâche.
Comment calculer uniquement la somme des cellules filtrées ou visibles dans Excel


Lorsque vous utilisez la fonction SOMME dans votre tableau de données pour calculer le total des nombres existant dans une colonne, et que vous filtrez vos données, vous remarquez que la fonction SOMME affiche toujours le premier résultat sans ignorer les cellules masquées.

La fonction Some ne calcule pas les cellules filtrées


Pour résoudre ce problème, Excel vous fournit une autre fonction à utiliser qui est la fonction  SOUS.TOTAL .

La fonction SOUS.TOTAL englobe plusieurs fonctions Excel qui permettent de renvoyer des sous-totaux et parmi ces fonctions vous trouverez la fonction SOMME.

Note : nous avons traité cette fonction dans notre article : Inclure automatiquement les valeurs de la ligne insérée directement au-dessus de la fonction SOMME

Voici comment procéder pour calculer le total des cellules visibles et ignorer les cellules cachées ou masquées après avoir appliqué un filtre à votre tableau :

Faire la somme des cellules filtrées en utilisant SOUS.TOTAL

Dans cet exemple vous allez utiliser la fonction SOUS.TOTAL pour la colonne Total.

  • Sélectionnez donc la cellule D20 et tapez =sou puis cliquez sur le nom de fonction SOUS.TOTAL
  • Dans la liste qui s’affiche cliquez sur SOMME spécifiée soit par le numéro 9 ou le numéro 109.
  • Tapez point-virgule puis sélectionnez la plage de cellules à additionner D2 :D19.
  • Fermez la parenthèse et validez.
La fonction SOUS.TOTAL utilisée

  • Appliquez un filtre de données pour n’afficher que les données du produit A.
  • Remarquez que la somme est calculée uniquement pour les cellules visibles.
Somme des cellules filtrées


Une autre façon pour faire la somme des cellules visibles


  • Commencez par filtrer vos données, par exemple selon le critère Montant supérieur à 1000 euros.
  • Sélectionnez la cellule D20 puis cliquez sur Somme automatique.
  • Excel insère alors la fonction SOUS.TOTAL et introduit automatiquement les arguments convenables.
  • Tapez Entrée pour valider.
Somme des cellules visibles en utilisant sous.total

  • Si vous annulez le filtrage, le résultat de la somme est mis à jour automatiquement aussi.

Différence entre SOMME-9 et SOMME-109

Vous avez remarqué qu’il n’y avait aucune différence entre l’utilisation de la fonction SOMME spécifiée par le numéro 9 ou SOMME spécifiée par le numéro 109 dans les arguments de la fonction SOUS.TOTAL, quand vous avez calculé la somme des cellules filtrées à l’aide de l’outil Filtrer.

Argument 9 et 109 dans la fonction sous.total


Cependant, la différence apparaîtra si vous masquez les cellules manuellement.


  • Par exemple, insérez dans la cellule D20 une fonction SOUS.TOTAL avec comme premier argument le numéro 9.
  • Sélectionnez les lignes de 5 à 10 puis cliquez avec le bouton droit de la souris sur ces cellules et choisissez Masquer.
argument 9 dans la fonction sous.total inclut les cellules masquées manuellement

  • Vous remarquez donc que la somme n’a pas changé.
  • Réaffichez les lignes masquées et remplacez 9 par 109.
  • Répétez l’opération pour masquer les lignes de 5 à 10.
Argument 109 de la fonction sous.total exclut les cellules masquées manuellement

  • Vous remarquez cette fois que le calcul est effectué seulement sur les cellules visibles.
  • C’est ça donc la différence.

Du code VBA pour calculer le total des cellules filtrées

Dans cet exemple, on va suivre le même principe utilisé par Excel, c’est-à-dire, on va tout d’abord créer notre propre fonction qui va calculer la somme des cellules visibles dans une plage de cellules à additionner qu’on va la nommer SommeCellulesVisibles
Ensuite on va faire appel à cette fonction en l’insérant dans la cellule qui va afficher le résultat du calcul.

  • Commencez donc par ouvrir l’éditeur de code VBA en tapant Alt+F11.
  • Cliquez ensuite sur le menu Insertion puis Module et collez le code suivant :
Insertion Module VBA Excel


  Function SommeCellulesVisibles(Plage As Range) As Double
      Dim plg As Range
     Dim Total As Double
    For Each plg In Plage
       If plg.Rows.Hidden = False And plg.Columns.Hidden = False Then
        Total = Total + plg.Value
      End If
  Next
    SommeCellulesVisibles = Total
  End Function

Code VBA Excel Somme cellules visibles



  • Tapez Alt+F11 pour revenir en mode Excel
  • Sélectionnez la cellule D20 et tapez =Sommecel puis cliquez sur le nom SommeCellulesVisibles puis sélectionnez la plage D2:D19 et fermez la parenthèse.
Somme cellules filtrées avec code vba Excel

  • Faites vos filtres ou masquez manuellement vos cellules et regardez si le calcul est effectué automatiquement ou non !
Note : Puisque vous avez entré du code VBA, vous devez enregistrer votre classeur en prenant en charge les macros.
PARTAGEZ

Excel Info

  • Image
  • Image
  • Image
  • Image
  • Image