Récupérer une valeur de la même colonne qu'une autre donnée recherchée
Bonjour,
J'ai réalisé un emploi du temps en colonnes et je souhaite rechercher toutes les fois où le cours est inscrit et récupérer la date associée.
Voici comment j'ai créé ça :
Par ex, je veux, sur un autre onglet, rechercher "Maths" et que ça me ressorte toutes les dates en ligne 1 associées à chaque occurence de Maths qu'il trouve. L'objectif est d'avoir une liste de dates pour chaque matière (j'ai déjà listé toutes les matières avec UNIQUE() ).
Merci !
Bonjour,
En feuille 2 ou il y a UNIQUE() en A2
=TEXTJOIN(" ";true;INDEX('Feuille 1'!A1:E14;1;EQUIV(A2;'Feuille 1'!A1:A14;0));INDEX('Feuille 1'!A1:E14;1;EQUIV(A2;'Feuille 1'!B1:B14;0));INDEX('Feuille 1'!A1:E14;1;EQUIV(A2;'Feuille 1'!C1:C14;0));INDEX('Feuille 1'!A1:E14;1;EQUIV(A2;'Feuille 1'!D1:D14;0));INDEX('Feuille 1'!A1:E14;1;EQUIV(A2;'Feuille 1'!E1:E14;0)))Bonne Journée
Bonjour Mathieu,
pour explorer toutes les pistes et avoir plus de flexibilité quant au nombre de données (nb de lignes et nb de colonnes), il serait bien d'avoir un lien vers un fichier test. Ce qui permet aussi de coller à tes paramètres régionaux (FR, CH, CA ?)
en A2 une matière
=textjoin(char(10);;query({transpose(offset((planning!$A$1);;;;counta(planning!$1:$1)))\transpose(query(
arrayformula(if({offset((planning!$A:$A);;;;counta(planning!$1:$1))}=A2;1;0));
"select "&"sum(Col"&arrayformula(textjoin("),sum(Col";true;sequence(1;counta(planning!$1:$1);1;1)))&")";
1))};"select Col1 where Col3>0"))à tirer vers le bas
https://docs.google.com/spreadsheets/d/1ZNPrQXpznPKH6pNYaQmf2jHdkMow99D348CwzcOFnXY/copy
les formules sont sur fond jaune
Super, merci beaucoup !
Ça fonctionne effectivement exactement comme je souhaitais. En revanche j'essaye depuis tout à l'heure d'ajuster la formule pour coller parfaitement avec mon doc original. J'essaye notamment de décaler tout de 2 lignes car en réalité, j'ai une ligne dédié au Semestre et une dédié au numéro de semaine.
Pour faire plus simple, j'ai fait un doc réduit qui correspond exactement à mon doc original : https://docs.google.com/spreadsheets/d/1E9kMbHsuDKtW52luNE8-LYdpLuYNrHb8R8ZTIFYSN8o/edit?usp=sharing
Ce que je cherche à faire c'est :
- Ajuster la formule pour que la première matière ne soit pas en A2 mais B4, comme vous pouvez voir sur l'onglet "Emploi du temps"
- J'ai tout décalé d'une colonne, car la première colonne sert à indiquer l'heure. Je n'arrive pas à ajuster la formule.
- Dans mon document original, plusieurs occurences d'une même matière se suivent. En fait, je fonctionne par bloc d'1h, mais tous les cours sont en 2, 3, ou 4h. Fonctionner par bloc d'1h me permet de compter les heures automatiquement (ce qui est difficile en fonctionnant par bloc fusionné car ça peut être 2, 3 ou 4h). Dans ce cas, j'ai compris que la formule faisait une sorte de nettoyage pour éviter qu'une matière citée plusieurs fois dans la même journée soit écrasée avec les autres occurences de la même journée. Ce que je cherche à faire là c'est, dans la colonne de résultat, rajouter aussi l'heure associée en colonne A, pour avoir quelque chose comme ça :
Maths : Lundi 4 Janvier - 14h00
l'heure étant une reprise du contenu de la colonne A de la ligne correspondante, exactement comme pour la date.
Comment je devrais m'y prendre du coup ?
Merci beaucoup !
C'est simple, dans mon fichier, tu ajoutes une colonne en A, 2 lignes en haut, tu changes le nom de l'onglet et tu recopies la formule !
=textjoin(char(10);;query({transpose(offset(('Emploi du temps'!$B$3);;;;counta('Emploi du temps'!$3:$3)))\transpose(query(
arrayformula(if({offset(('Emploi du temps'!$B:$B);;;;counta('Emploi du temps'!$3:$3))}=A2;1;0));
"select "&"sum(Col"&arrayformula(textjoin("),sum(Col";true;sequence(1;counta('Emploi du temps'!$3:$3);1;1)))&")";
1))};"select Col1 where Col3>0"))j'ai compris que la formule faisait une sorte de nettoyage pour éviter qu'une matière citée plusieurs fois dans la même journée soit écrasée avec les autres occurences de la même journée. Ce que je cherche à faire là c'est, dans la colonne de résultat, rajouter aussi l'heure associée en colonne A, pour avoir quelque chose comme ça :
oui, mais ce n'est pas ce que tu demandais ...
ça me ressorte toutes les dates en ligne 1 associées à chaque occurence de Maths qu'il trouve. L'objectif est d'avoir une liste de dates pour chaque matière (j'ai déjà listé toutes les matières avec UNIQUE() ).
je me prends une copie et je réfléchis ... cela peut être plus simple !
oui, mais ce n'est pas ce que tu demandais ...Oui, en fait en voyant comme ça fonctionnait, je me suis rendu compte que si nous pouvions ajouter les heures, ça m'éviter de les saisir manuellement également. À la base ce que je cherchais à faire c'était bien ce que tu avais réussi à faire.
je me prends une copie et je réfléchis ... cela peut être plus simple !Top merci,
C'est simple, dans mon fichier, tu ajoutes une colonne en A, 2 lignes en haut, tu changes le nom de l'onglet et tu recopies la formule !En fait c'est ce que j'ai fait, en pensant qu'en déplacant les lignes/colonnes, cela mettrait à jour automatiquement la formule. Ça l'a fait pour le nom de l'onglet mais pas pour le décalage. Mais top merci !
ah oui petite question supplémentaire, mais mon emploi du temps réél s'étale sur une quarantaine de colonnes. La liste de résultat semble s'arrêter vers la colonne CZ.
À quel endroit dans la formule je peux ajuster la colonne max pour la recherche ?
un peu plus simple
=TEXTJOIN(char(10);true;arrayformula(text(query({flatten(arrayformula('Emploi du temps'!$B$4:$G))\flatten(arrayformula('Emploi du temps'!$B$3:$G$3+'Emploi du temps'!$A$4:$A))};"select Col2 where Col1='"&A2&"' ");"ddd dd MMM yyyy à hh:mm")))dans cette formule, il n'y a pas le calcul auto du nbre de colonnes, il faut remplacer G par la dernière colonne
je pourrais l'inclure, mais c'est déjà bien complexe !
Ok parfait, j'ai ajusté la colonne max. Ça fonctionne, merci beaucoup !
Il y a juste un petit problème (regarde sur le gsheet linké plus haut). Certains cours qui ont plusieurs occurrences dans une même journée sont listés mais dans le désordre, et du coup je n'ai pas de continuité dans la liste des dates qui ressortent.
Ici par exemple, on voit que les cours du 18 aout ressortent dans le désordre :
Dans l'idéal, avoir la date + l'heure du début et l'heure de fin (=heure de début + nombre de fois que le cours est présent sur une seule colonne) serait top, mais je me dis que ça devient complexe.
un p'tit coup de sort()
=TEXTJOIN(char(10);true;arrayformula(text(sort(query({flatten(arrayformula('Emploi du temps'!$B$3:$G$3+'Emploi du temps'!$A$4:$A))\flatten(arrayformula('Emploi du temps'!$B$4:$G))};"select Col1 where Col2='"&A2&"' "));"ddd dd MMM yyyy à hh:mm")))en inversant les colonnes de query
C'est ça ! Merci beaucoup ! Le fait de jouer avec une heure qui n'est pas une chaine de caractère c'est super car je vais pouvoir jouer avec la data.
J'aimerai essayer de pousser un peu le truc une dernière fois. Si j'arrive à récupérer le nombre de fois qu'un cours a lieu par jour, je pourrais ajouter +2 ou +4 à la première heure de départ, et ainsi avoir quelque chose du style :
Lundi 4 mars 2021 - 14h30 - 18h30 (4h)
Cependant, quelle formule me permettrai de compter cela et à quel endroit l'ajouter dans la formule de base ?
Bonjour,
Si j'arrive à récupérer le nombre de fois qu'un cours a lieu par jour, je pourrais ajouter +2 ou +4 à la première heure de départ, et ainsi avoir quelque chose du style :
Lundi 4 mars 2021 - 14h30 - 18h30 (4h)
mais comment savoir si les heures sont continues dans la même journée ? ou alors il faudrait pour chaque cours récupérer heure de début et heure de fin
S'il est plutôt facile de compter le nombre d'occurrences dans la même journée, il n'est pas simple de savoir si les plages sont contigües, Et je ne suis pas sûr du tout de parvenir à mettre cette information dans la formule, je ne sais même pas si c'est possible avec des formules aussi puissantes soient-elles dans GSheets.
Par contre, de plus en plus, je pense qu'une simple mise à plat de l'emploi du temps (date | heure | matière) serait plus souple pour s'adapter ensuite à différents usages.
={"Date"\"Heure"\"Matière";query(sort({flatten(arrayformula('Emploi du temps'!$B$3:$G$3*('Emploi du temps'!$A$4:$A)^0))\
flatten(arrayformula(('Emploi du temps'!$B$3:$G$3)^0*'Emploi du temps'!$A$4:$A))\
flatten(arrayformula('Emploi du temps'!$B$4:$G))});"select * where Col3 is not null")}ensuite avec un TCD ou un query ou un sommeprod il est plus facile d'en extraire les informations.
Alors top, ça marche. Cependant pour m'aider à aller plus loin, j'aimerai rajouter une colonne dans le résultat.
En gros pour l'instant ça me donne ça :
et je voudrais ajouter une colonne d'heure de fin (qui est égal à la colonne heure + (1/24)) :
Mais j'ai beau triturer la formule, je n'arrive pas.
Autre point, une fois que j'ai ça, mais que je cherche à cumuler tous les mêmes cours d'une même journée, par quoi je peux commencer ?
Dans ma tête, la formule que tu m'as donné me permet de récupérer tous les cours sous cette forme, résultats que je garde sur une feuille de calcul à part. Sur une autre feuille, je pourrais avoir une formule qui regarde tout ça et qui compte le nombre de doublons dans une même journée. Les cours se suivent forcément, donc si je prends l'exemple de Maths ici, le cours commence à 9h et se termine à 11h.
Merci beaucoup.
En fait si, j'arrive à quelque chose finalement, mais j'ai un résultat étrange (chaque jour il me compte cours vide, alors que je n'ai pas changé les lignes/colonnes) :
https://docs.google.com/spreadsheets/d/1E9kMbHsuDKtW52luNE8-LYdpLuYNrHb8R8ZTIFYSN8o/edit?usp=sharing
Ma formule en A1 :
={"Date"\"Heure début"\"Heure fin"\"Matière";query(sort({flatten(arrayformula('Emploi du temps'!$B$3:$G$3*('Emploi du temps'!$A$4:$A)^0))\
flatten(arrayformula(('Emploi du temps'!$B$3:$G$3)^0*'Emploi du temps'!$A$4:$A))\flatten(arrayformula(('Emploi du temps'!$B$3:$G$3)^0*'Emploi du temps'!$A$4:$A))+(1/24)\
flatten(arrayformula('Emploi du temps'!$B$4:$G))});"select * where Col3 is not null")}Bon j'ai réussi finalement, en changeant col3 par col4 en fin de formule (aucune idée de pourquoi par contre). Reste mon problème de fond : comment additionner les mêmes cours d'une journée pour avoir des cours de 2/3/4h qui tiennent en 1 ligne
Bon j'ai réussi finalement, en changeant col3 par col4 en fin de formule (aucune idée de pourquoi par contre).
Mais si, parce que tu as ajouté une colonne, du coup c'est la colonne 4 qu'il faut filtrer en éliminant les valeurs nulles.
J'avais moi-même anticipé sur cette question (et j'en suis même à Col5 !!)
mais pour le moment je réfléchis à comment détecter les "suites" des cours. J'avais bien pensé faire un TCD avec valeurs mini sur début et maxi sur fin par jour, mais comment faire si un cours s'intercale ?? voir page suivante