ใน Excel 2003 และรุ่นก่อนหน้านั้น สูตรหนึ่งๆมีข้อจำกัดอยู่ 3 ประการ
- เซลล์รับสูตรได้ยาวมากที่สุด 1024 ตัวอักษร
- ในวงเล็บของสูตร เฉพาะบางสูตรซึ่งแบ่งได้ จะแยกเป็นส่วนย่อยๆได้สูงสุด 30 ส่วน แยกออกจากกันด้วยเครื่องหมาย comma เช่น สูตร CHOOSE เป็นต้น
- ในสูตรหนึ่ง จะนำสูตรอื่นมาซ้อนกันได้ไม่เกินกว่า 7 สูตร
เรานำสูตรมาซ้อนสูตร เพื่อทำให้สูตรๆเดียวสามารถคำนวณให้ผลลัพธ์ได้เลยทันที โดยไม่ต้องพึ่งพาสูตรในเซลล์อื่นๆ ช่วยให้เกิดประโยชน์ดังนี้
- ช่วยจำกัดเซลล์ที่ต้องใช้เพื่อสร้างสูตร
- ช่วยให้ค้นหาเซลล์สูตรได้ง่ายขึ้น เนื่องจากสูตรถูกนำมารวมกันไว้เพียงที่เดียว
- ถ้าไม่ได้ใช้สูตรซ้ำกันในเซลล์ต่างๆมากนัก จะทำให้ file มีขนาดเล็กลง เนื่องจากไม่ต้องเปลืองเซลล์หรือ sheet เพื่อแยกเก็บสูตร
- ช่วยให้คำนวณรวดเร็วขึ้น
ส่วนข้อเสียของการสร้างสูตรซ้อนสูตรก็คือ สร้างยาก และทำให้แก้ไขสูตรในภายหลังยากขึ้น จึงต้องเปรียบเทียบข้อดีข้อเสียดังกล่าวนี้เสมอ หากสร้างเองใช้เองแก้ไขเองคงไม่ต้องห่วงนัก แต่ถ้าสร้างสูตรแล้วทิ้งไว้ให้คนอื่นแก้ไข คงต้องลืมกันไปเลยว่า จะเริ่มต้นแก้ไขกันยังไงดี ส่วนมากมักสร้างสูตรขึ้นใหม่เลยดีกว่า
หลักการสร้างสูตรซ้อนสูตร
- ควรรวมกลุ่มของสูตรซึ่งคำนวณเป็นลำดับต่อเนื่องกันไว้เป็นสูตรเดียว และแยกขั้นตอนซึ่งไม่ต่อเนื่องออกเป็นอีกสูตรหนึ่ง
- ควรแยกสูตรออกให้ทำงานคืนค่าซึ่งมีความหมายสมบูรณ์ในขั้นนั้นๆ และง่ายต่อการตรวจสอบว่า ลำดับการคำนวณขั้นใดผิดพลาด จะได้สะดวกต่อการแก้ไขให้ถูกต้อง
- อย่ารวมทุกขั้นตอนของการคำนวณไว้ในสูตรๆเดียวจนยาวเกินไป
วิธีสร้างสูตรซ้อนสูตร
- ให้สร้างสูตรซึ่งควรเป็นสูตรวงเล็บในสุดก่อนสูตรอื่น สูตรนี้มักใช้คำนวณหาค่าพื้นฐานแรกสุด ซึ่งจะถูกนำไปใช้เป็นตัวแปรในสูตรอื่นถัดไป เช่น สร้างสูตรคำนวณหาเลขที่บรรทัด ด้วยสูตร =MATCH( MinimumCost, TotalCost, 0)
- จากนั้นจึงสร้างสูตรอื่นคร่อมข้างนอกเพื่อนำผลลัพธ์จากสูตร Match ไปใช้ เช่น
=INDEX( LotSize, MATCH( MinimumCost, TotalCost, 0), 1) - ถ้าเป็นสูตรช่วยการตัดสินใจ เช่น สูตร IF ให้พิจารณาหาสูตรซึ่งใช้ตัดสินใจอื่นๆ มาซ้อนเข้าไปในสูตรเดิม เมื่อถึงตัวแปรซึ่งต้องการใช้สูตรอื่นคำนวณค่าให้ เช่น
ต้องการตัดสินคะแนน 0 - 50 ให้เกรด C ถ้าคะแนน 51 - 80 ให้เกรด B แต่ถ้าเกินกว่า 80 ให้เกรด A
=IF(คะแนน>80,"A","BC")
แล้วค่อยพิจารณาต่อว่า จะตัดสินเกรด B C ต่อไปด้วยสูตร IF ยังไง
=IF(คะแนน>80,"A",IF(คะแนน>50,"B","C")) - ถ้าไม่ถนัดสร้างสูตรต่อกันไป ซ้อนกันไปเลยทีเดียว ให้สร้างสูตรแยกแต่ละขั้นตอนออกไปลงในเซลล์แยกจากกันก่อน แล้วจึง copy ตัวสูตร ไป paste ทับในตำแหน่งอ้างอิง เช่น
A1: =NOW()
A2: =DAY(A1)
ให้ copy สูตร NOW() จากเซลล์ A1 ไปทับคำว่า A1 ในเซลล์ A2 จะได้สูตรซ้อนกันเป็น =DAY(NOW())
วิธี copy สูตร ให้คลิกเลือกส่วนของสูตรที่ต้องการจากสูตรบน formula bar เช่น เลือกคำว่า NOW() แล้วคลิกขวาเลือก copy (หรือกด Ctrl+c) แล้วกด Esc ทีหนึ่งเพื่อออกจากการเลือก แล้วจึงไปคลิกเลือกคำว่า A1 บน formula bar แล้วคลิกขวาสั่ง paste (หรือกด Ctrl+v)