Concatener en matricielle

Hello there comment vous allez ?

Je reviens vers vous pour un nouveau problème sur lequel je bloque depuis plusieurs heures mtn ..

Est-il possible de NB.SI + Concatener en matriciel ?

C'est à dire de chercher toutes les combinaisons d'un concatener sur plusieurs colonnes dans une colonne ? Ca m'éviterai de faire une formule de 2km de long toute moche ...

Je sais c'est pas très clair comme ça mais jetez un coup d’œil à mon fichier joint ça aidera

Merci d'avance !

54test.xlsx (11.13 Ko)

Bonsoir,

Cela ne marchera pas... Si tu utilises CONCATENER sur 3 cellules en ligne en matriciel, tu obtiendras 3 combinaisons : celle des 1er éléments de chaque plage, des 2e et des 3e, mais dans la formule seul le premier élément concaténé est pris en compte.

Et de toute façon tu n'obtiendrais pas 6 ou 9 éléments concaténés, matrice de toutes les combinaisons entre tes deux plages.

La solution pratique me paraît être une fonction personnalisée qui les compte un par un !

Function NBCOMBI(PlgElm1 As Range, PlgElm2 As Range, PlgRech As Range)
    Dim n%, i%, j%, ar$, c As Range
    Application.Volatile
    For i = 1 To PlgElm1.Cells.Count
        For j = 1 To PlgElm2.Cells.Count
            ar = PlgElm1.Cells(i) & PlgElm2.Cells(j)
            For Each c In PlgRech
                If c = ar Then n = n + 1
            Next c
        Next j
    Next i
    NBCOMBI = n
End Function

Cordialement.

Hello,

Oui je me suis aperçu que ça ne marchait pas et je me doutai qu'il faudrait passer par une fonction perso, mais mon idée me paraissait pas si bête.

Par contre tu sais si je peux mettre NBCOMBI dans une formule de type NB.SI.ENS ? Car pour chaque concatener dans la plage de recherche se trouve une date en face et je veux savoir le nombre de NBCOMBI inférieur à une date en particulier.

J'ai essayé avec un SOMME(SI()) sans succès, à mon avis il va falloir que je trifouille dans la fonction perso mais ça dépasse mes compétences ..

Merci !!

Bonjour,

J'essaie d'aménager la fonction, mais pour l'instant au bout de multiples essais, quand elle fonctionne sans condition elle renvoie une erreur avec, et vice versa, et je ne comprends pas pourquoi encore...

Comme je ne peux passer la journée là-dessus, j'y reviendrai... avec plus d'inspiration peut-être...

Cordialement.

Vais-je enfin atteindre ta limite ?

En tout cas merci beaucoup !

Bonjour,

J'ai repris la chose avec un regard neuf pour constater que selon les cas la condition arrivait soit sous une forme <01/08/2017, soit <42948 ! Et évidemment dans le second cas en scindant en fonction d'une date à 10 caractères, tout foirait !

Pourquoi d'un côté je fais des tests qui fonctionne, où la condition est bien passée sous la première forme, et je transpose le code dans la fonction, rien ne va plus car la même condition arrive sous une autre forme ? (question à laquelle je ne répondrai pas ! )

J'ai donc changé complètement les tests de démarrage, et là ça a l'air de convenir... Le calcul sans condition continue à se faire aussi.

Voilà donc la dernière mouture de la fonction :

Function NBCOMBI(PlgElm1 As Range, PlgElm2 As Range, PlgRech As Range, Optional PlgCond _
 As Range, Optional Cond)
    Dim n%, i%, j%, k%, ar$, op$, cd As Boolean
    Application.Volatile
    If Not IsMissing(Cond) Then
        For i = 1 To 3
            If IsNumeric(Mid(Cond, i, 1)) Then Exit For
        Next i
        Select Case i
            Case 1: op = "=": Cond = CLng(CDate(Cond))
            Case 2, 3: op = Left(Cond, i - 1): Cond = CLng(CDate(Replace(Cond, op, "")))
            Case Else: NBCOMBI = CVErr(xlErrNum): Exit Function
        End Select
    Else
        cd = True
    End If
    For i = 1 To PlgElm1.Cells.Count
        For j = 1 To PlgElm2.Cells.Count
            ar = PlgElm1.Cells(i) & PlgElm2.Cells(j)
            For k = 1 To PlgRech.Cells.Count
                If Not IsMissing(Cond) Then
                    cd = Evaluate(PlgCond.Cells(k).Value2 & op & Cond)
                End If
                If PlgRech.Cells(k) = ar And cd Then n = n + 1
            Next k
        Next j
    Next i
    NBCOMBI = n
End Function

Les 3 premiers arguments sont inchangés. 2 arguments optionnels sont ajoutés. Ces 2 arguments fonctionnent ensemble pour le cas où l'on met une condition au calcul : le 4e est donc la plage de conditions et le 5e la condition proprement dite.

Si l'on met une plage de conditions et qu'on omet l'argument condition, ce sera évidemment incomplet, mais ne déclenchera pas d'erreur, car on teste la présence d'une condition demandée uniquement sur le 5e argument. Le calcul se fera donc, mais sans condition...

Le 4e argument, plage où doit être testée la condition, elle doit être de même taille que la plage de recherche, naturellement, toutefois, si elle était plus grande, comme pour les fonctions SOMME.SI et apparentées cela ne déclencherait pas d'erreur, puisque seules les cellules en correspondance avec la plage de recherche seraient testées, et les autres ignorées. Si la plage était plus petite que la plage de recherche, une erreur est possible, mais pas obligatoire, cela dépendra des cellules non indiquées qui seront tout de même testées ! (car si on a une 6 cellules à tester et qu'on indique une plage de 5, Excel trouvera une 6e cellule à tester, test qui ne déclenchera une erreur que si cela fait sortir de la feuille ou s'il y a incompatibilité de type de données pour opérer une comparaison).

Le 5e argument est à fournir dans les mêmes conditions que pour les fonctions SOMME.SI et apparentées, soit avec un opérateur de comparaison (=, <, >, <=, >=) ou sans (qui sera équivalent à =). Par contre, à la suite de l'opérateur, la fonction attend exclusivement une date. On peut donc l'indiquer littéralement (ou numériquement, puisque ça fonctionne !), ou la désigner par une référence de cellule : "<"&J1 (par exemple).

Si l'on n'indique pas une date, il est probable que l'on ait une erreur, à moins que la comparaison puisse s'opérer : les dates étant numériques, et comparées sous forme numérique, tous nombres pouvant correspondre à des dates pourront passer sans qu'Excel s'y oppose...

Cet ajout par rapport à la version précédente sans condition de la fonction, se traduit par une détection en début d'exécution de condition ou non. Si condition, on sépare l'opérateur de la date, de façon à assurer la conversion sous format numérique de la date. On recherche lequel des 3 premiers caractères est numérique (que la date soit déjà sous forme de nombre ou sous forme littérale, son 1er caractère sera toujours un chiffre) : si le 1er, c'est que pas d'opérateur (donc =), si le second (opérateur = ou < ou >) et si le 3e (opérateur <= ou >=), si on ne trouve pas de chiffre dans les 3 premiers caractères, la fonction affichera la valeur d'erreur #NOMBRE!

Lors du test ensuite, si l'on est sous condition, on prélève la date de la plage condition sous forme numérique pour former une expression avec l'opérateur et la condition, qui renverra VRAI ou FAUX lors de son évaluation, résultat recueilli dans une variable booléenne, systématiquement introduite dans le test principal qui figurait dans la version initiale.

Si l'on est dans un calcul sans condition, lors du test en début de fonction, cette variable booléenne sera alors mise à True et ne variera pas durant le déroulement de la fonction (puisque aucune évaluation de condition ne sera alors faite).

A toi la suite !


Resalut,

Je suis sûr que tu en as mal dormi

Je te remercie grandement pour ton aide (et pour les explications, où je vais devoir m'y reprendre à plusieurs fois avant de comprendre). A vrai dire il y aura toujours la condition < ou > à une date, donc si ça allège la macro tu peux supprimer le test de condition (désolé j'aurai dû le préciser avant ...)

J'ai essayé de le transposer à mon fichier qui au final comporte 3 plages à concatener, et bim bam boum j'ai tout cassé (il en faut peu).

Ci joint le code que j'ai "modifié",j'ai annoté quelques questions en commentaire pour ma culture personnelle :

Function NBCOMBI(PlgElm1 As Range, PlgElm2 As Range, plgElm3 As Range, PlgRech As Range, Optional PlgCond As Range, Optional Cond)
    Dim n%, i%, j%, k%, l%, ar$, op$, cd As Boolean
    Application.Volatile
    If Not IsMissing(Cond) Then
        For i = 1 To 3
            If IsNumeric(Mid(Cond, i, 1)) Then Exit For
        Next i
        Select Case i
            Case 1: op = "=": Cond = CLng(CDate(Cond))
            Case 2, 3: op = Left(Cond, i - 1): Cond = CLng(CDate(Replace(Cond, op, "")))
            Case Else: NBCOMBI = CVErr(xlErrNum): Exit Function
        End Select
    Else
        cd = True
    End If
    For i = 1 To PlgElm1.Cells.Count
        For j = 1 To PlgElm2.Cells.Count
            For k = 1 To plgElm3.Cells.Count
                ar = PlgElm1.Cells(i) & PlgElm2.Cells(j) & plgElm3.Cells(k)
                    For l = 1 To PlgRech.Cells.Count
                    If Not IsMissing(Cond) Then
                        cd = Evaluate(PlgCond.Cells(l).Value2 & op & Cond) 'Que signifie Value2 ? J'ai du mal à comprendre la définition de la variable cd
                    End If
                    If PlgRech.Cells(l) = ar And cd Then n = n + 1 'ne comprend pas cette ligne, veut dire que si ar (mon concatener) est dans la plage de recherche alors ajout +1 a n (que renvoi NBCOMBI), mais pourquoi le cd ?
                    Next l
                Next k
            Next j
    Next i
    NBCOMBI = n
End Function

Désolé pour avoir tout cassé lors de la transpo ... Ca m'apprendra à faire un fichier test propre et représentatif du 1er coup.

Merci par avance !

Edit : ne cherche pas ça fonctionne sur le fichier test, je vais enquêter sur le pourquoi du comment ça fonctionne pas sur le fichier source

Edit 2 : En fait c'était pas dur ... Comme un neneu j'ai selectionné des plages de recherches en colonne entière, d'où l'erreur .. Jvais faire des plages dynamiques à la place. Donc c'est bon je pense ! Par contre je veux bien les explications aux phrases vertes !

Dis donc ! Je me suis étendu sur le cd ! S'il y a condition de date, il est défini au-dessus par l'évaluation de l'expression faite avec Evaluate. S'il n'y a pas condition date, il est défini à True au début de la macro.

Et il est intégré dans le test de l'expression concaténée avec And. Son rôle sera nul s'il n'y a pas condition date (car toujours à True, seul ar déterminera le résultat. Mais il intervient à égalité si condition date.

Ah ! Value2 ! Value2 fait la même chose que Value, sauf pour les dates et les valeurs au format monétaire, pour lesquelles elle renvoie la valeur numérique, telle que stockée. C'est un des moyen d'éviter des problèmes avec les dates quand on peut l'utiliser.

Alors j'ai testé ça fonctionne bien.

Par contre, si dans une des cellules de la PlgCond on a une cellule vide c'est le drame et on a une erreur (je sais, un tableau ne devrait JAMAIS avoir de cellules vides).

Tu sais si ce problème peut être contourné dans le code ? Si c'est trop long ne t'embête pas, j'obligerai les gens à mettre 0 dans la cellule.

En te remerciant par avance.

Comme ça :

                If Not IsMissing(Cond) Then
                    If PlgCond.Cells(k) <> "" Then
                        cd = Evaluate(PlgCond.Cells(k).Value2 & op & Cond)
                    Else
                        cd = True
                    End If
                End If

Le fait que les cellules vides renvoient 0 (en contexte numérique) est toujours un problème avec les dates, car Excel reconnaît 0 comme date valide (du fait que les heures brutes sont sur la valeur racine date 0) et l'affiche comme 0 janvier 1900... Il vaut mieux éliminer ce cas, mais il ne devrait pas déclencher d'erreur dans une comparaison numérique.

Bordel de crotte j'avais essayé tout seul mais j'avais fait sur la conditions Plgcond.Cells(k) = "" un jour j'y arriverais !

Par contre me renvoie pas la bonne valeur avec cd = True, mais avec False c'est OK (J'ai pas cherché à comprendre ^^)

Merci dix mille fois !

Ça, ça dépend si tu veux qu'en l'absence de date on considère que la condition soit vraie ou fausse ! J'avais opté pour vrai en l'absence (le bénéfice du doute en quelque sorte ! ) mais si tu décides qu'il ne faut pas retenir, il faut mettre False !

Ah mais oui je suis con ... On a pas du tout la même logique du coup

Mais merci pour l'explication ahah jsuis fatigué dès le matin

La fonction personnalisée fonctionne mais malheureusement elle est trop lourde et fait planter mon fichier (pourtant j'ai un processeur 4 coeur) car je dois l'appliquer à une centaine de ligne .. (je comprend pourquoi, elle teste toutes les combinaisons possibles). Cependant je la garde de côté elle pourra servir un jour ou l'autre.

En attendant je vais me rabattre sur ma SOMME(NB.SI.ENS(de chaque combinaison)), formule très moche de 4km de long mais bon je laisserai les propriétaires du tableau se dépatouiller avec ça, ou trouver une solution alternative pour avoir des références uniques et éviter les CONCATENER

En tout cas merci beaucoup pour ton travail MFerrand toujours au top !

On va essayer de bâtir une version dico !

J'aurais peut-être dû commencer par là...

Bonsoir,

Essaie cette version pour voir...

Function NBCOMBISI(PlgElm1 As Range, PlgElm2 As Range, PlgRech As Range, Optional PlgCond _
 As Range, Optional Cond)
    Dim d As Object, i%, j%, ar$, op$, cd As Boolean
    Application.Volatile
    If Not IsMissing(Cond) Then
        For i = 1 To 3
            If IsNumeric(Mid(Cond, i, 1)) Then Exit For
        Next i
        Select Case i
            Case 1: op = "=": Cond = CLng(CDate(Cond))
            Case 2, 3: op = Left(Cond, i - 1): Cond = CLng(CDate(Replace(Cond, op, "")))
            Case Else: NBCOMBISI = CVErr(xlErrNum): Exit Function
        End Select
    End If
    Set d = CreateObject("Scripting.Dictionary")
    If Not IsMissing(Cond) Then
        For i = 1 To PlgCond.Cells.Count
            cd = Evaluate(PlgCond.Cells(i).Value2 & op & Cond)
            If cd Then
                ar = PlgRech.Cells(i): d(ar) = CInt(d(ar)) + 1
            End If
        Next i
    Else
        For i = 1 To PlgRech.Cells.Count
            ar = PlgRech.Cells(i): d(ar) = CInt(d(ar)) + 1
        Next i
    End If
    For i = 1 To PlgElm1.Cells.Count
        For j = 1 To PlgElm2.Cells.Count
            ar = PlgElm1.Cells(i) & PlgElm2.Cells(j)
            n = n + CInt(d(ar))
        Next j
    Next i
    NBCOMBISI = n
End Function

Les arguments sont les mêmes que pour la version précédente.

Cordialement.

Re !

C'est mieux mais il me faut tjs au moins 2 min pour calculer mes 100 lignes (c'est raisonnable mais plus lent que ma SOMME(NB.SI.ENS()) je comprend pas pourquoi il y a autant de différence.

T'embêtes pas plus je pense que jvais rester sur ma formule de 40km ..

Pour info voici la formule utilisée pour un CONCATENER de 3 conditions où la 1ère possède 3 variables, la deuxième 4 et la dernière est unique (Attention ça pique les yeux, c'est très moche et ça devrait être interdit, mais ça fonctionne )

=SOMME(NB.SI.ENS(CONCATENER;CONCATENER(D3;G3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(E3;G3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(F3;G3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(D3;H3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(E3;H3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(F3;H3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(D3;I3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(E3;I3;K3);DATE;">"&AA3);;NB.SI.ENS(CONCATENER;CONCATENER(F3;I3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(D3;J3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(E3;J3;K3);DATE;">"&AA3);NB.SI.ENS(CONCATENER;CONCATENER(F3;J3;K3);DATE;">"&AA3))

Juste au cas où tu pourrais rajouter une plage 3 de recherche pour la combi CONCATENER, et mettre que si sur PlgCond on a cellule vide alors cd = False ? J'ai essayé mais sans succès ..

Merci beaucoup et désolé pour le cassage de tête

Bonjour,

La fonction NB.SI.ENS a comme syntaxe : =NB.SI.ENS(plagecritère1;critère1;plagecritère2;critère2;...)

Or, dans ta formule :

plagecritère1 est : CONCATENER

plagecritère2 est : DATE

Je ne vois pas bien en quoi cela définit des plages ?

Ah oui parce que ce sont des noms définis sur une plage dynamique (oui j'aime me compliquer la vie, il faudra que je leur donne un autre nom pour éviter les confusions ) mais en gros mon CONCATENER = PlgRech et DATE = PlgCond. Désolé ^^

Rechercher des sujets similaires à "concatener matricielle"