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

Lorsque vous utilisez une formule complexe dépendant de plusieurs variables et que vous souhaitez comprendre efficacement comment la modification de ces entrées influence les résultats, la table de données issue de l’analyse de scénarios dans Excel constitue un outil puissant. Elle vous permet de visualiser tous les résultats possibles d’un seul coup d’œil. Voici un guide étape par étape pour créer une table de données dans Excel. Nous aborderons également quelques points essentiels pour utiliser efficacement les tables de données, ainsi que d’autres opérations telles que leur suppression, leur modification et leur recalcul.

Qu’est-ce qu’une table de données dans Excel ?

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

• Table de données orientée colonne
• Table de données orientée ligne
• Plusieurs formules dans une table de données à une variable

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

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

Autres opérations liées aux tables de données

• Supprimer une table de données
• Modifier le résultat d’une table de données
• Recalculer manuellement une table de données

Qu’est-ce qu’une table de données dans Excel ?

Dans Excel, une table de données fait partie des outils d’analyse de scénarios qui vous permettent d’expérimenter différentes valeurs d’entrée pour vos formules et d’observer les variations dans les résultats. Cet outil est particulièrement utile pour explorer divers scénarios et réaliser des analyses de sensibilité, notamment lorsque votre formule dépend de plusieurs variables.

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

Table de données à une variable : elle vous permet d’analyser l’impact de différentes valeurs d’une seule variable sur une formule. Vous pouvez configurer une table à une variable en orientation ligne ou colonne, selon que vous souhaitez faire varier votre entrée sur une ligne ou dans une colonne.

Table de données à deux variables : ce type vous permet d’observer l’effet combiné de deux variables différentes sur le résultat d’une formule. Dans une table à deux variables, les valeurs varient simultanément le long d’une ligne et d’une colonne.

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

Créer une table de données à une variable dans Excel est une compétence précieuse pour analyser l’impact des modifications d’une seule entrée sur différents résultats. Cette section vous accompagne pas à pas dans la création de tables orientées colonne, orientées ligne et comportant plusieurs formules.

Incroyable ! Utilisez Tabs dans Excel comme dans Chrome, Edge, Firefox et Safari !
Gagnez 50 % de votre temps et réduisez des milliers de clics de souris chaque jour !

---

Table de données orientée colonne

Une table orientée colonne est particulièrement utile lorsque vous souhaitez évaluer l’impact de différentes valeurs d’une seule variable sur le résultat d’une formule, ces valeurs étant organisées dans une colonne. Prenons un exemple financier simple :

Supposons que vous envisagiez un prêt de $50,000, que vous comptez rembourser sur une période de 3 ans (soit 36 mois). Afin d’évaluer quel montant mensuel serait abordable en fonction de votre salaire, vous souhaitez étudier comment différents taux d’intérêt affecteraient le montant à payer chaque mois.

Étape 1 : configurez la formule de base

Pour calculer le remboursement, j’utilise ici un taux d’intérêt de 5 %. Saisissez dans la cellule B4 la formule VPM, qui détermine automatiquement le montant du remboursement mensuel à partir du taux d’intérêt, du nombre de périodes et du montant emprunté. Voir la 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, saisissez des valeurs allant de 4 % à 11 % par incréments de 1 %, et laissez au moins une colonne vide à droite pour afficher les résultats. Voir la capture d’écran :

Étape 3 : créez la table de données

1. Dans la cellule E2, saisissez cette formule : =B4.
   
   Remarque : B4 est la cellule où se trouve votre formule principale ; c’est cette formule dont vous souhaitez observer l’évolution des résultats en faisant varier le taux d’intérêt.
   
2. Sélectionnez la plage comprenant votre formule, la liste des taux d’intérêt ainsi que les cellules adjacentes réservées aux résultats (par exemple, de D2 à E10). Voir la capture d’écran :
   
3. Accédez au Ruban, cliquez sur l’onglet Données, puis cliquez sur Analyse de scénarios>Table de données, voir la capture d’écran :
   Table de données"/>
4. Dans la boîte de dialogue Table de données, cliquez dans le champ Cellule d’entrée en colonne (puisque nous utilisons une colonne pour les valeurs d’entrée), puis sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons la cellule B1, qui contient le taux d’intérêt. Enfin, cliquez sur le bouton OK. Voir la capture d’écran :
   
5. Excel remplit automatiquement les cellules vides adjacentes à chacune de vos valeurs variables (différents taux d’intérêt) avec les résultats correspondants. Voir la capture d’écran :
   
6. Appliquez le format numérique de votre choix (Devise) aux résultats selon vos besoins. La table de données orientée colonne est désormais créée avec succès, vous permettant de parcourir rapidement les résultats pour évaluer les montants de remboursement mensuel qui resteraient abordables en cas de variation du taux d’intérêt. Voir la capture d’écran :
   

Table de données orientée ligne

Créer un tableau de données orienté lignes dans Excel consiste à organiser vos données de sorte que les valeurs des variables apparaissent sur une même ligne plutôt que dans une colonne. En vous appuyant sur l’exemple ci-dessus, suivez les étapes suivantes pour finaliser la création de votre tableau de données orienté lignes dans Excel.

Étape 1 : Répertorier les taux d’intérêt sur une ligne

Placez les valeurs des variables (taux d’intérêt) sur 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 la capture d’écran :

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

1. Dans la cellule A9, saisissez cette formule : =B4.
   
2. Sélectionnez la plage comprenant votre formule, la liste des taux d’intérêt et les cellules adjacentes destinées aux résultats (par exemple, sélectionnez A8 à I9). Ensuite, cliquez sur Données > Analyse de scénarios > Table de données.
3. Dans la boîte de dialogue Table de données, cliquez dans le champ Cellule d’entrée en ligne (puisque nous utilisons une ligne pour les valeurs d’entrée), puis sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons la cellule B1, qui contient le taux d’intérêt. Enfin, cliquez sur le bouton OK. Voir la capture d’écran :
   
4. Appliquez le format numérique de votre choix (Devise) aux résultats selon vos besoins. Le tableau de données orienté ligne est désormais créé — voir la capture d’écran :
   

Plusieurs formules dans une table de données à une variable

La création d’un tableau de données à une variable avec plusieurs formules dans Excel vous permet d’observer en temps réel comment la modification d’une seule entrée influence simultanément plusieurs résultats. Dans l’exemple ci-dessus, que se passe-t-il si vous souhaitez visualiser l’impact des variations du taux d’intérêt à la fois sur les remboursements mensuels et sur le montant total des intérêts ? Voici comment le configurer :

Étape 1 : Ajouter une nouvelle formule pour calculer le montant total des intérêts

Dans la cellule B5, saisissez la formule suivante pour calculer le montant total des intérêts :

=B4*B2*12-B3

Étape 2 : Organiser le Données source 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 la capture d’écran :

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

1. Dans la cellule E2, saisissez cette formule :=B4 afin de créer une référence vers le calcul du remboursement dans les données d’origine.
2. Dans la cellule F2, saisissez cette formule : =B5 afin de créer une référence vers le montant total des intérêts dans les données d’origine.
   
3. Sélectionnez la plage comprenant vos formules, la liste des taux d’intérêt et les cellules adjacentes destinées aux résultats (par exemple, de D2 à F10). Ensuite, cliquez sur Données > Analyse de scénarios > Table de données.
4. Dans la boîte de dialogue Table de données, cliquez dans le champ Cellule d’entrée en colonne (puisque nous utilisons une colonne pour les valeurs d’entrée), puis sélectionnez la cellule variable référencée dans votre formule. Dans cet exemple, nous sélectionnons la cellule B1, qui contient le taux d’intérêt. Enfin, cliquez sur le bouton OK. Voir la capture d’écran :
   
5. Appliquez le format numérique de votre choix (Devise) aux résultats, selon vos besoins. Vous pouvez désormais visualiser les résultats de chaque formule en fonction des différentes valeurs de la variable.
   

Libérez la magie d’Excel avec Kutools IA

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez des données et créez des graphiques — le tout grâce à des commandes simples.
• Formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
• Programmation VBA : Rédigez et implémentez du code VBA en toute simplicité.
• Interprétation de formules : Comprenez facilement les formules complexes.
• Traduction de texte : supprimez les barrières linguistiques au sein de vos feuilles de calcul.

Améliorez vos capacités Excel grâce à des outils alimentés par l’IA.Téléchargez dès maintenantet découvrez une efficacité inégalée !

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

Un tableau de données à deux variables dans Excel affiche l’impact combiné de deux séries de valeurs variables sur le résultat d’une formule, illustrant ainsi comment la modification simultanée de deux entrées influe sur ce résultat.

Ici, j’ai réalisé un croquis simple pour vous aider à mieux visualiser l’apparence et la structure d’un tableau de données à deux variables.

En poursuivant l’exemple utilisé pour créer un tableau à une variable, découvrez maintenant comment créer un tableau de données à deux variables dans Excel.

Dans le jeu de données ci-dessous, nous disposons du taux d’intérêt, de la durée du prêt et du montant emprunté, et avons également calculé le paiement mensuel à l’aide de cette formule :=-PMT($B$1/12; $B$2*12; $B$3). Nous allons ici nous concentrer sur deux variables clés de nos données : le taux d’intérêt et le montant du prêt, afin d’observer comment leurs variations simultanées influencent les montants des remboursements.

Étape 1 : Configurer les deux variables modifiables

1. Dans une colonne, listez les différents taux d’intérêt que vous souhaitez tester. Voir la capture d’écran :
   
2. Dans une ligne, saisissez les différents montants de prêt juste au-dessus des valeurs en colonne (à partir de la cellule située immédiatement à droite de la cellule contenant la formule), voir la capture d’écran :
   

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

1. Placez votre formule à l’intersection de la ligne et de la colonne où vous avez listé vos valeurs variables. Ici, je saisis la formule suivante : =B4 dans la cellule E2. Consultez la capture d’écran :
   
2. Sélectionnez la plage incluant les montants de prêt, les taux d’intérêt, la cellule contenant la formule ainsi que celles destinées à afficher les résultats.
   
3. Ensuite, cliquez sur Données > Analyse de scénarios > Table de données. Dans la boîte de dialogue Table de données :
   • Dans la zone Cellule d’entrée en ligne, sélectionnez la référence de la cellule correspondant à l’entrée des valeurs variables en 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 la cellule correspondant à la cellule d’entrée des valeurs variables en colonne (B1 contient le taux d’intérêt).
   • Cliquez ensuite sur le bouton OK.
     
4. Excel remplit désormais la table de données avec les résultats correspondant à chaque combinaison de montant de prêt et de taux d’intérêt, vous permettant de visualiser instantanément l’impact de ces variables sur votre remboursement mensuel — un atout précieux pour une planification et une analyse financières efficaces.
   
5. Enfin, appliquez aux résultats le format numérique de votre choix (Devise) selon vos besoins.
   

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

• La table de données nouvellement créée doit se trouver dans la même feuille de calcul que les données d’origine.
• Les résultats de la table de données dépendent de la cellule contenant la formule définie dans le Données source ; toute modification apportée à cette cellule met automatiquement à jour les résultats.
• Une fois les valeurs calculées à l’aide de la table de données, elles ne peuvent pas être annulées avec Ctrl + Z. Vous pouvez toutefois les sélectionner manuellement dans leur ensemble et les supprimer.
• La table de données génère des formules matricielles ; il est donc impossible de modifier ou de supprimer individuellement les cellules qu’elle contient.

Autres opérations liées aux tables de données

Une fois votre tableau de données créé, vous devrez peut-être effectuer des opérations supplémentaires pour le gérer efficacement : le supprimer, modifier ses résultats ou le recalculer manuellement.

Supprimer une table de données

Comme les résultats du tableau de données sont calculés à l’aide d’une formule matricielle, vous ne pouvez pas supprimer une valeur individuelle du tableau. Vous ne pouvez supprimer que l’intégralité du tableau.

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

Modifier le résultat d’une table de données

En effet, il est impossible de modifier directement des cellules individuelles dans un tableau de données, car celles-ci contiennent des formules matricielles générées automatiquement par Excel.

Pour apporter des modifications, vous devrez généralement supprimer le tableau existant, puis en recréer un nouveau intégrant les ajustements souhaités — ce qui implique de modifier votre formule de base ou vos valeurs d’entrée, puis de reconfigurer le tableau de données pour refléter ces changements.

Recalculer manuellement une table de données

Normalement, Excel recalcule toutes les formules de 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 effectue automatiquement les calculs de tous les tableaux de données à chaque modification dans le classeur, vous pouvez passer le mode de calcul de « Automatique » à « Manuel ». Procédez comme suit :

Accédez à l’onglet Formules, puis cliquez sur Options de calcul>Automatique sauf pour les tables de données, voir la capture d’écran :

Dès que vous modifiez ce paramètre, Excel n’actualisera plus automatiquement les calculs de vos tableaux de données lors du recalcul complet de votre classeur.

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

En suivant les étapes décrites dans ce guide et en gardant à l’esprit les points essentiels, vous maîtriserez efficacement les tableaux de données pour répondre à vos besoins dans Analyse des données. Envie de découvrir davantage d’astuces et de conseils Excel ? Notre site web propose des milliers de tutoriels ; cliquez ici pour y accéder. Merci de votre lecture, et nous espérons continuer à vous offrir des contenus utiles à l’avenir !