Lorsque vous disposez d'une formule complexe dépendant de plusieurs variables et que vous souhaitez comprendre l'impact efficace de la modification de ces entrées sur les résultats, un tableau de données d'analyse de simulation dans Excel est un outil puissant. Il vous permet de voir tous les résultats possibles d’un simple coup d’œil. Voici un guide étape par étape sur la façon de créer un tableau de données dans Excel. De plus, nous aborderons certains points clés pour utiliser efficacement les tableaux de données et démontrerons d'autres opérations telles que la suppression, la modification et le recalcul des tableaux de données.

Qu’est-ce qu’un tableau de données dans Excel ?

Créer un tableau de données à une variable

• Table de données orientée colonnes
• Table de données orientée lignes
• Plusieurs formules pour un tableau de données à une variable

Créer un tableau de données à deux variables

Points clés utilisant les tableaux de données

Autres opérations d'utilisation des tables de données

• Supprimer une table de données
• Modifier le résultat du tableau de données
• Recalculer manuellement le tableau de données

Qu’est-ce qu’un tableau de données dans Excel ?

Dans Excel, un tableau de données est l'un des outils d'analyse de simulation qui vous permet d'expérimenter différentes valeurs d'entrée pour les formules et d'observer les modifications dans la sortie de la formule. Cet outil est incroyablement utile pour explorer divers scénarios et effectuer des analyses de sensibilité, en particulier lorsqu'une formule dépend de plusieurs variables.

Il existe deux types de tableaux de données dans Excel :

Tableau de données à une variable: Cela vous permet d'analyser l'impact des différentes valeurs d'une variable sur une formule. Vous pouvez configurer un tableau de données à une variable dans un format orienté ligne ou colonne, selon que vous souhaitez faire varier votre entrée sur une ligne ou dans une colonne.

Tableau de données à deux variables: Ce type vous permet de voir l'impact de la modification de deux variables différentes sur le résultat d'une formule. Dans un tableau de données à deux variables, vous faites varier les valeurs à la fois sur une ligne et sur une colonne.

Créer un tableau de données à une variable

La création d'un tableau de données à une variable dans Excel est une compétence précieuse pour analyser l'impact des modifications apportées à une seule entrée sur divers résultats. Cette section vous guidera tout au long du processus de création de tableaux de données à une variable orientés colonnes, orientés lignes et à formules multiples.

Incroyable! Utiliser des onglets efficaces dans Excel comme Chrome, Edge, Firefox et Safari !
Économisez 50% de votre temps et réduisez chaque jour des milliers de clics de souris!

---

Table de données orientée colonnes

Un tableau de données orienté colonnes est utile lorsque vous souhaitez tester l'impact des différentes valeurs d'une variable unique sur le résultat d'une formule, les valeurs des variables étant répertoriées dans une colonne. Prenons un exemple financier simple :

Supposons que vous envisagez un prêt de 50,000 3 $ que vous prévoyez rembourser sur une période de 36 ans (l'équivalent de XNUMX mois). Afin d'évaluer quel paiement mensuel serait abordable en fonction de votre salaire, vous souhaitez explorer l'impact des différents taux d'intérêt sur le montant que vous devez payer chaque mois.

Étape 1 : Configurer la formule de base

Pour calculer le paiement, je fixerai ici les intérêts à 5 %. Dans une cellule B4, saisissez la formule PMT, elle calcule le paiement mensuel en fonction du taux d'intérêt, du nombre de périodes et du montant du prêt. Voir capture d'écran :

= -PMT($B$1/12, $B$2*12, $B$3)

Étape 2 : répertoriez les taux d’intérêt dans une colonne

Dans une colonne, listez les différents taux d’intérêt que vous souhaitez tester. Par exemple, répertoriez les valeurs de 4 % à 11 % par incréments de 1 % dans une colonne et laissez au moins une colonne vide à droite pour les résultats. Voir capture d'écran :

Étape 3 : Créer la table de données

1. Dans la cellule E2, saisissez cette formule : = B4.
   
   Notes: B4 est la cellule où se trouve votre formule principale, c'est la formule dont vous souhaitez voir les résultats changer à mesure que vous faites varier le taux d'intérêt.
   
2. Sélectionnez la plage qui comprend votre formule, la liste des taux d'intérêt et les cellules adjacentes pour les résultats (par exemple, sélectionnez D2 à E10). Voir capture d'écran :
   
3. Allez sur le ruban, cliquez sur le Données onglet, puis cliquez sur What-If Analysis > Table de données, voir capture d'écran:
   
4. Dans le Table de données boîte de dialogue, cliquez dans la Cellule de saisie de colonne (puisque nous utilisons une colonne pour les valeurs d'entrée) et sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons B1 qui contient le taux d'intérêt. Enfin, cliquez OK bouton, voir capture d'écran:
   
5. Excel remplira automatiquement les cellules vides adjacentes à chacune de vos valeurs variables (différents taux d'intérêt) avec les résultats correspondants. Voir capture d'écran :
   
6. Appliquez le format numérique souhaité aux résultats (devise) selon vos besoins. Désormais, le tableau de données orienté colonnes est créé avec succès et vous pouvez parcourir rapidement les résultats pour évaluer quels montants de paiement mensuels seraient abordables pour vous lorsque le taux d'intérêt change. Voir capture d'écran :
   

Table de données orientée lignes

La création d'un tableau de données orienté lignes dans Excel implique d'organiser vos données de manière à ce que les valeurs des variables soient répertoriées sur une ligne plutôt que dans une colonne. En prenant l'exemple ci-dessus comme référence, passons aux étapes pour terminer la création d'un tableau de données orienté lignes dans Excel.

Étape 1 : répertoriez les taux d’intérêt d’affilée

Placez les valeurs des variables (taux d'intérêt) dans une ligne, en vous assurant qu'il y a au moins une colonne vide à gauche pour la formule et une ligne vide en dessous pour les résultats, voir capture d'écran :

Étape 2 : Créer la table de données

1. Dans la cellule A9, saisissez cette formule : = B4.
   
2. Sélectionnez la plage qui comprend votre formule, la liste des taux d'intérêt et les cellules adjacentes pour les résultats (par exemple, sélectionnez A8 à I9). Puis clique Données > What-If Analysis > Table de données.
3. Dans le Table de données boîte de dialogue, cliquez dans la Cellule d'entrée de ligne (puisque nous utilisons une ligne pour les valeurs d’entrée) et sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons B1 qui contient le taux d'intérêt. Enfin, cliquez OK bouton, voir capture d'écran:
   
4. Appliquez le format numérique souhaité aux résultats (devise) selon vos besoins. Maintenant, la table de données orientée lignes est créée, voir capture d'écran :
   

Plusieurs formules pour un tableau de données à une variable

La création d'un tableau de données à une variable avec plusieurs formules dans Excel vous permet de voir comment la modification d'une seule entrée affecte plusieurs formules différentes à la fois. Dans l’exemple ci-dessus, que se passe-t-il si vous souhaitez voir l’évolution des taux d’intérêt à la fois sur les remboursements et sur le total des intérêts ? Voici comment le configurer :

Étape 1 : Ajoutez une nouvelle formule pour calculer les intérêts totaux

Dans la cellule B5, saisissez la formule suivante pour calculer les intérêts totaux :

=B4*B2*12-B3

Étape 2 : Organiser les données sources de la table de données

Dans une colonne, répertoriez les différents taux d'intérêt que vous souhaitez tester et laissez au moins deux colonnes vides à droite pour les résultats. Voir capture d'écran :

Étape 3 : Créez le tableau de données :

1. Dans la cellule E2, saisissez cette formule : = B4 pour créer une référence au calcul du remboursement dans les données d'origine.
2. Dans la cellule F2, saisissez cette formule : = B5 pour créer une référence à l'intérêt total dans les données originales.
   
3. Sélectionnez la plage qui comprend vos formules, la liste des taux d'intérêt et les cellules adjacentes pour le résultat (par exemple, sélectionnez D2 à F10). Puis clique Données > What-If Analysis > Table de données.
4. Dans le Table de données boîte de dialogue, cliquez dans la Cellule d'entrée de colonne (puisque nous utilisons une colonne pour les valeurs d'entrée) et sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons B1 qui contient le taux d'intérêt. Enfin, cliquez OK bouton, voir capture d'écran:
   
5. Appliquez le format numérique souhaité aux résultats (devise) selon vos besoins. Et vous pouvez voir les résultats de chaque formule en fonction des différentes valeurs des variables.
   

Créer un tableau de données à deux variables

Un tableau de données à deux variables dans Excel affiche l'impact de différentes combinaisons de deux ensembles de valeurs de variables sur le résultat d'une formule, démontrant comment les modifications apportées à deux entrées d'une formule affectent simultanément son résultat.

Ici, j'ai dessiné un croquis simple pour vous aider à mieux comprendre l'apparence et la structure d'un tableau de données à deux variables.

En nous basant sur l'exemple de création d'un tableau de données à une variable, passons maintenant à l'apprentissage de la création d'un tableau de données à deux variables dans Excel.

Dans l'ensemble de données ci-dessous, nous avons le taux d'intérêt, la durée du prêt et le montant du prêt, et avons calculé le paiement mensuel en utilisant cette formule : =-PMT($B$1/12, $B$2*12, $B$3) aussi. Ici, nous nous concentrerons sur deux variables clés de nos données : le taux d’intérêt et le montant du prêt, pour observer comment les changements de ces deux facteurs affectent simultanément les montants de remboursement.

Étape 1 : Configurer les deux variables changeantes

1. Dans une colonne, listez les différents taux d’intérêt que vous souhaitez tester. voir capture d'écran :
   
2. Dans une ligne, saisissez les différentes valeurs du montant du prêt juste au-dessus des valeurs des colonnes (en commençant par une cellule à droite de la cellule de formule), voir capture d'écran :
   

Étape 2 : Créer la table de données

1. Placez votre formule à l'intersection de la ligne et de la colonne où vous avez répertorié les valeurs des variables. Dans ce cas, je saisirai cette formule : = B4 dans la cellule E2. Voir capture d'écran :
   
2. Sélectionnez la plage qui comprend les montants de votre prêt, les taux d'intérêt, la cellule de formule et les cellules dans lesquelles les résultats seront affichés.
   
3. Puis clique Données > What-If Analysis > Table de données. Dans la boîte de dialogue Table de données :
   • Dans le Cellule d'entrée de ligne , sélectionnez la référence de cellule à la cellule d'entrée pour les valeurs de variable dans la ligne (dans cet exemple, B3 contient le montant du prêt).
   • Dans le Cellule d'entrée de colonnel, sélectionnez la référence de cellule à la cellule d'entrée pour les valeurs des variables dans la colonne (B1 contient le taux d'intérêt).
   • Et puis, cliquez OK Bouton.
     
4. Désormais, Excel remplira le tableau de données avec les résultats pour chaque combinaison de montant du prêt et de taux d’intérêt. Il offre une vue directe de la façon dont différentes combinaisons de montants de prêt et de taux d’intérêt affectent le paiement mensuel, ce qui en fait un outil précieux pour la planification et l’analyse financières.
   
5. Enfin, vous devez appliquer le format numérique souhaité aux résultats (devise) selon vos besoins.
   

Points clés utilisant les tableaux de données

• Le tableau de données nouvellement créé doit se trouver dans la même feuille de calcul que les données d'origine.
• La sortie du tableau de données dépend de la cellule de formule dans l'ensemble de données source, et toute modification apportée à cette cellule de formule mettra automatiquement à jour la sortie.
• Une fois les valeurs calculées à l'aide du tableau de données, elles ne peuvent plus être annulées avec Ctrl + Z. Cependant, vous pouvez sélectionner manuellement toutes les valeurs et les supprimer.
• Le tableau de données génère des formules matricielles, de sorte que les cellules individuelles du tableau ne peuvent pas être modifiées ou supprimées.

Autres opérations d'utilisation des tables de données

Une fois que vous avez créé une table de données, vous devrez peut-être effectuer des opérations supplémentaires pour la gérer efficacement, notamment la suppression de la table de données, la modification de ses résultats et l'exécution de recalculs manuels.

Supprimer une table de données

Étant donné que les résultats du tableau de données sont calculés avec une formule matricielle, vous ne pouvez pas supprimer une valeur individuelle du tableau de données. Cependant, vous ne pouvez supprimer que l'intégralité du tableau de données.

Sélectionnez toutes les cellules du tableau de données ou uniquement les cellules contenant les résultats, puis appuyez sur Supprimer touche du clavier.

Modifier le résultat du tableau de données

En effet, il n'est pas possible de modifier directement des cellules individuelles dans un tableau de données, car elles contiennent des formules matricielles générées automatiquement par Excel.

Pour apporter des modifications, vous devrez généralement supprimer la table de données existante, puis en créer une nouvelle avec les modifications souhaitées. Cela implique d'ajuster votre formule de base ou les valeurs d'entrée, puis de reconfigurer le tableau de données pour refléter ces changements.

Recalculer manuellement le tableau de données

Normalement, Excel recalcule toutes les formules de tous les classeurs ouverts à chaque fois qu'une modification est apportée. Si un grand tableau de données contenant de nombreuses valeurs de variables et des formules complexes ralentit votre classeur Excel.

Pour éviter qu'Excel effectue automatiquement des calculs pour tous les tableaux de données à chaque fois qu'une modification est apportée au classeur, vous pouvez faire passer le mode de calcul d'Automatique à Manuel. Veuillez procéder comme suit :

Allez à Formules onglet, puis cliquez sur Options de calcul > Automatique sauf pour les tableaux de données, voir capture d'écran:

Une fois ce paramètre modifié, lorsque vous recalculez l'intégralité de votre classeur, Excel ne mettra plus automatiquement à jour les calculs dans vos tableaux de données.

Si vous devez mettre à jour votre tableau de données manuellement, sélectionnez simplement les cellules où les résultats (cellules contenant les formules TABLE()) sont affichés, puis appuyez sur F9 clé.

En suivant les étapes décrites dans ce guide et en gardant les points clés à l'esprit, vous pouvez utiliser efficacement les tableaux de données pour vos besoins d'analyse de données. Si vous souhaitez découvrir davantage de trucs et astuces Excel, notre site Web propose des milliers de didacticiels, veuillez cliquer ici pour y accéder. Merci d'avoir lu et nous sommes impatients de vous fournir des informations plus utiles à l'avenir !