Czy ktoś zna formułę znajdującą wartość ostatniej niepustej komórki w kolumnie, w programie Microsoft Excel?
Czy ktoś zna formułę znajdującą wartość ostatniej niepustej komórki w kolumnie, w programie Microsoft Excel?
Odpowiedzi:
Działa to zarówno z tekstem, jak i liczbami i nie ma znaczenia, czy są puste komórki, tj. Zwróci ostatnią niepustą komórkę.
Musi być wpisany w tablicę , co oznacza, że po wpisaniu lub wklejeniu naciśniesz Ctrl-Shift-Enter . Poniższe dotyczy kolumny A:
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
CELL()
Lub OFFSET()
), podczas gdy ta z większą liczbą głosów zwraca wartość bez informowania, gdzie ona się znajduje.
Korzystanie z prostego wzoru jest znacznie szybsze
=LOOKUP(2,1/(A:A<>""),A:A)
W przypadku programu Excel 2003:
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
Daje następujące korzyści:
Wyjaśnienie:
(A:A<>"")
zwraca tablicę {TRUE,TRUE,..,FALSE,..}
1/(A:A<>"")
modyfikuje tę tablicę na {1,1,..,#DIV/0!,..}
.LOOKUP
oczekuje posortowanej tablicy w porządku rosnącym i biorąc pod uwagę, że jeśli LOOKUP
funkcja nie może znaleźć dokładnego dopasowania, wybiera największą wartość w lookup_range
(w naszym przypadku {1,1,..,#DIV/0!,..}
), która jest mniejsza lub równa wartości (w naszym przypadku 2
), formuła znajduje ostatnie 1
w tablicy i zwraca odpowiednią wartość z result_range
(trzeci parametr - A:A
).Mała uwaga - powyższa formuła nie uwzględnia komórek z błędami (widać to tylko wtedy, gdy ostatnia niepusta komórka zawiera błąd). Jeśli chcesz je wziąć pod uwagę, użyj:
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)
obraz poniżej pokazuje różnicę:
LOOKUP
mówi, że ostatni argument result_vector
jest opcjonalny. Jeśli jednak go pominę, otrzymam bardzo dziwny wynik, którego nie rozumiem.
LOOKUP
funkcja nie może znaleźć elementu lookup_value
, funkcja dopasowuje największą wartość, lookup_vector
która jest mniejsza lub równa lookup_value.
”. Jeśli użyję =LOOKUP(2,A:A<>"",A:A)
bez generowania #DIV/0!
błędu przez 1/...
, wygląda na to, że zwraca pewną wartość w środku wektor. Nie znalazłem dokładnej funkcjonalności w tym przypadku.
=LOOKUP(2,1/(A:A<>""),ROW(A:A))
ale dodanie ROW
funkcji doda efekt "zmienności" - formuła zostanie przeliczona za każdym razem, gdy zmieniona zostanie dowolna komórka w arkuszu
Oto inna opcja: =OFFSET($A$1;COUNTA(A:A)-1;0)
COUNTA
zamiast COUNT
). Nie wspominając o pustych komórkach.
Zainspirowany wspaniałą wskazówką udzieloną przez odpowiedź Douga Glancy'ego, wymyśliłem sposób na zrobienie tego samego bez potrzeby stosowania formuły tablicowej. Nie pytaj mnie dlaczego, ale staram się unikać stosowania formuł tablicowych, jeśli to w ogóle możliwe (nie z żadnego konkretnego powodu, to tylko mój styl).
Oto ona:
=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))
Aby znaleźć ostatni niepusty wiersz przy użyciu kolumny A jako kolumny odniesienia
=SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))
Aby znaleźć ostatnią niepustą kolumnę, używając wiersza 1 jako wiersza odniesienia
Może to być dalej wykorzystane w połączeniu z funkcją indeksu, aby efektywnie definiować dynamiczne nazwane zakresy, ale jest to temat na inny post, ponieważ nie jest to związane z bezpośrednim pytaniem, o którym tutaj mowa.
Przetestowałem powyższe metody w programie Excel 2010, zarówno „natywnie”, jak iw „Trybie zgodności” (dla starszych wersji programu Excel) i działają. Ponownie, dzięki tym nie musisz wykonywać żadnej z kombinacji Ctrl + Shift + Enter. Wykorzystując sposób, w jaki sumproduct działa w programie Excel, możemy ogarnąć potrzebę wykonywania operacji tablicowych, ale robimy to bez formuły tablicowej. Mam nadzieję, że ktoś może docenić piękno, prostotę i elegancję proponowanych rozwiązań sumarycznych tak samo jak ja. Nie zaświadczam jednak o wydajności pamięci powyższych rozwiązań. Wystarczy, że są proste, pięknie wyglądają, pomagają w zamierzonym celu i są na tyle elastyczne, że można je rozszerzyć na inne cele :)
Mam nadzieję że to pomoże!
Wszystkiego najlepszego!
Wiem, że to stare pytanie, ale nie jestem zadowolony z udzielonych odpowiedzi.
WYSZUKAJ, WYSZUKAJ.PIONOWO i WYSZUKAJ.PIONOWO mają problemy z wydajnością i nigdy nie powinny być używane.
Funkcje tablicowe wiążą się z dużym obciążeniem i mogą mieć również problemy z wydajnością, dlatego należy ich używać tylko w ostateczności.
COUNT i COUNTA napotykają problemy, jeśli dane nie są ciągłe niepuste, tj. Masz spacje, a następnie dane ponownie w przedmiotowym zakresie
POŚREDNI jest niestabilny, więc powinien być używany tylko w ostateczności
OFFSET jest niestabilny, więc powinien być używany tylko w ostateczności
wszelkie odniesienia do ostatniego możliwego wiersza lub kolumny (na przykład 65536-ty wiersz w programie Excel 2003) nie są solidne i powodują dodatkowe obciążenie
To jest to, czego używam
gdy typ danych jest mieszany: =max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1))
gdy wiadomo, że dane zawierają tylko liczby: =MATCH(1E+306,[RANGE],1)
gdy wiadomo, że dane zawierają tylko tekst: =MATCH("*",[RANGE],-1)
MATCH ma najniższy narzut i jest nieulotny, więc jeśli pracujesz z dużą ilością danych, jest to najlepsze rozwiązanie.
TRUE
/ FALSE
), pozostaną niewykryte. Pomimo tego, że ta odpowiedź nie jest kuloodporna, nadal uważam, że ta odpowiedź ma najmniejszy wpływ na wydajność.
=INDEX(N:N,MATCH(1E+306,N:N,1))
Działa to w programie Excel 2003 (i później z niewielkimi zmianami, patrz poniżej). Naciśnij Ctrl + Shift + Enter (nie tylko Enter), aby wprowadzić to jako formułę tablicową.
=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)
Należy pamiętać, że program Excel 2003 nie może zastosować formuły tablicowej do całej kolumny. Takie postępowanie przynosi rezultaty #NUM!
; mogą wystąpić nieprzewidywalne skutki! (EDIT : Sprzeczne informacje z firmy Microsoft: ten sam maj lub nie mogą być prawdziwe w programie Excel 2007, problemem może zostały ustalone w 2010 roku )
Dlatego stosuję formułę tablicową do zakresu A1:A65535
i traktuję w specjalny sposób ostatnią komórkę, która jest A65536
w programie Excel 2003. Nie mogę po prostu powiedzieć, A:A
ani nawet, A1:A65536
gdy ta ostatnia automatycznie powraca do A:A
.
Jeśli masz absolutną pewność, że A65536
jest puste, możesz pominąć tę IF
część:
=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))
Zwróć uwagę, że jeśli używasz programu Excel 2007 lub 2010, ostatni numer wiersza to 1048576, a nie 65536, więc dostosuj powyższe odpowiednio.
Jeśli nie ma puste komórki w środku swoich danych, to bym po prostu użyć prostszej formuły =INDEX(A:A,COUNTA(A:A))
.
=INDEX(19:19,COUNTA(19:19))
Alternatywnym rozwiązaniem bez formuł tablicowych, prawdopodobnie bardziej niezawodnym niż to z poprzedniej odpowiedzi z rozwiązaniem (wskazówka do a) bez formuł tablicowych , jest
=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))
Zobacz tę odpowiedź jako przykład. Uznanie dla Brada i Barry'ego Houdiniego , którzy pomogli rozwiązać to pytanie .
Możliwe przyczyny preferowania formuły innej niż tablicowa są podane w:
Oficjalna strona firmy Microsoft (poszukaj informacji „Wady używania formuł tablicowych”).
Formuły tablicowe mogą wydawać się magiczne, ale mają też pewne wady:
A
wystąpienia D
. Co ja robię źle?
jeśli szukasz w kolumnie (A) użyj:
=INDIRECT("A" & SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A)))))
jeśli Twój zakres to A1: A10, możesz użyć:
=INDIRECT("A" & SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10)))))
w tym wzorze:
SUMPRODUCT(MAX(($A$1:$A10<>"")*(ROW($A$1:$A10))))
zwraca ostatni niepusty numer wiersza, a funkcja pośrednia () zwraca wartość komórki.
=INDEX(A:A, COUNTA(A:A), 1)
zaczerpnięte stąd
=MATCH("*";A1:A10;-1)
dla danych tekstowych
=MATCH(0;A1:A10;-1)
dla danych liczbowych
Wypróbowałem wszystkie nieulotne wersje, ale żadna z wersji podanych powyżej nie zadziałała. Excel 2003/2007 aktualizacja. Z pewnością można to zrobić w programie Excel 2003. Nie jako tablica ani standardowa formuła. Otrzymuję tylko pusty błąd, 0 lub # wartość. Więc uciekam się do niestabilnych metod. To zadziałało.
= WYSZUKAJ (2,1 / (T4: T369 <> ""); T4: T369)
@Julian Kroné .. Używając „;” zamiast „,” NIE działa! Myślę, że używasz Libre Office, a nie MS Excel? LOOKUP jest tak irytująco zmienny, że używam go tylko w ostateczności
Umieść ten kod w module VBA. Zapisać. W obszarze Funkcje, Użytkownik szuka tej funkcji.
Function LastNonBlankCell(Range As Excel.Range) As Variant
Application.Volatile
LastNonBlankCell = Range.End(xlDown).Value
End Function
dla danych tekstowych:
EQUIV("";A1:A10;-1)
dla danych liczbowych:
EQUIV(0;A1:A10;-1)
To daje ci względny indeks ostatniej niepustej komórki w wybranym zakresie (tutaj A1: A10).
Jeśli chcesz uzyskać wartość, uzyskaj do niej dostęp przez POŚREDNIE po zbudowaniu - tekstowo - bezwzględnego odwołania do komórki, np .:
INDIRECT("A" & (nb_line_where_your_data_start + EQUIV(...) - 1))
Ja też miałem ten sam problem. Ta formuła również działa równie dobrze: -
=INDIRECT(CONCATENATE("$G$",(14+(COUNTA($G$14:$G$65535)-1))))
14 to numer pierwszego wiersza w wierszach, które chcesz policzyć.
Przewlekły Clawtooth
Jeśli wiesz, że między nimi nie będzie pustych komórek, najszybszym sposobem jest ta.
=INDIRECT("O"&(COUNT(O:O,"<>""")))
Po prostu zlicza niepuste komórki i odwołuje się do odpowiedniej komórki.
Może być również używany do określonego zakresu.
=INDIRECT("O"&(COUNT(O4:O34,"<>""")+3))
Zwraca ostatnią niepustą komórkę z zakresu O4: O34.
Myślę, że odpowiedź W5ALIVE jest najbliższa temu, czego używam do znalezienia ostatniego wiersza danych w kolumnie. Zakładając, że szukam ostatniego wiersza z danymi w kolumnie A, użyłbym następujących dla bardziej ogólnego wyszukiwania:
=MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0))
Pierwsza PODAJ.POZYCJĘ wyszuka ostatnią komórkę tekstową, a druga PODAJ.POZYCJĘ wyszuka ostatnią komórkę numeryczną. Funkcja JEŻELI.BŁĄD zwraca zero, jeśli pierwsze PODAJ.POZYCJĘ znajdzie wszystkie komórki liczbowe lub jeśli drugie dopasowanie znajdzie wszystkie komórki tekstowe.
Zasadniczo jest to niewielka odmiana rozwiązania mieszanego tekstu i liczb W5ALIVE.
Podczas testowania synchronizacji było to znacznie szybsze niż równoważne warianty LOOKUP.
Aby zwrócić rzeczywistą wartość ostatniej komórki, wolę używać pośrednich odwołań do komórek w następujący sposób:
=INDIRECT("A"&MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9.99999999999999E+307,A:A,1),0)))
Metoda oferowana przez sancho.s jest prawdopodobnie czystszą opcją, ale zmodyfikowałbym część, która znajduje numer wiersza, do tego:
=INDEX(MAX((A:A<>"")*(ROW(A:A))),1)
jedyną różnicą jest to, że „, 1” zwraca pierwszą wartość, a „, 0” zwraca całą tablicę wartości (wszystkie oprócz jednej nie są potrzebne). Nadal preferuję adresowanie komórki do funkcji indeksu, innymi słowy, zwracanie wartości komórki za pomocą:
=INDIRECT("A"&INDEX(MAX((A:A<>"")*(ROW(A:A))),1))
Świetny wątek!
Jeśli nie boisz się używać tablic, oto bardzo prosta formuła rozwiązania problemu:
= SUMA (JEŻELI (A: A <> ""; 1,0))
Musisz nacisnąć klawisze CTRL + SHIFT + ENTER, ponieważ jest to formuła tablicowa.
OK, więc miałem ten sam problem co pytający i wypróbowałem obie najlepsze odpowiedzi. Ale tylko błędy w formułach. Okazało się, że muszę zamienić "," na ";" aby formuły działały. Używam XL 2007.
Przykład:
=LOOKUP(2;1/(A:A<>"");A:A)
lub
=INDEX(A:A;MAX((A:A<>"")*(ROW(A:A))))
W przypadku śledzenia wersji (dodawanie litery v na początku numeru) uznałem, że ten działa dobrze w Xcelsius (SAP Dashboards)
="v"&MAX(A2:A500)