Сортировка в excel разными способами

В статье пошагово рассмотрим, как делается сортировка в excel совершенно любых данных (числовых, текстовых, временных) разными способами.

Начнем с вопроса:

Как открыть утилиту «Сортировка» в Excel

Есть несколько способов выполнить задуманную операцию:

По клику правой кнопкой мыши

Выделите мышкой столбец в excel, с которым планируете работать:

  • Кликните правой клавишей мыши. В появившемся окне ищите «Сортировка».
  • В следующем окне выберите ее вариант.окно с инструментом по клику правой кнопкой мыши

Из верхнего меню

В верхнем меню на вкладке «Главная» найдите блок «Редактирование» и кликните по кнопке «Сортировка и фильтр. Выберите действие.

Или на вкладке «Данные».

вкладки Главная и Данные с инструментами сортировки

Сортировка в excel с помощью инструмента «Фильтр»

Также сортировку можно выполнять с помощью инструмента «Фильтр». Его легко найти на:

  • «Главная» > «Сортировка и фильтр»,
  • «Данные» > Фильтр.
  • И по щелчку правой кнопкой мыши слева вверху таблицы.

С помощью «Фильтра» можно перемещать и по алфавиту, и по цвету. Также есть «Пользовательская сортировка», она же — настраиваемая.

Как сортировать в excel данные в столбцах

Перемещение внутри одного столбца — самое простое.

Инструменты него находятся: как на вкладке «Главная», так и на вкладке «Данные», а также открываются щелчком правой клавиши мыши.

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

Открываете:

  • на вкладке «Главная» > «Сортировка и фильтр» > «Настраиваемая сортировка»;
  • или на вкладке «Данные» кликаете иконку «Сортировка».вкладка Данные иконка Сортировка

В обоих случаях открывается диалоговое окно, где указываете по какому столбцу сортировать (например, «А») и в 2 следующих окошках — каким образом это сделать. Жмете «Ок» — данные всех выделенных колонок последуют за данными из «А» по выбранным условиям.

Группировка по цвету ячейки или шрифта станет доступна, если в нашем основном столбце «А» применена заливка для каких-то ячеек или использован другой цвет шрифта.

Когда необходимо учесть регистр, следует щелкнуть по кнопке «Параметры» и проставить галочку в чек-боксе «Учитывать регистр».

По алфавиту

Группировка по алфавиту — это вариант от «А до Я (по возрастанию)» или от «Я до А (по убыванию)». Выделяете столбцы, которые требуется изменить и кликаете на инструмент.

Важно: если сортируете несколько — первым выделяйте тот, где нужна последовательность. Данные остальных колонок последуют за данными из главного — выделенного первым.

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

Если вы вставляли значения из каких-то других программ, проверьте, чтобы в начале строк не было пробелов (или других невидимых символов) и обязательно удалите их для получения корректного результата.

По возрастанию или убыванию

Для числовых значений пользуемся тем же инструментом:

  • возрастание значений — «Сортировка от А до Я»
  • убывание — «от Я до А».

Группировка по дате и времени

Та же технология применяется по отношению к датам и времени:

  • «от А до Я» — от старых к новым (датам) и
  • «от Я до А» — от новых к старым.текст окна для работы с датами

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

Сортировка словосочетаний по количеству слов в столбце excel

И вот интересная формула для выборки текстовых данных по количеству слов в словосочетании.

Разберем на примере.

В столбце «А» у нас фразы: однословные, двухсловные, с 3 словами и т. д. Нам их нужно рассортировать по количеству слов.

Выделяем ячейку «В1» и в строку формул вставляем:

=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A1))=0;0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))+1)

Кликаем на галочку (1). В первой строке слова посчитались. Далее (2) за маркер в правом нижнем углу ячейки В1 тянем мышкой вниз, до конца нашего списка. Количество слов посчитано во всем списке. Следующим шагом от столбца «В» сортируем по возрастанию или наоборот.

Обратите внимание: чтобы фразы расположились по количеству слов — первым выделяете столбец «В», а потом уже «А».

формула для подсчета количества слов фразы

Далее можно удалить формулу из ячейки. Обычным способом это не получится сделать — вместе с формулой удалятся и значения. Поэтому. Выбираем столбец или весь диапазон значений, полученный с помощью формул. Копируем его и вставляем снова с помощью правой клавиши мыши и «Вставка» > «Значения».

вставить значения без формулы

Группировка в excel по столбцам

Возможна сортировка столбцами, по какой-то выбранной строке. Для этого выделяете столбцы, которые нужно поменять местами в соответствии с предъявляемым условием.

В инструментах вызываете окно настраиваемой сортировки. В «Параметрах» отмечаете точкой (галочкой) «Столбцы диапазона» > «Ок». Выбираете нужную из 60 строк, по которой будете менять расположение столбцов, и последующие условия. «Ок» > столбцы поменялись между собой.

сортировка столбцаминастройка перемещения столбцов

Как в excel выбрать фразы с определенным словом и сгруппировать их

Также в колонке можно выбрать фразы с повторяющимся словом и сгруппировать их в один список.

Для этого на вкладке «Главная»:

  • выделяете колонку, из которой будете выбирать фразы с нужным повторяющимся словом;
  • переходите в «Условное форматирование» > «Правила выделения ячеек» > «Текст содержит».

Условное форматирование Правила выделения ячеек Текст содержит

В открывшуюся форму вводите искомое слово.

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

Форма для ввода искомого слова

«Ок» и фразы с искомым словом окрасились цветом.

фразы с искомым словом выделены заливкойДалее группируем их. Из верхнего меню.

  1. Шаг 1

    Шаг 2

    Шаг 3

    Шаг 4

    Данные > Фильтр > клетка со стрелкой в первой строке сортируемой колонки > Вариант 1) по цвету

    Вариант 2) пользовательская

    Данные > Сортировка > По цвету ячейки
    Главная > Сортировка и фильтр > Вариант 1. Настраиваемая

    Вариант 2. Через окошко со стрелкой в верхней строке сортируемого столбца

  2. Или щелкаем правой клавишей мыши в сортируемом столбце.

Открывается окно, выбираете любой из 2 способов:

  • Либо «Сначала ячейки с выделенным цветом».
  • Либо «Настраиваемая сортировка».

Если необходимо сгруппировать выбранные фразы вместе с относящимися к ним данными из других колонок.

То выделяете мышкой оба столбца (или больше). Кликаете на любую из 2 кнопок «Фильтр» в верхнем меню. Далее кликаете по квадратику со стрелкой в нашей базовой колонке,

кнопка управления фильтрацией

выбираете «Сортировка по цвету». Результат → значения в последующих столбцах переместились вслед за «родительскими».

Если хотите перегруппировать подсвеченные фразы в порядке частотности. Например, по убыванию. Тогда основным столбцом выбираете «В» и перемещаете их через кнопку «Фильтр» или «Настраиваемой сортировкой».

фразы с искомым словом выстроились в порядке убывания частотности запросов

Повторный щелкой мышью по кнопке «Фильтр» отменяет его и убирает клетку со стрелкой.
Убрать заливку цветом можно с помощью: «Условное форматирование» > «Удалить правила» > «Удалить правила из выделенных ячеек».

Многоуровневая сортировка в excel

Многоуровневая предполагает одновременное использование нескольких фильтров. Возможна как внутри одного столбца, так и по нескольким.

По шагам.

На вкладке «Главная» переходите в блок инструментов «Редактирование». Кликаете мышью на «Сортировка и фильтр» > «Настраиваемая сортировка». Далее кликаете «Параметры». Ставите точку в «Строки диапазона» и «Ок». Переходите к работе с уровнями.

Внутри одного столбца

Допустим в сортируемой колонке строки отмечены разным цветом: желтым, зеленым, красным.

строки отмечены заливкой разного цвета

Нам нужно отсортировать их: сначала все красные, потом все зеленые, и все желтые.

Настраиваем первый уровень:

Копируем уровень и в последнем столбце копии изменяем красный цвет на следующий — зеленый.

Еще раз копируем уровень. И в последнем окошке выбираем желтый цвет.

добавление уровней для группировки по цвету

«Ок» и все строки выстроились в заданном порядке.

По нескольким столбцам

Важно. Сортируемые колонки должны быть выделены в таблице.

1-й уровень — это столбец «А». Выбираете из 4 предложенных вариантов и порядок: например, по алфавиту. Возьмем от «А до Я».

Жмете кнопку «Добавить уровень» — это столбец «В». Выбираете здесь нужный вариант. Например, по убыванию.

Если нужно, то добавляете 3 уровень и последующие. Выставляете для них параметры.

Дальше «Ок».

Обратите внимание. Что вначале идет сортировка по первому уровню, у нас это столбец «А»; фразы выстраиваются строго по алфавиту (причем, в алфавитной последовательности выстраиваются все буквы в словах и сами слова).

Далее. Внутри полученного блока в заданной последовательности сортируется 2-й уровень (столбец). И последним — 3-й уровень.

выставление параметров для многоуровневой по столбцам
В «А» выбраны ячейки, окрашенные зеленой заливкой. Для «В» по возрастанию

На следующем рисунке виден результат: сверху показаны ячейки нужного цвета по возрастанию их значений от меньшего к большему:

результат многоуровневой группировки

Сортировка в excel по списку

Сортировку по списку можно найти в форме «Настраиваемая…»:

настраиваемый список

С помощью уже встроенных списков доступна сортировка по дням недели или месяцам. Но можно создать собственную, по любым другим параметрам. Например: короткий, длинный, средний или S, M, L, XL).

свой настраиваемый список

«Настраиваемая сортировка» > окно «Порядок» > «Настраиваемый список». В следующем окне выбираете «Новый список» и справа вводите критерии сортировки через enter, в столбик. Жмете «Добавить» и выполняете операцию.

Если критериев много перенесите в excel-файл столбцом. Расположите в нужном порядке от верхней ячейки к нижней и выделите. Из верхнего меню перейдите Файл > Параметры > Дополнительно. Прокрутите вниз до раздела «Общие» >  «Изменить списки».

путь к "изменить списки"

Кликаете, открывается следующее окно — «Импорт». Здесь вы увидите обозначения выделенных вами ячеек с новым списком для сортировки. Жмите «Импорт».

импорт новых списков

Как убрать сортировку в excel

Самые простые способы отменить неудавшуюся операцию, это воспользоваться инструментом отмены последних действий в шапке программы или горячими клавишами Ctrl+Z. Нажимая столько раз, сколько действий хотите отменить.

Заключение

  1. Если о чем-то по теме не рассказано, или изложено не ясно; и у вас остались вопросы — задавайте их в комментариях. Так авторы увидят слабые места материала и постараются их исправить.
  2. Дополнительно о сортировке данных в excel на видео (8 мин.):
Оцените статью
( 5 оценок, среднее 5 из 5 )
Записки айтишника для каждого
Добавить комментарий

Нажимая кнопку "Отправить комментарий", я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.

  1. Павел Лейкин

    Отличная статья! Сразу нашёл для себя несколько способов для сортировки. Хорошо, что имеются такие авторы, которые трудятся над созданием такого контента. Добавляю сайт в закладки, надеюсь и в дальнейшем буду получать на данном сайте много нужной мне информации. Удачи автору в работе.

    Ответить
  2. Сергей

    Отлично. Спасибо. А то я с этим экселем вообще ну ни как. А тут все понятно что да как с этими таблицами делать

    Ответить
  3. Алла

    Большое спасибо за статью по Excel. Если в Word более менее разбираюсь, то Excel надо еще изучать и изучать. И как своевременно я нашла эту информацию.
    Надеюсь, что этот сайт станет моим помощником в решении многих проблем.

    Ответить
  4. олег

    Спасибо за информацию. Было полезно для меня!

    Ответить