Сортировка формулой
Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (Data - Sort). Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же "на лету" сортировать список формулой?
Способ 1. Числовые данные
Если список содержит только числовую информацию, то его сортировку можно легко сделать с помощью функций НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW):
Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) - это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) - второе по счету наименьшее и т.д.
Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2 и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.
Способ 2. Текстовый список и обычные формулы
Если в списке не числа, а текст, то функция НАИМЕНЬШИЙ (SMALL) уже не сработает, поэтому придется пойти другим, чуть более длинным, путем.
Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF):
В английской версии это будет:
=COUNTIF(A:A,"<"&A1)+COUNTIF($A$1:A1,"="&A1)
Первое слагаемое - это функция подсчета количества ячеек, которые меньше текущей. Второе - подстраховка на случай, если какое-либо имя встречается больше одного раза. Тогда они будут иметь не одинаковые, а последовательно возрастающие номера.
Теперь полученные номера надо расставить последовательно по возрастанию. Для этого можно использовать функцию НАИМЕНЬШИЙ (SMALL) из первого способа:
Ну, и наконец, осталось просто вытащить из списка имена по их номерам. Для этого можно использовать такую формулу:
Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.
Способ 3. Формула массива
Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):
В ячейку Е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));"")
Она перехватывает ошибку #ЧИСЛО и выводит вместо нее пустоту (пустые кавычки).
Ссылки по теме:
- Сортировка диапазона по цвету
- Что такое формулы массива и зачем они нужны
- Сортировка функцией СОРТ и динамические массивы в новом Office 365
Или я ошибаюсь.
разобрался - спасибо (способ 2)
2 и 3 способы возвращают случайные текстовые значения из исходного списка -
Параметры Excel-Дополнительно-Создать списки для сортировки и заполнения-Изменить списки.
Введите туда Ваш формат сортировки (второй столбец).
На листе: Сортировка-Настраиваемая сортировка-Выберите Ваш список сортировки.
ВСЕ!
Excel 2010. Существует ли такой способ сортировки в ранних версиях, сказать не могу.
Как в "Способ 2" можно изменить диапазон?
=СЧЁТЕСЛИ(A:A;"<"&A1)+СЧЁТЕСЛИ($A$1:A1;"="&A1) - если значения начинаются в ячейке A1, какая должна быть формула для диапазона, если значения начинаются в ячейке C24?
=ЕСЛИОШИБКА(ИНДЕКС(List;5+ПОИСКПОЗ(...
Если это не поможет, то придется разбираться в том как именно Вы задаете именованный диапазон List.
function onEdit(event){ var sheet = event.source.getActiveSheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 8; var tableRange = "A2:I900"; if(editedCell.getColumn() == columnToSortBy){ var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy } ); } }Может кто-нибудь знает как заставить этот макрос сортировать по ещё одному столбцу?