วันเสาร์ที่ 13 ตุลาคม พ.ศ. 2555

การใช้ 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
RowColumn
ABCx DEF
1DataADataBDataCข้อ แสดงการใช้ Functionผลลัพท์
2B4200B31=INDIRECT(A2) A2
3C41.82992 =INDIRECT(B2)#REF!
4C3A27003=INDIRECT(A3)700
523000A44=INDIRECT(C5)C3
65 =INDIRECT(INDIRECT(C5))99
7 6 =INDIRECT("B"&ROWS(A2:C5)+1)3000
อธิบายการใช้ฟังก์ชัน INDIRECT ของตางรางข้างบน( Column F2-F7)
Column
ข้อ ผลลัพท์ คำอธิบายผลลัพท์การใช้ฟังก์ชัน INDIRECT
1A2 ในเซลล์ 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

Using Indirect  to find reference text in Excel

ต่อไปจะเป็นการปรับใช้ 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. Define Name of Industry Business to create two drop down lists

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

  10. Setting Name Manager to create 2 drop down lists
  11. เมื่อเสร็จจากชีท Indirect ให้คลิกแทปชีท Indirect1 ซึ่งเป็นชีทที่เราใช้บันทึกข้อโดยดึงข้อมูลหลักจาก Indirect

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


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

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

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

  1. ไม่ระบุชื่อ2/07/2556 1:40 หลังเที่ยง

    ดูไฟล์ vdo ไม่ได้คะ

    ตอบลบ
    คำตอบ
    1. เข้าดูที่ youtube ตามลิ้งค์นี่ได้เลยค่ะ

      http://www.youtube.com/watch?v=oxTudxuyYeI

      ลบ
  2. ได้ประโยชน์มากเลย ครับ

    ตอบลบ