сложное двойное выражение INDEX MATCH, которое возвращает и использует уникальный сводный массив значений

Question

У меня есть запись первой помощи в Excel, и я пытаюсь автоматизировать некоторые расчеты, которые проводятся каждый год.

Я приложил образец файла (без идентификаторов), чтобы проиллюстрировать проблему. Ссылка Dropbox здесь.

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

Случаи, которые наблюдает каждый медик, заносятся в таблицу "Сводка" в таблице под названием "Запись". Для каждого случая указываются год, смена, имя медика и наличие ошибок в записи.

Каждый сдвиг имеет уникальный идентификатор, указанный в таблице "TimeIndex" в таблице с именем "ShiftValueT".

Я пытаюсь написать формулу, которая делает следующее:

1. Определяет строки, соответствующие нескольким критериям (Year, Medic, ISERROR = FALSE ())
2. Использует эти строки для определения значений UniqueShift, которые соответствуют
3. Создает массив из уникальных значений столбца UniqueShift, которые соответствуют (т.е. удаляет дубликаты).
4. Использует эти уникальные значения (INDEX MATCH) в таблице ShiftValueT, чтобы найти соответствующие часы для типа UniqueShift
5. Суммирует все экземпляры часов, соответствующие идентифицированным UniqueShifts

Я получил это далеко (как формула массива):

=SUM(INDEX(ShiftValueT[[Value]:[Value]],MATCH(INDEX(Record[[UniqueShift]:[UniqueShift]],MATCH(B$1&FALSE()&$A2,Record[[Year]:[Year]]&Record[[ISERROR]:[ISERROR]]&Record[[Medic]:[Medic]],0)),ShiftValueT[[UniqueShift]:[UniqueShift]],0)))

но это только возвращает первый экземпляр UniqueShift и ссылку на один час в ShiftValueT.

Как я могу изменить мою формулу, чтобы она возвращала сумму всех часов?

Примечание: я бы предпочел не использовать VBA.

Answer 1

Прежде всего, я бы рекомендовал использовать сводную таблицу для этого типа операций. Если сводная таблица не вариант, способ решить эту проблему будет

1. Измените '-' для PRE и POST на 0 в таблице 'ShiftValueT'.
2. Добавьте столбец в таблицу «Запись» с именем Time Index и используйте Vlookup, чтобы получить соответствующее значение времени для каждой записи.

=VLOOKUP([@UniqueShift],ShiftValueT,2,FALSE)

2. Затем на вкладке статистики, у вас есть 2 варианта в зависимости от вашего года Excel. Для 2016 года вы можете использовать формулу sumproduct (это в ячейке Stats!БИ 2)

=SUMPRODUCT((Record[Medic]=Stats!$A2)*(Record[Year]=Stats!B$1)*(Record[ISERROR]=FALSE)*Record[Time Index Value])

или если у вас есть предыдущие версии Excel (чтобы получить {}, нажмите Ctrl + Shift + Enter при выходе из ячейки)

{=SUM(IF(Record[Medic]=Stats!$A2,IF(Record[Year]=Stats!B$1,IF(Record[ISERROR]=FALSE,Record[Time Index Value],0),0),0))}

Примечание. Если вам не нужно включать дубликаты, выполните удаление дубликатов данных таблицы «Запись» перед использованием формул. Или следуйте инструкциям ниже

1. Добавьте еще один столбец в таблицу "Записи" под названием "Дубликаты" (это должно быть в столбце F) и вставьте эту формулу в F2. Примечание. Для этого приложения требуется Excel 2016

=IF(COUNTIFS($A$2:$A3,$A3,$B$2:$B3,$B3,$B$2:$B3,$B3,$C$2:$C3,$C3,$D$2:$D3,$D3,$E$2:$E3,$E3) >1, "Duplicate row", "")

2. Обновить формулы до

=SUMPRODUCT((Record[Medic]=Stats!$A2)*(Record[Year]=Stats!B$1)*(Record[ISERROR]=FALSE)*(Record[Duplicate]<>"Duplicate row")*Record[Time Index Value])

или же

{=SUM(IF(Record[Medic]=Stats!$A2,IF(Record[Year]=Stats!B$1,IF(Record[ISERROR]=FALSE,IF(Record[Duplicate]<>"Duplicate row",Record[Time Index Value],0),0),0),0))}

Answer 2

Добавление вспомогательного столбца, как предлагается в другом ответе, действительно упрощает проблему. Если вы можете сделать это, я рекомендую это.

Столбцы должны быть заблокированы в формуле:

=SUMPRODUCT((Record[[Medic]:[Medic]]=Stats!$G2)*(Record[[Year]:[Year]]=Stats!H$1)*(Record[[ISERROR]:[ISERROR]]=FALSE)*Record[[Hours]:[Hours]])

Как вы видите, я назвал новый столбец "Часы".

Если вы не можете добавить вспомогательный столбец, тогда для решения требуется очень странная формула INDEX() с отсутствующими ссылками, о которой вы можете прочитать подробнее в связанном вопросе.

=SUM(IFERROR(INDEX(ShiftValueT[Value],MATCH(INDEX(Record[UniqueShift],N(IF(1,AGGREGATE(15,6,ROW(A$1:A$350)/((Record[Year]=Stats!B$1)*(Record[Medic]=Stats!$A2)*(Record[ISERROR]=FALSE)),ROW(A$1:A$70))))),ShiftValueT[UniqueShift],0)),0))

Как ни странно, столбцы не должны быть заблокированы в этой формуле. Обе эти формулы дали одинаковые ответы, показанные в вашей заполненной таблице ниже:

Ни одна из этих формул не удаляет дубликаты. Пожалуйста, прокомментируйте, если вам это нужно.

Надеюсь, это поможет, и удачи.