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

meandme · 24 Février 2020

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.

R@chid · 24 Février 2020

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

Code:

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

@ toi de l'adapter pour les autres trades.

Cordialement

GALOUGALOU · 24 Février 2020

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 · 24 Février 2020

Bonsoir

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

@ plus

R@chid · 24 Février 2020

Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

Code:

=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 · 24 Février 2020

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 · 24 Février 2020

Bonsoir à tous

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

@ plus

GALOUGALOU · 24 Février 2020

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 · 24 Février 2020

R@chid à dit:
Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

Code:

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

@ toi de l'adapter pour les autres trades.

Cordialement

GALOUGALOU à dit:
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)".

R@chid à dit:
Bonsoir @ tous,
Bienvenue sur XLD,

Pour "Long"

Code:

=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 · 25 Février 2020

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

meandme · 25 Février 2020

R@chid à dit:
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 · 25 Février 2020

Re,
pour faire la somme :

VB:

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

Pour le MAX :

Code:

=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 · 25 Février 2020

R@chid à dit:
Re,
pour faire la somme :

VB:

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

Pour le MAX :

Code:

=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 · 25 Février 2020

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 · 25 Février 2020

Bonjour @ tous,
Salut @mapomme

Pour la formule SOMMEPROD() tu peux y ajouter le critère >0 ainsi :

VB:

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

Cordialement

meandme · 25 Février 2020

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

VB:

=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

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

meandme

XLDnaute Nouveau

Pièces jointes

R@chid

GALOUGALOU

XLDnaute Accro

CISCO

XLDnaute Barbatruc

R@chid

XLDnaute Barbatruc

meandme

XLDnaute Nouveau

CISCO

XLDnaute Barbatruc

GALOUGALOU

XLDnaute Accro

meandme

XLDnaute Nouveau

Pièces jointes

R@chid

XLDnaute Barbatruc

meandme

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

meandme

XLDnaute Nouveau

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

R@chid

XLDnaute Barbatruc

meandme

XLDnaute Nouveau

Discussions similaires