Сортированный список в EXCEL (ТЕКСТовые значения)

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

Сортированный список может быть полезен при создании Выпадающего (раскрывающегося) списка . Некоторые функции для нормальной работы также требуют сортированный список: ПРОСМОТР() , ВПР() , ПОИСКПОЗ() .

Решение задачи о сортировке списка с числами приведено в одноименной статье Сортированный список (Числовые значения) , в которой использована функция НАИМЕНЬШИЙ() . Если список содержит только текстовые значения, то функция НАИМЕНЬШИЙ() уже не сработает (см. Файл примера ).

Для сортировки списка с текстовыми значениями будем использовать операции сравнения. Операции сравнения <, > обычно используемые для числовых значений, отлично справляются и с текстовыми значениями. Например, если сравнивать имена Миша и Маша , то Маша < Миша. Слова сравниваются побуквенно в соответствии с алфавитом. Сначала берутся 2 первые буквы (в нашем случае обе =М ), если они равны, то берутся вторые буквы и т.д. В нашем случае а<и (буква и по алфавиту идет после а ) и, следовательно, Маша < Миша.

Для удобства создадим Динамический диапазон Список, содержащий исходный список текстовых значений (без пустых ячеек):

• вызовите меню Формулы/ Определенные имена/ Присвоить имя
• в поле Имя введите Список ;
• в поле Диапазон введите формулу =СМЕЩ($A$5;;;СЧЁТЗ($A$5:$A$22))
• нажмите ОК.

Добавим служебный столбец с формулой, с помощью которой будут попарно сравниваться все значения и, выводиться числа: сколько раз какое слово «больше» других. Формула выглядит так: =ЕСЛИ($A5<>"";СЧЁТЕСЛИ(Список;"<"&$A5)+1;"")

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

Далее в столбце, который будет содержать сортированные по возрастанию значения, запишем формулу: =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(СТРОКА(A1);$D$5:$D$22;0));"")

Функции ИНДЕКС() и ПОИСКПОЗ() позволяют вывести соответствующие имена в правильном порядке.

Для сортировки по убыванию напишем другую формулу: =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(СЧЁТЗ(Список)+1-СТРОКА(A1);$D$5:$D$22;0));"")

Сортировка списка с использованием формул массива

Другим вариантом сортировки является вариант с использованием формул массива .

В этом случае служебный столбец не нужен. Запишем формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список;"<"&Список); СТРОКА()-СТРОКА($K$5)); СЧЁТЕСЛИ(Список;"<"&Список);0));"")

Не забудьте нажать CTRL+SHIFT+ENTER .