Excel Tips : การใช้ Data Validation ตรวจสอบงบหรือบัญชี

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

จากตัวอย่าง การจัดทำบัญชีรายรับรายจ่าย(บัญชีเงินสด) ให้ท่านผู้อ่านเลือก Cell E4 ถึง E12 เพื่อเป็น Cell ที่คำนวณผลรวมและตรวจสอบความถูกต้องของรายการที่ลงบัญชี ดังภาพที่ 1 จากนั้นให้คลิกคำสั่งใช้งาน Data Validation และกำหนดให้ตรวจสอบผลรวมของรายจ่ายตั้งแต่ E4 - E12 โดยใช้คำสั่ง SUM และกำหนดเงื่อนไขให้ผลรวมของรายจ่ายต้องไม่เกิน Budget ที่เรากำหนดไว้ เช่น รายจ่ายเงินสดของเดือนนี้ต้องไม่เกิน 15,000 บาท เราจะเขียนสูตร Excel ในช่อง Formula ได้ดังนี้ SUM(E$4:E12) <= 15000 ดังแสดงในภาพที่ 1 โดยกำหนดให้ประเภทของการแจ้งเตือนข้อผิดพลาดเป็นคำเตือน ซึ่งจะทำให้เราสามารถกำหนดรายการที่เกินงบได้ แต่จะมีคำเตือนให้ทราบว่าขณะนี้ ผลรวมรายจ่ายของเราเดือนนี้เกิน Budget ที่ตั้งไว้แล้ว

ภาพที่ 1 การกำหนด Data Validation ใน Excel

ในการใช้งาน เมื่อมีการบันทึกรายจ่ายเพิ่ม ให้ท่านแทรกแถวใหม่ลงใน Worksheet ก่อนแถว 12 และบันทึกรายรับรายจ่ายลงตามปกติ สูตร Excel : SUM ที่กำหนดไว้ใน Data Validation ก็จะปรับเปลี่ยนตำแหน่งของรายจ่ายที่ต้องรวมให้ใหม่โดยอัตโนมัติ มาดูตัวอย่างกันครับสมมุติผมเพิ่มรายการอีก 1 รายการเป็นค่าซื้อของใช้ประจำเดือนเป็นเงิน 3,500 บาท ผลของการตรวจสอบจะเป็นดังภาพที่ 2 โดยจะมีหน้าต่างคำเตือนแจ้งให้ทราบว่า รายจ่ายรวมตอนนี้ของท่านเกิน Budget ที่ตั้งไว้ หากท่านต้องการลงรายการก็ให้กด ใช้ เพื่อรับทราบ ท่านก็สามารถบันทึกค่าใช้จ่ายรายการนี้ลงบัญชีรายรับรายจ่ายได้ดังภาพที่ 3 ซึ่งหลังจากรายจ่ายรายการนี้ไปถ้าท่านบันทึกลงบัญชีรายรับรายจ่ายท่านจะพบคำเตือนนี้แจ้งให้ท่านทุกครั้ง ก็เป็นการเตือนสติท่านอยู่เสมอว่า Over Budget แล้วนะเดือนนี้ 

ภาพที่ 2 คำเตือนหลังจากที่มีการบันทึกรายจ่ายที่ผลรวมรายจ่ายเกิน Budget

ภาพที่ 3 แม้จะเกิน Budget รายจ่าย หากจะทำรายการต่อก็สามารถบันทึกลงรายการได้

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

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 ป้องกันการบันทึกวันเวลาผิดพลาด