Сортировка диапазона по цвету
Способ 1. Если у вас Excel 2007 или новее...
Тут все просто. Начиная с 2007-й версии в Excel добавили сортировку и фильтрацию по цвету заливки и по цвету шрифта как штатную функцию. Проще всего до них добраться через стандартный автофильтр:
Из минусов только невозможность фильтровать сразу по нескольким цветам.
Способ 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). В качестве аргумента укажите ячейку, цвет заливки которой хотите получить в виде цифрового кода.
Применительно к спискам, такая функция позволит легко сортировать ячейки по цвету заливки:
Если вам нужно вытаскивать не код цвета заливки, а код цвета шрифта, то функция слегка изменится:
Public Function ColorIndex(Cell As Range)
ColorIndex = Cell.Font.ColorIndex
End Function
P.S.
Наша функция ColorIndex, к сожалению, имеет пару недостатков:
- С ее помощью нельзя получить цвет, который ячейка имеет при использовании условного форматирования
- Она не пересчитывается автоматически при изменении цвета одной из ячеек, поскольку Excel не считает изменение цвета редактированием содержимого ячейки и не запускает автоматического пересчета листа. Это нужно сделать самому, нажав Ctrl+Alt+F9, либо дописав к нашей функции в каждой ячейке вот такую добавку:
=ColorIndex(A2)+СЕГОДНЯ()*0
чтобы содержимое ячейка пересчитывалась автоматически при каждом пересчете листа.
Ссылки по теме
- Сортировка по цвету одной кнопкой (надстройка PLEX)
- Суммирование ячеек с определенным цветом заливки или цветом шрифта (надстройка PLEX)
- Что такое макрос, куда вставлять код макроса на VBA, как их использовать
Я попробовала использовать функцию сортировки цвета, но скорее всего сделала что-то не так.
У меня большая таблица с текстом по строкам 2-х цветов, зеленый и черный, их нужно отсортировать зеленые к зеленым и черные к черным.
Я добавила столбец и вставила функцию colorindex, выделила ближайшую ячейку, например с зеленым цветом, потом скопировала эту функцию на весь столбец, но значение по всему столбцу одно и тоже, -4142, т.е. функция не различает зеленых и черных ячеек. Что я сделала неправлильно?
макрос не сохраняется в самом Экселе, а только в текущей книге (((....
при открытии новой книги эта функция становится недоступной.... Подскажите, может что не так сделала?
Спасибо!.