หาค่าที่ไม่ซ้ำใน Excel ด้วย Advanced Filter,Data Validation และ Conditional Formatting

Filter เฉพาะแถวที่ทำสีด้วย Advanced Filter
กับคุณสมบัติของ Conditional Formatting กับ Data Validation

มาทำความรู้จักศักยภาพของ conditional formatting และ Data validation-drop down list ด้วยความช่วยเหลือเล็กน้อยจาก Advanced Filter ที่ทำให้เราสามารถสร้างตารางข้อมูลแบบไดนามิกไฮไลท์

ตารางที่ใช้เป็นตัวอย่างเก็บรายการสินค้าในสต็อค ซึ่งแต่ละหมวด (category) มีสินค้าหลายอย่าง และต้องการค้นหาว่าแต่ละหมวดมีสินค้ากี่รายการ โดยใช้การไฮไลท์แถวที่เลือกจาก drop down list

เช่น เมื่อเลือก "Fruit" จาก category list (A2)  จะเกิดไฮไลท์แถวสีชมพูในตารางข้อมูล Product (A4:F22) ที่มีค่าเป็น Fruit category เหมือนกัน ตามรูป

Figure 1

วิธีสร้าง Dynamic Table ที่จะไฮไลท์ข้อมูลที่ถูกเลือกจาก Drop down list

1. เปิดไฟล์งานวางเคอร์เซอร์ไว้ที่ไหนก็ได้แล้วเลือกคำสั่ง Data >Advanced Filter ก่อนอื่นเลยคือต้องกรอง category ให้เป็น unique list (ข้อมูลที่ไม่ซ้ำ) ซะก่อน 
2. ใต้ Action เลือกหัวข้อ Copy to another location 
3. ที่ List range : ใส่ $B$4:$B$22 (คอลัมท์ category)
4. ที่ Copy to: ใส่ $I$4 (ที่จริงจะแปะไว้ที่ไหนก็ได้บน worksheet แต่ที่เลือก I4 เพื่อให้เห็นว่าเราไม่จำเป็นต้องกำหนดพื้นที่เซลล์ที่จะวางเลย ข้อมูลที่ Filter แล้วจะ paste ลงมาเท่าที่มีโดยเริ่ม paste ที่เซลล์ I4)
5. แล้วติ๊กเลือกฟีเจอร์ Unique records only ซึ่ง category ที่กรองแล้วจะดึงเฉพาะค่าที่ไม่ซ้ำเท่านั้น
6. จากนั้นทำ Data validation drop down list ที่เซลล์ A2 โดยที่ Source: ใส่ช่วงเซลล์ unique category list (I5:I15) หรือคอลัมท์ที่ทำการกรองแล้ว





7. กำหนดรูปแบบด้วย Conditional Formatting โดยเลือก Rule Type เป็น Use a Formula to determine which cells to format ตามรูป
8. ที่ Format values where this formula is true: ใส่ =$B5=$A$2 (หมายถึงถ้าค่าในคอลัมท์ B เท่ากับ Category (A2) ให้เปลี่ยนเป็น Format ที่เลือก ซึ่งในตัวอย่างนี้ใช้สีชมพู
   
   
   

ข้อสังเกตุ

• ถ้าก่อนที่จะกำหนด Rule Type ในข้อ 7 และ 8 คุณเลือกคลุมพื้นที่เซลล์ทั้งหมดตามรูป ไฮไลท์จะแสดงทุกคอลัมท์ในแถว (ตามรูป Figure 1)
• แต่ถ้าเลือกคลุมเพียงบางคอลัมท์ สีไฮไลท์จะแสดงเฉพาะคอลัมท์ที่เลือกเท่านั้น