วิธีค้นหารายการที่ซ้ำกันระหว่างสองคอลัมน์ใน Google ชีต

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

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

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

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

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

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

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

เปิดสเปรดชีตที่คุณต้องการตรวจสอบรายการซ้ำใน Google ชีต ขั้นแรก เลือกคอลัมน์แรก (A) เพื่อตรวจสอบกับคอลัมน์ B คุณสามารถไฮไลต์ทั้งคอลัมน์ได้โดยคลิกที่ตัวอักษรของคอลัมน์ด้านบน

จากนั้น คลิกเมนู 'รูปแบบ' จากแถบเมนูและเลือก 'การจัดรูปแบบตามเงื่อนไข'

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

จากนั้นคลิกเมนูแบบเลื่อนลงภายใต้ 'กฎรูปแบบ' และเลือกตัวเลือก 'สูตรที่กำหนดเองคือ'

ตอนนี้ คุณต้องป้อนสูตรที่กำหนดเองในกล่อง 'ค่าหรือสูตร'

หากคุณเลือกทั้งคอลัมน์ (B:B) ให้ป้อนสูตร COUNTIF ต่อไปนี้ลงในช่อง "ค่าหรือสูตร" ใต้กฎการจัดรูปแบบ:

=countif($B:$B,$A2)>0

หรือ,

ถ้าคุณเลือกช่วงของเซลล์ในคอลัมน์ (เช่น ร้อยเซลล์ A2:A30) ให้ใช้สูตรนี้:

=COUNTIF($B$2:$B$30, $A2)>0

เมื่อคุณป้อนสูตร อย่าลืมแทนที่ตัวอักษร 'B' ทั้งหมดในสูตรด้วยตัวอักษรของคอลัมน์ที่คุณไฮไลต์ไว้ เรากำลังเพิ่มเครื่องหมาย '$' ก่อนการอ้างอิงเซลล์เพื่อทำให้เป็นช่วงที่แน่นอน ดังนั้นจึงไม่เปลี่ยนแปลงเราใช้สูตร

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

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

หรือคุณสามารถใช้เครื่องมือการจัดรูปแบบทั้งเจ็ด (ตัวหนา ตัวเอียง ขีดเส้นใต้ ขีดทับ สีข้อความ สีเติม) ใต้ส่วน "รูปแบบการจัดรูปแบบ" เพื่อเน้นรายการที่ซ้ำกัน

ที่นี่ เราเลือกสีเติมสำหรับเซลล์ที่ซ้ำกันโดยคลิกที่ไอคอน 'เติมสี' และเลือกสี 'สีเหลือง'

เมื่อคุณเลือกการจัดรูปแบบแล้ว ให้คลิก 'เสร็จสิ้น' เพื่อไฮไลต์เซลล์

ฟังก์ชัน COUNTIF จะนับจำนวนครั้งที่แต่ละค่าของเซลล์ใน "คอลัมน์ A" ปรากฏใน "คอลัมน์ B" ดังนั้น ถ้ารายการปรากฏขึ้นแม้แต่ครั้งเดียวในคอลัมน์ B สูตรจะส่งกลับค่า TRUE จากนั้นรายการนั้นจะถูกเน้นใน 'คอลัมน์ A' ตามรูปแบบที่คุณเลือก

การดำเนินการนี้จะไม่เน้นรายการที่ซ้ำกัน แต่จะเน้นรายการที่ซ้ำกันในคอลัมน์ B ซึ่งหมายความว่ารายการที่ไฮไลต์สีเหลืองแต่ละรายการจะมีรายการซ้ำกันในคอลัมน์ B

ตอนนี้ เราต้องใช้การจัดรูปแบบตามเงื่อนไขกับคอลัมน์ B โดยใช้สูตรเดียวกัน ในการทำเช่นนั้น เลือกคอลัมน์ที่สอง (B2:B30) ไปที่เมนู 'รูปแบบ' และเลือก 'การจัดรูปแบบตามเงื่อนไข'

หรือคลิกปุ่ม 'เพิ่มกฎอื่น' ใต้บานหน้าต่าง 'กฎรูปแบบตามเงื่อนไข'

ถัดไป ให้ยืนยันช่วง (B2:B30) ในช่อง "นำไปใช้กับช่วง"

จากนั้นตั้งค่าตัวเลือก 'จัดรูปแบบเซลล์ถ้า .. ' เป็น 'สูตรที่กำหนดเองคือ' และป้อนสูตรด้านล่างในกล่องสูตร:

=COUNTIF($A$2:$A$30, $B2)>0

เราใช้ช่วงของคอลัมน์ A ($A$2:$A$30) ในอาร์กิวเมนต์แรกและ '$B2' ในอาร์กิวเมนต์ที่สอง สูตรนี้จะตรวจสอบค่าของเซลล์ใน 'คอลัมน์ B' กับทุกเซลล์ในคอลัมน์ A หากพบค่าที่ตรงกัน (ซ้ำกัน) การจัดรูปแบบตามเงื่อนไขจะทำให้รายการนั้นสูงใน 'คอลัมน์ B'

จากนั้นระบุการจัดรูปแบบในตัวเลือก 'รูปแบบการจัดรูปแบบ' และคลิก 'เสร็จสิ้น' ที่นี่ เรากำลังเลือกสีส้มสำหรับคอลัมน์ B

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

คุณอาจสังเกตเห็นถึงแม้ว่าจะมีการซ้ำซ้อนสำหรับ 'Arcelia' ในคอลัมน์ A แต่ก็ไม่ได้เน้น เป็นเพราะค่าที่ซ้ำซ้อนอยู่ในคอลัมน์เดียว (A) ไม่ใช่ระหว่างคอลัมน์ จึงไม่เน้น

เน้นรายการซ้ำระหว่างสองคอลัมน์ในแถวเดียวกัน

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

ขั้นแรก เลือกคอลัมน์ทั้งสองที่คุณต้องการเปรียบเทียบ จากนั้นไปที่เมนู "รูปแบบ" และเลือก "การจัดรูปแบบตามเงื่อนไข"

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

จากนั้นป้อนสูตรด้านล่างในกล่อง 'ค่าหรือสูตร':

=$A2=$B2

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

จากนั้นระบุการจัดรูปแบบจากตัวเลือก "รูปแบบการจัดรูปแบบ" แล้วคลิก "เสร็จสิ้น"

อย่างที่คุณเห็น เฉพาะแถวที่มีข้อมูลที่ตรงกัน (ซ้ำกัน) ระหว่างสองคอลัมน์เท่านั้นที่จะถูกเน้น และข้อมูลที่ซ้ำกันอื่นๆ ทั้งหมดจะถูกละเว้น

เน้นเซลล์ที่ซ้ำกันในหลายคอลัมน์

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

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

ในตัวอย่าง เรากำลังเลือก A2:C30

จากนั้นคลิกตัวเลือก "รูปแบบ" ในเมนูและเลือก "การจัดรูปแบบตามเงื่อนไข"

ในกฎรูปแบบตามเงื่อนไข ตั้งค่ากฎรูปแบบเป็น 'สูตรที่กำหนดเองคือ' จากนั้นป้อนสูตรต่อไปนี้ในกล่อง 'ค่าหรือสูตร':

=countif($A$2:$C$30,A2)>

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

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

การดำเนินการนี้จะเน้นรายการที่ซ้ำกันในคอลัมน์ทั้งหมดที่คุณเลือกดังที่แสดงด้านล่าง

หลังจากนำการจัดรูปแบบตามเงื่อนไขไปใช้แล้ว คุณสามารถแก้ไขหรือลบกฎการจัดรูปแบบตามเงื่อนไขได้ทุกเมื่อที่ต้องการ

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

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

หากคุณต้องการเพิ่มการจัดรูปแบบตามเงื่อนไขอื่นเหนือกฎปัจจุบัน ให้คลิกปุ่ม "เพิ่มกฎอื่น"

นับรายการที่ซ้ำกันระหว่างสองคอลัมน์

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

หากต้องการค้นหาจำนวนครั้งที่ค่าในคอลัมน์ A มีอยู่ในคอลัมน์ B ให้ป้อนสูตรต่อไปนี้ในเซลล์ในอีกคอลัมน์หนึ่ง

=COUNTIF($B$2:$B$30,$A2)

ป้อนสูตรนี้ในเซลล์ C2 สูตรนี้นับจำนวนครั้งที่ค่าในเซลล์ A2 อยู่ในคอลัมน์ (B2:B30) และคืนค่าการนับในเซลล์ C2

เมื่อคุณพิมพ์สูตรแล้วกด Enter คุณลักษณะป้อนอัตโนมัติจะปรากฏขึ้น คลิก 'เครื่องหมายถูก' เพื่อเติมสูตรนี้โดยอัตโนมัติไปยังส่วนที่เหลือของเซลล์ (C3:C30)

ถ้าฟีเจอร์ป้อนอัตโนมัติไม่ปรากฏขึ้น ให้คลิกสี่เหลี่ยมสีน้ำเงินที่มุมล่างขวาของเซลล์ C2 แล้วลากลงเพื่อคัดลอกสูตรในเซลล์ C2 ไปยังเซลล์ C3:C30

คอลัมน์ 'เปรียบเทียบ 1' (C) จะแสดงจำนวนครั้งที่แต่ละค่าที่สอดคล้องกันในคอลัมน์ A ปรากฏในคอลัมน์ B ตัวอย่างเช่น ค่าของ A2 หรือ "Franklyn" ไม่พบในคอลัมน์ B ดังนั้น ฟังก์ชัน COUNTIF ส่งคืน “0” และค่า "Loreta" (A5) พบสองครั้งในคอลัมน์ B ดังนั้นจึงส่งกลับ "2"

ตอนนี้ เราต้องทำซ้ำขั้นตอนเดิมเพื่อค้นหาจำนวนที่ซ้ำกันของคอลัมน์ B โดยป้อนสูตรต่อไปนี้ในเซลล์ D2 ในคอลัมน์ D (เปรียบเทียบ 2):

=COUNTIF($A$2:$A$30,$B2)

ในสูตรนี้ แทนที่ช่วงจาก '$B$2:$B$30' เป็น '$A$2:$A$30' และ '$B2' เป็น '$A2' ฟังก์ชันจะนับจำนวนครั้งที่ค่าในเซลล์ B2 อยู่ในคอลัมน์ A (A2:A30) และคืนค่าการนับในเซลล์ D2

จากนั้น ป้อนสูตรอัตโนมัติในส่วนที่เหลือของเซลล์ (D3:D30) ในคอลัมน์ D ตอนนี้ 'การเปรียบเทียบ 2' จะแสดงจำนวนครั้งที่แต่ละค่าที่สอดคล้องกันในคอลัมน์ B ปรากฏในคอลัมน์ A ตัวอย่างเช่น ค่าของ B2 หรือ "Stark" พบสองครั้งในคอลัมน์ A ดังนั้นฟังก์ชัน COUNTIF จะส่งกลับ "2"

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

แค่นั้นแหละ.