Excel не работает фильтр по цвету excel

Обновлено: 06.01.2025

В Microsoft Office Excel, начиная с версии 2007 года, появилась возможность сортировки и фильтрации ячеек табличного массива по цвету. Такая функция позволяет быстрее ориентироваться в таблице, повышает ее презентабельность и эстетичность. В данной статье будут рассмотрены основные способы фильтрации информации в Excel по цвету.

Особенности фильтрации по цвету

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

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

Как отфильтровать данные по цвету с помощью встроенной в Excel опции

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

Выделить требуемый диапазон ячеек левой клавишей манипулятора и переместиться во вкладку «Главная», находящуюся в верхней панели инструментов программы.
В появившейся области в подразделе Редактирование необходимо найти кнопку «Сортировка и фильтр» и развернуть ее, нажав на стрелочку внизу.

В отобразившемся меню нажать по строчке «Фильтр».

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

После нажатия по стрелке в названии столбика отобразится похожее меню, в котором надо щелкнуть по строке Фильтр по цвету. Раскроется дополнительная вкладка с двумя доступными функциями: «Фильтр по цвету ячейки» и «Фильтр по цвету шрифта».

В разделе «Фильтр по цвету ячейки» выбрать оттенок, по которому нужно отфильтровать исходную таблицу, нажав на него ЛКМ.
Проверить результат. После проделывания вышеуказанных манипуляций в таблице останутся только ячейки с указанным ранее цветом. Остальные элементы пропадут, а табличка сократится.

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

Если выбрать нужный оттенок в разделе «Фильтр по цвету шрифта», то в таблице останутся только строчки, текст шрифта в которых прописан выбранным цветом.

Обратите внимание! В Microsoft Office Excel у функции фильтрации по цвету есть существенный недостаток. Пользователь может выбрать только один оттенок, по которому будет производиться фильтрация табличного массива. Указать несколько цветов сразу невозможно.

Как в Excel отсортировать данные по нескольким цветам

С сортировкой по цветам в Экселе обычно проблем не возникает. Она выполняется аналогичным образом:

По аналогии с предыдущим пунктом добавить фильтр к табличному массиву.
Щелкнуть по стрелочке, появившейся в названии столбца, и в выпадающем меню выбрать пункт «Сортировка по цвету».

Указать требуемый тип сортировки, к примеру выбрать нужный оттенок в колонке «Сортировка по цвету ячейки».
После выполнения предыдущих манипуляций строчки таблицы с выбранным ранее оттенком будут располагаться на первом месте массива по порядку. Также можно отсортировать и остальные цвета.

Дополнительная информация! Отсортировать данные в табличке также можно с помощью функции «Настраиваемая сортировка», добавив несколько уровней по цветам.

Как отфильтровать сведения в таблице по цвету с помощью пользовательской функции

Чтобы в Microsoft Office Excel выбрать фильтр для отображения сразу нескольких цветов в таблице, необходимо создать дополнительный параметров с оттенком заливки. По созданному оттенку данные в дальнейшем и будут фильтроваться. Пользовательская функция в Эксель создается по следующей инструкции:

Зайти в раздел «Разработчик», который находится сверху главного меню программы.
В открывшейся области вкладки кликнуть по кнопке «Visual Basic».
Откроется встроенный в программу редактор, в котором потребуется создать новый модуль и прописать код.

Чтобы применить созданную функцию, необходимо:

Важно! Сортировка в Excel с помощью пользовательской функции выполняется аналогичным образом.

Заключение

Таким образом, в MS Excel можно быстро отфильтровать исходный табличный массив по цвету ячеек различными методами. Основные способы фильтрации и сортировки, которыми рекомендуется пользоваться при выполнении поставленной задачи, были рассмотрены выше.

Гораздо лучше было бы на Вашем файле, но давайте попробуем без него
Варианты:
1. Раскраска у Вас, например, в диапазоне А2:А99, а автофильтр стоит на диапазон А1:А50. Тогда раскраски ячеек А51:А99 не войдут в список
2. Когда Вы жмете на кнопку автофильтра - фильтр по цвету, у Вас появляется кучка возможных цветов, а ниже надпись "Другие цвета ячеек". Если эта надпись есть - нажмите на нее и у Вас вывалится еще большая кучка цветов Гораздо лучше было бы на Вашем файле, но давайте попробуем без него
Варианты:
1. Раскраска у Вас, например, в диапазоне А2:А99, а автофильтр стоит на диапазон А1:А50. Тогда раскраски ячеек А51:А99 не войдут в список
2. Когда Вы жмете на кнопку автофильтра - фильтр по цвету, у Вас появляется кучка возможных цветов, а ниже надпись "Другие цвета ячеек". Если эта надпись есть - нажмите на нее и у Вас вывалится еще большая кучка цветов _Boroda_ Спасибо. Ни то, ни другое.
Вот как это выглядит у меня. На картинке слева показано состояние до нажатия на заголовок фильтра, а справа - после. В выпадающем списке нет ни слова о цвете Спасибо. Ни то, ни другое.
Вот как это выглядит у меня. На картинке слева показано состояние до нажатия на заголовок фильтра, а справа - после. В выпадающем списке нет ни слова о цвете borus

желаю вам счастья

Весь файл не нужен, достаточно десятка строк с разными цветами. Конф информацию можно заменить на нейтральную.
Весь файл не нужен, достаточно десятка строк с разными цветами. Конф информацию можно заменить на нейтральную. _Boroda_
Весь файл не нужен, достаточно десятка строк с разными цветами. Конф информацию можно заменить на нейтральную. Автор - _Boroda_
Дата добавления - 07.06.2018 в 10:30 Что я выяснил: если файл в режиме совместного доступа нескольких пользователей, то фильтр по цвету не предлагается. Можно ли при совместном доступе иметь фильтр по цвету? Файл(в режиме совместного доступа) приложил Что я выяснил: если файл в режиме совместного доступа нескольких пользователей, то фильтр по цвету не предлагается. Можно ли при совместном доступе иметь фильтр по цвету? Файл(в режиме совместного доступа) приложил borus

Не, нельзя.
Вот, читайте Дополнительные сведения здесь https://support.office.com/ru-ru. a628534
Там есть пункт "Сортировка и фильтрация по формату" в Неподдерживаемых действиях

По какому принципу у Вас раскраска? Можно в отдельном столбце по этому же принципу писать, например, числа 1-2-3-. или обозначения какие-то и по этому столбцу потом фильтровать

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Важно: Часть содержимого этого раздела может быть неприменима к некоторым языкам.

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

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

На вкладке Данные нажмите кнопку Фильтр.

Щелкните стрелку в столбце с содержимым, которое нужно отфильтровать.

В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите условия.

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

На панели инструментов Стандартная нажмите кнопку Фильтр .

Щелкните стрелку в столбце с содержимым, которое нужно отфильтровать.

В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите цвет.

Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.

Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
Применение пользовательских функций.

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

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

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

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

Сортировка по цвету ячеек

Пример данных, которые необходимо отсортировать относительно цвета заливки ячеек изображен ниже на рисунке:

Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:

Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.

Ожидаемый результат изображен ниже на рисунке:

Аналогичным способом можно сортировать данные по цвету шрифта или типу значка которые содержат ячейки. Для этого достаточно только указать соответствующий критерий в секции «Сортировка» диалогового окна настройки условий.

Фильтр по цвету ячеек

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

Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
Из всплывающего подменю выберите зеленый цвет.

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

Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:

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

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

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

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

В этой статье

Проблемы, которые приводят к существенной потере функциональности

При сортировке листа книги использовано более трех условий сортировки. В более ранних версиях Excel эта информация будет потеряна.

Что это означает. В Excel 2007 г. или более поздней 2007 г. для сортировки данных можно применить до 64 условий сортировки, но в Excel 97–2003 поддерживается только три состояния сортировки. Чтобы исключить потерю сведений об условиях сортировки в Excel 97–2003, может потребоваться сокращение количества условий сортировки до трех. В Excel 97–2003 вы также можете сортировать данные вручную.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном ее повторном открыть в Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти данные, отсортированные с использованием более трех условий, и сократите число условий сортировки до трех или менее.

При сортировке листа книги использовано условие сортировки с настраиваемым списком. В более ранних версиях Excel эта информация будет потеряна.

Что это означает. В Excel 2007 г. или более поздней можно сортировать по настраиваемой списку. Для получения таких же результатов сортировки в Excel 97–2003 можно сгруппировать данные, которые требуется отсортировать, а затем отсортировать их вручную.

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

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

Что это означает. В Excel 2007 г. или более поздней можно сортировать данные по определенному формату, такому как цвет ячейки, цвет шрифта или наборы значков. В Excel 97–2003 вы можете сортировать только текст.

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

Проблемы, которые приводят к небольшой потере точности

Книга содержит данные, к которым применен фильтр, не поддерживаемый более ранними версиями Excel. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.

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

Однако все сведения о состоянии фильтра остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.

Книга содержит данные, к которым применен фильтр по цвету ячейки. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.

Что это означает. В Excel 2007 г. или более поздней можно фильтровать по цвету ячейки, цвету шрифта или набору значков — эти методы не поддерживаются в Excel 97–2003. Чтобы не потерять функциональность фильтра, его лучше очистить перед сохранением книги в файле формата более ранней версии Excel. В Excel 97–2003 после этого можно фильтровать данные вручную.

Книга содержит данные, к которым применен фильтр по цвету шрифта. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.

Книга содержит данные, к которым применен фильтр по значку ячейки. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.

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

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

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

Что это означает. В Excel 2007 г. или более поздней можно фильтровать даты по сгруппанной иерархии. Поскольку в Excel 97–2003 эта возможность не поддерживается, может потребоваться разгруппировать иерархию дат. Чтобы не потерять функциональность фильтра, его лучше очистить перед сохранением книги в файле формата более ранней версии Excel.

Группировку данных также можно отключить на вкладке "Дополнительные параметры" Excel Параметры. (Файл, параметры).

Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

Стандартный фильтр и сортировка по цвету в Excel

Сортировка и фильтр по цвету с помощью функций

Функция цвета заливки ячейки на VBA

Читайте также: