☰

Сумма значений ВПР на нескольких листах

tag-icon • tag-icon microsoft-excel worksheet-function
Сумма значений ВПР на нескольких листах

Имею 12 листов Excel в рабочей книге на каждый месяц с января по декабрь. Каждый лист содержит имена и отпуска, отмеченные для этого месяца в той же строке, и значение суммы для каждой категории отпуска в конце таблицы. Мне нужно суммировать значения этих отдельных листов, найдя имя каждого человека на всех листах. как мне добиться этого в Excel.... Я пробовал сумму vlookup со всех листов, он выдает ошибку или не дает результата.

решение1

Возможно, здесь проще использовать СУММЕСЛИ, поскольку СУММЕСЛИ не выдаст ошибку, если человек не найден ни на одном конкретном листе (хотя все листы должны существовать), поэтому, если у вас есть листы с «янв» по ​​«дек», попробуйте эту формулу для итога за 12 месяцев

=SUM(SUMIF(INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!B8:B110"),B8,INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!AL8:AL110")))

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

Sheetlist ={"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}

тогда формула становится

=SUMPRODUCT(SUMIF(INDIRECT(Sheetlist&"!B8:B110"),B8,INDIRECT(Sheetlist&"!AL8:AL110")))

решение2

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

Я подозреваю, что ваша сумма vlookups выдает ошибку, потому что каждый человек берет отпуск только в несколько месяцев в году. Чтобы справиться с этим, нужно обернуть поиск в функции IFERROR. Таким образом, формулы должны выглядеть так:

 =sum(iferror(vlookup(<person name>,<January sheet name!data range>,<leave column number>,0),0),
      iferror(vlookup(<person name>,<February sheet name!data range>,<leave column number>,0),0),
      etc.
      )

Эту задачу можно было бы решить немного проще, настроив поиск для каждого месяца в отдельных столбцах, а затем суммируя результаты по всем столбцам.

Другая альтернатива, с которой я не очень хорошо знаком, но которая может удовлетворить ваши потребности, — это команда Data Consolidate. Объяснение команды можно найти здесьздесь.

Я настоятельно рекомендую подход с использованием сводных таблиц.

Связанный контент