Сортировка формулой

Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (Data - Sort). Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же "на лету" сортировать список формулой?

Способ 1. Числовые данные

Если список содержит только числовую информацию, то его сортировку можно легко сделать с помощью функций НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW):

sort_by_formulas1.gif

 

Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) - это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) - второе по счету наименьшее и т.д.

Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2  и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.

Способ 2. Текстовый список и обычные формулы

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

Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF):

sort_by_formulas2.gif

В английской версии это будет:

=COUNTIF(A:A,"<"&A1)+COUNTIF($A$1:A1,"="&A1)

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

Теперь полученные номера надо расставить последовательно по возрастанию. Для этого можно использовать функцию НАИМЕНЬШИЙ (SMALL) из первого способа:

sort_by_formulas3.gif

 

Ну, и наконец, осталось просто вытащить из списка имена по их номерам.  Для этого можно использовать такую формулу:

sort_by_formulas4.gif

 

Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.

Способ 3. Формула массива

Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):

sort_by_formulas5.gif

 

В ячейку Е1 копируем нашу формулу:

=ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; "<"&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; "<"&List); 0))

Или в англоязычной версии:

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.

Если нужно, чтобы формула учитывала не фиксированный диапазон, а могла подстраиваться при дописывании новых элементов к списку, то нужно будет слегка изменить стратегию.

Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы - Диспетчер имен (Formulas - Name Manager), создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=OFFSET(C1,0,0,СЧЁТЗ(C1:C1000),1)

Во-вторых, вышеописанную формулу массива нужно будет протянуть вниз с запасом - с расчетом на вводимые в будущем дополнительные данные. При этом формула массива начнет выдавать ошибку #ЧИСЛО на незаполненных пока ячейках. Чтобы ее перехватить, можно использовать функцию ЕСЛИОШИБКА, которую нужно дописать "вокруг" нашей формулы массива:

=ЕСЛИОШИБКА(ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; "<"&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; "<"&List); 0));"")

=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")

Она перехватывает ошибку #ЧИСЛО и выводит вместо нее пустоту (пустые кавычки).

Ссылки по теме:

 



Vitaly
26.10.2012 00:30:08
Всё это хорошо. Но как быть когда формула нужна для того, чтобы брать данные из соседних ячеек справа, выбирая имя в выпадающем списке столбца А:А. Ведь согласно этой формуле (2 вариант) у меня будет сортировать только первый столбец, а последующие останутся не сортированными, а значит данные будут не верны.
Или я ошибаюсь.
26.10.2012 00:31:01
Я бы отсортировал первый столбец с уникальными значениями, а потом подтягивал бы к нему остальные данные в строке с помощью функции ВПР
08.06.2015 17:40:22
у меня первый столбец с датами, т.е. при сортировке формулой вылезает несколько одинаковых дат подряд. как им присвоить уникальные значения?
разобрался - спасибо (способ 2)
Максим
26.10.2012 00:30:37
Николай, формула массива работает как надо! НО. Так как, у меня в столбце "С" порядка 15 000 строк, то после каждого ввода новых значений происходит 100% загрузки процессора на 5-10 секунд (отмечу, что ПК имеет достаточно неплохие характеристики). Это достаточно неудобно в том плане, что невозможно быстро вводить значения в столбец "С". Пока что выход только в отключении автоматического вычисления формул. Может быть есть способ как-то "ускорить" работу excel?.
26.10.2012 00:31:18
Формулы массива по определению медленная штука. Попробуйте макросом сортировать - должно быть шустрее.
12.02.2013 19:33:22
Добрый день Николай. А как можно сделать чтобы список был не фиксированный, а с возможностью добавления, а отсортированная таблица автоматически подстраивалась под исходную. Данная вопрос пересекается с темой: "Выпадающий список с добавлением новых элементов" и первым вопросом. Возможно ли это?
09.03.2013 08:36:20
Можно про создании именованного диапазона List использовать функцию СМЕЩ (OFFSET) для создания динамического "резинового" диапазона. А формулу массива для сортировки протянуть с запасом - ниже, чем нужно. Получившиеся в лишних ячейках ошибки #ЧИСЛО можно перехватывать функцией ЕСЛИОШИБКА (IFERROR). Получившийся диапазон вполне можно использовать в качестве источника для выпадающего списка. Допишу-ка я это, пожалуй, в статью.
01.04.2013 15:31:44
Добрый день. А как быть если в исходном списке не только текстовые значения, но и встречаются цифры?
11.04.2013 10:38:35
2й и 3й способы не помогут?
11.04.2013 10:50:14
Добрый день.
2 и 3 способы возвращают случайные текстовые значения из исходного списка -

СашаАлекс
МитяАлекс
ВасяБоря
КоляБоря
ЛенаВася
ЯнаВася
ТаняИван
АлексКоля
ЛенаЛена
БоряЛена
ИванМитя
ЩукарьСаша
5Таня
6Щукарь
7Яна
11.04.2013 21:17:57
Попробуйте поставить для ячеек с числами текстовый формат.
12.04.2013 10:46:31
Форматирование ячеек не помогает. Пробывал прописывать дополнительный столбец с формулой ТЕКСТ(ссылка на исходные данные;0) - тогда третий способ сортировки возвращает первое встреченное число вместо всех остальных чисел.
05.06.2013 21:14:29
Тарас Иванёха, воспользуйтесь "пользовательской сортировкой":
Параметры Excel-Дополнительно-Создать списки для сортировки и заполнения-Изменить списки.
Введите туда Ваш формат сортировки (второй столбец).
На листе: Сортировка-Настраиваемая сортировка-Выберите Ваш список сортировки.
ВСЕ!
Excel 2010. Существует ли такой способ сортировки в ранних версиях, сказать не могу.
31.01.2017 15:56:10
Спасибо!
Как в "Способ 2" можно изменить диапазон?

=СЧЁТЕСЛИ(A:A;"<"&A1)+СЧЁТЕСЛИ($A$1:A1;"="&A1) - если значения начинаются в ячейке A1, какая должна быть формула для диапазона, если значения начинаются в ячейке C24?
20.03.2019 11:16:04
Добрый день! Взял вашу формулу массива но она почему то не корректно работает если столбцы начинаются не с первой строки. как можно это исправить?
Наверх