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. ในแผ่นงานมีค่าที่คุณต้องการ vlookup ให้คลิกขวาที่แท็บแผ่นงานแล้วเลือก ดูรหัส จากเมนูบริบท ดูภาพหน้าจอ:

2. ในการเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน โปรดคัดลอกโค้ด VBA ด้านล่างลงในหน้าต่างรหัส

รหัส VBA 1: Vlookup และส่งคืนค่าด้วยการจัดรูปแบบ

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 จากนั้นคลิก สิ่งที่ใส่เข้าไป > โมดูลแล้วคัดลอกโค้ด VBA 2 ด้านล่างลงในหน้าต่างโมดูล

รหัส VBA 2: Vlookup และส่งคืนค่าด้วยการจัดรูปแบบ

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 คลิก เครื่องมือ > อ้างอิง. จากนั้นตรวจสอบไฟล์ รันไทม์ Microsoft Script กล่องใน เอกสารอ้างอิง - VBAProject กล่องโต้ตอบ ดูภาพหน้าจอ:

5 กด อื่น ๆ + Q ปุ่มเพื่อออกจากไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

6. เลือกเซลล์ว่างที่อยู่ติดกับค่าการค้นหาจากนั้นป้อนสูตร =LookupKeepFormat(E2,$A$1:$C$8,3) เข้าไปใน สูตรบาร์แล้วกดปุ่ม เข้าสู่ กุญแจ

หมายเหตุ: ในสูตร E2 มีค่าที่คุณจะค้นหา $ ก $ 1: $ C $ 8 คือช่วงของตารางและตัวเลข 3 หมายความว่าค่าที่สอดคล้องกันที่คุณจะส่งคืนจะอยู่ในคอลัมน์ที่สามของตาราง โปรดเปลี่ยนตามที่คุณต้องการ

7. เลือกเซลล์ผลลัพธ์แรกจากนั้นลาก Fill Handle ลงเพื่อให้ได้ผลลัพธ์ทั้งหมดพร้อมกับการจัดรูปแบบตามที่แสดงภาพด้านล่าง


บทความที่เกี่ยวข้อง:


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel ช่วยแก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • นำมาใช้ใหม่: ใส่อย่างรวดเร็ว สูตรที่ซับซ้อนแผนภูมิ และสิ่งที่คุณเคยใช้มาก่อน เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ โดยไม่สูญเสียข้อมูล แยกเนื้อหาของเซลล์ รวมแถว / คอลัมน์ที่ซ้ำกัน... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • คุณสมบัติที่ทรงพลังมากกว่า 300 รายการ. รองรับ Office / Excel 2007-2019 และ 365 รองรับทุกภาษา ใช้งานง่ายในองค์กรหรือองค์กรของคุณ ทดลองใช้ฟรี 30 วันเต็ม รับประกันคืนเงิน 60 วัน
kte แท็บ 201905

แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มผลผลิตของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
ด้านล่าง officetab
จัดเรียงความคิดเห็นโดย
ความคิดเห็น (42)
ยังไม่มีการให้คะแนน เป็นคนแรกที่ให้คะแนน!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มันให้ฉัน Compile Error, Syntax error

กรุณาช่วย
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ขอให้เป็นวันที่ดี,
รหัสได้รับการปรับปรุงในบทความ ขอบคุณสำหรับความคิดเห็นของคุณ.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันยังได้รับข้อผิดพลาดของคอมไพเลอร์
จะได้รับการแก้ไขหากคุณเปลี่ยนตัวแปรต่อไปนี้ด้วย "" ไม่ ';' ระหว่างกลาง.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี
ขออภัยในความผิดพลาด รหัสได้รับการปรับปรุงในบทความ
ข้อผิดพลาด " " ควรเป็นเครื่องหมายอัญประกาศสองตัว " " ขอบคุณสำหรับความคิดเห็นของคุณ.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันได้รับข้อผิดพลาดเดียวกัน

คุณจะต้องเปลี่ยน " " ตามจริง "' โดยไม่มี ';' ตามที่ระบุไว้ด้านล่าง
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี
ขออภัยในความผิดพลาด รหัสได้รับการปรับปรุงในบทความ ขอบคุณสำหรับการแชร์.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มันเยี่ยมมาก ขอบคุณ! ปัญหาเดียวคือ ฉันคิดว่ามันทำงานได้ดีถ้าฉันค้นหาในชีตเดียวกัน แต่ไม่สามารถใช้งานได้เมื่อฉันพยายามค้นหาในชีตแยกต่างหากไปยังแหล่งข้อมูล จะพยายามต่อไป
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
Julia แก้ไขบรรทัดนี้:
ในฟังก์ชัน LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & ค้นหา Rng.Parent.Name

ใน Sub Worksheet_Change:
ชีต(แยก(xDic.Items(I), "|")(1)).ช่วง(แยก(xDic.Items(I), "|")(0)).คัดลอก
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
เฮ้ ฮิวโก้


ฉันมีปัญหาเดียวกันกับจูเลีย มันใช้ไม่ได้กับชีตอื่น คุณช่วยเขียนโค้ดสำหรับทั้งฟังก์ชันและเวิร์กชีตย่อยได้ไหม ฉันไม่แน่ใจว่าจะแทนที่/แทรก xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" ได้ที่ไหน & LookupRng.Parent.Nam และ Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


ขอบคุณในทางกลับกัน
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
กราบขอบพระคุณที่ติดตาม ฮิวโก้!
น่าเสียดายเช่นเดียวกับ 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 ของฉัน เรากำลังพยายามอัปเกรดรหัสเพื่อแก้ปัญหา ขอบคุณสำหรับความคิดเห็นของคุณ.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี ขอบคุณสำหรับรหัส ฉันไม่ได้รับข้อความแสดงข้อผิดพลาดใดๆ แต่สูตรจะทำงานเหมือน vlookup ปกติเท่านั้น คุณช่วยได้ไหม ขอบคุณที่สละเวลา.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี

ฉันมีปัญหาเดียวกันทุกประการ คุณทราบวิธีแก้ปัญหาหรือไม่

ขอบคุณ!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี ฉันได้รับข้อผิดพลาด "ข้อผิดพลาดในการคอมไพล์: ตรวจพบชื่อที่ไม่ชัดเจน: xDic
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี ฉันได้รับข้อผิดพลาด "ข้อผิดพลาดในการคอมไพล์: ตรวจพบชื่อที่ไม่ชัดเจน: xDic
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี ฉันยังใหม่กับการใช้ VBA และลองใช้โค้ดนี้ในสเปรดชีตของฉัน แต่การจัดรูปแบบข้อความบนแท็บ Rec2 ไม่ปรากฏในแท็บ Rec เมื่อใช้การค้นหา ความช่วยเหลือใด ๆ ที่จะได้รับการชื่นชมอย่างมาก. ขอบคุณแพท
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
นี่คือไฟล์และรูปภาพ
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันได้รับข้อผิดพลาดชื่อกำกวมเหมือนกัน - มีใครสามารถแก้ไขได้หรือไม่
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันได้รับข้อผิดพลาดชื่อกำกวมเหมือนกัน - มีใครสามารถแก้ไขได้หรือไม่
มีความคิดเห็นยังไม่มีการโพสต์ที่นี่
โหลดเพิ่มเติม
แสดงความคิดเห็นของคุณ
โพสต์ในฐานะแขก
×
ให้คะแนนโพสต์นี้:
0   ตัวอักษร
สถานที่แนะนำ