บทความวิกิฮาวนี้จะแนะนำวิธีการใช้การจัดรูปแบบตามเงื่อนไขเพื่อเปรียบเทียบเซลล์ทั้งหมดในรายการสองรายการที่แยกจากกันในสเปรดชีต Excel และทำเครื่องหมายเซลล์ที่ปรากฏในทั้งสองรายการ ฟีเจอร์นี้มีให้ใช้งานในเวอร์ชันเดสก์ท็อปของ Excel เท่านั้น และแอปสำหรับอุปกรณ์เคลื่อนที่ไม่รองรับ
ขั้นตอน
วิธีที่ 1 จาก 2: การใช้สูตร CountIf
ขั้นตอนที่ 1 เปิดสเปรดชีต Excel ที่คุณต้องการแก้ไข
ค้นหาไฟล์สเปรดชีตที่มีรายการที่คุณต้องการเปรียบเทียบ แล้วดับเบิลคลิกที่ไฟล์เพื่อเปิดไฟล์ใน Microsoft Excel
ขั้นตอนที่ 2 เลือกรายการแรกของคุณ
คลิกเซลล์แรกในรายการแรกของคุณ แล้วลากเมาส์ลงไปที่เซลล์สุดท้ายของรายการเพื่อเลือกช่วงข้อมูลทั้งหมด
ขั้นตอนที่ 3 คลิกแท็บสูตรบนแถบเครื่องมือริบบิ้น
คุณจะพบแท็บนี้เหนือแถบเครื่องมือที่ด้านบนของสเปรดชีต จะเปิดเครื่องมือสูตรของคุณบน Ribbon ของแถบเครื่องมือ
ขั้นตอนที่ 4 คลิก กำหนดชื่อ บนแถบเครื่องมือ
ปกติตัวเลือกนี้จะอยู่ตรงกลางริบบิ้น "สูตร" มันจะเปิดหน้าต่างป๊อปอัปใหม่ขึ้นมา และให้คุณตั้งชื่อรายการของคุณ
ขั้นตอนที่ 5. พิมพ์ List1 ลงในช่อง Name
คลิกช่องข้อความที่ด้านบนของหน้าต่างป๊อปอัป แล้วป้อนชื่อรายการที่นี่
- คุณสามารถใช้ชื่อนี้เพื่อแทรกรายการของคุณลงในสูตรเปรียบเทียบได้ในภายหลัง
- หรือคุณสามารถตั้งชื่ออื่นให้กับรายชื่อของคุณได้ที่นี่ ตัวอย่างเช่น หากนี่คือรายการสถานที่ คุณสามารถตั้งชื่อว่า "locations1" หรือ "locationList"
ขั้นตอนที่ 6 คลิก ตกลง ในหน้าต่างป๊อปอัป
การดำเนินการนี้จะยืนยันการกระทำของคุณและตั้งชื่อรายการของคุณ
ขั้นตอนที่ 7 ตั้งชื่อรายการที่สองของคุณเป็น List2
ทำตามขั้นตอนเดียวกับรายการแรก และตั้งชื่อรายการที่สองของคุณ วิธีนี้จะช่วยให้คุณใช้รายการที่สองนี้ในสูตรเปรียบเทียบได้อย่างรวดเร็วในภายหลัง
คุณสามารถให้รายชื่อใดก็ได้ที่คุณต้องการ อย่าลืมจำหรือจดชื่อที่คุณตั้งให้กับแต่ละรายการของคุณที่นี่
ขั้นตอนที่ 8 เลือกรายการแรกของคุณ
คลิกเซลล์แรกในรายการแรก แล้วลากลงเพื่อเลือกช่วงข้อมูลทั้งหมด
ตรวจสอบให้แน่ใจว่าได้เลือกรายการแรกของคุณก่อนที่คุณจะเริ่มตั้งค่าการจัดรูปแบบตามเงื่อนไขของคุณ
ขั้นตอนที่ 9 คลิกแท็บหน้าแรกบนแถบเครื่องมือ
นี่คือแท็บแรกที่มุมบนซ้ายของ ribbon ของแถบเครื่องมือ มันจะเปิดเครื่องมือสเปรดชีตพื้นฐานของคุณบนแถบเครื่องมือ
ขั้นตอนที่ 10. คลิก Conditional Formatting บนแถบเครื่องมือ
ตัวเลือกนี้ดูเหมือนไอคอนสเปรดชีตเล็กๆ ที่มีบางเซลล์ไฮไลต์เป็นสีแดงและสีน้ำเงิน จะเปิดเมนูแบบเลื่อนลงของตัวเลือกการจัดรูปแบบทั้งหมดของคุณ
ขั้นตอนที่ 11 คลิก New Rule บนเมนูแบบเลื่อนลง
จะเปิดหน้าต่างป๊อปอัปใหม่ขึ้นมา และให้คุณตั้งค่ากฎการจัดรูปแบบใหม่สำหรับช่วงที่เลือกได้ด้วยตนเอง
ขั้นตอนที่ 12 เลือก "ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ" ตัวเลือก
ตัวเลือกนี้จะช่วยให้คุณพิมพ์สูตรการจัดรูปแบบด้วยตนเองเพื่อเปรียบเทียบสองรายการของคุณ
- บน Windows ที่ด้านล่างของรายการกฎในกล่อง "เลือกประเภทกฎ"
- บน Mac, เลือก คลาสสิค ในเมนูแบบเลื่อนลง "สไตล์" ที่ด้านบนของป๊อปอัป จากนั้น ให้ค้นหาตัวเลือกนี้ในเมนูแบบเลื่อนลงอันที่สองด้านล่างเมนูสไตล์
ขั้นตอนที่ 13 คลิกฟิลด์สูตรในหน้าต่างป๊อปอัป
คุณสามารถป้อนสูตร Excel ที่ถูกต้องได้ที่นี่เพื่อตั้งค่ากฎการจัดรูปแบบตามเงื่อนไข
ขั้นตอนที่ 14. พิมพ์ =countif(List2, A1)=1 ลงในแถบสูตร
สูตรนี้จะสแกนสองรายการของคุณและทำเครื่องหมายเซลล์ทั้งหมดในรายการแรกของคุณที่ปรากฏในรายการที่สองด้วย
- แทนที่ A1 ในสูตรด้วยหมายเลขของเซลล์แรกของรายการแรกของคุณ
- ตัวอย่างเช่น ถ้าเซลล์แรกของรายการแรกของคุณคือเซลล์ D5 สูตรของคุณจะมีลักษณะดังนี้ =countif(List2, D5)=1
- หากคุณตั้งชื่ออื่นให้กับรายการที่สอง อย่าลืมแทนที่ List2 ในสูตรด้วยชื่อจริงของรายการของคุณเอง
- หรือเปลี่ยนสูตรเป็น =countif(List2, A1)=0 ถ้าคุณต้องการทำเครื่องหมายเซลล์ที่ อย่า ปรากฏในรายการที่สอง
ขั้นตอนที่ 15. พิมพ์ =countif(List1, B1)=1 ในแถบสูตร (ตัวเลือก)
หากคุณต้องการค้นหาและทำเครื่องหมายเซลล์บนของคุณ ที่สอง รายการที่ปรากฏในรายการแรกด้วย ให้ใช้สูตรนี้แทนสูตรแรก
แทนที่ List1 ด้วยชื่อรายการแรกของคุณ และ B1 ด้วยเซลล์แรกของรายการที่สองของคุณ
ขั้นตอนที่ 16 เลือกรูปแบบที่กำหนดเองเพื่อทำเครื่องหมายเซลล์ (ไม่บังคับ)
คุณสามารถเลือกสีเติมพื้นหลังแบบกำหนดเองและรูปแบบฟอนต์ต่างๆ เพื่อทำเครื่องหมายเซลล์ที่สูตรของคุณพบได้
- บน Windows, คลิก รูปแบบ ปุ่มที่ด้านล่างขวาของหน้าต่างป๊อปอัป คุณสามารถเลือกสีพื้นหลังในแท็บ "เติม" และรูปแบบแบบอักษรในแท็บ "แบบอักษร"
- บน Mac เลือกรูปแบบที่กำหนดไว้ล่วงหน้าในเมนูแบบเลื่อนลง "รูปแบบที่มี" ที่ด้านล่าง คุณยังสามารถเลือก รูปแบบที่กำหนดเอง ที่นี่เพื่อเลือกการเติมพื้นหลังและรูปแบบตัวอักษรด้วยตนเอง
ขั้นตอนที่ 17 คลิก ตกลง ในหน้าต่างป๊อปอัป
การดำเนินการนี้จะยืนยันและใช้สูตรเปรียบเทียบของคุณ เซลล์ทั้งหมดในรายการแรกของคุณที่ปรากฏในรายการที่สองจะถูกทำเครื่องหมายด้วยสีและแบบอักษรที่คุณเลือก
- ตัวอย่างเช่น หากคุณเลือกเติมสีแดงอ่อนที่มีข้อความสีแดงเข้ม เซลล์ที่เกิดซ้ำทั้งหมดจะเปลี่ยนเป็นสีนี้ในรายการแรกของคุณ
- ถ้าคุณใช้สูตรที่สองข้างต้น การจัดรูปแบบตามเงื่อนไขจะทำเครื่องหมายเซลล์ที่เกิดซ้ำในรายการที่สองของคุณแทนที่จะเป็นเซลล์แรก
วิธีที่ 2 จาก 2: การใช้สูตร VLookup
ขั้นตอนที่ 1 เปิดสเปรดชีต Excel ของคุณ
ค้นหาไฟล์ Excel ที่มีรายการที่คุณต้องการเปรียบเทียบ แล้วดับเบิลคลิกที่ชื่อไฟล์หรือไอคอนเพื่อเปิดสเปรดชีตใน Microsoft Excel
ขั้นตอนที่ 2 คลิกเซลล์ว่างถัดจากรายการแรกในรายการที่สองของคุณ
ค้นหารายการที่สองของคุณในสเปรดชีต แล้วคลิกเซลล์ว่างถัดจากรายการแรกที่ด้านบน
- คุณสามารถแทรกสูตร VLookup ของคุณได้ที่นี่
- หรือจะเลือกเซลล์ว่างในสเปรดชีตก็ได้ เซลล์นี้จะช่วยให้คุณเห็นการเปรียบเทียบถัดจากรายการที่สองได้สะดวกยิ่งขึ้นเท่านั้น
ขั้นตอนที่ 3 พิมพ์ =vlookup(ลงในเซลล์ว่าง
สูตร VLookup จะช่วยให้คุณสามารถเปรียบเทียบรายการทั้งหมดในรายการสองรายการที่แยกจากกัน และดูว่าค่าเป็นค่าซ้ำหรือค่าใหม่
อย่าปิดวงเล็บสูตรจนกว่าสูตรของคุณจะสมบูรณ์
ขั้นตอนที่ 4 เลือกรายการแรกในรายการที่สองของคุณ
คลิกรายการแรกในรายการที่สองโดยไม่ต้องปิดวงเล็บสูตร เพื่อแทรกเซลล์แรกของรายการที่สองลงในสูตร
ขั้นตอนที่ 5. พิมพ์ a, เครื่องหมายจุลภาคในสูตร
หลังจากเลือกเซลล์แรกของรายการที่สองแล้ว ให้พิมพ์เครื่องหมายจุลภาคในสูตร คุณจะสามารถเลือกช่วงการเปรียบเทียบของคุณต่อไปได้
ขั้นตอนที่ 6 กดค้างไว้และเลือกรายการแรกทั้งหมดของคุณ
ซึ่งจะแทรกช่วงเซลล์ของรายการแรกของคุณในส่วนที่สองของสูตร VLookup
ซึ่งจะทำให้คุณสามารถค้นหารายการแรกสำหรับรายการที่เลือกจากรายการที่สองของคุณ (รายการแรกที่ด้านบนของรายการที่สอง) และส่งคืนหากเป็นค่าซ้ำหรือค่าใหม่
ขั้นตอนที่ 7 พิมพ์ a, เครื่องหมายจุลภาคในสูตร
การดำเนินการนี้จะล็อกช่วงการเปรียบเทียบในสูตรของคุณ
ขั้นตอนที่ 8 พิมพ์ 1 ในสูตรหลังเครื่องหมายจุลภาค
ตัวเลขนี้แสดงถึงหมายเลขดัชนีคอลัมน์ของคุณ จะแจ้งสูตร VLookup เพื่อค้นหาคอลัมน์รายการจริงแทนที่จะเป็นคอลัมน์อื่นที่อยู่ติดกัน
ถ้าคุณต้องการให้สูตรของคุณคืนค่าจากคอลัมน์ที่อยู่ถัดจากรายการแรกของคุณ ให้พิมพ์ 2 ที่นี่
ขั้นตอนที่ 9 พิมพ์ a, เครื่องหมายจุลภาคในสูตร
การดำเนินการนี้จะล็อกหมายเลขดัชนีคอลัมน์ (1) ในสูตร VLookup
ขั้นตอนที่ 10. พิมพ์ FALSE ในสูตร
การดำเนินการนี้จะค้นหารายการที่ตรงกันของรายการค้นหาที่เลือก (รายการแรกที่ด้านบนสุดของรายการที่สอง) แทนการจับคู่โดยประมาณ
- แทนที่จะเป็น FALSE คุณสามารถใช้ 0 ได้ มันเหมือนกันทุกประการ
- อีกวิธีหนึ่ง คุณสามารถพิมพ์ TRUE หรือ 1 หากคุณต้องการค้นหาค่าที่ตรงกันโดยประมาณ
ขั้นตอนที่ 11 พิมพ์) ท้ายสูตรให้ปิด
ตอนนี้คุณสามารถเรียกใช้สูตรของคุณ และดูว่ารายการค้นหาที่เลือกในรายการที่สองของคุณเป็นค่าซ้ำหรือค่าใหม่
ตัวอย่างเช่น ถ้ารายการที่สองของคุณเริ่มต้นที่ B1 และรายการแรกของคุณเริ่มจากเซลล์ A1 ถึง A5 สูตรของคุณจะมีลักษณะดังนี้ =vlookup(B1, $A$1:$A$5, 1, เท็จ).
ขั้นตอนที่ 12. กด ↵ Enter หรือ ⏎ กลับไปที่แป้นพิมพ์ของคุณ
การดำเนินการนี้จะเรียกใช้สูตรและค้นหารายการแรกของคุณสำหรับรายการแรกจากรายการที่สองของคุณ
- หากเป็นค่าที่เกิดซ้ำ คุณจะเห็นค่าเดิมพิมพ์อีกครั้งในเซลล์สูตร
- หากเป็นค่าใหม่ คุณจะเห็น " #N/A" พิมพ์ที่นี่.
- ตัวอย่างเช่น หากคุณกำลังค้นหารายการแรกสำหรับ "John" และตอนนี้เห็น "John" ในเซลล์สูตร จะเป็นค่าที่เกิดซ้ำในทั้งสองรายการ หากคุณเห็น "#N/A" แสดงว่าเป็นค่าใหม่ในรายการที่สอง
ขั้นตอนที่ 13 เลือกเซลล์สูตรของคุณ
หลังจากเรียกใช้สูตรของคุณและเห็นผลลัพธ์ของคุณสำหรับรายการแรก ให้คลิกที่เซลล์สูตรเพื่อเลือก
ขั้นตอนที่ 14. คลิกแล้วลากจุดสีเขียวที่ด้านล่างขวาของเซลล์ลงมา
วิธีนี้จะขยายเซลล์สูตรของคุณไปตามรายการ และใช้สูตรกับทุกรายการในรายการที่สองของคุณ
- วิธีนี้ทำให้คุณสามารถเปรียบเทียบทุกรายการในรายการที่สองกับรายการแรกทั้งหมดได้
- การดำเนินการนี้จะค้นหารายการแรกของคุณสำหรับทุกๆ รายการในรายการที่สองของคุณทีละรายการ และแสดงผลลัพธ์ถัดจากแต่ละเซลล์แยกกัน
- หากคุณต้องการเห็นเครื่องหมายอื่นสำหรับค่าใหม่แทนที่จะเป็น "#N/A" ให้ใช้สูตรนี้: =iferror(vlookup(B1, $A$1:$A$5, 1, false), "New Value") การดำเนินการนี้จะพิมพ์ "ค่าใหม่" สำหรับค่าใหม่แทน "#N/A"
