💡 Link ไปข้างหน้าหรือหลังดีกว่ากัน
- หลีกเลี่ยงการ Link ไปข้างหน้า เช่นใช้ A1 ทำการ Link ไป Z1000 อาจจะเกิดการคำนวณที่ช้าลงหากมีสูตรที่หลากหลายจำนวนมากเนื่องจาก Excel จะปรับลำดับการคำนวณ ทำให้เกิดการรอผลการคำนวณจากสูตรอื่น ๆ
💡 การคำนวณเป็นวงกลม
- จะทำให้เกิดการคำนวณช้าเพราะต้องคำนวณหลายรอบเนื่องจากคำนวณทุกเซลล์ที่เกี่ยวข้อง
- ควรกำหนดให้คำนวณให้อยู่ในชีตเดียวแทนการคำนวณหลายชีต
- ลดจำนวนเซลล์ให้เหลือน้อยที่สุดเท่าที่จำเป็น
💡 Link ระหว่างไฟล์
- หลีกเลี่ยงการ Link ระหว่างไฟล์ เนื่องจากทำให้คำนวณช้าและยากต่อการจัดการ
- ใช้ไฟล์ขนาดใหญ่จำนวนน้อยไฟล์ Link กันแทนใช้ไฟล์เล็ก ๆ จำนวนมาก Link กัน
- ใช้การอ้างอิงแบบง่าย ๆ ไปยังไฟล์ที่ปิดแทนการเขียนสูตรซับซ้อน เพื่อหลีกเลี่ยงการคำนวณ
- กรณีจำเป็นต้อง Link ข้ามไฟล์ให้เปิดไฟล์ขึ้นมาทำงานพร้อมกันโดยเปิดไฟล์ต้นทางก่อนเปิดไฟล์ปลายทาง
💡 การใช้พื้นที่เซลล์ที่ใช้งาน
- ปรับพื้นที่เซลล์ให้เหลือเท่าที่ใช้งานจริงโดยการกดแป้น F5 > Special > Last Cell > OK > ลบบรรทัดและคอลัมน์ว่างก่อน Last Cell > กดแป้น Ctrl+Home > Save
💡 การทำงานกับข้อมูลจำนวนมาก
- ใช้ Table เข้ามาช่วยแทนการเขียน Range Name ให้เพิ่มลดตามช่วงเซลล์ การสร้าง Table เข้าเมนู Insert > Table โดยข้อดีของ Table คือ
- ประสิทธิภาพดีกว่าใช้การอ้างอิงทั้งคอลัมน์
- สร้าง Table ได้หลาย Table ในชีตเดียว
- สูตรใน Table ขยายหรือลดตามขนาด Table อัตโนมัติ
💡 ข้อดีข้อเสียของการอ้างอิงทั้งคอลัมน์
- หลายฟังก์ชั่นสามารถทำงานได้เร็วแม้จะอ้างอิงทั้งคอลัมน์ เช่น Sum, Sumif เพราะสามารถหาบรรทัดสุดท้ายที่ใช้งานได้ ส่วน Sumproduct จะคำนวณทุกเซลล์ในคอลัมน์
- กรณีเขียนฟังก์ชั่นขึ้นมาใช้เองควรพิจารณาหาบรรทัดสุดท้ายที่ใช้งานเพื่อประสิทธิภาพที่ดีกว่า
- สูตร Array ในปัจจุบันทำงานทั้งคอลัมน์ได้ แต่จะคำนวณทุกเซลล์ทำให้ประสิทธิภาพต่ำ
- การกำหนดช่วงเซลล์ให้ยืดหยุ่นโดยใช้ Range Name
- ทำงานได้ดีกับสูตร Array
- การใช้หลาย Range Name ในคอลัมน์เดียวกันจะพึ่งพาสูตรเพื่อการนับจำนวนมาก
- การใช้ Range Name จำนวณมากทำให้ลดประสิทธิภาพการคำนวณ
💡 ฟังก์ชั่นจำพวก Lookups
- การใช้ Lookup ฟังก์ชั่นให้คำนึงถึง match_type และ range_lookup เสมอ
Match(lookup_value,lookup_array,match_type)Vlookup(lookup_value,table_array,col_index_num,range_lookup)
- จากฟังก์ชั่น
Match(lookup_value,lookup_array,match_type)match_type มีได้ 3 แบบ ให้ความหมายต่างกันคือ- 1 หรือ ปล่อยว่าง เป็นการหาค่าที่น้อยกว่าหรือเท่ากับ lookup_value ใน lookup_array โดยข้อมูลจะต้องเรียงจากน้อยไปหามากจึงจะให้คำตอบถูกต้อง
- -1 เป็นการหาค่าที่มากกว่าหรือเท่ากับ lookup_value ใน lookup_array โดยข้อมูลจะต้องเรียงจากมากไปหาน้อยจึงจะให้คำตอบถูกต้อง
- 0 เป็นการหาค่าที่เท่ากับ lookup_value ใน lookup_array โดยข้อมูลไม่จำเป็นต้องเรียง
- ลักษณะการค้นหา
- 1 หรือ ปล่อยว่าง และ -1 เป็นการหาค่าโดยประมาณหรือหาค่าแบบใกล้เคียง จะทำงานเร็วมากเนื่องจากลักษณะการค้นหาเป็นแบบ Binary Search
- 0 เป็นการค้นหาแบบตรงตัว เทียบค่าไปทีละตัว
- จากฟังก์ชั่น
Vlookup(lookup_value,table_array,col_index_num,range_lookup)range_lookup มีได้ 2 แบบ ให้ความหมายต่างกันคือ- True หรือ 1 หรือ ปล่อยว่าง เป็นการหาค่าที่น้อยกว่าหรือเท่ากับ lookup_value ในคอลัมน์แรกของ table_array โดยข้อมูลจะต้องเรียงจากน้อยไปหามากจึงจะให้คำตอบถูกต้อง
- False หรือ 0 เป็นการหาค่าที่เท่ากับ lookup_value ในคอลัมน์แรกของ table_array โดยข้อมูลไม่จำเป็นต้องเรียง
- เพื่อประสิทธิภาพการคำนวณ ให้เลือกที่จะ Sort ข้อมูลแล้วใช้ Lookup แบบใกล้เคียง แต่ต้องใช้ด้วยความเข้าใจเนื่องจากผลลัพธ์ที่ได้อาจะเป็นค่าใกล้เคียง
💡 Vlookup, Index+Match หรือ Offset
- ควรเลือกใช้ Index ร่วมกับ Match แทน Vlookup (Vlookup จะทำงานเร็วกว่า Index ร่วมกับ Match เล็กน้อย ใช้งานง่ายและกิน Memory น้อยกว่า Index+Match หรือ Offset) เนื่องจาก Index+Match มีความยืดหยุ่นสูงกว่า เราสามารถใช้ Match แสดงผลไว้ที่คอลัมน์ใด ๆ แล้วใช้ Index อ้างอิงมายังคอลัมน์นั้นเพื่อดึงข้อมูลที่เกี่ยวข้องได้โดยสะดวก
- Index ทำงานเร็วและไม่เป็น Volatile ในขณะที่ Offset เป็น Volatile แม้ Offset จะทำงานเร็วแต่ถูกกระทบให้คำนวณได้บ่อยครั้งจึงทำให้ความเร็วโดยรวมลดลง
💡 การใช้สูตร Array แบบหลายเงื่อนไข
- ให้แบ่งเงื่อนไขออกเป็นคอลัมน์แล้วใช้ Sumif, Sumifs มาช่วยจะเพิ่มความเร็วขึ้นได้
- ใช้ Dynamic Range Name แทนการเลือกทั้งคอลัมน์หรือเลือกข้อมูลเผื่อไว้
💡 การใช้ Sumproduct คำนวณแบบหลายเงื่อนไข
- ควรเลือกใช้ Sumifs, Countifs, AverageIfs แทน Sumproduct เนื่องจากทำงานได้เร็วกว่า
- Sumproduct สามารถทำงานได้เร็วกว่า Sum แบบ Array ที่ต้องกดแป้น Ctrl+Shift+Enter
- Sumproduct เป็นฟังก์ชั่นอรรถประโยชน์ใช้นับก็ได้ใช้รวมก็ได้
- หากเขียนเฉพาะเงื่อนไขเป็นการนับ
- หากเขียนช่วงที่ต้องการรวมเข้าไปด้วยเป็นการรวม
- วิธีเขียนสูตร Sumproduct สามารถเขียนได้เป็น
Sumproduct(--(condition1),--(condition2),sum_range)
สามารถใช้ +0 หรือ *1 แทน--ได้ แต่--ทำงานเร็วที่สุด (--คือเครื่องหมายลบสองตัวติดกัน)Sumproduct((condition1)*(condition2)*sum_range)
ยืดหยุ่นต่อการนำหลายคอลัมน์มาคูณกันเพื่อเป็นผลรวม แต่ทำงานได้ช้ากว่าแบบแรก และจะเกิด Error หากว่ามีเซลล์ใดเป็น Text
💡 Conditional Formatting และ Data Validation
- ใช้งานเท่าที่จำเป็น หากใช้มากจะทำให้การคำนวณช้าลง
💡 การกำหนด Range Name
- ไม่ควรใช้ Range Name อ้างอิง Range Name อื่น เนื่องจากจะคำนวณทุกครั้งที่สูตรอ้างอิง Range Name
💡 การใช้ Volatile Function
- Volatile function คือ Function ที่ถูกกระทบให้เปลี่ยนแปลงได้โดยง่าย เช่น Indirect, Offset, Rand, Today, Now จะก่อให้เกิดการคำนวณบ่อยครั้ง ควรเลือกใช้เท่าที่จำเป็น
- ฟังก์ชั่นที่ผ้นแปรเช่น เช่น Today, Now ควรเขียนไว้ในเซลล์ใดๆ แล้วอ้างอิงมาใช้แทนการเขียนไว้ในสูตรอื่น
💡 การทำงานกับ VBA และ Macro
- ปิดการทำงานบางฟังก์ชั่น (กำหนดค่าให้เป็น False) แล้วค่อยเปิดหลังจากทำงานเสร็จ (กำหนดค่าให้เป็น True) เช่น
Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False '...Your code Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True
- ยกเลิกการการคำนวณแบบ Automatic ตามด้านล่าง (กำหนดเป็น xlCalculationManual) แล้วเปิดการใช้งานใหม่ (กำหนดเป็น xlCalculationAutoMatic) หลังเสร็จสิ้นการทำงาน โดยมีลักษณะการใช้งานคือ
Application.Calculation = xlCalculationManual '...Your code Application.Calculation = xlCalculationAutoMatic
ศึกษาเพิ่มเติมได้ที่ https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-tips-for-optimizing-performance-obstructions
สอบถามปัญหา Excel and VBA ได้ที่ snasui.com