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

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

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

А еще можно создавать собственные сортировочные списки и сортировать по ним. Но об этом позже.

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

Итак, начнём!

Функция «Сортировка»

Итак, есть несколько вариантов запуска сортировки.

Кнопка функции в разделе «Сортировка и фильтр»

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

2 значка рядом позволяют быстро отсортировать данные от А до Я и наоборот.

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

Тоже самое и с численными значениями.

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

Окно функции «Сортировка»

Давайте запустим саму функцию.

Открылось следующее окно:

Это окно — самый гибкий вариант настройки сортировки. Да есть и другие способы отсортировать какие-либо данные, но самый мощный инструмент это сама функция. Здесь можно задать разные параметры и даже создать свои собственные.

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

Сортировка данных вместе с функцией «Фильтр»

Допустим, у вас есть какая-либо табличка, к которой уже применена функция «Фильтр» (её мы рассматривали ранее).

Например, наша табличка выглядит так:

Мы можем нажать на стрелочку, идущую вниз, откроется окошко функции «Фильтр», но там также можно делать сортировку.

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

Сортировка с помощью мыши

Еще можно сортировать данные используя мышь.

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

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

Но все же, самым действенным методом будет использование самой функции «Сортировка».

Итак, давайте рассмотрим, в каких ситуациях, что будет удобнее.

Сортировка общий раздел

Итак, запомните навсегда — чтобы сортировать данные вам, всегда нужно их ВЫДЕЛЯТЬ.

От А до Я и наоборот

Допустим, у нас есть такая табличка:

Давайте отсортируем данные в ней по именам продавцов:

• Выделяем табличку и открываем функцию «Сортировка»;

• Не забывайте ставить галочку «Мои данные содержат заголовки» если в вашей табличке они есть;

• В первом параметре выбираем, по какому столбику вашей табличку будем сортировать;

• Во втором параметре выбираем «Значения»;

• В третьем нужный порядок. В нашем случае, от А до Я;

• Подтверждаем.

Итак, результат:

Для таких ситуаций достаточно использовать быстрые кнопки сортировки от А до Я

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

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

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

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

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

По числам

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

Допустим у нас есть та же табличка, давайте отсортируем всех продавцов по количеству продаж:

Как это сделать?

Пошаговая инструкция:

• Откроем функцию «Сортировка»;

• В первом параметре выбираем столбик «Продажи»

• Во втором — «Значения»;

• И в третьем — «по возрастанию»;

• Подтверждаем.

Результат вы можете видеть на картинке ниже:

По дате

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

Допустим, у нас та же табличка:

Теперь давайте отсортируем все по датам.

Как это сделать?

Пошаговая инструкция:

• Открываем функцию «Сортировка»;

• В первом параметре — «Дата»;

• Во втором — «Значения»;

• В третьем — «по возрастанию»;

• Подтверждаем.

Готово!

По цвету или шрифту

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

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

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

Допустим у нас такая табличка:

Давайте отсортируем данные по оранжевому цвету:

Как это сделать?

Пошаговая инструкция:

• Открываем функцию «Сортировка»;

• В первом параметре выбираем любой столбец;

• Во втором — «Цвет ячейки»;

• А в третьем выберете цвет, который будет отличимым элементом;

• В четвертом параметре укажите где будет находиться этот цвет после сортировки;

• Подтвердите.

Итак, результат на картинке ниже:

Точно также можно сортировать и по шрифту.

Сортировка по двум и более критериям

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

А в добавок к этому у вас табличка на 1000+ строк и 50+ столбцов?

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

Допустим, у нас та же табличка, только отсортировать вам нужно уже по двум критериям:

• Регион

• Продажи

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

Итак, как же это сделать?

Пошаговая инструкция:

• Откройте функцию «Сортировка»;

• Сначала создадим первое правило:

• Первый параметр — «Регион»;

• Второй — «Значения»;

• Третий — «От А до Я»;

• Теперь щелкните на «Добавить уровень»;

• Здесь уже создаем второе правило:

• Первый параметр — «Продажи»;

• Второй — «Значения»;

• Третий — «По убыванию»;

• Подтвердите.

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

Создаем свой список сортировки

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

К примеру, у нас та же табличка:

Если мы выполним сортировку строк по регионам от А до Я, результат может быть совсем не тот, что нужен:

Я покажу вам как создать свой список сортировки.

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

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

• Щелкните «Файл»;

• «Параметры»;

• Вкладка «Дополнительно»;

• Промотайте вниз и найдете раздел «Общие» -> «Изменить списки…»;

• А теперь создайте свой список, просто введите порядок, например, регионов или городов. Введите его так, как он должен будет сортироваться.

• Щелкните «Import»;

• Подтвердите.

Итак, вы создали свой собственный список сортировки. Теперь вы можете вообще все файлы и таблички Excel сортировать по нему.

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

Сортировка с помощью этого списка

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

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

Как это сделать?

Пошаговая инструкция:

• Откройте функцию «Сортировка»;

• В первом параметре — «Регион»;

• Во втором — «Значения»;

• В третьем — «Настраиваемый список…»;

• В открывшемся окне выберите ваш недавно созданный;

• Подтвердите;

• Подтвердите.

Готово, вот результат в нашем случае:

Список необязательно создавать, как делали мы в прошлом случае, можно сделать это прямо в окне «настраиваемый список…»;

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

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

А теперь давайте рассмотрим пример сортировки столбцов, а не строк.

Допустим, мы имеем такой набор данных:

И да, действительно, мало кто в 2021 году создает такие отчёты. Но все же бывает и такое, важно уметь работать с многими типами таблиц.

Отсортируем «Регион» слева направо!

Как это сделать?

Пошаговая инструкция:

• Откроем функцию «Сортировка»;
• Щелкнем на «Параметры…»;

• Укажем опцию «столбцы диапазоны»;

• Подтвердите;

• В первом параметре — «Region»;

• Во втором — «Значения»;

• В третьем «От А до Я»;

• Применяем.

Готово, результат на картинке ниже:

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

Как учитывать регистр при сортировке

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

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

Допустим, у нас есть такая табличка и нам нужно отсортировать строки по региону, учитывая при этом регистр:

Как это сделать?

Пошаговая инструкция:

• Открываем функцию «Сортировка»;

• Щелкните «Параметры…»;

• Активируйте «Учитывать регистр»;

• Вот собственно и все, далее просто создаем правило сортировки.

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

Результат:

Как отменить сортировку

Бывают такие ситуации, когда сортировка перестала быть нужна по каким-то причинам. В таком случае, вам нужно знать — как удалить её.

Если вы сделали её недавно, вы можете просто нажать комбинацию горячих клавиш CTRL + Z и она отменится. Но что если вы сделали её довольно давно?

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

2 варианта «откатиться» к исходной сортировке данных:

• Делаем копию файла без сортировки;

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

Я покажу вам, как это делать.

Допустим, у нас есть такая табличка:

Давайте добавим столбик порядковых номеров строк, при исходной сортировке.

Теперь отсортируем данные:

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

Проблемы при сортировке

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

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

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

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

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

Вы можете видеть эту проблему на изображении ниже:

Поэтому лучше всего использовать саму функцию «Сортировка».

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

Excel не учитывает пустые строки (или столбцы) при сортировке

Проблема довольно масштабная.

Допустим, у нас есть такая табличка:

Вы можете заметить, что строка с порядковым номером 6 скрыта.

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

Можно увидеть это на изображении ниже:

Эту проблему легко не заметить.

Как не допустить такой ошибки?

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

Проверить набор данных можно просто выделив их (CTRL + A) и посмотрев, все ли выделено или что-то отсутствует.

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

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

Итак, теперь с помощью формулы отсортируем эту табличку по фамилии продавца:

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

Вот формула деления:

=ПРАВСИМБ(B2;ДЛСТР(B2)-НАЙТИ(" ";B2))

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

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

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