Cas n°1 – Table de données (a)

Nous avons déjà traité notre cas de deux façons : la recherche dichotomique pour converger rapidement vers le prix de vente optimal, et le solveur d’Excel. Il reste un troisième mode d’analyse, qui est hélas inconnu de la plupart des utilisateurs d’Excel : la table de données.

Il faut reconnaître que Microsoft a sa part de responsabilité dans l'ignorance de la table de données : cet outil, si puissant soit-il, a été hélas mal conçu et mal documenté, et ce depuis ses débuts dans Excel !

Il existe deux sortes de tables de données, aussi appelées tables d’hypothèses, des tables à 1 dimension et des tables à 2 dimensions. Dans une table à 1 dimension, il y a un paramètre et l’on peut calculer le résultat de plusieurs formules selon les valeurs de ce paramètre. Dans une table à 2 dimensions, il y a deux paramètres, mais on ne peut alors analyser les résultats que d’une seule formule.

Aujourd’hui, nous construisons une table à 1 dimension dans laquelle nous allons voir comment le prix de vente impacte les quantités vendues et la marge bénéficiaire.

Nous entrons en H8:H18 des prix de vente dégressifs de 5 € par ligne, en I7 la formule =B14 et en J7 la formule =B18. Les autres cases du tableau sont pour le moment vides. Nous sélectionnons alors le bloc H7:J18 et utilisons la séquence Outils de données – Analyse de scénarios – Table de données.

Nous remplissons alors le dialogue comme dans la copie d’écran et les résultats apparaissent aussitôt dans la table de données. Si ce n’est pas le cas, un petit calcul avec [F9] résout le problème.

Remarque 1 – C’est là où Excel n’est pas convivial ! Il faut reconnaître que « Cellule d’entrée en colonne » ne veut absolument rien dire…

Notez qu’une table de données est une structure particulièrement solide. Essayez donc de sélectionner les lignes 13 à 15 puis de les supprimer… Ou alors les colonnes H et I… On ne peut détruire que tout l’intérieur de la table : ainsi, quand vous sélectionnez I8:K19, vous avez le droit de supprimer. Mais pas avec I8:J17.

Remarque 2 – Avec la table ci-dessus, on peut converger très rapidement vers la solution optimale. Nous constatons dans la table que l’optimum se situe entre les prix de vente de 135 € et 125 €. Il suffit donc d’entrer 135 en H8 et 134 en H9 ; on tire ensuite H8:H9 vers le bas, on recalcule la table et on trouve le prix optimal à 1 € près…