Як скопіювати вихідне форматування комірки пошуку під час використання Vlookup в Excel?
У попередніх статтях ми говорили про збереження кольору фону при значенні vlookup в Excel. Тут, у цій статті, ми збираємось представити метод копіювання всього форматування комірок отриманої комірки при виконанні Vlookup в Excel. Будь ласка, виконайте наступне.
Скопіюйте форматування вихідного коду під час використання Vlookup в Excel із визначеною користувачем функцією
Припустимо, у вас є таблиця, як показано на знімку екрана. Тепер вам потрібно перевірити, чи вказане значення (у стовпці E) є у стовпці A, і повернути відповідне значення з форматуванням у стовпці C. Будь ласка, виконайте наступні дії, щоб його досягти.
1. На робочому аркуші міститься значення, яке потрібно переглянути, клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню. Дивіться знімок екрана:
2. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте нижче код VBA у вікно коду.
Код VBA 1: пошук і повернення значення з форматуванням
Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Set xRg = Application.Range(xDicStr)
xRg.Copy
Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
Else
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
Application.CutCopyMode = True
End Sub
3 Потім натисніть Insert > Модуліта скопіюйте наведений нижче код VBA 2 у вікно модуля.
Код VBA 2: пошук і повернення значення з форматуванням
Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
End If
Application.ScreenUpdating = True
End Function4. клацання Tools > посилання. Потім перевірте Виконання сценарію Microsoft коробка в Посилання - VBAProject діалогове вікно. Дивіться знімок екрана:
5 Натисніть кнопку інший + Q клавіші для виходу з Microsoft Visual Basic для додатків вікна.
6. Виберіть порожню комірку, поруч із значенням пошуку, а потім введіть формулу =LookupKeepFormat(E2,$A$1:$C$8,3) в Бар Формула, а потім натисніть вводити ключ
примітки: У формулі, E2 містить значення, яке ви будете шукати, $ A $ 1: $ C $ 8 - діапазон таблиці та номер 3 означає, що відповідне значення, яке ви повернете, знаходить у третьому стовпці таблиці. Будь ласка, змініть їх, як вам потрібно.
7. Продовжуйте обирати першу комірку результату, а потім перетягніть ручку заповнення вниз, щоб отримати всі результати разом із їх форматуванням, як показано на знімку екрана.
Статті по темі:
- Як шукати та повертати колір тла разом із значенням пошуку в Excel?
- Як здійснити пошук і повернення формату дати замість числа в Excel?
- Як використовувати vlookup і суму в Excel?
- Як vlookup повернути значення у сусідній або наступній комірці в Excel?
- Як vlookup значення і повернути true або false / так чи ні в Excel?
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
- Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
- Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
- Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
- Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
- Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
- Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
- Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
- Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
- Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
