Discussion :

[mauriceguillier]

Bonjour à tous,

Soit un tableau de 5 colonnes et 10 lignes. C'est seulement un exemple, tout autre composition conviendrait.

En première colonne, des dates. En colonne suivantes, des heures. À la croisée, des relevés de températures.

Mon objectif, et donc ma question : repérer la température maximum et la date correspondante, deux données à copier dans deux cellules contiguës, par exemple, et respectivement, en B11 et A11..... Exemple encore !

Je sais bien récupérer la température maximum par la fonction max () mais je n'arrive pas à mettre la date correspondante dans la cellule contiguë. J'ai essayé avec recherchev mais je me retrouve avec une réponse N/A.

Je vous serais gré de votre aide et je vous en remercie d'avance.

Cordialement,
Maurice GUILLIER

[Phanloga]

Je me suis précipité dans ma réponse.
Le problème ne tient pas simplement à l'utilisation de RECHERCHEV().
Je passe la main...

[Philippe Tulliez]

Bonjour,
Une solution parmi d'autres.
Avec une liste de données de 3 colonnes et plusieurs lignes nommée mesReleve avec en colonne
1 - Les dates
2 - Les heures
3 - Les températures
La colonne contenant les températures nommée mesReleve_Temperature
La formule ci-dessous renvoie la date de la ligne qui contient la température maximum.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX( mesReleve ; SOMMEPROD( (MAX(mesReleve_Temperature)=mesReleve_Temperature) * LIGNE(mesReleve)  ) - LIGNE(mesReleve) + 1 ; 1 )

Le chiffre en rouge est le n° de colonne des dates donc pour obtenir l'heure en plus il suffit de faire la concaténation ou l'addition de la même formule en modifiant la valeur de cet argument par 3.
La partie de la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD( (MAX(mesReleve_Temperature)=mesReleve_Temperature) * LIGNE(mesReleve)  ) - LIGNE(mesReleve) + 1

renvoie le n° de la ligne - 1

[EDIT]
Il y avait une légère erreur dans ma formule que je viens de corriger.

[mauriceguillier]

Bonjour à tous, et en particulier à Philippe,

D'abord, merci à Philippe pour s'être penché sur ma demande. Mais j'avoue ne pas avoir bien saisi comment je vais pouvoir "traduire" sur mon tableau. Notamment parce que le tableau déjà construit contient plus d'une année de relevés horaires de températures .... et je ne voudrais devoir tout reprendre

Et je pense surtout ne pas avoir bien expliqué ma demande et j'en reviens à la maxime de Philippe.... selon Boileau.

Pour tenter de mieux expliquer ma demande, je joins un fichier d'exemple ressemblant au mien.

Dans celui-là, je cherche à obtenir la valeur de la "date" de la colonne A au croisement de la ligne qui contient la valeur la plus élevée de la plage de "températures" B2:E2. Je recherche donc à obtenir en H2 (simple exemple) la valeur "s" de la cellule A16 qui correspond à la valeur "95" de la cellule C16, la "température" la plus élevée de la plage B2:E2.

Ai-je réussi à mieux m'exprimer ? Pas gagné ! En tout cas, si vous pouviez me donner ce petit coup de main....

Merci d'avance ; cordialement,
Maurice
Excel 2013

[papouclo]

Bonjour,
Ecrire de vraies dates dans les cellules de la plage A2:A20.
Une fois cela fait, écrire en H2 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= SOMMEPROD(MAX(A2:A20*(B2:E20=I2)))

Cordialement
Claude

[Philippe Tulliez]

Bonjour Maurice,
Pour reprendre ton exemple
Les plages nommées
mesReleve =Feuil1!$A$2:$E$20
mesReleve_Temperature =Feuil1!$B$2:$E$20
Le n° de colonne de la formule que je t'ai proposée
La formule que j'ai proposé plus haut

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX( mesReleve ; SOMMEPROD( (MAX(mesReleve_Temperature)=mesReleve_Temperature) * LIGNE(mesReleve)  ) - LIGNE(mesReleve) + 1 ; 1 )

Le n° en rouge est le n° de la colonne de la valeur à renvoyer
[EDIT]
La proposition de Claude est plus courte et plus intéressante que la mienne mais évidemment j'étais parti sur une solution avec retour d'informations éventuellement alphanumériques.

[mauriceguillier]

Rebonjour à tous deux,

Effectivement, ça fonctionne à merveille avec la formule de Claude. Il va falloir que j'approfondisse cette fonction SOMMEPROD que je découvre à l'occasion.

Maintenant, il me reste surtout à adapter cette proposition à mon tableau. En effet, celui-ci "descend" toutes les dates les unes au-dessous des autres et il faut donc repérer les changements de mois puisque que ma recherche de T° maximum se fait mois par mois pour chacune des années.

Je vois déjà la difficulté de bien "appeler" les plages de recherches correspondantes...... .... Peut-être devrais-je refaire appel à vos compétences !

Encore merci,
Cordialement,
Maurice GUILLIER

[papouclo]

Rebonjour,
L'emploi de la fonction SOMMEPROD est ici un emploi détourné de cette fonction pour éviter d'écrire vraiment une formule matricielle ;
j'aurais sans doute mieux fait de proposer la formule matricielle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MAX(A2:A20*(B2:E20=I2))

à valider par Ctrl + Maj + Entrée. Cela aurait été plus compréhensible.

Pour répondre à ton nouveau problème, (recherche mois par mois), j'ai construit dans le classeur joint un tableau plus grand portant sur des dates de deux mois.
La plage des dates est A2:A40; celle des mesures de T° est B2:B40.
En G2:G13, on a le 1er jour de chaque mois de l'année 2013 (sous le format mmmm aa).
En I2, la formule matricielle permettant de récupérer le maximum pour le mois lu en G2 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MAX(($B$2:$E$40)*(MOIS($A$2:$A$40)=MOIS($G2))*(ANNEE($A$2:$A$40)=ANNEE($G2)))

à valider par Ctrl + Maj + Entrée.
En H2, la formule matricielle permettant de récupérer la date correspondant à ce maximum est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MAX($A$2:$A$40*(MOIS($A$2:$A$40)=MOIS($G2))*(ANNEE($A$2:$A$40)=ANNEE($G2))*($B$2:$E$40=$I2))

à valider bien sûr par Ctrl + Maj + Entrée.
Ces deux formules peuvent se recopier vers le bas.
Cordialement
Claude

[mauriceguillier]

Bonjour Claude,

J'ai vu ce matin la réponse très efficace que tu as postée hier.

Je t'en remercie vivement. Je l'ai, à peine, adaptée pour tenir compte des données "températures" encore et normalement absentes car nous ne sommes pas encore, par exemple, en février 2014 !

J'ai également appliqué cette formule pour les minimum mensuels. Et là, bizarre ! En effet, ce que j'ai "écrit" fonctionne normalement pour certains mois et pas pour d'autres. Le résultat est "0" et la date est 0/1. C'est correct, en 2013, de janvier à mars, incorrect de avril à octobre et à nouveau correct de

Pourtant, lorsque j'applique la fonction MIN() au mois considéré, j'obtiens la bonne réponse. J'ai regardé les formats de cellules (tous ?.....) et là aussi, je ne constate pas de distorsions. Du coup, je ne saisis pas ce qui n'est pas conforme !

Pourrais-tu regarder mon fichier de travail et me dire le problème que tu découvres ? C'est sur la feuille "Températures!", vers les colonnes AL, et ligne 3.

Je t'en remercie d'avance.

Très cordialement,
Maurice

[mauriceguillier]

Oups ! Le fichier pourtant bien téléchargé n'est pas présent. Ai-je bien validé ? Je le renvoie.

[papouclo]

Bonjour Maurice,
Il est normal que cette formule qui fonctionne correctement pour la fonction MAX pose des problèmes pour la fonction MIN.
Dans la formule écrite en AO4 ou AQ4, le produit $E$2:$AB$731 * (MOIS($C$2:$C$731)=MOIS($AM4))*(ANNEE($C$2:$C$731) =ANNEE($AM4)) remplace la matrice $E$2:$AB$731 contenant tous les relevés de T° du tableau
par une matrice formée des relevés de T° lorsque le mois et l'année sont ceux recherchés et des zéros dans les autres cas.
Lorsque c'est pour chercher un maximum de T°, les zéros de cette nouvelle matrice ne gènent en rien.
Pour la recherche d'un minimum, s'il y a des T° négatives donc inférieures à zéro, la formule donnera bien le minimum attendu; mais si toutes les températures relevées durant le mois sont positives,
il ne faut pas s'étonner que la fonction MIN envoie 0 comme plus petit élément de la matrice.
Remarque : On aurait aussi des problèmes pour la fonction MAX si toutes les températures du mois étaient négatives ; le 0 renvoyé comme maximum serait aussi faux.

Ecrire en AQ4 la formule matricielle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(RECHERCHEV($AM4;$C$2:$E$731;3)="";"";MIN(SI((MOIS($C$2:$C$731)=MOIS($AM4))*(ANNEE($C$2:$C$731) = ANNEE($AM4)); $E$2:$AB$731 ;AO4)))

à recopier vers le bas dans la colonne.
Tout devrait rentrer dans l'ordre.
Cordialement
Claude

[mauriceguillier]

Bonsoir Claude,

Je venais directement sur le forum pour ajouter que je trouvais un début de piste, à savoir que si je mettais une température négative dans les mois à 0, la formule fonctionnait à nouveau bien mais sans vraiment comprendre pourquoi. C'était juste pour donner un élément d'appréciation.....

qui corrobore tout à fait la réponse que tu viens de me donner mais cette dernière au moins est motivée jusqu'au tréfonds ! Dont acte !.... et dont réussite !

Merci de ton efficacité et de ta célérité. GÉANT !

Bonne soirée puis bon dimanche à toi,
Très cordialement,
Maurice

[mauriceguillier]

Je reviens pour un point de compréhension. Pourquoi le dernier code fait-il référence, en bout de code, pour la cellule AQ4, à la cellule AO4 ? En effet, cette dernière cellule contient le résultat de la formule MAX.

Juste pour comprendre ce que je fais à moins que je n'ai pas été capable de comprendre tes explications dans le message précédent. Cela est bien possible malheureusement pour moi

Merci de ton obligeance,
Maurice

[papouclo]

Re,

Lorsqu'on cherche un maximum, on se "débarrasse" des valeurs gênantes (les températures correspondant à de "mauvais" mois) en les remplaçant par des minorants (généralement des zéros obtenus par calcul -
ici, le produit $E$2:$AB$731 * (MOIS($C$2:$C$731)=MOIS($AM4))*(ANNEE($C$2:$C$731) ) parce qu'on est généralement sûr que ces zéros n'interviendront pas dans la recherche du maximum.

Lorsqu'on cherche un minimum, il ne faut pas remplacer les températures (ou autres données) gênantes par des zéros qui pourraient se révéler être la plus petite des valeurs comparées mais plutôt par
des majorants ne pouvant pas interférer dans cette recherche de minimum. J'aurais pu te donner une formule du genre :
SI("bon mois"; température relevée ; Sinon 1000) mais en mettant SI(""bon mois"; température relevée ; Sinon Maximum du mois), on se doute que cette température maximum n'interfèrera pas
dans la recherche de la température du mois la plus basse.

J'espère ne pas avoir été trop obscur.
Cordialement
Claude

[mauriceguillier]

Re,

Non, merci; la démarche est bien comprise maintenant même si la réflexion à entamer a priori n'est pas évidente à supputer !

C'est très instructif et je t'en remercie.

@+
Maurice