วิธีการ vlookup คืนค่าหลายค่าในเซลล์เดียวใน Excel

VLOOKUP เป็นฟังก์ชันที่มีประสิทธิภาพใน Excel แต่โดยค่าเริ่มต้น ฟังก์ชันนี้จะส่งคืนเฉพาะค่าที่ตรงกันค่าแรกเท่านั้น จะเกิดอะไรขึ้นหากคุณต้องการดึงค่าที่ตรงกันทั้งหมดและรวมค่าเหล่านั้นไว้ในเซลล์เดียว นี่เป็นข้อกำหนดทั่วไปเมื่อวิเคราะห์ชุดข้อมูลหรือสรุปข้อมูล ในคู่มือนี้ เราจะแนะนำคุณทีละขั้นตอนเกี่ยวกับวิธีการส่งคืนค่าหลายค่าในเซลล์เดียวโดยใช้ทั้งสูตรและฟีเจอร์ที่มีประโยชน์

Vlookup เพื่อคืนค่าหลายค่าลงในเซลล์เดียวด้วยฟังก์ชัน TEXTJOIN (Excel 2019 และ Office 365)

• Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดลงในเซลล์เดียว
• Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดโดยไม่ซ้ำกันในเซลล์เดียว

Vlookup เพื่อส่งคืนค่าหลายค่าในเซลล์เดียวด้วยคุณสมบัติที่มีประโยชน์

Vlookup เพื่อส่งคืนค่าหลายค่าในเซลล์เดียวด้วย User Defined Function

• Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดลงในเซลล์เดียว
• Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดโดยไม่ซ้ำกันในเซลล์เดียว

Vlookup เพื่อคืนค่าหลายค่าลงในเซลล์เดียวด้วยฟังก์ชัน TEXTJOIN (Excel 2019 และ Office 365)

หากคุณมี Excel เวอร์ชันที่สูงกว่าเช่น Excel 2019 และ Office 365 มีฟังก์ชันใหม่ - ข้อความเข้าร่วมด้วยฟังก์ชันที่ทรงพลังนี้คุณสามารถ vlookup และคืนค่าที่ตรงกันทั้งหมดลงในเซลล์เดียวได้อย่างรวดเร็ว

  Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดลงในเซลล์เดียว

กรุณาใช้สูตรด้านล่างในเซลล์ว่างที่คุณต้องการใส่ผลลัพธ์จากนั้นกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์แรกจากนั้นลากที่จับเติมลงไปที่เซลล์ที่คุณต้องการใช้สูตรนี้และคุณจะได้รับค่าที่เกี่ยวข้องทั้งหมดตามภาพด้านล่างที่แสดง:

  Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดโดยไม่ซ้ำกันในเซลล์เดียว

หากคุณต้องการส่งคืนค่าที่ตรงกันทั้งหมดตามข้อมูลการค้นหาโดยไม่ซ้ำกันสูตรด้านล่างอาจช่วยคุณได้

กรุณาคัดลอกและวางสูตรต่อไปนี้ลงในเซลล์ว่างจากนั้นกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์แรกจากนั้นคัดลอกสูตรนี้เพื่อเติมเซลล์อื่น ๆ และคุณจะได้รับค่าที่เกี่ยวข้องทั้งหมดโดยไม่ต้องมีค่าที่ไม่ชัดเจนดังภาพด้านล่างที่แสดง:

Vlookup เพื่อส่งคืนค่าหลายค่าในเซลล์เดียวด้วยคุณสมบัติที่มีประโยชน์

ด้วยระบบเส้นทาง Kutools สำหรับ Excel's แถวรวมขั้นสูง คุณลักษณะนี้ช่วยให้คุณค้นหาค่าที่ตรงกันหลายค่าในเซลล์เดียวได้อย่างง่ายดาย โดยไม่ต้องใช้สูตรที่ซับซ้อน! บอกลาการแก้ปัญหาด้วยตนเองและปลดล็อกวิธีที่มีประสิทธิภาพยิ่งขึ้นในการจัดการงานการค้นหาใน Excel มาสำรวจกันว่า Kutools สำหรับ Excel ทำให้ทุกอย่างเป็นไปได้!

หลังจากติดตั้ง Kutools for Excel แล้วโปรดดำเนินการดังนี้:

1. เลือกช่วงข้อมูลที่คุณต้องการรวมข้อมูลคอลัมน์หนึ่งจากอีกคอลัมน์หนึ่ง

2 คลิก Kutools > ผสานและแยก > แถวรวมขั้นสูงดูภาพหน้าจอ:

3. ในโผล่ออกมา แถวรวมขั้นสูง กล่องโต้ตอบ:

• คลิกชื่อคอลัมน์หลักที่จะรวมกันโดยยึดตามแล้วคลิก คีย์หลัก.
• จากนั้นคลิกคอลัมน์อื่นที่คุณต้องการรวมข้อมูลตามคอลัมน์คีย์ และคลิกรายการดร็อปดาวน์จาก การดำเนินการ ฟิลด์ เลือกตัวคั่นตัวหนึ่งเพื่อแยกข้อมูลรวมจาก รวมกัน มาตรา.
• จากนั้นคลิก OK ปุ่ม

ค่าที่สอดคล้องกันทั้งหมดจากคอลัมน์อื่นซึ่งอิงจากค่าเดียวกันจะถูกรวมเข้าในเซลล์เดียว ดูภาพหน้าจอ:

เคล็ดลับ:หากคุณต้องการลบเนื้อหาที่ซ้ำกันขณะรวมเซลล์ เพียงทำเครื่องหมาย ลบค่าซ้ำซ้อน ตัวเลือกในกล่องโต้ตอบ วิธีนี้จะช่วยให้มั่นใจว่ามีการรวมเฉพาะรายการที่ไม่ซ้ำกันไว้ในเซลล์เดียว ทำให้ข้อมูลของคุณสะอาดและเป็นระเบียบมากขึ้นโดยไม่ต้องใช้ความพยายามพิเศษใดๆ ดูภาพหน้าจอ:

ดาวน์โหลดและทดลองใช้ Kutools for Excel ฟรีทันที!

Vlookup เพื่อส่งคืนค่าหลายค่าในเซลล์เดียวด้วย User Defined Function

ฟังก์ชัน TEXTJOIN ข้างต้นพร้อมใช้งานสำหรับ Excel 2019 และ Office 365 เท่านั้นหากคุณมี Excel เวอร์ชันอื่น ๆ ที่ต่ำกว่าคุณควรใช้รหัสบางอย่างเพื่อทำงานนี้ให้เสร็จ

  Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดลงในเซลล์เดียว

1. กดปุ่ม ALT + F11 และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล

รหัส VBA: Vlookup เพื่อส่งคืนค่าหลายค่าในเซลล์เดียว

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. จากนั้นบันทึกและปิดโค้ดนี้ กลับไปที่เวิร์กชีต และป้อนสูตรนี้: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") ลงในเซลล์ว่างที่คุณต้องการวางผลลัพธ์จากนั้นลากที่จับเติมลงเพื่อรับค่าที่เกี่ยวข้องทั้งหมดในเซลล์เดียวที่คุณต้องการดูภาพหน้าจอ:

  Vlookup เพื่อส่งคืนค่าที่ตรงกันทั้งหมดโดยไม่ซ้ำกันในเซลล์เดียว

หากต้องการละเว้นรายการที่ซ้ำกันในค่าการจับคู่ที่ส่งคืนโปรดดำเนินการกับรหัสด้านล่าง

1. กดปุ่ม Alt + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล

รหัส VBA: Vlookup และส่งคืนค่าที่ตรงกันที่ไม่ซ้ำกันหลายค่าในเซลล์เดียว

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. หลังจากใส่รหัสแล้วให้คลิก เครื่องมือ > อ้างอิง ในหน้าต่าง Microsoft Visual Basic for Applications ที่เปิดอยู่ จากนั้นในหน้าต่างที่เด้งออกมา เอกสารอ้างอิง - VBAProject กล่องโต้ตอบตรวจสอบ รันไทม์การเขียนสคริปต์ของ Microsoft ตัวเลือกใน อ้างอิงที่มีอยู่ กล่องรายการดูภาพหน้าจอ:

4 จากนั้นคลิก OK เพื่อปิดกล่องโต้ตอบบันทึกและปิดหน้าต่างรหัสกลับไปที่แผ่นงานและป้อนสูตรนี้: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

ไม่ว่าคุณจะเลือกใช้สูตรเช่น TEXTJOIN ร่วมกับฟังก์ชันอาร์เรย์ ใช้เครื่องมือเช่น Kutools สำหรับ Excel หรือฟังก์ชันที่ผู้ใช้กำหนด แนวทางทั้งหมดจะช่วยลดความซับซ้อนของงานค้นหา เลือกวิธีที่เหมาะสมกับความต้องการของคุณมากที่สุด หากคุณสนใจที่จะสำรวจเคล็ดลับและเทคนิค Excel เพิ่มเติม เว็บไซต์ของเรามีบทช่วยสอนหลายพันรายการ.

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