Modifier automatiquement des variables pour atteindre d'autres objectifs

Question

Bonjour,

J'aurais besoin de votre aide afin de trouver le moyen de faire des calculs inversés cohérents.

J'ai créé 4 feuillets (et je souhaiterais conserver ces 4 feuillets et non un seul).

Le 1er feuillet (Multi) contient deux séries de nombres :

En colonne A des données fixes et en colonne B des données qui devront varier.

Le feuillet 2 (Variables) comprends les chiffres qui font varier les chiffres du feuillet Multi (colonne B).

Le 3ème feuillet (Chiffre) correspond aux chiffres qui sont fixes. Le 4ème feuillet comprend:

• en colonne A : des chiffres fixes
• en colonne B : les chiffres qui auront été modifiés via les variables.
• en colonne C : la multiplication de la colonne A et B et qui indique chacune (en C1 et C5) le chiffre résultant de cette multiplication et qui sera par conséquent modifié puisque le chiffre de la colonne B indiquera le chiffre du 1er feuillet (colonne B) qui aura été modifié via le feuillet 2.

L'idée est de pouvoir modifier automatiquement les chiffres résultant de la multiplication (en C1 et C5) par d'autres objectifs que j'ai indiqués dans l'Excel en assurant une cohérence entre les 2 objectifs modifiés qui auront été obtenu grâce à la modification des 2 cellules variables du feuillet 2.

Concrètement, en le faisant manuellement (c’est-à-dire en modifiant les chiffres des variables) avec en A1 = 7 et B1=2. Cela permet d'optimiser les objectifs avec en C1 = 4,5 et C5 =3,5 (dans le feuillet Calcul).

Il n'est sans doute pas possible d'arriver à 5 en C1 et à 3 en C5 (comme demandé dans le fichier Excel) mais l'objectif est de tendre à + ou - 1. En l’espèce, la différence avec chacun des objectifs souhaités (5 et 3 prévus au départ) n’est que de 0,5 pour chaque objectif (puisqu’on arrive à 4,5 et 3,5).

Je vous communique en pj le fichier Excel pour une meilleure compréhension : https://www.cjoint.com/c/OBbkMyOZQHb

Je vous remercie de l'aide que vous pourrez m'apporter.

Restant à votre disposition pour toute information complémentaire.

Christophe

Answer 1

Cette réponse a été automatiquement traduite. Par conséquent, il peut y avoir des erreurs grammaticales ou des formulations étranges.

Salut Christophe PGG

C’est probablement impossible à réaliser.

Si vous souhaitez qu’Excel réponde à vos besoins, les termes « ajustement automatique » et « au plus près » que vous avez mentionnés manquent d’interprétabilité et de précision. Ils doivent être convertis en formules ou en organigrammes pour devenir des tâches qu’Excel peut comprendre et exécuter.

Si l’on prend l’exemple du fichier que vous avez fourni, dans le tableau des variables, les cellules A1 et B1 affectent non seulement les résultats de la feuille de calcul, mais elles ont également une relation de dépendance mutuelle. Cela signifie qu’il existe une infinité de combinaisons numériques qui permettent d’obtenir ce que vous appelez « aussi proche que possible de la cible » (C1 calculé comme 4,5, C5 calculé comme 3,5). Excel ne peut pas choisir spontanément l’ensemble auquel s’adapter à moins que des connaissances préalables plus approfondies ne soient données.

J’espère que cette explication vous aidera à comprendre.

Sincères salutations

Jonathan Z - MSFT

Answer 2

Bonjour Jonathan Z,

Je vous remercie pour ces explications.

L'idée n'est pas de créer des restrictions pour une plage de données ou de faire manuellement la recherche des variables pour arriver aux résultats en C1 et C5 dans le feuillet "Calcul".

Ce que je souhaiterais c'est qu'Excel fasse le calcul automatiquement en s'approchant le plus possible des objectifs à atteindre qui pour C1 est de 5 et pour C5 est 3.

Dans le fichier Excel communiqué, le feuillet "Calcul" indique en C1 = 3,5 et en C5=1.

Je souhaiterais qu'Excel puisse modifier les variables du feuillet "Variables" en A1 et B1 afin que C1 et C5 du feuillet "Calcul" affichent 4,5 et 3,5 (car ce sont les chiffres qui sont les plus proches des objectifs définis à savoir : pour C1, l'objectif de 5 et pour C5, l'objectif de 3).

Est-ce possible qu'Excel réalise ces ajustements via les variables pour atteindre (presque) les objectifs souhaités ?

Merci pour votre aide.

Bien à vous,

Christophe

Answer 3

Cette réponse a été automatiquement traduite. Par conséquent, il peut y avoir des erreurs grammaticales ou des formulations étranges.

Salut Christophe PGG

En prenant l’exemple de votre fichier exemple envoyé, afin de faire le résultat de calcul de C1 dans la table de calcul entre 4 et 6, et le résultat de calcul de C5 entre 2 et 4, vous pouvez en fait résoudre les inégalités simultanément pour obtenir l’ensemble de calcul de A1 dans la table de variables entre 4 et 8, tandis que la gamme de B1 est comprise entre 8-A1 et 12-A1. Définissez la plage de validation des données ci-dessus pour A1 et B1 dans la table Variables, afin que les utilisateurs ne puissent pas saisir de valeurs en dehors de cette plage (si la validation des données échoue, elle reviendra à la valeur antérieure à l’entrée, vous pouvez prédéfinir une valeur initiale par défaut ici, par exemple, 6 pour A1 et 4 pour B1).

La méthode et le calcul décrits ci-dessus sont basés sur une prémisse importante : la table Chiffre et la table Multi sont fixes et immuables. Si leurs valeurs changent, entraînant des résultats différents dans la colonne B de la table Calcul, la validation des données dans la table Variables ne sera plus applicable.

Sincères salutations

Jonathan Z - MSFT

Answer 4

Bonjour Jonathan Z,

Je vous remercie pour ces explications.

Pourriez-vous me montrer la formule permettant d'atteindre en C1 = 4,5 et en C5 =3,5 (dans la feuille Calcul) via la modification des cellules en A1 et B1 du feuillet Variables ?

Vous écrivez : « Lorsque la saisie de valeurs excessivement élevées ou trop faibles dans la table Variables entraîne le dépassement de la limite par le résultat de calcul cible dans la table Calcul, vous devez ajuster automatiquement les variables correspondantes dans la table Variables en fonction de cette restriction », cela vous oblige à fournir la plage pour l’ajustement automatique"

Si les valeurs ne sont pas atteignables (comme dans mon Excel, 5 et 3 étaient les objectifs initiaux mais non atteignables), je souhaiterais pouvoir préciser qu'Excel peut ajuster les objectifs à + ou - 0,5 ou 1 ou 1,5... pour qu'Excel arrivent aux objectifs au plus près.

Est-ce possible d'intégrer cela dans une formule ?

Merci beaucoup pour l'aide que vous pourrez m'apporter.

Je reste à votre disposition pour toute information complémentaire.

Cordialement,

Christophe

Answer 5

Cette réponse a été automatiquement traduite. Par conséquent, il peut y avoir des erreurs grammaticales ou des formulations étranges.

Salut Christophe PGG ,

Merci de visiter la communauté Microsoft.

Le résultat de calcul de C1 dans la table de calcul doit être maintenu entre 4 et 6. En fait, nous pouvons calculer directement les limites numériques des cellules correspondantes dans la table Variables sur la base de cette condition aux limites. La formule de calcul de C1 n’est pas complexe, et la relation avec les cellules dans les variables est même linéaire.

De même, vous pouvez calculer l’intervalle numérique d’une autre cellule de la table Variables en fonction des valeurs minimale et maximale du résultat du calcul C5. En prenant l’intersection de ces deux plages, vous pouvez obtenir toutes les plages possibles de la table Variables qui répondent aux exigences. Sur la base de cette plage, configurez la validation des données pour la table Variables afin de vous assurer que les variables d’entrée restent toujours dans la plage.

Si votre idée est la suivante : « Lorsque la saisie de valeurs excessivement élevées ou trop faibles dans la table Variables entraîne le dépassement de la limite par le résultat de calcul cible dans la table Calcul, vous devez ajuster automatiquement les variables correspondantes dans la table Variables en fonction de cette restriction », cela vous oblige à fournir la plage pour l’ajustement automatique (mais en fait, Si vous disposez déjà de cette plage spécifique, vous pouvez l’utiliser directement comme restriction d’entrée pour les cellules variables). Cela peut également nécessiter l’utilisation de VBA pour y parvenir.

Sinceres salutations

Jonathan Z - MSFT | Spécialiste du support communautaire Microsoft