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

Excel'de Vlookup kullanırken arama hücresinin kaynak biçimlendirmesi nasıl kopyalanır?

Önceki yazılarda, Excel'de vlookup değerleri olduğunda arka plan renginin korunmasından bahsetmiştik. İşte bu makalede, Excel'de Vlookup yaparken ortaya çıkan hücrenin tüm hücre biçimlendirmesini kopyalamanın bir yöntemini tanıtacağız. Lütfen aşağıdaki işlemleri yapın.

Excel'de Kullanıcı tanımlı bir işlevle Vlookup kullanırken kaynak biçimlendirmesini kopyalayın


Excel'de Kullanıcı tanımlı bir işlevle Vlookup kullanırken kaynak biçimlendirmesini kopyalayın

Aşağıda gösterilen ekran görüntüsü gibi bir tablonuz olduğunu varsayarsak. Şimdi, belirli bir değerin (E sütununda) A sütununda olup olmadığını kontrol etmeniz ve C sütununda biçimlendirmeyle karşılık gelen değeri döndürmeniz gerekir. Bunu elde etmek için lütfen aşağıdaki işlemleri yapın.

1. Çalışma sayfasında, vlookup etmek istediğiniz değeri içerir, sayfa sekmesine sağ tıklayın ve Kodu Görüntüle bağlam menüsünden. Ekran görüntüsüne bakın:

2. Açılışta Uygulamalar için Microsoft Visual Basic penceresi, lütfen aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu 1: Vlookup ve biçimlendirmeyle dönüş değeri

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. Sonra tıklayın Ekle > modülve aşağıdaki VBA kodu 2'yi Modül penceresine kopyalayın.

VBA kodu 2: Vlookup ve biçimlendirmeyle dönüş değeri

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. tık Tools > Referanslar. Sonra kontrol edin Microsoft Komut Dosyası Çalışma Zamanı kutusu Referanslar - VBAProject iletişim kutusu. Ekran görüntüsüne bakın:

5. Tuşuna basın. Ara Toplam + Q çıkış tuşları Uygulamalar için Microsoft Visual Basic pencere.

6. Arama değerinin yanında boş bir hücre seçin ve ardından formülü girin =LookupKeepFormat(E2,$A$1:$C$8,3) içine Formül çubuğutuşuna basın ve ardından tuşuna basın. Keşfet tuşuna basın.

not: Formülde, E2 arayacağınız değeri içerir, $ A $ 1: $ C $ 8 tablo aralığı ve numarasıdır 3 döndürdüğünüz karşılık gelen değerin tablonun üçüncü sütununda yer aldığı anlamına gelir. Lütfen ihtiyaç duyduğunuzda değiştirin.

7. İlk sonuç hücresini seçmeye devam edin ve ardından aşağıdaki ekran görüntüsünde gösterildiği gibi biçimlendirmeleriyle birlikte tüm sonuçları almak için Dolgu Tutamaçını aşağı sürükleyin.


İlgili yazılar:


En İyi Ofis Üretkenliği Araçları

Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır

  • Yeniden: Hızlıca yerleştirin karmaşık formüller, grafikler ve daha önce kullandığınız her şey; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
  • Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
  • Hücreleri / Satırları / Sütunları Birleştirme Veri kaybetmeden; Bölünmüş Hücre İçeriği; Yinelenen Satırları / Sütunları Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
  • Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
  • Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
  • Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
  • Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
  • Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
  • 300'den fazla güçlü özellik. Office / Excel 2007-2021 ve 365'i destekler. Tüm dilleri destekler. Kuruluşunuzda veya kuruluşunuzda kolay devreye alma. Tam özellikler 30 günlük ücretsiz deneme. 60 günlük para iade garantisi.
kte sekmesi 201905

Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir

  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
ofis tabanı
Yorumları sıralama ölçütü
Yorumlar (42)
Henüz derecelendirme yok. İlk değerlendiren siz olun!
Bu yorum sitedeki moderatör tarafından en aza indirildi
bana Derleme Hatası, Sözdizimi hatası veriyor

lütfen yardım
Bu yorum sitedeki moderatör tarafından en aza indirildi
Good Day,
Makalede kod güncellendi. Yorumun için teşekkür ederim.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Ayrıca derleyici hatası aldım.
Aşağıdaki değişkeni gerçek "" ile değiştirirseniz düzeltilir. Numara ';' ortada.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba,
Hata için özür dileriz, makalede kod güncellendi.
" " hatası iki tırnak işareti " " olmalıdır. Yorumun için teşekkür ederim.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Aynı hatayı aldım.

" " öğesini gerçek "' için ';' olmadan değiştirmeniz gerekecektir. aşağıda belirtildiği gibi
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba,
Hata için özür dileriz, makalede kod güncellendi. Paylaşım için teşekkürler.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Bu harika, teşekkürler! Tek sorun, aynı sayfada arama yaparsam iyi çalıştığını görüyorum, ancak kaynak verilere ayrı bir sayfada arama yapmaya çalıştığımda çalışmasını sağlayamıyorum. denemeye devam edecek
Bu yorum sitedeki moderatör tarafından en aza indirildi
Julia, şu satırları düzelt:
İşlev LookupKeepFormat'ta:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

Alt Worksheet_Change'de:
Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Kopyala
Bu yorum sitedeki moderatör tarafından en aza indirildi
Hey Hugo,


Julia ile aynı sorunu yaşıyorum. Diğer sayfalarda çalışmaz. Tüm işlev ve alt çalışma sayfası için kod yazmaya yardımcı olabilir misiniz? xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" öğesini nereye değiştireceğimi/ekleyeceğimi bilmiyorum & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1))).Range(Split(xDic.Items(I), "|")(0)).Kopyala


karşılığında teşekkürler
Bu yorum sitedeki moderatör tarafından en aza indirildi
Takip eden Hugo'yu çok takdir ediyorum!
Ne yazık ki Vi gibi ben de önerilen kod düzeltmelerini nereye ekleyeceğimi bulmakta çok acemiyim...

Tekrar teşekkürler, iyi günler :)
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba


Kodu kullanmaya çalıştım ancak ekteki resimdeki hatayı alıyorum. Herhangi bir yardım çok takdir edilecektir.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba,
Hata için özür dileriz, makalede kod güncellendi. Yorumun için teşekkür ederim.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba,

Hata almıyorum ve arama yapıyor, ancak arama değerim başka bir çalışma sayfasında olduğu için (daha olası bir senaryo), biçimlendirmeyi çekmiyor. Bunun için yapabileceğim kodda bir ince ayar var mı? (Kodlama konusunda acemi olduğum için değişikliğin nereye gitmesi gerektiği konusunda çok net olun) Teşekkürler! Bu özelliği e-tablolarımdan birine ekleyeceğim için heyecanlıyım!!
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba, bu soruda herhangi bir şans var mı, biçimlendirmenin sayfalar arasında aranmasını nasıl sağlayabiliriz?
Bu yorum sitedeki moderatör tarafından en aza indirildi
Ayrıca tweak arıyorum.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Ayrıca, formülünüzü bir "Eğer" ifadesinin parçası olarak eklersem (aşağıya bakın), hücreyi LOL istediği şekilde biçimlendirir (veya en azından öyle görünür. Bir hücrede, metin gölgeli ve kalın, üst kenarlıklı olarak görünür. hücre; başka bir hücre, metin ortalanmış)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Bu yorum sitedeki moderatör tarafından en aza indirildi
Bunu ve sadece renkli arka planı çekeni denedim ve aynı hatayı alıyorum. Derleme hatası: Belirsiz ad algılandı. Tamam'ı tıklıyorum ve xDic'i vurguluyor. Baska öneri? Tüm bunlara çok aşina değilim, bu yüzden lütfen yardım edin/açıklayın :) şimdiden teşekkürler
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba Jeni,
4. adımda belirtildiği gibi Microsoft Script Runtime seçeneğini etkinleştirmeyi unutmayın.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba. Boş bir e-tablo oluşturdum ve örneğinizi Excel 2013'te çoğalttım, ancak Derleme hatası almaya devam ediyorum: Sözdizimi hatası ve Dim I As Long vurgulanmış. Kaçırdığım bir şey mi var? Bunu çalıştırmayı çok isterim. Teşekkürler.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba Laura,
4. adımda belirtildiği gibi Microsoft Script Runtime seçeneğini etkinleştirmeyi unutmayın.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba, yukarıdaki kodu Excel 2010'da bugüne kadar sorunsuz bir şekilde kullanıyorum. Ancak, yakın zamanda Office 2016'ya yükseltildim ve şimdi birden fazla satırı doldurmaya çalıştığımda kod Excel'i çökertiyor. Ne yazık ki, bana "Microsoft Excel çalışmayı durdurdu" dışında bir hata vermiyor. Bu sorunla daha önce karşılaşıp karşılaşmadığınızı ve 2016'da çalışması için yapmam gereken bir şey olup olmadığını merak ediyordum. Teşekkürler!
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba Leigh,
Kod, Excel 2016'mda iyi çalışıyor. Sorunu çözmek için kodu yükseltmeye çalışıyoruz. Yorumun için teşekkür ederim.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba, kod için teşekkürler. Herhangi bir hata mesajı almıyorum ama formül sadece normal bir vlookup gibi çalışıyor. Lütfen yardımcı olur musunuz? Zaman ayırdığınız için teşekkürler.
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba

Tam olarak aynı sorunu yaşıyorum, nasıl çözeceğinizi buldunuz mu?

Teşekkürler!
Bu yorum sitedeki moderatör tarafından en aza indirildi
merhaba, "derleme Hatası: Belirsiz ad algılandı: xDic" hatası aldım
Bu yorum sitedeki moderatör tarafından en aza indirildi
merhaba, "derleme Hatası: Belirsiz ad algılandı: xDic" hatası aldım
Bu yorum sitedeki moderatör tarafından en aza indirildi
Merhaba, VBA'yı kullanmakta yeniyim ve bu kodu elektronik tablomda kullanmayı denedim, ancak arama kullanıldığında Rec2 sekmesindeki metin biçimlendirmesi Rec sekmesine gelmiyor. Herhangi bir yardım çok takdir edilecektir. Teşekkürler Pat
Bu yorum sitedeki moderatör tarafından en aza indirildi
İşte dosya ve resim
Bu yorum sitedeki moderatör tarafından en aza indirildi
Aynı Belirsiz ad hatasını alıyorum - bunu çözmeyi başaran var mı?
Bu yorum sitedeki moderatör tarafından en aza indirildi
Aynı Belirsiz ad hatasını alıyorum - bunu çözmeyi başaran var mı?
Buraya henüz hiç yorum yapılmamış
Daha Çok
Lütfen yorum yazın
Misafir olarak yayınlama
×
Bu gönderiyi değerlendirin:
0   Karakterler
Önerilen Konumlar

Bizi takip et

Telif Hakkı © 2009 - www.extendoffice.com. | Tüm hakları Saklıdır. Tarafından desteklenmektedir ExtendOffice. | | | Site Haritası
Microsoft ve Office logosu, Microsoft Corporation'ın Amerika Birleşik Devletleri ve / veya diğer ülkelerdeki ticari markaları veya tescilli ticari markalarıdır.
Sectigo SSL ile korunmaktadır