เชี่ยวชาญ Excel Data Validation: 6 วิธีที่พิสูจน์แล้ว ใช้รายการจากแผ่นงานอื่น

เราสามารถจำกัดการป้อนข้อมูลได้โดยการใช้ Data Validation (การตรวจสอบความถูกต้องของข้อมูล) ในบทความนี้ เราจะแสดง 6 ตัวอย่างวิธีใช้ Data Validation list (รายการตรวจสอบข้อมูล) จากแผ่นงานอื่น (another sheet) ใน Excel เราสามารถเห็นภาพถ่ายของชุดข้อมูลของเรา ปัจจุบัน เรามีคอลัมน์ Product (สินค้า) และ Category (หมวดหมู่) ในชุดข้อมูล ชุดข้อมูลนี้จะเปลี่ยนแปลงไปตลอดเวลาในคำแนะนำทีละขั้นตอนของเรา

6 วิธีใช้ Data Validation List จากแผ่นงานอื่น

1. ใช้ Data Validation List จากแผ่นงานอื่นเพื่อสร้าง Drop Down List

เรามี 2 คอลัมน์ ในชุดข้อมูลนี้ Product (สินค้า) ที่ลูกค้าซื้อจะถูกป้อนโดยใช้ Data Validation list จาก แผ่นงานอื่น (sheet) มาเริ่มขั้นตอนกันเลย

ขั้นตอน:

• ขั้นแรก เลือกช่วง เซลล์ (cell) B5:B11 จากแผ่นงาน "dropdown"
• จากนั้นจากแท็บ Data (ข้อมูล) >>> Data Validation (การตรวจสอบความถูกต้องของข้อมูล)

หลังจากนั้น กล่องโต้ตอบ Data Validation dialog box จะปรากฏ

• จากนั้น เลือก List (รายการ) จากเมนูแบบเลื่อนลง Allow (อนุญาต)

หมายเหตุ: Ignore blank (ละเว้นเซลล์ว่าง) และ In-cell dropdown (รายการแบบเลื่อนลงในเซลล์) ควรถูกติ๊กถูกโดยค่าเริ่มต้น หากไม่ใช่ ให้ติ๊กถูกที่นั่น

• หลังจากนั้น คลิกที่กล่อง Source (แหล่งที่มา)
• จากนั้น เลือกแผ่นงาน "source"

• หลังจากนั้น เลือกช่วง เซลล์ (cell) B5:B11 นี่คือ validation list (รายการตรวจสอบ) ของเรา
• สุดท้าย กด OK

เราสามารถเห็นไอคอนลูกศรได้

• คลิกที่ไอคอนลูกศรนั้น

เราสามารถเห็น Data Validation list ของเราจาก แผ่นงานอื่น

• หลังจากนั้น เลือกอะไรก็ได้จาก รายการ (list)

ค่าจะถูกป้อนเข้าไปในชุดข้อมูลของเรา

ที่นี่ เราได้เติมชุดข้อมูลของเราโดยใช้ Data Validation list จาก แผ่นงานอื่น ใน Excel

อ่านเพิ่มเติม: วิธีสร้าง Data Validation List จากตารางใน Excel (3 วิธี)

2. ใช้ Data Validation List จากแผ่นงานอื่นเพื่อกำหนดช่วงวันที่ (Date Range)

ตอนนี้ชุดข้อมูลของเรามีคอลัมน์ stock date (วันที่สต็อก) ว่างสำหรับ products (สินค้า) เราจะเติมข้อมูลนี้โดยใช้ Data Validation list ครั้งนี้ เราจะใช้ตัวเลือก Date between (วันที่ระหว่าง)

ขั้นตอน:

• ขั้นแรก เราจะเลือกช่วง เซลล์ (cell) C5:C11
• ที่สอง เปิดกล่องโต้ตอบ Data Validation dialog box
• ที่สาม เลือกการตั้งค่านี้:
  • Allow (อนุญาต): Date (วันที่)
  • Data (ข้อมูล): between (ระหว่าง)

• จากนั้น เลือก การอ้างอิงเซลล์ (cell reference) จากแผ่นงาน "source"
  • Start date (วันที่เริ่มต้น): เซลล์ B14 จากแผ่นงาน "source"
  • End date (วันที่สิ้นสุด): เซลล์ B15 จากแผ่นงาน "source"

บางครั้ง เราต้องใช้ การอ้างอิงเซลล์แบบสัมบูรณ์ (absolute cell reference) ในข้อมูลของเรา นี่จะทำให้ ข้อมูล ของเราไม่เปลี่ยนแปลงเมื่อย้ายไปยัง เซลล์อื่น

• สุดท้าย กด OK

ตอนนี้ หากเราป้อนอะไรนอกเหนือจากเกณฑ์ที่เรากำหนด เราจะได้ ข้อความข้อผิดพลาด (error message) ข้อความนี้สามารถปรับเปลี่ยนได้

เราสามารถเปลี่ยนข้อความโดยไปที่แท็บ Error Alert (การแจ้งเตือนข้อผิดพลาด) จาก Data Validation dialog box

• ขั้นแรก พิมพ์บางอย่างใน Title (หัวข้อ): นี่เป็นงานที่เลือกได้
• ที่สอง ปรับแต่งข้อความโดยพิมพ์ใน Error message (ข้อความข้อผิดพลาด):
• สุดท้าย กด OK

เราสามารถเห็นข้อความข้อผิดพลาดที่กำหนดเองของเราทำงานได้อย่างสมบูรณ์

เราสามารถพิมพ์วันที่ด้วยตนเองในช่องที่เหลือได้ ข้อความเตือน (warning message) จะแสดงหากเราพิมพ์บางอย่างนอกเหนือจากช่วงวันที่ นั่นคือวิธีที่เราสามารถใช้ Data Validation list เพื่อจำกัด เซลล์ (cells) ของเรา

อ่านเพิ่มเติม: วิธีใช้ Named Range สำหรับ Data Validation List กับ VBA ใน Excel

3. กำหนดขีดจำกัดเวลา (Time Limit) โดยใช้ Data Validation List จากแผ่นงานอื่น

ในตัวอย่างนี้ เราจะใช้ Data Validation list เพื่อจำกัดการป้อนเวลาใน เซลล์ (cells) นอกจากนี้ รายการ (list) นั้นอยู่บน แผ่นงานอื่น (sheet) เป้าหมายของเราคือการป้อนเวลาภายในเกณฑ์ของเราในคอลัมน์ Stock Time (เวลาสต็อก)

ขั้นตอน:

• ขั้นแรก เลือกช่วง เซลล์ (cell) D5:D11
• ที่สอง เปิดกล่องโต้ตอบ Data Validation dialog box
• ที่สาม เลือกตัวเลือกเหล่านี้:
  • Allow (อนุญาต): Time (เวลา)
  • Data (ข้อมูล): between (ระหว่าง)
  • Start time (เวลาเริ่มต้น): เซลล์ F14 จากแผ่นงาน "source"
  • End time (เวลาสิ้นสุด): เซลล์ F15 จากแผ่นงาน "source"
• สุดท้าย กด OK

ตอนนี้ เราสามารถเติมคอลัมน์ Stock Time ด้วยเวลาระหว่าง 8 AM และ 5 PM

เนื้อหาเกี่ยวข้อง: สร้าง Data Validation Drop-Down List พร้อมการเลือกหลายรายการใน Excel

บทความคล้ายกัน:

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• Excel Data Validation Based on Another Cell Value
• Excel Data Validation Alphanumeric Only (Using Custom Formula)
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)

4. ได้ตัวเลขที่มากกว่า (Greater Number) โดยใช้ Data Validation List จากแผ่นงานอื่น

ในตัวอย่างนี้ เราได้เพิ่ม คอลัมน์ (column) อีกหนึ่งคอลัมน์ให้กับชุดข้อมูลของเรา เราจะใช้เกณฑ์ greater than (มากกว่า) สำหรับ whole number (จำนวนเต็ม) เพื่อใช้ Data Validation list

ขั้นตอน:

• ขั้นแรก เลือกช่วง เซลล์ (cell) E5:E11
• ที่สอง จากแท็บ Data เปิด Data Validation dialog box

คุณอาจได้ ข้อความเตือน (warning message) คล้ายกัน คลิก NO

• จากนั้น จาก กล่องโต้ตอบ (dialog box) เลือกเหล่านี้:
  • Allow (อนุญาต): Whole number (จำนวนเต็ม)
  • Data (ข้อมูล): greater than (มากกว่า)
  • Minimum (ขั้นต่ำ): เซลล์ F7 จากแผ่นงาน "source"
• สุดท้าย กด OK

หลังจากนั้น เราสามารถใส่ค่าที่ มากกว่า (greater than) 0 ในคอลัมน์ Amount Sold (จำนวนที่ขายได้) โดยใช้ Data Validation list นี้ เราสามารถหลีกเลี่ยงการพิมพ์ค่าผิดได้

เนื้อหาเกี่ยวข้อง: วิธีลบเซลล์ว่างจาก Data Validation List ใน Excel (5 วิธี)

5. ใช้ Data Validation List จากแผ่นงานอื่นเพื่อจำกัดความยาวข้อความ (Text Length)

เราได้ชุดข้อมูลใหม่สำหรับตัวอย่างนี้ เราจะป้อน Salesperson (พนักงานขาย) ที่เกี่ยวข้องกับการขาย Product (สินค้า) เราจะป้อนชื่อสั้นๆ เท่านั้น ดังนั้น เราต้องจำกัดจำนวนตัวอักษรที่สามารถวางใน เซลล์ (cells) นั้นๆ

ขั้นตอน:

• ขั้นแรก เลือกช่วง เซลล์ (cell) C5:C11
• ที่สอง เปิดกล่องโต้ตอบ Data Validation dialog box จากแท็บ Data
• ที่สาม เลือกตัวเลือกเหล่านี้:
  • Allow: Text length (ความยาวข้อความ)
  • Data: between (ระหว่าง)
  • Minimum: เซลล์ B40 จากแผ่นงาน "source"
  • Maximum: เซลล์ B41 จากแผ่นงาน "source"
• สุดท้าย กด OK

ดังนั้น เราสามารถใช้ Data Validation list ของเราและเติมช่อง เซลล์ ที่เหลือ

อ่านเพิ่มเติม: วิธีใช้ Multiple Data Validation ในเซลล์เดียวใน Excel (3 ตัวอย่าง)

6. ใช้ Data Validation List จากแผ่นงานอื่นสำหรับสร้าง Dependent Drop Down List (รายการแบบเลื่อนลงที่ขึ้นกันได้)

ในตัวนี้ เราจะสร้าง Drop Down menu (เมนูแบบเลื่อนลง) ที่ขึ้นกันได้ (dependent) โดยใช้ Data Validation list เราจะใช้ฟังก์ชัน INDIRECT และ Named cell range (ช่วงเซลล์ที่ตั้งชื่อ) ที่นี่

ขั้นตอน:

ในตอนแรก เราจะสร้าง Named Range (ช่วงที่ตั้งชื่อ)

• ขั้นแรก เลือกช่วง เซลล์ (cell) B29:C33
• ที่สอง จากแท็บ Formulas (สูตร) >>> Create from Selection (สร้างจากการเลือก)

• กล่องโต้ตอบ dialog box จะปรากฏ
• จากนั้น เลือก Top row (แถวบนสุด)
• สุดท้าย กด OK

ที่มุมขวาบน มี Name Box (กล่องชื่อ) เราสามารถคลิกที่นั่น เพื่อดู Named Range ของเรา

เมื่อคลิก เราสามารถเห็น Named Ranges สอง (two) ตัวของเรา: Beverages (เครื่องดื่ม) และ Snacks (ของว่าง)

• ตอนนี้ เลือก เซลล์ B4
• จากนั้น เปิด Data Validation dialog box จาก Data
• หลังจากนั้น เลือกการตั้งค่านี้:
  • Allow: List (รายการ)
  • Source (แหล่งที่มา): เลือกช่วง เซลล์ (cell) B29:C29
• สุดท้าย กด OK

ตอนนี้ เราสามารถเห็นได้ว่า เซลล์ B5 แสดง สอง (two) หมวดหมู่ ตอนนี้ ขั้นตอนต่อไปคือการสร้างรายการที่ขึ้นกันได้ (dependent list) บน เซลล์ C5

• ขั้นแรก เลือกช่วง เซลล์ (cell) C5:C11
• ที่สอง จากแท็บ Data เปิด Data Validation dialog box
• ที่สาม ตั้งค่าเหล่านี้:
  • Allow: List (รายการ)
  • Source (แหล่งที่มา): พิมพ์สูตรต่อไปไปที่นั่น

=INDIRECT(B5)

• สุดท้าย กด OK

เราสามารถเห็นได้ว่า ใน เซลล์ C5 สินค้าจากหมวดหมู่ Snacks กำลังแสดง

เพิ่มเติม หากเราเปลี่ยน เซลล์ B5 เป็น Beverages แล้ว เซลล์ C5 ก็จะเปลี่ยนไปด้วย

สรุป เราสามารถทำเช่นนี้สำหรับ เซลล์ ที่เหลือและเติมพวกมันด้วยค่าที่ขึ้นกันได้ (dependent values)

อ่านเพิ่มเติม: วิธีใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)

ส่วนฝึกปฏิบัติ (Practice Section)

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

สรุป

บทความของเราได้แสดง 6 ตัวอย่างวิธีใช้ Data Validation list จาก แผ่นงานอื่น (another sheet) หากคุณเผชิญปัญหาใดๆ โปรดแสดงความคิดเห็นด้านล่างเพื่อขอการอธิบายเพิ่มเติม ขอบคุณที่อ่าน ทำได้ดีๆ ต่อไป!

บทความที่เกี่ยวข้อง

• วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)
• Excel VBA สร้าง Data Validation List จาก Array
• วิธีใช้ Custom VLOOKUP Formula ใน Excel Data Validation
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)