сумма vlookup с использованием формулы массива

Question

У меня есть такая таблица платежей:

account | amount
--------+-------
  101   | 3
  101   | 5
  102   | 7
  103   | 9

Я назвал диапазон этой таблицы "платежами". Свой первый столбец я назвал "счетами". Свой второй столбец я назвал "суммами".

У меня также есть другая таблица, которая назначает учетные записи группам:

account | group
--------+-------
  101   | 1
  102   | 1
  103   | 2

Я назвал диапазон этой таблицы "группы".

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

sum(if(vlookup(accounts,groups,2,false)=1,amounts,0))

Я также попробовал такую формулу массива:

sumif(if(vlookup(accounts,groups,2,false)=1,amounts,0))

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

Итак, как я могу рассчитать его по одной формуле?

Answer 1

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

=SUMPRODUCT(B1:B4*(LOOKUP(A1:A4,C1:C3,D1:D3)=1))

=1 относится к номеру группы, в которой вы ищете учетные записи. Обратите внимание, что это обычная формула, а не формула массива.

Я использовал обычные ссылки на ячейки в отличие от именованных областей, потому что я думаю, что это облегчает связь формулы с листом XLS.

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

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

=SUMPRODUCT(Payments*(LOOKUP(Accounts1,Accounts2,Groups)=1))

Для полноты картины ознакомьтесь с разделом Как использовать функцию LOOKUP в Excel для условий, при которых вы можете использовать LOOKUP .

Answer 2

Да, вы правы, VLOOKUP не возвращает массив, поэтому вам нужен другой подход. Предположим, что первый столбец группы называется accounts2 а второй столбец - numbers затем попробуйте эту формулу массива,

=SUM(IF(ISNUMBER(MATCH(accounts,IF(numbers=1,accounts2),0)),amounts))

подтвердить нажатием CTRL+SHIFT+ENTER