ทิปการเพิ่มประสิทธิภาพให้กับ Microsoft Excel

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