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

วิธี vlookup และเชื่อมต่อหลายค่าที่เกี่ยวข้องใน Excel

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

Vlookup และส่งคืนค่าที่ตรงกันหลายค่าในแนวตั้งด้วยสูตร

Vlookup และเชื่อมต่อค่าที่ตรงกันหลายค่าในเซลล์ด้วย User Defined Function

Vlookup และเชื่อมต่อค่าที่ตรงกันหลายค่าในเซลล์ด้วย Kutools for Excel


สมมติว่าฉันมีช่วงข้อมูลต่อไปนี้เพื่อให้ได้ค่าที่เกี่ยวข้องทั้งหมดตามค่าเฉพาะในแนวตั้งตามภาพหน้าจอต่อไปนี้คุณสามารถใช้สูตรอาร์เรย์ได้

doc vlookup ต่อกัน 1

1. ใส่สูตรนี้: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") ลงในเซลล์ว่างที่คุณต้องการใส่ผลลัพธ์ตัวอย่างเช่น E2 แล้วกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อรับค่าสัมพัทธ์ตามเกณฑ์เฉพาะดูภาพหน้าจอ:

doc vlookup ต่อกัน 2

หมายเหตุ: ในสูตรข้างต้น:

A1: A16 คือช่วงคอลัมน์ที่มีค่าเฉพาะที่คุณต้องการค้นหา

D2 ระบุค่าเฉพาะที่คุณต้องการ vlookup

B1: B16 คือช่วงคอลัมน์ที่คุณต้องการส่งคืนข้อมูลที่เกี่ยวข้อง

$ 1: $ 16 ระบุการอ้างอิงแถวภายในช่วง

2. จากนั้นเลือกเซลล์ E2 แล้วลากที่จับเติมลงไปที่เซลล์จนกว่าคุณจะได้เซลล์ว่างและค่าที่ตรงกันทั้งหมดจะแสดงในคอลัมน์ตามภาพหน้าจอต่อไปนี้:

doc vlookup ต่อกัน 3


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

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

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

รหัส VBA: Vlookup และเชื่อมต่อค่าที่ตรงกันหลายค่าในเซลล์

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. จากนั้นบันทึกและปิดรหัสนี้กลับไปที่แผ่นงานและป้อนสูตรนี้: = cusvlookup (D2, A1: B16,2) ลงในเซลล์ว่างที่คุณต้องการใส่ผลลัพธ์แล้วกด เข้าสู่ คีย์ค่าที่เกี่ยวข้องทั้งหมดตามข้อมูลเฉพาะได้ถูกส่งกลับไปยังเซลล์เดียวพร้อมตัวคั่นช่องว่างดูภาพหน้าจอ:

doc vlookup ต่อกัน 4

หมายเหตุ: ในสูตรข้างต้น: D2 ระบุค่าเซลล์ที่คุณต้องการค้นหา A1: B16 คือช่วงข้อมูลที่คุณต้องการดึงข้อมูลจำนวน 2 คือหมายเลขคอลัมน์ที่จะส่งคืนค่าที่ตรงกันคุณสามารถเปลี่ยนการอ้างอิงเหล่านี้ตามความต้องการของคุณ


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

Kutools สำหรับ Excel : ด้วย Add-in ของ Excel ที่มีประโยชน์มากกว่า 300 รายการทดลองใช้ฟรีโดยไม่มีข้อ จำกัด ใน 30 วัน.

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

1. เลือกช่วงข้อมูลที่คุณต้องการรับค่าที่สอดคล้องกันตามข้อมูลที่ระบุ

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

3. ใน แถวรวมขั้นสูง กล่องโต้ตอบคลิกชื่อคอลัมน์ที่คุณต้องการรวมจากนั้นคลิก คีย์หลัก ปุ่มดูภาพหน้าจอ:

doc vlookup ต่อกัน 6

4. จากนั้นคลิกชื่อคอลัมน์อื่นที่คุณต้องการส่งคืนค่าที่ตรงกันแล้วคลิก รวมกัน ในการเลือกตัวคั่นหนึ่งตัวเพื่อแยกค่ารวมดูภาพหน้าจอ:

doc vlookup ต่อกัน 7

5. จากนั้นคลิก Ok ค่าที่เกี่ยวข้องทั้งหมดตามค่าเดียวกันได้ถูกรวมเข้าด้วยกันโดยใช้ตัวคั่นเฉพาะดูภาพหน้าจอ:

doc vlookup ต่อกัน 8 2 doc vlookup ต่อกัน 9

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


Kutools สำหรับ Excel: ด้วย Add-in ของ Excel ที่มีประโยชน์มากกว่า 300 รายการให้ทดลองใช้ฟรีโดยไม่มีข้อ จำกัด ใน 30 วัน ดาวน์โหลดและทดลองใช้ฟรีทันที!

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

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

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

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

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
ด้านล่าง officetab
จัดเรียงความคิดเห็นโดย
ความคิดเห็น (16)
ยังไม่มีการให้คะแนน เป็นคนแรกที่ให้คะแนน!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ได้ผลอย่างไร. กรุณาช่วย. ข้อมูล data1 ผลลัพธ์ a 1 a1 b 2 a2 c b1 b2 c1 c2
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
เมื่อใช้ cusvlookup มีวิธีเพิ่มนามสกุลและเครื่องหมายจุลภาคระหว่างที่อาจปรากฏในคอลัมน์ C
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ชอบฟังก์ชันสำหรับ Excel 2013 แต่แก้ไขเล็กน้อยเพื่อเปลี่ยนอักขระแยกเป็น ";" แทน " " แล้วลบคำนำหน้า ";" จากค่าที่รวมกัน ค่าที่ตรงกับผลลัพธ์ในตัวอย่างของฉันจะมี ;result01 หรือ ;result01;result02 เพิ่มส่วนพิเศษ If Left(xResult, 1) = ";" เพื่อลบส่วนเกิน ";" ที่จุดเริ่มต้นของสตริงหากเป็นอักขระตัวที่ 1 ฉันแน่ใจว่ามีวิธีที่ดีกว่านี้ แต่มันได้ผลสำหรับฉัน :) ฟังก์ชัน CusVlookup (pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" สำหรับแต่ละ rng ใน pWorkRng ถ้า rng = pValue จากนั้น xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" จากนั้น xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ทำ if เงื่อนไขสำหรับผลลัพธ์ถ้าว่างเปล่า

ฟังก์ชัน CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As ช่วง
ผลสลัวเป็นสตริง
ผลลัพธ์ = ""
สำหรับแต่ละ x ใน lookuprange
ถ้า x = lookupval แล้ว
ถ้าไม่ได้ผล = "" แล้ว
ผลลัพธ์ = ผลลัพธ์ & " " & x.Offset(0, indexcol - 1)
อื่น
ผลลัพธ์ = x.Offset(0, indexcol - 1)
End If
ถัดไป x
CusVlookup = ผลลัพธ์
ฟังก์ชั่นเอนด์
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มันวิเศษมาก แต่ฉันกำลังมองหาอย่างอื่น ฉันมีตารางที่มี RollNo StudentName sub1, sub2, sub3 ... ผลลัพธ์ทั้งหมด เมื่อฉันป้อน Rollnumber มันควรให้ผลลัพธ์เช่น "SName Sub1 64, sub2 78,... รวม 389 ผลผ่าน" เป็นไปได้
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มีวิธีลบค่าที่ซ้ำกันใน concatenate หรือไม่?
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดีจาคอบ
อาจเป็นบทความต่อไปนี้สามารถช่วยคุณแก้ปัญหาของคุณได้
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

โปรดลอง หวังว่าจะช่วยคุณได้!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มีวิธีแสดงรายการค่าที่ซ้ำกันเพียงครั้งเดียวโดยใช้โค้ด vba และสูตรด้านบนหรือไม่ ฉันไม่แน่ใจว่าจะใส่คำสั่ง countif>1 ไว้ที่ใดในแถบสูตรหรือใน vba เอง กรุณาช่วย
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
คุณสามารถเพิ่มเงื่อนไขพิเศษสองข้อเพื่อข้ามเซลล์ว่างและข้ามรายการที่ซ้ำกัน:สำหรับ i = 1 ถึง CriteriaRange.Count
ถ้า CriteriaRange.Cells(i).Value = Condition แล้ว
ถ้า ConcatenateRange.Cells(i).Value <> "" แล้ว 'SKIP BANKS
ถ้า InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 แล้ว 'SKIP IF FOUND DUPLICATE
xResult = xResult & ตัวคั่น & ConcatenateRange.Cells (i).Value
End If
End If
End If
ต่อไปฉัน
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันต้องบอกว่าฉันพยายามหาสูตรสำหรับการรวมค่าหลายค่าและคืนค่าเป็นเซลล์เดียวเป็นเวลา 2 วันแล้ว "How To" นี้ช่วยชีวิตฉันไว้!! ขอบคุณมาก! ฉันจะไม่มีวันได้รับมันหากไม่มีโมดูลของคุณ!
ฉันมีคำถาม 2 ข้อแม้ว่า ฉันมีตัวคั่นเป็นลูกน้ำแทนที่จะเป็นช่องว่าง และด้วยเหตุนี้ มันจึงขึ้นต้นด้วยลูกน้ำ มีวิธีป้องกันเครื่องหมายจุลภาคเริ่มต้นแต่เก็บส่วนที่เหลือไว้หรือไม่
คำถามที่สองของฉันคือ เมื่อฉันใช้ที่จับเติม มันจะเปลี่ยนค่าช่วงและค่าของเซลล์ที่ฉันต้องการค้นหา ฉันต้องการให้เปลี่ยนหมายเลขเซลล์ที่ต้องการค้นหาต่อไป แต่คงค่าช่วงเดิมไว้ ฉันจะทำให้สิ่งนี้เกิดขึ้นได้อย่างไร

ขอบคุณมากสำหรับความช่วยเหลือของคุณ!!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
cusVlookup ใช้งานได้ดีสำหรับฉัน อีกวิธีหนึ่งในการมีตัวคั่นที่แตกต่างกันคือการห่อด้วยฟังก์ชันทดแทนสองฟังก์ชัน อันแรก (จากข้างในสู่ข้างนอก) แทนที่ช่องว่างแรกโดยไม่มีที่ว่าง อันที่สองแทนที่ช่องว่างอื่นทั้งหมดด้วย " / " ในตัวฉัน สามารถใช้ "," ถ้าคุณต้องการลูกน้ำ
=SUBSTITUTE(แทนที่(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

นอกจากนี้ ถ้าค่าการค้นหาของคุณไม่ใช่คอลัมน์แรก คุณสามารถใช้ 0 หรือตัวเลขติดลบเพื่อไปที่คอลัมน์ทางซ้าย
=SUBSTITUTE(แทนที่(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี เจฟ
ขอบคุณสำหรับการแบ่งปันของคุณ คุณต้องเป็นผู้ชายที่อบอุ่น
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มันใช้งานได้ดีสำหรับฉัน - มีการเปลี่ยนแปลงหรือไม่เพื่อตรวจสอบว่าเซลล์มีมากกว่าการจับคู่ทั้งหมดหรือไม่ โดยทั่วไปฉันมีรายการงานที่:
คอลัมน์ A: การอ้างอิง (เช่น 10003 10004 10008)
คอลัมน์ B: การอ้างอิงงาน (เช่น 10001)
คอลัมน์ C: งานอ้างอิง (คอลัมน์สำหรับผลลัพธ์ของสูตร) ​​- ตำแหน่งที่จะค้นหาการอ้างอิงงานเพื่อดูว่าแถวใดมีอยู่ในคอลัมน์ A จากนั้นแสดงรายการการอ้างอิงงานของงานเหล่านั้น

เช่น:

แถว | คอลัมน์ A | คอลัมน์ B | คอลัมน์ C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
คุณต้องการใช้ฟังก์ชัน Instr() ซึ่งจะตรวจสอบบางอย่างในสตริงข้อความในเซลล์ คุณยังสามารถใช้ Left() และ Right() ได้หากต้องการทราบรายละเอียดเริ่มต้นหรือสิ้นสุด
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
มีวิธีใดบ้างที่จะได้รับ "ชื่อ" เฉพาะสำหรับ "class1"
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สวัสดี ซิม-จอห์น
บางทีบทความด้านล่างสามารถแก้ปัญหาของคุณได้ โปรดดูที่:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
มีความคิดเห็นยังไม่มีการโพสต์ที่นี่
แสดงความคิดเห็นของคุณ
โพสต์ในฐานะแขก
×
ให้คะแนนโพสต์นี้:
0   ตัวอักษร
สถานที่แนะนำ

ช่องทางอื่นๆ

ลิขสิทธิ์© 2009 - wwwextendoffice.com | สงวนลิขสิทธิ์. ขับเคลื่อนโดย ExtendOffice. | แผนผังเว็บไซต์
Microsoft และโลโก้ Office เป็นเครื่องหมายการค้าหรือเครื่องหมายการค้าจดทะเบียนของ Microsoft Corporation ในสหรัฐอเมริกาและ / หรือประเทศอื่น ๆ
ได้รับการปกป้องโดย Sectigo SSL