วันจันทร์ที่ 12 พฤศจิกายน พ.ศ. 2555

หาค่าที่ไม่ซ้ำใน 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 เหมือนกัน ตามรูป

Choose the highlighted category from validation list
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 ที่กรองแล้วจะดึงเฉพาะค่าที่ไม่ซ้ำเท่านั้นUsing Advanced Filter copy unique category list to cell I4
  6. จากนั้นทำ Data validation drop down list ที่เซลล์ A2 โดยที่ Source: ใส่ช่วงเซลล์ unique category list (I5:I15) หรือคอลัมท์ที่ทำการกรองแล้ว

  7. create category data validation list

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

    Set format to fill pink background if category in B-column are equal to A2
ข้อสังเกตุ
  • ถ้าก่อนที่จะกำหนด Rule Type ในข้อ 7 และ 8 คุณเลือกคลุมพื้นที่เซลล์ทั้งหมดตามรูป ไฮไลท์จะแสดงทุกคอลัมท์ในแถว (ตามรูป Figure 1)
  • แต่ถ้าเลือกคลุมเพียงบางคอลัมท์ สีไฮไลท์จะแสดงเฉพาะคอลัมท์ที่เลือกเท่านั้น

Reactions:

1 ความคิดเห็น: