Использовать функцию РАНГ для диапазона ячеек на основе критериев в отдельном диапазоне ячеек

У меня есть вопрос относительно функции RANK в MS Excel 2010. У меня есть большой рабочий лист, строки которого я хочу ранжировать на основе значений в столбце. Эти значения могут быть положительными или отрицательными. Я нашел полезный совет здесь, который объясняет, как ранжировать значения в столбце, исключая все значения, равные нулю, из ранжирования и подсчета ранжирования. Они используют следующую формулу:

IF(O24<0, RANK(O24,$O$24:$O$29) - COUNTIF($O$24:$O$29,0), IF(O24=0, "", RANK(O24,$O$24:$O$29)))

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

Возможно ли что-то подобное и как мне это сделать? Как мне обновить приведенный выше пример формулы, чтобы изменения сработали? Заранее большое спасибо за вашу помощь.

P.S .: Я попытался поставить таблицу, но ничего не вышло, извините ...


excel excel-2010 rank
person rf2012    schedule 12.04.2013    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вы можете использовать функцию COUNTIFS для ранжирования на основе условия в другом столбце, например. эта формула в строке 24 скопирована [отредактирована, чтобы включить дополнительный IF)

=IF(O24=0,"",IF(N24="x",COUNTIFS(O$24:O$29,">"&O24,O$24:O$29,"<>0",N$24:N$29,"x")+1,""))

Это будет ранжироваться от высокого к низкому, где столбец N = "x", игнорируя нулевые значения.

См. этот пример, столбцы N и O содержат случайные значения - нажмите F9 для повторного создания новые случайные значения и результаты формул в столбце Q изменятся соответственно

person barry houdini    schedule 12.04.2013
comment
Спасибо, это выглядит очень интересно, но я пока не мог заставить его работать. Скажем, я не хочу исключать нулевые значения, а только ранжирую на основе строки N. Я хочу только ранжировать значения в строке O, если соответствующее значение в строке N составляет ›10. Как мне тогда обновить вашу формулу? - person rf2012; 12.04.2013
comment
Извините, это не совсем сработает, как написано (вам нужен дополнительный IF), попробуйте эту версию для вашего предложения =IF(N24>10,COUNTIFS(O$24:O$29,">"&O24,N$24:N$29,">10")+1,"") - person barry houdini; 13.04.2013
comment
Ура, круто! Теперь это работает тем, что возвращает 1 для каждого значения в строке O, которое содержит значение ›10 в строке N. Но как мне теперь подойти к ранжированию этих оставшихся ячеек на основе их значений в строке O? - person rf2012; 13.04.2013
comment
Вы точно используете эту формулу? (Мне нужно отредактировать) Он не должен возвращать 1 для каждой строки, он должен ранжировать значения (COUNTIFS эффективно ранжирует), я попытаюсь прикрепить пример ... - person barry houdini; 13.04.2013
comment
Аааа, нет, забыл включить вторую ›10. Я извиняюсь, спасибо, я думаю, теперь он работает отлично !!! - person rf2012; 13.04.2013
comment
Я прикрепил пример ссылки в своем ответе выше - функция RANK на самом деле является только версией COUNTIF, подсчитывая, сколько чисел в диапазоне больше определенного числа, COUNTIFS позволяет добавлять больше условий, поэтому сама функция COUNTIFS ранжируется с состояние - person barry houdini; 13.04.2013
comment
Спасибо за объяснение, я этого раньше не знал. Как я уже сказал, я пропустил ошибку с моей стороны, которую я исправил, и теперь она работает до совершенства !! Еще раз спасибо! - person rf2012; 13.04.2013

arrow_upward
0
arrow_downward

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

Я предлагаю создать столбец, исключающий нули (предположим, что это столбец P): =IF(O24 = 0, "", O24)

Затем в столбце R, чтобы исключить отрицательные значения (этот шаг не нужен, но ваша исходная формула делает нечто подобное): =IF(P24 = "", "", P24 - MIN(0, MIN($O$24:$O$29)))

Теперь в столбце S добавьте свои самые новые критерии: =IF(OR(R24="", [enter newest criteria here]), "", R24)

Наконец, столбец T выполняет ранжирование только выбранных строк: =IF(S24="", "", RANK(S24, S$24:S$29))

Если показ столбцов P, R и S утомляет, вы всегда можете скрыть их.

person ExactaBox    schedule 12.04.2013
comment
Спасибо! Однако, если возможно, я бы предпочел сохранить всю формулу в одной строке. Есть ли способ объединить вашу формулу в одну, чтобы я мог попробовать ее на своем листе Excel? - person rf2012; 12.04.2013
comment
Суть моего ответа в том, что объединение всех этих формул в одну ячейку - плохая идея. Лучше разделить каждую часть на отдельную формулу. Это способствует удобству обслуживания, пониманию и упрощает редактирование в будущем. - person ExactaBox; 13.04.2013

arrow_upward
0
arrow_downward

Переформулировка ответа Барри Гудини в табличном формате. Value_Col - это столбец со значениями для ранжирования. Group_Column - столбец с группой по значениям для ранжирования внутри групп.

= COUNTIFS ([Value_Col], ">" & [@ [Value]], [Value_Column], "‹> 0 ", [Group_Column], [@ [Group]]) +1

person psychonomics    schedule 22.02.2016