Calculer plusieurs résultats à l’aide d’une table de données dans Excel pour Mac

Les tables de données font partie d’une suite de commandes appelées Outils d’analyse de scénarios. Lorsque vous utilisez des tables de données, vous effectuez une analyse de scénario. L’analyse de scénario modifie les valeurs dans les cellules pour voir comment ces modifications affecteront le résultat des formules de la feuille. Par exemple, vous pouvez utiliser une table de données pour faire varier le taux d’intérêt et la durée d’un prêt afin de déterminer les montants de paiement mensuels possibles.

Il existe trois types d’outils d’analyse de scénarios dans Excel : les scénarios, les tableaux de données et la recherche d’objectifs. Les scénarios et les tables de données prennent des ensembles de valeurs d’entrée et déterminent les résultats possibles. Goal Seek fonctionne différemment des scénarios et des tables de données en ce qu’il prend un résultat et détermine les valeurs d’entrée possibles qui produisent ce résultat. À l’instar des scénarios, les tables de données vous aident à explorer un ensemble de résultats possibles. Contrairement aux scénarios, les tables de données affichent tous les résultats dans une table sur une feuille. L’utilisation de tables de données permet d’examiner facilement une gamme de possibilités en un coup d’œil. Étant donné que vous vous concentrez uniquement sur une ou deux variables, les résultats sont faciles à lire et à partager sous forme tabulaire.

Une table de données ne peut pas prendre en charge plus de deux variables. Si vous souhaitez analyser plus de deux variables, vous devez plutôt utiliser des scénarios. Bien qu’elle soit limitée à une ou deux variables (une pour la cellule d’entrée de ligne et une pour la cellule d’entrée de colonne), une table de données peut inclure de nombreuses valeurs de variable différentes. Un scénario peut avoir un maximum de 32 valeurs différentes, mais vous pouvez créer autant de scénarios que vous le souhaitez.

Notions de base de la table de données

Vous pouvez créer des tables de données à une ou deux variables, en fonction du nombre de variables et de formules que vous souhaitez tester. Utilisez une table de données à une variable si vous souhaitez voir comment les différentes valeurs d’une variable dans une ou plusieurs formules modifient les résultats de ces formules. Par exemple, vous pouvez utiliser une table de données à une variable pour voir comment les différents taux d’intérêt affectent un paiement hypothécaire mensuel à l’aide de la fonction PMT. Vous entrez les valeurs de variable dans une colonne ou une ligne, et les résultats sont affichés dans une colonne ou une ligne adjacente.

Pour plus d’informations, consultez Fonction PMT.

La cellule D2 contient la formule de paiement, =PMT(B3/12,B4,-B5), qui fait référence à la cellule d’entrée B3.

Table de données à une variable

cellule Input.

Liste des valeurs que Excel remplace dans la cellule d’entrée, B3.

Utilisez une table de données à deux variables pour voir comment les différentes valeurs de deux variables dans une formule modifient les résultats de cette formule. Par exemple, vous pouvez utiliser une table de données à deux variables pour voir comment différentes combinaisons de taux d’intérêt et de conditions de prêt affecteront un paiement hypothécaire mensuel.

La cellule C2 contient la formule de paiement, =PMT(B3/12 ;B4,-B5), qui utilise deux cellules d’entrée, B3 et B4.

Table de données à deux variables

cellule d’entrée Column.

Liste des valeurs que Excel remplace dans la cellule d’entrée de ligne, B4.

cellule d’entrée de ligne.

Liste des valeurs que Excel remplace dans la cellule d’entrée de colonne, B3.

Les tables de données sont recalculées lorsqu’une feuille est recalculée, même si elles n’ont pas changé. Pour accélérer le calcul d’une feuille contenant une table de données, vous pouvez modifier les options de calcul pour recalculer automatiquement la feuille, mais pas les tables de données.

Une table de données à une variable a des valeurs d’entrée qui sont répertoriées en bas d’une colonne (orientée colonne) ou sur une ligne (orientée ligne). Les formules utilisées dans une table de données à une variable ne doivent faire référence qu’à une seule cellule d’entrée.

1. Tapez la liste des valeurs que vous souhaitez remplacer dans la cellule d’entrée en bas d’une colonne ou sur une ligne. Laissez plusieurs lignes et colonnes vides de chaque côté des valeurs.

2. Effectuez l’une des opérations suivantes :

Si la table de données est	Procédez comme suit
Orienté colonne (vos valeurs de variable se trouvent dans une colonne)	Tapez la formule dans la cellule une ligne au-dessus et une cellule à droite de la colonne de valeurs. L’illustration de table de données à une variable indiquée dans la section Vue d’ensemble est orientée colonne et la formule est contenue dans la cellule D2. Remarque : Si vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, tapez les formules supplémentaires dans les cellules situées à droite de la première formule.
Orienté ligne (vos valeurs de variable se trouvent dans une ligne)	Tapez la formule dans la cellule 1 colonne à gauche de la première valeur et une cellule sous la ligne de valeurs Remarque : Si vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, tapez les formules supplémentaires dans les cellules situées sous la première formule.

1. Sélectionnez la plage de cellules qui contient les formules et les valeurs que vous souhaitez remplacer. D’après la première illustration de la section Vue d’ensemble précédente, cette plage est C2 :D5.

2. Cliquez sur Données > Analyse de scénarios > Table de données.

   

3. Effectuez l’une des opérations suivantes :

Si la table de données est	Procédez comme suit
Orienté colonne	Tapez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée colonne . À l’aide de l’exemple présenté dans la première illustration, la cellule d’entrée est B3.
Orienté ligne	Tapez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée de ligne.

Les formules utilisées dans une table de données à une variable doivent faire référence à la même cellule d’entrée.

1. Effectuez l’une des opérations suivantes :

Si la table de données est	Procédez comme suit
Orienté colonne (vos valeurs de variable se trouvent dans une colonne)	Tapez la nouvelle formule dans une cellule vide à droite d’une formule existante dans la ligne supérieure de la table de données.
Orienté ligne (vos valeurs de variable se trouvent dans une ligne)	Tapez la nouvelle formule dans une cellule vide sous une formule existante dans la première colonne de la table de données.

1. Sélectionnez la plage de cellules qui contient la table de données et la nouvelle formule.

2. Cliquez sur Données > Analyse de scénarios > Table de données.

   

3. Effectuez l’une des opérations suivantes :

Si la table de données est	Procédez comme suit
Orienté colonne	Tapez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée colonne .
Orienté ligne	Tapez la référence de cellule pour la cellule d’entrée dans la zone Cellule d’entrée de ligne.

Une table de données à deux variables utilise une formule qui contient deux listes de valeurs d’entrée. La formule doit faire référence à deux cellules d’entrée différentes.

1. Dans une cellule de la feuille, entrez la formule qui fait référence aux deux cellules d’entrée. Dans l’exemple suivant, dans lequel les valeurs de départ de la formule sont entrées dans les cellules B3, B4 et B5, tapez la formule =PMT(B3/12 ;B4,-B5) dans la cellule C2.

2. Tapez une liste de valeurs d’entrée dans la même colonne, sous la formule. Dans ce cas, tapez les différents taux d’intérêt dans les cellules C3, C4 et C5.

3. Entrez la deuxième liste dans la même ligne que la formule, à sa droite. Tapez les conditions du prêt (en mois) dans les cellules D2 et E2.

4. Sélectionnez la plage de cellules qui contient la formule (C2), la ligne et la colonne des valeurs (C3 :C5 et D2 :E2) et les cellules dans lesquelles vous voulez les valeurs calculées (D3 :E5). Dans ce cas, sélectionnez la plage C2 :E5.

5. Cliquez sur Données > Analyse de scénarios > Table de données.

   

6. Dans la zone Cellule d’entrée de ligne, entrez la référence à la cellule d’entrée pour les valeurs d’entrée dans la ligne. Tapez B4 dans la zone de cellule d’entrée de ligne.

7. Dans la zone Cellule d’entrée de colonne, entrez la référence à la cellule d’entrée pour les valeurs d’entrée dans la colonne. Tapez B3 dans la zone cellule d’entrée colonne .

Une table de données à deux variables peut montrer comment différentes combinaisons de taux d’intérêt et de conditions de prêt affecteront un paiement hypothécaire mensuel. Dans l’illustration suivante, la cellule C2 contient la formule de paiement, =PMT(B3/12,B4,-B5), qui utilise deux cellules d’entrée, B3 et B4.

cellule d’entrée Column.

Liste des valeurs que Excel remplace dans la cellule d’entrée de ligne, B4.

cellule d’entrée de ligne.

Liste des valeurs que Excel remplace dans la cellule d’entrée de colonne, B3.

1. Dans le menu Excel, cliquez sur Préférences.

2. Dans la section Formules et Listes, cliquez sur Calcul, puis sur Automatiquement, à l’exception des tables de données.