Резерв сотрудников, когда обычного ВПР недостаточно

[baroqueda]

Доброго всем дня!

Есть задача, с которой своими силами справиться не могу
Прошу помощи ПРОфессионалов и знатоков, как в части формул, так и в части их разъяснения

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

Необходимо, чтобы сотрудники из файла "Резерв город N" переносились в файл "Мониторинг достаточности резерва", закреплялись за конкретным магазином и подсчитывалась сумма резервистов (см. файл "Идеальная ситуация"). Так же существует критерий того, что не все сотрудники попадают в резерв. Так как в одном магазине может быть больше 1 резервиста обычный ВПР не работает, логику с ЕСЛИ я не нашел, так как всегда меняется искомая переменная (№ магазина).

Есть еще нюанс в том, что информацию по магазинам присылают коллеги из областей и ее в один файл собираю обычным копированием, однако нашел в интернете макрос, хочу уточнить подойдет ли он под эту ситуацию?

Sub Macros1()
    Application.ScreenUpdating = False: Application.DisplayAlerts = False
    
    Dim coll As New Collection, wb As Workbook, sh As Worksheet, newRow As Range
    Mask = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "*.xls")
    
    Filename = Dir(Mask)
    While Filename <> "" 
        If Not Filename Like ThisWorkbook.Name & "*" Then coll.Add Filename
        Filename = Dir
    Wend

    'For Each Item In coll: Debug.Print Item: Next
    On Error Resume Next
    For Each Item In coll
        Set wb = Workbooks.Open(Replace(ThisWorkbook.FullName, ThisWorkbook.Name, Item), , True)
        If Not wb Is Nothing Then
            Set sh = wb.Worksheets(1)
            LastRow = sh.Range("a65000").End(xlUp).Row
            If LastRow > 4 Then  
                For i = 5 To LastRow
                    Set newRow = Range("b10:b256" & lr).End(xlUp).Offset(1)
                    sh.Rows(i).Copy newRow
                    newRow.EntireRow.AutoFit
                Next i
            End If
            wb.Close False
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Заранее огромное спасибо!
Файлы во вложении.

[IgorGO]

ИНДЕКС и НАИМЕНЬШИЙ - решают вашу задачу

=ЕСЛИ(СТОЛБЕЦ()-3>RC3;"";ИНДЕКС(ФИО;НАИМЕНЬШИЙ(ЕСЛИ((ВСП=Лист1!RC2)*(Резерв="в резерв");СТРОКА(ВСП);999999);СТОЛБЕЦ()-3)))

1. в лист список скопировал исходные данные
2. сумму резервов перенес в 3-ю колонку (теперь формулы из колонки 4 можете копировать вправо столько раз сколько считаете нужным)

[baroqueda]

Добрый день, IgorGO!
Огромное спасибо за оперативную помощь!
С логикой СУММПРОИЗВ разобрался, внедрил в мейн таблицу, все работает.
Со второй формулой в процессе.
Теперь появилось еще одна необходимость, попробую разобраться сам.
Если не получиться, опишу задачу в этой теме.

Еще раз спасибо!

[baroqueda]

В продолжении данной темы появилась следующая задача:

Есть территориальные менеджеры, за ними закреплены несколько магазинов.
Так как сеть большая, магазины имеются в разных городах (города имеют признак по численности населения).

Необходимо посчитать резерв в разрезе каждого территориального менеджера, с нюансами, на численность городов.

Если у регионального менеджера город с населением свыше 1 млн.чел., город с населением от 500 до 1000 тыс.чел., город с населением от 100 до 500 тыс.чел. то данные суммируются по ячейкам G и ячейкам H, затем свариваются. Если у регионального менеджера выпадает город с населением менее 100 тыс.чел., то данные в общую сумму не попадают и результат резерва остается только на уровне магазина (см. Лист "Идеальная ситуация".

В очередной раз спасибо!

[IgorGO]

сеть большая

а считать-то нечего (не понятно что считать??)

категория городов:
а) город с населением свыше 1 млн.чел.
б) город с населением от 500 до 1000 тыс.чел.
...
е)....

конечно можно обрабатывать и это, но гораздо проще назвать колонку
Категории городов (население от ...)
а) 1000000
б) 500000

число 500000 обрабатывать гораздо проще, чем надпись "город с населением от 500 до 1000 тыс.чел.", в которой даже нет этого числа 500000, как и в надписи "город с населением свыше 1 млн.чел." нет числа 1000000

[baroqueda]

Допустим признак по городам будет:
1.1000000
2.500000
3.100000
4.10

Получиться подсчитать резерв в разрезе каждого регионального менеджера?

Что считать?
Если отфильтровать по Иванов Иван Иванович 2 на листе Резерв видно, что все магазина под его контролем больше 1000000, результат который хочется получить - это сумма столбца G сравнивается с суммой столбца, как в листе Идеальная ситуация. 12 строк превращаются в одну.

Понятно объяснил или нудно уточнить?

[IgorGO]

лист Идеальная...:
1. почему у ИИИ1 и ИИИ3 по 4 строки?
2. почему у ИИИ2 написано " магазина 146 если в данных у него 12 №?
3. магазинов и город г.С, если городов разных 4 шт.?

Вы определитесь какой вариант Вам проще:
обьяснить, что нужно сосчитать
или
самостоятельно написать формулы, которые сосчитают все необходимое
(в варианте 2 обьяснять вообще ничего не нужно)