แก้ไข Circular Reference ซ่อนใน Excel: 4 วิธีง่าย

บทความนี้สาธิตวิธีแก้ Circular Reference ใน Excel ที่ไม่สามารถรายการได้ การเจอ Error Circular Reference ขณะทำงานน่าขวัญใจ หากทำงานกับข้อมูลขนาดใหญ่หลายพันเซลล์ การตรวจสอบเซลล์ทีละอันยากมาก บทความนี้จะสอนวิธีรายการ Circular Reference errors ได้ง่ายจากข้อมูลขนาดใดก็ตาม

ดาวน์โหลด Practice Workbook ได้ที่นี่

Circular Reference คืออะไร?

Circular Reference คือสูตรที่อ้างอิงเซลล์เดียวกันหรือเซลล์อื่นซ้ำๆ ในลำดับการคำนวณ ทำให้เกิด Infinite Loop ทำให้สเปรดชีตช้าลงอย่างรุนแรง

อธิบายด้วยชุดข้อมูลตัวอย่าง: "Sales Amount" 6 เดือน ต้องการคำนวณยอดรวมในเซลล์ C11

เลือกช่วง C6:C10 ในสูตร SUM เพื่อได้ผลลัพธ์ แต่ถ้าเลือก C6:C11 ผิดๆ จะไม่ได้ผลที่คาดหวัง

สูตรใน C11 แจ้งเตือน Circular Reference เพราะสูตรพยายามคำนวณตัวเองด้วย

แบ่ง Circular Reference เป็น 2 ประเภท:

1. Direct Circular Reference: สูตรในเซลล์อ้างอิงเซลล์ตัวเองโดยตรง

2. Indirect Circular Reference: สูตรในเซลล์ไม่ได้อ้างอิงตัวเองโดยตรง แต่อ้างอิงผ่านเซลล์อื่น

4 วิธีง่ายแก้ Circular Reference ที่รายการไม่ได้

เมื่อเจอ Circular Reference ต้องแก้ทันที ต้องหาตำแหน่งก่อน บทความนี้ใช้ 4 วิธีรายการ Error แล้วแก้โดยแก้สูตร

1. ใช้ Error Checking Tool ใน Ribbon

ใช้ 'Error Checking' จาก Ribbon ระบุ Circular Reference ที่รายการไม่ได้ ใช้ชุดข้อมูลตัวอย่างที่มี Error ที่ C11

ขั้นตอน:

• ไปแท็บ Formulas

• จาก Ribbon ใต้แท็บ Formulas เลือกดรอปดาวน์ "Error Checking" > คลิก "Circular References"
• Sidebar แสดง Circular Reference เกิดที่เซลล์ C11
• สรุป: Formulas > Error Checking > Circular References

• เลือกเซลล์ C11 สูตรพยายามคำนวณตัวเอง

• แก้สูตร C11 เป็น:

• กด Enter
• ไม่มี Circular Reference Error ที่ C11 แล้ว ยอดขายรวม = $17,000

อ่านเพิ่ม: How to Fix Circular Reference Error in Excel (A Detailed Guideline)

2. ใช้ Status Bar แก้ Circular Reference

หา Circular Reference ด้วย "Status Bar" ง่ายที่สุด ใช้ชุดข้อมูลเดียวกัน

ขั้นตอน:

• เปิด Worksheet ที่มี Circular Reference
• ดู "Status Bar" ใต้แท็บ Worksheet
• Status Bar แสดง Circular Reference ที่เซลล์ C11

• แก้สูตร C11 เปลี่ยนช่วงจาก (C5:C11) เป็น (C5:C10)

• กด Enter
• คำสั่งแก้ Error ที่ C11 และคืนยอดรวม

หมายเหตุ: หาก Worksheet มี 2+ เซลล์ที่มี Circular Reference Status Bar จะแสดงแค่ตัวล่าสุดเท่านั้น

3. ใช้ Iterative Calculation แก้ Circular Reference

แก้ Circular Reference ด้วย Iterative Calculations เปิด Iterative Calculation ใน Excel

ขั้นตอน:

• ไปแท็บ File

• เลือก Options

• กล่องโต้ตอบ "Excel Options" ปรากฏ > เลือก Formulas > ติ๊ก "Enable iterative calculation" > ตั้ง Maximum iterations = 1 (หมายถึงสูตรจะ iterate ครั้งเดียวผ่าน C5 ถึง C10)
• กด OK

• ไม่มี Circular Reference Error ที่ C11 คืนยอดขายรวมที่ C11

อ่านเพิ่ม: How to Enable Iterative Calculation in Excel (with Easy Steps)

4. หาและแก้ Circular Reference ด้วย Tracing Methods

ไม่สามารถหาและแก้ Circular Reference ด้วยคลิกเดียว ต้อง Trace ทีละอัน แล้วแก้สูตร ใช้ "Trace Precedents" และ "Trace Dependents"

4.1 Trace Precedents แก้ Circular Reference

"Trace Precedents" ตรวจเซลล์ที่เซลล์ปัจจุบันพึ่งพา วาดเส้นลูกศรบอกว่เซลล์ไหนส่งผลต่อ Active Cell ในข้อมูลตัวอย่าง ผลรวม C5:C10 อยู่ที่ C11 เซลล์ C5:C10 ส่งผลต่อ C11

ขั้นตอน:

• เลือกเซลล์ C11

• ไปแท็บ Formulas > เลือก "Trace Precedents"

• เส้นลูกศรแสดงเซลล์ (C5:C11) ส่งผลต่อ C11 เนื่องจาก C11 พยายามนับตัวเอง จึงเกิด Error

• แก้สูตร C11 เปลี่ยนช่วงเป็น (C5:C10) จาก (C5:C11) สูตรจะเป็น:

• กด Enter คำสั่งลบ Circular Reference ออกจากเซลล์
• ใช้ "Trace Precedents" อีกครั้งที่ C11 จะเห็นว่าตอนนี้เซลล์ (C5:C10) ส่งผลต่อ C11 แทนที่จะเป็น (C5:C11) ก่อนหน้า

หมายเหตุ: Shortcut หา Trace Precedents: 'Alt + T U T'

4.2 Trace Dependents แก้ Circular Reference

"Trace Dependents" หาเซลล์ที่พึ่งพา Active Cell แสดงเซลล์ที่ขึ้นอยู่กับเซลล์ปัจจุบันด้วยเส้นลูกศร

ขั้นตอน:

• เลือกเซลล์ C11

• ไปแท็บ Formulas > เลือก "Trace Dependents" จาก Ribbon

• แสดงเซลล์ (C5:C10) ขึ้นอยู่กับ C11 ด้วยเส้นลูกศร

• แก้สูตร C11 เปลี่ยนช่วงเป็น (C5:C10) จาก (C5:C11) สูตร:

• กด Enter
• ไม่มี Circular Reference ที่ C11 แล้ว

หมายเหตุ: Shortcut หา Trace Dependents: 'Alt + T U D'

อ่านเพิ่ม: How to Find a Circular Reference in Excel (2 Easy Tricks)

สรุป

บทช่วยสอนนี้แสดงวิธีแก้ Excel Circular Reference ที่รายการไม่ได้ ใช้ Practice Workbook ที่มาด้วยเพื่อฝึก หากมีข้อสงสัย คอมเมนต์ได้เลย เราจะตอบเร็วที่สุด ขอให้ติดตาม Microsoft Excel โซลูชันสร้างสรรค์เพิ่มเติมในอนาคต

บทความเกี่ยวข้อง

• How to Remove Circular Reference in Excel (2 Ways)
• How to Allow Circular Reference in Excel (With 2 Suitable Uses)