หนึ่งในคำเตือนข้อผิดพลาดที่พบบ่อยที่สุดที่ผู้ใช้พบใน Excel คือ 'การอ้างอิงแบบวงกลม' ผู้ใช้หลายพันรายประสบปัญหาเดียวกัน และเกิดขึ้นเมื่อสูตรอ้างอิงกลับไปยังเซลล์ของตนเองโดยตรงหรือโดยอ้อม ทำให้เกิดการคำนวณวนซ้ำไม่รู้จบ
ตัวอย่างเช่น คุณมีค่าสองค่าในเซลล์ B1 และ B2 เมื่อป้อนสูตร =B1+B2 ลงใน B2 จะสร้างการอ้างอิงแบบวงกลม สูตรใน B2 จะคำนวณตัวเองซ้ำๆ เพราะทุกครั้งที่คำนวณ ค่า B2 จะเปลี่ยนไป
การอ้างอิงแบบวงกลมส่วนใหญ่เป็นข้อผิดพลาดโดยไม่ได้ตั้งใจ Excel จะเตือนคุณเกี่ยวกับสิ่งเหล่านี้ อย่างไรก็ตาม ยังมีการอ้างอิงแบบวงกลมอีกด้วย ซึ่งใช้ในการคำนวณแบบวนซ้ำ การอ้างอิงแบบวงกลมโดยไม่ได้ตั้งใจในเวิร์กชีตของคุณอาจทำให้สูตรคำนวณไม่ถูกต้อง
ดังนั้น ในบทความนี้ เราจะอธิบายทุกสิ่งที่คุณจำเป็นต้องรู้เกี่ยวกับการอ้างอิงแบบวงกลม ตลอดจนวิธีค้นหา แก้ไข นำออก และใช้การอ้างอิงแบบวงกลมใน Excel
วิธีค้นหาและจัดการการอ้างอิงแบบวงกลมใน Excel
เมื่อทำงานกับ Excel บางครั้งเราพบข้อผิดพลาดในการอ้างอิงแบบวงกลมซึ่งเกิดขึ้นเมื่อคุณป้อนสูตรที่มีเซลล์ที่มีสูตรของคุณอยู่ โดยพื้นฐานแล้วจะเกิดขึ้นเมื่อสูตรของคุณพยายามคำนวณเอง
ตัวอย่างเช่น คุณมีคอลัมน์ของตัวเลขในเซลล์ A1:A4 และคุณกำลังใช้ฟังก์ชัน SUM (=SUM(A1:A5)) ในเซลล์ A5 เซลล์ A5 หมายถึงเซลล์ของตัวเองโดยตรง ซึ่งไม่ถูกต้อง ดังนั้น คุณจะได้รับคำเตือนการอ้างอิงแบบวงกลมต่อไปนี้:
เมื่อคุณได้รับข้อความเตือนด้านบนแล้ว คุณสามารถคลิกปุ่ม 'ช่วยเหลือ' เพื่อทราบข้อมูลเพิ่มเติมเกี่ยวกับข้อผิดพลาด หรือปิดหน้าต่างข้อความแสดงข้อผิดพลาดโดยคลิกปุ่ม 'ตกลง' หรือ 'X' และรับ '0' ตามผลลัพธ์
บางครั้งการอ้างอิงแบบวนซ้ำอาจทำให้การคำนวณของคุณหยุดทำงานหรือทำให้เวิร์กชีตของคุณทำงานช้าลง การอ้างอิงแบบเวียนยังนำไปสู่ปัญหาอื่นๆ อีกจำนวนหนึ่ง ซึ่งจะไม่ปรากฏชัดในทันที ดังนั้นจึงควรหลีกเลี่ยงสิ่งเหล่านี้
การอ้างอิงแบบวงกลมทั้งทางตรงและทางอ้อม
การอ้างอิงแบบวงกลมสามารถแบ่งออกเป็นสองประเภท: การอ้างอิงแบบวงกลมโดยตรงและการอ้างอิงแบบวงกลมทางอ้อม
การอ้างอิงโดยตรง
การอ้างอิงแบบวงกลมโดยตรงนั้นค่อนข้างง่าย ข้อความเตือนการอ้างอิงแบบวงกลมโดยตรงจะปรากฏขึ้นเมื่อสูตรอ้างอิงกลับไปยังเซลล์ของตัวเองโดยตรง
ในตัวอย่างด้านล่าง สูตรในเซลล์ A2 อ้างอิงถึงเซลล์ของตัวเอง (A2) โดยตรง
เมื่อข้อความเตือนปรากฏขึ้น คุณสามารถคลิกที่ 'ตกลง' แต่จะส่งผลให้เป็น '0' เท่านั้น
การอ้างอิงแบบวงกลมทางอ้อม
การอ้างอิงแบบวงกลมทางอ้อมใน Excel เกิดขึ้นเมื่อค่าในสูตรอ้างอิงกลับไปยังเซลล์ของตัวเอง แต่ไม่ใช่โดยตรง กล่าวอีกนัยหนึ่งการอ้างอิงแบบวงกลมสามารถเกิดขึ้นได้จากสองเซลล์ที่อ้างอิงถึงกันและกัน
ให้เราอธิบายด้วยตัวอย่างง่ายๆ นี้
ตอนนี้ค่าเริ่มต้นจาก A1 ซึ่งมีค่า 20
ถัดไป เซลล์ C3 หมายถึงเซลล์ A1
จากนั้น เซลล์ A5 จะอ้างอิงถึงเซลล์ C3
ตอนนี้แทนที่ค่า 20 ในเซลล์ A1 ด้วยสูตรดังที่แสดงด้านล่าง ทุกเซลล์อื่นขึ้นอยู่กับเซลล์ A1 เมื่อคุณใช้การอ้างอิงของเซลล์สูตรอื่นๆ ก่อนหน้านี้ใน A1 จะทำให้เกิดคำเตือนการอ้างอิงแบบวงกลม เนื่องจากสูตรใน A1 หมายถึงเซลล์ A5 ซึ่งอ้างอิงถึง C3 และเซลล์ C3 อ้างอิงกลับไปที่ A1 จึงเป็นการอ้างอิงแบบวงกลม
เมื่อคุณคลิก 'ตกลง' จะส่งผลให้ค่า 0 ในเซลล์ A1 และ Excel จะสร้างเส้นที่เชื่อมโยงที่แสดง Trace Precedents และ Trace Dependents ดังที่แสดงด้านล่าง เราสามารถใช้คุณลักษณะนี้เพื่อค้นหาและแก้ไข/ลบการอ้างอิงแบบวงกลมได้อย่างง่ายดาย
วิธีเปิด / ปิดการอ้างอิงแบบวงกลมใน 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 หากคุณเปลี่ยนเป็น '50' Excel จะทำการคำนวณซ้ำ 50 ครั้งก่อนที่จะให้ผลลัพธ์สุดท้ายแก่คุณ โปรดจำไว้ว่ายิ่งจำนวนการทำซ้ำมากเท่าไร ทรัพยากรและเวลาในการคำนวณก็จะยิ่งมากขึ้นเท่านั้น
- การเปลี่ยนแปลงสูงสุด – กำหนดการเปลี่ยนแปลงสูงสุดระหว่างผลการคำนวณ ค่านี้กำหนดความถูกต้องของผลลัพธ์ ยิ่งตัวเลขน้อยเท่าไร ผลลัพธ์ก็จะยิ่งแม่นยำมากขึ้นเท่านั้น และใช้เวลาในการคำนวณเวิร์กชีตนานขึ้น
ถ้าเปิดใช้งานตัวเลือกการคำนวณซ้ำ คุณจะไม่ได้รับคำเตือนใดๆ เมื่อมีการอ้างอิงแบบวงกลมในเวิร์กชีตของคุณ เปิดใช้งานการคำนวณเชิงโต้ตอบเมื่อจำเป็นเท่านั้น
ค้นหาการอ้างอิงแบบวงกลมใน 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