Excel Forum

สวัสดีครับอาจารย์ ผมมีเรื่องมารบกวนถามอาจารย์อีกแล้วครับ ตอนนี้ผมได้ทำโปรแกรมสำหรับใช้งานใน 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จะทำให้เร็วขึ้นจริงรึเปล่า (ผมลองแล้วไม่มีผลอะไรเลยผมเพิ่มตัวเดียว)

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

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

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

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

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

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

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

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

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

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

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

ผมได้ลองลบพื้นที่ว่างในทุก ๆ 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 เช่นกันครับ

อาจารย์ครับ การทำ 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 จะไม่ขึ้นอะไรเลย ไม่รู้ผมทำอะไรผิดหรือเปล่าครับอาจารย์ และผมถ้าต่อกระทู้นี้ โดยไม่ตั้งกระทู้ใหม่ถูกต้องหรือเปล่าครับ ไม่รู้จริง ๆ ครับ อาจารย์) หากผิดพลาดประการใด ขออภัยด้วยครับ

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

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

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