Сортировка с помощью формулы

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: у вас есть числа в диапазоне D2:D11. Вам нужна формула, чтобы переставить числа по возрастанию или по убыванию.

Решение: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ (рис. 1). =НАИМЕНЬШИЙ($D$2:$D$11;1) возвращает наименьшее число в диапазоне, =НАИМЕНЬШИЙ($D$2:$D$11;2) возвращает второе минимальное число, и так далее.

Рис. 1. Сортировка с помощью формулы

Рис. 1. Сортировка с помощью формулы

Скачать заметку в формате Word или pdf, примеры в формате Excel

Формула в F3: =НАИМЕНЬШИЙ($D$2:$D$11;СТРОКА(A1)). Во-первых, ссылки в первом аргументе необходимо сделать абсолютными, чтобы формула оставалась адекватной при копировании вниз по столбцу. Во-вторых, вы должны найти способ, чтобы изменять второй аргумент с 1 на 2, 3 и т.д., при копировании вниз по диапазону. Замените жесткую единицу на СТРОКА(А1). Эта функция возвращает 1, а при копировании будет последовательно превращаться в СТРОКА(А2)=2, СТРОКА(А3)=3 и т.д.

Формула в Н3: =НАИБОЛЬШИЙ($D$2:$D$11;СТРОКА(C1)). При копировании вдоль столбца формула упорядочит числа по убыванию.

Примечание: Excel правильно обрабатывает повторяющиеся значения. Например, в ячейках D2 и D7 оба значения равны 10. В ячейках F4 и F5 Excel возвращает 10 в качестве значения функции НАИМЕНЬШИЙ для второго и третьего наименьшего в диапазоне.

Обратите внимание, как изменяется формула по мере копирования ее вниз по диапазону (рис. 2).

Рис. 2. Использование функции СТРОКА(А1)

Рис. 2. Использование функции СТРОКА(А1) позволяет сделать переменным второй аргумент функции НАИМЕНЬШИЙ

Резюме: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ для сортировки диапазона по убыванию или по возрастанию.

Комментарии: 3 комментария

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

Татьяна, пришлите файл в личку.

[…] Сортировка с помощью формулы на сайте Сергея Багузина. Что-то подобное проскакивало и в моих статьях. […]


Прокомментировать