Tableau de données dans Excel : Créer des tableaux de données à une variable et à deux variables

Lorsque vous avez une formule complexe dépendant de plusieurs variables et que vous souhaitez comprendre comment les modifications de ces entrées influencent efficacement les résultats, un tableau de données d'analyse "What-If" ("Et si") dans Excel est un outil puissant. Il vous permet de voir tous les résultats possibles en un 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 discuterons de 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

• Tableau de données orienté colonne
• Tableau de données orienté ligne
• Formules multiples pour un tableau de données à une variable

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

Points clés pour utiliser les tableaux de données

Autres opérations pour l'utilisation des tableaux de données

• Supprimer un tableau de données
• Modifier le résultat d’un tableau de données
• Recalculer manuellement un 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 "What-If" qui vous permet d'expérimenter différentes valeurs d'entrée pour des formules et d'observer les changements dans la sortie de la formule. Cet outil est incroyablement utile pour explorer divers scénarios et effectuer des analyses de sensibilité, surtout 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 comment différentes valeurs d'une seule variable influenceront une formule. Vous pouvez configurer un tableau de données à une variable au 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 le long d'une ligne et d'une colonne.

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

Créer un tableau de données à une variable dans Excel est une compétence précieuse pour analyser comment les changements d'une seule entrée peuvent impacter divers résultats. Cette section vous guidera à travers le processus de création de tableaux de données orientés colonnes, lignes et avec plusieurs formules à une variable.

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

---

Tableau de données orienté colonne

Un tableau de données orienté colonne est utile lorsque vous souhaitez tester comment différentes valeurs d'une seule variable influencent la sortie d'une formule, avec les valeurs des variables listées dans une colonne. Prenons un exemple financier simple :

Supposons que vous envisagiez un prêt de 50 000 $, que vous prévoyez de rembourser sur une période de 3 ans (équivalent à 36 mois). Afin d'évaluer quel paiement mensuel serait abordable en fonction de votre salaire, vous êtes intéressé par l'exploration de la manière dont divers taux d'intérêt affecteraient le montant que vous devez payer chaque mois.

Étape 1 : Configurer la formule de base

Pour calculer le paiement, ici, je vais fixer l'intérêt à 5 %. Dans une cellule B4, entrez la formule PMT, qui calcule le paiement mensuel basé sur le taux d'intérêt, le nombre de périodes et le montant du prêt. Voir capture d'écran :

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

Étape 2 : Listez 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, listez des valeurs allant 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éez le tableau de données

1. Dans la cellule E2, entrez cette formule : =B4.
   
   Remarque : B4 est la cellule où se trouve votre formule principale, c'est la formule dont vous souhaitez voir les résultats changer lorsque vous faites varier le taux d'intérêt.
   
2. Sélectionnez la plage qui inclut 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 dans le ruban, cliquez sur l'onglet Données , puis cliquez sur Analyse What-If > Tableau de données, voir capture d'écran :
   Tableau de données"/>
4. Dans la boîte de dialogue Tableau de données , cliquez dans la zone Cellule d'entrée en 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 sur OK 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. Maintenant, le tableau de données orienté colonne est créé avec succès, et vous pouvez rapidement parcourir 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 :
   

Tableau de données orienté ligne

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

Étape 1 : Listez les taux d'intérêt dans une ligne

Placez les valeurs des variables (taux d'intérêt) dans une ligne, en veillant à laisser 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éez le tableau de données

1. Dans la cellule A9, entrez cette formule : =B4.
   
2. Sélectionnez la plage qui inclut votre formule, la liste des taux d'intérêt et les cellules adjacentes pour les résultats (par exemple, sélectionnez A8 à I9). Ensuite, cliquez sur Données > Analyse What-If > Tableau de données.
3. Dans la boîte de dialogue Tableau de données , cliquez dans la zone Cellule d'entrée en 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 sur OK voir capture d'écran :
   
4. Appliquez le format numérique souhaité aux résultats (Devise) selon vos besoins. Maintenant, le tableau de données orienté ligne est créé, voir capture d'écran :
   

Formules multiples pour un tableau de données à une variable

Créer 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 voulez voir le changement des taux d'intérêt à la fois sur les remboursements et les intérêts totaux ? Voici comment procéder :

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

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

=B4*B2*12-B3

Étape 2 : Organisez les données sources du tableau de données

Dans une colonne, listez 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, entrez cette formule : =B4 pour créer une référence au calcul du remboursement dans les données originales.
2. Dans la cellule F2, entrez cette formule : =B5 pour créer une référence aux intérêts totaux dans les données originales.
   
3. Sélectionnez la plage qui inclut vos formules, la liste des taux d'intérêt et les cellules adjacentes pour les résultats (par exemple, sélectionnez D2 à F10). Ensuite, cliquez sur Données > Analyse What-If > Tableau de données.
4. Dans la boîte de dialogue Tableau de données , cliquez dans la zone Cellule d'entrée en 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 sur OK 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 pour chaque formule basés sur les valeurs variables variantes.
   

Découvrez la magie d'Excel avec Kutools AI

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
• Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
• Codage VBA : Écrivez et implémentez du code VBA sans effort.
• Interprétation des formules : Comprenez facilement des formules complexes.
• Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !

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 variables sur le résultat d'une formule, montrant comment les modifications simultanées de deux entrées d'une formule affectent 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 reprenant l'exemple de création d'un tableau de données à une variable, apprenons maintenant à créer 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 nous avons calculé le paiement mensuel en utilisant cette formule : =-PMT($B$1/12, $B$2*12, $B$3) également. 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 dans ces deux facteurs affectent simultanément les montants de remboursement.

Étape 1 : Configurez 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, entrez les différentes valeurs du montant du prêt juste au-dessus des valeurs de la colonne (en commençant d'une cellule à droite de la cellule de la formule), voir capture d'écran :
   

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

1. Placez votre formule à l'intersection de la ligne et de la colonne où vous avez listé les valeurs des variables. Dans ce cas, je vais entrer cette formule : =B4 dans la cellule E2. Voir capture d'écran :
   
2. Sélectionnez la plage qui inclut vos montants de prêt, taux d'intérêt, la cellule de formule et les cellules où les résultats seront affichés.
   
3. Ensuite, cliquez sur Données > Analyse What-If > Tableau de données. Dans la boîte de dialogue Tableau de données :
   • Dans la zone Cellule d'entrée en ligne, sélectionnez la référence de cellule vers la cellule d'entrée pour les valeurs variables dans la ligne (dans cet exemple, B3 contient le montant du prêt).
   • Dans la zone Cellule d'entrée en colonne, sélectionnez la référence de cellule vers la cellule d'entrée pour les valeurs variables dans la colonne (B1 contient le taux d'intérêt).
   • Et ensuite, cliquez sur OK Bouton.
     
4. Maintenant, Excel remplira le tableau de données avec les résultats pour chaque combinaison de montant de prêt et de taux d'intérêt. Cela fournit une vue directe de la manière 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 financière et l'analyse.
   
5. Enfin, vous devriez appliquer le format numérique souhaité aux résultats (Devise) selon vos besoins.
   

Points clés pour utiliser les tableaux de données

• Le tableau de données nouvellement créé doit être dans la même feuille de calcul que les données originales.
• 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 que les valeurs ont été calculées à l'aide du tableau de données, elles ne peuvent pas ê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, donc les cellules individuelles dans le tableau ne peuvent pas être modifiées ou supprimées.

Autres opérations pour l'utilisation des tableaux de données

Une fois que vous avez créé un tableau de données, vous pourriez avoir besoin d'opérations supplémentaires pour le gérer efficacement, y compris la suppression du tableau de données, la modification de ses résultats et l'exécution de recalculs manuels.

Supprimer un tableau 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 pouvez uniquement supprimer l'ensemble du tableau de données.

Sélectionnez toutes les cellules du tableau de données ou seulement les cellules avec les résultats, puis appuyez sur la touche Suppr du clavier.

Modifier le résultat d’un 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 le tableau de données existant, puis en créer un nouveau avec les modifications souhaitées. Cela implique d'ajuster votre formule de base ou les valeurs d'entrée, puis de configurer à nouveau le tableau de données pour refléter ces changements.

Recalculer manuellement un tableau de données

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

Pour éviter qu'Excel n'effectue automatiquement des calculs pour tous les tableaux de données à chaque modification dans le classeur, vous pouvez passer le mode de calcul de Automatique à Manuel. Veuillez procéder comme suit :

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

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

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

En suivant les étapes décrites dans ce guide et en gardant à l'esprit les points clés, vous pouvez utiliser efficacement les tableaux de données pour vos besoins d'analyse de données. Si vous êtes intéressé par l'exploration de plus d'astuces et conseils Excel, notre site Web propose des milliers de tutoriels, veuillez cliquer ici pour y accéder. Merci d'avoir lu, et nous espérons vous fournir plus d'informations utiles à l'avenir !