Calcul de l'intervalle de confiance dans Excel

Excel est utilisé pour effectuer diverses tâches statistiques, dont l'une est le calcul de l'intervalle de confiance, qui est utilisé comme le remplacement le plus approprié pour une estimation ponctuelle avec une petite taille d'échantillon.

Nous voulons tout de suite noter que la procédure même de calcul de l'intervalle de confiance est assez compliquée, cependant, dans Excel, il existe un certain nombre d'outils conçus pour faciliter cette tâche. Jetons un coup d'œil à eux.

Calcul de l'intervalle de confiance

Un intervalle de confiance est nécessaire pour donner une estimation d'intervalle à certaines données statiques. Le but principal de cette opération est de lever les incertitudes de l'estimation ponctuelle.

Il existe deux méthodes pour effectuer cette tâche dans Microsoft Excel :

• Opérateur NORME DE CONFIANCE – utilisé dans les cas où la dispersion est connue;
• Opérateur CONFIANCE.ÉTUDIANTlorsque la variance est inconnue.

Ci-dessous, nous analyserons étape par étape les deux méthodes dans la pratique.

Méthode 1 : instruction TRUST.NORM

Cette fonction a été introduite pour la première fois dans l'arsenal du programme dans l'édition Excel 2010 (avant cette version, elle était remplacée par l'opérateur "DE CONFIANCE"). L'opérateur est inclus dans la catégorie "statistique".

Formule de fonction NORME DE CONFIANCE Ressemble à ça:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Comme nous pouvons le voir, la fonction a trois arguments :

• "Alpha" est un indicateur du niveau de signification qui sert de base au calcul. Le niveau de confiance est calculé comme suit :
  • 1-"Альфа". Cette expression est applicable si la valeur "Alpha" présenté sous forme de coefficient. Par exemple, 1-0,7 0,3 =, où 0,7=70%/100%.
  • (100-"Альфа")/100. Cette expression sera appliquée si l'on considère le niveau de confiance avec la valeur "Alpha" en pourcentages. Par exemple, (100-70) / 100 = 0,3.
• "Écart-type" – respectivement, l'écart type de l'échantillon de données analysé.
• "Taille" est la taille de l'échantillon de données.

Remarque: Pour cette fonction, la présence des trois arguments est une condition préalable.

Opérateur "DE CONFIANCE", qui était utilisé dans les versions antérieures du programme, contient les mêmes arguments et exécute les mêmes fonctions.

Formule de fonction DE CONFIANCE comme suit:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Il n'y a pas de différences dans la formule elle-même, seul le nom de l'opérateur est différent. Dans Excel 2010 et les éditions ultérieures, cet opérateur se trouve dans la catégorie Compatibilité. Dans les anciennes versions du programme, il se trouve dans la section des fonctions statiques.

La limite de l'intervalle de confiance est déterminée par la formule suivante :

X+(-)ДОВЕРИТ.НОРМ

De Х est la valeur moyenne sur la plage spécifiée.

Voyons maintenant comment appliquer ces formules en pratique. Nous avons donc un tableau avec diverses données provenant de 10 mesures prises. Dans ce cas, l'écart type de l'ensemble de données est de 8.

Notre tâche est d'obtenir la valeur de l'intervalle de confiance avec un niveau de confiance de 95 %.

1. Tout d'abord, sélectionnez une cellule pour afficher le résultat. Ensuite, nous cliquons sur le bouton "Fonction d'insertion" (à gauche de la barre de formule).
2. La fenêtre de l'assistant de fonction s'ouvre. En cliquant sur la catégorie de fonctions en cours, déroulez la liste et cliquez sur la ligne qui s'y trouve "Statistique".
3. Dans la liste proposée, cliquez sur l'opérateur "NORME DE CONFIANCE", puis appuyez OK.
4. Nous verrons une fenêtre avec les paramètres des arguments de la fonction, remplissant laquelle nous appuyons sur le bouton OK.
   • dans le champ "Alpha" indiquer le niveau de signification. Notre tâche suppose un niveau de confiance de 95 %. En substituant cette valeur dans la formule de calcul, que nous avons considérée ci-dessus, nous obtenons l'expression : (100-95)/100. Nous l'écrivons dans le champ argument (ou vous pouvez immédiatement écrire le résultat du calcul égal à 0,05).
   • dans le champ « std_off » selon nos conditions, on écrit le chiffre 8.
   • dans le champ "Taille", indiquez le nombre d'éléments à examiner. Dans notre cas, 10 mesures ont été prises, nous écrivons donc le nombre 10.
5. Pour éviter d'avoir à reconfigurer la fonction lorsque les données changent, vous pouvez l'automatiser. Pour cela on utilise la fonction "CHÈQUE". Placez le pointeur dans la zone de saisie des informations d'argument "Taille", puis cliquez sur l'icône en forme de triangle sur le côté gauche de la barre de formule et cliquez sur l'élément "Plus de fonctionnalités…".
6. En conséquence, une autre fenêtre de l'assistant de fonction s'ouvrira. En choisissant une catégorie "Statistique", cliquez sur la fonction "CHÈQUE", alors ok.
7. L'écran affichera une autre fenêtre avec les paramètres des arguments de la fonction, qui est utilisée pour déterminer le nombre de cellules dans une plage donnée qui contiennent des données numériques.

   Formule de fonction COMMENCER c'est écrit comme ça : =СЧЁТ(Значение1;Значение2;...).

   Le nombre d'arguments disponibles pour cette fonction peut aller jusqu'à 255. Ici, vous pouvez écrire soit des nombres spécifiques, soit des adresses de cellules, soit des plages de cellules. Nous utiliserons la dernière option. Pour cela, cliquez sur la zone de saisie des informations du premier argument, puis, en maintenant le bouton gauche de la souris enfoncé, sélectionnez toutes les cellules d'une des colonnes de notre tableau (sans compter l'en-tête), puis appuyez sur le bouton OK.

8. À la suite des actions entreprises, le résultat des calculs pour l'opérateur sera affiché dans la cellule sélectionnée NORME DE CONFIANCE. Dans notre problème, sa valeur est égale à 4,9583603.
9. Mais ce n'est pas encore le résultat final de notre tâche. Ensuite, vous devez calculer la valeur moyenne sur un intervalle donné. Pour ce faire, vous devez utiliser la fonction "CŒUR"A qui effectue la tâche de calculer la moyenne sur une plage de données spécifiée.

   La formule de l'opérateur s'écrit ainsi : =СРЗНАЧ(число1;число2;...).

   Sélectionnez la cellule où nous prévoyons d'insérer la fonction et appuyez sur le bouton "Fonction d'insertion".

10. Dans la catégorie "Statistique" choisir un opérateur ennuyeux "CŒUR" et cliquez sur OK.
11. Dans les arguments de la fonction dans la valeur de l'argument "Nombre" spécifiez la plage, qui comprend toutes les cellules avec les valeurs de toutes les mesures. Ensuite, nous cliquons D'ACCORD.
12. À la suite des actions entreprises, la valeur moyenne sera automatiquement calculée et affichée dans la cellule avec la fonction nouvellement insérée.
13. Nous devons maintenant calculer les limites de l'IC (intervalle de confiance). Commençons par calculer la valeur de la bordure droite. Nous sélectionnons la cellule où nous voulons afficher le résultat, et effectuons l'addition des résultats obtenus à l'aide des opérateurs "CŒUR" et "NORMES DE CONFIANCE ». Dans notre cas, la formule ressemble à ceci : A14+A16. Après l'avoir tapé, appuyez sur Entrer.
14. En conséquence, le calcul sera effectué et le résultat sera immédiatement affiché dans la cellule avec la formule.
15. Ensuite, de manière similaire, nous effectuons le calcul pour obtenir la valeur de la bordure gauche du CI. Seulement dans ce cas la valeur du résultat "NORMES DE CONFIANCE » vous n'avez pas besoin d'ajouter, mais de soustraire du résultat obtenu à l'aide de l'opérateur "CŒUR". Dans notre cas, la formule ressemble à ceci : =A16-A14.
16. Après avoir appuyé sur Entrée, nous obtiendrons le résultat dans la cellule donnée avec la formule.

Remarque: Dans les paragraphes ci-dessus, nous avons essayé de décrire le plus en détail possible toutes les étapes et chaque fonction utilisée. Cependant, toutes les formules prescrites peuvent être écrites ensemble, dans le cadre d'une seule grande:

• Pour déterminer la bordure droite du CI, la formule générale ressemblera à ceci :

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• De même, pour la bordure gauche, seulement au lieu d'un plus, vous devez mettre un moins :

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Méthode 2 : Opérateur TRUST.STUDENT

Maintenant, familiarisons-nous avec le deuxième opérateur pour déterminer l'intervalle de confiance - CONFIANCE.ÉTUDIANT. Cette fonction a été introduite dans le programme relativement récemment, à partir de la version d'Excel 2010, et vise à déterminer l'IC de l'ensemble de données sélectionné en utilisant la distribution de Student, avec une variance inconnue.

Formule de fonction CONFIANCE.ÉTUDIANT comme suit:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Analysons l'application de cet opérateur sur l'exemple de la même table. Seulement maintenant nous ne connaissons pas l'écart type selon les conditions du problème.

1. Tout d'abord, sélectionnez la cellule où nous prévoyons d'afficher le résultat. Cliquez ensuite sur l'icône "Fonction d'insertion" (à gauche de la barre de formule).
2. La fenêtre de l'assistant de fonction déjà bien connue s'ouvrira. Choisissez une catégorie "Statistique", puis dans la liste des fonctions proposées, cliquez sur l'opérateur "ÉTUDIANT DE CONFIANCE", alors - OK.
3. Dans la fenêtre suivante, nous devons configurer les arguments de la fonction :
   • Dans le "Alpha" comme dans la première méthode, indiquez la valeur 0,05 (ou "100-95)/100").
   • Passons à l'argumentation. « std_off ». Parce que selon les conditions du problème, sa valeur nous est inconnue, nous devons faire les calculs appropriés, dans lesquels l'opérateur "STDEV.B ». Cliquez sur le bouton ajouter une fonction puis sur l'élément "Plus de fonctionnalités…".
   • Dans la fenêtre suivante de l'assistant de fonction, sélectionnez l'opérateur "STDEV.B » dans la catégorie "Statistique" et cliquez sur OK.
   • Nous entrons dans la fenêtre de configuration des arguments de la fonction, dont la formule ressemble à ceci : =СТАНДОТКЛОН.В(число1;число2;...). Comme premier argument, nous spécifions une plage qui inclut toutes les cellules de la colonne "Valeur" (sans compter l'en-tête).
   • Maintenant, vous devez revenir à la fenêtre avec les arguments de la fonction "CONFIANCE.ÉTUDIANT”. Pour cela, cliquez sur l'inscription du même nom dans le champ de saisie de la formule.
   • Passons maintenant au dernier argument "Taille". Comme dans la première méthode, vous pouvez ici soit simplement spécifier une plage de cellules, soit insérer l'opérateur "CHÈQUE". Nous choisissons la dernière option.
   • Une fois tous les arguments renseignés, cliquez sur le bouton OK.
4. La cellule sélectionnée affichera la valeur de l'intervalle de confiance en fonction des paramètres que nous avons spécifiés.
5. Ensuite, nous devons calculer les valeurs des limites du CI. Et pour cela, vous devez obtenir la valeur moyenne de la plage sélectionnée. Pour ce faire, nous appliquons à nouveau la fonction "CŒUR". L'algorithme des actions est similaire à celui décrit dans la première méthode.
6. Ayant reçu la valeur "CŒUR", vous pouvez commencer à calculer les limites du CI. Les formules elles-mêmes ne sont pas différentes de celles utilisées avec le "NORMES DE CONFIANCE »:
   • Bordure droite IC=MOYENNE+CONFIANCE DES ÉTUDIANTS
   • IC lié à gauche=CONFIANCE MOYENNE DE L'ÉLÈVE

Conclusion

L'arsenal d'outils d'Excel est incroyablement vaste et, en plus des fonctions courantes, le programme offre une grande variété de fonctions spéciales qui faciliteront grandement le travail avec les données. Peut-être que les étapes décrites ci-dessus peuvent sembler compliquées à certains utilisateurs à première vue. Mais après une étude détaillée du problème et de la séquence d'actions, tout deviendra beaucoup plus facile.