Utilisation RECHERCHEV, SI, FONCTIONS IMBRIQUEES

[DAGUENEL 75015]

Bonjour à tous et toutes,
J'ai besoin d'aide. Je vais essayer de faire le plus simple possible. Je vous joins mon tableau pour les explications.
Mon tableau initial est en feuil11. Je dois comparer des données par rapport à un autre fichier.
A partir de ce tableau, je dois rechercher par rapport à la colonne C, dans la feuille 1, les éléments des colonnes D, I, P, T. C'est pour cela que j'ai fait des recherches V (colonnes E, J, Q, U).
Dans les colonnes F, K, R, V, j'ai mis des fonctions "SI" pour vérifier l'exactitude ou pas.
Première question : est-ce que cela vous parait bien? Puis-je faire plus simple ?

Ensuite, et c'est là mon plus gros souci.
Toujours à partir de mon fichier initial, mais cette fois si, ma référence n'est pas en colonne C, mais en colonne L. Je voudrais, par rapport à la feuille 2, avoir en colonne M, l'ensemble des chiffres qui correspondent au petit "a" de la colonne F en feuille 2, et en N l'ensemble des chiffres du "b" de la colonne G toujours en feuille 2.
Je prends un exemple : dans ma feuille initiale, les lignes 16 et 17, notre colonne L "1620145113219". Si on recherche par rapport à cette donnée en feuille 2, il y a quatre lignes qui correspondent. Je souhaite qu'on m'affiche en M, les chiffres présents en "a", soit dans ce cas 1, 3, 1 et 3 . c'est bien si il n'y a pas les doublons, donc 1 et 3. Pour la colonne N, même démarche. je souhaite avoir les chiffres en "b", soit 2,2,2,2...Idem pour les doublons, soit 2.
J'espère avoir été le plus clair possible. Si quelqu'un peut me simplifier tout ça, ce serait super.
Je vous remercie d'avance.
Laurent

 

[CISCO]

Rebonjour à tous

Ma dernière proposition était vraiment trop compliquée... J'ai mis à la place une version beaucoup plus simple.

@ plus

 

[DAGUENEL 75015]

Re,

Fais un test en mettant ce code dans la FEUIL11

Private Sub Worksheet_Calculate()
Dim i As Byte
    Dim j As Integer
    Dim col As String
    col = "M"
    For j = 1 To Range(col & "65536").End(xlUp).Row
        If Range(col & j) <> "" Then
            Range(col & j).Font.ColorIndex = 1
            Range(col & j) = Range(col & j).Text
                For i = 1 To Range(col & j).Characters.Count
                    With Range(col & j).Characters(Start:=i, Length:=1)
                        Select Case .Caption
                        Case "1": .Font.ColorIndex = 4
                        Case "9": .Font.ColorIndex = 3
                        End Select
                    End With
                Next i
        End If
    Next j
    col = "N"
    For j = 1 To Range(col & "65536").End(xlUp).Row
        If Range(col & j) <> "" Then
            Range(col & j).Font.ColorIndex = 1
            Range(col & j) = Range(col & j).Text
                For i = 1 To Range(col & j).Characters.Count
                    With Range(col & j).Characters(Start:=i, Length:=1)
                        Select Case .Caption
                        Case "1": .Font.ColorIndex = 4
                        Case "9": .Font.ColorIndex = 3
                        End Select
                    End With
                Next i
        End If
    Next j
End Sub

Salut CISCO, en effet c'est uncode que j'avais trouvé sur le Net, il y a le similaire en placant les résultats en colonnes au lieu de les mettre en ligne

Bonjour M12.
Ca, je ne sais pas faire du tout !
Cordialement

 

[DAGUENEL 75015]

Bonjour

Cf. en pièce jointe.

j'ai mis deux MFC dans la colonne E (On aurait pu faire avec une seule). A toi de voir si cela te convient. Tu peux donc supprimer la colonne F.

J'ai aussi défini les noms dans le gestionnaire de noms ListRAS et ListQUI (formules de M12) utiles dans les formules dans les colonnes RAS et QUI.

@ plus

Re bonjour Cisco,
Nickel pour la colonne F. Je peux donc faire la même chose pour ma colonne K, Q et T ?
Pour le reste, ca me paraît bien. Il faut que je regarde à tête reposée pour te faire remonter mes remarques éventuelles. Dans tous les cas, un grand merci.

J'ai une autre remarque. Tu as bien remarqué que dans ma première recherche, par rapport à la colonne C, on veut contrôler l'exactitude des infos en D, I, O, et R. Pourrait-on sans passer par des macros ou autres choses très complexes, savoir en bout de ligne par exemple, pour chaque donnée, si c'est 4 informations coincident avec celles recueillies dans la feuille 1, avoir un feu vert ou un "ok".
À défaut mettre un "pb sur colonne I" par exemple...
Ça fait quoi le "lookup" ?
Merci encore.
Laurent

 

[CISCO]

Bonjour

Lookup, c'est RECHERCHER en anglais.
Donc, dans Excel, VLookUp, c'est aussi RECHERCHEV.

Ici, la personne qui a fait la macro mise dans le fichier (Fais Alt+F11 et clique sur le module 2 pour voir le code correspondant) a appelé sa fonction personnalisée VLookUpList pour que l'on comprenne facilement ce que fait la fonction en question, à savoir un peu comme VLookUp mais en affichant finalement une liste et pas seulement un résultat.

@ plus

 

[CISCO]

Bonjour

Re bonjour Cisco,
... Tu as bien remarqué que dans ma première recherche, par rapport à la colonne C, on veut contrôler l'exactitude des infos en D, I, O, et R. Pourrait-on sans passer par des macros ou autres choses très complexes, savoir en bout de ligne par exemple, pour chaque donnée, si c'est 4 informations coincident avec celles recueillies dans la feuille 1, avoir un feu vert ou un "ok".
À défaut mettre un "pb sur colonne I" par exemple...
Ça fait quoi le "lookup" ?
Merci encore.
Laurent

On doit certainement pouvoir le faire, mais comme je ne comprend pas exactement ce que tu veux...
Peut être avec quelque chose du genre
SI(D2&I2&O2&R2=...

Peux tu expliquer ce que tu veux avoir ?

@ plus

 

[DAGUENEL 75015]

Merci Cisco pour l'information.
À priori, dans la colonne QUI, ca ne fonctionne pas car quand je modifie un chiffre dans ma feuille 2 en colonne G, ca ne le prend pas en compte.
Par contre pour RAS, ca fonctionne !

Ça fait quoi ta fonction "substitue" ?
Merci
Laurent

 

[CISCO]

Rebonjour

Chez moi, ça fonctionne si on change une valeur dans la colonne b (bien sûr, dans les bonnes lignes).

Il y a peut être un petit pb de recalcul, de mise à jour. Essaye, après avoir changé des données dans la feuille 2, juste en sélectionnant N2 et en double-cliquant sur la poignée en bas à droite de cette cellule.

SUBSTITUE permet de remplacer une lettre ou un terme par un autre dans un mot, une phrase.
Par exemple SUBSTITUE("Paul";"a";"o") donnera Poul. Sélectionne une cellule contenant cette fonction, puis sur SUBSTITUE. Clique ensuite sur fx juste à gauche de la barre de formule. Tu auras quelques explications. Au besoin, utilise l'aide dans le bas de la fenêtre obtenue.

@ plus

 

[DAGUENEL 75015]

Rebonjour

Chez moi, ça fonctionne si on change une valeur dans la colonne b (bien sûr, dans les bonnes lignes).

Il y a peut être un petit pb de recalcul, de mise à jour. Essaye, après avoir changé des données dans la feuille 2, juste en sélectionnant N2 et en double-cliquant sur la poignée en bas à droite de cette cellule.

SUBSTITUE permet de remplacer une lettre ou un terme par un autre dans un mot, une phrase.
Par exemple SUBSTITUE("Paul";"a";"o") donnera Poul. Sélectionne une cellule contenant cette fonction, puis sur SUBSTITUE. Clique ensuite sur fx juste à gauche de la barre de formule. Tu auras quelques explications. Au besoin, utilise l'aide dans le bas de la fenêtre obtenue.

@ plus

Re Cisco,
Je n'arrive pas à reproduire ta liste de noms ainsi que ta formule "substitue". Je n'ai pas de résultats. Je dois oublier une manip ?
Il faut que je sélectionne la colonne entière pour donner un nom à la zone?
Sur quel élément tu as fait ton lookuplist?
Merci
Laurent

 

[DAGUENEL 75015]

Re Cisco et M12,

voici mon fichier un peu actualisé avec toutes les infos que vous m'avez données. C'est déjà beaucoup mieux et je vous en remercie.
1- je n'arrive pas à reproduire la fonction Vlookuplist ainsi que le gestionnaire de noms, je ne comprends pas vraiment. (colonne O et S)
2- Ensuite pour répondre à la demande de Cisco, je souhaiterais avoir si possible, concernant ma première partie de requète avec la feuille 1, si on regarde la ligne 1, toutes mes réponses test sont ok, (G2-K2-N2-R2) en bout de ligne un "OK" (comme j'ai mis dans l'exemple).
si une info n'est pas bonne ou manquante, alors mettre la mention "PB" voir exemple ligne 3.
C'est surtout pour attirer l'attention au premier coup d'oeil.
si il m'apporte l'info erronée, ce serait le top, mais je ferais sans, car à mon avis sans macro, ce sera difficile....voir exemple ligne 4.
J'espère avoir été un peu plus clair.
Je vous remercie de votre implication.
PS: ce soir, je ne serais pas disponible, alors ne soyez pas vexé ou surpris si je ne vous réponds pas immédiatement.

Laurent

 

[CISCO]

Bonsoir

Pas le temps ce soir non plus. Demain peut être...

@ plus

 

[DAGUENEL 75015]

Re bonjour à tous, Cisco et M12,
Tu mets la fonction Vlookuplist dans le gestionnaire de noms ? J'ai beau essayé, je ne trouve pas le moindre résultat. En espérant que tu vas pouvoir m'aider encore un peu !
Peut-on faire une MFC par rapport à des couleurs de cellules ?
Merci encore à tous les deux.
Laurent

 

[CISCO]

Bonjour

Pour ce qui est de VLookUpList, est-ce que tu travailles sur un des fichiers mis sur le forum par M12 ou par moi, ou sur un autre fichier ? Est-ce que lorsque tu fais Alt+F11 et que tu sélectionnes le module 2, en bas à gauche, tu as un code à l'écran ?

La fonction VLookUPList n'est pas native dans Excel (c-à-d qu'elle n'existe pas d'origine dans Excel, comme RECHECHEV, MAX, INDEX et tant d'autres). Pour que cela fonctionne, M12 a mis un code dans le module 2 sur le fichier qu'il a proposé en pièce jointe. Pour voir ce code, soit tu fais avec Alt+F11 etc comme décrit ci-dessus, soit tu fais avec un clic droit sur le nom de la feuille, sur l'onglet, puis tu choisis "Visualiser le code", puis tu cliques sur Module 2. Tu dois obtenir la fenêtre ci-dessous.

Si tu veux travailler avec un autre fichier que ceux proposés sur le forum, il faut que tu copies ce code et que tu le colles sur un module dans ton fichier perso.

Function VLookUpList(ValeurRecherchee As Range, TableDeRecherche As Range, NumColonne As Integer, Separator As String) As Variant

Dim NbLignes As Integer
NbLignes = TableDeRecherche.Rows.Count

Dim CompteurValeursTrouvees As Integer
CompteurValeursTrouvees = 0

For i = 1 To NbLignes
    If TableDeRecherche(i, 1).Value = ValeurRecherchee.Value Then
        CompteurValeursTrouvees = CompteurValeursTrouvees + 1
        If CompteurValeursTrouvees > 1 Then
            VLookUpList = VLookUpList & Separator & TableDeRecherche(i, NumColonne).Value
        Else
            VLookUpList = TableDeRecherche(i, NumColonne).Value
        End If
    End If
Next i

End Function

Pour ce qui est des MFC, non, on ne peut pas en faire une par rapport à la couleur des cellules. Mais par contre, on peut très bien utiliser le test choisi pour colorer ou pas la cellule. Et hop, tout se fait automatiquement...

@ plus

 

[DAGUENEL 75015]

Merci Cisco, c'est exactement ça.
Je vais copier ce code. ça devrait marcher, j'espère.
Je te tiens au courant dans la journée.

@plus
Laurent

 

[DAGUENEL 75015]

A Cisco et M12,

Ça fonctionne . Un grand et énorme Merci.
J'ai une dernière question. Il m'arrive fréquemment de traiter des fichiers comme celui ci. Comment puis je reproduire toutes ces requêtes, mise en forme, calculs....etc sans avoir tout à refaire à chaque fois !!! Un copier / coller sur l'ancien fichier ??
Merci beaucoup
Laurent

 

[CISCO]

Bonsoir

A Cisco et M12,
Ça fonctionne . Un grand et énorme Merci.
... toutes ces requêtes, mise en forme, calculs....etc sans avoir tout à refaire à chaque fois !!! Un copier / coller sur l'ancien fichier ??
Merci beaucoup
Laurent

Oui, tu peux faire avec un copier/coller sur l'ancien fichier. Il te faudra aussi peut être modifier les plages d'action des MFC, en fonction du nombre de lignes de ton nouveau fichier (Tu peux aussi mettre tout de suite un nombre de lignes très grand pour couvrir une plage d'action plus grande que nécessaire).

@ plus