ใน Excel การเกิด Circular Reference (การอ้างอิงแบบวนรูป) หมายถึงเซลล์สูตรอ้างอิงถึงตัวเองโดยตรงหรือโดยอ้อม Excel ไม่สามารถคำนวณได้โดยอัตโนมัติเมื่อเวิร์กชีตมี circular reference หากคุณปล่อยให้ circular reference อยู่นั้น Excel จะคำนวณเซลล์แต่ละเซลล์ที่เกี่ยวข้องกับ circular reference โดยใช้ค่าจากรอบการวนซ้ำก่อนหน้า สร้างลูปแบบไม่รู้จบที่ทำให้สเปรดชีตช้าลงมาก
ตอนนี้ เราจะอภิปรายเพิ่มเติมเกี่ยวกับว่า Circular Reference ใน Excel คืออะไร และคุณสามารถจัดการกับมันได้อย่างไร
ประเภทของ Circular Reference
หากเซลล์ที่คุณกำลังใส่สูตร ถูกกล่าวถึงในอ้างอิงอย่างใดอย่างหนึ่ง จะเรียกว่า circular reference มีหลักสองประเภท คือ direct และ indirect หากคุณใส่เซลล์ที่คุณกำลังพิมพ์สูตรไว้ในอ้างอิงของสูตร มันคือ direct circular reference หากคุณใส่เซลล์ในอ้างอิงของอีกเซลล์หนึ่ง โดยเซลล์แรกมีเซลล์ที่สองเป็นอ้างอิง มันคือ indirect circular reference
1. Direct Circular Reference (การอ้างอิงโดยตรง)
พิจารณาชุดข้อมูลต่อไปนี้ ซึ่งเราจะหายอดขายรวมโดยการบวกเซลล์ D5 ถึง D11 ในเซลล์ D12
ตอนนี้ หากเราเพิ่มเซลล์ D12 เข้าไปกับเซลล์เหล่านั้นในเซลล์ D12 เอง จะทำให้เกิด direct circular reference
หากเรากด ENTER กล่องโต้ตอบพร้อมข้อความผิดพลาด circular reference จะปรากฏ
หากเราคลิก OK เราจะเห็นว่าสูตรแสดง 0 แทนที่จะเป็นผลบวก สิ่งนี้เกิดขึ้นเพราะ direct circular reference
2. Indirect Circular Reference (การอ้างอิงโดยอ้อม)
ดูตัวอย่างต่อไปนี้ ซึ่งสูตรในเซลล์ D6 มีการอ้างอิงถึงเซลล์ C4 และสูตรในเซลล์ B8 มีการอ้างอิงถึงเซลล์ D6 ตอนนี้ หากสูตรในเซลล์ C4 มีการอ้างอิงถึงเซลล์ B8 จะทำให้เกิด circular referencing ที่นี่ สูตรในเซลล์ C4 ไม่ได้อ้างอิงตัวเองโดยตรง แต่หากเราติดตาม precedents เราจะเห็นว่าที่บางจุด C4 ถูกใช้เป็นอ้างอิง
หากเรากด ENTER เซลล์ C4 จะแสดง 0 และความสัมพันธ์ของเซลล์จะแสดงด้วยลูกศรสีน้ำเงิน
การค้นหาและแก้ไข Circular Reference ใน Excel
เพื่อให้ได้การคำนวณที่ถูกต้อง การค้นหา circular references และแก้ไขพวกมันจึงสำคัญ มีหลายวิธีในการค้นหา circular reference จากเวิร์กชีตของคุณ และตอนนี้เราจะอภิปรายเกี่ยวกับพวกมัน
1. ตัวเลือก Show Formula เพื่อค้นหา Circular Reference
คุณสามารถค้นหา circular references ของแผ่นข้อมูลด้วยตนเองได้โดยการแสดงสูตร
➤ ไปที่ Formulas > Formula Auditing > Show Formulas
มันจะแสดงสูตรทั้งหมดของเวิร์กชีตของคุณ ตอนนี้คุณสามารถระบุ circular references ได้อย่างง่ายดายโดยประเมินสูตรด้วยตนเอง
2. ด้วยตัวเลือก Error Checking
คุณยังสามารถระบุ circular reference ของเวิร์กชีตได้ด้วยตัวเลือก Error Checking
➤ ไปที่ Formulas > Formula Auditing > Error Checking > Circular References
ที่นี่ คุณจะเห็นเซลล์ทั้งหมดที่มี circular references
ตอนนี้คุณสามารถดำเนินการแก้ไขการคำนวณได้ เช่น การแก้ไขอ้างอิงในสูตร
อ่านเพิ่มเติม: วิธีแก้ไข Circular Reference Error ใน Excel (แนวทางโดยละเอียด)
3. จาก Status Bar
วิธีที่ง่ายที่สุดในการค้นหา circular reference คือการดูที่แถบสถานะ (status bar) หากเวิร์กชีตของคุณมี circular reference ใดๆ มันจะแสดงในแถบสถานะ
เมื่อคุณพบเซลล์ที่มี circular reference แล้ว คุณสามารถดำเนินการแก้ไขได้อย่างง่ายดาย
การติดตามความสัมพันธ์ใน Circular Reference
มีสองวิธีการติดตามที่จะช่วยให้คุณติดตามความสัมพันธ์ระหว่างสูตรและเซลล์ของ circular references วิธีการติดตามคือ trace precedents และ trace dependents เครื่องมือการติดตามเหล่านี้สามารถช่วยผู้ใช้ในการแก้ไข circular references โดยการแสดงเส้นทางเชื่อมโยงอ้างอิงผ่านเส้นที่สร้างระหว่างเซลล์ที่ตอบสนองต่อ circular references
1. Trace Precedents (ติดตามเซลล์ต้นทาง)
ฟีเจอร์ Trace precedents ติดตามเซลล์ที่เซลล์ปัจจุบันขึ้นอยู่กับ พวกเขาคือเซลล์ที่สูตรปัจจุบันพึ่งพาเพื่อข้อมูลที่จำเป็น ตัวเลือกนี้จะสร้างเส้นเพื่อแสดงว่เซลล์ไหนบ้างที่มีอิทธิพลต่อเซลล์ที่ใช้งานอยู่
ในการ trace precedents ก่อนอื่น
➤ เลือกเซลล์ที่มี circular reference
➤ ไปที่ Formulas > Formula Auditing > Trace Precedents หรือกด ALT+T+U+T
ผลคือ คุณจะเห็น ลูกศรสีน้ำเงินจะปรากฏจาก precedent ของเซลล์ที่ใช้งานอยู่ ไปยังเซลล์ที่ใช้งานอยู่
2. Trace Dependents (ติดตามเซลล์ที่พึ่งพา)
ฟังก์ชัน Trace dependents ติดตามเซลล์ที่พึ่งพาเซลล์ที่ใช้งานอยู่ หมายถึงพวกเขาพึ่งพาเซลล์ที่ใช้งานอยู่สำหรับข้อมูลที่พวกเขาต้องการเพื่อสร้างผลลัพธ์ ฟีเจอร์นี้จะวาดเส้นไปยังเซลล์ที่พึ่งพาเซลล์ที่ใช้งานอยู่
ในการ trace dependents ก่อนอื่น
➤ เลือกเซลล์ที่มี circular reference
➤ ไปที่ Formulas > Formula Auditing > Trace Dependents หรือกด ALT+T+U+D
ผลคือ คุณจะเห็น ลูกศรสีน้ำเงินจะปรากฏจากเซลล์ที่ใช้งานอยู่ ไปยังเซลล์ที่พึ่งพา
การเปิดใช้งาน Iterative Calculation เพื่ออนุญาต Circular References
Iterative calculation เป็นฟีเจอร์ที่น่าสนใจใน Excel เมื่อ iterative calculation ถูกปิดใช้งาน Excel จะแสดงข้อความแจ้งเตือน Circular Reference และคืนค่า 0 ในเซลล์แทนผลลัพธ์จริง ตอนนี้ หากคุณต้องการอนุญาต circular references ในเวิร์กชีตของคุณ คุณสามารถเปิดใช้งาน iterative calculation และมันจะอนุญาตให้คุณทำการคำนวณได้ คุณยังสามารถตั้งค่าจำนวนรอบการวนซ้ำที่อนุญาตได้ ดังนั้น คุณสามารถหยุดลูปวนซ้ำแบบไม่รู้จบของ circular reference ได้
เพื่อเปิด iterative calculation
➤ ไปที่แท็บ File และเลือก Options
มันจะเปิดหน้าต่าง Excel Options ตอนนี้
➤ ไปที่แท็บ Formulas ติ๊กถูกกล่อง Enable iterative calculation และคลิก OK
มันจะเปิดใช้งาน iterative calculation และตอนนี้คุณสามารถใช้ circular reference ได้
หากคุณกลับไปที่เวิร์กชีตของคุณ ครั้งนี้คุณจะเห็นเซลล์สูตรแสดงค่าแทน 0 และไม่มีข้อผิดพลาด circular reference นั่นหมายความว่า Excel อนุญาต circular references ตอนนี้
อ่านเพิ่มเติม: วิธีอนุญาต Circular Reference ใน Excel และการใช้งาน
ทำไม Iterative Calculation ควรปิดใช้งานไว้?
การเปิดใช้งาน Iterative calculation ใน Excel ไม่ได้รับแนะนำ ยกเว้นจะจำเป็น เมื่อ Iterative calculation ทำงานอยู่ Excel จะใช้พลังงานมากในการทำงาน และใช้เวลานานขึ้นในการคำนวณง่ายๆ นอกจากนี้ การคำนวณผิดจาก circular references อาจเกิดขึ้นและไม่ถูกสังเกต หาก Iterative calculation ถูกเปิดใช้งาน ดังนั้น เราควรเก็บ iterative calculation ไว้ในสถานะไม่ทำงาน เว้นแต่จะจำเป็น
สรุป
หวังว่าตอนนี้คุณจะรู้ว่า circular reference ใน Excel คืออะไร และคุณสามารถจัดการกับมันได้อย่างไร หากมีข้อสงสัย กรุณาแสดงความคิดเห็นครับ
บทความที่เกี่ยวข้อง
- วิธีลบ Circular Reference ใน Excel (2 วิธี)
- ค้นหา Circular Reference ใน Excel (2 เทคนิคง่าย)
