XL 2019 inscrire des données (n-1) afin de les récupérer lorsque l'affichage fluctue en fonction de l'année

JoBar57

XLDnaute Nouveau
Bonjour,

Compliqué de mettre un titre explicite !

J'ai réussi (avec un peu d'aide) à concocter un tableau qui m'affiche les Vacances "V", les Fériés "F" et les Récup Fériés "RF ....
Il sert à calculer les "V" "F" "RF ... posés et le nombre restant à poser.
Mais, petit problème pour les "V", ils sont tributaires de l'ancienneté et de l'âge de l'employé !
J'ai réussi dans "Tableau5" de l'onglet "Entrées à automatiser les calcul des "V" en fonction de ces deux critères, mais pour calculer ce qui restait à poser dans l'année (n-1) et en tenir compte pour les calculs dans l'année (n), je dois pouvoir stocker cette information (dans la cellule X 44 de la feuille "Entrées")

Il me reste donc à afficher dans la cellule X44 (dans la feuille « Entrées ») le nombre de V de l’année (n-1) en fonction du nom dans la cellule V2 de la feuille "Tableau" et de l'année dans la cellule D2 de la feuille "tableau, ce que je n'ai pas encore réussit à faire (mon cerveau fait une surchauffe !)
Ceci pour ne pas me retrouver avec un nombre de "V" à (n-1) erroné l'année où ce nombre change (passage de 49 à 50 ans notamment).

Voilà ! J'espère avoir été suffisamment claire dans mes explications.

Une bonne âme aurait-elle une solution ou une piste à m'offrir ?


D'avance merci.
 

Fichiers joints

Dernière édition:

Fred0o

XLDnaute Accro
Bonjour JoBar57

Voici une formule qui me semble fonctionner sur ton fichier exemple mais a tester sur ton ficheir reel :

=VLOOKUP(Tableau!$V$2,Tableau5[[Personnel]:[Nombre jours de vacances]],4)-SUMPRODUCT((Tableau1[Nom]=Tableau!V2)*(Tableau1[Symbole]="V")*(Tableau1[Année]=Tableau!D2-1)*(Tableau1[Nb de jours]*1))
 

JoBar57

XLDnaute Nouveau
Bonjour Fred0o,

Apparemment ça ne fonctionne pas chez moi. J'ai essayé avec un "copier - coller" et quand je valide la cellule réagit comme si j'avais collé du texte (malgré le "=" devant) et quand je retape une partie de la fonction alors seulement ça me dit qu'il y a une erreur !
J'ai essayé de remplacer les fonctions en anglais par le français (recherchev et sommeprod), mais pas plus de succès ! ça me dit juste qu'il y a une erreur.

J'ai aussi une question : à quoi correspond le "4'" dans la fonction VLOOKUP, en principe c'est "0" ou "1" (vrai - faux) ?

Pourrais-tu me renvoyer le fichier exemple avec ta formule qui fonctionne ?
Peut-être que ça m'aidera à comprendre ce qui ne va pas dans le mien, car en principe ils sont identiques

Je te remet le fichier avec les essais que j'ai fait.

Merci d'avoir pris le temps de te pencher sur mon problème
 

Fichiers joints

JoBar57

XLDnaute Nouveau
Bonjour Fred0o,

Je reviens sur le sujet pour apporter quelques précisions sur les essais que j'ai fait.
En remplacant =VLOOKUP(Tableau!$V$2,Tableau5[[Personnel]:[Nombre jours de vacances]],4) par =RECHERCHEV(Tableau!$V$2;Tableau5;4;0) seul dans une cellule, ça me renvoi bien le nombre de jours de "V" dans l'année (n)
Avec =VLOOKUP(Tableau!$V$2,Tableau5[[Personnel]:[Nombre jours de vacances]],4), ça ne fonctionne pas => #NOM?

Mais lorsque je teste =SOMMEPROD((Tableau1[Nom]=Tableau!$V$2)*(Tableau1[Symbole]="V")*(Tableau1[Année]=Tableau!$D$2-1)*(Tableau1[Nb de jours]*1))
ou bien =SUMPRODUCT((Tableau1[Nom]=Tableau!V2)*(Tableau1[Symbole]="V")*(Tableau1[Année]=Tableau!D2-1)*(Tableau1[Nb de jours]*1))
ça me renvoi dans le premier cas #N/A et dans le second cas #NOM?

= problème avec SUMPRODUCT ou SOMMEPROD, mais je ne vois pas où.
 
Dernière édition:

Phil69970

XLDnaute Nouveau
Bonjour Jobar57, bonjour Fred0o

Pour la 1ere partie de la question (RechercheV) :
En vba :
Sheets("Entrées").Range("X44") = "=VLOOKUP(Tableau!R[-42]C[-2],Tableau5[[#All],[Personnel]:[Nombre jours de vacances]],4,FALSE)"

En formule :
=RECHERCHEV(Tableau!V2;Tableau5[[#Tout];[Personnel]:[Nombre jours de vacances]];4;FAUX)

Cordialement
 

Fred0o

XLDnaute Accro
Bonjour JoBar57, Phil69970

Ma formule initiale fonctionne mais j'ai oublie de préciser qu'il te faut corriger la formule 'Symbole' dans le 'Tableau1'. C'est la ligne 96 qui pose problème. Donc, il faut mettre cette formule en F3 :
=SIERREUR(RECHERCHEV([@Type];Tableau4;2;0);"")

La formule en X44 doit être :
=RECHERCHEV(Tableau!$V$2;Tableau5[[Personnel]:[Nombre jours de vacances]];4)-SOMMEPROD((Tableau1[Nom]=Tableau!V2)*(Tableau1[Symbole]="V")*(Tableau1[Année]=Tableau!D2-1)*(Tableau1[Nb de jours]*1))

Le 4 du RECHERCHEV correspond a la valeur de la 4eme colonne (Nombre de jours de vacances).

Je n'ai pas teste les formules de Phil.
 

JoBar57

XLDnaute Nouveau
Bonsoir Fred0o, Phil69970
Ma formule initiale fonctionne mais j'ai oublie de préciser qu'il te faut corriger la formule 'Symbole' dans le 'Tableau1'. C'est la ligne 96 qui pose problème. Donc, il faut mettre cette formule en F3 :
=SIERREUR(RECHERCHEV([@Type];Tableau4;2;0);"")

Après avoir corrigé F3, ta formule fonctionne, mais le résultat obtenu n'est pas bon.

Par exemple avec D2 = 2020 et V2 = Christianne ta formule me renvoi un résultat de 10 alors que Nombre de jours de vancances = 30 (même nombre en n et n-1) et Nombre de V posés en (n-1) (dans cellule X36) = 2
Résultat => 30-2=28 au lieu de 10.
La formule calcule en fait les "V de 2019 (année civile 2020 -1) et non pas année glissante (8/2019 à 7/2020).
 

Fichiers joints

Fred0o

XLDnaute Accro
Bonjour JoBar57


La formule calcule en fait les "V de 2019 (année civile 2020 -1) et non pas année glissante (8/2019 à 7/2020).
Ah, ah !!!
Grande nouveauté ! Je découvre par ce post, qu'il faut calculer par 12 mois glissants et pas année n-1. Cela change tout et en effet, mes formule ne calculent pas cela car ce n'est pas ce qui avait été demandé...

Je me re-penche sur le sujet quand j'ai le temps.
 

JoBar57

XLDnaute Nouveau
Bonjour Fred0o,

Ah, ah !!!
Grande nouveauté ! Je découvre par ce post, qu'il faut calculer par 12 mois glissants et pas année n-1. Cela change tout et en effet, mes formule ne calculent pas cela car ce n'est pas ce qui avait été demandé...
En effet en relisant mon premier post je me rend compte que finalement je n'ai pas été aussi clair que prévu .... désolé !
 

Fred0o

XLDnaute Accro
Re-Bonjour
Je ne comprends pas. Pour 2019, si la personne a pose au total 20 jours alors qu'elle avait un quota de 30, il lui reste 10 jours a prendre et pas 2. Je ne comprends pas comment ces 2 jours sont calcules.

Edit :
Tu peux toujours essaye cette formule en X44 :
=VLOOKUP(Tableau!$V$2,Tableau5[[Personnel]:[Nombre jours de vacances]],4)-X36
 
Dernière édition:

JoBar57

XLDnaute Nouveau
Bonjour Fred0o,

Encore merci pour le temps que tu passes à mon problème.

Dans le cas cité ci-dessus, l'année (n-1) va de 8/2019 à 7/2020.
Comme j'ai groupé les saisies pour l'exemple, tu trouves tous les jours posés dans les lignes 146 à 151 de la feuille "Entrées"
Si tu regardes bien les lignes 146 à 150 ce sont des "V-1" posés dans l'année (n) c'est à dire des reliquats de "V" de l'année (n-2) alors que la ligne 151 comporte bien des "V" posés dans l'année n(-1), et il y en a bien 2.

Concernant ta formule, elle se réfère aux chiffres de la colonne 4 (Nombres de jours de vacances) mais ce sont les chiffres de (n) qui peuvent justement être différents de (n-1) en fonction de l'ancienneté ou de l'âge.

Cela dit, à force de cogiter pendant ma journée de boulot ( mon cerveau a failli fondre ! :) ) j'ai trouvé ce soir une solution bien moins élégante qu'une formule unique mais qui semble fonctionner j'ai rajouté des colonnes dans Tableau5 afin de calculer les jours de "V" dispo pour (n-1) exactement de la même manière que j'ai effectué le calcul pour (n).
J'aurais bien aimé une solution plus classe que celle là, mais si c'est trop casse bonbon, celle-là m'ira aussi. C'est toujours intéressant de trouver des solutions moins"simplistes", disons plutôt, plus synthétiques, plus "pro".

je te joins le fichier avec la modif.
Dans AA51 il y a le résultat avec ce que tu proposais (année civile) et dans AA46 il y a le résultat avec l'aide de mon double calcul dans Tableau5.
Dans AA53 ta formule ci-dessus qui ne veut pas fonctionner.
 

Fichiers joints

Dernière édition:

JoBar57

XLDnaute Nouveau
Pour compléter mon post précédent, et après quelques tests je vois que ma solution n'est que partielle car elle ne tient pas compte du mois mais seulement de l'année (civile). Ce qui pose problème avec les années glissantes
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas