Как быстро ранжировать значения, игнорируя ошибки в Excel?

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

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

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

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

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

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

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

Пример 1: Имеется документ, содержащий имена студентов и их общие оценки за семестр. Вы хотите отсортировать студентов по их оценкам.

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

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

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

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

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

Добавим столбец ранжирования и напишем следующую формулу в ячейке C2: =RANK. RV($B2;$B$2:$B$10;0). Протянув эту формулу вниз, вы получите следующий рейтинг:

Чтобы наша таблица была читаемой, необходимо добавить еще три столбца. Первый столбец будет содержать порядковые номера, второй — названия товаров, а третий — их количество. Давайте применим формулу к ячейке F2, чтобы таблица обновляла значения при изменении значений в столбцах A и B:

И на ячейку G2 в формуле:

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

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

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

Ячейка H1 будет служить заголовком сортировки. Выберите ее, а затем нажмите «ДАННЫЕ — Работа с данными — Проверить данные».

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

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

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

R ANG. Используя функцию SR(), мы можем вернуть ссылку на ячейку, которая находится на известном расстоянии от указанной ячейки. Все достаточно просто, указываем в качестве базы ячейку $B$2, а затем используем уже известную функцию ПОИСКПОЗ() с аргументами $H$1;$B$1:$G$1;0. В нашем случае нужно добавить «-1» ко второму аргументу функции SMESH(), так как для первой строки нам нужен 0, для второй — 1 и т.д.

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

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

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

Как быстро ранжировать значения, игнорируя ошибки в Excel?

В Excel I F — одна из самых универсальных и широко используемых функций, которая часто используется в нескольких местах в одной формуле, а также в сочетании с другими функциями. Поскольку такие утверждения могут быть встроены сложным образом, довольно просто запустить ошибку «#VALUE!». Обычно ошибку можно скрыть, добавив в формулу функции обработки ошибок, такие как EERROR, EERR или IFERROR.

Проблема: аргумент ссылается на ошибочные значения.

Если ячейка содержит ссылку на значение ошибки, то значение равно #VALUE. ошибка отображается «#VALUE! «.

Решение: Используйте функцию IF для обработки ошибок, таких как ERROR, EOSH и ELSE. В следующих разделах описано, как использовать функции ЕСЛИ, ERROR, EOSH и ESRROR в формуле, если аргумент относится к неправильным значениям.

Если вы используете Excel 2007, гораздо лучше использовать EERROR или EERR, поскольку они не требуют избыточного построения формул. EERROR и EERR анализируют формулу дважды, сначала для поиска ошибок, а затем для возврата результатов. IFERROR вычисляется только один раз.

Конструкция =ЕСЛИОШИБКА(Формула;0) гораздо лучше конструкции =ЕСЛИ(ЕОШИБКА(Формула;0;Формула)).

Проблема: неправильный синтаксис.

Если синтаксис функции построен неправильно, она может вернуть «#VALUE! «.

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

Оно будет умножено на 15%, если значение в ячейке A5 меньше 31 500. Проверьте, если значение меньше 72 500. В этом случае значение, если оно присутствует, должно быть умножено на 25%; в противном случае оно должно быть умножено на 28%.

Чтобы использовать функцию USER с существующей формулой, просто вставьте готовую формулу в функцию USER:

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

Примечание: Значения в расчетах разделяются точкой с запятой. Если вы разделите два значения запятой, функция ЕСЛИ будет рассматривать их как одно дробное значение. Процентные множители сопровождаются символом %. Это указывает Excel, что значение следует рассматривать как процент. В противном случае такие значения следует указывать в виде дробных множителей, например, «E2*0,25».

У вас есть вопрос об определенной функции?

Дополнительные сведения

Вы всегда можете задать вопрос в техническом сообществе Excel или обратиться за помощью в сообщество «Ответы».