Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Office Новый топик    Ответить
 Не могу понять, как работает готовая формула ранжирования  [new]
туплю_
Guest
Excel.
Помогите разобраться в формуле.
Для задачи вида: есть ряд чисел неупорядоченный в колонке A (например 2 5 3 8 3 7)
нужно присвоить ранг(место) без пропусков, от меньшего к большему, повторяющимся числам - одинаковое место.
Т.е. если упорядочить исходный 2 3 3 5 8 7, то места соотв-но будут у них: 1 2 2 3 4 5 (для 2ки -> 1, для 3ки -> 2, для 5ки->3 ...)

На просторах найдена формула, как это сделать. НЕ как формула массива, а обычная.
=СЧЁТ(1/ЧАСТОТА((A1>=A$1:A$6)*A$1:A$6;A$1:A$6))
Она вроде работает, но не хватает мозгов или опыта с excel-ем понять, как, что за логика заложена.
1 ноя 17, 15:27    [20919225]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
туплю_
Guest
опечатался в исходном, правильно так : "Т.е. если упорядочить исходный как 2 3 3 5 7 8"
1 ноя 17, 15:30    [20919236]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2736
=РАНГ()
2 ноя 17, 10:20    [20921168]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
туплю_
Guest
PlanB
=РАНГ()

ранг() будет с пропусками, т.е. места он выдаст не подряд: в указанном примере выдаст для 2ки->1, для 3ки ->2 (дважды),
а вот 3е место никому не даст, для 5ки уже даст не 3е, а 4е место.
А надо подряд, что указанная хитроумная конструкция и делает.

Как в отдельности работает сами по себе обычно функции СЧЕТ, ЧАСТОТА - понимаю, а вот в этой конструкции - не понимаю.
Какой-то искусственный разум это придумал )
2 ноя 17, 10:47    [20921251]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
туплю_
Guest
ну чтож, видать не только моего iq не хватает ))
хотя основная логика конечно понятна, чуть додумать только

Пригодится может кому формула )
7 ноя 17, 09:17    [20931358]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
Казанский
Member

Откуда:
Сообщений: 1290
Смотрите этапы вычисления формулы в файле.
Часть (A1>=A$1:A$6)*A$1:A$6 довольно очевидна - замена на 0 чисел, которые больше того, для которого определяется ранг. Кол-во чисел, отличных от 0, равно искомому рангу, если убрать повторы.
Определением повторов и занимается функция ЧАСТОТА. Если массив данных совпадает с массивом интервалов И все значения различны, функция возвращает массив вида {1:1:1:...:1:0}, т.е. каждое число попадает в "свой" интервал, кол-во единиц совпадает с кол-вом чисел.
Если в массиве есть один повтор, результат имеет вид {1:2:1:...:0:1:0}, потому что оба одинаковых числа попали в "свой" интервал, а следующий интервал имеет нулевой размер, в него ничего не может попасть. То есть кол-во чисел, отличных от 0, уменьшилось на 1. Если будет два повтора, кол-во не-нулей уменьшится еще на 1, и т.д. Таким образом, кол-во не-нулей в массиве, возвращаемом функцией ЧАСТОТА, равно искомому рангу.
Подсчет не-нулей в массиве производит функция СЧЁТ(1/{массив}) - при делении 0 превращается в значение ошибки и не входит в счет.

Кстати, формула некорректно работает с отрицательными числами.

Искусственный разум, который придумывает такие формулы, сосредоточен в основном тут: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=list&FID=1

К сообщению приложен файл (Ранг-Частота.xlsx - 9Kb) cкачать
31 дек 17, 02:01    [21075658]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
pobnn
Member

Откуда:
Сообщений: 4
Здравствуйте, подскажите, пожалуйста, как нужно изменить эту формулу =СЧЁТ(1/ЧАСТОТА((A1>=A$1:A$6)*A$1:A$6;A$1:A$6)), чтобы ранжирование было от большего к меньшему, т.е. наибольшее число имело порядковый номер 1 и т.д.??
22 дек 18, 18:30    [21771122]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
The_Prist
Member

Откуда: www.excel-vba.ru
Сообщений: 1806
pobnn,

так элементарно же - просто знак поменять:
=СЧЁТ(1/ЧАСТОТА((A1<=A$1:A$6)*A$1:A$6;A$1:A$6))
Оно?
22 дек 18, 19:39    [21771146]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
pobnn
Member

Откуда:
Сообщений: 4
The_Prist, я тоже так предположила, но практика показала, что результат не верный(((
22 дек 18, 19:47    [21771152]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
The_Prist
Member

Откуда: www.excel-vba.ru
Сообщений: 1806
The_Prist,

косякнул. Знак равно не убрал:
=СЧЁТ(1/ЧАСТОТА((A1<A$1:A$6)*A$1:A$6;A$1:A$6))
22 дек 18, 19:52    [21771156]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
pobnn
Member

Откуда:
Сообщений: 4
The_Prist, не подумала об этом. Работаем, спасибо
22 дек 18, 20:08    [21771163]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять, как работает готовая формула ранжирования  [new]
pobnn
Member

Откуда:
Сообщений: 4
Работает*
22 дек 18, 20:09    [21771164]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Office Ответить