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 !
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
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) = ""
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 !
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())
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