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

REDMOND

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

Способ 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) и скопируйте туда текст простой функции:

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

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

color-sort2.png

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

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

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

Archie Goodwin

Авторизация
Рубрики блога
  • Public / Общие темы
  • Новость + Мнение
  • Размышления
  • Уроки или советы
  • Это интересно
  • Юмор
  • Креатив
  • Рецензия
  • Личность
  • Притчи, истории
  • Новости сайта
  • Special / СпецРубрики
    • Записки вебмастера
    • Вопрос дизайна
    • Мой ПК
    • MS Office и VBA
    • Прочие офисные программы
    • Diary / Личный дневник
      • Дневник
      • Рекомендуем
        Последние комментарии
        Облако тегов
        Устами великих

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

        Реклама

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

        • Главная
        • Специализированные блоги
        • MS Office и VBA

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

        0.0 (0) | 13080 | 0

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

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

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

        REDMOND

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

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

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

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

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

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

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

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

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

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

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

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

        • 1
        • 2
        • 3
        • 4
        • 5
        О сайте

        "Понемногу обо всем и все, о немногом" — именно такой слоган, по-видимому, является наилучшим определением тематики блога. Здесь пишу о том, что для меня интересно или важно, собственно, поэтому разброс тематик очень широк – от размышлений на философские темы и смешных историй, до конкретных инструкций или анализа событий.

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

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

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

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

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

        Прочие офисные программы – рубрика о программах для ведения учета (конфигурации, платформы, внешние отчеты для 1C), сдачи отчетности (MeDoc, БестЗвіт) и статистического анализа данных (SPSS), также здесь можно найти обзоры программного обеспечения для работы с периферийными устройствами. Свернуть

        REDMOND

        Ссылка на основную публикацию