Créer un histogramme - Suite et fin

Poursuivons nos efforts pour tracer le plus facilement possible des histogrammes dans Excel. Cette semaine je voudrai lancer un concours de formule ! Qu'elle est la formule la plus simple pour extraire les bornes supérieures et inférieurs des classes de valeurs. Effectivement les valeurs groupées par classes étant dans le tableur des chaînes de textes elles ne sont pas directement exploitables. Dans le tableau 1, j'ai réalisé l'extraction de ces bornes grâce à une formule de traitement de la chaîne. auriez vous mieux à proposer ?

Tableau 1
Classes	Borne Inf.	Borne Sup.	Effectifs
[1300-1400[	1 300	1 400	10
[1400-1500[	1 400	1 500	12
[1500-1600[	1 500	1 600	7
[1700-1800[	1 700	1 800	3
[1800-1900]	1 800	1 900	13

 =CNUM(GAUCHE(DROITE(A5;(NBCAR(A5)-CHERCHE("-";A5;1)));NBCAR(DROITE(A5;(NBCAR(A5)-CHERCHE("-";A5;1))))-1))

Voici la formule magique que j'ai entré ici dans la cellule c5, passons sur la fonction cnum() qui me permet simplement de m'assurer que ma valeur finale est bien un nombre et pas un libellé. Le point commun étant le séparateur de valeur, ici "-" j'ai cherché sa position grâce à la fonction cherche() puis j'ai extrait tous les caractères placées à droite de cette position. Le nombre de caractère placé à droite du séparateur, m'étant inconnue je l'ai soustrait à la longueur totale de la chaîne grâce à la fonction nbcar().  Jusque là, la manipulation est tout ce qu'il y a de plus classique.

 

 =DROITE(A5;(NBCAR(A5)-CHERCHE("-";A5;1))) = 1400[

Il reste donc à extraire le dernier caractère de cette chaîne, quelque puisse être ce caractère. en fait nous allons raisonner à l'envers en extrayant tous les caractéres placés à gauche de ce dernier caractére.

=GAUCHE(A5;NBCAR(A5)-1) = 1400

Il reste alors assez logiquement à positionner la première partie de la formule à la place des A5 de la seconde formule. Pour une extraction de borne inférieure, nous nous contenterons d’inverser le raisonnement : 

=CNUM(DROITE(GAUCHE(A5;CHERCHE("-";A5;1)-1);NBCAR(GAUCHE(A5;CHERCHE("-";A5;1)-1))-1))

Nous pouvons maintenant passer au graphique, pour cela j'ai du créer un nouveau tableau prenant en compte les bornes supérieurs et les effectifs. Toutefois vous pouvez noter la particularité de la première ligne ou il est rappelé la borne inférieur de la première classe de valeurs, car cette dernière n'étant pas nul elle n'apparaitrait pas dans le graphique.
Resta alors l'afichage de cette première valeur grâce au menu contextuel de l'axe des abscisses . Mise en forme de l'axe / Options d'axes / Intersections de l'axe vertical / au numéro de catégorie : (mettre alors la valeur 2 (0 étant considéré comme la première)). 

Tableau 2
Borne Sup.	Effectifs
1 300
1 400	10
1 500	12
1 600	7
1 800	3
1 900	13

Appliquons maintenant tous cela de maniére identique à des classes dont les amplitudes sont inégales. (Tableau 3). La récupération des bornes permettra le calcul de la fréquence (f_i), de l'amplitude (a_i = (borne Sup - Born Inf )), du centre de casse (c_i = (borne Sup + Born Inf ) /2) et de l'amplitude corrigée (h_i = fi / ai).

Tableau 3
Classes	Borne Inf.	Borne Sup.	ni	ci	ai	fi	hi
[1100-1400[	1100	1400	12	1 250	300	0,25	0,00083
[1400-1500[	1400	1500	12	1 450	100	0,25	0,00250
[1500-1600[	1500	1600	7	1 550	100	0,15	0,00146
[1600-1800[	1600	1800	4	1 700	200	0,08	0,00042
[1800-2000]	1800	2000	13	1 900	200	0,27	0,00135
		Total :	48			1

Mais pour tracer le graphique nous devrons appliquer l'astuce de l'article précédent. Génération d'un nouveau tableau (Tableau 4) en classes d'amplitude égales avec évaluation des n_i réparties dans ces classes.

Tableau 4		Borne Sup.	ni	ai	fi	hi
		1100
[1100-1200[	1100		4	100	0,08	0,00083
[1200-1300[	1200		4		0,08	0,00083
[1300-1400[	1300	1400	4		0,08	0,00083
[1400-1500[	1400	1500	12		0,25	0,00250
[1500-1600[	1500	1600	7		0,15	0,00146
[1600-1700[	1600		2		0,04	0,00042
[1700-1800[	1700	1800	2		0,04	0,00042
[1800-1900[	1800		6,5		0,14	0,00135
[1900-2000]	1900	2000	6,5		0,14	0,00135
		Total :	48		1

Merci de votre attention...

Partager |