Calcul de la médiane

A la suite de l’article précédent poursuivons nos travaux sur une autre caractéristique de tendance centrale, la médiane. La médiane d'un série est la  valeur qui partage cette série en deux séries aux effectifs égaux. Dans la première les valeurs inférieures à la médiane et les valeurs supérieurs dans la seconde série. Nous ne considérons bien sur que des données quantitatives et nous envisagerons dans cet article deux hypothèses :

Hypothèse 1 : Effectif impair sans valeur répétée

Soit ici un effectif de 5 chiffres, qu'il faut d'abord  classés par ordre croissant, puis déterminer la valeur de la position qui partage l'effectif total en deux sous effectifs égaux en appliquant la formule (n + 1) / 2. 
Ainsi il a été utilisé ici la fonction =MEDIANE(B2:B6) dans le tableau de gauche, et dans le tableau ci-dessous une détermination de la position grâce à la fonction =NB() puis la récupération de la valeur de cette position à l'aide de la fonction =INDEX( ).
Bien sur il convient de vérifier qu'il y a bien partage de l'effectif en deux parties égales.

Hypothèse 2 : Effectif pair sans valeur répétée

Quand l'effectif est pair, la médiane n'est pas une valeur de la série. Soit ici un effectif de 8 chiffres, qu'il faut d'abord  classés par ordre croissant, puis appliquer la formule (n + 1) / 2, ce qui indique l'intervalle médian, ici entre la 4éme et la 5éme valeur. La médiane est donc = ( 7 + 9 ) / 2.
Donc la médiane est égale à la moyenne arithmétique. Bien sur il convient de vérifier qu'il y a bien partage de l'effectif en deux parties égales.
Dans le tableau de gauche on obtient directement le résultat à l'aide de la fonction =MEDIANE(B2:B9), dans le tableau ci dessous la méthode pour déterminer l'intervalle médian puis les 2 valeurs de l'intervalle, à l'aide des fonctions =ARRONDI.INF( ) et ARRONDI.SUP().

Merci de votre attention... 

Partager |

VBA : Mon premier SPOC

Après ces quelques semaines d’absence, j’ai le plaisir de vous inviter à ma nouvelle formation en ligne “VBA : Niveau 1” au format SPOC, qui vient d’être lancée sur le site www.capitainespoc.com.

Un “Small Private Online Course” est une formation en ligne tutorée et communautaire d’une durée de 5 semaines dans laquelle un groupe de participants a accès à un contenu sur-mesure : vidéos courtes interactives, quiz pour tester vos connaissances et un cas fil rouge qui vous permettra de construire progressivement vos premières macros.

Au programme de cette formation, je vous propose 5 modules couvrant chacun un aspect essentiel qui vous permettra de développer votre compréhension de l’outil VBA et de travailler sur votre propre projet de macro.

• Module 1 : Introduction à la programmation sur excel et aux macros commandes
• Module 2 : Les éléments de base du langage VBA
• Module 3 : Les formulaires VBA et la gestion des erreurs
• Module 4 : Éléments avancés du langage : procédures, fonctions, événements et tableaux
• Module Bonus : VBA et son environnement

Pour les lecteurs assidus de mon blog ou si c’est votre jour de chance et que vous tombez par hasard sur cet article, j’ai négocié pour vous une remise spéciale : -75€ sur votre formation. Il suffit d’utiliser le code promo SpocVBA1.

J’espère vous retrouver nombreux pour la première session du SPOC “VBA : Niveau 1” qui démarrera le 10 novembre 2014 !

Merci de votre attention !

Partager |

Calcul du mode

Le mode d'une série de valeur se définie comme la valeur la plus fréquente de cette série. Le calcul dépendra du type de données, ici nous ne considérerons que des données numériques (quantitatives). Une série peut posséder plusieurs modes. Voyons comment manipuler cette notion dans Excel.

Exemple 1 : Une série simple sans aucune valeur répétée.

Dans l’exemple suivant, chaque valeur n'étant répétée qu'une seule fois, (fréquence de chaque valeur = 1)  il n'y a pas de mode, celui-ci à été évalué à l'aide de la fonction =MODE.SIMPLE(B2:B7) et retourne la valeur #N/A. Profitons en pour se rafraichir la mémoire sur les fonctions Excel permettant de détourner les messages d'erreurs. Ici dans la cellule B9, la formule =SIERREUR( MODE.SIMPLE(B2:B7) ; "Pas de mode") permet aisément ce détournement.

Exemple 2 : Les effectifs sont groupés par valeurs.

Dans ce second exemple, la fonction =mode.simple() nous permet d'obtenir un mode égale à 13, pour vérifier le résultat nous allons déterminer la fréquence de chacune des moyennes présentes dans le tableau. Pour réaliser ce comptage un simple tableau croisé dynamique  à une dimension fera l'affaire. Il nous restera alors à convertir le tableau croisé en graphique croisé ici les n_i en fonction des effectifs (diagramme en bâtons), le mode est représenté alors par le bâton le plus haut de la série.

 
Exemple 3 : Groupons les effectifs par classes d’amplitudes égales.

Dans ce troisième exemple, nous souhaitons étudier un tableau de 30 valeurs en définissant 4 classes d’amplitudes égales comme dans le tableau 2.

Un tableau croisé dynamique pourra calculer automatiquement pour nous les n_i des 4 classes et ainsi mettre immédiatement en évidence la classe modale. La seule difficulté est de transformer automatiquement les x_i en classe, cela est rendu possible grâce à une fonctionnalité magique des tableaux croisés d'Excel.

1. Positionnez-vous dans le tableau croisé
2. Onglet contextuel Outils de tableau croisé dynamique  / Options
3. Bouton "Grouper la sélection"
4. définir vos classes en précisant les valeurs de départ, d'arrivée et le pas. 

 Pour calculer le mode nous utiliserons dans la cellule F6 la formule :

Mode = x ^inf_i + a * (d1 / (d1 + d2))

ou a est l'amplitude de classe et x ^inf _i la borne inférieure de la classe modale, ces deux valeurs sont saisies ici dans les cellules H1 et H2. Les valeurs d1 et d2 sont extraites du tableau croisé grâce la fonction =LIREDONNEESTABCROISDYNAMIQUE( ). Il nous reste alors à convertir le tableau croisé en graphique croisé. Pour la transformation du "diagramme bâton" en "histogramme", consultez l'article du 08/04/2014.

Les indications portées sur le graphique permettent ici de comprendre la formule de calcul.

Merci de votre attention...

Partager |

Le rendement d'un livret

Voici une vidéo à destination de tous les débutants, il s'agit à travers un exemple très simple (le calcul du rendement d'un livret) de manipuler les notions de références relatives, absolues et mixtes.







Bonne consultaion....

Partager |

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 |