ลักษณะการทำงานของ INDIRECT มันคล้ายกับการแข่งแรลลี่ หาอาร์ซีที่ซ่อนอยู่ตามจุด เพื่อแกะรอยเดินทางต่อ มันคล้าย INDIRECT ซ้อน INDIRECT ซ้อนกันจนถึงจุดฟินิช
ฟังก์ชัน INDIRECT ทำหน้าที่ส่งกลับค่าอ้างอิงที่อยู่ในเซลล์ ด้วยการระบุตำแหน่งของเซลล์ (Column Letter,Row Number)
และยังสามารถอ้างอิงไปยังภายนอก หรือสมุดงานอื่นได้ ทำให้ปรับใช้ได้กับฟังก์ชันอื่นๆ ได้อีกหลายฟังก์ชัน
เช่น ใช้ร่วมกับ Data Validation ในการสร้าง Drop down lists 2 คอลัมน์
และยังสามารถอ้างอิงไปยังภายนอก หรือสมุดงานอื่นได้ ทำให้ปรับใช้ได้กับฟังก์ชันอื่นๆ ได้อีกหลายฟังก์ชัน
เช่น ใช้ร่วมกับ Data Validation ในการสร้าง Drop down lists 2 คอลัมน์
ไวยากรณ์และความหมาย
=INDIRECT(ref_text, a1)
- ref_text คือเซลล์อ้างอิง ซึ่งถ้าการอ้างอิงไม่ใช่เซลล์ที่ถูกต้อง ฟังก์ชัน INDIRECT จะส่งกลับค่าความผิดพลาด #REF! และ
หากมีการอ้างอิงไปยังสมุดงานอื่น(การอ้างอิงภายนอก) สมุดงานที่เชื่อมโยงกันต้องเปิดอยู่ ไม่เช่นนั้นฟังก์ชันก็จะส่งค่าความผิดพลาดเช่นกัน
- ถ้า a1 เป็น TRUE หรือถูกละเว้น ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ A1
- ถ้า a1 เป็น FALSE แล้ว ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ R1C1
- ข้อจำกัดของ INDIRECT
- เซลล์ที่เป็น ref_text สูตรจะไม่อ้างอิงที่เซลล์เดิมถ้ามีการ ตัด(cut), ลบ(Delete) หรือแทรกแถว หรือคอลัมน์ (Insert) ซึ่งทำให้เซลล์เปลี่ยนตำแหน่ง
- ถ้าต้องการอ้างอิงที่เซลล์เดิม ตลอดไปไม่ว่าแถวที่อยู่บนเซลล์จะถูกลบหรือเซลล์นั้นจะถูกย้ายไป ให้ใช้ " " เพิ่มในตัวสูตร (=INDIRECT("ref_text"))
- สมมุติในเซลล์ 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 |
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 คอลัมน์
- เปิดไฟล์งานไปที่ชีท indirect ซึ่งเป็นที่เก็บข้อมูลรายชื่อบริษัทในธุรกิจอุตสาหกรรม
- ไปที่ Formula > Name Manger คลิก New เพื่อกำหนดชื่อให้กับพื้นที่เซลล์
- ที่ Name: พิมพ์ว่า INDUSTRY และส่วนของ Refer to : ใช้เม้าส์คลุมพื้นที่ช่วงเซลล์ B2:G2 คลิก ok (ชุดนี้คือประเภทธุรกิจซึ่งจะเป็นข้อมูลหลัก)
- คลิก New เพื่อสร้างชื่อกลุ่มใหม่ (ชุดนี้จะเป็นรายชื่อบริษัทในแต่ละประเภทธุรกิจนั่นคือเป็นกลุ่มย่อย)
- ที่ Name: พิมพ์ว่า ยานยนต์ และส่วนของ Refer to : ใช้เม้าส์คลุมช่วงเซลล์ B4:B13 คลิก ok
- คลิก New เพื่อเพิ่มชื่อกลุ่มใหม่
- ที่ Name: พิมพ์ว่า วัสดุอุตสาหกรรมและเครื่องจักร และส่วนของ Refer to :ใช้เม้าส์คลุมช่วงเซลล์ C4:C10 คลิก ok
- ทำแบบเดียวกันกับ ช่วงเซลล์ D4:D5 (กระดาษและวัสดุการพิมพ์), ช่วงเซลล์ E4:E13 (ปิโตรเคมีและเคมีภัณฑ์), ช่วงเซลล์ F4:F13 (บรรจุภัณฑ์), ช่วงเซลล์ G4:G13 (เหล็ก)
- เมื่อเสร็จจากชีท Indirect ให้คลิกแทปชีท Indirect1 ซึ่งเป็นชีทที่เราใช้บันทึกข้อโดยดึงข้อมูลหลักจาก Indirect
- สร้าง Drop down list ที่เซลล์ B3 (ใต้ประเภทธุรกิจ) แล้วคลิกที่ Data > Validation
- หัวข้อ Allow: เลือกเป็น List
- หัวข้อ Source: พิมพ์ =Industry
- แล้ว copy สูตรลงเซลล์ที่จะใช้งาน
- สร้าง drop down list ที่เซลล์ C3 (ใต้บริษัท)แล้วคลิกที่ Data > Validation
- หัวข้อ Allow: เลือกเป็น List
- หัวข้อ Source: พิมพ์ =INDIRECT(B3)
- แล้ว copy สูตรลงเซลล์ที่จะใช้งาน
- ตอนที่สร้าง Drop down list ในข้อ 11. Excel เมื่อคลิก ok จะแสดงกล่องเตือน error ขึ้้นมา สาเหตุเพราะว่า ข้อมูลหลักยังเป็นค่าว่าง ให้คลิก Yes
ดูไฟล์ vdo ไม่ได้คะ
ตอบลบเข้าดูที่ youtube ตามลิ้งค์นี่ได้เลยค่ะ
ลบhttp://www.youtube.com/watch?v=oxTudxuyYeI
ขอบคุณครับ
ตอบลบได้ประโยชน์มากเลย ครับ
ตอบลบ