Как сделать сумму по столбцам VLOOKUP и ARRAYFORMULA?

Question

[KirillRez]

Добрый день!
Есть таблица с данными https://docs.google.com/spreadsheets/d/1Fy3B_xyOtu...

В ячейке H5, сейчас формула прописана
=ARRAYFORMULA(SUM(VLOOKUP(F5;A5:D;{2;3;4};0)))
Но arrayformula не работает на ячейки ниже, не считает.

Как исправить формулу?
Буду благодарен за помощь!

Answer 1

[Alexander Ivanov]

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

============= Обновлено 2022-08-03 ==========

Решение для бесконечных диапазонов

=ARRAYFORMULA(VLOOKUP(
  R5:R;
  QUERY(
    SPLIT(FLATTEN(A5:A & "|" & B5:D);"|");
    "select Col1, sum(Col2) group by Col1");
  2;
))

============= ==========

Можно использовать формулу построчной суммы

={A4:A7\{"Сумма";INDEX(
  {TRANSPOSE(QUERY(
    {TRANSPOSE(B5:E7)};
    "select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A5:A7);1;1)) & ")";
    0))}
  ;;2
)}}

К сожалению, она требует конечных диапазонов, но настройка несложная.


Только вычисления, без формирования заголовков результата, данные отсортированы как в исходнике

={INDEX(
  {TRANSPOSE(QUERY(
    {TRANSPOSE(B5:E7)};
    "select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A5:A7);1;1)) & ")";
    0))}
  ;;2
)}

Comments

[KirillRez]

Благодарю за решение. Предложенная Вами формула не просто подсчитывает сумму по столбцам, но и формирует таблицу с заголовками. Мне нужны только подсчеты. Поэтому и выбрал vlookup

[Alexander Ivanov]

KirillRez, можно же вынуть нужную часть.

[KirillRez]

Alexander Ivanov, Добрый день. Подскажите, а как сделать Вашу формулу так, чтобы она считала сумму по столбцам по ФИО. Сейчас критерий ФИО не учитывается при подсчете

[Alexander Ivanov]

KirillRez, приведите пример данных. Гадать довольно сложно.

[KirillRez]

Alexander Ivanov, В таблице https://docs.google.com/spreadsheets/d/1Fy3B_xyOtu...
В столбец H у меня не получается заставить формулу корректно работать (как в зеленом столбце, но там не сумма, а только одно значение)
Мне нужно, чтобы в столбце H подсчитывалось сумма значений по столбцам по ФИО.

[Alexander Ivanov]

KirillRez, к сожалению, пока не понимаю проблемы. Я привел второй пример без заголовков и описал, как работает сортировка. Вот результат


Это именно то, что вам нужно.

Если это не так, попробуйте как-то переформулировать задание.

[KirillRez]

Мне нужна формула, чтобы считала сумму по столбцам от фамилии.

При этом для меня очень важно, чтобы формула была прописана только в ячейке H5, а ниже просто считалась сумма по столбцам от фамилии.
Если я фамилии местами поменяю, сумма тоже должна измениться соответственно фамилии

[Alexander Ivanov]

KirillRez, ну, так нужно применить VLOOKUP:

=ARRAYFORMULA(VLOOKUP(
  P5:P7;
  {A5:A7\INDEX(
    {TRANSPOSE(QUERY(
    {TRANSPOSE(B5:E7)};
    "select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A5:A7);1;1)) & ")";
    0))}
  ;;2
  )};
  2;
))

Я в самом начале в ответе написал про порядок вывода - это как раз и означало, как нужно применять эту формулу. Надеюсь, теперь вам будет проще разобраться.

[KirillRez]

Alexander Ivanov, в файле пример доступ только к чтению, не смог раскрыть сгруппированные столбцы.
Подскажите, если в формуле A5:A7 - изменить на A5:A, в таком случае формула перестает работать.
Возможно ли заставить работать в таких условиях? Т.е. если мы не знаем сколько строчек будет в столбце "фамилия" в исходных данных.

[Alexander Ivanov]

KirillRez, вы можете просто сделать копию Таблицы.

К сожалению, формула требует диапазоны с границами. Я об этом написал в ответе.

Уверен, что есть формула лучше, но что-то пока не идет ничего в голову. Например, всегда можно сделать просто FLATTEN

[Alexander Ivanov]

А, ну, вот и решение

=ARRAYFORMULA(VLOOKUP(
  R5:R;
  QUERY(
    SPLIT(FLATTEN(A5:A & "|" & B5:D);"|");
    "select Col1, sum(Col2) group by Col1");
  2;
))

Answer 2

[idShura]

Исправь формулу в ячейке G5:
=ARRAYFORMULA(SUM(VLOOKUP(F5;$A$5:D;{2;3;4};0)))

Затем протягиваешь ячейку вниз чтобы скопировалась формула.

Comments

[KirillRez]

Спасибо, я почему использую arrayformula - чтобы не протягивать формулу в каждую ячейку. Такое возможно организовать через vlookup?