Авторизация

Рубрики блога


Рекомендуем



Последние комментарии

Облако тегов


Устами великих

"Странный этот мир, где двое смотрят на одно и то же, а видят полностью противоположное." © Агата Кристи

Мы Вконтакте

MS Office и VBA Рубрика содержит интересные решения, малоизвестные функции и возможности, надстройки и макросы, в общем, все то, что может сделать вашу работу в пакете программ MS Office (в первую очередь - Excel, Word, Access) более эффективной.
23
Сен

Фильтр по цвету в Excel 2003

рейтинг материал 0.0 (0) | количество просмотров 8200 | количество коментариев 0
Уже, кажется, так давно работаю с Excel 2007 и выше, что иногда возвращаясь по необходимости к древнему Excel 2003, удивляешься почему там так многого нет... Одной из таких вещей является фильтр по цвету ячейки.
Download source

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

Идея

Чтобы все-таки воспользоваться возможностями ПК в анализе с помощью фильтрации цветов даже в Excel 2003 можно применить совсем маленькую пользовательскую функцию - ColorIndex.


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

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

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

Как это заставить работать?)

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

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

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

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

А что если?.. хочется фильтр по цвету текста.

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


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

А функция соответственно будет называться ColorIndexF.

PS К минусам этих функций можно отнести только:

1. Они не могут определить цвета используемые в условном форматировании, только те цвета, которые "проставленны" вручную.

2. Если надумаете использовать эту функцию как вставку в макросе, имейте ввиду, что она не пересчитывается автоматически, придется заставить эксель пересчитать значение ячеек.

Download source
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]