Excel SUM или COUNT на основе связанных полей из двух таблиц

Question

У меня есть две таблицы в Excel.

Небольшая таблица в 50 строк... tblGroups[#Headers]= Employee ID; Name; Group.

Вторая таблица с 3000+ строками...tblData[#Headers]= EventID; Date; Employee ID; Value

Теперь обратите внимание, что Value=1всегда. У них просто уникальные номера EventID. Я пытаюсь создать отчет, который отображает количество или сумму этого значения для определенного Group.

Опять Groupнаходится в моей маленькой таблице и Valueнаходится в большой таблице, но EmployeeIDнаходится в обеих. Есть ли способ написать поиск, чтобы вернуть SUM или COUNT Valueв большой таблице, где она включает EmployeeIDs из определенной группы? Таким образом, SUM или COUNT для всей группы.

Я могу получить первый EmployeeID в группе, используя=SUMIFS(tblData[Value],tblData[EmployeeID],INDEX(tblGroups[EmployeeID],MATCH(F4,tblGroups[Group],0)))

где F4= группа, в которой я хочу выполнить поиск, но как мне сделать так, чтобы он продолжался по списку идентификаторов этой группы и продолжал суммирование?

Теперь я знаю, что могу добавить вспомогательный столбец в мою большую таблицу с помощью ВПР, чтобы добавить группу в строку большой таблицы, но я спрашиваю, есть ли другой способ сделать это без вспомогательных столбцов в моей таблице данных, в основном потому, что мне нравится производить впечатление на своего босса и находить новые способы сделать это.

Вот пример файла для скачивания

Спасибо,

Answer 1

Вы можете использовать эту «формулу массива» в G4, скопированную вниз

=SUM(SUMIFS(tblData[Value],tblData[EmployeeID],IF(tblGroups[Group]=F4,tblGroups[EmployeeID])))

подтверждено с CTRL+ SHIFT+ENTER

Функция IF возвращает «массив» всех идентификаторов для этой группы, а затем SUMIFS также возвращает массив, поэтому вам нужно обернуть SUM вокруг этого, чтобы просуммировать их все для получения конечного результата.