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....
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....
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 (fi), de l'amplitude (ai = (borne Sup - Born Inf )), du centre de casse (ci = (borne Sup + Born Inf ) /2) et de l'amplitude corrigée (hi = 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 ni 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...
L'histogramme convient à l'étude des variables quantitatives quand celle-ci sont regroupées par classes. Alors comment créer un véritable histogramme au sens statistique du terme, deux problèmes vont venir se greffer :
- Excel considère les données de l'axe des abscisses comme des libellés et pas des valeurs (à l’exception du graphique en nuage de points)
- Les classes sur lesquels vous travaillez sont elles d'amplitudes égales ou inégales ?
| Tableau 1 | |||
| ni | fi | ||
| [0;1500[ | 1500 | 20 | 0,2 |
| [1500;3000[ | 3000 | 40 | 0,4 |
| [3000;4500[ | 4500 | 30 | 0,3 |
| [4500;6000[ | 6000 | 10 | 0,1 |
| Total : | 100 | 1 |
La colonne B contient les libellés (sous forme de texte '1500) de l'axe des abscisses. Passons sur les calculs effectués (en rouge) et traçons un graphique en barre à l'aide de la commande Insertion / graphique / histogramme 2D. Pour cela nous avons au préalable sélectionné la colonne des libellés de classe et des ni. Pour passer du graphique en barre à l'histogramme, deux manipulations seront nécessaires :- Alignement à droite des libellés de l'axe des abscisses.
- Mise en forme des séries de données,à l'aide du menu contextuel, mettre la largeur de l'intervalle à 0%.
Amplitudes de Classe inégales : Considérons le tableau 2, il est préférable d'apporter une correction à l'amplitude en divisant la fréquence par l'amplitude de classe.
| Tableau 2 | Amplitude de Classe | Amplitude corrigée | |||
| ai | ni | fi | hi = fi / ai | ||
| [0;1500[ | 1500 | 1500 | 20 | 0,2 | 0,00013 |
| [1500;4500[ | 4500 | 3000 | 70 | 0,7 | 0,00023 |
| [4500;6000[ | 6000 | 1500 | 10 | 0,1 | 0,00007 |
| 100 | 1 |
Nous tracerons alors l’histogramme sur l'amplitude corrigée plutôt que sur les ni. Pour cela il va falloir utiliser une astuce, car n'oublions pas que les classes sont d'amplitudes inégales. Nous allons donc créer un nouveau tableau qui montrera une égalité de valeur sur les ni (/2) pour la classe de revenu [1500;4500[ qui a été redécoupée.
| Classe de revenu | ni | hi = fi / ai |
| 1500 | 20 | 0,00013 |
| 3000 | 35 | 0,00023 |
| 4500 | 35 | 0,00023 |
| 6000 | 10 | 0,00007 |
| 100 |
Une fois le graphique tracé vous devez aller effacer la valeur du libellé '3000 dans le tableau.
Pour en finir avec cette question des histogrammes, Sachez que l'utilitaire Analysis ToolPak - VBA contient une macro permettant la réalisation de ces graphiques, personnellement à l'issue de plusieurs tests, je ne suis pas très convaincu par l'apport de cette macro.
Merci de votre attention...
Les touches de déplacements :
 |  |  |  | |
| Touche seule | Une colonne vers la droite Une colonne vers la gauche | Une ligne vers le bas Une ligne vers le haut | Un écran vers le haut Un écran vers le bas | Début de ligne |
| CTRL + | 1er colonne occupée Dernière colonne occupée | Dernière ligne occupée 1er ligne occupée | Feuille de calcul précédente Feuille de calcul suivante | Cellule A1 |
| ALT + | Un écran vers la gauche Un écran vers la droite |
Les méthode de sélection :
|
|  |  |  |  | |
| Maj + (ou F8) | Sélection d'une cellule vers la gauche Sélection d'une cellule vers la droite | Sélection d'une cellule vers le bas Sélection d'une cellule vers le haut | Sélection de cellules sur la hauteur d'un écran vers le bas Sélection de cellules sur la hauteur d'un écran vers le haut | Sélection jusqu'au début de la ligne | |
| Maj + CTRL + | Sélection jusqu'à la 1er colonne occupée Sélection jusqu'à la dernière colonne occupée | Sélection jusqu'à la dernière ligne occupée Sélection jusqu'à la 1er ligne occupée | Sélection jusqu'à la cellule A1 | ||
Je me suis limité aux possibilités les plus utiles, il existe de nombreuses autres combinaisons (comme par exemple la touche F5 pour atteindre une cellule par son nom ou sa référence), aussi j’attends vos commentaires et suggestions.
Merci de votre attention...
L’objet de cette nouvelle vidéo est d’expliquer comment il est possible de corriger l’échelle arithmétique d'un graphique lorsque cette dernière s’avère inadaptée.La mise en place d’une échelle logarithmique que ce soit par une approche graphique ou par une approche calculée permettra de résoudre ce type de problématique.
L’exemple de la vidéo, traite de deux entreprise A et B possédant des taux de croissance de chiffre d’affaires de proportion différentes, alors que la représentation graphique de ces taux montre deux droites parfaitement parallèles, pouvant laisser penser que la progression est rigoureusement identique.
Excel : Appliquer une échelle semi-logarithmique par O_Picot_chez_AV
Bonne consultation...
Inscription à :
Articles (Atom)