Страницы: 1
RSS
Подсчет уровней подчиненности таблицы Сотрудник-Начальник
 
Задача следующая. Есть простая таблица с двумя столбцами, строк примерно 5000:
  • столбец 1 - это ID Сотрудника
  • столбец 2 - это ID его Менеджера (Начальника)
Нужно заполнить 3й столбец - рядом с ID Менеджера заполнить ячейку Уровня подчиненности.
У каждого Менеджера тоже есть свой как бы Начальник. Получается иерархия.
Например:
ID Менеджера = 14369
Листаем вниз, и видим что ID  его Менеджера 11882
А у 11882 начальник 11881 и так далее....

Логику я вижу следующим образом:

Берем содержимое ячейки В2. Далее проверяем весь столбец А, находим там значение ячейки В2. Допустим это некая ячейка А"Х". Смотрим значение В"Х", и снова проверяем весь столбец А на наличие в нем В"Х". Получается несколько циклов, пока не выдаст ошибку что в столбце А такого значения нет. Для каждого значения столбца В мы посчитаем количество таких циклов, это и будет искомый уровень.

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

Нашел похожую тему: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=41697
Но в ней задача графически показать иерархию. Мне нужно нужно только количество.

Буду признателен за помощь. Спасибо.
 
Я думаю можно сделать на словаре. Вот только файла нет...
 
del
Изменено: PooHkrd - 13.06.2018 13:01:58
Вот горшок пустой, он предмет простой...
 
Название темы должно отражать суть задачи. Предложите новое. Модераторы заменят.
 
Еще одна вещь, которая поможет - у меня к каждому Менеджеру есть число его прямых подчиненных, таким образом цикл возможно, если получится, как-то ограничить этим числом.
 
Написал код на своём файле, у меня работает. Учтёте замечание - покажу.
 
Цитата
Hugo написал:
Написал код на своём файле, у меня работает. Учтёте замечание - покажу.
Спасибо, вы имеете в виду прикрепить исходный файл?
Изменено: Kivrus - 13.06.2018 13:31:16
 
и ещё, полагаю
Цитата
vikttur написал:
Название темы должно отражать суть задачи. Предложите новое. Модераторы заменят.
 
Под Ваш файл ничего корректировать уже не буду, поздно :)
 
по идее все решается элементарной  =VLOOKUP(B2;A:C;3)+1 и включением итераций.
По вопросам из тем форума, личку не читаю.
 
Для Модеров - тему я бы назвал "Подсчет уровней подчиненности таблицы Сотрудник-Начальник"
а файл прикрепил
спасибо
 
Как обещал:
Код
Sub tttt()
    Dim a, b, i&, k&, t$
    a = [a1].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a)
            t = Trim(a(i, 1))
            .Item(t) = Trim(a(i, 2))
        Next
        For i = 2 To UBound(a)
            k = 0: t = Trim(a(i, 2))
            Do
                If .exists(t) Then
                    k = k + 1: t = .Item(t)
                Else
                    t = ""
                End If
            Loop While Len(t)
            a(i, 3) = k
        Next
    End With
    [a1].CurrentRegion.Value = a

End Sub


Подогнал Ваш файл под код (писал по рисунку), проверил по первому подчинённому - правильно.
 
=IF(B2="NO_MANAGER";"0";INDEX(D:D;MATCH(B2;A:A;0))+1)
По вопросам из тем форума, личку не читаю.
 
Как эта формула работает - не понимаю, но с моим результатом совпадает.
Совпадает всё, кроме "0" - у меня это число :)
 
Hugo, с нулем это я перебрал :-)
По вопросам из тем форума, личку не читаю.
 
Спасибо! Теперь осталось разобраться в самой формуле )) но я это я уже сам (если кто не понял)

А о чем вы с Hugo что насчет нуля переписывались? Я не совсем понял..
Изменено: Kivrus - 13.06.2018 16:26:30
 
Цитата
Kivrus написал: А о чем вы с Hugo что насчет нуля переписывались?
Не берите в голову, это мы счетчик сообщений инкрементим. :-) , а серьезно, то  
или =IF(B2="NO_MANAGER";"";INDEX(D:D;MATCH(B2;A:A;0))+1)
или =IF(B2="NO_MANAGER";0;INDEX(D:D;MATCH(B2;A:A;0))+1)
или =IF(B2="NO_MANAGER";;INDEX(D:D;MATCH(B2;A:A;0))+1)
я ноль в кавычки случайно вставил.
Изменено: БМВ - 13.06.2018 16:27:27
По вопросам из тем форума, личку не читаю.
 
Спасибо всем еще раз большое за помощь. Очень красивое решение - как по макросу, так и по формуле.
 
Действительно, красиво! Не встречал ранее формульного аналога иерархических запросов (Oracle).
Владимир
 
БМВ,
Попробовал с вашим первым вариантом  =VLOOKUP(B2;A:C;3)+1
Работает аналогично! Вроде бы и ошибок нет.
 
Kivrus, только надо последний аргумент добавить 0. Но мне казалось, что ругалось на рекурсивную ссылку. Сейчас проверил , все ок
=IF(B4="NO_MANAGER";;VLOOKUP(B4;A:D;4;0)+1)
Изменено: БМВ - 13.06.2018 20:11:22
По вопросам из тем форума, личку не читаю.
 
БМВ,да, именно так и делал...с 0 в конце все ОК
Эх, снова рекурсия.... постоянно на нее натыкаюсь. Пока С изучал то замаялся чтобы разобраться в снежинке Коха... и тут опять.
 
Kivrus, Ну скажем так, рекурсия рекурсии рознь. Если в программах рекурсия позволяет крохотным кодом обсчитать огромные объёмы, то  в таблице это зацикливание и ошибка, да и то правильное использование итерации позволяет очень эффективно подбор делать даже в таблицах.

sokol92, красоты нет, прозаично все, хотя и неожиданно рабочее :-).
По вопросам из тем форума, личку не читаю.
 
Красота - понятие субъективное (что хорошо для человечества). Еще вариация на тему известной функции Oracle - цепочка менеджеров (через слэш):

=ЕСЛИ(B2="NO_MANAGER";A2;A2 & "/" & ВПР(B2;A:C;3;ЛОЖЬ))
Владимир
Страницы: 1
Наверх