Функции РАНГ, РАНГ

Синтаксис

=РАНГ(число, ссылка, [порядок]) — английская версия

=РАНГ(номер,ссылка,[порядок]) — русская версия

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

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

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

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

РАНГ(номер, ссылка, порядок)

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

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

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

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

 

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

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

 

 

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

 

 

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

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

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

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

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

=RANGE.EQ(число,ссылка,[порядок])

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

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

 

 

• Эти действия откроют окно Feature Wizard. Знакомит со всеми (за редким исключением) операторами, которые можно использовать для создания формул в Excel. В категории «Статистика» или «Полный алфавитный список» находим название «РАНК.РВ», выделяем его и нажимаем кнопку «ОК».

 

 

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

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

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

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

 

 

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

   

  

   

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

   

  

   

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

   

  

   

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

 

 

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

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

Синтаксис RANK.AVG очень похож на предыдущий оператор. Выглядит ли это так:

=ДИАПАЗОН.СРЕДНИЙ(число,ссылка,[порядок])

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

 

 

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

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

 

 

• Как видите, после выполненных действий результат расчета отобразился в отмеченной ячейке в первом пункте данной инструкции. Сама сумма представляет собой место, которое занимает конкретное значение среди других значений в диапазоне. В отличие от результата RANK.EQ, результат оператора RANK.AVG может иметь дробное значение.

 

 

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

 

 

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

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

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

j — наименьшее число, 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, для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ(). Последняя функция аналогична RANK().

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

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

 

 

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

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

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

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

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

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

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

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

 

 

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

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

 

 

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

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

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

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

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

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

 

 

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 касается всех столбцов при сортировке данных.

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

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

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

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

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

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

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

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

Что, если мы хотим…

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

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

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

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

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

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

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

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

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

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

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

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