การใช้ 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