Récupération d'une valeur dans un tableau

Bonjour,

Je cherche une formule qui me permettrait de retrouver une valeur dans un tableau en fonction de certains paramètres.

Mon tableau est constitué d'une colonne avec le nom des professeurs et d'autres colonnes avec des taux horaires et des dates de changement de ces taux horaires

J'aimerais pouvoir créer une formule qui me permette d'extraire de ce tableau le taux horaire d'un prof pour un mois donné et l'année en cours. Par exemple, pour le prof2, ce serait 27€ pour le mois de mars 2021. S'il n'y a pas eu de taux horaire modifié au mois de mars, il faudrait récupérer le dernier en date (35€ dans le cas du prof1).
Merci d'avance pour votre aide

Bonjour,

Il y a peut-être (?) plus simple.

Ma méthode est la suivante :

• il faut d'abord "linéariser" le tableau ("unpivot")

=query({
arrayformula(vlookup((roundup(flatten(sequence(columns(tab);rows(tab);1;1)/columns(tab))));{flatten(sequence(columns(tab);rows(tab);1;1))\flatten(transpose(tab))};2;0))\
arrayformula(vlookup(mod(flatten(sequence(rows(tab);columns(tab);0;1));columns(tab))+1;{flatten(sequence(rows(tab);columns(tab);1;1))\flatten(tab)};2;0))\
flatten(tab)
};"select * where Col1 is not null and Col2 is not null and Col3 is not null";0)

• et ensuite faire une recherche avec filter ou query

=query({unpivot!A:C};"select Col3 where Col1='"&I1&"' and Col2>=DATE'"&TEXT(I2;"yyyy-MM-dd")&"' order by Col2 limit 1";0)

https://docs.google.com/spreadsheets/d/10kqHJn8910pC8eBCrPyM5vU1ZooSlTvIdOdyS4vunu4/copy

j'ai donné un nom au tableau de données que tu as fourni

C'est ma faute, j'ai fait une erreur de signe

=query({unpivot!A:C};"select Col3 where Col1='"&I1&"' and Col2<=DATE'"&TEXT(I2;"yyyy-MM-dd")&"' order by Col2 desc limit 1";0)

je vais regarder pour nettement simplifier cela sans passer par unpivot/linéariser ... mais j'ai été confronté aux dates qui se mettaient en texte !

oui, la fonction unpivot est assez complexe, je suis parti d'une fonction déjà développée que je me suis approprié et arrangé à ma sauce pour bien comprendre

Une solution plus directe est de combiner les 2 sans passer par unpivot, mais pas beaucoup plus simple !

=query({
arrayformula(vlookup((roundup(flatten(sequence(columns(tab);rows(tab);1;1)/columns(tab))));{flatten(sequence(columns(tab);rows(tab);1;1))\flatten(transpose(tab))};2;0))\
arrayformula(vlookup(mod(flatten(sequence(rows(tab);columns(tab);0;1));columns(tab))+1;{flatten(sequence(rows(tab);columns(tab);1;1))\flatten(tab)};2;0))\
flatten(tab)
};"select Col3 where Col1='"&B1&"' and Col2<="&(B2*1)&" and Col3 is not null order by Col2 desc limit 1 ";0)

le problème que l'on rencontre est lié aux différents format numérique et date qui se mélangent !

HELP = si Gilbert trouve plus simple, cela m'intéresse ...

Cette solution est plus compréhensible après avoir transposé la matrice

H1 est le prof, H2 la date

=query(sort(filter(A2:E;A2:A<=H2);1;false);"select Col" & MATCH(H1;1:1;0) & " where Col" & MATCH(H1;1:1;0) & " is not null limit 1 ")

Je ne connais pas la fonction MATCH

Bonjour,

"HELP = si Gilbert trouve plus simple, cela m'intéresse ..."

Plus simple c'est de remplir toutes les cellules du tableau (copier/coller) pas trop difficile

De plus le tableau rempli ne provoque pas l'erreur dans la requête du QUERY avec uniquement un "="

la solution de Mike fonctionne , je n'ai pas trouvé mieux .......

Baylou Patrick a écrit :

Je ne connais pas la fonction MATCH

c'est la version anglaise de equiv

va dans le fichier, et je pense que ce sera traduit pour toi

ok. Merci. Je vais regarder.
Une autre question. J'ai l'impression que j'ai un petit bug sur le fichier

150€ est le résultat d'une petite formule qui multiplie 6 par 25

J'ai exactement la même opération juste en dessous avec les mêmes formats pour 19,5 et 25 mais j'ai un message d'erreur. Le truc vraiment bizarre, c'est que ça marchait au départ et tout d'un coup ça s'est mis à ne plus fonctionner. Est-ce que vous sauriez à quoi ça pourrait être dû?

ce qui est incompréhensible, c'est que tu as 25.00 et que tu obtiennes 150,00 alors que tout le reste du fichier est bien en annotation virgule pour les décimales ... peux-tu faire un extrait et nous le donner ?

Bonjour,

voilà l'utilisation correcte des formats à utiliser pour avoir de bons résultats

une autre possibilité mais pour uniquement les heures ;-)))

J'ai pu résoudre le problème en supprimant le format de la cellule référente.

J'aimerais mieux comprendre ta formule

=query({ arrayformula(RECHERCHEV((ARRONDI.SUP(flatten(sequence(COLONNES(tab);LIGNES(tab);1;1)/COLONNES(tab))));{flatten(sequence(COLONNES(tab);LIGNES(tab);1;1))\flatten(transpose(tab))};2;0))\ arrayformula(RECHERCHEV(mod(flatten(sequence(LIGNES(tab);COLONNES(tab);0;1));COLONNES(tab))+1;{flatten(sequence(LIGNES(tab);COLONNES(tab);1;1))\flatten(tab)};2;0))\ flatten(tab)

};"select * where Col1 is not null and Col2 is not null and Col3 is not null";0)

J'ai modifié le tableau d'origine et cette formule ne me donne pas le résultat voulu. Il faudrait que je comprenne mieux pour arriver à l'adapter.

Je connais "query", "arrayformula", "recherchev", "arrondi.sup" mais je ne connais pas "flatten" et "sequence". A quoi servent ces deux fonctions?

Est-ce que tu peux me détailler un peu la formule pour que je comprenne?

Je crois que j'ai compris pour flatten. Ca met les valeurs les unes au dessous des autres.

Voici comment se présente mon tableau à présent

C'est en effet l'une des formules les plus complexes que j'aie.

Et c'est justement l'une des meilleures façon d'apprendre que de décortiquer une formule (c'est ce que j'ai fait du reste) ... il suffit de décomposer en repérant notamment les séparateurs comme \

Je donnerai un topo complet demain sur cette formule.

Il y a 4 formules importantes

les lignes

=arrayformula(ROUNDUP(flatten(sequence(COLUMNS(tab);ROWS(tab);1;1)/COLUMNS(tab))))

les colonnes

=arrayformula(mod(flatten(sequence(ROWS(tab);COLUMNS(tab);0;1));COLUMNS(tab))+1)

la position des cellules

=flatten(sequence(COLUMNS(tab);ROWS(tab);1;1))

et les données

=flatten((tab))

et tout est mixé dans une belle recherchev suivie d'un query

@Mike

« apprendre à décortiquer une formule » Ou méthode de l’oignon ;)

A savoir les plages nommées ne sont pas installables sur IPad et IPhone dommage encore une chose à demander à GOOGLE ;(

Super ta recherche +1

Gilbert_RGI a écrit :

« apprendre à décortiquer une formule » Ou méthode de l’oignon ;)

Gilbert_RGI a écrit :

A savoir les plages nommées ne sont pas installables sur IPad et IPhone dommage encore une chose à demander à GOOGLE ;(

merci de l'info, je ne savais pas

mais pour le cas cité, je remplace ensuite tab par les adresses des cellules ... mais bon, je ne l'utilise guère, c'était plus un exercice de pelage de l'oignon pour moi !!

oui bien entendu" tab " remplace la plage nommée aucun problème...

mais si certains passants qui passent par ici avec ce matériel nous dirons "ça ne marche pas"