Distribution aléatoire

Question

Bonjour/Bonsoir,

Je cherche à distribuer aléatoirement un nombre de points définis dans plusieurs colonnes. Je m'explique:

Je souhaiterais distribuer les points de la colonne "Total" dans les 6 colonnes suivantes qui correspondent à des caractéristiques. Ma seule contrainte c'est les colonnes "Max" et "Min", la distribution doit respecter ces valeurs min et max.

Ma solution était: dans un premier temps distribuer les valeurs min dans chaques colonnes, puis de distribuer le reste des points aléatoirement, pour ça je pensais d'abord choisir l'ordre des colonnes aléatoirement pour chaque ligne (Ex: colonne 1 Intel, colonne 2 strentgh, etc.) pour éviter des redondances. Ensuite aléatoirement distribuer les points restants par colonne une par une (Ex: si(reste > val max - val min; alea(1; val max - val min); alea(1; reste)) (d'où le fait de changer l'ordre des colonnes à chaque fois pour éviter que ce soit toujours les mêmes colonnes qui passent en dernier).

Pour faire tout ça je pensais utiliser la fonction LET() que j'ai découvert récemment et que je trouve très pratique pour ce genre de choses mais j'ai plus de mal avec les fonction de tris comme SORT(), SCAN() etc. (dans l'idée j'aimerais mettre la formule la première colonne et qu'elle remplisse les 6 colonnes) donc je ne suis pas parvenu à faire quelque chose de fonctionnel, je ne sais pas si je m'y prend mal et si je prend le problème dans le mauvais sens ou s'il y a juste d'autre fonction que je ne connais pas qui pourrait faire le taf parfaitement.

Voilà le lien vers le fichier test: https://docs.google.com/spreadsheets/d/15SdoDmxV-yACNlh7lywKHwkywP40rwZI-LsPk8HiE5k/edit?usp=sharing

Merci d'avance pour vos réponses !

saboh12617 · Accepted Answer

Bonjour Julos, Avant de voir les fonctions il faut définir une méthode de résolution. Voici celle que j'ai implémentée : Méthode de résolution Problème : on cherche à remplir une liste de 6 valeurs avec des nombres compris entre min et max, de manière à ce que la somme de ces nombre soit égale à un total prédéfini. Stratégie de résolution : On va parcourir la liste case par case, et pour chacune calculer un score selon des bornes précises : La borne min sera le max entre &#91;le min prédéfini&#93; et &#91;total_courant - la somme maximal des résultats restant à calculer&#93;. La borne max sera le min entre &#91;la borne max&#93; et &#91;total_courant – la somme minimale des résultats restant à calculer&#93;. On remplit le tableau successivement en recalculant ces bornes au fur et à mesure, et en prenant une valeur random entre les 2 bornes. C'est terminé, on a un tableau qui correspond au critère, car plus on avance et plus les 2 bornes se ressèrent. AJOUT (dans votre classeur la formule DISTRIBALEA2) : cependant cette méthode a un biais : comme on remplit de gauche à droite, même si l'on prend des valeurs aléatoires, il y a un risque que les colonnes de droite, traitées en dernier, soient "moins pleines" que celles de gauche. Donc pour corriger le tir on mélange le tableau obtenu de manière aléatoire. EXEMPLE : total = 481, min = 60, max = 100. Case 1 : borne min = MAX(60; 481 – 100*5) = 60 borne max = MIN(100; 481 – 60*5) = 100 valeur aléatoire choisie = 96 Case 2 : borne min = MAX(60; 481 – 96 – 100*4) = 60 borne max = MIN(100; 481 – 96 – 60*4) = 100 … On choisit : 96, 67, 81, 96 avec ces bornes Case 5 : (total courant = 481-96-67-81-96=141) borne min = MAX(60; 141 – 100*1) = 60 borne max = MIN(100; 141 – 60*1) = 81 valeur aléatoire choisie = 80 Case 6 : (total courant = 481-96-67-81-96-80=61) borne min = MAX(60; 61 – 100*0) = 61 borne max = MIN(100; 61 – 60*0) = 61 valeur aléatoire choisie = 61 (obligé) On obtient la liste 96, 67, 81, 96, 80, 61. On la mélange et on a une distribution aléatoire (ça évite que en moyenne, sur de gros volumes, Charisma = dernière colonne se retrouve plus bas que Strength = première colonne). Implémentation dans GSheets Comment implémenter cela maintenant dans GSheets ? Et bien on va utiliser REDUCE qui permet de parcourir un tableau et d'y effectuer, à chaque cellule, un calcul. Je vais faire un tableau à 2 colonnes : en premier les _Srem = "remaining sum" = le total courant) et en 2 e les valeurs aléatoires choisies). C'est ce que fait la formule ci-après. Honnêtement, ce n'est pas très compliqué à comprendre si vous maitrisez déjà LET+LAMBDA+REDUCE. Si ce n'est pas le cas, je vous invite à vous former sur ces sujets dans un premier temps. Sur Google Sheets il n'y a peut-être pas beaucoup de tutoriels, mais sur Excel vous en trouverez davantage et le fonctionnement est identique. Je vous renvoie vers Fonction Excel : REDUCE (et même si c'est destiné à un public plus "programmeur" : &#91;O365&#93; Passer du VBA vers les nouvelles fonctions dynamiques ). Formule révisée (mélange aléatoire a posteriori) : =LET( baseArr; SEQUENCE(1; 6; _min; 0); _temp; REDUCE( HSTACK(tot; 0); SEQUENCE(COUNT(baseArr)); LAMBDA(a; i; LET( _nbLefts; COUNT(baseArr) - i; _Srem; INDEX(a; i; 1); _maxI; MIN(_max; _Srem - _nbLefts * _min); _minI; MAX(_min; _Srem - _nbLefts * _max); _xi; RANDBETWEEN(_minI; _maxI); VSTACK(a; HSTACK(_Srem - _xi; _xi)) ) ) ); _res1; CHOOSECOLS(_temp; 2); _res2; CHOOSEROWS(_res1; SEQUENCE(COUNT(_res1) - 1; 1; 2)); _res3; SORT(HSTACK(_res2; RANDARRAY(6)); 2); TOROW(CHOOSECOLS(_res3; 1)) )...

Andre13 · Answer

Bonjour toutes et tous et bon week-end @tester 1) créer un bouton ...Dessin.... Assigner le Script ==&gt; repartitionToutesLignes repartitionToutesLignes par : IA (version gratuite) un début, il bug parfois sur Max. 100 function repartitionToutesLignes() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const startRow = 3; // début des données const lastRow = sheet.getLastRow(); // dernière ligne du tableau const startCol = 3; // colonne C (NOM1) const endCol = 8; // colonne H (NOM6) const nCols = endCol - startCol + 1; // Efface d'abord les anciennes répartitions sheet.getRange(startRow, startCol, lastRow - startRow + 1, nCols).clearContent(); for (let row = startRow; row &lt;= lastRow; row++) { const total = sheet.getRange(row, 2).getValue(); // Colonne B (Total) const max = sheet.getRange(row, 9).getValue(); // Colonne I (Max) const min = sheet.getRange(row, 10).getValue(); // Colonne J (Min) // Vérifie les entrées valides if (typeof total !== 'number' || isNaN(total) || total &lt;= 0) continue; if (typeof max !== 'number' || typeof min !== 'number' || max &lt; min) continue; // Fonction interne pour créer une répartition const genererRepartition = () =&gt; { let parts = Array(nCols).fill(0); let remaining = total; for (let j = 0; j &lt; nCols; j++) { const resteCols = nCols - j; const maxPossible = Math.min(max, remaining - (resteCols - 1) * min); const minPossible = min; // si bornes invalides, attribue minimum if (maxPossible &lt; minPossible) { parts&#91;j&#93; = min; remaining -= min; continue; } const value = (j === nCols - 1) ? remaining : Math.floor(Math.random() * (maxPossible - minPossible + 1)) + minPossible; parts&#91;j&#93; = value; remaining -= value; } // Ajustement pour garantir la somme exacte const somme = parts.reduce((a, b) =&gt; a + b, 0); const ecart = total - somme; if (ecart !== 0) { const idx = Math.floor(Math.random() * nCols); parts&#91;idx&#93; += ecart; } return parts; }; // Première répartition let parts = genererRepartition(); // Vérification de validité const estValide = (arr) =&gt; arr.every(v =&gt; v &gt;= min && v &lt;= max) && arr.reduce((a, b) =&gt; a + b, 0) === total; // Si la première tentative échoue, refaire une deuxième répartition if (!estValide(parts)) { parts = genererRepartition(); // Si encore invalide, force les valeurs dans les bornes et ajuste le dernier if (!estValide(parts)) { parts = parts.map(v =&gt; Math.max(min, Math.min(v, max))); const sommeCorrigee = parts.reduce((a, b) =&gt; a + b, 0); const ecart = total - sommeCorrigee; const idx = Math.floor(Math.random() * nCols); parts&#91;idx&#93; += ecart; } } // Écrit le résultat dans la feuille sheet.getRange(row, startCol, 1, nCols).setValues(&#91;parts&#93;); } SpreadsheetApp.getActive().toast("✅ Répartition aléatoire terminée (avec double vérification) !"); }...

PierreLepinay · Answer

Avec des formules c'est surement possible mais il va nécessiter des 10aines de calculs intermédiaire ce qui nécessiterai plusieurs colonnes dédiées uniquement à ces calculs, le + simple est de créer une formule personnalisée, par exemple : /** * Répartit un total entier sur 6 cellules aléatoirement dans une plage &#91;min, max&#93;. * Usage dans Sheets : =DISPATCHER(total; min; max) * * @param {number} total - Le total à répartir * @param {number} min - Valeur minimale autorisée pour chaque cellule * @param {number} max - Valeur maximale autorisée pour chaque cellule * @return {number&#91;&#93;} - Tableau de 6 entiers aléatoires respectant les contraintes * @customfunction */ function DISPATCHER(total, min, max) { const n = 6; if (total &lt; min * n || total &gt; max * n) { throw new Error("Impossible de dispatcher : total incompatible avec min/max"); } let parts = Array(n).fill(min); let remaining = total - min * n; const marge = max - min; while (remaining &gt; 0) { for (let i = 0; i &lt; n; i++) { if (remaining &lt;= 0) break; const possible = Math.min(marge - (parts&#91;i&#93; - min), remaining); if (possible &gt; 0) { // ajouter un entier aléatoire const add = Math.floor(Math.random() * (possible + 1)); parts&#91;i&#93; += add; remaining -= add; } } } const diff = total - parts.reduce((a, b) =&gt; a + b, 0); parts&#91;parts.length - 1&#93; += diff; if (parts.some(v =&gt; v &lt; min || v &gt; max)) { throw new Error("Impossible de respecter les bornes min/max avec ce total"); } return &#91;parts&#93;; } Ce qui donne...

PierreLepinay · Answer

Je n'avais pas agit sur votre fichier test, je viens de créer un onglet et d'appliquer la formule personnalisée si vous souhaitez voir ce que ça donne, cela me parait fonctionnel ...

saboh12617 · Answer

Alternativement, on peut travailler en formules uniquement. Ci-après la formule nommée que j'ai ajoutée à votre classeur d'exemple. Notez qu'à chaque recalcul du classeur les valeurs sont réévaluées (utilisation de RANDBETWEEN qui est volatile) ! =LET( baseArr; SEQUENCE(1; 6; _min; 0); _temp; REDUCE( HSTACK(tot; 0); SEQUENCE(COUNT(baseArr)); LAMBDA(a; i; LET( _low; _min; _up; _max; _nbLefts; COUNT(baseArr) - i; _Srem; INDEX(a; i; 1); _maxI; MIN(_up; _Srem - _nbLefts * _low); _minI; MAX(_low; _Srem - _nbLefts * _up); _xi; IF(_nbLefts &gt; 0; RANDBETWEEN(_minI; _maxI); _maxI); VSTACK(a; HSTACK(_Srem - _xi; _xi)) ) ) ); _res1; TOROW(CHOOSECOLS(_temp; 2)); _res2; CHOOSECOLS(_res1; SEQUENCE(COUNT(_res1) - 1; 1; 2)); _res2 ) La formule prend 3 paramètres : le total tot , le min _min et le max _max . EDIT : voir https://forum.excel-pratique.com/s/goto/1256855 pour formule améliorée...

saboh12617 · Answer

Je vous en prie, content d'aider. N'oubliez pas de marquer les différentes solutions (fonctionnelles) qui vous ont été proposées pour clôturer le fil. Bonne journée...