ในขณะที่ทำงานกับชุดข้อมูลขนาดใหญ่ ความเร็วสเปรดชีตจะลดลง การจัดการสมุดงาน Excel ที่มีแถวหลายพันแถวและการคำนวณที่ซับซ้อนเป็นเรื่องท้าทาย คุณต้องเพิ่มประสิทธิภาพสเปรดชีตให้ทำงานกับชุดข้อมูลขนาดใหญ่ได้ การเพิ่มประสิทธิภาพสเปรดชีตของคุณช่วยให้มั่นใจได้ถึงประสิทธิภาพที่ราบรื่นยิ่งขึ้น เวลาโหลดเร็วขึ้น และเพิ่มประสิทธิภาพ
ในบทความนี้ เราจะอธิบายวิธีที่คุณสามารถเพิ่มประสิทธิภาพสเปรดชีตเพื่อปรับปรุงประสิทธิภาพของสมุดงานขนาดใหญ่
1. ลบข้อมูลที่ไม่จำเป็น
รักษาเวิร์กบุ๊กของคุณให้สะอาด และลบแถว คอลัมน์ และเวิร์กชีตที่ไม่ได้ใช้และไม่ต้องการ เก็บเฉพาะข้อมูลที่คุณต้องการ
การลบแถวและคอลัมน์ว่าง:
ชุดข้อมูลอาจมีแถวและคอลัมน์ว่างโดยไม่จำเป็น คุณสามารถใช้คุณลักษณะ Go To Special เพื่อระบุและลบได้อย่างรวดเร็ว
- เลือกช่วงข้อมูลของคุณ
- กด Ctrl + G>> เลือก พิเศษ>> เลือก ช่องว่าง .
- ลบ แถวหรือคอลัมน์ที่ไฮไลต์
ซึ่งจะช่วยลดขนาดไฟล์และปรับปรุงความเร็วในการคำนวณใหม่
2. ปรับสูตรให้เหมาะสม
2.1. แทนที่ฟังก์ชันผันผวน
ฟังก์ชันผันผวนจะคำนวณใหม่ทุกครั้งที่เปิดเวิร์กบุ๊กหรือเปลี่ยนแปลง แทนที่ฟังก์ชันระเหยด้วยฟังก์ชันที่ไม่ลบเลือนหากเป็นไปได้
นี่คือรายการฟังก์ชันระเหยทั่วไป:
- ตอนนี้()
- วันนี้()
- แรนด์()
- ออฟเซ็ต()
- ทางอ้อม()
แทนที่ทันทีด้วยค่าคงที่
แทนที่จะใช้ฟังก์ชัน NOW เพื่อแสดงวันที่และเวลาปัจจุบัน ให้ใช้แป้นพิมพ์ลัด ฟังก์ชัน NOW จะคำนวณใหม่ทุกครั้งที่มีการเปลี่ยนแปลง
- คุณสามารถใช้ Ctrl +; เพื่อแทรกวันที่ปัจจุบันเป็นค่าคงที่
2.1. ลดความซับซ้อนของสูตรอาร์เรย์
คุณจะต้องใช้สูตรอาร์เรย์อย่างมีประสิทธิภาพ แม้ว่าสูตรอาร์เรย์จะมีประสิทธิภาพแต่เวิร์กบุ๊กทำงานช้าลง จำกัดการใช้สูตรอาร์เรย์ด้วยความช่วยเหลือของคอลัมน์ตัวช่วย
แทนที่จะใช้ SUM และ IF แยกกัน คุณสามารถใช้ฟังก์ชัน SUMIF ได้
สูตรอาร์เรย์:
=SUM(IF(A1:A1000>10, B1:B1000, 0))
สำหรับสเปรดชีตขนาดใหญ่ SUMIF เป็นผู้ชนะที่ชัดเจนในแง่ของประสิทธิภาพและความเรียบง่าย ช่วยลดการใช้ทรัพยากร หลีกเลี่ยงความซับซ้อนที่ไม่จำเป็น และรักษาสมุดงานของคุณให้สะอาด
=SUMIF(A1:A1000, ">10", B1:B1000)
หากไม่มี SUMIF คุณสามารถใช้คอลัมน์ตัวช่วยได้
แทนที่ด้วยคอลัมน์ตัวช่วยด้วยการเพิ่มประสิทธิภาพ:
- แทรกคอลัมน์ใหม่และเพิ่ม:
- จากนั้น ใช้สูตรต่อไปนี้:
ซึ่งจะแบ่งสูตรอาร์เรย์ออกเป็นขั้นตอนเล็กๆ และใช้ทรัพยากรน้อยลง
ลดขอบเขตการอ้างอิง:
- แทนที่จะอ้างอิงทั้งคอลัมน์>> A:A.
- คุณสามารถใช้ช่วงเซลล์เฉพาะหรือช่วงที่กำหนด>> A1:A1000.
3. ใช้การจัดรูปแบบตามเงื่อนไขเท่าที่จำเป็น
แทนที่จะใช้การจัดรูปแบบกับทั้งคอลัมน์ ให้จำกัดให้อยู่ในช่วงข้อมูลจริง:
- เลือกเฉพาะช่วงข้อมูล (เช่น A1:A100)
- ใช้กฎการจัดรูปแบบตามเงื่อนไขของคุณที่นี่ หลีกเลี่ยงค่าใช้จ่ายที่ไม่จำเป็น
4. ลดลิงก์ภายนอก
แทนที่จะนำเข้าข้อมูลจากเวิร์กบุ๊กภายนอกด้วยการเชื่อมโยง คุณสามารถใช้ Power Query เพื่อนำเข้าและแปลงข้อมูลภายในเวิร์กบุ๊กของคุณได้:
- ไปที่แท็บข้อมูล>> คลิก รับข้อมูล>> เลือก จากไฟล์>> เลือก จากสมุดงาน .
- โหลดข้อมูลที่จำเป็นลงในไฟล์ปัจจุบันของคุณ
ซึ่งช่วยลดการพึ่งพาไฟล์ภายนอกและเพิ่มความเร็วในการเรียกข้อมูล
5. ปรับโหมดการคำนวณ
หากคุณกำลังทำงานกับชุดข้อมูลขนาดเล็ก คุณสามารถใช้ตัวเลือกการคำนวณอัตโนมัติได้ แต่เมื่อคุณจัดการข้อมูลขนาดใหญ่ด้วยสูตรที่ซับซ้อน ให้ใช้การตั้งค่าการคำนวณใหม่ด้วยตนเอง
สำหรับเวิร์กบุ๊กที่มีหลายสูตร:
- ไปที่ไฟล์ แท็บ>> เลือก ตัวเลือก .
- เลือก สูตร>> จาก ตัวเลือกการคำนวณ>> เลือก กำหนดเอง .
- คลิก ตกลง .
- กด F9 เพื่อคำนวณใหม่เมื่อจำเป็นเท่านั้น
ซึ่งช่วยให้คุณทำงานบนชีตของคุณได้โดยไม่เกิดความล่าช้าซึ่งเกิดจากการคำนวณใหม่อัตโนมัติ
ปิดการคำนวณซ้ำ
คุณยังสามารถปิดใช้งานการคำนวณซ้ำซึ่งอาจทำให้เกิดการคำนวณใหม่โดยไม่จำเป็น
- ปิดใช้งาน เปิดใช้งานการคำนวณซ้ำ .
- คลิก ตกลง .
6. ใช้ตารางข้อมูลและ PivotTable
หากต้องการสร้างสรุปแบบไดนามิก ให้ใช้คุณลักษณะตารางสรุปข้อมูล แทนที่จะคำนวณหรืออัปเดตตารางสรุปด้วยตนเอง
- เลือกข้อมูลของคุณ
- ไปที่ ส่วนแทรก แท็บ>> เลือก PivotTable .
- ลากช่องลงในแถวและคอลัมน์เพื่อสร้างสรุปแบบไดนามิก
ตารางสรุปจะคำนวณผลรวมโดยอัตโนมัติตามรายการฟิลด์ อัปเดตและจัดการชุดข้อมูลขนาดใหญ่ได้อย่างมีประสิทธิภาพเร็วขึ้น
7. เพิ่มประสิทธิภาพการออกแบบสมุดงาน
หากต้องการเพิ่มประสิทธิภาพสมุดงานของคุณ คุณสามารถใช้ Named Ranges เพื่ออ้างอิงถึงช่วงเซลล์แบบไดนามิก แทนที่จะอ้างอิงถึงสูตรทั้งคอลัมน์หรืออาร์เรย์
สมมติว่าคุณกำลังทำงานกับชุดข้อมูลการขายที่เติบโตขึ้นเมื่อเวลาผ่านไป แทนที่จะอ้างอิงถึงช่วงคงที่ เช่น A1:A1000 ให้ใช้ช่วงที่มีชื่อแบบไดนามิก:
- ไปที่ สูตร แท็บ>> เลือก ตัวจัดการชื่อ>> เลือก ใหม่ .
- กำหนดชื่อ:ข้อมูลการขาย .
- หมายถึง: ใส่สูตรต่อไปนี้:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
สูตรนี้จะอัปเดตสูตรหรือแผนภูมิที่มีอยู่โดยอัตโนมัติหากข้อมูลเพิ่มขึ้นหรือถูกลบออก
8. บีบอัดขนาดไฟล์
หากต้องการเพิ่มประสิทธิภาพไฟล์ คุณสามารถลดขนาดรูปภาพและขนาดไฟล์ภายนอกได้
หากสมุดงานของคุณมีรูปภาพ:
- เลือกรูปภาพ
- ไปที่ รูปแบบรูปภาพ แท็บ>> เลือก บีบอัดรูปภาพ .
- เลือกตัวเลือกความละเอียดที่ต่ำกว่า (เช่น เว็บ หรือ อีเมล ) เพื่อลดขนาดไฟล์โดยไม่กระทบต่อรายละเอียดที่จำเป็น
9. ใช้เครื่องมือขั้นสูง
ใช้ Power Query: ตัวแก้ไข Power Query มีฟีเจอร์ในตัวที่หลากหลายเพื่อนำเข้าและล้างข้อมูล ซึ่งช่วยลดความจำเป็นในการใช้สูตรที่ซับซ้อน
เปิดใช้งานโมเดลข้อมูล: สำหรับชุดข้อมูลขนาดใหญ่ คุณสามารถใช้โมเดลข้อมูลในตัวของ Excel เพื่อสร้างความสัมพันธ์และดำเนินการคำนวณได้
ใช้ VBA สำหรับงานที่เกิดซ้ำ: ทำกระบวนการที่ซ้ำกันโดยอัตโนมัติด้วย VBA เพื่อลดความซับซ้อนของสมุดงาน
10. ปิดการใช้งาน Add-In ที่ไม่จำเป็น
อย่าเปิดใช้งาน Add-in หากคุณไม่ต้องการเป็นประจำ ทุกครั้งที่คุณเปิด Excel โปรแกรมจะติดตั้ง Add-in ทั้งหมด
- ไปที่ไฟล์ แท็บ>> เลือก ตัวเลือก .
- จาก ตัวเลือก Excel>> เลือก ส่วนเสริม
- ระบุและปิดการใช้งาน Add-in ที่ไม่จำเป็นภายใต้ จัดการ COM Add-in .
- ลบ โปรแกรมเสริม ที่ไม่ได้ใช้ .
ซึ่งจะทำให้ทรัพยากรมีอิสระมากขึ้น โดยเฉพาะอย่างยิ่งเมื่อทำงานกับสเปรดชีตขนาดใหญ่
11. การบำรุงรักษาตามปกติ
หากต้องการล้างข้อผิดพลาดและลิงก์ที่ใช้งานไม่ได้ให้ใช้คุณลักษณะค้นหาและแทนที่ คุณสามารถลบความผิดปกติและข้อผิดพลาดได้โดยใช้คุณสมบัตินี้
หากต้องการใช้ค้นหาและแทนที่เพื่อล้างข้อผิดพลาด:
- ไปที่หน้าแรก แท็บ>> จาก ค้นหาและเลือก >> เลือก ค้นหา/แทนที่ .
- ประเภท ชื่อข้อผิดพลาดใน ค้นหา กล่อง
- แทนที่ข้อผิดพลาดทั้งหมดหรือแก้ไขลิงก์ที่ทำให้เกิดข้อผิดพลาด
หลังจากทำการเพิ่มประสิทธิภาพแล้ว ให้ทดสอบสมุดงานเพื่อให้แน่ใจว่าฟังก์ชันการทำงานไม่ได้รับผลกระทบ
บทสรุป
การเพิ่มประสิทธิภาพสเปรดชีตเป็นเรื่องง่าย สมุดงานขนาดใหญ่ไม่จำเป็นต้องซับซ้อน คุณสามารถปฏิบัติตามเทคนิคดังกล่าวพร้อมขั้นตอนการปฏิบัติเพื่อปรับปรุงประสิทธิภาพของสมุดงานขนาดใหญ่ ไม่ว่าจะเป็นการเพิ่มประสิทธิภาพสูตร การลดขนาดข้อมูล หรือการใช้เครื่องมือ Excel ขั้นสูง วิธีการเหล่านี้จะช่วยให้คุณเรียกใช้สเปรดชีตได้อย่างราบรื่น
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!