> 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
259,00 €PC Ankermann (Intel Pentium Dual Core, 8Go RAM, SSD 480Go, Win 10 Pro + Office 365) à 259 € livré
Valable jusqu'au 28 Mai

Amazon fait une promotion sur l'ordinateur de bureau Ankermann Silent PC Work à 259 € livré. Le PC est équipé d'un processeur Intel Pentium dual core à 2.7 GHz, de 8 Go de RAM, d'un SSD de 480 Go, d'un disque dur de 500 Go, d'un lecteur DVD, d'un lecteur de carte mémoires et tourne sous Windows 10 Pro 64 bits FR. Un abonnement d'un an à Microsoft Office 365 est également fourni.

Ajoutez un écran et un pack clavier souris et vous avez un PC complet à l'aise en bureautique et Internet. Garantie 2 ans.


> Voir l'offre
189,99 €Mini PC MeLE PCG35 (Celeron J4125, 8Go RAM, 128 SSD, Windows 10 Pro) à 189,99 €
Valable jusqu'au 26 Mai

Amazon fait une vente flash sur le mini PC MeLE PCG35 à 189,99 € au lieu de 219,99 € grâce à un coupon à activer sur la page du produit. La livraison est gratuite. Ce mini PC au format NUC d'Intel possède un processeur Intel Celeron J4125 4 coeurs avec chip graphique Intel UHD 600, 8 Go de RAM DDR4 et un SSD de 128 Go. Il dispose d'une connectique complète : un emplacement M.2 libre pour ajouter un SSD supplémentaire jusqu'à 4 To, un emplacement SATA 2.5 pouces pour ajouter un disque dur supplémentaire, le WiFi, le bluetooth, 4 ports USB 3.0, 2 ports HDMI 2.0 qui supportent jusqu'à la 4K, un port Ethernet Gigabit et tourne sous Windows 10 Pro (que vous pouvez mettre à jour pour Windows 11). Il est entièrement silencieux puisqu'il bénéficie d'une conception sans ventilateur et d'un refroidissement naturel. 

Branchez ce mini PC sur une TV (support VESA fourni) ou un écran et vous avez un ordinateur discret et performant pour Internet, de la bureautique et regarder des films.


> Voir l'offre
19,99 €Lecteur/graveur de CD/DVD externe USB 3.0 slim à 19,99 €
Valable jusqu'au 26 Mai

Cdiscount fait une promotion sur le lecteur/graveur de CD/DVD externe USB 3.0 slim qui passe à 19,99 €. Ce graveur vous permettra de lire et graver les CD et DVD sur votre ordinateur qui ne dispose pas de lecteur optique (soit la plupart des portables du marché). Branchez-le simplement sur un port USB de votre PC portable grâce à son câble intégré.


> Voir l'offre

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