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

การใช้ Data Validation ทำ 2 Drop down lists ด้วย INDIRECT

ลักษณะการทำงานของ INDIRECT มันคล้ายกับการแข่งแรลลี่ หาอาร์ซีที่ซ่อนอยู่ตามจุด เพื่อแกะรอยเดินทางต่อ มันคล้าย INDIRECT ซ้อน INDIRECT ซ้อนกันจนถึงจุดฟินิช

ฟังก์ชัน INDIRECT ทำหน้าที่ส่งกลับค่าอ้างอิงที่อยู่ในเซลล์ ด้วยการระบุตำแหน่งของเซลล์ (Column Letter,Row Number)

และยังสามารถอ้างอิงไปยังภายนอก หรือสมุดงานอื่นได้ ทำให้ปรับใช้ได้กับฟังก์ชันอื่นๆ ได้อีกหลายฟังก์ชัน

เช่น ใช้ร่วมกับ Data Validation ในการสร้าง Drop down lists 2 คอลัมน์

---

ไวยากรณ์และความหมาย

=INDIRECT(ref_text, a1)

• ref_text คือเซลล์อ้างอิง ซึ่งถ้าการอ้างอิงไม่ใช่เซลล์ที่ถูกต้อง ฟังก์ชัน INDIRECT จะส่งกลับค่าความผิดพลาด #REF! และ หากมีการอ้างอิงไปยังสมุดงานอื่น(การอ้างอิงภายนอก) สมุดงานที่เชื่อมโยงกันต้องเปิดอยู่ ไม่เช่นนั้นฟังก์ชันก็จะส่งค่าความผิดพลาดเช่นกัน
  
  
  1. ถ้า a1 เป็น TRUE หรือถูกละเว้น ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ A1
  2. ถ้า a1 เป็น FALSE แล้ว ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ R1C1


• ข้อจำกัดของ INDIRECT
  1. เซลล์ที่เป็น ref_text สูตรจะไม่อ้างอิงที่เซลล์เดิมถ้ามีการ ตัด(cut), ลบ(Delete) หรือแทรกแถว หรือคอลัมน์ (Insert) ซึ่งทำให้เซลล์เปลี่ยนตำแหน่ง
  2. ถ้าต้องการอ้างอิงที่เซลล์เดิม ตลอดไปไม่ว่าแถวที่อยู่บนเซลล์จะถูกลบหรือเซลล์นั้นจะถูกย้ายไป ให้ใช้ " " เพิ่มในตัวสูตร (=INDIRECT("ref_text"))
  3. สมมุติในเซลล์ A9 เป็นค่าว่าง และเมื่อแทนค่า =INDIRECT("A9") สูตรจะคืนค่า 0 แทน #REF!

---

ตาราง1 คอลัมท์ A2-C6 เป็นโจทย์ และ D1-F7 จะแสดงฟังก์ชันเพื่อแสดงคุณสมบัติพื้นฐานของ INDIRECT

Row	Column
	A	B	C	x	D	E	F
1	DataA	DataB	DataC		ข้อ	แสดงการใช้ Function	ผลลัพท์
2	B4	200	B3		1	=INDIRECT(A2)	A2
3	C4	1.82	99		2	=INDIRECT(B2)	#REF!
4	C3	A2	700		3	=INDIRECT(A3)	700
5	2	3000	A4		4	=INDIRECT(C5)	C3
6					5	=INDIRECT(INDIRECT(C5))	99
7					6	=INDIRECT("B"&ROWS(A2:C5)+1)	3000

อธิบายการใช้ฟังก์ชัน INDIRECT ของตางรางข้างบน( Column F2-F7)

Column
ข้อ	ผลลัพท์	คำอธิบายผลลัพท์การใช้ฟังก์ชัน INDIRECT
1	A2	ในเซลล์ A2 มีตัวอักษร B4 อยู่, สูตรจะไปค้นหาค่าในเซลล์ B4 จึงได้คำตอบเท่ากับ A2
2	#REF	B2 มีตัวเลข 200, สูตรไม่สามารถระบุตำแหน่งเซลล์อ้างอิงได้ ผลลัพท์เป็นค่าความผิดพลาด
3	700	A3 มีตัวอักษร C4, สูตรจะค้นหาค่าในเซลล์ C4 คำตอบคือ 700
4	C3	ที่เซลล์ C5 มีตัวอักษร A4 อยู่ สูตรจะคืนค่าในเซลล์ A4 ซึ่งคำตอบคือ C3
5	99	ซ้อน INDIRECT อีกชั้นจากข้อ 4, เท่ากับ INDIRECT(A4) สูตรคืนค่าในเซลล์ C3 คำตอบที่ได้คือ 99
6	3000	ใช้ "&" เพื่อล็อคตัว B เชื่อมกับฟังก์ชัน Rows(A2:C5) เท่ากับ 4 คำตอบที่ได้เท่ากับ "B"& 4+1 หรือ B5 นั่นเอง

ภาพด้านข้างแสดงการค้นหา
ค่าในเซลล์สไตล์ INDIRECT

---

ต่อไปจะเป็นการปรับใช้ INDIRECT ใน Drop down list ที่สร้างจาก Data Validation

ข้อมูลที่จะใช้ในตัวอย่างถัดไปเป็น บางส่วนของชื่อบริษัทหลักทรัพย์ในกลุ่มอุตสาหกรรม โดยแยกประเภทธุรกิจเป็นกลุ่มหลัก 6 กลุ่ม และในแต่ละกลุ่มจะมีบริษัทในธุรกิจไม่เท่ากัน

เราจะสร้าง 2 Drop down lists ร่วมกับฟังก์ชัน INDIRECT  กำหนดเงื่อนไขการบันทึก คือ ก่อนการเลือกชื่อของกลุ่มย่อย (บริษัท) ต้องเลือกชื่อกลุ่มหลัก (ประเภทธุรกิจ) ก่อน  มาเริ่มกันเลยดีกว่า

การใช้ Indirect ทำ Drop down lists 2 คอลัมน์

1. เปิดไฟล์งานไปที่ชีท indirect ซึ่งเป็นที่เก็บข้อมูลรายชื่อบริษัทในธุรกิจอุตสาหกรรม
2. ไปที่ Formula > Name Manger คลิก New เพื่อกำหนดชื่อให้กับพื้นที่เซลล์
3. ที่ Name: พิมพ์ว่า INDUSTRY และส่วนของ Refer to : ใช้เม้าส์คลุมพื้นที่ช่วงเซลล์ B2:G2 คลิก ok (ชุดนี้คือประเภทธุรกิจซึ่งจะเป็นข้อมูลหลัก)





4. คลิก New เพื่อสร้างชื่อกลุ่มใหม่ (ชุดนี้จะเป็นรายชื่อบริษัทในแต่ละประเภทธุรกิจนั่นคือเป็นกลุ่มย่อย)
5. ที่ Name: พิมพ์ว่า ยานยนต์ และส่วนของ Refer to : ใช้เม้าส์คลุมช่วงเซลล์ B4:B13 คลิก ok
6. คลิก New เพื่อเพิ่มชื่อกลุ่มใหม่
7. ที่ Name: พิมพ์ว่า วัสดุอุตสาหกรรมและเครื่องจักร และส่วนของ Refer to :ใช้เม้าส์คลุมช่วงเซลล์ C4:C10 คลิก ok
8. ทำแบบเดียวกันกับ ช่วงเซลล์ D4:D5 (กระดาษและวัสดุการพิมพ์), ช่วงเซลล์ E4:E13 (ปิโตรเคมีและเคมีภัณฑ์), ช่วงเซลล์ F4:F13 (บรรจุภัณฑ์), ช่วงเซลล์ G4:G13 (เหล็ก)



9. เมื่อเสร็จจากชีท Indirect ให้คลิกแทปชีท Indirect1 ซึ่งเป็นชีทที่เราใช้บันทึกข้อโดยดึงข้อมูลหลักจาก Indirect


10. สร้าง Drop down list ที่เซลล์ B3 (ใต้ประเภทธุรกิจ) แล้วคลิกที่ Data > Validation
    • หัวข้อ Allow:   เลือกเป็น List
    • หัวข้อ Source:  พิมพ์ =Industry
    • แล้ว copy สูตรลงเซลล์ที่จะใช้งาน
    
    
    
11. สร้าง drop down list ที่เซลล์ C3 (ใต้บริษัท)แล้วคลิกที่ Data > Validation
    • หัวข้อ Allow:  เลือกเป็น List
    • หัวข้อ Source: พิมพ์ =INDIRECT(B3)
    • แล้ว copy สูตรลงเซลล์ที่จะใช้งาน


12. ตอนที่สร้าง Drop down list ในข้อ 11.  Excel  เมื่อคลิก ok จะแสดงกล่องเตือน error ขึ้้นมา สาเหตุเพราะว่า ข้อมูลหลักยังเป็นค่าว่าง ให้คลิก Yes
    
    

สร้าง Drop Down List ด้วย Data Validation ใน Excel

การใช้ Data Validation สร้าง Drop Down Control List ใน MS Excel เป็นการสร้างลิสต์บ๊อกที่ง่าย และโดยทั่วไปมักใช้กับข้อมูลที่ไม่มีการเพิ่มเติมหรือเปลี่ยนแปลงมากนัก แต่อันที่จริงก็มีวิธีที่ทำให้เราสามารถเพิ่มข้อมูลในลิสต์ได้สะดวกด้วยเช่นกัน

ข้อดีของการใช้ drop down list ที่สร้างจาก data validation คือ

• เป็นรูปแบบที่สร้างได้อย่างง่าย โดยกำหนดให้ user เพียงใช้เม้าส์คลิกเลือกข้อมูลในลิสต์


• ลดความผิดพลาดจากการคีย์อินข้อมูล


• สามารถใช้ควบคุมขอบเขตของข้อมูลที่เราต้องการใช้บันทึกจริง


• ไม่จำเป็นที่จะต้องสร้างลิสต์จากข้อมูลที่อยู่บน Work sheet เดี่ยวกัน


• สามารถเพิ่มลิสต์ใน Drop down ได้

จะขอแบ่งวิธีเพิ่ม List Control Box ออกเป็น 3 ส่วน

---

Part 1 สร้าง Drop Down List บน Worksheet เดียวกัน

1. เลือก เซลส์ที่ต้องการวางลิสต์บ็อก แล้วไปที่ Menu เลือก Data Validation





2. Excel จะแสดงกล่องโต้ตอบ ที่แทป Setting เป็นตัวกำหนดการใช้ Validation criteria ให้กำหนดตามนี้
   1. หัวข้อ Allow:- เลือกเป็น List
   2. หัวข้อ Source:- ใส่ cell area ที่ต้องการให้อยู่ใน List ในรูป คือ =$D$5:$D$13 (หมายถึง เขตพื้นที่ตั้งแต่ D5 ถึง D13 และการใส่ $ เพื่อล็อคคอลัมภ์และแถวของพื้นที่) หรืออาจใช้เม้าส์คลิกเลือกพื้นที่ที่เราต้องการก็ได้ จากนั้น ok
   
   



3. จากภาพจะเห็นว่าข้อมูลที่เราเลือกทั้งหมดจะอยู่ในลิสต์บ็อกทั้งหมด เราสามารถเลือกข้อมูลจากลิสต์ได้และสามารถ copy ลงแถวอื่นๆ ได้เลย

---

Part 2 สร้าง Drop Down List ต่างหรือข้าม Worksheet

1. ก่อนอื่นเราต้องตั้งชื่อให้กับ Cell range ที่ต้องการใช้เป็นลิสต์ใน drop down ไปที่เมนู Formula เลือก Name Manager จากนั้นคลิกเลือก New ที่กล่องโต้ตอบ


2. ที่กล่องโต้ตอบ New Name ทำตามนี้
   1. หัวข้อ Name: ใส่ชื่อสำหรับพื้นที่เซลส์ที่เราต้องการ จากรูปใช้ชื่อ Product
   2. หัวข้อ Refer to: คือพื้นที่เซลส์ จากรูป เป็น ='Data validation1'!$D$5:$D$13 (หมายถึงเขตพื้นที่ตั้งแต่ D5 ถึง D13 ซึ่งอยู่อีกเวริ์คชีทหนึ่งที่ชื่อว่า Data validation1




3. หลังจากนั้นเราใช้วิธีสร้าง Data Validation เหมือน Part 1 เพียงแต่ในหัวข้อ Source: ใส่ชื่อพื้นที่เซลส์ที่เราตั้งไว้ คือ =Product และเมื่อต้องการเพิ่มข้อมูลสินค้าให้ใช้วิธี Insert/แทรกรายการลงในตารางข้อมูลที่เราได้ตั้งชื่อไว้ หรือ Edit ที่ Name Manager

---

Part 3 Drop Down List ที่สามารถเพิ่มข้อมูลหลักได้โดยไม่ต้อง Insert
ที่ Refer to: ใน Name Manager ใช้ฟังก์ชั่น OFFSET  แทนค่าเดิมจากตัวอย่างใน Part 2 ด้วย
=OFFSET('Data validation1'!$D$5,0,0,COUNTA('Data validation1'!$D:$D),1)

---

---