Dernière case non vide dans une colonne

utilisant la formule simple suivante est beaucoup plus rapide

=LOOKUP(2,1/(A:A<>""),A:A)

Pour Excel 2003:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

il vous donne les avantages suivants:

• il est pas de tableau formule
• c'est pas volatile formule

Explication:

• (A:A<>"") retourne un tableau {TRUE,TRUE,..,FALSE,..}
• 1/(A:A<>"") modifie ce tableau en {1,1,..,#DIV/0!,..} .
• depuis LOOKUP attend trié tableau dans Ascendant ordre, et en tenant compte que si la fonction LOOKUP ne peut pas trouver une correspondance exacte, il choisit la plus grande valeur dans le lookup_range (dans notre cas {1,1,..,#DIV/0!,..} ) qui est inférieur ou égal à la valeur (dans notre cas 2 ), la formule trouve la dernière 1 dans le tableau et renvoie la valeur correspondante de result_range (troisième paramètre - A:A ).

"aussi petite note - la formule ci-dessus ne tient pas compte des cellules avec des erreurs (vous pouvez le voir seulement si la dernière cellule non vide a une erreur). Si vous voulez en tenir compte, utilisez:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

l'image ci-dessous montre la différence:

Voici une autre option: =OFFSET($A;COUNTA(A:A)-1;0)

inspiré par la grande avance donnée par la réponse de Doug Glancy, j'ai trouvé un moyen de faire la même chose sans avoir besoin d'un tableau-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).

le voici:

=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

pour trouver la dernière ligne non vide en utilisant la colonne de référence

=SUMPRODUCT(MAX((:<>"")*(COLUMN(1:1))))

pour trouver la dernière colonne non vide en utilisant la ligne 1 comme ligne de référence

ceci peut être utilisé en conjonction avec la fonction d'index pour définir efficacement les plages nommées dynamiques, mais c'est quelque chose pour un autre post car ceci n'est pas lié à la question immédiate abordée ici.

j'ai testé les méthodes ci-dessus avec Excel 2010, à la fois" nativement "et en" mode Compatibilité " (pour les anciennes versions D'Excel) et ils fonctionnent. Encore une fois, avec ces vous n'a pas besoin de faire des combinaisons de touches Ctrl+Maj+Entrée. En tirant parti de la façon dont sumproduct fonctionne dans Excel, nous pouvons obtenir nos bras autour de la nécessité de porter des opérations-réseau, mais nous le faisons sans une formule-réseau. J'espère que quelqu'un appréciera autant que moi la beauté, la simplicité et l'élégance des solutions proposées. Je n'atteste pas de l'efficacité mémoire des solutions ci-dessus. Tout simplement qu'ils sont simples, regarder beau, aider le but visé et sont assez souple pour étendre leur utilisation à d'autres fins :)

Espérons que cette aide!

Tout le meilleur!

cela fonctionne dans Excel 2003 (et plus tard avec minor edit, voir ci-dessous). Appuyez sur Ctrl+Shift+Enter (pas seulement Enter) pour entrer cette formule dans un tableau.

=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)

soyez conscient que Excel 2003 est incapable d'appliquer une formule de tableau à une colonne entière. Cela donne #NUM! ; des résultats imprévisibles peuvent se produire! (EDIT : information contradictoire de Microsoft: le même peut ou ne peut pas être vrai au sujet D'Excel 2007; problème mai ont été corrigés en 2010. )

C'est pourquoi j'applique la formule du tableau à la gamme A1:A65535 et donne un traitement spécial à la dernière cellule, qui est A65536 dans Excel 2003. On ne peut pas dire simplement A:A ou même A1:A65536 car ce dernier revient automatiquement à A:A .

Si vous êtes absolument sûr A65536 est vide, alors vous pouvez sauter la IF partie:

=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))

notez que si vous utilisez Excel 2007 ou 2010, le numéro de la dernière ligne est 1048576 et non 65536, donc ajustez ce qui précède comme approprié.

S'il n'y a pas de cellules Vierges au milieu de vos données, alors j'utiliserais la formule plus simple, =INDEX(A:A,COUNTA(A:A)) .

je sais que cette question Est ancienne, mais je ne suis pas satisfait des réponses fournies.

• de RECHERCHE, RECHERCHEV et HLOOKUP a des problèmes de performances et devrait vraiment jamais être utilisé.

• les fonctions du tableau ont beaucoup de fonctions aériennes et peuvent aussi avoir des problèmes de performance, de sorte qu'il ne devrait être utilisé qu'en dernier recours.

• les données ne sont pas contiguement non vides, c'est-à-dire que vous avez des espaces vides et des données à nouveau dans la gamme en question

• INDIRECTE est volatile de sorte qu'il ne devrait être utilisé qu'en dernier recours

• OFFSET est volatile de sorte qu'il ne devrait être utilisé qu'en dernier recours

• toute référence à la dernière ligne ou colonne possible (la 65536e ligne dans Excel 2003, par exemple) n'est pas robuste et résulte en frais généraux supplémentaires

C'est ce que j'utilise

• lorsque le type de données est mélangé: =max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))

• quand il est connu que les données ne contiennent que des numéros: =MATCH(1E+306,[RANGE],1)

• quand on sait que les données ne contiennent que du texte: =MATCH("*",[RANGE],-1)

MATCH a les frais généraux les plus bas et est non-volatile, donc si vous travaillez avec beaucoup de données c'est le meilleur à utiliser.

une solution alternative sans formules de tableaux, peut-être plus robuste que celle de une réponse précédente avec un (indice à a) solution sans formules de tableaux , est

=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))

voir cette réponse à titre d'exemple. Bravo à Brad et barry houdini , qui a aidé à résoudre cette question .

Les raisons possibles pour préférer une formule sans tableau sont données dans:

1. une page officielle de Microsoft (cherchez"inconvénients d'utiliser des formules de tableau").
   
   Les formules de tableaux peuvent sembler magiques, mais elles ont aussi certains inconvénients:

   • vous pouvez parfois oublier d'appuyer sur CTRL+SHIFT+ENTER. N'oubliez pas d'appuyer sur cette combinaison de touches chaque fois que vous entrez ou éditez un la formule de tableau.
   • D'autres utilisateurs peuvent ne pas comprendre vos formules. Les formules de tableaux sont relativement peu documentées, donc si d'autres personnes ont besoin de modifier vos cahiers d'exercices, vous devriez éviter les formules de tableaux ou vous assurer que ces utilisateurs comprennent comment les modifier.
   • selon la vitesse de traitement et la mémoire de votre ordinateur, les formules de grande série peuvent ralentir les calculs.

2. Hérésie De Formule De Tableau .

=MATCH("*";A1:A10;-1) Pour données textuelles

=MATCH(0;A1:A10;-1) Pour données numériques

=INDEX(A:A, COUNTA(A:A), 1) extrait de ici

J'ai essayé toutes les versions non-volatiles mais pas une seule version donnée ci-dessus a fonctionné.. excel 2003 / 2007update. Cela peut certainement être fait dans excel 2003. Pas comme un tableau ou une formule standard. J'obtiens soit juste un blanc, 0 ou une erreur de valeur. Alors j'ai recours aux méthodes volatiles .. Cela a fonctionné..

=recherche (2,1/(T4:T369<>""), T4: T369)

@Julian Kroné .. Utiliser"; "au lieu de", " ne fonctionne pas! Je pense que vous êtes en utilisant Bureau Libre pas MS excel? La recherche est si bruyante que je ne l'utilise qu'en dernier recours

placez ce code dans un module VBA. Enregistrer. Sous fonctions, L'utilisateur défini regarder pour cette fonction.

Function LastNonBlankCell(Range As Excel.Range) As Variant
    Application.Volatile
    LastNonBlankCell = Range.End(xlDown).Value
End Function

Pour données textuelles:

EQUIV("";A1:A10;-1)

Pour données numériques:

EQUIV(0;A1:A10;-1)

cela vous donne l'indice relatif de la dernière case non vide dans la gamme sélectionnée (ici A1:A10).

si vous voulez obtenir la valeur, accédez-y Via INDIRECT après avoir construit-textuellement - la référence de cellule absolue, par exemple:

INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1))

j'ai eu le même problème aussi. Cette formule fonctionne également aussi bien: -

=INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G:$G535)-1))))

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 des prévisions captées à différents moments, je veux la dernière

=Hlookup(a1,a1:a8,count(a2:a8)+1)

utilise une formule HLOOKUP standard avec le tableau de recherche défini par le nombre d'entrées.

si vous savez qu'il n'y aura pas de cellules vides entre les deux, le moyen le plus rapide est celui-ci.

=INDIRECT("O"&(COUNT(O:O,"<>""")))

il compte juste les cellules non vides et se réfère à la cellule appropriée.

Il peut être utilisé pour une gamme spécifique.

=INDIRECT("O"&(COUNT(O4:O34,"<>""")+3))

renvoie la dernière case non vide dans la gamme O4:O34.

pour Microsoft office 2013

"Dernière" d'une partie non vide de ligne:

=OFFSET(Sheet5!$C,COUNTA(Sheet5!$C:$C)-2,0)

"Dernière" non vide de ligne:

=OFFSET(Sheet5!$C,COUNTA(Sheet5!$C:$C)-1,0)

cette formule a fonctionné avec moi pour office 2010:

=recherche (2;1/(A1:A100<>""); A1: a100)

A1: la première cellule A100: se référer à la dernière case en comparant

je pense que la réponse de W5ALIVE est la plus proche de ce que j'utilise pour trouver la dernière ligne de données dans une colonne. En supposant que je cherche la dernière ligne avec les données dans la colonne A, cependant, j'utiliserais la suivante pour la recherche plus générique:

=MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0))

la première correspondance trouvera la dernière cellule de texte et la seconde la dernière cellule numérique. La fonction IFERROR renvoie zéro si la première correspondance trouve toutes les cellules numériques ou si la seconde trouve tout le texte cellule.

fondamentalement, il s'agit d'une légère variation de la solution de texte et de nombre mixte de W5ALIVE.

lors de l'essai du timing, ce délai était beaucoup plus rapide que les variations de recherche équivalentes.

pour retourner la valeur réelle de cette dernière cellule, je préfère utiliser la référence indirecte de la cellule comme ceci:

=INDIRECT("A"&MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0)))

la méthode offerte par sancho.s est peut-être une option plus propre, mais je modifierais la partie qui trouve le numéro de ligne:

=INDEX(MAX((A:A<>"")*(ROW(A:A))),1)

la seule différence étant que le", 1 "renvoie la première valeur alors que le", 0 " renvoie la totalité du tableau des valeurs (toutes sauf une qui ne sont pas nécessaires). J'ai encore tendance à préférer m'adresser à la cellule à la fonction d'index, en d'autres termes, retourner la valeur de la cellule avec:

=INDIRECT("A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))

grand fil!

si vous n'avez pas peur d'utiliser des tableaux, alors la formule suivante est très simple pour résoudre le problème:

= SOMME (SI (A: A<>"",1,0))

vous devez appuyer sur Ctrl + SHIFT + ENTER car c'est une formule de tableau.

si vous recherchez dans la colonne (a) utilisez:

=INDIRECT("A" & SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A)))))

si votre gamme est A1:A10 vous pouvez utiliser:

=INDIRECT("A" & SUMPRODUCT(MAX(($A:$A10<>"")*(ROW($A:$A10)))))

dans cette formule:

SUMPRODUCT(MAX(($A:$A10<>"")*(ROW($A:$A10))))

renvoie le dernier numéro de ligne non vide ,et indirect() Renvoie la valeur de la cellule.

un simple qui fonctionne pour moi:

=F7-INDEX(A:A,COUNT(A:A))

Ok, donc j'ai eu le même problème que l'asker, et j'ai essayé les deux meilleures réponses. Mais seulement obtenir des erreurs de formule. Il s'est avéré que j'avais besoin d'échanger le "," à ";" pour que les formules fonctionnent. Je suis à l'aide de XL 2007.

exemple:

=LOOKUP(2;1/(A:A<>"");A:A)

ou

=INDEX(A:A;MAX((A:A<>"")*(ROW(A:A))))

pour le suivi de version (ajout de la lettre v Au début du numéro), j'ai trouvé que celui-ci fonctionne bien dans Xcelsius (tableaux de bord SAP)

="v"&MAX(A2:A500)