Содержание:
Если вам нужно отсортировать список, то к вашим услугам множество способов, самый простой из которых — кнопки сортировки на вкладке или в меню. Данные (Данные — Сортировка). Однако бывают ситуации, когда сортировку списка нужно производить автоматически, т.е. по формулам. Это может потребоваться, например, при формировании данных для выпадающего списка, при расчете данных для диаграмм и т.п. Как отсортировать список по формуле на лету?
Способ 1. Числовые данные
Если список содержит только числовую информацию, то его сортировку можно легко выполнить с помощью функций НАИМЕНЕЕ (НЕБОЛЬШОЙ) и ЛИНИЯ (РЯД):
Функция НАИМЕНЕЕ (НЕБОЛЬШОЙ) извлекает из массива (столбец A) n-й наименьший элемент подряд. Те. SMALL(A:A;1) — наименьшее число в столбце, SMALL(A:A;2) — второе наименьшее число и т. д.
Функция ЛИНИЯ (РЯД) возвращает номер строки для указанной ячейки, т.е. ROW(A1)=1, ROW(A2)=2 и т.д. В данном случае он используется просто как генератор последовательности чисел n=1,2,3… для наш отсортированный список. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.
Способ 2. Список текстов и обычные формулы
Если список содержит не числа, а текст, то функция МАЛЕНЬКИЙ уже не будет работать, поэтому придется идти другим, чуть более длинным путем.
Для начала добавим столбец услуги с формулой, в которой порядковый номер каждого имени в будущем отсортированном списке будет рассчитываться с помощью функции COUNTIF (СЧЁТЕСЛИ):
В английской версии это будет:
=СЧЁТЕСЛИ(A:A,»<"&A1)+СЧЁТЕСЛИ($A$1:A1,"="&A1)
Первое слагаемое — это функция подсчета количества ячеек, которые меньше текущей. Второе — это подстраховка на случай, если какое-либо имя встречается более одного раза. Тогда у них будет не прежняя, а последовательно возрастающая численность.
Теперь полученные числа необходимо расположить последовательно по возрастанию. Для этого вы можете использовать функцию НАИМЕНЕЕ (НЕБОЛЬШОЙ) из первого способа:
Ну и наконец, осталось просто вытащить имена из списка по их номерам. Для этого можно использовать следующую формулу:
Функция БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ) ищет в столбце B нужный серийный номер (1, 2, 3 и т.д.) и по сути возвращает номер строки, где находится этот номер. Функция ИНДЕКС (ПОКАЗАТЕЛЬ) вытаскивает из столбца А имя по этому номеру строки.
Метод 3: формула массива
Этот метод, по сути, представляет собой тот же алгоритм размещения, что и в Методе-2, но реализован с помощью формулы массива. Для упрощения формулы диапазону ячеек C1:C10 было присвоено название Список (выделите ячейки, нажмите Ctrl + F3 и кнопка Создавай):
В ячейку E1 скопируйте нашу формулу:
=ИНДЕКС(Список; СООТВЕТСТВИЕ(МАЛЕНЬКИЙ(СЧЁТЕСЛИ(Список; “<"&Список); СТРОКА(1:1)); СЧЁТЕСЛИ(Список; "<"&Список); 0))
Или в английской версии:
=ИНДЕКС(Список, СООТВЕТСТВИЕ(МАЛЕНЬКИЙ(СЧЕТЕСЛИ(Список, «<»&Список), СТРОКА(1:1)), СЧЁТЕСЛИ(Список, «<»&Список), 0))
и нажмите Shift + Ctrl + Enterчтобы ввести его как формулу массива. Затем полученную формулу можно скопировать на всю длину списка.
Если вы хотите, чтобы формула учитывала не фиксированный диапазон, а имела возможность корректироваться при добавлении новых элементов в список, то вам нужно будет немного изменить стратегию.
Во-первых, диапазон списка необходимо будет установить динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все доступные значения, независимо от их количества. Нажмите Alt + F3 или откройте вкладку Формулы – Менеджер имен (Формулы — Менеджер имен), создайте новое имя и в поле Ссылка (Ссылка) введите следующую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=СМЕЩ(C1,0,0,ШОТЦ(C1:C1000),1)
Во-вторых, приведенную выше формулу массива нужно будет растянуть с запасом вниз – с расчетом на ввод дополнительных данных в будущем. В этом случае формула массива начнет выдавать ошибку #ЧИСЛО в еще не заполненных ячейках. Чтобы перехватить его, вы можете использовать функцию IFERROR, который нужно добавить «вокруг» нашей формулы массива:
=ЕСЛИОШИБКА(ИНДЕКС(Список; СООТВЕТСТВИЕ(МАЛЕНЬКИЙ(СЧЁТЕСЛИ(Список; «<"&Список); СТРОКА(1:1)); СЧЁТЕСЛИ(Список; "<"&Список); 0));»»)
=ЕСЛИОШИБКА(NDEX(Список, СООТВЕТСТВИЕ(МАЛЕНЬКИЙ(СЧЁТЕСЛИ(Список, «<"&Список), СТРОКА(1:1)), СЧЁТЕСЛИ(Список, "<"&Список), 0));"")
Он улавливает ошибку #NUMBER и вместо этого выводит void (пустые кавычки).
:
- Сортировать диапазон по цвету
- Что такое формулы массива и зачем они нужны
- Сортировка SORT и динамические массивы в новом Office 365