Сортировка и фильтр в excel как сделать

Добавил пользователь Валентин П.
Обновлено: 06.09.2024

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

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

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

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

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

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

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

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Сортировка и фильтр по цвету с помощью функций

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

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

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

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

Вариант 1: Быстрая сортировка в Excel

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

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

Изменения вступают в силу сразу же. Нажмите Ctrl + Z для отмены действия, если что-то выполнено неправильно.

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

Вариант 2: Настраиваемая сортировка

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

Выберите вариант с расширением или сохранением диапазона, если предварительно выделили столбец.

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

Выберите столбец для сортировки из первого выпадающего списка.

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

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

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

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

Вариант 3: Функция НАИБОЛЬШИЙ или НАИМЕНЬШИЙ

Последний вариант – единственный динамический, то есть после внесения изменений в клетках сортировка автоматически перестроится и ее последовательность будет правильной. Как вы уже поняли, для этого понадобится использовать функцию НАИБОЛЬШИЙ или НАИМЕНЬШИЙ.

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

Далее в скобках укажите (C:C;СТРОКА(C1)), поменяв буквы на номер столбца сортировки.

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

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

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

Работа с большими таблицами для современных реалий является стандартной процедурой. Приходится обрабатывать большие массивы данных. Следовательно, важно иметь под рукой подходящие инструменты. Иногда нужно упорядочить что-либо по определённому критерию. Кроме того, не всегда вся информация является полезной и её следует фильтровать. Со всем этим помогает справляться специализированный софт MS Office. Статья посвящена вопросу о том, как выполняется сортировка и фильтрация данных в Excel.

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

Простая сортировка данных

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

Алгоритм выглядит примерно следующим образом:

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

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

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

Выполнение настраиваемой сортировки

Сортировка и фильтрация данных в Excel может иметь более сложный вид. Это обусловлено потребностями пользователей. О чём в данном случае может идти речь? Представьте ситуацию, когда выполняется сортировка по одному значению. К примеру, имеются сразу две позиции с одинаковыми фамилиями. Но в каком порядке тогда они расположатся? В произвольном, так как нет ориентации на другие данные.

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

Реализация настройки

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

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

Дополнительные возможности

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

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

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

Работа с фильтром

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

Переходя к алгоритму, всё будет выглядеть таким образом:

Усложнение фильтрации

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

Устранение фильтра

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

Работа с умной таблицей

Обратите внимание. Варианты отличаются только визуально. Функционал у каждого из стилей остаётся стандартным.

Реализация фильтрации

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

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

Заключение

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

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

В Excel предусмотрено три типа фильтров:

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

Автофильтр

Выделить одну ячейку из диапазона данных.
На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
Щелкнуть по кнопке Фильтр [Filter] .

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

Выбрать условие фильтрации.

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

При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

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

Отмена фильтрации

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

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

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Выделить срез.
На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

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

Задание условий фильтрации

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

Читайте также: