На чтение 3 мин Просмотров 4.4к. Опубликовано 25.11.2021
Содержание
- Пример использования
- Сортировка по цвету ячеек
- Фильтр по цвету ячеек
Функция =ЦВЕТЗАЛИВКИ(ЯЧЕЙКА) возвращает код цвета заливки выбранной ячейки. Имеет один обязательный аргумент:
- ЯЧЕЙКА — ссылка на ячейку, для которой необходимо применить функцию.
Ниже представлен пример, демонстрирующий работу функции.
Следует обратить внимание на тот факт, что функция не пересчитывается автоматически. Это связано с тем, что изменение цвета заливки ячейки Excel не приводит к пересчету формул. Для пересчета формулы необходимо пользоваться сочетанием клавиш Ctrl+Alt+F9
Пример использования
Так как заливка ячеек значительно упрощает восприятие данных, то пользоваться ей любят практически все пользователи. Однако есть и большой минус — в стандартном функционале Excel отсутствует возможность выполнять операции на основе цвета заливки. Нельзя просуммировать ячейки определенного цвета, посчитать их количество, найти максимальное и так далее.
С помощью функции ЦВЕТЗАЛИВКИ все это становится выполнимым. Например, «протяните» данную формулу с цветом заливки в соседнем столбце и производите вычисления на основе числового кода ячейки.
Создатели Excel решили, начиная от 2007-ой версии ввести возможность сортировки данных по цвету. Для этого послужило поводом большая потребность пользователей предыдущих версий, упорядочивать данные в такой способ. Раньше реализовать сортировку данных относительно цвета можно было только с помощью создания макроса VBA. Создавалась пользовательская функция и вводилась как формула под соответствующим столбцом, по которому нужно было выполнить сортировку. Теперь такие задачи можно выполнять значительно проще и эффективнее.
Сортировка по цвету ячеек
Пример данных, которые необходимо отсортировать относительно цвета заливки ячеек изображен ниже на рисунке:
Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:
- Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
- Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.
- Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».
- Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.
Ожидаемый результат изображен ниже на рисунке:
Аналогичным способом можно сортировать данные по цвету шрифта или типу значка которые содержат ячейки. Для этого достаточно только указать соответствующий критерий в секции «Сортировка» диалогового окна настройки условий.
Фильтр по цвету ячеек
Аналогично по отношению к сортировке, функционирует фильтр по цвету. Чтобы разобраться с принципом его действия воспользуемся тем же диапазоном данных, что и в предыдущем примере. Для этого:
- Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».
- Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».
- Из всплывающего подменю выберите зеленый цвет.
В результате отфильтруються данные и будут отображаться только те, которые содержать ячейки с зеленым цветом заливки:
Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:
Как всегда, Excel нам предоставляет несколько путей для решения одних и тех же задач. Пользователь выбирает для себя самый оптимальный путь, плюс необходимые инструменты всегда под рукой.
Когда мы хотим выбрать все ячейки, цвет или форматирование которых аналогичны указанной ячейке, как мы можем сделать это быстро? Эта статья посвящена простым способам выбора ячеек на основе другой ячейки.
Выберите ячейки на основе другой ячейки с помощью команды «Найти»;
Выберите ячейки на основе другой ячейки с помощью Kutools for Excel.
Предположим, у вас есть диапазон данных, как на следующем снимке экрана, ячейка F3 имеет определенное форматирование, которое вы хотите, и теперь вам нужно выбрать все ячейки, которые имеют такое же форматирование с ячейкой F3 из диапазона A1: D10. Здесь я представлю вам несколько быстрых способов.
Выберите ячейки на основе другой ячейки с помощью команды «Найти»
Excel позволяет выбирать ячейки на основе форматирования других ячеек. Вам необходимо выполнить несколько шагов:
Шаг 1: О Главная вкладку, перейдите к Найти и заменить, наведите на Арендовать опцию, вы также можете быстро активировать эту опцию поиска, нажав Ctrl + F ярлыки.
Шаг 2: Нажмите Формат в Найти и заменить диалоговое окно и выберите Выбрать формат из ячейки. Если вы не можете узнать Формат кнопку, нажмите Опции сначала кнопку. Смотрите скриншот:
Шаг 3: Выберите ячейку, содержащую формат, который вы хотите использовать для поиска, а затем щелкните значок Найти все кнопка. Все ячейки, которые имеют то же форматирование, что и выбранная ячейка, будут перечислены под полем списка, см. Снимок экрана:
Шаг 4: Нажмите Ctrl + чтобы выбрать все элементы в списке, и были выбраны те же ячейки форматирования с выбранной ячейкой, включая конкретную ячейку, см. снимок экрана:
Примечание: Если вы хотите настроить атрибуты базовой ячейки для выбора ячеек, нажмите Формат сразу после выбора базовой ячейки. Затем вы можете настроить атрибуты базовой ячейки.
Выберите ячейки на основе другой ячейки с помощью Kutools for Excel
Для Выбрать ячейки с форматом утилита сторонней надстройки Kutools for Excel, вы можете легко выбирать ячейки на основе другой выбранной ячейки.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Получить сейчас
После установки Kutools for Excel, подать заявление Выбрать ячейки с форматом в соответствии с этими шагами:
Шаг 1: Нажмите Кутулс > Выберите Инструменты > Выбрать ячейки с форматом. Смотрите скриншот:
Шаг 2: Укажите настройки во всплывающем окне Выбрать ячейки с форматом диалоговое окно. Смотрите скриншот:
1. Нажмите |
|
2, Затем нажмите Выбрать формат из ячейки кнопку, чтобы выбрать конкретную ячейку форматирования, на основе которой. | |
3. Все атрибуты конкретной ячейки внесены в список, отметьте их все. |
Шаг 3:: Щелкните OK, он покажет диалоговое окно, чтобы сообщить вам, сколько ячеек будет выбрано, если вы нажмете Да, он выберет ячейки, если вы нажмете Нет, он снова выполнит поиск в диапазоне. Смотрите скриншот:
Шаг 4: Нажмите Да, выбраны все ячейки с одинаковым форматированием в выбранном диапазоне с базовой ячейкой.
Для получения более подробной информации перейдите по ссылке Выбрать ячейки с форматом.
Относительная статья:
Как определить и выделить все жирные ячейки или текст в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
На работе столкнулся с такой задачей — имеется таблица в Excel, в которой ведется табель выходов рабочих в цеху.
В таблице подсчитывается количество часов, фактически отработанных; часов переработки и часов сверх нормы. Так вот, необходимо сделать так, чтобы производилась автоматическая выборка ячеек таблицы по цвету заливки последних.
То есть, нужно отобрать все ячейки с заливкой определенного цвета, подсчитать их количество; а затем применить к полученному значению определенные формулы.
Чтобы было понятнее, приведу изображение подобной таблицы. В ней необходимо произвести подсчет ячеек с заливкой зеленого цвета:
В Excel нет встроенных (готовых) инструментов для выборки подобного рода; можно отбирать ячейки только по одному условию — по значению, находящемуся в них. Поэтому решение задачи получалось только одно — через VBA (пользовательские функции).
Прекрасное и готовое решение моей задачи я нашел на сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под разные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.
Сразу скажу, что изображения были сделаны в Excel 2007. В Excel 2010 все несколько по другому, но запутаться невозможно, если что.
Первое, что нужно сделать — заставить Excel работать с пользовательскими функциями. Фактически, мы будем писать сценарий на языке VBA в Excel, но такая возможность по умолчанию отключена в этой программе. Включить ее можно следующим образом.
Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:
Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:
Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11.
Появиться окно, в котором выполняется написание кода на языке VBA, то есть фактически создаются пользовательские функции. Я писать их не буду, так как языка VBA не знаю и знать особого желания нет (все знать невозможно).
Вставка готовых функций в Excel VBA
Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert — Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.
Вставленные функции появятся в списке формул таблицы:
Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.
Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.
Обе функции имеют одинаковый синтаксис и принимают три входных аргумента, первые два из которых обязательные, а третий — необязательный:
— диапазон с ячейками для подсчета
1
rRange
— ячейка-образец с цветом заливки
1
rColorCell
—
1
bSumHide
1
ИСТИНА
1
1
1
ЛОЖЬ
1
0
Функция подсчета количества ячеек
'---------------------------------------------------------------------------------------
' Procedure : CountByInteriorColor
' Author : The_Prist(Щербаков Дмитрий)
' http://www.excel-vba.ru
' Purpose : Функция подсчета ячеек на основе цвета заливки.
' Аргументы:
' rRange - диапазон с ячейками для подсчета.
' rColorCell - ячейка-образец с цветом заливки.
' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки.
' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются.
'---------------------------------------------------------------------------------------
Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False)
Dim lColor As Long, rCell As Range, lCnt As Long, vVal
lColor = rColorCell.Interior.Color
For Each rCell In rRange
If rCell.Interior.Color = lColor Then
If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then
If bSumHide Then lCnt = lCnt + 1
Else
lCnt = lCnt + 1
End If
End If
Next rCell
CountByInteriorColor = lCnt
End Function
Синтаксис этой функции прост:
=CountByInteriorColor(D8:AG8;$E$65)
Функция подсчета суммы ячеек
'---------------------------------------------------------------------------------------
' Procedure : SumByInteriorColor
' Author : The_Prist(Щербаков Дмитрий)
' http://www.excel-vba.ru
' Purpose : Функция суммирования ячеек на основе цвета заливки.
' Аргументы:
' rRange - диапазон с ячейками для суммирования.
' rColorCell - ячейка-образец с цветом заливки.
' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки.
' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются.
'---------------------------------------------------------------------------------------
Function SumByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False)
Dim lColor As Long, rCell As Range, dblSum As Double, vVal
lColor = rColorCell.Interior.Color
For Each rCell In rRange
If rCell.Interior.Color = lColor Then
vVal = rCell.Value
If IsNumeric(vVal) Then
If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then
If bSumHide Then dblSum = dblSum + vVal
Else
dblSum = dblSum + vVal
End If
End If
End If
Next rCell
SumByInteriorColor = dblSum
End Function
Синтаксис этой функции следующий:
=SumByInteriorColor(D8:AG37;E63)
При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно воспользоваться либо “Мастером функций”, либо произвести указание диапазона ячеек и ячейку-критерий вручную.
Описание рабочей формулы
Готовый пример работы функции “CountByInteriorColor” можно посмотреть на рисунке “Табель выходов с зелеными ячейками”. В нем подсчет отработанного времени производится по следующей формуле:
=((Сумма фактически отработанных часов) - (Норма часов выхода за месяц)) + ((Кол-во дней с переработкой)*4)
Фактически эта формула получается такой (смотри строку №13 на рисунке):
=(AH13-AI13) + (CountByInteriorColor(D13:AG13;$E$65)*4)
Думаю, что больше сказать по поводу создания (точнее — вставки готового решения) пользовательских функций и способа выборки ячеек в таблице по цвету их заливки мне нечего.
Andy8 Пользователь Сообщений: 8 |
Всех приветствую! Изменено: Andy8 — 30.09.2021 14:26:17 |
vikttur Пользователь Сообщений: 47199 |
Вас устроит ответ — можно? Если нет — ознакомьтесь с правилами форума и прикрепите к первому сообщению пример |
БМВ Модератор Сообщений: 21634 Excel 2013, 2016 |
#3 30.09.2021 14:44:23 Использовать стандартный поиск (ctrl+f) выбрать формат, там указать заливку. А вот сокрытие строк — это или через фильтр или макросом.
Вить а что не так? По вопросам из тем форума, личку не читаю. |
||
vikttur Пользователь Сообщений: 47199 |
#4 30.09.2021 14:48:51
Мое сообщение — 13:58:56 |
||
Andy8 Пользователь Сообщений: 8 |
Я не вижу в этом диалоге выбор ячеек по формату. Excel для mac редуцирован(( Либо, не там ищу?.. |
БМВ Модератор Сообщений: 21634 Excel 2013, 2016 |
#6 30.09.2021 14:54:34
просто он обгрызан. Для мака есть раздел специальный на форуме, но не факт что этот функционал есть. По вопросам из тем форума, личку не читаю. |
||
БМВ Модератор Сообщений: 21634 Excel 2013, 2016 |
#7 30.09.2021 14:57:11
Клянусь косяком (жаль что не скринил) видел файл но Изменено: — там не было, а твое уже было. По вопросам из тем форума, личку не читаю. |
||
vikttur Пользователь Сообщений: 47199 |
|
Andy8 Пользователь Сообщений: 8 |
Вот же ошибся с веткой |
Jack Famous Пользователь Сообщений: 11036 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
Здесь тема и помрёт за отсутствием спецов Изменено: Jack Famous — 30.09.2021 15:33:43 Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
VSerg Пользователь Сообщений: 370 |
Я конечно далеко не спец, да и не факт что еще актуально. Во вложении вариант. Целевой цвет для поиска ячеек задается заливкой ячейки I7. Прикрепленные файлы
|
Andy8 Пользователь Сообщений: 8 |
Да это прям спасение!!! Изменено: Andy8 — 03.10.2021 21:01:16 |
VSerg Пользователь Сообщений: 370 |
#13 03.10.2021 21:10:05
Если я правильно понял вопрос, то вставить новый модуль в редакторе VBA, скопировать в него предлагаемый код из файла или этого сообщения и назначить выполнение на какое-либо событие.
|
||||
Andy8 Пользователь Сообщений: 8 |
Цель использования данного макроса — формирование списка ФИО на оплату. Т.е. в Столбце А указаны ФИО, а последующих столбцах проекты, где эти ФИО участвовали (одни и теже ФИО участвую в разных проектах с разными гонорарами). Проекты плодятся, а ФИО присылают отчеты не сразу. По мере получения отчетов, ячейка на пересечении проекта и ФИО отмечается желтым в ручном режиме. Как-то так… Но у меня проблема: |
VSerg Пользователь Сообщений: 370 |
Предположу, что забыли поменять имя листа. Замените в строке Set ws = wb.Sheets(«Sheet») текст в кавычках на название вашего листа с данными. |
Andy8 Пользователь Сообщений: 8 |
Да, вы правы — заработало!) Подскажите, пжст, как удовлетворить таким условиям: |
VSerg Пользователь Сообщений: 370 |
#17 04.10.2021 05:53:00
вместо
подставить
вместо
подставить
Должно помочь. |
||||||||||||
Andy8 Пользователь Сообщений: 8 |
#18 04.10.2021 11:37:38
Подставил «3» — все идеально! Спасибо вам! |
||
Skip to content
Выделение ячеек в Excel по значению и по цвету
Найдите ячейки по вашим критериям
Используя этот простой инструмент для Excel, вы можете находить ячейки с одинаковыми данными, а также ячейки, содержащие формулы, комментарии, изображения или условные форматы. Вы также можете выделить уникальные записи и пустые ячейки, выбрать текстовые значения, числа или даты, соответствующие определенным критериям.
-
60-дневная безусловная гарантия возврата денег
-
Бесплатные обновления на 2 года
-
Бесплатная и бессрочная техническая поддержка
С помощью инструмента Select by Value & Color вы сможете:
Выделить все ячейки с одинаковым значением
Выберите интересующую вас ячейку и затем выделите все остальные ячейки, содержащие то же значение.
Выделить ячейки с числами и датами по различным условиям
Найдите числовые значения, которые равны или не равны, больше или меньше указанного вами числа; в пределах или за пределами определенного диапазона; минимальное, максимальное или уникальные.
Найти специальные ячейки
Ищите ячейки с константами или формулами, которые содержат текст, числа, логические значения или ошибки.
Найти ячейки с определенной заливкой или цветом шрифта
Выделите все ячейки, которые имеют тот же цвет фона или шрифта, что и выбранная ячейка.
Выделить текстовые ячейки по самым разным шаблонам
Найдите текстовые значения, которые начинаются, заканчиваются или содержат определенные символы; точно соответствует или не совпадает с вашим текстом, уникальными данными и многим другим.
Выбрать ячейки определенного типа
Выделите все ячейки с комментариями, константами, формулами, объектами, условными форматами или просто пустые.
Что такое инструмент «Select by Value/Color» и зачем он мне нужен?
Речь идет не только о поиске ячеек по цвету и значению. Этот инструмент предлагает много различных вариантов для быстрого выделения ячеек в соответствии с вашими критериями.
С помощью надстройки вы можете:
- Найти ячейки по цвету шрифта выбранной в данный момент ячейки.
- Выделить все ячейки тем же цветом фона, что и выбранная ячейка.
- Найдите ячейки с теми же данными, что и выбранная ячейка.
- Выделите ячейки, содержащие комментарии, значения, формулы, пробелы, объекты, условные форматы или просто пустые.
Кроме того, вы можете выбрать числа, даты или текстовые ячейки, которые:
- Больше или меньше указанного значения
- Равны или не равны заданному значению
- В пределах или за пределами определенного диапазона
- Текстовые строки, начинающиеся или заканчивающиеся определенными символами
- Текст, содержащий или не содержащий указанные символы
- Максимальные или минимальные значения
- Уникальные данные
Как найти одинаковые данные с помощью этой утилиты?
Выберите любую ячейку, содержащую данные, которые вы ищете, и выберите «Все ячейки с одинаковым значением» (All cells with the same value). Инструмент выделит все ячейки, содержащие те же данные, что и в выбранной ячейке.
Мне нужно выделить все ячейки с числами больше 100. Как я могу это сделать?
Это можно сделать с помощью опции «Выбрать по значению» (Select by Value). Укажите свой диапазон поиска и отметьте «Числа» (Numbers). Выберите пункт «Выбрать ячейки которые больше» (Select cells that are greater), введите 100 в соответствующее поле и нажмите «Выбрать» . Все ячейки, содержащие числа больше 100, будут выделены.
Могу ли я выделить все ячейки с условным форматированием?
Как найти в Excel ячейки, содержащие изображения?
Опция Select Special Cells поможет. Выберите Выбрать ячейки, содержащие объекты (Select cells containing objects), и все ячейки с изображениями будут найдены.
Может ли эта утилита помочь мне найти даты, попадающие в определенный диапазон?
Да, программа предлагает такую возможность. Выберите свою область поиска и запустите надстройку Select by Value. Отметьте пункт «Даты» (Date). Затем выберите «Выбрать ячейки, которые находятся в диапазоне» (Select cells that are in the range) , определите этот диапазон и нажмите «Выбрать» .
Таким же образом вы можете найти даты, выходящие за пределы вашего диапазона, и даты, которые больше или меньше определенных дат.
Скачать Ultimate Suite