Note: The other languages of the website are Google-translated. Back to English

Як скопіювати вихідне форматування комірки пошуку під час використання Vlookup в Excel?

У попередніх статтях ми говорили про збереження кольору фону при значенні 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 Function

4. клацання Tools > посилання. Потім перевірте Виконання сценарію Microsoft коробка в Посилання - VBAProject діалогове вікно. Дивіться знімок екрана:

5 Натисніть кнопку інший + Q клавіші для виходу з Microsoft Visual Basic для додатків вікна.

6. Виберіть порожню комірку, поруч із значенням пошуку, а потім введіть формулу =LookupKeepFormat(E2,$A$1:$C$8,3) в Бар Формула, а потім натисніть вводити ключ

примітки: У формулі, E2 містить значення, яке ви будете шукати, $ A $ 1: $ C $ 8 - діапазон таблиці та номер 3 означає, що відповідне значення, яке ви повернете, знаходить у третьому стовпці таблиці. Будь ласка, змініть їх, як вам потрібно.

7. Продовжуйте обирати першу комірку результату, а потім перетягніть ручку заповнення вниз, щоб отримати всі результати разом із їх форматуванням, як показано на знімку екрана.


Статті по темі:


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (42)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
це дає мені помилку компіляції, синтаксичну помилку

будь ласка, допоможіть
Цей коментар був мінімізований модератором на сайті
Хороший день,
Код оновлено в статті. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Я також отримав помилку компілятора.
Це буде виправлено, якщо ви зміните наступну змінну на фактичний "". Ні ';' посередині.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Цей коментар був мінімізований модератором на сайті
привіт,
Вибачте за помилку, код оновлено в статті.
Помилка « » повинна складатися з двох лапок « ». Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Я отримав ту саму помилку.

Вам доведеться змінити « » на фактичний «', без ';' як зазначено нижче
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Цей коментар був мінімізований модератором на сайті
привіт,
Вибачте за помилку, код оновлено в статті. Дякую, що поділився.
Цей коментар був мінімізований модератором на сайті
Це чудово, дякую! Єдина проблема полягає в тому, що я вважаю, що він працює нормально, якщо я шукаю на тому самому аркуші, але не можу змусити його працювати, коли я намагаюся виконати пошук вихідних даних на окремому аркуші. Продовжу намагатися
Цей коментар був мінімізований модератором на сайті
Юлія, виправте ці рядки:
у функції LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

у Sub Worksheet_Change:
Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Копіювати
Цей коментар був мінімізований модератором на сайті
Гей, Хьюго,


У мене така ж проблема, як у Юлії. На інших аркушах не працює. Не могли б ви допомогти написати код для всієї функції та допоміжного аркуша? Я не знаю, де замінити/вставити xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Копіювати


дякую у відповідь
Цей коментар був мінімізований модератором на сайті
Щиро вдячний Г’юго за подальші дії!
На жаль, як і Vi, я надто новачок, щоб зрозуміти, куди вставити запропоновані вами виправлення коду...

Ще раз дякую, гарного дня :)
Цей коментар був мінімізований модератором на сайті
привіт Там


Я намагався використати код, але отримую помилку на доданому зображенні. Будь-яка допомога буде дуже вдячна.
Цей коментар був мінімізований модератором на сайті
привіт,
Вибачте за помилку, код оновлено в статті. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
привіт,

Я не отримую помилок, і він виконує пошук, але оскільки моє значення пошуку знаходиться на іншому робочому аркуші (більш вірогідний сценарій), воно не витягує форматування. Чи можна змінити код для цього? (Будьте чіткими щодо того, куди потрібно внести зміни, оскільки я новачок у кодуванні) Дякую! Я радий додати цю функцію до однієї зі своїх електронних таблиць!!
Цей коментар був мінімізований модератором на сайті
Привіт, пощастило з цим питанням, як ми можемо зробити так, щоб форматування шукалося на аркушах?
Цей коментар був мінімізований модератором на сайті
Також шукаю налаштування.
Цей коментар був мінімізований модератором на сайті
Крім того, якщо я додаю вашу формулу як частину оператора «Якщо» (див. нижче), вона відформатує клітинку так, як захоче, LOL (або, принаймні, так здається. Одна клітинка, текст став затіненим і жирним із верхньою межею клітинка; інша клітинка, текст по центру)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Цей коментар був мінімізований модератором на сайті
Я спробував цей і той, який витягує лише кольоровий фон, і отримую ту саму помилку. Помилка компіляції: виявлено неоднозначне ім’я. Я натискаю «ОК», і виділяється xDic. Будь-які пропозиції? Я не надто знайомий із усім цим, тому, будь ласка, допоможіть/поясніть :) заздалегідь дякую
Цей коментар був мінімізований модератором на сайті
Привіт Джені,
Не забудьте ввімкнути параметр Microsoft Script Runtime, як зазначено в кроці 4.
Цей коментар був мінімізований модератором на сайті
Привіт. Я створив порожню електронну таблицю та скопіював ваш приклад у Excel 2013, але постійно отримую помилку компіляції: синтаксична помилка та Dim I As Long виділено. Я щось втрачаю? Я хотів би, щоб це запрацювало. Дякую.
Цей коментар був мінімізований модератором на сайті
Привіт Лаура,
Не забудьте ввімкнути параметр Microsoft Script Runtime, як зазначено в кроці 4.
Цей коментар був мінімізований модератором на сайті
Привіт! На сьогоднішній день я використовую наведений вище код у Excel 2010 без проблем. Однак нещодавно я перейшов на Office 2016, і тепер код аварійно завершує роботу Excel щоразу, коли я намагаюся заповнити більше одного рядка. На жаль, він не дає мені іншої помилки, окрім "Microsoft Excel перестав працювати". Мені було цікаво, чи стикалися ви з цією проблемою раніше, і чи потрібно мені щось зробити, щоб вона працювала у 2016 році. Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт Лі,
Код добре працює в моєму Excel 2016. Ми намагаємося оновити код, щоб вирішити проблему. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за код. Я не отримую жодного повідомлення про помилку, але формула працює лише як звичайний віртуальний пошук. Не могли б ви допомогти? Дякую за ваш час.
Цей коментар був мінімізований модератором на сайті
Привіт

У мене точно така ж проблема, ви зрозуміли, як її вирішити?

Спасибо!
Цей коментар був мінімізований модератором на сайті
привіт, я отримав помилку "Помилка компіляції: виявлено двозначне ім'я: xDic
Цей коментар був мінімізований модератором на сайті
привіт, я отримав помилку "Помилка компіляції: виявлено двозначне ім'я: xDic
Цей коментар був мінімізований модератором на сайті
Привіт! Я новачок у використанні VBA і спробував використати цей код у своїй електронній таблиці, але форматування тексту на вкладці Rec2 не переходить на вкладку Rec, коли використовується пошук. Будь-яка допомога буде дуже вдячна. Дякую, Пет
Цей коментар був мінімізований модератором на сайті
Ось файл і фото
Цей коментар був мінімізований модератором на сайті
Я отримую ту саму помилку «Неоднозначне ім’я» – комусь вдалося її вирішити?
Цей коментар був мінімізований модератором на сайті
Я отримую ту саму помилку «Неоднозначне ім’я» – комусь вдалося її вирішити?
There are no comments posted here yet
Load More

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL