XL 2016 Assembler du Texte avec Sommeprod

[APPRENTI:)]

Bonjour à tous,

je cherche à assemble du texte avec la fonction sommeprod.

Je me sers pour le moment d'une formule type =SI(B2="x";B1;"")&SI(C2="x";C1;"")&SI(D2="x";D1;"")

Seulement, il m'arrive de créer de nouvelles colonnes.... alors je dois modifier la fonction dans de nombreuse cellule.

J'aimerais une fonction qui s'étend sur tout une plage, à l'instar du la fonction sommeprod.
Je n'arrive pas à retranscrire du texte avec celle-ci, avez vous des conseils?

Très bonne journée à vous!!

 

[Amilo]

Bonsoir APPRENTI,
En attendant une autre solution avec formule, voici une proposition avec un TCD à l'aide de Power Query et DAX
Cordialement

 

[APPRENTI:)]

Merci Amilo, alors j'ai pas réussi a voir comment ça fonctionne….
^^ peux tu m'éclairer?
Je ne sais pas si ça va être facile à insérer dans mon classeur.
le but de cette fonction est de créer un devis automatique.

On vient cocher des croix, dans le premier onglet, ce qui élabore un PVHT
Dans un même temps, au second onglet, un devis automatique est produit.

 

[mapomme]

Bonsoir @APPRENTI:), @Amilo ,

Avec une p'tite fonction personnalisée dont le code est dans module1:

Function ConcatSi(rgX As Range, rgC As Range) As String
Dim x, s As String
   For Each x In rgX
      If Trim(x) <> "" Then s = s & ", " & Trim(rgC.Parent.Cells(rgC.Row, x.Column))
   Next
   If Len(s) > 0 Then ConcatSi = Mid(s, 3)
End Function

nota: pensez à mettre en référence absolue la ligne à concaténer. Ici, la plage $B$1:$I$1
=ConcatSi(B2:I2;$B$1:$I$1)

 

[APPRENTI:)]

Bonsoir MaPomme!
Merci à toi!!!
ça à l'air de fonctionner!!
Que puis-je rajouter dans ton code pour que ça fonctionne avec un x et un s?
désolé, j'aurai le mentionner.

 

[mapomme]

Que puis-je rajouter dans ton code pour que ça fonctionne avec un x et un s?

En fait, je ne teste pas si la cellule contient un x ou autre chose. Je teste si la cellule est vide ou pas.
Si elle n'est pas vide, alors je concatène, si elle est vide , je passe.
Donc on peut mettre n'importe quoi dans les cellules : "x", "s", "truc" ou 123...

 

[APPRENTI:)]

C'est efficace!
merci à toi!!!!!!!

 

[APPRENTI:)]

Est ce que je peux modifier le code, pour que la sélection se fasse sur la cellule à coté?

Le x sur fond rouge élabore le prix et le devis.
Le x sur fond vert me sert à faire les situations.

J'ai donc un onglet un doc type situation qui se rempli automatiquement lorsque que je valide les tâches qui ont été effectuées

Un grand merci à toi, c'est des pavés de formule remplacé par quelques lignes grâce à tes connaissances!!!

 

[Amilo]

Re,
bonsoir mapomme,

Merci Amilo, alors j'ai pas réussi a voir comment ça fonctionne….
^^ peux tu m'éclairer?
Je ne sais pas si ça va être facile à insérer dans mon classeur.
le but de cette fonction est de créer un devis automatique.

On vient cocher des croix, dans le premier onglet, ce qui élabore un PVHT
Dans un même temps, au second onglet, un devis automatique est produit.

@APPRENTI:), comme c'est pour établir un devis et ajouter d'autres colonnes de prix notamment alors je pense qu'une une solution VBA est plus adaptée

Edit : lorsque je vois un peu la structure des tableaux, j'imagine une certaine complexité dans la conception des devis.

Cordialement

 

[APPRENTI:)]

Effectivement, lorsque je coche la case d'a coté (Fond en vert), cela rajoute une virgule entre les tâches. c'est pas trop méchant puisque le devis sera sorti avant que la réalisation ne commence.

Par contre, je ne vois pas du tout comment transformer le code pour qu'il s'applique aux situation d'avancement.

 

[APPRENTI:)]

J'ai modifié le tableau pour que ce soit plus compréhensible ^^

 

[mapomme]

Re à tous,

Voir la version v2:

La fonction a été réécrite. Elle possède maintenant un troisième paramètre qui doit être égal à "D" si on veut les devis ou à "S" si on veut la situation.

Pour les devis: =ConcatSi(B2:O2;$B$1:$O$1;"d")
Pour les situations: =ConcatSi(B2:O2;$B$1:$O$1;"s")

Le code dans module1:

Function ConcatSi(rgX As Range, rgC As Range, Typ As String) As String
' Typ doit valoir "D" ou "S"
Dim j As Long, s As String, debut As Long
   If UCase(Typ) = "D" Then debut = 1 Else debut = 2
   For j = debut To rgX.Columns.Count Step 2
      If Trim(rgX.Cells(1, j)) <> "" Then s = s & ", " & Trim(rgC.Parent.Cells(rgC.Row, 1 - debut + rgX.Cells(1, j).Column))
   Next
   If Len(s) > 0 Then ConcatSi = Mid(s, 3)
End Function

 

[APPRENTI:)]

Bonjour à tous

Merci MaPomme!
Pour élaborer le devis, je me sers du x lorsque c'est notre entreprise qui projette la réalisation, et d'un s lorsque que nous sous-traitons .
une fois la tache réalisée, je valide par un x ou par un s, suivant qui à réellement réalisé la tâche.

Suivant ces critères j'ai un onglet "prévisionnel&suivi" qui élabore le pvht et calcul la rentabilité en cours de réalisation.

Est-il possible que dans la première cellule pour établir le devis, nous rentrions x ou s
et que dans la cellule d'à côté , nous validions par x ou s pour la situation?

Peut être en se servant de la cellule "Temps U" et "Prix u Mat"???
c'est ce que j'ai fait pour additionner les temps unitaires et les prix unitaires des matériaux, en validant avec un x ou s à l'aide la fonction sommeprod. =SOMMEPROD((CF8:JN8="x")*($CF$3:$JN$3="Temps U");($CF$4:$JN$4))

 

[mapomme]

Re @APPRENTI:) ,

Je ne comprends pas bien. Je ne teste pas le "s" ou le "x" dans les cellules. Je teste juste si c'est vide ou pas. Je dois avoir raté quelque chose

 

[APPRENTI:)]

Mais au final, quoi que l'on rentre, cela fonctionne quand même!!!!