รวมสองรายการโดยไม่ซ้ำกัน

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

ตามธรรมเนียมแล้ว มาดูวิธีแก้ปัญหาทั่วไปหลายวิธีกัน ตั้งแต่ “บนหน้าผาก” แบบเดิมๆ ไปจนถึงวิธีที่ซับซ้อนกว่าแต่ดูสง่างาม

วิธีที่ 1: ลบรายการที่ซ้ำกัน

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

แน่นอนว่าวิธีนี้จะใช้ไม่ได้ผลหากข้อมูลในรายการที่มามักจะเปลี่ยนแปลง คุณจะต้องทำซ้ำขั้นตอนทั้งหมดหลังจากการเปลี่ยนแปลงแต่ละครั้งอีกครั้ง 

วิธีที่ 1ก. ตารางหมุน

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

อย่างที่คุณทราบ ตารางเดือยจะไม่สนใจการซ้ำซ้อน ดังนั้นที่ผลลัพธ์ เราจะได้รับรายการที่รวมกันโดยไม่มีการซ้ำซ้อน คอลัมน์เสริมที่มี 1 จำเป็นเท่านั้นเนื่องจาก Excel สามารถสร้างตารางสรุปที่มีอย่างน้อยสองคอลัมน์

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

วิธีที่ 2: สูตรอาร์เรย์

คุณสามารถแก้ปัญหาด้วยสูตร ในกรณีนี้ การคำนวณใหม่และการอัปเดตผลลัพธ์จะเกิดขึ้นโดยอัตโนมัติและทันที ทันทีหลังจากการเปลี่ยนแปลงในรายการเดิม เพื่อความสะดวกและกระชับ เรามาตั้งชื่อรายการของเรากัน List 1 и List 2การใช้ ชื่อผู้จัดการ แถบ สูตร (สูตร — ตัวจัดการชื่อ — สร้าง):

หลังจากตั้งชื่อสูตรที่เราต้องการจะมีลักษณะดังนี้:

เมื่อมองแวบแรกมันดูน่าขนลุก แต่จริงๆ แล้วทุกอย่างไม่ได้น่ากลัวขนาดนั้น ให้ฉันขยายสูตรนี้ในหลายบรรทัดโดยใช้คีย์ผสม Alt+Enter และเยื้องด้วยการเว้นวรรค ดังที่เราทำ ตัวอย่างที่นี่:

ตรรกะที่นี่มีดังต่อไปนี้:

• สูตร INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) เลือกองค์ประกอบที่ไม่ซ้ำทั้งหมดจากรายการแรก ทันทีที่มันหมด จะเริ่มแสดงข้อผิดพลาด #N/A:

  

• สูตร INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) จะแยกองค์ประกอบที่ไม่ซ้ำจากรายการที่สองในลักษณะเดียวกัน
• ฟังก์ชัน IFERROR ที่ซ้อนกัน 1 ฟังก์ชันจะนำเอาท์พุตของฟังก์ชันที่ไม่ซ้ำจากรายการ-2 ก่อน จากนั้นจึงใช้ฟังก์ชันจากลิสต์-XNUMX ทีละรายการ

โปรดทราบว่านี่เป็นสูตรอาร์เรย์ กล่าวคือ หลังจากพิมพ์จะต้องป้อนลงในเซลล์ที่ไม่ปกติ เข้าสู่แต่ด้วยแป้นพิมพ์ลัด Ctrl+เปลี่ยน+เข้าสู่ แล้วคัดลอก (ลาก) ลงไปที่เซลล์ลูกด้วยระยะขอบ

ใน Excel เวอร์ชันภาษาอังกฤษ สูตรนี้มีลักษณะดังนี้:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ),“”) 

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

วิธีที่ 3. Power Query

ถ้ารายการแหล่งที่มาของคุณมีองค์ประกอบจำนวนมาก ตัวอย่างเช่น หลายร้อยหรือหลายพัน ดังนั้นแทนที่จะใช้สูตรอาร์เรย์ที่ช้า ควรใช้วิธีการที่แตกต่างกันโดยพื้นฐาน กล่าวคือเครื่องมือ Add-in ของ Power Query Add-in นี้สร้างขึ้นใน Excel 2016 โดยค่าเริ่มต้น หากคุณมี Excel 2010 หรือ 2013 คุณสามารถดาวน์โหลดและติดตั้งแยกต่างหาก (ฟรี)

อัลกอริทึมของการกระทำมีดังนี้:

1. เปิดแท็บแยกต่างหากของโปรแกรมเสริมที่ติดตั้งไว้ Power Query (ถ้าคุณมี Excel 2010-2013) หรือเพียงแค่ไปที่แท็บ ข้อมูล (ถ้าคุณมี Excel 2016)
2. เลือกรายการแรกแล้วกดปุ่ม จากตาราง/ช่วง (จากช่วง/ตาราง). เมื่อถูกถามเกี่ยวกับการสร้าง “ตารางอัจฉริยะ” จากรายการของเรา เราเห็นด้วย:

   

3. หน้าต่างตัวแก้ไขแบบสอบถามจะเปิดขึ้น ซึ่งคุณสามารถดูข้อมูลที่โหลดและชื่อแบบสอบถาม 1 ตาราง (คุณสามารถเปลี่ยนเป็นของคุณเองได้หากต้องการ)
4. ดับเบิลคลิกที่ส่วนหัวของตาราง (word List 1) และเปลี่ยนชื่อเป็นชื่ออื่น (เช่น คน). ชื่ออะไรไม่สำคัญ แต่ต้องจำชื่อที่ประดิษฐ์ไว้เพราะ จะต้องใช้อีกครั้งในภายหลังเมื่อนำเข้าตารางที่สอง การรวมสองตารางในอนาคตจะใช้ได้ก็ต่อเมื่อส่วนหัวของคอลัมน์ตรงกันเท่านั้น
5. ขยายรายการแบบเลื่อนลงที่มุมซ้ายบน ปิดและดาวน์โหลด และเลือก ปิดและโหลดใน... (ปิด&โหลดไปที่…):

   

6. ในกล่องโต้ตอบถัดไป (อาจดูแตกต่างไปเล็กน้อย – ไม่ต้องตกใจ) ให้เลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น):

   

7. เราทำซ้ำขั้นตอนทั้งหมด (จุดที่ 2-6) สำหรับรายการที่สอง เมื่อเปลี่ยนชื่อส่วนหัวของคอลัมน์ สิ่งสำคัญคือต้องใช้ชื่อเดียวกัน (ผู้คน) เหมือนกับในคิวรีก่อนหน้า
8. ในหน้าต่าง Excel บนแท็บ ข้อมูล หรือบนแท็บ Power Query Choose รับข้อมูล – รวมคำขอ – เพิ่ม (รับข้อมูล — ผสานการสืบค้น — ผนวก):

   

9. ในกล่องโต้ตอบที่ปรากฏขึ้น ให้เลือกคำขอของเราจากรายการดรอปดาวน์:

   

10. เป็นผลให้เราจะได้รับแบบสอบถามใหม่ซึ่งสองรายการจะเชื่อมต่อกัน มันยังคงลบรายการที่ซ้ำกันด้วยปุ่ม ลบแถว – ลบรายการที่ซ้ำกัน (ลบแถว — ลบรายการที่ซ้ำกัน):

    

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

    

ในอนาคต หากมีการเปลี่ยนแปลงหรือเพิ่มเติมรายการเดิม เพียงแค่คลิกขวาเพื่ออัปเดตตารางผลลัพธ์ก็เพียงพอแล้ว

• วิธีรวบรวมหลายตารางจากไฟล์ต่างๆ โดยใช้ Power Query
• การแยกรายการที่ไม่ซ้ำออกจากรายการ
• วิธีเปรียบเทียบสองรายการเพื่อการจับคู่และความแตกต่าง