ในการคำนวณหาผลลัพธ์ บางครั้งไม่สามารถใช้สูตรของ Excel ที่มีอยู่มาใช้หาคำตอบได้ในสูตรเดียว จึงจำเป็นต้องใช้หลายสูตรมาซ้อนกัน ซ้อนกันไปซ้อนกันมาจนกลายเป็นสูตรยาวมากๆ ซึ่งสูตรยาวๆมีชื่อเรียกว่า Mega Formula

PushPullTime2

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

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

สูตรจะสั้นลงได้หลายวิธี

1. หาสูตรที่สามารถทำงานแทนหลายสูตรได้ เช่น ใช้ VLookup แทนที่จะใช้สูตร Match ร่วมกับ Index

2. ออกแบบตารางใหม่ ให้ตำแหน่งเซลล์อยู่ในตำแหน่งที่เอื้อประโยชน์ให้ใช้ในการคำนวณได้ง่ายขึ้น

3. เปลี่ยนตรรกที่ใช้ในการคำนวณใหม่ เช่น แทนที่จะคำนวณหาจำนวนวันทำงานให้ได้เลยรวดเดียว ให้เปลี่ยนไปคำนวณหาจำนวนวันหยุดแทน แล้วจึงนำจำนวนวันหยุดไปลบจากจำนวนวันทั้งหมด เหลือจำนวนวันทำงาน

PushPullTime

4. กระจายสูตรที่ต้องคำนวณซ้ำให้ใช้เซลล์หลายเซลล์ช่วยคำนวณทีละขั้น ทำให้ลดภาระที่สูตรหลายสูตรต้องมาคำนวณพร้อมกันทีเดียวในเซลล์เดียว ซึ่งวิธีนี้จะทำให้สูตรคำนวณเร็วขึ้น

5. คิดสูตรที่สร้างด้วย VBA ซึ่งวิธีนี้จะทำให้สูตรสั้นลงเหลือเป็นสูตรสั้นๆเพียงสูตรเดียว จากนั้นเมื่อสร้างเป็น VBA Add-in จะช่วยทำให้สามารถนำสูตรไปใช้กับแฟ้มอื่นเครื่องอื่นได้ด้วย

PushPullTime3

ดูตัวอย่างได้จาก
https://www.excelexperttraining.com/forum/articles/excel-formulas-articles/611