Recherche formule Excel

Bonjour à tous,

Je suis à la recherche d'une formule / astuce pour effectuer le calcul suivant:

J'ai 154 panneaux photovoltaïque. Je peux mettre 19 panneaux max sur une seule ligne (la quantité dépend d'une autre formule simple).

Si je fais 154/19= 8.11 soit 16 lignes minimum (16x9=171 soit 17 panneaux de trop)

Pour arriver à mon résultat 301 , il faudrait que je fasse 2 ligne de 19 / 2 lignes de 18 et 5 lignes de 16.

Il s'agit d'un exemple mais bien évidemment le nombre de panneaux total et le nombre max peut varier.

Si quelqu'un peut m'aider à trouver la solution pour avoir ma configuration rapidement.

Merci d'avance

Bonjour,

Explications difficilement plus confuses...

Que faites-vous de la simple combinaison 154 = 19 * 8 + 2 ???

Ci-joint une piste de réflexion.

Bonjour,

Désolé pour la confusion...

Le but étant que chaque ligne soit la plus proche de la valeur max.

En gros si 19x9 ne fonctionne pas , je vais tenter 19x8+18x1 si ça ne fonctionne tjs pas je vais tenter 19x7+18x2 et ainsi de suite pour arriver à mon total de panneaux (154).

Et la répartition 14x11 = 154 ne convient pas non plus ?

S'il faut "descendre" comme proposé je pense qu'il y a besoin de VBA, autrement je ne vois pas comment itérer dans XL surtout si les répartitions max (19 dans l'exemple) sont variables.

Je vais réfléchir à une méthode de répartition comme suit :

Vous avez défini un max a 19 (par ex.)

On cherche une répartition optimale sur 18, puis 17, etc... En comptant le nombre d'emplacements restant. Et on regarde quand on trouve un match parfait entre répartition sur lignes de 18 + espaces restants = Total a atteindre. Si pas trouvé on passe sur 17 etc ...

Je note ça la j'y réfléchirai d'ici demain.

Bonjour,

J'ai trouvé un algorithme, mais :

Comment définissez-vous un choix entre :

154 = 19*2 + 18*2 + 16*5

Et par exemple :

154 = 19*4 + 16*3 + 15*2

• Y-a-t-il une "étendue de répartition" des valeurs solutions à ne pas dépasser ? (c'est à dire que 15 est "trop loin" de 19) Si oui, laquelle ou comment la calculer ?
• Sinon, que faire des solutions du type :

154 = 19*1 + 17*7 + 16*1

154 = 19*2 + 17*4 + 16*3 …

Parce que actuellement je fais face à un dilemme :

• Si je comprends bien vous voulez remplir "au plus proche du max". Ok. Mais alors la solution 154 = 19*4 + 16*3 + 15*2 convient tout à fait, meme mieux puisque + de lignes sont pleines.
• Sinon s'il y a une étendue ("spread") à respecter et que 15 est trop loin, comment choisir entre la pléthore de possibilités que j'évoque au dessus, pourquoi choisir 18 plutot que 17… et que 19 ?
• Y-a-t-il, en plus de l'étendue de réparition, un "gap" entre les valeurs à respecter/préférer (c'est à dire préférer des sauts de -1 panneau / ligne au lieu de -2) ?

Merci de préciser votre demande, et d'ordonner les paramètres proposés dans l'ordre d'importance.

Selon moi votre besoin, pour correspondre à la solution donnée, est :

1. Réduire le Spread
2. Minimiser les gaps, surtout entre les lignes pleines/quasi pleines (puisque vous préférer faire 19 → 18 → 16 plutot que 19 → 17 → 16
3. Remplir le max de lignes à 19 en suivant ces paramètres

Ci-dessous quelques solutions listées et leurs paramètres.

Enfin, si je peux noter, je ne sais pas quelles sont, au fond, vos motivations, mais, de manière très subjective, je trouve le rangement 19*2+17*5+16*3 plus harmonieux visuellement que 19*2+18*2+16*5.

Bonjour et tout d'abord merci pour votre aide.

Des combinaisons il y en a beaucoup et certaines meilleures que d'autres en effet.

Le but étant de réduire le nombre de ligne en s'approchant de la cible qui est de 9 lignes.

Ensuite plus les lignes sont longues mieux c'est.

Et dernièrement, il faudrait limiter au maximum le type de combinaison différente.

Exemple :

1x18 + 8x17-->Meilleure solution

19x2+17x4+16x3

Malgrés tout ça , il n'y a pas de solution absolu, le but étant surtout de trouver une combinaison simple en quelques clic.

Je me retrouve souvent à traiter des millier de panneaux avec des longueurs de lignes différentes et je galère parfois à trouver une solution viable.

je suis fortement intéressé par le côté valeur haute (19) et valeur basse.

Si je devais priorisé les choses je dirai:

1--> Nombre de ligne à respecter

2-->Valeur basse -->Cette valeur peut être choisis manuelle ou calculé (exemple : max-4)

3-->Nombre de ligne pleine

J'espère avoir répondu à toutes les questions.

Bonjour,

Je suis heureux de vous proposer ci-joint une solution via UDF. Bien sur c'est adaptable si vous préférez une macro, mais j'ai pensé l'UDF peut etre plus "user friendly".

Deux remarques :

Il me semble, il faudrait faire une étude algébrique, qu'avec par défaut un Spread de 4 que vous indiquez (min = max-4), on peut toujours trouver une répartition en 2 types de panneaux seulement.

De meme, je pense que l'info du "lignes max" est redondant, ou contre productif, dans le sens que la macro que je vous propose utilisera toujours le minimum de lignes possibles (le processus fonctionne ainsi par défaut), et donc je ne l'ai pas indiquée. A la limite ce pourrait etre un paramètre pour vous dire "attention vous voulez faire des lignes trop courtes" mais à part ça je ne vois pas l'intéret.

J'ai testé la macro et il semble que les résultats soient justes.

Ci-après le VBA, et ci-joint le fichier d'exemple.

Vous pouvez appeler la fonction en tapant dans la cellule =RepartirPanneaux

Public Function RepartirPanneaux(QtePx As Long, MaxPx As Long, Optional ByVal MinPx As Long = -1) As String
  If MinPx = -1 Then MinPx = MaxPx - 4

  If QtePx Mod MaxPx = 0 Then
    RepartirPanneaux = QtePx / MaxPx & " x " & MaxPx & " px."
    Exit Function
  End If

  Dim pxPerRow As Long, usedRows As Long, pxRemaining As Long
  For pxPerRow = MaxPx - 1 To MinPx Step -1
    usedRows = WorksheetFunction.Floor_Math(QtePx / pxPerRow)
    pxRemaining = QtePx Mod pxPerRow
    If usedRows >= pxRemaining Then
      ' on est sur un optimum, répartir les panneaux restants sur les lignes dispo
      If pxRemaining = 0 Then
        ' cas particulier du calcul qui tombe juste
        RepartirPanneaux = usedRows & " x " & pxPerRow & " px."
      Else
        RepartirPanneaux = pxRemaining & " x " & pxPerRow + 1 & " px. + " & _
                           usedRows - pxRemaining & " x " & pxPerRow & " px."
      End If

      ' optimum trouvé
      Exit Function
    End If
  Next pxPerRow
End Function

Bonjour,

Merci c'est vraiment top.

Exactement ce dont j'avais besoin comme document.

Je vous en prie, je suis content que ça corresponde.

Si jamais vous voulez un export différent (peut etre plus pratique dans un tableau plutot que du texte), n'hésitez pas à demander ou adapter la fonction.

Je peux ajouter une vérification aussi si vous voulez pour être sur qu'il ne manque pas de panneau (un calcul dans le classeur ou une fenêtre pop-up message d'alerte).