Tag: performance

บทความนี้จะพาคุณลึกเข้าไปใน “Performance” ของฟังก์ชันตรวจสอบเงื่อนไขใน Excel
ตั้งแต่ IF() พื้นฐาน ไปจนถึง IFS(), SWITCH(), LET() และเทคนิคซ่อนสูตรด้วย LAMBDA() เพื่อเลื่อนการคำนวณ จับเวลาด้วย LET() + NOW() แล้วแสดงผลเป็นมิลลิวินาที (ms) ให้เห็นกันชัด ๆ

🌟 1. ทำไมต้องสนใจ Performance ของสูตร?

เมื่อสูตร Excel ซับซ้อนขึ้น หรือมีการคำนวณกับ dataset ขนาดใหญ่ ความเร็วในการคำนวณ (calculation speed) จะกลายเป็นปัจจัยสำคัญ

• ถ้าเราซ้อน VLOOKUP/XLOOKUP/MAKEARRAY/FILTER หลายครั้งในเงื่อนไขที่ไม่จำเป็น → Excel จะ “คิด” ทุกเงื่อนไขซ้ำนับร้อยครั้ง
• สูตรที่ช้าไม่ใช่แค่รอผลนาน แต่ยังกินทรัพยากรเครื่อง และอาจทำให้ workbook ทั่วทั้งไฟล์อืดตาม

บทความนี้อ้างอิงแนวคิดจาก

“The SWITCH and LET functions – Excel formula performance”
by Owen Price (Microsoft MVP)
ซึ่งลงลึกเรื่องว่า IF() กับ CHOOSE() ให้การประมวลผลแบบ lazy evaluation (short‑circuit) แต่ IFS()/SWITCH()/LET() จะคำนวณ ทุก พารามิเตอร์โดยไม่เลือก

🔧 2. วิธีจับเวลาใน Excel แบบง่าย ๆ ด้วย LET() + NOW()

Excel ไม่มีฟังก์ชัน Timer เหมือนภาษาโปรแกรม แต่เราสามารถวัดเวลาคร่าว ๆ ได้ด้วยสูตรนี้ (เพราะใน LET จะไล่คำนวณจากบนลงล่าง แม้จะไม่ได้เรียกใช้ตัวแปรนั้นๆ เลย) :

=LET(
  start,     NOW(),           // เก็บเวลาเริ่มต้น (หน่วยวัน)
  result,    <สูตรที่อยากวัด>,   // สูตรหนัก ๆ เช่น MAKEARRAY
  elapsed,   NOW() - start,   // ผลต่าง (หน่วยวัน)
  elapsedMs, elapsed * 24 * 60 * 60 * 1000,  // แปลงมิลลิวินาที
  elapsedMs                   // คืนค่าเวลา (ms)
)

หมายเหตุ:

• ค่าที่ได้เป็นการประมาณ ไม่แม่นยำระดับ millisecond เป๊ะ ๆ แต่ใช้เปรียบเทียบระหว่างสูตรได้ดีและง่าย โดยไม่ต้องใช้ VBA จับเวลา

🧪 3. ชุดทดสอบ: MAKEARRAY(2000,2000,PRODUCT)

เพื่อให้เห็นภาพชัดที่สุด เราใช้สูตรสร้าง array ขนาดใหญ่ เบิ้มๆ ไปเลย:

=MAKEARRAY(2000, 2000, PRODUCT)

เป็นการคูณเลขแถว × เลขคอลัมน์ จำนวน 4 ล้านเซลล์ → Excel ต้องคำนวณนานหลายร้อยมิลลิวินาที

จากนั้นเอาสูตรนี้ไปใส่ใน IF(), IFS(), SWITCH(), LET() และ LET+LAMBDA() แล้วดู ค่าเวลา (ms) จริงที่ได้

🧠 4. เจาะลึกแต่ละฟังก์ชัน

4.1 IF() – ขี้เกียจแบบฉลาด ทำให้คำนวณเร็ว 😂

=IF(condition, value_if_true, value_if_false)
=IF(TRUE, "เร็ว", MAKEARRAY(1000,1000,PRODUCT))

• Short‑circuit (lazy): ถ้า condition = TRUE → Excel จะไม่ไปคำนวณ value_if_false เลย
• เช่น ในทีนี้จะไม่คำนวณตรง MAKEARRAY(1000,1000,PRODUCT) เลย
• เหมาะกับกรณีที่เงื่อนไขหลักมีโอกาสเป็นจริงสูงมาก

Tips : พวก IFNA, IFERROR ก็คำนวณเร็วเช่นกันนะ

4.2 IFS() – อ่านง่าย เขียนง่าย แต่ขยันเกิน ทำให้ช้า

=IFS(cond1, val1, cond2, val2, …)
=IFS(
    TRUE, "เร็ว",
    FALSE, MAKEARRAY(2000,2000,PRODUCT)
  )

• ไม่ short‑circuit: Excel จะประมวล ทุกเงื่อนไข condN, valN แม้จะเจอเงื่อนไขแรกเป็นจริงแล้วก็ตาม (ทำเพื่อ! 😅)
• เช่น ในที่นี้แม้ Condition แรกจะจริงไปแล้ว แต่ก็มาคิด Condition ที่สองต่อ ทำให้ยังคำนวณ MAKEARRAY(2000,2000,PRODUCT) ช้าๆ ออกมาอยู่ดี
• ถ้าใช้ภายใน IFS() สูตรหนัก ๆ หลายตัว → ช้ากว่าซ้ำนับครั้งไม่ถ้วน

4.3 SWITCH() – อ่านเปรียบเทียบค่าคงที่ได้ดี แต่ขยันเกินจนช้า

=SWITCH(expression, val1, res1, val2, res2, …)=SWITCH(1,
    1, "เร็ว",
    2, MAKEARRAY(2000,2000,PRODUCT)
  )

• ไม่ short‑circuit: Excel จะคำนวณทุกเงื่อนไข แม้ expression จะตรงกับ val1 ตั้งแต่แรกแล้ว
• เช่น ในทีนี้ แม้ expression จะเป็น 1 ซึ่งตรงอันแรกแล้ว มันก็ยังคิด 2 ที่เป็น MAKEARRAY ช้าๆ อยู่ดี
• อ่านง่ายกว่าซ้อน IF/IFS แต่หากมีสูตรหนักก็ไม่ work

Tips: ใช้ CHOOSE จะ Short-circuit ทำให้เร็วได้ เช่น

=CHOOSE(3, "A", "B", MAKEARRAY(2000,2000,PRODUCT))  // ถ้าเลือก 3 จะคำนวณ MAKEARRAY แค่ครั้งเดียว

4.4 LET() – ลดการคำนวณตอนเรียกใช้ซ้ำแต่คำนวณทุกค่าที่ประกาศไว้

=LET(name1, expr1, name2, expr2, …, result)
=LET(slow, MAKEARRAY(2000,2000,PRODUCT),
  result, "เร็ว",
  result)

• ทุก exprN ที่มีการประกาศตัวแปรไว้ จะถูกประมวลผลทันทีเมื่อสูตรรัน โดยไม่สนว่า nameN จะถูกนำไปใช้ใน result สุดท้ายหรือไม่
• เช่น ในทีนี้ตัวแปร slow ไม่ได้ถูกเรียกใช้ในตอนจบเลย แต่ก็ยังคำนวณ MAKEARRAY ช้าๆ อยู่ดี
• ใช้ลดการซ้ำของสูตร เช่น VLOOKUP ครั้งละหลายรอบได้ดี แต่ประกาศตัวแปรหนัก ๆ แล้วไม่ได้ใช้ → ก็ยังช้าเหมือนเดิม

4.5 LET() + LAMBDA() – “Thunk” เลื่อนการคำนวณได้

=LET(
  Thunk, LAMBDA(MAKEARRAY(2000,2000,PRODUCT)),
  result,    "เร็ว",
  result
)

• เมื่อประกาศตัวแปร ที่ใช้ LAMBDA ครอบไว้ → Excel จะไม่ประมวล MAKEARRAY ทันที
• ถ้าต้องการคำนวณจริง ๆ ให้เรียก Thunk() โดยต้องเติมวงเล็บ () ไว้ข้างหลังด้วยนะ แบบนี้

=LET(
  Thunk, LAMBDA(MAKEARRAY(2000,2000,PRODUCT)),
  result,    "เร็ว",    Thunk())

🚀 5. ทริกปรับสูตรให้เร็วขึ้น

1. ใช้ IF() แทน IFS() ถ้าเงื่อนไขแรกมีโอกาสเป็น TRUE สูง
2. ห่อสูตรหนัก ๆ ใน LAMBDA() แล้วเรียกเมื่อจำเป็น
3. ลดการคำนวณซ้ำ ด้วย LET() แต่แน่ใจว่าชื่อที่ประกาศจะถูกใช้จริง
4. เลือก CHOOSE() แทน SWITCH() เมื่อค่าต้องเลือกเป็นลำดับ (1,2,3…) เพื่อได้ lazy evaluation

✨ 6. สรุปอีกทีให้เซฟเก็บ

• IF(), IFNA(), IFERROR() = ✅ Lazy Evaluation ทำให้เร็ว : หยุดคำนวณเงื่อนไขหลังทันที
• IFS() = ❌ Eager Evaluation ทำให้ช้า: คิดทุกเงื่อนไข
• CHOOSE() = ✅ Lazy Evaluation ทำให้เร็ว : คิดเฉพาะทางเลือกที่เลือกจริงๆ
• SWITCH() = ❌ Eager Evaluation ทำให้ช้า: คิดทุกทางเลือก
• LET() = ❌ Eager Evaluation ทำให้ช้า: คิดทุกตัวแปร
• LET+LAMBDA() = ✅ Lazy Evaluation ทำให้เร็ว: เลื่อนคำนวณจนกว่าจะเรียกใช้

ใครกำลังเจอ Excel อืดเพราะสูตรซ้อนซับซ้อน ลองเอาแนวทางนี้ไปปรับใช้ดูครับ
– บางทีแค่เปลี่ยนจาก IFS → IF หรือห่อสูตรใน LAMBDA ก็ช่วยลดเวลาไปได้ครึ่งหนึ่ง!

ถ้าคิดว่าบทความนี้เป็นประโยชน์ อย่าลืมแชร์หน้านี้ให้เพื่อน ๆ เห็นทริกเทพเอ็กเซลกันเยอะ ๆ นะครับ 😉