Как СОРТИРОВАТЬ в Excel (по строкам, столбцам, цветам, датам и числам)

Сортировка данных в Excel стала довольно простой благодаря всем встроенным параметрам.

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

Вы также можете выполнять многоуровневую сортировку столбцов (то есть сортировку по столбцу A, а затем по столбцу B), а также сортировку строк (слева направо).

И если этого было недостаточно, Excel также позволяет создавать свои собственные списки и сортировать на их основе (как это круто). Таким образом, вы можете сортировать данные по размеру рубашки (XL, L, M, S) или ответам (полностью согласен, согласен, не согласен) или интенсивности (высокая, средняя, ​​низкая).

Итог - в вашем распоряжении слишком много вариантов сортировки при работе с Excel.

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

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

Доступ к параметрам сортировки в Excel

Поскольку сортировка - это обычная вещь, необходимая при работе с данными, Excel предоставляет несколько способов доступа к параметрам сортировки.

Кнопки сортировки на ленте

Самый быстрый способ получить доступ к параметрам сортировки - использовать кнопки сортировки на лентах.

Когда вы щелкаете вкладку «Данные» на ленте, вы увидите параметры «Сортировка и фильтр». Три кнопки слева в этой группе предназначены для сортировки данных.

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

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

Эти кнопки также работают с числами, датами и временем.

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

Диалоговое окно сортировки

На вкладке «Данные» на ленте есть еще один значок кнопки «Сортировка» в группе сортировки.

Когда вы щелкаете значок кнопки «Сортировка», открывается диалоговое окно сортировки (как показано ниже).

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

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

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

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

В этом руководстве вы обнаружите, что я в основном использую диалоговое окно для сортировки данных. Это также связано с тем, что некоторые из вещей, которые я описываю в определенных разделах (например, многоуровневая сортировка или сортировка слева направо), могут быть выполнены только с помощью диалогового окна.Сочетание клавиш - Если вам нужно часто сортировать данные в Excel, я рекомендую вам изучить сочетание клавиш, чтобы открыть диалоговое окно сортировки. Его ALT + A + S + S

Параметры сортировки в меню фильтров

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

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

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

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

Параметры щелчка правой кнопкой мыши

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

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

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

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

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

Сортировка данных в Excel (текст, числа, даты)

Предостережение: В большинстве случаев сортировка работает, даже если вы выбираете одну ячейку в наборе данных. Но в некоторых случаях у вас могут возникнуть проблемы (когда в вашем наборе данных есть пустые ячейки / строки / столбцы). При сортировке данных лучше всего выбрать весь набор данных, а затем отсортировать его, чтобы избежать каких-либо проблем.

В зависимости от типа имеющихся данных вы можете использовать параметры сортировки в Excel.

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

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

Ниже приведены шаги для сортировки этих текстовых данных в алфавитном порядке:

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

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

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

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

И это правда.

Вышеупомянутый метод более длительный, но есть нет шансов на ошибку.

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

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

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

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

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

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

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

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

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

Ниже приведены шаги для сортировки этих данных по числам:

1. Выбрать весь набор данных
2. Перейдите на вкладку "Данные".
3. Щелкните значок сортировки. Откроется диалоговое окно «Сортировка».
4. В диалоговом окне «Сортировка» убедитесь, что у моих данных есть заголовки. Если у ваших данных нет заголовков, вы можете снять этот флажок.
5. В раскрывающемся списке «Сортировать по» выберите «Имя».
6. Убедитесь, что в раскрывающемся списке «Сортировка по» выбрано «Значения ячеек».
7. В раскрывающемся списке «Порядок» выберите «От наибольшего к наименьшему».
8. Щелкните ОК.

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

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

Хотя дата и время могут выглядеть по-разному, это не что иное, как числа.

Например, в Excel число 44196 будет значением даты 31 декабря 2021-2022 гг. Вы можете отформатировать это число, чтобы оно выглядело как дата, но в бэкэнде в Excel оно по-прежнему остается числом.

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

То же самое и в Excel.

Например, число 44196,125 означает 3 часа ночи 31 декабря 2021-2022 гг. В то время как целая часть числа представляет полный день, десятичная часть даст вам время.

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

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

Ниже приведены шаги для сортировки этих данных по датам:

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

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

Обратите внимание, что, хотя дата и время являются числами, Excel все же распознал, что они различаются по способу отображения. Поэтому при сортировке по дате отображаются критерии сортировки от «от старых к новым» и от «от новых к старым», а при использовании чисел - от «от наибольшего к наименьшему» или «от наименьшего к наибольшему». Такие мелочи, которые делают Excel отличным инструментом для работы с электронными таблицами (PS: Google Таблицы не показывают так много деталей, только мягкая сортировка по A-Z или Z-A)

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

Этот вариант потрясающий, и я использую его постоянно (может, даже чересчур).

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

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

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

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

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

Ниже приведены шаги для сортировки по цвету:

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

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

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

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

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

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

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

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

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

1. Область
2. Продажи

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

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

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

Ниже приведены шаги для сортировки данных по нескольким столбцам:

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

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

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

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

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

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

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

Но что, если я хочу, чтобы этот порядок сортировки был Восток, Запад, Север, Юг?

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

Правильный способ сделать это - использовать настраиваемые списки.

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

Вот некоторые примеры, в которых могут быть полезны настраиваемые списки:

• Сортировка данных по названию региона / города
• Сортировка по размеру футболки - маленький, средний, большой, очень большой
• Сортировка на основе ответов на опрос - полностью согласен, согласен, нейтрально, не согласен
• Сортировка по вероятности - высокая, средняя, ​​низкая

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

Шаги по созданию настраиваемого списка в Excel:

1. Перейдите на вкладку Файл.
2. Нажмите на Параметры
3. В диалоговом окне параметров Excel выберите «Дополнительно» из списка на левой панели.
4. В выбранном расширенном режиме прокрутите вниз и выберите «Редактировать настраиваемый список».
5. В диалоговом окне «Настраиваемые списки» введите критерии в поле «Записи списка». Введите критерии через запятую (Восток, Запад, Север, Юг) [вы также можете импортировать свои критерии, если они у вас есть в списке].
6. Нажмите кнопку "Добавить".
7. Нажмите ОК.

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

Обратите внимание, что порядок элементов в настраиваемом списке определяет способ сортировки вашего списка.

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

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

Предположим, у вас есть набор данных, как показано ниже, и вы хотите отсортировать его по регионам (порядок сортировки - Восток, Запад, Север и Юг).

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

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

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

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

Примечание. Вам не нужно заранее создавать собственный список для сортировки данных на его основе. Вы также можете создать его, находясь в диалоговом окне «Сортировка». Когда вы нажимаете Custom List (на шаге 7 выше), открывается диалоговое окно настраиваемого списка. Вы также можете создать собственный список.

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

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

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

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

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

В Excel есть встроенная функция, которая позволяет выполнять сортировку слева направо.

Ниже приведены шаги для сортировки этих данных слева направо:

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

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

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

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

Ниже приведено видео, в котором я показываю, как сортировать данные слева направо в Excel.

Сортировка с учетом регистра в Excel

До сих пор во всех приведенных выше примерах сортировка не зависела от регистра.

Но что, если вы хотите сделать сортировку чувствительной к регистру.

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

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

Предположим, у вас есть набор данных, показанный ниже, и вы хотите отсортировать эти данные по столбцу региона:

Ниже приведены шаги по сортировке данных по алфавиту, а также с учетом регистра:

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

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

В результате вы получите данные, как показано ниже:

При сортировке от A до Z текст в нижнем регистре размещается над текстом в верхнем регистре.

Получение исходного порядка сортировки

Часто при сортировке данных в Excel вам может потребоваться вернуться к более раннему или исходному порядку сортировки и начать заново,

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

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

Вот два простых способа убедиться, что вы не потеряете исходный порядок сортировки и не восстановите его даже после сортировки данных:

1. Сделайте копию исходного набора данных. Я рекомендую вам это сделать, даже если вам не нужен исходный порядок сортировки. Вы можете создать книгу со всеми данными, а затем просто создать копию книги и работать с ней. Когда я работаю над критически важными наборами данных, я делаю копию каждый день (с датой или номером версии как частью имени книги).
2. Добавьте столбец с серией номеров. Эта серия чисел смешивается, когда вы сортируете данные, но если вы хотите вернуться к исходным данным, вы можете отсортировать их на основе этой серии чисел.

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

Предположим, у вас есть набор данных, как показано ниже:

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

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

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

Предположим, я сортирую эти данные по региону и получаю данные, как показано ниже:

Теперь, если я хочу вернуться к исходному набору данных, я могу просто снова отсортировать эти данные, но на основе вспомогательного столбца (от меньшего к большему).

Просто … не правда ли?

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

Некоторые распространенные проблемы при сортировке данных в Excel

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

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

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

Не идентифицируя заголовки столбцов

Предположим, у вас есть набор данных, как показано ниже:

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

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

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

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

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

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

Отсутствие пустых строк / столбцов

Эта проблема сортировки немного сложнее - и встречается гораздо чаще, чем вы можете себе представить.

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

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

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

Как убедиться, что вы не совершаете эту ошибку сортировки?

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

Перед сортировкой данных выберите весь набор данных.

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

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

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

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

Частичная сортировка (по фамилии)

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

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

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

Ниже приведена формула, которая может дать мне фамилию:

= ПРАВО (B2; LEN (B2) -FIND (""; B2))

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

Теперь вы можете использовать столбец «Фамилия» для сортировки этих данных.

После этого вы можете удалить столбец с фамилией или скрыть его.

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

Это всего лишь один пример сортировки на основе частичных данных. Другие примеры могут включать сортировку по городам в адресе или идентификатору сотрудника на основе кодов отделов.

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

Другие примеры сортировки (с использованием формулы и VBA)

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

Автоматическая сортировка по формуле

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

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

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

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

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

Сортировка с использованием VBA

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

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

Вот подробное руководство, в котором я расскажу, как сортировать данные с помощью VBA и создавать что-то, как показано выше.

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

Надеюсь, вы нашли этот урок полезным.