วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel เพื่อหลีกเลี่ยงข้อมูลที่ผิดพลาด

คุณเห็นข้อผิดพลาดการอ้างอิงแบบวงกลมบนแผ่นงาน Excel และสงสัยว่าแผ่นงานเสียหรือไม่ เชื่อฉัน! คุณไม่ใช่คนแรกที่เผชิญกับข้อผิดพลาดของ Excel นี้ ผู้ใช้ Excel หลายแสนรายพบข้อผิดพลาดในการอ้างอิงแบบวงกลมทุกวัน ไม่ต้องกังวล! ต่อไปนี้คือวิธีค้นหาการอ้างอิงแบบวงกลมใน Excel และแก้ไขปัญหาเหล่านั้น

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

เมื่อมีคนเปลี่ยนค่าเซลล์ของการอ้างอิงเซลล์ เวิร์กชีตจะอัปเดตตัวเองโดยอัตโนมัติ สิ่งนี้เรียกว่าการวิเคราะห์ข้อมูลอัตโนมัติ และนักวิเคราะห์ข้อมูลชอบเล่นกับการอ้างอิงเซลล์

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

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

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

Excel พบข้อผิดพลาดในการอ้างอิงแบบวงกลมเมื่อเห็นว่าฟังก์ชันหนึ่งหรือหลายฟังก์ชันของเวิร์กชีตอ้างอิงกลับไปยังที่อยู่เซลล์ที่สร้างฟังก์ชันนั้น

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

คุณกำลังพยายามคูณ10จากเซลล์B1แล้วใส่ผลลัพธ์ลงในเซลล์B1เอง

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

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

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

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

นอกจากนี้ การอ้างอิงแบบวงกลมยังลดความเร็วของการคำนวณภายในแอป Excel ลงอย่างมาก ในกรณีที่ร้ายแรง ข้อผิดพลาดมากอาจทำให้พีซีทำงานช้ามากเช่นกันหากไม่จัดการในทันที

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

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

เมื่อใดที่คุณเห็นข้อผิดพลาดการอ้างอิงแบบวงกลมใน Excel

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

เมื่อต้องการหลีกเลี่ยงความเสียหายใดๆ ที่เกิดจากการอ้างอิงแบบวงกลมในสูตร Excel คุณต้องรู้ว่าเมื่อใดที่ปรากฏขึ้นและเมื่อใดที่ไม่ปรากฏ ค้นหาด้านล่างกรณี:

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

เมื่อใดที่คุณเห็นข้อผิดพลาดการอ้างอิงแบบวงกลมใน Excel

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

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

ประเภทของการอ้างอิงแบบวงกลมใน Excel

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

ตัวอย่างการใช้การอ้างอิงแบบวงกลม

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

การอ้างอิงแบบวงกลมที่ไม่ได้ตั้งใจใน Excel

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

=SUM(A2:A10)

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

ตัวอย่างของการอ้างอิงแบบวงกลมทางอ้อม

• การอ้างอิงแบบวงกลมทางอ้อมยังเป็นที่นิยมและเกิดขึ้นเมื่อคุณสร้างสูตรใหม่ในเซลล์ใดๆ เช่น เซลล์A1และสูตรใหม่นี้จะใช้ฟังก์ชันอื่นในเซลล์อื่น เช่น เซลล์B1ซึ่งใช้เซลล์A1เป็นการอ้างอิง อยู่แล้ว .

ตอนนี้คุณเข้าใจพื้นฐานของการอ้างอิงแบบวงกลมใน Excel แล้ว มาดูวิธีค้นหาการอ้างอิงแบบวงกลมใน Excel ด้านล่าง:

วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel: จากแถบสถานะ

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

• การอ้างอิงแบบวงกลม: C3  (หรือการอ้างอิงเซลล์อื่นๆ) เมื่อคุณอยู่บนแผ่นงานที่มีสูตรแบบวงกลมในสมุดงาน

การแสดงการอ้างอิงแบบวงกลมในแถบสถานะของ Excel

• คุณจะเห็นการอ้างอิงแบบวงกลม ก็ต่อ เมื่อคุณอยู่ในเวิร์กชีตที่ไม่มีสูตรใดๆ เลย แต่เวิร์กบุ๊กมีสูตรแบบวงกลมไม่กี่สูตร

วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel: การใช้การตรวจสอบข้อผิดพลาด

เป็นวิธีที่ดีที่สุดในการตรวจสอบการอ้างอิงแบบวงกลมในเวิร์กบุ๊กหรือเวิร์กชีต นี่คือขั้นตอนที่คุณต้องรู้:

• เปิดแผ่นงานที่คุณสงสัยว่ามีสูตรแบบวงกลม
• คลิก เมนู Formulasบน Ribbon ของ Excel

ใช้การตรวจสอบข้อผิดพลาดสำหรับวิธีค้นหาการอ้างอิงแบบวงกลมใน Excel

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

วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel: จากการติดตามสูตร

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

• เลือกเซลล์อ้างอิงหรือเซลล์ที่มีสูตร
• ไปที่ แท็บ Formulasบนเมนู Ribbon ของ Excel แล้วคลิกTrace Precedents
• Excel จะเน้นเซลล์ที่มีผลต่อผลลัพธ์ของสูตรในเซลล์ปัจจุบันด้วยลูกศร

ใช้การติดตามสูตรสำหรับวิธีค้นหาการอ้างอิงแบบวงกลมใน Excel

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

วิธีนี้มีความสำคัญอย่างยิ่งเมื่อคุณลักษณะการคำนวณซ้ำทำงานในเวิร์กชีต Excel

วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel: การใช้ตัวเลือกของ Excel

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

วิธีปิดใช้งานการคำนวณซ้ำใน Excel

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

วิธีแก้ไขการอ้างอิงแบบวงกลมใน Excel

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

วิธีแก้ไขการอ้างอิงแบบวงกลมใน Excel

อีกวิธีที่ง่ายคือการลบสูตรออกจากเซลล์ปัจจุบันและวางไว้ที่อื่นในเวิร์กชีตเดียวกัน

วิธีค้นหาการอ้างอิงแบบวงกลมใน Google ชีต (วิธีโบนัส)

เช่นเดียวกับ Excel Google ชีตไม่อนุญาตให้มีการอ้างอิงแบบวงกลมในสูตรอย่างน้อยหนึ่งสูตร หากคุณไม่ได้เปิดใช้งานคุณลักษณะการคำนวณซ้ำ ดังนั้น Google ชีตจะแสดง #REF ทันที! ข้อผิดพลาดในเซลล์ที่มีสูตรแบบวงกลม

Google ชีต วิธีค้นหาการอ้างอิงแบบวงกลม

ดังนั้น คุณสามารถเลือกเซลล์ทั้งหมดที่มี #REF! คำเตือนในการค้นหาและแก้ไขการอ้างอิงแบบวงกลมใน Google ชีต

การอ้างอิงแบบวงกลมของ Excel: คำสุดท้าย

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

ถัดไป  เทมเพลตปฏิทิน Excel ที่ดีที่สุดสำหรับปีต่างๆและ เทมเพลต  งบประมาณ Excel ที่ดีที่สุด