sumproduct excel несколько таблиц
У меня две таблицы, и я хочу подсчитать, сколько вхождений элемента, ссылаясь на другую таблицу,
например, я хочу знать, сколько змей принадлежит Бобу, очевидный ответ – два, но как это сделать в одной формуле? Я использовал sumproduct для подсчета числа вхождений отдельных змей, но я хочу, чтобы все они были в одной формуле, ссылаясь на вторую таблицу. Это возможно?
Примечание : у меня Excel 2003, поэтому sumproduct
- VBA Macro - копирование частичной строки из листа в новый лист, если ячейка соответствует ячейке на третьем листе
- Переключить значение ячейки на основе значения предыдущей ячейки
- Централизованный прайс-лист на сетевом хранилище - привязка к excel
- Почему LOOKUP не соответствует первому элементу массива?
- Функция Excel Hlookup возвращает первый символ в таблице
AB 1 Bob Boa 2 Ann Cow 3 Bob Sheep 4 Bob Python AB 1 Farm Snake 2 Cow Boa 3 Sheep Python - Поиск последнего значения в динамической строке (Excel)
- Excel lookup возвращает ошибку, если не сделано в определенной строке на листе
- Найти минимальное или равное значение для несортированных данных
- Возвращаемое значение из массива данных на основе нескольких условий
- Выбор максимального значения - критерий добавления
- VBA Excel: поиск нескольких значений и возврат согласованного значения в следующую ячейку
- Excel VBA Inputbox - выбор диапазона (тип 8), но не как фиксированный диапазон, т.е. A1 не $ A $ 1
- Как суммировать строку в Excel со значениями поиска из поиска на основе столбцов?
Это, безусловно, возможно сделать с вашим существующим макетом и без вспомогательных столбцов. Предположим, что первая таблица в 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"))