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

Y compris Power BI, Power Query et toute autre question en lien avec Excel
s
sandrine_7
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 7 octobre 2019
Version d'Excel : 2013

Message par sandrine_7 » 20 octobre 2019, 01:25

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
Classeur_envoi_forum_20oct2019.xlsx
(9.17 Kio) Téléchargé 12 fois
n
njhub
Membre impliqué
Membre impliqué
Messages : 1'516
Appréciations reçues : 90
Inscrit le : 5 juin 2017
Version de Calc : LO 6352 FR-YT

Message par njhub » 20 octobre 2019, 08:25

Bonjour sandrine_7,

Voyez si la solution proposée dans le fichier joint vous convient
sandrine_7-Classeur_envoi_forum_20oct2019.xlsx
(8.92 Kio) Téléchargé 8 fois
Avatar du membre
Raja
Passionné d'Excel
Passionné d'Excel
Messages : 5'190
Appréciations reçues : 14
Inscrit le : 3 février 2008
Version d'Excel : 2010 à 2016 FR et 2010 EN

Message par Raja » 20 octobre 2019, 13:40

Bonjour, Salut njhub,
Avec 1 colonne en plus + TCD pour calculer le nombre d'espèces distinct.
Copy of Classeur_envoi_forum_20oct2019.xlsx
(14.53 Kio) Téléchargé 17 fois
Cdlt,
Raja
t
tulipe_4
Passionné d'Excel
Passionné d'Excel
Messages : 8'789
Appréciations reçues : 262
Inscrit le : 1 janvier 2011
Version d'Excel : 2000 2007

Message par tulipe_4 » 20 octobre 2019, 14:16

bonjour
un essai
sandrine.xlsx
(10.18 Kio) Téléchargé 4 fois
cordialement
c'est en cherchant qu'on trouve
Avatar du membre
mbbp
Passionné d'Excel
Passionné d'Excel
Messages : 6'293
Appréciations reçues : 228
Inscrit le : 29 mars 2014
Version d'Excel : 2007 FR

Message par mbbp » 20 octobre 2019, 18:31

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).png
Avatar du membre
mbbp
Passionné d'Excel
Passionné d'Excel
Messages : 6'293
Appréciations reçues : 228
Inscrit le : 29 mars 2014
Version d'Excel : 2007 FR

Message par mbbp » 21 octobre 2019, 11:18

Re,

La colonne F est évidemment présentée avec une MFC.
MFC.png
MFC.png (41.81 Kio) Vu 169 fois
s
sandrine_7
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 7 octobre 2019
Version d'Excel : 2013

Message par sandrine_7 » 21 octobre 2019, 16:35

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
MBBP_Classeur_envoi_forum_20oct2019.xlsx
(10.29 Kio) Téléchargé 2 fois
Tulipe_Classeur_envoi_forum_Tulipe_pb_adaptation_formule.xlsx
(844.71 Kio) Téléchargé 3 fois
s
sandrine_7
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 7 octobre 2019
Version d'Excel : 2013

Message par sandrine_7 » 21 octobre 2019, 16:37

Et merci également à Raja et Njhub pour vos réponses...
n
njhub
Membre impliqué
Membre impliqué
Messages : 1'516
Appréciations reçues : 90
Inscrit le : 5 juin 2017
Version de Calc : LO 6352 FR-YT

Message par njhub » 21 octobre 2019, 17:46

Bonjour sandrine_7,

Voyez si la solution proposée dans le fichier joint vous convient
s
sandrine_7
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 7 octobre 2019
Version d'Excel : 2013

Message par sandrine_7 » 21 octobre 2019, 19:01

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
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message