Data Validation เป็นฟีเจอร์สำคัญใน Excel ในบทความนี้ เราจะเห็นว่าการตรวจสอบข้อมูล (Data Validation) ของ Excel ที่ขึ้นอยู่กับอีกเซลล์หนึ่ง ถูกสร้างขึ้นอย่างไร Data Validation ทำให้รายการมีความสร้างสรรค์และใช้งานง่ายขึ้น แทนที่จะมีข้อมูลในเซลล์ต่างๆ ของคอลัมน์ คุณมีตัวเลือกในการเลือกข้อมูลใดๆ ตามรายการในเซลล์ ในบทความนี้ เราจะเห็นกระบวนการสร้างรายการที่ขึ้นกันได้ (Dependent List) โดยใช้ Data Validation ของ Excel เราจะเห็นกระบวนการจำกัดการป้อนข้อมูลในช่วงของเซลล์ด้วย Data Validation ด้วย
Data Validation ใน Excel คืออะไร?
Data validation เป็นฟีเจอร์ของ Excel ที่คุณสามารถสร้างกฎได้ว่า คุณต้องการให้ข้อมูลประเภทไหนถูกป้อน vàoเซลล์ โดยพื้นฐานแล้ว มันอนุญาตให้คุณใช้กฎใดๆ ขณะป้อนข้อมูล มีกฎการตรวจสอบ (Validation rules) หลากหลาย ตัวอย่างเช่น คุณสามารถอนุญาตให้มีค่าตัวเลขหรือข้อความในเซลล์ผ่าน Data Validation หรืออนุญาตค่าตัวเลขในช่วงเฉพาะ Data validation สามารถจำกัดวันที่และเวลาที่อยู่นอกช่วงที่กำหนด มันช่วยให้เราตรวจสอบความถูกต้องและคุณภาพของข้อมูลก่อนนำไปใช้ Data validation ให้การตรวจสอบหลายอย่างเพื่อให้แน่ใจว่าข้อมูลที่ป้อนหรือจัดเก็บมีความสอดคล้อง
วิธีทำ Data Validation ใน Excel
เพื่อทำ Data Validation ใน Excel คุณต้องกำหนดกฎการตรวจสอบข้อมูล หลังจากนั้น หากคุณป้อนข้อมูลใดๆ Data Validation จะทำงานบนมัน หากข้อมูลตรงตามกฎ Data Validation จะวางข้อมูลลงในเซลล์ มิฉะนั้น จะแสดงข้อความผิดพลาด
ก่อนอื่น ให้ใช้ชุดข้อมูลที่รวม รหัสนักเรียน ชื่อนักเรียน และอายุ เราต้องการทำ Data Validation ที่อายุต้องน้อยกว่า 18
จากนั้น เลือกเซลล์ D11 หลังจากนั้น ไปที่แท็บ Data บนริบบอน จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ ที่นี่เลือกแท็บ Settings จากนั้น เลือก Whole Number จากส่วน Allow หลังจากนั้น ติ๊กเลือก Ignore Blank ต่อไป เลือกตัวเลือก Less Than จาก Date จากนั้น ตั้งค่า Maximum เป็น 18 สุดท้าย คลิกที่ OK
ต่อไป หากเราเขียน 20 เป็นอายุ จะแสดงข้อผิดพลาด เพราะมันเกินขีดจำกัดสูงสุดใน Data Validation นั่นคือสิ่งที่เราได้จาก Data Validation
4 ตัวอย่างที่เหมาะสมในการทำ Data Validation ตามอีกเซลล์หนึ่งใน Excel
เพื่อใช้ Data Validation ตามอีกเซลล์หนึ่งใน Excel เราพบ 4 ตัวอย่างที่แตกต่างกัน ซึ่งคุณจะได้แนวคิดชัดเจน ในบทความนี้ เราจะใช้ฟังก์ชัน INDIRECT และ Named Range เพื่อใช้ Data Validation เราจะใช้การอ้างอิงเซลล์ และวิธีจำกัดการป้อนค่าด้วย Data Validation ทุกวิธีเหล่านี้ใช้งานได้ค่อนข้างง่าย เพื่อเข้าใจชัดเจน โปรดทำตามวิธีการอย่างถูกต้องครับ
1. การใช้ฟังก์ชัน INDIRECT
วิธีแรกของเรานั้นขึ้นอยู่กับการใช้ ฟังก์ชัน INDIRECT ในวิธีนี้ เราต้องการใช้ ฟังก์ชัน INDIRECT นี้ในกล่องโต้ตอบ Data Validation ฟังก์ชันนี้ช่วยให้เราเปลี่ยนตัวเลือกแบบเลื่อนลงได้ตามเซลล์หนึ่ง เราใช้ชุดข้อมูลที่มี 2 รายการและประเภทต่างๆ ของพวกมัน
เพื่อเข้าใจวิธีนี้ชัดเจน ทำตามขั้นตอนครับ
ขั้นตอน
- ก่อนอื่น แปลงคอลัมน์ทั้งสามเป็นตารางที่ต่างกัน
- จากนั้น เลือกช่วงเซลล์ B5 ถึง B6
- ผลลัพธ์ แท็บ Table Design จะปรากฏ
- ไปที่แท็บ Table Design บนริบบอน
- จากนั้น เปลี่ยน Table Name จากกลุ่ม Properties
- จากนั้น เลือกช่วงเซลล์ D5 ถึง D9
- เปลี่ยน Table Name จากกลุ่ม Properties
- สุดท้าย เลือกช่วงเซลล์ F5 ถึง F9
- จากนั้น เปลี่ยน Table Name จากกลุ่ม Properties เหมือนวิธีก่อนหน้า
- หลังจากนั้น ไปที่แท็บ Formula บนริบบอน
- เลือก Define Name จากกลุ่ม Define Names
- จากนั้น กล่องโต้ตอบ New Name จะปรากฏ
- ตั้งชื่อ
- ในส่วน Refers to ให้เขียนดังต่อไปนี้
=Items[Item]
- คลิกที่ OK
- จากนั้น สร้างคอลัมน์ใหม่ 2 คอลัมน์ ที่เราต้องการเพิ่ม Data Validation
- หลังจากนั้น เลือกเซลล์ H5
- จากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เขียนดังต่อไปนี้ในส่วน Source
=Item
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ คุณจะได้ตัวเลือกแบบเลื่อนลงดังต่อไปนี้ ที่คุณสามารถเลือกได้ระหว่างไอศกรีมหรือน้ำผลไม้
- เลือกเซลล์ I5
- จากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow ส่วน
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เขียนดังต่อไปนี้ในส่วน Source
=INDIRECT(H5)
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ คุณจะได้ตัวเลือกแบบเลื่อนลงดังต่อไปนี้ ที่คุณสามารถเลือกรสชาติได้ ที่นี่ เราได้รสชาติสำหรับไอศกรีม
- ตอนนี้ หากเราเลือกน้ำผลไม้จากรายการรายการ รสชาติจะเปลี่ยนตามไปด้วย
2. การใช้ Named Range
วิธีที่สองของเรานั้นขึ้นอยู่กับการใช้ Named Range ในวิธีนี้ คุณสามารถใช้ชื่อกับช่วงในตาราง จากนั้น ใช้ชื่อตารางนี้ในกล่องโต้ตอบ Data Validation เราใช้ชุดข้อมูลที่รวม ชุดเสื้อผ้า สี และขนาด
เพื่อเข้าใจวิธีการ ทำตามขั้นตอนครับ
ขั้นตอน
- ก่อนอื่น สร้างตารางโดยใช้ชุดข้อมูล
- เพื่อทำเช่นนี้ ให้เลือกช่วงเซลล์ B4 ถึง D9
- จากนั้น ไปที่แท็บ Insert บนริบบอน
- เลือก Table จาก Tables กลุ่ม
- ผลลัพธ์ เราจะได้ผลลัพธ์ดังต่อไปนี้ ดูภาพหน้าจอ
- ต่อไป ไปที่แท็บ Formula บนริบบอน
- เลือก Define Name จากกลุ่ม Define Names
- จากนั้น กล่องโต้ตอบ New Name จะปรากฏ
- ตั้งชื่อ
- ในส่วน Refers to ให้เขียนดังต่อไปนี้
=Table1[Dress]
- จากนั้น คลิกที่ OK
- จากนั้น อีกครั้ง เลือก Define Name จากกลุ่ม Define Names
- จากนั้น กล่องโต้ตอบ New Name จะปรากฏ
- ตั้งชื่อ
- ในส่วน Refers to ให้เขียนดังต่อไปนี้
=Table1[Color]
- จากนั้น คลิกที่ OK
- ทำกระบวนการเดียวกันสำหรับขนาด (Size) ด้วย
- ตอนนี้ สร้างคอลัมน์ใหม่ 3 คอลัมน์
- จากนั้น เลือก F5
- หลังจากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เขียนดังต่อไปนี้ในส่วน Source
=Dress
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ เราจะได้ตัวเลือกแบบเลื่อนลงดังต่อไปนี้สำหรับชุดเสื้อผ้า
- จากนั้น เลือก G5
- หลังจากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow ส่วน
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เขียนดังต่อไปนี้ในส่วน Source
=Color
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ เราจะได้ตัวเลือกแบบเลื่อนลงดังต่อไปนี้สำหรับสี
- จากนั้น เลือก H5
- หลังจากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow ส่วน
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เขียนดังต่อไปนี้ในส่วน Source
=Size
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ เราจะได้ตัวเลือกแบบเลื่อนลงดังต่อไปนี้สำหรับขนาด
3. การใช้การอ้างอิงเซลล์ (Cell References) ใน Data Validation
วิธีที่สามของเรานั้นขึ้นอยู่กับการใช้อ้างอิงเซลล์โดยตรงใน Data Validation ในวิธีนี้ เราต้องการใช้อ้างอิงเซลล์ในกล่องโต้ตอบ Data Validation ผลลัพธ์คือ มันจะให้เราได้ตัวเลือกแบบเลื่อนลง ที่นี่ เราใช้ชุดข้อมูลที่รวม รัฐ และยอดขายของพวกเขา
เพื่อเข้าใจวิธีการ ทำตามขั้นตอนครับ
ขั้นตอน
- ก่อนอื่น สร้างเซลล์ใหม่ 2 เซลล์ รวม รัฐ และยอดขาย
- จากนั้น เลือกเซลล์ F4
- หลังจากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก List จาก Allow ส่วน
- หลังจากนั้น ติ๊กเลือก Ignore blank และ in-cell dropdown ตัวเลือก
- จากนั้น เลือกช่วงเซลล์ B5 ถึง B12
- สุดท้าย คลิกที่ OK
- ผลลัพธ์ คุณจะได้ตัวเลือกแบบเลื่อนลงที่คุณสามารถเลือกรัฐใดๆ ได้
- เราต้องการได้ยอดขายของรัฐที่สอดคล้องกัน
- เพื่อทำเช่นนี้ เลือกเซลล์ F5
- จากนั้น เขียนสูตรต่อไปนี้โดยใช้ ฟังก์ชัน VLOOKUP
=VLOOKUP(F4,$B$5:$C$12,2,0)
- คลิก Enter เพื่อใช้สูตร
- จากนั้น หากคุณเปลี่ยนรัฐจากตัวเลือกแบบเลื่อนลง ยอดขายจะเปลี่ยนโดยอัตโนมัติ ดูภาพหน้าจอ
4. จำกัดการป้อนค่าด้วย Data Validation
วิธีสุดท้ายของเรานั้นขึ้นอยู่กับวิธีจำกัดการป้อนค่าด้วย Data Validation ในวิธีนี้ เราต้องการใช้ Data Validation และใช้กฎบางอย่าง ซึ่งการป้อนข้อมูลจะถูกจำกัด หากคุณป้อนข้อมูลภายในช่วงที่กำหนด มันจะอนุญาตให้เราวางในเซลล์ มิฉะนั้น จะแสดงข้อผิดพลาด เราใช้ชุดข้อมูลที่รวม รหัสสั่งซื้อ รายการ วันที่สั่งซื้อ และจำนวน
ขั้นตอน
- ในวิธีนี้ เราต้องการจำกัดวันที่สั่งซื้อ ตั้งแต่วันที่ 1 มกราคม 2021 ถึง 5 พฤษภาคม 2022 นอกช่วงนี้จะแสดงข้อผิดพลาด
- เพื่อทำเช่นนี้ เลือกเซลล์ D10
- ไปที่แท็บ Data บนริบบอน
- หลังจากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก Date จากส่วน Allow
- หลังจากนั้น ติ๊กเลือก Ignore blank ตัวเลือก
- เลือกตัวเลือก between จากส่วน Date
- จากนั้น ตั้งค่าวันที่เริ่มต้นและสิ้นสุด
- สุดท้าย คลิกที่ OK
- ตอนนี้ หากเราวางวันที่ในเซลล์ D10 ซึ่งอยู่นอกช่วง มันจะแสดงข้อผิดพลาดให้เรา ดูภาพหน้าจอ
วิธีทำ Data Validation ตามเซลล์ที่อยู่ติดกัน (Adjacent Cell) ใน Excel
เราสามารถทำ Data Validation ตามเซลล์ที่อยู่ติดกันได้ ตัวอย่างเช่น คุณกำหนดข้อความบางอย่างในเซลล์ที่อยู่ติดกัน ตอนนี้ หากคุณวางมันใน Data Validation และกำหนดว่าไม่มีทางเขียนในคอลัมน์ถัดไปจนกว่าเงื่อนไขจะเป็นจริง คุณสามารถทำได้ง่ายในเซลล์ที่อยู่ติดกัน เราใช้ชุดข้อมูลที่รวม สอบหลายครั้ง ความคิดเห็น และเหตุผล เราต้องการเขียนบางอย่างในคอลัมน์เหตุผล หากความคิดเห็นของการสอบเป็น Hard (ยาก)
เพื่อเข้าใจกระบวนการ ทำตามขั้นตอนครับ
ขั้นตอน
- ก่อนอื่น เลือกช่วงเซลล์ D5 ถึง D9
- หลังจากนั้น ไปที่แท็บ Data บนริบบอน
- จากนั้น เลือกตัวเลือก Data validation แบบเลื่อนลงจากกลุ่ม Data Tools
- ผลลัพธ์ กล่องโต้ตอบ Data Validation จะปรากฏ
- ก่อนอื่น เลือกแท็บ Settings บนด้านบน
- จากนั้น เลือก Custom จากส่วน Allow
- หลังจากนั้น เขียนสูตรต่อไปนี้ในส่วน Formula
=$C5="Hard"
- สุดท้าย คลิกที่ OK
- จากนั้น คุณสามารถเพิ่มคำอธิบายในคอลัมน์เหตุผล เมื่อค่าของเซลล์ที่อยู่ติดกันเป็น Hard
- แต่ หากเราลองเพิ่มคำอธิบายเมื่อค่าของเซลล์ที่อยู่ติดกันต่างออกไป มันจะแสดงข้อผิดพลาดให้เรา
สรุป
ในบทความนี้ เราได้เห็นวิธีสร้างรายการโดยใช้ Data Validation ของ Excel เราสร้างรายการที่ขึ้นกันได้ (Dependent List) โดย Data Validation ของ Excel ตามอีกเซลล์หนึ่ง ที่เราใช้ฟังก์ชัน INDIRECT เราเห็นว่าการป้อนข้อมูลสามารถถูกจำกัดได้โดยใช้ Data Validation ตามอีกเซลล์หนึ่ง บทความนี้อาจมีประโยชน์สำหรับการดำเนินการทางสถิติหลายอย่าง หวังว่าคุณจะชอบบทความนี้ อยู่เย็นๆ และแสดงความคิดเห็นด้านล่างหากคุณเผชิญความยากลำบากใดๆ เกี่ยวกับบทความนี้ อย่าลืมเยี่ยมชมหน้า Exceldemy ของเรา
บทความที่เกี่ยวข้อง
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
