Сортировка в Excel | Компьютер плюс

Сортировка в Excel применяется для визуализации данных и их упорядочивания, что существенно облегчает восприятие информации, представленной в табличной форме. Необходимость в этой функции возникает при работе с бухгалтерскими ведомостями, списками складских запасов и строительными сметами.

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

Как отсортировать по алфавиту

Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту – фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».

Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.

Как в Еxcel отсортировать по возрастанию значений

Простое распределение по возрастанию в программе Эксель осуществляется аналогичным образом, что и по алфавиту. После выделения нужного столбца в верхней части главного окна на панели задач «Главная» в разделе «редактирование» выбирается кнопка «Сортировка и фильтр», имеющая дополнительное меню. В открывшемся списке необходимо выбрать подходящий вариант. Когда данные в таблице Эксель нужно упорядочить от большего к меньшему, их следует отсортировать по убыванию, в противном случае выбирается пункт «Сортировка по возрастанию».

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

Как сделать сортировку по дате

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

Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:

1. Выделить любую ячейку в столбце с датами, которые требуется упорядочить.
2. Во вкладке «Главная» найти кнопку «Сортировка и фильтр», навести на нее курсор и выбрать одно из предложенных действий в выпадающем списке. Это может быть распределение от новых к старым, когда в начале списка будут находится наиболее поздние даты и привязанные к ним значения, либо наоборот от старых к новым.

Сортировка по цвету ячейки и по шрифту

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

1. Курсором мыши нужно выбрать одну из ячеек с данными в нужном столбце.
2. На вкладке «Главная» в функциональной группе «Редактирование» следует найти кнопку «Сортировка и фильтрация» и выбрать в дополнительном меню команду «Настраиваемая сортировка».
3. В открывшемся окне сначала указать столбец, в котором нужно упорядочить данные, а потом тип сортировки. Это может быть цвет ячейки, цвет шрифта или значок условного форматирования.
4. В зависимости от выбранного вида сортировки в группе порядок необходимо отметить нужный значок либо оттенок заливки или шрифта.
5. Последний параметр, который нужно указать – это последовательность расположения (сверху или снизу).

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

Сортировка в Excel по нескольким столбцам

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

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

Динамическая сортировка таблицы в MS Excel

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

1. Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
2. Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;”

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

Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?

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

Как отменить сортировку в Excel

В Excel предусмотрено три типа фильтров:

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

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
3. Щелкнуть по кнопке Фильтр [Filter] .

1. В верхней строке диапазона возле каждого столбца появились кнопки со стрелочками. В столбце, содержащем ячейку, по которой будет выполняться фильтрация, щелкнуть на кнопку со стрелкой. Раскроется список возможных вариантов фильтрации.

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению — отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету — выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит…, начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации — завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] — одновременное выполнение 2 условий, ИЛИ [Or] — выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

Срезы — это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
2. В группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]) выбрать кнопку Вставить срез [Insert Slicer] .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.

Задание условий фильтрации

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

Сортировка данных в Excel

Добрый день!

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

Для избегания этих проблем в Excel есть очень классная возможность, которая так и называется «Сортировка» и данную процедуру можно произвести 4 способами:

1. Сортировка от А до Я;
2. Сортировка от Я до А;
3. Сортировка по цвету;
4. Сортировка с учётом регистра значений.

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

• При проведении сортировки данных в обязательно убедитесь, что фильтр наложен на все столбики таблицы. В этом случае произойдет динамическая сортировка, которая отсортирует по критерию все значения в таблице и правильно их отобразит. Если же сортировка будет наложена только на один столбик, то таблица будет разорвана и данные потеряны;
• Стоить, очень хорошо запомнить! Отменить сортировку невозможно! Если же вы произвели ошибку при сортировке, тогда у вас есть 2 варианта. Во-первых, закрываете файл без сохранения, но велик риск, потерять и другие произведенные изменения или внесенные данные. Во-вторых, сразу выполнить команду «Отмена последнего действия» на панели быстрого доступа или использовать горячие клавиши CTRL+Z. РЕКОМЕНДУЮ! Если же вам нужно просто вернуть значение, к первоначальным, до момента сортировки, создайте дополнительный столбик и укажите в нём порядковые номера (вернуть старый вариант сортировки возможно снова отсортировав по этому столбику);
• Строки, которые были скрытые, при проведении сортировки не будут перемещаться. Поэтому перед проведением правильной сортировки вам нужно отобразить все скрытые строки;

Сортировку данных в Excel, возможно, произвести при помощи панели управления, нажав на вкладке «Данные», кнопку «Сортировка», станет доступно диалоговое окно, в котором можно осуществить настройку сортировки. Хотя это окно и дублирует практически весь функционал встроенной сортировки в автофильтре, но всё есть небольшое отличие, вам станет доступна еще одна возможность сортировать свои данные с учётом регистра. В случаях, когда произошли изменения в таблице, данные были удалены, добавлены новые, заменены на другие, то есть возможность повторно отсортировать или наложить фильтр на свои данные. На вкладке «Данные» в блоке «Сортировка и фильтр» нужно нажать кнопочку «Повторить» и все ваши новые данные будут правильно отсортированы. На этом у меня всё, как правильно сортировать и на что обратить внимание для точности сортировки я рассказал и показал. Если у вас есть чем дополнить, жду ваши комментарии или замечания.

Ставьте лайк, если статья принесла вам пользу и была интересной!

До новых встреч!

Женщина может сделать миллионером любого мужчину-миллиардера.
Чарли Чаплин

Сортировка данных в Excel

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.

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

1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
2. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

1. Выбрать нужную кнопку: – сортировка по возрастанию или сортировка по убыванию.

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

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

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

1. Выделить одну ячейку из сортируемого массива данных.

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

1. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
2. Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:

1. Имя столбца для сортировки.
2. Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
3. Порядок сортировки (по убыванию или по возрастанию).

Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.

Сортировка по форматированию

Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.

Пошаговый порядок действий:

1. Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
2. На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
3. В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
4. В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
5. Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
6. При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

Сортировка данных в Excel по дате по алфавиту и по возрастанию

В данном примере рассмотрим самые быстрые и эффективные методы сортировки данных в Excel по различным критериям: по возрастанию или убыванию чисел, по датам от старых коновым и от к новым к старым, по алфавиту от А до Я, а так же в обратно-алфавитном порядке.

Сортировка строк по столбцу в Excel

Ниже на рисунке представлена выборка информации из базы данных по сотрудникам фирмы:

Допустим нам необходимо отсортировать таблицу относительно столбца «Премия» по убыванию сумм. Для решения этой задачи воспользуемся самыми простыми (базовыми) инструментами сортировки в Excel.

Перейдите курсором клавиатуры на любую ячейку в столбце G (можно даже на заголовок столбца G1). А Потом выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».

В результате данные упорядочились и организовались в определенную структуру:

Как оказалось, наибольшую премию получил сотрудник «Евгений Пруткий».

Внимание! Список, который будет сортирован не должен содержать объединенных ячеек.

Примечание. Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию».

И встроен в выпадающие меню заголовков таблиц в режиме автофильтра или умной таблицы:

Так же встроен в контекстное меню, которое появляется по нажатию правой кнопкой мышки:

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

Если таблицу необходимо будет привести в изначальный вид, тогда перед каждой сортировкой создавайте столбец нумерации строк. В данном примере – это столбец А (№п/п). Часто необходимо привести таблицу в изначальный вид после выполнения с ней многочисленных и сложных сортирований данных по разным столбцам, критериям или условиям. Тогда достаточно выполнить сортировку по столбцу с номерами строк, чтобы таблица приняла свой первоначальный вид.

Как в Excel сделать сортировку в столбце

Иногда нужно выполнить независимую сортировку только лишь внутри одного столбца таблицы без привязки к другим столбцам или их изменения. Например, столбец B «Имя». Такая задача решается так же просто:

1. Выделите целый столбец B щелкнув левой мышкой по заголовку столбца листа.
2. Выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
3. В появившемся диалоговом окне «Обнаруженные данные в неуказанного диапазона», выберите опцию: «сортировать в пределах указанного выделения». И нажмите на кнопку «Сортировка».

Обратите внимание на то, что значения ячеек в остальных столбцах остались без изменений:

Такой способ необходимо применять только как вспомогательный.

Сортировка по нескольким столбцам Excel

Как настроить сортировку в Excel? Допустим мы хотим сортировать сотрудников по дате рождения (от старых к новым) в соответствии с каждым городом (места рождения). Для этого:

1. Сначала для данного примера лучше сбросить сортировку предыдущих примеров. Для этого отсортируйте таблицу по первому столбцу А (№п/п) – по возрастанию.
2. Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ГЛЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка». Появиться диалоговое окно в котором можно задать несколько условий для сортировки таблицы.
3. В первом выпадающем списке выбираем заголовок столбца по которому будут сортированы данные в первую очередь. В данном примере – это «Место рождения». Второй выпадающий список оставим без изменений – «Значение», а в третьем указываем тип сортирования «От А до Я».
4. Нажмите на кнопку «Добавить уровень» и в следующем условии указываем новые критерии. 1 – «Дата рождения», 2 – «Значение», 3 – «От старых к новым». И нажмите на кнопку ОК.

Таблица приобрела следующую структуру:

Сначала ее значения отсортированы относительно названий городов в алфавитном порядке. Далее относительного каждого города распределены даты рождения от старых к новым.

Если мы работаем только с фрагментом таблицы где нет заголовков столбцов, а только лишь диапазон ячеек с текущими значениями, как показано ниже на рисунке:

Тогда в диалоговом окне инструмента «Настраиваемая сортировка» следует отключить опцию «Мои данные имеют заголовки столбцов». В результате чего в качестве критериев для каждого первого выпадающего списка секции «Столбец» в каждом уровне, будут использованы заголовки столбцов рабочего листа Excel (Столбец А, Столбец B, C и т.д.). А при применении всех параметров в сортировку будут включены все строки и ячейки исходного диапазона (фрагмента таблицы), что является правильным результатом в данном случаи.

Примечание. Кнопка вызова инструмента для настраиваемой сортировки так же доступна на закладке «ДАННЫЕ»-«Сортировка».

Таблицы данных, которые мы получаем от разных сотрудников в большинстве случаев не упорядочены и работать с ними без организации – сложно. Возникает необходимость отсортировать: текстовые значение по алфавиту, числовые – по возрастанию, а даты от старых до новых. Сортировать вручную все столбцы по отдельности – это не наилучшее решение для данной задачи. Особенно если столбцов более тысячи. Поэтому лучше воспользоваться специальным встроенным инструментом Excel – настраиваемой сортировкой. Данный инструмент автоматическое сортирует данные одновременно по нескольким столбцам. Он настолько эластичен, что его можно применить практически для любой сортировки с любым уровнем сложности.