วิธีเปรียบเทียบสองรายการใน Excel

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

เปรียบเทียบสองรายการใน Excel

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

การกำหนดปัญหา

เราจึงต้องเปรียบเทียบคอลัมน์เหล่านี้ ขั้นตอนการเปรียบเทียบเอกสาร XNUMX ฉบับ มีดังนี้

1. ถ้าเซลล์ที่ไม่ซ้ำของแต่ละรายการเหล่านี้เหมือนกัน และจำนวนรวมของเซลล์ที่ไม่ซ้ำทั้งหมดเหมือนกัน และเซลล์เหมือนกัน รายการเหล่านี้ก็ถือว่าเหมือนกัน ลำดับที่ค่าในรายการนี้ซ้อนกันไม่สำคัญมากนัก
2. เราสามารถพูดคุยเกี่ยวกับความบังเอิญบางส่วนของรายการได้หากค่าที่ไม่ซ้ำกันนั้นเหมือนกัน แต่จำนวนการทำซ้ำต่างกัน ดังนั้น รายการดังกล่าวอาจมีองค์ประกอบจำนวนแตกต่างกัน
3. ข้อเท็จจริงที่ว่าทั้งสองรายการไม่ตรงกันนั้นถูกระบุด้วยชุดค่าที่ไม่ซ้ำกันที่แตกต่างกัน

เงื่อนไขทั้งสามนี้พร้อมกันเป็นเงื่อนไขของปัญหาของเรา

ทางออกของปัญหา

มาสร้างช่วงไดนามิกสองช่วงเพื่อให้เปรียบเทียบรายการได้ง่ายขึ้น แต่ละรายการจะสอดคล้องกับแต่ละรายการ

หากต้องการเปรียบเทียบสองรายการ ให้ทำดังต่อไปนี้:

1. ในคอลัมน์ที่แยกจากกัน เราสร้างรายการค่าที่ไม่ซ้ำซึ่งเจาะจงสำหรับทั้งสองรายการ สำหรับสิ่งนี้เราใช้สูตร: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). ต้องเขียนสูตรเองเป็นสูตรอาร์เรย์
2. มาดูกันว่าค่าที่ไม่ซ้ำกันแต่ละค่าเกิดขึ้นในอาร์เรย์ข้อมูลกี่ครั้ง นี่คือสูตรสำหรับการทำเช่นนี้: =COUNTIF(List1,D5) และ =COUNTI(List2,D5)
3. หากทั้งจำนวนการทำซ้ำและจำนวนค่าที่ไม่ซ้ำเท่ากันในรายการทั้งหมดที่รวมอยู่ในช่วงเหล่านี้ ฟังก์ชันจะคืนค่าเป็น 0 ซึ่งบ่งชี้ว่าการจับคู่คือ XNUMX% ในกรณีนี้ ส่วนหัวของรายการเหล่านี้จะได้รับพื้นหลังสีเขียว
4. หากเนื้อหาที่ไม่ซ้ำทั้งหมดอยู่ในทั้งสองรายการ ให้ส่งคืนโดยสูตร =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) ค่าจะเป็นศูนย์ หาก E1 ไม่มีศูนย์ แต่ค่าดังกล่าวมีอยู่ในเซลล์ E2 และ F2 ในกรณีนี้ ช่วงจะถูกรับรู้ว่าเป็นการจับคู่ แต่เพียงบางส่วนเท่านั้น ในกรณีนี้ ส่วนหัวของรายการที่เกี่ยวข้องจะเปลี่ยนเป็นสีส้ม
5. และถ้าสูตรใดสูตรหนึ่งที่อธิบายข้างต้นคืนค่าที่ไม่ใช่ศูนย์ รายการจะไม่ตรงกันทั้งหมด

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

ตัวอย่างการทดสอบ

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

ในการเปรียบเทียบข้อมูล เราใช้ช่วง A5:B19 ซึ่งเราจะแทรกรายการคู่เหล่านี้สลับกัน เกี่ยวกับสิ่งที่จะเป็นผลมาจากการเปรียบเทียบเราจะเข้าใจโดยสีของรายการเดิม หากต่างกันโดยสิ้นเชิงก็จะกลายเป็นพื้นหลังสีแดง หากข้อมูลบางส่วนเหมือนกัน แสดงว่าเป็นสีเหลือง ในกรณีของข้อมูลประจำตัวที่สมบูรณ์ หัวเรื่องที่เกี่ยวข้องจะเป็นสีเขียว วิธีทำสีขึ้นอยู่กับผลลัพธ์คืออะไร? สิ่งนี้ต้องมีการจัดรูปแบบตามเงื่อนไข

ค้นหาความแตกต่างในสองรายการในสองวิธี

มาอธิบายอีกสองวิธีในการค้นหาความแตกต่างกัน ขึ้นอยู่กับว่ารายการเป็นแบบซิงโครนัสหรือไม่

ตัวเลือก 1 รายการซิงโครนัส

นี่เป็นตัวเลือกที่ง่าย สมมติว่าเรามีรายการดังกล่าว

ในการพิจารณาว่าค่าไม่มาบรรจบกันกี่ครั้งคุณสามารถใช้สูตร: =SUMPRODUCT(—(A2:A20<>B2:B20)). หากเราได้ 0 แสดงว่าทั้งสองรายการเหมือนกัน

ตัวเลือก 2: รายการสับเปลี่ยน

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

วิธีเปรียบเทียบ 2 คอลัมน์ทีละแถว

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

ตัวอย่าง. วิธีเปรียบเทียบ 2 คอลัมน์สำหรับการจับคู่และความแตกต่างในหนึ่งแถว

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

เราควรจดสูตรดังกล่าวเพื่อทำความเข้าใจว่าค่าในทั้งสองคอลัมน์เหมือนกันหรือไม่: =IF(A2=B2, “ตรงกัน”, “”). ตรรกะของฟังก์ชันนี้ง่ายมาก โดยจะเปรียบเทียบค่าในเซลล์ A2 และ B2 และหากค่าเท่ากัน จะแสดงค่า "Coincide" หากข้อมูลต่างกันจะไม่คืนค่าใดๆ คุณยังสามารถตรวจสอบเซลล์เพื่อดูว่ามีเซลล์ที่ตรงกันหรือไม่ ในกรณีนี้ สูตรที่ใช้คือ =IF(A2<>B2, “ไม่ตรงกัน”, “”) หลักการเหมือนกันคือขั้นแรกให้ดำเนินการตรวจสอบ หากปรากฎว่าเซลล์ตรงตามเกณฑ์ ค่า "ไม่ตรงกัน" จะปรากฏขึ้น

นอกจากนี้ยังสามารถใช้สูตรต่อไปนี้ในฟิลด์สูตรเพื่อแสดงทั้ง "ตรงกัน" หากค่าเหมือนกันและ "ไม่ตรงกัน" หากมีค่าต่างกัน: =IF(A2=B2; “ตรงกัน”, “ไม่ตรงกัน”) คุณยังสามารถใช้ตัวดำเนินการอสมการแทนตัวดำเนินการความเท่าเทียมกันได้ เฉพาะลำดับของค่าที่จะแสดงในกรณีนี้จะแตกต่างกันเล็กน้อย: =IF(A2<>B2, “ไม่ตรงกัน”, “ตรงกัน”) หลังจากใช้สูตรรุ่นแรกแล้วผลลัพธ์จะเป็นดังนี้

รูปแบบของสูตรนี้ไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ ดังนั้นหากค่าในคอลัมน์หนึ่งแตกต่างจากค่าอื่นที่เขียนด้วยตัวพิมพ์ใหญ่เท่านั้น โปรแกรมจะไม่สังเกตเห็นความแตกต่างนี้ เพื่อให้การเปรียบเทียบตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ คุณต้องใช้ฟังก์ชันในเกณฑ์ EXACT. อาร์กิวเมนต์ที่เหลือจะไม่เปลี่ยนแปลง: =IF(EXACT(A2,B2), “ตรงกัน”, “ไม่ซ้ำกัน”)

วิธีเปรียบเทียบหลายคอลัมน์สำหรับการจับคู่ในแถวเดียว

เป็นไปได้ที่จะวิเคราะห์ค่าในรายการตามเกณฑ์ทั้งชุด:

1. ค้นหาแถวที่มีค่าเท่ากันทุกที่
2. ค้นหาแถวที่มีรายการที่ตรงกันในสองรายการเท่านั้น

ลองดูตัวอย่างบางส่วนเกี่ยวกับวิธีการดำเนินการในแต่ละกรณีเหล่านี้

ตัวอย่าง. วิธีค้นหารายการที่ตรงกันในแถวเดียวในหลายคอลัมน์ของตาราง

สมมติว่าเรามีชุดของคอลัมน์ที่มีข้อมูลที่เราต้องการ เรากำลังเผชิญกับงานในการกำหนดแถวที่มีค่าเท่ากัน ในการทำเช่นนี้คุณต้องใช้สูตรต่อไปนี้: =IF(และ(A2=B2,A2=C2), “ตรงกัน”, ” “).

หากมีคอลัมน์มากเกินไปในตาราง คุณเพียงแค่ต้องใช้ร่วมกับฟังก์ชัน IF ผู้ประกอบการ COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”match”;” “) ตัวเลขที่ใช้ในสูตรนี้ระบุจำนวนคอลัมน์ที่จะตรวจสอบ ถ้ามันแตกต่างออกไป คุณต้องเขียนให้มากที่สุดเท่าที่เป็นจริงสำหรับสถานการณ์ของคุณ

ตัวอย่าง. วิธีค้นหารายการที่ตรงกันในหนึ่งแถวใน 2 คอลัมน์ของตาราง

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

เราใช้สูตรนี้: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). อาจมีสถานการณ์เมื่อมีคอลัมน์จำนวนมากในตาราง ในกรณีนี้ สูตรจะมีขนาดใหญ่และอาจต้องใช้เวลามากในการเลือกชุดค่าผสมที่จำเป็นทั้งหมด เพื่อแก้ปัญหานี้ คุณต้องใช้ฟังก์ชัน COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “สตริงที่ไม่ซ้ำ”; “สตริงที่ไม่ซ้ำ”)

เราเห็นว่าโดยรวมแล้วเรามีสองหน้าที่ COUNTIF. สำหรับคอลัมน์แรก เราจะกำหนดจำนวนคอลัมน์ที่มีความคล้ายคลึงกับ A2 และสำหรับคอลัมน์ที่สอง เราจะตรวจสอบจำนวนความคล้ายคลึงกันด้วยค่า B2 หากจากการคำนวณตามสูตรนี้ เราได้ค่าศูนย์ แสดงว่าแถวทั้งหมดในคอลัมน์นี้ไม่ซ้ำกัน ถ้ามากกว่านั้น มีความคล้ายคลึงกัน ดังนั้น หากผลจากการคำนวณโดยสูตรสองสูตรและเพิ่มผลลัพธ์สุดท้าย เราได้ค่าศูนย์ ค่าข้อความ "สตริงที่ไม่ซ้ำ" จะถูกส่งกลับ หากตัวเลขนี้มากกว่า แสดงว่าสตริงนี้ไม่ซ้ำกัน

วิธีเปรียบเทียบ 2 คอลัมน์ใน Excel สำหรับการจับคู่

ทีนี้ลองมาดูตัวอย่างกัน สมมติว่าเรามีตารางที่มีสองคอลัมน์ คุณต้องตรวจสอบว่าตรงกันหรือไม่ ในการทำเช่นนี้ คุณต้องใช้สูตรซึ่งฟังก์ชันจะถูกใช้ด้วย IF, และตัวดำเนินการ COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “ไม่มีรายการที่ตรงกันในคอลัมน์ B”, “มีรายการที่ตรงกันในคอลัมน์ B”)

ไม่ต้องดำเนินการใดๆ เพิ่มเติม หลังจากคำนวณผลลัพธ์ด้วยสูตรนี้แล้ว เราจะได้ถ้าค่าอาร์กิวเมนต์ที่สามของฟังก์ชัน IF การแข่งขัน หากไม่มีเนื้อหาของอาร์กิวเมนต์ที่สอง

วิธีเปรียบเทียบ 2 คอลัมน์ใน Excel สำหรับการจับคู่และเน้นด้วยสี

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

การค้นหาและเน้นการจับคู่ตามสีในหลายคอลัมน์

ในการระบุการจับคู่และไฮไลท์ คุณต้องเลือกช่วงข้อมูลที่จะทำการตรวจสอบก่อน จากนั้นเปิดรายการ "การจัดรูปแบบตามเงื่อนไข" บนแท็บ "หน้าแรก" จากนั้นเลือก "ค่าที่ซ้ำกัน" เป็นกฎการเลือกเซลล์

หลังจากนั้นกล่องโต้ตอบใหม่จะปรากฏขึ้นซึ่งในรายการป๊อปอัปด้านซ้ายเราจะพบตัวเลือก "ทำซ้ำ" และในรายการด้านขวาเราจะเลือกสีที่จะใช้สำหรับการเลือก หลังจากที่เราคลิกปุ่ม "ตกลง" พื้นหลังของเซลล์ทั้งหมดที่มีความคล้ายคลึงกันจะถูกเลือก จากนั้นเพียงเปรียบเทียบคอลัมน์ด้วยตา

การค้นหาและเน้นเส้นที่ตรงกัน

เทคนิคในการตรวจสอบว่าสตริงตรงกันหรือไม่แตกต่างกันเล็กน้อย ขั้นแรก เราต้องสร้างคอลัมน์เพิ่มเติม จากนั้นเราจะใช้ค่าที่รวมกันโดยใช้ตัวดำเนินการ & ในการทำเช่นนี้คุณต้องเขียนสูตรของแบบฟอร์ม: =A2&B2&C2&D2.

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

เราเห็นว่าไม่มีอะไรยากในการค้นหาการทำซ้ำ Excel มีเครื่องมือที่จำเป็นทั้งหมดสำหรับสิ่งนี้ สิ่งสำคัญคือต้องฝึกฝนก่อนนำความรู้ทั้งหมดนี้ไปปฏิบัติ