Как сделать сортировку по цвету в excel. Сортировка данных в Excel

Из данной короткой аннотации Вы узнаете, как быстро сортировать по цвету заливки и шрифта ячейки на листе в Excel 2010 и 2013.

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

Сортировка в Excel по цвету заливки ячейки

Сортировка ячеек в Excel по цвету заливки – это одна из простых операций, вровень с подсчётом количества ячеек, суммированием и даже фильтрацией. Не потребуются ни код VBA, ни формулы. Мы просто воспользуемся инвентарем «Настраиваемая сортировка», который находится во всех современных версиях Excel 2013, 2010 и 2007.

1. Выделите свою таблицу либо спектр ячеек.
2. На вкладке Главная (Home) в разделе Редактирование (Editing) нажмите клавишу Сортировка и фильтр (Sort & Filter) и в появившемся меню выберите Настраиваемая сортировка (Custom Sort).
3. В диалоговом окне Сортировка (Sort) настройте характеристики слева направо:
   • Укажите Столбец (Column), по которому необходимо выполнить сортировку (в нашем примере это столбец Delivery);
   • В поле Сортировка (Sort On) выберите Цвет ячейки (Cell Color);
   • Установите цвет ячеек, которые должны быть вверху;
   • В последнем правом выпадающем перечне выберите Сверху (On top).

   

4. Чтобы добавить ещё один уровень сортировки с таковыми же опциями, нажмите клавишу Копировать уровень (Copy Level). Потом в столбце Порядок (Order) выберите 2-ой по порядку цвет. Таковым же образом сделайте столько уровней сортировки, сколько цветов нужно отсортировать в таблице.
5. Нажмите ОК и проверьте в правильном ли порядке расположились строчки с данными.

В нашей таблице ввысь переместились строчки со значением Past Due (столбец Delivery), за ними следуют строчки со значениями Due in и в конце – строчки со значением Delivered. Все в точности, как мы настроили в диалоговом окне Сортировка (Sort).

Совет: Ежели ячейки раскрашены в огромное количество разных цветов – не непременно создавать правило для каждого. Настройте правила сортировки лишь для тех цветов, которыми отмечены вправду принципиальные данные (в нашем примере это строчки со значением Past Due), а все другие оставьте как есть.

Если требуется отобрать ячейки лишь 1-го цвета, то можно пользоваться ещё наиболее скорым методом. Кликните по стрелке автофильтра рядом с заголовком столбца, в котором необходимо произвести сортировку. В открывшемся меню нажмите Сортировка по цвету (Sort by Color) и выберите, какого цвета ячейки должны оказаться вверху, а какие – внизу. Кстати, выбрав в этом меню пункт Пользовательская сортировка (Custom Sort), как показано на рисунке ниже, можно быстро перейти к диалоговому окну Сортировка (Sort).

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

Сортировка ячеек в Excel по цвету шрифта выполняется точно так же, как сортировка по цвету заливки. Опять используем инструмент Настраиваемая сортировка (Custom Sort), который находится на вкладке Главная (Home) в меню Сортировка и фильтр (Sort & Filter), но на этот раз в поле Сортировка (Sort On) избираем Цвет шрифта (Font Color).

Если требуется отобрать ячейки с одним определённым цветом шрифта, то можно опять применять автофильтр Excel:

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

Сортировка ячеек по значку

Настроим, для примера, условное форматирование с внедрением наборов значков по столбцу Qty., где указано количество заказов (см. набросок ниже):

Как видите, мы отметили большие заказы (с количеством наиболее 6) красноватыми значками, средние заказы отмечены жёлтыми значками и самые мелкие – зелёными значками. Ежели необходимо расположить самые принципиальные заказы в начале перечня, воспользуемся инвентарем Настраиваемая сортировка (Custom Sort), как это было описано выше, и настроим сортировку по признаку – Значок ячейки (Cell Icon).

Достаточно указать очерёдность для первых 2-ух (из трёх) значков, и все строчки с зелёными значками автоматом переместятся вниз таблицы.

Как в Excel фильтровать ячейки по цвету заливки

Если необходимо отфильтровать ячейки в данном столбце по цвету заливки, используйте инструмент Фильтр по цвету (Filter by Color), доступный в Excel 2010 и 2013.

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

1. Создайте вспомогательный столбец в конце таблицы либо рядом со столбцом, в котором будете настраивать фильтр. Мы назовём его Filter by color.
2. Во вторую ячейку лишь что добавленного столбца введите формулу:

   Здесь F – это столбец, содержащий разукрашенные ячейки, и по которому необходимо настроить фильтр, а GetCellColor() – пользовательская функция со последующим кодом VBA:

1. Скопируйте эту формулу во все ячейки столбца Filter by color.
2. Примените автофильтр Excel и потом выберите нужные цвета по их кодам в выпадающем меню автофильтра.

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

На этом, пожалуй, всё на сейчас, благодарю за внимание!

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» либо «от Я до А»).  Если данные числовые, их можно отсортировать в порядке возрастания либо убывания. Ежели в спектре данных есть строчка либо столбец, в которых содержатся данные типа время либо дата, их можно отсортировать в прямом либо обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

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

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

Пошаговые действия:

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

1. Выбрать подходящую кнопку: – сортировка по возрастанию или  сортировка по убыванию.

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

Существует и иной удачный метод сортировки данных: щелкнув правой клавишей мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а дальше – требуемый вариант сортировки.

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

Пошаговые действия:

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

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

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

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

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

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

Сортировка по форматированию

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

Пошаговый порядок действий:

1. Щелкнуть по хоть какой ячейки из столбца, по которому будет выполняться сортировка.
2. На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а потом выбрать команду Сортировка [Sort].
3. В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
4. В поле Сортировка [Sort On] из всплывающего меню выбрать аспект сортировки: цвет ячейки, цвет шрифта либо значок ячейки.
5. Поле Порядок [Order] содержит два выпадающих перечня. В первом необходимо выбрать тип аспекта, а во втором – размещение ячеек, отсортированных по данному аспекту (строку Сверху [On Top] или Снизу [On Bottom]).
6. При необходимости добавить еще один аспект сортировки, в окне Сортировка необходимо выбрать кнопку Добавить уровень.

Можно также пользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

1. После выбора сортировки надавить клавишу ОК.