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

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

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

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

สารบัญ

• การอ้างอิงแบบวงกลมคืออะไร
• หน้าต่างข้อความแสดงข้อผิดพลาดการอ้างอิงแบบวงกลม
• วิธีค้นหาการอ้างอิงแบบวงกลมใน Excel
• วิธีค้นหาการอ้างอิงแบบวงกลมด้วยแถบสถานะของ Excel
• วิธีค้นหาการอ้างอิงแบบวงกลมที่ซ่อนอยู่ใน Excel
  • วิธีการใช้เครื่องมือติดตามขึ้นอยู่กับ
  • ติดตามเครื่องมือแบบอย่าง
• 2 วิธีในการลบการอ้างอิงแบบวงกลมใน Excel
  • 2. ใช้สูตรทางเลือก
• 5 เคล็ดลับในการป้องกันการอ้างอิงแบบวงกลม
  • 1. ระมัดระวังเมื่อเลือกช่วง
  • 2. วางแผนสูตรของคุณอย่างรอบคอบ
  • 3. ใช้สูตรทางเลือก
  • 4. แบ่งสูตรที่ซับซ้อนออกเป็นส่วนย่อยๆ
  • 5. แก้ไขปัญหาตามที่คุณไป
• วิธีเปิดใช้งานการคำนวณซ้ำ & ทำไมต้องกังวล?

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

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

สิ่งนี้เข้าใจง่ายขึ้นด้วยตัวอย่างง่ายๆ

สมมติว่าคุณมีตัวเลขสองตัวในเซลล์ A1 และ B1 คุณต้องการใช้สูตรในเซลล์ A3 ที่บวกเลขสองตัวเข้าด้วยกัน สูตรในเซลล์ A3 ควรมีลักษณะดังนี้:

=A1 + B1

อย่างไรก็ตาม หากคุณทำผิดพลาดและใส่ C1 ในการคำนวณ ตอนนี้คุณจะมีการอ้างอิงแบบวงกลม:

=A1 + B1 + C1

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

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

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

หน้าต่างข้อความแสดงข้อผิดพลาดการอ้างอิงแบบวงกลม

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

นี่คือข้อความอธิบาย:

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

เมื่อคุณเห็นกล่องโต้ตอบนี้ คุณควรคลิก "ตกลง" และใช้วิธีการในบทความนี้เพื่อแก้ไขปัญหา

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

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

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

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

รูปภาพนี้แสดงตำแหน่งที่ตั้งภายในกลุ่มการตรวจสอบสูตร

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

1. ไปที่แท็บสูตร

1. คลิกที่ดร็อปดาวน์การตรวจสอบข้อผิดพลาดในกลุ่มการตรวจสอบสูตร

2. เลือก “การอ้างอิงแบบวงกลม” จากรายการดรอปดาวน์

3. คลิกลิงก์เพื่อข้ามเคอร์เซอร์ไปที่เซลล์แรกที่มีปัญหา

รูปภาพด้านล่างแสดงว่าเครื่องมือพบข้อผิดพลาดแบบวงกลมในเซลล์ G1 เซลล์ที่แสดงเป็นไฮเปอร์ลิงก์

เมื่อคุณคลิกลิงก์ เคอร์เซอร์จะเลื่อนไปที่เซลล์ สิ่งนี้มีประโยชน์มากกับข้อมูลจำนวนมาก

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

ใช้ขั้นตอนในส่วนหลังของบทความนี้เพื่อแก้ไขปัญหาและใช้เครื่องมือเพื่อไปยังปัญหาถัดไป

หากไม่มีปัญหาแบบวงกลม รายการเมนู “การอ้างอิงแบบวงกลม” ในเมนูแบบเลื่อนลงจะเป็นสีเทา คุณไม่มีงานทำต่อไป

วิธีค้นหาการอ้างอิงแบบวงกลมด้วยแถบสถานะของ Excel

อีกวิธีในการระบุการอ้างอิงแบบวงกลมคือการดูที่แถบสถานะที่ด้านล่างของแผ่นงาน Excel

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

ตัวอย่างเช่น หากมีการอ้างอิงแบบวงกลมในเซลล์ C1 และไม่มีปัญหาอื่นๆ กับสเปรดชีต แถบสถานะจะอ่านว่า: "การอ้างอิงแบบวงกลม: C1"

ภาพนี้แสดงตำแหน่งที่จะดูหน้าจอ

วิธีค้นหาการอ้างอิงแบบวงกลมที่ซ่อนอยู่ใน Excel

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

สิ่งเหล่านี้เกิดขึ้นเมื่อเซลล์อ้างอิงเซลล์อื่นทางอ้อมผ่านชุดเซลล์ระดับกลาง และในที่สุดก็อ้างอิงกลับไปที่เซลล์เดิม

Excel มีเครื่องมือสองอย่างที่ช่วยคุณติดตามความสัมพันธ์และตรวจสอบปัญหาเหล่านี้:

1. เครื่องมือติดตามผู้อยู่ในอุปการะ

2. ติดตามเครื่องมือแบบอย่าง

วิธีการใช้เครื่องมือติดตามขึ้นอยู่กับ

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

เครื่องมือนี้มีอยู่ในส่วนการตรวจสอบสูตรบนแท็บสูตร

ในการใช้เครื่องมือ ให้ทำตามขั้นตอนเหล่านี้:

1. คลิกที่เซลล์ที่คุณต้องการติดตามผู้อยู่ในอุปการะ

2. ไปที่แท็บ 'สูตร' ในริบบิ้น Excel

3. คลิกที่ปุ่ม 'ติดตามผู้อยู่ในอุปการะ' ในกลุ่ม 'การตรวจสอบสูตร'

4. ตรวจสอบลูกศรติดตามหรือลูกศรที่แสดงการขึ้นต่อกัน

Excel แสดงลูกศรจากค่าเซลล์ที่ใช้งานอยู่ไปยังเซลล์ใดๆ ที่ขึ้นอยู่กับค่านั้น ลูกศรสีน้ำเงินในภาพด้านล่างแสดงว่าเซลล์ D6 มีการพึ่งพาเซลล์ B2

ติดตามเครื่องมือแบบอย่าง

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

เครื่องมือนี้มีอยู่ในส่วนการตรวจสอบสูตรบนแท็บสูตร

ในการใช้เครื่องมือ ให้ทำตามขั้นตอนเหล่านี้:

1. คลิกที่เซลล์ที่มีสูตรที่คุณต้องการตรวจสอบ

2. ไปที่แท็บ สูตร ใน Ribbon ของ Excel

3. คลิกที่ปุ่ม 'Trace Precedents' ในกลุ่ม 'Formula Auditing'

4. ตรวจสอบลูกศรติดตามที่ Excel แสดง

ลูกศรสีน้ำเงินในภาพด้านล่างแสดงให้คุณเห็นว่าเซลล์ D6 ขึ้นอยู่กับช่วงของเซลล์ A2:B5

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

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

มีสองวิธีหลักในการแก้ไขการอ้างอิงแบบวงกลม:

• ทำลายการอ้างอิงแบบวงกลม

• โดยใช้สูตรทางเลือก

ขออธิบายทั้งสองวิธี

1. ทำลายการอ้างอิงแบบวงกลม

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

โดยทำตามขั้นตอนที่อธิบายไว้ก่อนหน้านี้เพื่อค้นหาที่อยู่ของเซลล์

จากนั้นคุณควรตรวจสอบสูตรในแถบสูตร ค้นหาการอ้างอิงถึงเซลล์เอง

ในตัวอย่างแรกของการอ้างอิงแบบวงกลม สูตรการบวกในเซลล์ C1 รวมการอ้างอิงถึงตัวเซลล์ด้วย:

=A1 + B1 + C1

สิ่งนี้สามารถแก้ไขได้โดยการลบการเพิ่มขั้นสุดท้าย เมื่อแก้ไขปัญหานี้แล้ว คุณไปหาการอ้างอิงแบบวงกลมอื่นๆ และจัดการกับปัญหาเหล่านั้น

2. ใช้สูตรทางเลือก

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

ในตัวอย่างก่อนหน้านี้ สูตรใช้ตัวดำเนินการการบวกเพื่อคำนวณผลรวมของช่วงเซลล์ วิธีอื่นคือการใช้ฟังก์ชัน SUM Excel

ในการแก้ไขตัวอย่าง ให้แทนที่สูตรด้วย:

=SUM(A1:B1)

ในทำนองเดียวกัน คุณสามารถหาค่าเฉลี่ยช่วงโดยใช้สูตร เช่น =(A1+B1)/2 อย่างไรก็ตาม ฟังก์ชัน AVERAGE มีโอกาสน้อยที่จะทำให้เกิดข้อผิดพลาด

=เฉลี่ย(A1:B1)

5 เคล็ดลับในการป้องกันการอ้างอิงแบบวงกลม

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

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

ต่อไปนี้คือเคล็ดลับที่ดีที่สุด 5 ข้อของเราที่จะช่วยคุณป้องกันการอ้างอิงแบบวงกลม

1. ระมัดระวังเมื่อเลือกช่วง

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

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

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

2. วางแผนสูตรของคุณอย่างรอบคอบ

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

คุณสามารถวางแผนการคำนวณล่วงหน้าโดยใช้เครื่องมือสร้างไดอะแกรมหรือไวท์บอร์ด

วิธีนี้สามารถช่วยคุณระบุการวนซ้ำที่เป็นไปได้ที่อาจนำไปสู่การอ้างอิงแบบวงกลม

3. ใช้สูตรทางเลือก

ฟังก์ชันบางอย่าง เช่นINDIRECTหรือOFFSETสามารถสร้างการอ้างอิงแบบวงกลมโดยไม่ได้ตั้งใจเมื่อใช้อย่างไม่เหมาะสม

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

4. แบ่งสูตรที่ซับซ้อนออกเป็นส่วนย่อยๆ

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

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

5. แก้ไขปัญหาตามที่คุณไป

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

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

สิ่งนี้ทำให้ง่ายต่อการแก้ไขและป้องกันผลกระทบในสมุดงานของคุณ

วิธีเปิดใช้งานการคำนวณซ้ำ & ทำไมต้องกังวล?

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

ตัวอย่างเช่น การคำนวณทางวิศวกรรมหรือวิทยาศาสตร์ บางอย่าง จำเป็นต้องใช้ผลการคำนวณครั้งก่อน สิ่งนี้เรียกว่าวิธีแก้ปัญหาแบบวนซ้ำ

แต่ละลูปปรับแต่งผลลัพธ์ของการคำนวณจนกว่าจะถึงเกณฑ์หรือเป้าหมายเฉพาะ

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

ทำตามขั้นตอนเหล่านี้เพื่อให้การอ้างอิงแบบวงกลมทำงานในไฟล์ Excel ของคุณ:

1. คลิกแท็บไฟล์

2. เลือกตัวเลือก

3. เลือกหมวดหมู่สูตร

4. ภายใต้ส่วน 'ตัวเลือกการคำนวณ' ให้เลือกช่องสำหรับ 'เปิดใช้งานการคำนวณซ้ำ'

5. เปลี่ยนตัวเลขในกล่องการวนซ้ำสูงสุดหรือปล่อยให้เป็นค่าเริ่มต้นที่ 100

6. ใช้กล่องการเปลี่ยนแปลงสูงสุดเพื่อลดการวนซ้ำตามความต้องการของคุณ