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

Bagaimana cara vlookup dan mengembalikan warna latar belakang bersama dengan nilai pencarian di Excel?

Misalkan Anda memiliki tabel seperti gambar di bawah ini. Sekarang Anda ingin memeriksa apakah nilai yang ditentukan ada di kolom A dan kemudian mengembalikan nilai yang sesuai bersama dengan warna latar belakang di kolom C. Bagaimana mencapainya? Metode dalam artikel dapat membantu Anda mengatasi masalah tersebut.

Vlookup dan mengembalikan warna latar belakang dengan nilai pencarian oleh fungsi yang ditentukan pengguna


Vlookup dan mengembalikan warna latar belakang dengan nilai pencarian oleh fungsi yang ditentukan pengguna

Lakukan hal berikut untuk mencari nilai dan mengembalikan nilai yang sesuai bersama dengan warna latar belakang di Excel.

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 warna latar belakang dengan nilai pencarian

Sub Worksheet_Change(ByVal Target As Range)
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = 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 warna latar belakang dengan nilai pencarian

Public xDic As New Dictionary
Function LookupKeepColor (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepColor = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepColor = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Setelah memasukkan kedua kode tersebut, lalu 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 dan kembali ke lembar kerja.

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

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 seret Fill Handle ke bawah untuk mendapatkan semua hasil bersama dengan warna latar belakangnya. Lihat tangkapan layar.


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-2021 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.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (34)
Rated 5 dari 5 · peringkat 1
Komentar ini diminimalkan oleh moderator di situs
Bagaimana cara mengubah kode ini, agar dapat mengekstrak warna latar belakang dari lembar lain?
Misalnya, saya ingin menggunakan VLOOKUP di Lembar 2, yang mengekstrak data dan warna latar belakang dari Lembar 1.
Komentar ini diminimalkan oleh moderator di situs
Saya punya pertanyaan yang sama persis ini! Setiap saran akan sangat dihargai.
Komentar ini diminimalkan oleh moderator di situs
Saya juga ingin VLOOKUP pada lembar 2 dan mengekstrak data dan warna latar belakang dari lembar 1
Komentar ini diminimalkan oleh moderator di situs
Gunakan sedikit modifikasi dari kode yang diposting.


xDic Publik Sebagai Kamus Baru
strWB publik Sebagai String
strWS publik Sebagai String

Fungsi CLookup(ByRef FndValue, ByRef LookupRng Sebagai Rentang, ByRef xCol Selama)
Redupkan xFindCell Sebagai Rentang
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Ingat Buku Kerja tempat asal data dan warna
strWS = LookupRng.Parent.Name '*** Ingat Lembar Kerja tempat asal data dan warna

Setel xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Jika xFindCell Bukan Apa-apa Maka
CPencarian = ""
xDic.Add Application.Caller.Address, ""
Lain
CLookup = xFindCell.Offset(0, xCol - 1).Nilai
xDic.Tambahkan Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Alamat

End If
End Function

Sub Worksheet_Change (Target ByVal Sebagai Rentang)
Redup Aku Selamanya
Redupkan xKeys Selamanya
Redupkan xDicStr Sebagai String
Redupkan rngLoc Sebagai Rentang
On Error Resume Next
Application.ScreenUpdating = Salah
xKeys = UBound(xDic.Keys)
Jika xKeys >= 0 Maka
Untuk I = 0 Ke UBound(xDic.Keys)
xDicStr = xDic.Barang(I)
Jika xDicStr <> "" Maka
Rentang(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheet(strWS).Range(xDic.Items(I)).Interior.Color
Lain
Rentang(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Tetapkan xDic = Tidak ada
End If
Application.ScreenUpdating = Benar
End Sub
Komentar ini diminimalkan oleh moderator di situs
Apakah ini untuk memperbaiki kesalahan dalam kode asli atau apakah ini untuk memungkinkannya mencari dari lembar yang berbeda?
Komentar ini diminimalkan oleh moderator di situs
Perubahan pada kode asli ini memungkinkan Anda melakukan vlookup dengan warna dari satu Lembar Kerja ke Lembar Kerja lainnya atau dari satu Buku Kerja ke Buku Kerja lainnya. Tetapi kode ini perlu ditempatkan di lembar kerja TARGET daripada lembar kerja SOURCE seperti yang dijelaskan dalam kode aslinya. Itu karena kode asli hanya berfungsi di satu Lembar Kerja, jadi itu adalah Sumber dan Target. Ini bukan perbaikan pada kode aslinya. Saya baru saja menambahkan kode untuk memungkinkan Anda menarik dari Buku Kerja/Lembar Kerja (Sumber) apa pun ke dalam Lembar Kerja (Target) Anda. Kode asli berfungsi seperti yang dimaksudkan oleh programmer.
Komentar ini diminimalkan oleh moderator di situs
halo saya sudah melakukan prosedurnya tetapi saya tidak dapat membawa warna latar belakang di lembar kerja baru, saya ragu apakah saya memasukkan perintah strWB dan strWS dengan cara yang benar, saya meletakkan ini strWB = LookupRng.Reporte_Opcionales
strWS = LookupRng.Imprimir Reporte_Opcionales adalah nama buku kerja saya
Komentar ini diminimalkan oleh moderator di situs
Saya percaya garis-garisnya seharusnya sebagai berikut (PERSIS):

strWB = LookupRng.Parent.Parent.Name

strWS = LookupRng.Parent.Name


Saya membuat ini sekitar 4 bulan yang lalu jadi saya tidak ingat persis bagaimana saya membuat ini, tetapi Anda tidak seharusnya mengganti kode ini dengan yang lain.
Komentar ini diminimalkan oleh moderator di situs
apa nama di strWB telah diulang Parent.Parent ???? apakah itu benar?
Terima kasih sebelumnya.
Komentar ini diminimalkan oleh moderator di situs
Bob, tolong bantu saya, bisakah Anda memeriksa kodenya? saya yakin Anda dapat memperbaikinya karena itu menghilangkan warna latar belakang dari lembar lain.

omong-omong kode yang untuk bekerja di lembar kerja yang sama tetapi saya perlu membawa data dari lembar lain :(.

terima kasih sebelumnya
salam dari Monterrey México.
Komentar ini diminimalkan oleh moderator di situs
Ini bekerja dengan baik, terima kasih!
Rated 5 dari 5
Komentar ini diminimalkan oleh moderator di situs
kode ini bekerja pada lembar yang sama, bagaimana saya bisa mencari warna dari satu lembar ke lembar lainnya?
Komentar ini diminimalkan oleh moderator di situs
Gunakan sedikit modifikasi dari kode yang diposting.


xDic Publik Sebagai Kamus Baru
strWB publik Sebagai String
strWS publik Sebagai String

Fungsi CLookup(ByRef FndValue, ByRef LookupRng Sebagai Rentang, ByRef xCol Selama)
Redupkan xFindCell Sebagai Rentang
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Ingat Buku Kerja tempat asal data dan warna
strWS = LookupRng.Parent.Name '*** Ingat Lembar Kerja tempat asal data dan warna

Setel xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Jika xFindCell Bukan Apa-apa Maka
CPencarian = ""
xDic.Add Application.Caller.Address, ""
Lain
CLookup = xFindCell.Offset(0, xCol - 1).Nilai
xDic.Tambahkan Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Alamat

End If
End Function

Sub Worksheet_Change (Target ByVal Sebagai Rentang)
Redup Aku Selamanya
Redupkan xKeys Selamanya
Redupkan xDicStr Sebagai String
Redupkan rngLoc Sebagai Rentang
On Error Resume Next
Application.ScreenUpdating = Salah
xKeys = UBound(xDic.Keys)
Jika xKeys >= 0 Maka
Untuk I = 0 Ke UBound(xDic.Keys)
xDicStr = xDic.Barang(I)
Jika xDicStr <> "" Maka
Rentang(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheet(strWS).Range(xDic.Items(I)).Interior.Color
Lain
Rentang(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Tetapkan xDic = Tidak ada
End If
Application.ScreenUpdating = Benar
End Sub
Komentar ini diminimalkan oleh moderator di situs
Halo Bob! Kode berfungsi, bagaimanapun, untuk beberapa alasan menyalin nilai dari Lembar 2 ke Lembar 1, tetapi menyalin format sel dan meninggalkannya di Lembar 2... Sulit untuk dijelaskan, tetapi pada dasarnya membagi satu tindakan (salin teks + formasi salinan dan paste ke dalam sel) menjadi dua. Apakah Anda tahu cara membuatnya melakukan keduanya dalam satu lembar? Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
kode ini berjalan pada lembar yang sama tetapi bagaimana saya bisa mencari warna sel dari satu lembar ke lembar lain di excel
Thanks in advance :)
Komentar ini diminimalkan oleh moderator di situs
Gunakan sedikit modifikasi dari kode yang diposting.


xDic Publik Sebagai Kamus Baru
strWB publik Sebagai String
strWS publik Sebagai String

Fungsi CLookup(ByRef FndValue, ByRef LookupRng Sebagai Rentang, ByRef xCol Selama)
Redupkan xFindCell Sebagai Rentang
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Ingat Buku Kerja tempat asal data dan warna
strWS = LookupRng.Parent.Name '*** Ingat Lembar Kerja tempat asal data dan warna

Setel xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Jika xFindCell Bukan Apa-apa Maka
CPencarian = ""
xDic.Add Application.Caller.Address, ""
Lain
CLookup = xFindCell.Offset(0, xCol - 1).Nilai
xDic.Tambahkan Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Alamat

End If
End Function

Sub Worksheet_Change (Target ByVal Sebagai Rentang)
Redup Aku Selamanya
Redupkan xKeys Selamanya
Redupkan xDicStr Sebagai String
Redupkan rngLoc Sebagai Rentang
On Error Resume Next
Application.ScreenUpdating = Salah
xKeys = UBound(xDic.Keys)
Jika xKeys >= 0 Maka
Untuk I = 0 Ke UBound(xDic.Keys)
xDicStr = xDic.Barang(I)
Jika xDicStr <> "" Maka
Rentang(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheet(strWS).Range(xDic.Items(I)).Interior.Color
Lain
Rentang(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Tetapkan xDic = Tidak ada
End If
Application.ScreenUpdating = Benar
End Sub
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki windows untuk Mac , ketika saya mencapai Langkah 4 - tidak ada opsi untuk Microsoft Scripting Runtime, apakah ada hal lain yang harus saya pilih?
Komentar ini diminimalkan oleh moderator di situs
Ketika saya membuka jendela Lihat Kode, ada jendela tetapi tidak kosong. Bisakah saya menempelkan kode di bawah teks yang sudah ada atau bagaimana cara membuka "halaman kosong" baru?
Komentar ini diminimalkan oleh moderator di situs
Saya mengembalikan nilai, tetapi tidak mendapatkan warnanya. menggunakan kode lembar ke lembar, diikuti ke T. Adakah ide mengapa saya tidak mendapatkan warna?
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk memodifikasi ini untuk digunakan sebagai Hlookup?
Komentar ini diminimalkan oleh moderator di situs
selamat siang bob untuk kode-kode ini Anda dapat mengubahnya selain warna panggil saya format warna yang sama dan font yang berisi sel

Terima kasih
Komentar ini diminimalkan oleh moderator di situs
ini berfungsi dengan baik di office 2010, tetapi bukan versi 2013. Apakah ada pembaruan untuk makro?
Komentar ini diminimalkan oleh moderator di situs
Hai, Dapatkah saya menerapkan vlookup pada sel warna tanpa data di dalamnya?
Komentar ini diminimalkan oleh moderator di situs
saya mendapatkan warna sel yang diperlukan tetapi saya juga membutuhkan nilai pencarian karena mengembalikan integer alih-alih string
Komentar ini diminimalkan oleh moderator di situs
Saya telah menggunakan ini di Excel 2016 dan hanya data yang ditransfer dari Sumber ke Target.......warna tidak ditransfer. Pikiran tentang masalah apa yang mungkin terjadi: Apakah ini tidak kompatibel dengan Excel 2016? Terima kasih. MT
Komentar ini diminimalkan oleh moderator di situs
Ini luar biasa! ikuti langkah-langkahnya dan itu berfungsi dengan baik! Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki banyak catatan, terlalu lama untuk diproses, dan kode terus berjalan bahkan setelah selesai. Tolong bantu
Komentar ini diminimalkan oleh moderator di situs
Halo, saya punya lembar dengan 10,948 baris, butuh beberapa waktu untuk menarik informasi dengan warna, masih menunggu. Apakah ini normal, atau ada yang salah?
Komentar ini diminimalkan oleh moderator di situs
Bagaimana saya melakukannya?
Komentar ini diminimalkan oleh moderator di situs
Saya menggunakan waktu dan tanggal dari laporan excel untuk membuat timesheets untuk karyawan kami. Jika tanggal yang ditentukan, misalnya, 2020/08/11 cocok dengan tanggal pada larik tab berikutnya (yang berisi banyak sel dengan tanggal yang sama tetapi waktu yang berbeda) saya ingin itu hanya menarik sel yang diisi oranye yang akan dinyatakan sebagai 2020/08/11 7:45. Apakah ini mungkin?
Komentar ini diminimalkan oleh moderator di situs
Hai, Apakah kode ini berfungsi untuk office 2016 dan versi yang lebih baru?
Komentar ini diminimalkan oleh moderator di situs
tidak, warnanya tidak kembali.
Komentar ini diminimalkan oleh moderator di situs
Kode ini berfungsi dengan baik, kecuali di sel di mana rumus dimasukkan untuk memunculkan 0 ketika sel yang dicarinya kosong, pertanyaan saya adalah bagaimana cara membuatnya mengabaikan sel kosong dan mencegah sel yang dimasukkan rumusnya a 0 , apakah ada tempat dalam kode untuk memasukkan fungsi =IFERROR mungkin?
Komentar ini diminimalkan oleh moderator di situs
Hai Kyle,

Saya menguji kode ini dan tidak memunculkan 0 saat sel yang dicari kosong.
Mungkin Anda bisa menyertakan rumus dalam fungsi IF, seperti yang ditunjukkan di bawah ini, untuk mencegah pengembalian hasil 0.
=IF(B2="","",LookupKeepColor(E2,$A$1:$C$8,3))
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL