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 |

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

Partager |

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 R²={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 R² et de l'équation de la droite. Si vous ne maitrisez pas cette partie, reportez vous à mon article du 1^er 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,

Partager |

VBA : La fonction OnTime

Comment déclencher l’exécution d’une macro commande ou d’une procédure VBA en fonction du temps, c’est-à-dire comment créer un minuteur pouvant déclencher l’exécution d’une action à une date précise ou après un intervalle de temps déterminé. Nous allons étudier ici la méthode OnTime de l’objet application qui permet d’arrivée à ce résultat. Cette méthode es décrite à l’aide de 4 paramètres que nous allons décrire ici. La syntaxe en est :

Application.OnTime EarliestTime, Procedure, [LatestTime], [Schedule]

 

EarliestTime

 

(Argument obligatoire) est la valeur temps qui indique le moment de démarrage d’une procédure. Cette programmation horaire peut s’écrire et se concevoir e deux manières :

 

Lancer une procédure à une heure précise : 

 

Dans ce premier exemple (Sub attendre_exemple1) l’exécution de la macro aura lieu à 10 h et 53’. Nous utilisons la fonction TimeValue( ) qui va retourner une variable de type date contenant l’heure. Ensuite la méthode OnTime exécutera la procédure affiche_1 (noter que le nom de la procédure est utilisé sous la forme d’une chaîne de texte). La boite de dialogue affiche alors l’heure courante.

Public heure As Date ‘ou Variant
Sub attendre_exemple1() ‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = TimeValue("10:53:00")
Application.OnTime heure, "affiche_1", , True
End Sub
Sub affiche_1()
MsgBox "ll est : " & heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Lancer une procédure après un délai imposé :

 

Dans ce deuxième exemple il faudra attendre 10 secondes pour voir la macro s’exécuter. Nous utilisons la fonction Now qui va retourner une variable de type date contenant l’heure et la date système de l’ordinateur à laquelle nous allons ajouter le délai. OnTime exécutera la procédure affiche_2. La boite de dialogue affiche ici la date système complète.

Public heure As Date
Sub attendre_exemple_2()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:10")
Application.OnTime heure, "affiche_2", , True
End Sub
Sub affiche_2()
MsgBox heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Procedure

 

(Argument obligatoire) est la valeur de type chaîne de texte qui contient le nom de la procédure à exécuter.

LatestTime


(Argument facultatif) est la valeur temps qui indique le délai maximal d’attente d’Excel en cas d’indisponibilité de ce dernier (exécution d’une autre procédure en cours). Si le logiciel n'est pas disponible au bout de ce délai, la procédure ne s'exécutera pas. Si ce paramètre est omis, le logiciel peut attendre indéfiniment avant l’exécution de OnTime, il semble donc que cette seconde option soit recommandable.
Si vous devez indiquer une valeur LatestTime, vous pouvez la calculer à partir de EarliestTime.

LatestTime = EarliestTime + 10 'pour attendre 10 secondes la disponibilité d’Excel.

Schedule

 

 (Argument facultatif) est la valeur de type booléen qui indique si la procédure doit être exécutée ou non. La valeur par défaut est True. Le problème est que pour stopper une procédure OnTime il faut renvoyer à nouveau cette dernière en paramétrant la valeur de Schedule à False. Cette opération générant une erreur il faudra utiliser le processus habituel en matière de gestion d’erreur « On Error Resume Next » qui détournera tous messages d’erreurs liés aux instructions suivantes.

Dans ce troisième et dernier exemple, nous allons afficher à quatre reprises (pendant une minute) l’heure courante dans la cellule A1 (mise en format hh:mm:ss) avec un intervalle de 15 secondes entre chaque nouvel affichage, puis nous interromprons la procédure en passant l’argument Schedule à False, c’est seulement de cette manière que l’arrêt de la méthode OnTime s’effectue convenablement.

Public heure As Date
Public compteur As Byte
Sub attendre_exemple_3()()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:15")
Application.OnTime heure, "affiche_3", , True
End Sub
Sub affiche_3()
Dim EcrireH As String
Range("a1").ClearContents
EcrireH = heure
Range("a1").Value = EcrireH
compteur = compteur + 1
If compteur = 4 Then
MsgBox "TERMINE", vbOKOnly + vbInformation, "Horloge : "
Range("a1").ClearContents
compteur = 0
On Error Resume Next
Application.OnTime heure, "affiche_3", , False
Else
attendre_exemple_3
End If
End Sub

Bon courage pour vos tests et vos adaptations...

Partager |

VBA : La suite de Fibonacci

Poursuivons sur le thème de la semaine dernière, à savoir l'utilisation des fonctions récursives en VBA. Un autre exemple incontournable en algorithmie se trouve dans la suite de Fibonacci, un mathématicien italien du 13éme siècle.
Il s'agit d'une suite d'entier dans laquelle chaque terme est le somme des deux termes qui le précédent. Si on démarre la suite en posant F(0) = 0 et F(1)  = 1, le reste de la suite s'écrira : 

F(n) = F(n-1) + F(n-2)

De quoi écrire une belle fonction récursive de type :

fonction fibo(n)
si (n ≤ 1)
  retourner n 
sinon 
  retourner fibo(n - 1) + fibo(n - 2)
fin de la fonction

Voici sa traduction en VBA, ici on saisira un nombre entier dans la cellule F1 de la feuille de

calcul, et Excel, enrichie de cette nouvelle fonction retournera le résultat en  E7.

Function fibonacci(ByVal n As Integer) As Long
If n <= 0 Then
'definition de F(0) et F(1)
    fibonacci = 0
    Else
        If n = 1 Then
            fibonacci = 1
        Else
        'recurence à partir du rang 2
            fibonacci = fibonacci(n - 1) + fibonacci(n - 2)
        End If
    End If
End Function

Toutefois la récursivité ne s’avère pas toujours, la méthode de calcul la plus rapide, 
aussi voici un algorithme plus linéaire dans l’hypothèse de la manipulation de grand nombres.

Sub Debut()
    Dim x As Byte
    x = InputBox("entrez un entier n = ", "", 0)
    fibonacci x
End Sub

'***************************************
Function fibo(ByVal n As Byte) As Integer
Dim f1 As Integer
Dim f2 As Integer
Dim i As Byte
    Select Case n
        Case 0
            fibo = 0
        Case 1, 2
            fibo = 1
        Case Else
            f1 = 1
            f2 = 1
            For i = 3 To n
                fibo = f2 + f1
                f2 = f1
                f1 = fibo
            Next i
   End Select
   MsgBox "F " & n & " = " & fibo, vbOKOnly + vbCritical, "Fibonacci"
End Function

Partager |

VBA : Calcul d'une factorielle

Poursuivons notre tour d'horizon des grands classiques proposés lors de l'apprentissage de la programmation informatique. L'appel de fonctions (et) ou de procédures est évidement un sujet particulièrement important puisqu'il touche à l'architecture même des programmes. Rapidement on en vient à parler des fonctions récursives, souvent la bête noire des apprentis programmeurs.

En informatique, une fonction est dite récursive si le calcul nécessite d'invoquer la fonction elle même.

Récursivité donc incontournable pour assurer le calcul de la factorielle d'un entier naturel n.

La factorielle de n (notée n!) est le produit des nombres entiers strictement positif inférieur ou égaux à n.

Exemple 4!  =  4 *  3 * 2 * 1  - Nous posons bien sur 0! = 1 -

Peut on résoudre ce probléme en VBA par un appel récursif  de type ?

  Fonction factorielle (n)

     Si n > 1

        Renvoyer n * factorielle(n - 1)

     Sinon

        Renvoyer 1

     Fin si

  Fin fonction

 Voici  un code très simple permettant de répondre par l'affirmatif, attention toutefois à la croissance exponentielle de l'algorithme. 

Option Explicit
'****************************
Function Factorielle(ByVal x As Integer) As Long
    'ne pas oublier le type de données de la fonction
   If x = 0 Then
      Factorielle = 1
   Else
      Factorielle = Factorielle(x - 1) * x
      ' et voici l'appel récursif
   End If
End Function
'**************************************
 Sub depart2()
   Dim n As Integer
   Dim resultat As Long
   n = InputBox("Entrez un entier n = ", "Factorielle", 0)
   Range("d1").Value = n
   resultat = Factorielle(n) 'appel de la fonction
   'et récupération du résultat
   Range("e1").Value = resultat
End Sub

Bien sur Excel intègre déjà une fonction = FACT( ), mais vous en conviendrez ce n'est pas le même plaisir....
A la semaine prochaine...

Partager |

La fonction cumul.princper

A la suite de la vidéo précédente, découvrons la fonction financière cumul.princper, qui cette fois permettra de réaliser le calcul du montant cumulé des remboursements d'un emprunt entre deux périodes.

Bonne Consultation...

Partager |

La fonction cumul.inter

Retour sur les mathématiques financières avec la fonction cumul.inter qui nous permettra de  réaliser le calcul des intérêts cumulés entre deux périodes.






Bonne consultation...

Partager |

Amortissement linéaire

Impossible de clore le chapitre des tableaux d'amortissements, sans apprendre à calculer la valeur d'amortissement grâce aux amortissements linéaires. La fonction amorlin d'Excel permet d'obtenir simplement cet amortissement linéaire.

Bonne consultation...

Partager |

La fonction DDB

Reprenons un tableau d'amortissement dégressif mais cette fois introduisons la notion de taux double en utilisant la fonction DDB.

Bonne consultation...

Partager |

Amortissement dégressif

En avril je suis branché mathématiques financières, aussi dans la continuité de la vidéo précédente voyons comment  réaliser un tableau d'amortissement dégressif à taux fixe sur une durée donnée. Dans cette vidéo nous faisons varier la durée, histoire de manipuler quelques références de cellules.

Bonne consultation...

Partager |

Taux effectif et taux nominal

Pour tous publics et surtout pour mes chers étudiants qui aiment tant les mathématiques financières, une vidéo très simple pour redéfinir et manipuler les notions de taux effectif et taux nominal. Vous n'imaginez pas tout ce qu'Excel peut faire pour vous !









Bonne consultation...

Partager |

VBA : Ou sommes nous ?

Oui ! Ou sommes nous ? Je veux dire, ou se trouve le pointeur de cellule active au moment ou nous avons besoin de lui pour enclencher  une action. Quel classeur, Quelle feuille ? Si nous l'avons perdu nous ne pourrons pas optimiser notre code. Heureusement cette vidéo vous explique comment le retrouver ?









Bonne consultation...

Partager |