วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel ใน 4 วิธี

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

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

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

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

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

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

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

คุณกำลังพยายามคูณ 10 จากเซลล์ บี1 และใส่ผลลัพธ์ลงในเซลล์ บี1 นั่นเอง

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=SUM(A2:A10)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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