Valeurs min et max d'une colonne excel Fermé

Question

Bonjour, 



J'ai des fichiers excel composés de deux colonnes (A, B) qui comportent des valeurs périodiques. Pour chaque fichier, je veux chercher toutes les valeurs min et max de la deuxième colonne B. Ensuite, pour chaque couplet (min, max) successif une quantité physique qui a pour formule : quantité = (valeur max - valeur min)*100 / (valeur colonne A correspondant à max - valeur colonne A correspondant à min). Le résultat sera mis dans une nouvelle colonne C.

Voici un extrait de mon fichier :
Colonne A Colonne B
0	0,208
0,001	0,117
0,001	0,208
0,004	0,189
0,005	0,353
0,008	6,769
0,01	12,442
0,012	17,535
0,022	27,631
0,026	29,861
0,027	28,411
0,032	33,866
0,033	40
0,041	40
0,035	36,621
0,039	36,44
0,041	38,488
0,037	37,165
0,036	32,652
0,034	25,891
0,032	26,254
0,026	13,947
0,026	14,889
0,021	6,479
0,015	-2,366
0,008	-6,698
-0,001	-13,096
-0,006	-17,773
-0,011	-22,848
-0,021	-28,068
-0,029	-32,11
-0,038	-34,793
-0,045	-37,239
-0,056	-38,327
-0,06	-38,816
-0,059	-38,363
-0,057	-36,877
-0,055	-34,466
-0,057	-31,204
-0,051	-27,107
-0,05	-22,522
-0,043	-17,193
-0,039	-11,773
-0,033	-5,683
-0,027	0,334
-0,02	6,225
-0,016	12,261
-0,008	17,698
-0,003	23,154
0,005	27,849
0,012	31,601
0,015	34,754
0,021	37,074
0,024	38,416
0,026	38,633
0,026	38,271
0,024	36,585
0,02	33,087
0,018	28,048
0,014	27,559
0,01	25,076
0,004	16,357
-0,003	8,998
-0,008	3,76
-0,013	-1,369
-0,015	-7,532
-0,02	-13,332
-0,029	-18,407
-0,029	-23,845
-0,036	-28,358
-0,043	-32,364
-0,051	-35,373
-0,058	-37,312
-0,065	-38,889
-0,069	-38,653
-0,069	-38,345
-0,068	-36,768
-0,065	-34,358
-0,061	-30,914
-0,057	-26,854
-0,052	-22,232
-0,048	-17,03
-0,045	-11,393
-0,037	-5,339
-0,032	0,769
-0,026	6,769
-0,02	12,823
-0,013	18,26
-0,009	23,354
-0,003	27,957
0	31,691
0,007	34,863
0,009	37,22
0,013	38,343
0,016	38,978
0,013	38,289
0,017	36,821
0,013	34,247
0,013	33,141
0,01	29,842
0,007	21,759
0,002	16,593
-0,002	9,361
-0,006	3,216
-0,011	-3,037
-0,017	-6,1
-0,026	-13,713
-0,03	-19,259
-0,039	-24,171
-0,042	-28,757
-0,052	-32,092
-0,058	-35,119
-0,065	-37,33
-0,072	-38,744
-0,08	-38,798
-0,081	-38,454
-0,079	-37,022
-0,076	-34,484
-0,073	-31,167
-0,067	-27,053
-0,064	-22,34
-0,057	-16,975
-0,054	-11,52
-0,049	-5,393
-0,043	0,769
-0,035	6,841
-0,03	12,714
-0,022	18,351
-0,013	23,625
-0,006	28,302
0,001	32,036
0,006	35,28
0,011	37,546
0,012	38,543
0,013	39,34
0,015	38,833
0,012	36,911
0,01	33,957
0,009	34,356
0,009	29,462
0	22,049
-0,004	16,194
-0,008	14,001
-0,016	7,131
-0,02	0,425
-0,027	-6,118
-0,032	-12,081
-0,038	-17,32
-0,047	-23,029
-0,052	-27,633
-0,059	-32,255
-0,065	-35,264
-0,074	-37,366
-0,082	-38,78
-0,09	-39,578
-0,091	-38,762
-0,091	-37,221
-0,088	-34,72
-0,084	-31,53
-0,078	-27,47
-0,077	-22,775
-0,07	-17,465
-0,063	-11,828
-0,057	-5,701
-0,047	0,57
-0,038	6,461
-0,033	12,297
-0,024	17,898
-0,018	23,136
-0,009	27,686
-0,004	31,546
0,001	34,754
0,004	36,893
0,008	37,727
0,008	38,615
0,011	38,307
0,008	36,567
0,007	34,319
0,006	31,057
0,004	27,287
-0,004	19,638
-0,005	15,306
-0,01	11,917
-0,016	6,479
-0,025	-0,735
-0,031	-6,263
-0,041	-12,136
-0,049	-18,878
-0,055	-23,682
-0,064	-28,412
-0,07	-31,711
-0,081	-34,956
-0,091	-37,203
-0,098	-38,544
-0,104	-38,834
-0,106	-38,345
-0,105	-36,949
-0,103	-34,448
-0,102	-31,077
-0,096	-26,98
-0,092	-22,377
-0,085	-17,084

Merci d'avance.

Mike-31 · Answer

Salut,

Pas tout compris mais bon si tes valeurs sont en A1 à A202 et B1 à B202
regarde si cette formule correspond à tes attentes

=((MAX(B1:B202)-MIN(B1:B202))*100)/(MAX(A1:A202)-MIN(A1:A202))

Yac02 · Answer

Merci Mike-31, J'ai plusieurs max et plusieurs min et pour chaque max et min successif j'applique la formule. Donc, j'aurai plusieurs valeurs avec cette formule. J'espère que le problème est bien posé.

Mike-31 · Answer

Re,

Je n'avais pas contrôlé tes valeurs, effectivement avec plusieurs valeur ça change tout je te prépare une formule

Mike-31 · Answer

Re,

Regarde cette formule matricielle, pour écourter la formule il vaut mieux nommer les deux plages.
La première plage je l'ai nommée Col_A et la deuxième Col_B
Colle cette formule dans une cellule

=((MAX(Col_B)-MIN(Col_B))*100)/(SI(LIGNES($1:1)<=NB.SI(Col_B;MAX(Col_B));INDEX(Col_A;PETITE.VALEUR(SI(Col_B=MAX(Col_B);LIGNE(INDIRECT("1:"&LIGNES(Col_B))));LIGNES($1:1)));""))-MIN(Col_A)

Et valide la en cliquant en même temps sur les touches Ctrl, Shift et Entrée si tu fais bien la formule se mettra entre crochet {}
Comme cela
{=((MAX(Col_B)-MIN(Col_B))*100)/(SI(LIGNES($1:1)<=NB.SI(Col_B;MAX(Col_B));INDEX(Col_A;PETITE.VALEUR(SI(Col_B=MAX(Col_B);LIGNE(INDIRECT("1:"&LIGNES(Col_B))));LIGNES($1:1)));""))-MIN(Col_A)}
Ensuite incrémente-la vers le bas sur plusieurs lignes pour prendre en compte les valeurs identiques

Colonne B tu as deux valeurs max 40 avec en face la première colonne A 0,033 ce qui je pense doit te donner 795780,106
Et pour la deuxième valeur max 40 en face tu as 0,041 ce qui te donne 194092,7889

Contrôle les deux calculs voir si le résultat est bon. Dans l'affirmative, si tu as besoin d'un modèle fais signe

Yac02 · Answer

Salut Mike-31, je pense que ta formule est bien intéressante. J'ai renommé les deux colonnes par Col_A et Col_B (voir l'exemple), puis j'ai collé ta formule dans une cellule  appartenant à une troisième colonne, mais une fois que je clique sur sur les touches Ctrl, Shift et Entrée il m'affiche #NOM?

Si tu peux m'éclaircir les étapes, je serai très ravi. Je te fais savoir que j'ai plusieurs fichiers que je dois le traiter de la même façon.

Merci beaucoup.

Col_A	Col_B	
0	0,208	#NOM?
0,001	0,117	#NOM?
0,001	0,208	#NOM?
0,004	0,189	#NOM?
0,005	0,353	#NOM?
0,008	6,769	#NOM?
0,01	12,442	#NOM?
0,012	17,535	#NOM?

Yac02 · Answer

Salut Vaucluse et merci pour ton aide,

En attendant Mike, J'ai supprimé les deux noms Col_A et Col_B et j'ai écris la formule de cette façon :

=((MAX(B1:B2724)-MIN(B1:B2724))*100)/(SI(LIGNES($1:1)<=NB.SI(B1:B2724;MAX(B1:B2724));INDEX(A1:A2724;PETITE.VALEUR(SI(B1:B2724=MAX(B1:B2724);LIGNE(INDIRECT("1:"&LIGNES(B1:B2724))));LIGNES($1:1)));""))-MIN(A1:A2724)

au lieu de :

=((MAX(Col_B)-MIN(Col_B))*100)/(SI(LIGNES($1:1)<=NB.SI(Col_B;MAX(Col_B));INDEX(Col_A;PETITE.VALEUR(SI(Col_B=MAX(Col_B);LIGNE(INDIRECT("1:"&LIGNES(Col_B))));LIGNES($1:1)));""))-MIN(Col_A) 

Il m'affiche #DIV/0!

Vaucluse · Answer

Alors vous avez des valeurs nulles ou des cellules vides dans votre tableau qui sont renvoyées dans la partie diviseur par PETITE.VALEUR , là on va laisser Mike traiter le sujet, je n'en suis pas capable sur cette formule.
crdlmnt

Vaucluse · Answer

... mettez lui plutôt un fichier avec les deux colonnes remplies sur: 
https://www.cjoint.com/ 
et revenez placer ici le lien donné par le site. 
crdlmnt 
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?

Yac02 · Answer

Voici le lien :

http://cjoint.com/?BAztSSogLjs


Merci Vaucluse

Mike-31 · Answer

Re,

Je me suis absenté pour l'après midi, je viens de récupérer tes valeurs et adapté la formule.
Récupère le fichier joint et on en reparle

https://www.cjoint.com/?BAzvQ2Z1mCM

je vais supprimer un de tes post avec la liste des données qui allonge inutilement la discussion

Yac02 · Answer

Salut Mike,

J'espère que tu vas bien. Je t'informe que la recherche des valeurs min et max se fait pour chaque sinusoïde. Avec un calcul manuel, je trouve 16 max et 16 min et par conséquent, on applique 16 fois la formule pour chaque max et min de chaque sinusoïde. Voici le lien du fichier avec mes calculs :

http://cjoint.com/?BAAkCgJe0Bm

Le résultat de la formule est mis, à chaque fois, dans la colonne C à côté de min.

A+

Mike-31 · Answer

Re,

Je viens de regarder rapidement ton fichier, mais à première vue le problème est de savoir quel critère logique défini chaque plage de tes sinusoïdes, si j'ai bien compris ton tableau,
la première plage A1:B35 soit 35 lignes
la deuxième A36:B74 39 lignes
la troisème A75:B115 41 lignes etc ...

Yac02 · Answer

Salut Mike,

La plage de chaque sinusoïde n'est pas constante sur la même colonne et varie aussi d'un fichier à un autre. Je souhaiterai trouver une astuce pour trouver tous les max et les min des sinusoïdes.

P.S.: J'ai beaucoup de fichiers que je dois tous les traiter de la même façon.

Mike-31 · Answer

Re,

La seule astuce et renseignement logique qu'il faut arriver à donner à Excel, c'est la définition des plages, ou commence chaque sinusoïde et ou ça se termine quel est le point commun.
même manuellement comment définis tu ces plages !

Yac02 · Answer

A chaque sinusoïde, je cherche le max (la plus grande valeur positive) et le min (la plus petite valeur négative). Puisque le phénomène est périodique, les cycles se répètent avec la même façon.

Le problème est que les max et les min changent un petit peu d'un cycle à un autre et il faut prendre la va leur correspondante de la colonne A.

Mike-31 · Answer

Re,

Si on suit ta logique, sur ton dernier fichier, le deuxième calcul en C74 serait faux, en effet tu as pris comme valeur min la cellule A74 avec -0,065 alors qu'en A75 et A76 nous avons -0,069 donc inférieur de 0,004 ce qui donnerait comme formule =(B55-A75)*100/(A55-A75) et le résultat serait 40738,94737
peux tu le contrôler et me donner des explications

Yac02 · Answer

Salut,

Pour la recherche des valeurs Max et Min je m'intéresse seulement à la colonne B. Pour chaque sinusoïde, je cherche dans la colonne B la valeur Max et Min puis j'utiliserai la colonne A pour prendre les valeurs correspondantes au Max et Min trouvés.

La valeur A74 correspond au Min du 2ème cycle. J'espère que tu m'as bien compris.

Yac02 · Answer

Salut de nouveau,

Comme tu m'as précisé qu'il faut définir la plage où commence chaque sinusoïde, j'ajoute une colonne E au fichier qui montre le nombre de cycles ou de sinusoïdes, c-à-d on peut effectuer la recherche du Min et Max d'une sinusoïde entre deux cycles (1 et 2 , 2 et 3 , ....). Cette procédure sera généralisée pour tout le nombre de cycles. Le lien du nouveau fichier est :

http://cjoint.com/?BACkw0jW6AF

N.B : La recherche du Min et Max se fera sur la colonne B seulement puis on prend leur correspondant de la colonne A.

Merci pour ton aide.

Mike-31 · Answer

Salut, 

J'ai écrit plusieurs formule et code en VBA sans pouvoir trouver un critère logique qui séparerait tes plages surtout en fin  de colonne B ou tes valeurs subissent des variations importantes dont les montants sont aléatoires 

pas de problème pour les neuf premières soit jusqu'à la ligne 393 après c'est pas gagné. je cherche encore voir si je trouve un code logique 
A+ 
Mike-31 

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Valeurs min et max d'une colonne excel

19 réponses

Discussions similaires