recherche de la première valeur d'une colonne

[napo124]

Bonjour,
Je voudrais lister horizontalement les valeurs des cellules non vides d'une colonne à l'aide d'une formule.
Exemple:
Colonne d'origine:

vide
vide
vide
125
vide
456
vide
vide
875

Ma ligne serait au final:
125 / 456 / 875

Merci de votre aide!

 

[JCGL]

Re : recherche de la première valeur d'une colonne

Bonjour à tous,

Par formule, je ne sais pas mais tu fais un tri sur ta colonne, Copier, Collage Spécial en cochant Transpose

A+ à tous

 

[hoerwind]

Re : recherche de la première valeur d'une colonne

Bonjour, salut JCGL,

Petite formule matricielle sous la pièce jointe.
A valider par Ctrl+Maj+Entrée

 

[JCGL]

Re : recherche de la première valeur d'une colonne

Bonjour à tous,
Salut HoerWind 🙂,

Jolie Formule...

A+ à tous

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Bonjour hoerwind
Bravo pour ta formule! Elle résout un problème que j'ai depuis un moment et que j'ai soumis à plusieurs forums, sans succès.
Par contre, une fois ta formule appliquée à 3 lignes horizontales, je n'ai pas réussi à déplacer la matrice sans provoquer des erreurs (voir fichier joint).

J'apprécierais beaucoup que tu regardes rapidement ma matrice pour repérer le soucis!


Merci

 

[job75]

Re : recherche de la première valeur d'une colonne

Bonjour le fil, bienvenue Rodriguez sur le forum,

C'est sûr, on ne fait pas ce qu'on veut avec des formules matricielles dans les tableaux 😉

Pour faire un copier/coller du 1er tableau, Rodriguez, procédez ainsi :

1) Commencez par sélectionner la plage A4:J6 et touche Suppr pour effacer toutes les formules (il y avait 3 plages en matrices...).

2) Entrez en A4 la formule matricielle (validation par Ctrl+Maj+Entrée) :

=SI(NBVAL(1:1)>NBVAL($A4:DECALER(A4;;-1))-ESTERR(DECALER(A4;;-1));INDEX(1:1;GRANDE.VALEUR(SI($A1:$IU1<>"";COLONNE($A1:$IU1));NBVAL(1:1)-NBVAL($A4:DECALER(A4;;-1))+ESTERR(DECALER(A4;;-1))));"")

Tirez-la à droite puis vers le bas pour remplir la plage A4:J6.

3) Maintenant, grâce au DECALER(A4;;-1)), vous pouvez copier/coller le 1er tableau où vous voulez dans la feuille. Cela marchera s'il n'y a aucune valeur existante dans les lignes où l'on colle.

Nota : bien sûr le couper/coller ne marchera pas...

Edit : si l'on veut être puriste, utiliser ESTREF dans la formule :

=SI(NBVAL(1:1)>NBVAL($A4:DECALER(A4;;-1))-NON(ESTREF(DECALER(A4;;-1)));INDEX(1:1;GRANDE.VALEUR(SI($A1:$IU1<>"";COLONNE($A1:$IU1));NBVAL(1:1)-NBVAL($A4:DECALER(A4;;-1))+NON(ESTREF(DECALER(A4;;-1)))));"")

A+

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Merci beaucoup job75, c'est parfait! Après 1 jour et demi de recherches, je vais maintenant pouvoir continuer mon travail!

Bonne continuation

 

[job75]

Re : recherche de la première valeur d'une colonne

Re,

Je continue, avec une formule un peu plus simple :

=SI(NBVAL(1:1)>NBVAL($A4:DECALER(A4;;-1))-[COLOR="Red"](COLONNE()=1)[/COLOR];INDEX(1:1;GRANDE.VALEUR(SI($A1:$IU1<>"";COLONNE($A1:$IU1));NBVAL(1:1)-NBVAL($A4:DECALER(A4;;-1))+(COLONNE()=1)));"")

Aussi avec PETITE.VALEUR :

=SI(NBVAL(1:1)>NBVAL($A4:DECALER(A4;;-1))-(COLONNE()=1);INDEX(1:1;PETITE.VALEUR(SI($A1:$IU1<>"";COLONNE($A1:$IU1));NBVAL($A4:DECALER(A4;;-1))+(COLONNE()>1)));"")

A+

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Encore mieux!
J'abuserais une dernière fois en te demandant une précision qui résoudrait mon pb à 100%: est-il possible de restreindre le calcul aux données du tableau (B9:K11), sans tenir compte des cellules avant et après?
J'ai essayé de remplacer 9:9 par $B9:$K9, mais cela n'a pas fonctionné.

Merci beaucoup!

 

[job75]

Re : recherche de la première valeur d'une colonne

Re,

Cela ne paraît pas possible.

Pour une cellule du tableau, s'il y a des valeurs à ne pas prendre en compte en début ou en fin de ligne, comment savoir où commence et où finit le tableau ?

C'est pour ça que j'utilise la plage $Ax:$IUx pour la ligne x : ses extémités sont toujours les mêmes, quelle que soit la position du tableau. En fait c'est un tableau élargi qui est étudié.

Remarquer que les formules fonctionnent aussi en utilisant la référence x:x pour la ligne x. Mais sous Excel 2007, cela fait travailler chaque formule sur une plage de 16384 cellules, c'est lourd...

A+

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Très bien, merci pour tout!

 

[job75]

Re : recherche de la première valeur d'une colonne

Re,

Evidemment, on peut toujours adapter la formule de départ à la nouvelle position du tableau (position des colonnes). Voyez le fichier joint et comparez les formules en A4 et en B12.

En B12:

=SI(NBVAL($B9:$K9)>COLONNE()-[B][COLOR="Red"]2[/COLOR][/B];INDEX($B9:$K9;PETITE.VALEUR(SI($B9:$K9<>"";COLONNE($B9:$K9)-[COLOR="Red"][B]1[/B][/COLOR]);COLONNE()-[COLOR="Red"][B]1[/B][/COLOR]));"")

Mais je pense que vous voulez que la formule s'adapte toute seule (ce que j'ai fait précédemment), et ça ce n'est pas possible s'il y a des valeurs à gauche ou à droite du tableau .

A+

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Bon et bien on arrive à THE formule! C'est exactement ce que je recherchais, je n'ai plus besoin de monopoliser des lignes spécialement pour ce tableau et on peux utiliser les cellules de droite et de gauche pour d'autres calculs.
Je ne voulais pas spécialement que la formule s'adapte toute seule, mais plutôt de limiter le calcul aux cellules du tableau, ce qui est désormais chose faite!

 

[job75]

Re : recherche de la première valeur d'une colonne

Re,

Bon OK, j'avais été trop loin, une formule générale n'était pas nécessaire.

Mais cela suppose que vous ayez bien compris comment adapter manuellement la formule si vous déplacez le tableau vers d'autres colonnes 🙂

A+

 

[Rodriguez]

Re : recherche de la première valeur d'une colonne

Pour Info je joins mon vrai fichier sur lequel j'ai pu appliquer votre formule.
Il est vrai que je n'avais pas pensé au pb engendré par les valeurs de cellules = à texte nul ("") qui posent problème à la matrice (elle affiche #NOMBRE!). Mais cela reste secondaire.

Bonne soirée!