Excel not responding แก้ ยัง ไง

เพราะการอ้างอิง $A:$E เป็นการอ้างอิงทั้งคอลัมน์ (เทียบได้กับ $A$1:$E$1,048,576) การอ้างอิงแบบนี้ทำให้ประมวลผลช้า

แล้วควรทำยังไง?

ควรอ้างอิงโดยจำกัดพื้นที่ เช่น เปลี่ยนสูตรเป็น

=VLOOKUP( B4, Store!$A$2:$E$1000, 2, 0 )

ใช่, จำกัดพื้นที่ไปเลยว่าอ้างอิงตั้งแต่บรรทัดที่ 2 ถึง 1000 แบบนี้ประมวลผลเร็วกว่าเดิมเยอะ
(อ้างอิง 1,000 บรรทัด ยังไงก็ประมวลผลเร็วกว่า 1,048,576 บรรทัด)

“ถ้าเขียนสูตรแบบนี้ เวลามีข้อมูลเพิ่มในชีต Store ก็ต้องปรับสูตรใหม่ แบบนี้ก็ไม่สะดวกสิ”

ถ้าต้องการให้ข้อมูลปรับเปลี่ยนได้แบบไดนามิก อาจปรับฟอร์แมตข้อมูลเป็น Table (Ctrl+T)

เมื่อปรับฟอร์แมตข้อมูลเป็น Table (เช่น ตั้งชื่อว่า StoreMaster) การอ้างอิงจะเปลี่ยนไป เช่น เปลี่ยนเป็น

=VLOOKUP( B4, StoreMaster, 2, 0 )

ข้อดีของการปรับฟอร์แมตเป็น Table คือ เมื่อมีข้อมูลใหม่ Table จะขยายขอบเขตเองแบบอัตโนมัติ (ไดนามิก) โดยที่เราไม่ต้องเปลี่ยนสูตร

“ไม่อยากปรับฟอร์แมตเป็น Table มีวิธีอื่นไหม?”

ถ้าไม่อยากปรับฟอร์แมตเป็น Table ทางแก้คือ เผื่อช่วงข้อมูล (ที่อาจขยายในอนาคต) เช่น จากเดิมอ้างอิง 1,000 บรรทัด
ก็เผื่อเป็น 5,000 บรรทัด

=VLOOKUP( B4, Store!$A$2:$E$5000, 2, 0 )

ยังไงก็ประมวลผลเร็วกว่าอ้างอิงทั้งคอลัมน์แน่นอน ^_^

3. ลดการคำนวณซ้ำ

ถ้าใช้สูตรเดียวกันหลายคอลัมน์ (เช่น เขียนสูตรที่ D4 ก๊อปปี้ไปทางขวาถึง G4 แล้วก๊อปปี้ลงมา 500,000 บรรทัด)
ไม่ควรเขียนสูตรในลักษณะนี้

=INDEX(
   Store!$A$2:$E$1000,
   MATCH($B4,Store!$A$2:$A$1000,0),
   MATCH(D$3,Store!$1:$1,0)
 )

ทำไม?

เพราะคอลัมน์อื่น (E, F, G) จะคำนวณ MATCH($B4,Store!$A$2:$A$1000,0) ซ้ำ
และบรรทัดอื่น ก็จะคำนวณ MATCH(D$3,Store!$1:$1,0) ซ้ำ
ทำให้ประมวลผลช้ามาก

ควรทำยังไง?

ควรสร้างคอลัมน์ช่วย (Helper Column), บรรทัดช่วย (Helper Row) แล้วอ้างอิงสูตรจากคอลัมน์และบรรทัดช่วยนั้น เช่น

เขียนสูตรใน C4 (แล้วก๊อปปี้จนถึง C500,000) เป็น

=MATCH($B4,Store!$A$2:$A$1000,0)

เขียนสูตรใน D1 (แล้วก๊อปปี้ไปทางขวาจนถึง G1) เป็น

=MATCH(D$3,Store!$1:$1,0)

จากนั้นก็เปลี่ยนสูตร D2 (แล้วก๊อปปี้จนถึง G500,000) เป็น

=INDEX(Store!$A$2:$E$1000,$C4,D$1)

แม้การมีคอลัมน์ช่วย (จากภาพคือคอลัมน์ C) และบรรทัดช่วย (จากภาพคือ D1-F1) อาจทำให้ไฟล์ดูไม่เท่
แต่เชื่อเถอะ เร็วขึ้นกว่าเดิมเยอะ ^_^

4. หลีกเลี่ยง Volatile Function

Volatile Function คืออะไร?

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

พูดง่าย ๆ คือ Volatile Function ทำให้คำนวณช้า

Volatile Function มีอะไรบ้าง?

มีหลายฟังก์ชัน เช่น

• OFFSET
• INDIRECT
• TODAY
• NOW
• RAND
• RANDBETWEEN
• RANDARRAY
• CELL
• INFO

Reference: http://www.decisionmodels.com/calcsecretsi.htm

Volatile Function ที่ถูกใช้บ่อย ๆ ก็คือ OFFSET, INDIRECT

ดังนั้น ถ้าต้องเขียนสูตรกับข้อมูลเยอะ ๆ หลีกเลี่ยงการใช้ OFFSET, INDIRECT (ให้ใช้ฟังก์ชัน INDEX แทน) แล้วไฟล์จะคำนวณเร็วขึ้น

5 ปรับการคำนวณให้เป็นแบบแมนวล (F9)

โดยปกติแล้ว Excel จะคำนวณแบบอัตโนมัติเสมอ

เช่น เมื่อก๊อปปี้สูตรลงมา 1,000 บรรทัด Excel ก็จะคำนวณ 1,000 บรรทัดนั้นทันที

ดังนั้น ถ้าก๊อปปี้สูตรลงมา 500,000 บรรทัด Excel ก็จะคำนวณ 500,000 บรรทัดทันที แน่นอนว่าทำให้ประมวลผลช้า
และอาจยังไม่จำเป็นต้องประมวลผล 500,000 บรรทัดในตอนนั้น

ทางแก้คือ ปรับการคำนวณให้เป็นแบบแมนวล (Manual)

โดยคลิกริบบิน Formula/ Calculation/ Manual

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

หรือถ้าต้องการคำนวณเฉพาะชีตที่ใช้งานอยู่ ให้กดปุ่ม Shift+F9
(วีธีนี้สะดวกกว่า เพราะถ้ากด F9 จะคำนวณทุกชีต ซึ่งบางครั้งเราอาจต้องการให้คำนวณแค่ชีตเดียว)

เวลาเขียนสูตรกับข้อมูลจำนวนมาก ผมมักปรับการคำนวณให้เป็นแบบแมนวล
เขียนสูตรบรรทัดแรกเสร็จ ก๊อปปี้ให้ครบทุกบรรทัด กด F9 แล้วลุกไปดื่มน้ำ พอกลับมาก็คำนวณเสร็จพอดี ^_^

อ้อ! ถ้าไฟล์นั้นถูกปรับให้คำนวณแบบแมนวล เวลาส่งต่อให้เพื่อน ต้องแจ้งเพื่อนให้กด F9 ด้วยนะครับ ไม่งั้นเพื่อนอาจงงว่าทำไมก๊อปปี้สูตรแล้วตัวเลขไม่เปลี่ยน ^^

6. เปลี่ยนสูตรให้เป็นค่าคงที่ (Paste Value)

การทำงานกับข้อมูลจำนวนมาก ส่วนใหญ่มักต้องดึงข้อมูลจากตารางอื่น หรือรวมตัวเลขตามเงื่อนไขต่าง ๆ

สูตรที่ใช้มักเป็น VLOOKUP, INDEX, MATCH, SUMIFS, COUNTIFS

เมื่อคำนวณเสร็จแล้ว ควรเปลี่ยนสูตรเหล่านั้นให้เป็นค่าคงที่ (Copy -> Paste Value)
ยิ่งเปลี่ยนเป็นค่าคงที่ทั้งหมด แปลว่าไฟล์นั้นไม่มีการคำนวณเลย ปัญหาเรื่องคำนวณจนคอมพ์ค้างก็จะหมดไป

“แต่ข้อมูลอาจเปลี่ยนแปลงในอนาคต ถ้า Paste Value ทั้งหมด ก็ต้องมานั่งเขียนสูตรใหม่สิ?”

ถ้ากังวลว่าข้อมูลอาจเกิดการเปลี่ยนแปลง (แล้วต้องเขียนสูตรใหม่) วิธีแก้ง่าย ๆ คือ เหลือสูตรเฉพาะบรรทัดแรกเท่านั้น
บรรทัดที่เหลือปรับเป็นค่าคงที่ทั้งหมด

ถ้าเกิดการเปลี่ยนแปลงจริง ๆ ก็แค่ก๊อปปี้สูตรจากบรรทัดแรกให้คำนวณใหม่ จากนั้นก็เปลี่ยนให้เป็นค่าคงที่เหมือนเดิม
รับรองว่าเร็วขึ้นเยอะแน่นอน ^_^

7. ใช้ Power Query แทน VLOOKUP

การทำงานกับข้อมูลเยอะ ๆ ส่วนใหญ่มักดึงข้อมูลจากหลาย ๆ ตาราง แล้วรวมกันเป็นตารางเดียว
(ภาษาชาวบ้านเรียกว่า “ประกอบร่าง” )

สูตรที่ใช้มักเป็น VLOOKUP, INDEX, MATCH, OFFSET, INDIRECT

แม้จะปรับการเขียนสูตรตามวิธีที่ 2, 3, 4 แล้ว ไฟล์ก็ยังคำนวณช้า เพราะข้อมูลมีจำนวนมาก

ทางแก้นึงที่ทำได้คือ ใช้ Power Query ดึงข้อมูล โดยใช้ฟีเจอร์ที่ชื่อว่า Merge Queries (Home/ Merge Queries)

หลักการทำงานของ Merge Queries คล้าย VLOOKUP แต่ประมวลผลเร็วกว่าเยอะมาก
แถมดึงข้อมูลได้ครั้งละหลายคอลัมน์อีกต่างหาก

ถ้าใครทำงานกับข้อมูลเยอะ ๆ ลองใช้ Merge Queries แล้วคุณจะลืม VLOOKUP ไปเลย ^_^

8. ใช้ Power Pivot แทน Pivot Table

การทำงานกับข้อมูลเยอะ ๆ มักดึงข้อมูลจากหลายตารางมารวมกัน แล้วสรุปด้วย Pivot Table

แม้จะใช้ Power Query ดึงข้อมูลแทน VLOOKUP ก็ต้องใช้เวลาอยู่ดี ยิ่งข้อมูลมีจำนวนมาก เวลาที่ใช้ก็มาก

ทางแก้คือ ไม่ต้องดึงข้อมูลมารวมกัน แต่ใช้ Data Model แทน

Data Model คืออะไร?

คือโมเดลความสัมพันธ์ของตาราง

การที่เราดึงข้อมูลมารวมกันได้ แปลว่าตารางนั้น ๆ มีความสัมพันธ์กัน เช่น สัมพันธ์กันด้วย ProductID, CustomerID

ดังนั้นจึงไม่จำเป็นต้องดึงข้อมูลมารวมกันเป็นตารางเดียว แต่ให้สร้างความสัมพันธ์ระหว่างตารางต่าง ๆ เข้าด้วยกัน

แล้วจึงสรุปข้อมูลด้วย Pivot Table (สามารถดึงข้อมูลได้จากทุกตารางใน Data Model)

Pivot Table แบบนี้ไม่ใช่ Pivot Table ธรรมดา แต่เป็น Data Model Pivot Table
หรือก็คือ Power Pivot นั่นเอง

(อ่านรายละเอียดของ Power Pivot ได้จาก บทความนี้)

Power Pivot ทำทุกอย่างที่ Pivot Table แบบธรรมดาทำได้ แถมยังเพิ่มการคำนวณสุดเจ๋งที่เรียกว่าสูตร DAX ได้อีกต่างหาก

9. ใช้ Power BI แทน Power Pivot

แม้ Power Pivot จะแก้ปัญหาเรื่องการรวมตารางได้ แต่ Power Pivot สร้างกราฟได้เพียงไม่กี่แบบ

และสูตร DAX ใน Power Pivot ก็มีข้อจำกัดบางอย่าง (ไม่สามารถสร้างตารางจาก Query Function)

ทางแก้คือใช้โปรแกรม Power BI แทน Power Pivot

Power BI รองรับข้อมูลได้มากกว่า 1 ล้านบรรทัด มีทั้งความสามารถของ Power Query, Data Model, DAX, สร้างกราฟได้หลากหลาย แถมยังประมวลผลเร็วกว่า Excel

เรียกได้ว่าเป็นโปรแกรมที่เก่งมาก แต่ก็มีรายละเอียดเยอะมากเช่นกัน

10. ใช้โปรแกรมจัดการฐานข้อมูล

แม้ Power BI เป็นโปรแกรมที่เก่งมาก แต่เป็นโปรแกรมด้าน BI (Business Intelligence) ไม่ใช่โปรแกรมจัดการฐานข้อมูล ไม่สามารถเพิ่ม ลบ หรือแก้ไขข้อมูลต้นทางได้

ควรใช้โปรแกรมจัดการฐานข้อมูล (เช่น MS Access, MS SQL Server, MS Azure) จัดการข้อมูล แล้วค่อยเชื่อมต่อมาที่ Power BI เพื่อวิเคราะห์หรือสร้างแดชบอร์ด (Dashboard)

(หรือจะเชื่อมต่อมาที่ Excel แล้วแสดงผลด้วย Power Pivot ก็ได้เช่นกัน)

การเขียนสูตรใน Excel จนคอมพ์ค้าง แท้จริงแล้วคือการเขียนสูตรกับฐานข้อมูล

การ “ประกอบร่าง” หลายตารางเป็นตารางเดียว คือสิ่งที่เรียกว่า Query ในโปรแกรมฐานข้อมูล

โปรแกรมฐานข้อมูลสร้างและจัดการ Query ได้ดีกว่า Excel แถมยังรองรับข้อมูลปริมาณมาก
(Excel รองรับได้เพียง 1 ล้านบรรทัด)

ข้อเสียของโปรแกรมฐานข้อมูลคือมีรายละเอียดที่ต้องศึกษาเยอะมาก แต่ถ้าได้ทักษะนี้จะต่อยอดและทำอะไรได้อีกเยอะมากเช่นกัน