เปิดใช้งาน Circular Reference ใน Excel: คู่มือปฏิบัติ พร้อม 2 กรณีใช้งานสำคัญ

“Allow Circular Reference in Excel” (อนุญาตการอ้างอิงแบบวนรอบ) เป็นวิธีง่ายๆ ในการอ้างอิงเซลล์ตัวเองใน Excel ตามนิยามของ Microsoft Circular Reference (การอ้างอิงแบบวนรอบ) เกิดขึ้นเมื่อสูตร Excel อ้างอิงกลับไปที่เซลล์ของตัวมัน เอง โดยตรงหรือโดยอ้อม ตัวอย่างเช่น หากเราต้องการบวกค่าสองรายการ (เช่น B2 และ C2) แต่แสดงผลในรายการหนึ่ง (เช่น B2) เราก็สร้าง Circular Reference ขึ้น หลังจากกด ENTER ครั้งแรก Excel จะแสดงคำเตือนว่ามี Circular Reference อย่างน้อยหนึ่งรายการในสูตรเท่านั้น

Circular reference คำนวณผลลัพธ์สูตรแบบวนซ้ำ (iterations) ดังนั้น อาจสร้างลูปได้ หากตั้งค่าจำนวนรอบการวนซ้ำสูงสุด (maximum iteration) สูงเกินไป

ในบทความนี้ เราจะพูดถึง Circular Reference ปัญหาที่เกี่ยวข้อง และการใช้งาน

ดาวน์โหลดสมุดงาน Excel

วิธีตรวจสอบว่า Circular Reference เปิดใช้งานหรือไม่

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

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

หลังจากใส่การอ้างอิงเซลล์ในสูตร คุณอาจเห็น Excel คืนค่าเป็นศูนย์ (0) โดยไม่มีเหตุผลชัดเจน ตรวจสอบข้อความทางขวาของสถานะ Ready ที่ด้านล่างของเวิร์กชีตใดๆ หากแสดง Circular Reference: cell แสดงว่าตัวเลือก Circular Reference ปิดใช้งานสำหรับเวิร์กชีตนั้น

เราสามารถไปที่แท็บ Formulas (สูตร) > เลือก Error Checking (ตรวจสอบข้อผิดพลาด) ในส่วน Formula Auditing (ตรวจสอบสูตร) > เลือก Circular References (การอ้างอิงวนรอบ) จากตัวเลือก Error Checking จะแสดงตำแหน่งที่ ข้อผิดพลาดของ Circular Reference เกิดขึ้น

อ่านเพิ่มเติม: วิธีค้นหา Circular Reference ใน Excel (2 เทคนิคง่ายๆ)

อนุญาต Circular Reference ใน Excel ผ่านริบบิ้น File

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

เพื่ออนุญาต Circular Reference ให้ทำตามลำดับขั้นตอนด้านล่าง

➤ ไปที่ริบบิ้น File ของเวิร์กชีตใดๆ

➤ เลือก Options (ตัวเลือก) จากเมนู File (หน้าต่าง Excel Options จะปรากฏ) > เลือก Formulas (ทางด้านขวาของหน้าต่าง) > ติ๊กตัวเลือก Enable iterative calculation (เปิดใช้งานการคำนวณแบบวนซ้ำ) (ป้อนจำนวน Maximum iterations (รอบวนซ้ำสูงสุด) และ Maximum change (การเปลี่ยนแปลงสูงสุด) ที่ต้องการ)

คลิก OK

การดำเนินการตามลำดับนี้ คุณสามารถอนุญาตให้สูตรคืนค่าผลลัพธ์พร้อม Circular Reference ที่มีอยู่ได้

ปัญหาเกี่ยวกับ Maximum Iterations (จำนวนรอบวนซ้ำสูงสุด)

เมื่อเกิด Circular Reference ใน Excel จะแสดงคำเตือนครั้งแรก ตามภาพด้านล่าง

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

เพื่อแก้ไขปัญหาการคำนวณนี้ เราต้องติ๊กตัวเลือก Enable iterative calculation ไปที่ตัวเลือกแท็บ File ตามภาพด้านล่าง

หลังจากคลิกที่ริบบิ้น File เลือก Options (หน้าต่าง Excel Options จะปรากฏ) > เลือก Formulas (ในส่วนซ้ายของหน้าต่าง Excel Options) > ติ๊ก Enable iterative calculation แล้วคลิก OK

ตอนนี้ เราสามารถใช้ Circular Reference ในสูตรได้ แต่หากคุณป้อน 1 (ตามที่เราทำ) ในกล่อง Maximum iterations สูตร (เช่น =B2+C2) จะคืนค่า 20 (ซึ่งถูกต้อง เพราะ B2 ว่างเปล่า และ C2 มีค่า 20) ตามภาพด้านล่าง

อย่างไรก็ตาม หากเราป้อน 5 ในกล่องตัวเลือก Maximum iterations ตามภาพด้านล่าง

สูตรจะคืนค่า 100 เพราะมันวนคำนวณซ้ำ 5 ครั้ง นี่คือการคำนวณผิดที่รบกวนมากที่สุดใน Excel

การใช้งาน Circular Reference ใน Excel

Circular Reference มีประโยชน์มากในบางด้าน บางเหตุการณ์ต้องการ Circular Reference เพื่อแสดงค่าให้มีประสิทธิภาพมากขึ้น ในบทความนี้ เราจะพูดถึง 2 กรณีที่ใช้ Circular Reference มากที่สุด

ตัวอย่างที่ 1: อนุญาต Circular Reference ใน Excel เพื่อแทรก Timestamp แบบคงที่ (Static Timestamp)

สมมติว่าเรามี สินค้า พร้อม วันที่สั่งซื้อ และ สถานะการจัดส่ง และเราต้องการแทรก Timestamp (ประทับเวลา) ในเซลล์เมื่อสินค้าถูกจัดส่งแล้ว เราแสดง สถานะการจัดส่ง ด้วย Yes หรือ No

ดังนั้น หาก สถานะการจัดส่ง เป็น Yes แล้ว Timestamp จะแสดง เวลา และ วันที่ ณ ขณะนั้น

➤ วางสูตรต่อไปนี้ในเซลล์ว่างใดๆ (เช่น E5) ที่ต้องการแสดง Timestamp

สูตรนี้ทำการ logical_test (ตรวจสอบเงื่อนไขตรรกะ) เพื่อจับคู่ Yes กับรายการในคอลัมน์ D จากนั้นแสดงเวลาปัจจุบันในเซลล์ที่คืนค่า TRUE หรือเว้นเซลล์ว่างไว้ในกรณีอื่น

➤ กด ENTER แล้วลาก Fill Handle (จุดจับเติม) เพื่อแทรก Timestamp ให้กับสินค้าที่จัดส่งแล้วทั้งหมด

เนื่องจาก Circular Reference ก่อให้เกิดปัญหาการอ้างอิงเซลล์ตัวเอง บางครั้งสูตรอาจดูเหมือนจะไม่ทำงานได้อย่างถูกต้อง ในกรณีนั้น ให้เขียน Delivery Status (สถานะการจัดส่ง) หรือเงื่อนไขใหม่ แล้วใช้สูตรใดๆ อีกครั้ง และคุณอาจต้องเปลี่ยนรูปแบบเซลล์เพื่อให้ได้รูปแบบวันที่-เวลา

ตัวอย่างที่ 2: อนุญาต Circular Reference ใน Excel สำหรับการคำนวณแบบวนซ้ำ (Iterative Calculation)

บางครั้ง เราต้องทำการคำนวณแบบวนซ้ำ (iterative calculations) ในการคำนวณแบบวนซ้ำ เราจะได้ค่าวนซ้ำโดยตรงโดยไม่ต้องคำนวณทั้งกระบวนการ

สมมติในสถานการณ์หนึ่ง เราลงทุนเงินสดเริ่มต้น (เช่น $1500) และต้องการผ่อนชำระรายเดือนจากสินทรัพย์ เราสามารถคำนวณจำนวนผ่อนชำระเดือนแรกได้โดยใช้สูตรด้านล่าง

จากนั้นอ้างอิงจำนวนผ่อนชำระก่อนหน้า เพื่อคำนวณจำนวนผ่อนชำระเดือนถัดไป ตามภาพด้านล่าง

➤ อย่างไรก็ตาม หากเราใช้ Circular Reference เราสามารถได้ n^th (ผ่อนชำระครั้งที่ n) จำนวนผ่อนชำระได้โดยการป้อนหมายเลขผ่อนชำระเป็นจำนวน Maximum iterations สำหรับผลลัพธ์ที่ถูกต้อง เราป้อน 5 เป็น Maximum iterations และ 1^th ของ 1000 การเปลี่ยนแปลงที่อนุญาต ตามภาพด้านล่าง

เราป้อนหมายเลข maximum iterations 5 เพราะเราต้องคำนวณผ่อนชำระครั้งที่ 6 และอนุญาตให้จำนวนที่เปลี่ยนแปลงได้แตกต่างกัน 1^th ของ 1000 ส่วน