Aperçu des sections

  • A. Hypothèse à une variable

    Une table d'hypothèse peut concerner une ou plusieurs formules mais avec une seule entrée : Une seule des cellules d'entrée doit alors être renseignée (de préférence, celle des colonnes).

     onglet "données"
    groupe "prévisions"
     (6ème bloc)
    sélectionner la plage où va s'inscrire la table
    <clic g> sur  
    <clic g> sur

    indiquer
    la cellule d'entrée (ligne ou colonne)
     pour valider

     

    paramètres

    plage de la table
    plage où vont s'inscrire les différentes valeurs incluant la colonne ou la ligne de référence aux formules

    cellule d'entrée en ligne ou colonne
    cellule définissant la variable à analyser

    1.     TABLE NON LIÉE À UNE BASE

    Si la table n'est pas liée à une base de données, les paramètres de création sont les suivants :

    cellule d'entrée
    C'est la variable, base de l'analyse de la formule.

    la table
    indiquer en première ligne les formules (référence obligatoire à la cellule d'entrée)
    indiquer dans la première colonne les valeurs prises par la variable

     les intersections ligne/colonnes sont renseignées par Excel

     Un représentant a une commission de 3% du chiffre d'affaires encaissé.
    L'entreprise, pour calculer son résultat, doit diminuer le chiffre d'affaires de 3200 € de couts fixes ainsi que de la commission du commercial.
    Représenter ce problème avec Excel : définir une table donnant pour divers taux (
    5% à 10%)  la commission payée au représentant puis calculer le résultat afférent pour l'entreprise
    (voir problème tabhyp.xlsx, feuille " prob 1 a " et solution  tabhyp terminé.xlsx feuille "hyp 1 a")

    paramètres

    cellule d'entrée colonne : B1

    plage sélectionnée : B11.D17

    C11 : référence cellule commission soit$ B$5 (à recopier)

    D11 : référence cellule résultat soit $B$7 (à recopier)

    2.     TABLE LIÉE À UNE BASE

    Si la table est liée à une base de données, les paramètres sont les suivants :

    le champs de critères (A19.A20)
    recopier le titre de la rubrique à analyser dans une cellule libre
    la cellule située en dessous doit être vierge et sera la cellule d'entrée

    la table d'hypothèses (B21.E25)
    indiquer en première ligne les formules (référence obligatoire aux cellules d'entrée)
    indiquer dans la première colonne les valeurs prises par la rubrique à analyser.

     Afficher la feuille "prob 1 b" du classeur "tabhyp.xlsx"
    A partir de ce tableau, calculer pour chaque région, l'ancienneté moyenne des commerciaux, leurs ventes moyennes ainsi que leur ventes totales
    Calculer les résultats à l'aide d'une zone de critères et de la fonction bdmoyenne

    Représenter les données à l'aide d'une table de données par région (voir problème tabhyp.xlsx, feuille " prob 1 b " et solution  tabhyp terminé.xlsx feuille "hyp 1 b")

    paramètres

    C21 ancienneté moyenne : =BDMOYENNE(A1:E17;4;A19:A20) (à saisir)
    N° De Rubrique/Critère : 4

    D21 ventes : =BDSOMME(A1:E17;5;A19:A20) (à saisir)
    N° De Rubrique/Critère : 5

    E21 ventes moyennes : =BDMOYENNE(A1:E17;5;A19:A20) (à saisir)
    N° De Rubrique/Critère : 5

    Champs De Données : A1: E17

    Cellule d'entrée colonne : A20

    Champs De Critères : A19.A20

    Sélection : B21 : E25

    • E. Fonctions statistiques

      Elles figurent dans le groupe "fonctions statistiques" mais aussi dans le groupe "fonctions de compatibilité". Elles permettent d'obtenir des informations quantitatives sur un ensemble de données. Ce sont le plus souvent les références d'une plage de cellules qui désignent la série. Elles permettent d'en extraire le nombre, les extrêmes, la moyenne, l'écart type, la variance.
      Les fonctions statistiques de Excel sont nombreuses et correspondent à l'application de techniques statistiques élaborées (khi deux, loi de Fisher, de Pearson, de Poisson, de Student...). Sont étudiées ici les fonctions les plus courantes, liées à une série d'observations.

      (Fréquentation et ventes d'un parc d'attraction en fonction de la température)

      Lorsqu'il existe une corrélation entre les différents facteurs, il est intéressant de les représenter sous forme de nuages de points et de calculer un trend (tendance) à l'aide d'une droite de régression linéaire dont la pente et la distance à l'ordonnée peuvent être calculés par l'intermédiaire des fonctions appropriées ; cette droite de "tendance" peut aussi être directement insérée dans le graphique en nuage de points en choisissant l'outil de disposition :

       (foncstat.xls-graphe visiteurs)

      1.      =ECARTYPE.STANDARD(liste)

      Cette fonction calcule l'écart type de la liste de données ; c'est à dire la dispersion des points par rapport à la moyenne. La base de l'estimation est un échantillon (celui-ci devant être représentatif).

       Plus l'écart type est faible, plus les points sont regroupés autour de la moyenne et donc, plus l'ensemble est homogène.
      Plus l'écart type est grand, moins les données sont homogènes. Elles sont alors souvent inexploitables d'un point de vue statistique car on ne peut en retirer aucune règle, aucune tendance

      2.     =ECARTYPE.PEARSON(liste)

      Cette fonction calcule l'écart type de la liste de données ; c'est à dire la dispersion des points par rapport à la moyenne. La base de l'estimation est la population entière.

      3.      =NB(liste)

      Cette fonction calcule le nombre de données de la liste (le nombre de cellules occupées d'une plage).

      4.      =MAX(liste)

      Cette fonction extrait la valeur maximale de la liste.

      5.      =MEDIANE(liste)

      Cette fonction extrait la valeur qui partage la liste en deux sous-ensembles égaux.

      6.      =MIN(liste)

      Cette fonction extrait la valeur minimale de la liste.

      7.      =MOYENNE(liste)

      Cette fonction extrait la valeur moyenne de la liste. La base de l'estimation est un échantillon et non la population entière.

      8.      =VARP(liste)

      La variance est le carré de l'écart type. La base de l'estimation est ici la population entière.

      9.      =CENTILE.INCLURE(LISTE,K)

      Un centile est chacune des 99 valeurs qui divisent les données triées en 100 parts égales, de sorte que chaque partie représente 1/100 de l'échantillon ou de la population. Cette formule renvoie le kième centile des valeurs d'une plage où k se trouve compris dans la plage de 0 à 1.

      10.  =INTERVALLE.CONFIANCE.NORMAL (precision,ecart type, taille)

      Un intervalle de confiance permet d'évaluer la précision de l'estimation d'un paramètre statistique sur un échantillon. La formule renvoie l'intervalle de confiance pour la moyenne d'une population, basé sur la loi normale..

      11.  =INTERVALLE.CONFIANCE.PEARSON (precision,ecart type, taille)

      La formule renvoie l'intervalle de confiance pour la moyenne d'une population, basé sur la loi de Pearson.

      12.  =ORDONNEE.ORIGINE(X connu;Yconnu)

      Cette fonction détermine la distance entre l'origine et le point ou la courbe de tendance coupe cette origine, ce qui équivaut au coefficient b de la droite de régression : y=ax + b.

      13.  =DROITEREG(X connu;Yconnu)

      Cette fonction détermine la pente de la courbe de tendance, ce qui équivaut au coefficient a de la droite de régression :  y=ax + b.

       Ouvrir le classeur " foncstat.xlsx"
      Calculer pour la colonne visiteurs les principales fonctions statistiques (minimum, maximum, moyenne, somme, médiane, variance, écart type).
      Représenter ces données graphiquement et calculer le trend (tendance)

      14. ANALYSE

      Un utilitaire d'analyse avancé est disponible sous Excel.

       S'il n'est pas affiché :
      Aller dans le menu "fichier" et dans les options d'Excel, dans le volet gauche "compléments"
      Les options d'analyse sont alors disponibles dans l'onglet "données", bloc "analyse"

       

       onglet "données"
      groupe "analyse"
      (dernier bloc)
      <clic g> sur
      indiquer le type d'analyse
      sélectionner la plage de données
       pour valider

       Ouvrir le classeur " foncstat2.xlsx"
      Effectuer une analyse de variance à un facteur puis à deux facteurs sans répétition d'expérience puis de corrélation sur des feuilles séparées