Localiser les sommets d'une courbe - partie 2/2

Le précédent billet présentait la problématique qui consiste à compter les sommets d’une courbe et à en déterminer les coordonnées. Comme promis, je poursuis ici cette réflexion, en proposant regarder de près la courbe suivante :

Cette courbe se trouve dans le classeur Excel téléchargeable ici, de même que la mise en application du raisonnement à venir.

Ces données sont inventées pour l’exercice mais il pourrait s’agir de relevés climatologiques par exemple - températures ou pluviométrie. De manière plus marquée encore que dans les cas théoriques ci-dessus, la courbe montre la présence de pics intermédiaires qui font partie de zones uniques (les massifs montagneux de tout à l’heure) et dont on souhaite ne pas tenir compte.


Les données-source occupent les colonnes A (abscisses) et B (ordonnées), la série allant de la ligne 2 à la ligne 1000. Sur toute la hauteur de la colonne C apparaît la valeur du seuil, seuil référencé dans la cellule F2, pour qu’on puisse le modifier facilement.

Je fixe ici ce seuil à 250 (la ligne en hachure rouge) : on a donc 3 massifs.

L’idée est de raisonner séparément sur chaque zone, c'est à dire entre les points de passage par le seuil pris deux par deux. Un sommet est alors la valeur maximale sur la portion de courbe correspondant à la zone entre un passage montant et le passage descendant qui suit !

Pour isoler les zones, il faut repérer ces passages. Si on est perfectionniste, on relève tous ces passages, ceux en montant et ceux en descendant (il y en a six). Mais en réalité on n'a pas besoin d'être aussi perfectionniste. Il suffit de repérer les passages dans un sens, les descendants par exemple puisque, entre un descendant et le montant qui suit il ne peut pas y avoir de sommet ! On travaillera donc simplement entre un passage descendant et le suivant.

Plongeons dans la feuille Excel.

Je fais dans la colonne D un test de passage descendant par le seuil. Ceci consiste à tester, sur une ligne lambda, “si la valeur dans la colonne B sur cette même ligne est inférieure à 250 ET la valeur sur la ligne précédente supérieure (ou égale) à 250”.

La fonction Excel ET renvoie la valeur VRAI si ces deux conditions sont vérifées, FAUX sinon. Le seuil étant référencé dans F2, ça donnera ceci pour la cellule D3 :

=ET(B2>=$F$2;B3<$F$2)

Et on recopie cette formule jusqu’en bas du tableau. Voilà : une valeur VRAI se trouve sur chaque ligne où la courbe vient de traverser le seuil en descendant.

Maintenant que sont identifiés ces passages , on va se pencher séparément sur chaque zone.

On fait ce travail dans la grille située au-dessus du graphe.

L’idée est d’abord de délimiter la zone : définir sa ligne de début puis sa ligne de fin, pour pouvoir construire l’adresse de la plage de cellules qui nous intéresse. Une fois qu’on aura fait ce repérage, on cherchera à identifier son sommet , c’est à dire l’ordonnée maximale atteinte par la courbe puis l’abscisse où se trouve cette ordonnée  maximale.


Travaillons sur la première zone :

- en I3 : la ligne de début de la zone. C’est une info que je saisis (du reste, le seuil et ce numéro de ligne sont les deux seules infos à saisir !)

- en J3 : le nombre de lignes couvertes depuis la ligne de début jusqu'au premier passage descendant qu’on rencontre. On calcule ça avec la fonction EQUIV, en lui passant :

- la valeur VRAI (la marque de passage descendant)

- à chercher dans la plage qui commence à la ligne qu'on vient de définir en I3 (on se sert de la fonction INDIRECT pour ça) et qui se termine en D1000 :

=EQUIV(VRAI;INDIRECT("D"&I3):D1000;0)

Résultat : il y a 297 lignes couvertes du début de la zone jusqu’au passage par le seuil.

- en K3 : le numéro de la ligne de fin. C’est la somme des deux nombres précédents, –1 puisqu’EQUIV a aussi compté la ligne de départ

=I3+J3-1

- en L3 : l’adresse de la zone qu’on vient de parcourir. C’est une simple concaténation des lettres de colonne (“B”, qu’on pourrait éventuellement variabiliser, mais bon...) et des numéros de ligne, avec un double-point au milieu :

="B"&I3&":B"&K3

Voilà que la zone est délimitée !
À partir de là, on cherche le pic lui-même :

- en M3 : l’ordonnée du sommet. Ca c’est facile : comme on a l'adresse de la zone en L3, la zone elle-même est donnée par INDIRECT et donc il suffit d'en prendre le MAX :

=MAX(INDIRECT(L3))

- en N3 : le nombre de lignes couvertes depuis la ligne de début de zone jusqu’au sommet. C'est la même idée que ce qu’on a fait plus haut pour localiser le  passage de seuil, sauf qu’on cherche ici l’ordonnée du sommet et qu’on regarde dans la colonne B :

=EQUIV(M3;INDIRECT("B"&I3):B1000;0)

- en O3 : le numéro de la ligne du sommet. On additionne pour ça la ligne du début de zone et le nombre de lignes qu’on vient de calculer (–1, encore une fois).

=I3+N3-1

- et enfin, en P3 : l’abscisse du sommet. Un dernier coup d’INDIRECT, sur la bonne colonne et avec le numéro de ligne qu’on vient de déterminer :

=INDIRECT("A"&O3)

Super, on a tout pour le premier sommet !


Maintenant, travaillons sur le deuxième pic :
- en I4 : la ligne de début de zone. C'est tout simplement la ligne de fin de la zone précédente, +1.
- et toute la suite c'est pareil.

Et pour les pics suivants, il n'y a plus qu'à recopier vers le bas. J’ai dimensionné la grille sur 5 pics mais on peut en prévoir autant qu’on veut Et on peut déplacer le seuil, pour voir...


Tant qu’à faire, on peut s’offrir deux petits bonus :
- le nombre de sommetsest facile à calculer avec la fonction NB.SI et en couvrant la grille à la recherche des valeurs qui ne sont pas #N/A.
- ajoutons au graphique une série de données “sommets”, sans trait et avec une marque adéquate pour pointer les sommets identifiés, et voilà !