Как сделать сортировку данных в эксель. Как сделать сортировку в Excel по возрастанию и по убыванию Сортировка по цифрам excel

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

Видео по использованию сортировки Excel

Что можно сортировать?

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

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

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

Сортировку в Экселе условно можно разделить на простую и сложную. Простой считается сортировка по возрастанию или по убыванию.

Существует 2 основных типа сортировки — по возрастанию и по убыванию

Итак, перед тем как начать, необходимо открыть Excel и заполнить какой-то информацией. К примеру, можно заполнить 10 ячеек цифрами от 1 до 10. Теперь нужно выделить весь столбец (в данном случае — все 10 ячеек) и выбрать в панели меню пункты «Данные — Сортировка». Откроется новое окно, в котором надо указать, как отсортировать информацию, по возрастанию или по убыванию. Можно, например, выбрать пункт «по убыванию» и нажать кнопку «ОК». Теперь числа будут идти от 10 и до 1. Можно снова открыть окно сортировки и выбрать «по возрастанию» — числа будут идти от 1 до 10. Также эта процедура может выполняться одновременно по 3 колонкам. Хотя такую сортировку лучше выполнять .

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

Итак, чтобы включить автофильтр, необходимо выделить весь лист и в панели меню выбрать пункты «Данные — Фильтр — Автофильтр». В ячейках с названиями столбцов (наименование, количество и пр.) должен появиться небольшой значок, при нажатии на который откроется выпадающий список. Наличие такой стрелочки означает, что автофильтр включен правильно. В этом списке можно отсортировать данные по убыванию или по возрастанию, указать, чтобы в таблице отображались только первые 10 наименований (в данном примере эта опция не сработает) или чтобы показывало какой-то определенный товар (например, ботинки мужские). Также можно выбрать пункт «Условие» и указать, например, чтобы программа отобразила все товары, количество которых меньше или равно 10.

Если стрелочка автофильтра окрашена в синий цвет, это означает, что данная колонка уже была отсортирована.

Хитрости сортировки

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

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

Отсортировать данные совсем не трудно. Зато в результате можно получить удобную таблицу или отчет для быстрого просмотра необходимой информации и принятия решения.

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

Упорядочивание чисел

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

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

Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».

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

Выбираем «Расширить», так как мы заинтересованы в упорядочивании полных записей таблицы, и нажимаем «Сортировка». Записи выстроены в порядке возрастания стоимости проживания.

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

Для того чтобы числа были отсортированы верно, обратите внимание на корректность формата данных в столбце. Если значения внесены в таблицу как текст, а не число, выстраивание пройдет не в ожидаемом порядке. В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.

Упорядочивание текста

В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. Произведем эту команду в колонке уже известной нам таблицы цен на комнаты в отеле «Питание». Выделяем столбец кликом по его заголовку и открываем панель инструментов «Редактирование» вкладки «Главная». Теперь в выпадающем меню вместо команд сортировки по возрастанию и убыванию расположены команды сортировки от А до Я и наоборот.

Выполняем построение ячеек от А до Я, опять же указывая расширение диапазона операции.

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

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

Упорядочивание дат

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

Для дат действует то же правило, что и для чисел: чтобы упорядочивание прошло корректно, убедитесь в том, что сортируемая информация представлена именно в формате даты, а не в текстовом.

Настраиваемые списки

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

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

В поле «Порядок» выбираем элемент «Настраиваемый список». Из перечня выбираем дни недели и нажимаем ОК.

Упорядочивание по цвету и значкам

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

Для этого заходим в настраиваемую сортировку. Кроме описанного выше способа, можно воспользоваться меню панели «Редактирование» вкладки «Главная». Выбираем из списка команд «Сортировка и фильтр» элемент «Настраиваемая сортировка».

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

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

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

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

Первыми идут цифры от меньшего к большему;
Затем идут специальные символы ($, %, * и т.д.);
За ними следуют знаки препинания;
Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).

Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:

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

Рассмотрим все пункты подробнее.

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

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

Исходное состояние:

Установка порядка первого столбца таблицы при выборе первого варианта:

Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.

Те же самые действия, но при выборе второго варианта:

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

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

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

Мы специально будем использовать такие данные, чтобы наглядно отобразить, как Excel осуществляет сортировку.

Выделите диапазон нашей таблицы и кликните по значку "Сорировка". Появится следующее окно:

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

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

Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.

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

Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».

Рассмотрим получившийся результат:

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

Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.

Также в окне сортировки можно задавать различные параметры.

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

Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):

Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:

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

Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере. Если Вы работаете в другой версии приложения, никаких затруднений возникнуть не должно, поскольку сортировка в Excel 2007 и Excel 2013 работает практически так же. Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…

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

Сортировка данных в Excel по заголовкам строк и столбцов

Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.

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

Нам нужно отсортировать этот список фотокамер по наиболее важным для нас параметрам. Для примера первым делом выполним сортировку по названию модели:

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

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

Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4 , в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:

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

Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?

Сортировка в произвольном порядке (по настраиваемому списку)

Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.

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

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.

Сортировка данных по собственному настраиваемому списку

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

Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК .

Вот так выглядит наш список домашних дел, упорядоченных по важности:

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

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

Чисел

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

Теперь у нас данные в С размещены в порядке убывания.

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

По алфавиту

Она делается по тому же принципу, как было описано выше. Выделяем нужный диапазон, и нажимаем кнопочку «Сортировка и фильтр» . В выпадающем меню пункты изменились. Выберите или от «А до Я» , или от «Я до А» .

Список имен в примере отсортирован по алфавиту.

По дате

Чтобы отсортировать даты в Эксель, сначала обратите внимание, какой формат установлен для тех ячеек, в которых они записаны. Выделите их и на вкладке «Главная» посмотрите на группу «Число» . Лучше всего подойдет или формат «Дата» , краткий или длинный, или «(все форматы)» – дата может быть записана различными способами: ДД.ММ.ГГГГ, ДД.МММ, МММ.ГГ.

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

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

По цвету ячейки или текста

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

Выделяем весь диапазон, кликаем на кнопочку «Сортировка и фильтр» и выбираем из меню «Настраиваемая…» .

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

Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .

Наш столбец отсортирован следующим образом.

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

Теперь наш столбец отсортирован и по цвету текста и в порядке возрастания данных.

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

Таблицы

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

Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.

В окне сортировки ставим галочку в поле «Мои данные содержат заголовки» . В разделе «Столбец» выбираем из списка «Класс» , сортировка по «Значению» , а порядок «По возрастанию» .

Чтобы сделать все тоже самое по среднему балу, нажмите на кнопочку «Добавить уровень» . В разделе «Столбец» выбираем «Средн.бал» . Нажмите «ОК» .

Данные в таблице отсортированы.

Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.

Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …» .

В открывшемся окне уже есть два уровня, которые мы сделали раньше. Эти уровни имеют приоритет – у первого самый большой, у второго меньше и так далее. То есть, если мы хотим, чтобы сначала выполнилась сортировка данных в таблице девочки/мальчики, затем по классу, а затем по среднему балу – нужно в таком порядке и расставить уровни.

Нажимаем на кнопку «Добавить уровень» . В разделе «Столбец» выбираем «Имя» , сортировка – «Цвет ячейки» , порядок – «розовый» , «Сверху» .