Отсортировать данные в Excel быстрее всего так: выделите любую ячейку столбца и нажмите «Данные → Сортировка от А до Я» (или от Я до А), либо включите фильтр (Ctrl+Shift+L) и выберите порядок в меню заголовка. Сортировку по нескольким столбцам задают в окне «Данные → Сортировка» кнопкой «Добавить уровень». В Excel 365 отсортированную копию строит =СОРТ(A2:C100;2;-1), а сортировать по окончаниям, длине текста или числу слов в пару кликов помогают процедуры !SEMTools.
Сортировка данных - одна из самых необходимых процедур при работе с ними. Она существенным образом упрощает аналитику, предоставляя возможность
- взглянуть на наибольшие и наименьшие значения числовых диапазонов
- упорядочить текстовые данные по алфавиту для быстроты поиска нужных в них строк
- расположить события по возрастанию или убыванию даты/времени, чтобы понять их последовательность, и какое из них было самым ранним или поздним
- выбирать из больших диапазонов только главную, значимую их часть
Excel предоставляет в этой связи множество возможностей и инструментов. Однако иногда и их может не хватать, поэтому мной были разработаны несколько дополнительных процедур сортировки в моей надстройке для Excel !SEMTools. Но о них в самом конце, а сначала рассмотрим штатные возможности.
Способы сортировки данных в Excel
Я знаю и использую три способа сортировки обычных табличных данных на листе (не сводных). Это инструмент "Фильтр", контекстное меню и процедура на панели "Данные". Из всех мне лично больше всего нравится "Фильтр".
Сортировка "Фильтром"
Чтобы использовать инструмент фильтр для сортировки данных, потребуется
- создать фильтр на уровне заголовков данных
- использовать контекстные меню на уровне заголовков, для нужной сортировки.
Инструмент позволяет сортировать данные большинством основных способов:
- по возрастанию и убыванию
- по цвету заливки и цвету шрифта
- по значку ячейки
Контекстное меню
Контекстное меню вызывается правой кнопкой мыши после выделения диапазона и позволяет быстро сортировать его прямо на месте, не добавляя заголовков.
Интересен тот факт, что сортировка будет зависеть от того, с какой стороны вы начали выделять диапазон - справа налево или слева направо. Зная эту особенность, можно быстро сортировать диапазон из нескольких столбцов по крайнему правому или крайнему левому.
Процедура на панели «Данные»
Этот способ является наиболее универсальным и гибким инструментом сортировки в Excel. Он позволяет задать точные параметры сортировки, использовать несколько уровней, а также управлять направлением сортировки и дополнительными условиями.
Чтобы открыть расширенное окно сортировки, необходимо:
- выделить диапазон данных или любую ячейку внутри таблицы;
- перейти на вкладку «Данные» на ленте Excel;
- нажать кнопку «Сортировка».
После этого откроется диалоговое окно, в котором можно задать все параметры сортировки.
Основные элементы окна сортировки:
- Столбец — указывает поле, по которому будет выполняться сортировка;
- Сортировать по — определяет тип значения: обычные значения, цвет ячейки, цвет шрифта или значок условного форматирования;
- Порядок — задаёт направление сортировки.
Для обычных значений доступны следующие варианты порядка:
- от А до Я или от Я до А для текстовых данных;
- от меньшего к большему или от большего к меньшему для чисел;
- от старых к новым или от новых к старым для дат и времени.
Одной из наиболее полезных возможностей этого окна является многоуровневая сортировка. Она позволяет сортировать таблицу сразу по нескольким столбцам.
Например, можно сначала отсортировать данные по региону, затем внутри каждого региона — по категории товара, а уже внутри категории — по сумме продаж.
Для добавления второго или третьего уровня используется кнопка «Добавить уровень». Excel будет применять уровни сортировки сверху вниз, начиная с первого.
Практический пример:
- первый уровень — сортировка по столбцу Регион от А до Я;
- второй уровень — сортировка по столбцу Менеджер от А до Я;
- третий уровень — сортировка по столбцу Продажи от большего к меньшему.
В результате Excel сначала сгруппирует записи по регионам, затем внутри каждого региона расположит строки по менеджерам, а уже внутри каждой группы отсортирует данные по объёму продаж.
Ещё одна полезная возможность — сортировка по цвету. Если в таблице используются цветные заливки или условное форматирование, можно задать порядок, в котором цвета будут располагаться после сортировки.
Например, можно сначала вывести строки с красной заливкой (критические значения), затем жёлтые (предупреждения), а после них — все остальные.
Также Excel позволяет сортировать данные по значкам условного форматирования. Это удобно, если используются индикаторы статуса, стрелки роста или другие визуальные элементы.
Ещё одна настройка, о которой многие пользователи не знают — направление сортировки. По умолчанию Excel сортирует строки (сверху вниз), но можно изменить направление и выполнять сортировку по строкам, то есть менять порядок столбцов.
Для этого необходимо:
- открыть окно сортировки;
- нажать кнопку «Параметры»;
- выбрать вариант «Слева направо».
После этого в поле выбора сортировки будут отображаться строки вместо столбцов.
Такой режим бывает полезен, например, при анализе временных рядов, когда необходимо изменить порядок месяцев или кварталов.
Важно помнить ещё одну особенность: если таблица имеет строку заголовков, обязательно нужно включить параметр «Мои данные содержат заголовки». В противном случае Excel может включить заголовки в процесс сортировки, что приведёт к неправильному результату.
В целом расширенное окно сортировки является наиболее мощным стандартным инструментом Excel. Оно позволяет решать практически любые задачи, связанные с упорядочиванием данных.
Сортировка формулами в Excel 365
В Excel 365 есть функции СОРТ и СОРТПО. Они возвращают отсортированную копию данных и сами обновляют её при изменении исходника. Отсортируем таблицу по второму столбцу по убыванию:
=СОРТ(A2:C100;2;-1)
СОРТПО умеет сортировать по вычисляемому значению, которого в таблице нет вовсе. Например, по количеству символов в ячейке, от коротких к длинным:
=СОРТПО(A2:A100;ДЛСТР(A2:A100);1)
| A | B | C | |
|---|---|---|---|
| 1 | Запрос | По длине (СОРТПО) | Длина > 12 (ФИЛЬТР) |
| 2 | купить диван угловой | диван=СОРТПО(A2:A5;ДЛСТР(A2:A5);1) | купить диван угловой=ФИЛЬТР(A2:A5;ДЛСТР(A2:A5)>12) |
| 3 | диван | диван-кровать | диван недорого |
| 4 | диван недорого | диван недорого | диван-кровать |
| 5 | диван-кровать | купить диван угловой |
СОРТПО упорядочила запросы от коротких к длинным, ФИЛЬТР оставил только те, что длиннее 12 символов. Обе формулы сами «разливаются» вниз и обновляются при изменении исходных данных.
В английском Excel: =SORTBY(A2:A5,LEN(A2:A5),1)
Фильтр по количеству символов
В автофильтре нет условия по длине текста. Добавьте вспомогательный столбец с формулой =ДЛСТР(A2) и используйте «Числовые фильтры → больше…», или в Excel 365 выведите подходящие строки формулой =ФИЛЬТР(A2:A100;ДЛСТР(A2:A100)>12).
В более старых версиях для того же понадобится дополнительный столбец с формулой =ДЛСТР(A2), сортировка по нему и его удаление.
Сортировка данных с помощью !SEMTools
Поскольку мне в моей практике часто приходилось сортировать данные именно указанными ниже способами, я решил упростить себе жизнь и добавить эти процедуры. Ведь в пару кликов всегда удобнее, чем создавать дополнительные столбцы и прописывать формулы.
Итак, перечислю эти возможности.
- Сортировка диапазона по окончаниям
- Сортировка по количеству символов
- Сортировка по количеству слов
Функции сортировки включают в себя простую сортировку и сортировку данных диапазона по окончаниям. Простая сортировка данных помогает упорядочить текстовые и цифровые данные. Для текста доступна сортировка по буквам от А до Я, для числовых данных (числа, даты) - от меньшего к большему, в случае с датами - от старых дат к новым.
Сортировка диапазона по окончаниям позволяет отсортировать ячейки диапазона по последним буквам в алфавитном порядке.
Подробнее рассказал в видео:
Почему сортировка работает неправильно
- Отсортировался только один столбец, данные «перепутались». На предупреждение Excel выбрали «сортировать в пределах указанного выделения». Отмените (Ctrl+Z) и выберите «автоматически расширить выделенный диапазон».
- Числа сортируются как текст: 1, 10, 2. Числа сохранены как текст — преобразуйте их в числа.
- Заголовок оказался внутри таблицы. Не включена галочка «Мои данные содержат заголовки».
- Даты сортируются не по порядку. Часть дат — текст. Проверьте выравнивание: настоящие даты выравниваются по правому краю.
- Пустые строки разбили таблицу. Сортировка работает до первой пустой строки — выделите весь диапазон вручную или удалите пустые строки.
Сортировка в Excel — частые вопросы
Как отсортировать данные в Excel?
Выделите ячейку столбца и нажмите «Данные → Сортировка от А до Я» или используйте фильтр в заголовке. Для нескольких условий — окно «Данные → Сортировка».
Как сортировать по нескольким столбцам?
В окне «Данные → Сортировка» кнопкой «Добавить уровень» — уровни применяются сверху вниз.
Как отсортировать по длине текста?
В Excel 365 — =СОРТПО(A2:A100;ДЛСТР(A2:A100);1), в старых версиях — через вспомогательный столбец с ДЛСТР.
Как отсортировать формулой, чтобы результат обновлялся?
Функцией =СОРТ(A2:C100;2;-1) — копия таблицы, отсортированная по второму столбцу по убыванию.
Отсортировать ячейки по окончаниям, длине текста или количеству слов — без вспомогательных столбцов и формул — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как сортировать данные в Excel
| Задача | Способ |
|---|---|
| Быстро по одному столбцу | Данные → Сортировка от А до Я / фильтр |
| По нескольким столбцам, по цвету | Окно «Данные → Сортировка» |
| Отсортированная копия с автообновлением | =СОРТ(…) или =СОРТПО(…) (Excel 365) |
| По окончаниям, длине, числу слов | Процедуры !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: отсортировать слова внутри ячейки и быстрая сводная таблица.