Pobierz ostatnią niepustą komórkę w kolumnie w Arkuszach Google


147

Używam następującej funkcji

=DAYS360(A2, A35)

obliczyć różnicę między dwiema datami w mojej kolumnie. Jednak kolumna stale się rozszerza i obecnie muszę ręcznie zmienić „A35” podczas aktualizowania arkusza kalkulacyjnego.

Czy istnieje sposób (w Arkuszach Google), aby znaleźć ostatnią niepustą komórkę w tej kolumnie, a następnie dynamicznie ustawić ten parametr w powyższej funkcji?


Odpowiedzi:


178

Może być bardziej wymowny sposób, ale oto, co wymyśliłem:

Funkcja znajdująca ostatnią wypełnioną komórkę w kolumnie to:

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

Więc jeśli połączysz to z obecną funkcją, wyglądałoby to tak:

=DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))

1
Nie przejmuję się elokwencją w tym przypadku, o ile działa jak urok (co robi) - wielkie dzięki Sam!
MichaelS

4
Skończyło się na porównaniu z pustym ciągiem zamiast używania ISBLANK, który traktuje niektóre wyglądające na puste komórki (np. Formuły zwracające puste miejsce, takie jak = "", jako niepuste. Zatem: '= index (filter (A: A, A: A <> ""), wiersze (filter (A: A, A: A <> ""))) '
circlepi314

Miałem też problemy z ISBLANK z powodu formuł. Ale używając = INDEKS (FILTR (F3: F; F3: F <> "")); ROWS (FILTER (F3: F; F3: F <> "")) błąd analizy wyników formuły. Masz jakiś pomysł, co jest nie tak?
klor

Pytanie. Jeśli zamiast A: A jest importrange (), w jaki sposób można to przepisać bez czterokrotnego wykonania tej samej metody importrange ()?
Irina Rapoport

4
Nieco uproszczona odpowiedź, która również obsługuje spacje, autorstwa Douga Bradshawa: =INDEX(FILTER(A1:A,NOT(ISBLANK(A1:A))),COUNTA(A1:A))(można zmienić wiersz początkowy A1) pełny opis pod adresem: stackoverflow.com/a/27623407/539149
Zack Morris

83

Aby znaleźć ostatnią niepustą komórkę, możesz użyć INDEXi MATCHfunkcji takich jak:

=DAYS360(A2; INDEX(A:A; MATCH(99^99;A:A; 1)))

Myślę, że jest to trochę szybsze i łatwiejsze.


1
To jest prostsze i przetestowałem to. Działa świetnie.
Ciaran

26
Twoja metoda wydaje się znajdować tylko ostatnią komórkę, jeśli wartość jest liczbą, moja metoda znajdzie ostatni wiersz, jeśli jest również typu string. Pierwotnie pytanie dotyczyło dat, więc to zadziała, ale ponieważ artykuł wciąż przyciąga uwagę, warto zauważyć różnicę. Wszystko zależy od Twoich potrzeb. Jeśli potrzebujesz tylko numeru, polecam ten sposób.
Sam Plus Plus

1
Zakłada się, że kolumna jest posortowana
Andrej Adamenko

Również dało się zrobić jako=DAYS360(A2;VLOOKUP(99^99;A:A;1))
vstepaniuk

56

Jeśli A2: A zawiera daty ciągłe, to INDEKS (A2: A, LICZBA (A2: A)) zwróci ostatnią datę. Ostateczna formuła to

=DAYS360(A2,INDEX(A2:A,COUNT(A2:A)))

9
Osobiście uważam, że to powinna być akceptowana odpowiedź. Zwięzłe i proste.
SwampThingTom

4
To jest najdokładniejsza odpowiedź. Podczas gdy powyższa odpowiedź @Poula działa w tym przypadku, chciałem znaleźć ostatnią komórkę z rzeczywistymi danymi, a to sprawdza, czy dane są w porządku, czy nie.
Chuck Claunch

4
To jest doskonałe. Po drugie wniosek, aby była to akceptowana odpowiedź.
shiri,

37

Moim ulubionym jest:

=INDEX(A2:A,COUNTA(A2:A),1)

Tak więc na potrzeby PO:

=DAYS360(A2,INDEX(A2:A,COUNTA(A2:A),1))

... chociaż "MAX", który napisał Poul powyżej, wygląda czysto w przypadku, gdy ostatnia data jest zawsze najpóźniejsza.
Eric Smalling

1
Miał na myśli +1 nie, 1
Newbrict

1
To nie odpowiada na pytanie - wybiera ostatnią wartość, ale nie ostatnią niepustą wartość.
NateS

17

Jeśli kolumna rozszerzyła się tylko o kolejne dodane daty, tak jak w moim przypadku - użyłem tylko funkcji MAX, aby uzyskać ostatnią datę.

Ostateczna formuła będzie wyglądać następująco:

=DAYS360(A2; MAX(A2:A)) 

3
Geniusz! Proste i skuteczne.
Asu

16

Chociaż odpowiedź na to pytanie jest już udzielona, ​​istnieje wymowny sposób, aby to zrobić.

Use just the column name to denote last non-empty row of that column.

Na przykład:

Jeśli Twoje dane są dostępne A1:A100i chcesz mieć możliwość dodania większej ilości danych do kolumny A, powiedzmy, że może być A1:A105lub nawet A1:A1234później, możesz użyć tego zakresu:

A1:A

Próba

Aby otrzymać ostatnią niepustą wartość w zakresie, użyjemy 2 funkcji:

  • COUNTA
  • INDEKS

Odpowiedź jest =INDEX(B3:B,COUNTA(B3:B)).

Oto wyjaśnienie:

COUNTA(range) zwraca liczbę wartości w zakresie, możemy to wykorzystać, aby uzyskać liczbę wierszy.

INDEX(range, row, col)zwraca wartość z zakresu na pozycji rowi col( col=1jeśli nie została określona)

Przykłady:

INDEX(A1:C5,1,1) = A1
INDEX(A1:C5,1) = A1 # implicitly states that col = 1
INDEX(A1:C5,1,2) = A2
INDEX(A1:C5,2,1) = B1
INDEX(A1:C5,2,2) = B2
INDEX(A1:C5,3,1) = C1
INDEX(A1:C5,3,2) = C2

Na powyższym zdjęciu nasz zakres będzie B3:B. Więc będziemy liczyć ile istnieją wartości w przedziale B3:Bod COUNTA(B3:B)pierwszego. Po lewej stronie będzie produkować, 8ponieważ istnieje 8 wartości, a 9po prawej stronie. Wiemy również, że ostatnia wartość znajduje się w pierwszej kolumnie zakresu, B3:Bwięc colparametr INDEXmusi wynosić 1, a rowparametr powinien być COUNTA(B3:B).

PS : proszę, zagłosuj na odpowiedź @ bloodymurderlive, ponieważ napisał ją pierwszy, tylko wyjaśniam to tutaj.


1
Podoba mi się to, ale zauważyłem problemy, w których czasami zwraca przedostatnią pozycję zamiast ostatniej.
Eric Smalling

1
@EricSmalling to może się zdarzyć, ponieważ masz jeden dodatkowy wiersz na końcu wszystkich wierszy, a kiedy go kopiujesz i wklejasz, może to zostać uznane za niepustą komórkę. Czy masz jakiś przykładowy arkusz kalkulacyjny ujawniający ten problem?
Ramazan Polat

Wygląda na najmądrzejsze rozwiązanie!
MemLeak

To nie działa. Skąd przy okazji otrzymałeś wycenę? Myślałem, że to z dokumentacji arkusza Google.
Atul

1
@RamazanPolat: OP pyta sposób (w Arkuszach Google), aby znaleźć ostatnią niepustą komórkę w kolumnie A1:A nie daje mi ostatniej niepustej wartości komórki w kolumnie A
Atul

8

Oto kolejny:

=indirect("A"&max(arrayformula(if(A:A<>"",row(A:A),""))))

Ostateczne równanie jest następujące:

=DAYS360(A2,indirect("A"&max(arrayformula(if(A:A<>"",row(A:A),"")))))

Inne równania tutaj działają, ale podoba mi się to, ponieważ ułatwia uzyskanie numeru wiersza, co wydaje mi się, że muszę robić częściej. Tylko numer wiersza wyglądałby tak:

=max(arrayformula(if(A:A<>"",row(A:A),"")))

Początkowo próbowałem znaleźć to, aby rozwiązać problem z arkuszem kalkulacyjnym, ale nie mogłem znaleźć niczego przydatnego, co po prostu podało numer wiersza ostatniego wpisu, więc mam nadzieję, że jest to pomocne dla kogoś.

Ma to również tę dodatkową zaletę, że działa dla dowolnego typu danych w dowolnej kolejności, a między wierszami z zawartością mogą znajdować się puste wiersze, a nie zlicza komórek z formułami, których wynikiem jest „”. Może również obsługiwać powtarzające się wartości. Podsumowując, jest bardzo podobne do równania, które używa tutaj max ((G: G <> "") * row (G: G)), ale sprawia, że ​​wyciągnięcie numeru wiersza jest trochę łatwiejsze, jeśli tego szukasz .

Alternatywnie, jeśli chcesz umieścić skrypt na swoim arkuszu, możesz sobie to ułatwić, jeśli planujesz dużo to robić. Oto ten fragment:

function lastRow(sheet,column) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (column == null) {
    if (sheet != null) {
       var sheet = ss.getSheetByName(sheet);
    } else {
      var sheet = ss.getActiveSheet();
    }
    return sheet.getLastRow();
  } else {
    var sheet = ss.getSheetByName(sheet);
    var lastRow = sheet.getLastRow();
    var array = sheet.getRange(column + 1 + ':' + column + lastRow).getValues();
    for (i=0;i<array.length;i++) {
      if (array[i] != '') {       
        var final = i + 1;
      }
    }
    if (final != null) {
      return final;
    } else {
      return 0;
    }
  }
}

Tutaj możesz po prostu wpisać następujące polecenie, jeśli chcesz, aby ostatni wiersz znajdował się w tym samym arkuszu, który obecnie edytujesz:

=LASTROW()

lub jeśli chcesz mieć ostatni wiersz określonej kolumny z tego arkusza lub określonej kolumny z innego arkusza, możesz wykonać następujące czynności:

=LASTROW("Sheet1","A")

I ogólnie dla ostatniego wiersza konkretnego arkusza:

=LASTROW("Sheet1")

Następnie, aby uzyskać rzeczywiste dane, możesz użyć pośredniego:

=INDIRECT("A"&LASTROW())

lub możesz zmodyfikować powyższy skrypt w ostatnich dwóch wierszach powrotu (ostatnie dwa, ponieważ musiałbyś umieścić zarówno arkusz, jak i kolumnę, aby uzyskać rzeczywistą wartość z rzeczywistej kolumny) i zastąpić zmienną następującą:

return sheet.getRange(column + final).getValue();

i

return sheet.getRange(column + lastRow).getValue();

Jedną z zalet tego skryptu jest to, że możesz wybrać, czy chcesz uwzględnić równania, których wynikiem jest „”. Jeśli nie zostaną dodane żadne argumenty, równania obliczane na „” zostaną policzone, ale jeśli określisz arkusz i kolumnę, zostaną zliczone. Ponadto istnieje duża elastyczność, jeśli chcesz używać odmian skryptu.

Prawdopodobnie przesada, ale wszystko możliwe.


1
„Nieznana funkcja OSTATNIA”
Berit Larsen,

Powinno być +100 za faktyczną odpowiedź na nagłówek pytania: Ostatni wiersz, a nie wartość w ostatnim wierszu
Norbert van Nobelen

6

A co z tą formułą na uzyskanie ostatniej wartości:

=index(G:G;max((G:G<>"")*row(G:G)))

A to byłaby ostateczna formuła twojego pierwotnego zadania:

=DAYS360(G10;index(G:G;max((G:G<>"")*row(G:G))))

Załóżmy, że data początkowa to G10.


5

Poszedłem inną drogą. Ponieważ wiem, że będę dodawał coś do wiersza / kolumny jeden po drugim, znajduję ostatni wiersz, licząc najpierw pola, które zawierają dane. Pokażę to za pomocą kolumny:

=COUNT(A5:A34)

Więc powiedzmy, że zwrócone 21. A5 jest o 4 wiersze w dół, więc muszę uzyskać 21. pozycję od czwartego wiersza w dół. Mogę to zrobić używając inderect, na przykład:

=INDIRECT("A"&COUNT(A5:A34)+4)

Znajduje liczbę wierszy z danymi i zwraca liczbę, której używam jako modyfikator indeksu.


1
Zauważ, że nie może być pustych komórek. Ponadto COUNTA powinien pasować do większej liczby scenariuszy
Bernardo Dal Corno

4

dla rzędu:

=ARRAYFORMULA(INDIRECT("A"&MAX(IF(A:A<>"", ROW(A:A), ))))

dla kolumny:

=ARRAYFORMULA(INDIRECT(ADDRESS(1, MAX(IF(1:1<>"", COLUMN(1:1), )), 4)))

1
Doskonała odpowiedź. Dziękuję Ci.
Nick

3

Wydaje się, że jest to najprostsze rozwiązanie, jakie znalazłem, aby pobrać ostatnią wartość w stale rozwijającej się kolumnie:

=INDEX(A:A,COUNTA(A:A),1)

3

To działa dla mnie. Pobierz ostatnią wartość kolumny A w arkuszu Google:

=index(A:A,max(row(A:A)*(A:A<>"")))

(Pomija również puste wiersze między, jeśli istnieją)



1

To da zawartość ostatniej komórki:

=indirect("A"&max(ARRAYFORMULA(row(a:a)*--(a:a<>""))))

To da adres ostatniej komórki:

="A"&max(ARRAYFORMULA(row(a:a)*--(a:a<>"")))

To da wiersz ostatniej komórki:

=max(ARRAYFORMULA(row(a:a)*--(a:a<>"")))

Może wolisz scenariusz. Ten skrypt jest znacznie krótszy niż ogromny, który opublikował powyżej ktoś inny:

Przejdź do edytora skryptów i zapisz ten skrypt:

function getLastRow(range){
  while(range.length>0 && range[range.length-1][0]=='') range.pop();
  return range.length;
}

Po wykonaniu tej czynności wystarczy wpisać to w komórce:

=getLastRow(A:A)

0

Sposób, w jaki robi to amator, to „= CONCATENATE („ A ”, COUNTUNIQUE (A1: A9999))”, gdzie A1 to pierwsza komórka w kolumnie, a A9999 znajduje się niżej w tej kolumnie, niż kiedykolwiek spodziewałem się, że będą miały jakiekolwiek wpisy. Ten wynikowy znak A # może być używany w razie potrzeby z funkcją POŚREDNI.


Przepraszam. Działa to tylko wtedy, gdy wszystkie wpisy w kolumnie są unikalne.
Conrad Lindes

1
Choć doceniam twój wkład w StackOverflow, Conrad, twoja odpowiedź zawiera pewne problemy. Po pierwsze, nie określił, że daty są unikalne, więc powinieneś użyć count () zamiast countunique (). Po drugie, użycie funkcji pośredniej () i konkatenacji powiela istniejącą funkcjonalność index (), jak widać w innych odpowiedziach. Po trzecie, zamiast A1: A9999, dlaczego nie użyć po prostu A1: A?
sondra.kinsey

0

Aby znaleźć ostatni niepusty numer wiersza (dopuszczając spacje między nimi) użyłem poniżej do wyszukiwania kolumny A.

=ArrayFormula(IFNA(match(2,1/(A:A<>""))))
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.