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

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

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

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

  • Могу ли я использовать функцию соответствия Excel для соответствия более чем одному типу значения
  • Как имитировать полное внешнее соединение в Excel?
  • Что такое формула excel, которая вернет мне последний номер строки ячейки в пределах диапазона, который содержит конкретные данные?
  • Условное соответствие на основе даты и времени
  • Может ли Excel использовать данные ввода формулы для ссылок на ячейки?
  • Справка Excel. Возможно ли вернуть номер столбца второго совпадения в горизонтальном массиве?
  • У меня также есть другая таблица, которая присваивает учетные записи группам:

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

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

    Теперь я хочу суммировать все платежи счетов группы 1, используя только одну формулу, без использования каких-либо дополнительных столбцов. Я пытаюсь сделать это, используя такую ​​формулу массива: sum (if (vlookup (accounts, groups, 2, false) = 1, amount, 0)) Я также пробовал такую ​​формулу массива: sumif (if (vlookup (учетные записи, группы , 2, ложно) = 1, составляет, 0))

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

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

  • Почему этот файл Excel продолжает запрашивать сохранение?
  • Временная диаграмма в Excel
  • Сумма, если ТОЛЬКО все ячейки имеют значение?
  • Сравните две таблицы и получите отсутствующие записи
  • Проверьте, является ли текст ячейки Excel числовым, используя только формулу
  • Кнопка добавления / вычитания Excel VBA
  • 2 Solutions collect form web for “Сумма vlookup с использованием формулы массива”

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

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

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

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

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

    Снимок экрана, показывающий значения и более общую функцию

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

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

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

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

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

    Подтвердите с помощью CTRL + SHIFT + ВВОД

    Давайте будем гением компьютера.