Tri et concaténation sans macro

Bonjour,

J'ai un tableau "recette" avec une colonne avec une liste d'ingrédients, une 2ème colonne avec les grammages de chaque ingrédient.

Je voudrais créer une cellule qui contiendrait la liste des ingrédients, par ordre décroissant de grammage, suivi du grammage (c'est une obligation légale).

ex pour un riz au lait, ça donnerait :

lait 100g, riz 10g, sucre 5g, vanille 0,1g

J'ai pensé à trier les données puis à les concaténer, mais je voudrais pouvoir zapper l'opération de tri car les utilisateurs de mon fichier ne vont pas savoir faire, même avec une macro bouton ça va être le bazar j'en suis sûre.

Du coup je me demandais s'il n'y avait pas moyen d'utiliser une fonction matricielle pour recopier le tableau, mais cette fois trié par ordre décroissant sur le grammage (et c'est ce tableau qui servirait à la concaténation). C'est possible ?

Ou y a-t-il une solution plus simple ?

Merci d'avance.

Bonjour,

Proposition de fonction personnalisée :

Function CONCATINGRED(pl As Range)
    Dim ting, tft(1), i%, j%
    Application.Volatile
    If pl.Columns.Count < 2 Then
        CONCATINGRED = CVErr(xlErrValue)
        Exit Function
    End If
    For i = 1 To pl.Rows.Count
        ting = ting & ";" & pl.Cells(i, 1) & " " & pl.Cells(i, 2) & "," & Chr(10)
    Next i
    ting = Split(ting)
    For i = 1 To UBound(ting) - 1
        For j = i + 1 To UBound(ting)
            If Val(ting(j)) > Val(ting(i)) Then
                tft(0) = Left(ting(j), InStr(ting(j), "," & Chr(10)) - 1)
                tft(1) = Left(ting(i), InStr(ting(i), "," & Chr(10)) - 1)
                ting(i) = Replace(ting(i), tft(1), tft(0))
                ting(j) = Replace(ting(j), tft(0), tft(1))
                tft(0) = Right(ting(j - 1), Len(ting(j - 1)) - InStr(ting(j - 1), ";"))
                tft(1) = Right(ting(i - 1), Len(ting(i - 1)) - InStr(ting(i - 1), ";"))
                ting(i - 1) = Replace(ting(i - 1), tft(1), tft(0))
                ting(j - 1) = Replace(ting(j - 1), tft(0), tft(1))
            End If
        Next j
    Next i
    ting(0) = Replace(ting(0), ";", "")
    ting(UBound(ting)) = Replace(ting(UBound(ting)), "," & Chr(10), "")
    ting = Replace(Join(ting), Chr(10) & ";", " " & Chr(10))
    CONCATINGRED = ting
End Function

Je ne suis pas sûr qu'elle soit tout à fait au point, ni que ton exemple soit suffisamment représentatif, et comme pas de fichier, on n'a pas eu de liste suffisante...

Conditions dans lesquelles elle doit normalement fonctionner dans son état actuel :

• l'argument est une plage de 2 colonnes, censée contenir les ingrédients dans la 1re et le grammage dans la 2e
• ingrédients (1re colonne) : une chaîne de caractères sans espace ni ponctuation

- grammage (2e colonne) : un nombre suivi de "g" accolé au nombre

la valeur décimale est prise en compte, cependant s'il y en avait plusieurs seule la partie entière serait considérée... (je n'ai pas inclu de conversion pour l'instant, mais cela pourrait se faire)

A tester en tenant compte de ces éléments. Et mettre un classeur représentatif pour des essais plus circonstanciés.

Un point que j'ai omis de dire : le résultat apparaît en ligne, mais si la cellule est mise en retour ligne auto, le résultats s'affichera sur autant de lignes que d'ingrédients.

Bye !

Bonjour MFerrand,

ouh là tu m'emmènes un cran au-dessus de mes compétences : je maîtrise un peu (tout petit peu) les macros mais toujours en lien avec des boutons, alors du coup je ne sais pas du tout quoi faire de ta formule.

Tu as raison, vaut mieux te transmettre le fichier (j'ai bazardé tous les autres onglets pour éviter de polluer le forum) : j'ai fait apparaître en rouge le besoin et j'ai encadré en vert les zones de données utilisées.

(comme tu pourras le voir en scrollant j'ai fait bon usage de tes formules matricielles de l'autre jour )

Le besoin :

Faire apparaître en E7 la liste des ingrédients (colonne C) suivi du % (colonne G : si possible chiffre entier sauf si <1% auquel cas avec 1 chiffre après la virgule), par ordre décroissant de %

càd dans cet exemple : Lait entier, UHT (74%) ; Sucre roux (18%) ; Riz complet, sec (7%) ; Cannelle (0,7%).

L'absolue cerise sur le gâteau serait que lorsque l'ingrédient est réputé allergène ("oui" dans la colonne L), il apparaisse en gras dans la composition !!

Merci beaucoup de ton aide.

Ça ne va pas aller si tu changes de question à chaque intervention...

Le problème posé consistait à concaténer une liste d'ingrédients dans une 1re colonne avec des poids en grammes de ces ingrédients dans une 2e colonne, en les classant par ordre de poids décroissant.

Il faut :

1) Indiquer où je trouve la colonne Ingrédients, où je trouve les valeurs à concaténer, quelle forme doit prendre la concaténation.

2) Si tri il y a à faire, quel est le critère de tri.

3) Avoir un éventail exhaustif de tout ce qui peut figurer dans la colonne ingrédients et de tout ce qui peut figurer comme valeurs à concaténer aux ingrédients.

Et on peut repartir à 0 pour bâtir une fonction...

Une fonction personnalisée est certes une macro, mais dont l'utilisation est transparente car identique aux fonctions intégrées. On met en place des formules sans aucune différence, sinon que ce sera plus simple en général, car personnalisé, donc adapté à un usage particulier pour lequel elle a été conçue.

Cordialement.

MFerrand a écrit :

Ça ne va pas aller si tu changes de question à chaque intervention...

Le problème posé consistait à concaténer une liste d'ingrédients dans une 1re colonne avec des poids en grammes de ces ingrédients dans une 2e colonne, en les classant par ordre de poids décroissant.

Il faut :

1) Indiquer où je trouve la colonne Ingrédients, où je trouve les valeurs à concaténer, quelle forme doit prendre la concaténation.

2) Si tri il y a à faire, quel est le critère de tri.

3) Avoir un éventail exhaustif de tout ce qui peut figurer dans la colonne ingrédients et de tout ce qui peut figurer comme valeurs à concaténer aux ingrédients.

Et on peut repartir à 0 pour bâtir une fonction...

Une fonction personnalisée est certes une macro, mais dont l'utilisation est transparente car identique aux fonctions intégrées. On met en place des formules sans aucune différence, sinon que ce sera plus simple en général, car personnalisé, donc adapté à un usage particulier pour lequel elle a été conçue.

Cordialement.

MFerrand voici les réponses à tes questions :

1) Indiquer où je trouve la colonne Ingrédients,

C22:C42 (encadré vert sous le nom "ingrédient")

où je trouve les valeurs à concaténer,

ligne par ligne, les blocs C22:C42 et G22:G42,

quelle forme doit prendre la concaténation.

- le bloc G22;G42 étant précédé d'une parenthèse ouverte, suivi d'une parenthèse fermée + espace + point virgule (ou point final pour le dernier ingrédient) pour séparer les ingrédients et faciliter la lecture

comme dans l'exemple mentionné :

Lait entier, UHT (74%) ; Sucre roux (18%) ; Riz complet, sec (7%) ; Cannelle (0,7%).

- les pourcentages sont des nombres entiers, sauf si le % est < 1, car dans ce cas il faudrait avoir 1 chiffre après la virgule

2) Si tri il y a à faire, quel est le critère de tri.

- Trier la liste des ingrédients par ordre décroissant de % (bloc G22:G42)

ex : dans mon fichier, l'utilisateur a saisi dans le tableau riz / cannelle / sucre / lait, je veux que la cellule fasse apparaître lait / sucre / riz / cannelle car c'est là l'ordre décroissant des quantités de chaque ingrédient dans la recette

- Idéalement, mettre en gras les ingrédients dont la colonne L renseigne que "oui" ce sont des ingrédients allergènes (si pas possible : je ferai apparaître ailleurs la liste des ingrédients allergènes de la recette).

3) Avoir un éventail exhaustif de tout ce qui peut figurer dans la colonne ingrédients et de tout ce qui peut figurer comme valeurs à concaténer aux ingrédients.

• colonne ingrédients : du texte, longueur variable, possibilités plusieurs virgules
• colonne % : des nombres
• autres signes : voir exemple plus haut, des parenthèses et séparateurs pour bien visualiser chaque groupe "ingrédient (%)"

Voilà j'espère que c'est plus clair, excuse-moi je croyais que ça ressortait clairement du fichier joint mais je crois que j'ai un peu trop la tête dans le guidon

Concernant ton explication sur la "fonction personnalisée", je comprends le principe, mais ça ne me dit pas où copier-coller le code ?

Je te remercie pour le temps consacré à mon problème. Encore une fois je suis émerveillée par la puissance de ce forum et la disponibilité et la compétence des contributeurs.

Leila

Tu trouveras la fonction LISTINGRED dans le Module1.

Pour voir les modules et autres composants VBA, il faut se rendre sur l'éditeur VBA : onglet Développeur > Visual Basic

ou bien accès par le raccourci clavier Alt+F11.

La fonction s'utilise dans une formule comme toute autre fonction :

=LISTEINGRED(plageIngrédients;plageProportion)

Si les données pointées par la formule sont modifiées, le résultat se mettra à jour (recalcul).

Par contre, aucun texte résultat de formule ne peut être mis en forme de la façon que tu voudrais, car la cellule ne contient pas de texte mais une formule.

Il te faudra utiliser d'autres moyens...

Cordialement.

MFerrand c'est extra.

Je n'imaginais même pas qu'on pouvait inventer et enregistrer ses propres formules sur excel. Je suis bluffée. Merci pour ces apprentissages (même si je ne saurai jamais programmer cela toute seule !!)

Est-ce que tu me permets une remarque supplémentaire ? si je n'abuse pas.

voici ce que j'obtiens :

Lait entier, pasteurisé (53%) ; Porc, épaule, crue (27%) ; Jaune d'oeuf, cuit (11%) ; Groseille, fraîche (5%) ; Emmental (4%) ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; () ; ().

Les () ; en trop sont liés aux lignes non utilisées pour la recette donnée.

Pour d'autres recettes, elles seront peut-être utilisées.

Du coup : y a-t-il moyen de faire détecter les lignes vides et donc supprimer les "() ." inutiles ?

Ce n'était pas prévu pour lui faire traiter des lignes vides...

Tu vas apporter une petite modification dans la boucle d'affectation:

    For i = 1 To n
        If plIngr.Cells(i, 1).Value <> "" Then
            Ting(i, 0) = plIngr.Cells(i, 1).Value
            Ting(i, 1) = plProp.Cells(i, 1).Value
            If Not IsNumeric(Ting(i, 1)) Then
                Ting(i, 2) = Ting(i, 1)
                Ting(i, 1) = -1
            End If
        Else
            n = i - 1
            Exit For
        End If
    Next i

Les ajouts sont surlignés.

Vache, c'est génial.

Tu n'es rien moins qu'un génie !!!!!!!!!!!!!

Bravo et merci

ah zut j'avais pas vu : il n'y a pas le format spécial de chiffre à une décimale pour les % < 1

ex : vanille (0,1%) alors que riz (24%)

Pas possible ?

Ne me fais pas enfler les chevilles (surtout que mes problèmes actuels sont à l'autre extrémité )

J'ai essayé de plusieurs façons de faire la distinction, mais pour le moment rien à faire, un format décimal quelles que soient les conditions que j'y ai mis s'est appliqué à tous les éléments, ce pourquoi j'ai supprimé. Mais si cela est un élément à rechercherje ferai quelques autres tests (je n'ai pas épuisé toutes les possibilités)

mouarf

écoute si jamais tu as un éclair de génie qui te (re)traverse, oui, fais-moi signe !

merci dans tous les cas

Tu as bien fait d'y revenir, je ne sais plus exactement ce que j'avais écrit auparavant pour que ça ne marche pas, mais peut-être tout bêtement une condition telle que <1 (alors que tout % est une fraction de 1).

La boucle de mise en forme (la 2e) à modifier :

    For i = 1 To n
        If Ting(i, 1) <> -1 Then
            Ting(i, 1) = "(" & IIf(Ting(i, 1) < 0.01, Format(Ting(i, 1), "0.0%"), _
             Format(Ting(i, 1), "0%")) & ") ; "
        Else
            Ting(i, 1) = "(" & Ting(i, 2) & ") ; "
        End If
    Next i

Ligne surlignée à substituer.

Yesssss !!