ขณะพยายามใส่สูตรในชีต Excel ของคุณ มันไม่ทำงานด้วยเหตุผลบางอย่าง แทนที่จะเป็นเช่นนั้น มันแจ้งเตือนคุณเกี่ยวกับ circular reference ล้านๆ ผู้ใช้ติดกับปัญหาเดียวกันทุกวัน เนื่องจากขอให้ฟังก์ชัน Excel คำนวณเซลล์ของตัวเอง บทเรียน מהירה นี้จะแนะนำคุณผ่านพื้นฐานของ circular reference error ใน Excel และทำไมคุณควรหลีกเลี่ยงพวกมัน คุณยังจะได้เรียนรู้วิธีวิเคราะห์ ค้นหา และแก้ไข circular reference error ในสเปรดชีต Excel รวมถึงวิธีเปิดใช้งานและใช้สูตร circular หากตัวเลือกก่อนหน้าไม่พร้อมใช้งาน
Circular Reference คืออะไร?
เมื่อคุณได้สูตรในเซลล์แล้ว คุณสร้าง circular reference ขึ้นมา - ซึ่งทำการคำนวณโดยใช้อ้างอิงเซลล์ (ที่สูตรถูกใส่) เหมือนงูไล่หางตัวเอง
คำพูดอีกแบบหนึ่ง circular reference เกิดขึ้นเมื่อสูตร ไม่ว่าจะโดยตรงหรือโดยอ้อม อ้างอิงกลับไปที่เซลล์ของตัวเอง ทำให้เกิดลูปการคำนวณที่ไม่มีที่สิ้นสุด ค่าของเซลล์จะเปลี่ยนแปลงทุกครั้งที่ลูปอ้างอิงไม่มีที่สิ้นสุดไม่ถูกหยุด
นี่คือตัวอย่างเพื่อช่วยให้คุณเข้าใจ
ในตารางต่อไปนี้ เราแสดงสถิติรายได้ของหนึ่งในแบรนด์ใหญ่ที่สุดของโลก คือ Apple ตลอดทศวรรษที่ผ่านมา ตัวอย่างเช่น ใช้ SUM Function ที่ฟิลด์ C16 เรากำลังพยายามคำนวณรายได้รวมของพวกเขา อย่างไรก็ตาม เราให้ช่วง C5:C16 ให้กับ SUM Function
มันสร้างลูปไม่มีที่สิ้นสุด เพราะช่วงที่ให้มาก็มีเซลล์ C16 (ซึ่งเป็นที่ที่ผลลัพธ์ต้องเขียน) ด้วยเช่นกัน Excel จะเพิ่มค่าใหม่เข้าไปในเซลล์ C16 ตลอดเวลา ซึ่งจะเพิ่มขึ้นแบบเอ็กซ์โพเนนเชียล
สิ่งนี้กระตุ้นให้ Excel แสดงคำเตือน circular reference ตามภาพด้านล่าง
📝 หมายเหตุ.
- นี่ไม่ใช่ข้อผิดพลาด เพราะมันไม่หยุดหรือแจ้งเตือนผู้ใช้ให้เปลี่ยนการคำนวณ มันเป็นการแจ้งเตือนว่าการคำนวณของผู้ใช้น่าจะผิดพลาด
- ในหลายกรณี เมื่อคุณใส่สูตรมากกว่าหนึ่งสูตรที่มี circular reference Excel จะไม่แสดงข้อความเตือนซ้ำๆ
ใช้ Iterative Calculations เพื่อเปิด/ปิด Circular References Error
Iterative computations เป็นฟีเจอร์ที่น่าสนใจใน Excel หากผู้ใช้ปิดใช้งาน iterative calculations Excel จะแสดงการแจ้งเตือน Circular Reference และแสดง 0 แทนผลลัพธ์จริงในเซลล์ เพราะมันเป็นลูปไม่มีที่สิ้นสุด
หากคุณกำลังทำงานกับชุดข้อมูลขนาดใหญ่ และต้องการให้ Excel ให้คุณทำงานต่อไปโดยไม่รบกวนด้วยคำเตือน circular reference คุณอาจใช้ Iterative Calculations
เปิด/ปิด Iterative Calculations
ในภาพหน้าจอด้านล่าง คุณเห็นว่ามี circular reference ในเซลล์ C16 แสดงใน status bar ของเวิร์กชีต ดังนั้น มันทำให้ได้ศูนย์ (0) ในเซลล์ C16 ตอนนี้ หากคุณต้องการให้งานของคุณทำงานโดยไม่มีปัญหา คุณต้อง เปิดใช้งาน Iterative calculations
มาดูวิธีเปิดหรือปิด iterative calculations ใน Excel ตอนนี้ที่เราเข้าใจแล้วว่ามันทำงานอย่างไร ทำตามขั้นตอนด้านล่างเพื่อเปิดหรือปิด iterative calculations ใน Excel
ขั้นตอน:
- ไปที่แท็บ File ⇨ Options ⇨ Formulas
- ติ๊กถูกกล่อง Enable iterative calculation
- กด Enter
ผลคือ คุณเห็นว่า circular reference dialog สว่างส่องด้วย calculate ตามภาพหน้าจอด้านล่าง ตอนนี้ มีค่าในเซลล์ 16 แทนศูนย์ (0)
เพราะว่า เซลล์แต่ละเซลล์ที่เกี่ยวข้องกับ circular reference ก่อนหน้านี้ จะถูกคำนวณ
พารามิเตอร์ Maximum Iterations & Maximum Change
Iterative Calculations มีสองพารามิเตอร์:
Maximum Iterations
ลูปนี้จะทำซ้ำเมื่อ Excel คำนวณผลลัพธ์สุดท้าย สิ่งนี้อยู่ภายใต้การควบคุมของคุณทั้งหมด โปรดจำไว้ว่า การวนซ้ำมากขึ้นเท่ากับ Excel ทำงานมากขึ้น ซึ่งหมายความว่าเครื่องจะต้องการทรัพยากรและกำลังประมวลผลมากขึ้น จะใช้เวลานานขึ้นด้วย
Maximum Change
Maximum change คือตัวเลขที่ต้องบรรลุเพื่อให้การวนซ้ำดำเนินต่อไป มันเกี่ยวกับความแม่นยำของผลลัพธ์ ทำให้ค่านี้ต่ำที่สุดเท่าที่จะเป็นไปได้ เพื่อให้ได้ผลลัพธ์ที่แม่นยำ โดยค่าเริ่มต้น maximum change ถูกตั้งไว้ที่ 0.001
ทำไมเราควรหลีกเลี่ยงการเปิดใช้งาน Iterative Calculations โดยเจตนา?
การใช้ Circular References ใน Excel ไม่ใช่แนวทางที่ดี เมื่อคุณเปิดใช้งาน iterative calculations การใช้ circular references โดยเจตนาเป็นวิธีลับที่ซับซ้อนในการทำให้สิ่งต่างๆ ทำงาน ซึ่งกินทรัพยากรและกำลังคำนวณมาก เมื่อคุณต้องแก้ไขบางส่วน มันมักให้ผลลัพธ์ที่ไม่พึงประสงค์หรือไม่แม่นยำ ซึ่งอาจทำให้หงุดหงิด
ค้นหา Circular References Error ใน Excel
เมื่อเกิด circular reference error คุณจะได้รับคำเตือน แต่คุณยังต้องค้นหาว่าข้อผิดพลาดเกิดขึ้นในเซลล์ไหน จะง่ายต่อการจัดการและแก้ไขข้อผิดพลาดเมื่อคุณรู้ตำแหน่งเซลล์ที่แน่นอนของข้อผิดพลาด
เทคนิคเหล่านี้มีประโยชน์มากเมื่อจัดการกับชุดข้อมูลขนาดใหญ่
1. ใช้ Error Checking Drop-down เพื่อค้นหา Circular Reference Error ใน Excel
นี่คือวิธีใช้ Ribbon ใน Excel เพื่อค้นหา circular references
ขั้นตอนที่ 1:
- ไปที่แท็บ Formulas
- คลิกที่เมนูดรอปดาวน์ Error Checking
ขั้นตอนที่ 2:
- เลือก Circular References จากตัวเลือก
Excel จะแสดง circular references ทั้งหมดของเวิร์กชีตในส่วนนี้
เห็นได้ในภาพหน้าจอข้างต้น ชุดข้อมูลที่เราใช้มี circular reference หนึ่งในเซลล์ C16
อ่านเพิ่มเติม: วิธีค้นหา Circular Reference ใน Excel (2 เทคนิคง่าย)
2. ใช้ Status Bar เพื่อค้นหา Circular Reference Error ใน Excel
การค้นหา circular references โดยใช้ status bar ค่อนข้างง่าย หากเวิร์กชีตมี circular reference ผู้ใช้จะสามารถมองเห็นได้ในแถบสถานะด้านล่างชื่อเวิร์กชีต
📝 หมายเหตุ.
- ฟีเจอร์นี้ถูกปิดใช้งานเมื่อตัวเลือก Iterative Calculation ถูกเปิดใช้งาน ดังนั้นคุณต้องปิดมันก่อนเริ่มตรวจสอบสมุดงานเพื่อ circular references
- มันแสดงเฉพาะ circular reference ล่าสุด และอนุญาตให้ผู้ใช้ย้อนกลับจากที่นั่น
อ่านเพิ่มเติม: วิธีอนุญาต Circular Reference ใน Excel (พร้อม 2 การใช้งานที่เหมาะสม)
แก้ไข Circular Reference Error ใน Excel
Circular references ไม่สามารถแก้ไขใน Excel ด้วยการคลิกเดียวได้ ในการจัดการ circular references คุณต้องลบพวกมันทีละอัน
ตัวอย่างเช่น หลังจากค้นหาข้อผิดพลาดในเซลล์ C16 คุณต้องเขียนสูตรที่ถูกต้องใหม่ หรือเลือกช่วงที่ถูกต้อง
ขั้นตอนที่ 1:
- เลือกช่วงที่ถูกต้องเพื่อแก้ไขข้อผิดพลาดในเซลล์ C16
ขั้นตอนที่ 2:
- กด Enter ในเซลล์ C16 เพื่อดูผลลัพธ์
อ่านเพิ่มเติม: วิธีลบ Circular Reference ใน Excel (2 วิธี)
ติดตามความสัมพันธ์ระหว่างสูตรและเซลล์
เมื่อ Excel circular reference ไม่ชัดเจน คุณอาจใช้ฟีเจอร์ Trace Precedents และ Trace Dependents เพื่อวาดเส้นหนึ่งหรือหลายเส้น เพื่อแสดงว่เซลล์ไหนบ้างที่มีอิทธิพลหรือได้รับผลกระทบจากเซลล์ที่เลือก
ในภาพหน้าจอด้านล่าง เราแสดงรายได้ที่คาดการณ์ของ Apple ในปี 2022 ตอนนี้เราต้องการ Sum รายได้รวมจากปี 2011 ถึง 2022 เพื่อทำเช่นนี้ เราพิมพ์สูตรต่อไปนี้ในเซลล์ D10
แต่มันมี circular reference error ในเซลล์ C16 จึงจะมีค่าข้อผิดพลาดในเซลล์ D10 ในปัญหานี้ เราใช้ Trace Precedents และ Trace Dependents ติดตามความสัมพันธ์ระหว่างสูตรและเซลล์
Trace Precedents เพื่อเชื่อมโยง Circular Reference Error ใน Excel
ติดตามเซลล์ที่จัดหาข้อมูลให้สูตร วาดเส้นเพื่อแสดงว่เซลล์ไหนบ้างที่มีผลกระทบต่อเซลล์ที่เลือก ในการทำเช่นนี้ ทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- ไปที่แท็บ Formulas ใน Excel
- เลือก Trace Precedents
ดังนั้น คุณจะเห็นลูกศรที่ระบุเซลล์ที่มีผลกระทบต่อเซลล์ที่ใช้งานอยู่
📝 หมายเหตุ. Shortcut สำหรับ Trace Precedents: Alt+T U T
Trace Dependents เพื่อเชื่อมโยง Circular Reference Error ใน Excel
วาดเส้นระบุเซลล์ที่ได้รับอิทธิพลจากเซลล์ที่เลือก และติดตามเซลล์ที่พึ่งพาเซลล์ที่ใช้งานอยู่ ในการทำเช่นนี้ ทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- ไปที่แท็บ Formulas ใน Excel
- เลือก Trace Decedents
ผลคือ มันแสดงว่เซลล์ไหนบ้างที่มีสูตรอ้างอิงถึงเซลล์ที่ใช้งานอยู่ปัจจุบัน
📝 หมายเหตุ. Shortcut สำหรับ Trace Decedents: Alt+T U D
สรุป
สรุปแล้ว ผมหวังว่าบทความนี้จะให้คำแนะนำโดยละเอียดในการแก้ไข circular reference error ใน Excel ทุกขั้นตอนควรเรียนรู้และนำไปใช้กับชุดข้อมูลของคุณ ดูสมุดงานฝึกหัดและทดสอบทักษะเหล่านี้ เราถูกกระตุ้นให้ทำบทช่วยสอนแบบนี้ต่อไปเนื่องจากการสนับสนุนที่มีค่าของคุณ
หากมีคำถาม - ถามได้เลยครับ นอกจากนี้ รู้สึกสบายใจที่จะทิ้งความคิดเห็นในส่วนด้านล่าง
เรา ทีม Exceldemy พร้อมตอบสนองต่อคำถามของคุณเสมอ
อยู่กับเราและเรียนรู้ต่อไป
บทความที่เกี่ยวข้อง
- แก้ไข Circular Reference ที่ไม่สามารถรายการได้ใน Excel (4 วิธีง่าย)
