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

Jak skopiować formatowanie źródłowe komórki odnośnika podczas korzystania z Vlookup w programie Excel?

W poprzednich artykułach rozmawialiśmy o zachowaniu koloru tła podczas przeglądania wartości w programie Excel. W tym artykule wprowadzimy metodę kopiowania całego formatowania komórki wynikowej podczas wykonywania Vlookup w programie Excel. Wykonaj następujące czynności.

Kopiuj formatowanie źródłowe podczas korzystania z Vlookup w programie Excel z funkcją zdefiniowaną przez użytkownika


Kopiuj formatowanie źródłowe podczas korzystania z Vlookup w programie Excel z funkcją zdefiniowaną przez użytkownika

Przypuśćmy, że masz tabelę pokazaną na poniższym zrzucie ekranu. Teraz musisz sprawdzić, czy podana wartość (w kolumnie E) znajduje się w kolumnie A i zwrócić odpowiednią wartość z formatowaniem w kolumnie C. Aby to osiągnąć, wykonaj następujące czynności.

1. W arkuszu zawiera wartość, którą chcesz przeoczyć, kliknij prawym przyciskiem myszy kartę arkusza i wybierz Wyświetl kod z menu kontekstowego. Zobacz zrzut ekranu:

2. W otwarciu Microsoft Visual Basic for Applications okno, skopiuj poniższy kod VBA do okna Code.

Kod VBA 1: przeglądanie i zwracanie wartości z formatowaniem

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. Następnie kliknij wstawka > Modułi skopiuj poniższy kod VBA 2 do okna Module.

Kod VBA 2: przeglądanie i zwracanie wartości z formatowaniem

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. kliknij Narzędzia > Referencje. Następnie sprawdź Środowisko wykonawcze skryptów firmy Microsoft Microsoft Okno w Referencje - VBAProject Okno dialogowe. Zobacz zrzut ekranu:

5. wciśnij inny + Q klawisze, aby wyjść z Microsoft Visual Basic for Applications okno.

6. Wybierz pustą komórkę sąsiadującą z wartością wyszukiwania, a następnie wprowadź formułę =LookupKeepFormat(E2,$A$1:$C$8,3) do Pasek formuły, a następnie naciśnij Wchodzę klawisz.

Uwagi: We wzorze E2 zawiera wartość, którą będziesz szukać, 1 $ A: 8 $ C $ to zakres i numer tabeli 3 oznacza, że ​​odpowiednia wartość, którą zwrócisz, znajduje się w trzeciej kolumnie tabeli. Zmień je według potrzeb.

7. Kontynuuj wybieranie pierwszej komórki wynikowej, a następnie przeciągnij uchwyt wypełnienia w dół, aby uzyskać wszystkie wyniki wraz z ich formatowaniem, jak pokazano poniżej.


Podobne artykuły:


Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół
Sortuj komentarze według
Komentarze (42)
Brak ocen. Oceń jako pierwszy!
Ten komentarz został zminimalizowany przez moderatora na stronie
to daje mi błąd kompilacji, błąd składni

proszę o pomoc
Ten komentarz został zminimalizowany przez moderatora na stronie
Dobry dzień,
Kod został zaktualizowany w artykule. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Dostałem też błąd kompilatora.
Zostaje poprawiony, jeśli zmienisz następującą zmienną z rzeczywistym „”. Nie ';' pośrodku.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, „ ”
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Przepraszamy za błąd, kod został zaktualizowany w artykule.
Błąd " " powinien zawierać dwa cudzysłowy " ". Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Mam ten sam błąd.

Będziesz musiał zmienić " " na rzeczywisty "', bez ';' jak wskazano poniżej
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, „ ”

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Przepraszamy za błąd, kod został zaktualizowany w artykule. Dziękuję za udostępnienie.
Ten komentarz został zminimalizowany przez moderatora na stronie
To świetnie, dziękuję! Jedynym problemem jest to, że działa dobrze, jeśli szukam w tym samym arkuszu, ale nie mogę go uruchomić, gdy próbuję wykonać wyszukiwanie w osobnym arkuszu do danych źródłowych. Nadal będę próbował
Ten komentarz został zminimalizowany przez moderatora na stronie
Julio, popraw te linie:
w funkcji LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Adres & "|" & LookupRng.Parent.Name

w podarkuszu_zmiana:
Arkusze(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Kopiuj
Ten komentarz został zminimalizowany przez moderatora na stronie
Hej Hugo,


Mam ten sam problem co Julia. Nie działa na innych arkuszach. Czy możesz pomóc napisać kod dla całej funkcji i podarkusza? Nie jestem pewien, gdzie zastąpić/wstawić 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)).Copy


dzięki w zamian
Ten komentarz został zminimalizowany przez moderatora na stronie
Bardzo doceniam kontynuację Hugo!
Niestety, podobnie jak Vi, jestem zbyt wielkim nowicjuszem, aby dowiedzieć się, gdzie wstawić sugerowane poprawki kodu...

Jeszcze raz dziękuję, życzę miłego dnia :)
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć


Próbowałem użyć kodu, jednak otrzymuję błąd na załączonym zdjęciu. Każda pomoc będzie bardzo mile widziana.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,
Przepraszamy za błąd, kod został zaktualizowany w artykule. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć,

Nie otrzymuję błędów i wykonuje wyszukiwanie, ale ponieważ moja wartość wyszukiwania znajduje się w innym arkuszu (bardziej prawdopodobny scenariusz), formatowanie nie jest pobierane. Czy mogę w tym celu wprowadzić poprawki w kodzie? (Bądź bardzo konkretny, gdzie powinna nastąpić zmiana, ponieważ jestem nowicjuszem w kodowaniu) Dziękuję! Cieszę się, że mogę dodać tę funkcję do jednego z moich arkuszy kalkulacyjnych!!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, masz szczęście w odpowiedzi na to pytanie, jak możemy uzyskać formatowanie do przeglądania w arkuszach?
Ten komentarz został zminimalizowany przez moderatora na stronie
Również szukanie poprawki.
Ten komentarz został zminimalizowany przez moderatora na stronie
Ponadto, jeśli dodam twoją formułę jako część instrukcji „Jeśli” (patrz poniżej), formatuje ona komórkę, jednak chce LOL (a przynajmniej tak się wydaje. Jedna komórka, tekst został zacieniony i pogrubiony z górnym obramowaniem na komórka; inna komórka, tekst wyśrodkowany)


= JEŻELI ($ F19 = "", "", LookupKeepFormat (F19;'Element #s'!$A$1:$M$1226,2))
Ten komentarz został zminimalizowany przez moderatora na stronie
Wypróbowałem ten i ten, który ciągnie tylko kolorowe tło i otrzymuję ten sam błąd. Błąd kompilacji: wykryto niejednoznaczną nazwę. Klikam OK i podświetla xDic. Jakieś sugestie? Nie znam się na tym wszystkim, więc proszę o pomoc/wyjaśnienie :) z góry dzięki
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Jeni,
Nie zapomnij włączyć opcji Microsoft Script Runtime, jak wspomniano w kroku 4.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć. Utworzyłem pusty arkusz kalkulacyjny i zduplikowałem twój przykład w programie Excel 2013, ale wciąż otrzymuję błąd kompilacji: podświetlony jest błąd składni i Dim I As Long. Czy jest coś, czego mi brakuje? Chciałbym, żeby to zadziałało. Dziękuję Ci.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Laura,
Nie zapomnij włączyć opcji Microsoft Script Runtime, jak wspomniano w kroku 4.
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam, używam powyższego kodu w programie Excel 2010 bez żadnych problemów do tej pory. Jednak niedawno zostałem uaktualniony do pakietu Office 2016 i teraz kod powoduje awarię programu Excel za każdym razem, gdy próbuję wypełnić więcej niż jeden wiersz. Niestety nie daje mi błędu innego niż „Microsoft Excel przestał działać”. Zastanawiałem się, czy wcześniej zetknąłeś się z tym problemem i czy jest coś, co muszę zrobić, aby działał w 2016 roku. Dzięki!
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć Leigh,
Kod działa dobrze w moim Excelu 2016. Próbujemy uaktualnić kod, aby rozwiązać problem. Dziękuję za Twój komentarz.
Ten komentarz został zminimalizowany przez moderatora na stronie
Witam, Dzięki za kod. Nie otrzymuję żadnego komunikatu o błędzie, ale formuła działa tylko tak, jak normalna przeglądarka. Czy mógłbyś pomóc? Dziękuję za Twój czas.
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć

Mam dokładnie ten sam problem, czy wymyśliłeś, jak go rozwiązać?

Dzięki!
Ten komentarz został zminimalizowany przez moderatora na stronie
cześć, pojawił się błąd „Błąd kompilacji: wykryto niejednoznaczną nazwę: xDic
Ten komentarz został zminimalizowany przez moderatora na stronie
cześć, pojawił się błąd „Błąd kompilacji: wykryto niejednoznaczną nazwę: xDic
Ten komentarz został zminimalizowany przez moderatora na stronie
Cześć, jestem nowy w używaniu VBA i próbowałem użyć tego kodu w moim arkuszu kalkulacyjnym, ale formatowanie tekstu na karcie Rec2 nie przechodzi do karty Rec, gdy używane jest wyszukiwanie. Każda pomoc byłaby bardzo mile widziana. Dzięki Pat
Ten komentarz został zminimalizowany przez moderatora na stronie
Oto plik i zdjęcie
Ten komentarz został zminimalizowany przez moderatora na stronie
Dostaję ten sam błąd Niejednoznacznej nazwy - czy ktoś zdołał go rozwiązać?
Ten komentarz został zminimalizowany przez moderatora na stronie
Dostaję ten sam błąd Niejednoznacznej nazwy - czy ktoś zdołał go rozwiązać?
Nie ma tu jeszcze żadnych komentarzy
Pokaż więcej
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL