comment créer et utiliser un tableau de données dans Excel

le tutoriel montre comment utiliser des tableaux de données pour l’analyse de simulation dans Excel. Découvrez comment créer une table à une variable et deux variables pour voir les effets d’une ou deux valeurs d’entrée sur votre formule, et comment configurer une table de données pour évaluer plusieurs formules à la fois.

Vous avez construit une formule complexe dépendant de plusieurs variables et vous voulez savoir comment la modification de ces entrées modifie les résultats., Au lieu de tester chaque variable individuellement, créez un tableau de données D’analyse de simulation et observez tous les résultats possibles d’un simple coup d’œil!

• table de Données dans Excel
• Créer une variable tableau de données
• Faire deux variables tableau de données
• tableau de Données pour évaluer de multiples formules
• Excel tableaux de données – 3 choses que vous devez savoir
• Supprimer une table de données dans Excel
• Modifier les données de la table de résultats
• Recalculer les données de la table manuellement

qu’est Ce qu’un tableau de données dans Excel?,

dans Microsoft Excel, un tableau de données est l’un des outils D’analyse de simulation qui vous permet d’essayer différentes valeurs d’entrée pour les formules et de voir comment les modifications de ces valeurs affectent la sortie des formules.

Les tables de données sont particulièrement utiles lorsqu’une formule dépend de plusieurs valeurs et que vous souhaitez expérimenter différentes combinaisons d’entrées et comparer les résultats.

Actuellement, il existe une table de données variables et deux tables de données variables. Bien que limité à un maximum de deux cellules d’entrée, d’un tableau de données vous permet de tester autant de valeurs de variables que vous le souhaitez.,

Comment faire pour créer une variable de tableau de données dans Excel

Une variable de tableau de données dans Excel permet de tester une série de valeurs pour une seule cellule d’entrée et montre comment ces valeurs influencer le résultat d’une formule.,

pour vous aider à mieux comprendre cette fonctionnalité, nous allons suivre un exemple spécifique plutôt que de décrire des étapes génériques.

supposons que vous envisagiez de déposer vos économies dans une banque, qui paie 5% d’intérêt qui se compose mensuellement. Pour vérifier différentes options, vous avez construit le calculateur d’intérêt composé suivant où:

• B8 contient la formule FV qui calcule le solde de clôture.
• B2 est la variable que vous souhaitez tester (investissement initial).,

et maintenant, faisons une simple analyse de simulation pour voir quelles seront vos économies dans 5 ans en fonction du montant de votre investissement initial, allant de 1 000 $à 6 000$.

Voici les étapes pour rendre une variable tableau de données:

1. Entrez les valeurs des variables, soit dans une colonne ou sur une ligne. Dans cet exemple, nous allons créer un tableau de données orienté colonne, nous tapons donc nos valeurs de variables dans une colonne (D3:D8) et laissons au moins une colonne vide à droite pour les résultats.,
2. Tapez votre formule dans la cellule une ligne au-dessus et une cellule à droite des valeurs des variables (E2 dans notre cas). Ou, liez cette cellule à la formule de votre jeu de données d’origine (si vous décidez de changer la formule à l’avenir, vous n’aurez besoin de mettre à jour qu’une seule cellule). Nous avons choisi la dernière option, et entrer dans cette simple formule en E2: =B8
   
   Astuce., Si vous souhaitez examiner l’impact des valeurs des variables sur d’autres formules qui se réfèrent à la même cellule d’entrée, entrez la ou les formules supplémentaires à droite de la première formule, comme indiqué dans cet exemple.
3. sélectionnez la plage de table de données, y compris votre formule, les cellules de valeurs variables et les cellules vides pour les résultats (D2:E8).,
4. allez dans L’onglet Données > Groupe Outils de données, cliquez sur le bouton Analyse de simulation, puis cliquez sur Tableau de données…
   
5. Dans la fenêtre de dialogue Tableau de données, cliquez dans la boîte cellule D’entrée de colonne (parce que nos valeurs D’investissement sont dans Dans cet exemple, nous sélectionnons B3 qui contient la valeur d’investissement initiale.
   
6. cliquez sur OK, et Excel remplira immédiatement les cellules vides avec des résultats correspondant à la valeur de la variable dans la même ligne.,
7. appliquez le format numérique souhaité aux résultats (devise dans notre cas), et vous êtes prêt à partir!

Maintenant, vous pouvez jeter un coup d’œil à votre table de données à une variable, examiner les soldes possibles et choisir la taille optimale du dépôt:

tableau de données orienté ligne

L’exemple ci-dessus montre comment configurer un tableau de données vertical ou orienté colonne, Si vous préférez une disposition horizontale, voici ce que vous devez faire:

1. tapez les valeurs des variables dans une ligne, en laissant au moins une colonne vide à gauche (pour la formule) et une ligne vide ci-dessous (pour les résultats). Pour cet exemple, nous entrons les valeurs des variables dans les cellules F3: J3.
2. entrez la formule dans la cellule qui est une colonne à gauche de votre première valeur de variable et une cellule ci-dessous (E4 dans notre cas).,
3. créez un tableau de données comme indiqué ci-dessus, mais entrez la valeur d’entrée (B3) dans la boîte de cellule D’entrée de ligne:
   
4. cliquez sur OK, et vous obtiendrez le résultat suivant:
   

comment créer un tableau de données à deux variables>

un tableau de données à deux variables montre comment différentes combinaisons de 2 ensembles de valeurs variables affectent le résultat de la formule. En d’autres termes, il montre comment la modification de deux valeurs d’entrée de la même formule modifie la sortie.,

Les étapes pour créer deux variables tableau de données dans Excel sont fondamentalement les mêmes que dans l’exemple ci-dessus, sauf que vous entrez deux plages de valeurs d’entrée possibles, l’une dans une rangée et une autre dans une colonne.

pour voir comment cela fonctionne, utilisons le même calculateur d’intérêt composé et examinons les effets de la taille de l’investissement initial et du nombre d’années sur le solde. Pour ce faire, configurez votre table de données de cette manière:

1. entrez votre formule dans une cellule vide ou liez cette cellule à votre formule d’origine., Assurez-vous d’avoir suffisamment de colonnes vides à droite et de lignes vides ci-dessous pour accueillir vos valeurs de variables. Comme précédemment, nous lions la cellule E2 à la formule FV d’origine qui calcule le solde: =B8
2. tapez un ensemble de valeurs d’entrée sous la formule, dans la même colonne (Valeurs d’investissement dans E3:E8).
3. Entrez l’autre ensemble de valeurs de variables à droite de la formule, dans la même ligne (nombre d’années en F2:H2).,

   à ce stade, votre table de données à deux variables devrait ressembler à ceci:
   

4. sélectionnez toute la plage de la table de données, y compris la formule, la ligne et la colonne des valeurs des variables, et les cellules dans lesquelles les valeurs calculées apparaîtront. Nous sélectionnons la gamme E2: H8.,
5. créez un tableau de données de la manière déjà familière: onglet Données> bouton D’analyse de simulation> tableau 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 de variables dans la ligne (dans
7. dans la zone cellule D’entrée de colonne, entrez la référence à la cellule d’entrée pour les valeurs de variable dans la colonne (B3 contenant la valeur D’investissement initiale).
8. cliquez sur OK.,n Notre cas), et analyser les résultats:

   

tableau de données pour comparer plusieurs résultats

Si vous souhaitez évaluer plus d’une formule en même temps, construisez votre tableau de données comme indiqué dans les exemples précédents, et entrez la(Les) formule (S) supplémentaire (s):

• à droite de colonnes
• sous la première formule dans le cas d’une table de données horizontale organisée en lignes

pour que la table de données « multi-formules » fonctionne correctement, toutes les formules doivent se référer à la même cellule d’entrée.,

à titre d’exemple, ajoutons une autre formule à notre tableau de données à une variable pour calculer l’intérêt et voir comment il est affecté par la taille de l’investissement initial. Voici ce que nous faisons:

1. dans la cellule B10, calculez l’intérêt avec cette formule: =B8-B3
2. arrangez les données source de la table de données comme nous l’avons fait précédemment: valeurs variables dans D3:D8 et E2 liés à B8 (formule D’équilibre).,
3. ajoutez une colonne supplémentaire à la plage de table de données (colonne F) et liez F2 à B10 (formule d’intérêt):
   
4. sélectionnez la plage de table de données étendue (D2:F8).
5. ouvrez la boîte de dialogue Tableau de données en cliquant sur L’onglet Données > analyse de simulation > tableau de données…
6. dans la zone cellule D’entrée de colonne, fournissez la cellule d’entrée (B3) et cliquez sur OK.,

Voilà, vous pouvez maintenant observer les effets de vos valeurs de variables sur les deux formules:

tableau de données dans Excel – 3 choses à savoir

pour utiliser efficacement les tableaux de données dans Excel, gardez à l’Esprit ces 3 faits simples:

1. Pour même feuille que le tableau de données.,
2. Microsoft Excel utilise la fonction TABLE (row_input_cell, colum_input_cell) pour calculer les résultats de la table de données:
   • Dans la table de données à une variable, l’un des arguments est omis, selon la disposition (orientée colonne ou orientée ligne). Par exemple, dans notre table de données horizontale à une variable, la formule est =TABLE(, B3) où B3 est la cellule d’entrée de colonne.
   • Dans la table de données à deux variables, les deux arguments sont en place. Par exemple, =TABLE(B6, B3) où B6 est la cellule d’entrée de ligne et B3 est la cellule d’entrée de colonne.,

   la fonction TABLE est entrée en tant que formule de tableau. Pour vous en assurer, sélectionnez n’importe quelle cellule avec la valeur calculée, regardez la barre de formule et notez les {accolades} autour de la formule. Cependant, ce n’est pas une formule de tableau normale – vous ne pouvez pas la taper dans la barre de formule ni en modifier une existante. C’est juste « pour le spectacle ».

3. étant donné que les résultats de la table de données sont calculés avec une formule de tableau, les cellules résultantes ne peuvent pas être modifiées individuellement. Vous pouvez uniquement modifier ou supprimer l’ensemble du tableau de cellules comme expliqué ci-dessous.,

comment supprimer un tableau de données dans Excel

Comme mentionné ci-dessus, Excel n’autorise pas la suppression de valeurs dans les cellules individuelles contenant les résultats. Chaque fois que vous essayez de le faire, un message d’erreur « Impossible de changer une partie d’une table de données » s’affiche.

cependant, vous pouvez facilement effacer l’ensemble du tableau des valeurs résultantes. Voici comment:

1. en fonction de vos besoins, sélectionnez Toutes les cellules du tableau de données ou uniquement les cellules avec les résultats.
2. Appuyez sur la touche Suppr.

Fait!, 🙂

comment modifier les résultats du tableau de données

comme il n’est pas possible de modifier une partie d’un tableau dans Excel, vous ne pouvez pas modifier des cellules individuelles avec des valeurs calculées. Vous ne pouvez remplacer toutes ces valeurs que par la vôtre en effectuant ces étapes:

1. sélectionnez Toutes les cellules résultantes.
2. supprimer la formule de TABLE dans la barre de formule.
3. tapez la valeur souhaitée et appuyez sur Ctrl + Entrée.,

ceci insérera la même valeur dans toutes les cellules sélectionnées:

Une fois que la formule de TABLE a disparu, l’ancienne table de données devient une plage habituelle, et vous êtes libre de modifier n’importe quelle cellule individuelle normalement.

comment recalculer manuellement la table de données

Si une grande table de données avec plusieurs valeurs de variables et formules ralentit votre Excel, vous pouvez désactiver les recalculs automatiques dans cette table et dans toutes les autres tables de données.,

pour cela, allez dans L’onglet Formules> groupe de calcul, cliquez sur le bouton Options de calcul, puis cliquez sur Automatique sauf Tables de données.

cela désactivera les calculs automatiques de table de données et accélérera les recalculs de l’ensemble du classeur.

pour recalculer manuellement votre table de données, sélectionnez ses cellules résultantes, c’est-à-dire les cellules avec les formules TABLE (), et appuyez sur F9.

Voici comment vous créez et utilisez un tableau de données dans Excel., Pour regarder de plus près les exemples discutés dans ce tutoriel, vous pouvez télécharger notre classeur exemples de tableaux de données Excel. Je vous remercie d’avoir lu et je serais heureux de vous revoir la semaine prochaine!

• what-if analyse avec l’Objectif de Chercher dans Excel
• Comment utiliser le Solveur Excel: étape par étape, avec des exemples
• Comment grouper des lignes dans Excel