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

Light bulbLink ไปข้างหน้าหรือหลังดีกว่ากัน

  • หลีกเลี่ยงการ Link ไปข้างหน้า เช่นใช้ A1 ทำการ Link ไป Z1000 อาจจะเกิดการคำนวณที่ช้าลงหากมีสูตรที่หลากหลายจำนวนมากเนื่องจาก Excel จะปรับลำดับการคำนวณ ทำให้เกิดการรอผลการคำนวณจากสูตรอื่น ๆ

Light bulbการคำนวณเป็นวงกลม

  • จะทำให้เกิดการคำนวณช้าเพราะต้องคำนวณหลายรอบเนื่องจากคำนวณทุกเซลล์ที่เกี่ยวข้อง
  • ควรกำหนดให้คำนวณให้อยู่ในชีตเดียวแทนการคำนวณหลายชีต
  • ลดจำนวนเซลล์ให้เหลือน้อยที่สุดเท่าที่จำเป็น

Light bulbLink ระหว่างไฟล์

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

Light bulbการใช้พื้นที่เซลล์ที่ใช้งาน

  • ปรับพื้นที่เซลล์ให้เหลือเท่าที่ใช้งานจริงโดยการกดแป้น F5 > Special > Last Cell > OK > ลบบรรทัดและคอลัมน์ว่างก่อน Last Cell > กดแป้น Ctrl+Home > Save

Light bulbการทำงานกับข้อมูลจำนวนมาก

  • ใช้ Table เข้ามาช่วยแทนการเขียน Range Name ให้เพิ่มลดตามช่วงเซลล์ การสร้าง Table เข้าเมนู Insert > Table โดยข้อดีของ Table คือ
    • ประสิทธิภาพดีกว่าใช้การอ้างอิงทั้งคอลัมน์
    • สร้าง Table ได้หลาย Table ในชีตเดียว
    • สูตรใน Table ขยายหรือลดตามขนาด Table อัตโนมัติ

Light bulbข้อดีข้อเสียของการอ้างอิงทั้งคอลัมน์

  • หลายฟังก์ชั่นสามารถทำงานได้เร็วแม้จะอ้างอิงทั้งคอลัมน์ เช่น Sum, Sumif เพราะสามารถหาบรรทัดสุดท้ายที่ใช้งานได้ ส่วน Sumproduct จะคำนวณทุกเซลล์ในคอลัมน์
  • กรณีเขียนฟังก์ชั่นขึ้นมาใช้เองควรพิจารณาหาบรรทัดสุดท้ายที่ใช้งานเพื่อประสิทธิภาพที่ดีกว่า
  • สูตร Array ในปัจจุบันทำงานทั้งคอลัมน์ได้ แต่จะคำนวณทุกเซลล์ทำให้ประสิทธิภาพต่ำ
  • การกำหนดช่วงเซลล์ให้ยืดหยุ่นโดยใช้ Range Name
    • ทำงานได้ดีกับสูตร Array
    • การใช้หลาย Range Name ในคอลัมน์เดียวกันจะพึ่งพาสูตรเพื่อการนับจำนวนมาก
    • การใช้ Range Name จำนวณมากทำให้ลดประสิทธิภาพการคำนวณ

Light bulbฟังก์ชั่นจำพวก 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 แบบใกล้เคียง แต่ต้องใช้ด้วยความเข้าใจเนื่องจากผลลัพธ์ที่ได้อาจะเป็นค่าใกล้เคียง

Light bulbVlookup, Index+Match หรือ Offset

  • ควรเลือกใช้ Index ร่วมกับ Match แทน Vlookup (Vlookup จะทำงานเร็วกว่า Index ร่วมกับ Match เล็กน้อย ใช้งานง่ายและกิน Memory น้อยกว่า Index+Match หรือ Offset) เนื่องจาก Index+Match มีความยืดหยุ่นสูงกว่า เราสามารถใช้ Match แสดงผลไว้ที่คอลัมน์ใด ๆ แล้วใช้ Index อ้างอิงมายังคอลัมน์นั้นเพื่อดึงข้อมูลที่เกี่ยวข้องได้โดยสะดวก
  • Index ทำงานเร็วและไม่เป็น Volatile ในขณะที่ Offset เป็น Volatile แม้ Offset จะทำงานเร็วแต่ถูกกระทบให้คำนวณได้บ่อยครั้งจึงทำให้ความเร็วโดยรวมลดลง

Light bulbการใช้สูตร Array แบบหลายเงื่อนไข

  • ให้แบ่งเงื่อนไขออกเป็นคอลัมน์แล้วใช้ Sumif, Sumifs มาช่วยจะเพิ่มความเร็วขึ้นได้
  • ใช้ Dynamic Range Name แทนการเลือกทั้งคอลัมน์หรือเลือกข้อมูลเผื่อไว้

Light bulbการใช้ 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

Light bulbConditional Formatting และ Data Validation

  • ใช้งานเท่าที่จำเป็น หากใช้มากจะทำให้การคำนวณช้าลง

Light bulbการกำหนด Range Name

  • ไม่ควรใช้ Range Name อ้างอิง Range Name อื่น เนื่องจากจะคำนวณทุกครั้งที่สูตรอ้างอิง Range Name

Light bulbการใช้ Volatile Function

  • Volatile function คือ Function ที่ถูกกระทบให้เปลี่ยนแปลงได้โดยง่าย เช่น Indirect, Offset, Rand, Today, Now จะก่อให้เกิดการคำนวณบ่อยครั้ง ควรเลือกใช้เท่าที่จำเป็น
  • ฟังก์ชั่นที่ผ้นแปรเช่น เช่น Today, Now  ควรเขียนไว้ในเซลล์ใดๆ แล้วอ้างอิงมาใช้แทนการเขียนไว้ในสูตรอื่น

Light bulbการทำงานกับ VBA และ Macro

  • ปิดการทำงานบางฟังก์ชั่น (กำหนดค่าให้เป็น False) แล้วค่อยเปิดหลังจากทำงานเสร็จ  (กำหนดค่าให้เป็น True) เช่น
    • Application.ScreenUpdating
    • Application.DisplayStatusBar
    • Application.EnableEvents
    • ActiveSheet.DisplayPageBreaks
  • ยกเลิกการการคำนวณแบบ Automatic ตามด้านล่าง (กำหนดเป็น xlCalculationManual) แล้วเปิดการใช้งานใหม่ (กำหนดเป็น xlCalculationAutoMatic) หลังเสร็จสิ้นการทำงาน
    • Application.Calculation