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.

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.

Pour résoudre ce problème, vous pouvez utiliser la fonction SOMME, mais en passant par une  autre fonction fournie par Excel, qui est la fonction  SOUS.TOTAL .

En effet, 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 donc 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 calculer la somme des montants se trouvant dans 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.

• 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.

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.

• 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.

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.

• 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.

• 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 :

  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

• 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.

• 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 PARTAGEZ PARTAGEZ