Formule complexe

Bonjour à tous,

J'essaie en vain de paramétrer une formule a priori complexe (trop pour moi en tout cas) dans un fichier Google Sheets.

L'idée en quelques mots :

Sur le premier onglet :

  1. Verticalement, une liste de personnes
  2. Horizontalement, une liste de postes de travail avec pour chacun d'entre eux une version
  3. A la croisée des 2 :
    • un niveau de formation sur le poste compris entre 1 et 4 : niveau i = niveau 1 / L = niveau 2 / U = niveau 3 / O = niveau
    • la dernière version sur laquelle la personne a été formée

Sur le second onglet :

  1. La liste des postes qu'on retrouve en onglet 1avec :
    1. une case avec le nom du poste,
    2. en dessous, une case "validation",
    3. en dessous, le nom d'une personne

La formule doit se trouver dans la case validation. Le résultat est déterminé selon le niveau de la personne et la dernière version à laquelle elle a été formée :

  • Si la personne n'est pas formée (= la case est vide dans l'onglet 1 à la croisée de son nom et du poste), afficher "Non formée"
  • Si la personne n'est pas formée à la dernière version du poste mais qu'elle est au niveau I, L, U ou O : afficher "Non à jour"
  • Si la personne est formée à la dernière version du poste et elle est au niveau I : afficher "en formation"
  • Si la personne est formée à la dernière version du poste et elle est au niveau L, U ou O : afficher "OK"

Je joins à ce post le fichier concerné en formation xlsx.

Merci par avance pour votre aide,

Bonjour Alex ... c'est du excel ou du GSheets ? si c'est le second cas, as-tu un embryon de fichier ?

Bonjour,

Voici un lien vers un fichier.

ok, je me prends une copie pour y réfléchir ce soir ...

Regarde dans le fichier, j'aime bien les formules complexes, mais elle risque de ne pas être maintenable, alors j'ai décomposé.

=IF(B8="";"Non Formée";IF(B7<>B9;"Non à jour";IF(B8="I";"En formation";"ok")))

avec un calcul de B5 à B9

Bonjour, salut Michel !

Un essai :

=SI(INDEX(Postes!$B$4:$I$8;EQUIV(B$3;Postes!$A$4:$A$8;0);EQUIV(B$1;Postes!$B$1:$I$1;0))=0;"Non formée";SI(INDEX(Postes!$B$4:$I$8;EQUIV(B$3;Postes!$A$4:$A$8;0);EQUIV(B$1;Postes!$B$1:$I$1;0)+1)<INDEX(Postes!$B$2:$I$2;1;EQUIV(B$1;Postes!$B$1:$I$1;0));"Non à jour";SI(INDEX(Postes!$B$4:$I$8;EQUIV(B$3;Postes!$A$4:$A$8;0);EQUIV(B$1;Postes!$B$1:$I$1;0))="I";"En formation";"OK")))

Il y a des redites mais je n'ai pas trouvé mieux sans passer par plusieurs formules distinctes. Si on décompose un peu la formule :

INDEX(Postes!$B$4:$I$8;EQUIV(B$3;Postes!$A$4:$A$8;0);EQUIV(B$1;Postes!$B$1:$I$1;0)) donne le niveau de la personne pour le poste

INDEX(Postes!$B$4:$I$8;EQUIV(B$3;Postes!$A$4:$A$8;0);EQUIV(B$1;Postes!$B$1:$I$1;0)+1) donne la version de la formation de la personne pour le poste (idem formule précédente, avec juste un décalage d'une colonne).

INDEX(Postes!$B$2:$I$2;1;EQUIV(B$1;Postes!$B$1:$I$1;0)) donne la dernière version du poste

Concernant les fonction utilisées : INDEX + EQUIV est une alternative plus souple aux fonctions RECHERCHE. Voir ici pour plus de détails.

Bonjour Pedro,

Formidable, ça fonctionne ! Un grand merci à toi, j'y ai passé du temps, à me perdre dans les recherchev / rechercheh / index-equiv sans jamais réussir !

Pour ta solution, Mikhail, le problème est que j'ai parfois 60 postes, pour autant de personnes, que ce fichier est utilisé par pas mal de monde et qu'il est plus "propre" sans formule décomposée, qui a par contre clairement l'avantage d'être plus simple et potentiellement mise à jour par tous en cas de besoin.

Bonjour Pedro,

Formidable, ça fonctionne ! Un grand merci à toi, j'y ai passé du temps, à me perdre dans les recherchev / rechercheh / index-equiv sans jamais réussir !

Merci de ton retour. Par contre je rejoins Michel (ton pseudo ne passe pas à l'envoi du message !), la formule est complexe et si tu en as la possibilité, tu peux restructurer tes données vers un format plus conventionnel. Ceci permettra d'utiliser des formules beaucoup plus simples et plus faciles à maintenir dans le temps.

Qu'entends-tu par "restructurer les données" ?

Pendant que j'y suis, je cherchais également à optimiser l'utilisation du fichier (sans succès également). Il comprend dans les faits plusieurs onglets, un par équipe (j'ai mis à jour le google sheets pour donner une idée).

Mon idée d'amélioration est la suivante : les postes sont strictement identiques pour toutes les équipes. L'en-tête (que j'ai coloré en bleu) est ainsi le même pour chaque onglet. Bien sûr, dans le fichier utilisé, pour les onglets equipe 2 et suivants, j'ai mis des formules simples (= Eqp1!E1 par exemple) qui permettent de tenir à jour tous les onglets en n'en modifiant qu'un seul. Là où j'aimerais améliorer le fonctionnement, c'est quand on ajoute un nouveau poste : comment faire en sorte que le poste ajouté dans l'onglet 1 soit automatiquement créé dans les onglets suivants (ceci implique l'ajout de 2 colonnes pour 1 poste ajouté et c'est là que ça se complique) ? L'importrange ne fonctionne pas car il décale l'en-tête mais pas le reste.

J'avais essayé grâce à l'enregistrement macro, avec des références absolues et relatives, mais ça n'a rien donné, il me crée les colonnes n'importe où ou presque et mes connaissances sont très limitées.

Peut-être faut-il que j'ouvre un nouveau sujet ?

Merci par avance,

Qu'entends-tu par "restructurer les données" ?

Je n'ai pas la prétention d'en faire une référence en la matière, mais c'est à ça que je pense : structurer des données avec Excel.

Hello Pedro, oui je vois que tu sais traduire le cyrillique ! Mon pseudo veut dire acier-fils tout comme ma version anglaise et la version originale néerlandaise !

  • et tu as raison sur la structure des données : c'est plus facile quand on a une pseudo-base de données (en fait une liste à la Prévert sur les couples hommes-postes, et sur les versions des postes).
  • mais c'est aussi un problème d'ergonomie, car remplir une liste n'est pas toujours sympa (pas de visibilité d'ensemble) et on conçoit qu'il est encore préférable de renseigner une grille comme celle présentée.

En excel, j'aurais construit la base de données au fur et a mesure et de façon masquée, juste avec une macro événementielle qui mettrait à jour cette base de données quelle que soit l'équipe.

GSheets pourrait faire de même mais de façon plus simple, sans macro, juste avec une compilation des différents éléments des différentes équipes et une fonction de type query/importrange/...

Je vais y réfléchir, quitte à revoir quand même un poil les différents onglets car c'est vrai, les cellules fusionnées, c'est bien pour la présentation, mais c'est la galère ensuite !

Merci pour vos conseils. Je vous rejoins en effet à propos, notamment sur les postes qui sont sur 2 colonnes et qui compliquent les mises à jour.

Il y a quand même un certain nombre d'éléments qui compliquent la tâche, d'autant que je vous ai partagé une version simplifiée du fichier (par exemple : il y a 5 autres informations renseignées concernant les postes). L'autre difficulté, c'est que le fichier est utilisé par mal de monde (dont certains n'ont pas vraiment l'habitude de l'outil informatique) et qu'il est ainsi difficile d'en modifier complètement la forme.

Souvent, tu as une base de données source (ou plusieurs à croiser ensemble) et un onglet de type tableau de bord qui te permet d'interagir avec ta/tes bases de données pour n'en ressortir que les éléments d'intérêt (filtre par date, par poste, calculs, moyennes, graphiques, etc). C'est sur cet onglet que tu peux te permettre des "fantaisies" sur le visuel et la structure, et qui permet à des "néophytes" de pouvoir travailler sur ton fichier (sans nécessairement en comprendre le fonctionnement global).

J'ai trouvé ceci qui est intéressant et que je vais exploiter d'abord "théoriquement" avec un cas d'école : comment passer d'une matrice à une table ...

https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets/

C'est souvent de cette façon que je construit ma boîte à outils.

Rechercher des sujets similaires à "formule complexe"