DATA > Validation ใน Microsoft 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 โดย มีความต้องการดังนี้
- เลือกเพียงรหัสลูกค้า และ excel จะทำการดึงข้อมูลลูกค้ามาใส่ให้เอง
- เลือกเพียงรหัสสินค้า excel จะทำการดึงข้อมูล สินค้ามาใส่ให้เอง
- กรอกจำนวนสั่งซื้อ excel จะทำการรวมราคาสินค้า คำนวนภาษี และ จำนวนเงินทั้งหมด
หวังว่าท่านผู้อ่านคงได้ประโยชน์บ้างนะครับผม
ความเห็น (12)
เพื่อนผมเองครับ เก่งจริง
ถ้ากรณีที่เลือก list นั้นมีเยอะมาก เราจะทำให้มันหาเฉพาะ list ที่ขึ้นต้นด้วยตัวอักษรนั้นๆ ที่เราต้องการเรียกเพื่อลดการหาข้อมูลได้ไหมคะ รบกวนตอบด้วยนะคะ ตามอีเมล์ที่ให้ไว้ ขอบคุณค่ะ
คุณRas พอจะมีตัวอย่าให้มั๊ยครับ
ขอบคุณมากๆค่ะ เก่งมากเลย ใช้งานแล้ว OK เลยค่ะ
ช่วยได้เยอะเลยค่ะ ขอบคุณค่ะ
Robin bond
รบกวนด้วยจ้า
เมื่อคลิกเลือกข้อมูลจาก validation จะแสดงข้อมูลแค่ 8 แถวเท่านั้น
มีวิธีที่จะทำให้แสดงข้อมูลมากกว่านี้หรือทั้งหมดได้หรือไม่
่ขอบคุณครับ
อ่านแล้วเข้าใจง่ายดี ขอบคุณค่ะ
ดาวน์โหลดไฟล์งานไม่ได้เลยค่ะ
ผมมาดูตอนนี้รูปหายไปหลายรูป บางรูปเดาไม่ได้ว่ากำลังจะทำอะไร ถ้าเจ้าของเข้ามาดูรบกวน แก้ไขให้ด้วยครับ
ผลทำตรงราคาสินค้าไม่ได้อะ ครับ (ขอโทดครับมือใหม่)
แก้ไข link ดาวโหลดไฟล์งานแล้วครับ
รูปภาพ ขึ้นปกตินะครับ
วัชรินทร์ ไชยมงคล
ดาวโหลดไม่ได้เลยคับ
กระทู้เป็นประโยชน์มากค่ะ
แต่ไม่สามารถดาวน์โหลดไฟล์ได้แล้ว
รบกวนเจ้าของกระทู้ด้วยนะคะ