Excel Tips : การสร้างรายการข้อมูลด้วย Data Validation

Excel Tips ในบทความนี้ขอนำเสนอการสร้างรายการข้อมูล (List Data) ใน Excel ด้วยการใช้ Data validation ครับ ต่อเนื่องจาก Excel tips ในเรื่องการป้องกันการซ้ากันของรายการข้อมูล มาดูแนวทางการนำไปใช้ เช่น สร้างเป็นรายการข้อมูลให้ผู้ใช้เลือก หรือรายการ คีย์หลัก สำหรับค้นหาในฐานข้อมูลเป็นต้น ดังนั้นสมบัติของรายการข้อมูลที่ต้องมีคือข้อมูลในรายการต้องไม่ซ้ำกัน และจะต้องอัพเดตรายการข้อมูลอยู่เสมอหากมีการเพิ่มหรือลดจำนวนรายการข้อมูล จากข้อกำหนดดังกล่าว เราจะใช้ความรู้จากเรื่อง การป้องกันการซ้ากันของรายการข้อมูลและการสร้างรายการข้อมูลแบบอัตโนมัติเข้าช่วยครับ มาดูขั้นตอนการสร้างรายการข้อมูลกันเลยครับ

สมมุติให้ ข้อมูลหลักหรือ คีย์หลัก ถูกบันทึกลงใน คอลัมภ์ B โดยเริ่มตั้งแต่ B2 เป็นต้นไป และสมมุติให้บันทึกได้ถึง B100 

เราจะเริ่มเขียนสูตร excel ใน Data validation เพื่อป้องกันการซ้ำกันของข้อมูล รายละเอียดสามารถดูได้ในบทความ Excel tips เรื่อง การป้องกันการซ้ากันของรายการข้อมูล โดยสูตร excel สำหรับ B2 คือ

= COUNTIF($B$2:$B$100,B2)=1

สำหรับ B3 ,..., B100 ก็สามารถเขียนได้ในทำนองเดียวกัน

กำหนดให้ D2 เป็น CELL แสดงรายการข้อมูล เราสามารถใช้  Data validation และเลือกการตั้งค่าเป็น รายการ หรือ List และเขียนสูตร excel ได้เป็น 

=OFFSET($B$2,0,0,COUNTA($B$2:$B$100),1)

ภาพที่ 1 การตั้งค่าใน Data validation โดยใช้สูตร excel

ผลการสร้างรายการข้อมูลใน excel แสดงได้ดังภาพที่ 2

ภาพที่ 2 ผลการสร้างรายการข้อมูลใน excel ด้วย Data validation

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

Excel Tips : การป้องกันรายการข้อมูลซ้ำกัน (Excel Data validation)

 Excel Tips ในบทความนี้ขอนำเสนอการป้องกันรายการข้อมูลซ้ำกัน ซึ่งเป็นประโยชน์มากกับท่านผู้อ่านที่ต้องการบันทึกข้อมูลลงใน excel เพื่อนำไปใช้ในการทำรายการ  List แบบอัตโนมัติ หรือเป็นตารางข้อมูลในสูตร excel :  vlookup หรือสูตร excel : Index และ สูตร excel : MATCH ซึ่งโดยส่วนใหญ่ข้อมูลที่เราไม่ต้องการให้ซ้ำกันจะเรียกว่าคีย์หลัก (Primary key) การป้องกันการซ้ำกันเราจะอาศัยวิธีการ Data Validation (การตรวจสอบความถูกต้องของข้อมูล) ครับ โดยจะกำหนดสูตร excel เพื่อตรวจสอบข้อมูลก่อนจะบันทึกลงใน CELL มาดูวิธีการกันเลยครับ สมมุติเราต้องการให้รายการข้อมูลตั้งแต่ CELL B2 จนถึง B10 ต้องไม่มีข้อมูลซ้ำกัน ทำได้ดังนี้

1   คลิกเลือก B2

2 ไปที่เมนูข้อมูลเลือก เครื่องมือ Data Validation... จะปรากฏหน้าต่าง การตรวจสอบความถูกต้องของข้อมูล ให้คลิกเลือกแท็บ การตั้งค่า 

3 ใน List อนุญาตให้เลือก กำหนดเอง (Custom)

4 พิมพ์สูตร excel   = COUNTIF($B$2:$B10,B2) = 1 ดังแสดงในภาพที่ 1 เพื่อตรวจสอบความถูกต้องของข้อมูล

5 เลือก B2 และทำการลากคลุมถึง B10 เพื่อ copy สูตร excel ใน B2 

6 ทดลองพิมพ์ข้อมูลใน B2 เป็น A001

7 ทดลองพิมพ์ข้อมูลใน B3 เป็น A001 ท่านจะพบหน้าต่างแสดงความผิดพลาดดังแสดงในภาพที่ 2 ซึ่งหมายถึงว่าตอนนี้ข้อมูลในเซลล์ B2 จนถึง B10 ถูกตรวจสอบการซ้ำกันของข้อมูลเรียบร้อยแล้ว

ภาพที่ 1 หน้าต่างสำหรับกำหนดสูตร excel เพื่อตรวจสอบความถูกต้องของข้อมูล

ภาพที่ 2 หน้าต่างแสดงความผิดพลาดจากการบันทึกข้อมูลที่ซ้ำกันลงใน B3

จาก Excel Tips นี้เราก็จะนำไปเป็นขั้นตอนการตรวจสอบการซ้ำกันของข้อมูลก่อนนำไปใช้งานอื่นๆซึ่งจะนำเสนอต่อไป สวัสดีครับ