จำกัดข้อมูลนำเข้าด้วยการตรวจสอบข้อมูล

การตรวจสอบความถูกต้องของข้อมูลจะกำหนดกฎว่าควรยอมรับค่าใดในเซลล์ แทนที่จะต้องเชื่อว่าผู้ใช้จะพิมพ์ข้อมูลได้ถูกต้อง สเปรดชีตจะบังคับใช้กฎดังกล่าว

คุณสามารถกำหนดให้เซลล์รับเฉพาะจำนวนเต็ม เฉพาะวันที่ในช่วงที่กำหนด หรือเฉพาะค่าจากรายการที่อนุมัติไว้ได้

• ตรวจจับการพิมพ์ผิดก่อนที่จะทำให้ข้อมูลของคุณปนเปื้อน
• แนะนำผู้ใช้ด้วยคำแนะนำและข้อความเตือน
• ใช้งานได้ทั้งใน Excel และ Google Sheets

ฟีเจอร์นี้อยู่ในตำแหน่งที่แตกต่างกันเล็กน้อยในแต่ละโปรแกรม

• Excel: เลือกเซลล์ ไปที่แท็บ ข้อมูล แล้วคลิก การตรวจสอบความถูกต้องของข้อมูล
• Google Sheets: เลือกเซลล์ จากนั้นเลือก ข้อมูล > การตรวจสอบความถูกต้องของข้อมูล แล้วคลิก เพิ่มกฎ

ทั้งสองโปรแกรมจะเปิดแผงที่ให้คุณเลือกประเภทกฎ กำหนดขีดจำกัด และตัดสินใจว่าจะเกิดอะไรขึ้นเมื่อมีผู้ป้อนข้อมูลที่ไม่ถูกต้อง

กฎทั่วไปข้อหนึ่งคือการจำกัดให้เซลล์รับเฉพาะตัวเลข ในกล่องโต้ตอบของ Excel ให้เลือก จำนวนเต็ม หรือ ทศนิยม จากนั้นกำหนดเงื่อนไข เช่น ระหว่าง 1 ถึง 100

หากมีผู้พิมพ์ abc หรือ 150 รายการที่ป้อนจะถูกปฏิเสธ

• ใช้ จำนวนเต็ม สำหรับจำนวนรายการและปริมาณ
• ใช้ ทศนิยม สำหรับราคาและเปอร์เซ็นต์
• ตัวดำเนินการได้แก่ ระหว่าง มากกว่า และเท่ากับ

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

วิธีนี้เหมาะอย่างยิ่งสำหรับตารางการจองหรือกำหนดส่งงาน ซึ่งวันที่ในอดีตไม่สมเหตุสมผล

• ปฏิเสธวันที่ก่อน =TODAY() เพื่อป้องกันการลงวันที่ย้อนหลัง
• กำหนดวันที่เริ่มต้นและวันที่สิ้นสุดร่วมกันเพื่อบังคับใช้ช่วงเวลาของโครงการ

สเปรดชีตจะตรวจสอบทุกรายการที่ป้อนเทียบกับกฎโดยอัตโนมัติ

การตรวจสอบความถูกต้องยังใช้ควบคุมข้อความได้ด้วย Excel มีการตรวจสอบแบบ ความยาวข้อความ ซึ่งเหมาะสำหรับช่องข้อมูลอย่างรหัสประเทศสองตัวอักษรหรือ ID ที่มีความยาวตายตัว

• กำหนดให้ความยาว เท่ากับ 2 สำหรับรหัสรัฐ
• กำหนดให้ความยาว น้อยกว่าหรือเท่ากับ 50 สำหรับบันทึกสั้น ๆ

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

หากกฎที่มีมาให้ไม่สามารถรองรับสิ่งที่ต้องการได้ ให้ใช้กฎ กำหนดเอง (Excel) หรือ สูตรกำหนดเองคือ (Sheets) คุณจะเขียนสูตรที่ต้องประเมินผลเป็น TRUE จึงจะอนุญาตให้ป้อนค่าได้

ตัวอย่างเช่น หากต้องการบังคับให้ข้อความในเซลล์ A2 เป็นตัวพิมพ์ใหญ่:

=EXACT(A2,UPPER(A2))

หากค่าที่พิมพ์ไม่ใช่ตัวพิมพ์ใหญ่ทั้งหมด สูตรจะคืนค่าเป็น FALSE และรายการที่ป้อนจะถูกบล็อก

กฎแบบกำหนดเองที่ได้รับความนิยมข้อหนึ่งคือการป้องกันไม่ให้ป้อนค่าซ้ำในคอลัมน์ เลือก A2:A100 แล้วใช้สูตรแบบกำหนดเอง:

=COUNTIF($A$2:$A$100,A2)=1

สูตรนี้หมายความว่า ค่าที่เพิ่งพิมพ์ต้องปรากฏเพียงครั้งเดียวในช่วงดังกล่าว หากมีค่านั้นอยู่แล้ว จำนวนจะเป็น 2 และกฎจะไม่ผ่าน

• เหมาะอย่างยิ่งสำหรับ ID ที่ไม่ซ้ำกัน อีเมล หรือเลขที่ใบแจ้งหนี้
• สังเกตช่วงแบบสัมบูรณ์และ A2 แบบสัมพัทธ์

Excel ให้คุณแสดง ข้อความแจ้งการป้อนข้อมูล — คำแนะนำสั้น ๆ ที่จะปรากฏเมื่อเลือกเซลล์ ก่อนที่ผู้ใช้จะพิมพ์ข้อมูล

ใช้ข้อความนี้เพื่ออธิบายกฎด้วยภาษาที่เข้าใจง่าย เช่น ป้อนจำนวนเต็มตั้งแต่ 1 ถึง 100

• แนะนำผู้ใช้ล่วงหน้า
• ลดจำนวนรายการที่ถูกปฏิเสธและความหงุดหงิด

Google Sheets มีคำแนะนำลักษณะเดียวกันผ่านข้อความช่วยเหลือการตรวจสอบความถูกต้องในกฎ

เมื่อข้อมูลไม่เป็นไปตามกฎ แท็บ การแจ้งเตือนข้อผิดพลาด ของ Excel จะกำหนดระดับความเข้มงวด:

• หยุด — ปฏิเสธรายการที่ป้อนโดยสิ้นเชิง
• คำเตือน — แจ้งให้ทราบ แต่ยังให้ผู้ใช้ดำเนินการต่อได้
• ข้อมูล — เพียงแจ้งให้ผู้ใช้ทราบ

Google Sheets มีตัวเลือกคล้ายกันคือ ปฏิเสธข้อมูลที่ป้อน หรือ แสดงคำเตือน เลือก หยุด เมื่อข้อมูลที่ไม่ถูกต้องเป็นสิ่งที่ยอมรับไม่ได้ และเลือก คำเตือน เมื่อยังอนุญาตให้ใช้วิจารณญาณได้

คุณสามารถตรวจสอบหรือล้างกฎได้ทุกเมื่อ

• ใน Excel ให้เลือกเซลล์แล้วเปิด การตรวจสอบความถูกต้องของข้อมูล อีกครั้ง จากนั้นคลิก ล้างทั้งหมด เพื่อลบกฎ
• เครื่องมือ วงกลมข้อมูลที่ไม่ถูกต้อง ของ Excel จะเน้นค่าที่มีอยู่เดิมซึ่งไม่เป็นไปตามกฎที่เพิ่งเพิ่ม
• ใน Sheets ให้เปิด การตรวจสอบความถูกต้องของข้อมูล แล้วลบกฎออกจากแผง

โปรดจำไว้ว่าการตรวจสอบความถูกต้องจะตรวจเฉพาะรายการที่ป้อน ใหม่ เว้นแต่คุณจะตรวจสอบรายการเก่าด้วย

ยังมีกฎอีกประเภทหนึ่งที่ควรกล่าวถึง เพราะจะนำไปสู่บทเรียนถัดไป นั่นคือกฎ รายการ

แทนที่จะยอมรับตัวเลขหรือวันที่ใด ๆ กฎรายการจะจำกัดเซลล์ให้รับเฉพาะตัวเลือกที่กำหนด เช่น Open Closed หรือ Pending

• เป็นพื้นฐานของเมนูแบบดรอปดาวน์
• คุณสามารถพิมพ์ตัวเลือกเองหรืออ้างอิงไปยังช่วงเซลล์ได้

ในบทเรียนถัดไป คุณจะเปลี่ยนสิ่งนี้ให้เป็นรายการแบบดรอปดาวน์ที่เรียบร้อยและใช้งานง่าย

คุณต้องการให้คอลัมน์หนึ่งไม่ยอมรับค่าที่มีอยู่แล้วที่อื่นในคอลัมน์ สูตรการตรวจสอบความถูกต้องแบบกำหนดเองใดทำเช่นนี้สำหรับเซลล์ A2 ในช่วง A2:A100?

คุณได้เรียนรู้การควบคุมข้อมูลที่ป้อนด้วย การตรวจสอบความถูกต้องของข้อมูล:

• เปิดใช้งานผ่านแท็บ ข้อมูล (Excel) หรือ ข้อมูล > การตรวจสอบความถูกต้องของข้อมูล (Sheets)
• จำกัดให้รับเฉพาะจำนวนเต็ม ทศนิยม วันที่ หรือข้อความที่มีความยาวตามกำหนด
• เขียน สูตรแบบกำหนดเอง เช่น =COUNTIF($A$2:$A$100,A2)=1 สำหรับกฎขั้นสูง
• เพิ่มข้อความแจ้งการป้อนข้อมูล และเลือก หยุด หรือ คำเตือน สำหรับข้อผิดพลาด

ถัดไป คุณจะสร้าง รายการแบบดรอปดาวน์ ที่ใช้งานง่าย โดยใช้ช่วงที่มีชื่อ