Порядковый номер ранга как найти

Синтаксис

= RANK (number, reference, [order]) — английская версия

= RANK (number; link; [order]) — русская версия

Что возвращает функция

Возвращает число, указывающее ранг числа относительно других значений в списке.

Синтаксис функции:

РАНГ (число; ссылка; порядок)

Описание аргумента:

• — число: указание ячейки, положение которой необходимо вычислить;
• — соединение: указание диапазона ячеек, с которым будет производиться сравнение;
• — порядок: значение, указывающее тип заказа: 0 — по убыванию, 1 — по возрастанию.

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

Функция RANK.CP () указывает, что если результаты совпадают, будет присвоено значение, соответствующее среднему значению рейтинговых чисел.

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

Пример 1. Есть документ, в котором записаны имена учеников и сумма оценок за четверть. Необходимо определить рейтинг учеников по успеваемости.

Используем для ячейки C2 формулу = RANK (B2; $ B $ 2: $ B $ 7,0), для ячейки D2 — формулу = RANK.РВ (B2; $ B $ 2: $ B $ 7; 0) а для ячейки E2 — формула = RANK.CP (B2, $ B $ 2: $ B $ 7,0). Растягиваем все формулы на ячейки ниже.

Таким образом, можно видеть, что рейтинг для функций RANK () и RANK.RV () не отличается: есть два студента, которые заняли второе место, нет третьего места, и есть также два студента, которые заняли второе место место, четвертое место, даже пятого места не бывает… Рейтинг составлен по максимально возможному варианту.

В то же время функция RANK.CP () присваивала подобранным студентам среднее количество мест, которые они могли бы занять, если бы сумма баллов, например, имела разницу в один балл. Для второго и третьего места средний показатель — 2,5; для четвертого и пятого — 4,5.

Функция РАНГ.РВ

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

Синтаксис этого оператора записывается следующим образом:

= RANGO.RV (номер; ссылка; [заказ])

Ссылочный номер и аргументы являются обязательными, а порядок указывать необязательно. В качестве аргумента «число» необходимо ввести ссылку на ячейку, содержащую значение, чей порядковый номер вы хотите узнать. Аргумент ссылки содержит адрес всего классифицируемого диапазона. Аргумент порядка может иметь два значения: «0» и «1». В первом случае порядок считается по убыванию, а во втором — по возрастанию. Если этот аргумент не указан, программа автоматически считает его равным нулю.

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

1. Выделите на листе ячейку, в которой будет отображаться результат обработки данных. Нажмите кнопку «Вставить функцию». Он расположен слева от строки формул.
2. Эти действия запускают окно мастера. В нем представлены все (за редким исключением) операторы, которые можно использовать для составления формул в Excel. В категории «Статистика» или «Полный алфавитный список» находим имя «RANK.RV», выбираем его и нажимаем на кнопку «ОК».
3. После выполнения вышеуказанных шагов будет активировано окно аргументов функции. В поле «Число» введите адрес ячейки, данные, которые вы хотите классифицировать. Это можно сделать вручную, но удобнее сделать так, как будет описано ниже. Установите курсор в поле «Число», затем просто выберите нужную ячейку на листе.

   После этого ваш адрес будет введен в поле. Таким же образом мы вставляем данные в поле «Ссылка», только в этом случае мы выбираем весь диапазон, в котором происходит ранжирование.

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

   После ввода всех вышеперечисленных данных нажмите кнопку «ОК».

4. После выполнения этих действий в заданной ячейке отобразится серийный номер, который имеет выбранное значение из всего списка данных.

   

   Если вы хотите ранжировать всю указанную область, вам не нужно вводить отдельную формулу для каждой меры. Прежде всего, сделаем адрес абсолютным в поле «Ссылка». Добавьте знак доллара ($) к каждому значению координат. В этом случае не следует изменять значения в поле «Число» на абсолютные значения, иначе формула будет рассчитана некорректно.

   

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

   

   Как видите, таким образом формула будет скопирована, и ранжирование будет выполнено по всему диапазону данных.

Функция РАНГ.СР

Вторая функция, выполняющая операцию ранжирования в Excel, — это RANK.SR. В отличие от функций RANK и RANK.RV, если значения нескольких элементов совпадают, этот оператор возвращает средний уровень. То есть, если два значения равны по величине и следуют за пронумерованным значением 1, им обоим будет присвоен номер 2,5.

Синтаксис RANK.CP очень похож на предыдущий оператор. Похоже на то:

= RANK.SR (число; ссылка; [порядок])

Формулу можно ввести вручную или с помощью мастера. На последнем варианте остановимся подробнее.

1. Выбираем ячейку на листе для просмотра результата. Аналогично, как и в предыдущий раз, перейдите в мастер через кнопку «Вставить функцию».
2. После открытия окна мастера функций выберите имя RANK.SR в списке категорий «Статистика» и нажмите кнопку «ОК».
3. Окно темы активировано. Аргументы для этого оператора точно такие же, как для функции RANK.RV:< >Число (адрес ячейки, в которой находится элемент, уровень которого необходимо определить);Ссылка (координаты диапазона, рейтинг в пределах которого выполняется);Заказ (необязательно).Как видите, после выполненных действий результат расчета отображался в ячейке, отмеченной в первом абзаце данной инструкции. Сама сумма представляет собой место, которое занимает конкретное значение среди других значений в диапазоне. В отличие от результата RANK.RV, итог оператора RANK.SR может иметь дробное значение.
4. Как и в случае с предыдущей формулой, изменив ссылки с относительных на абсолютные и указав индикатор выбора, вы можете ранжировать весь диапазон данных с помощью автозаполнения. Алгоритм действий точно такой же.

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

Функция РАНГ() в MS EXCEL

RANK (), английская версия RANK (), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10; 20; 5} число 5 будет иметь ранг 1, то есть

поскольку это наименьшее число, 10 — это ранг 2, а 20 — ранг 3 (это возрастающий ранг, поскольку меньшему значению присваивается ранг 1). Если список отсортирован, рангом числа будет его позиция (если нет повторов).

• Синтаксис
• РАНГ (число>; ссылка>>
• Число — это номер, по которому определяется ранг.

Ссылка: ссылка на список чисел (диапазон ячеек с числами). Невозможно указать массив напрямую, формула = RANK (10; {10: 50: 30: 40: 50}) не будет работать. Но если вы введете формулу = РАНГ (B7; $ A $ 7: $ A $ 11), она будет работать (хотя ячейка B7 отсутствует в списке с числами). Если B7 содержит число вне нумерованного списка, формула вернет ошибку # N / A.

Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, также не присваивается ранг, функция обрабатывает их как текст.

Заказ — это номер, который определяет, как вы заказываете.

• Если порядок равен 0 (ноль) или опущен, MS EXCEL присваивает ранг = 1 максимальному числу, меньшим значениям назначаются более высокие ранги.
• Если порядок — ненулевое число, MS EXCEL присваивает ранг = 1 минимальному числу, большим значениям присваиваются более высокие ранги.

Примечание. Начиная с MS EXCEL 2010 функции RANK.SR () и RANK.RV () также используются для вычисления ранга. Последняя функция аналогична RANK().

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне A7: A11, формула = RANK (A7; $ A $ 7: $ A $ 11) определит ранг числа из ячейки A7 (см. Файл примера).

Поскольку аргумент порядка опущен, MS EXCEL присвоил ранг = 1 максимальному числу (50) и максимальному рангу (5 = количество значений в списке) минимальному (10).

1. Альтернатива: = СЧЁТЕСЛИ ($ A $ 7: $ A $ 11; «>» & A7) +1
2. Столбец C содержит формулу = RANK (A7; $ A $ 7: $ A $ 11; 1) с возрастающим рангом, ранг = 1 присваивается минимальному числу. Альтернатива: = СЧЁТЕСЛИ ($ A $ 7: $ A $ 11; «» & B3) +1
3. Столбец A содержит имена групп, столбец B — значения.

Связь функций НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ() и РАНГ()

Функции LARGE () и RANK () дополняют друг друга в том смысле, что, написав формулу = LARGE ($ A $ 7: $ A $ 11; RANK (A7; $ A $ 7: $ A $ 11)), вы получите тот же массив исходный A7: A11.

Определяем ранг в списке с повторами

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

Наличие повторяющихся номеров влияет на ранги последующих номеров.

Например, если в списке целых чисел, отсортированных в порядке возрастания, есть число 10, которое получает 5-й ранг дважды, число 11 будет ранжировано 7 (ни одно из чисел не будет ранжировано 6).

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

Формула = РАНГ (A37; A $ 37: A $ 44) + СЧЁТЕСЛИ (A $ 37: A37, A37) -1

Предполагается, что исходный список с номерами находится в диапазоне A37: A44.

Примечание. В MS EXCEL 2010 добавлена ​​функция РАНГ.

PB (число; ссылка; [порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает одинаковые значения ранга повторяющимся числам).

Пример файла объясняет, как работает эта функция. Мы также добавили функцию RANK.AV (число; ссылка; [порядок]). Если несколько значений имеют одинаковый ранг, возвращается среднее значение.

Массив рангов

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

1. Как видно из изображения выше, значения в диапазоне B60: B67 и в ячейке D60 совпадают. Эту матрицу можно получить по формуле = РАНГ (A60: A67; A60: A67) или по формуле = СЧЁТЕСЛИ (A60: A67; «>» & A60: A67) +1
2. Повышение рангов можно получить с помощью формулы = РАНГ (A60: A67; A60: A67; 1) или = СЧЁТЕСЛИ (A60: A67;»

Как сортировать данные в таблицах Excel (правильный способ)

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

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

1. Сортируйте данные всего за несколько кликов
2. Настройка нескольких правил сортировки в шахматном порядке, таких как сортировка по алфавиту по состоянию, а затем все вместе.
3. Полностью создайте свои собственные настройки сортировки для сортировки данных с использованием любого установленного вами правила.

Как сортировать данные в электронной таблице Excel (короткое видео)

Этот скринкаст охватывает несколько методов сортировки данных. Посмотрите это 3-минутное видео, чтобы быстро изучить эти профессиональные методы работы с Excel. Мы начнем с простой сортировки и перейдем к более продвинутым методам, чтобы вы всегда могли отсортировать свои данные так, как хотите.

Примеры данных (бесплатная загрузка рабочей книги Excel)

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

Простая сортировка в Excel

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

В книге Excel начните с щелчка по ячейке столбца, которую вы хотите отсортировать. Теперь убедитесь, что вы находитесь на вкладке «Главная» на ленте Excel и найдете кнопку «Сортировка и фильтр» в правой части этой панели.

Кнопка «Сортировка и фильтр» расположена справа от вкладки «Главная.

Обратите внимание, что в параметрах сортировки вы можете отсортировать текст «от А до Я» или от «Я до А». Эти простые параметры помогут вам отсортировать данные в Excel в алфавитном порядке или в обратном порядке, в зависимости от вашего выбора.

При сортировке данных в Excel сортируется вся строка. По сути, выбранный вами столбец будет «ключом», который Excel использует для определения способа сортировки данных, но каждая строка представляет собой запись, которая должна оставаться сгруппированной.

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

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

На самом деле простая сортировка настолько проста. Просто щелкните данные, выберите вариант сортировки, и Excel восстановит данные в таблице.

Я отсортировал данные в этой таблице по клиентам всего за несколько щелчков мышью.

Интересный совет: попробуйте также выполнить сортировку, щелкнув правой кнопкой мыши внутри столбца и выбрав «Сортировка», а затем укажите, как сортировать исходные данные.

Как НЕ нужно сортировать данные в Excel

не менее важно знать самый опасный способ сортировки данных в Excel, он может испортить исходные данные.

Проблема возникает, если в таблице много данных и вы случайно отсортировали только один столбец данных. Каждая строка данных в Microsoft Excel действительно выглядит как запись, которая должна быть одинаковой во всей строке.

Я дал цвет линиям в этом примере, чтобы мы могли убедиться, что наши данные отсортированы правильно. Если данные отсортированы правильно, цвета должны плавно пересекать каждую строку.

Большая ошибка для пользователей Excel — выбрать только один столбец при сортировке и выбрать неправильный вариант в следующем окне.

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

Мне всегда казалось, что варианты, предлагаемые этим окном, не совсем понятны. Просто знайте, что вы захотите использовать автоматическое расширение выделения, чтобы Excel касался всех столбцов при сортировке данных.

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

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

На скриншоте ниже вы можете увидеть, насколько проблематична такая сортировка. Поскольку столбец «Сумма счета» был отсортирован от наименьшего к наибольшему, все остальные столбцы остались на своих местах. Это означает, что наши данные больше не верны.

Как видно из несоответствия цветов, были отсортированы только данные в столбце Billed Amount, поэтому таблица теперь неверна.

Следовательно, при сортировке данных есть два ключа «НЕ»:

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

Расширенная сортировка данных Excel

До сих пор простая сортировка позволяла нам сортировать данные одинаково. Что, если нам нужны два типа данных для сортировки?

А если бы мы хотели…

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

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

Чтобы начать, щелкните в любом месте своих данных и найдите параметр «Сортировка и фильтр», затем выберите «Пользовательская сортировка.

Перейдите к расширенным параметрам сортировки, выбрав «Сортировка и фильтр»> «Пользовательская сортировка.

В этом окне мы можем добавлять разные уровни сортировки. Для начала щелкните раскрывающееся меню рядом с полем «Сортировка» и выберите столбец, который нужно отсортировать.

В моем случае я выберу «Клиент» в раскрывающемся меню и оставлю «Сортировка по значениям» и «Сортировка от А до Я». На простом языке это будет отсортировать электронную таблицу Excel в алфавитном порядке.

Теперь нажмите «Добавить слой». Это создаст новую строку в сопоставлении и позволит нам добавить второй уровень организации.

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

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

После нажатия кнопки «ОК» Excel отсортирует таблицу в соответствии с правилами, которые мы создали в этом окне.

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

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

Функция

РАНГ(

)

, английский вариант RANK(),

возвращает

ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 будет иметь ранг 1, т.к. это наименьшее число, число 10 – ранг 2, а 20 – ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).

Синтаксис

РАНГ

(

число

;

ссылка

;порядок)

Число

— число, для которого определяется ранг.

Ссылка

— ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;{10:50:30:40:50}) работать не будет. Но, если ввести формулу

=РАНГ(B7;$A$7:$A$11)

, то она будет работать (хотя ячейка

B7

– вне списка с числами). Если в

B7

содержится число вне списка с числами, то формула вернет ошибку #Н/Д.

Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.

Порядок

— число, определяющее способ упорядочения.

• Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б
  
  о
  
  льшие ранги.
• Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б
  
  о
  
  льшим значениям присваиваются б
  
  о
  
  льшие ранги.

Примечание

: Начиная с MS EXCEL 2010 для вычисления ранга также используются функции

РАНГ.СР()

и

РАНГ.РВ()

. Последняя функция аналогична

РАНГ()

.

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне

A7:A11

, то формула

=РАНГ(A7;$A$7:$A$11)

определит ранг числа из ячейки

А7

(см.

файл примера

).

Т.к. аргумент

порядок

опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) – минимальному (10).

Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;”>”&A7)+1

В столбце

С

приведена формула

=РАНГ(A7;$A$7:$A$11;1)

с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;”<“&A7)+1

Если исходный список

отсортировать

, то ранг числа будет его позицией в списке.

Ранг по условию

Если список состоит из значений, относящихся к разным группам (например, к разным маркам машин), то ранг можно вычислить не только относительно всей совокупности данных, но и относительно данных каждой отдельной группы.

В

файле примера

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

=СЧЁТЕСЛИМН($A$3:$A$22;A3;$B$3:$B$22;”>”&B3)+1

В столбце А содержатся названия группы, в столбце В – значения.

Связь функций

НАИБОЛЬШИЙ()

/

НАИМЕНЬШИЙ()

и

РАНГ()

Функции

НАИБОЛЬШИЙ()

и

РАНГ()

являются взаимодополняющими в том смысле, что записав формулу

=НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11))

мы получим тот же исходный массив

A7:A11

.

Определяем ранг в списке с повторами

Если список содержит

повторы

, то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция

РАНГ()

или

РАНГ.РВ()

) или среднее значение, если

РАНГ.СР()

). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

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

В этом нам поможет формула

=РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1

Предполагается, что исходный список с числами находится в диапазоне

А37:А44

.

Примечание

. В

MS EXCEL 2010

добавилась функция

РАНГ.РВ(число;ссылка;[порядок])

Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В

файле примера

дается пояснение работы этой функции. Также добавилась функция

РАНГ.СР(число;ссылка;[порядок])

Если несколько значений имеют одинаковый ранг, возвращается среднее.

Массив рангов

Для построения некоторых сложных

формул массива

может потребоваться

массив

рангов, т.е. тот же набор рангов, но в одной ячейке.

Как видно из картинки выше, значения из диапазона

В60:В67

и в ячейке

D60

совпадают. Такой массив можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67)

или с помощью формулы

=СЧЁТЕСЛИ(A60:A67;”>”&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67;1)

или

=СЧЁТЕСЛИ(A60:A67;”<“&A60:A67)+1

.

Такой подход использется в статьях

Отбор уникальных значений с сортировкой в MS EXCEL

и

Динамическая сортировка таблицы в MS EXCEL

.

Наряду
с линейным коэффициентом корреляции r
для измерения тесноты связи между двумя
коррелируемыми признаками используются
менее точные, но более простые по расчету
непараметрические
показатели:
коэффициент Фехнера, коэффициенты
корреляции рангов
(или ранговые
коэффициенты корреляции)
Спирмэна
иКендэла
,
которые основаны на корреляции не самих
значений коррелируемых признаков, а ихрангов.

Ранг
– это порядковый номер, присваиваемый
каждому индивидуальному значению х
и у
(отдельно) в ранжированном ряду.

Ранжирование
– это процедура упорядочения объектов
изучения, которая выполняется на основе
предпочтения.

Правила
ранжирования:

• оба
  признака необходимо ранжировать
  (нумеровать) в одном и том же порядке:
  от меньших значений к большим (и
  наоборот);

• если
  встречается несколько одинаковых
  значений х
  (или у),
  то каждому из них присваивается ранг,
  равный частному от деления суммы рангов
  (суммы мест), приходящихся на это
  значение, на число равных значений
  (например,
  если за рангом 3 следуют три
  равных значений признака, то им всем
  присвоится ранг 5, т.к. (4 + 5 + 6) / 3 = 5);

• ранги
  признаков х
  и у
  обозначаются символами
  и(иногдаи).

Суждение
о связи между изменениями значений х
и у
основано на сравнении поведения рангов
по двум признакам параллельно:

• если
  у каждой пары х
  и у
  ранги совпадают, это характеризует
  максимально
  тесную прямую
  связь;

• если
  же наблюдается полная противоположность
  рангов, т.е. в одном ряду ранги возрастают
  от 1 до n,
  а в другом – убывают от n
  до 1, это максимально
  возможная
  обратная
  связь.

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

Коэффициент
Спирмэна рассчитывается так:

• значения
  признаков х
  и у
  нумеруют (отдельно) в порядке возрастания
  от 1 до n,
  т.е. им присваивают определенный ранг
  (и)
  – порядковый номер в ранжированном
  ряду;

• для
  каждой пары рангов находят их разность
  d
  =
  –;

• вычисляют
  квадраты этих разностей
  ;

• вычисляют
  коэффициент корреляции рангов Спирмэна
  по одной из формул

или

.
(16)

Коэффициент
корреляции рангов Спирмэна может
принимать следующие значения:

(17)

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

Пример.
Расчет коэффициента корреляции рангов
Спирмена по данным о часовой оплате
труда х
и уровне текучести кадров у
(таблица 5).

Значения
х
располагаем по возрастанию, поэтому и
значения
располагаются по порядку возраста. Если
бы значенияу
для соответствующих значений х
в первых двух строках таблицы располагались
«правильно», то они стояли бы в таком
порядке: 35, 34, …, но фактическое их
расположение – 34. 35, … Поэтому для них
не 8, 7, …, а 7, 8, и т.д. (ср. также строки 5 и
6). Для данного примера= – 0,952, что свидетельствует осильной
обратной
связи между х
и у.

Таблица
5

х	у	Ранги	Разность рангов d=–
30	34	1	7	–6	36
40	35	2	8	–6	36
50	33	3	6	–3	9
60	28	4	5	–1	1
70	20	5	3	2	4
80	24	6	4	2	4
90	15	7	2	5	25
100	11	8	1	7	49
n = 8					=164

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

или
,
(18)

где
S
– сумма положительных и отрицательных
баллов.

Расчет
коэффициента
выполняется в следующей последовательности:

1. Ранги
   х
   (и
   )
   располагаются строго впорядке
   возрастания.

2. Значения
   у
   располагаются в порядке, соответствующем
   значениям
   х.

3. Параллельно
   записывают соответствующее каждому
   значению
   значение(см. пример к вычислению коэффициента).

4. Устанавливается
   мера соответствия последовательности
   «правильному» следованию(напоминаем,расположены строго по возрастанию):

а)
для каждого
последовательно определяют число
следующих за ним рангов,превышающих
его значение, т.е. определяют «правильное»
следование, и они учитываются со знаком
«+», а их сумма обозначается буквой P;

б)
для каждого
последовательно определяют число
следующих за ним рангов,меньших
по значению, т.е. определяют «неправильное»
следование, и они учитываются со знаком
«–», а их сумма обозначается буквой Q.

1. Определяется
   сумма баллов по всем членам ряда, т.е.
   S=P+Q.

2. Вычисляется
   по (18).

Пример.
По 10 хозяйствам имеются данные об
урожайности картофеля у
(ц/га) и о количестве внесенных на 1 га
удобрений х
(кг). Необходимо измерить тесноту связи
между изменениями х
и у
с помощью коэффициента корреляции
рангов Кендэла (табл. 6).

Таблица
6

х	у	Ранги	Подсчет баллов
		«+»	«–»
1	2	3	4	5	6
138	218	1	1	9	0
175	240	2	3	7	1
190	232	3	2	7	0
196	280	4	6	4	2
200	260	5	4	5	0
235	310	6	9	1	3
250	290	7	7	2	1
260	278	8	5	2	0
275	300	9	8	1	0
290	320	10	10	–	–
n=10				P=38	Q=–7

Рассмотрим
примеры определения значений в столбцах
5
и 6.

В
первой
строке столбца 5
стоит число «9», как оно получено?

Обращаемся
к первой
строке столбца 4,
т.е. к значениям
:
там стоит число «1». Как видим, в этом
столбцевсе
последующие
значения
больше
«1», т.е. число
значений, превышающих
«1», равняется 9 = 10 – 1. Поэтому в первой
строку столбца 5
ставим число «9».

В
первой
строке столбца 6
стоит число «0».

Обращаемся
к первой
строке столбца 4,
т.е. к значениям
:
там стоит число «1». Как видим, в этом
столбцевсе
последующие
значения
больше
«1», т.е. число
значений, меньших
«1», равняется 0 = 9 – 9. Поэтому в первой
строку столбца 6
ставим число «0».

Например,
в четвертой
строке столбца 5
стоит число «4», которое получено из
следующих рассуждений.

Обращаемся
к четвертой
строке столбца 4,
т.е. к значениям
:
там стоит число «6». Как видим, в этом
столбце изпоследующих
значений
9, 7, 8, 10 (всего 4 значения)больше
«6», поэтому в четвертую
строку столбца 5
ставим число «4».

Во
второй
строке столбца 6
стоит число «2», оно получено так.

Обращаемся
ко второй
строке столбца 4,
т.е. к значениям
:
там изпоследующих
значений
числа 4 и 5 (всего 2 значения)меньше
«6». Поэтому в четвертую
строку столбца 6
ставим число «2».

Рассуждая
подобным образом, находим все значения
в столбцах для «+» и «–».

Далее
вычисляем P
= 38 и Q
= –7, т.е.
S=P+Q
= 38 – 7 = 31, и
= 0,69; полученное значениехарактеризует довольно большую (выше
среднего) тесноту связи между изменениямих
и у.

Заметим,
рассчитывается аналогично и для случая
противоположной напрвленности ранговх
и у.

Преимущества
коэффициентов корреляции рангов Спирмэна
и Кендэла:

• они
  легко вычисляются;

• могут
  быть использован для определения связи
  как между количественными,
  так и между качественными
  признаками;

• не
  требуется знать форму
  связи
  изучаемых явлений.

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

,
(19)

где
S
– сумма квадратов отклонений суммы m
рангов от их средней

величины;

m
– число ранжируемых признаков;

n
– число
ранжируемых единиц (число наблюдений).

Коэффициент
конкордации W
может принимать следующие значения:

;
(20)

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

Соседние файлы в папке регрессия_Лекции_10-11

• #
• #

Рейтинг в эксель по двум параметрам

Столкнулась с тем что мне необходимо сделать оценку работы филиалов.

Для этого вычислена выручка и расходы (см.ДДС). По выручке присвоила категории (см. в файле (от 1-10))
Далее исходя из расчета % расходов от выручки, и учетом категории нужно начислить баллы (см. лист система оценки)

Нужна формула или функция, т.к. через если не корректно выходит ( слишком большая формула)

Столкнулась с тем что мне необходимо сделать оценку работы филиалов.

Для этого вычислена выручка и расходы (см.ДДС). По выручке присвоила категории (см. в файле (от 1-10))
Далее исходя из расчета % расходов от выручки, и учетом категории нужно начислить баллы (см. лист система оценки)

Нужна формула или функция, т.к. через если не корректно выходит ( слишком большая формула) Melissy

Сообщение Доброго дня!

Столкнулась с тем что мне необходимо сделать оценку работы филиалов.

Для этого вычислена выручка и расходы (см.ДДС). По выручке присвоила категории (см. в файле (от 1-10))
Далее исходя из расчета % расходов от выручки, и учетом категории нужно начислить баллы (см. лист система оценки)

Филиал Перово Выручка 2335258 Расход 1999285 Процент расходдов от выручки 86% Оценка филиала по выручке 3

На листе » система оценки » есть табличка «оценка по расходам в зависимости от категории «

Исходя из нее, на пересечении категории «3» (Оценка филиала по выручке) и % «85» (Процент расходов от выручки, 86% (меньше 90, но больше или равно 85)) = 5 баллов

Филиал Перово Выручка 2335258 Расход 1999285 Процент расходдов от выручки 86% Оценка филиала по выручке 3

На листе » система оценки » есть табличка «оценка по расходам в зависимости от категории «

Исходя из нее, на пересечении категории «3» (Оценка филиала по выручке) и % «85» (Процент расходов от выручки, 86% (меньше 90, но больше или равно 85)) = 5 баллов

Сообщение Филиал Перово Выручка 2335258 Расход 1999285 Процент расходдов от выручки 86% Оценка филиала по выручке 3

На листе » система оценки » есть табличка «оценка по расходам в зависимости от категории «

Исходя из нее, на пересечении категории «3» (Оценка филиала по выручке) и % «85» (Процент расходов от выручки, 86% (меньше 90, но больше или равно 85)) = 5 баллов

Источник

Примеры функции РАНГ для ранжирования списков по условию в Excel

Функция РАНГ() при применении возвращает в виде результата номер позиции элемента в конкретно определённом списке. Сам результат представляет собой число, которое показывает, какое бы место занимал элемент в этой строке, если бы указанный диапазон был отсортирован по возрастанию или по убыванию.

Примеры использования функции РАНГ в Excel

Функция РАНГ.РВ() не отличается по работе от общей функции РАНГ(). Как и было указано выше, если программа обнаружит несколько элементов, значения которых будут равны, то присвоит им высший ранг – например, при совпадении результатов им всем будет присвоено одно место.

Функция РАНГ.СР() указывает, что при совпадении результатов им будет присвоено значение, соответствующее среднему между номерами ранжирования.

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

Пример 1. Есть документ, в котором записаны фамилии учеников и суммы баллов по оценкам за четверть. Требуется определить ранжирование учеников по успеваемости.

Используем для ячейки C2 формулу =РАНГ(B2;$B$2:$B$7;0), для ячейки D2 – формулу =РАНГ.РВ(B2;$B$2:$B$7;0), а для ячейки E2 – формулу =РАНГ.СР(B2;$B$2:$B$7;0). Протянем все формулы на ячейки ниже.

Таким образом, видно, что ранжирование по функциям РАНГ() и РАНГ.РВ() не отличается: есть два ученика, которые заняли второе место, третьего места нет, а также есть два ученика, которые заняли четвёртое место, пятого места также не существует. Ранжирование было произведено по высшим из возможных вариантов.

В то же время функция РАНГ.СР() присвоила совпавшим ученикам среднее значение из мест, которые они могли бы занимать, если бы сумма баллов, например, была с разницей в один балл. Для второго и третьего места среднее значение – 2,5; для четвёртого и пятого – 4,5.

Ранжирование товаров по количеству в прайсе

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

Добавим колонку ранжирования и в ячейку C2 впишем следующую формулу: =РАНГ.РВ($B2;$B$2:$B$10;0). Протянем эту формулу вниз и получим следующий результат распределения мест:

Теперь нам потребуются три дополнительные колонки для создания удобной для восприятия таблицы. В первой колонке у нас будет записаны порядковые номера, во второй – отображены наименования товара, в третьей – их количество. Для того, чтобы таблица работала корректно и обновляла значения при их изменении в колонках А и B, применим к ячейке F2 формулу:

а к ячейке G2 – формулу:

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

Расчет рейтинга продавцов по количеству продаж в Excel

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

В качестве шапки для сортировки мы будем использовать клетку H1. Выделим её и перейдём в меню «ДАННЫЕ — Работа с данными — Проверка данных».

В окне «Проверка вводимых значений» в качестве типа данных выберем «Список» и укажем диапазон ячеек, в которых записаны месяцы. Так будет реализовано выпадающее меню со списком месяцев для удобства ранжирования. Диапазон выглядит следующим образом: =$B$1:$G$1.

Таким образом, итоговая формула для ячейки H2 будет выглядеть следующим образом:

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

Источник

Рейтинг в excel как сделать

Если необходимо быстро построить рейтинг значений, не прибегая к сортировке данных в таблице, можно воспользоваться функцией РАНГ. Для этого:

1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)

2. Заполняем аргументы в открывшемся окне: «Число» – указываем первое значение в нашей таблице в той же строке, где находится формула.

3. «Ссылка» — указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).

4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.

5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.

При пользовании данной функцией, расчет рейтинга производится автоматически, и если вы изменили какое-либо значение, то по рейтингу произойдет автоматический пересчет.

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

То, что мы хотим получить в конце — это сгенерированный список из 10 самых продаваемых товаров. Также мы хотим, что бы этот список автоматически обновлялся при каждом изменении количества продаж товаров и мы не хотим использовать VBA макросы для упрощения задачи.

Пожалуйста скачайте пример по ссылке ниже, что бы было проще понять те действия, которые будут описаны ниже:

Первый этап.

Во-первых, давайте отсортируем все продажи по убыванию и выберем 10 лучших.

Для этого я решил использовать функцию

Наша формула выглядит следующим образом:

где C4:C19 это диапазон с количеством реализованных продуктов.

В результате мы получаем лист Топ-10 продаж. Далее, более сложная часть.

Второй этап.

Как назначить названия продуктов номерам?

Если Вы уверены, что количество проданных товаров

никогда не будет одинаковым

(т.е. не будет повторяющихся значений), то мы можем использовать функции ИНДЕКС и ПОИСКПОЗ для поиска соответствующего наименования продукта выбранному количеству продаж.

Наша формула может выглядеть следующим образом:

И она будет работать отлично.

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

Для первого продукта мы воспользуемся формулой:

А для последующих названий продуктов, будем использовать следующую формулу:

Протягиваем эту формулу для всех оставшихся ячеек.

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

Обратите внимание на фигурные скобки перед и после формулы. Эти скобки обозначают что формула применена для массива. Что бы Вам добиться такого же результата, то внесите в ячейку формулу, а после нажмите комбинацию клавиш Ctrl+Shift+Enter.

При каждом изменении количества проданных товаров, перечень Топ-10 продаж будет автоматически перестраиваться.

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел <30;2;26>число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).

Определим ранжирование чисел в списке без повторов:

Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.

В третьем столбце приведена формула с рангом по возрастанию.

Определим номера значений в списке, где присутствуют повторяющиеся значения.

Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.

Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2

Серым цветом выделены значения, которые соответствуют заданному условию. Для чисел, которые больше 30, выводится пустая строка.

Пример функции РАНГ.РВ в Excel

В версиях Excel, начиная с 2010 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).

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

Пример функции РАНГ.СР в Excel

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

Вот результат работы функции:

Формула в столбце «по убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так, функция значению 87 присвоила средний номер 1,5.

Допустим, в списке чисел три повторяющихся значения (выделены оранжевым цветом).

Функция присвоила каждому из них ранг 5, что является средним для 4, 5 и 6.

Сравним работу двух функций:

Напомним, что эти две функции работают только в Excel 2010 и выше. В более ранних версиях для этих целей можно задействовать формулу массива.

Скачать примеры функции ранжирования РАНГ в Excel.

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

Вам нужно присвоить каждому числу в Excel свой номер, чтобы можно было их отсортировать по этому номеру? Можно придумать сложные конструкции для текстовых данных, но для числовых данных есть специальная функция РАНГ в Excel. Относится к числу статических функций и бывает довольно полезной. В статье мы так же рассказываем о новых функциях из Excel 2010 РАНГ.CP() и РАНГ.РВ()

Как пользоваться. Функция РАНГ в Excel

Функцией РАНГ пользоваться довольно просто. Набор аргументов тоже несложный, а именно:

. Если поставить 0 или не заполнять этот аргумент то ранг определяется по убыванию. Т.е. наибольшее число 2000 будет на 1ом месте.

. Если поставить любое другое число, то максимальное значение будет под последним местом.

Подробно показано, как использовать функцию на первой картинке.

Примечание. Если списке 2 одинаковых числа — в нашем случае 1 400, то им будет присвоены одинаковые значения ранга — 2, т.е. второе место. При этом следующее место будет уже не 3, а 4 (1 250). Довольно важное свойство, которое не всех устраивало. Поэтому разработчики пошли дальше и сделали дополнительные функции РАНГ.CP() и РАНГ.РВ() в Excel 2010.

РАНГ.CP

Если чисел с одинаковым значением 1400 3 и более, всем им будет присвоен ранг =2 — это для обычной функции РАНГ. Функция РАНГ.СР посчитает место как среднее из 3 мест, т.е. 3 места занимают второе, третье и четвертое место

РАНГ присвоит всем 3 числам второе место.

РАНГ.CH присвоит всем троим четвертое место.

РАНГ.РВ

Все просто — это та же функция РАНГ только для новых версий Excel — 2010 и старше.

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

Источник

Формула для выборки нескольких наибольших значений в Excel

Функции МАКС и МИН прекрасно подходят для поиска наибольшего и наименышего значения в диапазоне данных. Но если нужно найти несколько максимальных или минимальных значений для этой задачи Excel предлагает несколько интересных функций: НАИБОЛЬШИЙ, НАИМЕНЬШИЙ, РАНГ и РАНГ.СР.

Пример выборки нескольких наибольших значений в Excel

Ниже на рисунке представлены результаты турниру по боулингу. Участники в турнирной таблице должны быть отсортированы по возрастанию по имени. Сложно проверить, который из них является победителем. Допустим нам необходимо узнать, которые участники заняли первые 3 места и какие их результаты. Ниже указанная формула, которая возвращает третье наибольшее число со списка результатов:

В формуле использованной для поиска имени участника используется комбинация функций ИНДЕКС и ПИСКПОЗ:

Функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ используются для поиска порядкового наибольшего и наименьшего значения в списке данных. В первом аргументе функции НАИБОЛЬШИЙ, как и в функции МАКС указывается ссылка на исходный диапазон просматриваемых значений. Однако отличие между ними заключается в том, что функция НАИБОЛЬШИЙ имеет еще один обязательный для заполнения аргумент «К» в котором можно указать порядковый номер возвращаемого наибольшего значения (второе наибольшее, третье… и т.д.).

Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.

В ячейке E2 используется функция СТРОКА(A1) определяющая значение аргумента «К». Функция строка возвращает номер строки листа для ячейки, указанной в аргументе – в данном примере строка №1. Как второй аргумент функции НАИБОЛЬШИЙ можно было бы просто указать число 1, однако благодаря функции СТРОКА(A1) номер строки листа будет увеличиваться автоматически во время копирования формулы в очередные ячейки в низ от начальной. Ссылка на ячейку A1 является относительной, когда потом формула будет скопирована в ячейку E3, функция будет уже иметь следующий вид: СТРОКА(A2). С таким аргументом функция уже будет возвращать значение 2, а функция НАИБОЛЬШИЙ в ячейке E3 возвратит второе наибольшее значение из столбца «Результат» в исходном списке данных.

Формула ранжирования в Excel для выборки наибольших значений

В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:

В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.

Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:

Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:

Функция РАНГ.СР для ранжирования списка с повторяющимися значениями

В программе Excel версии 2010 и старше появились две новые функции ранжирования значений: РАНГ.РВ и РАНГ.СР, в которых по-разному решается проблема с одинаковыми значениями в диапазоне ячеек. Формула РАНГ.РВ – это аналог функции РАНГ, она возвращает тот же самый результат при таких же условиях с теми же самыми одинаковыми ячейками. А вот функция РАНГ.СР возвращает средний результат вычисления для всех одинаковых значений.

Допустим в таблице находятся результаты четырех участников с одинаковым вторым наименьшим показателем времени 21:38:

Для наилучшего времени в данной таблице функция РАНГ.СР естественно возвращает номер 1, а для четырех последующих наилучших показателей времени возвращено дробное значение 3,5. Эти четыре участника одновременно занимают места: 2, 3, 4 и 5, в итоге все места разделены между ними поровну, поэтому каждый участник получил по 3,5 балла. То есть 2+3+4+5/4=3,5.

Источник

Как сделать рейтинг в excel формула

РАНГ (функция РАНГ)

​Смотрите также​Intech​ в готовый каркас.​​Совет. Для быстрой вставки​​ названию (латинской букве)​

Описание

​: Спасибо решение великолепное​ к максимуму. Т.е​ баллов полученных каждым​ терпения :)​ второй у пети​ а в третьем​ операторами, но его​ кнопку​Если вы желаете проранжировать​

​ хотите, чтобы выводился​​ функции. В старых​ добавить поправочный коэффициент​В этой статье описаны​: Подсчитать, какие оценки​ Если потребуется дополнительный​ столбца нужно выделить​ левой кнопкой мыши.​МВТ​ самая максимальная 10​ участником. Т.е. в​Микки​ 5 балов, у​ – 4.​ нужно учитывать при​«OK»​

​ всю указанную область,​ результат обработки, но​ версиях приложения был​ (см. ниже) к​

Синтаксис

​ синтаксис формулы и​

​ получили ученики, если​ столбец, ставим курсор​

​ столбец в желаемом​​Для выделения строки –​: Так?​

​ выборе того, какой​​.​ то совсем не​

​ для многих пользователей​ один оператор, предназначенный​ значению, возвращаемому функцией​ использование функции​ «5» получают те,​ в предназначенную для​ месте и нажать​ по названию строки​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=РАНГ(I3;ДВССЫЛ(«I» & СТРОКА($I$3) &​ убывающей. Если повторяются, то​ №1 получил 14​ можно удалить макрорекордером​ бала, у Саши​ (иногда это мешает​ именно функцией пользователю​Активируется окно аргументов. Аргументы​ нужно для каждого​

Замечания

​ №3 4 балла,​ с ней работало​ надо поставить какие​ формула:​Для ранжирования данных в​ как и у​ делаем адрес в​.​. В целях совместимости​ когда ранг вычисляется​ списке чисел. Ранг​ 15 до 19​ расширится.​ в программе Excel.​ левой кнопкой мыши​ Variant, Начальная_ячейка As​ так Код =ЕСЛИ(E3=0;0;11-СУММ(—(ЧАСТОТА((E$3:E$13>E3)*E$3:E$13;E$3:E$13)>0)))Fairuza,​ и т.д.​Микки​

Пример

​ установить пределы. Например,​

​ функции РАНГ, РАНГ.РВ,​

​ отдельной категории формул​ (аргумент «порядок» имеет​ его величина относительно​

​ границы, добавлять строки​ и протаскиваем.​Optional Количество_Ячеек As​

Выполнение ранжирования в Microsoft Excel

​Для выделения столбца с​ Long, Optional Порядок​

Функции ранжирования

Способ 1: функция РАНГ.РВ

​ в них все-таки​​ порядке возрастания (значение​​ список, то ранг​ 10 балов​ и протягиваем вниз.​Заполняем вручную шапку –​ ставим курсор в​ Variant​ рейтинг по всем​ фотографиям не лечим​ на листе 1-​ сделать??​ решить задачу, применим​ числовых значений. Рассмотрим​ элемент, уровень которого​($)​«Вставить функцию»​ желательно работать с​ аргумента «порядок» не​ числа будет его​Все нужно в​С выходом новых версий​​ названия столбцов. Вносим​​ любую ячейку нужного​If Начальная_ячейка.Count >​ инструментам, плюс итоговый​китин​

​ исходный текст- указанную​vikttur​

​ функцию ЕСЛИ (=ЕСЛИ(A2​

​ которые соответствуют заданному​ в перечне чисел.​(координаты диапазона, ранжирование​«Число»​Эти действия приводят к​ возможность. К ним​ рангов = [СЧЕТ(ссылка)​ Эта функция была заменена​​Подскажите, что не​​ стала интересней и​

​Мастера функций​и​ – РАНГ(число, ссылка,​ обеспечивают более высокую​Vlad999​ таблица, становится доступным​​ высоту для строк.​​Если информация при заполнении​ Long​ сделать относительную ссылку​ чем отличается от​

​ для рейтинга.​ фотографии (:​Все что смог​​ и сортировка по​​В версиях Excel, начиная​

​ 0 Then L​ расчета рейтинга она​: так?и картинка тоже​Владимир​Гость​ появилась функция РАНГ.РВ.​ позицию числа. Например,​ и у предыдущего​ курсор в нижний​ можно использовать для​

​ ними мы поговорим​ коэффициент равен (5​ эта функция все​ F2>=10;3;2)))​ имя таблице, изменить​

​ «=». Таким образом,​​Передвинуть вручную, зацепив границу​

Способ 2: функция РАНГ.СР

​: Странно, но когда​: А если нужно​ Это абсолютный аналог​​ в массиве чисел​​ оператора. После того,​ правый угол ячейки,​​ составления формул в​​ далее.​​ + 1 –​​ еще используется для​что за запись​ размер.​ мы сигнализируем программе​ ячейки левой кнопкой​ _​ и J.​:​ перенёс исходные данные​ сделать так: график​ предыдущей функции. Синтаксис​

​ <30;2;26>число 2​​ когда все настройки​​ и дождаться появления​ Excel. В категории​Оператор​

​ обеспечения обратной совместимости,​ O2=3?​Доступны различные стили, возможность​ Excel: здесь будет​ мыши.​

​ то ошибка не​ по порядку. (Петя,​ указывают на то,​ 30 –3 (как​«OK»​ Затем зажать левую​«Полный алфавитный перечень»​ и выводит в​​ учетом связей в​​ версиях Excel, поэтому​

​ формулой одинаковых значений​​ списке).​Как видим, после выполненных​

​ протянуть маркер параллельно​​«РАНГ.РВ»​ номер заданного аргумента​

Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

Пример функции РАНГ в Excel

​ функция такая Суммпроизв​

​ ней левой кнопкой​ нижнем углу сформируется​

​ кнопкой «Перенос текста»​Программа Microsoft Excel удобна​ так же просчитывала​: Привет всем. Подскажите,​Или еще проще​Примерчик бы показали.​Как видно из примера,​ или диапазон ячеек​

​ представляет собой место,​ данных.​ аргументов функции. В​

​ значений. Если, например,​ будет равен 0,​Аргументы функции РАНГ описаны​

​ 2 место и​ мыши. Вводим текстовое​ крестик. Он указываем​ на панели инструментов.​ для составления таблиц​ все только первые​ как можно реализовать​ (желтый диапазон):​Guest​ данная функция обрабатывает​ с числовыми значениями.​ которое занимает конкретное​Урок:​ поле​ два значения будут​ поскольку РАНГ для​ ниже.​

​ так далее.) и​ /числовое значение. Жмем​ на маркер автозаполнения.​Для изменения ширины столбцов​ и произведения расчетов.​

​ 40 значений. или​ заполнения ячеек исходя​=НАИБОЛЬШИЙ($B$2:$B$14;СТРОКА(1:1))​: Отдельно на листе​ повторяющиеся числа в​ Если задать в​ значение среди других​

​Мастер функций в Excel​«Число»​ иметь одинаковую величину,​ связи не будет​Число​

Пример функции РАНГ.РВ в Excel

​ ВВОД. Если необходимо​ Цепляем его левой​ и высоты строк​ Рабочая область –​ же наоборот если​ из данных. Т.е.​vikttur​ в первой колонке​ списке точно так​ качестве аргумента просто​ величин диапазона. В​Урок:​следует ввести адрес​ то им обоим​ изменяться.​

Пример функции РАНГ.СР в Excel

​ же, как и​ числа, то функция​ отличие от результата​Абсолютные и относительные ссылки​ той ячейки, данные​ будет присвоен второй​Скопируйте образец данных из​ которого определяется ранг.​

​ ставим курсор в​ ведем до конца​ диапазоне выделяем область,​ которые можно заполнять​ 60 она просчитывала​

​ по которым проставляются​ G2 такая же​ второе, третье место),​

​ в обычной формуле.​ вернет ошибку. Нечисловым​РАНГ.РВ​ в Excel​ в которой нужно​

​ следующей таблицы и​Ссылка​ рейтинг.​ эту же ячейку​ столбца. Формула скопируется​ увеличиваем 1 столбец​ данными. Впоследствии –​ первые 60 значений.​

​ оценки. Вот хотелось​ формула, как в​

Как сделать итоговый рейтинг в Excel??

​ ссылка на список​​ формулу что бы​

​ данные.​​Обозначим границы нашей таблицы.​
​ – автоматически изменится​ построения графиков, диаграмм,​Michael_S​ проставление места в​laporta13​

​ 4, 3. И​​ другую формулу (см.​Порядок. Способ упорядочения чисел​может иметь дробное​ Экселе, является​ удобнее выполнить тем​ четвертый. Кстати, полностью​ листа Excel. Чтобы​ чисел. Нечисловые значения​ второй столбец автоматически​При введении повторяющихся значений​

​ Выделяем диапазон с​​ размер всех выделенных​
​ сводных отчетов.​

​. В отличие от​​ пойдет речь ниже.​ оператор​ выделите их и​Порядок​

​ изменения в столбец​​ их. Достаточно набрать​

​ «Главная»-«Границы» (на главной​​Примечание. Чтобы вернуть прежний​ таблицами для начинающих​Покажите в примере,​ даже пальцы на​

​ целые??например екзель ставит​​ порядке занимаемых мест​ в списке (порядковый​

​ или опущен –​​ с предыдущей формулой,​

​ функций​​ Устанавливаем курсор в​РАНГ​ нажмите клавишу F2,​

​ Необязательный. Число, определяющее​​ 3.​ на клавиатуре несколько​ странице в меню​ размер, можно нажать​
​ пользователей может на​

​ номер относительно других​​ значение 1 присваивается​ с помощью изменения​РАНГ​ поле​в более старых​

​ а затем —​​ способ упорядочения.​В файле примере​ символов и нажать​ «Шрифт»). И выбираем​ кнопку «Отмена» или​ первый взгляд показаться​ получить.​ как это сделать​
​ числа например 96.5​ маша и в​ значений). То есть​ максимальному числу в​
​ ссылок с относительных​и​

​«Число»​​ версиях Эксель, так​ клавишу ВВОД. При​Если значение аргумента «порядок»​ прописал вручную что​ Enter.​ «Все границы».​

​ комбинацию горячих клавиш​​ сложной. Она существенно​

​РАНГ.РВ​​, а затем просто​ что данные функции​ необходимости измените ширину​ равно 0 или​

​ можно считать идентичными.​​ столбцов, чтобы видеть​

​ опущено, ранг числа​​ что нужно. Реально​ таблице формулу для​ столбцов и строк​
​ срабатывает тогда, когда​

Создание рейтинга в Excel (Формулы/Formulas)

​ мы с малого:​​ не понял, но​
​Chehonte​ их не на​ Маша изменит свой​

​Вот результат работы функции:​​ нулю числу –​ Алгоритм действий точно​ уровень. То есть,​

​ будет занесен в​​=РАНГ.РВ(число;ссылка;[порядок])​7​ если бы ссылка​

​ формула массива какая​​ одну первую ячейку​

​ можно форматировать данные​​Чтобы вернуть строки в​ ​ с создания и​

​ для изменения размеров​​: Подойдет. Уже сделал!​ одну и ту​
​ бал на 6,​

​Формула в столбце «по​​ номер ранжирования 1​ такой же.​ если два значения​

Автоматическое проставление места в рейтинге по оценкам

​ поле. Точно таким​​Аргументы​3,5​ была списком, отсортированным​ то, типа с​ этого столбца. Программа​ таблицы Excel, как​ исходные границы, открываем​ форматирования таблицы. И​ массива используйте функцию​ Но, я наверно​ же позицию а​ то теперь в​ убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так,​ присваивается минимальному числу​Урок:​ имеют равную величину​

​ же образом заносим​​«число»​

​3,5​​ в порядке убывания.​ использованием, если, мин,​ скопирует в остальные​ в программе Word.​ меню инструмента: «Главная»-«Формат»​ в конце статьи​200?’200px’:»+(this.scrollHeight+5)+’px’);»>СМЕЩ​ не правильно выразился.​ на разные?​ графике она должна​ функция значению 87​ в списке (как​Другие статистические функции в​ и следуют после​ данные и в​и​1​Если значение аргумента «порядок»​
​ макс и тому​ ячейки автоматически.​Поменяйте, к примеру, размер​ и выбираем «Автоподбор​ вы уже будете​

​osuna​​ Мне не рейтинг​
​Guest​

​ стать автоматически первой​​ присвоила средний номер​ будто список отсортирован​ Microsoft Excel​ значения под номером​ поле​
​«ссылка»​

Создание относительной формулы для рейтинга (Формулы/Formulas)

​ кто делал такое.​​ столбец со значениями​
​ «жирным». Можно установить​Для столбцов такой метод​ инструмента для создания​
​ рейтинга, которая будет​ баллов по оценке.​Z​ тоже.​
​Допустим, в списке чисел​Определим ранжирование чисел в​Как сделать автозаполнение в​ обоим будет присвоен​
​, только в этом​«порядок»​Описание (результат)​ ранг числа определяется​Буду благодарен за​ плюс пустая ячейка​ текст по центру,​ не актуален. Нажимаем​ таблиц, чем Excel​ зависеть от значения​ Т.е. набольшая оценка​: При вашем везении​Гость​
​ три повторяющихся значения​

​ списке без повторов:​​ Эксель​ номер 2,5.​
​ случае выделяем весь​— необязательным. В​Результат​

​Простейший способ создания таблиц​​ эту цифру. Выделяем​ ​ Excel для чайников​

​ этих 2-х столбцов​​ НО. Если попадается​

​-38858-​​ всегда по убываню​
​ них ранг 5,​ Следовательно, в данной​
​ для определения ранжирования​
​очень похож на​Если вы хотите, чтобы​нужно ввести ссылку​
​ приведенном выше списке​ списком, отсортированным в​ не пойдет сразу​ «Редактирование» на закладке​
​ уже известен. Но​ любую ячейку в​
​ не терпит спешки.​
​ будут меняться, и​
​ одинаковая оценка, то​
​VictorBaars​ значения​
​ что является средним​
​ функции номера присваивались​ конкретного значения в​ схему предыдущего оператора.​ ранжирование происходило от​
​ на ту ячейку,​ (3)​
​ порядке возрастания.​ говорю.​
​ «Главная» или нажмите​ в Excel есть​ столбце, границы которого​
​ Создать таблицу можно​
​ не факт что​
​ бал ставиться с​

Как работать в Excel с таблицами для чайников: пошаговая инструкция

​: Здравствуйте, уважаемые участники.​Гость​ для 4, 5​ значениям от большего​ диапазоне данных:​ Выглядит он так:​ меньшего к большему,​ где содержится значение,​3​Функция РАНГ присваивает повторяющимся​

​Dinoxromniy​ комбинацию горячих клавиш​ более удобный вариант​ необходимо «вернуть». Снова​ разными способами и​ будет 50 строк.​ +1 следующему по​ Немного потерял знания​: Вот файл​ и 6.​ к меньшему. Максимальному​РАНГ.РВ​=РАНГ.СР(число;ссылка;[порядок])​ то в поле​ порядковый номер которого​=РАНГ(A2;A2:A6;1)​ числам одинаковые значения​

Как создать таблицу в Excel для чайников

​ числу 87 присвоен​и​

​ будет и 60​ реально сделать, чтоб,​ не могу решить​

​: Видимо надо сделать​

Как выделить столбец и строку

​Напомним, что эти две​ номер 1.​РАНГ.СР​ или через Мастер​

​следует установить цифру​«ссылка»​ приведенном выше списке​

​ повторяющихся чисел влияет​ =РАНГ.РВ​ справа каждого подзаголовка​ данными).​ заданный программой показатель​

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

Как изменить границы ячеек

​«1»​содержит адрес всего​ (5)​ на ранги последующих​

​ Аргумент​При работе с данными​ в списке целых​ в столбце 3​ дополнительным инструментам для​

Как вставить столбец или строку

​ работы с данными​ (или нажмите комбинацию​ с размером в​ лист табличного процессора:​ формула рейтинга в​ если участок ничего​ участников конкурса по​: Сделал автосортировку при​

​ для этих целей​ списке, где присутствуют​, который, по сути,​ листе для вывода​ меньшему (а в​

​может иметь два​ выяснить, какое место​

​ возрастанию, дважды встречается​zewsua​ таблицы.​ горячих клавиш CTRL+T).​ 11 пунктов). ОК.​

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

Пошаговое создание таблицы с формулами

​ тот или иной​ 11 будет иметь​ хотя на практике​

​ Чтобы посмотреть итоги,​ данных. Отмечаем, что​ где нужно вставить​ таблица. Столбцы обозначены​

​ ячейке A1​Chehonte​ т.д. По задумке​Микки​ РАНГ в Excel.​ определяется один и​

Как создать таблицу в Excel: пошаговая инструкция

​. Главное отличие формул​ переходим в​ поле оставляем пустым.​«1»​ показатель. В статистике​ ранг 7 (ни​ вероятность этого крайне​

​ нужно пролистать не​

​ это называется ранжированием.​ одно из чисел​ низкая. По крайне​ одну тысячу строк.​ Жмем ОК. Ничего​

​ есть столбец появится​ – цифрами. Если​ ячейке стоит значение​Допишите Если 0;0​ дается 5 баллов,​ см файл в​ описанные примеры позволяют​ Например, числу 7​и​

​через кнопку​ вышеуказанные данные внесены,​ отсчет порядка идет​ В Excel имеются​ не будет иметь​

Как работать с таблицей в Excel

​ мере сз списка​ Удалить строки –​ страшного, если сразу​ слева от выделенной​ вывести этот лист​ 20 то формула​Chehonte​ за второе 4,​ предыдущем моем посте​

​ автоматизировать работу по​ во втором столбце​РАНГ.СР​

​«Вставить функцию»​ жмем на кнопку​ по убывающей, а​ инструменты, которые позволяют​

​ ранга 6).​ 50-ти человек дубликатов​ не вариант (данные​ не угадаете диапазон.​ ячейки. А строка​

​ всяких границ.​ если 40 то​ За наибольшую оценку​ B3-F3 Это предпочтения​ ошибку в макросе​ без применения сортировки.​ строке, и в​

​ них указывает наивысший​Мастера функций​После выполнения данных действий​ данный аргумент не​ процедуру. Давайте выясним,​ связей в учетной​работает как надо,​ этой цели воспользуйтесь​Примечание. Можно пойти по​ – выбираем в​Сначала давайте научимся работать​ 40 строк, и​ она присваивает наименьший​ (номера участников) первого​ в данной строке​

Формула с функцией «если»: оценка в зависимости от числа баллов

​Жду ответа!​​ девятой); в третьем​ уровень при совпадении​выделяем в перечне​ в предварительно указанной​ указан, то он​
​ как ими пользоваться.​ записи. В предыдущем​
​ а я и​ числовыми фильтрами (картинка​
​ другому пути –​ выпадающем меню «Вставить»​ с ячейками, строками​
​ т д.​ бал.​
​ члена жюри, B4-F4​
​ выдает​: Задача по типу​
​ столбце – 3.​

​ значений, а вторая​​ категории​ ячейке будет выведен​
​ автоматически считается программой​Скачать последнюю версию​
​ примере был взят​ не знал что​

Формула вычисления рейтинга

​ выше). Убираете галочки​​ сначала выделить диапазон​
​ (или жмем комбинацию​ и столбцами.​Michael_S​
​C =СУММ(—(ЧАСТОТА((E$3:E$13Правильно поменял?​ второго, и так​ActiveWorkbook.Worksheets(«Ëèñò1»).Sort.SortFields.Clear​ такая:​ Но ни одно​ выводит средний показатель​«Статистические»​ порядковый номер, который​ равным нулю.​ Excel​ пересмотренный ранг 5.5​
​ есть такая функция,​ напротив тех значений,​ ячеек, а потом​ горячих клавиш CTRL+SHIFT+»=»).​​:​
​Код =ЕСЛИ(E3=0;0;СУММ(—(ЧАСТОТА((E$3:E$130)))Ок, все​ далее. Соответственно в​Гость​Допустим в первой​ из чисел во​ в виде десятичной​наименование​ имеет выбранное вами​Данную формулу можно записывать​Для выполнения ранжирования в​ для числа 10.​ (никогда не использовал).​
​ которые должны быть​ нажать кнопку «Таблица».​

​Отмечаем «столбец» и жмем​​Чтобы выделить весь столбец,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=РАНГ(I3;СМЕЩ($I$3;;;$A$1))&ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ($I$3;;;$A$1);I3)-1;»-«&РАНГ(I3;СМЕЩ($I$3;;;$A$1))+СЧЁТЕСЛИ(СМЕЩ($I$3;;;$A$1);I3)-1;»»)​ работает. Но, можно​

​ колонках I2-H11 Нужно​​: Скажите как. И​ колонке имена Петя,​ втором столбце не​
​ дроби. Это единственное​РАНГ.СР​ значение среди всего​ вручную, в ту​

​ Экселе предусмотрены специальные​​ Для этого следует​Буду знать. Спасибо!​ спрятаны.​Теперь вносите необходимые данные​ ОК.​ щелкаем по его​osuna​ ли привязать оценку​ посчитать общее количество​
​ дай бог вам​ Маша, Саша, во​ будет иметь 10,​ различие между этими​и жмем на​
​ перечня данных.​

Источник

Как ранжировать числовые данные в Excel?

Научимся ранжировать числовые данные в Excel с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:

Сортировка данных

Начнем с самого простого и доступного варианта — сортировки.

Мы уже частично разбирали как можно структурировать данные с помощью фильтра и сортировки.
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр, а далее указать по какому критерию нужно произвести сортировку.

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

Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.

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

Ранжирование данных

При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ (а также РАНГ.РВ и РАНГ.СР).

Функция РАНГ в Excel

Синтаксис и описание функции:

РАНГ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке.

• Число(обязательный аргумент) — число для которого вычисляется ранг;
• Ссылка(обязательный аргумент) — массив или ссылка на массив чисел;
• Порядок(необязательный аргумент) — способ упорядочения. Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).

Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР, а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.

Функции РАНГ.РВ и РАНГ.СР в Excel

Синтаксис и описание функций:

РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.

РАНГ.СР(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается средний ранг.

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

Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.

В случае с РАНГ.РВ равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки и Мультиварки соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.

На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок нужно указать значение 1:

Автоматическая сортировка

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

Например, это можно сделать с помощью функции ВПР, или комбинации ИНДЕКС и ПОИСКПОЗ, однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:

В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции СТРОКА или СТОЛБЕЦ, поделенные на заведомо большую величину.

Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:

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

Спасибо за внимание!
Если у вас остались вопросы — пишите в комментариях.

Выполнение ранжирования в Microsoft Excel

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

Функции ранжирования

Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.

Способ 1: функция РАНГ.РВ

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

Синтаксис этого оператора записывается следующим образом:

Аргументы «число» и «ссылка» являются обязательными, а «порядок» — необязательным. В качестве аргумента «число» нужно ввести ссылку на ту ячейку, где содержится значение, порядковый номер которого нужно узнать. Аргумент «ссылка» содержит адрес всего диапазона, который ранжируется. Аргумент «порядок» может иметь два значения – «0» и «1». В первом случае отсчет порядка идет по убывающей, а во втором – по возрастающей. Если данный аргумент не указан, то он автоматически считается программой равным нулю.

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

Выделяем на листе ячейку, в которую будет выводиться результат обработки данных. Щелкаем по кнопке «Вставить функцию». Она локализуется слева от строки формул.

Эти действия приводят к тому, что запускается окно Мастера функций. В нем представлены все (за редким исключениям) операторы, которые можно использовать для составления формул в Excel. В категории «Статистические» или «Полный алфавитный перечень» находим наименование «РАНГ.РВ», выделяем его и щелкаем по кнопке «OK».

После указанных выше действий будет активировано окно аргументов функции. В поле «Число» следует ввести адрес той ячейки, данные в которой нужно ранжировать. Это можно сделать вручную, но удобнее выполнить тем способом, о котором пойдет речь ниже. Устанавливаем курсор в поле «Число», а затем просто выделяем нужную ячейку на листе.

После этого её адрес будет занесен в поле. Точно таким же образом заносим данные и в поле «Ссылка», только в этом случае выделяем весь диапазон, в границах которого происходит ранжирование.

Если вы хотите, чтобы ранжирование происходило от меньшего к большему, то в поле «Порядок» следует установить цифру «1». Если же нужно, чтобы порядок распределялся от большего к меньшему (а в подавляющем числе случаев именно это и требуется), то данное поле оставляем пустым.

После того, как все вышеуказанные данные внесены, жмем на кнопку «OK».

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

Если вы желаете проранжировать всю указанную область, то совсем не нужно для каждого показателя вводить отдельную формулу. Прежде всего, делаем адрес в поле «Ссылка» абсолютным. Перед каждым значением координат добавляем знак доллара ($). При этом изменять значения в поле «Число» на абсолютные ни в коем случае не следует, иначе формула будет рассчитываться некорректно.

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

Как видим, таким образом, формула скопируется, а ранжирование будет произведено на всем диапазоне данных.

Способ 2: функция РАНГ.СР

Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.

Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:

Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.

Производим выделение ячейки на листе для вывода результата. Таким же образом, как и в предыдущий раз, переходим в Мастер функций через кнопку «Вставить функцию».

После открытия окна Мастера функций выделяем в перечне категории «Статистические» наименование РАНГ.СР и жмем на кнопку «OK».

Активируется окно аргументов. Аргументы у данного оператора точно такие же, как и у функции РАНГ.РВ:

• Число (адрес ячейки содержащей элемент, уровень которого следует определить);
• Ссылка (координаты диапазона, ранжирование внутри которого выполняется);
• Порядок (необязательный аргумент).

Внесение данных в поля происходит точно таким же способом, как и у предыдущего оператора. После того, когда все настройки выполнены, жмем на кнопку «OK».

Как видим, после выполненных действий результат расчета был выведен в ячейку, отмеченную в первом пункте данной инструкции. Сам итог представляет собой место, которое занимает конкретное значение среди других величин диапазона. В отличие от результата РАНГ.РВ, итог оператора РАНГ.СР может иметь дробное значение.

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

Как видим, в Экселе существует две функции для определения ранжирования конкретного значения в диапазоне данных: РАНГ.РВ и РАНГ.СР. Для более старых версий программы используется оператор РАНГ, который, по сути, является полным аналогом функции РАНГ.РВ. Главное отличие формул РАНГ.РВ и РАНГ.СР состоит в том, что первая из них указывает наивысший уровень при совпадении значений, а вторая выводит средний показатель в виде десятичной дроби. Это единственное различие между этими операторами, но его нужно учитывать при выборе того, какой именно функцией пользователю лучше воспользоваться.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Microsoft Excel

трюки • приёмы • решения

Как в таблицах Excel ранжировать значения

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

Если вы уже использовали функцию РАНГ, то могли заметить, что положения, выдаваемые этой функцией, не имеют связей, которые могли бы вам подойти. Например, если два значения связаны третьим местом, то функция РАНГ присвоит обоим номер 3. Вы, возможно, предпочтете назначить каждому средний ранг — другими словами, ранг 3,5 для обоих значений, поставленных на третье место.

Excel 2010 содержит новую функцию, названную РАНГ.CP, которая исправляет эту проблему. На рис. 98.1 показана таблица, где используется два метода для ранжирования значений в столбце. Первый метод (столбец С) применяет функцию РАНГ, а в столбце D задается новая функция РАНГ.СР. Диапазон В2:В16 назван Продажи.

Рис. 98.1. Ранжирование данных с помощью функции РАНГ и формул массивов

Обратите внимание на то, что два человека стоят на третьем месте. Функция РАНГ присваивает каждому из них значение 3, а функция РАНГ.CP — 3,5. Кроме того, три человека стоят на десятом месте. Функция РАНГ дает каждому из них ранг 10, а функция РАНГ.CP, с другой стороны, дает им троим ранг 11, что является средним для 10,11 и 12.

Формулы, которые используют функцию РАНГ.CP, вернут ошибку в предыдущих версиях Excel. Если вы собираетесь делиться книгой с теми, кто использует версию Excel ниже 2010, то можете задействовать формулу массива вместо функции РАНГ.СР.

Посмотрите на рис. 98.1 и введите следующую формулу массива в строку 4. Затем скопируйте ее в нижестоящие ячейки. Результат будет точно таким же, как и при использовании функции РАНГ.СР:
=СУММ(1*(B4 .

Формула массива представляет собой особый вид формулы, которая работает с внутренними массивами данных. При вводе формулы массива вы должны нажать Ctrl+Shift+Enter (а не просто Enter), чтобы указать, что это формула массива. Excel добавит фигурные скобки вокруг формулы, напоминая вам, что это формула массива. Если вы не нажмете Ctrl+Shift+Enter, формула не будет работать.

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

Функция РАНГ() в MS EXCEL

Синтаксис

РАНГ(число;ссылка ;порядок)

Число — число, для которого определяется ранг.

Ссылка — ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;<10:50:30:40:50>) работать не будет. Но, если ввести формулу =РАНГ(B7;$A$7:$A$11) , то она будет работать (хотя ячейка B7 — вне списка с числами). Если в B7 содержится число вне списка с числами, то формула вернет ошибку #Н/Д.

Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.

Порядок — число, определяющее способ упорядочения.

• Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются большие ранги.
• Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, большим значениям присваиваются большие ранги.

Примечание: Начиная с MS EXCEL 2010 для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ() . Последняя функция аналогична РАНГ() .

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне A7:A11, то формула =РАНГ(A7;$A$7:$A$11) определит ранг числа из ячейки А7 (см. файл примера ).

Т.к. аргумент порядок опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) — минимальному (10).

Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;»>»&A7)+1

В столбце С приведена формула =РАНГ(A7;$A$7:$A$11;1) с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;» «&B3)+1

В столбце А содержатся названия группы, в столбце В — значения.

Связь функций НАИБОЛЬШИЙ() / НАИМЕНЬШИЙ() и РАНГ()

Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11.

Определяем ранг в списке с повторами

Если список содержит повторы, то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция РАНГ() или РАНГ.РВ() ) или среднее значение, если РАНГ.СР() ). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

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

В этом нам поможет формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1

Предполагается, что исходный список с числами находится в диапазоне А37:А44.

Примечание. В MS EXCEL 2010 добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.

Массив рангов

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

Как видно из картинки выше, значения из диапазона В60:В67 и в ячейке D60 совпадают. Такой массив можно получить с помощью формулы =РАНГ(A60:A67;A60:A67) или с помощью формулы =СЧЁТЕСЛИ(A60:A67;»>»&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;» Похожие задачи

Сводные таблицы Excel

В Excel 2010 появились функции ранжирования данных. С помошью этих функций различным строкам в наборе данных присваиваются ранги 1, 2, 3 и т.д. Ранг 1 можно присвоить наибольшему либо наименьшему значению. Как показано на рис. 3.38. показатели региона Юго-Запад были перемещены с пятого на третье место в 2010 году. В 2011 и в 2012 годах эти показатели находятся на четвертом месте.

Новые функции ранжирования, которые появились в Excel 2010, загадочным образом обрабатывают привязки. «Загадочность» проявляется в том, что применяемые при обработке привязок методы отличаются от методов, используемых функциями Excel РАНГ (), РАНГ.CP либо РАНГ.РВ.

Рис. 3.38. Функции ранжирования появились в Excel 2010

Например, если два менеджера связаны между собой, им присваивается ранг 1,вто время как третьему менеджеру присваивается ранг 2. На рис. 3.39 сравниваются результаты применения функции ранжирования сводных таблиц и функций вычисления ранга Excel.

Рис. 3.39. В сводных таблицах Excel 2010 появился новый способ обработки привязок

Трудно сказать однозначно, какая функция ранжирования лучше, — функция ранжирования сводных таблиц или функция вычисления ранга Excel. Просто используйте данные функции там, где это целесообразно.