XL 2010 FAIRE LISTE AUTOMATIQUE SUIVANT NOMBRES

[mcj1997]

Bonjour,

En PJ, problématique pour générer en automatique des listes suivant deux critères.

Merci d'avance,

 

[mcj1997]

J'ai laissé 6 lignes car c'est le maximum de portefeuilles qu'il puisse y avoir.

 

[Dranreb]

Oui, bon, vous ne répondez jamais à aucune de mes observations, alors je suppose que vous avez tout ce qu'il vous faut.

 

[mcj1997]

Je suis loin d'avoir tout ce qu'il me faut, j'en suis toujours au même point que post 29.
Oui effectivement il serait utile d'utiliser les lignes 19, 32, ...... J'avais mis des formules en lignes 9 / 22 / 35 / 48....
Si ce n'est pas réalisable par macro, je vais tenter des formules.

 

[Dranreb]

Ce n'est pas que ce n'est pas réalisable par macros, c'est que ce n'est plus utile si plus rien ne bouge en positions et tailles de lignes.
Je peux toutefois vous écrire une macro qui installe ces formule une fois pour toutes, ou les restaure en cas de destruction accidentelle.

 

[mcj1997]

Je veux bien pour essayer de m'en sortir,

 

[Dranreb]

Voici la procédure, à mettre dans un module standard qui installerait juste les formules :

Option Explicit
Sub InstallerFormules()
   Dim L As Long
   For L = 9 To 100 Step 13
      Range(Cells(L, "D"), Cells(L, "AD")).FormulaR1C1 = "=IF(ISBLANK(R[-2]C),0,R[-3]C)"
      Range(Cells(L + 3, "B"), Cells(L + 8, "B")).FormulaR1C1 = "=""PF ""&ROWS(R" & L + 3 & ":R)"
      Cells(L + 3, "C").FormulaR1C1 = "=MIN(R" & L & "C,R" & L + 2 & "C)"
      Range(Cells(L + 4, "C"), Cells(L + 8, "C")).FormulaR1C1 = "=MIN(MAX(R" & L & "C-SUM(R" & L + 3 & "C:R[-1]C),0),R" & L + 2 & "C)"
      Cells(L + 3, "D").FormulaR1C1 = "=MIN(R" & L & "C,RC3)"
      Range(Cells(L + 4, "D"), Cells(L + 8, "D")).FormulaR1C1 = "=MIN(MAX(R" & L & "C-SUM(R" & L + 3 & "C:R[-1]C),0),RC3)"
      Range(Cells(L + 3, "E"), Cells(L + 3, "AD")).FormulaR1C1 = "=MIN(R" & L & "C,MAX(RC3-SUM(RC4:RC[-1]:RC4),0))"
      Range(Cells(L + 4, "E"), Cells(L + 8, "AD")).FormulaR1C1 = "=MIN(MAX(R" & L & "C-SUM(R" & L + 3 & "C:R[-1]C),0),MAX(RC3-SUM(RC[-1]:RC4),0))"
      Range(Cells(L + 10, "D"), Cells(L + 10, "AD")).FormulaR1C1 = "=R[-13]C-SUM(R[-7]C:R[-2]C)"
      Next L
   End Sub

On pourrait y ajouter des instruction qui harmonisent les formats de nombres (à 2 décimales car j'ai vu qu'il le faudrait) et qui n'affiche pas les valeurs nulles.

 

[mcj1997]

Merci

 

[mcj1997]

Comme suggéré, je tente de faire sans macro, j’ai réussi à trouver des formules mais je suis bloqué car le comptage dans le tableau joint se fait sur trois cellules seulement en C8 / D8 et E8. Je souhaite le faire sur 7 en intégrant F8 / G8 / H8 et I8.

 

[Dranreb]

Si vous changez tout le temps les règles, ça ne va pas !
Vous ne pouvez pas vous inspirer des formules installées par ma procédure du #36, elles sont plus courtes, non ?
Par exemple votre formule en C11 :
=SI((D11*$D$10+E11*$E$10)=$L$7;0;SI((($L$7-D11*$D$10-$E$10*E11)/$C$10)<$C$8;($L$7-D11*$D$10-$E$10*E11)/$C$10;$C$8))
pourrait se simplifier comme ça :
=SI((D11*$D$10+E11*$E$10)=$L$7;0;MIN(($L$7-D11*$D$10-$E$10*E11)/$C$10;$C$8))
=SI(V1<V2;V1;V2) c'est la même chose que =MIN(V1;V2) sauf qu'on ne répete pas les V1 et V2, ce qui est appréciable si sont de longues expressions !
Là, les formules sont si longues que je n'arrive même pas à comprendre ce que vous voulez faire.
Reprenez le principe des formules de l'autre classeur. C'est assez simple, tout part de la 1ère cellule en D12 :
=MIN(D$9;$C12): on prend le plus petit des 2. Logique, n'est-ce pas.
Mais quand on est sur une ligne au delà de la 12 on remplace le terme de gauche D$9 par MAX(D$9-SOMME(D$12😀12);0)
et quand on est dans une colonne à droite de la D, on remplace le terme de droite $C12 par MAX($C12-SOMME($D12😀12);0)
Vous suivez ?

 

[Dranreb]

Je viens de me rendre compte qu'il est inutile de prendre les MAX(…;0): ça ne peut pas être < 0
Alors j'ai modifié la procédure d'installation des formules. Elle n'est pas plus simple à comprendre mais elle installe des formules plus simples et avec expressions spéciales pour la 2ème ligne ou colonne. Inutile en effet de retranche une SOMME quand elle ne comporte que la cellule d'avant !
Je me suis aussi occupé des formats de nombre et du non affichage des valeurs nulles.

Option Explicit
Sub InstallerFormules()
   Const NumFmt = "0.00" '="0,00" — "General" '="Standard" —
   Dim L As Long, L3 As Long, C3 As Long, Cel As Range
   [C4:AD6].NumberFormat = NumFmt
   For L = 9 To 100 Step 13
      Cells(L, "C").FormulaR1C1 = "=SUM(RC4:RC30)"
      Range(Cells(L, "D"), Cells(L, "AD")).FormulaR1C1 = "=IF(ISBLANK(R[-2]C),0,R[-3]C)"
      Range(Cells(L + 3, "B"), Cells(L + 8, "B")).FormulaR1C1 = "=""PF ""&ROWS(R" & L + 3 & ":R)"
      For L3 = 1 To 3
         Set Cel = Cells(L + 2 + L3, "C"): If L3 = 3 Then Set Cel = Cel.Resize(4)
         Cel.FormulaR1C1 = "=MIN(R" & L & "C" & Choose(L3, "", "-R[-1]C", "-SUM(R" & L + 3 & "C:R[-1]C)") & ",R" & L + 2 & "C)"
         For C3 = 1 To 3
            Set Cel = Cells(L + 2 + L3, 3 + C3): If L3 = 3 Then Set Cel = Cel.Resize(4)
            If C3 = 3 Then Set Cel = Cel.Resize(, 25)
            Cel.FormulaR1C1 = "=MIN(R" & L & "C" & Choose(L3, "", "-R[-1]C", "-SUM(R" & L + 3 & "C:R[-1]C)") _
               & ",RC3" & Choose(C3, "", "-RC[-1]", "-SUM(RC4:RC[-1])") & ")"
            Next C3, L3
      Range(Cells(L + 10, "D"), Cells(L + 10, "AD")).FormulaR1C1 = "=R[-13]C-SUM(R[-7]C:R[-2]C)"
      [C:AD].Rows(L).NumberFormat = NumFmt
      [C:AD].Rows(L + 3).Resize(6).NumberFormat = NumFmt
      [C:AD].Rows(L + 10).NumberFormat = NumFmt
      Next L
   ActiveWindow.DisplayZeros = False
   End Sub

 

[mcj1997]

Bonjour

Merci pour vos retours, je ne pense pas changer les règles mais j'ai du mal à adapter à mon fichier définitif que je ne peux mettre en ligne. N'y arrivant pas avec macro je vais faire sans comme dans mon post 38 avec vos formules.

 

[mcj1997]

Désolé mais ce n'est pas simple à expliquer, ce dont j'ai besoin c'est dans le post 14 fichier feuil2.
- répartir les catégories sélectionnées par les croix en portefeuille avec le max en C11 en prenant les valeurs à gauche en premier comme dans l'exemple 56 + 31 + 13,
- et ensuite si besoin création d'un nouveau paquet avec nouvelle taille de portefeuille ....

 

[Dranreb]

Bonjour.
Je ne comprend rien. Il n'y a pas de feuil2 ni de croix ni de valeurs 56, 31 et 13 dans votre fichier joint au #14. Vous seriez vous trompé de fichier ?

 

[Dranreb]

Moi j'en étais resté là, n'est ce pas.

 

[mcj1997]

C'est exactement cela, merci beaucoup, j'étais en train de faire sans macro suite à vos derniers messages.
Je vais mettre des MFC afin que seuls les "paquets" utilisés apparaissent, par exemple dans votre fichier Temp,
du blanc à partir de la ligne 34 et en mettant une croix sur ligne 33 un nouveau paquet apparaît et ainsi de suite. Voyez vous ou je veux en venir ?