ไฟล์ Excel ขนาดใหญ่นั้นมีได้หลายสาเหตุ และมีวิธีแก้ได้หลายวิธีเช่นกัน ในบทความนี้ผมจะขอนำเสนอหลายๆ วิธีที่จะช่วยลดขนาดไฟล์ Excel ใหญ่ๆ เหล่านั้นได้ครับ
แนวทางลดขนาดไฟล์ Excel#1 :
ลบสิ่งที่ไม่จำเป็นออกซะ โดยเฉพาะ Used Range ที่ไม่ใช้แล้ว
ถ้าบอกว่าจะลดขนาดไฟล์ได้ ให้ “ลบสิ่งที่ไม่จำเป็นออกซะ” อันนี้จะเรียกว่าเทคนิคได้มั้ยนะ 55 ดูจะขวานผ่าซากมากๆ แต่ก็เป็นเรื่องที่ต้องตรวจสอบก่อนอยู่ดี ดังนั้นขอให้ตรวจสอบดังนี้
- ข้อมูลใน Hidden Sheet มีรึเปล่า? ถ้ามี แล้วไม่ใช้ก็ลบซะ
- การใส่ Format ต่างๆ รวมถึง Conditional Format ที่ไม่จำเป็น พวกนี้ก็เปลี่องเนื้อที่นะ ถ้าอยากให้ไฟล์เล็กลงก็ลบออกเถอะ
- การใส่สูตรที่ไม่จำเป็น พวกนี้ถ้าแปลงเป็น Value ก็จะทำให้ไฟล์เล็กลงได้นะ
เทคนิคข้างบนนี่ก็เป็นเรื่องพื้นๆ แต่ตัวที่หลายคนไม่รู้ ก็คือเรื่องของ Used Range ต่างหาก! ซึ่งเกิดขึ้นจากใน Sheet เราอาจเคยมีการใช้งาน Cell นั้นมาก่อน แต่ตอนนี้ไม่ใช้แล้ว ซึ่งเจ้า Used Range นี้อาจทำให้ขนาดของไฟล์ใหญ่โดยไม่จำเป็นได้นะ
ตัวอย่างเรื่อง Used Range
ไฟล์ดั้งเดิมผมมีข้อมูลอยู่เยอะเลย 68615 แถว ดังนี้
ต่อมาผมลองลบข้อมูลทิ้ง ให้มันเหลืออยู่แค่ 10 แถว โดยการกด del บน Keyboard เพื่อลบเอา content เหล่านั้นทิ้งไปซะ
ปรากฎว่าไฟล์ขนาดลดลง แต่ว่ามันก็ยังดูเยอะแปลกๆ
พอกลับไปดูในไฟล์ แล้วกด Ctrl+End (หรือบางเครื่องกด Ctrl+Fn+End) เพื่อให้มันเด้งไป Cell สุดท้ายที่มีการใช้งาน ปรากฎว่า มันดันกลายเป็น Cell K68615 ทั้งๆ ที่มันไม่มีข้อมูลแล้ว (มันควรจะไปประมาณแถวที่สิบไม่ใช่เรอะ!!)
ไอนี่แหละ ที่ทำให้ไฟล์มีขนาดใหญ่ครับ
ถ้าเกิดเราเปลี่ยนวิธี เป็นลบ Cell นั้นทิ้งไปเลย เช่น เลือกแล้ว คลิ๊กขวา Delete Row แล้วลองกด Save ไฟล์ดู แล้วเปิดไฟล์ลงไป จะเห็นว่าขนาดไฟล์เหลือน้อยลงมากๆ
เรื่องนี้จึงเป็นเรื่องสำคัญที่เราจะต้องตระหนักไว้นะครับ เพราะมองด้วยตาเปล่าไม่เห็นซะด้วย
แนวทางลดขนาดไฟล์ Excel#2 :
Save ไฟล์เป็น .xlsb (Excel Binary Workbook)
วิธีนี้เหมาะอย่างยิ่งกับคนที่อยากจะลดขนาดไฟล์แบบรวดเร็ว และไม่อยากจะคิดอะไรมากนัก เพราะวิธีนี้ทำได้ง่ายแสนง่าย นั่นก็คือ ให้ ไปที่ File => Save As แล้วเลือก Save as Type เป็นนามสกุล Excel Binary Workbook (*.xlsb) นั่นเอง
พอลองเปรียบเทียบขนาดไฟล์แล้ว พบว่า ไฟล์เล็กลงเกินครึ่งนึงเลยด้วยซ้ำ!!
แล้ว xlsb มีข้อเสียอะไรมั้ย?
หลายคนคงเริ่มคิดในใจว่า เอ๊ะ! ถ้าไฟล์ xlsb มันขนาดเล็กดีแบบนี้ แล้วค่า default มันจะให้เรา save เป็น .xlsx ไปทำไม? สู้ save เป็น .xlsb ไปให้เลยไม่ดีกว่าเหรอ?
ข้อเสียของ .xlsb คือ
- ไฟล์ xlsx เก็บข้อมูลแบบ xml เป็นวิธีการเก็บแบบมาตรฐานสากล ทำให้ใช้งานร่วมกับเครื่องมืออื่นๆ ได้ดีกว่า xlsb ที่เก็บไฟล์เป็น bin (binary)
- ต้องลอง test ดูว่า xlsb ของเราใช้กับพวก 3rd-party software ได้รึเปล่า
- เคยอ่านเจอในเว็บฝรั่งว่า Power Query ดึงไฟล์ .xlsb ไม่ได้ แต่ไม่จริงนะครับ ผมลองแล้วมันดึงได้นะ
- ดูจากภายนอกจะไม่รู้ว่ามี Macro แฝงอยู่หรือไม่
ถ้าอ่านแล้วรู้สึกว่า เรารับกับข้อเสียเหล่านี้ได้ ก็ save เป็น .xlsb โลดเลยครับ!
Tips : อย่า Save ไฟล์เป็นนามสกุล .xls (2003)
ถ้า save ไฟล์เป็นนามสกุลแบบ xlsx (2007 ขึ้นไป) ไฟล์จะเล็กกว่าแบบ xls (2003) เพราะไฟล์แบบ 2007 จะเหมือนมีการ Zip ไฟล์มาแล้วในตัวครับ
Tips : รู้หรือไม่ว่า ไฟล์ CSV กินที่กว่า ไฟล์ Excel นะ
อันนี้ไม่เกี่ยวกับไฟล์ Excel โดยตรง แต่บางทีเราก็อาจมี Save ไฟล์โดยที่นามสกุลเป็นไฟล์ CSV ซึ่งจะทำให้ขนาดไฟล์ใหญ่กว่าไฟล์ Excel ปกตินะครับ
ไฟล์ csv มันดีกว่าตรงที่ข้อมูลมันเกิน 1 ล้านแถวได้นี่แหละ แต่ข้อเสียคือ ไฟล์ใหญ่ และไม่สามารถมีสูตรหรือ format อะไรได้เลย
แนวทางลดขนาดไฟล์ Excel #3 :
Compress Pictures บีบอัดรูปซะ
บางทีเรามีการเอารูปเข้ามาไว้ใน File Excel ซึ่งรูปนั้นเดิมทีอาจจะเป็นรูปที่มีความละเอียดสูง ทำให้ไฟล์มีขนาดใหญ่
แต่หากว่าเราไม่ได้ต้องการรูปที่มีความละเอียดสูงขนาดนั้น หรือ เรามีการ Crop รูปให้เห็นแค่บางส่วนของรูปก็เพียงพอแล้ว เราก็สามารถใช้วิธี Compress รูปได้ครับ
โดยให้ Double Click ที่รูป หรือเลือกรูปแล้วไปที่ Picture Tool => Compress Picture แล้วอย่าลืมติ๊ก Apply only to this picture ออกด้วย เพื่อที่จะลดขนาดทุกรูปในไฟล์เลย
จากนั้นเลือกความละเอียดที่ต้องการ ถ้าเลือก Email (96 ppi) ก็จะขนาดเล็กสุด แต่ภาพก็จะห่วยสุดด้วย ซึ่งถ้าคิดว่าภาพหยาบเกินไป ก็เลือกซัก 150 ppi ก็ ok ครับ
บางทีเราก็มีการ Crop รูป เอาเฉพาะส่วนที่ต้องการด้วย ซึ่งถ้า Crop แล้ว Compress ขนาดไฟล์จะเล็กลงมาก
ผลลัพธ์ที่ได้ พบว่า ระหว่างภาพปกติดั้งเดิม กับภาพที่ Crop แล้ว แต่ยังไม่ได้มีการ Compress Picture เลย จะมีขนาดไฟล์ใหญ่เท่ากัน ที่ 947 KB
แต่ถ้ามีการ Compress แล้ว ขนาดไฟล์จะลดลงจนเหลือ 99 KB และ 23 KB ตามลำดับ
ข้อเสียของการ Compress Pictures
ข้อเสียชัดๆ เลย คือ ภาพที่ได้จะคุณภาพห่วยลง เพราะเครื่องมือการ Compress ของ Excel มันไม่ได้เทพขนาดที่จะลดขนาดรูปในขณะที่ภาพยังคงดูสวยเช่นเดิมได้ แต่ถ้าเกิดเราใช้เครื่องมืออื่น เช่น Photoshop หรือ เว็บ TinyPNG ในการบีบอัดรูปให้ขนาดเล็กลงก่อนเอาเข้ามาใน Excel มันก็จะได้ภาพที่ขนาดเล็กแต่ยังสวยอยู่ได้ครับ
แนวทางลดขนาดไฟล์ Excel #4 :
แก้ปัญหาไฟล์ใหญ่เพราะ Pivot Table
ก่อนอื่น ต้องอธิบายก่อนว่า ปกติเวลาเราสร้าง PivotTable ขึ้นมาจาก Source Data ใดๆ ก็ตาม Excel จะมีการสร้างสิ่งที่เรียกว่า Pivot Cache ขึ้นมาด้วยโดยอัตโนมัติ
และเวลาที่ PivotTable เอาข้อมูลไปประมวลผล มันจะไม่ได้เอา Source Data ไปใช้ตรงๆ แต่จะใช้สิ่งที่มีใน Pivot Cache ไปคำนวณต่างหาก และนี่คือสาเหตุที่ว่าทำไม Source data เปลี่ยนไปแล้วเราจะต้อง Refresh PivotTable ด้วยเสมอ
ผมพิสูจน์ให้ดูด้วยการสร้าง PivotTable เปล่าๆ ขึ้นมาแล้ว Save as ทันที โดยที่ยังไม่ได้มีการลากข้อมูลอะไรมา Pivot ทั้งสิ้น
ปรากฎว่าขนาดไฟล์ใหญ่ขึ้นมาเกือบ 2 เท่าเลยทีเดียว
วิธีแก้ไขที่ผมจะแนะนำคือ เราจะสั่งให้ Excel ไม่ต้อง Save Pivot Cache เอาไว้ในไฟล์ แต่ให้มัน Refresh ดึงข้อมูลมาใหม่ ตอนเปิดไฟล์ Excel แทน ซึ่งมีวิธีทำดังนี้ครับ
- ให้เราคลิ๊กขวาที่ Pivot Table แล้ว เลือก Pivot Table Options แล้วกดตามรูปเลย
- ที่สำคัญคือให้ติ๊ก Save Source data with file ออกไปซะ (อันนี้แหละ คือ PivotCache)
- แต่ทีนี้ผมขอแนะนำให้ติ๊ก Refresh data when opening the file ด้วย เผื่อเราลืม Refresh นี่ซวยเลยครับ
พอ Save ไฟล์อีกทีก็จะเห็นว่าขนาดลดลงไปเยอะเลยครับ (ทำไมมันเล็กกว่า Original อีกเนี่ย งงจริงๆ 555)
แนวทางลดขนาดไฟล์ Excel # 5 :
ไม่ต้องเอาข้อมูลมาไว้ใน Excel โดยสิ้นเชิง
เวลาเราจะวิเคราะห์ข้อมูล ใน PivotTable จริงๆ แล้วเราสามารถ Pivot ข้อมูลจาก Source Data อื่นที่ไม่อยู่ใน Excel ได้ด้วยนะครับ (จะดึงผ่าน PivotTable เอง หรือจะผ่าน Power Query ก็ได้)
อันนี้เป็นตัวอย่างการใช้ Pivot Table เชื่อมเอง
และไฟล์นั้นสามารถใหญ่กว่า 1 ล้านแถวของ Excel ได้ด้วย (เช่น เชื่อมกับไฟล์ Access) แถมข้อดีอีกอย่างคือ ไฟล์ Excel จะมีขนาดเล็ก เพราะไม่ต้องเก็บ Data จริงๆ ไว้ด้วย
(ถ้า Pivot แล้วอย่าลืมเอา Pivot Cache ออกล่ะ ไม่งั้นก็ใหญ่อีกนะ)
แนวทางลดขนาดไฟล์ Excel # 6 :
ถ้าจะเก็บข้อมูลไว้ใน Excel ลองเอาเข้า Data Model ดูสิ
ถ้าแนวทางที่ 5 ดู Hard core เกินไป ลองพิจารณาลองเอาข้อมูลไว้ใน Data Model ของ Excel ดูสิ เพราะใน Data Model จะมีวิธีเก็บข้อมูลพิเศษ ที่จะเก็บเฉพาะข้อมูลที่ไม่ซ้ำๆ กันเท่านั้น ทำให้ไฟล์มีขนาดเล็กมากครับ
เราสามารถดึงข้อมูลเข้า Power Query ก่อน จากนั้นค่อยเลือก Load to…
แล้วเลือก Pivot Table Report หรือ จะ Only Create Connection ก็ได้ จากนั้นก็ให้ติ๊ก Add to Data Model ด้วย (ถ้าอยากเก็บไฟล์ไว้ใน Excel นะ)
จะเห็นว่าเราสามารถ Pivot Data ได้ และมีการ Load ไฟล์เข้า Data Model ไปแล้ว
จะเห็นว่าไฟล์มีขนาดเล็กลงพอสมควร แต่ไม่ได้เล็กเท่าเอา Data ไว้ข้างนอกแน่นอนอ่ะนะ…
แนวทางอื่นๆ ในการลดขนาดไฟล์ Excel
ใครมีวิธีอื่นๆ ที่น่าสนใจในการลดขนาดไฟล์อีก ก็สามารถ Comment บอกมาได้ครับ คนอื่นๆ จะได้นำเทคนิคเหล่านั้นไปใช้ได้ด้วย สำหรับบทความนี้ผมขอจบเท่านี้ครับ ขอบคุณที่อ่านจนจบนะ ^^
