เก่งแบบย้าวยาว ต้องอย่าขัดขา Excel
ริจะใช้ Excel อย่าขัดขา Excel (ตอนที่ 11)
=INDEX(Begin,COUNTIF(Cum_Hour,”<“&(I10+((INDEX(Cum_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1,1)+(((I10+((INDEX(Cum_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1))+((J10 – VLOOKUP(J10, Begin, 1))*24)))-(INDEX(Cum_Hour,(COUNTIF(Cum_Hour,”<“&(I10+((INDEX(Cum_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1),1)-INDEX(Working_Hour,(COUNTIF(Cum_Hour,”<“&(I10+((INDEX(Cum_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1),1)))/24)
พอเห็นสูตรย้าวยาวที่ซ้อนกันไปซ้อนกันมาแบบนี้ เห็นแล้วต้องคิดว่าคนที่สร้างสูตรนี้ต้องเก่งมากใช่ไหม ตอนแรกที่ผมสร้างสูตรยาวเป็น รู้สึกว่าตัวเองเก่งเหมือนกัน ชอบสร้างสูตรยาวๆไปช่วยเหลือคนอื่นที่ตั้งคำถามมา แม้ได้รับคำชมแต่กลับต้องแบกภาระต่อไปไม่รู้จบ เพราะคนที่ลอกสูตรคำตอบไปใช้ แก้ไขสูตรเองไม่เป็น พอติดปัญหาก็ย้อนมาขอความช่วยเหลืออีก เดี๋ยวนี้ผมจึงพยายามเลี่ยงไม่ตอบเป็นสูตรยาวๆอีกแล้ว
สูตรย้าวยาวมีประโยชน์ต่อเมื่อใช้คำนวณเพื่อหาคำตอบในเซลล์เดียว ไม่เหมาะที่จะ copy สูตรไปใช้คำนวณซ้ำอีกที่เซลล์อื่น เพราะสูตรส่วนที่เขียนเหมือนกันจะต้องคำนวณซ้ำแล้วซ้ำอีก ทำให้ Excel คำนวณช้าลงและแฟ้มมีขนาดใหญ่ขึ้น
สูตรสั้นๆก็ทำให้ Excel คำนวณช้าลงและแฟ้มมีขนาดใหญ่ขึ้นได้เช่นกัน เช่น =3*4*A1 พอ copy สูตรนี้ไปใช้อีก 1,000 เซลล์ ตำแหน่งอ้างอิง A1 จะเปลี่ยนไปใช่ไหม แต่ส่วนของ 3*4 จะต้องคำนวณซ้ำอีกถึง 1,000 ครั้ง
ก่อนจะคิดสร้างสูตรย้าวยาว ต้องสร้างสูตรส่วนที่ต้องคำนวณซ้ำที่ให้คำตอบเดียวกันแยกไว้ในเซลล์หนึ่งไว้ก่อนเพื่อให้ Excel เสียเวลาคำนวณเพียงครั้งเดียว จากนั้นจึงลิงก์คำตอบที่ได้ไปใช้คำนวณต่อในสูตรอื่นที่ทำหน้าที่คำนวณขั้นตอนต่อไป
ในตัวอย่างสูตรย้าวยาวข้างต้น ขอให้สังเกตว่ามีส่วนของสูตร VLOOKUP(J10,Begin,1) ซ้ำกันหลายครั้ง ซึ่งควรแยกสูตรนี้ไปคำนวณของมันเองก่อนดีกว่า
หากสูตรย้าวยาวที่สร้างขึ้นไม่มีส่วนในในสูตรที่ต้องคำนวณซ้ำแบบหาคำตอบเดียวกันเลย ก็จำเป็นต้องใช้สูตรย้าวยาว copy ไปใช้ที่เซลล์อื่น แต่แน่นอนว่าแฟ้มจะคำนวณช้าลง แฟ้มใหญ่ขึ้น และที่สำคัญจะมีแต่คนสร้างสูตรคนเดียวเท่านั้นที่เข้าใจที่ไปทีมาของสูตร
ทางออกที่ดีกว่าสำหรับ Excel แต่ยากกว่าสำหรับคน ก็คือหันไปใช้สูตรที่สร้างขึ้นด้วย VBA ซึ่งแทนที่จะใช้ VBA รับค่าจากเซลล์ทีละเซลล์ไปคำนวณหาคำตอบแล้วส่งผลลัพธ์กลับลงมาในตารางทีละเซลล์ ควรสร้างรหัส VBA ที่รับค่าทุกเซลล์จากตารางไปคำนวณ เก็บผลลัพธ์ไว้ในหน่วยความจำของคอมพิวเตอร์ก่อน จากนั้นจึงส่งผลลัพธ์กลับมาทีเดียวพร้อมกันทั้งตาราง
แทนที่จะเขียนรหัสแบบนี้
เปลี่ยนมาใช้แบบนี้จะเร็วกว่ามาก
ดูรายละเอียดของรหัส VBA นี้ได้จาก
https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions?WT.mc_id=M365-MVP-4000499
ส่วนสูตรย้าวยาวของผมข้างต้น พอหันมาใช้ VBA จะกลายเป็นแบบนี้ ดูวิดีโอแสดงวิธีการที่
https://www.excelexperttraining.com/forum/articles/excel-formulas-articles/611