> Tous les forums > Forum Bureautique
 Comparaison 3 lignes avec fichier
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
JJ82
  Posté le 18/06/2015 @ 11:03 
Aller en bas de la page 
Petit astucien

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

Publicité
ferrand
 Posté le 18/06/2015 à 16:06 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

ferrand
 Posté le 18/06/2015 à 17:20 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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).

ferrand
 Posté le 18/06/2015 à 17:43 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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).

ferrand
 Posté le 18/06/2015 à 17:59 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

ferrand
 Posté le 18/06/2015 à 18:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

JJ82
 Posté le 19/06/2015 à 10:12 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

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

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour participer à la discussion.
Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !
Recevoir PC Astuces par e-mail


La Lettre quotidienne +226 000 inscrits
Avec l'actu, des logiciels, des applis, des astuces, des bons plans, ...

Les bonnes affaires
Une fois par semaine, un récap des meilleurs offres.

Les fonds d'écran
De jolies photos pour personnaliser votre bureau. Une fois par semaine.

Les nouveaux Bons Plans
Des notifications pour ne pas rater les bons plans publiés sur le site.

Les bons plans du moment PC Astuces

Tous les Bons Plans
Batterie externe OUKITEL BP2000 2048 Wh (extensible jusqu'à 16 Kw), 2200W, LifePOE4 à 899 €
899 € 1500 € -40%
@Geekbuying
Centrale électrique OUKITEL P1201 960Wh, 1200W, LifePOE4 à 479 €
479 € 700 € -32%
@Geekbuying
Lego Le Seigneur des Anneaux : Fondcombe (10316) à 424,99 €
424,99 € 499,99 € -15%
@Amazon
SSD Lexar NQ100 240 Go (SATA, 2.5 pouces) à 17,99 €
17,99 € 20 € -10%
@Amazon
Chauffe-matelas double XXL Beurer TS 26 (150x140 cm) à 49,99 €
49,99 € 94,49 € -47%
@Amazon
Ecouteurs sans fil Bluetooth Xiaomi Redmi Buds 6 Play à 10,96 €
10,96 € 20 € -45%
@Amazon

Sujets relatifs
Faire un tableau sur Excel avec des sauts de lignes
Fichier joint avec un fichier pdf
Pas de son fichier pps lu avec OpenOffice
Comment créer fichier client avec carte de fidélité
securiser fichier avec mot de passe pour chacun
fichier lock avec mon disque dur externe
fichier créé sur word 2007...illisible avec word 2002...
Fichier .csv avec Excel
PB avec mon logiciel Outlook 2010 et fichier Outlook.pst
impossible d'ouvrir directement fichier word avec wmail
Plus de sujets relatifs à Comparaison 3 lignes avec fichier
 > Tous les forums > Forum Bureautique