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

Bagaimana cara menyalin pemformatan sumber dari sel pencarian saat menggunakan Vlookup di Excel?

Di artikel sebelumnya, kita telah berbicara tentang menjaga warna latar belakang saat nilai vlookup di Excel. Di sini, di artikel ini, kami akan memperkenalkan metode menyalin semua pemformatan sel dari sel yang dihasilkan saat melakukan Vlookup di Excel. Silakan lakukan sebagai berikut.

Salin pemformatan sumber saat menggunakan Vlookup di Excel dengan fungsi yang ditentukan pengguna


Salin pemformatan sumber saat menggunakan Vlookup di Excel dengan fungsi yang ditentukan pengguna


Misalkan Anda memiliki tabel seperti gambar di bawah ini. Sekarang Anda perlu memeriksa apakah nilai yang ditentukan (di kolom E) ada di kolom A dan mengembalikan nilai yang sesuai dengan pemformatan di kolom C. Lakukan hal berikut untuk mencapainya.

1. Di lembar kerja berisi nilai yang ingin Anda vlookup, klik kanan tab lembar dan pilih Lihat kode dari menu konteks. Lihat tangkapan layar:

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi jendela, harap salin kode VBA di bawah ini ke jendela Kode.

Kode VBA 1: Vlookup dan mengembalikan nilai dengan pemformatan

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. Lalu klik Menyisipkan > Modul, dan salin kode VBA 2 di bawah ini ke jendela Modul.

Kode VBA 2: Vlookup dan mengembalikan nilai dengan pemformatan

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. klik Tools > Referensi. Kemudian periksa Waktu Proses Skrip Microsoft kotak di Referensi - VBAProject kotak dialog. Lihat tangkapan layar:

5. tekan lain + Q kunci untuk keluar dari Microsoft Visual Basic untuk Aplikasi jendela.

6. Pilih sel kosong yang berdekatan dengan nilai pencarian, lalu masukkan rumus =LookupKeepFormat(E2,$A$1:$C$8,3) ke dalam Formula Bar, lalu tekan Enter kunci.

Note: Dalam rumusnya, E2 berisi nilai yang akan Anda cari, $ A $ 1: $ C $ 8 adalah rentang tabel, dan angka 3 berarti bahwa nilai yang sesuai yang akan Anda kembalikan terletak di kolom ketiga tabel. Harap ubah sesuai kebutuhan Anda.

7. Tetap memilih sel hasil pertama, dan kemudian seret Fill Handle ke bawah untuk mendapatkan semua hasil bersama dengan pemformatannya seperti yang ditunjukkan gambar di bawah ini.


Artikel terkait:


Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (42)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
itu memberi saya Kesalahan Kompilasi, kesalahan Sintaks

tolong bantu
Komentar ini diminimalkan oleh moderator di situs
Selamat siang,
Kode telah diperbarui di artikel. Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
Saya juga mendapatkan kesalahan kompiler.
Itu akan diperbaiki jika Anda mengubah variabel berikut dengan "". Tidak ';' di tengah-tengah.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Komentar ini diminimalkan oleh moderator di situs
Hai,
Maaf atas kesalahan, kode telah diperbarui di artikel.
Kesalahan " " harus berupa dua tanda kutip " ". Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan yang sama.

Anda harus mengubah " " menjadi "' yang sebenarnya, tanpa ';' seperti yang ditunjukkan di bawah ini
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Komentar ini diminimalkan oleh moderator di situs
Hai,
Maaf atas kesalahan, kode telah diperbarui di artikel. Terima kasih sudah berbagi.
Komentar ini diminimalkan oleh moderator di situs
Ini bagus, terima kasih! Satu-satunya masalah adalah, saya merasa ini berfungsi dengan baik jika saya mencari di lembar yang sama, tetapi tidak dapat membuatnya berfungsi ketika saya mencoba melakukan pencarian di lembar terpisah ke data sumber. Akan terus mencoba
Komentar ini diminimalkan oleh moderator di situs
Julia, perbaiki baris ini:
di Fungsi LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Alamat & "|" & LookupRng.Parent.Name

di Sub Worksheet_Change:
Sheets(Pisahkan(xDic.Items(I), "|")(1)).Rentang(Split(xDic.Items(I), "|")(0)).Copy
Komentar ini diminimalkan oleh moderator di situs
Hei Hugo,


Saya memiliki masalah yang sama dengan Julia. Ini tidak bekerja pada lembar lain. Bisakah Anda membantu menulis kode untuk seluruh fungsi dan sub lembar kerja? Saya tidak yakin di mana harus mengganti/memasukkan xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam dan Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


terima kasih sebagai gantinya
Komentar ini diminimalkan oleh moderator di situs
Sangat menghargai tindak lanjut Hugo!
Sayangnya seperti Vi, saya terlalu pemula untuk mencari tahu di mana harus memasukkan perbaikan kode yang Anda sarankan ...

Terima kasih sekali lagi, semoga harimu menyenangkan :)
Komentar ini diminimalkan oleh moderator di situs
Hai yang disana


Saya telah mencoba menggunakan kode tersebut namun saya mendapatkan kesalahan pada gambar terlampir. Setiap bantuan akan sangat dihargai.
Komentar ini diminimalkan oleh moderator di situs
Hai,
Maaf atas kesalahan, kode telah diperbarui di artikel. Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
Hai,

Saya tidak mendapatkan kesalahan dan melakukan pencarian, tetapi karena nilai pencarian saya ada di lembar kerja lain (skenario yang lebih mungkin), itu tidak menarik pemformatan. Apakah ada tweak pada kode yang bisa saya buat untuk itu? (Sangat spesifik ke mana perubahan harus pergi karena saya seorang pemula pengkodean) Terima kasih! Saya senang menambahkan fitur ini ke salah satu spreadsheet saya!!
Komentar ini diminimalkan oleh moderator di situs
Hai, semoga berhasil dalam pertanyaan ini, bagaimana kami bisa membuat pemformatan terlihat di seluruh lembar?
Komentar ini diminimalkan oleh moderator di situs
Juga mencari tweak.
Komentar ini diminimalkan oleh moderator di situs
Juga, jika saya menambahkan rumus Anda sebagai bagian dari pernyataan "Jika" (lihat di bawah), itu memformat sel sesuai keinginannya LOL (atau setidaknya tampaknya begitu. Satu sel, teks menjadi gelap dan tebal dengan batas atas pada sel; sel lain, teks di tengah)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
Komentar ini diminimalkan oleh moderator di situs
Saya mencoba yang ini dan yang hanya menarik latar belakang warna dan mendapatkan kesalahan yang sama. Galat kompilasi: Nama ambigu terdeteksi. Saya mengklik OK dan itu menyoroti xDic. Ada saran? Saya tidak terlalu akrab dengan semua ini jadi tolong bantu/jelaskan :) terima kasih sebelumnya
Komentar ini diminimalkan oleh moderator di situs
Hai Jeni,
Jangan lupa untuk mengaktifkan opsi Microsoft Script Runtime seperti yang disebutkan pada langkah 4.
Komentar ini diminimalkan oleh moderator di situs
Halo. Saya membuat spreadsheet kosong dan menggandakan contoh Anda di Excel 2013, tetapi tetap mendapatkan kesalahan Kompilasi: Kesalahan sintaks dan Dim I As Long disorot. Apakah ada sesuatu yang saya lewatkan? Saya akan senang untuk mendapatkan ini bekerja. Terima kasih.
Komentar ini diminimalkan oleh moderator di situs
Hai Laura,
Jangan lupa untuk mengaktifkan opsi Microsoft Script Runtime seperti yang disebutkan pada langkah 4.
Komentar ini diminimalkan oleh moderator di situs
Halo, saya telah menggunakan kode di atas di Excel 2010 tanpa masalah hingga saat ini. Namun, saya baru-baru ini memutakhirkan ke Office 2016 dan sekarang kode crash Excel setiap kali saya mencoba mengisi lebih dari satu baris. Sayangnya, itu tidak memberi saya kesalahan selain "Microsoft Excel telah berhenti bekerja". Saya ingin tahu apakah Anda pernah menemukan masalah ini sebelumnya, dan apakah ada sesuatu yang perlu saya lakukan untuk membuatnya bekerja pada tahun 2016. Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hai Leigh,
Kode berfungsi dengan baik di Excel 2016. Kami mencoba memutakhirkan kode untuk menyelesaikan masalah. Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
Halo, Terima kasih untuk kodenya. Saya tidak mendapatkan pesan kesalahan apa pun tetapi rumusnya hanya berfungsi seperti vlookup normal. Bisakah Anda membantu? Terima kasih atas waktunya.
Komentar ini diminimalkan oleh moderator di situs
Halo

Saya memiliki masalah yang persis sama, apakah Anda menemukan cara untuk menyelesaikannya?

Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
hai saya mendapat kesalahan "Kesalahan kompilasi: Nama ambigu terdeteksi: xDic
Komentar ini diminimalkan oleh moderator di situs
hai saya mendapat kesalahan "Kesalahan kompilasi: Nama ambigu terdeteksi: xDic
Komentar ini diminimalkan oleh moderator di situs
HI, saya baru menggunakan VBA dan mencoba menggunakan kode ini di spreadsheet saya, tetapi pemformatan teks pada tab Rec2 tidak masuk ke tab Rec saat pencarian digunakan. Bantuan apa pun akan sangat dihargai. Terima kasih Pat
Komentar ini diminimalkan oleh moderator di situs
Ini file dan fotonya
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan nama Ambigu yang sama - adakah yang berhasil menyelesaikannya?
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan nama Ambigu yang sama - adakah yang berhasil menyelesaikannya?
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan