วิธีการแก้ไขข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel (คำแนะนำโดยละเอียด)

ขณะพยายามป้อนสูตรในแผ่นงาน Excel จะไม่ทำงานด้วยเหตุผลบางประการ แต่จะแจ้งให้คุณทราบถึงการอ้างอิงแบบวงกลม . ผู้ใช้หลายล้านรายประสบปัญหาเดียวกันทุกวัน อันเป็นผลมาจากการขอให้ฟังก์ชัน Excel คำนวณเซลล์ของตัวเอง บทเรียนสั้นๆ นี้จะแนะนำคุณเกี่ยวกับพื้นฐานของข้อผิดพลาดในการอ้างอิงแบบวงกลม ใน Excel และเหตุผลที่คุณควรหลีกเลี่ยง คุณจะได้เรียนรู้วิธีวิเคราะห์ ค้นหา และแก้ไขข้อผิดพลาดในการอ้างอิงแบบวงกลม ในสเปรดชีต Excel ตลอดจนวิธีการเปิดใช้งานและใช้สูตรแบบวงกลม หากไม่มีตัวเลือกก่อนหน้านี้

การอ้างอิงแบบวงกลมคืออะไร

เมื่อคุณลงเอยด้วยสูตรในเซลล์ คุณจะสร้าง การอ้างอิงแบบวงกลม – ซึ่งทำการคำนวณโดยใช้การอ้างอิงเซลล์ (ที่ป้อน) เหมือนกับงูไล่หางของมันเอง

กล่าวอีกนัยหนึ่ง การอ้างอิงแบบวงกลม เกิดขึ้นเมื่อสูตร ไม่ว่าจะทางตรงหรือทางอ้อม อ้างถึงเซลล์ของตัวเอง ส่งผลให้มีการคำนวณวนซ้ำไม่รู้จบ ค่าของเซลล์จะเปลี่ยนไปทุกครั้งที่ไม่มีการหยุดการวนซ้ำอ้างอิงที่ไม่สิ้นสุด

นี่คือตัวอย่างที่จะช่วยให้คุณเข้าใจ

ในตารางต่อไปนี้ เราได้แสดงสถิติของหนึ่งในแบรนด์ที่ใหญ่ที่สุดในโลกของ Apple ที่สร้างรายได้ในช่วงทศวรรษที่ผ่านมา ตัวอย่างเช่น การใช้ ฟังก์ชัน SUM C16 เรากำลังพยายามคำนวณรายได้ทั้งหมดของพวกเขา อย่างไรก็ตาม เราให้ช่วง C5:C16 ไปยัง ฟังก์ชัน SUM .

มันสร้างการวนซ้ำไม่รู้จบเพราะช่วงที่ให้มายังมี C16 เซลล์ (ซึ่งจำเป็นต้องเขียนผลลัพธ์) ดังนั้น Excel จะเพิ่มค่าใหม่ให้กับเซลล์ C16 ซึ่งจะเพิ่มมากขึ้นเรื่อยๆ

ซึ่งจะทริกเกอร์ Excel ให้แสดงคำเตือนการอ้างอิงแบบวงกลม ดังที่แสดงในภาพด้านล่าง

📝 หมายเหตุ

• นี่ไม่ใช่ข้อผิดพลาดเพราะจะไม่หยุดหรือแจ้งให้ผู้ใช้แก้ไขการคำนวณ เป็นการแจ้งเตือนที่ระบุว่าการคำนวณของผู้ใช้มีแนวโน้มว่าจะไม่ถูกต้อง
• ในหลายกรณี เมื่อคุณป้อนสูตรมากกว่าหนึ่งสูตรด้วยการอ้างอิงแบบวงกลม , Excel จะไม่แสดงข้อความเตือนซ้ำๆ

ใช้การคำนวณซ้ำเพื่อเปิดใช้งาน/ปิดใช้งานข้อผิดพลาดการอ้างอิงแบบวงกลม

การคำนวณซ้ำเป็นคุณลักษณะที่น่าสนใจใน Excel . หากผู้ใช้ปิดใช้งาน การคำนวณซ้ำ , Excel แสดง การอ้างอิงแบบวงกลม แจ้งเตือนและแสดง 0 แทนที่จะเป็นผลลัพธ์จริงในเซลล์ เพราะมันเป็นการวนซ้ำที่ไม่สิ้นสุด จึงเป็นเช่นนี้

หากคุณกำลังทำงานกับชุดข้อมูลขนาดใหญ่และต้องการ Excel เพื่อนั่งลงและปล่อยให้คุณทำงานต่อไปโดยไม่รบกวนคุณด้วยคำเตือนอ้างอิงแบบวงกลม คุณสามารถใช้ การคำนวณซ้ำ .

เปิด/ปิดการคำนวณซ้ำ

ในภาพหน้าจอด้านล่าง คุณจะเห็นว่ามี การอ้างอิงแบบวงกลม ในเซลล์ C16 ระบุไว้ใน สถานะ แถบของแผ่นงาน ส่งผลให้เป็นศูนย์ (0 ) ในเซลล์ C16 . ตอนนี้ ถ้าคุณต้องการให้งานของคุณทำโดยไม่มีปัญหาใดๆ คุณต้อง เปิดใช้งานการคำนวณแบบวนซ้ำ .

มาดูวิธีการเปิดหรือปิดใช้งาน การคำนวณซ้ำ ใน Excel ตอนนี้เราได้กำหนดวิธีการทำงานแล้ว ในการเปิดหรือปิดใช้งาน การคำนวณซ้ำ ใน Excel ให้ทำตามขั้นตอนด้านล่าง

ขั้นตอน:

• ไปที่ ไฟล์ แท็บ ⇨ ตัวเลือก ⇨ สูตร .
• ทำเครื่องหมายที่ช่องทำเครื่องหมาย เปิดใช้งานการคำนวณซ้ำ .
• กด Enter .

ดังนั้น จะเห็นว่า การอ้างอิงแบบวงกลม กล่องโต้ตอบสว่างด้วย คำนวณ ตามที่แสดงในภาพหน้าจอด้านล่าง ตอนนี้ มีค่าในเซลล์ 16 แทนที่จะเป็นศูนย์ (0 )

ด้วยเหตุนี้ แต่ละเซลล์ก่อนหน้านี้เกี่ยวข้องกับ การอ้างอิงแบบวงกลม จะถูกคำนวณ

การวนซ้ำสูงสุด &พารามิเตอร์การเปลี่ยนแปลงสูงสุด

การคำนวณซ้ำมีสองพารามิเตอร์:

จำนวนครั้งสูงสุด

วนซ้ำนี้จะถูกทำซ้ำเมื่อ Excel คำนวณผลลัพธ์สุดท้าย ทั้งหมดนี้อยู่ภายใต้การควบคุมของคุณ โปรดทราบว่าการทำซ้ำมากขึ้นเท่ากับการทำงานของ Excel มากขึ้น ซึ่งหมายความว่าเครื่องจะต้องใช้วัสดุและความสามารถในการประมวลผลมากขึ้น นอกจากนี้ยังใช้เวลานานขึ้นอีกด้วย

การเปลี่ยนแปลงสูงสุด

การเปลี่ยนแปลงสูงสุดคือจำนวนที่ต้องทำเพื่อให้การวนซ้ำดำเนินต่อไป มันคือทั้งหมดที่เกี่ยวกับความแม่นยำของผลลัพธ์ ทำให้ค่านี้ต่ำที่สุดเท่าที่จะเป็นไปได้เพื่อให้ได้ผลลัพธ์ที่แม่นยำ โดยค่าเริ่มต้น การเปลี่ยนแปลงสูงสุดตั้งไว้ที่ 0.001

เหตุใดเราจึงควรหลีกเลี่ยงการเปิดใช้การคำนวณซ้ำโดยเจตนา

ไม่ควรใช้ การอ้างอิงแบบวงกลม ใน Excel เมื่อคุณเปิดใช้งานการคำนวณแบบวนซ้ำ การใช้การอ้างอิงแบบวงกลมโดยเจตนาเป็นวิธีที่ยุ่งยากในการทำให้สิ่งต่าง ๆ ทำงานได้ซึ่งกินทรัพยากรจำนวนมากและพลังในการคำนวณ เมื่อคุณต้องแก้ไขบางอย่าง มักจะให้ผลที่ไม่พึงประสงค์หรือไม่ถูกต้อง ซึ่งอาจทำให้หงุดหงิด

ค้นหาข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel

เมื่อ อ้างอิงแบบวงกลม เกิดข้อผิดพลาด คุณจะได้รับคำเตือน แต่คุณยังต้องค้นหาว่าปัญหาเกิดขึ้นที่เซลล์ใด การจัดการและแก้ไขข้อผิดพลาดจะง่ายขึ้นเมื่อคุณทราบตำแหน่งเซลล์เฉพาะของข้อผิดพลาด

เทคนิคเหล่านี้มีประโยชน์มากเมื่อต้องรับมือกับชุดข้อมูลขนาดใหญ่

1. ใช้การตรวจสอบข้อผิดพลาดแบบเลื่อนลงเพื่อค้นหาข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel

ต่อไปนี้คือวิธีใช้ Ribbon ใน Excel เพื่อค้นหาการอ้างอิงแบบวงกลม .

ขั้นตอนที่ 1:

• ไปที่ แท็บสูตร
• คลิกที่ การตรวจสอบข้อผิดพลาด เมนูแบบเลื่อนลง

ขั้นตอนที่ 2:

• เลือก การอ้างอิงแบบวงกลม จากตัวเลือก

Excel จะแสดงการอ้างอิงแบบวงกลมของเวิร์กชีตทั้งหมดในส่วนนี้

ดังที่คุณเห็นในภาพหน้าจอด้านบน สเปรดชีตที่เราใช้มีการอ้างอิงแบบวงกลมหนึ่งรายการในเซลล์ C16 .

อ่านเพิ่มเติม: วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel (2 เคล็ดลับง่ายๆ)

2. ใช้แถบสถานะเพื่อค้นหาข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel

การค้นหา การอ้างอิงแบบวงกลม . ค่อนข้างง่าย โดยใช้แถบสถานะ . ถ้าเวิร์กชีตมีการอ้างอิงแบบวงกลม ผู้ใช้จะสามารถดูได้ในแถบสถานะด้านล่างชื่อเวิร์กชีต

📝 หมายเหตุ

• คุณลักษณะนี้ถูกปิดใช้งานเมื่อ การคำนวณซ้ำ เปิดตัวเลือกไว้ ดังนั้นคุณต้องปิดก่อนที่จะเริ่มตรวจสอบสมุดงานเพื่อหาการอ้างอิงแบบวงกลม
• แสดงเฉพาะการอ้างอิงแบบวงกลมล่าสุดและอนุญาตให้ผู้ใช้ย้อนกลับจากที่นั่น

อ่านเพิ่มเติม: วิธีอนุญาตการอ้างอิงแบบวงกลมใน Excel (ด้วย 2 การใช้งานที่เหมาะสม)

แก้ไขข้อผิดพลาดการอ้างอิงแบบวงกลมใน Excel

การอ้างอิงแบบวงกลม ไม่สามารถแก้ไขได้ใน Excel ด้วยการคลิกเพียงครั้งเดียว เพื่อจัดการกับการอ้างอิงแบบวงกลม คุณต้องลบออกทีละรายการ

ตัวอย่างเช่น หลังจากพบข้อผิดพลาดในเซลล์ C16 คุณต้องเขียนสูตรที่ถูกต้องใหม่หรือเลือกช่วงที่ถูกต้อง

ขั้นตอนที่ 1:

• เลือกช่วงที่ถูกต้องเพื่อแก้ไขข้อผิดพลาดในเซลล์ C16 .

ขั้นตอนที่ 2:

• กด ป้อน ในเซลล์ C16 เพื่อดูผลลัพธ์

อ่านเพิ่มเติม: วิธีลบการอ้างอิงแบบวงกลมใน Excel (2 วิธี)

ติดตามความสัมพันธ์ระหว่างสูตรและเซลล์

เมื่อ Excel การอ้างอิงแบบวงกลมไม่ชัดเจน คุณสามารถใช้ Trace Precedents และ ติดตามผู้อยู่ในอุปการะ คุณลักษณะเพื่อวาดเส้นอย่างน้อยหนึ่งเส้นเพื่อแสดงว่าเซลล์ใดส่งผลกระทบหรือได้รับผลกระทบจากเซลล์ที่เลือก

ในภาพหน้าจอด้านล่าง เราได้แสดงรายได้ที่คาดหวังของ Apple ในปี 2022 . ตอนนี้เราต้องการสรุปรายได้รวมจากปี 2011 ถึง 2022 . เมื่อต้องการทำเช่นนี้ เราพิมพ์สูตรต่อไปนี้ในเซลล์ D10 .

แต่เนื่องจากเรามี การอ้างอิงแบบวงกลม ข้อผิดพลาดในเซลล์ C16 จะมีค่าผิดพลาดในเซลล์ D10 . ในฉบับนี้ เราใช้ Trace Precedents และ ติดตามผู้อยู่ในอุปการะ ติดตามความสัมพันธ์ระหว่างสูตรและเซลล์

ติดตามแบบอย่างที่เกี่ยวข้องกับข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel

ติดตามเซลล์ที่ให้ข้อมูลกับสูตร วาดเส้นเพื่อแสดงว่าเซลล์ใดมีผลกระทบต่อเซลล์ที่เลือก โดยทำตามขั้นตอนด้านล่าง

ขั้นตอน:

• ไปที่ สูตร แท็บใน Excel
• เลือก Trace Precedents .

ดังนั้น คุณจะเห็นลูกศรที่ระบุว่าเซลล์ส่งผลต่อเซลล์ที่ใช้งานอยู่

📝 หมายเหตุ ทางลัดไปยัง Trace Precedents:Alt +ท U T

ติดตามผู้อยู่ในอุปการะที่เกี่ยวข้องกับข้อผิดพลาดในการอ้างอิงแบบวงกลมใน Excel

ลากเส้นที่ระบุเซลล์ที่ได้รับอิทธิพลจากเซลล์ที่เลือก และติดตามเซลล์ที่ขึ้นอยู่กับเซลล์ที่ใช้งานอยู่ โดยทำตามขั้นตอนด้านล่าง

ขั้นตอน:

• ไปที่ สูตร แท็บใน Excel
• เลือก ติดตามผู้มีสิทธิ์ .

ด้วยเหตุนี้ จึงแสดงว่าเซลล์ใดมีสูตรที่อ้างอิงถึงเซลล์ที่ใช้งานอยู่ในปัจจุบัน

📝 หมายเหตุ ทางลัดไปยัง Trace Decedents:Alt +ท คุณดี

บทสรุป

สรุป ฉันหวังว่าบทความนี้จะให้คำแนะนำโดยละเอียดเพื่อแก้ไขการอ้างอิงแบบวงกลม ข้อผิดพลาดใน Excel ขั้นตอนทั้งหมดเหล่านี้ควรเรียนรู้และนำไปใช้กับชุดข้อมูลของคุณ ดูสมุดแบบฝึกหัดและนำทักษะเหล่านี้ไปทดสอบ เรามีแรงจูงใจที่จะทำบทแนะนำแบบนี้ต่อไปเนื่องจากการสนับสนุนที่มีค่าของคุณ

หากคุณมีคำถามใด ๆ - อย่าลังเลที่จะถามเรา นอกจากนี้ โปรดแสดงความคิดเห็นในส่วนด้านล่างนี้

พวกเรา เก่งมาก ทีมงานจะตอบคำถามของคุณเสมอ

อยู่กับเราและเรียนรู้ต่อไป

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

• แก้ไขการอ้างอิงแบบวงกลมที่ไม่สามารถแสดงรายการใน Excel (4 วิธีง่ายๆ)