Search

วันเสาร์ที่ 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

วันพฤหัสบดีที่ 11 ตุลาคม พ.ศ. 2555

การใช้ฟังก์ชัน IFERROR กับ VLOOKUP

การพัฒนาฟีเจอร์ต่างๆ ของ MS Office ทำให้เกิดฟังก์ชันใหม่ๆ ที่ใช้งานง่ายขึ้นและสามารถใช้ตรวจสอบความถูกต้องได้ดีขึ้น
โดยที่เวอร์ชั่นเก่าจะต้องใช้หลายฟังก์ชันซ้อนกัน และฟังก์ชัน IFERROR ก็เป็นหนึ่งฟังก์ชันที่พูดถึง

IFERROR ทำหน้าที่คล้าย IF เพียงแต่เพิ่มศักยภาพการจัดการกับข้อมูลได้กว้างขึ้นและตรวจสอบความถูกต้องไปพร้อมๆ กัน
ไวยากรณ์และความหมาย

IFERROR(value,value_if_error)

  • value คืออาร์กิวเมนต์ที่ใช้ตรวจสอบเพื่อหาข้อผิดพลาด
  • value_if_error คือค่าที่จะส่งกลับถ้า value ได้ผลลัพท์เป็นค่าความผิดพลาด(Error_val) และชนิดของค่าความผิดพลาดที่จะทำให้ฟังก์ชันส่งคืนค่า value_if_error มีอยู่ 7 ชนิด
    1. #N/A
    2. #VALUE!
    3. #REF!
    4. #DIV/0!
    5. #NUM!
    6. #NAME?
    7. #NULL!


โจทย์ตัวอย่างทดสอบการใช้ IFERROR

RowColumn
2ABCDE
3ProductPriceDis. Dis.PriceFormula in Dis.Price
4Ice-Cream Maker C30
5,400
3%
5,238
=IFERROR(B4-(B4*C4),B4)
5Ice-Cream Maker C45
9,100
5%
8,645
=IFERROR(B5-(B5*C5),B5)
6Ice-Cream Maker C50
17,900
7%
16,647
=IFERROR(B6-(B6*C6),B6)
7Mini Prep Processor
3,600
3,600
=IFERROR(B7-(B7*C7),B7)
8Soup Blender SB30
10,000
3%
9,700
=IFERROR(B8-(B8*C8),B8)
9Stick hand Blender 
3,000

3,000
=IFERROR(B9-(B9*C9),B9)
10Waffle Maker WF20
5,800
2%
5,684
=IFERROR(B10-(B10*C10),B10)

จากตัวอย่างกำหนดให้ Dis.Price เป็นราคาสุทธิที่หักส่วนลด (Dis.) ตามเปอร์เซนต์ที่กำหนดไว้โดยให้ Price เป็นราคาฐานที่่ใช้คำนวณส่วนลด และถ้าสินค้าชนิดใดไม่มีส่วนลด (Dis.) ให้ใช้ราคาปกติ (Price) ซึ่งจะแสดงสูตรที่ใช้ในคอลัมท์ Formula in Dis.Price

using IFERROR function
นอกจากนี้ถ้าเราใช้ IFFEROR ร่วมกับ Vlookup เพื่อให้สูตรคืนค่าผลลัพท์จากสองฐานข้อมูลได้ในเวลาเดียวกัน ซึ่งจะเป็นข้อมูลจาก Worksheet เดียวกัน หรือ ต่าง Worksheet ก็ได้

ตัวอย่างถัดไปเป็นการทดสอบ IFERROR ร่วมกับ VLOOKUP
เราจะใช้ข้อมูลจากรูปเป็นฐานข้อมูล ซึ่งข้อมูลด้านซ้ายเป็นจำนวนสต็อคสินค้าของ LED TV และด้านขวาเป็นจำนวนสต็อคสินค้าของ Kitchenware
RowColumn
2ABC
3LED TVQTY
41LG
300
52SAMSUNG
250
63PANASONIC
120
74BRAVIA
90
85TOSHIBA
560
96SUNYO
50
10
11
12

E FG
KICHENWAREQTY
1OTTO
1,110
2IMARFLEX
500
3HOUSE WORTH
459
4MISAWA
50
5VICTOR
89
6SHARP
300
7PHILIPS
70
8DEBRANDT
79
9CUISINART
250

เราจะใช้การค้นหาจำนวนสต็อคของสินค้า 2 หมวดนี้ โดยใช้ฟังก์ชัน IFERROR ที่ โดยใช้เซลล์ A13 เป็นตัวกำหนดเงื่อนไข ให้หาจำนวนสินค้าในสต็อคแบรนด์ Cuisinart ว่ามีจำนวนกี่หน่วย ซึ่งผลลัพท์เท่ากับ 250 ที่เซลล์ B13 และ C13 เป็นรูปแบบฟังก์ชัน IFERROR ที่ใช้ใน B13
และ ที่ เซลล์ A14 เป็นการทดสอบสูตรแบบเดียวกันเพียงแต่เปลี่ยนเป็น แบรนด์ LG ได้คำตอบ 300 หน่วย (B14), แสดงสูตรที่ (C14)

RowColumn
11ABC
12BrandQty.Formula in Qty. (Column B)
13CUISINART
250
=IFERROR(VLOOKUP(A13,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))
14 LG300=IFERROR(VLOOKUP(A14,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))