你如何创建一个“反向支点”在Google表格中？ [英] How do you create a "reverse pivot" in Google Sheets?

问题描述

我试图产生一个反向枢轴功能。我已经搜索了很长时间，很难找到这样一个函数，但是找不到一个已经存在的函数。

我有一个总结表，其中包含20列和数百行，但我想将它转换为一个平面列表，以便我可以导入到数据库（甚至可以使用平面数据来创建更多的透视表！）

所以，我有这种格式的数据：

  |客户1 |客户2 |客户3 
 ---------- + ------------ + ------------ + ------- ---- 
产品1 | 1 | 2 | 3 
产品2 | 4 | 5 | 6 
产品3 | 7 | 8 | 9 
  

并且需要将其转换为以下格式：

  Customer |产品|数量
 ----------- + ----------- + ---- 
客户1 |产品1 | 1 
客户1 |产品2 | 4 
客户1 |产品3 | 7 
客户2 |产品1 | 2 
客户2 |产品2 | 5 
客户2 |产品3 | 8 
客户3 |产品1 | 3 
客户3 |产品2 | 6 
客户3 |产品3 | 9 
  

我已经创建了一个函数来读取 sheet1 并在同一张纸的底部添加重新格式化的行，但是我试图让它工作，所以我可以在 sheet2 上使用这个函数将阅读整个范围从 sheet1 。

无论我尝试什么，我似乎都无法得到它工作，并想知道是否有人可以给我任何指针？

这是我迄今为止：

< pre class =lang-js prettyprint-override> function readRows（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var rows = sheet.getDataRange（）;
var numRows = rows.getNumRows（）;
var values = rows.getValues（）; （var i = 1; i <= numRows-1; i ++）{
for（var j = 1; $ i


heads = values [0] ; j <= values [0] .length-1; j ++）{
var row = [values [i] [0]，values [0] [j]，values [i] [j]];
sheet.appendRow（row）
}
}
};


解决方案

基本上是数组操作...下面是你可以根据你的需要编写代码并将结果写回现有数据的下面。

如果你喜欢，你当然可以用它来写在新的表格上。

  function transformData（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var data = sheet.getDataRange（）。getValues（）; //读取整个工作表
 var output = []; 
 var headers = data.shift（）; //获得标题
 var empty = headers.shift（）; //删除左边的空单元
 var products = []; 
 for（var d in data）{
 var p = data [d] .shift（）; //获得每行第一列的产品名称
 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）{//迭代2个循环（标题和产品）
 var row = []; 
 row.push（headers [h]）; 
 row.push（products [p]）; 
 row.push（data [p] [h]）
 output.push（row）; //在输出数组中分隔行收集数据
} 
} 
 Logger.log（'output array ='+ output）; 
 sheet.getRange（sheet.getLastRow（）+ 1,1，output.length，output [0] .length）.setValues（output）; 
} 
  

  var ns = SpreadsheetApp.getActive（）。getSheets（）。length + 1 
 SpreadsheetApp.getActiveSpreadsheet（）。insertSheet（'New片'+ NS，NS）.getRange（1,1，output.length，输出[0]。长度）.setValues（输出）; 
  

I am trying to produce a "reverse pivot" function. I have searched long and hard for such a function, but cannot find one that is already out there.

I have a summary table with anywhere up to 20 columns and hundreds of rows, however I would like to convert it into a flat list so I can import to a database (or even use the flat data to create more pivot tables from!)

So, I have data in this format:

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

And need to convert it to this format:

 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

I have created a function that will read the range from sheet1 and append the re-formatted rows at the bottom of the same sheet, however I am trying to get it working so I can have the function on sheet2 that will read the whole range from sheet1.

No matter what I try, I can't seem to get it to work, and was wondering if anybody could give me any pointers?

Here is what I have so far:

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

解决方案

That is basically array manipulation... below is a code that does what you want and writes back the result below existing data.

You can of course adapt it to write on a new sheet if you prefer.

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

to automatically write the result in a new sheet replace last line of code with these :

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

这篇关于你如何创建一个“反向支点”在Google表格中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！