Bonjour le Forum

Je souhaiterais récupérer des données concaténées avec le numéro de ligne sous une condition.

Je joins un exemple commenté.

merci de votre aide

http://www.cjoint.com/c/EFsjbvrIEng

Bonjour,

Voici une ébauche qui respecte intégralement la disposition de ton fichier. D'autres solutions sont possibles en modifiant l'organisation de tes différentes plages...

La première partie de la solution constitue une solution incomplète dans la mesure où elle affiche à l'emplacement souhaité (à une cellule près) le numéro de la ligne mais pas la concaténation des valeurs. Elle n'utilise que des fonctions standard d'Excel.

Cette solution procède en 2 temps : définition du numéro de ligne en regard de la plage de recherche (chaque colonne à partir de CF étant affectée à la recherche d'une ligne des valeurs en BE:BI (à partir de la ligne 10), soit la recherche des valeurs BE10:BI10 faite en CF, BE11:BI11 en CG, etc. ; les lignes trouvées sont ensuite récapitulés sur les lignes 10, 11... à partir de BK (BJ devant rester vide pour les besoins de la formule).

La formule de recherche dans les colonnes CF, CG... aurait pu opérer la concaténation au lieu de se contenter de fournir seulement le numéro de ligne, mais je n'étais pas très enclin à écrire une formule de plusieurs lignes... D'autant que la récapitulation sur les lignes 10, 11... aurait conduit à intercaler une ligne vide entre chaque ligne de valeur cherchée. J'ai préféré prolonger la première partie par une seconde utilisant des fonctions personnalisées.

Voyons pas à pas, en commençant par la première partie.

Les formules utiliseront une définition indirecte des plages. Il est judicieux de placer ces définitions de façon que leur utilisation permettent des formules recopiables.

Définition des plages de valeurs cherchées -

La formule placée en BJ1 renvoie l'adresse de la première plage de valeurs cherchées. En la tirant sur la ligne 1, on obtient les plages de valeurs cherchées suivantes. On voit que INDIRECT(BJ$1) utilisée dans une formule en colonne CF renverra BE10:BI10, mais si la formule est tirée sur CG, l'expression deviendra INDIRECT(BK$1) et renverra BE11:BI11.

Définition des plages de résultats de recherche -

Il s'agit des plages dans lesquelles on aura mis une formule renvoyant la ligne pour laquelle la recherche est positive.

La formule placée en BJ2 renvoie l'adresse de la plage où l'on aura les résultats pour BE10:BI10. En la tirant vers le bas, on obtient les adresses des plages de résultats pour BE11:BI11, BE12:BI12... De façon semblable à l'utilisation vue précédemment, INDIRECT($BJ2) dans une formule placée sur la ligne 10 deviendra INDIRECT($BJ11) en étant recopiée ligne 11, etc.

Ces préparatifs étant réalisés, on peut passer aux formules de recherche.

Formule de recherche -

La formule placée en CF1 renvoie le numéro de la ligne si la plage CA1:CE1 répond à la condition de recherche pour les valeurs de BE10:BI10. Elle est écrite de façon à pouvoir être recopiée aussi bien vers le bas que vers la droite en tirant la formule sur la ligne, puis en tirant la ligne de formules vers le bas.

A noter que NB.SI(plage de recherche;plage de valeurs cherchées) renvoie une matrice de 1 (pour chaque valeur cherchée figurant dans la plage de recherche) ou 0 (si elle n'y figure pas), que SOMMEPROD additionne. Le problème présuppose l'absence de doublons dans chacune des plages...

Récapitulation de résultats -

La formule en BK10 va chercher dans la plage CF1:CF1000 la valeur minimale affichée (qui correspond nécessairement à la première ligne répondant à la condition). En décalant la plage de cette première valeur, le minimum dans la plage décalée sera la deuxième ligne répondant à la condition, et ainsi de suite, en tirant la formule vers la droite, on obtiendra toutes les lignes ayant répondues à la condition. EQUIV appliqué à une plage partant de la ligne 1 renvoie directement le numéro de ligne cherché, sans autre correction. BJ reste vide car le décalage initial pour la première valeur cherchée est 0 (si on veut utiliser cette colonne on ne peut mettre dès le départ une formule recopiable sur la ligne, laisser la colonne vide permet de ne taper qu'une formule au lieu de 2). SIERREUR empêche l'affichage de #NA lorsqu'il n'y a plus de résultat.

Comme précédemment, la formule est tirable vers le bas et vers la droite.

Pour aboutir au résultat cherché -

Dans la formule de recherche (voir ci-dessus), on voit que lorsque la condition est vraie, la formule renvoie LIGNE(), soit le numéro de la ligne. On aurait pu faire précéder cet éléments des autres que l'on veut concaténer, soit chaque valeur en testant si elle répond à la condition (5 tests), des tirets entre chacune et un slash à la fin (avant le numéro de ligne), sans oublier un test supplémentaire pour éliminer un éventuel tiret initial lorsque la première valeur ne répond pas à la condition. Soit une formule de 3 lignes, sinon plus.

La récapitulation des résultats ensuite ne se serait pas opérée aussi simplement. On récupère des chaînes et non des nombres. Il aurait donc fallu opérer sur 2 lignes pour la récap de chacune : une pour avoir les valeurs numériques de décalage à utiliser (comme on l'a vu) et l'autre pour prélever le résultat...

Il paraissait plus "économique", soit de procéder entièrement par macro pour cette opération, soit rester dans le dispositif formules qu'on a vu en le complétant par l'élaboration de fonctions personnalisées, utilisables avec les fonctions intégrées d'Excel et adaptées au résultat recherché.

J'ai opté pour une fonction personnalisée réalisant la concaténation et pouvant être intégrée à la formule de recherche d'une part, et une autre permettant plus aisément la récupération des résultats (en remplacement de la formule vue ci-dessus).

Les fonctions personnalisées sont des macros, des procédures VBA de type Function, renvoyant un résultat, et qui peuvent être utilisées dans les mêmes conditions que les fonctions de feuille de calcul intégrées à Excel.

Les procédures ci-après doivent être placées dans un module standard (par copier-coller, sans difficulté). Le classeur devra alors être enregistrée en .xlsm (parce qu'il contient des macros), l'enregistrement en .xlsx se solderait par la suppression des macros...

Fonction de concaténation adaptée au résultat recherchée -

Function CONCAT_CONDIT(plch As Range, plrech As Range) As String
Dim c As Range, cc(), tch(), m%, n%, i%
Application.Volatile
m = plch.Cells.Count - 1
ReDim tch(m)
For Each c In plch.Cells
tch(i) = c.Value
i = i + 1
Next c
ReDim cc(0)
For Each c In plrech.Cells
For i = 0 To m
If c.Value = tch(i) Then
n = n + 1
ReDim Preserve cc(n)
cc(n) = c.Value
End If
Next i
Next c
For i = 1 To n
cc(0) = cc(0) & cc(i) & "-"
Next i
Mid(cc(0), Len(cc(0)), 1) = "/"
CONCAT_CONDIT = cc(0)
End Function

Comme je l'ai déjà indiqué, on va l'utiliser en l'intégrant à la formule de recherche :

La modification de la formule en CF1 consiste en l'ajout encadré de vert.

Ce dernier est constitué par la fonction personnalisée, réclament 2 arguments : la plage de valeurs testées [INDIRECT(BJ$1) renvoie à BE10:BI10] et la plage de recherche (ligne concernée de la plage). La fonction opère la concaténation prévue (jusqu'au slash et il y est ajouté le numéro de ligne).

On se retrouve dans le cas décrit : la recherche renvoie la chaîne souhaitée, mais pour récapituler les résultats sans toucher à l'organisation des données sur la feuille, une fonction personnalisée permettra d'y procéder plus facilement.

Fonction de récupération des résultats -

Function RECH_CONDIT(plrech As Range, idx As Integer) As String
Dim i%, n%, c As Range
Application.Volatile
For Each c In plrech.Cells
If c.Value Like "*/*" Then
n = n + 1
If n = idx Then
RECH_CONDIT = c.Value
Exit Function
End If
End If
Next c
RECH_CONDIT = ""
End Function

Utilisation :

Elle se substitue à la formule précédemment mise pour récupérer des numéros de ligne. La formule en BJ1 (on ne laisse plus la colonne vide car on n'utilise plus de décalages à initialiser à 0...) utilise la fonction avec 2 arguments : la plage de résultats [INDIRECT($BJ2) renvoie CF1:CF1000] et le rang du résultat trouvé [COLONNE(A:A) renvoie 1, recopié dans la colonne suivante devient COLONNE(B:B) qui renvoie 2...]. Rien n'est affiché lorsqu'il n'y a plus de résultat.

Pour conclure, ton fichier en retour : http://www.cjoint.com/c/EFsqeNkefsy

J'ai dupliqué la feuille 1 pour distinguer les 2 parties décrites dans les posts précédents.

Le classeur est en .xlsm et il convient d'activer les macros à l'ouverture pour que les fonctions personnalisées soient actives.

Bonjour,

Un grand merci pour cette solution et surtout la méthodologie...

Il me reste à imprimer et à regarder en détail tout ça ce we...

bonne journée