ранг работника

[Олег Корзников]

есть таблица в которой первый столбец работники(от 0 до 100 чел) второй выполнение заданий (от 0 до 200)
Для определения ранга используется формула =РАНГ
но столкнулись с тем что могут быть работники с одним рангом
в другой таблице с помощью функции =ВПР (из первой таблицы) все работники выставлены в порядке возрастания ранга вот тут и начинается проблема.
Люди с одним рангом выбирается только первый  работник, а надо чтобы люди с одним рангом шли в алфавитном порядке, далее пошли люди со следующим рангом.

[MCH]

для определения ранга используйте формулу:

Код Выделить Развернуть

=РАНГ(B2;$B$2:$B$21)+СЧЁТЕСЛИ(B$2:B2;B2)-1

[zs]

Здравствуйте, Олег Корзников. Продолжение темы:чтобы люди с одним рангом шли в алфавитном порядке-по первой букве.
=ЕСЛИ(СЧЁТЕСЛИ($C$25:$C$44;C25)>1;ЕСЛИ(И(СЧЁТЕСЛИ(C25:$C$44;C25)>1;СЧЁТЕСЛИ(F25:ИНДЕКС($F$25:$F$44;ПОИСКПОЗ(C25;$C$25:$C$44));F25)>1);C25+РАНГ(КОДСИМВ(B25);F25:ИНДЕКС($F$25:$F$44;ПОИСКПОЗ(C25;$C$25:$C$44));1)-1+СЧЁТЕСЛИ(F25:ИНДЕКС($F$25:$F$44;ПОИСКПОЗ(F25;$C$25:$C$44));F25)-1;C25+РАНГ(КОДСИМВ(B25);F25:ИНДЕКС($F$25:$F$44;ПОИСКПОЗ(C25;$C$25:$C$44));1)-1);A25)
Р.S. По второй букве тоже самое и без дополнительных столбцов, наверное не обойдешься)))
С уважением, ZS5

[MCH]

Продолжение темы:чтобы люди с одним рангом шли в алфавитном порядке ...

zs, зачем так сложно, если нужно чтобы при равных количествах заданий происходила сортировка по алфавиту, то достаточно для определения ранга использовать формулу:

Код Выделить Развернуть

=РАНГ(B2;$B$2:$B$21)+СЧЁТЕСЛИМН(B$2:B$21;B2;A$2:A$21;"<"&A2)
или

Код Выделить Развернуть

=РАНГ(B2;$B$2:$B$21)+СУММПРОИЗВ((B$2:B$21=B2)*(A$2:A$21<A2))

[zs]

Здравствуйте, MCH. В представленных формулах не прослеживается алгоритм анализа по алфавиту, поэтому заменяя, например, фамилию "Новиков" на "Яковлев" ранг остается прежним. Поэтому без ориентирования на алфавит любые попытки сортировки по алфавиту будут неверны (хотя частный случай может и совпадать))), а если ориентироваться на алфавит, то необходима формула (алгоритм) приведенная мною, при этом не исключается вариант формулировки в более компактном виде, но это уже задача оптимизации формулы,))) что является мотивацией пользователей форума к поиску решений в Excel.
С уважением, ZS5

[MCH]

В представленных формулах не прослеживается алгоритм анализа по алфавиту, поэтому заменяя, например, фамилию "Новиков" на "Яковлев" ранг остается прежним.

Почему же нет анализа по алфавиту?
Эта часть второй формулы: (A$2:A$21<A2) - отвечает за сортировку по алфавиту
Поменяв "Новикова" на "Яковлева" ничего не меняется, только потому, что из трех фамилий (Голубев, Зайцев, Новиков) с одинаковым количеством заданий Новиков последний по алфавиту, нет смысла менять его на Яковлева, попробуйте лучше поменять Голубева на Яковлева

Пример с формулой во вложении

[zs]

Поменяв "Новикова" на "Яковлева" ничего не меняется, только потому, что из трех фамилий (Голубев, Зайцев, Новиков) с одинаковым количеством заданий Новиков последний по алфавиту, нет смысла менять его на Яковлева, попробуйте лучше поменять Голубева на Яковлева

Действительно работает! Извиняюсь - это не для средних умов.) Но в случае, если имеет место однофамильцы с одинаковым количеством заданий необходима все таки формула побольше - гибрид предложенных вариантов
=ЕСЛИ(СЧЁТЕСЛИ(A14:$A$21;A14)>1;РАНГ(B14;$B$2:$B$21)+СУММПРОИЗВ((B$2:B$21=B14)*(A$2:A$21<A14))+СЧЁТЕСЛИ(B14:$B$21;B14)-1;РАНГ(B14;$B$2:$B$21)+СУММПРОИЗВ((B$2:B$21=B14)*(A$2:A$21<A14)))
P.S. Если не трудно, можно разъяснить каким образом часть второй формулы: (A$2:A$21<A2) - отвечает за сортировку по алфавиту.
С уважением, ZS5

[Serge 007]

...каким образом часть второй формулы: (A$2:A$21<A2) - отвечает за сортировку по алфавиту.

zs, как Excel выполняет сортировку?
Так же и в формуле

[MCH]

Но в случае, если имеет место однофамильцы с одинаковым количеством заданий необходима все таки формула побольше ...

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

[zs]

(имена и отчества должны отличатся)

Здравствуйте, MCH
ОК))) Можно ранг определить и по ранее предложенному варианту в виде:
=СУММ(РАНГ(B16;$B$2:$B$21);СУММ((B$2:B$21=B16)*(A$2:A$21<A16));СЧЁТЕСЛИ(B16:$B$21;B16)-1)
С уважением, ZS5