เชี่ยวชาญ Data Validation ใน Excel: คู่มือฉบับสมบูรณ์ทีละขั้นตอน

ในบทช่วยสอนนี้ คุณจะได้เรียนรู้ทุกอย่างเกี่ยวกับ Data Validation ตั้งแต่วัตถุประสงค์ จนถึงวิธีการนำไปใช้ในเวิร์กชีต Excel ของคุณ

ในภาพตัวอย่าง เราได้ใช้ Data Validation ในคอลัมน์ "Stock Quantity" (จำนวนสินค้าคงเหลือ) เพื่อให้เซลล์รับค่าตัวเลขเท่านั้น หากป้อนข้อความ จะแสดงกล่องข้อความว่า "This value doesn't match the data validation restrictions defined for this cell" (ค่านี้ไม่ตรงตามข้อจำกัดของ Data Validation ที่กำหนดไว้สำหรับเซลล์นี้) นั่นคือการทำงานของ Data Validation

⏷ Data Validation ใน Excel
⏷ วัตถุประสงค์ในการใช้ Data Validation
⏷ ประเภทของข้อมูลนำเข้า Data Validation
⏷ 1. วิธีใช้ Data Validation ใน Excel
⏵ 1.1 ใช้ Data Validation ในเซลล์ Excel
⏵ 1.2 ใช้กฎ Data Validation หลายกฎในเซลล์เดียว
⏵ 1.3 ใช้ Data Validation อ้างอิงจากเซลล์อื่น
⏵ 1.4 ใช้ Data Validation พร้อม Checkbox
⏷ 2. สร้าง Drop-down List ด้วย Data Validation
⏷ 3. ใช้ Data Validation ใน Excel พร้อมสีสัน
⏷ 4. การประยุกต์ใช้ Custom Data Validation ใน Excel
⏵ 4.1 อนุญาตเฉพาะตัวอักษรและตัวเลข (Alphanumeric) ด้วย Data Validation
⏵ 4.2 อนุญาตเฉพาะตัวเลขด้วย Custom Data Validation
⏵ Custom Excel Data Validation Rule Not Working
⏷ 5. แก้ไข Data Validation
⏷ 6. คัดลอก Data Validation ไปยังเซลล์ Excel อื่น
⏷ ค้นหาเซลล์ที่มีกฎ Data Validation
⏷ ลบ Data Validation ใน Excel
⏷ เคล็ดลับที่มีประโยชน์สำหรับการใช้ Data Validation
⏷ ข้อจำกัดของ Data Validation
⏷ ปัญหากับ Data Validation และวิธีแก้ไข

ตลอดบทความนี้ คุณจะได้เรียนรู้วิธี:

• ใช้ Data Validation (ในเซลล์, หลายกฎ, กฎอ้างอิงเซลล์อื่น, พร้อม Checkbox)
• สร้าง Drop-down list ด้วย Data Validation
• ใช้ Data Validation
• ใช้ Custom Data Validation (เฉพาะตัวอักษรตัวเลข, เฉพาะตัวเลข)
• แก้ไข Data Validation
• ค้นหา Data Validation
• ลบ Data Validation

หมายเหตุ: เราใช้ Microsoft 365 ในการเตรียมชุดข้อมูลสำหรับบทความนี้ คุณสามารถใช้วิธีที่กล่าวมากับรุ่นตั้งแต่ Excel 2007 เป็นต้นไป

Data Validation ใน Excel คืออะไร?

Data Validation ใน Excel เป็นฟีเจอร์ที่ช่วยควบคุมประเภทของข้อมูลที่ป้อนเข้าเซลล์ ช่วยรักษาความถูกต้องและความสอดคล้องโดยการตั้งกฎสำหรับสิ่งที่สามารถป้อนได้ เช่น จำกัดให้ป้อนได้เฉพาะจำนวนเต็ม ทศนิยม วันที่ หรือรายการที่กำหนดไว้ล่วงหน้า

วัตถุประสงค์ในการใช้ Data Validation ใน Excel

เราสามารถใช้ Data Validation ในเวิร์กชีต Excel ได้เนื่องจากมัน:

• ป้องกันการป้อนข้อมูลที่ผิดพลาดในเซลล์
• รักษารูปแบบข้อมูลให้สม่ำเสมอ
• ทำให้การป้อนข้อมูลง่ายและรวดเร็วขึ้น
• เพิ่มความน่าเชื่อถือของข้อมูลโดยรวม
• สร้างประสบการณ์ผู้ใช้ที่ดี
• อนุญาตให้สร้างกฎกำหนดเองด้วยสูตรสำหรับความต้องการเฉพาะ

ประเภทของข้อมูลนำเข้า Data Validation ใน Excel มีอะไรบ้าง?

1 – แท็บ Settings (การตั้งค่า)
มีกล่องป้อนข้อมูล 2 กล่องคือ Allow และ Data ใช้สำหรับตั้งค่ากฎ Data Validation

ก) กล่อง Allow:
คุณสามารถเลือกประเภท Data Validation ได้ดังนี้:

• Whole Number (จำนวนเต็ม): อนุญาตเฉพาะจำนวนเต็ม
• Decimal (ทศนิยม): อนุญาตตัวเลขทศนิยมด้วยความแม่นยำที่กำหนด
• List (รายการ): สร้าง Drop-down list ให้เลือกจากรายการ
• Date (วันที่): รับประกันว่าการป้อนอยู่ในช่วงวันที่ที่กำหนด
• Time (เวลา): อนุญาตเฉพาะเวลา
• Text Length (ความยาวข้อความ): ควบคุมจำนวนตัวอักษรที่อนุญาต
• Custom (กำหนดเอง): ตั้งกฎเฉพาะตัวด้วยสูตร

ข) กล่อง Data:
ในแท็บ Data คุณต้องกำหนดเกณฑ์ตาม Validation ที่เลือกในแท็บ Allow

• Whole Number: ระบุค่าต่ำสุดและสูงสุด
• Decimal: ตั้งจำนวนตำแหน่งทศนิยมที่อนุญาต
• List: สร้างรายการที่ยอมรับได้
• Date/Time: กำหนดค่าวันที่/เวลาต่ำสุดและสูงสุด
• Text Length: กำหนดจำนวนตัวอักษรต่ำสุดและสูงสุด
• Custom: ป้อนสูตรกำหนดเองสำหรับ Validation

2 – แท็บ Input Message (ข้อความอินพุต):
ใช้แท็บนี้เพื่อปรับแต่งข้อความแนะนำให้ผู้ใช้

กล่อง "Show input message when cell is selected": แสดงข้อความเมื่อผู้ใช้เลือกเซลล์ที่มี Validation

กล่อง "Title and Input Message": ปรับแต่งหัวข้อและข้อความแจ้งเตือน

3 – แท็บ Error Alert (การแจ้งเตือนข้อผิดพลาด):

ก) "Show error alert after invalid data is entered": ให้แจ้งเตือนผู้ใช้หากป้อนข้อมูลไม่ถูกต้อง

ข) Style (รูปแบบ): เลือกประเภทการแจ้งเตือน:

• Stop (หยุดการป้อน)
• Warning (เตือนแต่ไม่บล็อก)
• Information (แจ้งข้อมูลโดยไม่บล็อก)

ค) Title and Error Message: ปรับแต่งหัวข้อและข้อความแจ้งเตือนข้อผิดพลาด

คู่มือ 1 – วิธีใช้ Data Validation ใน Excel

• เลือกเซลล์หรือช่วงที่ต้องการ Validation
• ไปที่แท็บ "Data"
• คลิก "Data Validation" และเลือกประเภท Validation ที่ต้องการ
• กำหนดเกณฑ์ในทุกแท็บตามต้องการ และคลิก "OK" เพื่อใช้ Validation

อ่านต่อสำหรับตัวอย่างเฉพาะเจาะจง

กรณี 1.1 – วิธีใช้ Data Validation ในเซลล์ Excel

เรามีชุดข้อมูลดังภาพ โดยเราจะใช้ Data Validation ในคอลัมน์ "Date of Birth" (วันเกิด) เพื่อรับเฉพาะวันที่

• เลือกช่วง F6:F19
• คลิกแท็บ Data และเลือก Data Validation

• เลือก Date ในกล่อง Allow
• เลือก "greater than" (มากกว่า) ในกล่อง Data
• ตั้ง Start date เป็น 1/1/1900 (รูปแบบ mm/dd/yyyy)
• คลิก OK

• เซลล์ในคอลัมน์จะรับวันที่ในรูปแบบ mm/dd/yyyy เท่านั้น มิฉะนั้น Excel จะแสดงข้อความผิดพลาด

• หากป้อนข้อมูลที่ถูกต้อง จะอนุญาตให้ผ่าน

กรณี 1.2 – วิธีใช้กฎ Data Validation หลายกฎในเซลล์เดียว

เรามีชุดข้อมูลดังภาพ จะรับข้อมูลและตรวจสอบในเซลล์ E6 ตามเกณฑ์ 1 (ราคา > 50) และเกณฑ์ 2 (สินค้าต้องอยู่ในรายการ)

• เลือกเซลล์ E6
• คลิกแท็บ Data ไปที่ Data Validation

• เลือก Custom สำหรับ Allow
• ใช้สูตรต่อไปนี้ในกล่อง Formula:

=OR(E6>$B$6, COUNTIF($B$9:$B$12,E6)=1)

• คลิก OK

• Data Validation ในเซลล์ E6 ทำงานแล้ว หากป้อน 40 จะแสดงข้อความผิดพลาดทันที เพราะไม่ตรงเกณฑ์ไหนเลย

• หากป้อน 70 (มากกว่า 50) จะยอมรับค่า เพราะตรงเกณฑ์ 1

• หากป้อน "Coconut" ในเซลล์ E6 ก็จะยอมรับ เพราะตรงเกณฑ์ 2

กรณี 1.3 – วิธีใช้ Data Validation อ้างอิงจากเซลล์อื่นใน Excel

สมมติเรามีข้อมูลในเซลล์ F6:F11 เราจะใช้มันตรวจสอบค่าในคอลัมน์ "Designation" (ตำแหน่ง)

• เลือกช่วง F5:F11
• กด Ctrl + T
• ติ๊กเลือก My table has headers
• คลิก OK

• เลือกตารางที่สร้างขึ้น
• คลิก Table Design
• ตั้ง Table name ว่า "Info"

• เลือกช่วง D6:D16
• แทรก Data Validation

• เลือก List สำหรับ Allow
• ใช้สูตรด้านล่างในกล่อง Source:

=INDIRECT("Info[Designation]")

• คลิก OK

• จะสร้าง Drop-down list จากตาราง ตอนนี้ในคอลัมน์ "Designation" ให้ใช้รายการนี้ป้อนข้อมูล

• คุณสามารถเลือกค่าได้ตามต้องการ

กรณี 1.4 – วิธีใช้ Data Validation พร้อม Checkbox ใน Excel

หากใช้ Checkbox คุณต้องคลิกที่ Checkbox เพื่อเปิดใช้งาน Data Validation สำหรับการป้อนข้อมูล

พิจารณาชุดข้อมูลดังภาพ เราจะใช้ Data Validation ในคอลัมน์ "Name" และ "Leave (Taken)" โดยใช้ Checkbox

• ไปที่ Developer, Insert, และ Check Box

• เลือก Checkbox, คลิกขวาเลือก Edit Text

• เปลี่ยนชื่อเป็น "Validate Name"

• ทำซ้ำเพื่อเพิ่ม Checkbox อีกอัน เปลี่ยนชื่อเป็น "Validate Leave (Taken)"

• เลือก Checkbox แรก, คลิกขวาเลือก Format Control

• คลิกแท็บ Control ในกล่องโต้ตอบ Format Object
• เลือกเซลล์ $G$6 ใน Cell link
• คลิก OK

• จาก Checkbox จะคืนค่า TRUE หรือ FALSE ในเซลล์ติดกัน G6
• ลิงก์ G7 กับ Checkbox อีกอัน "Validate Leave (Taken)"

• เลือกช่วง C6:C16
• แทรก Data Validation

• เลือก Custom ในกล่อง Allow
• คัดลอกสูตรนี้ในกล่อง Formula:

=IF($G$6, ISTEXT(C5:C16),FALSE)

• คลิก OK

• Data Validation ทำงานแล้ว หากพยายามป้อน "Marylin Pittaman" ในคอลัมน์ "Name" จะมีข้อความผิดพลาด เพราะยังไม่ติ๊ก Checkbox "Validate Name"

• เมื่อติ๊ก "Validate Name" จะรับ 'Marylin Pittaman' ได้ นี่คือวิธี Checkbox ตรวจสอบข้อมูลในคอลัมน์ Name

• เลือกช่วง D6:D16
• แทรก Data Validation ใหม่
• เลือก Custom ใน Allow
• คัดลอกสูตรต่อไปนี้ในกล่อง Formula:

=IF($G$7, ISNUMBER(D6:D16),FALSE)

• คลิก OK

• เมื่อไม่ติ๊ก Checkbox ที่สอง จะไม่สามารถป้อนตัวเลขในคอลัมน์ "Leave (Taken)" ได้

• หากติ๊ก "Validate Leave (Taken)" จะอนุญาตให้ป้อนตัวเลขได้

• ติ๊กทั้งสอง Checkbox จะสามารถป้อนข้อมูลในคอลัมน์ได้ ผลลัพธ์จะเป็นดังภาพ

คู่มือ 2 – วิธีสร้าง Drop-down List ด้วย Data Validation ใน Excel

สมมติเราต้องการสร้าง Drop-down list ตามภาพจากข้อมูลที่มีอยู่ในชีท

• เลือกช่วง D6:D16
• แทรก Data Validation

• เลือก List ในกล่อง Allow
• คลิกไอคอน Source

• เลือกช่วง F6:F11 รายการเหล่านี้จะเพิ่มเข้า Drop-down list

• คลิก OK

• คลิกที่เซลล์ใดเซลล์หนึ่ง
• เลือกไอคอน Drop-down ด้านขวา
• Drop-down list จะโผล่ขึ้นมา เลือกรายการที่ต้องการ

• เราใช้ Drop-down list เติมข้อมูลคอลัมน์ตามภาพ

คู่มือ 3 – วิธีใช้ Data Validation ใน Excel พร้อมสีสัน

สมมติเรามีชุดข้อมูลดังภาพ ในคอลัมน์ "Acquired Number" เราจะอนุญาตเฉพาะจำนวนเต็ม (มากกว่า 40) และไฮไลท์ค่าเหล่านั้นตามเงื่อนไขด้วย Conditional Formatting

• เลือกช่วง D6:D16
• แทรก Data Validation
• เลือก Whole number ใน Allow
• เลือก between ในกล่อง Data
• ตั้ง 40 เป็น Minimum และ 100 เป็น Maximum
• คลิก OK

• Data Validation ทำงานแล้ว สามารถป้อนตัวเลข 40-100 ได้ หากป้อนน้อยกว่า 40 (เช่น 34) จะมีข้อความผิดพลาด

• เลือกช่วง D6:D16
• คลิก Conditional Formatting เลือก New Rule

• คลิก "Format only cells that contain"
• เลือก between
• พิมพ์ตัวเลข 40 และ 59 ในกล่องภายใต้ "Format only cells within"
• คลิก Format เพื่อเพิ่มสี

• คลิกแท็บ Fill ในกล่อง Format Cells
• เลือกสีที่ต้องการ
• คลิก OK

• คลิก OK

• ในคอลัมน์ จะไฮไลท์ตัวเลขน้อยกว่า 60 ด้วยสีที่เลือก

• ทำซ้ำสำหรับช่วง 60-100 ด้วย New Conditional Formatting Rule

• เลือกสี Fill อื่น

• ตัวเลขมากกว่า 60 จะถูกไฮไลท์ด้วยสีที่เลือก ตามภาพ

คู่มือ 4 – การประยุกต์ใช้ Custom Data Validation ใน Excel

เราตั้งกฎ/สูตรสำหรับข้อมูลจาก Custom rules ในกล่องโต้ตอบ Data Validation ตามภาพ

กรณี 4.1 – อนุญาตเฉพาะตัวอักษรและตัวเลข (Alphanumeric) ด้วย Data Validation

Alphanumeric characters คือการรวมตัวอักษร (letters) และตัวเลข (numbers) เรามีชุดข้อมูลดังภาพ และจะเพิ่ม Alphanumeric Characters ในคอลัมน์ "Model"

• เลือกช่วง D6:D16
• ไปที่ Data Validation
• เลือก Custom ในกล่อง Allow
• ใช้สูตรต่อไปนี้:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(B5, ROW(INDIRECT("1:"&LEN(D6))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

• คลิก OK

• จะอนุญาตเฉพาะ Alphanumeric characters ในช่วงที่เลือก

• เราสามารถเก็บเฉพาะตัวอักษรตัวเลขในเซลล์ได้

กรณี 4.2 – อนุญาตเฉพาะตัวเลขด้วย Custom Data Validation ใน Excel

สมมติชุดข้อมูลดังภาพ เราต้องการอนุญาตเฉพาะตัวเลขในคอลัมน์ "Stock Quantity"

• เลือกช่วง D6:D14
• แทรก Data Validation
• เลือก Custom ใน Allow
• ใช้สูตรต่อไปนี้ในกล่อง Formula:

=ISNUMBER(D6)

• คลิก OK

• จะอนุญาตเฉพาะตัวเลขในเซลล์คอลัมน์ มิฉะนั้นจะแสดงข้อความผิดพลาด

• เราเติมข้อมูลเสร็จแล้ว คอลัมน์ "Stock Quantity" มีเฉพาะตัวเลข

Custom Excel Data Validation Rule Not Working: สาเหตุและวิธีแก้

• ตรวจสอบว่าสูตร Custom ที่ป้อนถูกต้อง ตรวจสอบ Syntax error หรือ Typo
• หากสูตรอ้างอิงเซลล์อื่น ให้แน่ใจว่าการอ้างอิงเซลล์ถูกต้อง และเซลล์ที่อ้างอิงมีค่าตามที่คาดหวัง
• ตรวจสอบว่าคุณเลือกช่วงเซลล์ที่ถูกต้องสำหรับการใช้กฎ Data Validation
• ตรวจสอบว่าตั้ง Error alerts ถูกต้อง กรณีกฎถูกละเมิด บางครั้ง Error alerts อาจไม่มองเห็น หรือรูปแบบ Alert อาจไม่ตรงกับสถานการณ์
• ตรวจสอบว่ารูปแบบเซลล์เหมาะสมกับกฎ Validation เช่น กฎตรวจสอบวันที่ เซลล์ควรเป็นรูปแบบวันที่
• หากใช้ Excel รุ่นเก่า ให้ระวังปัญหา Compatibility mode บางฟีเจอร์อาจทำงานไม่ได้ตามที่คาด
• หากเวิร์กชีตถูกป้องกัน ตรวจสอบว่าอนุญาตให้เปลี่ยน Data Validation ได้หรือไม่ บางครั้งการตั้งค่าป้องกันอาจจำกัดการใช้กฎใหม่
• ตรวจสอบความขัดแย้งกับกฎ Conditional Formatting กฎที่ขัดแย้งอาจทับซ้อนกัน

คู่มือ 5 – วิธีแก้ไข Data Validation ใน Excel

เรามีชุดข้อมูลดังภาพ มี Drop-down list ที่ใช้ Data Validation อยู่แล้ว เราจะแก้ไขและเพิ่มรายการใหม่ "Representative" เข้า Drop-down list

• เลือกเซลล์ใดเซลล์หนึ่งในคอลัมน์ "Designation"
• เพิ่ม ", Representative" ในกล่อง Source (รวมคอมมาด้วย)
• ติ๊ก "Apply these changes to other cells with the same setting" เพื่อนำไปใช้กับเซลล์อื่นที่มีการตั้งค่าเดียวกัน
• คลิก OK

• จะเพิ่มรายการใหม่ "Representative" เข้าในรายการ

• คุณสามารถใช้รายการนี้ป้อนข้อมูลในเซลล์ได้

คู่มือ 6 – วิธีคัดลอก Data Validation ไปยังเซลล์ Excel อื่น

เรามีชุดข้อมูลที่คอลัมน์ "Stock Quantity" มี Data Validation กฎจำนวนเต็มเท่านั้น เราจะคัดลอก Data Validation นี้ไปที่คอลัมน์ "Sold Quantity"

• เลือกเซลล์ใดเซลล์หนึ่งใน "Stock Quantity"
• กด Ctrl + C
• เลือกเซลล์ทั้งหมดในคอลัมน์ "Sold Quantity"
• คลิกขวา แล้วคลิก Paste Special จากเมนู

• คลิก Validation ภายใต้ Paste และเลือก OK

• หากป้อนตัวเลขในเซลล์ "Sold Quantity" จะอนุญาต แต่หากป้อนข้อความ จะโผล่กล่องข้อความผิดพลาด

• คุณสามารถป้อนตัวเลขในเซลล์คอลัมน์ได้

วิธีค้นหาเซลล์ที่มีกฎ Data Validation

ในภาพด้านล่าง เรามีชุดข้อมูลที่มี Data Validation แต่ไม่รู้ว่าอยู่คอลัมน์ไหน

• คลิก Home ไปที่ Find & Select
• เลือก Data Validation จากรายการ

• จะเลือกคอลัมน์หรือเซลล์ที่มี Data Validation (เช่น D6:D16)

วิธีลบ Data Validation ใน Excel

เรามีชุดข้อมูลดังภาพที่มี Data Validation ในคอลัมน์ "Designation" มาลบออกกัน

• เลือกช่วง D6:D16 ในคอลัมน์
• คลิก Data ไปที่ Data Validation
• คลิก "Clear All" ในกล่องโต้ตอบ Data Validation
• เลือก OK

• จะลบ Data Validation ที่มีอยู่ในเซลล์ที่เลือกออก

เคล็ดลับที่มีประโยชน์สำหรับการใช้ Data Validation ใน Excel

• ใช้ Drop-down list สำหรับการป้อนข้อมูลที่สม่ำเสมอและไม่ผิดพลาด
• ให้ข้อความแนะนำที่มีประโยชน์สำหรับผู้ใช้
• ตั้งค่า Alert สำหรับการป้อนข้อมูลที่ไม่ถูกต้อง
• ใช้ Named ranges สำหรับ Drop-down list ที่ยืดหยุ่น
• ใช้สูตรสำหรับ Validation เฉพาะเจาะจง
• พิจารณาป้องกันชีทเพื่อบังคับใช้กฎ
• ทดสอบอย่างละเอียดก่อนยืนยันกฎ

ข้อจำกัดของ Data Validation ใน Excel

• ตรวจจับข้อผิดพลาดเฉพาะหลังจากย้ายไปเซลล์ถัดไป
• ต้องตั้งค่ากฎแยกกันสำหรับแต่ละชีท
• กฎใช้งานระดับเซลล์ ไม่ใช่ข้ามหลายเซลล์
• Drop-down list ไม่อัปเดตอัตโนมัติเมื่อรายการแหล่งที่มาทำการเปลี่ยนแปลง
• ตัวเลือกช่วงวันที่ในตัว (Built-in) ค่อนข้างจำกัด
• ตัวเลือกปรับแต่ง Error alerts จำกัด
• ไม่มีตัวเลือก Built-in สำหรับจำกัดหรืออนุญาตตัวอักษรพิเศษเฉพาะ

ปัญหากับ Data Validation และวิธีแก้ไข

มีปัญหาอื่นๆ เช่น Data Validation ทำงานไม่ถูกต้องกับ Copy & Paste และ Data Validation สีเทา (Greyed out) ใน Excel คุณสามารถอ่านบทความเหล่านั้นเพื่อเรียนรู้ปัญหาทั่วไปและวิธีแก้ไข

ดาวน์โหลด Practice Workbook

Data Validation ใน Excel: Knowledge Hub

• How to Set Limit in Excel Cell
• Excel Formula Not to Exceed a Certain Value
• Data Validation List from Table in Excel
• Data Validation List from Another Sheet
• Excel Data Validation for Date Format
• How to Use Data Validation in Excel with Color
• Circle Invalid Data in Excel
• Data Validation with Checkbox Control in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel
• Excel Data Validation Greyed Out
• Multiple Data Validation in One Cell
• How to Copy Data Validation in Excel
• Remove Blanks from Data Validation List in Excel
• Remove Data Validation Restrictions in Excel
• Excel Custom Data Validation

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!