您如何创建“反向支点"?在 Google 表格中? [英] How do you create a "reverse pivot" in Google Sheets?
问题描述
我正在尝试生成反向支点"；功能.我对这样的功能进行了长时间的艰苦搜索，但找不到已经存在的功能.
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.
我有一个最多包含 20 列和数百行的汇总表，但是我想将其转换为平面列表，以便我可以导入到数据库(甚至使用平面数据从!)
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:
| 客户 1 | 客户 2 | 客户 3 | |
|---|---|---|---|
| 产品 1 | 1 | 2 | 3 |
| 产品 2 | 4 | 5 | 6 |
| 产品 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
我创建了一个函数，该函数将从 sheet1 读取范围并将重新格式化的行附加到同一张表的底部，但是我正在尝试让它工作，以便我可以拥有sheet2 上的函数将从 sheet1 读取整个范围.
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?
这是我目前所拥有的:
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)
}
}
};
推荐答案
我编写了一个简单的通用自定义函数，它是 100% 可重用的，您可以对任意大小的表进行反透视/反向透视.
I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size.
在你的情况下，你可以这样使用它:=unpivot(A1:D4,1,1,"customer","sales")
In your case you could use it like this: =unpivot(A1:D4,1,1,"customer","sales")
因此您可以像使用电子表格中的任何内置数组函数一样使用它.
So you can use it just like any built-in array function in spreadsheet.
Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765
以下为出处:
/**
* 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<data[0].length;j++)
if (data[0][j] != '')
tmp = data[0][j];
else
data[0][j] = tmp;
// for 2 fixed rows calculate unique column number
if (fixRows == 2)
{
uniqueCols = 0;
tmp = {};
for (j=fixColumns;j<data[1].length;j++)
if (typeof tmp[ data[1][j] ] == 'undefined')
{
tmp[ data[1][j] ] = 1;
uniqueCols++;
}
}
// return first row: fix column titles + pivoted values column title + values column title(s)
row = [];
for (j=0;j<fixColumns;j++) row.push(fixRows == 2 ? data[0][j]||data[1][j] : data[0][j]); // for 2 fixed rows we try to find the title in row 1 and row 2
for (j=3;j<arguments.length;j++) row.push(arguments[j]);
ret.push(row);
// processing rows (skipping the fixed columns, then dedicating a new row for each pivoted value)
for (i=fixRows; i<data.length && data[i].length > 0; i++)
{
// skip totally empty or only whitespace containing rows
if (data[i].join('').replace(/s+/g,'').length == 0 ) continue;
// unpivot the row
row = [];
for (j=0;j<fixColumns && j<data[i].length;j++)
row.push(data[i][j]);
for (j=fixColumns;j<data[i].length;j+=uniqueCols)
ret.push(
row.concat([data[0][j]]) // the first row title value
.concat(data[i].slice(j,j+uniqueCols)) // pivoted values
);
}
return ret;
}
这篇关于您如何创建“反向支点"?在 Google 表格中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
