Cas FORTEMPS et son système d'indemnités de déplacement

Pour le brave FORTEMPS payé par tranches de jeudis dans le mois, je dois calculer le nombre de jours prestés avant ce dernier jeudi pour calculer le nombre d'indemnités de déplacement à payer.
Ce qui donnera aussi le nombre de déplacements à reporter au mois suivant.

Par exemple, en mars, nous avons 6 T avant le dernier jeudi du mois et ensuite 2 T à reporter.

Comment automatiser le truc ? J'avais envie de le creuser et je vous en fait part  

La recette à suivre ("algorithme" pour faire bien).

L'idée est :
1) de récupérer l'adresse (=Z27) du dernier jeudi du mois et
2) ensuite de faire la somme des "t" sur la ligne (33) qui se trouvent entre le début du mois et cette adresse butoir (Z27).
3) Enfin de calculer le nombre total de "t" sur le mois et de retrancher ceux avant le dernier mois pour obtenir ceux à reporter.

Donc il faut que ma chimie détermine qu'il y a 6 "t" avant le dernier jeudi; qu'il y en a 8 au total et donc 8-6=2 à reporter.



D'abord, il faut récupérer l'adresse du dernier "Je".
Je vais donc utiliser le fonction ADRESSE() qui prend 2 paramètres, un n° de ligne, et un n° de colonne.
Par exemple si je tapais =ADRESSE(25; 16) sur ma feuille, il irait chercher la valeur de la ligne 25, colonne P, soit "dont".
Pour mars, je vais donc faire mes recherches sur la ligne 27.

Reste à trouver le 2° paramètre, soit la colonne.

Mais là, y a un hic: je ne cherche pas sur une colonne, mais sur plusieurs colonnes, en fait toutes les colonnes qui forment le mois (ligne 27, de B à AF).
Il faut donc trouver un truc pour chercher la bonne colonne, celle qui a le dernier "Je".
Sur cette plage, je dois trouver le texte "Je" qui se trouve dans la colonne la plus à droite, or dans excel, les colonnes ont une valeur: leur valeur alphabétique. La colonne C > la colonne A, la colonne E < la colonne F et ainsi de suite. Et donc la colonne la plus à droite est celle qui a la plus grande valeur.

Dans ma ligne 27, il faut que je repère la colonne qui a la plus grande valeur et qui contient un "Je".
Quand on veut rechercher la plus grande valeur entre des choses, on utilise la fonction MAX().

C'est cette fonction MAX() qui va m'aider à déterminer la 2° paramètre dont j'ai besoin pour ADRESSE()

Donc j'en suis là pour le moment: =ADRESSE(27, MAX('quelquechose'))

Pour trouver le n° d'une colonne en excel, il y a la fonction ... COLONNE() qui prend en paramètre l'adresse d'une cellule et qui revoie le n° de la colonne, comme ceci: COLONNE(E5) renvoie 5, car la colonne E est la 5°.

=ADRESSE(27, MAX(COLONNE('quelquechose')))

Pour trouver le 'quelquechose' en paramètre de COLONNE() dans MAX(), je vais utiliser un paramètre ne pourra pas être une cellule, mais une plage de cellules (dans mon cas, une ligne qui va de B27 à AF27).
Mon paramètre aura une syntaxe un peu spéciale puisque je dois lui dire de me renvoyer les colonnes des jeudis "Je".

Voici la syntaxe: COLONNE(B27:AF27)*(B27:AF27="Je"), que je traduis par "dans la plage B27 à AF27 de la ligne 27, donne-moi les valeurs des colonnes qui contiennent "Je".
Cette fonction va renvoyer 4 "noms" de colonne, celles où il y a des "Je", soit E, L, S et Z. Les jeudis du mois de mars.

Et le MAX() de la fonction précédente ne va en retenir qu'une: celle qui a la plus grande valeur, soit Z.
Et cette valeur rescapée va remplir la fonction ADRESSE() en ADRESSE(27, Z), soit la cellule Z27 qui est bien l'adresse de mon dernier jeudi du mois.

Donc j'en étais à = ADRESSE(27, MAX('quelquechose')) et j'ai trouvé mon 'quelquechose' qui est COLONNE(B27:AF27)*(B27:AF27="Je")

Et donc: =ADRESSE(27;MAX(COLONNE(B27:AF27)*(B27:AF27="Je")))



Et la formule me renvoie bien $Z$27  :-)

Bon ... j'ai l'adresse de mon dernier "Je". Content, mais j'en fais quoi ?    

Je dois trouver le nombre de "t" entre le début du mois (B27) et cette date du dernier "J" = Z27



Je dois utiliser un NB.SI(), avec les paramètres NB.SI(B33:"dernierJe";"t") (B33 car la ligne des codes est la 33).

La cellule qui a déterminé le $Z$27 est la AO33.

J'ai donc écrit la formule NB.SI(B33:"=AO33"; "t")  ... AO33 étant censée renvoyer "$Z$27" mais ça n'a pas marché car le paramètre "=AO33" ne fonctionne pas.
Pour recopier ce qui se trouve dans la cellule AO33 (à savoir $Z$27), je dois utiliser la fonction INDIRECT(AO33) qui renvoie "$Z$27".
La fonction INDIRECT() renvoie le contenu d'une cellule, ce qui est pratique quand on doit renvoyer une adresse de cellule.

Et donc: =NB.SI(B33:INDIRECT(AO33);"t") qui va trouver le nombre de "t" entre B33 et Z27, soit 6. (La ligne 33 est celle des codes où on cherche les "t").



Il ne me reste plus qu'à compter le nombre total de "t" sur le mois, soit 8, avec 6 déplacements à payer ce mois et 8-6=2 à reporter au mois suivant.

Bingo.

... sauf si le mois suivant, l'indemnité de déplacement change ) car j'aurais alors le report à un taux et les nouveaux à un nouveau taux. Et il faudra (un peu) chambarder la formule.