Répartition de données
Fermé
Bonjour,
Je cherche une solution sur excel pour répartir des données automatiquement (si possible sans macro).
J'ai une liste de données (une trentaine ou plus) qui correspond à des longueurs en mètre.
Je souhaite répartir ses données par groupe de XXml (pour l'exemple partons sur 25ml), sans dépasser cette valeur de 25ml et avec le moins de perte possible.
Aujourd'hui en travaillant à tatons je parviens à mes fins mais sans garantie d'être optimale.
Si quelqu'un à une solution, je suis preneur
Benjamin
Je cherche une solution sur excel pour répartir des données automatiquement (si possible sans macro).
J'ai une liste de données (une trentaine ou plus) qui correspond à des longueurs en mètre.
Je souhaite répartir ses données par groupe de XXml (pour l'exemple partons sur 25ml), sans dépasser cette valeur de 25ml et avec le moins de perte possible.
Aujourd'hui en travaillant à tatons je parviens à mes fins mais sans garantie d'être optimale.
Si quelqu'un à une solution, je suis preneur
Benjamin
A voir également:
- Répartition de données
- Supprimer les données de navigation - Guide
- Reinstaller windows sans perte de données - Guide
- Trier des données excel - Guide
- Comment sauvegarder toutes les données de mon téléphone - Guide
- Barre de données excel pourcentage 100 ✓ - Forum Excel
10 réponses
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
18 janv. 2017 à 04:49
18 janv. 2017 à 04:49
Bonjour.
Difficile de travailler sans support ...
Difficile de travailler sans support ...
1) Tu vas dans https://www.cjoint.com/
2) Tu cliques sur [Parcourir] pour sélectionner ton fichier (15 Mo maxi)
3) Tu défiles vers le bas pour cliquer sur le bouton bleu [Créer le lien Cjoint]
4) Au bout de quelques secondes la deuxième page s'affiche, avec le lien en gras ; tu fais un clic-droit dessus et tu choisis "Copier le lien"
5) Tu reviens dans ta discussion sur CCM, et dans ton message de réponse
tu fais "Coller".
=>Voir la fiche https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
asm3711
Messages postés
5
Date d'inscription
mercredi 18 janvier 2017
Statut
Membre
Dernière intervention
19 janvier 2017
1
18 janv. 2017 à 08:33
18 janv. 2017 à 08:33
Bonjour,
Lien du fichier ci dessous :
https://www.cjoint.com/c/GAshwbp1LUG
Je souhaite que les données du tableau "Longueur à répartir", soit automatiquement placé dans le tableau des groupes sans dépasser la longueur maxi par groupe (25m).
Merci
Lien du fichier ci dessous :
https://www.cjoint.com/c/GAshwbp1LUG
Je souhaite que les données du tableau "Longueur à répartir", soit automatiquement placé dans le tableau des groupes sans dépasser la longueur maxi par groupe (25m).
Merci
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
18 janv. 2017 à 15:00
18 janv. 2017 à 15:00
Bonjour.
1) trier la colonne B
2) prolonger le tableau des groupes jusqu'à la ligne 60
3) déplacer la donnée fixe de D6 à D5
4) saisir les formules :
1) trier la colonne B
2) prolonger le tableau des groupes jusqu'à la ligne 60
3) déplacer la donnée fixe de D6 à D5
4) saisir les formules :
a) en D6:D60, =ENT($D$5/B:B)
b) en C6:C60, =MOD($D$5;B:B)
c) en F6, =B6
d) en F7, =SI($D$5-SOMME(F$6:F6)<$B7;"-";SI(NB(F$6:F6)+1<=D7;B7;"@"))
à recopier jusqu'en F60
e) en G7, =SI(NB($F7:F7)=0;SI($D$5-SOMME(G$6:G6)<$B7;"-";SI(F7="-";
SI(NB(G$6:G6)+1<=$D7;$B7;"");""));"")
à recopier jusqu'en AI60
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
18 janv. 2017 à 15:09
18 janv. 2017 à 15:09
J'ai rectifié ta formule en ligne 63 (il manquait les "$")
J'ai ajouté quelques contrôles :
- En D62, la somme des longueurs utilisées en F62:AI62
- Ligne 64, indication des chutes inutilisables
- Ligne 5, mise en forme conditionnelle pour indiquer les groupes vides
https://www.cjoint.com/c/GAsojAGMiiN
J'ai ajouté quelques contrôles :
- En D62, la somme des longueurs utilisées en F62:AI62
- Ligne 64, indication des chutes inutilisables
- Ligne 5, mise en forme conditionnelle pour indiquer les groupes vides
https://www.cjoint.com/c/GAsojAGMiiN
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
asm3711
Messages postés
5
Date d'inscription
mercredi 18 janvier 2017
Statut
Membre
Dernière intervention
19 janvier 2017
1
18 janv. 2017 à 15:46
18 janv. 2017 à 15:46
J'ai qu'une chose à dire :
Merci,
A première vue tout marche bien je viens d'insérer lignes et colonnes pour adapter suivant le cas, le tableau fonctionne très bien.
En changeant ma valeur de référence tout ce modifie en fonction, c'est le top du top.
Après j'avoue ne pas trop comprendre les formules mais je vais travailler dessus sans aucun problème.
Encore un très grand merci à toi Raymond
Merci,
A première vue tout marche bien je viens d'insérer lignes et colonnes pour adapter suivant le cas, le tableau fonctionne très bien.
En changeant ma valeur de référence tout ce modifie en fonction, c'est le top du top.
Après j'avoue ne pas trop comprendre les formules mais je vais travailler dessus sans aucun problème.
Encore un très grand merci à toi Raymond
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
18 janv. 2017 à 16:21
18 janv. 2017 à 16:21
Eh bien, Benjamin, sache que :
- d'abord je suis vraiment content que mon fichier te donne satisfaction ;
- ensuite je suis soulagé d'avoir finalement trouvé une formule qui marche mieux que ce que j'avais espéré, parce que je craignais des complications en partie basse du tableau (ce qui heureusement ne s'est pas produit).
- d'abord je suis vraiment content que mon fichier te donne satisfaction ;
- ensuite je suis soulagé d'avoir finalement trouvé une formule qui marche mieux que ce que j'avais espéré, parce que je craignais des complications en partie basse du tableau (ce qui heureusement ne s'est pas produit).
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
18 janv. 2017 à 17:20
18 janv. 2017 à 17:20
Concernant la compréhension des formules :
a) $D$5/B:B Je divise la valeur de référence par chacune des longueurs pour savoir combien de pièces seront tirées de la valeur-référence.
ENT($D$5/B:B) permet d'afficher le nombre entier trouvé
b) MOD($D$5;B:B) affiche le reste de la division de D5 par un multiple de B ; je vois si cela suffit à tirer une pièce de longueur faible (fin de liste)
d) NB(F$6:F6) compte combien de nombres figurent depuis le début de la colonne jusqu'à la ligne précédente
SI(NB(F$6:F6)+1<=D7;B7;"@")) : tant que cette quantité ne dépasse pas le nombre de pièces calculé en D, j'affiche de nouveau la longueur B
SI($D$5-SOMME(F$6:F6)<$B7;"-";SI(...)) m'affiche "-" si la longueur restante est insuffisante pour tirer une nouvelle pièce de longueur B
e) SI(NB(G$6:G6)+1<=$D7;$B7;"") tant que la quantité de nombres depuis le début de la colonne ne dépasse pas le nombre D, j'affiche B
SI(F7="-";SI(...) autorise la suite du calcul si en F il n'y a aucun nombre
SI($D$5-SOMME(G$6:G6)<$B7;"-";SI(...) autorise la suite du calcul si la longueur restante suffit pour tirer une nouvelle pièce de longueur B
SI(NB($F7:F7)=0;SI(...) vérifie que les colonnes précédentes n'ont pas déjà permis d'obtenir la pièce. ---
C'est bien, la retraite ! Surtout aux Antilles ... :-)
Raymond (INSA, AFPA, CF/R)
a) $D$5/B:B Je divise la valeur de référence par chacune des longueurs pour savoir combien de pièces seront tirées de la valeur-référence.
ENT($D$5/B:B) permet d'afficher le nombre entier trouvé
b) MOD($D$5;B:B) affiche le reste de la division de D5 par un multiple de B ; je vois si cela suffit à tirer une pièce de longueur faible (fin de liste)
d) NB(F$6:F6) compte combien de nombres figurent depuis le début de la colonne jusqu'à la ligne précédente
SI(NB(F$6:F6)+1<=D7;B7;"@")) : tant que cette quantité ne dépasse pas le nombre de pièces calculé en D, j'affiche de nouveau la longueur B
SI($D$5-SOMME(F$6:F6)<$B7;"-";SI(...)) m'affiche "-" si la longueur restante est insuffisante pour tirer une nouvelle pièce de longueur B
e) SI(NB(G$6:G6)+1<=$D7;$B7;"") tant que la quantité de nombres depuis le début de la colonne ne dépasse pas le nombre D, j'affiche B
SI(F7="-";SI(...) autorise la suite du calcul si en F il n'y a aucun nombre
SI($D$5-SOMME(G$6:G6)<$B7;"-";SI(...) autorise la suite du calcul si la longueur restante suffit pour tirer une nouvelle pièce de longueur B
SI(NB($F7:F7)=0;SI(...) vérifie que les colonnes précédentes n'ont pas déjà permis d'obtenir la pièce. ---
C'est bien, la retraite ! Surtout aux Antilles ... :-)
Raymond (INSA, AFPA, CF/R)
asm3711
Messages postés
5
Date d'inscription
mercredi 18 janvier 2017
Statut
Membre
Dernière intervention
19 janvier 2017
1
18 janv. 2017 à 20:38
18 janv. 2017 à 20:38
Une petite interrogation,
Rien de bien grave, mais je suis dans la création de mon fichier type qui me servira pour tout type de projet.
La suppression des valeurs de la colonne "Longueur à répartir" donne le code erreur "#DIV/0!", logique pour une division par zéro.
Avez vous une solution simple pour ne pas afficher ce code (la formule si erreur peut résoudre ce problème mais à emboiter dans la formule pas évident) ?
Ce petit problème empêche les sommes des groupes de ce faire lorsque des cellules ne sont pas remplis (en supprimant les lignes je résous ce problème mais je souhaite voir en temps réel l'avancement des répartition des groupes).
Merci pour les compréhensions des formules.
Rien de bien grave, mais je suis dans la création de mon fichier type qui me servira pour tout type de projet.
La suppression des valeurs de la colonne "Longueur à répartir" donne le code erreur "#DIV/0!", logique pour une division par zéro.
Avez vous une solution simple pour ne pas afficher ce code (la formule si erreur peut résoudre ce problème mais à emboiter dans la formule pas évident) ?
Ce petit problème empêche les sommes des groupes de ce faire lorsque des cellules ne sont pas remplis (en supprimant les lignes je résous ce problème mais je souhaite voir en temps réel l'avancement des répartition des groupes).
Merci pour les compréhensions des formules.
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
>
asm3711
Messages postés
5
Date d'inscription
mercredi 18 janvier 2017
Statut
Membre
Dernière intervention
19 janvier 2017
Modifié par Raymond PENTIER le 18/01/2017 à 22:32
Modifié par Raymond PENTIER le 18/01/2017 à 22:32
"... à emboiter dans la formule pas évident" ; pourquoi donc ? seules les courtes formules des colonnes C et D sont concernées, et deviennent respectivement
=SI(B:B=0;0;MOD($D$5;B:B)) et =SI(B:B=0;0;ENT($D$5/B:B))
L'ensemble du tableau continue de bien fonctionner ...
Mais tu peux aussi utiliser les formules de la vidéo proposée par The_boss_68 :
=SIERREUR(MOD($D$5;B:B);0) et =SIERREUR(ENT($D$5/B:B);0)
=SI(B:B=0;0;MOD($D$5;B:B)) et =SI(B:B=0;0;ENT($D$5/B:B))
L'ensemble du tableau continue de bien fonctionner ...
Mais tu peux aussi utiliser les formules de la vidéo proposée par The_boss_68 :
=SIERREUR(MOD($D$5;B:B);0) et =SIERREUR(ENT($D$5/B:B);0)
The_boss_68
Messages postés
945
Date d'inscription
dimanche 15 novembre 2015
Statut
Membre
Dernière intervention
25 février 2025
180
18 janv. 2017 à 21:42
18 janv. 2017 à 21:42
Bonsoir
Voir cette vidéo
https://www.youtube.com/watch?v=YfGrccEQGKk
Slts
Voir cette vidéo
https://www.youtube.com/watch?v=YfGrccEQGKk
Slts
Bonjour,
Je viens de faire un test après élaboration de mon fichier base,
Au vu des résultats obtenus je n'obtient pas la solution optimale.
Ci-dessous lien de mon fichier :
http://www.cjoint.com/c/GAxk4yfj3xu
J'arrive à un résultat de 23 rouleaux, or j'arrive en travaillant à tâtons à 22 rouleaux (voir feuille 2)
Une solution pour obtenir un résultat optimal sans travailler en tâtons ?
Merci
Je viens de faire un test après élaboration de mon fichier base,
Au vu des résultats obtenus je n'obtient pas la solution optimale.
Ci-dessous lien de mon fichier :
http://www.cjoint.com/c/GAxk4yfj3xu
J'arrive à un résultat de 23 rouleaux, or j'arrive en travaillant à tâtons à 22 rouleaux (voir feuille 2)
Une solution pour obtenir un résultat optimal sans travailler en tâtons ?
Merci
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
23 janv. 2017 à 22:10
23 janv. 2017 à 22:10
Tu as dû te tromper de fichier ... celui que tu as joint ne comporte pas de feuille 2 !
asm3711
>
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
Modifié par asm3711 le 24/01/2017 à 08:03
Modifié par asm3711 le 24/01/2017 à 08:03
En effet,
http://www.cjoint.com/c/GAyhcn6oUPg
http://www.cjoint.com/c/GAyhcn6oUPg
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
17 291
>
asm3711
24 janv. 2017 à 13:45
24 janv. 2017 à 13:45
... ce qui démontre la supériorité du cerveau humain sur la machine !
Non, plus sérieusement, j'ai proposé une méthode pour répartir les coupes automatiquement, et sans trop de pertes.
Je n'ai pas garanti que c'était la solution optimale ...
Si tu pouvais décortiquer pas à pas la procédure "à tatons" que tu as suivie, cela nous permettrait de la reconstituer par formules Excel.
Non, plus sérieusement, j'ai proposé une méthode pour répartir les coupes automatiquement, et sans trop de pertes.
Je n'ai pas garanti que c'était la solution optimale ...
Si tu pouvais décortiquer pas à pas la procédure "à tatons" que tu as suivie, cela nous permettrait de la reconstituer par formules Excel.
asm3711
>
Raymond PENTIER
Messages postés
58870
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
3 mars 2025
24 janv. 2017 à 16:13
24 janv. 2017 à 16:13
La fameuse méthode à tatons :
- Je commence par trier mes valeurs du grand au plus petit (tout comme ta méthode)
- La somme me donne un résultat que je divise par ma valeur type (25ml), ce qui me donne arrondi au supérieur un nombre de rouleaux approximatif que j'aurais besoin.
- Après je commence par répartir mes données, par la plus grande (11.70ml) je vois ce qu'il me reste en bas (13.30ml), est-ce que dans mes valeurs qu'il me reste je peux obtenir 13.30, oui avec 8.60 et 4.70, mon 1er rouleau est complet.
- Ensuite je fais de même pour les autres valeurs, dans le cas où je n'arrive pas à un résultat satisfaisant (trop de perte), je vais partir d'une valeur de base plus petite (8.60ml pour l'exemple) et je vois si je peux compléter en obtenant un rouleau complet.
Après, ta méthode est déjà une très bonne base qui solutionne je pense 75 à 80% de mes cas (l'exemple par lequel j'ai testé n'est pas forcément idéal), le plus souvent je vais avoir des longueurs plus petites (dans les 1, 2 ou 3ml) et beaucoup moins de grande longueurs.
Je pense qu'une solution (si réalisable bien sur), c'est de prendre en premier lieu les valeurs qui sont égales à 25ml, une fois les rouleaux de 25ml terminé, que le rouleau suivant soit le plus proche de 25ml (24.90, 24.80ml, etc.), jusqu'à arriver au dernier qui se terminera comme il se terminera.
Voila à voir si une solution sans macro est réalisable, sinon je vais partir de mon fichier type et malgré tout gagner un temps considérable par rapport à ma méthode d'avant
- Je commence par trier mes valeurs du grand au plus petit (tout comme ta méthode)
- La somme me donne un résultat que je divise par ma valeur type (25ml), ce qui me donne arrondi au supérieur un nombre de rouleaux approximatif que j'aurais besoin.
- Après je commence par répartir mes données, par la plus grande (11.70ml) je vois ce qu'il me reste en bas (13.30ml), est-ce que dans mes valeurs qu'il me reste je peux obtenir 13.30, oui avec 8.60 et 4.70, mon 1er rouleau est complet.
- Ensuite je fais de même pour les autres valeurs, dans le cas où je n'arrive pas à un résultat satisfaisant (trop de perte), je vais partir d'une valeur de base plus petite (8.60ml pour l'exemple) et je vois si je peux compléter en obtenant un rouleau complet.
Après, ta méthode est déjà une très bonne base qui solutionne je pense 75 à 80% de mes cas (l'exemple par lequel j'ai testé n'est pas forcément idéal), le plus souvent je vais avoir des longueurs plus petites (dans les 1, 2 ou 3ml) et beaucoup moins de grande longueurs.
Je pense qu'une solution (si réalisable bien sur), c'est de prendre en premier lieu les valeurs qui sont égales à 25ml, une fois les rouleaux de 25ml terminé, que le rouleau suivant soit le plus proche de 25ml (24.90, 24.80ml, etc.), jusqu'à arriver au dernier qui se terminera comme il se terminera.
Voila à voir si une solution sans macro est réalisable, sinon je vais partir de mon fichier type et malgré tout gagner un temps considérable par rapport à ma méthode d'avant
JvDo
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
858
24 janv. 2017 à 05:26
24 janv. 2017 à 05:26
Bonsoir à tous,
Déjà dans ton premier exemple tu avais une solution à 22 au lieu de 23 et ça ne semblait pas te perturber.
Si tu veux une solution facile à utiliser, tu peux utiliser decouli.xls de jean duprat qui te donne 22 dans tes 2 exemples. Il te restera à remettre en forme le résultat.
Tu as d'autres solutions avec solveur mais ça ne semble pas te correspondre.
Cordiialement
Déjà dans ton premier exemple tu avais une solution à 22 au lieu de 23 et ça ne semblait pas te perturber.
Si tu veux une solution facile à utiliser, tu peux utiliser decouli.xls de jean duprat qui te donne 22 dans tes 2 exemples. Il te restera à remettre en forme le résultat.
Tu as d'autres solutions avec solveur mais ça ne semble pas te correspondre.
Cordiialement