QUERY. Построчные вычисления

memory dump
QUERY. Построчные вычисления
Содержимое страницы

QUERY - многофункциональная и полезная формула. Может ли она считать “в строчку”? Под катом пример того, как можно оседлать “сверхсложную и запутанную” формулу. До сих пор не уверен, является ли она “оптимальной”, но моим критериям она соответствует и применяется в реальных проектах.

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

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

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

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

Построчные вычисления минимумов для QUERY Гугл таблиц

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

={A3:E7\{"Минимум";INDEX(
  {TRANSPOSE(QUERY(
    {TRANSPOSE(B4:E7)};
    "select " & "min(Col" & JOIN("), min(Col"; SEQUENCE(1;ROWS(A4:A7);1;1)) & ")";
    0))}
  ;;2
)}}

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

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

Изменим ориентацию данных: колонки станут строками, а строки - колонками. Временно, конечно.

Транспонирование в Таблицах Гугл

=TRANSPOSE(B4:E7)

Агрегатные вычисления (в данном случае min) лучше всего считать в QUERY . Сформируем строку запроса, учитывая, сколько строк в текущем “повернутом” состоянии

Минимумы QUERY в Таблицах Гугл для заданного числа колонок

="select " & "min(Col" & JOIN("), min(Col"; SEQUENCE(1;ROWS(A4:A7);1;1)) & ")"

Сама формула QUERY выглядит безобидно, как и положено хорошим формулам

Красивая Google QUERY

=QUERY({A11:D14};A18; 0)

Из результата видно, что уже получено решение. Необходимо “повернуть” всё обратно

Обратное транспонирование

=TRANSPOSE(A22:D23)

Подготовим данные, обрезав их и добавив заголовок

Обрезка данных

={"Минимум";INDEX({A27:B30};;2)}

Сведение данных - это обычная конкатенация массивов

Сведение данных

Примеры

Максимумы

Google QUERY построчные максимумы

={A3:E7\{"Максимум";INDEX(
  {TRANSPOSE(QUERY(
    {TRANSPOSE(B4:E7)};
    "select " & "max(Col" & JOIN("), max(Col"; SEQUENCE(1;ROWS(A4:A7);1;1)) & ")";
    0))}
  ;;2
)}}

Средние и суммы

Google QUERY построчные средние и суммы

={
  A3:E7\
  {"Сумма";
    INDEX({TRANSPOSE(QUERY(
      {TRANSPOSE(B4:E7)};"select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A4:A7);1;1)) & ")"; 0))};;2)}\
  {"Среднее";
    INDEX({TRANSPOSE(QUERY(
      {TRANSPOSE(B4:E7)};"select " & "avg(Col" & JOIN("), avg(Col"; SEQUENCE(1;ROWS(A4:A7);1;1)) & ")"; 0))};;2)}
}

Ссылки