Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: у вас есть числа в диапазоне D2:D11. Вам нужна формула, чтобы переставить числа по возрастанию или по убыванию.
Решение: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ (рис. 1). =НАИМЕНЬШИЙ($D$2:$D$11;1) возвращает наименьшее число в диапазоне, =НАИМЕНЬШИЙ($D$2:$D$11;2) возвращает второе минимальное число, и так далее.
Рис. 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) позволяет сделать переменным второй аргумент функции НАИМЕНЬШИЙ
Резюме: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ для сортировки диапазона по убыванию или по возрастанию.
Скажите у меня в диапозоне( кол-во 39) было много повторяющихся чисел а после применения функции их осталось 20 т.е повторяющиеся колиичества он два раза не вывел, хотя в вашем примере он вывел все 10 количеств.
Татьяна, пришлите файл в личку.
Уведомление: 02 Ноября 2015 - Excel-дайджест - Excel для экономиста