QUERY. Большое число колонок

memory dump
QUERY. Большое число колонок
Содержимое страницы

Недавно натолкнулся на задачу, в которой необходимо было посчитать минимумы для неопределенного числа колонок. Изменять структуру данных было жаль, т.к. массив был организован очень качественно. Поэтому я решил прибегнуть к модификации часто используемой формулы с QUERY, ARRAYFORMULA и COLUMN. Под катом мой мемори дамп того, что получилось.

Области применения

Эта статья будет интересна тем, кто использует формулы массива и стремится к сокращению зависимостей между диапазонами до минимального количества формул.

Постановка задачи

Дано неизвестное количество колонок. Необходимо посчитать агрегатные функции для этих колонок. Например, минимумы

QUERY. Большое число колонок

Формула для тех, кто все уже знает

=QUERY(
  {B6:T15};
  "select min(Col" & JOIN("), min(Col"; SEQUENCE(1;COLUMNS(B6:T15);1;1)) & ")";
  0
)

Как это работает

Самой важной операцией в этом решении является конкатенация массива в запрос функции QUERY с помощью JOIN. Для построения последовательности используется SEQUENCE

Формула SEQUENCE

=SEQUENCE(1;COLUMNS(B6:T6);1;1)

Аналог этой формулы

=ARRAYFORMULA(COLUMN(B6:T6)-1)

Построение запроса с JOIN. В данном месте стоит обратить внимание на то, что агрегатная функция повторяется дважды из-за особенностей соединения строк.

Скрепление строки запроса QUERY с помощью JOIN

="select min(Col" & JOIN("), min(Col"; B20:T20) & ")"

После подстановки формул вместо их диапазонов в формулу QUERY получаем результат

=QUERY(
  {B6:T15};
  "select min(Col" & JOIN("), min(Col"; SEQUENCE(1;COLUMNS(B6:T15);1;1)) & ")";
  0
)

Другие примеры

Для произвльного числа колонок в произвольной последовательности можно опустить использование SEQUENCE, задавая колонки вручную. В данном примере считаются суммы только 19, 4, 1, 14, 9 колонок.

Произвольный массив для QUERY

=QUERY({B7:T15};"select sum(Col" & JOIN("), sum(Col"; {19\4\1\14\9}) & ")"; 0)

Обрезать шапку можно с помощью INDEX

Обрезка массива с помощью INDEX

=INDEX(
  QUERY({B7:T15};"select sum(Col" & JOIN("), sum(Col"; {19\4\1\14\9}) & ")"; 0);
  2
)

Часто используемая формула с ARRAYFORMULA имеет вид

Комбинация QUERY ARRAYFORMULA COLUMNS

=ARRAYFORMULA(QUERY(
  {B6:T15};
  "select sum(Col" & JOIN("), sum(Col"; COLUMN(B6:T6)-1) & ")";
  0
))

Ссылки