☰

Excel 2016 for Mac - 將矩陣轉換為列表

tag-icon • tag-icon macos microsoft-excel-2016
Excel 2016 for Mac - 將矩陣轉換為列表

我已經在這裡看到了一些答案，但沒有一個適用於 Excel 2016 for Mac。

我想要一個簡單的解決方案將矩陣轉換為如下列表：

date    city1   city2   cityN
jan1    value   value   value
jan2    value   value   value

到

date    city    value
jan1    city1   value
jan1    city2   value
jan1    cityN   value
jan2    city1   value
jan2    city2   value
jan2    cityN   value

答案1

我使用了谷歌電子表格腳本：

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);
 var ns = SpreadsheetApp.getActive().getSheets().length+1
 SpreadsheetApp.getActiveSpreadsheet().insertSheet('New Sheet'+ns,ns).getRange(1,1,output.length,output[0].length).setValues(output);
}

相關內容