Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Ранжирование объектов - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Ранжирование объектов (Формулы/Formulas)
Ранжирование объектов
Yanush Дата: Пятница, 07.04.2017, 12:21 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день, Коллеги!
Прошу помощи в решении задачи, суть которой состоит в следующем:

Необходимо провести ранжирование объектов наблюдения по следующим условиям.
1. Все что больше "1", ранг присваивается в порядке убывания, начиная с "16" с шагом "1".
2. Все что меньше "1", ранг присваивается в порядке убывания, начиная с "-1" с шагом "1".
3. Если значения у двух или более объектов совпадают, то присваивается средний ранг, равный среднему арифметическому рангов, подлежащих распределению между указанными объектами.
4. Функция ранг.ср недоступна, т.к. Excel 2007.

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

Можно ли повторить автоматический расчет без вспомогательных ячеек или существует иное решение данной задачи?

---
С Уважением, Yanush
К сообщению приложен файл: 5325583.xls (52.5 Kb)
 
Ответить
СообщениеДобрый день, Коллеги!
Прошу помощи в решении задачи, суть которой состоит в следующем:

Необходимо провести ранжирование объектов наблюдения по следующим условиям.
1. Все что больше "1", ранг присваивается в порядке убывания, начиная с "16" с шагом "1".
2. Все что меньше "1", ранг присваивается в порядке убывания, начиная с "-1" с шагом "1".
3. Если значения у двух или более объектов совпадают, то присваивается средний ранг, равный среднему арифметическому рангов, подлежащих распределению между указанными объектами.
4. Функция ранг.ср недоступна, т.к. Excel 2007.

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

Можно ли повторить автоматический расчет без вспомогательных ячеек или существует иное решение данной задачи?

---
С Уважением, Yanush
Автор - Yanush
Дата добавления - 07.04.2017 в 12:21
AlexM Дата: Пятница, 07.04.2017, 13:32 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Формулу возможно можно оптимизировать
Код
=((17-2*(B3<1)-РАНГ(B3;B$3:B$18))*СЧЁТЕСЛИ(B$3:B$18;B3)-СЧЁТЕСЛИ(B$3:B$18;B3)+1)/СЧЁТЕСЛИ(B$3:B$18;B3)
К сообщению приложен файл: 5325583_01.xls (55.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеФормулу возможно можно оптимизировать
Код
=((17-2*(B3<1)-РАНГ(B3;B$3:B$18))*СЧЁТЕСЛИ(B$3:B$18;B3)-СЧЁТЕСЛИ(B$3:B$18;B3)+1)/СЧЁТЕСЛИ(B$3:B$18;B3)
Автор - AlexM
Дата добавления - 07.04.2017 в 13:32
sboy Дата: Пятница, 07.04.2017, 13:37 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
формула массива (пока не усредняет ранг одинаковых значений)
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;9^9;$B$3:$B$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($B$3:$B$18;"<1");-1*ПОИСКПОЗ(B3;НАИБОЛЬШИЙ(ЕСЛИ($B$3:$B$18<1;$B$3:$B$18;-9^9);СТРОКА($1:$16));))
К сообщению приложен файл: 5147739.xls (56.5 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
формула массива (пока не усредняет ранг одинаковых значений)
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;9^9;$B$3:$B$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($B$3:$B$18;"<1");-1*ПОИСКПОЗ(B3;НАИБОЛЬШИЙ(ЕСЛИ($B$3:$B$18<1;$B$3:$B$18;-9^9);СТРОКА($1:$16));))
Автор - sboy
Дата добавления - 07.04.2017 в 13:37
AlexM Дата: Пятница, 07.04.2017, 13:39 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Когда три совпадения округляет не правильно. Постараюсь исправить.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКогда три совпадения округляет не правильно. Постараюсь исправить.
Автор - AlexM
Дата добавления - 07.04.2017 в 13:39
Yanush Дата: Пятница, 07.04.2017, 13:53 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Цитата AlexM, 07.04.2017 в 13:39, в сообщении № 4 ()
Когда три совпадения округляет не правильно. Постараюсь исправить.


да, поэтому и было принято решение, что бы максимально упростить расчет и выполнить его пошагово.
 
Ответить
Сообщение
Цитата AlexM, 07.04.2017 в 13:39, в сообщении № 4 ()
Когда три совпадения округляет не правильно. Постараюсь исправить.


да, поэтому и было принято решение, что бы максимально упростить расчет и выполнить его пошагово.
Автор - Yanush
Дата добавления - 07.04.2017 в 13:53
AlexM Дата: Пятница, 07.04.2017, 13:58 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Кажется получилось, проверяйте.
Формула массива
Код
=СУММ(18-2*(B3<1)-РАНГ(B3;B$3:B$18)-СТРОКА(A$1:ИНДЕКС(A:A;СЧЁТЕСЛИ(B$3:B$18;B3))))/СЧЁТЕСЛИ(B$3:B$18;B3)
PS. Расчет пошагово делать не так интересно.
К сообщению приложен файл: 5325583_02.xls (56.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Пятница, 07.04.2017, 14:03
 
Ответить
СообщениеКажется получилось, проверяйте.
Формула массива
Код
=СУММ(18-2*(B3<1)-РАНГ(B3;B$3:B$18)-СТРОКА(A$1:ИНДЕКС(A:A;СЧЁТЕСЛИ(B$3:B$18;B3))))/СЧЁТЕСЛИ(B$3:B$18;B3)
PS. Расчет пошагово делать не так интересно.
Автор - AlexM
Дата добавления - 07.04.2017 в 13:58
Yanush Дата: Пятница, 07.04.2017, 14:06 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Цитата AlexM, 07.04.2017 в 13:58, в сообщении № 6 ()
Кажется получилось, проверяйте.
Формула массива


К сожалению формула не совсем верно работает при изменении данных выдает "0", а этого быть не может.

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

Цитата AlexM, 07.04.2017 в 13:58, в сообщении № 6 ()
S. Расчет пошагово делать не так интересно.

Согласен, поэтому и обратился)
К сообщению приложен файл: 6745183.xls (56.0 Kb)
 
Ответить
Сообщение
Цитата AlexM, 07.04.2017 в 13:58, в сообщении № 6 ()
Кажется получилось, проверяйте.
Формула массива


К сожалению формула не совсем верно работает при изменении данных выдает "0", а этого быть не может.

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

Цитата AlexM, 07.04.2017 в 13:58, в сообщении № 6 ()
S. Расчет пошагово делать не так интересно.

Согласен, поэтому и обратился)
Автор - Yanush
Дата добавления - 07.04.2017 в 14:06
_Boroda_ Дата: Пятница, 07.04.2017, 14:10 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Такой вариант
Код
=(РАНГ(B3;B$3:B$18;1)+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)*-1^(B3<1)

Не, отрицательные неверно
К сообщению приложен файл: 5325583_1.xls (53.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТакой вариант
Код
=(РАНГ(B3;B$3:B$18;1)+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)*-1^(B3<1)

Не, отрицательные неверно
Автор - _Boroda_
Дата добавления - 07.04.2017 в 14:10
AlexM Дата: Пятница, 07.04.2017, 14:13 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Yanush, вы когда данные меняете, то и столбец с ручным расчетом меняйте.
Но формула действительно неверно считает.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Пятница, 07.04.2017, 14:14
 
Ответить
СообщениеYanush, вы когда данные меняете, то и столбец с ручным расчетом меняйте.
Но формула действительно неверно считает.
Автор - AlexM
Дата добавления - 07.04.2017 в 14:13
Yanush Дата: Пятница, 07.04.2017, 14:16 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
AlexM, "столбец Ранг (ручной расчет)" можно скрыть. подавался в качестве примера
 
Ответить
СообщениеAlexM, "столбец Ранг (ручной расчет)" можно скрыть. подавался в качестве примера
Автор - Yanush
Дата добавления - 07.04.2017 в 14:16
Yanush Дата: Пятница, 07.04.2017, 14:17 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Цитата _Boroda_, 07.04.2017 в 14:10, в сообщении № 8 ()
Не, отрицательные неверно

Да, по отрицательным значениям должна быть другая последовательность от "-1" (не включая) по убыванию


Сообщение отредактировал Yanush - Пятница, 07.04.2017, 14:36
 
Ответить
Сообщение
Цитата _Boroda_, 07.04.2017 в 14:10, в сообщении № 8 ()
Не, отрицательные неверно

Да, по отрицательным значениям должна быть другая последовательность от "-1" (не включая) по убыванию
Автор - Yanush
Дата добавления - 07.04.2017 в 14:17
sboy Дата: Пятница, 07.04.2017, 14:27 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
вот так вроде получилось, проверяйте (массивная)
Код
=ЕСЛИОШИБКА((ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;9^9;$B$3:$B$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($B$3:$B$18;"<1"))-(1-1/СЧЁТЕСЛИ($B$3:$B$18;B3));-1*ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;$B$3:$B$18;9^9);СТРОКА($1:$16));)-(1-1/СЧЁТЕСЛИ($B$3:$B$18;B3)))
К сообщению приложен файл: 4054370.xls (60.5 Kb)

Яндекс: 410016850021169
 
Ответить
Сообщениевот так вроде получилось, проверяйте (массивная)
Код
=ЕСЛИОШИБКА((ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;9^9;$B$3:$B$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($B$3:$B$18;"<1"))-(1-1/СЧЁТЕСЛИ($B$3:$B$18;B3));-1*ПОИСКПОЗ(B3;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$18<1;$B$3:$B$18;9^9);СТРОКА($1:$16));)-(1-1/СЧЁТЕСЛИ($B$3:$B$18;B3)))
Автор - sboy
Дата добавления - 07.04.2017 в 14:27
Yanush Дата: Пятница, 07.04.2017, 14:33 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Цитата sboy, 07.04.2017 в 14:27, в сообщении № 12 ()
вот так вроде получилось, проверяйте (массивная)

Благодарю, однако по отрицательным рангам средние значения не верно рассчитываются, т.к. ранг может быть либо целым числом, либо целым с десятичной частью кратной "0.5".

В частности по трем отрицательным рангам: к примеру:

Значения Сквозной ранг Итоговый ранг
0,988 -1 -2
0,988 -2 -2
0,988 -3 -2
 
Ответить
Сообщение
Цитата sboy, 07.04.2017 в 14:27, в сообщении № 12 ()
вот так вроде получилось, проверяйте (массивная)

Благодарю, однако по отрицательным рангам средние значения не верно рассчитываются, т.к. ранг может быть либо целым числом, либо целым с десятичной частью кратной "0.5".

В частности по трем отрицательным рангам: к примеру:

Значения Сквозной ранг Итоговый ранг
0,988 -1 -2
0,988 -2 -2
0,988 -3 -2
Автор - Yanush
Дата добавления - 07.04.2017 в 14:33
_Boroda_ Дата: Пятница, 07.04.2017, 14:38 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
А так?
Код
=(B3<1)*(16-СЧЁТЕСЛИ(B$3:B$18;"<1"))--1^(B3>1)*(РАНГ(B3;B$3:B$18;1-(B3<1))+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА так?
Код
=(B3<1)*(16-СЧЁТЕСЛИ(B$3:B$18;"<1"))--1^(B3>1)*(РАНГ(B3;B$3:B$18;1-(B3<1))+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)
Автор - _Boroda_
Дата добавления - 07.04.2017 в 14:38
Yanush Дата: Пятница, 07.04.2017, 14:42 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, почти только в случае равенства значения "1", ранг должен быть еще положительным)
 
Ответить
Сообщение_Boroda_, почти только в случае равенства значения "1", ранг должен быть еще положительным)
Автор - Yanush
Дата добавления - 07.04.2017 в 14:42
sboy Дата: Пятница, 07.04.2017, 14:43 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Цитата Yanush, 07.04.2017 в 14:33, в сообщении № 13 ()
Значения Сквозной ранг Итоговый ранг
0,988 -1 -2
0,988 -2 -2
0,988 -3 -2

а положительных должно быть ?
1,988 2,33
1,988 2,33
1,988 2,33

Яндекс: 410016850021169
 
Ответить
Сообщение
Цитата Yanush, 07.04.2017 в 14:33, в сообщении № 13 ()
Значения Сквозной ранг Итоговый ранг
0,988 -1 -2
0,988 -2 -2
0,988 -3 -2

а положительных должно быть ?
1,988 2,33
1,988 2,33
1,988 2,33
Автор - sboy
Дата добавления - 07.04.2017 в 14:43
Yanush Дата: Пятница, 07.04.2017, 14:44 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
sboy, конечно и для положительных, и для отрицательных
 
Ответить
Сообщениеsboy, конечно и для положительных, и для отрицательных
Автор - Yanush
Дата добавления - 07.04.2017 в 14:44
_Boroda_ Дата: Пятница, 07.04.2017, 14:45 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата Yanush, 07.04.2017 в 14:42, в сообщении № 15 ()
в случае равенства значения "1", ранг должен быть еще положительным)


Да не вопрос
Код
=(B3<1)*(16-СЧЁТЕСЛИ(B$3:B$18;"<1"))--1^(B3>=1)*(РАНГ(B3;B$3:B$18;1-(B3<1))+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Yanush, 07.04.2017 в 14:42, в сообщении № 15 ()
в случае равенства значения "1", ранг должен быть еще положительным)


Да не вопрос
Код
=(B3<1)*(16-СЧЁТЕСЛИ(B$3:B$18;"<1"))--1^(B3>=1)*(РАНГ(B3;B$3:B$18;1-(B3<1))+(СЧЁТ(B$3:B$18)+1-РАНГ(B3;B$3:B$18;0)-РАНГ(B3;B$3:B$18;1))/2)
Автор - _Boroda_
Дата добавления - 07.04.2017 в 14:45
sboy Дата: Пятница, 07.04.2017, 14:48 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Yanush, понял, - на + забыл поменять для отрицательных
Код
=ЕСЛИОШИБКА((ПОИСКПОЗ(C7;НАИМЕНЬШИЙ(ЕСЛИ($C$3:$C$18<1;9^9;$C$3:$C$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($C$3:$C$18;"<1"))-(1-1/СЧЁТЕСЛИ($C$3:$C$18;C7));-1*ПОИСКПОЗ(C7;НАИМЕНЬШИЙ(ЕСЛИ($C$3:$C$18<1;$C$3:$C$18;9^9);СТРОКА($1:$16));)+(1-1/СЧЁТЕСЛИ($C$3:$C$18;C7)))
К сообщению приложен файл: 2528835.xls (60.5 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеYanush, понял, - на + забыл поменять для отрицательных
Код
=ЕСЛИОШИБКА((ПОИСКПОЗ(C7;НАИМЕНЬШИЙ(ЕСЛИ($C$3:$C$18<1;9^9;$C$3:$C$18);СТРОКА($1:$16)))+СЧЁТЕСЛИ($C$3:$C$18;"<1"))-(1-1/СЧЁТЕСЛИ($C$3:$C$18;C7));-1*ПОИСКПОЗ(C7;НАИМЕНЬШИЙ(ЕСЛИ($C$3:$C$18<1;$C$3:$C$18;9^9);СТРОКА($1:$16));)+(1-1/СЧЁТЕСЛИ($C$3:$C$18;C7)))
Автор - sboy
Дата добавления - 07.04.2017 в 14:48
Yanush Дата: Пятница, 07.04.2017, 14:53 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 16
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, благодарю это то, что нужно!
Забегая вперед, правильно ли я понимаю, что в случае обратной сортировки необходимо изменить "<" на ">" и порядок формулы РАНГ?

То есть данная формула работает в случае если показатель стремиться к улучшению, а если к ухудшению? То есть чем меньше, тем лучше, а если больше "1", то отрицательные значения?
 
Ответить
Сообщение_Boroda_, благодарю это то, что нужно!
Забегая вперед, правильно ли я понимаю, что в случае обратной сортировки необходимо изменить "<" на ">" и порядок формулы РАНГ?

То есть данная формула работает в случае если показатель стремиться к улучшению, а если к ухудшению? То есть чем меньше, тем лучше, а если больше "1", то отрицательные значения?
Автор - Yanush
Дата добавления - 07.04.2017 в 14:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Ранжирование объектов (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!