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 !



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 ni 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 ni des 4 classes et ainsi mettre immédiatement en évidence la classe modale. La seule difficulté est de transformer automatiquement les xi 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 infi + 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...



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....




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 (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...



Créer un histogramme, un vrai !




Créer un histogramme dans Excel, cela semble d'une telle facilité que l'on pourrait ne pas percevoir l’intérêt de rédiger un article sur un tel sujet. Pourtant, les graphiques que nous désignions généralement sous le terme générique d'histogramme sont en réalité des diagrammes en bâtons ou en barres verticales.
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 :
  1. 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)
  2. Les classes sur lesquels vous travaillez sont elles d'amplitudes égales ou inégales ?
Amplitude de classe égales : Considérons le tableau 1, donnant répartition des salaires de 100 personnes par classe de 1500 €.



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...




Déplacez vous ! vite.



Cet article, promis aux lecteurs depuis fort longtemps, fait suite à l'article du 7 Mars 2009 portant sur les raccourcis clavier  dans Excel. Ici nous allons exposer les raccourcis permettant le déplacement du pointeur de la cellule active puis les méthodes de sélection des plages de cellules toujours à l'aide du clavier.

 

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...



Les échelles semi-logarithmiques



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...



Excel 2010 : Supprimer les doublons




Pour en finir avec la question des doublons dans les listes de données, je souhaitais simplement rappeler qu'un code VBA ou une macro commande ne vous sera probablement pas utile si vous êtes utilisateurs des versions Excel 2010 ou 2013. Effectivement dans ces versions,  des outils sont intégrés pour la suppression des doublons et devraient largement couvrir la plupart de vos besoins dans ce domaine.

Cette vidéo vous explique la démarche à suivre à travers deux méthodes très simples. Elle vous permettra de plus de découvrir la notion de tableau mise en œuvre par Microsoft pour faciliter le travaux les plus courants sur vos listes de données.




Excel 2010 : Supprimer les doublons dans une... par O_Picot_chez_AV



Bonne consultation...




Le quartet d'Anscombe




Démarrons aujourd'hui une nouvelle série d'articles sur la construction des graphiques dans Excel. La problématique ne sera pas la réalisation technique de ces graphiques (les manipulations nécessaires étant en général d'une extrême simplicité) mais le choix du bon type de représentation en fonction des données à analyser.
Dans ce premier article nous allons étudier le quartet d'Anscombe, il s'agit d'une suite de 4 séries statistique dont les moyennes arithmétiques simples et les variances sont rigoureusement identiques mais dont les tracés sont totalement inégales. Certainement, il s'agit d'un cas fortuit très particulier, mais il met parfaitement en lumière l'importance de l'expression graphique dans l'analyse des données chiffrées.

Étape 1 : Commençons  par la réalisation du tableau chiffrée, après la saisie il suffira de calculer la moyenne =MOYENNE(B5:B15) et la variance =VAR.P.N(B5:B15) et de les recopier vers la droite.


Étape 2 : Traçons maintenant les 4 graphiques (X,Y) à l'aide du type nuage de points, correspondant aux quatre séries de données.



Étape 3 : Nous pouvons maintenant vérifier d'autres propriétés statistiques et constater à nouveau des résultats identiques pour les quatre séries.
En premier lieu vérifions la corrélation des plages X et Y à l'aide des coefficients de corrélation r={COEFFICIENT.CORRELATION(C5:C15;B5:B15)} ou de détermination R2={COEFFICIENT.DETERMINATION(C5:C15;B5:B15)}.



Ensuite calculons les paramètres a et b de l'équation y = ax + b à l'aide de la fonction ={DROITEREG(C5:C15;B5:B15)}, le résultat est immanquablement :

y = 1/2x + 3

Ne reste plus alors que l'ajout à l'aide du menu contextuel de la droite de régression linéaire sur le graphique et la vérification par la méthode graphique d'excel du R2 et de l'équation de la droite. Si vous ne maitrisez pas cette partie, reportez vous à mon article du 1er Mars 2009 sur la tendance d'une série de valeur.


Etape 4 : Essayons maintenant de calculer le r, non pas pour les 11 valeurs de la  série 3, mais uniquement avec 10 valeurs en excluant la valeur aberrante, vous constaterez alors que le coefficient passe de 0.82 à 1. (r = 1 ou r = -1 indiquant une corrélation parfaite)

Conclusion :  Éclairage sur l’intérêt des représentations graphiques et mise en évidence de l'influence des données aberrantes, voici l'apport du quartet d'Anscombe.

Merci de votre attention,



top