Как сделать двухуровневую сортировку в excel

Как выполнить многоуровневую сортировку данных в Excel

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

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

Пример сценария

В этой статье мы создали образец таблицы для объяснения метода многоуровневой сортировки. В следующей таблице у меня есть 3 столбца, а именно Имя, Предмет, а также Метки.

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

Решение 1. Выполните многоуровневую сортировку с помощью окна сортировки

Этот метод использует окно сортировки, которое легко доступно в вашем Excel.

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

Шаг 1: Сначала выберите данные которые вы хотите отсортировать.

Теперь нажмите Данные вкладку на верхней ленте и нажмите кнопку Сортировать кнопка.

Шаг 2: теперь в окне «Сортировка» установите первый критерий сортировки.

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

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

После того, как первый критерий сортировки установлен, нажмите кнопку Добавить уровень кнопка вверху.

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

Шаг 3: Далее установите второй критерий таким же образом. Выберите столбец из Затем по выпадающее меню и установите порядок как От большего к меньшему в Заказ выпадающее меню.

Ударь ХОРОШО кнопка.

Шаг 4: Вот и все. Теперь ваши данные отсортированы на нескольких уровнях. Наслаждаться!

Решение 2. Выполните многоуровневую сортировку с помощью значков сортировки

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

Шаг 1: Во-первых, выберите столбец что нужно отсортировать из от большего к меньшему. В примере сценария я хочу, чтобы Метки столбец для сортировки от большего к меньшему, поэтому я выбрал Метки столбец.

Теперь нажмите на ДАННЫЕ вкладка вверху.

В дальнейшем под ДАННЫЕ варианты, нажмите на Я -> А значок сортировки.

Шаг 2: Когда вы получите Предупреждение о сортировке диалоговое окно, нажмите кнопку переключатель соответствующий Расширьте выбор и ударил Сортировать кнопка.

Шаг 3: Если вы сейчас посмотрите на лист Excel, вы увидите, что Метки сортируются из от большего к меньшему. Давайте теперь продолжим и отсортируем Имя столбец.

Выберите Имя столбец рядом. Ударь Данные вкладку вверху, а затем нажмите на А -> Я значок сортировки.

Шаг 4: В Предупреждение о сортировке диалоговом окне, еще раз выберите переключатель, соответствующий Расширьте выбор вариант и нажмите Сортировать кнопка.

Шаг 5: Вот и все. Обе сортировки вместе привели к многоуровневой сортировке, и теперь ваша таблица хорошо отсортирована!

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

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

Office обнаружил проблему с этим файлом [Защищенный просмотр] Excel

Иногда после обновления их Окна 10, многие пользователи жаловались на многочисленные проблемы, связанные с MicrosoftОфис Приложения.Microsoft Excel может не открывать документы, поскольку режим защ.

Исправлено: проблема с таблицей Excel в Windows 10 отображается серым цветом. Windows 10 Excel

Это Excel лист отображается серым цветом, когда вы пытаетесь открыть лист на своем компьютере? Если вы столкнулись с этой проблемой и ищете решение, вы попали в нужное место. В этой статье обсуждаю.

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

10 марта 2016 г. От TechieWriterКак использовать функции автоматического восстановления и автоматического резервного копирования Excel для защиты данных: — У вас есть важный отчет, который нужно от.

Уровни сортировки в Excel

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

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

Добавление уровня сортировки в Excel

В следующем примере мы отсортируем таблицу по Номеру аудитории (столбец А) и по Фамилии (столбец С).

1. Выделите любую ячейку в таблице, которую необходимо сортировать. В нашем примере мы выберем ячейку A2.
2. Откройте вкладку Данные, затем нажмите команду Сортировка.
3. Откроется диалоговое окно Сортировка. Из выпадающего списка выберите столбец, по которому Вы желаете выполнить сортировку. В данном примере мы будем сортировать по столбцу Номер аудитории (столбец А).
4. Щелкните Добавить уровень, чтобы добавить еще один столбец для сортировки.
5. Из выпадающего списка выберите второй столбец, по которому Вы хотите сортировать таблицу, затем нажмите OK. В нашем примере мы будем сортировать по Фамилии (столбец С).
6. Таблица будет отсортирована в соответствии с выбранным порядком. В нашем примере номера аудиторий сортируются по возрастающей, а для каждой аудитории студенты расположены по фамилии в алфавитном порядке.

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

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

Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) — по текстовому столбцу.

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй — числовой Объем Продаж (см. файл примера ).

Задача1 (Сортировка таблицы по числовому столбцу)

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

Для наглядности величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Также желтым выделены повторяющиеся значения.

Примечание : Задача сортировки отдельного столбца (списка) решена в статьях Сортированный список (ТЕКСТовые значения) и Сортированный список (ЧИСЛОвые значения) .

Решение1

Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:

• Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
• Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

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

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

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

В столбцах D и E разместим таблицу, которая будет динамически сортироваться,

В ячейке Е7 запишем зубодробительную формулу массива :

Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(. ))

В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.

Для наглядности, величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.

Тестируем

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

1. В ячейку А15 исходной таблицы введите слово Морковь ; 2. В ячейку В15 введите Объем продаж Моркови = 25; 3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица; 4. В сортированной таблице новая строка будет отображена предпоследней.

Скорость вычислений формул

На «среднем» по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа ( Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).

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

Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.

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

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

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

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

Какой вариант предпочтительней — как всегда — выбирать разработчику.

Еще одна формула массива (+дополнительный столбец). Задача1.1

Сортировку таблицы можно сделать с помощью другой, более простой формулы массива , но нам понадобится дополнительный (служебный) столбец D (см. файл примера лист Пример2 ):

В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.

Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет возвращать несколько значений: <0:2:0:0:0:0:0:8>, т.е. число 74 находится в строках 2 и 8.

С помощью функции НАИБОЛЬШИЙ() сначала выводится 2, затем 8 (в разных строках).

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

Задача2 (Сортировка таблицы по текстовому столбцу)

Отсортируем строки таблицы по содержимому Текстового столбца (по Фруктам).

Примечание : Про сортировку списка текстовых значений можно прочитать в статье Сортированный список в MS EXCEL (ТЕКСТовые значения)

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

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

Эта формула является аналогом ранга для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший «ранг». Например, значению Яблоки соответствует максимальный «ранг» 7 (с учетом повторов).

В столбце E введем обычную формулу:

Эта формула учитывает повторы текстовых значений и корректирует «ранг». Теперь разным значениям Яблоки соответствуют разные «ранги» — 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.

Как сделать многоуровневую сортировку данных в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выберите столбец, который вы хотите отсортировать последним (в этом случае сначала выберите данные о продажах — C1: C13).
2. Щелкните вкладку «Данные«.
3. В группе «Сортировка и фильтр» щелкните значок сортировки от Я до А. Это отсортирует данные о продажах от наибольших к наименьшим.
4. Вышеупомянутый шаг вызовет всплывающее диалоговое окно с предупреждением о сортировке. Убедитесь, что установлен флажок «Развернуть выделение». Это обеспечивает сортировку всего набора данных, а не только данных в столбце «Продажи».
5. Щелкните Сортировать.
6. Выберите столбец «Регион».
7. На вкладке «Данные» щелкните значок сортировки от А до Я.
8. Убедитесь, что во всплывающем диалоговом окне «Предупреждение о сортировке» выбрано «Развернуть выделение».
9. Щелкните Сортировать.

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

Хотя этот метод работает нормально, я рекомендую использовать метод bo в диалоговом окне сортировки.

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

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