plage de cellules qui change en fonction de critères et calcule de fréquences

[Hervé]

Bonjour, un petit problème pour ceux qui maîtrisent EXCEL ! (en tout cas plus que moi !)

J’aurais besoin de quelques conseils pour faire une feuille de calcule avec EXCEL.
Voilà mon problème en fait il y en à plusieurs.

j’ai une liste de paramètres à étudier (1, 2, 3, 4, ….15) dans 3 sites géographiques différents. Chaque paramètre peut prendre plusieurs valeurs possibles. Dans chaque site plusieurs prélèvements sont effectués (80 environ). Les données sont organisées dans un tableau.


1 2 3
site taille masse taille Masse Taille Masse Taille Masse taille masse
A 123 500 110 410 32 30 56 32 55 36
142 530 123 506 41 33 70 36
130 600 63 60 50 25 45 32
142 610 150 620 60 27 50 30
B


c


si le tableau n'apparait pas une rapide présentation:
Colonne 1 : site a, b ou c
Ligne 1 : les 15 paramètres étudiés dans les différents sites
Ligne 2 : type de l'information Taille ou masse pour chaque paramètre (plrs tailles par paramètres)
Ligne 3 : prélèvement dans site 1
Ligne 4 : prélèvement dans site 1
…
ligne 81 : prélèvement dans site 2
ligne 82 : prélèvement dans site 2
…..

ligne 170 : prélèvement dans site 3
ligne 171 : prélèvement dans site 4

(normalement une colonne pour l’identifiant du prélèvement est en deuxième position mais pour simplifier on peut l’oublier)

Colonne 2 : taille du paramètre 1
Colonne 3 : masse associées à cette taille
Colonne 4 : une autre valeur de la taille pour le même paramètre
Colonne 5 : masse associée à cette deuxième taille

Colonne 6 : taille du paramètre 2
Colonne 7 : masse associée à cette taille

Colonne 8: taille du paramètre 3
Colonne 9 : masse associée à cette taille
Colonne 10 : taille du paramètre 3
Colonne 11 : masse associée à cette taille

Donc en fonction du paramètre et du prélèvement on peut trouver plusieurs colonnes avec les valeurs de taille intercalées avec les colonnes donnant la masse .
Toutes les lignes ne sont pas obligatoirement remplies.
Ainsi pour un site choisi et un paramètre déterminé le tableau donne plusieurs valeurs de taille sur plusieurs lignes (les unes sous les autres avec parfois des cellules vides) et plusieurs colonnes (séparées par une colonne dite masse).

Je voudrais en indiquant dans deux cellules de la feuille le paramètre (1,2,3,…ou 15) dans la première cellule et le site de prélèvement (a, b ou c) dans la deuxième cellule
que EXCEL choisisse la bonne plage de cellule correspondante et qu’il me donne :

1. Le nombre de tailles différentes observées pour un paramètre donné dans un site donné
2. Une liste de toutes ces tailles observées
3. La fréquence de ces différentes tailles observées
4. Les tailles extrêmes (la plus grande et la plus petite)

J’ai commencé à chercher comment faire avec des formules comme BD..(plage de cellule ;champ ;critère) mais les recherches ne se font que dans la première colonne taille de la plage de cellule

Pour un choix de la plage de cellule à étudier en fonction du paramètre et du site sélectionnés j’ai essayé de chercher la bonne plage de cellule avec la fonction choisir( N° index ; cellule 1 ; cellule 2 ;…)

Donc en mélangeant les deux fonctions

BD…(choisir(N° ;… ;…. ;…. : choisir( n° ;… ;… ;…) ;champs ;critères)

Qu’est ce que vous en pensez ? avez vous des solutions à proposer ? merci!!merci beaucoup !!! pour votre aide !!!!

Hervé

 

[Jean-Marie]

Bonjour Hervé

Inspire toi, de ce fichier.

Il y a une plage déterminée par une fonction INDEX et suivant les choix de l'utilisateur. Pour faire plus court, le nombre de prélèvements par site est de dix.

@+Jean-Marie

 

[C@thy]

C'est SUPER ça, Jean-Marie, avec les listes déroulantes et tout et tout et toujours tes merveilleuses formules!

Bon ouik tout le monde.

C@thy

 

[Hervé]

Bonjour!

mille mercis à Jean-Marie!!!j'ai renvoyé un mail qui n'apparait pas sur le forum alors pour y remédier voilà un petit message:

comme je te disais dans mon mail c'est vraiment génial je regarde comment je l'utiliser au mieux et je t'envoie mes noouvelles interrogations!

encore une chose dans ton tableau chaque critère n'englobe qu'une seule colonne. alors que dans mon cas j'ai quatre colonnes par exemple sous le critère 1
avec colonne 1: taille du critère 1
colonne 2:masse correspondante
colonne 3: autre taille possible pour le critère 1
colonne 4:masse correspondante
donc en fait la plage de cellule comprend 4 colonne sur X lignes mais en réalité seulement deux colonnes (les colonnes tailles ) sont étudiées

en fait je te précise ça mais je n'ai pas encore vérifié tes formules sur mon tableau donc peut-être que cela ne va rien changer

encore merci!!!!
à+ Hervé

 

[Jean-Marie]

Bonjour, Cathy, Hervé

Merci Cathy.

Je m'excuse Hervé, je n'avais pas tout compris, dans l'énoncé de ton problème. Dans le premier fichier, une seule colonne de données est prise en compte.

Donc voici le fichier modifié, pour tenir compte de deux colonnes de valeurs, j'ai essayé de garder les mêmes formules en les adaptant à la gestion de plage de cellules multi-zones, ce qui n'est pas évidant, certaines fonctions ne savant pas les utiliser.

Bonne soirée

@+Jean-Marie

 

[Jean-Marie]

Bonjour

Nouvelle version, les formules pour lister les valeurs sans doublons, ne dépendent plus du nombres de zones dans la plage nommée.

J'espère avoir mis assez de commentaires, pour que tu puisses adapter les formules à ton cas précis.

Si tu as des problèmes n'hésite pas.

@+Jean-Marie

 

[Hervé]

Bonjour!! Jean-Marie

j'ai regardé les formules du premier doc et je dois l'avouer certaines zones d'ombre persistent, avant de t'en faire part je regarde les deux autres docs. je viens de jetter un bref coup d'oeil sur le dernier et je crois que ................Jean-Marie............... t'es un BON !!
Dans la vie y les BONS et les moins bons et bien toi tu appartiens à la première catégorie....

merci beaucoup pour ton aide!!!

quand même ça m'a fait un petit peu plaisir quand tu as dit "ce qui n'est pas évidant" ça veut dire que même pour un bon c'était pas facile ???

Bon je regarde ça et je t'envois un mail...ma semaine est tres chargée je ne sais pas trop quand....

à + Hervé

tu m'aurais vu devant mon ordi on aurait dit un petit enfant, les yeux grands ouverts avec un sourire jusque là!!
merci encore!!

Pendant que j'y pense sur ce forum j'ai découvert visual basic ça semble être un sacré outils as tu des sites pour débuter? mais ça c'est pour plus tard

 

[Jean-Marie]

Bonsoir, Hervé

Un site, attend je cherche..... heu, où il y a des masters en VBA, attend je cherche, mais que je suis bête, sur Xld il y en a beaucoup, Vériland, Laurent, Ti, Zon, Christophe et j'en oublie ....

Non pas moi, mes connaissances en VBA sont nulles.

Merci pour me mettre dans la catégorie des Bons, il y a une très bonne, pour pas la nommée Monique.

Ce n'est pas l'essentiel d'être catalogué, c'est de rendre service, heureux donc de t'avoir rendu service, et pour ces remerciements qui comble tous les posts sans remerciement.

Bonne soirée

@+Jean-Marie

 

[C@thy]

Bonjour tout le monde,

je confirme, Hervé, Jean-Marie et Monique sont des super bons dans les formules, et en plus toujours prêts à rendre service et ça, c'est vraiment très précieux.

Des pros en VBA, oui, il y en a plein ici, mais pour des cours VBA débutant tu peux Ce lien n'existe plus

Il y a aussi les 2 packs de @+Thierry à télécharger et d'autres trésors sur ce site.
En gros, tu commences par te débrouiller tout seul, tu cherches le maximum de sites (et de bouquins) et si tu veux faire quelquechose de précis et que ça coince, tu poses tes questions sur le forum XLD, mais les pros du VBA sur XLD il ne faut pas tout leur demander non plus, faut d'abord faire un petit effort avant, enfin, je te dis ça, c'est pour que tu perçoives l'esprit de ce forum, c'est un peu une grande famille, il y en a même qui nous font part de la naissance de leur bébé, c'est super, non?

Allez, bon courage à toi et bonne journée

C@thy

 

[Hervé]

Bonjour tout le monde!!
dans l'ordre même si c'est un peu austère:

1) Merci à toi Jean-Marie et à Cathy pour ces pistes sur le VBA

2) j'espère ne pas avoir trop abusé, et ne pas m'être trop éloigné de l'esprit du forum (humour pour Cathy)

3) Pour Jean-Marie: j'ai bien regardé tes feuilles excel avec l'aide justement de l'aide de microsoft mais plusieurs questions subsistes et c'est pas faute d'avoir essayé

a) je n'ai pas compris comment faire ces listes déroulantes ? est ce que c'est une sorte de formulaire?

b) Pour nommer une plage de cellule , ok je la sélectionne et je rentre son nom en haut à gauche mais pour associer un nom a une formule tel que ;
=SI(Feuil1!$D$4="A";Feuil1!$B$19:$B$28;SI(Feuil1!$D$4="B";Feuil1!$B$29:$B$38;Feuil1!$B$39:$B$48)) comment faire ? où metre cette formule
.....sinon c'est bon j'ai compris la fonction SI c'est déjà ça !

c) j'ai réutilisé la formule =SOMME((FREQUENCE(Plagechoisie5;Plagechoisie5)>0)*1) qui donne le nombre de valeurs différentes ça marche super bien merci! j'ai compris la fonction fréquence mais je ne comprends pas ce *1

d) j'ai aussi du mal avec ces matrices ou les formules avec matrices, en plus quand je veux les modifier un message d'alerte me dit que je ne peux pas modifier une partie de la matrice !

e) les choses sérieuses, ta grande formule:

=SI(LIGNE()-LIGNE(S$12)<T$8;PETITE.VALEUR(Plagechoisie5;1+SOMME((PETITE.VALEUR(Plagechoisie5;LIGNE(A$1:A$20))<=SI(ESTNUM(T11);T11;MIN(Plagechoisie5)-1))*1));"")

jusqu'à PETITE.VALEUR je pense avoir saisi le raisonnement
apres la fonction petite valeur comprend (la plage; le nombre du rang du chiffre que l'on veut)

donc

1+SOMME((PETITE.VALEUR(Plagechoisie5;LIGNE(A$1:A$20))<=SI(ESTNUM(T11);T11;MIN(Plagechoisie5)-1))*1)

doit normalement varier de 1 à nombre max de valeur différentes
pour mieux la comprendre peux tu me dire pourquoi ce *1
et pourquoi faut-il un tableau matriciel dans la fonction petite valeur ...je n'ai pas vu cette possibilité dans l'aide, je ne comprends pas tout dans cette partie

tu dis qu'il faut connaître le nombre de de valeurs inférieures ou égale à une valeur précédente pour éliminer les doublons mais comment ce fait le raisonnement ...(j'suis peut-être un peu chiant là ,je jette un coup d'oeuil à mon message et c'est vrai il est un peu long)

f) je termine par ça j'avais d'autres questions pour cette fois c'est pas mal
dans le tableau que tu m'as envoyé les doublons ne sont effectivements pas un problème parcontre les cellules vides le sont si je rajoute une cellule vides dans la plage étudiée il n'y a plus que des ###### dans toutes les cellules... au secour!


d) merci encore à toi Jean-Marie et à ce forum (j'apprends pas mal en lisant les autres messages!) qui se rapproche assez de l'idée que je me fais d'Internet: sans but lucratif!! ça devient de plus en plus rare!!

à + Hervé

j'ai regardé la feuille de Celeda et Monique mais quand j'utilise leur formule j'ai des soucis je vais refaire une tentative

par simple curiosité Jean-Marie, ça te prend combien de temps en moyenne ce forum?

et merci.............

 

[Jean-Marie]

Bonsoir, Hervé

Regarde le fichier, j'ai placé des explications dans la feuille2

@+Jean-Marie