Сортировка по сложному ключу в excel это

Обновлено: 07.07.2024

На этом шаге мы рассмотрим более сложную сортировку.

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

Пусть необходимо выполнить сортировку по нескольким полям списка (рис. 1), например по полям Месяц и Менеджер .

Рис.1. Пример списка

Для этого надо выбрать команду Данные | Сортировка - появится диалоговое окно Сортировка диапазона (рис. 2).

Рис.2. Диалоговое окно Сортировка диапазона

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

Если результат сортировки оказался неудовлетворительным, выберите команду Правка | Отменить или нажмите комбинацию клавиш Ctrl + Z , чтобы вернуть список к первоначальному состоянию.

Если в диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры , на экране появится диалоговое окно Параметры сортировки , показанное на рисунке 3.

Рис. 3. Диалоговое окно Параметры сортировки

Ниже приведено описание опций этого диалогового окна:

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

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

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

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

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.

В большинстве случаев вам нужно отсортировать один столбец.

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

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

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

В этом руководстве я покажу вам два способа выполнения многоуровневой сортировки данных в Excel (т. е. Сортировки по двум столбцам).

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

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

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

Выберите весь набор данных, который вы хотите отсортировать.
Щелкните вкладку Данные.
Щелкните значок сортировки (показанный ниже). Откроется диалоговое окно «Сортировка».
В диалоговом окне «Сортировка» выберите следующие параметры.
- Сортировать по (столбцу): региону (это первый уровень сортировки)
- Сортировать по: значениям
- Порядок: от А до Я
- Если у ваших данных есть заголовки, убедитесь, что установлен флажок «Мои данные имеют заголовки».
- Затем по (Столбец): Продажи
- Сортировать по: значениям
- Порядок: от большего к меньшему
Вышеупомянутые шаги дадут вам результат, как показано ниже. Это сортирует данные сначала по региону, а затем по столбцу продаж. Обратите внимание: поскольку при сортировке столбца «Продажи» сначала сортируется столбец «Регион», столбец «Регион» остается неизменным.

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

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

Многоуровневая сортировка с использованием значков сортировки

Не многие люди знают этот способ многоуровневой сортировки данных в Excel.

Вот как это сделать:

Вышеупомянутые шаги сортируют данные так же, как и в первом методе.

Хотя этот метод работает нормально, я рекомендую использовать метод 1 в диалоговом окне сортировки. Диалоговое окно «Сортировка» делает его менее подверженным ошибкам (поскольку вы можете видеть, какие уровни сортируются в каком порядке).

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

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

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

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

Сортировка таблицы по отдельному столбцу:
1. Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
2. Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.

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

Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.

Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
1. Выделяем столбец – правая кнопка мыши – «Сортировка».
2. Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
3. Соглашаемся «автоматически расширить диапазон».
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».

В открывшемся окне вводим необходимые параметры:

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

По такому же принципу сортируются данные по шрифту.

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

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

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

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

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

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

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

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

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

Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.
2. Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

Для динамической сортировки текстовых значений понадобятся формулы массива.
1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
4. Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон "фрукты" еще одно значение "помело" и проверим:
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.

Читайте также: