sumproduct excel несколько таблиц

У меня две таблицы, и я хочу подсчитать, сколько вхождений элемента, ссылаясь на другую таблицу,

например, я хочу знать, сколько змей принадлежит Бобу, очевидный ответ – два, но как это сделать в одной формуле? Я использовал sumproduct для подсчета числа вхождений отдельных змей, но я хочу, чтобы все они были в одной формуле, ссылаясь на вторую таблицу. Это возможно?

Примечание : у меня Excel 2003, поэтому sumproduct

AB 1 Bob Boa 2 Ann Cow 3 Bob Sheep 4 Bob Python AB 1 Farm Snake 2 Cow Boa 3 Sheep Python 

Это, безусловно, возможно сделать с вашим существующим макетом и без вспомогательных столбцов. Предположим, что первая таблица в A1:B4 и вторая таблица в D1:E3 и вы можете получить количество змей, которые Боб владеет этой формулой

=SUMPRODUCT((A1:A4="Bob")*ISNUMBER(MATCH(B1:B4,INDEX(D2:E3,0, MATCH("snake",D1:E1,0)),0)))

Думаю, первый критерий самоочевиден.

Для второй эта часть находит все имена змей:

INDEX(D2:E3,0,MATCH("snake",D1:E1,0))

…. потому что он соответствует «змее» против заголовков в D1: E1, и возвращает весь столбец ниже [используя нуль в качестве аргумента строки в INDEX, вы получаете весь столбец].

Затем, имея все имена змей, вы можете использовать MATCH для сопоставления B1: B4 с ними. Если есть совпадение, вы получаете номер, если не получаете # N / A, чтобы вы обменивали функцию MATCH в ISNUMBER, чтобы вернуть TRUE / FALSE.

Другой способ – использовать COUNTIF вместо ISNUMBER / MATCH

=SUMPRODUCT((A1:A4="Bob")*(COUNTIF(INDEX(D2:E3,0, MATCH("snake",D1:E1,0)),B1:B4)>0))

Прежде всего, вам нужно правильно настроить таблицу поиска. Невозможно найти вторую таблицу, как есть. Сначала измените его следующим образом:

 Farm Cow Farm Sheep Snake Boa Snake Python 

Теперь вы можете найти, какой тип предмета есть, используя эту формулу:

 =INDEX(Sheet2!A:A,MATCH(B1,Sheet2!B:B,0)) 

Если вы добавите это в столбец C на своем оригинальном листе, вы получите следующее:

 Bob Boa Snake Ann Cow Farm Bob Sheep Farm Bob Python Snake 

Тогда вы можете просто использовать sumproduct:

 =SUMPRODUCT((A1:A4="Bob")*(C1:C4="Snake")) 
  • Сводная таблица должна отличаться от двух других таблиц
  • Найдите результат, затем диапазон и скопируйте
  • Формула Excel - формула суммы для каждой строки в таблице
  • автоматическое обновление списка SP Foundation
  • Поиск следующей даты выполнения для задачи
  • Excel с использованием правильной формулы, Lookup vs Vlookup vs IF
  • Отслеживание значений предыдущих дат в базе данных Excel
  • Excel VBA эффективный способ сравнения двух 2D-массивов
  • EXCEL - обратный поиск в матрице
  • Как включить пользователя для редактирования данных поиска Power Pivot
  • Как условно окрасить ячейку на основе самой нижней повторяющейся ценности?
  • Interesting Posts

    Функция Excel IMABS возвращает ложное значение

    В Excel 2007, как я могу индексировать индексы SUMIFS из нескольких столбцов из именованного диапазона?

    Excel VBA для отображения столбца и добавления строки столбца в объединенный диапазон

    Как подключиться к серверному порту в Excel PowerQuery

    Глобальный массив, который функция может редактировать в VBA

    Excel VBA: добавление элементов пользовательской формы в выбранный рабочий лист

    Цитирование через рабочие листы и вставка последних строк каждого в другой лист

    удалите строку, используя инструмент NPOI Excel c #

    Можем ли мы поместить формулу в поле «value_if_true» в IF-функции в Excel?

    excel macro vba по формуле vlookup

    vba проверить, существует ли лист – ошибка объекта

    экспортировать данные в excel из базы данных

    Две почти равные таблицы excel, загруженные с помощью OLEDB, – получили один заголовок и один без

    Excel вводит дату в ячейку, когда другая ячейка имеет введенное значение

    Добавление формулы в рабочий лист Excel приводит к тому, что HRESULT: 0x800A03EC

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