열의 마지막 값 선택

열의 마지막 값 선택

---

G 열에 일부 값이있는 스프레드 시트가 있습니다. 일부 셀은 그 사이에 비어 있으며 해당 열의 마지막 값을 다른 셀로 가져와야합니다.

다음과 같은 것 :

=LAST(G2:G9999)

그것은 LAST함수가 아니라는 것을 제외하고 .

---

따라서이 솔루션은 문자열을 매개 변수로 사용합니다. 시트에있는 행 수를 찾습니다. 지정된 열의 모든 값을 가져옵니다. 빈 문자열이 아닌 값을 찾을 때까지 끝에서 처음으로 값을 반복합니다. 마지막으로 가치를 되돌립니다.

스크립트:

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

용법:

=lastValue("G")

편집하다:

자동 업데이트 기능을 요청하는 댓글에 대한 응답 :

내가 찾을 수있는 가장 좋은 방법은 위의 코드와 함께 사용하는 것입니다.

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

사용 섹션 상태 와 같은 셀에서 더 이상 함수를 사용할 필요가 없습니다 . 대신 업데이트하려는 셀과 추적하려는 열을 하드 코딩합니다. 이를 구현하는 더 웅변적인 방법 (하드 코딩되지 않은 방법)이있을 수 있지만 지금은 이것이 제가 찾을 수있는 최선의 방법입니다.

앞서 언급 한 것과 같이 셀에서 함수를 사용하면 다시로드 할 때 업데이트됩니다. 아마도 onEdit()셀 함수에 연결하여 강제로 업데이트 하는 방법이있을 수 있습니다. 문서에서 찾을 수 없습니다.

---

caligari의 답변에 대한 유사한 답변 이지만 전체 열 범위를 지정하여 정리할 수 있습니다.

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

---

실제로 여기에서 더 간단한 해결책을 찾았습니다.

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

다음과 같이 보입니다.

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

---

범위 내에서 마지막 셀을 선택하기 위해 LAST () 함수는 현재 구현되지 않았습니다. 그러나 귀하의 예를 따르십시오.

=LAST(G2:G9999)

다음 과 같은 방법으로 INDEX () 및 COUNT () 함수를 사용하여 마지막 셀을 얻을 수 있습니다 .

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

spreedsheet에 동일한 문제 (sheet , cell )를 발견하고 해결 한 실제 예제 가 있습니다 . 문서 내의 다른 시트를 참조해도 완벽하게 작동합니다.OrzamentosI5

---

요약:

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

세부:

나는 여러 가지 답변을 검토하고 시도했으며 여기에 내가 찾은 것이 있습니다. 가장 간단한 솔루션 ( Dohmoose의 답변 참조 )은 공백이 없으면 작동합니다.

=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) )

---

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

---

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.

---

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?

---

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.

---

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

no hard coding.

---

This one works for me:

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

---

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

---

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

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

---

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)

---

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.

---

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

---

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)

---

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

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

---

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

---

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

---

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.

---

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

참고URL : https://stackoverflow.com/questions/4169914/selecting-the-last-value-of-a-column