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.

La fonction Some ne calcule pas les cellules filtré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.

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.

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 :

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.