Как обернуть формулу в массив arrayformula?

Question

[KirillRez]

Добрый день.
Решал свой кейс и остановился на последнем действии.
Прошу помощи экспертов.
https://docs.google.com/spreadsheets/d/1O_SalYgToJ...

Как обернуть формулу =INDEX('Лист1'!A$2:A;(MATCH(1;MMULT(-('Лист1'!B$2:E=A2);TRANSPOSE(COLUMN('Лист1'!B$2:E)^0));0)))
в массив arrayformula (Лист2 столбец B) ?

Answer 1

[Григорий Боев]

Я так понял, вам нужно сделать PIVOT наоборот. Когда у меня был такой кейс, сделал себе по-быстрому такую функцию:

/**
 * Разворачивает данные в плоскую таблицу
 *
 * @author Boew Grigory ([e-mail])
 * @param {Array} data Исходные данные
 * @param {Boolean} [isRemoveNull=true] Удалять ли из выдачи пустые, нулевые и ложные значения
 * @customfunction
*/
function unPivot(data, isRemoveNull=true) {
  let colHeaders = [...(data[0])].slice(1);
  let rowHeaders = data.slice(1).map(row=>row[0]);

  let outData = [];
  rowHeaders.forEach((rowHeader,ri)=>{
    colHeaders.forEach((colHeader,ci)=>{
      let value = data[1+ri][1+ci];
      if(!isRemoveNull || value){
        outData.push([rowHeader, colHeader, value]);
      };
    });
  });

  if(!outData.length){
    return [[]];
  };
  return outData;
};

А в полученном массиве можно найти всё что угодно через ВПР.

Comments

[KirillRez]

Григорий Боев
Добрый день. Спасибо за решение.
Честно говоря не думал, что так глубоко нужно будет углубится, рассчитывал решить кейс с помощью формул.
Функция сработала, единственный вопрос - как убрать пустой столбец B посередине? (лист 4)
ссылка на таблицу лист 4

[Григорий Боев]

KirillRez, это значения B1:E1
Можете сделать так:
outData.push([rowHeader, value]);

Answer 2

[Alexander Ivanov]

Возможно, это просто

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A6&"_"&B2:E6);"_");"where Col2<>''"))

Пример в Таблице https://docs.google.com/spreadsheets/d/1tAZFRSb-N4...

Answer 3

[dollar]

Думаю, нужно совместить функции MATCH и INDEX.
Отредактировал ваш пример.

Comments

[KirillRez]

@dollar
Добрый день.
Спасибо за вариант. Я пробовал сочетание index и match, как и vlookup и так и по другому)
Описал задачу не полностью( Я не знаю какое количество столбцов будет в массиве, всегда разное. В Вашем варианте match работает по каждому столбцу отдельно, поэтому не очень решение подойдет.
Моя логика в том, чтобы искать значение в массиве и возвращать номер строки, в которой оно находится. а потом по номеру строки уже тянуть название показателя. Только вот как это написать в формуле не могу понять.
И чтобы максимально автоматизировать, все это круто было бы завернуть в arrayformula.