ไปยังเนื้อหาหลัก

วิธีการต่อข้อความตามเกณฑ์ใน Excel ได้อย่างง่ายดาย?

สมมติว่าฉันมีคอลัมน์ของหมายเลข ID ซึ่งมีรายการที่ซ้ำกันและคอลัมน์ชื่อและตอนนี้ฉันต้องการเชื่อมต่อชื่อตามหมายเลข ID ที่ไม่ซ้ำกันตามภาพหน้าจอด้านซ้ายที่แสดงเพื่อรวมข้อความอย่างรวดเร็วตามเกณฑ์เราจะทำอย่างไร ทำใน Excel?

doc รวมข้อความตามเกณฑ์ 1

เชื่อมต่อข้อความตามเกณฑ์ด้วย User Defined Function

เชื่อมต่อข้อความตามเกณฑ์ด้วย Kutools for Excel

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

1. ยกตัวอย่างข้อมูลต่อไปนี้คุณต้องแยกหมายเลข ID เฉพาะก่อนโปรดใช้สูตรอาร์เรย์นี้: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),"")， ป้อนสูตรนี้ลงในเซลล์ว่างตัวอย่างเช่น D2 จากนั้นกด Ctrl + Shift + Enter คีย์เข้าด้วยกันดูภาพหน้าจอ:

doc รวมข้อความตามเกณฑ์ 2

ปลาย: ในสูตรข้างต้น A2: A15 คือช่วงข้อมูลรายการที่คุณต้องการแยกค่าที่ไม่ซ้ำกัน D1 เป็นเซลล์แรกของคอลัมน์ที่คุณต้องการใส่ผลลัพธ์การแยก

2. จากนั้นลากที่จับเติมลงเพื่อแยกค่าที่ไม่ซ้ำกันทั้งหมดจนกระทั่งช่องว่างปรากฏขึ้นดูภาพหน้าจอ:

doc รวมข้อความตามเกณฑ์ 3

3. ในขั้นตอนนี้คุณควรสร้างไฟล์ ฟังก์ชันที่ผู้ใช้กำหนด หากต้องการรวมชื่อตามหมายเลขประจำตัวที่ไม่ซ้ำกันโปรดกดปุ่ม ALT + F11 และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

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

รหัส VBA: ต่อข้อความตามเกณฑ์

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

5. จากนั้นบันทึกและปิดรหัสนี้กลับไปที่แผ่นงานของคุณแล้วป้อนสูตรนี้ลงในเซลล์ E2 = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") ดูภาพหน้าจอ:

doc รวมข้อความตามเกณฑ์ 4

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

doc รวมข้อความตามเกณฑ์ 5

ทิปส์:

1. ในสูตรข้างต้น A2: A15 เป็นข้อมูลดั้งเดิมที่คุณต้องการรวมโดยยึดตาม D2 คือค่าเฉพาะที่คุณแยกออกมาและ B2: B15 คือคอลัมน์ชื่อที่คุณต้องการรวมเข้าด้วยกัน

2. อย่างที่คุณเห็นฉันรวมค่าที่คั่นด้วยลูกน้ำคุณสามารถใช้อักขระอื่นใดก็ได้โดยเปลี่ยนเครื่องหมายจุลภาค“,” ของสูตรตามที่คุณต้องการ

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

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

หลังจากการติดตั้ง Kutools สำหรับ Excelทำตามขั้นตอนต่อไปนี้:

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

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

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

doc รวมข้อความตามเกณฑ์ 7

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

doc รวมข้อความตามเกณฑ์ 8

5. หลังจากเสร็จสิ้นการตั้งค่าเหล่านี้คลิก OK เพื่อออกจากกล่องโต้ตอบและข้อมูลในคอลัมน์ B ถูกรวมเข้าด้วยกันตามคอลัมน์หลัก A ดูภาพหน้าจอ:

doc รวมข้อความตามเกณฑ์ 9

ด้วยคุณสมบัตินี้ปัญหาต่อไปนี้จะได้รับการแก้ไขโดยเร็วที่สุด:

วิธีการรวมหลายแถวเป็นหนึ่งเดียวและรวมรายการที่ซ้ำกันใน Excel

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

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

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์  |   VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่   |  สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์   |  ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก:  12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด

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

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (38)
No ratings yet. Be the first to rate!
Laurie S.
Laurie S.
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
Reply
0
0
skyyang
skyyang     Laurie S.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
View Attachments (1 / 5)
Reply
Report
0
0
Md. Zaker Hossain
Md. Zaker Hossain
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
Reply
0
0
skyyang
skyyang     Md. Zaker Hossain
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
Reply
Report
0
0
Md. Zaker Hossain
Md. Zaker Hossain     skyyang
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
Reply
0
0
AA
AA
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
Reply
0
0
AS
AS
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
Reply
0
0
Pete
Pete
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
Reply
0
0
Merijn
Merijn
This comment was minimized by the moderator on the site
BTW i used the VBA solution
Reply
0
0
Merijn
Merijn
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
Reply
0
0
cadrose97
cadrose97
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
Reply
Report
0
0
skyyang
skyyang     cadrose97
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
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
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
Reply
Report
0
0
victor
victor
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
Reply
0
0
David
David
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
Reply
0
0
skyyang
skyyang     David
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
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 & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations