วิธีสร้างกับดักใน Excel

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

แก้ที่ต้นตอ

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

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

โดยทั่วไปโปรแกรมสำเร็จรูปมีระบบการ export ข้อมูลออกมาเป็นแฟ้ม Excel ได้โดยตรง แต่ผู้สั่ง export ต้องเลือกประเภทของตารางให้ถูกต้องด้วย พึงหลีกเลี่ยงการ export ข้อมูลที่ถูกจัดเป็นหน้ารายงาน แต่ควรเลือก export ข้อมูลดิบซึ่งมีโครงสร้างเป็นฐานข้อมูลจะได้ตารางที่นำมาใช้กับ Excel ได้ทันที แต่ถ้าไม่มีระบบการ export ออกมาเป็นแฟ้ม Excel หรือ export ออกมาเป็น Excel แล้วกลับได้ข้อมูลที่ถูกโปรแกรมจัดสถานะหรือรูปแบบให้ผิดเพี้ยนไปจากที่ควร ให้ export ออกมาเป็นแฟ้มนามสกุล csv, dbf, txt, prn, หรือ sql ซึ่งจะได้ข้อมูลดิบตามที่ถูกจัดเก็บไว้ จากนั้นจึงใช้คำสั่ง Data > Get External Data ใน Excel เลือกดึงข้อมูลเฉพาะ field และช่วงรายการที่ต้องการเข้ามาใช้งาน

กับดักในตารางบันทึกข้อมูล

แม้คำสั่ง Data Validation เป็นเครื่องมือช่วยตรวจสอบข้อมูลว่าถูกต้องตามที่กำหนดไว้หรือไม่ก็ตาม แต่ Data Validation จะแสดงคำเตือนให้เห็นในขณะที่เกิดการบันทึกลงไปในเซลล์เท่านั้น ไม่สามารถป้องกันการใช้วิธี copy หรือสร้างสูตรลิงค์ค่าเข้าไปในตารางฐานข้อมูล อีกทั้งยังไม่สามารถช่วยป้องกันการลบข้อมูลที่บันทึกไว้

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

สิ่งที่ต้องระวังในการ copy ข้อมูล ในส่วนของพื้นที่ต้นทางต้องไม่มีการสั่ง Filter เอาไว้เพราะหาก copy ตารางที่ Filter ไว้จะได้เฉพาะเซลล์ที่มองเห็นติดมากับการ copy เท่านั้น และถ้าในตารางต้นทางมีสูตร ที่ตารางปลายทางควรสั่ง paste แบบ value เพื่อทำให้นำเฉพาะค่าผลลัพธ์มาใช้ต่อ โดยไม่ต้องห่วงว่าสูตรจะคำนวณผิดเพี้ยนไปจากเดิม และไม่ติดรูปแบบจากตารางต้นทางที่อาจใช้ปกปิดข้อมูลหรือทำให้ข้อมูลที่บันทึกไว้นั้นแสดงค่าต่างไปจากความเป็นจริง

ก่อนที่จะสั่ง copy และเมื่อ paste แล้วต้องตรวจสอบขอบเขตตารางว่าได้เลือกพื้นที่เซลล์มาครบทุกเซลล์ที่ต้องการหรือไม่ โดยคลิกลงไปเลือกเซลล์ใดก็ได้ในตารางที่บันทึกข้อมูลไว้แล้วกดปุ่ม Ctrl + * (หมายถึงกดปุ่ม Ctrl ไว้ก่อนแล้วกดปุ่ม * ตาม) เพื่อทำให้ Excel เลือกพื้นที่ตารางทั้งหมดที่ติดต่อกันให้ หรือกดปุ่ม F5 เพื่อเลือกตารางตามชื่อ Range Name ที่ตั้งไว้ จากนั้นให้กดปุ่ม Ctrl + จุด ซ้ำไปเรื่อยๆเพื่อทำให้ Excel แสดงหัวมุมตารางแต่ละมุมให้เห็นชัดว่าเลือกครบหรือยัง

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

เมื่อได้ข้อมูลจากโปรแกรมอื่นมาใช้ต่อในตาราง Excel แล้ว หากต้องการสร้างตารางใหม่ในชีทอื่นหรือแฟ้มอื่น พึงหลีกเลี่ยงการพิมพ์ข้อมูลที่มีเก็บอยู่ซ้ำอีก เพราะการพิมพ์ใหม่อาจทำให้เกิดข้อมูลที่ต่างไปจากเดิม จึงจำเป็นต้องเรียนรู้การใช้สูตร VLookup, Match, และ Index เพื่อดึงข้อมูลที่เก็บไว้มาใช้ซ้ำอีก หรือใช้ Data Validation แบบ List เพื่อทำให้คลิกเลือกรายการข้อมูลที่เก็บไว้มาใช้โดยไม่ต้องพิมพ์ใหม่อีก

ส่วนคำสั่ง Conditional Formatting ซึ่งสามารถเปลี่ยนสีและรูปแบบของเซลล์ให้เปลี่ยนไปจากเดิมตามเงื่อนไขที่กำหนดไว้ ซึ่งมักนำมาใช้กับการค้นหาด้วยการแสดงสีเซลล์ที่ต่างไปจากปกติ เพื่อบอกตำแหน่งเซลล์ของข้อมูลหรือตรวจสอบเซลล์ว่ามีข้อมูลตรงตามที่ต้องการหรือไม่ แต่หากใช้ Conditional Formatting กับตารางขนาดใหญ่ จะทำให้ Excel คำนวณช้าลงอย่างมาก เนื่องจาก Conditional Formatting ทำงานแบบ Volatile โดยจะคำนวณเองเสมอเมื่อเซลล์อื่นมีการคำนวณหรือเปลี่ยนแปลงค่าต่างไปจากเดิม แม้ว่าเซลล์เหล่านั้นไม่ได้ผูกสูตรเกี่ยวข้องกับตารางที่กำหนด Conditional Formatting ไว้ก็ตาม จึงแนะนำให้เลือกใช้ Conditional Formatting เฉพาะเซลล์ที่จำเป็นเท่านั้น หรือเปลี่ยนไปใช้ Format แบบธรรมดาซึ่งสามารถเปลี่ยนรูปแบบของตัวเลขบวก ลบ เลขศูนย์ และตัวอักษรให้แสดงต่างไปจากเดิมโดยใช้เครื่องหมาย ; ควบคุมการแสดงค่าเหล่านั้น เช่น รูปแบบ 0.00;”Wrong”;”Wrong”;”Wrong” จะแสดงคำว่า Wrong ให้เห็นในเซลล์ที่ไม่ได้มีตัวเลขซึ่งมีค่ามากกว่า 0 บันทึกไว้

หากสามารถใช้ VBA เป็น ควรใช้ VBA ช่วยในการส่งข้อมูลจากเซลล์รับค่าไปบันทึกในตารางฐานข้อมูลตามตำแหน่งที่ต้องการ โดยใช้คำสั่ง Data Validation และ Conditional Formatting ร่วมกันในเซลล์รับข้อมูลเพื่อทำให้มั่นใจว่าข้อมูลซึ่งกรอกลงในเซลล์รับข้อมูลนั้นถูกต้องตรงตามที่ต้องการจริง จากนั้นเมื่อใช้ VBA ส่งค่าไปบันทึกแล้วก็ย่อมเป็นข้อมูลที่ถูกต้องอย่างไม่ต้องสงสัย และยังทำให้ Conditional Formatting ถูกเลือกใช้เพียงไม่กี่เซลล์ ไม่ทำให้แฟ้มคำนวณช้าลงมากแต่อย่างใด

เมื่อกับดักไม่ได้ผล

วิธีแก้ปัญหาที่ดีต้องแก้ครั้งเดียวแล้วหมดปัญหาไปเลย ไม่ใช่ว่าต้องแก้แล้วแก้อีก แต่ปัญหาที่เกิดขึ้นจากความไม่รู้ไม่ใส่ใจของคนนั้นแก้ยากมาก พอให้ความรู้กับคนนี้ไปแล้วต่อมาเมื่อเปลี่ยนคนใหม่ก็มักมีโอกาสเกิดปัญหาขึ้นมาอีก อย่างที่เตือนว่าการบันทึกข้อมูลที่ดีนั้นข้อมูลที่เป็นตัวเลขต้องเป็นตัวเลขจริงๆ ข้อมูลที่เป็นตัวอักษรต้องเป็นตัวอักษรจริงๆ ยังถือว่าเป็นปัญหาที่ตรวจสอบพบได้ง่ายกว่าการบันทึกข้อมูลขาดเกิน เช่น บันทึกชื่อลูกค้าแล้วมีวรรคเกิน หรือสะกดตัวอักษร ฎ เป็น ฏ หรือ ถ เป็น ภ เพราะเมื่อมองผ่านๆแล้วเห็นไม่แตกต่างกันนัก

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

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

ในกรณีสะกดตัวอักษรชื่อลูกค้าหรือชื่อสินค้าผิด ประเภทสะกดขาดไป เกินไป หรือสะกดไม่ตรง ให้ใช้คำสั่ง Data > Advanced แบบ Unique records only เพื่อสรุปรายชื่อทั้งหมดที่มีก่อน จากนั้นให้สั่ง Data > Sort เรียงชื่อจากน้อยไปมากจะพบรายชื่อที่สะกดผิดคล้ายกันเรียงลำดับไว้ใกล้กันหรือเรียงผิดลำดับที่ควรจะเป็น ทำให้ทราบว่ามีข้อมูลที่สะกดแตกต่างกันแบบใดบ้าง แล้วใช้คำสั่ง Find ค้นหาตำแหน่งเซลล์ของข้อมูลชื่อที่สงสัย หรือหากมีตารางรายชื่อที่ถูกต้องอยู่แล้วให้ใช้สูตร Match หาตำแหน่งรายการว่ามีอยู่แล้วในตารางรายชื่อที่ถูกต้องหรือไม่ (ถ้าใช้ CountIF นับจำนวนเซลล์ว่าเซลล์ใดที่นับแล้วได้ค่าเท่ากับ 0 ซึ่งแสดงว่าไม่มีข้อมูลชื่อนั้นอยู่ในรายชื่อที่ถูกต้องจะใช้เวลาคำนวณนานกว่าสูตร Match)

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

การใช้สูตร Count หรือ CountA อาจไม่สามารถตรวจสอบความครบถ้วนของรายการข้อมูลได้แม่นยำเสมอไป เพราะในตารางอาจมีหรือไม่มีข้อมูลในจำนวนพอดีที่ทั้งสองสูตรคืนค่าเท่ากันโดยบังเอิญก็ได้ การตรวจสอบอีกทางหนึ่งใช้วิธีนับจำนวนรายการทั้งหมดด้วยสูตร เลขที่ row ของรายการสุดท้าย – เลขที่ row ของรายการแรก +1 โดยใช้สูตรต่อไปนี้

หาเลขที่ row ของรายการบรรทัดสุดท้ายด้วยสูตร Array (วงเล็บปีกกาเกิดจากการปุ่ม Ctrl+Shift+Enter พร้อมกันแทนการกดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์)

{=Max(IF(DataRange<>0,Row(DataRange)))}

หาเลขที่ row ของรายการแรกด้วยสูตร =Row(Cellในรายการแรก)

นอกจากนี้ยังมีสูตรอื่นซึ่งใช้ตรวจสอบจำนวนรายการว่าครบถ้วนหรือไม่ได้อีก เช่น สูตร CountBlank นับจำนวนเซลล์ที่เป็นช่องว่าง หรือสูตร SubTotal(102,NumRange) นับจำนวนรายการที่กรองแล้วเฉพาะที่เป็นตัวเลข หรือ SubTotal(103,DataRange) นับจำนวนรายการที่กรองแล้วเฉพาะที่เป็นตัวเลขหรือตัวอักษร