Как сортировать в Excel (по строке, столбцу, цвету, дате и номеру)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

鍵盤 快捷 鍵– Если вам нужно часто сортировать данные в Excel, я рекомендую изучить сочетания клавиш, открывающие диалоговое окно сортировки.этоALT + А + С + С

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Это реально.

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

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

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

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

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

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

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

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

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

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

Вот шаги для численной сортировки этих данных:

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

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

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

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

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

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

То же самое касается времени в Excel.

Например, число 44196.125 означает 2020 декабря 12 г., 31:3.В то время как целая часть числа представляет весь день, дробная часть представляет время.

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот шаги, отсортированные по цвету:

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

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

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

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

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

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

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

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

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

1. Регион
2. Продажа

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

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

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

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

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

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

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

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

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

Теперь, если я отсортирую его в алфавитном порядке по регионам, у меня будет два варианта — от А до Я или от Я до А.Вот что я получаю, когда сортирую эти данные в алфавитном порядке от А до Я, используя столбец диапазона.

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

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

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

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

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

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

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

Шаги для создания пользовательского списка в Excel:

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

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

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

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

Сортировка с помощью пользовательского списка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Получить исходный порядок сортировки

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

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

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

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

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

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

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

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

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

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

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

Простой. .не так ли?

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

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

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

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

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

заголовки столбцов не распознаются

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

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

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

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

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

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

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

Пустая строка/столбец не распознана

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот формула, которая даст мне мою фамилию:

=ВПРАВО(B2,ДЛСТР(B2)-НАЙТИ(" ",B2))

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

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

После этого вы можете удалить столбец «Фамилия» или скрыть его.

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

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

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

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

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

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

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

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

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

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

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

Сортировать с помощью VBA

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

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

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

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

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