Excel VBA - Formules Plusieurs critères

Version imprimable

24/06/2021, 17h13
hollomanne

1 pièce(s) jointe(s)

Excel VBA - Formules Plusieurs critères

Bonjour à tous,

Je me permets de poster ici pour vous avoir un petit peu d'aide.

Je m'explique : je cherche à faire apparaître une donnée (l'échelon) selon deux critère(le grade) et l'ancienneté(nombre de mois).

J'ai un tableau que je vous mets en pièce jointe avec 1 liste déroulante pour le grade et des tableaux pour les échelons selon le grade.

En gros, je veux lui dire si mon grade c'est "attaché" et que mon ancienneté est comprise entre "ça" et "ça" je veux que tu m'affiches "x échelons" comme dans l'onglet "administratif".

Pensez-vous que ce soit possible ?

Merci d'avance pour votre aide.

Bonne soirée

Bonjour,

Essayez ceci, solution en VBA, le calcul des échelons se fait lors du changement de grade.

Code:

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
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Sortie
    Application.EnableEvents = False
    If Target.Address = "$B$6" Then Echelon
Sortie:
    Application.EnableEvents = True
End Sub
 
Sub Echelon()
    Dim f1 As Worksheet, f2 As Worksheet
    Dim Lig As Long, DerLig_f2 As Long, i As Long, j As Long, Anciennete As Long, Col As Long
    Dim Grade As String
    Dim g As Range
    Application.ScreenUpdating = False
    Set f1 = Sheets("Simulation")
    Set f2 = Sheets("Administratif")
    DerLig_f2 = f2.Cells.SpecialCells(xlCellTypeLastCell).Row
    Grade = f1.Cells(6, "B")
    With f2.Range("A2:Y" & DerLig_f2)
        Set g = .Find(Grade, lookat:=xlWhole)
        If Not g Is Nothing Then
            Col = g.Column
            Lig = g.Row
            DerLig_f2 = f2.Cells(Lig, Col).End(xlDown).Row
        End If
    End With
 
    For i = 20 To 22
        Anciennete = f1.Cells(i, "H")
        If Anciennete < f2.Cells(Lig + 2, Col + 1) Then
            f1.Cells(i, "I") = f2.Cells(Lig + 1, Col)
            Exit For
        End If
        For j = Lig + 1 To DerLig_f2
            If Anciennete >= f2.Cells(j, Col + 1) And Anciennete <= f2.Cells(j, Col + 2) Then
                f1.Cells(i, "I") = f2.Cells(j, Col)
                Exit For
            End If
        Next j
    Next i
    Set g = Nothing
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Pièce jointe 600541

Cdlt

25/06/2021, 08h06
Pierre Fauconnier

Salut.

Avec une meilleure organisation de tes données, tu pourrais t'en sortir sans VBA. Ca t'éviterait d'utiliser un code que tu ne comprends sans doute pas et qui va péter à la moindre modification de donnés dans ton classeur. Construire un truc en VBA sur une conception foireuse ne pourra t'amener que des déboires ;)

Tant qu'à faire, donne aussi les valeurs attendues pour les 3 cases en orange et explique à quoi correspondent les deux colonnes de chiffres de chaque tableau de l'onglet "administratif". Ca nous aiderait à t'aider.

Citation:
Envoyé par ARTURO83
Bonjour,

Essayez ceci, solution en VBA, le calcul des échelons se fait lors du changement de grade.
Code:

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 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Sortie Application.EnableEvents = False If Target.Address = "$B$6" Then Echelon Sortie: Application.EnableEvents = True End Sub Sub Echelon() Dim f1 As Worksheet, f2 As Worksheet Dim Lig As Long, DerLig_f2 As Long, i As Long, j As Long, Anciennete As Long, Col As Long Dim Grade As String Dim g As Range Application.ScreenUpdating = False Set f1 = Sheets("Simulation") Set f2 = Sheets("Administratif") DerLig_f2 = f2.Cells.SpecialCells(xlCellTypeLastCell).Row Grade = f1.Cells(6, "B") With f2.Range("A2:Y" & DerLig_f2) Set g = .Find(Grade, lookat:=xlWhole) If Not g Is Nothing Then Col = g.Column Lig = g.Row DerLig_f2 = f2.Cells(Lig, Col).End(xlDown).Row End If End With For i = 20 To 22 Anciennete = f1.Cells(i, "H") If Anciennete < f2.Cells(Lig + 2, Col + 1) Then f1.Cells(i, "I") = f2.Cells(Lig + 1, Col) Exit For End If For j = Lig + 1 To DerLig_f2 If Anciennete >= f2.Cells(j, Col + 1) And Anciennete <= f2.Cells(j, Col + 2) Then f1.Cells(i, "I") = f2.Cells(j, Col) Exit For End If Next j Next i Set g = Nothing Set f1 = Nothing Set f2 = Nothing End Sub
Pièce jointe 600541

Cdlt
Bonjour Arthur83,

Tout d'abord, merci pour votre retour.

Alors petite remarque : l'échelon qui ressort n'est pas le bon.
En effet, par exemple pour le grade d'attaché : si on reprend 32 mois je devrais avoir comme résultat 2ème échelon (car compris entre 42 et 18 ) donc je prends le niveau inférieur car je n'ai pas encore atteints les 42 mois nécessaires pour être classé au 3ème échelon du grade.

Pouvez-vous m'indiquer où je dois modifier la macro ?

Merci d'avance,

Citation:

Envoyé par Pierre Fauconnier

Salut.

Avec une meilleure organisation de tes données, tu pourrais t'en sortir sans VBA. Ca t'éviterait d'utiliser un code que tu ne comprends sans doute pas et qui va péter à la moindre modification de donnés dans ton classeur. Construire un truc en VBA sur une conception foireuse ne pourra t'amener que des déboires ;)

Tant qu'à faire, donne aussi les valeurs attendues pour les 3 cases en orange et explique à quoi correspondent les deux colonnes de chiffres de chaque tableau de l'onglet "administratif". Ca nous aiderait à t'aider.

Bonjour Pierre,

En effet, si possible je préfère passer sans macros avec juste des formules, que je maîtrise beaucoup mieux.

Comment pensez-vous que je puisse mettre en forme au mieux ? Car c'est vrai que là je suis partie d'un tableau existant sans aucune mise en forme particulière mis à part les formules de calculs de date.

Alors pour une explication plus poussée, le résultat attendu dans la cellule I20 est 2ème échelon car mon ancienneté reprise (H20) est comprise entre 18 et 42.
Pour atteindre le 3ème échelon du grade, je dois avoir au moins 42 mois d'ancienneté, or ici j'en ai que 32 donc je dois être classée au 2ème échelon du grade.
Pour la cellule I21 c'est 2ème échelon aussi car 24 est compris entre 18 et 42. Et pour la cellule I22 le résultat est 1er échelon car compris entre 0 et 18.

Pour les 2 colonnes dans la feuille administratif, il s'agit pour la 2ème à gauche du nombre de mois à passé dans l'échelon pour pouvoir atteindre l'échelon supérieur (Soit sur le grade d'attaché si je suis nommé au 1er échelon au 01/01/2021, je devrais passer 18 mois dans cet échelon pour passer à l'échelon 2 soit au 01/07/2022.

Pour la colonne la plus à droite, il s'agit d'une simple addition du nombres de mois requis pour être classé à un échelon particulier (Soit sur le grade d'attaché si j'ai 6 ans d'ancienneté (soit 72 mois) je serais classée automatiquement au 4ème échelon (entre 90 et 66 donc on prend l'inférieur).

En espérant avoir été plus claire

Merci à tous les deux,

Bonne journée

25/06/2021, 09h47
hollomanne

1 pièce(s) jointe(s)

Citation:

Envoyé par Pierre Fauconnier

Salut.

Avec une meilleure organisation de tes données, tu pourrais t'en sortir sans VBA. Ca t'éviterait d'utiliser un code que tu ne comprends sans doute pas et qui va péter à la moindre modification de donnés dans ton classeur. Construire un truc en VBA sur une conception foireuse ne pourra t'amener que des déboires ;)

Tant qu'à faire, donne aussi les valeurs attendues pour les 3 cases en orange et explique à quoi correspondent les deux colonnes de chiffres de chaque tableau de l'onglet "administratif". Ca nous aiderait à t'aider.

Encore moi

Ne serait-il pas préférable de faire en sorte que selon le grade, le tableau d'échelon correspondant apparaisse sur la 1ère page et derrière avec une mise en forme conditionnelle ou une formule SI, je fasse apparaisse un texte comme dans l'exemple que je vous mets ci-joint ?

Merci d'avance,;)
25/06/2021, 09h48
Pierre Fauconnier

Citation:

Envoyé par hollomanne

[...]

Alors pour une explication plus poussée, le résultat attendu dans la cellule I20 est 2ème échelon car mon ancienneté reprise (H20) est comprise entre 18 et 42.
Pour atteindre le 3ème échelon du grade, je dois avoir au moins 42 mois d'ancienneté, or ici j'en ai que 32 donc je dois être classée au 2ème échelon du grade.[...]

Voici une formule (très) compliquée pour obtenir ce que tu souhaites en I20:

Code:

=INDEX(DECALER(Administratif!A1;SI(ESTERREUR(EQUIV(B6;Administratif!2:2;0));16;2);SIERREUR(EQUIV(B6;Administratif!2:2;0);EQUIV(B6;Administratif!16:16;0))-1;13);EQUIV(H20;DECALER(Administratif!A1;SI(ESTERREUR(EQUIV(B6;Administratif!2:2;0));16;2);SIERREUR(EQUIV(B6;Administratif!2:2;0);EQUIV(B6;Administratif!16:16;0))+1;13);1))

Elle est "compliquée" car elle doit rechercher la table des échelons en fonction du grade mentionné en B6 et pour cela, elle répète des portions de formule dont elle a besoin. Elle est beaucoup trop liée à la structure de tes classeurs. Pour les premiers tableaux, tu as 12 échelons alors que pour les tableaux du dessous, tu en as 13. Ici, j'ai considéré qu'il y en avait 13 partout et de rapides tests, ça a l'air de fonctionner.
25/06/2021, 10h01
hollomanne

Citation:

Envoyé par Pierre Fauconnier

Voici une formule (très) compliquée pour obtenir ce que tu souhaites en I20:

Code:

=INDEX(DECALER(Administratif!A1;SI(ESTERREUR(EQUIV(B6;Administratif!2:2;0));16;2);SIERREUR(EQUIV(B6;Administratif!2:2;0);EQUIV(B6;Administratif!16:16;0))-1;13);EQUIV(H20;DECALER(Administratif!A1;SI(ESTERREUR(EQUIV(B6;Administratif!2:2;0));16;2);SIERREUR(EQUIV(B6;Administratif!2:2;0);EQUIV(B6;Administratif!16:16;0))+1;13);1))

Elle est "compliquée" car elle doit rechercher la table des échelons en fonction du grade mentionné en B6 et pour cela, elle répète des portions de formule dont elle a besoin. Elle est beaucoup trop liée à la structure de tes classeurs. Pour les premiers tableaux, tu as 12 échelons alors que pour les tableaux du dessous, tu en as 13. Ici, j'ai considéré qu'il y en avait 13 partout et de rapides tests, ça a l'air de fonctionner.

Alors oui, ça fonctionne super !!!

Par contre en effet, c'est une formule compliquée :? Je vais aller essayer de la comprendre :roll:

Dernière petite question, pense-tu que ma 2ème solution avec une présentation différente aurait pu être plus pratique à mettre en place ? Avec juste la copie des éléments d'une plage donnée selon le critère de grade ?

En tout cas merci beaucoup
25/06/2021, 10h11
ARTURO83

Juste en réponse à hollomane.

Citation:

Alors petite remarque : l'échelon qui ressort n'est pas le bon.
En effet, par exemple pour le grade d'attaché : si on reprend 32 mois je devrais avoir comme résultat 2ème échelon (car compris entre 42 et 18 ) donc je prends le niveau inférieur car je n'ai pas encore atteints les 42 mois nécessaires pour être classé au 3ème échelon du grade.

Comme il n'y avait aucune explication concernant les valeurs sur 2 colonnes dans la feuille "Administratif", j'avais pensé que la valeur seuil mini était dans la colonne de gauche et la valeur seuil max était dans la droite d'où le résultat obtenu.

Cdlt
25/06/2021, 10h16
hollomanne

Citation:

Envoyé par ARTURO83

Juste en réponse à hollomane.

Comme il n'y avait aucune explication concernant les valeurs sur 2 colonnes dans la feuille "Administratif", j'avais pensé que la valeur seuil mini était dans la colonne de gauche et la valeur seuil max était dans la droite d'où le résultat obtenu.

Cdlt

En effet,

C'est de ma faute je n'ai pas été assez claire.

Merci quand même pour le temps passé, ça m'a permise de voir ce qui était possible.

Bonne journée à vous
25/06/2021, 10h51
Pierre Fauconnier

1 pièce(s) jointe(s)

Citation:

Envoyé par ARTURO83

[...]
Comme il n'y avait aucune explication concernant les valeurs sur 2 colonnes dans la feuille "Administratif", j'avais pensé que la valeur seuil mini était dans la colonne de gauche et la valeur seuil max était dans la droite d'où le résultat obtenu[...]

Avec plusieurs fois la même valeur dans la colonne de gauche, il me semblait assez délicat de pouvoir définir le seuil "mini" sur base de cette colonne :koi:

@Hollomanne

Tu devrais travailler avec des tableaux structurés (Voir mon tuto à ce sujet). Ca simplifie beaucoup les traitements. Tu pourrais alors avoir trois tableaux: Filières, grades, échelons. Tu pourrais aisément, sur base du tableau des grades trié par ordre croissant sur les filières puis les grades, créer tes listes en cascade (voir le gestionnaire de noms) et les cellules B4 et B6 de la feuille Simulation.

La formule en I20 serait alors assez simple: =DECALER(t_Echelons[[#En-têtes];[Echelon]];MAX(LIGNE(t_Echelons)*(t_Echelons[Grade]=B6)*(t_Echelons[Ancienneté]<=H20))-1;0;1) à valider en matricielle (SHIFT+CTRL+ENTER)

L'idée est de trouver le plus grand numéro de ligne pour l'ancienneté inférieure ou égale à celle cherchée pour le grade renseigné. DECALER permet alors de partir de l'entête de la colonne des échelons et de décaler du nombre de lignes trouvé -1. Avec cette conception, tu peux déplacer tes données, en ajouter, en supprimer, etc, sans que ta formule doive être modifiée, du moment que les grades soient triés par filière. Et sans VBA, surtout fortement couplé avec des adresses en dur dans le code!!.

La conception du fichier joint est pour moi la seule valable pour ce genre de traitements ;)
25/06/2021, 11h19
Pierre Fauconnier

1 pièce(s) jointe(s)

Code:

If Target.Address = "$B$6" Then Echelon

Plus loin dans le même code...

Code:

Grade = f1.Cells(6, "B")

Si, pour une raison x, le formulaire est réaménagé, le code pète en deux endroits et il faut modifier DEUX syntaxes différentes à deux endroits du code.

Est-ce si difficile de nommer la cellule? Par exemple, la nommer "Grade", ce serait le top, non? ;) Ca existe depuis toujours sur Excel et ça permet de simplifier le code tout en le rendant plus solide. Depuis le temps que l'on propose cette solution sur le forum plutôt que des adresses hardcodées :triste:

Pièce jointe 600579

Du coup, le code devient

Code:

If Target.Address = range("Grade").address Then Echelon

et

Code:

Grade = range("Grade").value

Si on modifie le formulaire, le code continue à fonctionner sans modifications...
25/06/2021, 18h01
hollomanne

Merci Pierre pour tout ça.
En réunion toute la journée donc j'ai pas eu le temps de tester.

Je vous tiens au courant.
Bonne soirée
29/06/2021, 08h53
hollomanne

1 pièce(s) jointe(s)

Bonjour à tous,

Encore moi :)

Tout d'abord merci Pierre c'est parfait. J'ai rentrer tous les grilles d'échelon selon les grades et ça fonctionne du tonnerre !!

Ensuite une petite dernière question dont je n'ai pas trouver la réponse malgré de nombreuses recherches tout le week-end et hier :furieux:

J'ai rajouté une nouvelle feuille qui doit me renvoyer exactement le nombre de mois exact à passer dans un échelon pour passer au suivant... J'ai essayé avec la fonction DECALER mais je suppose qu'il lui manque un point de départ ...

En concret, pour être plus clair pour le grade d'adjoint administratif territorial, je veux afficher le nombre de mois à passer dans l'échelon 9 pour atteindre l'échelon 10. Le résultat de cette formule doit apparaître dans la feuille "REVALORISATION" en cellule B12. Le résultat doit correspondre à la cellule D11=36 dans la feuille "Echelons" que j'ai mis en jaune. En gros, il doit rechercher l'échelon mis en cellule B8 de "Revalorisation" et decaler d'une ligne et d'une colonne.

Mais impossible pour moi de combiner la recherchev et le décaler... A-on une autre solution ??
01/07/2021, 10h21
Pierre Fauconnier

Salut.

=INDEX(t_Echelons[Durée];EQUIV(1;(t_Echelons[Filière]=B4)*(t_Echelons[Grade]=B6)*(t_Echelons[Echelon]=B8);0)) à valider en matricielle (SHIFT+CTRL+ENTER). Perso, je trouve 24 pour la combinaison Filière/Grade/Echelon...
01/07/2021, 12h17
hollomanne

Citation:

Envoyé par Pierre Fauconnier

Salut.

=INDEX(t_Echelons[Durée];EQUIV(1;(t_Echelons[Filière]=B4)*(t_Echelons[Grade]=B6)*(t_Echelons[Echelon]=B8);0)) à valider en matricielle (SHIFT+CTRL+ENTER). Perso, je trouve 24 pour la combinaison Filière/Grade/Echelon...

Bonjour Pierre,

Tout d'abord merci pour le retour.
Cependant le résultat attendu n'est pas 24 mois mais 36 mois. On doit passé 36 mois dans l'échelon 9 pour passer à l'échelon 10.

Il faut récupérer la donnée correspondante à la ligne du dessous soit 9e échelon (C10) = résultat attendu (D11)
01/07/2021, 12h41
Pierre Fauconnier

1 pièce(s) jointe(s)

Le principe d'un tableau structuré ou d'une table, c'est qu'une ligne correspond à une "fiche", un item. Dès lors, il convient de placer sur la même lignes les données qui concernent le même item.

Ici, le triplet filière\grade\échelon pointe vers la ligne encadrée en rouge. C'est donc sur cette ligne que l'on doit trouver la durée de travail de cet échelon particulier avant de passer au suivant. Il convient donc de décaler toute les données de la colonne Durée pour les ajuster aux lignes auxquelles elles se rattache. Ainsi, tu n'as pas à "décaler", et ton tableau est directement compréhensible.

Pièce jointe 600914

Sans cet ajustement, ton tableau est incompréhensible et inutilisable, notamment pour chaque dernier échelon des paires filière/grade, puisque tu devrais mettre la durée correspondante sur la ligne qui suit et qui ne correspond pas du tout aux valeurs de cette paire. Le lecteur de ton tableau doit pouvoir "suivre du doigt" les données sur une ligne de ton tableau. L'alternance des lignes par défaut illustre bien cette notion de "fiche" constituée d'une ligne de ton tableau.
01/07/2021, 13h37
hollomanne

Ok merci Pierre.

Je vais retravailler sur mon onglet Échelon.

Je clôture le sujet.

Merci à vous