Wybór ostatniej wartości kolumny

Mam arkusz kalkulacyjny z niektórymi wartościami w kolumnie G. Niektóre komórki są puste między nimi i muszę przenieść ostatnią wartość z tej kolumny do innej komórki.

Coś jak:

=LAST(G2:G9999)

ale LASTto nie jest funkcja.

Więc to rozwiązanie przyjmuje łańcuch jako parametr. Znajduje liczbę wierszy w arkuszu. Pobiera wszystkie wartości w określonej kolumnie. Przechodzi przez wartości od końca do początku, dopóki nie znajdzie wartości, która nie jest pustym ciągiem. Wreszcie odzyskuje wartość.

Scenariusz:

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return values[lastRow - 1];
}

Stosowanie:

=lastValue("G")

EDYTOWAĆ:

W odpowiedzi na komentarz z prośbą o automatyczną aktualizację funkcji:

Najlepszym sposobem, jaki mogłem znaleźć, jest użycie tego z powyższym kodem:

function onEdit(event) {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue(lastValue("G"));
}

Nie byłoby już wymagane używanie funkcji w komórce, tak jak w sekcji Użycie . Zamiast tego na sztywno kodujesz komórkę, którą chcesz zaktualizować, i kolumnę, którą chcesz śledzić. Możliwe, że istnieje bardziej elokwentny sposób na wdrożenie tego (miejmy nadzieję, że nie jest on zakodowany na stałe), ale jest to najlepszy, jaki na razie znalazłem.

Zauważ, że jeśli użyjesz funkcji w komórce, jak wspomniano wcześniej, zaktualizuje się ona po ponownym załadowaniu. Może jest sposób na podłączenie się onEdit()i wymuszenie aktualizacji funkcji komórki. Po prostu nie mogę znaleźć tego w dokumentacji.

Podobna odpowiedź do odpowiedzi Caligari , ale możemy to uporządkować, po prostu określając pełny zakres kolumn:

=INDEX(G2:G, COUNT(G2:G))

Właściwie znalazłem tutaj prostsze rozwiązanie:

http://www.google.com/support/forum/p/Google+Docs/thread?tid=20f1741a2e663bca&hl=en

To wygląda tak:

=FILTER( A10:A100 , ROW(A10:A100) =MAX( FILTER( ArrayFormula(ROW(A10:A100)) , NOT(ISBLANK(A10:A100)))))

Funkcja LAST () nie jest obecnie zaimplementowana w celu wybrania ostatniej komórki w zakresie. Jednak idąc za przykładem:

=LAST(G2:G9999)

jesteśmy w stanie uzyskać ostatnią komórkę za pomocą kilku funkcji INDEX () i COUNT () w ten sposób:

=INDEX(G2:G; COUNT(G2:G))

W arkuszu spreedsheet znajduje się przykład na żywo, w którym znalazłem (i rozwiązałem) ten sam problem (arkusz Orzamentos, komórka I5). Zwróć uwagę, że działa idealnie nawet w odniesieniu do innych arkuszy w dokumencie.

Podsumowanie:

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNTA(G2:G) )

Detale:

Przejrzałem i wypróbowałem kilka odpowiedzi i oto, co znalazłem: Najprostsze rozwiązanie (patrz odpowiedź Dohmoose ) działa, jeśli nie ma żadnych spacji:

=INDEX(G2:G; COUNT(G2:G))

Jeśli masz puste miejsca, nie powiedzie się.

Możesz obsłużyć jeden pusty, zmieniając po prostu z COUNTna COUNTA (patrz odpowiedź użytkownika3280071 ):

=INDEX(G2:G; COUNTA(G2:G))

Jednak to się nie powiedzie w przypadku niektórych kombinacji spacji. ( 1 blank 1 blank 1mi się nie udaje.)

Poniższy kod działa (zobacz odpowiedź Nadera i komentarz Jasona ):

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , ROWS( FILTER( G2:G , NOT(ISBLANK(G2:G)) ) ) )

ale wymaga zastanowienia się, czy chcesz użyć, COLUMNSczy ROWSdla danego zakresu.

Jeśli jednak COLUMNSzostanie zastąpione przez COUNT, wydaje się, że otrzymuję niezawodną, ​​odporną na puste implementację LAST:

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNT( FILTER( G2:G , NOT(ISBLANK(G2:G)) ) ) ) 

A ponieważ COUNTAma wbudowany filtr, możemy uprościć dalsze korzystanie

=INDEX( FILTER( G2:G , NOT(ISBLANK(G2:G))) , COUNTA(G2:G) )

Jest to dość proste i poprawne. I nie musisz się martwić, czy liczyć wiersze, czy kolumny. W przeciwieństwie do rozwiązań skryptowych aktualizuje się automatycznie wraz ze zmianami w arkuszu kalkulacyjnym.

A jeśli chcesz uzyskać ostatnią wartość w wierszu, po prostu zmień zakres danych:

=INDEX( FILTER( A2:2 , NOT(ISBLANK(A2:2))) , COUNTA(A2:2) )

Aby zwrócić ostatnią wartość z kolumny wartości tekstowych, musisz użyć COUNTA, więc potrzebujesz tej formuły:

=INDEX(G2:G; COUNTA(G2:G))

Spróbuj tego: =INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))

Załóżmy, że kolumną, w której szukasz ostatniej wartości, jest B.

I tak, działa ze spacjami.

Wygląda na to, że Google Apps Script obsługuje teraz zakresy jako parametry funkcji. To rozwiązanie akceptuje zakres:

// Returns row number with the last non-blank value in a column, or the first row
//   number if all are blank.
// Example: =rowWithLastValue(a2:a, 2)
// Arguments
//   range: Spreadsheet range.
//   firstRow: Row number of first row. It would be nice to pull this out of
//     the range parameter, but the information is not available.
function rowWithLastValue(range, firstRow) {
  // range is passed as an array of values from the indicated spreadsheet cells.
  for (var i = range.length - 1;  i >= 0;  -- i) {
    if (range[i] != "")  return i + firstRow;
  }
  return firstRow;
}

Zobacz także dyskusję na forum pomocy Google Apps Script: Jak wymusić ponowne obliczenie formuł?

Spojrzałem na poprzednie odpowiedzi i wyglądało na to, że pracują zbyt ciężko. Może po prostu poprawiła się obsługa skryptów. Myślę, że funkcja jest wyrażona w ten sposób:

function lastValue(myRange) {
    lastRow = myRange.length;
    for (; myRange[lastRow - 1] == "" && lastRow > 0; lastRow--)
    { /*nothing to do*/ }
    return myRange[lastRow - 1];
}

W moim arkuszu kalkulacyjnym używam:

= lastValue(E17:E999)

W funkcji otrzymuję tablicę wartości z jedną na komórkę, do której istnieje odwołanie, i to po prostu iteruje od końca tablicy do tyłu, aż znajdzie niepustą wartość lub zabraknie elementów. Odniesienia do arkuszy powinny być interpretowane przed przekazaniem danych do funkcji. Nie na tyle wyszukane, aby obsługiwać wiele wymiarów. Pytanie dotyczyło ostatniej komórki w jednej kolumnie, więc wydaje się, że pasuje. Prawdopodobnie umrze, jeśli zabraknie też danych.

Twój przebieg może się różnić, ale to działa dla mnie.

function lastRow(column){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = sheet.getLastRow();
  var lastRowRange=sheet.getRange(column+startRow);
  return lastRowRange.getValue();
}

bez twardego kodowania.

Ten działa dla mnie:

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

Pobiera ostatnią wartość i obsługuje puste wartości:

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

W kolumnie ze spacjami można uzyskać ostatnią wartość za pomocą

=+sort(G:G,row(G:G)*(G:G<>""),)

Odpowiedź

$ =INDEX(G2:G; COUNT(G2:G))

nie działa poprawnie w LibreOffice. Jednak z niewielką zmianą działa idealnie.

$ =INDEX(G2:G100000; COUNT(G2:G100000))

Działa zawsze tylko wtedy, gdy rzeczywisty zakres jest mniejszy niż (G2:G10000)

Czy można odpowiedzieć na pierwotne pytanie, udzielając odpowiedzi ściśle spoza tematu :) Możesz w tym celu napisać wzór w arkuszu kalkulacyjnym. Może brzydki? ale skuteczne w normalnym działaniu arkusza kalkulacyjnego.

=indirect("R"&ArrayFormula(max((G:G<>"")*row(G:G)))&"C"&7)


(G:G<>"") gives an array of true false values representing non-empty/empty cells
(G:G<>"")*row(G:G) gives an array of row numbers with zeros where cell is empty
max((G:G<>"")*row(G:G)) is the last non-empty cell in G

Jest to przemyślenie na szereg pytań w obszarze skryptów, które można wiarygodnie dostarczyć za pomocą formuł tablicowych, które mają tę zaletę, że często działają w podobny sposób w programie Excel i Openoffice.

function getDashboardSheet(spreadsheet) {
  var sheetName = 'Name';
  return spreadsheet.getSheetByName(sheetName);
}
      var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);  
      var dashboardSheet = getDashboardSheet(spreadsheet);
      Logger.log('see:'+dashboardSheet.getLastRow());

Bawiłem się kodem podanym przez @tinfini i pomyślałem, że ludzie mogą skorzystać z tego, co uważam za nieco bardziej eleganckie rozwiązanie (uwaga, nie sądzę, aby skrypty działały tak samo, gdy tworzył oryginalną odpowiedź) ...

//Note that this function assumes a single column of values, it will 
//not  function properly if given a multi-dimensional array (if the 
//cells that are captured are not in a single row).

function LastInRange(values) 
{
  for (index = values.length - 1; values[index] == "" && index > 0; index--) {}
  return String(values[index]);
}

W użyciu wyglądałoby to tak:

=LastInRange(D2:D)

Odnośnie komentarza @ Jon_Schneider, jeśli kolumna ma puste komórki, po prostu użyj COUNTA ()

=INDEX(G2:G; COUNT**A**(G2:G))

Znalazłem inny sposób, może ci to pomoże

=INDEX( SORT( A5:D ; 1 ; FALSE) ; 1 ) -wróci ostatni wiersz

Więcej informacji z anab tutaj: https://groups.google.com/forum/?fromgroups=#!topic/How-to-Documents/if0_fGVINmI

Znalazłem niewielką zmianę, która działała, aby wyeliminować puste miejsca na dole tabeli. = indeks (G2: G, LICZ.JEŻELI (G2: G; „<>”))

Dziwię się, że nikt wcześniej nie udzielił takiej odpowiedzi. Ale to powinno być najkrótsze i działa nawet w programie Excel:

=ARRAYFORMULA(LOOKUP(2,1/(G2:G<>""),G2:G))

G2:G<>""tworzy tablicę 1 / prawda (1) i 1 / fałsz (0). Ponieważ LOOKUPstosuje podejście odgórne w celu znalezienia 2i Ponieważ nigdy nie znajdzie 2, dochodzi do ostatniego niepustego wiersza i podaje jego pozycję.

Innym sposobem, aby to zrobić, jak mogli wspomnieć inni, jest:

=INDEX(G2:G,MAX((ISBLANK(G2:G)-1)*-ROW(G2:G))-1)

Znalezienie MAXimum ROWniepustego wiersza i podanie goINDEX

W tablicy z zerową pustą przerwaniem kolejną opcją jest użycie INDIRECT RCnotacji z COUNTBLANK. Jeśli V4: V6 jest zajęty wpisami, to

V18 :

=INDIRECT("R[-"&COUNTBLANK(V4:V17)+1&"]C",0)

poda pozycję V6.

aby uzyskać ostatnią wartość z kolumny, możesz również użyć MAXfunkcji z IFfunkcją

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