Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ранг по сумме с учетом варьирования, ранжировать по сумме с учетом наименьшего варьирования чисел в диапазоне
 
#1
23.10.2018 11:56:53
Здравствуйте.
Имеется таблица рангов. Нужно на основании суммы по столбцам ранжировать результат по строкам, но при этом учитывать наименьшее варьирование (разброс) чисел в строке (по столбцам).
Можно ли сделать эту формулу, которая суммирует и ранжирует в одной ячейке, т.е. без дополнительного столбца с суммой?
Формула должна работать в Excell 2003
 
 
 
#2
23.10.2018 12:14:13
=СЧЁТЕСЛИ($G$4:$G$12;"<"&$G4)+СЧЁТЕСЛИ(ИНДЕКС($G:$G;СТРОКА($3:$3)+1):ИНДЕКС($G:$G;СТРОКА());$G4)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#3
23.10.2018 12:26:28
Вариант.
=РАНГ(G4;G$4:G$12;1)+СЧЁТЕСЛИ(G$4:G4;G4)-1
 
 
 
#4
23.10.2018 17:45:04
JayBhagavan, AleksSid, Спасибо, обе формулы работают.
P.S. Я так понял суммировать и ранжировать одновременно нельзя?
 
 
 
#5
23.10.2018 20:49:32
Можно 2в1:
=МИН(ЕСЛИ(СУММ(C4:F4)+СТРОКА(A1)*0,01=НАИМЕНЬШИЙ(МУМНОЖ($C$4:$F$12;{1:1:1:1})+(СТРОКА($C$4:$C$12)-3)*0,01;СТРОКА($C$4:$C$12)-3);СТРОКА($C$4:$C$12)-3))
Формула массива, водится тремя клавишами

Теперь сравните варианты и подумайте, стоит ли? )
 
 
 
#6
24.10.2018 09:09:37
vikttur, спасибо.
Цитата
стоит ли?
Иногда очень нужно. Чтобы в итоговой таблице были только информативные показатели, а скрыть колонку общей суммы или средней не возможно, так как над таблицей обширный материала исходных  данных (по ширине и высоте).

vikttur, поясните пожалуйста по формуле:
1) ссылка на ячейку А1 - обязательно должна быть на 3 строки выше результатов? Это связано с последующим числом в формуле -3? Пробовал опускать ниже (А2) и менять значения в формуле на "-2", но получается ошибка #ЧИСЛО!.
2) как правильно указывать массив2 "{1:1:1:1}" в функции МУМНОЖ, чтобы он соответствовал массиву1 "$C$4:$F$12", т.е. за что отвечают цифры и их количество через двоеточие в массиве2?
3) Преобразуйте пожалуйста формулу таким образом, что бы расчет вёлся по трём столбцам - для того, что бы я разобрался с принципом работы и мог её править под другие массивы.
4) Чтобы не начинать новую тему покажите пожалуйста как будет выглядеть формула, если нужно ранжировать не по сумме, а по среднему значению. Пробовал просто заменить в формуле функцию СУММ на СРЗНАЧ, но получаются нули.
 
 
 
#7
24.10.2018 10:36:07
0. Доп. столбец можно разместить где угодно (и скрыть), не обязательно в зоне данных
1. Можно ссылаться на любую ячейку первой строки, хоть ААА1. Для формулы во второй строке диапазона будет ссылка на строку 2... Номер нужен для разделения одинаковых сумм - к суммам добавляем дробную часть.
Для НАИМЕНЬШИЙ и для определения номера строки - СТРОКА($C$4:$C$12)-3, где 3 - номер строки перед первой строкой диапазона (например, СТРОКА($C$25:$C$320)-24)
2. Количество столбцов в массиве. Если много, можно задать массивом СТРОКА($1:$4)^0 (по количеству столбцов), ТРАНСП(СТОЛБЕЦ($A:$D))^0,  ТРАНСП($A4:$D4)^0 (при условии, что нет нулевых значений)
3. Теперь Вы уже знаете о столбцах и сами сможете изменить формулу
4. Другая тема. МУМНОЖ форирует массив сумм построчно.
 
 
 
#8
24.10.2018 11:14:51
vikttur, что-то я не разберусь с этой строкой.
Посмотрите, что не так сделал?
 
 
 
#9
24.10.2018 11:20:08
Внимательно прочитайте ответ на вопрос 1. Должно быть
СТРОКА(C1) (любая ячейка строки 1)
СТРОКА($F$11:$F$19)-10 (минус номер строки перед диапазоном)
 
 
 
#10
24.10.2018 11:45:29
vikttur, да, спасибо, я понял.
Просто думал, что первую строку можно задавать любую, путем изменения в начале формулы СТРОКА(C500) - и отсчет для НАИМЕНЬШИЙ будет от 500-ой строки, т.е.
Если массив СТРОКА($F$506:$F$529)-5 (минус 5 строк от указанной нами первой строки в начале формулы).
Чтобы не писать в формуле ...СТРОКА(C1)...СТРОКА($F$506:$F$529)-505
 
 
 
#11
24.10.2018 12:04:21
НАИМЕНЬШИ(A2:A200;СТРОКА($F$506:$F$529)-5) - формируем массив значений по возрастанию, состоящий из 24 элементов. Первый элемент массива -  501-е по величине значение :)
 
 
 
Страницы: 1
Наверх