Affecter une valeur correspondant à un nb de valeurs différentes +critère

Bonjour,

J'ai un petit casse tête à proposer, je n'arrive pas à trouver la formule qui me permette de calculer le nb de valeurs d'espèces différentes (colonne D) pour chaque point (colonne A) pour lesquelles la valeur d'une autre colonne (E) est égale à 1.

J'ai fait un fichier simplifié en pièce jointe où j'ai mis les résultats attendus, maintenant il me faut trouver la formule pour le calculer automatiquement. Je sais que je peux avoir les résultats avec des tableaux croisés dynamiques, mais là je voudrais avoir une formule pour incrémenter cette donnée dans le tableau.

Merci par avance,

SAndrine

Bonjour sandrine_7,

Voyez si la solution proposée dans le fichier joint vous convient

Bonjour, Salut njhub,

Avec 1 colonne en plus + TCD pour calculer le nombre d'espèces distinct.

bonjour

un essai

6sandrine.xlsx (10.18 Ko)

cordialement

Bonsoir le forumn

G2 : =SIERREUR(SI(A1<>A2;SOMME(SI($A$2:$A$23=A2;1/NB.SI($F$2:$F$23;$F$2:$F$23)));"")-NB.SI.ENS($A$2:$A$23;A2;$E$2:$E$23;0);"")

Formule matricielle.

valeur s

Re,

La colonne F est évidemment présentée avec une MFC.

mfc

Bonjour,

Merci beaucoup pour vos réponses à tous et vos formules assez incroyables. Vous avez passé du temps à regarder mon petit casse tête qui semble vous poser moins problème à vous qu'à moi...

Du coup, comme j'aimerais avoir le résultat calculé dans la colonne sans recourir à un tableau croisé dynamique, j'ai essayé de comprendre et adapter les formules de Tulipe et Mbbp. J'avoue que pour l'instant je n'ai pas réussi à les adapter comme il faut. Aussi je me permets de vous poser une question chacun :

  • pour Tulipe : la formule marche en effet sur le mini-fichier excel de démonstration que je vous avais envoyé. Mais j'ai essayé de l'adapter dans mon tableur et les résultats en nombre d'espèces sont sous-estimés. j'ai essayé de trier les colonnes par point car j'avais l'impression que le bug venait de là mais ça ne change pas le résultat. Je vous envoie mon fichier dans lequel j'ai mis en jaune les colonnes concernées (celles qui apparaissaient dans le mini-fichier). Et en orange les deux colonnes ajoutées. Dans votre formule (que j'avoue ne pas arriver à bien comprendre... je veux bien une petite traduction) vous n'utilisez pas la colonne F qui fait la concaténation du nom du point et de l'espèce. Est-ce normal?
  • pour MBBP : j'ai essayé de copier votre formule mais le résultat est en décimal et ne correspond pas. Je vous envoie le fichier "MBBP etc." où j'ai copié la formule. J'ai fait ctrl maj entrée pour la valider mais ça ne semble pas aller. Auriez-vous une explication si cela marche chez vous et pas chez moi?
Merci par avance !

SAndrine

Et merci également à Raja et Njhub pour vos réponses...

Bonjour sandrine_7,

Voyez si la solution proposée dans le fichier joint vous convient

Bonsoir,

Merci pour cette solution proposée! Cela sort le bon résultat, c'est super! Pour que je comprenne:

  • la colonne BW est-elle utile au final pour ce calcul ?
  • Vous avez créé la colonne BV qui comprend le numéro du point; quelle est son utilité dans la formule?
  • du coup, la formule marche quelque soit le tri des cellules j'ai l'impression; pas besoin de faire un tri par point avant. C'est nickel!
  • comment je pourrai modifier la formule pour qu'en dernière colonne (BX), le nombre d'espèces du point 4 apparaisse devant une ligne du point 4 (n'importe quelle ligne du moment qu'elle correspond au point 4), celui du point 3 devant le point 3 etc., ça me permettra de faire des graphes après plus facilement.
Merci!!!

Sandrine

Bonsoir sandrine_7,

la colonne BW est-elle utile au final pour ce calcul ?

Oui elle détermine si l'espèce a déjà été recensée à ce point.

comment je pourrai modifier la formule pour qu'en dernière colonne (BX),

=SI(BT2<>BT1;SI(ESTNUM(EQUIV("Point "&DROITE(BW2;2)*1;BT$2:BT$236;0));"Point "&DROITE(BW2;2)*1&" Nbre d'esp. "&NB.SI(BV$2:BV$236;DROITE(BW2;2)*1);"");"")

Voyez le résultat en colonne BY, vous pourrez déplacer la formule en BX si elle vous convient

Bonjour,

Merci pour ces réponses et la formule modifiée. Le seul hic est que du coup, les résultats des points 10, 6 et 2 n'apparaissent plus dans la colonne... je ne comprends pas d'où cela vient....

Bonjour sandrine_7,

Le seul hic est que du coup, les résultats des points 10, 6 et 2 n'apparaissent plus dans la colonne... je ne comprends pas d'où cela vient....

Je n'avais pas vu ça, merci.

Voyez si la solution proposée dans le fichier joint modifié en conséquence vous convient

Bonjour,

Merci! Effectivement vos corrections fonctionnent bien et le nb d'espèces apparaît bien pour chaque point.

J'ai modifié de façon mineure la formule (j'ai enlevé le texte avant le nb d'espèces au point pour des raisons d'utilisation ultérieure en graphes; et j'ai élargi à toute la colonne BT) de cette façon :

=SI(ET(BU1=0;BU2=1;BT2=BT1);SI(ESTNUM(EQUIV("Point "&DROITE(BW2;2)*1;BT:BT;0));NB.SI(BV$2:BV$236;DROITE(BW2;2)*1);"");"")

Par contre, il est possible que mes points aient des numéros potentiellement de 1000, 10 000 (en fait le tableau de données sera un TCD donc évolutif) donc j'ai voulu modifier DROITE(BW2;2) en DROITE(BW2;5) mais cela ne fonctionne plus (alors qu'on devrait atteindre les caractères de Point ?). Il y a-t-il moyen d'adapter la formule pour envisager ces cas?

Et si ce n'est pas trop abusé, pourriez-vous m'expliquer la syntaxe de la formule car même si je vois le principe général de calcul, je ne comprends pas l'ensemble de la formulation...

Mille merci !!

Sandrine

DROITE(BW2;2)

mais en fait c'est
DROITE(BW2;2)*1

qui donne un nombre naturel en base 10.

Si vous voulez aller jusqu'à 99999; "DROITE(BW2;5)*1", il faudra renommer tous les points pour qu'ils aient le même format càd "Point espace et cinq caractères numériques", pour le point1 ça donnerait "Point 00001", pour le point10 ça donnerait "Point 00010", etc...

pourriez-vous m'expliquer la syntaxe de la formule

=SI(ET(BU1=0;BU2=1;BT2=BT1);SI(ESTNUM(EQUIV("Point "&DROITE(BW2;2)*1;BT:BT;0));NB.SI(BV$2:BV$236;DROITE(BW2;2)*1);"");"")

Dans la parenthèse du ET() on vérifie si les trois conditions sont réunies

auquel cas on poursuit par

SI(ESTNUM(EQUIV("Point "&DROITE(BW2;2)*1;BT:BT;0));NB.SI(BV$2:BV$236;DROITE(BW2;2)*1);"")

sinon on renvoie vide.

Dans

EQUIV("Point "&DROITE(BW2;2)*1;BT:BT;0)

on recherche la première occurence exacte de '"Point "&DROITE(BW2;2)*1' dans la colonne BT. Si cette occurence est trouvée on enchaine sur le décompte avec

NB.SI(BV$2:BV$236;DROITE(BW2;2)*1)

qui dénombre les apparitions de 'DROITE(BW2;2)*1' dans la colonne BV,

sinon on renvoie vide.

Il faut articuler les composantes de la formule avec un peu d'intelligence, de malice voire de vice pour arriver à ses fins, il y en a des plus simples et d'autres qui me sont inaccessibles...

Ah merci pour toutes ces explications! Mon seul souci reste le nom du point; effectivement je peux avoir des numériques élevés, mais aussi un mélange alphanumérique (ex : IRR37_03). La formule n'identifie que les numériques?

Il faudra soit adapter le nom des points pour qu'ils soient uniques par les cinq ou plus, derniers chiffres de droite, soit trouver une formule qui conviendra. On peut également étendre à, 10 ou plus, chiffres à droite. En fait pour que ce soit fonctionnel il faut faire simple, le plus simple possible.

Ok. J'essaie de comprendre pour arriver à trouver une solution simple :

  • Quand je fais DROITE(BW2;3)*1 dans la formule, il ne veut plus me calculer le nombre d'espèces car il intègre un caractère de texte parmi les 3 ("T 1")?
  • Du coup, il faudrait que mon numéro de point n'intègre pas de caractère textuel du coup? Dans ce cas est-il possible qu'on incrémente dans la colonne BW à la fin un numéro de point du type 0000001 (un numéro unique par point, qui ne servirait qu'au calcul). Ca réglerait le problème, non?

Oui, en affectant un numéro unique à chaque point, on a déjà une valeur numérique, il n'y a plus besoin de multiplier par 1, et le point de recensement est identifié.

Du coup, pour le point identifié par 101 la formule devient:

=SI(ET(BU1=0;BU2=1;BT2=BT1);SI(ESTNUM(EQUIV(101;BT:BT;0));NB.SI(BV$2:BV$236;101);"");"")

Vous disposez de plusieurs colonnes pour les points, la colonne "Identifiant de la localisation" me semble la plus appropriée, pas besoin de l'inventer, le numéro unique existe

354697 Identifiant de la localisation

Coquibus_1 Nom de la localisation

Coquibus_1 Numéro du point

Point 1 Nom du point

Rechercher des sujets similaires à "affecter valeur correspondant valeurs differentes critere"