Discussion :

[jean bapt']

Bonjour,

Je recherche une formule pour trouver la valeur finale du Fer "Fe" dans chaque tableau en fonction du batch noté en haut a gauche de chaque tableau.

Exemple:
Je veux trouver le batch 347 ==> descendre jusqu'a D1111 puis trouver la valeur Fe = 3,54
idem batch 352, ... 353, ...



Je vous remercie de vos conseils!

[Invité]

Bonjour,

Essayez cette fonction ResultatBatch. Il faut lui indiquer le numéro de batch et l'atome recherché. Cela ne marche bien sûr que si les zones des différents batch sont séparées par au moins une ligne vide. Pour le cuivre, il faut lui donner la valeur de la cellule titre "Cu mg/L".

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
Option Explicit
 
Function ResultatBatch(ByVal ValeurBatch As String, ByVal Atome As String) As Single
 
Dim DerniereColonne As Long
Dim LigneDerniereMesure As Long
 
Dim CellRecherchee As Range
Dim CelluleTitre As Range
Dim AireTitre As Range
 
    Application.Volatile
 
    ResultatBatch = 0#
 
    With ActiveSheet
 
         Set CellRecherchee = .Cells.Find(What:=ValeurBatch, LookIn:=xlValues)
         If Not CellRecherchee Is Nothing Then
 
                LigneDerniereMesure = CellRecherchee.End(xlDown).Row
                DerniereColonne = CellRecherchee.End(xlToRight).Column
 
                Set AireTitre = Range(.Cells(CellRecherchee.Row, CellRecherchee.Column), .Cells(CellRecherchee.Row, DerniereColonne))
                For Each CelluleTitre In AireTitre
                    If CelluleTitre = Atome Then ResultatBatch = .Cells(LigneDerniereMesure, CelluleTitre.Column)
                Next CelluleTitre
                Set AireTitre = Nothing
 
         End If
         Set CellRecherchee = Nothing
 
    End With
 
End Function

Cordialement.

[parmi]

Bonjour,

S'il est possible pour toi de donner un nom à tes plages, tu pourrais utiliser une formule comme

Code :

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

=INDEX(Batch_347;LIGNES(Batch_347);EQUIV("Fe";B2:F2;0))

Ici, j'ai nommé la plage Batch_347 et j'écris en dur "Fe" qui pourrait être une valeur dans une cellule, disons...

[jean bapt']

Bonjour,

Je souhaitais savoir si une fomule excel peut faire ça, type un recherchev amélioré. (donc ça doit être pour ça que je ne trouvait pas)

Pour donner plus d'info, c'est un fichier qui va synthétiser des données qui se trouvent dans des excels qui sont crées tous les mois. (donc chaque moi aller chercher dans un autre fichier)

parmi, il est difficile de créer des plages en avance pour les tableaux ne sachant pas combien il y en aura dans le mois, la taille de chacun (nombre de lignes)
Ils sont créées au fur et à mesure des besoins. (Je préfére que l'utilisateur ne puisse pas érroner le processus en cas de mauvaise définition de plage etc...)

Eric, ta solution m'intérésse.
J'ai changé le With ActiveSheet en With Windows("Document.xlsx").Activate

Pour ce qui est de la formule, plutôt que d'écrire le numéro du batch, je veux faire glisser la formule comme dans le tableau ci-dessous.
Or il a pas l'air d'aimer ça



Est-ce que c'est liée au changement de fichier?

Merci de vos aides,

JB

[Invité]

Bonjour,

Le fichier contient une nouvelle fonction ResultatBatchV2. Le principe est de collecter sur une feuille Résultats, la valeur des batchs en lui indiquant le nom de la feuille concernée.

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
 
Function ResultatBatchV2(ByVal NomFeuilleMesures As String, ByVal ValeurBatch As String, ByVal Atome As String) As Single
 
Dim DerniereColonne As Long
Dim LigneDerniereMesure As Long
 
Dim CellRecherchee As Range
Dim CelluleTitre As Range
Dim AireTitre As Range
 
Dim FeuilleMesures As Worksheet
 
    Application.Volatile
 
    ResultatBatchV2 = 0#
 
    Set FeuilleMesures = Sheets(NomFeuilleMesures)
 
    With FeuilleMesures
 
         Set CellRecherchee = .Cells.Find(What:=ValeurBatch, LookIn:=xlValues)
         If Not CellRecherchee Is Nothing Then
 
                LigneDerniereMesure = CellRecherchee.End(xlDown).Row
                DerniereColonne = CellRecherchee.End(xlToRight).Column
 
                Set AireTitre = Range(.Cells(CellRecherchee.Row, CellRecherchee.Column), .Cells(CellRecherchee.Row, DerniereColonne))
                For Each CelluleTitre In AireTitre
                    If CelluleTitre = Atome Then ResultatBatchV2 = .Cells(LigneDerniereMesure, CelluleTitre.Column)
                Next CelluleTitre
                Set AireTitre = Nothing
 
         End If
         Set CellRecherchee = Nothing
 
    End With
 
    Set FeuilleMesures = Nothing
 
End Function

Le fichier joint contient également une macro permettant d'inventorier les batchs pour une feuille donnée. Les cellules B2 et B3 sont des listes de validation.

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
Sub InventorierLesBatchs(ByVal FeuilleMesures As Worksheet, ByVal FeuilleResultats As Worksheet, ByVal Atome As String)
 
Dim DerniereLigne As Long
 
Dim CellRecherchee As Range
Dim CelluleEnCours As Range
Dim Cellule As Range
Dim PremiereCelluleTrouvee As Range
Dim AireBatchs As Range
 
Dim LigneDeTitreFeuilleResultat As Long
Dim ColFeuilleBatch As Long
Dim ColNomDuBatch As Long
Dim ColAtome As Long
Dim ColValeur As Long
Dim LigneResultatBatch As Long
 
Dim Continuer As Boolean
 
    LigneDeTitreFeuilleResultat = 10
    LigneResultatBatch = LigneDeTitreFeuilleResultat + 1
    ColFeuilleBatch = 1
    ColNomDuBatch = 2
    ColAtome = 3
    ColValeur = 4
 
    ' Effacement de la feuille résultats
    '-----------------------------------
    With FeuilleResultats
        .Range(.Cells(LigneResultatBatch, 1), .Cells(.Rows.Count, .Columns.Count)).ClearContents
    End With
 
    With FeuilleMesures
 
         Continuer = True
 
         Set CelluleEnCours = .Cells(1, 2)
         Set PremiereCelluleTrouvee = .Cells.Find(What:="batch", After:=CelluleEnCours, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
         If Not PremiereCelluleTrouvee Is Nothing Then
            Set CelluleEnCours = PremiereCelluleTrouvee
         Else
            Continuer = False
         End If
 
         Do While Continuer = True
 
            Set CellRecherchee = .Cells.Find(What:="batch", After:=CelluleEnCours, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
 
            If Not CellRecherchee Is Nothing Then
 
                   ' Le programme est interrompu si le premier batch est de nouveau trouvé.
                   If CellRecherchee = PremiereCelluleTrouvee Then
                      Continuer = False
                   End If
 
                   Set CelluleEnCours = CellRecherchee
                   With FeuilleResultats
                       .Cells(LigneResultatBatch, ColFeuilleBatch) = FeuilleMesures.Name
                       .Cells(LigneResultatBatch, ColNomDuBatch) = CellRecherchee
                       .Cells(LigneResultatBatch, ColAtome) = .Range("AtomeARecuperer")
                       LigneResultatBatch = LigneResultatBatch + 1
                   End With
 
            End If
 
            Set CellRecherchee = Nothing
 
        Loop
 
        Set CelluleEnCours = Nothing
        Set PremiereCelluleTrouvee = Nothing
 
    End With
 
    ' Mise en place de la formule ResultatBatchV2
    With FeuilleResultats
 
         DerniereLigne = .Cells(.Rows.Count, ColFeuilleBatch).End(xlUp).Row
 
         If DerniereLigne > LigneDeTitreFeuilleResultat Then
 
            Set AireBatchs = .Range(.Cells(LigneDeTitreFeuilleResultat + 1, ColValeur), .Cells(DerniereLigne, ColValeur))
            For Each Cellule In AireBatchs
                With Cellule
                    .FormulaR1C1 = "=ResultatBatchV2(RC[-3],RC[-2],RC[-1])"
                    .NumberFormat = "0.00"
                     .HorizontalAlignment = xlCenter
                End With
            Next Cellule
            Set AireBatchs = Nothing
 
         End If
 
         ' Tri des batchs
         Set AireBatchs = .Range(.Cells(LigneDeTitreFeuilleResultat, 1), .Cells(DerniereLigne, ColValeur))
 
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=Range(.Cells(LigneDeTitreFeuilleResultat + 1, ColNomDuBatch), .Cells(DerniereLigne, ColNomDuBatch)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
         With .Sort
            .SetRange AireBatchs
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
 
         Set AireBatchs = Nothing
 
 
         End With
 
    Set FeuilleMesures = Nothing
 
End Sub

Cordialement.

[papouclo]

Bonjour,
Dans le classeur ci-joint, le tableau source est en feuille 1, sur la plage B3:F35 et le tableau résultats en feuille 2 sur la plage D2:H3.
Ecrire en D3 la formule:

Code :

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

=RECHERCHEV(GAUCHE($C3;CHERCHE(" ";$C3)-1);DECALER(DECALER(Feuil1!$B$1;EQUIV(Feuil2!D$2;Feuil1!$B:$B;0););;;20;4);4;FAUX)

à recopier vers la droite sur toute la plage D3:H6.
Dans la double utilisation de la fonction DECALER, la première permet de définir une plage variable de 20 lignes de haut (mais on peut augmenter ce paramètre si le besoin s'en fait sentir)
et 5 colonnes de large; la deuxième permet de situer dans la feuille cette plage variable de façon à inclure le "mini tableau batch" recherché.
Cette formule n'est valable que parce qu'il n'y a qu'une seule occurrence de "D1111" par mini tableau batch.
Cordialement
Claude