Afficher tout

Masquer tout

Une table de données est une plage de cellules qui montre comment la modification d’une ou deux variables dans vos formules (formule : suite de valeurs, références de cellule, noms, fonctions ou opérateurs dans une cellule permettant de générer une nouvelle valeur. Une formule commence toujours par le signe égal (=).) peut affecter le résultat de ces formules. Les tables de données fournissent un raccourci permettant de calculer plusieurs résultats en une seule opération ainsi qu’un mode d’affichage et de comparaison des résultats de toutes les différentes variations dans votre feuille de calcul.

• Présentation

• Créer une table de données à une variable

• Ajouter une formule à une table de données à une variable

• Créer une table de données à deux variables

• Accélérer le calcul dans une feuille de calcul contenant des tables de données

Présentation

Les tables de données font partie d’une série de commandes qui sont appelées outils d’analyse de simulation. Lorsque vous utilisez des tables de données, vous effectuez une analyse de simulation.

L’analyse de simulation est le processus qui consiste à modifier des valeurs dans les cellules pour voir comment ces modifications affectent le résultat des formules dans la feuille de calcul. Par exemple, vous pouvez utiliser une table de données pour faire varier le taux d’intérêt et la longueur des échéances utilisés dans un prêt afin de déterminer le montants des paiements mensuels possibles.

Types d’analyses de simulation  Il existe trois types d’outils d’analyses de simulation dans Excel : les scénarios, les tables de données et la Valeur cible. Les scénarios et les tables de données prennent des valeurs d’entrée et déterminent les résultats possibles. la Valeur cible fonctionne à l’inverse des deux autres dans le sens qu’elle prend un résultat et détermine les valeurs d’entrée possibles qui produisent ce résultat.

Comme les 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 vous montrent tous les résultats dans une table dans une feuille de calcul. L’utilisation des tables de données permet d’examiner plus facilement une plage 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 de tableau.

Une table de données ne peut pas contenir plus de deux variables. Pour analyser plus de deux variables, il est préférable d’utiliser des scénarios. Bien qu’elle soit limitée à seulement une ou deux variables (une pour la cellule d’entrée de ligne et l’autre pour la cellule d’entrée de colonne), une table de données peut inclure autant de différentes valeurs de variable que vous le souhaitez. 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.

Pour plus d’informations sur les outils d’analyse de simulation, cliquez sur les liens situés dans la section Voir aussi.

Notions de base des tables de données

Vous pouvez créer des tables de données à une ou deux variables, suivant le nombre de variables et de formules que vous voulez tester.

Tables de données à une variable  Utilisez une table de données à une variable si vous voulez voir comment différentes valeurs d’une variable dans une ou plusieurs formules modifiera le résultat 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 remboursement mensuel à l’aide de la fonction VPM. Vous entrez les valeurs de la variable dans une colonne ou une ligne, et les résultats s’affichent dans une colonne ou une ligne adjacente.

Dans la figure suivante, la cellule D2 contient la formule de remboursement =VPM(B3/12,B4,-B5) qui fait référence à la cellule d’entrée B3.

Tables de données à deux variables  Utilisez une table de données à deux variables pour voir comment différentes valeurs de deux variables dans une formule modifieront les résultats de cette formule. Par exemple, vous pouvez utiliser une table de données à deux variables pour voir comment différentes combinaisons du taux d’intérêt et des échéances de prêt affecteront un remboursement mensuel.

Dans la figure suivante, la cellule C2 contient la formule de remboursement =VPM(B3/12,B4,-B5) qui utilise les deux cellules d’entrée B3 et B4.

Calculs de tables de données     Les tables de données sont recalculées en même temps qu’une feuille de calcul, même si elles n’ont pas été modifiées. Pour accélérer le calcul d’une feuille de calcul contenant une table de données, vous pouvez modifier les options de Calcul de façon à recalculer automatiquement la feuille de calcul sans les tables de données. Voir Accélérer le calcul dans une feuille de calcul contenant des tables de données.

 Haut de la page

Créer une table de données à une variable

Une table de données à une variable a des valeurs d’entrée répertoriées dans une colonne (orientée en colonne) ou sur une ligne (orientée en ligne). Les formules utilisées dans une table de données à une variable doivent faire référence à une seule cellule d’entrée (cellule d'entrée : cellule dans laquelle chaque valeur d'entrée issue d'une table de données est insérée. N'importe quelle cellule de feuille de calcul peut servir de cellule d'entrée. Bien qu'il ne soit pas nécessaire que la cellule d'entrée fasse partie de la table de données, les formules des tables de données doivent faire référence à la cellule d'entrée.).

1. Tapez la liste des valeurs que vous souhaitez remplacer dans la cellule d’entrée dans une colonne ou sur une ligne. Laissez quelques lignes et colonnes vides de chaque côté des valeurs.
2. Effectuez l'une des actions suivantes :
   • S’il s’agit d’une table de données orientée en colonne (les valeurs de la variable sont répertoriées en colonne), tapez la formule dans la cellule une ligne au-dessus et une cellule à droite de la colonne des valeurs. La figure de la table des données à une variable illustrée dans la section Vue d’ensemble est orientée en colonne, et la formule est contenue dans la cellule D2.

     Si vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, tapez les formules supplémentaires dans les cellules à droite de la première formule.

   • S’il s’agit d’une table de données en ligne (les valeurs de la variable sont affichées sur une ligne), tapez la formule dans la cellule située une colonne à gauche de la première valeur et une cellule sous la ligne des valeurs.

     Si vous souhaitez examiner les effets de différentes valeurs sur d’autres formules, tapez les formules supplémentaires dans les cellules sous la première formule.

3. Sélectionnez la plage de cellules contenant les formules et les valeurs que vous souhaitez remplacer. Selon la première figure de la section Vue d’ensemble précédente, cette plage est C2:D5.
4. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Analyse de scénarios, puis sur Table de données.
5. Effectuez l'une des actions suivantes :
   • S’il s’agit d’une table de données orientée en colonne, tapez la référence de la cellule (référence de cellule : ensemble de coordonnées correspondant à l'emplacement d'une cellule dans une feuille de calcul. Par exemple, la référence de la cellule qui se trouve à l'intersection de la colonne B et de la ligne 3 est B3.) de la cellule d’entrée dans la zone Cellule d’entrée en colonne. Dans l’exemple illustré dans la première figure, la cellule d’entrée est B3.

   • S'il s'agit d'une table de données en ligne, entrez la référence de la cellule d'entrée dans la zone Cellule d'entrée en ligne.

 Remarque   Une fois la table de données créée, vous pouvez avoir besoin de modifier le format des cellules de résultat. Dans la figure, les cellules de résultat sont au format monétaire.

 Haut de la page

Ajouter une formule à une table de données à une variable

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 actions suivantes :
   • S’il s’agit d’une table de données en colonne (les valeurs de la variable sont dans une colonne), tapez la nouvelle formule dans une cellule vide à droite d’une formule existante, sur la première ligne de la table.

   • S’il s’agit d’une table de données en ligne (les valeurs de la variables sont sur une ligne), tapez la nouvelle formule dans une cellule vide sous une formule existante, dans la première colonne de la table.

2. Sélectionnez la plage de cellules contenant la table de données et la nouvelle formule.
3. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Analyse de scénarios, puis sur Table de données.
4. Effectuez l'une des actions suivantes :
   • S’il s’agit d’une table orientée en colonne, entrez la référence de cellule de la cellule d’entrée dans la zone Cellule d’entrée en colonne.

   • S'il s'agit d'une table de données en ligne, entrez la référence de la cellule d'entrée dans la zone Cellule d'entrée en ligne.

 Haut de la page

Créer une table de données à deux variables

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 de calcul, tapez la formule faisant 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, vous devez taper la formule =VPM(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 cet exemple, tapez les différents taux d’intérêt dans les cellules C3, C4 et C5. 

3. Entrez la seconde liste dans la même ligne que la formule, à sa droite.

   Tapez les échéances de 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 de valeurs (C3:C5 et D2:E2) et les cellules dans lesquelles vous voulez que les valeurs soient calculées (D3:E5).

   Dans cet exemple, sélectionnez la plage C2:E5.

5. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Analyse de scénarios, puis sur Table de données.
6. Dans la zone Cellule d'entrée en ligne, tapez la référence de la cellule d'entrée pour les valeurs d'entrée dans la ligne.

   Tapez cellule B4 dans la zone Cellule d’entrée en ligne.

7. Dans la zone Cellule d'entrée en colonne, tapez la référence de la cellule d'entrée pour les valeurs d'entrée dans la colonne.

   Tapez B3 dans la zone Cellule d’entrée en colonne.

8. Cliquez sur OK.

Exemple  Une table de données à deux variables peut afficher la manière dont différentes combinaisons de taux d’intérêt et d’échéances de prêt affectent un remboursement d’emprunt. Dans la figure suivante, la cellule C2 contient la formule de remboursement =VPM(B3/12,B4,-B5) qui utilise deux cellule d’entrée, B3 et B4.

 Haut de la page

Accélérer le calcul dans une feuille de calcul contenant des tables de données

1. Cliquez sur le Bouton Microsoft Office , sur Options Excel, puis sur la catégorie Formules
2. Dans la section Mode de calcul, sous Calculer, cliquez sur Automatiquement sauf dans les tableaux de données.

    Conseil   Autrement, vous pouvez cliquer sur la flèche Options de calcul, dans le groupe Calcul de l'onglet Formules, puis cliquer sur Automatiquement sauf dans les tables de données.

 Remarque   Si vous sélectionnez cette option de calcul, les tables de données sont ignorées tandis que le reste du classeur est recalculé. Pour recalculer manuellement votre table de données, sélectionnez ses formules, puis appuyez sur F9.

 Haut de la page