Как вы создаете "обратную ось" в Google Таблицах?

Ответ 1

Я написал простую обычную пользовательскую функцию, которая на 100% пригодна для повторного использования. Вы можете развернуть/развернуть таблицу любого размера.

В вашем случае вы можете использовать его следующим образом: =unpivot(A1:D4,1,1,"customer","sales")

Таким образом, вы можете использовать его как любую встроенную функцию массива в электронной таблице.

Посмотрите здесь 2 примера: 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 && data[i][0];i++)
  {
    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;
}

Ответ 2

Это в основном манипуляция массивом... ниже - это код, который делает то, что вы хотите, и записывает результат ниже существующих данных.

Конечно, вы можете адаптировать его для записи на новом листе, если хотите.

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

чтобы автоматически записать результат на новом листе, замените последнюю строку кода на эти:

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

Ответ 3

Я не думаю, что у вас достаточно ответов на формулы массива, так что вот еще один.

Тестовые данные (лист 1)

Формула для клиента

=ArrayFormula(hlookup(int((row(indirect("1:"&Tuples))-1)/Rows)+2,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

(использует немного математики для повторения и hlookup для поиска правильного столбца в заголовках столбцов)

Формула для продукта

=ArrayFormula(vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$A},2))

(аналогичный подход с использованием mod и vlookup для поиска правильных строк в заголовках строк)

Формула для количества

=ArrayFormula(vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$Z},int((row(indirect("1:"&Tuples))-1)/Rows)+3))

(расширение вышеуказанного подхода для поиска строки и столбца в 2d массиве)

Затем объединение этих трех формул в запрос, чтобы отфильтровать любые пустые значения для количества

=ArrayFormula(query(
   {hlookup(int((row(indirect("1:"&Tuples))-1)/Rows)+2, {COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
    vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$A},2),
    vlookup(mod(row(indirect("1:"&Tuples))-1,Rows)+2,{row(Sheet1!$A:$A),Sheet1!$A:$Z},int((row(indirect("1:"&Tuples))-1)/Rows)+3)},
"select * where Col3 is not null"))

Заметка

Именованные диапазоны Rows и Cols получаются из первого столбца, а строка данных с использованием счетчиков и Tuples является их произведением. Отдельные формулы

=counta(Sheet1!A:A)

=counta(Sheet1!1:1)

а также

=counta(Sheet1!A:A)*counta(Sheet1!1:1)

при необходимости может быть включен в основную формулу с некоторой потерей читабельности.

Для справки, вот "стандартное" решение для разделения/объединения (с ограничением данных в 50 КБ), адаптированное для текущей ситуации:

=ArrayFormula(split(transpose(split(textjoin("♫",true,transpose(if(Sheet1!B2:Z="","",Sheet1!B1:1&"♪"&Sheet1!A2:A&"♪"&Sheet1!B2:Z))),"♫")),"♪"))

Это также довольно медленно (обработка 2401 элементов массива). Если вы ограничите вычисление фактическими размерами данных, это будет намного быстрее для небольших наборов данных:

=ArrayFormula(split(transpose(split(textjoin("♫",true,transpose(if(Sheet1!B2:index(Sheet1!B2:Z,counta(Sheet1!A:A),counta(Sheet1!1:1))="","",Sheet1!B1:index(Sheet1!B1:1,counta(Sheet1!1:1))&"♪"&Sheet1!A2:index(Sheet1!A2:A,counta(Sheet1!A:A))&"♪"&Sheet1!B2:index(Sheet1!B2:Z,counta(Sheet1!A:A),counta(Sheet1!1:1))))),"♫")),"♪"))

Ответ 4

Вот демонстрационный файл, который использует метод с использованием встроенных пользовательских функций и формул массива:

• Создайте новый лист и переименуйте его как "Aux"
• В листе Aux добавьте следующие формулы: (это предполагает, что исходные данные находятся в листе с именем data)
  A1: =COUNTA(data!A:A) Рассчитать количество строк.
  A2: =COUNTA(data!1:1) Рассчитать количество столбцов.
  A3: =CELL("address",data!A1) Промежуточный шаг.
  A4: =LEFT(A3,FIND("!",A3)-1) Вычисляет имя листа с исходными данными.
• Создать новый лист
• Добавьте следующий новый лист A1: заголовки строк
  A2:

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

B1: заголовки столбцов
B2:

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

C1: Значения
C2:

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

Описание основных конструкций

• ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2) возвращает массив последовательных чисел с той же высотой, что и требуемый конечный результат.
• {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)} возвращает массив с первым столбцом, включая индекс строки, а следующие столбцы являются исходными данными.

Ответ 5

Если ваши данные имеют единственный уникальный столбец, эта таблица может иметь то, что вам нужно.

В листе вашего неволя будет содержаться:

• Ключевой столбец =OFFSET(data!$A$1,INT((ROW()-2)/5)+1,0)
• Столбец заголовка столбца =OFFSET(data!$A$1,0,IF(MOD(ROW()-1,5)=0,5,MOD(ROW()-1,5)))
• Столбец значения ячейки =INDEX(data!$A$1:$F$100,MATCH(A2,data!$A$1:$A$100,FALSE),MATCH(B2,data!$A$1:$F$1,FALSE))

где 5 - количество столбцов, которые можно отключить.

Я не сделал таблицу. Я прошел через него в том же поиске, что и привело меня к этому вопросу.

Ответ 6

=ARRAYFORMULA({"Customer", "Product", "Qty"; 
 QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:Z<>"", B1:1&"♠"&A2:A&"♠"&B2:Z&"♦", )), , 999^99)), , 999^99)), "♦")), "♠")), 
 "where Col1<>'' order by Col1")})

Ответ 7

Манипулирование array.reduce с использованием array.reduce и array.splice - минималистичный подход:

/**
 * Unpivots the given data
 *
 * @return Unpivoted data from array
 * @param {A1:F4} arr 2D Input Array
 * @param {3} numCol Number of static columns on the left
 * @param {A1:C1} headers [optional] Custom headers for output
 * @customfunction
 */
function unpivot(arr, numCol, headers) {
  var out = arr.reduce(function(acc, row) {
    var left = row.splice(0, numCol); //static columns on left
    row.forEach(function(col, i) {
      acc.push(left.concat([acc[0][i + numCol], col])); //concat left and unpivoted right and push as new array to accumulator
    });
    return acc;
  }, arr.splice(0, 1));//headers in arr as initial value
  headers ? out.splice(0, 1, headers[0]) : null; //use custom headers, if present.
  return out;
}

Использование:

=UNPIVOT(A1:F4,1,{A1,"Month","Sales"})//Outputs 1 static and 2 unpivoted columns from 1 static and 4+ pivoted columns 

Ответ 8

Пожалуйста, попробуйте этот код:

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

Это дает больше возможностей тому, что вы поворачиваете

Пример ввода:

  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...'