Comment la table de données dans Microsoft Excel vous permet de comparer les résultats

Vous avez toujours voulu essayer différentes valeurs pour une formule afin de voir quelle sortie vous obtenez pour chacune? Le tableau de données dans Excel est un excellent outil pour avoir un aperçu des possibilités.

Analyse des hypothèses dans Excel: le tableau de données

Les outils d'analyse des hypothèses dans Excel sont un excellent ensemble de fonctions qui vous permettent de prévoir le changement de vos données de sortie concernant le changement de vos valeurs. Excel dispose de trois outils d'analyse des hypothèses: le gestionnaire de scénarios, la recherche d'objectifs et le tableau de données.

L'outil Tableau de données vous permet de voir l'impact des différentes entrées sur le résultat de votre formule. Vous pouvez utiliser la table de données pour obtenir une perspective de votre formule et voir quelle sortie vous pouvez obtenir à partir de différentes entrées.

Fonctionnement de la table de données

Le tableau de données dans Excel prend un ensemble d'entrées, les place dans votre formule et crée enfin un tableau des sorties pour chaque entrée.

Pour utiliser la table de données dans Excel, vous devez d'abord préparer une formule. Ensuite, vous pouvez vous référer à la formule dans une autre cellule et y travailler la table de données. Enfin, vous pouvez alimenter la table de données en deux ensembles de données: la cellule d'entrée de ligne et la cellule d'entrée de colonne .

Le tableau de données prendra alors les valeurs de la ligne adjacente comme entrée pour la cellule d'entrée de ligne et les valeurs de la colonne adjacente comme entrée pour la cellule d'entrée de colonne et créera un tableau des sorties de la formule.

Lié: Comment créer un tableau croisé dynamique dans Excel

Exemple 1: tableau de données à deux variables

Pour cet exemple, supposons que vous ayez six petites voitures avec des prix différents et que vous souhaitiez savoir combien de revenus vous obtiendrez en vendant des quantités données.

Pour ce faire, vous devez multiplier le nombre de petites voitures vendues d'un type par le prix, puis enfin soustraire le taux de taxe pour générer des revenus.

Donc, dans l'ensemble, pour ce tableau de données, vous aurez deux entrées variables: la quantité et le prix. Commençons par créer la formule:

1. Dans les cellules A1 , B1 , C1 et D1 , saisissez respectivement Prix , Quantité , Taxe et Revenu .
2. Sélectionnez les cellules A1 et D1 .
3. Dans l'onglet Accueil , dans la section Nombres , cliquez sur le symbole $ pour modifier la mise en forme des nombres de ces cellules en comptabilité (c'est parce que ces cellules abriteront des valeurs comptables).
4. Sélectionnez la cellule C2 .
5. Dans l'onglet Accueil , dans la section Nombres , cliquez sur le symbole % pour modifier la mise en forme des nombres de cette cellule en pourcentage .
6. Sélectionnez la cellule D2 sous Revenu et entrez la formule suivante dans la barre de formule, puis appuyez sur Entrée:

    =(A2*B2)-(A2*B2*C2)

   Cette formule multipliera le prix des unités vendues (A2) par leur quantité (B2), puis elle en soustraira la valeur fiscale (A2 * B2 * C2).

Vous pouvez aller de l'avant et donner des exemples de valeurs aux cellules et observer pendant qu'Excel calcule les revenus tirés des ventes.

En relation: Comment masquer ou afficher des colonnes et des lignes dans Excel

Tableau de données des différentes entrées d'Excel

Pour créer une table de données pour différentes entrées, vous devez disposer d'une table nue contenant les deux entrées.

1. Sélectionnez la cellule G2 et entrez la formule ci-dessous dans la barre de formule:

    =D2

   Cela définira la cellule G2 égale à la formule que vous avez créée précédemment.

2. Dans les cellules sous G2 (colonne G), saisissez les quantités possibles de pièces vendues. Pour cet exemple, les nombres sont 5, 10, 15, 20, 25 et 30.
3. Dans les cellules à côté de G2 (ligne 2), saisissez les prix de chaque pièce. Pour cet exemple, les prix sont 10, 20, 30, 40, 50 et 60.
4. Sélectionnez les cellules dans lesquelles vous avez inséré les prix et les cellules ci-dessous, qui afficheront les revenus possibles, et modifiez leur formatage des nombres en comptabilité .

Enfin, maintenant que vous avez défini la ligne et la colonne, il est temps de transformer cette table en table de données.

1. Sélectionnez le tableau en cliquant sur G1 et en le faisant glisser jusqu'à M7 .
2. Accédez à l' onglet Données et dans la section Prévision, cliquez sur Analyse des hypothèses . Une liste de trois éléments apparaîtra.
3. Dans la liste, sélectionnez Table de données . Cela fera apparaître la boîte de dialogue Table de données.
4. Dans la cellule d'entrée Ligne, entrez A2 . La ligne du tableau contient les prix et l'entrée de prix dans votre formule d'origine est la cellule A2.
5. Dans la cellule d'entrée Colonne, entrez B2 . La colonne du tableau contient les quantités de pièces vendues.
6. Une fois que vous avez défini les deux entrées, cliquez sur OK . Excel va maintenant générer un tableau de données.

Vous avez maintenant un tableau de données pour votre formule! Le tableau de données vous fournit des informations précieuses sur vos ventes potentielles.

Par exemple, vous pouvez maintenant voir comment vous pouvez obtenir un revenu de 1000 $ ou plus en regardant les six cellules dans le coin inférieur droit. Ou vous pouvez comparer et savoir que la vente de 25 voitures miniatures à 20 $ vous rapportera plus de revenus que la vente de 15 voitures miniatures à 30 $.

Exemple 2: Table de données à une variable

Étant donné que la table de données est, après tout, une table, elle ne peut contenir que des entrées dans une ligne et une colonne. Cela signifie que vous ne pouvez pas avoir plus de deux entrées variables dans une table de données. Cependant, vous pouvez certainement en avoir moins de deux: Une table de données avec une seule entrée de variable.

Pour cet exemple, gardez l'exemple précédent à l'esprit. Cependant, cette fois, supposons que vous souhaitiez obtenir un tableau des revenus potentiels exclusivement pour les petites voitures à 50 $.

La méthode est toujours la même que celle du tableau de données à deux variables, bien que le positionnement soit légèrement différent.

Connexes: Comment copier des formules dans Microsoft Excel

Pour commencer à créer la table de données, vous devez créer une formule. La formule de cet exemple est la même que la précédente. Une fois que vous avez la formule prête, il est temps de présenter les données.

1. Sélectionnez la cellule H1 et dans la barre de formule, entrez la formule ci-dessous et appuyez sur Entrée :

    =D2

2. Entrez les nombres dans les cellules G2 et ci-dessous. Pour cet exemple, entrez 5, 10, 15, 20, 25 et 30 dans les cellules G2 à G7 .

Il est maintenant temps de créer la table de données.

1. Sélectionnez le tableau en cliquant sur G1 et en le faisant glisser vers H7 .
2. Allez dans l' onglet Données , et dans la section Prévisions, cliquez sur Analyse des hypothèses .
3. Dans la liste What-If Analysis, sélectionnez Data Table .
4. Dans la boîte de dialogue Table de données, cliquez sur la cellule d'entrée de colonne et tapez B2 .
5. Laissez la cellule d'entrée Row vide.
6. Puisque l'objectif est d'obtenir un tableau de données sur les revenus pour un prix fixe, il vous suffit de renseigner le tableau de données avec le nombre de petites voitures vendues et non leurs prix. La quantité est indiquée dans la ligne du tableau de données et son entrée dans la formule est la cellule B2.
7. Cliquez sur OK . Excel créera un tableau de données à une variable.

Vous pouvez maintenant déterminer les revenus que vous pouvez générer en vendant des voitures miniatures à 50 $, moins les taxes, bien sûr.

Définissez les possibilités dans un tableau

Le tableau de données vous donne un bon aperçu du résultat de votre formule avec différentes entrées, et vous savez maintenant comment en créer une.

La table de données n'est que l'un des outils d'analyse hypothétique d'Excel. Il y a plus à apprendre si vous voulez répondre à toutes vos questions de simulation avec Excel.