אחת מאזהרות השגיאה הנפוצות ביותר שמשתמשים נתקלים ב-Excel היא 'הפניה מעגלית'. לאלפי משתמשים יש את אותה בעיה, והיא מתרחשת כאשר נוסחה מפנה חזרה לתא שלה באופן ישיר או עקיף, מה שגורם ללולאה אינסופית של חישובים.
לדוגמה, יש לך שני ערכים בתאים B1 ו-B2. כאשר הנוסחה =B1+B2 מוזנת ל-B2, היא יוצרת הפניה מעגלית; הנוסחה ב-B2 מחשבת את עצמה שוב ושוב כי בכל פעם שהיא מחשבת, ערך B2 השתנה.
רוב הפניות המעגליות הן טעויות לא מכוונות; אקסל יזהיר אותך לגבי אלה. עם זאת, יש גם הפניות מעגליות מיועדות, המשמשות לביצוע חישובים איטרטיביים. הפניות מעגליות לא מכוונות בגליון העבודה שלך עלולות לגרום לנוסחה שלך לחישוב שגוי.
לכן, במאמר זה נסביר את כל מה שאתה צריך לדעת על הפניות מעגליות, וכן כיצד למצוא, לתקן, להסיר ולהשתמש בהפניות מעגליות באקסל.
כיצד למצוא ולטפל בהפניה מעגלית באקסל
כאשר עובדים עם 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 יוצר קו מקושר המציג את תקדימים מעקב ותלויי מעקב כפי שמוצג להלן. אנו יכולים להשתמש בתכונה זו כדי למצוא ולתקן/להסיר בקלות הפניות מעגליות.
כיצד להפעיל / להשבית הפניות מעגליות ב- Excel
כברירת מחדל, חישובים איטרטיביים כבויים (מושבתים) ב-Excel. חישובים איטרטיביים הם חישובים חוזרים ונשנים עד שהם עומדים בתנאי מסוים. כאשר הוא מושבת, Excel מציג הודעת הפניה מעגלית ומחזיר 0 כתוצאה מכך.
עם זאת, לפעמים יש צורך בהפניות מעגליות כדי לחשב לולאה. כדי להשתמש בהפניה מעגלית, עליך להפעיל חישובים איטרטיביים באקסל שלך וזה יאפשר לך לבצע את החישובים שלך. כעת, הרשו לנו להראות לכם כיצד תוכלו להפעיל או להשבית חישובים איטרטיביים.
ב-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, הוא יראה לך את כתובת התא העדכנית ביותר עם הפניה מעגלית, כגון 'הפניות מעגליות: B6' (ראה צילום מסך למטה).
ישנם דברים מסוימים שאתה צריך לדעת בעת טיפול בהפניה מעגלית:
- שורת המצב לא תציג את כתובת תא ההפניה המעגלי כאשר האפשרות חישוב איטרטיבי מופעלת, לכן עליך להשבית אותה לפני שתתחיל להסתכל בחוברת העבודה עבור הפניות מעגליות.
- במקרה שלא נמצא הפניה מעגלית בגיליון הפעיל, שורת המצב מציגה רק 'הפניות מעגליות' ללא כתובת תא.
- תקבל הפניה מעגלית רק פעם אחת ולאחר שתלחץ על 'אישור', היא לא תציג את ההנחיה שוב בפעם הבאה.
- אם חוברת העבודה שלך כוללת הפניות מעגליות, היא תציג לך את ההנחיה בכל פעם שתפתח אותה עד שתפתור את ההפניה המעגלית או עד שתפעיל חישוב איטרטיבי.
הסר הפניה מעגלית באקסל
קל למצוא הפניות מעגליות, אבל תיקון זה לא כל כך פשוט. למרבה הצער, אין אפשרות באקסל שתאפשר לך להסיר את כל ההפניות המעגליות בבת אחת.
כדי לתקן הפניות מעגליות, עליך למצוא כל הפניה מעגלית בנפרד ולנסות לשנות אותה, להסיר את הנוסחה המעגלית לגמרי או להחליף אותה באחרת.
לפעמים, בנוסחאות פשוטות, כל מה שאתה צריך לעשות הוא להתאים מחדש את הפרמטרים של הנוסחה כך שהיא לא תפנה חזרה לעצמה. לדוגמה, שנה את הנוסחה ב-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. לאחר שהפעלת חישוב איטרטיבי, תוכל להתחיל להשתמש בהפניות מעגליות לטובתך.
נניח שאתה קונה בית ואתה רוצה לתת לסוכן שלך עמלה של 2% על העלות הכוללת של הבית. העלות הכוללת תחושב בתא B6 ואחוז העמלה (עמלת סוכן) יחושב ב-B4. העמלה מחושבת מהעלות הכוללת והעלות הכוללת כוללת את העמלה. מכיוון שהתאים B4 ו-B6 תלויים זה בזה, זה יוצר התייחסות מעגלית.
הזן את הנוסחה לחישוב העלות הכוללת בתא B6:
=SUM(B1:B4)
מכיוון שהעלות הכוללת כוללת את עמלת הסוכן, כללנו את B4 בנוסחה הנ"ל.
כדי לחשב עמלת סוכן של 2%, הכנס את הנוסחה הזו ב-B4:
=B6*2%כעת הנוסחה בתא B4 תלויה בערך של B6 כדי לחשב 2% מסך העמלה והנוסחה ב-B6 תלויה ב-B4 כדי לחשב את העלות הכוללת (כולל עמלת סוכן), ומכאן ההתייחסות החוזרת.
אם החישוב האיטרטיבי מופעל, Excel לא ייתן לך אזהרה או 0 בתוצאה. במקום זאת, התוצאה של התאים B6 ו-B4 תחושב כפי שמוצג לעיל.
אפשרות החישובים האיטרטיביים מושבתת בדרך כלל כברירת מחדל. אם לא הפעלת את זה וכאשר תזין את הנוסחה ב-B4 שתיצור הפניה מעגלית. אקסל תנפיק את האזהרה וכאשר תלחץ על 'אישור', חץ המעקב יוצג.
זהו זה. זה היה כל מה שאתה צריך לדעת על הפניות מעגליות באקסל.