La dernière cellule non vide dans une colonne
Que quelqu'un connait la formule pour trouver la valeur de la dernière cellule non vide dans une colonne, dans Microsoft Excel?
- Je serais à la recherche de VBA pour cela.
Vous devez vous connecter pour publier un commentaire.
Cela fonctionne avec à la fois du texte et des chiffres, et ne se soucie pas si il y a des cellules vides, c'est à dire, il sera de retour le dernier non-vide de la cellule.
Il doit être entrées dans un tableau, ce qui signifie que vous appuyez sur Ctrl-Maj-Entrée après vous tapez ou collez la dans le. Ci-dessous est pour la colonne A:
CELL()
ouOFFSET()
), alors que les les plus upvoted one renvoie une valeur sans vous dire où il est.=INDEX($C:$C,SUMPRODUCT(MAX(($C:$C<>"")*(ROW($C:$C)))))
... avec SUMPRODUCT donc n'a pas besoin d'être inscrit en tant que formule matricielleUtilisant la formule simple suivante est beaucoup plus rapide
Pour Excel 2003:
Il vous donne les avantages suivants:
Explication:
(A:A<>"")
retourne un tableau{TRUE,TRUE,..,FALSE,..}
1/(A:A<>"")
modifie ce tableau à{1,1,..,#DIV/0!,..}
.RECHERCHE
attend triés tableau dans croissant ordre, et tenant compte du fait que si leLOOKUP
fonction ne peut pas trouver une correspondance exacte, il choisit la plus grande valeur de lalookup_range
(dans notre cas{1,1,..,#DIV/0!,..}
) qui est inférieure ou égale à la valeur (dans notre cas2
), formule trouve la dernière1
dans le tableau et renvoie la valeur correspondante deresult_range
(troisième paramètre -A:A
).Aussi petite note - formule ci-dessus ne prend pas en compte les cellules avec des erreurs (vous pouvez le voir seulement si la dernière cellule non vide a l'erreur). Si vous voulez les prendre en compte, à l'utilisation:
image ci-dessous montre la différence:
LOOKUP
dit que le dernier argumentresult_vector
est facultatif. Cependant, si j'omets, je reçois un très étrange raison que je ne comprends pas.LOOKUP
fonction ne peut pas trouver lelookup_value
, la fonction correspond à la plus grande valeur danslookup_vector
qui est inférieure ou égale àlookup_value.
" Si j'utilise=LOOKUP(2,A:A<>"",A:A)
sans generatin la#DIV/0!
erreur par1/...
, il semble qu'elle retourne une valeur dans le milieu du vecteur. Je n'ai pas trouvé ce qui est exactement la fonctionnalité dans ce cas.1/(A:A<>"")
(toujours1
exepté vide col Un). 2)A:A<>""
retourne un booléen tableau, mais vous avez besoin de nombre de tableau -=LOOKUP(2,1*(A:A<>""),A:A)
, mais aussi il ne fonctionne pas comme vous le voulez de la recherche repose toujours sur le fait que le tableau est trié. Depuis le dernier élément est toujours inférieure à la valeur de la recherche, il n'a pas de sens pour afficher le reste de la matrice et de recherche tout simplement la retourne (ou de la valeur correspondante de result_vector). Donc,=LOOKUP(2,1*(A1:A10<>""),A1:A10)
renvoie toujours la valeur deA10
(si ce n'est pas une erreur).LOOKUP()
sans le 3e paramètre retourne la valeur de la 2ème paramètre et pas dans le sous-jacent vecteur, 2. Excel recherche arrière à partir de la dernière champ de vecteur. Serez-vous de votre dernier commentaire est aussi à votre poste? Vous l'accroissement de sa valeur encore plus 🙂 je vais supprimer tous mes commentaires en-dessous, afin de garder le fil propre.=LOOKUP(2,1/(A:A<>""),ROW(A:A))
mais l'ajout deROW
fonction ajouter la "volatilité" de l'effet de la formule sera recalculé à chaque fois qu'une cellule sur le wroksheet changéIci est une autre option:
=OFFSET($A$1;COUNTA(A:A)-1;0)
COUNTA
au lieu deCOUNT
). Pour ne pas mentionner les cellules vides.;
à,
Inspiré par le grand exemple donné par Doug Glancy réponse, je suis venu avec une façon de faire la même chose sans la nécessité d'un tableau de formule. Ne me demandez pas pourquoi, mais je suis désireux d'éviter l'utilisation de la matrice de formules si possible (pas pour une raison particulière, c'est juste mon style).
Ici, il est:
Pour trouver le dernier non-vide de ligne à l'aide de la Colonne A dans la colonne de référence
Pour trouver la dernière non vide de la colonne à l'aide de la ligne 1 à la ligne de référence
Cela peut encore être utilisé en conjonction avec la fonction index efficace de définir la dynamique des plages nommées, mais c'est quelque chose pour un autre post que ce n'est pas lié à la question immédiate est question dans les présentes.
J'ai testé les méthodes ci-dessus avec Excel 2010, les deux "nativement" et en "Mode de Compatibilité" (pour les anciennes versions d'Excel) et ils travaillent. Encore une fois, avec ces vous n'avez pas besoin de faire des combinaisons de touches Ctrl+Maj+Entrée. En tirant parti de la façon sumproduct travaille dans Excel que nous pouvons obtenir nos bras autour de la nécessité d'effectuer tableau des opérations, mais nous le faisons sans un tableau de formule. J'espère que quelqu'un là-bas peut apprécier la beauté, la simplicité et l'élégance de ce projet de sumproduct solutions autant que je le fais. Je ne témoignent pas de la mémoire de l'efficacité des solutions ci-dessus si. Juste qu'ils sont simple, belle, aide à l'usage prévu et sont suffisamment flexibles pour étendre leur utilisation à d'autres fins 🙂
Espérons que cette aide!
Tout le meilleur!
Je sais que cette question est ancienne, mais je ne suis pas satisfait de la réponse fournie.
De RECHERCHE, RECHERCHEV et HLOOKUP a des problèmes de performances et devrait vraiment jamais être utilisé.
Les fonctions de tableau a beaucoup de frais généraux et peuvent également avoir des problèmes de performances, il ne doit être utilisé qu'en dernier recours.
COMTE et NBVAL rencontrer des problèmes si les données n'est pas contigu non vide, c'est à dire que vous avez des espaces vides, puis à nouveau les données dans la plage en question
INDIRECTE est volatile de sorte qu'il ne devrait être utilisé qu'en dernier recours
DÉCALAGE est volatile de sorte qu'il ne devrait être utilisé qu'en dernier recours
toutes les références à la dernière ligne ou colonne (le 65536th ligne dans Excel 2003, par exemple) n'est pas robuste et résultats dans les frais généraux supplémentaires
C'est ce que j'utilise
lorsque le type de données est mixte:
=max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))
lorsque l'on sait que les données ne contient que des nombres:
=MATCH(1E+306,[RANGE],1)
lorsque l'on sait que les données ne contient que du texte:
=MATCH("*",[RANGE],-1)
MATCH le plus bas à la verticale et est non-volatile, donc si vous travaillez avec un grand nombre de données c'est le meilleur à utiliser.
TRUE
/FALSE
), ils passent inaperçus. Malgré le fait que cette réponse n'est pas l'épreuve des balles, je pense toujours que cette réponse a le moins d'impact sur les performances.=INDEX(N:N,MATCH(1E+306,N:N,1))
Cela fonctionne dans Excel 2003 (& plus tard, avec une modification mineure, voir ci-dessous). Appuyez sur Ctrl+Maj+Entrée (pas seulement de l'Entrée) pour entrer dans ce en tant que formule matricielle.
Être conscient que Excel 2003 est impossible d'appliquer une formule de tableau à une colonne entière. Ce faisant, les rendements
#NUM!
; les résultats sont imprévisibles! (MODIFIER: des informations Contradictoires à partir de Microsoft: La même peut ou ne peut pas être vrai à propos de Excel 2007; problème peut ont été fixés en 2010.)C'est pourquoi j'applique la formule de matrice de gamme
A1:A65535
et de donner un traitement spécial à la dernière cellule, qui estA65536
dans Excel 2003. Ne peut pas simplement direA:A
ou mêmeA1:A65536
que ce dernier revient automatiquement àA:A
.Si vous êtes absolument sûr de
A65536
est vide, alors vous pouvez sauter leIF
partie:Notez que si vous utilisez Excel 2007 ou 2010, le dernier numéro de ligne est 1048576 pas 65536, afin d'ajuster le ci-dessus selon le cas.
Si il n'existe pas de cellules vides dans le milieu de vos données, ensuite, je voudrais juste utiliser la formule plus simple,
=INDEX(A:A,COUNTA(A:A))
.=INDEX(19:19,COUNTA(19:19))
Une solution alternative sans formules de tableau, éventuellement plus robuste que une réponse précédente avec un (allusion à une solution sans formules de tableau, est
Voir cette réponse comme un exemple.
Bravo à Brad et barry houdini, qui a aidé à la résolution de cette question.
Possible des raisons de préférer un non-formule de tableau sont donnés:
Une page officielle de Microsoft (chercher "les Inconvénients de l'utilisation de formules de tableau").
Les formules de tableau peut sembler magique, mais ils ont aussi quelques inconvénients:
La Formule De Tableau Hérésie.
A
occurrences avec unD
. Ce que je fais mal?si vous recherchez dans la Colonne (A) utilisation :
si votre plage A1:A10, vous pouvez utiliser:
dans cette formule :
renvoie dernier non vide numéro de ligne et indirects() renvoie la valeur de la cellule.
=INDEX(A:A, COUNTA(A:A), 1)
prises de ici=MATCH("*";A1:A10;-1)
pour les données textuelles=MATCH(0;A1:A10;-1)
pour les données numériquesJ'ai essayé tous les non-volatile versions, mais Pas une version donnée ci-dessus a travaillé.. excel 2003/2007update. Certes, cela peut être fait dans excel 2003. Pas comme un tableau, ni la formule standard.
Soit je reçois juste un vide, 0 ou #valeur d'erreur.
J'ai donc recours à la volatilité des méthodes .. Cela a fonctionné..
@Julien Kroné .. l'Aide ";" au lieu de "," ne fonctionne PAS! Je pense que vous êtes à l'aide de Libre Office MS excel?
De RECHERCHE est si fâcheusement volitile-je l'utiliser que comme un dernier recours seulement
Placez ce code dans un module VBA. Enregistrer. En vertu de fonctions définies par l'Utilisateur de regarder pour Cette fonction.
pour les données textuelles:
pour les données numériques:
Cela vous donne l'indice relatif de la dernière cellule non vide dans la plage sélectionnée (ici A1:A10).
Si vous voulez obtenir la valeur, d'y accéder via INDIRECTE après la construction -textuellement - l'absolu de la cellule de référence, par exemple:
J'ai eu le même problème aussi. Cette formule fonctionne aussi bien:-
14 étant le numéro de ligne de la première ligne dans les lignes que vous souhaitez compter.
Chronique Clawtooth
J'ai utilisé HLOOKUP
A1
a une date;A2:A8
a prévisions capturées à différents moments, je veux les derniersIl utilise un standard hlookup formule avec la recherche tableau défini par le nombre d'entrées.
Si vous savez qu'il ne vont pas être les cellules vides dans les entre les, le moyen le plus rapide est de cette.
Il seulement compte de la non-cellules vides et fait référence à la cellule appropriée.
Il peut être utilisé pour une gamme spécifique ainsi.
Ceci renvoie à la dernière cellule non vide dans la gamme O4:O34.
Pour Microsoft office 2013
"Dernière" d'une partie non vide de ligne:
"Dernière" non vide de ligne:
Cette formule a travaillé avec moi pour office 2010:
=LOOKUP(2;1/(A1:A100<>"");A1:A100)
A1: la première cellule
A100: reportez-vous à la dernière cellule de la comparaison de
Je pense que la réponse de W5ALIVE est le plus proche de ce que j'utilise pour trouver la dernière ligne de données dans une colonne. En supposant que je suis à la recherche de la dernière ligne avec les données de la Colonne A, cependant, je voudrais utiliser ce qui suit pour les plus génériques de recherche:
Le premier MATCH va trouver la dernière cellule de texte et le deuxième MATCH trouve le dernier nombre de la cellule. La fonction sierreur renvoie zéro si le premier MATCH trouve tout numérique cellules ou si le deuxième match trouve tous les textes des cellules.
Fondamentalement, c'est une légère variation de W5ALIVE mixte du texte et le nombre de solution.
De tester le timing, c'était significativement plus rapide que l'équivalent de RECHERCHE de variations.
De retourner la valeur réelle de la dernière cellule, je préfère l'utilisation indirecte des cellules de référencement comme ceci:
La méthode offerte par sancho.s est peut-être une option plus propre, mais je voudrais modifier la partie qui se trouve le numéro de ligne à ceci:
la seule différence étant que l' ",1" retourne la première valeur, tandis que la ",0" retourne l'ensemble de la gamme de valeurs (tous, sauf un, qui ne sont pas nécessaires). J'ai toujours tendance à préférer l'adressage de la cellule à la fonction de l'index, en d'autres termes, de retourner la valeur de la cellule avec:
Grand fil!
Si vous n'êtes pas peur d'utiliser des tableaux, puis le suivant est une formule très simple pour résoudre le problème:
=SOMME(SI(A:A<>"",1,0))
Vous devez appuyer sur CTRL + MAJ + ENTRÉE parce que c'est une formule de tableau.
Simple qui fonctionne pour moi:
Ok, donc j'ai eu le même problème que la personne, et essayé les deux réponses les plus fréquentes. Mais seulement obtenir des erreurs de formule. S'est avéré qu'il fallait que je change le "," à ";" pour les formules de travail. Je suis à l'aide de XL 2007.
Exemple:
ou
Pour le suivi de version (en ajoutant la lettre v pour le début du numéro), j'ai trouvé celui-ci pour bien travailler en Xcelsius (SAP tableaux de bord)