КАК: Как сортировать данные в Excel — 2024

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

1. Сортировка одной колонки с использованиемСортировка и фильтр или горячие клавиши
2. Сортировка нескольких столбцов
3. Сортировать по датам или времени
4. Сортировка по дням недели, месяцам или другим пользовательским спискам
5. Сортировать по строкам — переупорядочить столбцы

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

Как сортировать и фильтровать данные в Excel

1. Выделите диапазон сортируемых ячеек.
2. Нажми на Главная вкладку ленты.
3. Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
4. Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
5. Под колонка заголовок в диалоговом окне, выберите название из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
6. Сортировать по параметр остается установленным для Ценности — поскольку сортировка основана на фактических данных в таблице.
7. Под Порядок сортировки заголовок, выберите От Z до A в раскрывающемся списке для сортировки данных Name в порядке убывания.
8. В верхней части диалогового окна нажмите Добавить уровень чтобы добавить второй вариант сортировки.
9. Для второго ключа сортировки, под колонка заголовок, выберите Возраст из раскрывающегося списка для сортировки записей с дублирующимися именами столбцом «Возраст».
10. Под Порядок сортировки заголовок, выберите Самый большой и наименьший из раскрывающегося списка для сортировки данных возраста в порядке убывания.
11. Нажмите Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.

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

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

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

Установка фильтра по дате и формату

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

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

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

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

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

Теперь все эти варианты следует рассмотреть отдельно.

По нескольким столбцам

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

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

• Сначала откройте меню под названием «Настраиваемая сортировка». Здесь назначьте первый критерий для процедуры. А именно столбец.
• Теперь кликните по клавише «Добавить уровень».
• После этого на экране отобразятся окна для ввода данных последующего условия для сортировки. Их нужно заполнить.

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

Как сортировать строки

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

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

Ничего сложного. Буквально несколько кликов, и задача выполнена.

Случайный способ сортировки

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

Но есть специальная функция под названием СЛЧИС, которая решает эту проблему инструмента от Microsoft.

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

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

Метод динамической сортировки

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

Для этого применяется соответствующая формула. Рассмотреть это нужно на конкретном примере.

Если же необходимо сделать сортировку на убывание, тогда вместо НАИМЕНЬШИЙ прописывается НАИБОЛЬШИЙ.

Ничего принципиально сложного. С такой задачей легко справится даже новичок.

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

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

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

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

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

Вариант 3: Настройка фильтрации

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

Использование данной функции происходит по следующей инструкции:

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

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

1. Напротив названия нужного столбца нажмите по иконке фильтра. Наличие таковой означает, что к данному столбцу уже применён фильтр.
2. Здесь можно внести изменения в настройки, например, выбрать другую дату и/или другого сотрудника. После нажатия на «Ок» перед вами будут отображены данные указанного сотрудника.
3. Если вам требуется совсем удалить фильтр, жмите на кнопку «Удалить фильтр с (название столбца)».
4. При условии, что в таблице много фильтров, сбрасывать каждый вручную будут слишком долго по времени. В качестве альтернативы просто нажмите по кнопке «Сортировка и фильтр», а там выберите пункт «Очистить».

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

Во-вторых, вышеописанную формулу массива нужно будет протянуть вниз с запасом – с расчетом на вводимые в будущем дополнительные данные. При этом формула массива начнет выдавать ошибку #ЧИСЛО на незаполненных пока ячейках. Чтобы ее перехватить, можно использовать функцию ЕСЛИОШИБКА, которую нужно дописать «вокруг» нашей формулы массива:

Сортировка данных формулой в excel

• Пункты настроек «От А до Я» и «От Я до А» указываются в том случае, если в ячейках в первую очередь идёт текстовая информация;
• Если в ячейках в первую очередь расположены числовые значения, то пункты меняются на «От минимальному к максимальному» и «От максимального к минимальному»;
• Если в ячейках присутствуют даты, то пункты будут изменены на «Сортировка от старых к новым» и «Сортировка от новых к старым».

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

2. Как НЕ нужно сортировать данные в Excel

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

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

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

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

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

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

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

На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.

Таким образом, при сортировке данных есть два ключевых «НЕ»:

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

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

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

1. Сортировка данных всего в несколько кликов
2. Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
3. Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.

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

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

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

1. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
2. Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:

1. Имя столбца для сортировки.
2. Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
3. Порядок сортировки (по убыванию или по возрастанию).

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

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

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

1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
2. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.

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

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

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

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

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

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

Функция цвета заливки ячейки на VBA

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

Во-вторых, вышеописанную формулу массива нужно будет протянуть вниз с запасом – с расчетом на вводимые в будущем дополнительные данные. При этом формула массива начнет выдавать ошибку #ЧИСЛО на незаполненных пока ячейках. Чтобы ее перехватить, можно использовать функцию ЕСЛИОШИБКА, которую нужно дописать «вокруг» нашей формулы массива:

Фильтр по цвету ячеек

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

1. Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
2. Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
3. Из всплывающего подменю выберите зеленый цвет.

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

Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:

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

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

Решение1

• Они не работают с ячейками, в которых заливка определяется условным форматированием;
• При изменении раскраски ячейки в Excel формулы автоматически не пересчитываются, в связи с этим пересчет нужно сделать самостоятельно (Shift + F9 для пересчета формул только на активном листе, F9 — для всей книги).

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