Ранжирование в Excel с несколькими критериями. Ранжирование в excel

Ранжирование в Excel с несколькими критериями MS Excel онлайн

Например, мне нужно создать список достоинств нескольких учеников на основе итоговых оценок (столбец C ), затем более высокие оценки в математике (столбец B ) –

Ожидаемая позиция по заслугам приведена в графе D

Я могу использовать функцию RANK но могу сделать это только для одного столбца (общее число). Если общее число нескольких учеников одинаково, я не мог найти решения.

Вы можете попробовать это в D1

=COUNTIF($C$1:$C$99,">"&C1)+1+SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))

а затем скопируйте / заполните. Позвольте мне знать, если это помогает.

объяснение

Первые критерии находятся в столбце C , а второй – в столбце B. В основном, сначала он подсчитывает количество записей ($ C $ 1: $ C $ 99) , которые больше, чем сама запись ($ C1) . Для первого в рейтинге вы получите нуль , поэтому вам нужно добавить 1 к каждому результату (+1) . До сих пор вы получите двойное ранжирование, если у вас одинаковое значение дважды. Поэтому вам нужно добавить еще один аргумент для выполнения дополнительных вычислений на основе вторых критериев: Чтобы решить ситуацию с привязкой, вам нужно sumproduct две формулы массива и добавить результат к предыдущему аргументу, цель состоит в том, чтобы найти количество записей, которые равны этой записи, с помощью $ C $ 1: $ C $ 99 = C1 и иметь большее значение во втором столбце критериев $ B $ 1: $ B $ 99> B1 :

вы добавляете – для преобразования TRUE и FALSE в 0 с и 1 с, чтобы их можно было умножить: SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1)) первый массив – посмотреть, сколько у вас связей в первых критериях. И второй массив должен найти количество больших значений, чем сама запись.

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

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

=C2+B2/1000

Заполните, если необходимо. Теперь вы можете использовать обычную функцию RANK в этом вспомогательном столбце, например =RANK(D2, D$2:D$9) для своих ординалов рейтинга.

Очень просто (или, по крайней мере, намного проще, чем тот, который предоставляется лучшим ответом ) «математическое» решение: выполняйте линейную комбинацию с весами. Сделайте что-нибудь вроде

weighted_marks = 10*colC + colB

затем сортировать взвешенные метки, используя простую функцию ранга.

Он действительно решает вашу проблему, увеличивая ранжирование, в котором вы нуждаетесь.

Если вам не нравится ограничивать количество строк или числа, используемые в критериях, подход Jeeped может быть расширен. Вы можете использовать следующие формулы в ячейках от D2 до L2, считая, что есть три критерия: первая в столбце A, вторая в столбце B, а третья в столбце C:

=RANK($A2,$A:$A,1) =RANK($B2,$B:$B,1) =D2*2^27+E2 =RANK(F2,F:F,1) =RANK($C2,$C:$C,1) =G2*2^27+h3 =RANK(I2,I:I,1) =J2*2^27-ROW() =RANK(K2,K:K,0) , =RANK($A2,$A:$A,1) =RANK($B2,$B:$B,1) =D2*2^27+E2 =RANK(F2,F:F,1) =RANK($C2,$C:$C,1) =G2*2^27+h3 =RANK(I2,I:I,1) =J2*2^27-ROW() =RANK(K2,K:K,0)

Формулы должны быть скопированы. Результат – в столбце L. Связи разбиваются, используя номер строки.

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

Добавить новый критерий между столбцами C и D.
Вставьте три новых столбца между столбцами I и J.
Скопируйте столбцы G: I в новые столбцы J: L.
Скопируйте столбец G в столбец M, переписав его содержимое.
Измените формулу в столбце L, чтобы указать на новый критерий.

Коэффициент 2^27 используемый в формулах, уравновешивает точность 53 бит, доступных в числах с двойной точностью . Этого достаточно, чтобы охватить ограничение строки текущих версий Excel.

excel.bilee.com

Взвешенное ранжирование в excel

Я пытаюсь разработать надежный способ обеспечить средний балл оценки событий.

Я работаю с несколькими различными организациями. Событие произойдет, и я дам оценку оценки за событие, основанное на 2 критериях. Оценки критериев объединяются, чтобы дать общий оценочный балл для мероприятия.

Затем я хотел получить агрегированные оценки для организации на основе событий, связанных с этой организацией. Я использую формулы AVERAGEIFS, чтобы дать мне средний балл по 2 критериям для каждой организации. Однако, когда я использую формулу AVERAGEIFS для вычисления общей оценки оценки (которая является функцией 2-х критериев), результаты не дают правильного изображения. Сейчас несколько организаций имеют очень высокие оценки, но это связано с тем, что у них было только 1 или 2 связанных с ними событий; это заставляет эти организации выделяться, когда им этого не нужно. Мне нужно, чтобы построить на этом ниже формуле включить COUNT функции, либо добавить или удалить отступы

=AVERAGEIFS($C$2:$C$100, $B$2:$B$100, "Organization A", $C$2:$C$100, ">0")

Что-то, что подсчитывает количество раз появляется «Организация A» в колонке B, если это < 5 раз, то результат AVERAGEIFS будет умножен на 0,7 (или какое-либо другое случайное число), или если появится «Организация A»> 50 раз, чем AVERAGEIFS результат будет умножен на 1,2 и так далее.

Я включены предложения из комментариев структурировать по следующей формуле:

=AVERAGEIFS(C2:C100, B2:B100, "A", C2:C100, ">0") * IF(COUNTIF(B2:B100, "A") < 3, 0.7, IF(COUNTIF(B2:B100, "A") < 10, 0.9, IF(COUNTIF(B2:B100, "A") > 30, 1.1, IF(COUNTIF(B2:B100, "A") > 50, 1.2, 1))))

Эта формула работает, но по какой-то причине, используя данные, которые у меня есть, я знаю значение AVERAGEIFS для «A» должен умножиться на 1,2, так как в Col. B. имеется более 50 экземпляров «A». В результате приведенная выше формула дает умножения на 1.1.

Не знаете, где именно я ошибаюсь.

Кроме того, я расширил приведенную выше формулу, чтобы включить другие факторы отступов:

Здесь я получаю сообщение об ошибке: «Формула вы ввели содержит ошибку».

stackoverrun.com

Использование функции ранжирования в Excel

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

У меня есть базовая таблица для получения результатов пробного времени. Электронная таблица предназначена для мужчин и женщин. В принципе, очки присуждаются в течение самых быстрых раз в течение всех участников на 30-секундных интервалах, которые являются прекрасными (Cloumn N) ... (я это сделал)

Мой вопрос - В верхней части этого топ 7 мужчин в занимаемая должность получает дополнительные бонусные баллы, а топ-3 (только потому, что на соревнованиях меньше женщин, чем мужчин) женщины также получают дополнительные бонусные баллы.

Я создал столбец указать M или при добавлении конкурента F (колонка C), а также с использованием ранговых

=IF(G7=0,0,RANK(G7,$G$6:$G$36,1)-COUNTIF($G$6:$G$36,0))

по времени - Колонка K

Но я на самом деле борясь с тем, как использовать формулу для извлечения 7 лучших мужчин и 3 лучших женщин и награждать очки. Т.е. там будет 1-е место - 7-е место, а также 1-е место - 3-е место. Поэтому, по сути, я могу извлечь два набора рангов из идентификации F и M из соответствующего столбца.

На данный момент я могу получить базовый рейтинг и использовать инструкцию IF (AND), чтобы возвращать результаты для применения бонусных очков, если условия совпадают, но это не помогает с определением ранжирования по словам Male (1-й -7th) или Женский (первый-третий)

Вы также можете увидеть на моем экране отвала, что, хотя я добавил гавань формулу для назначения женских точек, что из-за условий выполнены, я не имею бонус баллы за 5-е место, потому что я поставил секс в F, и я надеялся, что кто-то может помочь мне с

Извините за болтовню, но я в настоящее время трудясь с этим 3-х дней, и я просто собираюсь в кругах

Действительно ценю любой ответ

excel function excel-formula excel-2010 rank 88

stackoverrun.com

function - Использование функции ранжирования в Excel

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

У меня есть базовая таблица для результатов пробных испытаний. Электронная таблица предназначена для мужчин и женщин. В основном, очки присуждаются в течение самых быстрых раз в течение всех участников на 30-секундных интервалах, которые являются штрафными (Cloumn N)... (я это сделал)

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

Я установил столбец для указания M или F (столбец C), когда участник добавлен, а также используя RANK

=IF(G7=0,0,RANK(G7,$G$6:$G$36,1)-COUNTIF($G$6:$G$36,0))

по времени - столбец K

Но я действительно борюсь с тем, как использовать формулу для извлечения 7 лучших мужчин и 3 лучших женщин и награждения очков. Т.е. там будет 1-е место - 7-е место, а также 1-е место - 3-е место. Поэтому, по сути, я могу извлечь два набора рангов из идентификации F и M из соответствующего столбца.

На данный момент я могу получить только базовый рейтинг и использовать оператор IF (AND), который я могу вернуть результаты для применения бонусных очков, если условия совпадают, но это не помогает идентифицировать ранжирование по словам Male (1-го-7-го) или Женский (1-й-3-й)

Вы также можете увидеть на моем экране дамп, что, хотя я не добавил формулу для присвоения женских очков, что из-за условий было выполнено, у меня нет бонусных баллов за 5-е место, потому что я задал секс F, и я надеялся, что кто-то может помочь я с

Извините за waffling, но я уже 3 года работаю, и я просто собираюсь в кругах

Очень ценю любой ответ

qaru.site

Как оперативно ранжировать риски в Excel

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

Как с помощью модели в Excel ранжировать риски

Модель Excel для ранжирования рисков состоит из двух таблиц. В первую предстоит ввести исходные данные (см. рисунок 1. Форма таблицы с исходными данными о рисках компании).

Вопрос: Когда оправданно ранжировать риски с помощью модели в Excel

Елена Сережкина , генеральный директор ООО «АРСЭ Консалтинг»

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

Вопрос: Когда может понадобиться ранжирование с помощью Excel

Елена Сережкина , генеральный директор ООО «АРСЭ Консалтинг»

Модель ранжирования в Excel может понадобиться при инвентаризации основных средств и дебиторской задолженности. С помощью нее можно оперативно выявить самые дорогостоящие активы предприятия и определить самых крупных должников компании.

Рисунок 1. Форма таблицы с исходными данными о рисках компании

В первой графе этой таблицы нужно указать номер риска, во второй – его название или описание, в третьей – причину его возникновения. Далее следует указать значения возможного ущерба в трех возможных сценариях развития событий (пессимистическом, наиболее вероятном и оптимистическом – в гр. 4, 6 и 8) и вероятности реализации каждого из сценариев. Последние должны быть отражены в графах 5, 7 и 9 (в сумме равны единице). Значение вероятности третьего сценария вносить не нужно, оно рассчитается автоматически. Средний ущерб, который может причинить каждый из выявленных рисков, также определяется автоматически.

Ранги рискам присваиваются во второй таблице на листе tabl2 (см. рисунок 2. Расчетная часть модели ранжирования рисков).

Рисунок 2. Расчетная часть модели ранжирования рисков

В этой таблице графы 2 и 3 (описание риска и причина возникновения) дублируют информацию из первой таблицы (см. рисунок 1. Форма таблицы с исходными данными о рисках компании). В графе 4 на основе исходных данных таблицы 1 по формуле (см. ячейку D5 на рисунке 2. Расчетная часть модели ранжирования рисков) автоматически определяется ожидаемое значение оценки риска (подробнее о том, как рассчитать эту величину, см. Как учесть несколько сценариев при расчете риска). В графе 5 таблицы 2 на основании данных о суммах ущерба по каждому из трех сценариев (см. таблицу 1 на рисунке 1. Форма таблицы с исходными данными о рисках компании), а также средней величины вероятного убытка по расчетной формуле автоматически определяется отклонение сумм ущерба в рамках этих сценариев от средней (см. рисунок 3. Расчет отклонения от средней суммы ущерба).

Рисунок 3. Расчет отклонения от средней суммы ущерба

Далее в графе 6 автоматически определяется итоговая оценка риска: сумма данных граф 4 и 5 (см. рисунок 2. Расчетная часть модели ранжирования рисков).

Итоговая расчетная величина, вычисляемая автоматически, – ранг риска (гр. 7 таблицы 2 на рисунке 2. Расчетная часть модели ранжирования рисков). Он определяется с помощью функции РАНГ. Например, для первого риска (в строке 1) эта формула выглядит так: =РАНГ(F5;$F$5:$F$36;), где: F5 – итоговая оценка этого риска; $F$5:$F$36 – диапазон (все значения гр. 6 таблицы 2).

В рамках этой функции программа сравнивает значения из ячейки F5 с остальными данными из этого диапазона и по результатам сравнения присваивает ей ранг (см. рисунок 4. Использование функции ранжирования). По той же схеме ранжируются все остальные значения.

Рисунок 4. Использование функции ранжирования

Как ранжировать данные без использования готовой модели Excel

Если понадобится проранжировать данные вне готовой модели, к примеру, суммы задолженности перед кредиторами (см. рисунок 5. Заполнение данных для ранжирования), можно вручную задать параметры функции РАНГ. Для этого достаточно в пустую ячейку (например, соседнюю со списком ранжируемых значений) ввести функцию РАНГ, в ней указать исходное число (В3). И через точку с запятой указать диапазон значений, с которыми нужно его сравнить (см. рисунок 5. Заполнение данных для ранжирования). После диапазона снова ввести точку с запятой, указать 0 или это место оставить пустым (если предстоит ранжирование на убывание). Если необходимо сравниваемому числу присвоить ранг по возрастанию (т. е. первый ранг присвоить наименьшему числу), то вместо слова «порядок» нужно указать любое число от 1. Затем остается зафиксировать от изменений выделенный диапазон (В3:В34) при помощи клавиши F4 (в итоге получится, например, диапазон $В3$:$В$15).

Рисунок 5. Заполнение данных для ранжирования

Далее предстоит скопировать формулу вниз до уровня последнего значения диапазона В3:В15 (см. рисунок 6. Применение функции ранжирования). Так каждая сумма из первого столбца (В) будет проранжирована в порядке убывания (т. е. первый ранг будет присвоен максимальной сумме 5939, а последний ранг 13 – минимальной 462).

Рисунок 6. Применение функции ранжирования

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

Рисунок 7. Подготовка к настройке параметров сортировки

Далее на вкладке «Данные» в группе «Сортировка и фильтр» нужно выбрать кнопку «Сортировка» (см. рисунок 8. Настройка параметров сортировки).

Рисунок 8. Настройка параметров сортировки

В открывшемся диалоговом окне в разделе «Столбец» поля «Сортировать по» из названий двух граф таблицы (сумма и ранг) можно выбрать первую, поскольку планируется сортировка по этим значениям. Далее в разделе «Сортировка» нужно выбрать «Значения» (это тип группируемых данных). А в группе «Порядок» предстоит выбрать тип группировки: по возрастанию – это если нужно, чтобы вверху графы была указана минимальная сумма, или по убыванию, если необходимо получить обратную картинку (подробнее о прочих параметрах и критериях сортировки см. Как отсортировать реестр оплат одновременно по двум критериям с помощью Excel).

В итоге можно получить упорядоченный список долгов с учетом их ранга (см. рисунок 9).

Рисунок 9. Упорядоченный список задолженности

fd.ru