Как в excel сделать сортировку по цвету

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

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

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

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

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

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

  • Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
  • Применение пользовательских функций.

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

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


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


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

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

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


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


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

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

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

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

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

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

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

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

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

Читать еще:  Как в excel разбить фио на три ячейки

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

  1. Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
  2. Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
  3. Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
  4. Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.

Ожидаемый результат изображен ниже на рисунке:

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

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

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

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

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

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

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

Сортировка диапазона по цвету

Способ 1. Если у вас Excel 2007 или новее.

Тут все просто. Начиная с 2007-й версии в Excel добавили сортировку и фильтрацию по цвету заливки и по цвету шрифта как штатную функцию. Проще всего до них добраться через стандартный автофильтр:

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

Способ 2. Если у вас Excel 2003 или старше.

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

Для этого откройте редактор Visual Basic через меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor) , вставьте новый пустой модуль (меню Insert — Module) и скопируйте туда текст простой функции:

Теперь можно закрыть редактор Visual Basic, вернуться в Excel и, выделив любую пустую ячейку, вызвать созданную функцию ColorIndex через меню Вставка — Функция — категория Определенные пользователем (Insert — Function — User defined) . В качестве аргумента укажите ячейку, цвет заливки которой хотите получить в виде цифрового кода.

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

Если вам нужно вытаскивать не код цвета заливки, а код цвета шрифта, то функция слегка изменится:

Наша функция ColorIndex, к сожалению, имеет пару недостатков:

  • С ее помощью нельзя получить цвет, который ячейка имеет при использовании условного форматирования
  • Она не пересчитывается автоматически при изменении цвета одной из ячеек, поскольку Excel не считает изменение цвета редактированием содержимого ячейки и не запускает автоматического пересчета листа. Это нужно сделать самому, нажав Ctrl+Alt+F9, либо дописав к нашей функции в каждой ячейке вот такую добавку:
    =ColorIndex(A2)+СЕГОДНЯ()*0
    чтобы содержимое ячейка пересчитывалась автоматически при каждом пересчете листа.
Читать еще:  Как сохранить диаграмму 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).

  • Чтобы добавить ещё один уровень сортировки с такими же настройками, нажмите кнопку Копировать уровень (Copy Level). Затем в столбце Порядок (Order) выберите второй по порядку цвет. Таким же образом создайте столько уровней сортировки, сколько цветов надо отсортировать в таблице.
  • Нажмите ОК и проверьте в правильном ли порядке расположились строки с данными.
  • В нашей таблице вверх переместились строки со значением Past Due (столбец Delivery), за ними следуют строки со значениями Due in и в конце – строки со значением Delivered. Все в точности, как мы настроили в диалоговом окне Сортировка (Sort).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Как сортировать по цвету в Excel

    В этой заметке разберемся с тем, как сортировать в Excel по цвету ячейки.

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

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

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

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

    Далее указать вариант сортировки (5) — Цвет ячейки или Цвет шрифта. И из выпадающего списка Порядок необходимо выбрать один из цветов (6).

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

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

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

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector