google-apps-script script - Selezione dell'ultimo valore di una colonna





sheets scripting (17)


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

nessuna codifica difficile.

Ho un foglio di calcolo con alcuni valori nella colonna G. Alcune celle sono vuote in mezzo e ho bisogno di ottenere l'ultimo valore da quella colonna in un'altra cella.

Qualcosa di simile a:

=LAST(G2:G9999)

tranne che LAST non è una funzione.




Ho guardato le risposte precedenti e sembra che stiano lavorando troppo duramente. Forse il supporto per gli script è semplicemente migliorato. Penso che la funzione sia espressa in questo modo:

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

Nel mio foglio di calcolo, quindi, utilizzo:

= lastValue(E17:E999)

Nella funzione, ottengo un array di valori con uno per ogni cella di riferimento e questo esegue semplicemente un'iterazione dalla fine dell'array all'indietro finché non trova un valore non vuoto o esaurisce elementi. I riferimenti del foglio devono essere interpretati prima che i dati vengano passati alla funzione. Non abbastanza elegante da gestire anche le multi-dimensioni. La domanda chiedeva l'ultima cella in una singola colonna, quindi sembra adattarsi. Probabilmente morirà se si esauriranno anche i dati.

Il tuo chilometraggio può variare, ma questo funziona per me.




Sommario:

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

Dettagli:

Ho esaminato e provato diverse risposte, ed ecco cosa ho trovato: la soluzione più semplice (vedere la risposta di Dohmoose ) funziona se non ci sono spazi vuoti:

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

Se hai spazi vuoti, fallisce.

Puoi gestire uno spazio vuoto cambiando semplicemente da COUNT a COUNTA (Vedi la risposta dell'utente3280071 ):

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

Tuttavia, questo fallirà per alcune combinazioni di spazi vuoti. ( 1 blank 1 blank 1 non riesce per me.)

Il seguente codice funziona (vedi la risposta di Nader e il commento di jason ):

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

ma richiede di pensare se si desidera utilizzare COLUMNS o ROWS per un determinato intervallo.

Tuttavia, se COLUMNS viene sostituito con COUNT mi sembra di ottenere un'implementazione affidabile e a prova di vuoto di LAST :

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

E poiché COUNTA ha il filtro integrato, possiamo semplificare ulteriormente l'utilizzo

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

Questo è piuttosto semplice e corretto. E non devi preoccuparti se contare le righe o le colonne. E a differenza delle soluzioni di script, si aggiorna automaticamente con le modifiche al foglio di calcolo.

E se vuoi ottenere l'ultimo valore in una riga, modifica l'intervallo di dati:

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



Per poter restituire l'ultimo valore da una colonna di valori di testo devi usare COUNTA, quindi avresti bisogno di questa formula:

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



Questo funziona per me:

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



Sembra che Google Apps Script ora supporti intervalli come parametri di funzione. Questa soluzione accetta un intervallo:

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

Consulta anche la discussione nel forum di assistenza di Google Apps Script: come posso forzare il ricalcolo delle formule?




Quindi questa soluzione prende una stringa come parametro. Trova quante righe ci sono nel foglio. Ottiene tutti i valori nella colonna specificata. Passa attraverso i valori dalla fine all'inizio fino a quando non trova un valore che non è una stringa vuota. Alla fine restituisce il valore.

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

Uso:

=lastValue("G")

MODIFICARE:

In risposta al commento che richiede l'aggiornamento automatico della funzione:

Il modo migliore che ho trovato è di usare questo con il codice qui sopra:

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

Non sarebbe più necessario utilizzare la funzione in una cella come gli stati della sezione Uso . Invece stai codificando con difficoltà la cella che desideri aggiornare e la colonna che desideri monitorare. È possibile che esista un modo più eloquente per implementarlo (si spera che non sia codificato con difficoltà), ma per ora è il massimo che riesco a trovare.

Si noti che se si utilizza la funzione nella cella come indicato in precedenza, verrà aggiornata al momento della ricarica. Forse c'è un modo per collegarsi a onEdit() e forzare le funzioni di cella per l'aggiornamento. Non riesco a trovarlo nella documentazione.







Sono sorpreso che nessuno abbia mai dato questa risposta prima. Ma questo dovrebbe essere il più breve e funziona anche in Excel:

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

G2:G<>"" crea una matrice di 1 / true (1) e 1 / false (0). Poiché LOOKUP esegue un approccio dall'alto verso il basso per trovare 2 e Siccome non troverà mai 2, arriva fino all'ultima riga non vuota e ne fornisce la posizione.

L'altro modo per farlo, come altri potrebbero aver menzionato, è:

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

Trovare il massimo della riga vuota della riga non vuota e alimentarlo in INDEX

In una matrice di interruzione di zero vuoti, l'uso della notazione INDIRECT RC con COUNTBLANK è un'altra opzione. Se V4: V6 è occupato con voci, quindi,

V18 :

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

darà la posizione di V6.




Stavo giocando con il codice dato da @tinfini, e pensavo che le persone potessero trarre beneficio da quella che penso sia una soluzione leggermente più elegante (nota che non penso che gli script abbiano funzionato allo stesso modo quando ha creato la risposta originale) ...

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

Nell'uso sarebbe simile a questo:

=LastInRange(D2:D)



prova questo: =INDIRECT("B"&arrayformula(max((B3:B<>"")*row(B3:B))))

Supponiamo che la colonna in cui stai cercando l'ultimo valore sia B.

E sì, funziona con spazi vuoti.




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



La risposta

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

non funziona correttamente in LibreOffice. Tuttavia, con un piccolo cambiamento, funziona perfettamente.

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

Funziona sempre solo se l'intervallo reale è inferiore a (G2:G10000)




La funzione LAST () non è implementata al momento per selezionare l'ultima cella all'interno di un intervallo. Tuttavia, seguendo il tuo esempio:

=LAST(G2:G9999)

siamo in grado di ottenere l'ultima cella utilizzando le due funzioni INDICE () e COUNT () in questo modo:

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

C'è un esempio dal vivo nello spreedsheet dove ho trovato (e risolto) lo stesso problema (foglio Orzamentos , cella I5 ). Si noti che funziona perfettamente anche facendo riferimento ad altri fogli all'interno del documento.




È accettabile rispondere alla domanda originale con una risposta fuori tema rigorosamente :) È possibile scrivere una formula nel foglio di calcolo per fare ciò. Brutto forse? ma efficace nel normale funzionamento di un foglio di calcolo.

=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

Questo è offerto come un pensiero per una serie di domande nell'area dello script che possono essere fornite in modo affidabile con formule di array che hanno il vantaggio di lavorare spesso in modo simile in Excel e OpenOffice.




Per quanto riguarda il commento di @ Jon_Schneider, se la colonna ha celle vuote usa solo COUNTA ()

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



Puoi farlo andando nella direzione opposta. A partire dall'ultima riga nel foglio di calcolo e salendo fino a ottenere un certo valore. Ciò funzionerà in tutti i casi anche se nel frattempo ci sono alcune righe vuote. Il codice si presenta come di seguito:

var iLastRowWithData = lastValue('A');
function lastValue(column) {
  var iLastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var aValues = SpreadsheetApp.getActiveSheet().getRange(column + "2:" + column + lastRow).getValues();

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




google-apps-script google-spreadsheet