tri/réorganisation automatique de valeurs

coanda

XLDnaute Nouveau
Bonsoir,

Dans le cadre de mon travail, je récupère un grand nombre de résultats d'analyses chimiques pour plein de paramètres chimiques différents. Chaque paramètre, analysé pour tous les points de prélèvements (XXX01, XXX02,...) correspond à une ligne dans le tableau excel joint et est analysé sur un support dédié. La laboratoire me fournit les résultats de chaque paramètre dans une même ligne mais sur plein de colonnes différentes (une colonne par support et par point de prélèvement). Donc pour un même point de prélèvement (intitulé XXX01 dans mon fichier - pour un même projet j'ai plein de points différents XXX01, XXX02,...) j'obtiens plusieurs colonnes avec plein de cases vides dans lesquelles il n'y a qu'une seule valeur.

Au final je me retrouve avec une très grande matrice pas du tout lisible, dont je ne vous ai donné qu'une infime partie pour l'exemple

Le but de ma demande est de parvenir à trouver une formule pour réorganiser toutes ces valeurs afin d'avoir tout les résultats d'un même point XXX01 sur une seule et même colonne (voir onglet réorg). J'ai fait un exemple pour que ca soit plus compréhensible. A noter que pour chaque point de prélèvement XXX0X et pour chaque paramètre en général on fait 2 mesures simultanées une "mesure" et un "contrôle". il faut que je puisse toujours voir l'ensemble des résultats après la réorganisation.

Le but ultime derrière est d'utiliser ces résultats pour comparer ces résultats à des seuils prédéfinis. Le conditionnal formatting est prêt mais il faut que toutes les valeurs d'un même point XXX01 soit dans une seule et même colonne et que je puisse étendre les formules vers le bas et vers le côté pour gagner du temps. A noter enfin, que si je change de laboratoire je veux pouvoir encore utiliser mon fichier même si le labo change l'ordre des lignes associées à chaque paramètre. Il faudrait donc que le fichier recherche dans un onglet le nom du paramètre et associe la valeur située sur sa ligne et dans la colonne du point XXX0X correspondant.

Je me tire les cheveux depuis plusieurs heures à base de index + equiv mais je ne parviens pas à trouver une formule qui fonctionne. Quelqu'un aurait la gentillesse de m'aider?

Pour info, voilà la formule à laquelle je suis arrivé à l'heure actuelle. Ca vous permettra peut etre de mieux saisir ce que je veux faire:
=INDEX('données brutes labo'!$C$8:$Z$300;EQUIV($B5;'données brutes labo'!$A$8:$A$300;0);EQUIV(CONCATENER("*";STXT(C$3;1;5);"*";"Mesure";"*");'données brutes labo'!$C$6:$Z$6;0))

Merci d'avance pour votre aide.

Fabien
 

Pièces jointes

  • test reorg resultats.xlsx
    11.1 KB · Affichages: 23
  • test reorg resultats.xlsx
    11.1 KB · Affichages: 31
  • test reorg resultats.xlsx
    11.1 KB · Affichages: 31

CISCO

XLDnaute Barbatruc
Re : tri/réorganisation automatique de valeurs

Bonjour

Le début du travail en pièce jointe. Ne connaissant pas l'étendue de ton fichier réel, je ne peux pas te proposer une solution réellement polyvalente. A toi de chercher ou de nous proposer une fichier exemple un peu plus complet (par exemple pour afficher automatiquement les intitulés XXX01, mesure, contrôle...).

@plus
 

Pièces jointes

  • test reorg resultats.xlsx
    11.1 KB · Affichages: 18
  • test reorg resultats.xlsx
    11.1 KB · Affichages: 29
  • test reorg resultats.xlsx
    11.1 KB · Affichages: 30

coanda

XLDnaute Nouveau
Re : tri/réorganisation automatique de valeurs

Cisco,

Je viens de regarder ta formule. Je dois avouer que j'ai du mal à comprendre comment elle fonctionne. Et j'ai donc du mal à l'adapter à mon cas personnel.

Si je décortique la formule que tu propose voilà ce que je comprends:
- SUBSTITUE('données brutes labo'!$C$5:$CS$5;"chiffresA";"");5) : il remplace tous les "chiffresA" par rien dans tout la ligne C5 CS5.
- SI((GAUCHE(SUBSTITUE('données brutes labo'!$C$5:$CS$5;"chiffresA";"");5)=I$3)*('données brutes labo'!$C8:$CS8<>"");COLONNE($C:$CS));1)). A partir de la, je ne suis pas sur de piger, notamment le "*" entre les 2 parenthèses. Ca veut dire "ET"?
Ce que je pige:
après avoir supprimé "chiffreA", excel regarde si les 5 premiers caractère de gauche du résultat sont égal à I3 (XXX01).
Ensuite le colonne($C;$cS) je me pige pas non plus. Ca donnera toujours 3? pourquoi tu utilises cette fonction ici?

Même si je ne pige pas tout pour l'instant, je ne pense pas que ca va m'aider au final car je ne suis pas sûr que ca fasse vraiment ce que je veux faire au final.

Pour essayer d'expliquer en français ce que je cherche à faire

Dans l'onglet "données brutes labo", on devra faire uniquement un copier coller du fichier excel fourni par le laboratoire.
Donc, en fonction du labo et des analyses faites, les paramètres recherché (CS2, NH3, Hg ou les dizaines d'autres non présentés ici), ne seront pas forcément dans le même ordre de ligne, ni tous présents.
De même, les numéros "chiffreA" ne seront pas les mêmes d'un rapport d'analyse à un autre. En gros à chaque projet, tu auras un "chiffreA" différent. Donc on ne peut pas demander à excel de chercher "chiffreA" puisque ca ne serait valable que pour un projet et pas pour les centaines que j'ai à faire. Le but est de faire un fichier qui marchera pour les centaines de projet. Je perds pas mal de temps maintenant pour en gagner plein par la suite :)

Donc, au final, ce qu'il me faudrait, c'est que, pour afficher la bonne valeur dans la case I5 de l'onglet "réorg", le fichier excel cherche :
1. la chaine de caractère présente dans la case I3 de l'onglet réorg (c'est a dire "XXX01") dans toute la ligne n°5 de l'onglet "données brutes labo" (dans la réalité, il y aura des numéros XXX01, XXX02, XXX03,..., YYY01, YYY02, ..., ZZZ01.... Et tu peux avoir n'importe quel texte avant et après la chaine de caractère recherchée)
2. une fois qu'il a déterminé les colonnes où XXX01 se retrouve, il cherche dans la colonne des paramètres (colonne A dans l'onglet "données brutes labo"), la chaine de caractère présente dans la case G5 de l'onglet "réorg" ("Disulfure de carbone (CS2)").
3. Il choisit la case où il y a quelque chose d'écrit (un nombre ou du texte, car on peut aussi avoir des choses du genre "<5" ou "-/-", et il faut que ca apparaisse dans l'onglet "réorg"). Pour chaque échantillon "XXX01 Mesure" et pour chaque paramètre (CS2 et.), il n'y aura qu'une case avec une valeur dans l'onglet "données brutes labo". Idem pour "XXX01 Contrôle". En gros tu n'auras jamais pour un même paramètre par ex : CS2) une valeur dans 2 colonnes "XXX01 Mesure" différentes


Pour essayer de t'aider à mieux comprendre ce que je veux faire, tu trouveras ci-joint un exemple de ce que j'ai fait avec d'autres types de prélèvement. Dans ce cas c'était "simple" car tout les résultats pour chaque XXX01 était dans une seule et même colonne dans l'onglet "données brute labo" alors que la c'est dans de multiples colonnes et que je veux regroupé tout ce qui concerne un point XXX01 dans une seule et même colonne dans l'onglet "réorg".

Merci d'avance pour le temps que tu vas consacrer à la lecture de ce post et à me donner de l'aide :)
 

Pièces jointes

  • exemple_détaillé_excel_download.xlsx
    47.6 KB · Affichages: 16

CISCO

XLDnaute Barbatruc
Re : tri/réorganisation automatique de valeurs

Bonjour

Cisco,

Je viens de regarder ta formule......
- SUBSTITUE('données brutes labo'!$C$5:$CS$5;"chiffresA";"");5) : il remplace tous les "chiffresA" par rien dans tout la ligne C5 CS5.
- SI((GAUCHE(SUBSTITUE('données brutes labo'!$C$5:$CS$5;"chiffresA";"");5)=I$3)*('données brutes labo'!$C8:$CS8<>"");COLONNE($C:$CS));1)). A partir de la, je ne suis pas sur de piger, notamment le "*" entre les 2 parenthèses. Ca veut dire "ET"?
Ce que je pige:
après avoir supprimé "chiffreA", excel regarde si les 5 premiers caractère de gauche du résultat sont égal à I3 (XXX01).
Ensuite le colonne($C;$cS) je me pige pas non plus. Ca donnera toujours 3? pourquoi tu utilises cette fonction ici?

Pour comprendre ce que font les formules, il faut prendre l'habitude de passer par l'onglet "Formule", puis par "Evaluation de la formule" (la petite loupe avec fx dedans). Une fenêtre s'ouvre alors, et avec "Suivant", tu peux voir tous les calculs intermédiaires. C'est très pratique en général, sauf lorsque la formule concerne des plages très grandes, comportant beaucoup de lignes par exemple, car alors la fenêtre est trop petite pour tout afficher.

On peut aussi faire en surlignant chaque partie de la formule étudiée, sans oublier les parenthèses, et en appuyant sur F9 (Echap pour revenir en arrière).

Ceci dit, tu as à peu près compris comment fonctionne ma dernière proposition. D'après ce que tu écris, cette dernière ne semble pas assez polyvalente, ce dont je me doutais. Par exemple, pour reperer les XXX, elle supprimait d'abord les "chiffresA", ce qui ne convient pas puisque tous ces termes ne commencent pas avec des "chiffresA".

En fait, ce que tu veux, c'est classer les données en fonction des XXX et des paramètres étudiés, en supprimant toutes les cellules vides. Ca doit être faisable, sans trop de problème, mais là, je n'ai pas le temps. En conséquence, je regarderai ton nouveau fichier dès que possible, ce soir ou demain.

@ plus
 
Dernière édition:

coanda

XLDnaute Nouveau
Re : tri/réorganisation automatique de valeurs

OK. Merci pour le tuyau.

Merci beaucoup du temps que tu consacres à mon problème.

En parallèle j'essaie d'autres idées. Je te préviens si je trouve une solution qui fonctionne.
 

coanda

XLDnaute Nouveau
Re : tri/réorganisation automatique de valeurs

Merci Cisco.

J'y suis presque. Je bloque sur un petit quelque chose. Pour choisir si je dois appliquer une formule ou non, je demande à excel de vérifier le nombre de cellule non vides dans une ligne. Dans la ligne il n'y a que 2 cellules non vides. Pourtant nbval me donne 6 comme résultat. J'ai essayé de supprimer les espaces éventuel avec un "remplacer par" mais rien n'y fait. Il faut que j'efface à la main pour que le nbval soit juste. Une idée?

Aussi parfois quand je tape une formule, elle s'affiche et ne se calcule pas. Pourtant "calcul automatique" est bien coché. Pour résoudre le probleme je vais dans données/convertir et ca calcule. Si je refais entrée dans la cellule, le problème réapparait.

Des idées?

(je suis sous office mac 2011)
 

CISCO

XLDnaute Barbatruc
Re : tri/réorganisation automatique de valeurs

Bonsoir

Est-ce que cela va mieux avec ce nouveau fichier ? J'ai mis un test pour éliminer les cellules vides, mais cela n'est peut être pas indispensable dans ton fichier réel. J'ai considéré que la valeur mesure se trouve toujours le plus à gauche, en premier, et celle de contrôle plus à droite, en second, mais pas forcément juste à coté de la valeur mesure. Est-ce le cas ?

@ plus

P.S : Cela ne fonctionnera pas correctement s'il y a des colonnes ayant des en-têtes se "ressemblant", par exemple avec XXX01 et XXX011. Dis moi si ce type de situation peut exister dans ton fichier réel. J'essayerai de trouver un autre test ne fonctionnant pas avec un NBCAR(.....) < NBCAR(...)
 

Pièces jointes

  • test reorg resultats bis.xlsx
    11.1 KB · Affichages: 16
Dernière édition:

coanda

XLDnaute Nouveau
Re : tri/réorganisation automatique de valeurs

toujours à gauche la mesure oui. par contre parfois y'a pas de contrôle. Juste 1 mesure et dans ce cas le mot "mesure" n'est pas ajouté. :)

Normalement mesure et contrôle sont côte à côte. Mais au cas où je prends ta proposition. Merci pour ton fichier. Je regarde si ca simplifie ce que j'ai fait. J'ai des formules qui font 3 lignes... :)
 

CISCO

XLDnaute Barbatruc
Re : tri/réorganisation automatique de valeurs

Rebonsoir

Attention, il n'y a pas une formule, mais deux, à savoir une par colonne. Elles se ressemblent beaucoup, il ni a que le dernier paramètre différent, 1 (pour renvoyer la valeur correspondant à la mesure) ou 2 (pour renvoyer la valeur correspondant au contrôle). Si tu veux travailler avec une seule formule, on peut remplacer cela avec un MOD. Si il manque souvent la valeur de contrôle, on peut rajouter un SIERREUR(la formule;"").

@ plus
 

CISCO

XLDnaute Barbatruc
Re : tri/réorganisation automatique de valeurs

Bonjour

Quelques explications sur la formule utilisée en I5
Code:
INDEX('données brutes labo'!$A$8:$CS$10;EQUIV($G5;'données brutes labo'!$A$8:$A$10;0);PETITE.VALEUR(SI((NBCAR(SUBSTITUE('données brutes labo'!$C$5:$CS$5;I$3;""))<NBCAR('données brutes labo'!$C$5:$CS$5))*(DECALER('données brutes labo'!$C$7;EQUIV(réorg!$G5;'données brutes labo'!$A$8:$A$10;0);;;95)<>"");COLONNE($C:$CS));1))

*INDEX('données brutes labo'!$A$8:$CS$10; donne la plage à utiliser
*EQUIV($G5;'données brutes labo'!$A$8:$A$10;0) donne le n° de la ligne de cette plage à considérer (Excel renvoie la position du contenu de G5 dans la plage A8:A10)
Attention, voilà le gros morceau...
Code:
PETITE.VALEUR(SI((NBCAR(SUBSTITUE('données brutes labo'!$C$5:$CS$5;I$3;""))<NBCAR('données brutes labo'!$C$5:$CS$5))*(DECALER('données brutes labo'!$C$7;EQUIV(réorg!$G5;'données brutes labo'!$A$8:$A$10;0);;;95)<>"");COLONNE($C:$CS));1)
*(NBCAR(SUBSTITUE('données brutes labo'!$C$5:$CS$5;I$3;""))<NBCAR('données brutes labo'!$C$5:$CS$5)) renvoie en matriciel une liste de VRAI (lorsque le contenu de I3 est dans le contenu des en-têtes C5:CS5) et de FAUX (dans le cas contraire)
*(DECALER('données brutes labo'!$C$7;EQUIV(réorg!$G5;'données brutes labo'!$A$8:$A$10;0);;;95)<>"") renvoie en matriciel une liste de VRAI (lorsque le contenu de la ligne considérée (correspondant à G5) est non-vide) et de FAUX (lorsque... est vide)

Le produit (NBCAR....<NBACR....)*(DECALER(.....)<>"") renvoie une liste de 0 et 1.
Ensuite, les 0 sont remplacés par des FAUX, les 1 par le n° de la colonne où ils se trouvent grâce à COLONNE(C:CS).
Finalement, la formule PETITE.VALEUR(......;1) renvoie le plus petit de ces n° de colonnes, c-à-d celui de la colonne le plus à gauche correspondant à Mesure.

On a donc quelque chose du genre
INDEX('données brutes labo'!$A$8:$CS$10;n° de ligne de la plage;n° de colonne de la plage)

La formule en J5 travaille exactement de la même manière, sauf qu'elle renvoie la 2nde valeur de cette liste de n° de colonnes, correspondant au contrôle.

@ plus
 
Dernière édition:

Discussions similaires

Réponses
8
Affichages
490