2ème maximum

[philoo]

Bonjour à tous !

Je recherche le moyen de calculer le nombre ayant un rang=2 dans une plage (en gros le deuxième plus grand nombre). Je m'en sors pour l'instant avec la fonction =rang(maPlage), et avec une somme.si pour le critère rang=2.
Mais cela demande deux colonnes : la liste des valeurs et la liste de leur rangs...
Donc si quelqu'un connait une forumule permettant d'avoir directement dans une cellule le deuxième plus grand nombre d'une plage de cellules...

Merci
A+
Philoo

 

[g]

=GRANDE.VALEUR(X1:Y2;2)
Cette fonction recherche la deuxième plus grande valeur dans une plage X1:X2.
Bonne journée.

 

[philoo]

super ! merci beaucoup !!

 

[williamss]

Re : 2ème maximum

Bonjour,

Je remonte ce vieux post car je suis surpris quand utilisant GRANDE.VALEUR(....) que je retrouve les 2 mêmes résultats pour les 2 plus grands même si....

Car à la colonne 48ème colonne j'ai les numéros de 1, 2, 3, jusqu'à....49. Dans la colonne E$855:E$903 le plus grand chiffre et le 2ème plus grand chiffre sont 12 mais l'un est à la 889ème ligne et l'autre à la 892ème ligne. Avec la formule =RECHERCHEV(GRANDE.VALEUR(E$855:E$903;1);E$855:$AZ$903;48;0) cela me donne comme résultat le chiffre à la889ème ligne de la 48ème colonne. Et voilà qu'avec la formule =RECHERCHEV(GRANDE.VALEUR(E$855:E$903;2);E$855:$AZ$903;48;0) qui devrait me donner suivant le 2ème plus grand chiffre de la colonne E$855:E$903 (toujours le chiffre 12) le numeros de la ligne 892ème à la 48ème colonne après la colonne E$855:E$903. Pourtant cela me donne le même résultat que si je mets =RECHERCHEV(GRANDE.VALEUR(E$855:E$903;1);E$855:$AZ$903;48;0) donc en se basant toujours sur la 889ème ligne et non la 892ème ligne.

Pourquoi cela reprends en compte la même ligne (889ème) pour le 2ème plus grand chiffre comme pour le plus grand chiffre et non la 892ème ligne qui est la ligne qui a à nouveau le chiffre le plus grand (égale au plus grand donc 12) ??

Merci en espérant avoir été assez clair

Williams

 

[Mytå]

Re : 2ème maximum

Salut le forum

La fonction RECHERCHEV va toujours retourner la première valeur trouvée dans la colonne.

Regarde le lien suivant : Recherchev avec résultats multiples

Mytå

 

[williamss]

Re : 2ème maximum

Salut le forum

La fonction RECHERCHEV va toujours retourner la première valeur trouvée dans la colonne.

Regarde le lien suivant : Recherchev avec résultats multiples

Mytå

Apres avoir regardé et cherché à modifier la formule je n'y suis pas arrivé

Quelle formule pourrait résoudre ce problème (sans insérer de nouvelle colonne) ?

merci

Williams

 

[hbenalia]

Re : 2ème maximum

Bonjour à tous,

Une solution avec la formule:

=GRANDE.VALEUR(E$855:E$903;NB.SI(E$855:E$903;GRANDE.VALEUR(E$855:E$903;1))+1)

Cordialement

 

[hbenalia]

Re : 2ème maximum

Bonjour à tous,

Si on veut extraire la liste des maximums dans la plage E$855:E$903 sans doublons et par ordre décroissant, on pourra saisir (en $H$2 par exemple) la formule matricielle (A valider par CTRL+MAJ+ENTREE):

=GRANDE.VALEUR(E$855:E$903;SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))+1)

avec en H1 un vide ou contenant la première plus grande valeur

Pour éviter les erreurs de la formule en recopiant vers le bas, la formule s'écrira:

* En 2003 :

=SI(GRANDE.VALEUR(E$855:E$903;SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))+1);"";GRANDE.VALEUR(E$855:E$903;SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))+1))

Ou

=SI(SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))>=NBVAL(E$855:E$903);"";GRANDE.VALEUR(E$855:E$903;SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))+1))

* En 2007 et + :

=SIERREUR(GRANDE.VALEUR(E$855:E$903;SOMMEPROD(1*(E$855:E$903=TRANSPOSE($H$1:H1)))+1);"")

NB: Valider toutes ces formules par CTRL+MAJ+ENTREE

Cordialement

 

[mapomme]

Re : 2ème maximum

Bonjour williamss,

Pas certain d'avoir tout compris.Un essai avec deux formules matricielles donnant pour la Nième grande valeur de Tablo:

soit le numéro de ligne sur la feuille correspondant à la Nième grande valeur de Tablo (position absolue):

=PETITE.VALEUR(SI(Tablo=GRANDE.VALEUR(Tablo;F6);LIGNE(Tablo);"");F6-NB.SI(Tablo;">" & GRANDE.VALEUR(Tablo;F6)))

soit la position de la Nième grande valeur de Tablo au sein de Tablo (position relative):

=PETITE.VALEUR(SI(Tablo=GRANDE.VALEUR(Tablo;F6);LIGNE(Tablo);"");F6-NB.SI(Tablo;">" & GRANDE.VALEUR(Tablo;F6)))+1-LIGNE(Tablo)

Avec l'une ou l'autre de ces valeurs, on peut ensuite utiliser une fonction de type Index ou Decaler pour aller rechercher des valeurs au sein de la ligne repérée par le résultat des formules matricielles ci-dessus.

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

edit: v2 avec un exemple d'utilisation

 

[williamss]

Re : 2ème maximum

Comme je ne suis pas sûr que vous ayez compris voici un fichier brouillon .

Dans ce fichier on nous dit combien de fois on a 2 chiffres qui se sont retrouvés ensembles. Par exemple le chiffre 1 c'est retrouve 10 fois avec le 2, le chiffre 3 c'est retrouvé 9 fois avec le 1 etc.

Je cherche une formule pour savoir pour chaque chiffre de la colonne B (B5 à B53) quel est le chiffre de la ligne 4 (K7 à AY4) qu'on retrouve le plus et qu'on retrouve le plus en 2ème position.

Merci

Williams

 

[mapomme]

Re : 2ème maximum

Bonsoir williamss,

Il faut m'expliquer longtemps !
Stop, je crois que j'ai pigé.

Et en cas d'ex-aequoe, que faire ? (deux fois ou plus la même plus grande valeur ou sinon deux fois ou plus la seconde plus grande valeur)

 

[mapomme]

Re : 2ème maximum

(re) Bonsoir williamss,

Un essai dans le fichier joint.

C'est une adaptation de la 2eme formule matricielle du post de 9h03 en remplaçant LIGNE par COLONNE et en affectant à F6 la valeur 1 pour le nombre le plus fréquent ou la valeur 2 pour le deuxième nombre le plus fréquent.

Pour le plus fréquent (formule matricielle colonne BA en ligne 5) à copier vers la bas

=PETITE.VALEUR(SI($C5:$AY5=GRANDE.VALEUR($C5:$AY5;1);COLONNE($C5:$AY5);"");1-NB.SI($C5:$AY5;">" & GRANDE.VALEUR($C5:$AY5;1)))+1-COLONNE($C5:$AY5)

Pour le deuxième le plus fréquent (formule matricielle colonne BB en ligne 5) à copier vers la bas

=PETITE.VALEUR(SI($C5:$AY5=GRANDE.VALEUR($C5:$AY5;2);COLONNE($C5:$AY5);"");2-NB.SI($C5:$AY5;">" & GRANDE.VALEUR($C5:$AY5;2)))+1-COLONNE($C5:$AY5)

 

[williamss]

Re : 2ème maximum

Merci Mapomme,

j'allais justement te répondre pour mieux t'expliquer.

Tes 2 formules sont justement ce que je cherchais

Williams

 

[benouille]

Re : 2ème maximum

Bonjour, je ne sais pas si je publie au bon endroit mais ce post se rapproche au plus de ma question.

J'obtient une courbe sur excel possédant 2 maximums. Lorsque que j'applique la formule =MAX(X1:X2) j'obtient mon premier maximum. Jusque là tout va bien
J'aimerais ensuite connaitre mon deuxième maximum (et c'est là que ça se complique). Bêtement j'ai essayé la formule qui a été donné en solution à ce post, c'est à dire =GRANDE.VALEUR(X1:Y2;2). Mais comme il fallait s'y attendre cette formule ma donné la valeur juste après mon premier maximum, le point juste après mon maximum lors de la décroissance de ma courbe.

Donc ma question est la suivante: Y a t'il une formule (et non visuellement) pour obtenir ce deuxième maximum sur ma courbe ?

Mon objectif final étant de vouloir connaitre chaque maximum que ma courbe prend pour n'importe qu'elle courbe que je traiterais.

Merci beaucoup

Benouille

 

[mapomme]

Re : 2ème maximum

Bonsoir benouille et bienvenue sur XLD ,

Bravo pour avoir recherché un post dans le forum se rapprochant de votre question

Dans le fichier joint, une formule donnant les maximum d'une colonne de nombres. Si cela ne correspond pas à votre cas, veuillez attacher à votre post initial un fichier décrivant exactement votre problème.

La formule en D2 est une formule matricielle (à valider par Ctrl+Maj+Entrée) à tirer vers le bas:

=SIERREUR(PETITE.VALEUR(SI(($A$3:$A$28>$A$2:$A$27)*($A$3:$A$28>$A$4:$A$29);$A$3:$A$28);C2);"")

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

Pour joindre un fichier:
Quand vous rédigez un nouveau message ou quand vous modifiez un de vos messages, passez en mode avancé et cliquez sur 'Gérer les pièces jointes' ou bien cliquez directement sur l'icone 'Trombone'.
Choisissez vos fichiers (boutons Choisir un fichier), cliquez sur envoyer (bouton envoyer) pour les charger, quand ils sont chargés (les noms des fichiers s'affichent en couleur) refermez la fenêtre (bouton Fermer cette fenêtre) puis cliquez sur 'envoyez...' ou 'enregistrer les changements'.

nb: dans votre profil, vous pouvez indiquer quelle version d'Excel vous utilisez.