Excel Pour la Prise de décision

En plus des formules, une autre façon d’effectuer des calculs mathématiques dans Excel consiste à utiliser des fonctions. Les fonctions statistiques appliquent un processus mathématique à un groupe de cellules d’une feuille de calcul. Par exemple, la fonction SUM est utilisée pour ajouter les valeurs contenues dans une plage de cellules. Une liste des fonctions statistiques couramment utilisées est présentée dans le tableau 2.4. Les fonctions sont plus efficaces que les formules lorsque vous appliquez un processus mathématique à un groupe de cellules. Si vous utilisez une formule pour ajouter les valeurs dans une plage de cellules, vous devrez ajouter chaque emplacement de cellule à la formule un à la fois. Cela peut prendre beaucoup de temps si vous devez ajouter les valeurs dans quelques centaines d’emplacements de cellules. Cependant, lorsque vous utilisez une fonction, vous pouvez mettre en surbrillance toutes les cellules contenant les valeurs que vous souhaitez additionner en une seule étape. Cette section présente une variété de fonctions statistiques que nous ajouterons au Cahier de travail du budget personnel. En plus de démontrer les fonctions, cette section examine également le pourcentage de calculs totaux et l’utilisation de références absolues.

Tableau 2.4 Fonctions Statistiques Couramment Utilisées

La fonction SOMME est utilisée lorsque vous devez calculer des totaux pour une plage de cellules ou un groupe de cellules sélectionnées sur une feuille de calcul. En ce qui concerne la feuille de calcul détaillée du budget, nous utiliserons la fonction SOMME pour calculer les totaux de la ligne 12. Il est important de noter qu’il existe plusieurs méthodes pour ajouter une fonction à une feuille de calcul, qui seront démontrées tout au long du reste de ce chapitre. Ce qui suit illustre comment une fonction peut être ajoutée à une feuille de calcul en la tapant dans un emplacement de cellule:

1. Cliquez sur l’onglet Feuille de calcul Détaillée du budget pour ouvrir la feuille de calcul.
2. Cliquez sur la cellule C12.
3. Tapez un signe égal =.
4. Tapez le nom de la fonction SUM.
5. Tapez une parenthèse ouverte (.
6. Cliquez sur la cellule C3 et faites glisser vers le bas jusqu’à la cellule C11. Cela place la plage C3: C11 dans la fonction.
7. Tapez une parenthèse de fermeture).
8. Appuyez sur la touche ENTRÉE. La fonction calcule le total de la colonne des dépenses mensuelles, qui est de 1 496 $.

La figure 2.11 montre l’apparence de la fonction SOMME ajoutée à la feuille de calcul de détail du budget avant d’appuyer sur la touche ENTRÉE.

Comme le montre la Figure 2.11, la fonction SOMME a été ajoutée à la cellule C12. Cependant, cette fonction est également nécessaire pour calculer les totaux dans les colonnes Dépenses annuelles et Dépenses courantes. La fonction peut être copiée et collée dans ces emplacements de cellules en raison du référencement relatif. Le référencement relatif sert le même but pour les fonctions que pour les formules. Ce qui suit montre comment la ligne totale est terminée:

1. Cliquez sur la cellule C12 dans la feuille de calcul Détaillée du budget.
2. Cliquez sur le bouton Copier dans l’onglet Accueil du ruban.
3. Mettez en surbrillance les cellules D12 et E12.
4. Cliquez sur le bouton Coller dans l’onglet Accueil du ruban. Cela colle la fonction SOMME dans les cellules D12 et E12 et calcule les totaux pour ces colonnes.
5. Cliquez sur la cellule F11.
6. Cliquez sur le bouton Copier dans l’onglet Accueil du ruban.
7. Cliquez sur la cellule F12, puis cliquez sur le bouton Coller dans l’onglet Accueil du ruban. Puisque nous avons maintenant des totaux dans la ligne 12, nous pouvons coller la formule de changement de pourcentage dans cette ligne.

La figure 2.12 montre la sortie de la fonction SOMME qui a été ajoutée aux cellules C12, D12 et E12. De plus, la formule de variation en pourcentage a été copiée et collée dans la cellule F12. Notez que cette version du budget prévoit une diminution des dépenses de 1,7% par rapport à l’année dernière.

Références Absolues (Calcul du Pourcentage des totaux)

Fichier de données: Continuez avec le Budget personnel CH2.

Étant donné que les totaux ont été ajoutés à la ligne 12 de la feuille de calcul détaillée du budget, un pourcentage du calcul total peut être ajouté à la colonne B à partir de la cellule B3. Le calcul du pourcentage du total indique le pourcentage de chaque valeur de la colonne Dépenses annuelles par rapport au total de la cellule D12. Cependant, une fois la formule créée, il sera nécessaire de désactiver la fonction de référencement relatif d’Excel avant de copier et coller la formule sur le reste des emplacements de cellules de la colonne. La désactivation de la fonction de référencement relatif d’Excel s’effectue via une référence absolue. Les étapes suivantes expliquent comment cela est fait:

1. Cliquez sur la cellule B3 dans la feuille de calcul Détaillée du budget.
2. Tapez un signe égal =.
3. Cliquez sur la cellule D3.
4. Tapez une barre oblique /.
5. Cliquez sur la cellule D12.
6. Appuyez sur la touche ENTRÉE. Vous verrez que les services publics des ménages représentent 16,7 % du budget annuel des dépenses (voir la figure 2.14).

Figure 2.14 montre la formule complète qui calcule le pourcentage que représentent les Dépenses annuelles des services publics des ménages par rapport aux Dépenses annuelles totales pour le budget (voir la cellule B3). Normalement, nous copions cette formule et la collons dans la plage B4: B11. Cependant, en raison du référencement relatif, les deux références de cellules augmenteront d’une ligne à mesure que la formule sera collée dans les cellules en dessous de B3. Ceci est très bien pour la première référence de cellule dans la formule (D3) mais pas pour la deuxième référence de cellule (D12). La figure 2.15 illustre ce qui se passe si nous collons la formule dans la plage B4: B12 dans son état actuel. Notez qu’Excel produit le code d’erreur #DIV/0. Cela signifie qu’Excel essaie de diviser un nombre par zéro, ce qui est impossible. En regardant la formule dans la cellule B4, vous voyez que la première référence de cellule a été modifiée de D3 à D4. C’est bien parce que nous voulons maintenant diviser les Dépenses annuelles d’assurance par les Dépenses annuelles totales de la cellule D12. Cependant, Excel a également changé la référence de cellule D12 en D13. Étant donné que l’emplacement de la cellule D13 est vide, la formule produit le code d’erreur #DIV/0.

Pour éliminer l’erreur de division par zéro illustrée à la Figure 2.15, nous devons ajouter une référence absolue à la cellule D12 dans la formule. Une référence absolue empêche le référencement relatif de modifier une référence de cellule dans une formule. On parle également de verrouillage d’une cellule. Ce qui suit explique comment cela est accompli:

1. Double-cliquez sur la cellule B3.
2. Placez le pointeur de la souris devant D12 et cliquez. Le curseur clignotant doit se trouver devant le D dans la référence de cellule D12.
3. Appuyez sur la touche F4. Vous verrez un signe dollar ($) ajouté devant la lettre de colonne D et le numéro de ligne 12. Vous pouvez également taper les signes dollar devant la lettre de colonne et le numéro de ligne.
4. Appuyez sur la touche ENTRÉE.
5. Cliquez sur la cellule B3.
6. Cliquez sur le bouton Copier dans l’onglet Accueil du ruban.
7. Mettez en surbrillance la plage B4:B11.
8. Cliquez sur le bouton Coller dans l’onglet Accueil du ruban.

La figure 2.16 montre le pourcentage de la formule totale avec une référence absolue ajoutée à D12. Notez que dans la cellule B4, la référence de cellule reste D12 au lieu de passer à D13 comme le montre la figure 2.15. De plus, vous verrez que les pourcentages sont calculés dans le reste des cellules de la colonne et que l’erreur de division par zéro est maintenant éliminée.

La fonction de COMPTAGE

Fichier de données: Continuez avec le budget personnel CH2.

La fonction suivante que nous ajouterons à la feuille de calcul Détaillée du budget est la fonction de COMPTAGE. La fonction COUNT est utilisée pour déterminer le nombre de cellules d’une plage contenant une entrée numérique. La fonction COUNT ne fonctionnera pas pour compter du texte ou d’autres entrées non numériques. Pour la feuille de calcul Détaillée du budget, nous utiliserons la fonction DE COMPTAGE pour compter le nombre d’éléments prévus dans la colonne Dépenses annuelles (colonne D). Ce qui suit explique comment la fonction COUNT est ajoutée à la feuille de calcul à l’aide de la liste des fonctions:

1. Cliquez sur la cellule D13 dans la feuille de calcul Détaillée du budget.
2. Tapez un signe égal =.
3. Tapez la lettre C.
4. Cliquez sur la flèche vers le bas de la barre de défilement de la liste des fonctions (voir Figure 2.17) et recherchez le NOMBRE de mots.
5. Double-cliquez sur le NOMBRE de mots dans la liste des fonctions.
6. Mettez en Surbrillance la plage D3:D11.
7. Vous pouvez taper une parenthèse de fermeture), puis appuyez sur la touche ENTRÉE, ou appuyez simplement sur la touche ENTRÉE et Excel fermera la fonction pour vous. La fonction produit une sortie de 9 car il y a 9 éléments prévus sur la feuille de calcul.

Figure 2.17 affiche la zone de liste des fonctions qui apparaît après avoir terminé les étapes 2 et 3 de la fonction DE COMPTAGE. La liste des fonctions fournit une méthode alternative pour ajouter une fonction à une feuille de calcul.

La figure 2.18 montre la sortie de la fonction de COMPTAGE après avoir appuyé sur la touche ENTRÉE. La fonction compte le nombre de cellules dans la plage D3:D11 qui contiennent une valeur numérique. Le résultat de 9 indique qu’il y a 9 catégories prévues pour ce budget.

La Fonction MOYENNE

La fonction suivante que nous ajouterons à la feuille de calcul détaillée du budget est la fonction MOYENNE. Cette fonction est utilisée pour calculer la moyenne arithmétique d’un groupe de nombres. Pour la feuille de calcul Détaillée du budget, nous utiliserons la fonction pour calculer la moyenne des valeurs dans la colonne Dépenses annuelles. Nous ajouterons cela à la feuille de calcul en utilisant la bibliothèque de fonctions. Les étapes suivantes expliquent comment cela est accompli:

1. Cliquez sur la cellule D14 dans la feuille de calcul Détaillée du budget.
2. Cliquez sur l’onglet Formules du ruban.
3. Cliquez sur le bouton Plus de fonctions dans le groupe de commandes de la Bibliothèque de fonctions.
4. Placez le pointeur de la souris sur l’option Statistique dans la liste déroulante des options.
5. Cliquez sur le nom DE la fonction MOYENNE dans la liste des fonctions qui apparaissent dans le menu (voir Figure 2.19). Cela ouvre la boîte de dialogue Arguments de fonction.
6. Cliquez sur le bouton de dialogue Réduire dans la boîte de dialogue Arguments de fonction (voir Figure 2.20).
7. Mettez en Surbrillance la plage D3:D11.
8. Cliquez sur le bouton Développer la boîte de dialogue dans la boîte de dialogue Arguments de fonction (voir Figure 2.21). Vous pouvez également appuyer sur la touche ENTRÉE pour obtenir le même résultat.
9. Cliquez sur le bouton OK dans la boîte de dialogue Arguments de fonction. Cela ajoute la fonction MOYENNE à la feuille de calcul.

La figure 2.19 illustre comment une fonction est sélectionnée dans la Bibliothèque de fonctions de l’onglet Formules du Ruban.

La Figure 2.20 affiche la boîte de dialogue Arguments de fonction. Cela apparaît après la sélection d’une fonction dans la bibliothèque de fonctions. Le bouton Réduire la boîte de dialogue est utilisé pour masquer la boîte de dialogue afin qu’une plage de cellules puisse être mise en surbrillance sur la feuille de calcul, puis ajoutée à la fonction.

La Figure 2.21 montre comment une plage de cellules peut être sélectionnée dans la boîte de dialogue Arguments de fonction une fois qu’elle a été réduite.

Figure 2.22 affiche la boîte de dialogue Arguments de fonction après la définition de la plage de cellules pour la fonction MOYENNE. La boîte de dialogue affiche le résultat de la fonction avant qu’elle ne soit ajoutée à l’emplacement de la cellule. Cela vous permet d’évaluer la sortie de la fonction pour déterminer si elle a du sens avant de l’ajouter à la feuille de calcul.

La figure 2.23 montre la fonction MOYENNE terminée dans la feuille de calcul Détaillée du budget. Les résultats de la fonction montrent qu’en moyenne, nous prévoyons dépenser 1 994 $ pour chacune des catégories énumérées dans la colonne A du budget. Ce calcul des dépenses moyennes par catégorie peut être utilisé comme indicateur pour déterminer quelles catégories coûtent plus ou moins cher que les dépenses budgétisées moyennes.

Les fonctions MAX et MIN

Fichier de données: Continuez avec le Budget personnel CH2.

Les deux dernières fonctions statistiques que nous ajouterons à la feuille de calcul Détaillée du budget sont les fonctions MAX et MIN. Ces fonctions identifient les valeurs les plus élevées et les plus basses dans une plage de cellules. Les étapes suivantes expliquent comment ajouter ces fonctions à la feuille de calcul Détaillée du budget:

1. Cliquez sur la cellule D15 dans la feuille de calcul Détaillée du budget.
2. Tapez un signe égal =.
3. Tapez le mot MIN.
4. Tapez une parenthèse ouverte (.
5. Mettez en Surbrillance la plage D3:D11.
6. Tapez une parenthèse de fermeture) et appuyez sur la touche ENTRÉE, ou appuyez simplement sur la touche ENTRÉE et Excel fermera la fonction pour vous. La fonction MIN produit un résultat de 1 200 $, ce qui est la valeur la plus faible dans la colonne des dépenses annuelles (voir la figure 2.24).
7. Cliquez sur la cellule D16.
8. Tapez un signe égal =.
9. Tapez le mot MAX.
10. Tapez une parenthèse ouverte (.
11. Mettez en Surbrillance la plage D3:D11.
12. Tapez une parenthèse de fermeture) et appuyez sur la touche ENTRÉE, ou appuyez simplement sur la touche ENTRÉE et Excel fermera la fonction pour vous. La fonction MAX produit une sortie de 3 500 $. Il s’agit de la valeur la plus élevée dans la colonne des dépenses annuelles (voir Figure 2.25).

Copier et Coller des Formules (Coller sans Formats)

Fichier de données: Continuez avec le Budget personnel CH2.

Comme le montre la figure 2.25, les fonctions COUNT, AVERAGE, MIN et MAX résument les données dans la colonne Dépenses annuelles. Vous remarquerez également qu’il y a de l’espace pour copier et coller ces fonctions sous la colonne LY Spend. Cela nous permet de comparer ce que nous avons dépensé l’année dernière et ce que nous prévoyons de dépenser cette année. Normalement, nous copions et collons simplement ces fonctions dans la plage E13: E16. Cependant, vous avez peut-être remarqué la bordure de style à double ligne utilisée autour du périmètre de la plage B13: E16. Si nous utilisions la commande de collage régulière, la double ligne sur le côté droit de la plage E13: E16 serait remplacée par une seule ligne. Par conséquent, nous allons utiliser l’une des commandes spéciales Coller pour coller uniquement les fonctions sans aucun traitement de formatage. Ceci est accompli par les étapes suivantes:

1. Mettez en surbrillance la plage D13:D16 dans la feuille de calcul Détaillée du budget.
2. Cliquez sur le bouton Copier dans l’onglet Accueil du ruban.
3. Cliquez sur la cellule E13.
4. Cliquez sur la flèche vers le bas sous le bouton Coller dans l’onglet Accueil du ruban.
5. Cliquez sur l’option Formules dans la liste déroulante des boutons (voir Figure 2.26).

La figure 2.26 montre la liste des boutons qui apparaissent lorsque vous cliquez sur la flèche vers le bas sous le bouton Coller dans l’onglet Accueil du ruban. Une chose à noter à propos de ces options est que vous pouvez les prévisualiser avant de faire une sélection en faisant glisser le pointeur de la souris sur les options. Comme le montre la figure, lorsque le pointeur de la souris est placé sur le bouton Formules, vous pouvez voir comment les fonctions apparaîtront avant de faire une sélection. Notez que la bordure à deux lignes ne change pas lorsque cette option est prévisualisée. C’est pourquoi cette sélection est faite à la place de l’option de collage régulière.