Как отсортировать данные в Excel

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

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel Online и Excel для Mac.

Выберите данные для сортировки

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

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

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

Ключ сортировки и порядок сортировки в Excel

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

Быстрая сортировка данных

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

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

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

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

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

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

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

В приведенном ниже примере данные в диапазоне от A2 до E12 сортируются по двум столбцам данных. Данные сначала сортируются по имени, а затем по возрасту.

1. Выделите диапазон ячеек для сортировки. В этом примере выбраны ячейки с A2 по E12.
2. Выберите Домой .
3. Выберите Сортировка и фильтр , чтобы открыть раскрывающийся список.
4. Выберите Пользовательская сортировка , чтобы открыть диалоговое окно «Сортировка».
5. Поставьте галочку напротив Мои данные имеют заголовки .
6. Под заголовком столбца выберите стрелку вниз Сортировать по и выберите Имя в раскрывающемся списке, чтобы сначала отсортировать данные по столбцу Имя.
7. Под заголовком «Сортировать по» оставьте значение Значения ячеек . Сортировка основана на фактических данных в таблице.
8. Под заголовком «Порядок» выберите стрелку вниз и выберите от Z до A , чтобы отсортировать данные имени в порядке убывания.
9. Выберите Добавить уровень , чтобы добавить второй параметр сортировки.
10. Под заголовком столбца выберите стрелку вниз Затем по и выберите Возраст , чтобы отсортировать записи с повторяющимися именами по столбцу Возраст.
11. Под заголовком «Заказ» выберите в раскрывающемся списке От самого большого до самого маленького , чтобы отсортировать данные по возрасту в порядке убывания.
12. Выберите ОК , чтобы закрыть диалоговое окно и отсортировать данные.

В результате определения второго ключа сортировки, показанного в приведенном ниже примере, две записи с одинаковыми значениями для поля Name сортируются в порядке убывания с использованием поля Age . Это приводит к записи для студента Уилсона Дж., 21 год, до записи для Уилсона П., 19 лет.

Первый ряд: заголовки столбцов или данные

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

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

Если первая строка не содержит заголовков, Excel использует букву столбца (например, Столбец D или Столбец E) в качестве вариантов в параметре Столбец диалогового окна Сортировка.

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

Сортировать данные по дате или времени в Excel

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

Диалоговое окно «Быстрая сортировка и сортировка»

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

Пример сортировки по дате

Чтобы выполнить быструю сортировку по дате в порядке возрастания, от самого старого до самого нового:

1. Выделите диапазон ячеек для сортировки. Чтобы следовать приведенному выше примеру, выделите ячейки от G2 до K7.
2. Выберите Домой .
3. Выберите Сортировка и фильтр , чтобы открыть раскрывающийся список.
4. Выберите Пользовательская сортировка , чтобы открыть диалоговое окно «Сортировка».
5. Под заголовком столбца выберите стрелку вниз Сортировать по и выберите Заем , чтобы сначала отсортировать данные по заимствованной дате.
6. Под заголовком «Сортировать по» выберите Значения ячеек . Сортировка основана на фактических данных в таблице.
7. Под заголовком «Порядок сортировки» в раскрывающемся списке выберите «strong> Oldest to Newest» .
8. Выберите ОК в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.

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

Смешанные данные и быстрые сортировки

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

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

Возможное Сортировка Предупреждение

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

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

Сортировать данные по дням недели или по месяцам в Excel

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

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

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

1. Выделите диапазон ячеек для сортировки.
2. Выберите Домой .
3. Выберите Сортировка и фильтр , чтобы открыть раскрывающийся список.
4. Выберите Пользовательская сортировка , чтобы открыть диалоговое окно «Сортировка».
5. Под заголовком столбца выберите Начало месяца в раскрывающемся списке, чтобы отсортировать данные по месяцам года.
6. Под заголовком «Сортировать по» выберите Значения ячеек . Сортировка основана на фактических данных в таблице.
7. Под заголовком «Порядок» выберите стрелку вниз рядом с параметром «от А до Я» по умолчанию, чтобы открыть раскрывающееся меню.
8. Выберите Пользовательский список , чтобы открыть диалоговое окно «Пользовательские списки».
9. В левом окне диалогового окна выберите январь, февраль, март, апрель .
10. Выберите ОК , чтобы подтвердить выбор и вернуться в диалоговое окно «Сортировка».
11. Выбранный список (январь, февраль, март, апрель) отображается под заголовком Заказа.
12. Выберите ОК , чтобы закрыть диалоговое окно и отсортировать данные по месяцам года.

По умолчанию пользовательские списки отображаются только в порядке возрастания в диалоговом окне «Пользовательские списки». Чтобы отсортировать данные в порядке убывания с помощью настраиваемого списка после выбора нужного списка, чтобы он отображался под заголовком «Порядок» в диалоговом окне «Сортировка»:

1. Нажмите стрелку вниз рядом с отображаемым списком, например январь, февраль, март, апрель , чтобы открыть раскрывающееся меню.
2. В меню выберите параметр настраиваемого списка, который отображается в порядке убывания, например декабрь, ноябрь, октябрь, сентябрь .
3. Нажмите ОК , чтобы отсортировать данные в порядке убывания, используя пользовательский список.

Сортировать по строкам, чтобы изменить порядок столбцов в Excel

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

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

Настройте порядок столбцов

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

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

Как только сортировка завершена, добавленную строку чисел можно легко удалить.

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

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

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

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

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

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

[expert_bq id=»1570″]Тип критерия в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

КАК: Как сортировать данные в Excel — 2024

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

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

Почему не сортируется столбец в excel

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

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

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

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

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

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

Если диапазоне данных имеются пустые столбцы или строкой, то 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. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

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

На одном листе располагается несколько таблиц

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

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Разнесение информации по разным листам книги «для удобства»

Информация в комментариях

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

Бардак с форматированием

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

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

[expert_bq id=»1570″]В этом случае, как показано на изображении выше, столбцы были переупорядочены, так что программа столбец сначала слева, а затем Месяц начался , название , так далее. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Как упоминалось выше, в то время как пользовательские сортировки доступны в диалоговом окне «Сортировка» в Excel, эти параметры нелегко использовать, когда речь идет о переупорядочении столбцов на листе. Опции для создания пользовательского порядка сортировки, доступные в диалоговом окне Сортировка, предназначены для сортировки данных по цвету ячеек, цвету шрифта и значку.

Как отсортировать данные в Excel — gadgetshelp,com

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

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