Microsoft 365 Comment utiliser NB.SI dans une plage résultant d'un tableau filtré

[meandme]

Bonjour,

Dans un tableau Excel 365, en utilisant la fonction =NB.SI je suis incapable d'obtenir un résultat autre que sur l'ensemble du tableau même si j'utilise un filtre. Par ex. (dans le fichier ci-joint), si je sélectionne uniquement les trades pour le mois de février 2020 (filtre appliqué à la colone "Entry date"), les totaux au bas du tableau (ligne verte) se mettent à jour correctement. Cependant les cellules statistiques (en jaune) demeurent inchangées, qu'il y ait un filtre activé ou non. Le souci est là.

À l'intérieur d'une plage de résultats, par exemple, en utilisant =NB.SI, je veux savoir combien de trades "long" ont été faites pour février 2020. Excel retourne toujours le même résultat (36) soit le nombre total de trades "long" pour l'ensemble des transactions du tableau, alors que le nombre réel de trades "long" pour février 2020 est de (10) en réalité.

Alors comment faire pour que les fonctions =NB.SI =MAX =MIN =SOMME.SI etc. n'utilisent que la plage résultante d'un tableau filtré au lieu d'utiliser les données de tout le tableau? Pourtant, les totaux du tableau fonctionnent en lien avec les résultats des filtres appliqués. J'ai longuement cherché la solution mais sans succès.

Merci à l'avance pour votre aide.

PS: Modifiez le fichier au besoin pour illustrer votre solution. Merci.

 

[GALOUGALOU]

bonjour meandme bonjour le forum
j'aime bien la solution ci-dessous trouvé sur un autre forum
je vous fais un copier-coller
cordialement
galougalou


Une bonne astuce à ce problème est de créer une colonne qui indique si la ligne a été "filtrée" ou non grâce à la fonction "SOUS.TOTAL".

Créer une colonne avec pour fonction "SOUS.TOTAL(3;A1)" (en supposant A1 une case toujours remplie) : SOUS.TOTAL permet d'effectuer des opérations sur une liste filtrée. Ici, la fonction 3 correspond à NBVAL. En résumé, si la ligne est filtrée, la fonction "SOUS.TOTAL(3;A1)" renverra 0, 1 sinon.

Puis effectuer un comptage (NB.SI.ENS) ou une somme (SOMME.SI.ENS) avec les critères de sélection voulus : "NB.SI.ENS(colonne;critère 1; colonne créée; 1)".

 

[CISCO]

Bonsoir

Une possibilité sur ce forum, à adapter à ton besoin.

@ plus

 

[R@chid]

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D75)));))*(B5:B75="Long"))

@ toi de l'adapter pour les autres trades.


Cordialement

 

[meandme]

Bonjour galougalou,

Merci pour ton reply. Cependant ce n'est pas la solution au problème. Le problème n'est pas de compter le nombre de records obtenus lors de l'application d'un filtre. J'ai déjà ce résultat.

Le souci est qu'une fois que le filtre est appliqué et que j'obtiens 16 résultats (sur 68) pour les transactions effectuées au mois de février 2020 seulement, je veux savoir à l'intérieur de ces 16 entrées, combien correspondent au critère de la colone B qui est "long". Donc =NB.SI(B5:B75;"long").

Cette formule (dans la première cellule jaune) retourne toujours le même résultat parce qu'elle calcule la variable "long" dans tout le tableau au complet, alors que je cherche comment calculer le nombre de "long" dans le tableau filtré.

Donc la solution qui fonctionnera sera celle qui retournera 10 à la cellule D78.

Merci

Cordialement, meandme

 

[CISCO]

Bonsoir à tous

Meandme, R@chid ( ) t'a mâché le travail, il me semble.

@ plus

 

[GALOUGALOU]

bonsoir meandme bonsoir le fil
il ne s'agit pas de compter
la solution proposer dans ce fil est de
créer une colonne supplémentaire avec la fonction sous total sur laquelle s’appuierait la fonction nb.si qui serait opérationnel uniquement sur les lignes visibles.

mais il me semble que r@chid fait une proposition très intéressante
@+

 

[meandme]

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D75)));))*(B5:B75="Long"))

@ toi de l'adapter pour les autres trades.


Cordialement

bonjour meandme bonjour le forum
j'aime bien la solution ci-dessous trouvé sur un autre forum
je vous fais un copier-coller
cordialement
galougalou


Une bonne astuce à ce problème est de créer une colonne qui indique si la ligne a été "filtrée" ou non grâce à la fonction "SOUS.TOTAL".

Créer une colonne avec pour fonction "SOUS.TOTAL(3;A1)" (en supposant A1 une case toujours remplie) : SOUS.TOTAL permet d'effectuer des opérations sur une liste filtrée. Ici, la fonction 3 correspond à NBVAL. En résumé, si la ligne est filtrée, la fonction "SOUS.TOTAL(3;A1)" renverra 0, 1 sinon.

Puis effectuer un comptage (NB.SI.ENS) ou une somme (SOMME.SI.ENS) avec les critères de sélection voulus : "NB.SI.ENS(colonne;critère 1; colonne créée; 1)".

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D75)));))*(B5:B75="Long"))

@ toi de l'adapter pour les autres trades.


Cordialement

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D75)));))*(B5:B75="Long"))

@ toi de l'adapter pour les autres trades.


Cordialement

Bonjour à tous,

Merci pour votre intérêt.

R@chid merci pour la solution ça fonctionne super bien!! Enfin!. J'ai réussi à adapter la formule aux autres cellules utilisant =NB.SI mais je n'ai pas été capable d'ajuster ta formule avec les fonctions =SOMME.SI, =MAX et =MIN.

Si tu avais la gentillesse de les inclure dans la formule que tu me suggères, avec un exemple dans mon fichier (les cellules en jaune) j'apprécierais beaucoup. Ça m'aiderait beaucoup car je ne comprends pas totalement la structure de la formule utilisée j'avoue.

Merci à l'avance.

 

[R@chid]

Re,
tu veux aussi faire la SOMME(), MAX() et MIN() pour un traide ou bien juste pour le mois de Février seul ?

 

[meandme]

Re,
tu veux aussi faire la SOMME(), MAX() et MIN() pour un traide ou bien juste pour le mois de Février seul ?

Re,

Si tu regardes dans la section stats en bas (en jaune) avec =SOMME.SI je veux faire la somme des gains pour la période sélectionnée, ici c'est février mais je vais faire cela pour chaque mois, chaque trimestre chaque année etc.

Par la suite je veux connaitre le plus gros gain de la période sélectionné avec =MAX et la plus grosse perte durant la même période avec =MIN. Voilà!

Merci pour ton aide c'est très apprécié.

 

[R@chid]

Re,
pour faire la somme :

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));))*(I5:I73))

Pour le MAX :

=MAX(SI(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));));I5:I73))

Formule matricielle à valider par la combinaison des 3 touches Ctrl+Shift+Enter
Une fois la validation matricielle réussie tu vas voir apparaitre des accolades {} qui entourent la formule dans la barre de formules.

@ toi de l'adapter pour le MIN


Cordialement

 

[meandme]

Re,
pour faire la somme :

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));))*(I5:I73))

Pour le MAX :

=MAX(SI(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));));I5:I73))

Formule matricielle à valider par la combinaison des 3 touches Ctrl+Shift+Enter
Une fois la validation matricielle réussie tu vas voir apparaitre des accolades {} qui entourent la formule dans la barre de formules.

@ toi de l'adapter pour le MIN


Cordialement

Merci Rachid c'est bien noté pour les 3 touches de validation.

Les formules =MAX et =MIN fonctionnent très bien également dans la plage résultante peu importe le filtre.

Pour la formule de remplacement de =SOMME.SI que tu proposes dans la case D78 (en jaune), il manque un argument. Pour les gains (c'est à dire les transactions positives pour la période de février 2020) il faut indiquer d'additionner le résultat uniquement si la valeur est de la colonne I (Net P/L) >= à 0 et inversement pour les transactions négatives pour la même période <=0 en D79. A titre de référence, le total des gains en D78 doit donner $1,413.10 pour cette période (février 2020). Je n'ai pas réussi à trouver comment faire pour ajouter le ">=0" dans la formule proposée. Merci à l'avance.

Pour terminer, j'aimerais avoir également le total des transactions "long" (de la colonne B) dont le résultat apparaît dans la colonne I. Et le total de la colonne à la case I76 (qu'il soit positif ou négatif) pour la même période. Je transposerai la formule pour le coté "short" et pour les autres cases je serai ok.

Une fois ces formules complétées, j'aurai un outil statistique très performant pour analyser mes résultats boursiers. On y est presque.

Merci de partager ton expertise sur ce forum. Ton aide est précieuse.

Cordialement

 

[mapomme]

Bonjour @meandme, @GALOUGALOU, @CISCO , @R@achid,

Une autre possibilité basée sur la solution de @R@chid (que je salue ) est de décomposer sa solution via une colonne supplémentaire intégrée au tableau structuré.

Cette colonne est appelée OK. Elle contient une formule simple retournant 1 si la ligne est affichée et retournant 0 si la ligne est masquée (mais on ne verra jamais les zéros puisque leurs lignes sont masquées - c'est comme l'obscurité dans le réfrigérateur ).

On utilisera cette colonne dans les formules de type SOMMEPROD(...), NB.SI(...), SOMME.SI.ENS(...), formules matricielles ..., etc.

Voir le fichier joint.

 

[R@chid]

Bonjour @ tous,
Salut @mapomme
Pour la formule SOMMEPROD() tu peux y ajouter le critère >0 ainsi :

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));))*(I5:I73>0);I5:I73)

Cordialement

 

[meandme]

Bonjour @ tous,
Salut @mapomme
Pour la formule SOMMEPROD() tu peux y ajouter le critère >0 ainsi :

=SOMMEPROD(SOUS.TOTAL(2;DECALER(D4;LIGNE(INDIRECT("1:"&LIGNES(D5:D73)));))*(I5:I73>0);I5:I73)

Cordialement

Tout fonctionne merveilleusement bien dans tous mes tableaux. Vraiment génial. Très apprécié. Une économie de temps et d'énergie sans précédent. Plus d'infos, plus vite, en moins de temps! Wow!

Merci!!

Cordialement