Selecting the last value of a column

问题:

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

Something like:

=LAST(G2:G9999)

except that LAST isn't a function.

回答1:

So this solution takes a string as its parameter. It finds how many rows are in the sheet. It gets all the values in the column specified. It loops through the values from the end to the beginning until it finds a value that is not an empty string. Finally it retunrs the value.

Script:

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];
}

Usage:

=lastValue("G")

EDIT:

In response to the comment asking for the function to update automatically:

The best way I could find is to use this with the code above:

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

It would no longer be required to use the function in a cell like the Usage section states. Instead you are hard coding the cell you would like to update and the column you would like to track. It is possible that there is a more eloquent way to implement this (hopefully one that is not hard coded), but this is the best I could find for now.

Note that if you use the function in cell like stated earlier, it will update upon reload. Maybe there is a way to hook into onEdit() and force in cell functions to update. I just can't find it in the documentation.

回答2:

Similar answer to caligari's answer, but we can tidy it up by just specifying the full column range:

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

回答3:

Actually I found a simpler solution here:

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

It looks like this:

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

回答4:

LAST() function is not implemented at the moment in order to select the last cell within a range. However, following your example:

=LAST(G2:G9999)

we are able to obtain last cell using the couple of functions INDEX() and COUNT() in this way:

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

There is a live example at the spreedsheet where I have found (and solved) the same problem (sheet Orzamentos, cell I5). Note that it works perfectly even refering to other sheets within the document.

回答5:

Summary:

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

Details:

I've looked through and tried several answers, and here's what I've found: The simplest solution (see Dohmoose' answer) works if there are no blanks:

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

If you have blanks, it fails.

You can handle one blank by just changing from COUNT to COUNTA (See user3280071's answer):

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

However, this will fail for some combinations of blanks. (1 blank 1 blank 1 fails for me.)

The following code works (See Nader's answer and jason's comment):

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

but it requires thinking about whether you want to use COLUMNS or ROWS for a given range.

However, if COLUMNS is replaced with COUNT I seem to get a reliable, blank-proof implementation of LAST:

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

And since COUNTA has the filter built in, we can simplify further using

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

This is somewhat simple, and correct. And you don't have to worry about whether to count rows or columns. And unlike script solutions, it automatically updates with changes to the spreadsheet.

And if you want to get the last value in a row, just change the data range:

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

回答6:

In order to return the last value from a column of text values you need to use COUNTA, so you would need this formula:

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

回答7:

try this: =INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))

Suppose the column in which you are looking for the last value is B.

And yes, it works with blanks.

回答8:

It looks like Google Apps Script now supports ranges as function parameters. This solution accepts a range:

// 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;
}

Also see discussion in Google Apps Script help forum: How do I force formulas to recalculate?

回答9:

I looked at the previous answers and they seem like they're working too hard. Maybe scripting support has simply improved. I think the function is expressed like this:

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

In my spreadsheet I then use:

= lastValue(E17:E999)

In the function, I get an array of values with one per referenced cell and this just iterates from the end of the array backwards until it finds a non-empty value or runs out of elements. Sheet references should be interpreted before the data is passed to the function. Not fancy enough to handle multi-dimensions, either. The question did ask for the last cell in a single column, so it seems to fit. It will probably die on if you run out of data, too.

Your mileage may vary, but this works for me.

回答10:

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

no hard coding.

回答11:

This one works for me:

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

回答12:

This gets the last value and handles empty values:

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

回答13:

In a column with blanks, you can get the last value with

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

回答14:

The answer

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

doesn't work correctly in LibreOffice. However, with a small change, it works perfectly.

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

It always works only if the true range is smaller than (G2:G10000)

回答15:

Is it acceptable to answer the original question with a strictly off topic answer:) You can write a formula in the spreadsheet to do this. Ugly perhaps? but effective in the normal operating of a spreadsheet.

=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

This is offered as a thought for a range of questions in the script area that could be delivered reliably with array formulas which have the advantage of often working in similar fashion in excel and openoffice.

回答16:

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());

回答17:

I was playing with the code given by @tinfini, and thought people might benefit from what I think is a slightly more elegant solution (note I don't think scripts worked quite the same way when he created the original answer)...

//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]);
}

In usage it would look like this:

=LastInRange(D2:D)

回答18:

Regarding @Jon_Schneider's comment, if the column has blank cells just use COUNTA()

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

回答19:

I found another way may be it will help you

=INDEX( SORT( A5:D ; 1 ; FALSE) ; 1 ) -will return last row

More info from anab here: https://groups.google.com/forum/?fromgroups=#!topic/How-to-Documents/if0_fGVINmI

回答20:

Found a slight variation that worked to eliminate blanks from the bottom of the table. =index(G2:G,COUNTIF(G2:G,"<>"))

回答21:

I'm surprised no one had ever given this answer before. But this should be the shortest and it even works in excel :

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

G2:G<>"" creates a array of 1/true(1) and 1/false(0). Since LOOKUP does a top down approach to find 2 and Since it'll never find 2,it comes up to the last non blank row and gives the position of that.

The other way to do this, as others might've mentioned, is:

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

Finding the MAXimum ROW of the non blank row and feeding it to INDEX

In a zero blank interruption array, Using INDIRECT RC notation with COUNTBLANK is another option. If V4:V6 is occupied with entries, then,

V18:

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

will give the position of V6.

回答22:

to get the last value from a column you can also use MAX function with IF function

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