Archie Goodwin
MS Office и VBA Рубрика содержит достойные внимания решения, малоизвестные функции и способности, надстройки и макросы, в общем, все то, что в состоянии сделать вашу работу в пакете программ MS Office (в первую очередь — Excel, Word, Access) наиболее действенной.
Фильтр по цвету в Excel 2003
0.0 (0) | 13382 | 0
Те, кто имеют дело с анализом массивов данных в Excel, весьма нередко для удобства подкрашивают нужные ячейки в различные цвета, чтоб структурно выделить какие-то моменты либо просто выделить только то, что принципиально. Логичным окончанием таковых манипуляций является сортировка либо фильтрация по выделенным цветам. К несчастью в Excel 2003 этот момент не реализован, в отличии от Excel 2007 и выше, где вероятна и сортировка, и фильтр по цвету в пару кликов через инструментарий Автофильтра.
Чтоб все-же пользоваться способностями ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем) в анализе при помощи фильтрации цветов даже в Excel 2003 можно применить совершенно небольшую пользовательскую функцию — ColorIndex.
Сущность работы функции сводится к тому, что она описывает цифровое обозначение цвета выделенной ячейки:
А уже по цифровому обозначению цвета ячейки мы можем фильтровать фактически как угодно:
Как это вынудить работать?)
Для этого откройте редактор 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. Если надумаете употреблять эту функцию как вставку в макросе, имейте ввиду, что она не пересчитывается автоматом, придется вынудить эксель перечесть значение ячеек.
О веб-сайте
«Понемногу обо всем и все, о немногом» — конкретно таковой слоган, по-видимому, является лучшим определением темы блога. Тут пишу о том, что для меня любопытно либо принципиально, фактически, потому разброс тем весьма широкий – от раздумий на философские темы и забавных историй, до определенных инструкций либо анализа событий.
Правда, кроме общих тем, которые есть практически на любом личном блоге, стоит выделить спец рубрики блога, которые будут полезны и увлекательны веб-мастерам, программерам, дизайнерам, офисным работникам и юзерам ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем), желающим повысить свои способности и уровень познаний. Подробнее о спецрубриках
Записки веб-мастера – рубрика, которая призвана собрать коллекцию нужных скриптов и авторских решений, увлекательных особенностей и эталонов верстки, решение вопросцев юзабилити и функционала, нужных ресурсов и программ.
Вопросец дизайна – это животрепещущие тренды, пошаговые и видео-уроки в фотошопе, нужные плагины для фоторедакторов, векторные и PSD исходники, PNG иконки и GIF анимации, кириллические шрифты с зарубками и без засечек, заливки (паттерны) и градиенты.
Мой ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем) – любая статья в данной рубрике ориентирована на то, чтоб выяснить собственный комп лучше. Тут можно будет почитать о системных действиях и редактировании системного реестра, о методах защитить личные данные и комп в целом, о настройке локальной сети и подключениях к сети веб, обзор ряда программ, которые делают работу за компом удобнее, резвее и приятнее.
MS Office и VBA – эта рубрика содержит достойные внимания решения, малоизвестные функции и способности, надстройки и макросы, в общем, все то, что в состоянии сделать вашу работу в пакете программ MS Office (в первую очередь — Excel, Word, Access, PowerPoint) наиболее действенной.
Остальные офисные приложения – рубрика о программках для ведения учета (конфигурации, платформы, наружные отчеты для 1C), сдачи отчетности (MeDoc, БестЗвіт) и статистического анализа данных (SPSS), также тут можно отыскать обзоры программного обеспечения для работы с периферийными устройствами. Свернуть
Как создать фильтр по цвету в Excel при помощи макросов VBA
Результат: узнайте, как использовать фильтры цвета и значков в VBA. Включает примеры фильтрации по цвету шрифта, цвету заливки и значкам условного форматирования.
Уровень мастерства: Средний
Скачать файл
Файл Excel, содержащий код, можно скачать ниже. Этот файл содержит код для фильтрации разных типов данных и типов фильтров. Пожалуйста, ознакомьтесь с моей статьей Фильтрация сводной таблицы либо среза по самой крайней дате либо периоду для наиболее подробной инфы.
VBA AutoFilters Guide.xlsm (100.5 KB)
Фильтры цвета и значков в Excel
Фильтры для шрифта и цвета заливки были введены в Excel 2010. Это дозволяет нам стремительно фильтровать столбцы на базе форматирования ячеек, который можно использовать вручную либо при помощи условного форматирования.
Меню «Фильтр по цвету» возникает, когда столбец содержит какой-нибудь шрифт либо заливку. Оно также возникает, когда столбец содержит значки, сделанные условным форматированием. Мы также можем употреблять VBA для внедрения этих фильтров.
Давайте поглядим на некие примеры кода для разных цветовых фильтров.
Фильтры цвета и значков в VBA
Фильтры и типы данных
Характеристики раскрывающегося меню фильтра меняются в зависимости от типа данных в столбце. У нас есть различные фильтры для текста, чисел, дат и цветов. Это делает МНОГО разных композиций операторов и критериев для всякого типа фильтра.
Я сделал отдельные статьи для всякого из этих типов фильтров. Статьи содержат пояснения и примеры кода VBA.
- Как очистить фильтры при помощи VBA
- Как отфильтровать пустые и непустые ячейки
- Как фильтровать текст при помощи VBA
- Как отфильтровать даты по VBA
- Как фильтровать числа при помощи VBA
Файл в разделе загрузок выше содержит все эти примеры кода в одном месте. Вы сможете добавить его в свою личную книжку макросов и употреблять макросы в собственных проектах.
Пожалуйста, оставьте комментарий ниже с хоть какими вопросцами либо предложениями. Спасибо!
Как в excel создать сортировку по цвету
Пример данных, которые нужно отсортировать относительно цвета заливки ячеек изображен ниже на рисунке:
Чтоб расположить строчки в последовательности: зеленоватый, желтоватый, красноватый, а позже без цвета – выполним последующий ряд действий:
- Щелкните на всякую ячейку в области спектра данных и изберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
- Удостоверьтесь, что отмечена галочкой функция «Мои данные содержат заглавия», а опосля чего же из первого выпадающего перечня изберите значение «Наименование». В секции «Сортировка» изберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на клавишу зеленоватого квадратика.
- Нажмите на клавишу «Копировать уровень» и в сей раз укажите желтоватый цвет в секции «Порядок».
- Аналогичным методом устанавливаем новое условие для сортировки относительно красноватого цвета заливки ячеек. И нажмите на клавишу ОК.
Ожидаемый итог изображен ниже на рисунке:
Аналогичным методом можно сортировать данные по цвету шрифта либо типу значка которые содержат ячейки. Для этого довольно лишь указать соответственный аспект в секции «Сортировка» диалогового окна опции критерий.
В данной статье
Общие сведения о сортировке и фильтрации данных по цвету и набору значков
Действенное внедрение цвета при анализе данных
Выбор более пригодных цветов
Несколько примеров с пошаговыми объяснениями
Фильтр и сортировка ячеек по цвету в Excel
Разглядим главные методы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.
Для начала вспомним, в чем все-таки полезность от сортировки и фильтрации данных в Excel, и для чего она совершенно нужна?
При проведении анализа данных сортировка и фильтрация помогают структурировать, упорядочивать данные либо создавать поиск.
Например, ячейки либо спектры данных можно окрашивать в разные цвета с целью выделения определенных данных, которые в последствии можно добавочно проанализировать.
При всем этом фильтрация дозволяет показать лишь те данные, которые удовлетворяют данным аспектам, и соответственно, скрыть те данные, которые не подступают по условию.
Сортировка же указывает все данные, но упорядочивает их согласно данным аспектам.
В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию либо убыванию (для чисел), но давайте познакомимся с еще одним вариантом сортировки — по цвету, и разглядим 2 метода, дозволяющие сортировать и использовать фильтр к данным:
- Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
- Применение пользовательских функций.
Текстовый фильтр в Эксель
Разглядим, как отфильтровать в Excel ячейки с определенным текстом. Простой метод – это, по аналогии с прошедшим примером, ввести подходящий текст (либо его часть) в поиске.
Но, можно и наиболее гибко настраивать отбор. Если в окне фильтра надавить « Текстовые фильтры », то в контекстном меню покажется выбор метода сравнения: равно, не равно, начинается с, завершается на, содержит, не содержит.
К примеру, необходимо избрать людей, которых не зовут Богдан. Выберем вариант «не содержит» и запишем ему аспект « Богдан ». Поставим пробелы перед именованием и опосля. По другому, к примеру, Богданов Егор Егорович тоже попадет под фильтр, хотя его не зовут Богданом:
Сортировка по собственному списку
Большая часть юзеров понимает, как отсортировать строчки в Excel по алфавиту либо возрастанию чисел. Но время от времени необходимо расставить строчки в данном порядке, который не непременно соответствует алфавиту либо растущей последовательности. Это быть может иерархия частей, городка и страны в комфортной последовательности и т.п. В общем то, что имеет свою логику.
Самый обычный (а, означает, достойный внимания) метод заключается в том, чтоб в столбце рядом проставить числа в согласовании с хотимыми порядком строк, а потом выполнить сортировку таблицы по этому столбцу.
Метод неплох, но просит сотворения доп временного столбца. При нередком повторении операции будет отымать время. Если приходится сортировать одни и те же данные, то можно сделать особый перечень, на базе которого потом и произойдет сортировка. Это этот же перечень, который употребляется в автозаполнении ячеек.
Заходим в Файл – Характеристики – Добавочно – Общие – Поменять списки…
Тут создаем вручную либо импортируем перечень, отсортированный в подходящем порядке.
Сейчас в окне сортировки в поле Порядок необходимо избрать Настраиваемый перечень…
И в последующем окне указать подходящий перечень.
Сделанным перечнем можно воспользоваться и в остальных файлах Excel.
Сортировка и фильтр по цвету при помощи функций
Как мы узнали обычным методом отфильтровать данные по нескольким цветам не получится, но это ограничение можно обойти при помощи сотворения доп параметра с цветом заливки (либо текста), по которому дальше и будем сортировать либо фильтровать данные.
Функция цвета заливки ячейки на VBA
Для сотворения пользовательских функций перейдем в редактор Visual Basic (композиция кнопок Alt + F11), сделаем новейший модуль и добавим туда код последующей функции:
Public Function ColorFill ( MyCell As Range )
ColorFill = MyCell . Interior . ColorIndex
Функция ColorFill в качестве значения возвращает числовой код цвета заливки ячейки, она весьма идентична с функцией, которую мы употребляли при подсчете количества и суммирования ячеек по цвету.
Вернемся в Excel и применим новейшую функцию ColorFill — или конкретно введем формулу в ячейку, или вызовем ее при помощи мастера функций (выбрав из группы Определенные юзером).
В доп столбце прописываем код заливки ячейки:
Дальше мы можем сортировать либо использовать фильтр к данным уже по новенькому параметру.
Например, для того, чтоб отфильтровать таблицу по красноватой и зеленоватой заливке ячеек, в фильтре укажем надлежащие каждой заливке числовые коды:
Функция цвета текста ячейки на VBA
В случае если мы желаем проанализировать ячейки по цвету текста, то необходимо будет внести маленькие конфигурации в код функции:
Public Function ColorFont ( MyCell As Range )
ColorFont = MyCell . Font . ColorIndex
Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее внедрения аналогичен примеру рассмотренному выше.
Как создать сортировку по дате
В таблицах, где в хронологическом порядке отражаются определенные операции, их нередко требуется отсортировать по дате проведения. Так как дата это сочетание целых чисел, представленных определенным образом, для правильного воплощения сортировки нужно, чтоб был избран соответственный формат ячеек. Если формат указан некорректно, программка не сумеет распознать значения дат, потому упорядочить их не получится.
Чтоб рассортировать строчки таблицы по дате в редакторе Эксель необходимо выполнить последующую последовательность действий:
- Выделить всякую ячейку в столбце с датами, которые требуется упорядочить.
- Во вкладке «Основная» отыскать клавишу «Сортировка и фильтр», навести на нее курсор и избрать одно из предложенных действий в выпадающем перечне. Это быть может распределение от новейших к старенькым, когда в начале перечня будут находится более поздние даты и привязанные к ним значения, или напротив от старенькых к новеньким.
Фильтр по цвету в Excel
Если для неких ячеек установлен цвет фона либо текста, сможете фильтровать по ним. При всем этом, перечень цветов программка сформирует сама на основании тех форматов, которые заданы в столбце:
