Validation Excel

DATA > Validation ใน Microsoft Excel และการประยุกต์ใช้

**อัพเดท link ดาวโหลดไฟล์งานตัวอย่างแล้วครับ 2015-สิงหาคม-15 อยู่ด้านล้าง

ในบทความนี้จะนำเสนอการใช้งาน Validation เพื่อสร้าง list ของข้อมูลดังภาพด้านล่าง

ขั้นตอนแรกในการทำงานคือให้สร้างชีทชื่อ DATA_ITEM เพื่อเป็นฐานข้อมูลของสินค้า
จากนั้นเลือกคอลัมภ์ A ทั้งคอลัมภ์ ตั้งชื่อช่วงข้อมูลว่า CODE_ITEM ตามภาพ

จากนั้น เลือกคอลัมภ์ A:B:C ทั้งคอลัมภ์ ตั้งชื่อช่วงข้อมูลว่า ITEM_DATA ตามภาพ

สร้างชีทใหม่ขึ้นมา ชื่อ DATA_CUSTOMER เพื่อเป็นฐานข้อมูลลูกค้า
จากนั้นเลือกคอลัมภ์ A ทั้งคอลัมภ์ ตั้งชื่อช่วงข้อมูลว่า CODE_CUS ตามภาพ

จากนั้น เลือกคอลัมภ์ A:B:C:D ทั้งคอลัมภ์ ตั้งชื่อช่วงข้อมูลว่า CUS_DATA ตามภาพ

สร้าง ชีทใหม่ตามภาพขึ้นมา ชื่อว่า TEST เพื่อทดสอบ การใช้ Validation

อธิบายสี่งที่ต้องการจากการทดสอบนี้ คือ เมื่อเลือกรหัสลูกค้าจาก list แล้ว excel จะทำการ ใส่ชื่อลูกค้า,ชื่อบริษัท และที่อยู่ให้เองโดยอัตโนมัติ

ขั้นตอนการทำ

เลือกเซล B1 ไปที่เมนู Data > Validation

ที่ช่อง Allow เลือก list

ที่ช่อง Source ให้กดปุ่ม F3 เพื่อเลือกช่วงของข้อมูล ในขั้นตอนนี้ เลือก CODE_CUS คือ รหัสลูค้า

จะได้แบบในรูป เสร็จแล้วกด ok

ที่ เซล B1 จะสามารถเลือก รหัสลูกค้าได้แล้ว แบบในรูป

แต่ว่า ชื่อลูกค้า ชื่อบริษัท และที่อยู่ ยังไม่ขึ้นตามมา

ที่เซล B2 ใส่สูตร vlookup ลงไป

ที่ Lookup_Value เลือก เซล B1

ที่ Table_array กด F3 เพื่อเลือกช่วงข้อมูล CUS_DATA

ที่ Col_index_num เลือก 2 เพื่อดึงข้อมูลในคอลัมภ์ที่สอง (ในที่นี้หมายถึง ชื่อลูกค้า)

ในส่วนของ Range_lookup ใส่ 0 ลงไป

เมื่อเสร็จแล้ว จะเห็นว่า ชื่อลูกค้า จะออกมา ตรงกับรหัสของลูกค้า

ในส่วนของชื่อ บริษัท ให้ทำเหมือนขั้นตอนที่ผ่านมา

เพียงแต่ในส่วนของ Col_index_num เลือก 3 เพื่อดึงข้อมูลในคอลัมภ์ที่สาม (ในที่นี้หมายถึง ชื่อบริษัท)

จะได้ดังภาพ

สุดท้ายคือ ที่อยู่ ให้ทำเหมือนเดิม เปลี่นน Col_index_num เป็น 4

เพื่อดึงข้อมูลในคอลัมภ์ที่สี่ (ในที่นี้หมายถึง ที่อยู่)

คราวนี้ ลองทดสอบ โดยการเลือกรหัสลูกค้าใหม่อีกครั้งหนึ่ง โดยลองเลือก "รหัสลูกค้า_07" ตามภาพ

เมื่อเลือกรหัสลูกค้าแล้ว จะเห็นว่า ข้อมูลทั้งหมดเปลี่ยนโดยอัตโนมัติ

จบขั้นตอนการใช้งาน Validation

ต่อไปคือการเลือก สินค้าจาก รหัสสินค้า

ที่เซล A7 ไปที่เมนู Data > Validation ตามภาพ

ทำเหมือนเดิม แต่ที่ source เลือก เป็น CODE_ITEM เพื่อเลือกรหัสสินค้า

เมื่อเสร็จแล้ว จะสามารถเลือก รหัสสินค้าได้ตามแบบภาพด้านล่าง

ที่เซล B7 ใส่สูตร vlookup ตามภาพ และที่ Table_array เลือก ITEM_DATA ตามภาพ

ที่ col_index_num ใส่ 2 เพื่อเลือกชื่อสินค้า

จากนั้นทำเหมือนเดิม ในส่วนของราคาสินค้า เมื่อเสร็จแล้วจะได้ตามภาพ

ทดสอบโดยการเลือกรหัสสินค้า ตามภาพ

ข้อมูลจะเปลี่ยนไปโดยอัตโนมัติ

ขั้นตอนการ copy เพื่อเพีม row เพื่อให้สามารถเลือกได้หลายๆ row ดังภาพ

ทำการเลือกช่วงของเซลตามภาพ

คลิกมุมตามลูกศร ลากลากลงมา

ลากลงมา

จะได้ตามภาพ

เมื่อเสร็จแล้ว เราก็จะสามารถ เลือกรหัสสินค้า และข้อมูลที่เหลือก็จะแสดงออกมาโดยอัตโนมัติ

ในกรณี ที่ไม่มีรหัสสินค้า จะเกิด error ขึ้นตามภาพ

อธิบายการเกิด error ให้เข้าใจกันก่อนว่า สูตร vlookup ที่ค้นหาโดยใช้รหัสสินค้า

เมื่อรหัสสินค้าเป้นค่าว่าง สูตรจะคืนค่า #N/A ขึ้นมา

ทำการดักไม่ให้เกิด error โดยกำนดเงื่อนไขว่า

ถ้า เซลรหัสสินค้า เป็นค่าว่าง ให้คืนค่า ว่าง

ถ้า เซลรหัสสินค้า ไม่เป็นค่าว่าง ให้ใช้งานสูตร vlookup ต่อไป

ขั้นตอนการทำงานคือ

เลือกเซล B8 แล้ว ก๊อปปี้ สูตรไว้

จากนั้นลบสูตร vlookup ทิ้งไป แล้วใส่สูตร IF เข้าไป ตามภาพ

ที่ logical_test ใส่ A8="" หมายถึง ถ้าเซล A8 เป็นค่าว่าง

ที่ Value_if_true ใส่ "" หมายถึง ถ้าเงื่อนไขเป็นจริง (หมายถึง A8 เป็นค่าว่าง) ให้คืนค่าว่าง

ที่ Value_if_false ใส่สูตร vlookup ที่ก๊อบไว้เมื่อกี้ หมายถึง ถ้าเงื่อนไขเป็นเท็จ (หมายถึง A8 ไม่เป็นค่าว่าง) ให้ใช้งานสูตร vlookup

เมื่อเสร็จแล้วที่เซล B8 error จะหายไปแล้ว

ให้ทำเหมือนเดิมที่เซล C8 เหมือนกัน

error จะหายไปแล้ว

ทดสอบโดยการเลือก รหัสสินค้า ตามภาพ

เป็นอันเสร็จ การดักไม่ให้เกิด error

ทำการ ลากเพื่อ copy เหมือนเดิม เป็นอันจบการทำงาน

ตัวอย่างการประยุกต์ใช้

ต้องการทำ invoice โดย มีความต้องการดังนี้

  1. เลือกเพียงรหัสลูกค้า และ excel จะทำการดึงข้อมูลลูกค้ามาใส่ให้เอง
  2. เลือกเพียงรหัสสินค้า excel จะทำการดึงข้อมูล สินค้ามาใส่ให้เอง
  3. กรอกจำนวนสั่งซื้อ excel จะทำการรวมราคาสินค้า คำนวนภาษี และ จำนวนเงินทั้งหมด

ดาวโหลด ไฟล์งานได้ที่นี่ครับ

หวังว่าท่านผู้อ่านคงได้ประโยชน์บ้างนะครับผม