หาค่าที่ไม่ซ้ำใน 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)
• แต่ถ้าเลือกคลุมเพียงบางคอลัมท์ สีไฮไลท์จะแสดงเฉพาะคอลัมท์ที่เลือกเท่านั้น

การใช้ Excel's Advanced Filter ตัวกรองขั้นสูง

Advanced Filter

เปรียบได้กับเครื่องกรองสารพัดประโยชน์,ที่ทำงานตามโปรแกรมที่เราได้ตั้งไว้

ก่อนอื่น มารู้จักหน้าตาของเจ้ากรองขั้นสูง หรือ
Advanced Filter ตัวนี้

เรียกใช้งานด้วยการเข้าเมนู Data > Sort & Filter แล้วคลิกที่ Advanced ซึ่ง Excel จะแสดงกล่องโต้ตอบ Advanced Filter ดูใต้ตัวเลือก Action จะเห็นหัวข้อต่างๆที่มีคุณสมบัติการใช้งานต่างๆ ดังนี้

1. Filter the list, in-place : เราใช้ตัวเลือกนี้ให้แสดงข้อมูลที่กรองแล้ว บนช่วงพื้นที่เซลล์เดิม
2. Copy to another location : ตัวเลือกนี้กรองข้อมูลแล้ว copy ไปวางไว้ที่ ช่วงเซลส์อื่น หรือ ชีทอื่น
3. List range : เซลล์เรนจ์ที่เราต้องการฟิลเตอร์ข้อมูล
4. Criteria range : ไคทีเรียเรนจ์? หรือ เรนจ์ที่เราวางข้อกำหนด
5. Copy to : ใช้เมื่อเราเลือกบันทึกไปพื้นที่อื่น
6. Unique records only : กรองเฉพาะรายการที่เป็น Unique คือไม่ซ้ำ ไม่ duplicate 

---

ตัวอย่างวิธีใช้ Advanced Filter ตัวกรองขั้นสูง 2 แบบ

ค้นหา Unique rows (ค่าที่ไม่ซ้ำ) ซึ่งในที่นี้แถว3 กับ แถว7 มีข้อมูลที่ซ้ำกันอยู่ คือ AP0001

1. เปิดไฟล์งาน วางเม้าส์ไว้ที่ไหนก็ได้ใน worksheet
2. เลือก Data > Advanced Filter
3. Excel จะแสดงกล่องโต้ตอบ Advanced Filter
4. ใต้ Action เลือก Filter the list, in-place 
5. ที่ List range: ใส่ ($A$2:$A$19)
6. ติ๊กเครื่องหมายถูกที่ Unique records only
7. จากนั้นคลิก ok จากภาพด้านล่างจะเห็นว่าแถว 7 หายไป
8. สมมุติว่า ถ้าคอลัมท์ C ของ Row7 เป็นค่า 10 gram ตัวกรองจะไม่ซ่อน Row7 เพราะตัวกรองจะไม่เห็นว่ารายการนี้เป็นรายการที่ซ้ำ
9. แล้วถ้าเราต้องการหาค่าไม่ซ้ำ Unique record เฉพาะบางคอลัมท์ล่ะจะทำยังไง ? จากตัวอย่างนี้ สมมุติว่าเราจะกรองรายการที่ไม่ซ้ำของคอลัมท์ Product ก็ให้เลือกเฉพาะพื้นที่คอลัมท์ A  คือให้ใส่ $A2:$A19 ที่ List range เท่านั้น

---

Filter Criteria range & copy to ..กรองข้อมูลจากเงื่อนไขและวางผลลัพท์ไว้ที่เซลล์เรนจ์อื่น

1. เปิดไฟล์งาน วางเม้าส์ไว้ที่ไหนก็ได้ใน worksheet
2. เลือก Data > Advanced Filter
3. Excel จะแสดงกล่องโต้ตอบ Advanced Filter
4. ใต้ Action เลือก Copy to another location คือจากเลือกไปวางไว้ที่อื่นที่ไม่ใช่พืนที่ข้อมูลนี้
5. ที่ List range: ใส่ ($A$2:$A$19)
6. ที่ Criteria range:  ใส่เซลล์เรนจ์ ($H$2:$I$3) พื้นที่กำหนดเงื่อนไข ซึ่งเราได้ใส่ < 100 คือค้นหารายการสินค้าที่ Stock Level น้อยกว่า 100 และ Stock Value มากกว่า 2,000
7. ช่อง Copy to: ใส่เซลล์เรนจ์ที่ต้องการวางข้อมูลหลังจากกรองข้อมูล์แล้ว ($H$6:$I$13)
8. จากนั้นคลิก ok

ผลลัพท์ของการกรองขั้นสูง แบบที่ 2

ข้อสังเกตุการใช้ตัวกรองขั้นสูง (Advanced Filter) แบบที่สอง

1. ป้ายชื่อ Heading Label ทั้ง Criteria range (H2:I2) และ Copy to (H6:I6) ต้องเป็นชื่อเดียวกันกับตารางข้อมูลหลัก List range ไม่เช่นนั้น ตัวกรองจะไม่ทำการค้นหาให้
2. ข้อมูลที่ทำใช้การกรองขั้นสูง์ ไม่จำเป็นต้องแสดงที่เซลล์เรนจ์เดิม ซึ่งยืดหยุ่นกว่าการใช้ตัวกรองอัตโนมัติ (Autofilter)
3. เราสามารถปรับเปลี่ยน Criteria และ Copy to ได้ตลอดเวลาด้วยการเปลี่ยนป้ายชื่อ เช่นถ้าเปบี่ยน H2 เป็น ราคา ก็สามารถทำได้ด้วยการเปลี่ยนป้ายชื่อ(heading label) แล้วเรียกเมนูตัวกรองซ้ำอีกครั้ง ซึ่งทำให้ไม่ต้องไปเกี่ยวข้องกับตารางข้อมูลหลัก ซึ่งดีกว่าการใช้ Filter