Excel จะทำงานเร็วขึ้น เมื่อหาทางป้องกันไม่ให้เกิด Error ดีกว่ามาหาทางแก้ Error

สูตร VLookup, Match, XLookup ตอนที่หาค่าไม่พบจะเกิด Error NA = Not Available ขึ้นมาแล้วเราต้องหาทางเปลี่ยน Error ให้เป็นค่าอื่นด้วยสูตร IFError ใช่ไหม ในสูตรรุ่นใหม่อย่าง XLookup ก็มี Option พิเศษให้จัดการเปลี่ยน Error ได้ในตัว

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

แทนที่จะรอมาแก้ Error แนะนำให้หาทางป้องกันไม่ให้เกิด Error ก่อน ดีกว่าปล่อยให้วัวหายแล้วค่อยมาล้อมคอก

เครื่องมือสำคัญที่จะช่วยป้องกันไม่ให้เกิด Error ก็คือ การใช้คำสั่ง Data Validation แบบ List ซึ่งช่วยทำให้ไม่ต้องเสียเวลามาพิมพ์คำที่จะใช้ค้นหาเองอีกต่อไป

จากภาพตัวอย่างนี้ เซลล์สีส้ม F5 ใช้ Data Validation แบบ List โดยลิงก์ข้อมูลมาจากพื้นที่ของรหัส ID ช่วยทำให้ยังไงๆก็ตามจะกรอกได้แต่รหัสที่มีอยู่เท่านั้น ช่วยตัดไฟแต่ต้นลม สูตร VLookup / XLookup จะหาค่าเจอแน่นอน 100%

Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1k16UiuVrJKeI4VfDxSh3Kbhuzci9FFFJ/view?usp=sharing

ปล สำหรับผู้ใช้ Excel 365 จะพบว่า List ทำงานเหนือกว่า Excel รุ่นก่อนๆ
1. ทำหน้าที่ Filter ตัดรหัสที่ซ้ำให้ในตัว
2. จะช่วยค้นหารหัสให้โดยไม่ต้องไล่หาเอง แค่พิมพ์รหัสบางส่วนลงไปจะเลื่อนไปหารหัสนั้นให้เองอีกด้วย 

ในตัวอย่างนี้ยังใช้ CountIF มาช่วยตรวจสอบซ้ำเป็นด้วป้องกันอีกชั้น เพื่อช่วยตัดสินใจว่าจะให้ใช้สูตรไหนต่อไปดี

ถ้านับแล้วเท่ากับ 1 แสดงว่ามีเพียงค่าเดียว ให้ใช้สูตร VLookup / Xlookup
ถ้านับแล้ว >=1 แสดงว่ามีค่าซ้ำ ให้ใช้สูตร Filter หาค่าซ้ำต่อ

ช่วยทำให้ Excel ไม่เสียเวลาไปค้นหาด้วยสูตร Filter ที่จะทำงานช้ากว่าโดยไม่จำเป็น