Сортировка по сложному ключу в excel это
Итак, нам вручили электронную таблицу Excel с тысячами строк внутри нее и вы понимаете, что все данные в неправильном порядке. Возможно, вам придется сортировать её на основе имен столбцов или путем сортировки данных от большего к меньшему.
На первый взгляд, сортировка данных в Excel задача простая, и приложение, безусловно, упрощает сортировку. Однако, более важно то, как вы можете отсортировать и изменить порядок данных в ваших листах. Вот три метода сортировки данных Excel, о которых вы узнаете в этом уроке:
- Сортировка данных всего в несколько кликов
- Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
- Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.
Сортировка данных иногда может казаться опасной; что, если вы отсортируете только один столбец и данные будут смещены? Я покажу вам, как избежать этого. Давайте начнем.
Как сортировать данные в электронной таблице Excel (короткое видео)
Этот скринкаст охватывает несколько методов сортировки ваших данных. Просмотрите этот трехминутный видеоролик, чтобы быстро изучить эти профессиональные техники работы Excel. Мы начнем с простой сортировки и перейдем к более продвинутым методам, чтобы вы всегда смогли отсортировать данные так, как вам нужно.
Читайте дальше пошаговое руководство по сортировке данных в электронных таблицах Excel с использованием простых и передовых методов.
Примеры данных (бесплатная загрузка рабочей книги Excel)
В рамках этого урока я создал книгу, с которой вы можете работать, изучая сортировку данных. Загрузите книгу бесплатно и используйте её во время изучения сортировки в Excel.
1. Простая сортировка в Excel
Сортировка может быть очень простой, всего пара кликов для перестановки данных в ваших таблицах. Давайте узнаем как.
В книге Excel, начните с нажатия на ячейку столбца, который вы хотите отсортировать. Теперь, убедитесь, что вы находитесь на вкладке Главная на ленте Excel'я и найдите кнопку Сортировка и фильтр на самой правой стороне этой панели.
Кнопка Сортировка и фильтр обитает в самой правый части вкладки Главная.
Заметьте, что в вариантах сортировки, вы можете отсортировать текст "А до Я" или "Я до А". Эти простые варианты помогут отсортировать данные в Excel В алфавитном или обратным порядках, в зависимости от того что вы выбрали.
Когда вы сортируете данные в Excel, сортируется вся строка. По сути, выбранный вами столбец будет «ключом», который Excel использует, чтобы решить, как сортировать данные, но каждая строка это запись, которая должна оставаться сгруппированной вместе.
В зависимости от данных, которые вы выбрали, можете произвести сортировку по алфавитному или числовому порядку. Если столбцы содержат числовые данные, вы можете отсортировать в порядке от малого к большому числу, текстовые данные сортируются в алфавитном порядке.
В примере выше, варианты сортировки изменились, потому что я выбрал столбец с цифрами.
Выполнить обычную сортировку на самом деле так просто. Просто кликните по данным, выберите вариант сортировки и Excel перестроит данные в таблице.
Я отсортировал данные в этой таблице на основе клиента всего в несколько кликов.
Дельный совет: попробуйте также сортировать, щелкнув правой кнопкой мыши внутри столбца и выбрав Сортировка, а затем указать способ сортировки исходных данных.
2. Как НЕ нужно сортировать данные в Excel
Не менее важно узнать, о самом опасном способе сортировки данных в Excel, такой метод может испортить ваши исходные данные.
Проблема возникает если в таблице много данных, а вы случайно отсортировали только один столбец данных. Каждая строка с данными в Microsoft Excel действительно похоже на запись, которая должна быть такой же по всей строке.
На изображении ниже, Я раскрасил строки, таким образом опасное место с сортировкой только одного столбца данных, будет выделено.
Я задал цвета строкам в этом примере, чтобы мы могли убедиться, что наши данные отсортированы правильно. Цвета должны проходить полностью через каждую строку без перерывов, если данные сортируются правильно.
Большая ошибка пользователей Excel заключается в выборе только одного столбца при сортировке и выборе неправильного параметра в следующем окне.
Excel даже пытается предупредить нас, показывая окно Обнаруженны данные вне указанного диапазона. Во всплывающем окне можно выбрать автоматически расширить выделенный диапазон (выберите это!) и сортировать в пределах указанного выделения.
Я всегда думал, что варианты, которые дает вам это окно, не совсем ясны. Просто знайте, что вы захотите использовать автоматически расширить выделенный диапазон, чтобы убедиться, что Excel затронет все столбцы при сортировке данных.
Для тестирования давайте посмотрим, что произойдет, если мы выберем один столбец и выберем тип сортировки сортировать в пределах указанного выделения.
Использование вариант сортировать в приделах указанного диапазона сортирует только один столбец данных, который обязательно разрушит вашу исходную электронную таблицу.
На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.
Как вы видите из несоответствия цветов, были отсортированы только данные в столбце Amount Billed, поэтому теперь таблица некорректна.
Таким образом, при сортировке данных есть два ключевых «НЕ»:
- Не начинайте, выделив один столбец в своей электронной таблице.
- Не используйте вариант сортировать в приделах указанного диапазона, если вы работаете не с одним столбцом, убедитесь, что вы расширили выделенный диапазон.
3. Расширенная сортировка данных Excel.
До сих пор простая сортировка позволяла нам сортировать данные однотипно. Что, если мы хотим два типа данных в нашей сортировке?
Что, если мы хотим…
- Сортировка в алфавитном порядке по состоянию, а затем по области.
- Сортировка в алфавитном порядке по имени клиента, а затем по каждому типу проекта, который мы сделали для них.
- Сортировка клиентов в список по алфавиту, а затем по количеству для каждого отдельного проекта, от наибольшего до наименьшего.
Ответ на всё это — расширенная сортировка, при которой вы можете установить несколько уровней сортировки данных. Давайте рассмотрим последний пример, используя образцы данных.
Чтобы начать работу, щелкните где-нибудь внутри своих данных и найдите параметр Сортировка и фильтр, а затем выберите Настраиваемая сортировка.
Перейдите к расширенным параметрам сортировки, выбрав Сортировка и фильтр > Настраиваемая сортировка.
В этом окне мы можем добавить несколько уровней сортировки. Начните с нажатия на раскрывающийся список рядом с Сортировка и выберите столбец, который вы хотите отсортировать.
В моем случае я выберу Client в раскрывающемся меню и оставлю значение Сортировка равным Значения, а Порядок — От А до Я. На простом языке это отсортирует электронную таблицу Excel на основе алфавитного порядка.
Теперь давайте нажмем Добавить уровень. Это создаст новую строку в параметрах сортировки и позволит нам добавить второй уровень организации.
Теперь я могу выбрать Amount billed во втором раскрывающемся списке. Комбинация этих двух правил начнется путем сортировки на основе имени клиента, а затем суммы, выставленного счёта за каждый проект.
Вы можете продолжить добавлять столько уровней, сколько хотите в это окно расширенной сортировки. Последовательность строк имеет значение, т.е. вы можете переместить строку вверх для сортировки сначала по выставленному счету, например, а затем по клиенту.
Как только мы нажмем OK, Excel отсортирует таблицу на основе правил, которые мы создали в этом окне.
Совет: Для более продвинутой сортировки попробуйте в раскрывающимся меню Сортировка изменить тип сортировки на расширенные функции, такие как сортировка на основе цвета ячейки.
Расширенная сортировка позволяет создавать два уровня организации данных в вашей таблице. Если сортировки по одному фактору недостаточно, используйте расширенную сортировку, чтобы добавить больше возможностей.
Повторение и продолжение обучения
Сортировка это ещё одно умение, которое нужно держать в готовом виде в своей книге Excel. Когда вам нужно повторно сопоставить данные в электронной таблице, слишком много времени можно потратить для вырезания и вставки строк в определенном порядке, поэтому сортировка является обязательной.
Как всегда, вы можете опираться на навыки, которые вы узнали сегодня, погрузившись в более удобные уроки по Excel. Посмотрите ссылки ниже:
- Сортировка данных это мощный навык, но иногда вам может понадобится подвести итог и уяснить наборы данных, поэтому вместо этого используйте сводные таблицы.
- Ниже приведен обзор дополнительных руководств по сортировке, если вы хотите изучить этот навык из другого источника.
- Узнайте, как работать с формулами Excel — Как работать с формулами в Excel: копирование, вставка и автозаполнение.
Как вы сортируете данные? Используете ли вы простые методы или расширенный инструмент сортировки для обеспечения идеальной сортировкт?
Приступая к выполнению лабораторных работ, необходимо предварительно изучить учебный материал по данной теме Лекция 6. Табличный процессор MS Excel , который освещает назначение и принципы работы табличного процессора. Учебный материал, как лекционный, так и лабораторные работы, осваивается с одновременной работой на компьютере. На компьютере Вы сможете выполнять действия, описанные в лекциях, и просматривать рисунки примеров.
1. Лабораторную работу необходимо выполнить два – три раза, постепенно осваивая и закрепляя новые знания
2. В учебном материале применяется следующая аббревиатура (сокращения):
a. ЛКМ – левая кнопка мыши;
b. ПКМ – правая кнопка мыши;
Данная лабораторная работа состоит из трех частей: 1) теоретический материал, 2) разбор примеров; 3) самостоятельная часть работы. Разбор одного из примеров может быть выполнен устно.
Лабораторная работа №16. MS EXCEL. Команда Данные. Сортировка данных. (2 ч.)
Цель работы: ознакомиться с видами и методами сортировки данных в электронных таблицах. Рассмотреть виды сортировки: по умолчанию, по возрастанию и убыванию. Научиться выполнять сортировку всей таблицы или части таблицы; осуществлять сортировку по одному или нескольким ключам; устанавливать порядок направление сортировки для каждого ключа.
Введение
Табличный процессор MS Excel позволяет упорядочить строки в соответствии с содержимым определенных столбцов. При выполнении команды Данные, Сортировка, Microsoft Excel переставляет строки в соответствии с форматом данных выбранного столбца и заданным порядком направления сортировки. Сортировка осуществляется «на месте» (на том же листе, в той же таблице). Можно выполнить сортировку как всей таблицы, так и заданной области. Различают стандартный порядок сортировки и пользовательский. Стандартной сортировкой считают сортировку по одному столбцу в порядке возрастания или убывания данных.
При сортировке по возрастанию упорядочение строк идет от меньшего к большему по алфавиту, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки. Excel использует следующий порядок по возрастанию:
· числа сортируются от наименьшего отрицательного до наибольшего положительного;
· текст, в том числе содержащий числа, сортируется в следующем порядке:
· ошибки - все значения считаются равными;
· логические значения -ЛОЖЬ ставится перед значением ИСТИНА;
· пустые значения всегда ставятся в конец.
Сортировка по убыванию использует обратный порядок от большего к меньшему.
Пользовательские сортировки содержат варианты выбора сортировки по строкам или столбцам, по одному или нескольким столбцам. Для результата сортировки по двум или трем столбцам существен порядок выбора столбцов («старший» ключ сортировки, «промежуточный» ключ сортировки, «младший ключ сортировки»). Создать собственный список для сортировки, можно с помощью команды Сервис, Параметры, вкладка Списки. Кнопкой Добавить ввести элементы нового списка.
Чтобы использовать нестандартный порядок для сортировки нескольких столбцов, следует последовательно выполнить несколько отдельных сортировок для каждого из столбцов. Например, чтобы сортировать данные по столбцам А и В. Сначала проводится сортировка по столбцу А, задав требуемый порядок сортировки в окне Параметры сортировки. Затем проводится аналогичная сортировка списка по столбцу В.
1. Откройте меню кнопки ПУСК.
2. Выберите элемент Программы, MS Excel.
3. Выполните команду Файл.
4. Из списка последних открываемых файлов выберите Ведомость.
5. Откройте Лист4 и переименуйте его в Сортировка.
6. Наберите таблицу по заданному образцу в ячейках А1:Е8.
Рис.16-1. Территория и население по континентам
7. Выделите ячейки В2:В7. Щелкните на панели инструментов кнопку Автосумма.
8. Выделите ячейки D2:D7. Щелкните на панели инструментов кнопку Автосумма.
Рис.16-2. Стандартные виды сортировки (кнопки на панели инструментов)
Сортировка по возрастанию по одному ключу (столбцу)
1. Выделите диапазон ячеек С1:С7.
2. На панели инструментов нажмите кнопку Сортировка по возрастанию.
Рис.16-3. Диапазон сортировки
4. Обратите внимание на результаты сортировки. MS Excel переставил данные только в пределах одного столбца С. Положение Наименований и остальных данных осталось неизменным, то есть сортировка выполнена не корректно. Подумайте, как избежать ошибки?
Сортировка по нескольким ключам
1. Выделите диапазон ячеек А1:Е7.
2. Выполните команду Данные, Сортировка.
Рис.16-5. Параметры сортировки
3. В окне Сортировка диапазона в списке Сортировать по выберите главный ключ Территория, вид сортировки установите по возрастанию.
4. В списке Затем по выберите промежуточный ключ Наименование, вид сортировки установите по возрастанию.
5. В списке В последнюю очередь по выберите младший ключ сортировки Год, вид сортировки установите по убыванию.
Если у вас есть большие объемы данных, это может быть ошеломляющим, если они неправильно отсортированы в вашей книге. Изучите различные методы сортировки данных в Excel, чтобы повысить производительность и упростить управление электронными таблицами.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel Online и Excel для Mac.
Выберите данные для сортировки
Прежде чем данные могут быть отсортированы, Excel должен знать точный диапазон, который должен быть отсортирован. Excel выберет области связанных данных, если данные удовлетворяют следующим условиям:
- В области связанных данных нет пустых строк или столбцов .
- Пустые строки и столбцы находятся между областями связанных данных.
Excel определяет, есть ли в области данных имена полей, и исключает строку из записей для сортировки. Разрешение Excel выбирать диапазон для сортировки может быть рискованным, особенно с большими объемами данных, которые трудно проверить.
Чтобы убедиться, что выбраны правильные данные, выделите диапазон перед началом сортировки. Если один и тот же диапазон будет отсортирован несколько раз, лучшим подходом будет присвоить ему имя .
Ключ сортировки и порядок сортировки в Excel
Сортировка требует использования ключа сортировки и порядка сортировки. Ключ сортировки — это данные в столбце или столбцах, которые вы хотите отсортировать, и определяется заголовком столбца или именем поля. На рисунке ниже возможны следующие ключи сортировки: идентификатор ученика, имя, возраст, программа и месяц начала.
Быстрая сортировка данных
Для быстрой сортировки выберите одну ячейку в столбце, содержащем ключ сортировки. Затем выберите способ сортировки данных. Вот как:
- Выберите ячейку в столбце, содержащем ключ сортировки.
- Выберите Дом .
- Выберите Sort & Filter, чтобы открыть раскрывающееся меню параметров сортировки.
- Выберите способ сортировки данных. Выберите восходящий или нисходящий порядок.
При использовании сортировки и фильтрации параметры порядка сортировки в раскрывающемся списке изменяются в зависимости от типа данных в выбранном диапазоне. Для текстовых данных возможны следующие варианты: Сортировка от А до Я и Сортировка от Z до А. Для числовых данных: Сортировка от наименьшего к наибольшему и сортировка от наименьшего к наименьшему.
Сортировать несколько столбцов данных в Excel
Помимо выполнения быстрой сортировки на основе одного столбца данных, пользовательская функция сортировки Excel позволяет сортировать по нескольким столбцам путем определения нескольких ключей сортировки . При сортировке по нескольким столбцам ключи сортировки определяются путем выбора заголовков столбцов в диалоговом окне «Сортировка » .
Как и в случае быстрой сортировки, ключи сортировки определяются путем идентификации заголовков столбцов или имен полей в таблице, содержащей ключ сортировки.
Пример сортировки по нескольким столбцам
В приведенном ниже примере данные в диапазоне от A2 до E12 сортируются по двум столбцам данных. Данные сначала сортируются по имени, а затем по возрасту.
Чтобы отсортировать несколько столбцов данных:
- Выделите диапазон ячеек для сортировки. В этом примере выбраны ячейки с A2 по E12.
- Выберите Дом .
- Выберите Sort & Filter, чтобы открыть раскрывающийся список.
- Выберите Custom Sort, чтобы открыть диалоговое окно Sort.
- Поставьте галочку напротив пункта Мои данные с заголовками .
- Под заголовком столбца выберите « Сортировать по стрелке вниз» и выберите « Имя» в раскрывающемся списке, чтобы сначала отсортировать данные по столбцу «Имя».
- Под заголовком «Сортировать по» оставьте значение « Значения ячеек» . Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок» выберите стрелку вниз и выберите « Z — A», чтобы отсортировать данные «Имя» в порядке убывания.
- Выберите Добавить уровень, чтобы добавить второй параметр сортировки.
- Под заголовком столбца выберите стрелку « Затем по внизу» и выберите « Возраст», чтобы отсортировать записи с повторяющимися именами по столбцу «Возраст».
- Под заголовком «Порядок» выберите « От наибольшего к наименьшему» в раскрывающемся списке, чтобы отсортировать данные о возрасте в порядке убывания.
- Выберите ОК, чтобы закрыть диалоговое окно и отсортировать данные.
В результате определения второго ключа сортировки, показанного в примере ниже, две записи с одинаковыми значениями для поля « Имя» сортируются в порядке убывания с использованием поля « Возраст» . Это приводит к записи для студента Уилсона Дж., 21 год, до записи для Уилсона П., 19 лет.
Первый ряд: заголовки столбцов или данные
Диапазон данных, выбранных для сортировки в приведенном выше примере, включает заголовки столбцов над первой строкой данных. Эта строка содержит данные, которые отличаются от данных в последующих строках. Excel определил, что первая строка содержит заголовки столбцов, и скорректировал доступные параметры в диалоговом окне «Сортировка», чтобы включить их.
Excel использует форматирование, чтобы определить, содержит ли строка заголовки столбцов. В приведенном выше примере заголовки столбцов отличаются от шрифта данных в остальных строках.
Если первая строка не содержит заголовков, Excel использует букву столбца (например, Столбец D или Столбец E) в качестве вариантов в параметре Столбец диалогового окна Сортировка.
Excel использует это различие, чтобы определить, является ли первая строка строкой заголовка. Если Excel делает ошибку, диалоговое окно «Сортировка» содержит флажок «Мои данные имеют заголовки», который отменяет этот автоматический выбор.
Сортировать данные по дате или времени в Excel
В дополнение к сортировке текстовых данных по алфавиту или по номерам от наибольшего к наименьшему, параметры сортировки в Excel включают значения даты сортировки. Доступные заказы сортировки на даты включают в себя:
- По возрастанию: от старейшего к новейшему.
- По убыванию: от самого нового к старому.
Диалоговое окно «Быстрая сортировка и сортировка»
Даты и время, отформатированные как числовые данные, такие как «Дата заимствования» в приведенном выше примере, используют метод быстрой сортировки для сортировки по одному столбцу. Для сортировок, включающих несколько столбцов дат или времени, используйте диалоговое окно «Сортировка » так же, как и для сортировки нескольких столбцов числовых или текстовых данных.
Пример сортировки по дате
Чтобы выполнить быструю сортировку по дате в порядке возрастания, от самого старого до самого нового:
- Выделите диапазон ячеек для сортировки. Чтобы следовать примеру выше, выделите ячейки от G2 до K7.
- Выберите Дом .
- Выберите Sort & Filter, чтобы открыть раскрывающийся список.
- Выберите Custom Sort, чтобы открыть диалоговое окно Sort.
- Под заголовком столбца выберите « Сортировать по стрелке вниз» и выберите « Заимствовано», чтобы сначала отсортировать данные по заимствованной дате.
- Под заголовком «Сортировать по» выберите « Значения ячеек» . Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок сортировки» в раскрывающемся списке выберите « Самый старый» .
- Выберите OK в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.
Если результаты сортировки по дате не получаются, как ожидалось, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, сохраненные как текстовые данные, а не как числа (даты и время являются просто отформатированными числовыми данными).
Смешанные данные и быстрые сортировки
При использовании метода быстрой сортировки, если записи, содержащие текстовые и числовые данные, смешаны вместе, Excel сортирует числовые и текстовые данные отдельно, помещая записи с текстовыми данными в нижней части отсортированного списка.
Excel также может включать заголовки столбцов в результаты сортировки, интерпретируя их как просто еще одну строку текстовых данных, а не как имена полей для таблицы данных.
Возможное Сортировка Предупреждение
- Сортируйте все, что выглядит как число, как число.
- Сортировка чисел и номеров хранится в виде текста отдельно.
Если вы выберете первый вариант, Excel попытается разместить текстовые данные в правильном месте результатов сортировки. Выберите второй вариант, и Excel поместит записи, содержащие текстовые данные, внизу результатов сортировки, как это происходит с быстрыми сортировками.
Сортировать данные по дням недели или по месяцам в Excel
Вы также можете сортировать данные по дням недели или месяцам года, используя тот же встроенный настраиваемый список, который Excel использует для добавления дней или месяцев на лист с помощью маркера заполнения . Эти списки позволяют сортировать по дням или месяцам в хронологическом порядке, а не в алфавитном порядке.
Как и в случае с другими параметрами сортировки, значения сортировки по пользовательскому списку могут отображаться в порядке возрастания (с воскресенья по субботу или с января по декабрь) или в порядке убывания (с субботы по воскресенье или с декабря по январь).
На изображении выше были выполнены следующие шаги для сортировки выборки данных в диапазоне от А2 до Е12 по месяцам года:
- Выделите диапазон ячеек для сортировки.
- Выберите Дом .
- Выберите Sort & Filter, чтобы открыть раскрывающийся список.
- Выберите Custom Sort, чтобы открыть диалоговое окно Sort.
- Под заголовком столбца выберите « Начало месяца» в раскрывающемся списке, чтобы отсортировать данные по месяцам года.
- Под заголовком «Сортировать по» выберите « Значения ячеек» . Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок» выберите стрелку вниз рядом с параметром «от А до Я» по умолчанию, чтобы открыть раскрывающееся меню.
- Выберите Custom List, чтобы открыть диалоговое окно Custom Lists.
- В левом окне диалогового окна выберите январь, февраль, март, апрель .
- Выберите OK, чтобы подтвердить выбор и вернуться в диалоговое окно «Сортировка».
- Выбранный список (январь, февраль, март, апрель) отображается под заголовком Заказа.
- Выберите ОК, чтобы закрыть диалоговое окно и отсортировать данные по месяцам года.
По умолчанию пользовательские списки отображаются только в порядке возрастания в диалоговом окне «Пользовательские списки». Чтобы отсортировать данные в порядке убывания, используя пользовательский список после выбора нужного списка, чтобы он отображался под заголовком «Порядок» в диалоговом окне «Сортировка»:
- Выберите стрелку вниз рядом с отображаемым списком, например, январь, февраль, март, апрель, чтобы открыть раскрывающееся меню.
- В меню выберите параметр пользовательского списка, который отображается в порядке убывания, например, декабрь, ноябрь, октябрь, сентябрь .
- Нажмите OK, чтобы отсортировать данные в порядке убывания, используя пользовательский список.
Сортировать по строкам, чтобы изменить порядок столбцов в Excel
Как показано с предыдущими опциями сортировки, данные обычно сортируются с использованием заголовков столбцов или имен полей. Результатом является переупорядочение целых строк или записей данных. Менее известный и, следовательно, менее используемый параметр сортировки в Excel — это сортировка по строкам, что приводит к изменению порядка столбцов слева направо на рабочем листе.
One reason for sorting by row is to match the column order between different tables of data. With the columns in the same left to right order, it is easier to compare records or to copy and move data between the tables.
Customize the Column Order
Very seldom, however, is getting the columns in the correct order a straightforward task due to the limitations of the ascending and descending sort order options for values. Usually, it is necessary to use a custom sort order, and Excel includes options for sorting by cell or font color or by conditional formatting icons.
The easiest way of telling Excel the order of columns is to add a row above or below the data table containing numbers that indicate the order of columns from left to right. Sorting by rows then becomes a simple matter of sorting the columns smallest to largest by the row containing the numbers.
Sort by Rows Example
In the data sample used for this series on Excel sort options, the Student ID column has always been first on the left, followed by Name and then Age.
In this instance, as shown in the image above, numbers have been added to the columns to prepare the worksheet to reorder the columns so that the Program column is first on the left followed by Month Start, Name, Age, and Student ID.
Here’s how to change the column order:
-
above the row containing the field names.
- In this new row, enter the following numbers left to right starting in column A: 5, 3, 4, 1, 2.
- Highlight the range to be sorted. In this example, highlight A2 to E13.
- Select Home.
- Select Sort & Filter to open the drop-down list.
- Select Custom Sort to open the Sort dialog box.
- Select Options to open the Sort Options dialog box.
- In the Orientation section, select Sort left to right to sort the order of columns left to right in the worksheet.
- Select OK to close the Sort Options dialog box.
- With the change in Orientation, the Column heading in the Sort dialog box changes to Row.
- Select the Sort bydown arrow and choose Row 2. This is the row containing the custom numbers.
- Under the Sort On heading, choose Cell Values.
- Under the Order heading, choose Smallest to Largest from the drop-down list to sort the numbers in row 2 in ascending order.
- Select OK to close the dialog box and sort the columns left to right by the numbers in row 2.
- The order of columns begins with Program followed by Month Start, Name, Age, and Student ID.
Use Excel’s Custom Sort Options to Reorder Columns
While custom sorts are available in the Sort dialog box in Excel, these options are not easy to use when it comes to reordering columns in a worksheet. Options for creating a custom sort order available in the Sort dialog box are to sort the data by cell color, font color, and icon.
Unless each column has already had unique formatting applied, such as different font or cell colors, that formatting needs to be added to individual cells in the same row for each column to be reordered.
Сортировка списка - это выстраивание строк или столбцов в определенном порядке.
MS Excel предоставляет две возможности для сортировки списков. Можно сортировать строки или столбцы в возрастающем или убывающем порядке значения какого -либо атрибута (ключа) или нескольких атрибутов. При сортировке строк изменяется порядок их следования, а порядок следования столбцов остается неизменным. При сортировке столбцов изменяется порядок следования столбцов, а строки остаются на своих местах.
Заголовок обычно не сортируется.
Можно сортировать весь список или его часть.
Для сортировки строк всего списка можно использовать два варианта: сортировка по одному ключу и сортировка по нескольким ключам.
Для сортировки по одному ключу нужно:
- выделить любую ячейку (но не диапазон) в столбце, который содержит ключ сортировки;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка от А до Я или Сортировка от Я до А.
Для сортировки по нескольким ключам:
- выделить любую ячейку (но не диапазон) списка;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка. MS Excel проведет анализ списка для определения размера списка, определит строку заголовка, исключаемую из сортировки, и выведет на экран диалоговое окно Сортировка (рис. 1).
- кликнуть на кнопке Параметры диалогового окна;
- в диалоговом окне Параметры сортировки (рис. 2) установить опцию Строки диапазона и , если нужно, опцию Учитывать регистр;
- установить порядок сортировки, добавляя уровни.
Сортировка производится по нескольким ключам (уровням): сначала по первому, указанному пользователем в поле Сортировать по , затем в пределах строк, у которых эти поля совпадают, будет произведена сортировка по второму ключу (уровню), установленному в поле Затем по и т. д.
Ключи для сортировки выбираются из раскрывающихся списков.
В каждом ключе можно установить Порядок сортировки По возрастанию или По убыванию .
Чтобы заголовки списков не включались в сортировку нужно установить опцию Мои данные содержат заголовки .
После щелчка на кнопке ОК будет выполнена сортировка списка.
Если выделить не одну ячейку, а несколько строк и столбцов, то будет сортироваться только выделенная часть списка.
Сортировка столбцов списка
Для сортировки по столбцам нужно:
- установить курсор в какую-либо ячейку списка;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка.
- кликнуть в диалоговом окне Сортировка кнопку Параметры . Откроется диалоговое окно Параметры сортировки (рис. 2), в котором нужно активизировать переключатель Столбцы диапазона и кликнуть на ОК .
Пользовательский порядок сортировки (настраиваемый)
Иногда возникает необходимость применения нестандартного порядка сортировки. Например, сортировку не в алфавитном порядке, а в виде списка: № счета, Приход, Расход, Дата операции, № п.п . Для создания пользовательского порядка сортировки нужно выполнить следующие действия:
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка;
- в списке Порядок диалогового окна Сортировка выбрать пункт Настраиваемый список;
- в появившемся диалоговом окне Списки области Списки выделить Новый Список;
- В области Элементы списк а ввести, разделяя запятыми, или на новых строках каждый элемент пользовательского списка (рис. 3).
- кликнуть на кнопке Добавить;
- п осле появления пользовательского списка в области Списки кликнуть на кнопке ОК;
- выделить ячейку или диапазон списка для сортировки, на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка; - в открывшемся диалоговом окне Сортировка кликнуть на кнопке Параметры и в открывшемся диалоговом окне Параметры сортировки установить опцию По столбцам, кликнуть на ОК ;
- в диалоговом окне Сортировка в списке Порядок выбрать необходимый пользовательский порядок сортировки и кликнуть на ОК (рис. 4).
На этом шаге мы рассмотрим более сложную сортировку.
В отдельных случаях необходимо выполнить сортировку по двум или нескольким столбцам. Такая операция производится, если сортировка по одному полю оставляет неотсортированными записи, соответствующие одинаковым значениям в сортируемом поле.
Пусть необходимо выполнить сортировку по нескольким полям списка (рис. 1), например по полям Месяц и Менеджер .
Рис.1. Пример списка
Для этого надо выбрать команду Данные | Сортировка - появится диалоговое окно Сортировка диапазона (рис. 2).
Рис.2. Диалоговое окно Сортировка диапазона
В раскрывающемся списке Сортировать по нужно выбрать поле и указать порядок сортировки. Затем аналогичные действия надо выполнить для второго поля. Если необходимо отсортировать и по третьему полю, то можно определить третье поле в третьем разделе. Когда в разделе Идентифицировать поля по установлен переключатель Подписям , строка заголовков полей не будет влиять на сортировку. Щелкните на кнопке ОК , и строки перегруппируются.
Если результат сортировки оказался неудовлетворительным, выберите команду Правка | Отменить или нажмите комбинацию клавиш Ctrl + Z , чтобы вернуть список к первоначальному состоянию.
Если в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры , на экране появится диалоговое окно Параметры сортировки , показанное на рисунке 3.
Рис. 3. Диалоговое окно Параметры сортировки
Ниже приведено описание опций этого диалогового окна:
- Сортировка по первому ключу. Позволяет определить особый порядок сортировки.
- Учитывать регистр. При сортировке будет учитываться различие между прописными и строчными буквами. В результате при сортировке по возрастанию прописные буквы будут располагаться перед строчными. По умолчанию различие между прописными и строчными буквами не учитывается.
- Сортировать. Позволяет выполнить сортировку по столбцам, а не по строкам (задано по умолчанию).
Для выполнения сортировки произвольного диапазона рабочей таблицы последний не обязательно должен быть списком. Однако необходимо помнить следующее. При использовании кнопок сортировки панели инструментов Сортировка по возрастанию и Сортировка по убыванию верхняя строка может ошибочно считаться строкой заголовков, и эти ячейки не будут учитываться при сортировке.
Поэтому, чтобы избежать возможных ошибок, не используйте эти кнопки панели инструментов. Вместо этого выделите весь диапазон и используйте команду Данные | Сортировка . Убедитесь, что в диалоговом окне Сортировка диапазона в разделе Идентифицировать поля по выбран переключатель Обозначениям столбцов листа .
Читайте также: