Conditional Formatting - Duplicate Values

หลายคนค้นหาเรื่อง “สูตร Excel นับจำนวนเซลล์ที่มีข้อความเหมือนกัน” เพราะอยากรู้ว่าในตารางมีข้อมูลซ้ำกี่ครั้ง หรืออยากเช็กชื่อ/รหัส/รายการที่พิมพ์ซ้ำแบบไม่ตั้งใจ เราใช้คู่กันได้เลย: ทำไฮไลต์ด้วย Conditional Formatting เพื่อ “มองเห็น” ค่าซ้ำ และใช้สูตรเพื่อ “นับจำนวน” ค่าซ้ำอย่างแม่นยำ 1) นับจำนวน “ข้อความที่ระบุ” ว่ามีซ้ำกี่เซลล์ (COUNTIF) - ถ้าต้องการนับว่าคำว่า “คะแหน” ปรากฏกี่ครั้งในช่วง A2:A100 =COUNTIF(A2:A100,"คะแหน") - หรือถ้าจะอ้างอิงจากเซลล์ เช่น คำที่ต้องการนับอยู่ใน D2 =COUNTIF(A2:A100,D2) ทริค: ถ้าข้อมูลมีเว้นวรรคเกิน (เช่น พิมพ์ชื่อแล้วมีช่องว่างท้ายคำ) จำนวนอาจเพี้ยน แนะนำทำคอลัมน์ช่วยด้วย TRIM ก่อน =TRIM(A2) แล้วค่อยนับจากคอลัมน์ที่ตัดช่องว่างแล้ว 2) นับจำนวน “ค่าที่ซ้ำ” ในทั้งช่วง (นับเฉพาะเซลล์ที่ซ้ำ ไม่รวมค่าไม่ซ้ำ) ถ้าต้องการนับว่าใน A2:A100 มีเซลล์ที่เป็นค่าซ้ำทั้งหมดกี่เซลล์ (เช่น AB ซ้ำ 3 ครั้ง ให้นับเป็น 3) =SUMPRODUCT(--(COUNTIF(A2:A100,A2:A100)>1)) สูตรนี้เหมาะมากเวลาเราทำ Conditional Formatting แบบ Duplicate Values แล้วอยากได้ตัวเลขสรุปประกอบ 3) นับจำนวน “รายการที่ซ้ำกันกี่ค่า” (นับจำนวนค่าที่ซ้ำแบบไม่ซ้ำรายการ) ถ้าต้องการรู้ว่าในช่วง A2:A100 มี “กี่ค่า” ที่ถูกซ้ำ (เช่น AB ซ้ำ, XY ซ้ำ รวมเป็น 2 ค่า) สำหรับ Excel 365/2021 ทำได้ง่ายด้วย =COUNTA(UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))) 4) ใช้ร่วมกับ Conditional Formatting (Duplicate Values) ให้ทำงานเป็นระบบ - ขั้นแรก: เลือกช่วงข้อมูล > Conditional Formatting > Highlight Cells Rules > Duplicate Values > เลือก Duplicate และเลือก Format/สีที่ชอบ - ขั้นสอง: วางสูตรนับไว้ด้านบน/ด้านข้างตาราง เพื่อสรุปจำนวนค่าซ้ำแบบอัตโนมัติ ส่วนตัวเราชอบวิธีนี้มาก เพราะ “เห็นด้วยตา” จากไฮไลต์ และ “มั่นใจด้วยตัวเลข” จากสูตรนับจำนวน ทำให้ตรวจข้อมูลได้เร็วขึ้น โดยเฉพาะตอนเช็กชื่อคน, รหัสสินค้า, หรือรายการคะแนนในตารางใหญ่ๆ