Сумма 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 без кавычек?
  • Microsoft Excel 2003 - Объединить по столбцу
  • Многострочные заголовки и сортировка / фильтрация Excel 2007
  • Как заставить Excel (и другие продукты Microsoft Office) прекратить открытие файлов в одном приложении?
  • Excel save - как параметры CSV - возможно ли изменить запятую на трубку или вкладку?
  • 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 + ВВОД

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