Как выполнить ВПР и вернуть цвет фона вместе со значением поиска в Excel?
Предположим, у вас есть таблица, подобная той, что показана на скриншоте ниже. Теперь вы хотите проверить, содержится ли указанное значение в столбце A, а затем получить соответствующее значение из столбца C вместе с его цветом фона. Как этого добиться? Метод, описанный в статье, поможет вам решить эту задачу.
ВПР и возврат Цвет фона вместе со значением поиска с помощью пользовательской функции
ВПР и возврат Цвет фона вместе со значением поиска с помощью пользовательской функции
Выполните следующие действия, чтобы найти значение и вернуть соответствующее значение вместе с цветом фона в Excel.
1. На листе, содержащем значение, которое вы хотите найти с помощью ВПР, щелкните правой кнопкой мыши ярлык листа и выберите Просмотреть код в контекстном меню. См. скриншот:
2. В открывшемся окне Microsoft Visual Basic для приложений скопируйте приведённый ниже код VBA в окно кода.
Код VBA 1: ВПР и возврат Цвет фона вместе со значением поиска
Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Range(xDic.Keys(I)).Interior.Color = _
Range(xDic.Items(I)).Interior.Color
Else
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
End Sub3. Затем нажмите Вставка > Модуль и скопируйте приведённый ниже код VBA 2 в окно модуля.
Код VBA 2: ВПР и возврат Цвет фона вместе со значением поиска
Public xDic As New Dictionary
Function LookupKeepColor (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepColor = ""
xDic.Add Application.Caller.Address, ""
Else
LookupKeepColor = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
End Function4. После вставки обоих фрагментов кода нажмите Сервис > Ссылки. В открывшемся диалоговом окне Ссылки – VBAProject установите флажок напротив элемента Microsoft Script Runtime. См. скриншот:
5. Нажмите клавиши Alt+Q, чтобы закрыть окно Microsoft Visual Basic для приложений и вернуться на лист.
6. Выберите пустую ячейку рядом со значением для поиска, введите формулу =LookupKeepColor(E2,$A$1:$C$8,3) в строку формул и нажмите клавишу Enter.
Примечание: в формуле E2 содержит значение, которое вы ищете, $A$1:$C$8 — это диапазон таблицы, а число 3 означает, что возвращаемое значение находится в третьем столбце таблицы. При необходимости измените эти параметры.
7. Не снимая выделения с первой ячейки результата, перетащите маркер заполнения вниз, чтобы получить все результаты вместе с их цветом фона. См. скриншот.
См. также:
- Как скопировать форматирование исходной ячейки при использовании функции ВПР (VLOOKUP) в Excel?
- Как выполнить ВПР и получить формат даты вместо числа в Excel?
- Как использовать ВПР вместе с функцией СУММ в Excel?
- Как выполнить ВПР, чтобы возвращаемое значение находилось в соседней или следующей ячейке в Excel?
- Как с помощью ВПР найти значение и вернуть ИСТИНА или ЛОЖЬ (ДА или НЕТ) в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек