การกำหนดตำแหน่งอ้างอิงแบบวงกลม (Circular Reference)

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

คำเตือนยาวที่ปรากฏบนกลางจอว่า There are one or more circular references where a formula refers to its own cell either directly or indirectly. ... Try removing or changing these references, or moving the formulas to different cells. เป็นคำเตือนที่อาจจะเกิดขึ้นตอนที่เปิดแฟ้มหรือตอนที่สร้างสูตรลงไปในเซลล์ หลังจากที่กดปุ่ม OK แล้วจะมีคำเตือนที่มุมซ้ายล่างของจอปรากฏขึ้นแทนว่า Circular References: ตามด้วยตำแหน่งเซลล์

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

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

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

ตัวอย่างการสร้างสูตรที่มีตำแหน่งอ้างอิงแบบวงกลมที่ง่ายที่สุด เช่น ในเซลล์ A1 สร้างสูตร =A1 ลงไป หรือในเซลล์ B3 สร้างสูตร = B1+B2+B3 ลงไป หรือสังเกตง่ายๆว่าสูตรในเซลล์ใดมีตำแหน่งอ้างอิงที่ระบุถึงเซลล์นั้นเองอยู่ด้วย ซึ่งนอกจากนี้หากมีสูตรที่ link ต่อๆกันไปหลายๆเซลล์แล้วกลับส่งค่ากลับมาในเซลล์ใดเซลล์หนึ่งในวงจรของเซลล์ที่ link กัน เช่น เซลล์ A1 มีสูตร =A3 และเซลล์ A2 มีสูตร =A1 แล้วเซลล์ A3 มีสูตร =A2 ย่อมทำให้เกิด ตำแหน่งอ้างอิงแบบวงกลมได้เช่นกัน ซึ่งเรียกอีกแบบหนึ่งว่าเกิดการคำนวณแบบวงกลม (Circular Calculation)

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

• ในเงื่อนไขการคำนวณต้นทุนสินค้า ซึ่งนำค่าใช้จ่ายทั้งทางตรงและทางอ้อมมา allocate เป็นต้นทุนของสินค้า จากนั้นหากสินค้านั้นกลับไปใช้เป็นวัตถุดิบให้กับสินค้าอื่น แล้วสุดท้ายก็นำสินค้าอื่นนั้นมาใช้เป็นองค์ประกอบร่วมกับสินค้าเดิมอีก
• หากบริษัทรับจ่ายภาษีเงินได้บุคคลธรรมดาแทนพนักงาน ซึ่งตามกฎเกณฑ์ทางภาษีอากรกำหนดให้นำภาษีที่จ่ายแทนนั้นนำมาบวกเป็นเงินได้เพิ่ม แล้วเงินได้รวมที่เพิ่มขึ้นนี้ก็ต้องนำกลับไปย้อนคิดภาษีเงินได้อีก
• ค่าใช้จ่ายบางรายการ อาจมีข้อกำหนดทางภาษีว่าห้ามนำมาหักภาษีซ้ำอีก แต่ค่าใช้จ่ายนั้นกลับมีฐานการคำนวณที่เกิดขึ้นจากกำไรสุทธิ
• ในกรณีของการกู้เงิน ถ้าบริษัทนำดอกเบี้ยจ่ายเงินกู้ไปเพิ่มยอดเงินที่ต้องกู้เพิ่ม ย่อมทำให้ต้องจ่ายดอกเบี้ยเพิ่มและทำให้ต้องกู้เพิ่ม
• การของบประมาณค่าใช้จ่ายที่กำหนดว่าจะให้ตามอัตราร้อยละของกำไร ซึ่งถ้ากำไรเพิ่มก็จะทำให้ได้งบประมาณค่าใช้จ่ายเพิ่ม แต่ค่าใช้จ่ายที่เพิ่มขึ้น ย่อมส่งผลให้กำไรลด และทำให้ได้งบประมาณค่าใช้จ่ายลดลงตาม ซึ่งกลับทำให้กำไรเพิ่มขึ้น
• งานคำนวณอื่นๆ เช่น การควบคุมให้ Excel เพิ่มค่าตัวเลขครั้งละเท่าๆกัน โดยสร้างสูตร =Number+A1 ลงไปในเซลล์ A1 หรือการบันทึกประวัติตัวเลขสูงสุด โดยสร้างสูตร =Max(A1,NumberRange) ลงไปในเซลล์ A1

หมายเหตุ แทนที่จะใช้สูตรที่มีตำแหน่งอ้างอิงแบบวงกลม ซึ่งทำให้แฟ้มนั้นมีระบบการคำนวณแบบพิเศษที่ต้องสั่งให้ Excel ยอมรับก่อนจึงจะคำนวณได้คำตอบถูกต้อง เราสามารถใช้คำสั่งอื่นที่ทำหน้าที่สั่งให้ Excel คำนวณซ้ำได้ เช่น Goal Seek หรือใช้ Solver Add-in หรือใช้คำสั่งจาก Visual Basic ควบคุมการคำนวณแทน

ขั้นตอนการสร้างสูตรคำนวณแบบ Circular Reference

1. ให้เลือกสร้างสูตรอื่นๆที่คำนวณตามปกติทุกสูตร (ที่ไม่ต้องใช้การคำนวณแบบ Circular Reference) ให้เสร็จก่อน แล้วจึงสร้างสูตรที่ต้องคำนวณแบบ Circular Reference เป็นสูตรสุดท้าย เนื่องจากหากต้องการให้ Excel คำนวณแบบ Circular เมื่อใด คุณจะต้องสั่งให้ Excel เลิกเปิดคำเตือน ซึ่งหากมีการสร้างสูตรผิดพลาดเผลอไปสร้างสูตร Circular Reference ขึ้นโดยไม่เจตนา ก็ย่อมไม่มีการเตือนให้ทราบ
2. เมื่อสร้างสูตรคำนวณแบบ Circular Reference แล้วกดปุ่ม Cancel เพื่อปิดคำเตือนบนหน้าจอตามรูปข้างต้นแล้ว จะพบคำว่า Circular References: ต่อด้วยตำแหน่งเซลล์แสดงขึ้นตรงขอบด้านล่างซ้ายของจอ พร้อมทั้งในพื้นที่ตารางก็จะมีเส้นลูกศรสีน้ำเงินชี้ตำแหน่งเซลล์ที่ทำให้เกิดการอ้างอิงแบบวงกลม ซึ่งหากต้องการให้ Excel แสดงชื่อตำแหน่งเซลล์ที่เกี่ยวข้องกับการอ้างอิงแบบวงกลมด้วย ให้สั่ง Formulas > Error Checking > Circular References แล้วจะพบตำแหน่งเซลล์ที่เกี่ยวข้องปรากฏต่อท้ายคำสั่งนี้
   
   
   
   
3. เมื่อต้องการให้ Excel ยอมรับการคำนวณแบบ Circular Reference ให้สั่ง File > Excel Options > Formulas > กาช่อง Enable iterative calculation (Excel 2003 สั่ง Tools > Options > Calculation > กาช่อง Iteration)
   
   
   
   
4. ช่อง Maximum Iterations และช่อง Maximum Change นั้นไม่จำเป็นต้องเข้าไปแก้ไขตัวเลขใดๆ ซึ่ง 2 ช่องนี้ทำหน้าที่สั่งให้ Excel คำนวณสูงสุดถึง 100 ครั้ง หรืออาจไม่ถึง 100 ครั้งก็ได้โดยให้คำนวณจนกว่าจะพบว่า คำตอบที่คำนวณได้ในแต่ละรอบให้ค่าแตกต่างกันน้อยกว่า .001 ก็จะหยุดคำนวณ ซึ่งการคำนวณเป็นรอบดังกล่าวนี้จะเกิดขึ้นทันทีที่มีการสร้างสูตรนั้นเสร็จแล้วกดปุ่ม Enter รับสูตรลงไปหรือเมื่อมีการกดปุ่ม F9 เพื่อสั่งให้ Excel คำนวณ
   • ให้เพิ่มตัวเลขจำนวนรอบสูงสุดในช่อง Maximum Iterations เมื่อพบว่าทุกครั้งที่กดปุ่ม F9 เพื่อสั่งคำนวณ จะได้ตัวเลขคำตอบต่างจากเดิมไปเสมอ ซึ่งหากเพิ่มตัวเลขในช่องนี้แล้วกดปุ่ม F9 พบว่าตัวเลขผลจากการคำนวณไม่เปลี่ยนแปลงต่อไปอีก ย่อมแสดงว่าได้คำตอบสุดท้ายแล้ว
   • ให้ปรับตัวเลขค่าความละเอียดในช่อง Maximum Change ต่อเมื่อต้องการให้ Excel คำนวณละเอียดขึ้น ซึ่งย่อมทำให้ต้องใช้จำนวนครั้งในช่อง Maximum Iterations เพิ่มขึ้นตามไปด้วย
5. หลังจากสั่งคำสั่งตามข้อ 3 แล้ว จะไม่พบข้อความหรือตำแหน่งเซลล์ที่แสดงบนหน้าจอตามตำแหน่งต่างๆที่เคยแสดงไว้ตามข้อ 2 อีกต่อไป แต่จะพบคำว่า Calculate แสดงค้างอยู่ตรงขอบจอด้านล่างซ้ายของโปรแกรม Excel แทน และขอให้สังเกตว่าทุกครั้งที่กดปุ่ม F9 จะทำให้คำว่า Calculate นี้หายไปชั่วขณะหนึ่งแล้วกลับมาแสดงใหม่ (โดยไม่เกี่ยวข้องกับการสั่งให้คำนวณแบบ Manual ที่จะมีคำว่า Calculate แสดงขึ้นมาเช่นกัน แต่เมื่อกดปุ่ม F9 เพื่อสั่งคำนวณแบบ Manual จะทำให้คำว่า Calculate หายไปโดยไม่กลับมาใหม่อีก)
   
   
   
   
6. ในกรณีที่ต้องการตรวจสอบที่ไปที่มาของสูตรที่มีการใช้ตำแหน่งอ้างอิงแบบวงกลมว่ามีเซลล์ใดที่เกี่ยวข้องบ้าง ให้ย้อนกลับไปตัดกาช่อง Enable iterative calculation ทิ้งไป จากนั้น Excel จะแสดงตำแหน่งเซลล์ที่เกี่ยวข้องไว้ที่คำสั่ง Formulas > Error Checking > Circular References ซึ่งเซลล์เหล่านั้นอาจจะเป็นเซลล์สูตรที่สร้างไว้อย่างตั้งใจหรือเผลอแก้ไขสูตรให้ต่างจากเดิมไปจนทำให้เกิด Circular reference ขึ้นก็ได้ จากนั้นให้ทยอยลบสูตรในเซลล์ดังกล่าวทิ้งไปทีละเซลล์ จนกว่าจะพบว่าเมื่อลบสูตรในเซลล์ใดเสร็จแล้ว ทำให้ไม่เกิดคำเตือน Circular ต่อไปอีก หลังจากนั้นจึงไล่สร้างสูตรใหม่ขึ้นมาแทน ซึ่งหลายๆครั้งจะพบว่าต้องไล่ลบสูตรในเซลล์ทิ้งแล้วต้องสร้างสูตรใหม่นับร้อยนับพันเซลล์ทีเดียว โดยเฉพาะตารางคำนวณที่ต้องคำนวณรายการรายวันหรือรายเดือนต่อกันไปเป็นตารางขนาดใหญ่ ดังนั้นผู้ใช้ Excel ทุกคนต้องรู้ตัวทันทีเมื่อตัวเองเผลอสร้างสูตรที่มีตำแหน่งอ้างอิงแบบวงกลมขึ้นมาเป็นครั้งแรก หากปล่อยไว้ก็จะต้องเสียแรงเสียเวลาไล่ย้อนมาแก้ไขกันใหม่ทีละเซลล์
7. เซลล์ที่ทำให้เกิด Circular นี้ ควรใส่สีให้ต่างจากเซลล์อื่น หรือทำเครื่องหมายพิเศษ หรือเขียนอธิบายกำกับไว้ด้วยเพื่อเตือนผู้ใช้งานให้แก้ไขสูตรอย่างระมัดระวัง
8. ควรตั้งชื่อแฟ้มที่มีสูตรที่มีตำแหน่งอ้างอิงแบบวงกลมให้มีชื่อที่สะดุดตา จะได้ไม่เผลอเปิดแฟ้มที่ต้องคำนวณแบบ Iterations นี้ขึ้นมาใช้งานพร้อมกับแฟ้มอื่น เพราะแฟ้มอื่นๆจะรับคำสั่ง Iterations นี้ตามไปด้วยโดยไม่จำเป็น
9. หากปิดโปรแกรม Excel แล้วเปิด Excel ขึ้นมาใหม่ จะพบว่า Excel ตัดกาช่อง Enable iterative calculation ทิ้งไปให้เสมอ