Comment créer un tableau de données à une variable dans Excel 2016

Les tableaux de données vous permettent d'entrer une série de valeurs possibles qu'Excel 2016 branche ensuite dans une seule formule. Une table de données à une variable substitue une série de valeurs possibles à une seule valeur d'entrée dans une formule.

La figure suivante montre une feuille de calcul des prévisions de ventes 2017 pour laquelle un tableau de données à une variable doit être créé. Dans cette feuille de calcul, le montant des ventes prévu dans la cellule B5 est calculé en ajoutant le total des ventes de l'année dernière dans la cellule B2 au montant qu'il devrait augmenter en 2017 (calculé en multipliant le total de l'année dernière dans la cellule B2 par le pourcentage de croissance dans la cellule B3 ), vous donnant la formule

Feuille de calcul de projection des ventes avec une colonne de pourcentages de croissance possibles à connecter à un tableau de données à une variable.

=B2+(B2*B3)

Étant donné que vous avez cliqué sur le bouton de commande Créer à partir de la sélection dans l'onglet Formules du ruban après avoir sélectionné A2:B5 et accepté la case à cocher de la colonne de gauche par défaut, la formule utilise les en-têtes de ligne de la colonne A et lit :

=Ventes_2016+(Ventes_2016*Croissance_2017)

Comme vous pouvez le voir sur cette figure, une colonne de taux de croissance possibles allant de 1 % jusqu'à 5,0 % a été inscrite dans la colonne B dans la plage B8:B24.

Feuille de calcul de projection des ventes après avoir créé le tableau de données à une variable dans la plage C8:C24.

Pour créer le tableau de données à une variable qui associe chacune de ces valeurs à la formule de croissance des ventes, suivez ces étapes simples :

Copiez la formule d'origine entrée dans la cellule B5 dans la cellule C7 en tapant = (égal à), puis en cliquant sur la cellule B5 pour créer la formule =Projected_Sales_2017.

La copie de la formule d'origine (pour remplacer la série de taux de croissance différents dans B8:B24) est désormais l'en-tête de colonne du tableau de données à une variable.

Sélectionnez la plage de cellules B7:C24.

La plage de la table de données comprend la formule ainsi que les différents taux de croissance.

Cliquez sur Data→What-If Analysis→Data Table sur le ruban.

Excel ouvre la boîte de dialogue Table de données.

Cliquez sur la zone de texte Cellule d'entrée de colonne dans la boîte de dialogue Table de données, puis cliquez sur la cellule B3, la cellule Growth_2017 avec le pourcentage d'origine, dans la feuille de calcul.

Excel insère l'adresse de cellule absolue, $B$3, dans la zone de texte Cellule d'entrée de colonne.

Cliquez sur OK pour fermer la boîte de dialogue Table de données.

Dès que vous cliquez sur OK, Excel crée le tableau de données dans la plage C8:C24 en entrant une formule à l'aide de sa fonction TABLE dans cette plage. Chaque copie de cette formule dans le tableau de données utilise le pourcentage de taux de croissance dans la même ligne de la colonne B pour déterminer le résultat possible.

Cliquez sur la cellule C7, puis sur le bouton de commande Format Painter dans le groupe Presse-papiers de l'onglet Accueil et faites glisser la plage de cellules C8:C24.

Excel copie le format du numéro de comptabilité dans la plage de résultats possibles calculés par ce tableau de données.

Quelques points importants à noter sur le tableau de données à une variable créé dans cette feuille de calcul :

• Si vous modifiez des pourcentages de taux de croissance dans la plage de cellules B8:B24, Excel met immédiatement à jour le résultat des ventes projetées associé dans le tableau de données. Pour empêcher Excel de mettre à jour la table de données tant que vous n'avez pas cliqué sur le bouton de commande Calculer maintenant (F9) ou Calculer la feuille (Maj+F9) dans l'onglet Formules, cliquez sur le bouton Options de calcul dans l'onglet Formules, puis sur le bouton Automatique sauf pour les tables de données option (Alt+MXE).

• Si vous essayez de supprimer une seule formule TABLE dans la plage de cellules C8:C24, Excel affiche une alerte Impossible de modifier une partie d'une table de données. Vous devez sélectionner toute la gamme de formules (C8:C24 dans ce cas) avant d'appuyer sur Supprimer ou de cliquer sur le bouton Effacer ou Supprimer dans l'onglet Accueil.