บทความนี้จะใช้เวลาประมาณ 10 นาทีในการอ่าน ในอีก 5 นาทีข้างหน้า คุณสามารถเปรียบเทียบสองคอลัมน์ใน Excel ได้อย่างง่ายดาย และค้นหาว่ามีคอลัมน์ที่ซ้ำกันหรือไม่ ลบออก หรือไฮไลต์เป็นสี ดังนั้นถึงเวลาแล้ว!
Excel เป็นแอปพลิเคชั่นที่ทรงพลังและยอดเยี่ยมมากสำหรับการสร้างและประมวลผลข้อมูลจำนวนมาก หากคุณมีเวิร์กบุ๊กหลายตัวที่มีข้อมูล (หรือตารางขนาดใหญ่เพียงตารางเดียว) คุณอาจต้องการเปรียบเทียบ 2 คอลัมน์ ค้นหาค่าที่ซ้ำกัน จากนั้นดำเนินการบางอย่างกับสิ่งเหล่านี้ เช่น ลบ ไฮไลต์ หรือล้างเนื้อหา คอลัมน์สามารถอยู่ในตารางเดียวกัน ติดกันหรือไม่ติดกัน อาจอยู่บนแผ่นงาน 2 แผ่น หรือแม้แต่ในหนังสือคนละเล่ม
ลองนึกภาพเรามี 2 คอลัมน์ที่มีชื่อคน – 5 ชื่อต่อคอลัมน์ A และ 3 ชื่อในคอลัมน์ B. คุณต้องเปรียบเทียบชื่อในสองคอลัมน์นี้และค้นหารายการที่ซ้ำกัน ตามที่คุณเข้าใจ ข้อมูลนี้เป็นข้อมูลสมมติ ยกตัวอย่างเท่านั้น ในตารางจริง เรากำลังจัดการกับระเบียนนับพันหรือหลายหมื่นรายการ
ตัวเลือก A: ทั้งสองคอลัมน์อยู่ในแผ่นงานเดียวกัน ตัวอย่างเช่น คอลัมน์ A และคอลัมน์ B.
ตัวเลือก B: คอลัมน์อยู่บนแผ่นงานต่างๆ ตัวอย่างเช่น คอลัมน์ A บนแผ่น Sheet2 และคอลัมน์ A บนแผ่น Sheet3.
Excel 2013, 2010 และ 2007 มีเครื่องมือในตัว ลบรายการที่ซ้ำกัน (Remove Duplicates) แต่ในสถานการณ์นี้ไม่มีอำนาจ เนื่องจากไม่สามารถเปรียบเทียบข้อมูลใน 2 คอลัมน์ได้ นอกจากนี้ยังสามารถลบได้เฉพาะรายการที่ซ้ำกันเท่านั้น ไม่มีตัวเลือกอื่นเช่นการเน้นหรือการเปลี่ยนสี และชี้!
ต่อไป ฉันจะแสดงวิธีที่เป็นไปได้ในการเปรียบเทียบสองคอลัมน์ใน Excel ซึ่งจะช่วยให้คุณสามารถค้นหาและลบระเบียนที่ซ้ำกัน
เปรียบเทียบ 2 คอลัมน์ใน Excel และค้นหารายการที่ซ้ำกันโดยใช้สูตร
ตัวเลือก A: ทั้งสองคอลัมน์อยู่ในแผ่นงานเดียวกัน
- ในเซลล์ว่างแรก (ในตัวอย่างของเราคือเซลล์ C1) เราเขียนสูตรต่อไปนี้:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
ในสูตรของเรา A1 นี่คือเซลล์แรกของคอลัมน์แรกที่เราจะเปรียบเทียบ $บี$1 и $บี$10000 นี่คือที่อยู่ของเซลล์แรกและเซลล์สุดท้ายของคอลัมน์ที่สองซึ่งเราจะทำการเปรียบเทียบ สังเกตการอ้างอิงแบบสัมบูรณ์ – ตัวอักษรคอลัมน์และหมายเลขแถวนำหน้าด้วยเครื่องหมายดอลลาร์ ($) ฉันใช้การอ้างอิงแบบสัมบูรณ์เพื่อให้ที่อยู่เซลล์ยังคงเหมือนเดิมเมื่อคัดลอกสูตร
หากคุณต้องการค้นหารายการที่ซ้ำกันในคอลัมน์ Bให้เปลี่ยนการอ้างอิงเพื่อให้สูตรมีลักษณะดังนี้:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")แทนที่ "เหลือเพียง"และ"ซ้ำ» คุณสามารถเขียนป้ายกำกับของคุณเองได้ เช่น «ไม่พบ"และ"พบ“หรือปล่อยไว้เท่านั้น”ซ้ำ' และป้อนอักขระเว้นวรรคแทนค่าที่สอง ในกรณีหลัง เซลล์ที่ไม่พบข้อมูลซ้ำกันจะยังคงว่างเปล่า และฉันเชื่อว่าการแสดงข้อมูลนี้สะดวกที่สุดสำหรับการวิเคราะห์เพิ่มเติม
- ทีนี้ลองคัดลอกสูตรของเราไปที่เซลล์ทั้งหมดในคอลัมน์ Cไปจนถึงแถวล่างสุดซึ่งมีข้อมูลอยู่ในคอลัมน์ A. เมื่อต้องการทำสิ่งนี้ ให้เลื่อนตัวชี้เมาส์ไปที่มุมล่างขวาของเซลล์ C1, ตัวชี้จะอยู่ในรูปของเป้าเล็งสีดำ ดังแสดงในภาพด้านล่าง:คลิกปุ่มซ้ายของเมาส์ค้างไว้แล้วลากเส้นขอบของเฟรมลง โดยเน้นเซลล์ทั้งหมดที่คุณต้องการแทรกสูตร เมื่อเลือกเซลล์ที่ต้องการทั้งหมดแล้ว ให้ปล่อยปุ่มเมาส์:
คลิกปุ่มซ้ายของเมาส์ค้างไว้แล้วลากเส้นขอบของเฟรมลง โดยเน้นเซลล์ทั้งหมดที่คุณต้องการแทรกสูตร เมื่อเลือกเซลล์ที่ต้องการทั้งหมดแล้ว ให้ปล่อยปุ่มเมาส์:
เคล็ดลับ: ในตารางขนาดใหญ่ การคัดลอกสูตรจะเร็วขึ้นหากคุณใช้แป้นพิมพ์ลัด เน้นเซลล์ C1 และกด Ctrl + C (เพื่อคัดลอกสูตรไปยังคลิปบอร์ด) จากนั้นคลิก Ctrl + Shift + สิ้นสุด (เพื่อเลือกเซลล์ที่ไม่ว่างทั้งหมดในคอลัมน์ C) และสุดท้ายกด Ctrl + V (เพื่อแทรกสูตรลงในเซลล์ที่เลือกทั้งหมด)
- เยี่ยมมาก ตอนนี้ค่าที่ซ้ำกันทั้งหมดจะถูกทำเครื่องหมายเป็น “ซ้ำ":
ตัวเลือก B: สองคอลัมน์อยู่ในแผ่นงานที่แตกต่างกัน (ในสมุดงานที่แตกต่างกัน)
- ในเซลล์แรกของคอลัมน์ว่างแรกบนเวิร์กชีต Sheet2 (ในกรณีของเราคือคอลัมน์ B) ป้อนสูตรต่อไปนี้:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Here Sheet3 คือชื่อชีตที่มีคอลัมน์ที่ 2 และ $ ก $ 1: $ ก $ 10000 คือที่อยู่ของเซลล์ตั้งแต่ที่ 1 ถึงสุดท้ายในคอลัมน์ที่ 2 นี้
- คัดลอกสูตรไปยังเซลล์ทั้งหมดในคอลัมน์ B (เหมือนกับตัวเลือก A)
- เราได้รับผลลัพธ์นี้:
กำลังประมวลผลรายการซ้ำที่พบ
เยี่ยมมาก เราพบรายการในคอลัมน์แรกซึ่งมีอยู่ในคอลัมน์ที่สองด้วย ตอนนี้เราต้องทำอะไรกับพวกเขา การดูระเบียนที่ซ้ำกันทั้งหมดในตารางด้วยตนเองนั้นค่อนข้างไม่มีประสิทธิภาพและใช้เวลานานเกินไป มีวิธีที่ดีกว่า
แสดงเฉพาะแถวที่ซ้ำกันในคอลัมน์ A
หากคอลัมน์ของคุณไม่มีส่วนหัว คุณจำเป็นต้องเพิ่มคอลัมน์เหล่านั้น เมื่อต้องการทำสิ่งนี้ ให้วางเคอร์เซอร์บนตัวเลขที่แสดงบรรทัดแรก และมันจะกลายเป็นลูกศรสีดำ ดังแสดงในรูปด้านล่าง:
คลิกขวาและเลือกจากเมนูบริบท การแทรก (แทรก):
ตั้งชื่อคอลัมน์ เช่น “Name"และ"ทำซ้ำ?» จากนั้นเปิดแท็บ ข้อมูล (ข้อมูล) และกด ตัวกรอง (กรอง):
หลังจากนั้นคลิกที่ลูกศรสีเทาเล็ก ๆ ถัดจาก "ทำซ้ำ?« เพื่อเปิดเมนูตัวกรอง ยกเลิกการเลือกรายการทั้งหมดในรายการนี้ ยกเว้น ซ้ำและกด OK.
นั่นคือทั้งหมด ตอนนี้คุณเห็นเฉพาะองค์ประกอบเหล่านั้นของคอลัมน์ Аซึ่งซ้ำกันในคอลัมน์ В. มีเพียงสองเซลล์ดังกล่าวในตารางการฝึกของเรา แต่ตามที่คุณเข้าใจ ในทางปฏิบัติจะมีเซลล์เหล่านี้อีกมาก
เพื่อแสดงแถวทั้งหมดของคอลัมน์อีกครั้ง А, คลิกสัญลักษณ์ตัวกรองในคอลัมน์ Вซึ่งตอนนี้ดูเหมือนกรวยที่มีลูกศรเล็กๆ แล้วเลือก เลือกทั้งหมด (เลือกทั้งหมด). หรือคุณสามารถทำเช่นเดียวกันผ่าน Ribbon โดยคลิก ข้อมูล (ข้อมูล) > เลือก & กรอง (เรียง & กรอง) > ทูโทนช็อคชิพ (ชัดเจน) ตามที่แสดงในภาพหน้าจอด้านล่าง:
เปลี่ยนสีหรือไฮไลต์ที่พบซ้ำ
ถ้าหมายเหตุ “ซ้ำ” ไม่เพียงพอสำหรับวัตถุประสงค์ของคุณ และคุณต้องการทำเครื่องหมายเซลล์ที่ซ้ำกันด้วยสีแบบอักษร สีเติม หรือวิธีการอื่น...
ในกรณีนี้ ให้กรองข้อมูลที่ซ้ำกันตามที่แสดงด้านบน เลือกเซลล์ที่กรองแล้วทั้งหมดแล้วคลิก Ctrl + 1เพื่อเปิดกล่องโต้ตอบ จัดรูปแบบเซลล์ (รูปแบบเซลล์). ตัวอย่างเช่น เปลี่ยนสีเติมของเซลล์ในแถวที่ซ้ำกันเป็นสีเหลืองสดใส แน่นอน คุณสามารถเปลี่ยนสีเติมด้วยเครื่องมือได้ ใส่ (เติมสี) แท็บ หน้าแรก (หน้าแรก) แต่ข้อดีของกล่องโต้ตอบ จัดรูปแบบเซลล์ (รูปแบบเซลล์) ซึ่งคุณสามารถกำหนดค่าตัวเลือกการจัดรูปแบบทั้งหมดได้พร้อมกัน
ตอนนี้คุณจะไม่พลาดทุกเซลล์ที่ซ้ำกันอย่างแน่นอน:
การลบค่าที่ซ้ำกันออกจากคอลัมน์แรก
กรองตารางเพื่อแสดงเฉพาะเซลล์ที่มีค่าที่ซ้ำกัน แล้วเลือกเซลล์เหล่านั้น
หาก 2 คอลัมน์ที่คุณกำลังเปรียบเทียบอยู่บนชีตต่างกันนั่นคือในตารางต่างๆ ให้คลิกขวาที่ช่วงที่เลือกและเลือก ลบแถว (ลบบรรทัด):
ข่าวประชา OKเมื่อ Excel ขอให้คุณยืนยันว่าคุณต้องการลบทั้งแถวของแผ่นงานจริงๆ แล้วล้างตัวกรอง อย่างที่คุณเห็น เหลือเฉพาะแถวที่มีค่าไม่ซ้ำกัน:
ถ้า 2 คอลัมน์อยู่บนแผ่นเดียวกันใกล้กัน (ติดกัน) หรือไม่ใกล้กัน (ไม่ติดกัน) จากนั้นขั้นตอนการลบรายการที่ซ้ำกันจะซับซ้อนขึ้นเล็กน้อย เราไม่สามารถลบทั้งแถวที่มีค่าที่ซ้ำกัน เนื่องจากจะเป็นการลบเซลล์ออกจากคอลัมน์ที่สองด้วย ดังนั้นให้เหลือเฉพาะรายการที่ไม่ซ้ำในคอลัมน์ А, ทำเช่นนี้:
- กรองตารางเพื่อแสดงเฉพาะค่าที่ซ้ำกันและเลือกเซลล์เหล่านั้น คลิกขวาที่พวกมันและเลือกจากเมนูบริบท เนื้อหาที่ชัดเจน (เนื้อหาที่ชัดเจน).
- ทำความสะอาดตัวกรอง
- เลือกเซลล์ทั้งหมดในคอลัมน์ А, เริ่มจากเซลล์ A1 ไปจนถึงด้านล่างที่มีข้อมูล
- คลิก ข้อมูล (ข้อมูล) และกด เรียงลำดับ A ถึง Z (เรียงจาก A ถึง Z). ในกล่องโต้ตอบที่เปิดขึ้น ให้เลือก ดำเนินการต่อด้วยการเลือกปัจจุบัน (เรียงตามส่วนที่เลือก) แล้วคลิกปุ่ม Black (การเรียงลำดับ):
- ลบคอลัมน์ที่มีสูตร คุณจะไม่ต้องการมันอีกต่อไป จากนี้ไปคุณจะมีเพียงค่าที่ไม่ซ้ำกัน
- แค่นั้นแหละ ตอนนี้คอลัมน์ А มีเฉพาะข้อมูลที่ไม่ซ้ำที่ไม่อยู่ในคอลัมน์ В:
อย่างที่คุณเห็น การลบรายการที่ซ้ำกันออกจากสองคอลัมน์ใน Excel โดยใช้สูตรนั้นไม่ใช่เรื่องยาก