Discussion :

[zaza45]

Bonjour

J'ai besoin d'un petit coup de pouce.

Dans une table en col A j'ai des valeurs et en B des codes:

A	B
FT453	1000
th894
7539y
AvgT
1459
7539y
FT453	1000
FT453	2000
7539y
FT453	2000

Par exemple, pour la valeur FT453, je peux retrouver plusieurs lignes avec des codes qui commence par 1000 ou 2000

J'aimerai trouver la formule qui m'affiche en col C, pour la valeur FT453, toujours le code qui commence par 2000.

Je vous remercie beaucoup pour votre aide.

[ARTURO83]

Bonjour,

En C2 et à tirer vers le bas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ET($A2="FT453";$B2>=2000;$B2<3000);$B2;"")

Cdlt

[zaza45]

Merci Arturo83 pour ta réponse

Dans mon exemple j'ai simplifié mais c'est une table, chaque valeur en col A aura plusieurs codes et je ne veux récupérer que les codes qui commencent par 2000 pour chaque valeur.

Je ne connais pas les valeurs.

[zaza45]

J'ai essayé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(GAUCHE(b4;4)<>"2000";GAUCHE(RECHERCHEV(Q4;$a$1:$b$29;2;FAUX);4)="2000";RECHERCHEV(a4;$a$1:$b$29;2;FAUX))

Mais il répond vrai et ne me donne pas le code

[zaza45]

Desolé, j ai fais un mauvais copier coller

J'ai posé la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(GAUCHE(RECHERCHEV(a2;$a$1:$b$29;2;FAUX);4)="2000";RECHERCHEV(a2;$a$1:$b$29;2;FAUX))

Si le code en 2000 est placé avant les autres codes, cela fonctionne bien mais si le code est placé après dans la formule donne "faux"

[MarcelG]

Bonjour,

Pour ma part, j'utiliserais une fonction VBA personnalisée basée sur la méthode Find
Ceci en adaptant l'exemple donné dans l'aide Microsoft.
Ainsi, la recherche bouclerait jusqu'à ce que la bonne valeur (2000) soit trouvée.

[zaza45]

Bonjour Marcel G

Merci pour ta réponse mais je ne connais rien en VBA.

[MarcelG]

Salut zaza,

Pas de problème.
VBA, bien utilisé, avec rigueur peut rendre bien des services.
Avec bien entendu un minimum d'investissement personnel, et l'aide du forum, on peut s'y amuser et y prendre intérêt.
En pièce jointe, une liste d'espaces qui pourraient t'être utiles.Liens connaissances VBA.docx

Pour ceux qui nous lisent, à toute fin utile, voici cette fonction.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Explicit
 
Public Function recherche_max(lecode As String) As Integer
 
Dim dernl As Integer
Dim c As Range
Dim lemax As Integer
Dim firstaddress As String
 
With Worksheets("labase")
        dernl = .Cells(.Rows.Count, 1).End(xlUp).Row
        With .Range("A2:A" & dernl)
            Set c = .Find(What:=lecode, LookIn:=xlValues, lookat:=xlWhole)
            If c Is Nothing Then
                    recherche_max = 0
            Else
                    firstaddress = c.Address
                    lemax_0 = c.Offset(0, 1).Value
                    Do
                        Set c = .FindNext(c)
                        lemax = WorksheetFunction.Max(lemax, c.Offset(0, 1).Value)
                    Loop While Not c Is Nothing And c.Address <> firstaddress
            End If
            Set c = Nothing
        End With
End With
 
recherche_max = lemax
 
End Function

[zaza45]

Serait-il possible de m'indiquer dans le code l'explication de chaque ligne afin de comprendre la signification de la macro.

Merci beaucoup

[MarcelG]

Salut,

Serait-il possible de m'indiquer dans le code l'explication de chaque ligne afin de comprendre la signification de la macro.

Mais parfaitement, ma petite zaza!
Tonton Marcel est là!

Voici, voilou.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Option Explicit
 
Public Function recherche_max(lecode As String) As Integer
 
Dim dernl As Integer
Dim c As Range
Dim lemax As Integer
Dim firstaddress As String
 
With Worksheets("labase")
        'Recherche de la dernière ligne afin de délimiter la zone de recherche
        dernl = .Cells(.Rows.Count, 1).End(xlUp).Row
        'Champ de recherche ainsi délimié
        With .Range("A2:A" & dernl)
            'Recherche de l'argument code exemple FT553
            'La fonction Find retourne un objet Range.
            'Toute variable affectée à un objet est précédée de l'instruction Set
            Set c = .Find(What:=lecode, LookIn:=xlValues, lookat:=xlWhole)
            'Cas de recherche infructueuse
            If c Is Nothing Then
                    recherche_max = 0
            'Cas de recherche aboutie
            Else
                    'Relevé de la 1ère adresses. Excel va balayer tout le champ ci-dessus
                    firstaddress = c.Address
                    'Relevé du code numérique sur la même ligne, avec un décalage de 1 colonne d'où l'argumentation (0,1)
                    lemax = c.Offset(0, 1).Value
                    'Boucle sur le champ de recherche
                    Do
                        'Recherche de la prochaine valeur dans le champ
                        Set c = .FindNext(c)
                        'La valeur à considérer est le maximum entre la précédente et celle relevée à l'adresse
                        lemax = WorksheetFunction.Max(lemax, c.Offset(0, 1).Value)
                    'Boucle tant que le code est trouvé sur le champ de recherche et que l'adresse n'est pas la même que la 1ère
                    Loop While Not c Is Nothing And c.Address <> firstaddress
            End If
            'Libération de la variable
            Set c = Nothing
        End With
End With
 
'Affectation de la valeur trouvée à la fonction
recherche_max = lemax
 
End Function

Nota:
En plaçant tes cellules en colonnes A et B, tu peux écrire en cellule C2

=recherche_max(A2)

Tu trouveras la description de toutes les méthodes et propriétés que j'ai utilisées dans les espaces documentaires que je t'ai retournés.
Les fonctions personnelles, notamment, font l'objet d'un tutoriel de Silkyroad.


A bientôt donc.

[zaza45]

Merci beaucoup pour tes explications.

Comment puis-je intégrer cette fonction dans une macro

est-ce que je dois l'insérer dans

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub Macro()
 
' Macro Macro
 
End Sub

mais cela ne fonctionne pas.

Je suppose que dans la macro je dois faire appel à cette fonction mais comment ?

De plus comment indiquer que je recherche les valeurs qui commencent par 2000.

[MarcelG]

Salut,

La fonction doit être déclarée dans un module standard.
Ensuite, tu peux intégrer cette fonction dans une procédure.

Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Public Sub essai()
Debug.Print recherche_max(Worksheets("labase").[A2])
Debug.Print recherche_max("FT53")
End Sub

De plus comment indiquer que je recherche les valeurs qui commencent par 2000.

Comme tu l'as constaté, la fonction va rechercher la valeur maximale.
Par hypothèse, ici 2000.
Si le code, en colonne B, est de 3000, la fonction doit-elle retourner 2000?

A te lire, donc.

[zaza45]

Effectivement, je ne recherche que les codes qui commencent par 2000.

Chaque valeur passe par différente phase et à chaque phase un code est associé

Phase 1 les codes commencent par 1000 ex 1000235987

Phase 2 les codes commencent par 2000 ex 2000374512

Phase 3 etc

phase 4 etc

Attention, j'ai des milliers de valeurs

[MarcelG]

Zaza,

Voici une fonction à valeur booléenne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Option Explicit
 
Public Function recherche_val(lecode As String, lavaleur As Integer) As Boolean
 
Dim dernl As Integer
Dim c As Range
Dim retval As Integer
Dim firstaddress As String
 
With Worksheets("labase")
        'Recherche de la dernière ligne afin de délimiter la zone de recherche
        dernl = .Cells(.Rows.Count, 1).End(xlUp).Row
        'Champ de recherche ainsi délimié
        With .Range("A2:A" & dernl)
            'Recherche de l'argument code exemple FT553
            'La fonction Find retourne un objet Range.
            'Toute variable affectée à un objet est précédée de l'instruction Set
            Set c = .Find(What:=lecode, LookIn:=xlValues, lookat:=xlWhole)
            'Cas de recherche infructueuse
            If Not c Is Nothing Then
                    'Relevé du code numérique sur la même ligne, avec un décalage de 1 colonne d'où l'argumentation (0,1)
                    retval = c.Offset(0, 1).Value
                    'Boucle sur le champ de recherche
                   If retval <> lavaleur Then
                            'Relevé de la 1ère adresses. Excel va balayer tout le champ ci-dessus
                            firstaddress = c.Address
                            Do
                                'Recherche de la prochaine valeur dans le champ
                                Set c = .FindNext(c)
                                'La valeur à considérer est le maximum entre la précédente et celle relevée à l'adresse
                                retval = c.Offset(0, 1).Value
                            'Boucle tant que le code est trouvé sur le champ de recherche et que l'adresse n'est pas la même que la 1ère
                            Loop While Not c Is Nothing And c.Address <> firstaddress And retval <> lavaleur
                    End If
            End If
            'Libération de la variable
            Set c = Nothing
        End With
End With
 
'Affectation de la valeur trouvée à la fonction
recherche_val = (retval = lavaleur)
 
End Function

Maintenant, il reste à connaître la finalité de ta recherche.
Quelle destination auront les valeurs trouvées?
Pour quel usage?

[zaza45]

Bonjour Marcel G

Me revoilà. Tu me demandais la finalité de ma demande.

J'ai une table que je dois synthétiser dans un TCD.

Le problème c'est que, PAR EXEMPLE, dans une col A, je peux avoir une valeur et un code en col B sur une ligne.
Sur une autre ligne pour la même valeur,j'aurais une autre code. etc.

Donc, j'ai testé manuellement à l'extraction de ma table, l'ajout de col qui reprennent certains codes, pour créer un TCD beaucoup plus succinct.

Pour être plus claire :

Ma table
col A valeur col B code col c autre code

TOTO788 15xxxxxxxx -
TOTO788 1500xxxxxx
TOTO788 2000556622
TOTO788 1000774411
TOTO788 4000996633
TOTO788 3000996633

Comme la table évolue en permanence, je ne peux pas filtrer.

Les codes numériques de la col b peuvent commencer par 11 -12 -13 -14--------19


Donc mon idée était de rajouter deux col à ma table avec pour chaque ligne TOTO788, dès que l'on trouve le code 2000xxxxx, on met se code idem pour les codes en 4000.

Ce qui me permet d'avoir un TCD
TOTO788 15xxxxxxxxxx 2000556622 4000996633.

Je ne sais pas si j'ai été assez claire dans mes explications.

J'ai testé ton code mais comme je n'ai pas de connaissances avec les macros je n'arrive pas à faire fonctionner ta fonction.

J'ai l'habitude d'utiliser des fonctions Excel dans des macros.

J'ai besoin un peu d'aide pour débuter et je dois trouver des réponses rapidement comme toujours.....

Merci beaucoup de ta patience

[MarcelG]

Bonjour Zaza,

Désolé, je ne comprends pas.
A ce stade de la discussion, pourrais-tu joindre un classeur
- avec des enregistrements en nombre limité sur une première feuille
- sur une deuxième feuille, la présentation que tu souhaites obtenir en partant de ces enregistrements

[zaza45]

Évidement en envoyant la discussion, il y a une partie de mes colonnes qui ont disparues dans le message.

En conséquence, c'était difficile de comprendre le problème.


En orange les colonnes que je dois rajoutée dans la macro après l'extraction de la table pour la création du TCD
Sur l'autre feuille le TDC que j'aimerai avoir

[MarcelG]

Salut,

Dans ces conditions, au sein du tableau croisé dynamique, pourquoi ne pas
- considérer le N° de pièce finale comme 4ème champ
- affecter un sous-total au champ Document Référence

[zaza45]

Tout d'abord, la table comporte des centaines de nom et de code départ. Il faut que ce soit le plus lisible possible en très peu de ligne

Si je prends le champs "pièces finales" pour le TCD je ne peux pas sélectionner deux valeurs dans le filtre.
Si je filtre les valeurs commençant par 4000 je ne retrouve pas la ligne à vide qui est mon solde.

[MarcelG]

Si je filtre les valeurs commençant par 4000 je ne retrouve pas la ligne à vide qui est mon solde.

Bien sûr que si.
Je viens d'effectuer un test sur le fichier que tu as joint.
Le filtre peut s'effectuer sur 4000 et (Vide). Cette dernière occurrence étant cochée par défaut.