ทำงานกับการอ้างอิง Cyclic ใน Excel

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

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

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

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

สร้างลิงค์วงจร

ตอนนี้เรามาดูกันว่าจะสร้างการแสดงออกของวัฏจักรที่ง่ายที่สุด นี่จะเป็นลิงค์ที่ตั้งอยู่ในเซลล์เดียวกันซึ่งมันหมายถึง

1. เราเน้นองค์ประกอบของแผ่น A1 และเขียนนิพจน์ต่อไปนี้ในนั้น:

   = A1

   ถัดไปคลิกที่ปุ่ม Enter บนแป้นพิมพ์



2. หลังจากนั้นกล่องโต้ตอบการเตือนการแสดงออกของ Cyclic จะปรากฏขึ้น คลิกในปุ่ม "ตกลง"



3. ดังนั้นเราจึงได้รับการดำเนินการวัฏจักรบนแผ่นงานที่เซลล์หมายถึงตัวเอง

ความซับซ้อนเล็กน้อยงานและสร้างการแสดงออกของวงจรจากเซลล์หลายเซลล์

1. ในองค์ประกอบใด ๆ ของแผ่นงานเขียนตัวเลข ปล่อยให้เป็นเซลล์ A1 และหมายเลข 5



2. ในเซลล์อื่น (B1) เขียนนิพจน์:

   = C1



3. ในองค์ประกอบถัดไป (C1) เราจะบันทึกสูตรดังกล่าว:

   = A1



4. หลังจากนั้นเรากลับไปที่เซลล์ A1 ซึ่งตั้งค่าหมายเลข 5. อ้างอิงถึงองค์ประกอบ B1:

   = B1

   คลิกที่ปุ่ม Enter



5. ดังนั้นวงจรจึงปิดและเราได้รับลิงค์วงจรคลาสสิก หลังจากปิดหน้าต่างคำเตือนแล้วเราจะเห็นว่าโปรแกรมที่ทำเครื่องหมายเป็นพันธะวนรอบด้วยลูกศรสีน้ำเงินบนแผ่นงานซึ่งเรียกว่าลูกศรติดตาม

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

1. หากต้องการคลายสูตรในบรรทัดแรกเราเน้นองค์ประกอบแผ่นด้วยจำนวนผลิตภัณฑ์แรก (B2) แทนที่จะเป็นค่าคงที่ (6) ให้ป้อนสูตรที่นั่นซึ่งจะพิจารณาจำนวนสินค้าโดยการหารจำนวนเงินทั้งหมด (D2) สำหรับราคา (C2):

   = D2 / C2

   คลิกที่ปุ่ม Enter



2. เราได้เปิดการเชื่อมโยงวงจรแรกความสัมพันธ์ที่คุ้นเคยกับลูกศรร่องรอย แต่อย่างที่เราเห็นผลลัพธ์นั้นผิดพลาดและเท่ากับศูนย์ตามที่ได้กล่าวไว้แล้วว่า Excel บล็อกการดำเนินการของการดำเนินการวงจร



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



4. อย่างที่คุณเห็นการแสดงออกถูกคัดลอกไปยังองค์ประกอบทั้งหมดของคอลัมน์ แต่มีความสัมพันธ์เดียวเท่านั้นที่ทำเครื่องหมายด้วยลูกศรร่องรอย จดบันทึกไว้สำหรับอนาคต

ค้นหาลิงค์วงจร

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

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



2. เมื่อคลิกที่อยู่ที่เฉพาะเจาะจงเซลล์ที่สอดคล้องกันจะถูกเลือกในแผ่นงาน

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

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

บทเรียน: วิธีการค้นหาลิงค์วงกลมไปยัง Excel

การแก้ไขการอ้างอิงของวงจร

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

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

1. ในกรณีของเราแม้จะมีความจริงที่ว่าโปรแกรมถูกต้องชี้ไปที่หนึ่งในเซลล์รอบ (D6) ข้อผิดพลาดจริงอยู่ในเซลล์อื่น เลือกองค์ประกอบ D6 เพื่อค้นหาเซลล์ที่ดึงค่าขึ้นมา เราดูการแสดงออกในสตริงสูตร อย่างที่เราเห็นค่าในองค์ประกอบของแผ่นนี้จะเกิดขึ้นจากการคูณเนื้อหาของเซลล์ B6 และ C6



2. ไปที่เซลล์ C6 เราเน้นและดูที่สตริงสูตร อย่างที่เราเห็นมันเป็นค่าคงที่ปกติ (1,000) ซึ่งไม่ใช่ผลิตภัณฑ์ที่คำนวณสูตร ดังนั้นจึงปลอดภัยที่จะบอกว่าองค์ประกอบที่ระบุไม่มีข้อผิดพลาดที่ก่อให้เกิดการดำเนินงานของวงจร



3. ไปที่เซลล์ต่อไป (B6) หลังจากการเลือกในแถวของสูตรเราจะเห็นว่ามันมีนิพจน์ที่คำนวณได้ (= D6 / C6) ซึ่งดึงข้อมูลจากองค์ประกอบตารางอื่นโดยเฉพาะจากเซลล์ D6 ดังนั้นเซลล์ D6 หมายถึงข้อมูลขององค์ประกอบ B6 และในทางกลับกันซึ่งทำให้เกิดการวนซ้ำ

   

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

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

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



5. การดำเนินการดังกล่าวจะดำเนินการมากกว่าการแสดงออกทางวงจรอื่น ๆ ทั้งหมดหากพวกเขาอยู่บนแผ่นงาน หลังจากที่ลิงก์วงจรทั้งหมดถูกลบออกจากหนังสือข้อความเกี่ยวกับการปรากฏตัวของปัญหานี้ควรหายไปจากสตริงสถานะ

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

ได้รับอนุญาตจากการดำเนินการของการดำเนินงานของวงจร

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

1. ก่อนอื่นเราย้ายไปที่แท็บ "ไฟล์" ของแอปพลิเคชัน Excel



2. ถัดไปคลิกที่รายการ "พารามิเตอร์" ที่อยู่ทางด้านซ้ายของหน้าต่างที่เปิด



3. หน้าต่างพารามิเตอร์ที่ถูกเนรเทศเริ่มทำงาน เราต้องเข้าไปในแท็บ "สูตร"



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

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

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



5. หลังจากนั้นเราจะไปที่แผ่นงานปัจจุบันโดยอัตโนมัติ อย่างที่เราเห็นในเซลล์ที่มีสูตร Cyclic ตั้งอยู่ตอนนี้ค่าคำนวณอย่างถูกต้อง โปรแกรมไม่ได้ปิดกั้นการคำนวณในนั้น

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

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