Fonction recherche

Bonjour ,

J'aimerai pouvoir faire une synthèse de l'ensemble des dates pour chaque profs que j'ai distribué dans le temps.

Ainsi, j'ai placé des étudiants et des enseignants pour des épreuves dans l'onglet "planning Profs". Mais pour faciliter la lecture des collègues, j'aimerais faire une feuille synthèse pour chaque enseignant dans l'onglet "TEST" pour l'instant avec les infos mentionnées dans les colonnes.

J'essaie de faire une fonction recherche V mais je ne suis pas sûr que ce soit le plus adapté...

En espérant ne pas avoir été trop confus.

Merci

Bonjour,

pas facile de travailler avec des cellules fusionnées

rien que pour une "tranche" de E à H (test1)

=arrayformula(query(split(flatten('Planning Profs'!A8:A&"~"&'Planning Profs'!E1&"~"&'Planning Profs'!E4&"~"&'Planning Profs'!E6&"~"&'Planning Profs'!E8:E&"~"&'Planning Profs'!G8:G&"~"&'Planning Profs'!H8:H);"~");"select Col5,Col1,Col2,Col6,Col4,Col3 where Col7 = '"&$A$2&"' ";0))

ou (test2)

=sort(arrayformula(query(split(flatten('Planning Profs'!A8:A&"~"&'Planning Profs'!E1&"~"&'Planning Profs'!E4&"~"&'Planning Profs'!E6&"~"&'Planning Profs'!E8:E&"~"&'Planning Profs'!G8:G&"~"&'Planning Profs'!H8:H);"~");"select Col7,Col5,Col1,Col2,Col6,Col4,Col3 where Col7 is not null";0)))

il faudra maintenant empiler le reste des colonnes ... et il y a 39 dates !!

pour 2 semaines (test2)

=sort({
arrayformula(query(split(flatten('Planning Profs'!A8:A&"~"&'Planning Profs'!E1&"~"&'Planning Profs'!E4&"~"&'Planning Profs'!E6&"~"&'Planning Profs'!E8:E&"~"&'Planning Profs'!G8:G&"~"&'Planning Profs'!H8:H);"~");"select Col7,Col5,Col1,Col2,Col6,Col4,Col3 where Col7 is not null";0));
arrayformula(query(split(flatten('Planning Profs'!A8:A&"~"&'Planning Profs'!I1&"~"&'Planning Profs'!I4&"~"&'Planning Profs'!I6&"~"&'Planning Profs'!I8:I&"~"&'Planning Profs'!K8:K&"~"&'Planning Profs'!L8:L);"~");"select Col7,Col5,Col1,Col2,Col6,Col4,Col3 where Col7 is not null";0))
})

en bleu le critère, en jaune la formule à compléter

j'ai limité le nombre de lignes sinon cela ramait déjà trop !

Eu égard à la très grande complexité des formules, mieux vaut passer par un script

Voir menu spécifique en haut à droite et onglet BdD

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Créer listing par prof ', 'creerBdD')
    .addToUi();
}
function creerBdD() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const planning = ss.getSheetByName('Planning Profs')
  const bdd = ss.getSheetByName('BdD')
  const data = planning.getDataRange().getValues()
  let result = []
  for (var i = 7; i < data.length; i++) {
    for (var j = 4; j < data[0].length; j += 4) {
      if (data[i][j] != '') {
        result.push([
          data[i][j + 3],
          data[i][0], data[i][1], data[i][2], data[i][3],
          data[0][j], data[1][j], data[2][j], data[3][j], data[4][j], data[5][j],
          data[i][j], data[i][j + 1], data[i][j + 2]
        ])
      }
    }
  }
  result = result.sort(function (a, b) {
    return ('' + a[0]).localeCompare(b[0]);
  })
  bdd.clearContents()
  bdd.getRange(2, 1, result.length, result[0].length).setValues(result)
}

Bonjour

Déjà un grand merci pour ces formules qui me dépassent ! ;-)

La dernière solution (script) semble effectivement davantage répondre à ma demande.

Cependant, j'ai quelques questions :

- Si je change des infos dans le planning prof (qui est ma base), sera-t-il pris en compte dans l'onglet "BdD" ?

- Dans la feuille BdD, Je peux trier mes colonnes sans risque ? (j'ai essayé, a priori, ca passe)

- Dans la feuille BdD, Je peux déplacer mes colonnes sans risque ? (idem)

- Si j'ajoute une donnée avec un nouveau prof par exemple dans une cellule vide, sera-ce pris en compte ?

Etant donné que je ne souhaitais ma diffuser le nom de mes étudiants et pensais pouvoir reproduire la formule dans mon tableau "officiel" , serait-il possible que vous puissiez faire le même script sur le tableau ci-dessous ? c'est le même avec des onglets en plus... Il y a toujours l'onglet "planning prof" et un onglet BdD avec les infos souhaitées.. ou me dire comme on fait mais je crains de ne pas réussir...

Je vous fais confiance pour :

- ne pas toucher aux cellules du "planning profs " qui m'a demandé un weekend de travail ! 😓

- ne pas diffuser les noms des étudiants 😜

Merci encore !

lorenzofreestyle a écrit :

- Si je change des infos dans le planning prof (qui est ma base), sera-t-il pris en compte dans l'onglet "BdD" ?

il faudra actualiser en relançant le script

mais je pense que c'est plus rapide (et en tous cas moins pénalisant) que d'utiliser les formules à rallonge (on aurait atteint le max de caractères adimissibles je pense) qui aurait fortement ralenti l'introduction de nouvelles données

lorenzofreestyle a écrit :

- Dans la feuille BdD, Je peux trier mes colonnes sans risque ? (j'ai essayé, a priori, ca passe)

oui

lorenzofreestyle a écrit :

- Dans la feuille BdD, Je peux déplacer mes colonnes sans risque ? (idem)

c'est à dire ? c'est le script qui fige en quelque sorte les données dans chaque colonne

lorenzofreestyle a écrit :

- Si j'ajoute une donnée avec un nouveau prof par exemple dans une cellule vide, sera-ce pris en compte ?

absolument

je regarde le lien

lorenzofreestyle a écrit :

Je vous fais confiance pour :

- ne pas toucher aux cellules du "planning profs " qui m'a demandé un weekend de travail ! 😓

- ne pas diffuser les noms des étudiants 😜

pas de soucis pour moi, mais je vais demander à un modérateur d'effacer le lien s'il y a des infos non anonymes, c'est préférable

à vérifier

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Créer listing par prof ', 'creerBdD')
    .addToUi();
}
function creerBdD() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const planning = ss.getSheetByName('Planning Profs')
  const bdd = ss.getSheetByName('BdD')
  const data = planning.getDataRange().getValues()
  let result = [[' Profs', 'Épreuve', 'Nom Étudiant', 'Prénom Étudiant', 'Date', 'Heure', 'Salle', 'SPE', 'APSA', 'Dossier 01']]
  for (var i = 7; i < data.length; i++) {
    for (var j = 5; j < data[0].length; j += 4) {
      if (data[i][j] != '') {
        /*
        result.push([
          data[i][0], data[i][1], data[i][2], data[i][3], data[i][4],
          data[0][j], data[1][j], data[2][j], data[3][j], data[4][j], data[5][j],
          data[i][j], data[i][j + 1], data[i][j + 2],data[i][j + 3],
        ])
        */
        result.push([data[i][j + 3], data[i][j], data[i][0], data[i][1], data[0][j], data[i][j + 2], data[i][j + 1], data[i][4], data[3][j], data[5][j]])
      }
    }
  }
  result = result.sort(function (a, b) {
    return ('' + a[0]).localeCompare(b[0]);
  })
  bdd.clearContents()
  bdd.getRange(1, 1, result.length, result[0].length).setValues(result)
}

C'est exactement ça que je souhaitais ! C'est top !

En revanche, quand je corrige des données dans planning profs, et que je lance le script dans la barre du haut MENU (entouré des flèches vers le bas), cela n'est pas automatique. Je dois accepter des avertissements, me connecter... Et surtout ça n'actualise pas. :-(

Dois-je installer un module ?

il faut accéder à "projet sans titre" en bas, cliquer et poursuivre jusqu'à donner la validation

si cela t'ennuie, je peux faire autrement !

Si tu sais faire plus vite ou plus facile, je suis preneur !

donc plus de menu, tu cliques sur A1 pour réactualiser

la formule est en A2

Enorme !!

Je suis admiratif de tes compétences et de ta réactivité.

Un grand merci !

Ça marche parfaitement

A bientôt