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

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

ตัวอย่างเช่นคุณมีสองค่าในเซลล์ A1 และ A2 เมื่อใส่สูตร=A1 + A2 ลงใน A2 จะสร้างการอ้างอิงแบบวงกลม สูตรใน A2 จะคำนวณตัวเองซ้ำ ๆ ซ้ำ ๆ เพราะทุกครั้งที่คำนวณค่า A2 จะเปลี่ยนไป

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

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

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

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

ตัวอย่างเช่นคุณมีคอลัมน์ของตัวเลขในเซลล์ A1: A4 และคุณกำลังใช้ฟังก์ชัน SUM (=SUM (A1: A5)) ในเซลล์ A5 เซลล์ A5 หมายถึงเซลล์ของตัวเองอย่างตรงไปตรงมาซึ่งเป็นไปไม่ได้ ดังนั้นคุณจะได้รับคำเตือนการอ้างอิงแบบวงกลมดังต่อไปนี้:

เมื่อคุณได้รับข้อความเตือนด้านบนคุณสามารถคลิก”Help”เพื่อดูข้อมูลเพิ่มเติมหรือปิดหน้าต่างข้อความโดยคลิกปุ่ม”OK”หรือ”X”และได้รับ”0″ตามผลลัพธ์

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

การอ้างอิงแบบวงกลมโดยตรงและโดยอ้อม

การอ้างอิงแบบวงกลมสามารถแบ่งได้เป็นสองประเภท: การอ้างอิงแบบวงกลมโดยตรงและการอ้างอิงแบบวงกลมโดยอ้อม

การอ้างอิงโดยตรง

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

ตัวอย่างเช่นสูตรในเซลล์ A2 ด้านล่างหมายถึงเซลล์ของตัวเองโดยตรง (A2)

เมื่อข้อความเตือนปรากฏขึ้นผู้ใช้สามารถคลิกที่”ตกลง”แต่จะให้ผลลัพธ์เป็น”0″เท่านั้น

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

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

ลองดูตัวอย่างง่ายๆ

ตอนนี้ค่าเริ่มต้นจาก A1 ซึ่งมีค่า 20

ถัดไปเซลล์ C3 หมายถึงเซลล์ A1

จากนั้นเซลล์ A5 หมายถึงเซลล์ C3

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

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

วิธีเปิด/ปิดการใช้งานการอ้างอิงแบบวงกลมใน Excel

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

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

ใน Excel 2010, Excel 2013, Excel 2016, Excel 2019 และ Microsoft 365 ไปที่แท็บ”ไฟล์”ที่มุมบนซ้ายของ Excel จากนั้นคลิก”ตัวเลือก”ในบานหน้าต่างด้านซ้าย

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

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

เพื่อให้บรรลุสิ่งนี้ใน Excel เวอร์ชันที่สมบูรณ์ให้ทำตามขั้นตอนเหล่านี้:

• ใน Excel 2007 คลิกที่ปุ่ม Office> ตัวเลือก Excel> สูตร> พื้นที่การทำซ้ำ
• ใน Excel 2003 และเวอร์ชันก่อนหน้าคุณต้องไปที่เมนู> เครื่องมือ> ตัวเลือก> การคำนวณ แท็บ

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

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

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

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

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

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

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

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

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

การใช้แถบสถานะ

อีกวิธีง่ายๆในการค้นหาข้อมูลอ้างอิงแบบวงกลมคือการดูที่แถบสถานะ บนแถบสถานะของ Excel จะแสดงที่อยู่เซลล์ล่าสุดพร้อมการอ้างอิงแบบวงกลมเช่น”การอ้างอิงแบบวงกลม: B6″(ดูภาพหน้าจอด้านล่าง)

มีบางสิ่งที่คุณควรทราบเมื่อทำงานกับข้อมูลอ้างอิงแบบวงกลม:

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

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

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

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

บางครั้งในสูตรง่ายๆสิ่งที่คุณต้องทำคือปรับพารามิเตอร์ของสูตรใหม่เพื่อไม่ให้อ้างอิงกลับมาที่ตัวมันเอง ตัวอย่างเช่นเปลี่ยนสูตรใน B6 เป็น=SUM (B1: B5) * A5 (เปลี่ยน B6 เป็น B5)

จะส่งคืนผลลัพธ์ของการคำนวณเป็น”756″

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

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

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

ร่องรอยก่อนหน้า

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

ในตัวอย่างด้านล่างลูกศรสีน้ำเงินแสดงเซลล์ที่มีผลต่อค่า B6 คือ B1: B6 และ A5 ดังที่คุณเห็นด้านล่างเซลล์ B6 จะรวมอยู่ด้วยซึ่งทำให้เป็นการอ้างอิงแบบวงกลมและทำให้สูตรกลับมาเป็น 0

สิ่งนี้สามารถแก้ไขได้อย่างง่ายดายโดยการแทนที่ B6 ด้วย B5 ในอาร์กิวเมนต์ของ SUM:=SUM (B1: B5)

ติดตามผู้อยู่ในอุปการะ

คุณลักษณะการติดตามการติดตามจะติดตามเซลล์ที่ขึ้นอยู่กับเซลล์ที่ใช้งานอยู่ คุณลักษณะนี้วาดเส้นเพื่อระบุว่าเซลล์ใดได้รับผลกระทบจากเซลล์ที่เลือก กล่าวอีกนัยหนึ่งก็คือแสดงว่าเซลล์ใดมีสูตรที่อ้างอิงเซลล์ที่ใช้งานอยู่ คีย์ลัดสำหรับใช้ผู้ติดตามคือ Alt + T U D

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

จงใจใช้การอ้างอิงแบบวงกลมใน Excel

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

ให้เราอธิบายโดยใช้ตัวอย่าง

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

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

ป้อนสูตรเพื่อคำนวณต้นทุนทั้งหมดในเซลล์ B6:

 =SUM (B1: B4)  

เนื่องจากค่าใช้จ่ายทั้งหมดรวมค่าธรรมเนียมตัวแทนเราจึงรวม B4 ไว้ในสูตรข้างต้น

ในการคำนวณค่าธรรมเนียมตัวแทน 2% ให้ใส่สูตรนี้ใน B4:

 =B6 * 2%  

ตอนนี้สูตรในเซลล์ B4 ขึ้นอยู่กับค่าของ B6 ในการคำนวณ 2% ของค่าธรรมเนียมทั้งหมดและสูตรใน B6 ขึ้นอยู่กับ B4 ในการคำนวณต้นทุนทั้งหมด (รวมค่าธรรมเนียมตัวแทน) ดังนั้นการอ้างอิงแบบวงกลม

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

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

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