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

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

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

color-sort1.png

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

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

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

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

Public Function ColorIndex(Cell As Range)    
    ColorIndex = Cell.Interior.ColorIndex
End Function

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

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

color-sort2.png

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

Public Function ColorIndex(Cell As Range)    
    ColorIndex = Cell.Font.ColorIndex
End Function

P.S.

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

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

Ссылки по теме

 



Наталия
10.11.2012 11:37:28
Добрый день,
Я попробовала использовать функцию сортировки цвета, но скорее всего сделала что-то не так.
У меня большая таблица с текстом по строкам 2-х цветов, зеленый и черный, их нужно отсортировать зеленые к зеленым и черные к черным.
Я добавила столбец и вставила функцию colorindex, выделила ближайшую ячейку, например с зеленым цветом, потом скопировала эту функцию на весь столбец, но значение по всему столбцу одно и тоже, -4142, т.е. функция не различает зеленых и черных ячеек. Что я сделала неправлильно?
10.11.2012 11:41:58
У вас ячейки окрашены не вручную, а условным форматированием, скорее всего. Цвет условного форматирования эта функция вытащить не может.
Лейла
10.11.2012 11:38:43
Эта функция мне очень нравится... Я очень часто использую цветовую кодировку, работаю с большими базами данных и списками. Вы мне очень помогли..
NaTaLi
10.11.2012 11:39:54
отличная функция, спасибо! Но вот только одна проблема:
макрос не сохраняется в самом Экселе, а только в текущей книге (((....
при открытии новой книги эта функция становится недоступной.... Подскажите, может что не так сделала?
Спасибо!.
10.11.2012 11:43:16
Это нормально. Ее нужно копировать в каждую книгу, где вы собираетесь эту функцию использовать. Либо добавить в личную книгу макросов - см. здесь
MEP
10.02.2017 07:27:56
Рассмотрите пожалуйста задачу фильтра по цвету, созданному при помощи условного форматирования еще и в сводной таблице.:D  
30.07.2018 12:12:03
Доброго дня.
Штука интересная но не удобная:
- во-первых, не работает с условным форматированием;
- во-вторых, не фильтрует по цветам, то есть сначала все ячейки одного цвета, затем другого и т.д.
Нашел в инете гораздо более простое и эффективное решение, с применением обычной сортировки:
http://exceltable.com/filtr-sortirovka/filtr-i-sortirovka-po-cvetu
Наверх