Come crei un “reverse pivot” in Fogli Google?

Sto cercando di produrre una funzione “reverse pivot”. Ho cercato a lungo e duramente per una tale funzione, ma non riesco a trovarne una che sia già là fuori.

Ho una tabella riassuntiva con un massimo di 20 colonne e centinaia di righe, tuttavia vorrei convertirlo in un elenco semplice in modo da poter importare in un database (o anche utilizzare i dati flat per creare più tabelle pivot da!)

Quindi, ho dati in questo formato:

| Customer 1 | Customer 2 | Customer 3 ----------+------------+------------+----------- Product 1 | 1 | 2 | 3 Product 2 | 4 | 5 | 6 Product 3 | 7 | 8 | 9 

E devi convertirlo in questo formato:

  Customer | Product | Qty -----------+-----------+---- Customer 1 | Product 1 | 1 Customer 1 | Product 2 | 4 Customer 1 | Product 3 | 7 Customer 2 | Product 1 | 2 Customer 2 | Product 2 | 5 Customer 2 | Product 3 | 8 Customer 3 | Product 1 | 3 Customer 3 | Product 2 | 6 Customer 3 | Product 3 | 9 

Ho creato una funzione che leggerà l’intervallo da sheet1 e aggiungerà le righe riformattate nella parte inferiore dello stesso foglio, tuttavia sto cercando di farlo funzionare in modo che possa avere la funzione su sheet2 che leggerà l’intero intervallo da sheet1 .

Non importa quello che provo, non riesco a farlo funzionare, e mi stavo chiedendo se qualcuno potesse darmi qualche suggerimento?

Ecco cosa ho finora:

 function readRows() { var sheet = SpreadsheetApp.getActiveSheet(); var rows = sheet.getDataRange(); var numRows = rows.getNumRows(); var values = rows.getValues(); heads = values[0] for (var i = 1; i <= numRows - 1; i++) { for (var j = 1; j <= values[0].length - 1; j++) { var row = [values[i][0], values[0][j], values[i][j]]; sheet.appendRow(row) } } }; 

Questa è in pratica la manipolazione della matrice … sotto c’è un codice che fa quello che vuoi e scrive il risultato sotto i dati esistenti.

Ovviamente puoi adattarlo per scrivere su un nuovo foglio, se preferisci.

 function transformData(){ var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues();//read whole sheet var output = []; var headers = data.shift();// get headers var empty = headers.shift();//remove empty cell on the left var products = []; for(var d in data){ var p = data[d].shift();//get product names in first column of each row products.push(p);//store } Logger.log('headers = '+headers); Logger.log('products = '+products); Logger.log('data only ='+data); for(var h in headers){ for(var p in products){ // iterate with 2 loops (headers and products) var row = []; row.push(headers[h]); row.push(products[p]); row.push(data[p][h]) output.push(row);//collect data in separate rows in output array } } Logger.log('output array = '+output); sheet.getRange(sheet.getLastRow()+1,1,output.length,output[0].length).setValues(output); } 

inserisci la descrizione dell'immagine qui

per scrivere automaticamente il risultato in un nuovo foglio sostituire l’ultima riga di codice con questi:

  var ns = SpreadsheetApp.getActive().getSheets().length+1 SpreadsheetApp.getActiveSpreadsheet().insertSheet('New Sheet'+ns,ns).getRange(1,1,output.length,output[0].length).setValues(output); 

Ho scritto una semplice funzione generale personalizzata, che è riutilizzabile al 100% , puoi aprire / rovinare la rotazione di una tabella di qualsiasi dimensione.

Nel tuo caso potresti usarlo in questo modo: =unpivot(A1:D4,1,1,"customer","sales")

Quindi puoi usarlo proprio come qualsiasi funzione di array incorporata nel foglio di calcolo.

Vedi qui 2 esempi: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765

La seguente è la fonte:

 /** * Unpivot a pivot table of any size. * * @param {A1:D30} data The pivot table. * @param {1} fixColumns Number of columns, after which pivoted values begin. Default 1. * @param {1} fixRows Number of rows (1 or 2), after which pivoted values begin. Default 1. * @param {"city"} titlePivot The title of horizontal pivot values. Default "column". * @param {"distance"[,...]} titleValue The title of pivot table values. Default "value". * @return The unpivoted table * @customfunction */ function unpivot(data,fixColumns,fixRows,titlePivot,titleValue) { var fixColumns = fixColumns || 1; // how many columns are fixed var fixRows = fixRows || 1; // how many rows are fixed var titlePivot = titlePivot || 'column'; var titleValue = titleValue || 'value'; var ret=[],i,j,row,uniqueCols=1; // we handle only 2 dimension arrays if (!Array.isArray(data) || data.length < fixRows || !Array.isArray(data[0]) || data[0].length < fixColumns) throw new Error('no data'); // we handle max 2 fixed rows if (fixRows > 2) throw new Error('max 2 fixed rows are allowed'); // fill empty cells in the first row with value set last in previous columns (for 2 fixed rows) var tmp = ''; for (j=0;j 0 && data[i][0];i++) { row = []; for (j=0;j 

Ecco un file dimostrativo che utilizza un metodo che utilizza funzioni personalizzate e formule di array incorporate:

  1. Crea un nuovo foglio e rinominalo come “Aux”
  2. Nel foglio Aux aggiungi le seguenti formule: (questo presuppone che i dati di origine si trovino in un foglio chiamato dati)
    A1: =COUNTA(data!A:A) Calcola il numero di righe.
    A2: =COUNTA(data!1:1) Calcola il numero di colonne.
    A3: =CELL("address",data!A1) Passaggio intermedio.
    A4: =LEFT(A3,FIND("!",A3)-1) Calcola il nome del foglio con i dati di origine.
  3. Crea un nuovo foglio
  4. Aggiungi il seguente il nuovo foglio
    A1: intestazioni di riga
    A2:
 = ArrayFormula (
   VLOOKUP (
     MOD (!! ROW (INDIRETTO ( "A1: A" & Aux A1 * Aux A2)) - 1, Aux A1!) + 1 + 1,
     {(ROW (INDIRETTO ( "A1: A" & Aux A1 + 1))), INDIRETTI (! Aux A4 & "R1C1: R!" & Aux A1 + 1 e "C" & Aux A2 + 1, falso!!)},
     2
   )
 )

B1: intestazioni di colonna
B2:

 = ArrayFormula (
   VLOOKUP (
     SIGN (ROW (INDIRETTO ( "A1: A" & Aux A1 * Aux A2))!!),
     {(ROW (INDIRETTO ( "A1: A" & Aux A1 + 1))), INDIRETTI (! Aux A4 & "R1C1: R!" & Aux A1 + 1 e "C" & Aux A2 + 1, falso!!)},
     MOD (! ROW (INDIRETTO (!! "A1: A" & Aux A1 * Aux A2)) - 1, Aux A2) + 1 + 2
   )
 )

C1: valori
C2:

 = ArrayFormula (
   VLOOKUP (
     MOD (!! ROW (INDIRETTO ( "A1: A" & Aux A1 * Aux A2)) - 1, Aux A1!) + 1 + 1,
     {(ROW (INDIRETTO ( "A1: A" & Aux A1 + 1))), INDIRETTI (! Aux A4 & "R1C1: R!" & Aux A1 + 1 e "C" & Aux A2 + 1, falso!!)},
     MOD (! ROW (INDIRETTO (!! "A1: A" & Aux A1 * Aux A2)) - 1, Aux A2) + 1 + 2
   )
 )

Descrizione dei principali costrutti

  • ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2) restituisce una serie di numeri consecutivi che ha la stessa altezza del risultato finale richiesto.
  • {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)} restituisce un array con la prima colonna incluso l’indice di riga e le colonne successive sono i dati di origine.

Se i tuoi dati hanno una singola colonna chiave univoca, questo foglio di calcolo può avere ciò di cui hai bisogno.

Il tuo foglio unpivot conterrà:

  • La colonna chiave =OFFSET(data!$A$1,INT((ROW()-2)/5)+1,0)
  • Colonna dell’intestazione della colonna =OFFSET(data!$A$1,0,IF(MOD(ROW()-1,5)=0,5,MOD(ROW()-1,5)))
  • La colonna del valore della cella =INDEX(data!$A$1:$F$100,MATCH(A2,data!$A$1:$A$100,FALSE),MATCH(B2,data!$A$1:$F$1,FALSE))

dove 5 è il numero di colonne da non decomporre.


Non ho creato il foglio di calcolo. Mi è capitato attraverso la stessa ricerca che mi ha portato a questa domanda.

Per favore prova questo codice:

https://github.com/Max-Makhrov/GoogleSheets/blob/master/UnpivotTable.js

Dà più potere su ciò che stai ruotando

Campione di input:

  input: * file = file object, default: ActiveSpreadsheet * strHead1 = "'Wide_Table_Sample2'!A2:B2"; * strHead2Start = "'Wide_Table_Sample2'!C1:C2"; * strLabels = 'PlanFact,Metric,Volume'; default: 'Label1,Label2,Label3...'