Поиск в ячейке по цвету в Excel

Обновлено: 02.07.2024

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

Скачать рабочую тетрадь

Загрузите эту практическую рабочую тетрадь.

Функция GET.CELL: обзор

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

Основной синтаксис:

type_num – это число, указывающее, какой тип информации о ячейке вам нужен.

В следующем списке показаны возможные значения type_num и соответствующие результаты.

Одна из проблем заключается в том, что вы не можете использовать GET.CELL непосредственно на листе.

Этапы указаны ниже:

<р>1. Перейдите в «Формулы» > «Диспетчер имен». Появится диалоговое окно «Диспетчер имен».

<р>2. Затем нажмите «Создать».

<р>3. Дайте ему любое имя.

<р>4. В поле Относится к введите следующий формат:

Поскольку мы работаем с фоновыми цветами, мы используем 63 в аргументе type_num.

<р>5. Наконец, нажмите "ОК".

Теперь вы можете использовать GET.CELL с именем, которое вы дали.

Введение в цветовой индекс и значения RGB

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

С другой стороны, RGB (красный, зеленый и синий) представляет цвета на дисплее компьютера. Мы смешиваем красный, зеленый и синий в различных пропорциях, чтобы получить любой цвет в видимом цвете. Значения R, G и B могут варьироваться от 0 до 100 процентов от полной интенсивности. Мы представляем его диапазоном десятичных чисел от 0 до 255 (256 уровней для каждого цвета), эквивалентным диапазону двоичных чисел от 00000000 до 11111111 или шестнадцатеричных чисел от 00 до FF. Общее количество доступных цветов составляет 256 x 256 x 256, или 16 777 216 возможных цветов.

Как найти индекс цвета и значения RGB?

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

С другой стороны, вы можете найти значение RGB любого цвета на вкладке "Главная" в Excel.

📌 Шаги

<р>1. Сначала перейдите на вкладку "Главная".

<р>2. Затем нажмите раскрывающийся список «Цвет заливки» > «Другие цвета».

<р>3. Нажмите "Пользовательский".

Здесь вы можете найти значения RGB любого цвета.

2 эффективных метода получения любого цвета ячейки в Excel

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

Для демонстрации этого руководства мы будем использовать этот набор данных:

Здесь у нас есть несколько цветов в разных ячейках. Мы найдем эти индексы цветов и значения RGB, используя эти два метода.

1. Использование функции GET.CELL для получения цвета ячейки в Excel

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

📌 Шаги

<р>1. Сначала перейдите на вкладку Формула. Нажмите «Диспетчер имен». Появится диалоговое окно «Диспетчер имен».

<р>2. Нажмите «Создать».

<р>3. Теперь дайте ему имя. Мы используем его в качестве фона.

<р>4. В поле Относится к введите следующую формулу:

<р>5. Нажмите "ОК".

<р>6. Теперь в ячейке B5 введите =Background.

<р>7. Затем нажмите Enter.

Как видите, он показывает индекс цвета. Теперь повторите то же самое для каждой ячейки.

1.1 Отображение индекса цвета левой ячейки

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

📌 Шаги

<р>1. Снова зайдите в диспетчер имен. Назовите его «getLeftColor».

<р>2. В поле Относится к введите следующую формулу:

<р>3. Теперь в ячейке E5 введите =getLeftColor.

<р>4. Затем нажмите Enter.

<р>5. Наконец, перетащите значок ручки заполнения в диапазон ячеек E6:E12.

Как видите, мы успешно нашли цвет ячейки в другой ячейке.

1.2 Отображение индекса цвета правой ячейки

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

📌Шаги

<р>1. Снова зайдите в диспетчер имен. Назовите его «getRightColor».

<р>2. В поле Относится к введите следующую формулу:

<р>3. Теперь в ячейке G5 введите =getRightColor.

<р>4. Затем нажмите Enter.

<р>5. Наконец, перетащите значок ручки заполнения в диапазон ячеек G6:G12.

Как видите, мы успешно нашли цвет ячейки в другой ячейке.

Ограничение использования функции GET.CELL:

Если вы измените цвет ячейки, значение не изменится. Чтобы решить эту проблему, нажмите F9 на клавиатуре, чтобы пересчитать его снова.

2. Использование кодов VBA для получения цвета ячеек в Excel

Если вы знаете коды Excel VBA, этот метод покажется вам слишком простым. Есть два кода VBA, которые вы можете использовать в своем наборе данных. Первый для индексов. Второй — для значений RGB.

2.1 Код VBA для получения индекса цвета ячейки

Теперь этот метод может не дать вам точных индексов, как предыдущий. Но вы можете рассматривать это как индексы. Я надеюсь, что это пригодится.

📌 Шаги

<р>1. Сначала нажмите Alt+F11 на клавиатуре, чтобы открыть редактор VBA.

<р>2. Затем нажмите «Вставить» > «Модуль».

<р>3. Введите следующий код:

<р>4. Сохраните файл.

<р>5. Теперь в ячейке B5 введите следующую формулу:

<р>6. Затем нажмите Enter. Он покажет вам индекс цвета.

<р>7. Наконец, перетащите значок маркера заполнения на диапазон ячеек B6:B12

Как видите, нам удалось получить цвет ячейки в Excel.

2.2 Код VBA для получения значения RGB ячеек

Этот метод поможет вам найти значение RGB для ячейки. Этот метод более эффективен, чем предыдущий.

📌 Шаги

<р>1. Сначала нажмите Alt+F11 на клавиатуре, чтобы открыть редактор VBA.

<р>2. Затем нажмите «Вставить» > «Модуль».

<р>3. Введите следующий код:

<р>4. Сохраните файл.

<р>5. Теперь в ячейке B5 введите следующую формулу:

<р>6. Затем нажмите Enter. Он покажет вам индекс цвета.

<р>7. Наконец, перетащите значок маркера заполнения на диапазон ячеек B6:B12

В конце вы увидите значения RGB для всех ячеек.

💬 Что нужно помнить

✎ Чаще всего используются значения RGB. По нашему мнению, вы всегда должны пытаться найти значения RGB.

✎ Индекс цвета не меняется после изменения цвета ячейки. Нажмите F9, чтобы пересчитать.

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

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

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

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

Что-то вроде того, как показано ниже (когда я ввожу название элемента в ячейку B2 и нажимаю Enter, выделяется вся строка):

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

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

Это руководство охватывает:

Поиск и выделение соответствующих ячеек

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

Как показано ниже:

Вот шаги для поиска и выделения всех ячеек с совпадающим текстом:

Теперь введите что-нибудь в ячейку B1 и нажмите Enter. Будут выделены совпадающие ячейки в наборе данных, содержащие ключевое слово в ячейке B1.

Как это работает?

Условное форматирование применяется всякий раз, когда указанная в нем формула возвращает значение TRUE.

В приведенном выше примере мы проверяем каждую ячейку по формуле =A4=$B$1

Условное форматирование проверяет каждую ячейку и проверяет, совпадает ли содержимое ячейки с содержимым ячейки B1. Если это то же самое, формула возвращает ИСТИНА, и ячейка выделяется. Если это не то же самое, формула возвращает FALSE и ничего не происходит.

Поиск и выделение строк с совпадающими данными

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

Ниже приведен пример, в котором выделяется вся строка, если тип продукта совпадает с типом в ячейке B1.

Вот шаги для поиска и выделения всей строки:

1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
2. Перейдите на вкладку "Главная".
3. В группе "Стили" нажмите "Условное форматирование".
4. В раскрывающемся списке выберите Новое правило.
   
5. В диалоговом окне "Новое правило форматирования" выберите параметр "Использовать формулу для определения форматируемых ячеек".
6. Введите следующую формулу: =$B4=$B$1
7. Нажмите кнопку "Форматировать...".
8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
9. Нажмите "ОК".

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

Обратите внимание, что при этом будет проверяться только столбец элементов. Если вы введете здесь имя торгового представителя, это не сработает. Если вы хотите, чтобы это работало для имени торгового представителя, вам нужно изменить формулу на =$C4=$B$1

Примечание. Причина, по которой выделяется вся строка, а не только соответствующая ячейка, заключается в том, что мы использовали знак $ перед ссылкой на столбец ($B4). Теперь, когда условное форматирование анализирует ячейки в строке, оно проверяет, равно ли значение в столбце B этой строки значению в ячейке B1. Таким образом, даже когда он анализирует A4, B4 или C4 и т. д., он проверяет только значение B4 (поскольку мы заблокировали столбец B с помощью знака доллара).

Поиск и выделение строк (на основе частичного совпадения)

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

Например, если у вас есть такие элементы, как "Белая доска", "Зеленая доска" и "Серая доска", и вы хотите выделить их все по слову "Доска", вы можете сделать это с помощью функции ПОИСК.

Как показано ниже:




Вот как это сделать:




1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
2. Перейдите на вкладку "Главная".
3. В группе "Стили" нажмите "Условное форматирование".
4. В раскрывающемся списке выберите Новое правило.
   
5. В диалоговом окне "Новое правило форматирования" выберите параметр "Использовать формулу для определения форматируемых ячеек".
6. Введите следующую формулу: =И($B$1<>””,ЕЧИСЛО(ПОИСК($B$1,$B4)))
7. Нажмите кнопку "Форматировать...".
8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
9. Нажмите "ОК".

Как это работает?

Функция ищет строку поиска/ключевое слово во всех ячейках подряд. Он возвращает ошибку, если ключевое слово для поиска не найдено, и возвращает число, если находит совпадение. Функция преобразует ошибку в FALSE, а числовые значения в TRUE. функция проверяет наличие дополнительного условия – ячейка C2 не должна быть пустой.

Теперь всякий раз, когда вы вводите ключевое слово в ячейку B1 и нажимаете Enter, выделяются все строки, в которых есть ячейки, содержащие это ключевое слово.

Дополнительный совет: если вы хотите сделать поиск чувствительным к регистру, используйте функцию НАЙТИ вместо ПОИСК.

Функциональность динамического поиска и выделения (подсветка по мере ввода)

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

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

Как показано ниже:

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

Ниже показано видео, в котором я показываю, как это сделать: