Добрый день, уважаемые подписчики и читатели блога. Сегодня мы поговорим о сортировке данных в таблицах Excel. Но сортировке не простой, а с определённым условием — менять очередность данных в таблице нельзя.
Действительно, наши проблемы решил бы простой фильтр, но любая сортировка по возрастанию или убыванию изменит положение данных в таблице согласно их величине. Рассмотрим вот такой пример. Имеется таблица:
Если мы включаем фильтр от большего к меньшему, то порядок магазинов изменится, так как необходимо увидеть лучший и худший результаты. Выделяем заголовок таблицы, на вкладке «Главная» нажимаем кнопку «Сортировка и фильтр» далее выбираем строчку «Фильтр». Появится возможность выбирать критерии сортировки. Согласно условию выбираем сортировку «От максимального к минимальному».
Порядок магазинов изменился, но, согласно условию задачи, этого допускать нельзя. В таком случае идеально подойдёт функция РАНГ. Начиная с версии Excel 2010 были введены новые функции РАНГ.РВ и РАНГ.СР. Функция РАНГ осталась для совместимости с предыдущими версиями программы поэтому будет использоваться схожая функция РАНГ.РВ.
Главное отличие функций — РАНГ.РВ учитывает равные значения и присваивает им одинаковый ранг.
РАНГ.СР — возвращает среднее между двумя одинаковыми рангами (их порядковыми номерами).
Синтаксис формулы будет следующий:
=РАНГ.РВ(число;ссылка;порядок)
число — числовое значение для которого вычисляется ранг (обязательный аргумент);
ссылка — адрес ячейки или диапазона ячеек среди которых будет произведено ранжирование (обязательный аргумент);
порядок — порядок выставления ранга (если стоит 0 или поле останется пустым значит присвоение ранга будет по убыванию, если стоит 1, то будет производиться ранжирование по возрастанию, необязательный аргумент).
Совершим следующие действия:
- добавим в нашу таблицу дополнительный столбец «Место в прибыли»;
- вызываем мастер функций;
- вводим в поле поиска РАНГ.РВ;
- нажимаем «ОК»;
- заполняем аргументы функции.
В этой формуле специально закреплён диапазон от B2 до B10, чтобы можно было использовать маркер автозаполнения. Протянем за него до конца таблицы. Результат не заставит себя долго ждать.
В аргументе функции «Порядок» был выставлен 0, поэтому ранг 1 назначен самому большому значению, 6 — самому маленькому.
Дальнейшую сортировку можно усложнять с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ.