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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

Способ 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
  чтобы содержимое ячейка пересчитывалась автоматически при каждом пересчете листа.

Источник

Cоветы и руководства по Google Таблицам

Мастер Google Таблиц с полезными советами и руководствами

Как сортировать по цвету в Google Таблицах (простое пошаговое руководство)

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

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

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

Сортировать по цвету фона ячейки

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

Ниже приведены шаги для сортировки по цвету ячеек в Google Таблицах:

• Выберите любую ячейку в данных (или выберите весь набор данных)
• В меню выберите пункт «Данные».
• В появившихся опциях нажмите «Создать фильтр». Это применит фильтр к верхней строке в наборе данных.
• Щелкните значок фильтра в любом из заголовков столбцов (где столбец имеет цвет, на основе которого вы хотите отсортировать)
• Наведите курсор на параметр «Сортировать по цвету», а затем наведите его на параметр «Цвет заливки». Это покажет вам все цвета, которые использовались в наборе данных.
• Щелкните цвет, по которому вы хотите выполнить сортировку. В этом примере я выбрал «светло-желтый 2» цвет.

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

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

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

Но что, если у вас в наборе данных более одного цвета (как показано ниже):

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

Ниже приведены шаги для сортировки по нескольким цветам в Google Таблицах:

• Выберите любую ячейку в данных (или выберите весь набор данных)
• В меню выберите пункт «Данные».
• В появившихся опциях нажмите «Создать фильтр». Это применит фильтр к верхней строке в наборе данных.
• Щелкните значок фильтра в любом из заголовков столбцов (где столбец имеет цвет, на основе которого вы хотите отсортировать)
• Наведите курсор на параметр «Сортировать по цвету», а затем наведите его на параметр «Цвет заливки». Это покажет вам все цвета, которые использовались в наборе данных.
• Нажмите на вариант цвета «светло-желтый 2». Это отсортирует набор данных, в котором все строки с желтым цветом находятся вверху.
• Теперь снова нажмите значок фильтра в любом заголовке, наведите курсор на «Сортировать по цвету», затем на «Цвет заливки», а затем нажмите на вариант цвета «светло-зеленый 2».

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

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

Сортировать по цвету текста в ячейке (цвету шрифта)

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

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

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

• Выберите любую ячейку в данных
• В меню выберите пункт «Данные».
• Щелкните Создать фильтр. Это включит фильтры для верхней строки набора данных.
• Щелкните значок фильтра в любом из заголовков столбцов.
• Наведите курсор на параметр «Сортировать по цвету», а затем наведите его на параметр «Цвет заливки». Это покажет вам все цвета, которые использовались в наборе данных.
• Выберите цвет, для которого вы хотите, чтобы все ячейки были вверху. В этом примере я выбираю красный

Вышеупомянутые шаги сортируют выбранные цветные ячейки вверху.

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

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

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

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

Хитрость здесь заключается в том, чтобы добавить вспомогательный столбец (слева или справа от набора данных) и пронумеровать их с шагом 1 (например, 1,2,3…), как показано ниже:

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

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

Вот способы сортировки по цвету в Google Таблицах (будь то цвет ячейки или цвет шрифта текста).

Источник