วิธีเร่งความเร็วมาโคร LibreOffice Calc เมื่อใช้งานกับชุดข้อมูลขนาดใหญ่

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

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

ณ เดือนตุลาคม 2026 เอกสาร SDK ของ LibreOffice ฉบับปัจจุบันระบุอินเทอร์เฟซสเปรดชีตที่ใช้ในเอกสารอ้างอิง API ของ LibreOffice 26.8 โดยเมธอดหลักเป็นอินเทอร์เฟซ UNO ที่ใช้งานมานานแล้ว ไม่ใช่คุณสมบัติเพิ่มประสิทธิภาพที่เพิ่งเพิ่มเข้ามาใหม่

1. วัดค่ามาโครก่อนทำการเปลี่ยนแปลง

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

Dim t0 As Long, elapsed As Long

t0 = GetSystemTicks()

' Run the work you want to measure here.

elapsed = GetSystemTicks() - t0
MsgBox "Elapsed ticks: " & elapsed

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

ผลลัพธ์ที่ดีไม่ได้หมายความถึงแค่ตัวเลขเวลาที่ลดลงเท่านั้น ตรวจสอบให้แน่ใจว่าจำนวนแถว ผลรวม สูตร ค่าข้อความ วันที่ และช่วงผลลัพธ์ใดๆ ยังคงตรงกับค่าพื้นฐาน

แหล่งอ้างอิงอย่างเป็นทางการ: คู่มือช่วยเหลือของ LibreOffice: ฟังก์ชัน GetSystemTicks

2. ลบการอ่านและการเขียนแบบเซลล์ต่อเซลล์ออกก่อน

หากมาโครวนลูปผ่านช่วงข้อมูลขนาดใหญ่และเรียกใช้เมธอดซ้ำๆ เช่นgetCellByPosition()`move`, รูปแบบดังกล่าวมักจะเป็นจุดแรกที่ควรตรวจสอบ API ของ UNO มีเมธอด `move` XCellRangeData.getDataArray()และ ` setDataArray()move` โดยเฉพาะสำหรับการย้ายช่วงเซลล์ทั้งหมดเป็นอาร์เรย์

API อย่างเป็นทางการระบุว่าฟังก์ชันนี้getDataArray()จะส่งคืนลำดับของแถว โดยแต่ละแถวจะมีคอลัมน์ ค่าที่ส่งคืนจะเป็นสตริงหรือเลขฐานสอง นอกจากนี้setDataArray()ยังเขียนอาร์เรย์กลับไปยังช่วงที่กำหนด และมิติของอาร์เรย์ต้องตรงกับช่วงนั้นด้วย

ตัวอย่างเช่น สมมติว่าคอลัมน์ A ถึง E มีข้อมูลที่มาโครของคุณแปลง แทนที่จะอ่านแต่ละเซลล์ทีละเซลล์ ให้เลือกช่วงที่ต้องการเพียงครั้งเดียว:

Dim doc As Object
Dim sheet As Object
Dim sourceRange As Object
Dim data As Variant

doc = ThisComponent
sheet = doc.Sheets.getByName("Data")

' Example only: A2:E50001.
sourceRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)
data = sourceRange.getDataArray()

ประมวลผลdataในหน่วยความจำพื้นฐาน จากนั้นเขียนอาร์เรย์ผลลัพธ์ไปยังช่วงปลายทางที่ตรงกันในขั้นตอนเดียว วิธีนี้ช่วยลดจำนวนการเรียกใช้ UNO ลงอย่างมาก เมื่อเทียบกับการอ่านและเขียนแต่ละครั้งนับพันครั้ง

เอกสารอ้างอิงอย่างเป็นทางการ: LibreOffice SDK: XCellRangeDataและLibreOffice SDK: XCellRange

3. ประมวลผลเฉพาะช่วงข้อมูลที่คุณต้องการใช้งานจริงเท่านั้น

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

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

สิ่งนี้มีความสำคัญอย่างยิ่ง โดยเฉพาะอย่างยิ่งเมื่อเวิร์กชีตมีการจัดรูปแบบที่อยู่ต่ำกว่าข้อมูลจริงมาก หรือเมื่อเนื้อหาเก่าได้ขยายพื้นที่ใช้งานที่เห็นได้ชัดออกไป อาร์เรย์ขนาดใหญ่ในหน่วยความจำใช้ RAM มาก และการจัดสรรขนาดใหญ่เกินไปเพียงครั้งเดียวอาจแย่กว่าการประมวลผลบล็อกขนาดปานกลาง

เมื่อใดควรเปลี่ยนไปใช้การแบ่งข้อมูลเป็นส่วนๆ (chunking)

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

4. ระงับการแจ้งเตือนการอัปเดตหน้าจอระหว่างการทำงานหนัก

เอกสาร Calc ใช้XModelส่วนต่อประสานของ LibreOffice โดยเมธอดของมันlockControllers()จะระงับการแจ้งเตือนตัวควบคุมบางอย่างที่ใช้สำหรับการอัปเดตการแสดงผลชั่วคราว แล้วจึงunlockControllers()เรียกใช้งานอีกครั้ง ซึ่งจะช่วยลดการทำงานด้านภาพที่ไม่จำเป็นในขณะที่มาโครทำการเปลี่ยนแปลงจำนวนมาก

Dim doc As Object
doc = ThisComponent

doc.lockControllers()

' Perform bulk changes here.

doc.unlockControllers()

API อย่างเป็นทางการระบุไว้อย่างชัดเจนว่า การล็อกและการปลดล็อกคอนโทรลเลอร์ต้องจับคู่กัน นั่นหมายความว่ามาโครสำหรับการใช้งานจริงควรมีการจัดการข้อผิดพลาดที่รับประกันว่าunlockControllers()จะทำงานได้แม้ว่าการประมวลผลจะล้มเหลว การปล่อยให้เอกสารถูกล็อกไม่ใช่การเพิ่มประสิทธิภาพ แต่เป็นเส้นทางการล้างข้อมูลที่ผิดพลาด

เอกสารอ้างอิงอย่างเป็นทางการ: LibreOffice SDK: XModel

5. ควบคุมการคำนวณซ้ำอัตโนมัติเมื่อสูตรมีการใช้งานมากเกินไปในระหว่างการประมวลผล

หากมาโครทำการเปลี่ยนแปลงเซลล์ที่ใช้เป็นข้อมูลป้อนเข้าสูตรจำนวนมาก ซ้ำๆ Calc อาจใช้เวลานานในการคำนวณใหม่ในขณะที่มาโครยังคงอัปเดตค่าระหว่างกลางอยู่ เอกสารสเปรดชีตแสดงXCalculatableส่วนติดต่อผู้ใช้ รวมถึงisAutomaticCalculationEnabled(), enableAutomaticCalculation(), calculate(), และcalculateAll()

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

Dim doc As Object
Dim wasAutoCalc As Boolean

doc = ThisComponent
wasAutoCalc = doc.isAutomaticCalculationEnabled()

doc.enableAutomaticCalculation(False)

' Perform bulk updates.

doc.enableAutomaticCalculation(wasAutoCalc)

If wasAutoCalc Then
    doc.calculate()
End If

calculate()ฟังก์ชันนี้จะคำนวณเซลล์ที่เปลี่ยนแปลงใหม่calculateAll()ทั้งหมด ซึ่งอาจใช้ทรัพยากรมาก ดังนั้นอย่าเรียกใช้ฟังก์ชันนี้โดยอัตโนมัติเพียงเพราะมาโครทำงานเสร็จแล้ว ควรใช้การคำนวณใหม่ทั้งหมดเฉพาะเมื่อตรรกะของเวิร์กบุ๊กต้องการจริงๆ เท่านั้น

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

ข้อมูลอ้างอิงอย่างเป็นทางการ: LibreOffice SDK: XCalculatable

6. ผสานรวมการปรับปรุงประสิทธิภาพอย่างปลอดภัย

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

รูปแบบการจัดระเบียบไฟล์ที่ใช้งานได้จริงใน LibreOffice Basic มีลักษณะดังนี้:

Sub ProcessLargeData
    Dim doc As Object
    Dim sheet As Object
    Dim dataRange As Object
    Dim data As Variant
    Dim wasAutoCalc As Boolean

    On Error GoTo CleanUp

    doc = ThisComponent
    sheet = doc.Sheets.getByName("Data")
    dataRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)

    wasAutoCalc = doc.isAutomaticCalculationEnabled()

    doc.lockControllers()
    doc.enableAutomaticCalculation(False)

    data = dataRange.getDataArray()

    ' Transform data in memory here.
    ' Build an output array with dimensions matching its target range.

CleanUp:
    doc.enableAutomaticCalculation(wasAutoCalc)

    If doc.hasControllersLocked() Then
        doc.unlockControllers()
    End If

    If wasAutoCalc Then
        doc.calculate()
    End If
End Sub

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

7. พิจารณาฟังก์ชันในตัวของ Calc ก่อนที่จะเขียนฟังก์ชันเหล่านั้นขึ้นมาใหม่

LibreOffice Basic สามารถเรียกใช้ฟังก์ชันของ Calc ผ่านทางcom.sun.star.sheet.FunctionAccessบริการได้ หากมาโครของคุณกำลังใช้งานการค้นหา การรวม หรือการดำเนินการอื่นๆ ที่ Calc มีให้แล้ว การใช้ฟังก์ชันในตัวอาจช่วยลดความซับซ้อนของโค้ดและลดการวนลูปแบบกำหนดเองได้

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

แหล่งอ้างอิงอย่างเป็นทางการ: คู่มือช่วยเหลือของ LibreOffice: การใช้ฟังก์ชัน Calc ในมาโคร

จะประเมินได้อย่างไรว่าการปรับปรุงประสิทธิภาพนั้นดีพอแล้ว

ข้อผิดพลาดทั่วไปที่ทำให้ประโยชน์ที่ได้รับหมดไป

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

เทคนิคเหล่านี้หยุดให้ความช่วยเหลือเมื่อใด

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

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

ลำดับการปรับแต่งที่แนะนำ

1. วัดค่ามาโครปัจจุบันด้วยชุดข้อมูลที่สามารถสร้างซ้ำได้
2. แทนที่การอ่านและเขียนเซลล์ซ้ำๆ ด้วยอาร์เรย์ช่วง (range array)
3. จำกัดการประมวลผลเฉพาะแถวและคอลัมน์ที่จำเป็นเท่านั้น
4. ให้ใช้การแบ่งข้อมูลเป็นส่วนๆ หากอาร์เรย์ขนาดใหญ่เกินไปจนไม่สามารถใช้หน่วยความจำได้อย่างสะดวกสบาย
5. ล็อกตัวควบคุมระหว่างการผลิตจำนวนมาก เพื่อรับประกันการทำความสะอาดอย่างมีประสิทธิภาพ
6. ระงับการคำนวณอัตโนมัติชั่วคราวเมื่อพบว่าการคำนวณสูตรซ้ำๆ เป็นอุปสรรคสำคัญ
7. คำนวณใหม่เฉพาะส่วนที่จำเป็นเท่านั้น คืนค่าสถานะของแอปพลิเคชัน และตรวจสอบผลลัพธ์
8. วัดผลใหม่ หากเวลาในการทำงานยังคงถูกครอบงำด้วยการคำนวณมากกว่าการเข้าถึง UNO ให้ปรับปรุงอัลกอริธึมแทน

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