Google Sheet recherche d'une somme

Question

En prenant le tableur suivant comme exemple: https://docs.google.com/spreadsheets/d/1CCykl0FeOv4LftgviHqEEB_WjnYBZ1Gcb-OJ1r9TS6k/edit?usp=sharing Je cherche à trouver les "Niveaux" (Qui vont ici de 1 à 100) et qui correspondent à un certains nombres de type "N4" niveau 6 de la "List1" vaut 36. La somme des "points" correspondant aux niveaux doivent être égale au nombre de la cellule "A9". (La "List1" et "List1B" servent ensemble et il n'est pas forcé de trouver des points partout, comme "N13" peut très bien être de niveau 0. Par contre, si par exemple "N5" le niveau est 4, tous les "points" des niveaux précédent doivent être ajouter. Comme "N5" de niveau 2 vaut 7.) Le soucis c'est que la somme rechercher (Ici "A9") peut varier de plus ou moins 10 points maximum. (Impossible d'avoir la réel valeur, des décimales sont ajouter pour qu'au calcul il soit très dur (En tout cas ça l'est pour moi) de trouver la combinaison de "niveaux" pour avoir les "points" correspondant.) C'est le même principe pour la "List2" (Sauf qu'il n'y a qu'une liste et qu'elle correspond à une somme différente à trouver ici en "A14") Je ne sais pas si c'est assez clair, en tout cas je l'espère, n'hésitez pas à me poser des questions, je répondrais au mieux. Merci d'avance, en espérant que vous réussissiez la ou moi j'ai échoué...

сталиевич · Answer

Il faut partager le fichier...

сталиевич · Answer

Pas très clair pour moi ... je bute sur cette phrase qui n'est pas grammaticalement correcte. si tu pouvais mettre un exemple ... même si dans ton exemple la somme ne vaut pas exactement le contenu de la cellule A9...

сталиевич · Answer

Ok, casse-tête chinois !! j'ai 2 questions : peux-tu expliquer la philosophie du calcul, à quoi cela sert ? quelle est la logique ? pourquoi en H18 de search on a =IFERROR(IF($H$9-A18=0;0;HLOOKUP($H$9-A18;List1B!$D$1:$CY$9;2;FALSE));0) et pas =IFERROR(IF($H$9-A18=0;0;HLOOKUP($H$9-A18;List1!$D$1:$CY$9;8;FALSE));0) pas sûr que j'y arrive car pour faire des simulations, aujourd'hui, cela passe par le feuille de calcul elle-même et google va mettre trop de temps; il faudrait pouvoir le faire en script pur ... comment le fais-tu aujourd'hui ? à la main ...

сталиевич · Answer

Je n'ai pas beaucoup avancé, cela me rappelle l'histoire du nombre 42, résultat de la somme de 3 nombres au cube !! 1 million d'heures de calcul.

https://www.pourlascience.fr/sd/mathematiques/les-secrets-du-nombre-42-18744.php

Ce fut le résultat d’un calcul faramineux coordonné par Andrew Booker et Andrew Sutherland. Des ordinateurs participant au réseau Charity Engine d’ordinateurs personnels calculant pendant l’équivalent de plus de 1 million d’heures découvrirent que :

42 = (– 80538738812075974)3 + 804357581458175153 + 126021232973356313

сталиевич · Answer

J'ai repris une copie de ton fichier que j'ai simplifiée de mon côté pour m'approprier les formules et la logique de calcul.

J'ai remplacé les HLOOKUP par des fonctions de type

=if(B$3-$A6<=0;0;index(List2!$1:$15;column();B$3-$A6+1))

qui sont plus facilement intégrables dans un script.

сталиевич · Answer

Voici une (première) solution

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('↓ Calculer ... ↓')
    .addItem('Lancer le calcul ...', 'calculer')
    .addToUi();
}
function calculer() {
  var list2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('List2').getDataRange().getValues()
  var cible = list2[0][0]
  var result=[]
  var sum=0
  for (var i=1;i<list2.length;i++){
    var prov=maxi(list2[i],cible-sum)
    sum+=prov[1]
    result.push(prov)
  }
  var f=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Search')
  for (var i=0;i<result.length;i++){
    f.getRange('B3').offset(0,i).setValue(result[i][0])
  }
}
function maxi(data,cible){
  sum=0
  for (var i=1;i<data.length;i++){
    sum+=data[i]
    if (sum>cible){
      return ([i-1,sum-data[i]])
    }
  }
}

lance le menu calculer en haut à droite ...

mais ce n'est qu'une solution (qui tombe juste ici), je n'ai pas cherché toutes les combinaisons.

https://docs.google.com/spreadsheets/d/1Db85wJR3MBjzMMdHIlwe5ze2zJ_s58bcvgdysooCLIk/copy

Cela doit fonctionner même en laissant les formules avec HLOOKUP que tu avais!

сталиевич · Answer

Mon challenge est d'essayer maintenant de trouver toutes les combinaisons.

En fait je vais commencer plutôt par un algorithme qui recherchera en fonction de la valeur décroissante de Nx/1ère colonne ... car comme tu as parfois des valeurs très faibles, je suis presque certain de toujours trouver la solution (enfin, c'est une conjecture)

edit hé bien non, la version v2 ne donne pas de résultat dans ce cas !!

je poursuis donc en recherchant toutes les combinaisons (il va falloir optimiser le temps de traitement limité par google), d'où un onglet cumul

сталиевич · Answer

Sur Liste1 https://docs.google.com/spreadsheets/d/1kh6co70H-hOXyQ7ZM9750GSZfTjpsFCdroAOgb0TUdY/copy...

сталиевич · Answer

C'est bon, j'ai pu faire varier N1, N2 et N3 (au-delà Google refuse car données trop importantes) ... si tu reviens fais moi signe.

Sur la list2, j'ai trouvé 1108 combinaisons donnant le résultat exact.

Sur List1 ... 2001 ! ... et ce n'est pas la totalité.

сталиевич · Answer

Voici mon document de travail. Attention, j'ai reconfiguré le tien pour y voir plus clair, mais aussi pour réduire le temps de calcul !

https://docs.google.com/spreadsheets/d/13OYRq3i6vmYhsIKqlwbkbUjkJG0GZWsyklL6vcEBnUs/copy

La liste est donc l'onglet 'List'. J'y ai mis Liste1 et Liste1B. J'ai aussi testé avec Liste2 en copiant les données.
La cible à atteindre est en A1.
J'ai un onglet 'Cumul' qui en fait fait le cumul des valeurs ligne par ligne.
Les possibilités se trouvent dans l'onglet 'Search' avec une formule en A2. Tu peux cocher/décocher la case en A1 pour relancer le calcul, mais normalement ce n'est pas nécessaire. Copie des lignes au hasard dans ton fichier initial et vérifie ...

Je n'ai pas bien compris ...

Sachant qu'actuellement rien ne peut être au dessus de 28 pour la List1+List1B car c'est impossible tout simplement.

Fais d'autres essais si besoin.

сталиевич · Answer

Au passage, pour utiliser, tu peux mettre une formule sui va chercher la somme cible, et une formule qui va chercher les valeurs de la liste. ET puis c'est tout, le reste suivra...