[Résolu] Sommeprod et filtre automatique

[TooFatBoy]

Bonjour,

J'ai un grand tableau dont seulement trois colonnes sont ici utiles.
La colonne E contient des valeurs comprises entre 0 et 15.
La colonne AD contient la valeur "OK" ou la valeur "KO".
La colonne N contient des jours de la semaine.

Ce qu'il m'interesse de connaître, c'est le nombre de lignes où j'ai à la fois la valeur 0 (zéro) en colonne E et "OK" en colonne AD.
Pour cela j'utilise un Sommeprod : =SOMMEPROD((AD9:AD622="OK")*(E9:E622=0))
Cela fonctionne parfaitement bien.

Maintenant, si un filtre automatique est appliqué sur la colonne N, le résultat reste identique car les lignes masquées par le filtrage sont tout de même prises en compte pour le calcul.
Or, je voudrais bien évidemment que la formule ne s'applique qu'aux lignes visibles.

J'ai cherché toute la nuit sur internet en général et sur ce forum en particulier, mais je n'ai pas trouvé mon bonheur.
Je pensai que Sous.total était une partie de la solution, mais je n'ai pas réussi à trouver une formule valable.
J'ai vu aussi des formules utilisant Decaler, mais je n'ai pas bien saisi comment transposer cela à mon problème.


Merci par avance à ceux qui voudront bien se pencher sur mon problème.

 

[joss56]

Re : Sommeprod et filtre automatique

Bonjour Marcel32, le forum

Tu veux juste avoir le nombre de lignes?
Si oui: une petite procédure VBA fera ton bonheur

Dis nous.

Jocelyn

 

[hoerwind]

Re : Sommeprod et filtre automatique

Bonjour,

Sans fichier joint, pas facile de te montrer comment faire.

Il me semble néanmoins qu'il ne faut pas filtrer mais ajouter la condition du filtre dans la formule.
A titre d'exemple :
=SOMMEPROD((AD9:AD622="OK")*(E9:E622=0)*(A9:A622="oui"))
la partie en police rouge étant la condition du filtre que tu aurais employée.

Au lieu de placer le "oui" dans la formule, tu peux faire référence à une cellule contenant cette valeur.
En appliquant à cette cellule une liste de validation tu obtiendras la même chose qu'un filtre.

 

[JCGL]

Re : Sommeprod et filtre automatique

Bonjour à tous,
Salut HoerWind,

Pas facile sans fichier joint...
Deux exemples de SOMMEPROD() sur filtre automatique

A + à tous

 

[TooFatBoy]

Re : Sommeprod et filtre automatique

Tout d'abord, merci pour vos réponses.

En suite, je me doutais bien qu'il serait plus simple pour vous que je joigne un fichier, mais vu l'heure un peu tardive je n'avais pas le temps d'en créer un dépourvu de données personnelles. Désolé.


@ joss56 : oui, il me faut juste le nombre de lignes visibles correspondant aux deux critères ("OK" en cellule ADxxx et la valeur zéro en cellule Exxx) simultanément.
J'ai essayé de faire une fonction, mais ce n'était pas rafraîchi quand j'activais, désactivais ou changeais la valeur du filtre automatique.


@ hoerwind : ta proposition de faire référence à une autre cellule est intéressante car la formule reste simple et du coup je la comprends.
En revanche, j'ai peur que ça ne fonctionne pas si la personne qui utilise le tableau active plusieurs filtres.


@ JCGL : je vais aller regarder tes deux fichiers joints dans ta réponses.


Je vais essayer de céer un fichier à vous joindre, mais il y a pas mal de données un "confidentielles" et énormément de lien venant d'autres onglets, il faut que je simplie tout ça avant de pouvoir vous l'envoyer.

Merci encore pour vos réponses si rapides.

 

[TooFatBoy]

Re : Sommeprod et filtre automatique

Me revoici après quelques tests basés sur le premier fichier de la réponse de JCGL.


Je n'ai pas encore essayé de l'adapter à mon fichier, mais j'ai effectué quelques modifications (ajout d'une colonne entre autres) et ça semble parfaitement correspondre à ce que je cherchais.

La formule qui est doublement magique pour moi (elle fonctionne et je ne la comprend pas) est identique à celle que j'ai pu trouver lors de mes recherches effectuées cette nuit avant de poser ma question.
Identique à ceci prés : je n'arrive toujours pas à la comprendre à 100 %, mais j'ai réussi à l'adapter à mes besoins tellement ton exemple est bien fait JCGL.

La formule en question :
=SOMMEPROD((SOUS.TOTAL(3;DECALER($B$5;LIGNE($1:$95); )))*($B$6:$B$100=B3))


J'ai cru comprendre que le SOUS.TOTAL n'est en fait là que pour renvoyer une matrice des lignes affichées.
En revanche, je ne comprends pas le DECALER($B$5;LIGNE($1:$95); )

Même si j'effectue un filtrage automatique sur la colonne C, je peux garder $B$5 comme "référence" dans ma formule, ou tout aussi bien mettre $A$5.
Ce qu'il faut, si j'ai bien compris, c'est que la "référence" en question soit un en-tête du tableau filtré, quel que soit la colonne de cette référence.
Ai-je bon sur ce premier point ?

Je crois comprendre que le LIGNE($1:$95) veut dire que la formule ne tiendra compte que des 95 premières lignes du tableau (ou plus exactement ici 94 lignes puisque la première ligne sera l'en-tête).
Ai-je bon sur ce deuxième point ?


Merci encore pour vos réponses.

 

[TooFatBoy]

Re : Sommeprod et filtre automatique

J'ai adapté à mon classeur la formule donnée par JCGL et ça marche super bien, même mieux que je ne l'espérais !!

Toutefois, quand je fais une copie du fichier après filtrage, la valeur indiquée dans la partie LIGNE() de la formule ne correspond pas au nombre de lignes réel.
Par exemple, je me retrouve avec LIGNE($1:$85) au lieu de LIGNE($1:$87) ce qui renvoit biensûr une erreur.

Je connais la valeur que je dois avoir puisque c'est le contenu de la cellule J1 augmenté de 2.

Ma question est donc : comment faire varier le $95 de la formule originelle en fonction du contenu de la cellule J1 ?

 

[TooFatBoy]

Re : Sommeprod et filtre automatique

Bon ben voilà, j'ai fini par trouver une formule qui fonctionne et qui me convient.
J'ai remplacé LIGNE($1:$95) par LIGNE($9:$103)-8

Cela peut paraître bizarre, mais il faut dire que le reste de la formule était déjà basé sur cette plage de lignes (de 9 à 103).

Donc un immense merci à tous. ​