Fonction QUERY et tableau
Bonjour,
Je souhaite récupérer une liste de Nom Prénom qui se trouve sur un autre fichier via la commande QUERY et remplir mon fichier en renseignant des informations dans la ligne concernée par la personne. Jusque là pas de soucis, sauf que lorsque j'alimente mon fichier de Nom Prénom, il peut en fonction du nom et de sa position dans la liste se créer un décalage des informations que j'ai pu renseigner. C'est un peu compliqué à expliquer.
Dans mon exemple, j'ai rempli le chiffre 2,00 (Colonne E) dans la ligne de BIGETTI Jordan, mais si ma liste de nom évolue, le chiffre 2,00 se décale.
N'hésitez pas à me poser des questions, je vais continuer à chercher aussi de mon côté.
Bonjour,
C'est normal étant donné que le QUERY est dynamique et ton tableau à côté est fixe.
Tu es donc obligé d'ajouter une ligne manuellement à chaque ajout d'un nouveau nom pour compenser le décalage...
Bonjour,
Merci pour ta réponse, c'est effectivement ce que je craignais ...
Je te propose une solution alternative :
https://docs.google.com/spreadsheets/d/1ctP5mIRY3iDtQ7tQ0eAVfvcukJMHdgshWnXR9Oqwe2c/edit?usp=sharing
En K20 :
=query(Personnel!A2:D101;"select A, B, C, D where not A matches "&ArrayFormula("'"&textjoin(".*|";1;".*"&query(A20:A;"select * where A is not null"))&".*'"))Le tableau de l'onglet "Copie de HSUP2023" devient un tableau fixe (copie de "Personnel") et en K20 tu as la liste du personnel ajouté depuis la dernière mise à jour de ton tableau fixe .
Tu n'as plus qu'à insérer une nouvelle ligne à la position souhaitée et faire un copier/coller (collage spécial "valeurs uniquement) du membre du personnel à ajouter...
Cordialement,
Fil.
Merci @Filoche pour cette proposition.
Si je comprends bien, apparait en K20 dans ton exemple les noms issus du Query qui ne se trouvent pas dans le tableau 'fixe' ?
C'est exactement ça.
Après tu n'as plus qu'à faire un copier/coller de la ligne du nouveau nom et à l'insérer à sa bonne position de classement alphabétique.
Une fois que le nom est dans le tableau fixe, il se retire automatiquement de la colonne K !
Je pense qu'il doit être possible de faire un script pour automatiser tout ça...
Top @Filoche
Je l'ai du coup un peu adapté afin de m'éviter de rapatrier la liste du personnel dans un onglet.
=query(IMPORTRANGE("1Rijh2h_Y5DXeWH3cc5oa6s2i3FfPVpu3XKN1ICK10U4";"A2:H");"SELECT Col1,Col6,Col7,Col8 where not Col1 matches "&ArrayFormula("'"&textjoin(".*|";1;".*"&query(A20:A156;"select * where A is not null"))&".*'"))Par contre j'aimerai éventuellement pouvoir sélectionner uniquement les intérimaires, cette info se trouve dans la colonne 7, que faut-il que j'ajoute ?
La syntaxe est :
=query(IMPORTRANGE("1r7P5oGWQVbsHrPRkCkL76WwSKhyhgCWcqBvPoT5Xw8o";"A2:H");"SELECT Col1,Col6,Col7,Col8 where Col7 = 'INTERIM' ")Par contre, je ne suis pas convaincu que ton adaptation de la formule soit un franc succès...
Sans prendre en compte le critère INTERIM, l'adaptation de TA formule (je te remercie beaucoup) fonctionne
=query(IMPORTRANGE("1Rijh2h_Y5DXeWH3cc5oa6s2i3FfPVpu3XKN1ICK10U4";"A2:H");"SELECT Col1,Col6,Col7,Col8 where Col7 = 'INTERIM' and not Col1 matches "&ArrayFormula("'"&textjoin(".*|";1;".*"&query(A20:A;"select * where A is not null"))&".*'"))Merci, ça fonctionne parfaitement
Super !
Si tu peux clôturer le sujet, stp?
Merci !
Bonjour Filoche,
Comme j'ai un petit bug d'affichage, peux-tu m'expliquer un peu la formule ?
Merci.
=query(IMPORTRANGE("1Rijh2h_Y5DXeWH3cc5oa6s2i3FfPVpu3XKN1ICK10U4";"A2:H");"SELECT Col1,Col6,Col7,Col8 where Col7 = 'INTERIM' and not Col1 matches "&ArrayFormula("'"&textjoin(".*|";1;".*"&query(A20:A;"select * where A is not null"))&".*'"))
Avec QUERY, on fait un IMPORTRANGE des colonnes 1, 6, 7 et 8 uniquement des données de la colonne 7, qui contiennent 'INTERIM' et de la Col1 qui ne font pas partie de la liste des noms en A20:A...Merci pour ces explications.
Du coup je ne comprends pas pourquoi j'ai le nom AUDINET qui reste dans la liste à ajouter alors qu'il se trouve bien en ligne 3 (Onglet Personnel)
https://docs.google.com/spreadsheets/d/1HFNLwH0Jq6kNgyg9Rx4DaIZ_Y6r-EljXGZsxyo1GFJ0/edit?usp=sharing
Le nom disparait bien de la liste à ajouter si AUDINET ne se trouve pas en haut de la liste dans l'onglet MAJ
Bonjour,
En fait, c'est normal. J'avais oublié de mettre ";0" à la fin de mon QUERY, donc la première valeur était considérée comme l'en-tête de la colonne !
Voilà corrigée et adaptée à ta feuille...
=SIERREUR(query(query('Copie de MAJ'!$A$2:$G;"select * where Col1 is not null");"select Col1, Col2, Col3, Col4, Col5 where not Col7 matches "&ArrayFormula("'"&textjoin(".*|";1;".*"&query($A$2:$A$120;"select * where A is not null"))&".*'");0);"Aucune personne à ajouter !")Bonjour,
J'ai fais un petit script avec un déclencheur à l'ouverture pour copier directement les nouvelles entrées dans ton tableau :
https://docs.google.com/spreadsheets/d/1-_8wW6-aK0MUrYAKdmVt6LdB4QCANbBsHA7okcyOGbk/edit?usp=sharing
function copiercolleplage() {
let spreadsheet = SpreadsheetApp.getActive();
const source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Essai').getRange('K4:N');
const cible = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Essai');
const lastCible = cible.getLastRow()+1;
const cibleRange = cible.getRange(lastCible,1);
SpreadsheetApp.flush();
source.copyTo(cibleRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES
, false);
spreadsheet.getRange('A5:J').activate().sort({column: 1, ascending: true});
var sheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var e=sheet.getRange('A'+sheet.getMaxRows()).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
for (k=4;k<=e;k++) {
if(sheet.getRange('A'+k).getValue()=='') {
sheet.deleteRow(k);
k=4;e--
if(k==e){break};
SpreadsheetApp.flush();
}
}
};Cordialement,
Fil.
Top, je vais essayer de le mettre en place
Bonjour,
J'ouvre à nouveau le sujet car j'ai une bizarrerie que je n'arrive pas à résoudre !!!
J'ai simplifié dans le fichier joint mais je fais un simple QUERY pour importer une liste que je met à jour sauf que le QUERY m'importe en cellule A1 les 3 premières ligne de mon fichier. Je ne comprends pas car je sélectionne bien la liste à importer A1:J124 et je sélectionne bien les données qui se trouve en Col1 (Colonne A).
=QUERY(IMPORTRANGE("1Rijh2h_Y5DXeWH3cc5oa6s2i3FfPVpu3XKN1ICK10U4";"Personnel!A1:J124");"SELECT Col1 ORDER BY Col1 ASC")
Merci pour votre aide car je sèche surtout que tout fonctionnait très bien la semaine dernière ...