7 การตั้งค่า Excel แอบแฝงที่ทำให้เวิร์กบุ๊กขนาดใหญ่ช้า

เมื่อเวิร์กบุ๊ก Excel เริ่มค้าง คำนวณซ้ำนาน หรือหน่วงทุกครั้งที่เลื่อนดู ผู้คนส่วนใหญ่โทษสูตรซับซ้อนหรือข้อมูลมากเกินไป บางครั้งเป็นจริง แต่บ่อยครั้งสาเหตุจริงคือชุดเล็กของการตั้งค่า Excel ที่ทำงาน против คุณในพื้นหลัง เวิร์กบุ๊ก Excel ขนาดใหญ่อาจช้าลงเพราะการตั้งค่าและคุณสมบัติที่แอบแฝงซึ่งบริโภคทรัพยากรโดยไม่จำเป็น หากคุณจัดการกับชุดข้อมูลขนาดใหญ่ สูตรซับซ้อน หรือหลายชีต การปรับแต่งการตั้งค่าเหล่านี้สามารถปรับปรุงประสิทธิภาพได้อย่างมีนัยสำคัญ

ในบทช่วยสอนนี้ เราจะแสดง 7 การตั้งค่า Excel ที่อาจลดความเร็วเวิร์กบุ๊กขนาดใหญ่ลงในลับ พร้อมขั้นตอนปฏิบัติจริงในการระบุและแก้ไข เราจะครอบคลุมโหมดการคำนวณ Add-ins การจัดรูปแบบตามเงื่อนไข และอื่นๆ

1. โหมดการคำนวณอัตโนมัติ – คำนวณซ้ำทุกอย่าง ตลอดเวลา

โดยค่าเริ่มต้น Excel มักทำงานในโหมด Automatic Calculation นั่นหมายความว่าทุกครั้งที่คุณแก้ไขค่า Excel จะคำนวณสูตรที่ขึ้นอยู่กับกันทันที ในเวิร์กบุ๊กเล็ก นี่สะดวก แต่วORKบุ๊กขนาดใหญ่ มันทำให้ทุกการกดคีย์บอร์ดรู้สึกแพง

ถ้าไฟล์ของคุณมีสูตรหลายพันสูตร การค้นหา Dynamic arrays สรุปจาก Pivot หรือฟังก์ชัน Volatile การคำนวณซ้ำอัตโนมัติสามารถกระตุ้นการปฏิกิริยาโซ่หลังการแก้ไขเพียงเล็กน้อย เปลี่ยนเซลล์อินพุตหนึ่งเซลล์ และ Excel อาจเริ่มคำนวณซ้ำครึ่งหนึ่งของเวิร์กบุ๊ก

ทำไมมันช้า:

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

วิธีแก้:

• ไปที่แท็บ File >> เลือก Options >> เลือก Formulas
• ภายใต้ Calculation options เปลี่ยนจาก Automatic เป็น Manual >> คลิก OK

หรือจาก Ribbon:

• ไปที่แท็บ Formulas >> เลือก Calculation Options >> เลือก Manual

• เพื่อคำนวณซ้ำด้วยมือเมื่อต้องการ:
  • F9 คำนวณซ้ำเวิร์กบุ๊กที่เปิดอยู่ทั้งหมด
  • Shift + F9 คำนวณซ้ำชีทที่ใช้งานอยู่
  • Ctrl + Alt + F9 บังคับคำนวณซ้ำเต็มรูปแบบ

การเปลี่ยนแปลงนี้เพียงอย่างเดียวสามารถลด Lag ลง 50% ถึง 80% ในไฟล์ที่มีสูตรมาก

จำไว้: อย่าทิ้งโหมด Manual ไว้ถาวรหากคนอื่นใช้ไฟล์ เพราะอาจทำให้เกิดข้อผิดพลาดร้ายแรง เพิ่มหมายเหตุที่เด่นชัดหรือจำไว้ให้สลับกลับเป็น Automatic ก่อนมอบไฟล์ให้คนอื่น มิฉะนั้นพวกเขาอาจเห็นผลลัพธ์เก่า

เคล็ดลับโปร: สำหรับการควบคุมที่เลือกได้ ใช้ Automatic Except for Data Tables หากคุณมี Data Tables ที่ไม่ต้องการอัปเดตตลอดเวลา

2. Add-ins ที่ไม่จำเป็น – ทำงานในพื้นหลังที่คุณไม่เคยใช้

Excel Add-ins เป็นหนึ่งในสาเหตุที่พลาดมองมากที่สุดของประสิทธิภาพช้า Add-ins โหลดพร้อมแอปพลิเคชันและสามารถ Hook เข้าไปใน Events เช่น เปิดเวิร์กบุ๊ก เปลี่ยนเซลล์ คำนวณซ้ำ และบันทึก แม้ Add-ins ที่คุณลืมไปแล้ว ก็สามารถแทรกแซง Events เหล่านี้และทำงานเงียบๆ ในพื้นหลังทุกครั้งที่คุณโต้ตอบกับข้อมูล

Add-ins ทั่วไปรวมถึง เครื่องมือมรดกของบริษัท Power Pivot COM Add-ins Analysis ToolPak (ถ้าไม่ได้ใช้) Solver ไลบรารีแผนภูมิของบุคคลที่สาม และเครื่องมือบุคคลที่สาม เช่น ตัวแปลง PDF

ทำไมมันช้า:

แม้คุณจะไม่ได้ใช้งานพวกเขาโดยตรง Add-ins ยังคงบริโภคหน่วยความจำและรบกวนประสิทธิภาพเวิร์กบุ๊ก พวกเขาสามารถเพิ่มเวลาเริ่มต้น Excel ใช้ RAM ในพื้นหลัง Hook เข้าไปใน Events ของเวิร์กบุ๊ก และโต้ตอบกับสูตร การเชื่อมต่อข้อมูล หรือ Ribbon ทำให้เกิดความล่าช้าแม้จะว่างเปล่า

ในเวิร์กบุ๊กเบา คุณอาจไม่สังเกต แต่วORKบุ๊กหนัก ค่าใช้จ่ายเพิ่มเติมจะชัดเจน

วิธีแก้:

• เปิด File >> เลือก Options >> เลือก Add-ins
• ขยายเมนูดรอปดาวน์ Manage >> เลือก COM Add-ins หรือ Excel Add-ins >> คลิก Go

• ตรวจสอบ Add-ins ทั้งหมดและยกเลิกติ๊กสิ่งที่คุณไม่ได้ใช้งานอยู่
  • ปิด Analysis ToolPak หากคุณไม่ได้ใช้เครื่องมือสถิติของมัน

• คุณสามารถเปิดใช้งานกลับได้ตลอดเวลา การยกเลิกติ๊กไม่ได้ถอนการติดตั้ง
• รีสตาร์ท Excel และทดสอบประสิทธิภาพ
• ถ้า Add-in สำคัญ ตรวจสอบอัปเดต
  • เปิด File >> เลือก Account >> เลือก Update Options เนื่องจากเวอร์ชันเก่ามักมีบั๊ก

การปิด Add-in ที่อพยพเพียงตัวเดียวสามารถลดเวลาโหลดได้หลายวินาที

3. การใช้ Conditional Formatting มากเกินไป – กฎที่ใช้กับคอลัมน์ทั้งหมด

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

ทำไมมันช้า:

Excel อาจต้องตรวจสอบกฎการจัดรูปแบบใหม่ทุกครั้งที่ข้อมูลเปลี่ยน ชีตถูกวาดใหม่ หรือสูตรคำนวณซ้ำ เมื่อคุณนำกฎไปใช้กับคอลัมน์ทั้งคอลัมน์ Excel จะประเมินกฎนั้นสำหรับเซลล์มากกว่าล้านเซลล์ทุกครั้งที่ชีตคำนวณซ้ำ แม้เซลล์ส่วนใหญ่จะว่างเปล่า Conditional Formatting บ่อยครั้งทำให้ Lag ระหว่аниеเลื่อนดู กรอง และแก้ไข แม้สูตรจะไม่ซับซ้อนมาก

วิธีแก้:

• เลือกช่วงที่มีการจัดรูปแบบ
• ไปที่แท็บ Home >> เลือก Conditional Formatting >> เลือก Manage Rules

• ตรวจสอบและลบกฎซ้ำ หรือรวมกฎที่คล้ายกัน
• จำกัดกฎให้กับช่วงเฉพาะแทนชีตทั้งหมดหรือคอลัมน์ทั้งหมด
  • ตรวจสอบคอลัมน์ Applies To สำหรับแต่ละกฎ
  • แทนที่ช่วงคอลัมน์ทั้งหมด เช่น =$A:$M ด้วยช่วงที่จำกัด เช่น =$A$2:$M$5000

• ถ้าข้อมูลของคุณเติบโตแบบไดนามิก ให้ใช้ Excel Table การจัดรูปแบบตามเงื่อนไขที่นำไปใช้กับคอลัมน์ตารางจะคงอยู่ในขนาดจริงของตารางโดยอัตโนมัติ
• หลีกเลี่ยง กฎที่อิงสูตร เมื่อเป็นไปได้ ใช้ตัวเลือกในตัว เช่น Greater Than หรือ Duplicate สำหรับการประมวลผลที่เร็วกว่า
  • ตัวอย่าง กฎเช่น =COUNTIF($B:$B,A1)>1 ถูกประเมินทีละเซลล์ และอาจหนักกว่ากฎที่อิงค่าอย่างมีนัยสำคัญ

• เพื่อล้างกฎทั้งหมด:
  • ไปที่แท็บ Home >> เลือก Conditional Formatting >> เลือก Clear Rules >> เลือก Clear Rules from Entire Sheet

กับดักทั่วไป: การคัดลอกและวางเซลล์ที่มี Conditional Formatting อยู่แล้ว เป็นหนึ่งในวิธีที่พบบ่อยที่สุดที่กฎทำซ้ำโดยไม่ได้ตั้งใจ ตามเวลา เวิร์กบุ๊กสามารถสะสมกฎซ้ำหลายสิบกฎที่นำไปใช้กับช่วงที่แตกต่างกันเล็กน้อย ตรวจสอบและรวมกฎเป็นระยะๆ ไม่แปลกที่จะพบกฎซ้ำ 200 กฎขึ้นไปในเวิร์กบุ๊กที่โตเต็มที่

พิจารณาลดความซับซ้อนของตรรกะกฎ หรือย้ายตรรกะซับซ้อนไปยังคอลัมน์ช่วย แล้วจัดรูปแบบจากผลลัพธ์เหล่านั้น การตรวจสอบและลดจำนวนกฎสามารถปรับปรุงการตอบสนองของเวิร์กบุ๊กได้อย่างมีนัยสำคัญ

4. ฟังก์ชัน Volatile ในสูตร – กระตุ้นการคำนวณซ้ำเต็มรูปแบบทุกการแก้ไข

ฟังก์ชัน Excel บางตัวจัดอยู่ในประเภท Volatile หมายถึงมันคำนวณซ้ำทุกครั้งที่อะไรก็ตามในเวิร์กบุ๊กเปลี่ยนแปลง ไม่ว่าอินพุตของมันจะเปลี่ยนหรือไม่ ฟังก์ชันอย่าง TODAY(), NOW(), RAND(), OFFSET() และ INDIRECT() ล้วนอยู่ในประเภทนี้

ผู้กระทำผิดทั่วไป:

• NOW() และ TODAY(): อัปเดตในทุกการคำนวณซ้ำ
• RAND() และ RANDBETWEEN(): สร้างค่าใหม่ตลอดเวลา
• OFFSET(): พบบ่อยในสูตรช่วงไดนามิก
• INDIRECT(): ยืดหยุ่นแต่ช้ามากในระยะกว้าง
• CELL() และ INFO(): นادرًا ที่ต้องการ และมักมีค่าใช้จ่ายสูง

ทำไมมันช้า:

ฟังก์ชันเหล่านี้สามารถบังคับให้คำนวณซ้ำซ้ำๆ แม้สำหรับการแก้ไขเล็กน้อยที่ดูไม่เกี่ยวข้อง นั่นทำให้พวกเขาอันตรายโดยเฉพาะในเวิร์กบุ๊กที่กดดันจากโซ่สูตรขนาดใหญ่อยู่แล้ว สูตร INDIRECT() เดียวที่อ้างอิงช่วง 10,000 แถว คัดลอกข้าม 50 คอลัมน์ สามารถทำให้เวิร์กบุ๊กช้าอย่างเด็ดขาดได้ด้วยตัวมันเอง

วิธีแก้:

• ไปที่แท็บ Formulas >> เลือก Show Formulas (Ctrl + `)

• กด Ctrl + F เพื่อค้นหาฟังก์ชัน Volatile ในสูตร
• แทนที่เมื่อเป็นไปได้:
  • ใช้วันที่คงที่แทน TODAY()
  • แทนที่ช่วงไดนามิกแบบ OFFSET() ด้วยการอ้างอิงตารางที่มีโครงสร้าง เช่น Table1[Column]
  • แทนที่ INDIRECT() ด้วยการอ้างอิงโดยตรงหรือ INDEX() เมื่อช่วงไม่จำเป็นต้องขับเคลื่อนด้วยข้อความ

• ถ้าหลีกเลี่ยงไม่ได้ ให้แยกไว้ในเซลล์เดียวและอ้างอิงเซลล์นั้นที่อื่นๆ
• สำหรับ RAND() พิจารณาสร้างค่าครั้งเดียวและวางเป็นตัวเลขคงที่
  • Copy >> Paste Special >> Values

การลดสูตร Volatile สามารถป้องกันการคำนวณซ้ำตลอดเวลาและทำให้เวิร์กบุ๊กตอบสนองได้ดีขึ้น

5. การจัดรูปแบบที่ไม่ได้ใช้ในเวิร์กชีตทั้งหมด

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

ทำไมมันช้า:

Excel อาจติดตามการจัดรูปแบบสำหรับล้านๆ เซลล์ ซึ่งเพิ่มการใช้หน่วยความจำและช้าการบันทึกและโหลด

วิธีแก้:

• ระบุพื้นที่ที่จัดรูปแบบ:
  • กด Ctrl + End เพื่อกระโดดไปเซลล์ที่ใช้งานล่าสุด Excel รวมเซลล์ที่จัดรูปแบบไว้ที่นี่ แม้จะว่างเปล่า
• เลือกแถวและคอลัมน์ที่ไม่ได้ใช้เกินข้อมูลของคุณ
  • คลิกขวาแถวที่อยู่ต่ำกว่าแถวข้อมูลสุดท้าย และเลือก Delete
• ล้างการจัดรูปแบบ:
  • เลือกช่วงที่ไม่ได้ใช้
  • ไปที่แท็บ Home >> เลือก Editing >> เลือก Clear >> เลือก Clear Formats

• เพื่อรีเซ็ต Used Range ของชีต ให้บันทึก ปิด และเปิดเวิร์กบุ๊กใหม่หลังจากลบแถวและคอลัมน์เกิน
• สำหรับหลายชีต ใช้ VBA หากคุณคล่อง:
  • กด Alt + F11 แทรกโมดูล และรันโค้ดเพื่อลบการจัดรูปแบบจากพื้นที่ไม่ได้ใช้

ActiveSheet.UsedRange.ClearFormats

การลบการจัดรูปแบบที่ไม่ได้ใช้สามารถลดขนาดไฟล์ได้อย่างมีนัยสำคัญ และเร่งการเปิดและบันทึก

6. ลิงก์ภายนอกและการเชื่อมต่อข้อมูล

ลิงก์ไปยังเวิร์กบุ๊กอื่นหรือแหล่งภายนอก เช่น ฐานข้อมูล สามารถรีเฟรชข้อมูลอัตโนมัติและสืบค้นข้อมูลในพื้นหลัง

ทำไมมันช้า:

เวิร์กบุ๊กที่ลิงก์กับไฟล์อื่นบังคับให้ Excel ตรวจสอบการอ้างอิงภายนอก รีเฟรชการเชื่อมต่อ และคำนวณการขึ้นอยู่กับกัน สูตรภายนอก เช่น ='[Sales2024.xlsx]Sheet1'!A2 สามารถช้าประสิทธิภาพได้อย่างมาก หากไฟล์ที่ลิงก์ใหญ่หรือจัดเก็บบน Network Drive ลิงก์ที่เสียหายหรือช้าก็สามารถทำให้ Timeout และการรีเฟรชอัตโนมัติสามารถรบกวนงานของคุณในไฟล์ใหญ่

วิธีแก้:

• ไปที่แท็บ Data >> เลือก Queries & Connections >> เลือก Edit Links
• ยกเลิกลิงก์ที่ไม่จำเป็นโดยคลิก Break All

• สำหรับการเชื่อมต่อข้อมูล:
  • ไปที่แท็บ Data >> เลือก Queries & Connections
  • คลิกขวา Query >> ยกเลิกติ๊ก Enable background refresh

• แปลงข้อมูลภายนอกเป็นค่าคงที่:
  • รีเฟรชครั้งเดียว แล้ว Copy >> Paste Special >> Values
• หลีกเลี่ยงการลิงก์ไป Network Drive เมื่อเป็นไปได้ คัดลอกข้อมูลต้นทางมาในเครื่องแทน

การยกเลิกลิงก์ที่ไม่จำเป็นสามารถกำจัดการค้างแบบสุ่ม ได้โดยเฉพาะในสภาพแวดล้อมร่วมกัน

7. การตั้งค่าการบันทึกและเปิด – ทำให้ไฟล์ใหญ่รู้สึกหนักกว่าจริง

ไม่ใช่ทุกความช้าที่เกิดขณะแก้ไข บางครั้งความหงุดหงิดจริงๆ เห็นตอนเปิด บันทึก หรือปิดเวิร์กบุ๊ก นั่นมักชี้ไปที่การตั้งค่าการจัดการไฟล์มากกว่าตรรกะสูตร

ผู้กระทำผิดทั่วไป: มีหลายอย่างที่มักมีส่วนทำให้เกิด:

• การบันทึก AutoRecover บ่อยเกินไป
• ลิงก์ภายนอกอัปเดตตอนเปิด
• Named ranges เก่าและวัตถุซ่อน
• Compatibility mode ในไฟล์ .xls เก่า
• การเลือกไฟล์รูปแบบที่ไม่มีประสิทธิภาพ

ทำไมมันช้า:

เวิร์กบุ๊กใหญ่ใช้เวลานานในการเขียนดิสก์ หาก Excel ยังสร้างสแนปชอท Recovery บ่อย ตรวจสอบลิงก์ภายนอก หรือรักษาพฤติกรรมความเข้ากันได้เก่า เวลาเปิดและบันทึกจะกลายเป็นเรื่องน่าหงุดหงิด

วิธีแก้:

• ตรวจสอบความถี่ AutoRecover:
  • ไปที่แท็บ File >> เลือก Options >> เลือก Save
  • ถ้า AutoRecover ตั้งกระชับเกินไป Excel อาจหยุดพักบ่อยกว่าจำเป็นขณะคุณทำงานในไฟล์ใหญ่มาก
• ตรวจสอบลิงก์ภายนอก หากเวิร์กบุ๊กของคุณมีไฟล์ลิงก์ ให้ตัดสินใจว่าลิงก์เหล่านั้นยังต้องอัปเดตอัตโนมัติหรือไม่
• ตรวจสอบรูปแบบไฟล์ หากคุณยังทำงานใน .xls คุณเกือบแน่นอนกำลังทำให้ Excel ทำงานหนักกว่าจำเป็น
• สำหรับเวิร์กบุ๊กภายในที่ใหญ่ พิจารณาบันทึกเป็น .xlsb แทน .xlsx รูปแบบไบนารีมัก:
  • เปิดเร็วกว่า
  • บันทึกเร็วกว่า
  • ใช้พื้นที่ดิสก์น้อยกว่า
  • จัดการเวิร์กบุ๊กขนาดใหญ่ได้มีประสิทธิภาพมากกว่า

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

เคล็ดลับสุดท้ายสำหรับประสิทธิภาพสูงสุด

• ติดตามเวลาคำนวณซ้ำด้วย Formulas > Calculation > Calculate Now ก่อนและหลังการเปลี่ยนแปลง
• ใช้ File > Info > Check for Issues > Inspect Document เพื่อลบข้อมูลซ่อน เช่น ข้อมูลส่วนตัวหรือสไตล์ที่ไม่ได้ใช้
• สำหรับไฟล์ใหญ่สุดๆ พิจารณาแบ่งเวิร์กบุ๊กเป็นหลายไฟล์ หรือใช้ Power BI สำหรับการวิเคราะห์
• ทดสอบการเปลี่ยนแปลงบนสำเนาของเวิร์กบุ๊กก่อน เพื่อหลีกเลี่ยงการสูญเสียข้อมูล

สรุป

นี่คือ 7 การตั้งค่า Excel ที่อาจลดความเร็วเวิร์กบุ๊กขนาดใหญ่ของคุณลงในลับ เมื่อ Excel ช้า หลายคนเริ่มเขียนสูตรใหม่ทันที นั่นไม่ใช่การเคลื่อนไหวแรกที่ดีที่สุดเสมอไป ประสิทธิภาพเวิร์กบุ๊กขนาดใหญ่มักถูกกำหนดโดยการตั้งค่าที่นั่งเงียบๆ ในพื้นหลัง: พฤติกรรมการคำนวณ Add-ins ขอบเขตการจัดรูปแบบตามเงื่อนไข ลิงก์ภายนอก และตัวเลือกการบันทึก สิ่งเหล่านี้แทบไม่สำคัญในไฟล์เล็ก ในไฟล์ใหญ่ พวกเขาสำคัญมาก นั่นคือเหตุผลว่าการปรับแต่งประสิทธิภาพใน Excel ไม่ได้เกี่ยวกับแค่สูตรที่ฉลาดกว่า มันยังเกี่ยวกับการกำจัดค่าใช้จ่ายเงียบๆ ด้วยการแก้ไขการตั้งค่าเหล่านี้ คุณสามารถเปลี่ยนสเปรดชีทที่ช้าให้กลายเป็นเครื่องมือที่มีประสิทธิภาพมากขึ้น