อยากทราบการทำให้ Excel 2007 ใช้เวลาเปิดให้เร็วกว่านี้ครับ

[ARNONT]

สวัสดีครับอาจารย์ ผมมีเรื่องมารบกวนถามอาจารย์อีกแล้วครับ ตอนนี้ผมได้ทำโปรแกรมสำหรับใช้งานใน Excel 2007ขนาดไฟล์อยู่ที่ 15 MB 1 workbook 10 sheet แยกเป็น DATA 6 sheet เป็น From 3 sheet และเป็น Menu 1 sheet เวลาเปิดใช้งานและการบันทึกข้อมูลแต่ละครั้ง(ทุก 5 นาทีบันทึก1ครั้งAUTO) ใช้เวลาประมาณ 5- 8 นาที
คำถามครับ

1. มีวิธีไหนบ้างทำให้การเปิดและบันทึกโปรแกรม ใช้เวลาน้อยกว่านี้
2. ถ้าเราแยกsheet DATA ไปเป็นอีกไฟล์ จะทำให้ใการเปิดหรือบันทึกข้อมูลหรือใช้งานใดๆใช้เวลาน้่อยลงรึเปล่า
3. การเพิ่มAdd-in บางตัวเข้าไปเช่น Lookup wizardจะทำให้เร็วขึ้นจริงรึเปล่า (ผมลองแล้วไม่มีผลอะไรเลยผมเพิ่มตัวเดียว)

รบกวนด้วยครับ

[snasui]

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

การแยก Data ไปอีกไฟล์ แต่สูตรยังเหมือนเดิมก็ไม่ได้ช่วยอะไรครับ การที่คำนวณช้าในลักษณะนี้หลัก ๆ คิดว่าเนื่องจากการประมวลผลทีเกิดจากสูตรทั้งหลายรวมทั้งทรัพยากรของเครื่องเช่น CPU, RAM ถ้าเครื่องแรงก็คำนวณเร็วกว่าเครื่องไม่แรง นอกจากนี้หากไฟล์ประกอบด้วย Object เช่นภาพ กล่องข้อความ รูปวาดต่าง ๆ ฯลฯ จำนวนมากก็ทำให้เปิดไฟล์ช้าและไฟล์มีขนาดใหญ่ได้เช่นกันครับ

เรื่อง Add-ins การ Lookup ข้อมูลเป็นการใช้ Add-ins ในการสร้างสูตรแบบ Array กรณีเราต้องการหาข้อมูลแบบหลายเงื่อนไข ซึ่งพอมาเป็น 2007 การค้นหาข้อมูลหลายเงื่อนไขสามารถใช้ Sumifs, Countifs มาช่วยได้มากโดยไม่ต้องพึ่งสูตร Array ครับ

[ARNONT]

อยากส่งไฟล์งานให้อาจารย์ช่วยวิเคราะห์ให้อะครับ จะส่งยังไงดีครับ

[snasui]

ลอง Zip ดูครับว่าจะลดเหลือเท่าไร ผมสังเกตว่าสำหรับ Version 2007 ขึ้นไปอัตราการลดขนาดจะน้อยกว่า 2003 ค่อนข้างมากครับ

[ARNONT]

ผมบันทึกเป็น 2003 แล้วลองzipแล้วครับ อยุ่ที่ 3.18MB พอได้รึป่าวครับ

[snasui]

คิดว่าแนบมาใน Forum ไม่ผ่านแน่ครับ ลองส่งมาที่ snasui@gmail.com ครับ

[snasui]

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

1. ลบชื่อที่ให้ไว้กับช่วงเซลล์ที่แสดงค่าผิดพลาดออกทั้งหมด
2. สูตร Vlookup ทั้งหลายให้แก้ไขช่วงข้อมูลใหม่ โดยช่วงข้อมูลควรจะใช้แบบ Dynamic Range หากคิดว่ามีข้อมูลมีการเพิ่มหรือลดได้เสมอ

[ARNONT]

ขอบคุณมากครับ สำหรับคำแนะนำครับอาจารย์ ผมรบกวนอาจารย์ส่งไฟล์มาให้ดูด้วยครับ (เพราะผมต้องทำเพิ่มอีกหลายงาน และบางตัวไม่ค่อยเข้าใจครับ กำลังศึกษาวิธีทำอยู่อะครับก็เลยออกมาเป็นแบบที่อาจารย์เห็นแหละครับ) และช่วยอธิบายขยายความ Dynamic Range เพิ่มเติมหน่อยครับ และเราควรใช้กับงานแบบไหนบ้างครับ งานชิ้นนี้ผมใช้เวลาเป็นเดือนเลยครับเพราะเริ่มจาก0เลยทีเดียว ซื้อหนังสือมา งมอ่านไปทำไปครับ..ดีที่ได้ตำราอาจารย์ช่วยเยอะมากจริงๆครับขอบคุณอีกครั้งครับ

[snasui]

ผมได้ลองลบพื้นที่ว่างในทุก ๆ Sheet เพื่อทำการ Reset Used Cells ใหม่ ทำให้ไฟล์เล็กลงเหลือ 8 เมกซึ่งได้ส่งไปให้ทางเมลแล้ว โดยทำดังนี้ครับ

ส่วนที่ ๑ จัดการกับ Last Cells

1. แถวที่ ๑ ไปยังพื้นที่ด้านหลังของข้อมูลซึ่งเป็นเซลล์แรกที่เป็นเซลลว่างและไม่มีข้อมูลใดอยู่ด้านล่างและด้านขวา
2. กดแป้น Ctrl+Shift+ลูกศรขวา, ตามด้วยลูกศรลง จะเป็นการคลุมพื้นที่
3. ไปที่เมนู Home > เลือก Clear all
4. ทำซ้ำกับพื้นที่ด้านล่างข้อมูล
5. ทำซ้ำกับทุก Sheet

ส่วนที่ ๒ จัดการกับสูตร Vlookup ที่มี เปลี่ยนช่วงข้อมูลให้เหลือ ๕๐๐๐

ผมสังเกตพบว่า Vlookup หลายสูตรไม่ได้ทำการ Lock ช่วงเซลล์แล้ว Copy ไปยังเซลล์ด้านล่าง การทำเช่นนั้นจะทำให้สูตรแสดงผลผิดพลาดได้ครับ ปกติ Vlookup จะต้องมีการ Lock ช่วงเซลล์เสมอ

การ Lock คือการใส่ $ หน้าคอลัมน์และหน้า บรรทัด ซึ่งปกติใช้การกดแป้น F4 เช่น

A1:D20 ==> ยังไม่ Lock เซลล์ ถ้า Lock แล้วจะเป็น $A$1:$D$20

ส่วนที่ ๓ คือ Dynamic Range Name

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

ยกตัวอย่างเรามีข้อมูลอยู่ที่ Sheet1!A1:Z1000 และข้อมูลมีการเปลี่ยนแปลงตลอดเวลา เราสามารถให้ชื่อกับพื้นที่ดังกล่าวแล้วนำไปใช้ในสูตรได้ การให้ชื่อทำดังนี้ครับ

1. กดแป้น Alt+I , N, D
2. ช่อง Name in workbook: คีย์ชื่อที่ต้องการ เช่น Database
3. ช่อง Refers to: คีย์สูตร

=Offset($A$1,0,0,Counta($A:$A),Counta($1:$1))

จากนั้นกดปุ่ม OK

ส่วนที่ ๔ การนำ Dynamic Range Name มาใช้ในสูตร เช่น Vlookup ใน Sheet ใด ๆ ที่อ้างถึงข้อมูลใน Sheet1

=Vlookup(B5,Database,2,0)

Enter

[ARNONT]

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

[snasui]

ขอบคุณสำหรับคำอวยพร ขอให้พรนี้ส่งผลกลับยังยังคุณ ARNONT เช่นกันครับ

[wisit]

อาจารย์ครับ การทำ Reset Last Cells ที่อาจารย์ระบุ ถ้าของผมเป็น Excel 2003 จะเข้าเมนูส่วนนี้อย่างไรครับ
ส่วนที่ ๑ จัดการกับ Last Cells ไปที่เมนู Home > เลือก Clear all (Excel 2003 ต้องเข้าที่เมนูใดครับ)
ส่วนที่ ๓ คือ Dynamic Range Name กดแป้น Alt+I , N, D (ผมลองกด Alt+I จะขึ้นเมนู INsert เมื่อกด Alt+D จะขึ้นเมนู Data ถ้ากด Alt+N จะไม่ขึ้นอะไรเลย ไม่รู้ผมทำอะไรผิดหรือเปล่าครับอาจารย์ และผมถ้าต่อกระทู้นี้ โดยไม่ตั้งกระทู้ใหม่ถูกต้องหรือเปล่าครับ ไม่รู้จริง ๆ ครับ อาจารย์) หากผิดพลาดประการใด ขออภัยด้วยครับ

[snasui]

กรณี Clear All เพื่อ Reset Last Cell สำหรับ Excel 2003 เข้าเมนู Edit ครับ

ส่วน Dynamic range name ที่ถาม เป็นการสร้างชื่อให้กับช่วงเซลล์ ตามที่แจ้งมานั้นกดแป้นไม่ถูกต้องครับ Alt+I, ตามด้วย N แล้วตามด้วย D โดยมี Alt เข้ามาด้วยแค่ตอนกดแป้น I เท่านั้นครับ หากเข้าเมนูก็ Insert > Name > Define

[wisit]

ขอบคุณมากครับอาจารย์ เดี่๋ยวผมจะลองทำดูครับ