Excel Tips : การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด

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

 

ก่อนอื่นตรวจสอบวันเดือนปีที่กรอกใน CELL ต้องไม่ใช่วันเสาร์อาทิตย์ โดยเราจะใช้สูตร Excel : WEEKDAY ซึ่งสูตร Excel จะคืนลำดับของวันใน 1 สัปดาห์กลับมา โดย เรียงลำดับดังนี้

 1 Sunday  2 Monday  ......  7 Saturday ครับ 

จากค่าที่ส่งคืนกลับ เราจะตรวจสอบ เงื่อนไขว่า ค่าวันที่คืนกลับมาต้องไม่เป็ย 1 และ 7 ซึ่งเป็นวันอาทิตย์ และ เสาร์ โดยเงื่อนไขการตรวจสอบต้องไม่ใช่ทั้ง 2 เงื่อนไข นั่นคือ ทั้งวันอาทิตย์และวันเสาร์ ดังนั้นเราจะเขียนเงื่อนไขตรวจสอบทั้ง 2 ได้โดยใช้สูตร AND  มาดูสูตร Excel กันเลยครับ

สมมุติ ต้องการตรวจสอบวันเดือนปีที่ Cell B4 เราจะเขียนสูตร Excel ได้ดังนี้

AND(WEEKDAY(B4) <> 1 , WEEKDAY(B4) <> 7)

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

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

มาถึงจุดนี้ให้ท่านผู้อ่านลองแก้ไข วันเดือน ปี ใน Cell B4 และดูผลลัพธ์ที่เกิดขึ้นดูครับ 

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

เอาหล่ะครับ มาถึงตอนนี้ เราจะ Copy สูตร Excel ใน D4 ไปใช้ใน Data Validation ซึ่งได้แสดงวิธีการกำหนดใน บทความ การใช้ Data Validation เพื่อป้องกันความผิดพลาดไปแล้ว  ให้ท่านผู้อ่านเลือก Cell B4:B10 ก่อนเรียกหน้าต่าง การตรวจสอบความถูกต้องของข้อมูล ดังแสดงในภาพที่ 2 

ภาพที่ 2 การกำหนดสูตร Excel ใน Data Validation

ผลลัพธ์ของการตรวจสอบวันเดือนปีที่บันทึกไม่ถูกต้องแสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลลัพธ์กรณีวันเดือนปีที่บันทึกเป็นวัน เสาร์ อาทิตย์

เอาหล่ะครับ มาถึงตรงนี้ท่านผู้อ่านคงได้เห็นประโยชน์ของการใช้ Data Validation ในอีก Application หนึ่ง ซึ่งที่ผมได้ยกตัวอย่างมาเป็นเพียงการประยุกต์ใช้เล็กๆน้อยๆ ซึ่ง Excel ยังมีความสามารถมากกว่านี้อีกมาก เชื่อว่า มาถึงตรงนี้ ผู้ใช้ Excel อยู่คงมีความมั่นใจในความถูกต้องของ Data ที่บันทึกลงไฟล์ Excel มากขึ้นนะครับ  

Excel Tips : การใช้ Data Validation เพื่อป้องกันการกรอกข้อมูลผิดรูปแบบ-1

สวัสดีครับท่านผู้อ่าน เรื่องของ Data Validation ผมได้นำเสนอมาหลากหลายทั้ง การป้องกันการบันทึกรายการข้อมูลซ้ำกันเหมาะสำหรับพวกการเก็บ Invoice หรือข้อมูลที่ต้องไม่ซ้ำกัน หรือวิธีการสร้างรายการข้อมูล(List) และการสร้างรายการที่มีรายการย่อย เป็นต้น ในบทนี้จะขอนำเสนอการใช้ Data Validation อีกตัวอย่างหนึ่งใน Excel นั่นคือ การป้องกันการกรอกข้อมูลผิดรูปแบบ ตัวอย่างคือ Code ต่างๆที่มีรูปแบบแน่นอน  สมมุติว่า เป็น Code วัตถุดิบที่ต้องนำเข้า โดยรูปแบบของ Code จะต้องขึ้นต้นด้วยตัวอักษร R และต่อท้ายด้วยตัวเลข  4 หลัก เช่น R0125 เป็นต้น ดังนั้น เราต้องใช้ Data Validation ใน Excel ช่วยป้องกันการกรอก Code ผิดรูปแบบ มาดูแนวคิดกันครับ การทำงานของ Excel ต้องตรวจสอบเงื่อนไข ดังนี้ครับ

อักขระตัวแรกต้องเป็นตัว R และ ต้องมีความยาวของโค้ดรวม 5 ตัว และ 4 ตัวหลังต้องเป็นตัวเลข

สมมุติว่า Code วัตถุดิบถูกกรอกที่ Cell B2 

ให้ท่านทดลองเขียนสูตร Excel ในCell D3 ดังนี้ครับ AND(LEFT(B2) = "R",LEN(B2) = 5 , ISNUMBER(VALUE(RIGHT(B2,4)))

ขออธิบายสูตร Excel นี้นะครับ

ในสูตร AND จะมีเงื่อนไขให้ตรวจสอบ 3 เงื่อนไข โดยคืนค่า TRUE หรือ 1 ก็ต่อเมื่อ ทั้งสามเงื่อนไขเป็นจริง นอกนั้นจะคืนค่า FALSE หรือ 0 ครับ

เงื่อนไขแรก จะตรวจสอบอักขระตัวแรกว่าเป็น R หรือไม่ โดยใช้สูตร LEFT(B2) = "R" เป็นการบังคับให้ผู้กรอกต้องพิมพ์ R เท่านั้น

เงื่อนไขต่อมา จะตรวจสอบว่าอักขระที่กรอกลงไปมีทั้งหมด 5 ตัวหรือไม่ โดยใช้สูตร LEN(B2) = 5 เป็นการบังคับให้ต้องกรอกอักขระ 5 ตัวเท่านั้น

เงื่อนไขสุดท้าย จะเริ่มจากตัดอักขระ 4 ตัวจากด้านขวา โดยใช้สูตร RIGHT(B2,4) จากนั้นจะแปลงอักขระเป็นตัวเลขโดยใช้ สูตร VALUE สุดท้ายจะตรวจสอบว่าอักขระที่แปลงเป็นตัวใช้หรือไม่โดยใช้สูตร ISNUMBER 

ผลแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการตรวจสอบเงื่อนไข

เอาหล่ะครับ ถึงตอนนี้ท่านผู้อ่านทดลองกรอก Code ในช่อง B2 และสังเกตุผลลัพธ์ใน Cell D2 ดูนะครับ จากนั้นเราจะ Copy สูตร Excel ใน Cell D2 ไปใช้ใน Data Validation มาต่อกันเลยครับ 

1. คลิกเมาส์เลือก Cell B2:B10 เลือก เมนู Data และเลือกทูลบาร์ Data Validation จะปรากฎไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

2. คลิก Tab การตั้งค่า

3. ในหัวข้อ อนุญาตให้ เลือก กำหนดเอง

4. ในหัวข้อ Formula ให้วางสูตร Excel ที่ Copy จาก D2 ลงไป ดังแสดงในภาพที่ 2

ภาพที่ 2 การกำหนดเงื่อนไขใน Formula

5. คลิก Tab ข้อความที่ใส่ เพื่อกำหนดการแสดงข้อความเมื่อ Cell B2:B10 ถูกเลือก เป็นการช่วยลดความผิดพลาดลงไปได้อีก ดังแสดงในภาพที่ 3 

ภาพที่ 3 การกำหนดข้อความแจ้งเตือนขณะกรอก Code

6. คลิก Tab การแจ้งเตือนข้อผิดพลาด เพื่อกำหนดข้อความแจ้งเตือนเมื่อมีการกรอก Code ผิดรูปแบบที่กำหนด ดังแสดงในภาพที่ 4

7. คลิกปุ่ม ตกลง เพื่อปิดไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

ภาพที่ 4 การกำหนดการแจ้งเตือนข้อผิดพลาดที่เกิดขึ้น

ภาพที่ 5 ผลลัพธ์จากการกรอก Code วัตถุดิบผิดรูปแบบ

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

บทความที่ใกล้เคียงกัน

การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด

มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน ตอน 4 (How to use MS Excel to operate with matrix-4)

บทความการใช้ Excel หัวข้อนี้มิได้ต้องการส่งเสริมให้ นักเรียนนักศึกษาละเลยพื้นฐานและวิธีการดำเนินการของเมตริกซ์ เพียงแต่ต้องการนำเสนอวิธีการใช้ Excel ช่วยคำนวณผลการดำเนินการกับเมตริกซ์ อย่างน้อยก็ช่วยให้ นักเรียน นักศึกษาที่กำลังเรียนคณิตศาสตร์ในเรื่อง เมตริกซ์ ได้ใช้เป็นเครื่องมือตรวจสอบผลลัพธ์ที่ทำด้วยลายมือ ซึ่งมีวิธีการคิดอย่างเป็นขั้นเป็นตอน โดยผู้เขียนจะค่อยๆทยอยนำวิธีการใช้ Excel มาดำเนินการกับเมตริกซ์ในแต่ละหัวข้อครับ บทความนี้จะนำเสนอการแก้ระบบสมการเชิงเส้นด้วยระเบียบวิธีการทำเมตริกซ์ผกผัน (Inverse matrix)

จากบทความเรื่องการ การหาเมตริกซ์ผกผันด้วยสูตร Excel เราได้ทราบวิธีการใช้ Excel หาเมตริกซ์ผกผันแล้ว กลับมาพิจารณาระบบสมการเชิงเส้น (Linear Equation System) โดยระบบสมการเชิงเส้นจำนวน n สมการสามารถเขียนอยู่ในรูปแบบของเมตริกซ์ได้ คือ

[A]{X} = {B}            (a)

เมื่อ เมตริกซ์ A เป็นเมตริกซ์จัตุรัสขนาด nxn ที่ประกอบไปด้วยสมาชิกที่ทราบค่าและเป็นค่าคงที่ เวกเตอร์ X ขนาด nx1 เป็นเมตริกซ์ แถวตั้งที่ประกอบด้วยตัวไม่ทราบค่า n ค่า และ เวกเตอร์ B ขนาด nx1 เป็นเมตริกซ์ แถวตั้งที่ประกอบด้วยตัวไม่ทราบค่า n ค่า การแก้ระบบสมการเชิงเส้นมีหลายวิธี แต่ในบทความนี้จะนำเสนอระเบียบวิธีการทำเมตริกซ์ผกผัน มาพิจารณาระบบสมการตามสมการที่ a

[A]{X} = {B}

หากเมตริกซ์ A ไม่เป็น Singular Matrix เราสามารถนำเมตริกซ์ผกผันของเมตริกซ์ A คูณเข้าด้านซ้ายและขวาของสมการที่ a ได้ดังนี้

[A]^-1[A]{X} = [A]^-1{B}

[I]{X} = [A]^-1{B}

{X} = [A]^-1{B}

ดังนั้นคำตอบของระบบสมการเชิงเส้นจะเท่ากับผลคูณของเมตริกซ์ผกผันของเมตริกซ์ A กับเวกเตอร์ B นั่นเอง โดยในการใช้ Excel จะมีขั้นตอนดำเนินการดังนี้

1.     คำนวณหา เมตริกซ์ผกผันของเมตริกซ์ A โดยใช้สูตร Excel : MINVERSE

2.     หากเมตริกซ์ผกผันของเมตริกซ์ A สามารถหาค่าได้ ให้นำมาคูณกับเวกเตอร์ B โดยใช้สูตร Excel : MMULT

มาดูตัวอย่างกันครับ สมมุติมีระบบสมการ 3 ตัวแปร ดังนี้

4X -4Y      = 400          (1)

-X+4Y-2Z = 400          (2)

-2Y+4Z     = 400          (3)

จากระบบสมการดังกล่าว สามารถเขียนในรูประบบสมการ [A]{X} = {B} ได้ดังภาพที่ 1 เรามาเริ่มตามขั้นตอนกันเลยครับ

1. หาเมตริกซ์ผกผันของ [A] โดยเลือก F9:H11

2. พิมพ์สูตร Excel ดังนี้ =MINVERSE(B4:D6)

3. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

4. จะได้ เมตริกซ์ผกผัน ในเซลล์ F9:H11 ดังแสดงในภาพที่ 1

5. หาคำตอบระบบสมการ โดยเลือก F14:F16

6. พิมพ์สูตร Excel ดังนี้ =MMULT(F9:H11,F4:F6)

7. กดปุ่ม Ctrl + Shift + Enter เพื่อเป็นการคำนวณแบบ Array

8. จะได้ เวกเตอร์ X  ในเซลล์ F14:F16 ซึ่งเป็นคำตอบของระบบสมการ ดังแสดงในภาพที่ 1

ภาพที่ 1 การใช้ excel ช่วยแก้ระบบสมการเชิงเส้น

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

หัวข้อที่เกี่ยวข้อง

การใช้ Excel หา Determinant ของเมตริกซ์

การใช้ Excel คูณระหว่างเมตริกซ์

         การใช้ Excel คำนวณหาเมตริกซ์ผกผัน

Excel Tips : การสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้ใน สูตร excel VLOOKUP

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

1.รายการข้อมูลในคีย์หลักจะต้องไม่ซ้ำกัน ซึ่งเราสามารถกำหนดได้ รายละเอียดดูได้ใน excel การป้องกันรายการข้อมูลซ้ำกัน

2. สร้างตารางข้อมูลแบบไดนามิกส์โดยใช้สูตร excel : OFFSET ผ่านวิธีการกำหนดข้อมูลแบบพลวัตร

มาดูตัวอย่างกันเลยครับ

สมมุติรูปแบบของ table_array เป็นดังภาพที่ 1

ภาพที่ 1 รูปแบบของ table_array ใน excel

จากรูปแบบของ table_array จะกล่าวได้ว่า table_array มี 5 ฟิลด์ และกำหนดให้จำนวน record สูงสุดเท่ากับ 1,000 แถว

ดังนั้นเราสามารถกำหนด Data validation แบบ Custom เพื่อป้องกันรายการข้อมูลซ้ำกันใน B4 ได้ดังนี้
สูตร excel : COUNTIF($B$4:$B$1004,B4)=1
Copy สูตรดังกล่าวและวางลงใน B5 – B1003

สร้าง table_array แบบไดนามิกส์ โดยไปที่เมนู สูตร เลือก กำหนดชื่อ จะปรากฏหน้าต่าง ชื่อใหม่ ให้กำหนดชื่อข้อมูลเป็น tb_cutomer ขอบเขตเป็น สมุดงาน และกำหนดสูตร excel ใน หัวข้ออ้างอิงไปยัง โดยใช้ สูตร excel ดังนี้
OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B$4:Sheet1!$B$1003),5)

 

ภาพที่ 2 การกำหนดข้อมูลแบบไดนามิกส์ใน excel

ทดลองสร้างสูตร excel: VLOOKUP เพื่อแสดงผลการค้นหาข้อมูลตามรหัสพนักงาน โดยกำหนดให้ผลการค้นหาแสดงในแถวที่ 2 โดยกำหนดให้ผู้ใช้ พิมพ์รหัสลูกค้าใน B2 และแสดงรายชื่อ ที่อยู่ เบอร์โทรศัพท์ และ ผู้ติดต่อ ใน C2, D2, E2 และ F2 ตามลำดับ โดยใช้สูตร excel ดังนี้


C2 : IFERROR(VLOOKUP(B2,tb_customer,2,False),”Data does not found”)

D2 : IFERROR(VLOOKUP(B2,tb_customer,3,False),”Data does not found”)

E2 : IFERROR(VLOOKUP(B2,tb_customer,4,False),”Data does not found”)

F2 : IFERROR(VLOOKUP(B2,tb_customer,5,False),”Data does not found”)

ทดลองพิมพ์รหัสลูกค้า A003 ใน B2 Excel จะแจ้งผลการค้นหาว่า Data does not found ดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการค้นหาในกรณีที่ไม่พบข้อมูล

ทดลองเพิ่มข้อมูล A003 ในแถวที่ 6 และเมื่อทดสอบพิมพ์รหัสลูกค้า A003 ลงใน B2 จะได้ผลลัพธ์ดังแสดงในภาพที่ 4

ภาพที่ 4 ผลการค้นหาในกรณีที่ข้อมูล

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