Excel - Dernière ligne remplie d'une colonne?

[fab]

Bonjour,

Je reviens vers vous pour une question que je n'arrive pas à résoudre.
J'ai une série de fichiers qui fonctionnent tous pareil, et j'ai besoin de récupérer dans ma cellule B3 de chaque fichier la valeur de la dernière cellule remplie en colonne A. A chaque fois, je dois refaire ma formule, ça marche mais c'est fastidieux. J'ai essayé de trouver une astuce, mais sans succès, donc je viens vous appeler à l'aide ... est-ce possible et par quel moyen ? par formule, ou est-ce qu'il faut une maco pour ça ?
Merci pour vos idées,
FAB

[DeVline]

Bonjour Fab,

Si ta colonne A contient des valeurs numériques, tu peux saisir en B3 une formule du genre:

Code: Tout sélectionner

=RECHERCHE(9^9;A:A)

Cette formule consiste à chercher une valeur énorme (ici 9^9 est égale à 9 fois 9 fois 9 etc.. 9 fois soit le chiffre 387420489) dans la colonne A,si la fonction RECHERCHE ne peut trouver cette valeur cherchée, elle utilise la plus grande valeur de la colonne qui est inférieure ou égale à celle de la valeur cherchée.
Si la valeur cherchée est inférieure à la plus petite valeur de la colonne, la fonction RECHERCHE renvoie la valeur d’erreur #N/A.
En fonction des valeurs de la colonne A, si le 9^9 ne suffit pas, tu peux adapter (10^10, 11^11, etc ...) et tu auras toujours la valeur de la dernière ligne.

Si ta colonne contient des valeurs texte, le principe est le même, mais cette fois la formule peut-être:

Code: Tout sélectionner

=RECHERCHE("zzz";A:A)

L'idée ici est de chercher une valeur qui n'existe pas dans la colonne, comme "ZZZ", mais nous pourrions avoir "µ" ou tout autre caractère rare dont la présence est improbable dans la colonne A.
Ainsi RECHERCHE() renverra la dernière valeur texte de la colonne.

Si ta colonne contient soit du texte soit des valeurs numériques, une formule pourrait-être (sur un fichier de 200 lignes, donc à adapter à ton cas, ... souviens toi ce que nous avions dit la dernière fois, les formules matricielles n'acceptent pas des colonnes entières):

Code: Tout sélectionner

{=INDEX(A1:A200;MAX(NON(ESTVIDE(A1:A200))*LIGNE(A1:A200)))}

formule matricielle donc à valider par CTRL SHIFT ENTER

En décomposant, NON(ESTVIDE(A1:A200))*LIGNE(A1:A200) renverra une matrice contenant les numéros des lignes non vides de la colonne, mais avec un zéro pour les lignes vides (par exemple une matrice {1;2;3;0;0;6;0;8... etc .}, MAX() prend la plus grande valeur de cette matrice donc le plus grand numéro de ligne, enfin INDEX se charge de prendre la valeur contenue dans la cellule trouvée.

Si tu as besoin d'une macro, tu peux utiliser par exemple ce code:

Code: Tout sélectionner

Sheets("Feuil1").Range("B3") = Sheets("Feuil1").Range("A65530").End(xlUp)

ou sur Excel 2007:

Code: Tout sélectionner

Sheets("Feuil1").Range("B3") = Sheets("Feuil1").Range("A1048576").End(xlUp)

L'instruction consiste à partir de la dernière cellule en bas de la colonne A, End(xlup) étant l'équivalent des touches de raccourci CTRL SHIFT qui remonte sur la dernière cellule remplie, qui est justement celle que tu cherches :)


Voilà Fab, vois si cela peut t'aider, si non n'hésite pas à revenir ou à déposer un extrait de fichier (sans données confidentielles) pour que l'on puisse t'aider concrètement à mettre cela en place sur ta propre structure de données.

Bonne soirée/nuit et à bientôt,

[fab]

Bonjour, et mille mercis DeVline

Tout simplement GENIAL !!. Je n'ai pas pu répondre plus tôt, mais j'ai fait tous mes essais très facilement grâce à vos explications très claires, et j'obtiens exactement ce que je souhaite.
Un ENORME MERCI Devline, j'ai l'impression qu'avec Excel tout est possible finalement

Bonne soirée, et certainement à bientôt

FAB

[DeVline]

Bonsoir Fab,

Merci beaucoup pour ton retour :)

A une prochaine fois,

[cholor34]

Je ne suis pas parvenu à utiliser la formule donnée par DeVline: {=INDEX(A1:A200;MAX(NON(ESTVIDE(A1:A200))*LIGNE(A1:A200)))}
donc je relance l'échange.
Quelle formule mettre dans une cellule tableau pour: pour la première cellule vide d'une colonne C par exemple, retourne les valeurs de la même ligne des colonnes A et B.
voir image ci-dessous

merci d'avance.

[DeVline]

Bonjour cholor34

Un petit essai dans un tableau comme ci-dessous:



avec cette formule en G2 et copiée vers la droite:

Code: Tout sélectionner

=DECALER($A$1;MIN(SI(ESTVIDE(C1:C20);LIGNE(C1:C20)))-1;;;)&DECALER($B$1;MIN(SI(ESTVIDE(C1:C20);LIGNE(C1:C20)))-1;;;)

(formule matricielle à valider par les trois touches CTRL SHIFT ENTER)

Le fichier exemple est disponible sur ce lien :
http://www.cijoint.fr/cjlink.php?file=cj201102/cijN6yfzrV.xls

Bien cordialement,

D

[cholor34]

Merci DeVline pour ta réponse mais je ne parviens pas à l'adapter à mon cas
- Le résultat doit être sur une autre feuille
- comme je ne comprends pas toutes les étapes de la formule, je ne peux pas adapter.
Peux-tu me la traduire en français svp
merci encore

[DeVline]

Bonsoir cholor34,

Tu trouveras sur ce lien un nouveau fichier avec deux propositions, la première dans l'onglet "Résultat 1", qui est la formule précédente mais adaptée pour ce nouvel onglet "Résultat 1", les données de base sont placées dans l'onglet "Base", et une seconde proposition dans l'onglet "Résultat 2" en passant par des colonnes intermédiaires, sans utilisation de formules matricielles et qui sera peut-être plus facile à adapter : http://www.cijoint.fr/cjlink.php?file=cj201102/cij7sUZDEJ.xls

Concernant la formule matricielle utilisée dans ce fichier:



La formule placée dans l'onglet "Résultat 1" va puiser dans l'onglet "Base" et devient par exemple en B1:

Code: Tout sélectionner

=DECALER(Base!$A$1;MIN(SI(ESTVIDE(Base!D1:D20);LIGNE(Base!D1:D20)))-1;;;)&DECALER(Base!$B$1;MIN(SI(ESTVIDE(Base!D1:D20);LIGNE(Base!D1:D20)))-1;;;)

En décomposant cette formule, voyons déjà cette partie là:

Code: Tout sélectionner

MIN(SI(ESTVIDE(Base!D1:D20);LIGNE(Base!D1:D20)))

LIGNE(Base!D1:D20) en matricielle renvoie une matrice de 1 à 20 comme ceci : {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

Toujours en matricielle, la formule ESTVIDE(Base!D1:D20) renvoie elle aussi une matrice composée de valeurs VRAI ou FAUX. Si la cellule analysée est vide, la résultat est VRAI, dans le cas contraire le résultat est FAUX. Dans la cellule de notre exemple la matrice renvoyée est :
{FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI}

La fonction SI() utilisée ici: SI(ESTVIDE(Base!D1:D20);LIGNE(Base!D1:D20)) teste le résultat de la matrice renvoyée pas ESTVIDE(), si ce résultat est VRAI, on retient l'élément correspondant dans la matrice LIGNE(), si le résultat est FAUX on ne prend pas cet élément et on reste sur FAUX.
Dans notre exemple, la matrice renvoyée par cette fonction SI() est : {FAUX;2;FAUX;FAUX;FAUX;FAUX;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
On voit que toutes les cellules remplies dans la colonne D de l'onglet base correspondent à un FAUX. La ligne d'entête est remplie, la matrice renvoie FAUX, la ligne suivante est vide, la deuxième position de la matrice est égale à 2, etc ...

Enfin, la fonction MIN() va lire cette matrice et renvoyer la valeur minimum qu'elle contient, en l'occurrence 2.

Maintenant que l'on sait que la première cellule vide de la colonne D se trouve ligne 2, il faut aller chercher la valeur de cette ligne 2 en colonne A, c'est là qu'intervient la fonction DECALER().

La formule utilisée est celle-ci:

Code: Tout sélectionner

DECALER(Base!$A$1;MIN(SI(ESTVIDE(Base!D1:D20);LIGNE(Base!D1:D20)))-1;;;)

si je remplace le calcul par le résultat 2 obtenu ci-dessus, cette formule est en réalité:

Code: Tout sélectionner

DECALER(Base!$A$1;(2)-1;;;)

C'est à dire que je me place sur la cellule A1 de l'onglet base, je descends de 2 cellules, moins une à cause de l'entête, je ne change pas de colonne (la valeur par défaut est zéro dans ces cas là), la hauteur de mon champ est 1 tout comme la largeur (1 étant la valeur par défaut, elle n'est pas renseignée, mais j'aurais tout aussi bien pu écrire DECALER(Base!$A$1;(2)-1;0;1;1) ).
J'arrive donc sur la cellule A2 de l'onglet Base, qui contient la lettre A, c'est bien ce que renvoie cette formule.

Même raisonnement pour aller chercher la valeur située en colonne B sur la ligne 2.
Ici la formule utilisée revient à écrire DECALER(Base!$B$1;(2)1;;;), j'aurais pu dre aussi DECALER(Base!$A$1;(2)-1;1;;), le résultat est 1 soit la valeur se trouvant dans la cellule B2 de l'onglet Base.

Au final, il suffit de concaténer les deux résultats, c'est le signe & qui équivaut à la fonction CONCATENER() qui s'en charge et renvoie donc le résultat "A1"

Voilà pour les explications de cette première formule, j'espère qu'elles pourront t'aider, mais comme les formules matricielles ne sont pas toujours faciles à utiliser, voici une autre proposition (dans l'onglet Résultat 2)



Dans cet exemple, les colonnes A B et C contiennent les formules intermédiaires. En A2 et copiée vers le bas et vers la droite:

Code: Tout sélectionner

=SI(ET(Base!C2="";NB.SI(A$1:A1;"<>0")=1);Base!$A2&Base!$B2;0)

Cette formule renvoie 0 (zéro) sauf si la condition est remplie, c'est à dire que la lige correspondante de l'onglet Base est vide et que les cellules de la colonne A en partant de la première ligne jusqu'à la ligne supérieure ne contiennent pas autre chose que zéro. Il n'y a donc qu'une seule valeur par colonne.

En cellule E1 et copiée vers la droite la formule est :

Code: Tout sélectionner

=RECHERCHE("xxxx";A2:A20)

Elle va chercher une valeur texte colonne A et ne peut en trouver qu'une, qu'elle affiche donc.
Au cas où il n'y aurait que des zéros dans cette zone, la fonction RECHERCHE() renverrait #N/A.
Pour éviter cette situation inesthétique il suffit de tester cette erreur et d'écrire comme formule:

Code: Tout sélectionner

=SI(ESTNA(RECHERCHE("xxxx";A2:A20));"";RECHERCHE("xxxx";A2:A20))

Voilà pour cette seconde formule, plus simple et qui rejoint du coup un exemple proposé à FAB précédemment.

En espérant que cela pourra t'aider,

bien cordialement,

D

[cholor34]

Vraiment merci Devline!
J'ai passé l'après-midi à adapter ta première proposition et j'ai réussi un quart d'heure avant de recevoir ton 2ème tuto!
j'ai conservé la formule matricielle, voici le résultat sur mon fichier.

C'est formateur tout ça, n'est-ce pas?
Encore merci à toi, je vais étudier le tuto après un peu de repos.
Bien cordialement.

[DeVline]

re :)

Wouhawooo, il a l'air rudement bien ce fichier, merci pour ton message et vraiment bravo pour ton travail et ta tenacité :) :)

Bon repos bien mérité, et à une prochaine fois :)

D