Формула в excel ранг

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

​ выше, значения из​ (выделено цветом) будет​ числу, меньшим значениям​​ 1, т.к. это​​ помогло.)​

Описание

​Guest​ формула: (РАНГ +​ колличество повторяющихся значений​ массива.​ Это абсолютный аналог​Аргумент, определяющий способ упорядочения​ РАНГ.СР. Все они​ процентиль-ранг (percentile rank),​ массив. Можно указать​

​ к наибольшему.​ рангов = [СЧЕТ(ссылка)​ числа определяется в​

Синтаксис

​ присвоен одинаковый ранг​ присваиваются б​

​ наименьшее число, число​​Dubick​: Ваш пример-это как​

​ РАНГ + СЧЕТ​​ 17/2=8,5 следующий за​Скачать примеры функции ранжирования​ предыдущей функции. Синтаксис​ чисел, равен «0».​ возвращают номер числа​

​ который служит оценкой​​ ссылку на диапазон​5​

Замечания

​ + 1 –​ Excel так, как​В60:В67​ (максимальный). Наличие повторяющихся​о​ 10 — ранг​: Есть такая задача:​ раз то, что​

​ — 1)/2, упростив​ ними ранг идёт​ РАНГ в Excel.​ такой же. Буквы​ Следовательно, в данной​ в ранжированном списке​ относительного положения значения​ только с числами​

​=РАНГ.РВ(A6;A2:A6)​ РАНГ.РВ(число, ссылка, 0)​ если бы ссылка​и в ячейке​ чисел влияет на​льшие ранги.​ 2, а 20​есть порядок чисел:​ и нужно было!​ ее математически получилась​ 10.​Таким образом, все выше​ «РВ» в названии​ функции номера присваивались​ числовых значений. Рассмотрим​ в массиве. Для​

​ и не ставить​Ранг числа 2 в​ – РАНГ.РВ(число, ссылка,​ была списком, отсортированным​D60​ ранги последующих чисел.​Если порядок — любое​ — ранг 3​ 10; 4; 8;​ Текстовое n. 35 я​ вышеуказанная формула​Какие мысли?​ описанные примеры позволяют​ указывают на то,​ значениям от большего​ подробнее синтаксис, примеры.​ заданного значения вычисляется​ галочку.​ том же списке.​ 1)]/2.​ по убыванию.​совпадают. Такой массив​

​ Например, если в​ ненулевое число, то​ (это ранг по​ 12; 4; 5;​ указывал последовательность до​Guest​

​Guest​ автоматизировать работу по​ что при обнаружении​ к меньшему. Максимальному​Функция используется при ранжировании​ сколько значений в​Ссылку на выходной интервал​ Так как значение​В примере, содержащемся в​Если значение аргумента «порядок»​ можно получить с​ списке целых чисел,​ то MS EXCEL​ возрастанию, когда наименьшему​ 17; 19; 7​ 35! Мне и​: Спасибо, очень доступно​

Пример

​: функция РАНГ​ ранжированию данных и​ формулой одинаковых значений​ числу 87 присвоен​ в перечне чисел.​ массиве меньше или​ укажем на текущем​ аргумента «Порядок» не​ книге, РАНГ.РВ(A3,A2:A6,1) равен​ — любое число,​ помощью формулы =РАНГ(A60:A67;A60:A67) или с​ отсортированных по возрастанию,​ присваивает ранг=1 минимальному​

​ значению присваивается ранг​

​Функцией РАНГ.РВ их​

​ нужно было, чтоб​

​ составлению рейтинга значений​

​ функция вернет высший​

​ То есть позволяет​

​ равно ему. Точнее​

​ листе, например, в​ указано, список сортируется​ 3. Поправочный коэффициент​ кроме нуля, то​ помощью формулы =СЧЁТЕСЛИ(A60:A67;»>»&A60:A67)+1​ дважды встречается число​ числу, б​ 1). Если список​

​Guest​: Большое спасибо, суть​ без применения сортировки.​ номер ранжирования (то​В третьем столбце приведена​ узнать величину числа​ — какой процент​

​ по умолчанию — от​

​ равен (5 +​ ранг числа определяется​

​Ранги по возрастанию можно​

Замечания

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

Может потребоваться использование определения ранга с учетом связей в учетной записи. В предыдущем примере был взят пересмотренный ранг 5.5 для числа 10. Для этого следует добавить поправочный коэффициент (см. ниже) к значению, возвращаемому функцией РАНГ. Данный поправочный коэффициент может применяться в обоих случаях: когда ранг вычисляется в порядке убывания (аргумент «порядок» имеет нулевое значение или опущен) и в порядке возрастания (значение аргумента «порядок» не равно нулю).

Поправочный коэффициент для связанных рангов = /2.

В следующем примере РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.

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

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

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

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

1. Производим выделение ячейки на листе для вывода результата. Таким же образом, как и в предыдущий раз, переходим в Мастер функций через кнопку «Вставить функцию».
2. После открытия окна Мастера функций выделяем в перечне категории «Статистические» наименование РАНГ.СР и жмем на кнопку «OK».
3. Активируется окно аргументов. Аргументы у данного оператора точно такие же, как и у функции РАНГ.РВ:
   • Число (адрес ячейки содержащей элемент, уровень которого следует определить);
   • Ссылка (координаты диапазона, ранжирование внутри которого выполняется);
   • Порядок (необязательный аргумент).

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

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

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

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

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

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

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

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

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

=РАНГ.РВ(число;ссылка;)

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

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

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

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

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

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

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

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

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

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

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

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

Урок: Мастер функций в Excel

Урок: Абсолютные и относительные ссылки в Excel

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

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

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

1. Число. Для, которого необходимо определить номер в ранжировании.
2. Ссылка. На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
3. Порядок. Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​— число, для​ 5 и т.д​ цифр, часто они​ берутся с ссылкой​ эксел новичок)​ примеру) 1 .​ в обычной формуле.​ тот же номер.​

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

​ 1; 26 –2;​ равны ему). Повторы​ выше):​ анализа.​ только один раз,​ на ранги последующих​В этой статье описаны​ ранги не повторялись​ из ячейки​ которого определяется ранг.​ соотвествовало 4, 5,​ повторяются. Нужно каждому​ на другой лист).​=РАНГ(A1;A$1:A$12;1)+((СЧЁТЕСЛИ(A$1:A$12;A1))-1)/2​ 2 . 3​ Если необходимо избежать​

​ Например, числу 7​

1. ​ 30 –3 (как​ в массиве имеют​Столбец Массив. Это просто​
2. ​Надстройка Пакет анализа MS​ поправочный коэффициент будет​ чисел. Например, если​ синтаксис формулы и​ (например, при определении​А7​Ссылка​ 7, 8 и​ значению присвоить свой​
3. ​ Так вот когда​сначала указываем функцию​ . 4 .​ повторений рангов, используем​ во втором столбце​ наибольшее значение в​ один и тот​ наш входной массив​ EXCEL позволяет быстро​ равен 0, поскольку​ в списке целых​ использование функции​ призовых мест, когда​(см. файл примера). ​— ссылка на​ т.д.​

​ уникальный ранг. В​ у меня на​

​ РАНГ, A$1:A$12 -​ 5 . 6​ другую формулу (см.​ присвоен номер 9​ списке).​ же ранг и​ сортированный по убыванию.​ вычислить ранги для​

​ РАНГ.РВ для связи​ чисел, отсортированных по​РАНГ​

​ нельзя занимать нескольким​Т.к. аргумент​ список чисел (диапазон​

​AlexM​ случае, если значения​ другом листе последовательность​ ссылка на массив,​ . 7 .​ выше).​ (и во второй​Синтаксис функции:​ процентиль-ранг. Подробнее про​ Его можно сформировать​ значений массива.​ не будет изменяться.​ возрастанию, дважды встречается​в Microsoft Excel.​ людям одно место).​порядок​ ячеек с числами).​

​: Изобразите задание в​ одинаковы, по идее,​ скажем не из​ а что тут​ 8 . 8​

​Возвращает номера числового значения​ строке, и в​Число. Для, которого необходимо​ процентиль-ранг см. статью про​ формулой =НАИБОЛЬШИЙ($B$7:$B$32;A7) (см.​Пусть в диапазоне​Скопируйте образец данных из​

​ число 10, имеющее​Возвращает ранг числа в​В этом нам поможет​опущен, то MS​ Напрямую массив задать​

​ файле. Будет понятнее.​

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

​ каждому последующему нужно​ 35, а к​ указывают A1 и​ . 9 .​ в списке (порядковый​ девятой); в третьем​ определить номер в​ Процентили (раздел про Функцию​ файл примера)​В7:В32​ следующей таблицы и​ ранг 5, число​ списке чисел, то​ формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1​ EXCEL присвоил ранг=1​

​ нельзя, формула =РАНГ(10;)​buchlotnik​ лишь прибавлять единицу.​ примеру, из 20,​ 1. далее условие​ 10 . 10​ номер относительно других​ столбце – 3.​ ранжировании.​ ПРОЦЕНТРАНГ.ВКЛ()). Этот столбец​

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

​Столбец со странным заголовком​имеется числовой массив​ вставьте их в​ 11 будет иметь​ есть его величину​Предполагается, что исходный список​ максимальному числу (50),​ работать не будет.​

​К примеру:​ то остальные 15​ идёт- всё что​ и мне необходимо​ значений). То есть​

​ Но ни одно​Ссылка. На массив чисел​ можно сформировать с​

​ Точка. В нем​ (см. файл примера).​ ячейку A1 нового​ ранг 7 (ни​ относительно других значений​

​ с числами находится​

​ а максимальный ранг​ Но, если ввести​Dubick​Значение Ранг​ в данном листе​ я понял, а​ их проранжировать (т.е​ выполняет ту же​

​ из чисел во​ или диапазон ячеек​

​ помощью формулы =ПРОЦЕНТРАНГ.ВКЛ($B$7:$B$32;I7).​ выведены первоначальные позиции​Вызовем диалоговое окно надстройки​ листа Excel. Чтобы​ одно из чисел​ в списке. Если​

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

У нас есть учебная таблица со списком работников и окладом:

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

Диапазон сортировки расширяется автоматически. Получается так:

Как сортировать по алфавиту в Excel?

Выделяем столбец с фамилиями – правая кнопка мыши – выбираем: настраиваемая сортировка.

«Сортировать по» — выбираем имя столбца, значения которого нужно отсортировать по алфавиту. У нас – «ФИО». В сортировке выбираем, на какие параметры будет ориентироваться инструмент. Это может быть цвет ячейки, шрифта, значок ячейки. В нашем примере – «значения» (анализ значений в ячейке). «Порядок» — от А до Я либо от Я до А. Сделаем прямой порядок. Жмем ОК.

Amount billed

Функция Excel РАНГ.СРЕДНЯЯ

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

Функция RANK.AVG VS. Функция РАНГ.ЭКВ.

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

аргументы

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

Пример

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

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

=RANK.AVG(C6,$C$6:$C$18,0)

Ноты:

1) В этом случае функция RANK.AVG присваивает среднее значение ранга (6.5) числу 77, которое встречается в списке дважды и занимает 6-ю и 7-ю позиции. Между тем, вы можете видеть, что ни одно из чисел не имеет ранга 7. Это связано с тем, что наличие повторяющихся значений влияет на ранги последующих чисел.

2) Приведенная выше формула ранжирует все оценки в порядке убывания. Чтобы ранжировать баллы в порядке возрастания, можно применить следующую формулу:

=RANK.AVG(C6,$C$6:$C$18,1)

Связанные функции

Функция Excel КВАРТИЛЬ.ВКЛ Функция КВАРТИЛЬ.ВКЛ возвращает квартиль для заданного набора данных на основе диапазона процентилей от 0 до 1 включительно.

Функция Excel КВАРТИЛЬ Функция КВАРТИЛЬ вычисляет квартиль для заданного набора данных.

Функция Excel КВАРТИЛЬ.ИСКЛ Функция КВАРТИЛЬ.ИСКЛ возвращает квартиль для заданного набора данных на основе диапазона процентилей от 0 до 1 исключая.

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

Функция Excel RANK.EQ Функция RANK.EQ возвращает ранг числа по сравнению с другими числами в том же списке.

Kutools for Excel — поможет вам выделиться из толпы

Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80% времени.

• Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
• Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
• Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
• 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.

Функция МАКС

Возвращает максимальное числовое значение из списка аргументов.

Синтаксис: =МАКС(число1; ; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.

Пример использования:

=МАКС({1;2;3;4;0;-5;5;”50″}) – возвращает результат 5, при этом строка «50» игнорируется, т.к. задана в массиве.=МАКС(1;2;3;4;0;-5;5;”50″) – результатом функции будет 50, т.к. строка явно задана в виде отдельного аргумента и может быть преобразована в число.=МАКС(-2; ИСТИНА) – возвращает 1, т.к. логическое значение задано явно, поэтому не игнорируется и преобразуется в единицу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачать файл с примером.

Удачи вам и до скорых встреч на страницах блога TutorExcel. Ru!

Присвоить каждому значению уникальный ранг (Формулы/Formulas)

​Для работы функции можно​​ аргумент равен «0»​ функцию также см.​ каждому повтору будет​ см. статью Пакет​7​ числа 10. Для​ ниже.​ повторяющимся числам одинаковые​ вариант: =СЧЁТЕСЛИ($A$7:$A$11;»​​ то формула вернет​​ нажатием Ctrl+Shift+Enter в​​ она перестала корректно​​MCH​​ справку.​​ им присваивается СРЕДНИЙ​ ​ три повторяющихся значения​ ​ установить пределы. Например,​ или опущен –​ статью про Процентили.​ соответствовать своя позиция.​ анализа MS EXCEL.​3,5​ этого следует добавить​​Число​ значения ранга). В​Если исходный список отсортировать,​​ ошибку #Н/Д.​ строке формул.​ работать.​

​: Нужен пример (​​Если записать просто​ РАНГ.​​ (выделены оранжевым цветом).​ необходимо ранжировать только​

​ значение 1 присваивается​​Примечание​ Этот столбец можно​

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

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

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

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

Для ячейки H2 требуется определить формулу. Добавим в функцию РАНГ.СР() её обязательные элементы. Для этого включим в качестве аргумента число функцию ИНДЕКС(), где в аргументе массив определим общий диапазон интересующих нас данных в виде значения $B$2:$G$5. В качестве аргумента номер_строки функции укажем 0, а в качестве аргумента номер_столбца – функцию ПОИСКПОЗ().

Для того, чтобы функция ПОИСКПОЗ() работала корректно, укажем на диапазон, который будет интересовать нас при выборе месяца. Аргументы этой функции будут выглядеть следующим образом: $H$1;$B$1:$G$1;0, где $H$1 – ячейка с выбором месяца, значение которого будет искаться в диапазоне $B$1:$G$1 с полным соответствием.

В качестве аргумента ссылка функции РАНГ.СР() будет использована функция СМЕЩ(), позволяющая возвращать ссылку на ячейку, находящуюся в некотором известном отдалении от указываемой ячейки. Проще говоря, мы указываем ячейку $B$2 как основу, а затем, не смещая её по строкам, указываем с помощью уже известной функции ПОИСКПОЗ() с аргументами $H$1;$B$1:$G$1;0. Добавим ко второму аргументу функции СМЕЩ() значение «-1», т.к. для первой строки нам понадобится значение 0, для второй – 1 и т.д.

Для записи необязательного, но в нашем случае важного аргумента высота воспользуемся простой функцией СЧЁТЗ(), которая поможет подсчитать количество ячеек, заполненных в диапазоне $B$2:$B$5. В качестве аргумента ширина укажем значение «1»

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

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

Функция РАНГ устаревшая с 2010-й версии Excel, оставлена для обратной совместимости с 2007 и более ранними версиями, рекомендуется воспользоваться функциями и .

Работа с формулами

Вычисление значений в формулах

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

В формуле могут использоваться арифметические операторы + — * /.

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

Ссылки на ячейку или на группу ячеек

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

Например, D50 – ссылка на ячейку, расположенную в пересечении столбца D с 50-й строкой.

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

Диапазон ячеек столбца A с 10-й строки по 20-ю – A10:A20.

Относительные и абсолютные ссылки

Относительные ссылки указывают относительное расположение ячеек и меня­ются при копировании.

Копирование ячейки В6 в ячейку В7 приведет к изменению формулы – в ячейке В7 будет содержаться формула =A7+5.

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

Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью, которая не должна изменяться.

Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере: =A5*$C$1.

Присвоение имени ячейке, диапазону или формуле

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

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

Для удаления имени из списка следует выделить его и щелкнуть кнопку Удалить.

Функции

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

Наиболее распространенной является функция СУММ, суммирующая диапазоны ячеек.

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

Amount Billed