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

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

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

Сортировка числовых значений в Excel

Сортировка числовых значений по возрастанию - это такая расстановка значений, при которой значения располагаются от наименьшего к наибольшему (от минимального к максимальному).

Соответственно, сортировка числовых значений по убыванию - это расположение значений от наибольшего к наименьшему (от максимального к минимальному).

Сортировка текстовых значений в Excel

"Сортировка от А до Я" - сортировка данных по возрастанию;

"Сортировка от Я до А" - сортировка данных по убыванию.

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

Текстовые значения могут содержать алфавитные, числовые и специальные символы. При этом числа могут быть сохранены как в числовом, так и в текстовом формате. Числа, сохраненные в числовом формате, меньше, чем числа, сохраненные в текстовом формате. Для корректной сортировки текстовых значений все данные должны быть сохранены в текстовом формате. Кроме того, при вставке в ячейки текстовых данных из других приложений, эти данные могут содержать пробелы в своем начале. Перед началом сортировки необходимо удалить начальные пробелы (либо другие непечатаемые символы) из сортируемых данных, иначе сортировка будет выполнена некорректно.

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле "Учитывать регистр".

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

"Сортировка от старых к новым" - это сортировка значений даты и времени от самого раннего значения к самому позднему.

"Сортировка от новых к старым" - это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

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

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

Параметры сортировки

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

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

Сортировка по строке

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

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

Итак, если производится сортировка по столбцу, то строки меняются местами, если данные сортируются по строке, то местами меняются столбцы.

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

Кроме всего прочего при сортировке можно учитывать, либо не учитывать регистр.

Надстройка для сортировки данных в Excel

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

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

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

Но, все-таки, как отсортировать ячейки Excel по горизонтали ? Для горизонтальной сортировки в Excel припасено несколько вариантов, которые дополняют друг друга:

1. С помощью стандартного механизма настраиваемой сортировки: Вкладка “Главная”/ группа “Редактирование”/ команда “Сортировка и фильтр”/ “Настраиваемая сортировка”. Далее, в параметрах выбираем сортировку по столбцам диапазона.

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

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

1. С помощью механизма . Мы уже неоднократно использовали данный инструмент при решении конкурсных заданий ExcelWorldChamp и . На первый взгляд решение может показаться громоздким, однако, по мере освоения Power Query, можно привыкнуть. А, если вы планируете заниматься обработкой данных в Excel, то без использования данного инструмента сейчас просто нельзя.

Используем горизонтальную сортировку с помощью Power Query

Для совершения каких либо манипуляций в Power Query данные необходимо предварительно загрузить. На вкладке данные, в зависимости от типа источника данных пользуемся соответствующей командой. В нашем, простом случае, – это “Из таблицы/диапазона”.

В самом окне редактора Power Query переходим на вкладку “Добавление столбца”, команда “Настраиваемый столбец”. В диалоговом окне настройки столбца вводим функцию List.Sort(), в качестве аргументов передаем столбцы. По умолчанию функция сортируем по возрастанию, поэтому этот опциональный параметр нужно обязательно указывать, если сортировка нужна в убывающем порядке.

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Ascending)

что равноценно

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]})

а вот формула для убывающего порядка

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Descending)

Поскольку результатом получается элемент типа список, нужно определиться с дальнейшими действиями сортировки, если нужно получить столбец со значениями, выбираем опцию “Развернуть в новые строки”, однако, если идет сортировка для большого количества данных, лучше воспользоваться опцией “Извлечь значения…”. При извлечении значений все они извлекаются в одну ячейку с определенным разделителем (ну или без). Если потом возникнет необходимость разнести значения по ячейкам сделать это можно несколькими способами, мы их рассмотрели в данном , однако, лучшим вариантом будет воспользоваться командой “Разделить столбец” вкладки “Главная” в самом Power Query.

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

Возрастающий порядок сортировки:

Числа будут отсортированы в порядке от наименьшего отрицательного до наибольшего положительного числа.

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

Текст будет отсортирован по алфавиту. При этом сначала будут расположены заданные в качестве текста числовые значения.

При сортировке в возрастающем порядке логических значений сначала будет отображено значение ЛОЖЬ, а затем – значение ИСТИНА.

Значения ошибки будут отсортированы в том порядке, в котором они были обнаружены (с точки зрения сортировки все они равны).

Пустые ячейки будут отображены в конце отсортированного списка.

Убывающий порядок сортировки:

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

Пользовательский порядок сортировки:

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

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

Для сортировки списка поместите указатель ячейки внутри списка и выполните команду Данные – Сортировка.

Excel автоматически выделит список и выведет на экран диалоговое окно “Сортировка диапазона” в котором нужно указать параметры сортировки.

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

Excel автоматически распознает имена полей, если формат ячеек, содержащих имена, отличается от формата ячеек с данными.

Диалоговое окно “Сортировка диапазона”.

Если выполненное программой выделение диапазона не совсем корректно, установите переключатель внизу диалогового окна в нужное положение (Идентифицировать поля по “подписям (первая строка диапазона)” или же “обозначениям столбцов листа”).

Заданные в диалоговом окне “Сортировка” диапазона и “Параметры сортировки” параметры будут сохранены и отображены в диалоговом окне при следующем его открытии.

Вы можете также задать сортировку столбцов диапазона вместо строк. Для этого нажмите в диалоговом окне “Сортировка диапазона” кнопку “Параметры” и в диалоговом окне “Параметры сортировки” в группе “Сортировать” установите переключатель в положение “Столбцы диапазона”.

26. Фильтрация данных в Excel.

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

Автофильтр

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

Вставка автофильтра

1. Поместите указатель ячейки внутри списка.

2. В подменю Данные – Фильтр выберите команду “Автофильтр”. Рядом с именами полей будут отображены кнопки со стрелками, нажав которые, можно открыть список.

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

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

5. Выберите при необходимости из списка другого поля нужный элемент. На экране будут отображены только те записи, которые соответствуют всем заданным условиям фильтрации (условия отдельных полей объединяются с помощью логической операции “И”).

Кнопки открытия автофильтров полей, для которых заданы условия фильтрации, окрашены синим цветом.

Если перед выполнением команды “Автофильтр” Вы выделили один или несколько столбцов, то раскрывающиеся списки будут добавлены только соответствующим полям.

Чтобы снова отобразить на экране все записи списка, выполните команду “Отобразить все” из подменю Данные – Фильтр.

Критерий фильтрации для отдельного поля можно убрать, выбрав в списке автофильтра этого поля элемент “Все”.

Чтобы деактивировать функцию автофильтра (удалить раскрывающиеся списки), выберите повторно команду Данные – Фильтр – Автофильтр.

Применение пользовательского автофильтра

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

Вставьте в список автофильтр, выбрав команду Данные – Фильтр – Автофильтр.

Откройте список автофильтра для нужного поля и выберите в нем элемент (Условие).

В открывшемся диалоговом окне “Пользовательский автофильтр” (Рис. 6.3.27.) укажите первый критерий.

Выберите логический оператор, объединяющий первый и второй критерии.

Диалоговое окно “Пользовательский автофильтр”.

Вы можете задать для отдельного поля в пользовательском автофильтре один или два критерия. В последнем случае их можно объединить логическим оператором “И” либо “ИЛИ”.

Задайте второй критерий.

Нажмите кнопку “OK”. Excel отфильтрует записи в соответствии с указанными критериями.

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

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

Диапазон критериев

Критерии можно задать в любом свободном месте рабочего листа. В диапазоне критериев Вы можете вводить и сочетать два типа критериев:

Простые критерии: программа сравнит содержимое полей с заданным критерием (аналогично применению автофильтра).

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

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

В строке (строках), расположенной непосредственно под строкой с именами полей, следует задать критерии. При задании критериев Excel не различает регистр. Вы можете использовать при задании критериев символы подстановки? и *.

Все критерии, заданные в одной строке, должны выполняться одновременно (соответствует логическому оператору “И”). Чтобы задать соединение критериев оператором “ИЛИ”, укажите критерии в различных строках.

Применение расширенного фильтра

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

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

2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.

3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.

Вы можете применить в рабочем листе только один расширенный фильтр.

Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.

Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.

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

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

Чисел

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

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

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

По алфавиту

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

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

По дате

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

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

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

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

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

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

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

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

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

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

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

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

Таблицы

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

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

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

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

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

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

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

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

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

Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК» .

Таблица с отсортированными данными выглядит следующим образом.

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

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

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

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

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

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

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

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

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

Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки…

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

Теперь в окне сортировки в поле Порядок нужно выбрать Настраиваемый список…

И в следующем окне указать нужный список.

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

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

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

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

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

Последующие настройки обычные: задаем строку (!) и порядок. Единственное, теперь нельзя использовать названия строк (по аналогии с названием столбцов), будут только номера.

Сортировка промежуточных итогов

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