เชื่อมข้อความตามเงื่อนไข

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

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

กล่าวอีกนัยหนึ่ง เราต้องการเครื่องมือที่จะติด (เชื่อมโยง) ข้อความตามเงื่อนไข - อะนาล็อกของฟังก์ชัน ซัมเมสลี (ซูมิฟ)แต่สำหรับข้อความ

วิธีที่ 0. สูตร

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

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

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

วิธีที่ 1 Macrofunction ของการติดกาวโดยเงื่อนไขเดียว

หากรายชื่อเดิมไม่ได้จัดเรียงตามบริษัท สูตรง่ายๆ ข้างต้นจะไม่สามารถใช้งานได้ แต่คุณสามารถใช้ฟังก์ชันแบบกำหนดเองขนาดเล็กใน VBA ได้อย่างง่ายดาย เปิด Visual Basic Editor โดยการกดแป้นพิมพ์ลัด Alt + F11 หรือใช้ปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา). ในหน้าต่างที่เปิดขึ้น ให้แทรกโมดูลเปล่าใหม่ผ่านเมนู แทรก – โมดูล และคัดลอกข้อความของฟังก์ชันของเราที่นั่น:

ฟังก์ชั่น MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " การติดกาวไม่เท่ากัน - เราจะออกโดยมีข้อผิดพลาด หาก SearchRange.Count <> TextRange.Count จากนั้น MergeIf = CVErr(xlErrRef) ออกจากฟังก์ชัน End ถ้า 'ผ่านเซลล์ทั้งหมด ให้ตรวจสอบเงื่อนไขและรวบรวมข้อความในตัวแปร OutText For i = 1 To SearchRange Cells.Count ถ้า SearchRange.Cells(i) ชอบเงื่อนไข จากนั้น OutText = OutText & TextRange.Cells(i) & Delimeter ถัดไป i 'แสดงผลลัพธ์โดยไม่มีตัวคั่นสุดท้าย MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End การทำงาน  

หากคุณกลับไปที่ Microsoft Excel ในรายการฟังก์ชัน (button fx ในแถบสูตรหรือแท็บ สูตร – ฟังก์ชันแทรก) ก็จะสามารถค้นหาฟังก์ชันของเราได้ ผสานถ้า ในหมวดหมู่ กำหนดโดยผู้ใช้ (กำหนดโดยผู้ใช้). อาร์กิวเมนต์ของฟังก์ชันมีดังนี้:

วิธีที่ 2 เชื่อมข้อความตามเงื่อนไขที่ไม่แน่นอน

ถ้าเราแทนที่อักขระตัวแรกในบรรทัดที่ 13 ของมาโครของเรา = ไปยังตัวดำเนินการจับคู่โดยประมาณ Likeจากนั้นจะเป็นไปได้ที่จะทำการติดกาวโดยการจับคู่ที่ไม่แน่นอนของข้อมูลเริ่มต้นกับเกณฑ์การเลือก ตัวอย่างเช่น หากสามารถเขียนชื่อบริษัทในรูปแบบต่างๆ ได้ เราก็สามารถตรวจสอบและรวบรวมทั้งหมดได้ด้วยฟังก์ชันเดียว:

รองรับไวด์การ์ดมาตรฐาน:

• เครื่องหมายดอกจัน (*) – หมายถึงจำนวนอักขระใด ๆ (รวมถึงการขาดหายไป)
• เครื่องหมายคำถาม (?) – ย่อมาจากอักขระตัวเดียว
• เครื่องหมายปอนด์ (#) – ย่อมาจากตัวเลขใดหลักหนึ่ง (0-9)

โดยค่าเริ่มต้น ตัวดำเนินการ Like จะคำนึงถึงขนาดตัวพิมพ์ เช่น เข้าใจ ตัวอย่างเช่น "Orion" และ "orion" เป็นบริษัทต่างๆ หากต้องการละเว้นตัวพิมพ์ คุณสามารถเพิ่มบรรทัดที่จุดเริ่มต้นของโมดูลในตัวแก้ไข Visual Basic ตัวเลือกเปรียบเทียบข้อความซึ่งจะเปลี่ยน Like เป็นแบบไม่คำนึงถึงขนาดตัวพิมพ์

ด้วยวิธีนี้ คุณสามารถเขียนมาสก์ที่ซับซ้อนมากสำหรับตรวจสอบเงื่อนไขได้ เช่น

• ?1##??777RUS – การเลือกป้ายทะเบียนทั้งหมดของภูมิภาค 777 เริ่มต้นด้วย 1
• LLC* – บริษัททั้งหมดที่มีชื่อขึ้นต้นด้วย LLC
• ##7## – ผลิตภัณฑ์ทั้งหมดที่มีรหัสดิจิทัลห้าหลัก โดยที่หลักที่สามคือ 7
• ????? – ชื่อทั้งหมดห้าตัวอักษร ฯลฯ
  

วิธีที่ 3 ฟังก์ชันมาโครสำหรับการติดข้อความภายใต้สองเงื่อนไข

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

ฟังก์ชั่น MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'อักขระตัวคั่น (สามารถถูกแทนที่ด้วยช่องว่างหรือ ; ฯลฯ) e.) 'หากช่วงการตรวจสอบและช่วงการติดกาวไม่เท่ากัน ให้ออกโดยมีข้อผิดพลาด หาก SearchRange1.Count <> TextRange.Count หรือ SearchRange2.Count <> TextRange.Count จากนั้น MergeIfs = CVErr(xlErrRef) ออกจากฟังก์ชัน สิ้นสุด ถ้า 'ตรวจดูทุกเซลล์ ตรวจสอบเงื่อนไขทั้งหมด และรวบรวมข้อความลงในตัวแปร OutText For i = 1 To SearchRange1.Cells.Count ถ้า SearchRange1.Cells(i) = Condition1 และ SearchRange2.Cells(i) = Condition2 จากนั้น OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'แสดงผลลัพธ์โดยไม่มีตัวคั่นสุดท้าย MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

จะใช้ในลักษณะเดียวกันทุกประการ – ตอนนี้ต้องระบุอาร์กิวเมนต์เพิ่มเติมเท่านั้น:

วิธีที่ 4 การจัดกลุ่มและการติดกาวใน Power Query

คุณสามารถแก้ปัญหาได้โดยไม่ต้องเขียนโปรแกรมใน VBA หากคุณใช้ Add-in ของ Power Query ฟรี สำหรับ Excel 2010-2013 สามารถดาวน์โหลดได้ ที่นี่ และใน Excel 2016 จะมีอยู่แล้วตามค่าเริ่มต้น ลำดับของการกระทำจะเป็นดังนี้:

Power Query ไม่ทราบวิธีการทำงานกับตารางปกติ ดังนั้นขั้นตอนแรกคือเปลี่ยนตารางของเราให้เป็นตารางที่ "ฉลาด" ในการดำเนินการนี้ ให้เลือกและกดชุดค่าผสม Ctrl+T หรือเลือกจากแท็บ หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง). บนแท็บที่ปรากฏขึ้น นวกรรมิก (ออกแบบ) คุณสามารถตั้งชื่อตารางได้ (ฉันออกจากมาตรฐาน 1 ตาราง):

ตอนนี้ ให้โหลดตารางของเราลงใน Add-in ของ Power Query ในการทำเช่นนี้บนแท็บ ข้อมูล (ถ้าคุณมี Excel 2016) หรือบนแท็บ Power Query (ถ้าคุณมี Excel 2010-2013) คลิก จากโต๊ะ (ข้อมูล — จากตาราง):

ในหน้าต่างตัวแก้ไขแบบสอบถามที่เปิดขึ้น ให้เลือกคอลัมน์โดยคลิกที่ส่วนหัว บริษัท และกดปุ่มด้านบน บัญชีกลุ่ม (จัดกลุ่มตาม). ป้อนชื่อคอลัมน์ใหม่และประเภทของการดำเนินการในกลุ่ม – ทุกสาย (ทุกแถว):

คลิกตกลงและเราจะได้รับตารางย่อของค่าที่จัดกลุ่มสำหรับแต่ละบริษัท เนื้อหาของตารางจะมองเห็นได้ชัดเจนหากคุณคลิกซ้ายบนพื้นหลังสีขาวของเซลล์ (ไม่ใช่ที่ข้อความ!) ในคอลัมน์ผลลัพธ์:

ตอนนี้ มาเพิ่มอีกหนึ่งคอลัมน์ โดยใช้ฟังก์ชัน เราติดเนื้อหาของคอลัมน์ที่อยู่ในแต่ละตารางขนาดเล็ก โดยคั่นด้วยเครื่องหมายจุลภาค ในการทำเช่นนี้บนแท็บ เพิ่มคอลัมน์ เรากด คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง) และในหน้าต่างที่ปรากฏขึ้น ให้ป้อนชื่อของคอลัมน์ใหม่และสูตรการมีเพศสัมพันธ์ในภาษา M ที่สร้างไว้ใน Power Query:

โปรดทราบว่าฟังก์ชัน M ทั้งหมดคำนึงถึงขนาดตัวพิมพ์ (ต่างจาก Excel) หลังจากคลิกที่ OK เราได้รับคอลัมน์ใหม่พร้อมที่อยู่ติดกาว:

มันยังคงลบคอลัมน์ที่ไม่จำเป็นอยู่แล้ว ที่อยู่ตาราง (คลิกขวาที่หัวเรื่อง) ลบคอลัมน์) และอัปโหลดผลลัพธ์ไปยังแผ่นงานโดยคลิกที่แท็บ หน้าแรก — ปิดและดาวน์โหลด (หน้าแรก — ปิดและโหลด):

ความแตกต่างที่สำคัญ: ไม่เหมือนกับวิธีการ (ฟังก์ชัน) ก่อนหน้านี้ ตารางจาก Power Query จะไม่อัปเดตโดยอัตโนมัติ หากจะมีการเปลี่ยนแปลงใดๆ ในแหล่งข้อมูลในอนาคต คุณจะต้องคลิกขวาที่ใดก็ได้ในตารางผลลัพธ์และเลือกคำสั่ง อัปเดตและบันทึก (รีเฟรช).

• วิธีแยกสตริงข้อความยาวออกเป็นส่วนๆ
• หลายวิธีในการติดข้อความจากเซลล์ต่างๆ ให้เป็นหนึ่งเดียว
• การใช้ตัวดำเนินการ Like เพื่อทดสอบข้อความกับหน้ากาก