בעת עבודה עם נוסחאות של Excel, ייתכן שתראה פעם את הודעת האזהרה הבאה.
הנחיה זו אומרת לך שיש הפניה מעגלית בגליון העבודה שלך וזה יכול להוביל או לחישוב שגוי על ידי הנוסחאות. הוא גם מבקש ממך להתייחס לבעיית הפניה מעגלית זו ולמיין אותה.
במדריך זה אכסה את כל מה שאתה צריך לדעת על הפניה מעגלית, וכן כיצד למצוא ולהסיר הפניות מעגליות ב- Excel.
אז בואו נתחיל!
מהו הפניה חוזר ב- Excel?
במילים פשוטות, הפניה מעגלית מתרחשת כאשר בסופו של דבר יש לך נוסחה בתא - שבעצמה משתמש בתא (שבו הוא הוזן) לחישוב.
תן לי לנסות להסביר זאת באמצעות דוגמה פשוטה.
נניח שיש לך את מערך הנתונים בתא A1: A5 ותשתמש בנוסחה שלהלן בתא A6:
= SUM (A1: A6)
זה ייתן לך אזהרת סימוכין מעגלית.
הסיבה לכך היא שברצונך לסכם את הערכים בתא A1: A6, והתוצאה צריכה להיות בתא A6.
זה יוצר לולאה מכיוון שאקסל רק ממשיכה להוסיף את הערך החדש בתא A6, הממשיך להשתנות (מכאן לולאת הפניה מעגלית).
כיצד למצוא הפניות מעגליות ב- Excel?
אף על פי ששורת האזהרה ההפניה המעגלית מספיק אדיבה לומר לך שהיא קיימת בגליון העבודה שלך, היא לא אומרת לך היכן היא מתרחשת ואיזה הפניות לתאים גורמות לה.
אז אם אתה מנסה למצוא הפניות מעגליות בגליון העבודה ולטפל בו, אתה צריך לדעת דרך למצוא אותן איכשהו.
להלן השלבים למציאת הפניה מעגלית ב- Excel:
- הפעל את גליון העבודה המכיל את ההפניה המעגלית
- לחץ על הכרטיסייה נוסחאות
- בקבוצה עריכת נוסחאות, לחץ על הסמל הנפתח בבדיקת שגיאות (חץ קטן כלפי מטה מימין)
- העבר את הסמן מעל האפשרות הפניות מעגליות. הוא יראה לך את התא שיש לו הפניה מעגלית בגליון העבודה
- לחץ על כתובת התא (המוצגת) והיא תעביר אותך לתא זה בגליון העבודה.
לאחר שתתייחס לבעיה, תוכל שוב לבצע את אותם השלבים שלמעלה והיא תציג הפניות לתאים נוספות בעלות ההפניה המעגלית. אם אין, לא תראה שום הפניה לתא,
עוד דרך מהירה וקלה למצוא את ההפניה המעגלית היא על ידי הסתכלות בשורת המצב. בחלק השמאלי של זה, הוא יראה לך את הטקסט עיגול מעגלי יחד עם כתובת התא.
ישנם מספר דברים שעליך לדעת בעת עבודה עם הפניות מעגליות:
- במקרה שהחישוב האיטרטיבי מופעל (יוסבר בהמשך הדרכה זו), שורת המצב לא תציג את כתובת תא ההתייחסות המעגלית.
- במקרה שההפניה המעגלית לא נמצאת בגיליון הפעיל (אלא בגיליונות אחרים באותו חוברת עבודה), היא תציג רק הפניה מעגלית ולא את כתובת התא
- במקרה שתקבל פעם אחת הודעת אזהרה מעגלית ותבטל אותה, היא לא תציג את ההנחיה שוב בפעם הבאה.
- אם תפתח חוברת עבודה עם הפניה המעגלית, היא תראה לך את ההנחיה ברגע שנפתח חוברת העבודה.
כיצד להסיר הפניה מעגלית ב- Excel?
לאחר שזיהית שיש גיליונות מעגליים בגיליון שלך, הגיע הזמן להסיר אותם (אלא אם כן אתה רוצה שהם יהיו שם מסיבה כלשהי).
למרבה הצער, זה לא פשוט כמו לחיצה על מקש המחיקה. מכיוון שהן תלויות בנוסחאות וכל נוסחה שונה, עליך לנתח זאת על בסיס כל מקרה לגופו.
במקרה שמדובר רק בכך שהפניה לתא גורמת לבעיה בטעות, אתה יכול פשוט לתקן על ידי התאמת ההפניה.
אבל לפעמים, זה לא כל כך פשוט.
הפניה מעגלית יכולה להיגרם גם על סמך תאים מרובים שניזונים זה לזה ברמות רבות.
תן לי להראות לך דוגמא.
להלן יש הפניה מעגלית בתא C6, אך לא מדובר רק במקרה של התייחסות עצמית. זה רב רמות שבו התאים שבהם הוא משתמש בחישובים מתייחסים גם זה לזה.
- הנוסחאות בתא A6 הן = SUM (A1: A5)+C6
- הנוסחה היא תא C1 = A6*0.1
- הנוסחה בתא C6 היא = A6+C1
בדוגמה שלעיל, התוצאה בתא C6 תלויה בערכים בתא A6 ו- C1, אשר בתורם תלויים בתא C6 (ובכך גורמים לשגיאת ההתייחסות המעגלית)
ושוב, בחרתי בדוגמה ממש פשוטה רק למטרות הדגמה. במציאות, אלה יכולים להיות די קשים לברר ואולי רחוקים באותו גליון עבודה או אפילו מפוזרים על פני גליונות עבודה מרובים.
במקרה כזה, יש דרך אחת לזהות את התאים שגורמים להתייחסות מעגלית ולאחר מכן לטפל באלה.
זה באמצעות האפשרות Trace Precedents.
להלן השלבים לשימוש בתקדימי עקבות לאיתור תאים המזינים לתא בעל הפניה המעגלית:
- בחר את התא המכיל את ההפניה המעגלית
- לחץ על הכרטיסייה נוסחאות
- לחץ על עקוב אחר תקדימים
השלבים שלעיל יראו לך חיצים כחולים שיגידו לך אילו תאים מוזנים לנוסחה בתא שנבחר. כך תוכלו לבדוק את הנוסחאות והתאים ולהיפטר מההתייחסות המעגלית.
במקרה שאתה עובד עם מודלים פיננסיים מורכבים, יתכן שתקדימים אלה גם עומקים במספר רבדים.
זה עובד היטב אם יש לך את כל הנוסחאות המתייחסות לתאים באותו גליון עבודה. אם הוא נמצא בדפי עבודה מרובים, שיטה זו אינה יעילה.
כיצד להפעיל/לבטל חישובים איטרטיביים ב- Excel
כאשר יש לך הפניה מעגלית בתא, תחילה תקבל את הודעת האזהרה כפי שמוצג להלן, ואם תסגור תיבת דו -שיח זו, היא תיתן לך 0 כתוצאה מהתא.
הסיבה לכך היא שכאשר יש הפניה מעגלית, מדובר בלולאה אינסופית ואקסל לא רוצה להיתפס לזה. אז הוא מחזיר 0.
אבל במקרים מסוימים, ייתכן שתרצה שההפניה המעגלית תהיה פעילה ותעשה כמה חזרות. במקרה כזה, במקום לולאה אינסופית, ותוכל להחליט כמה פעמים יש להריץ את הלולאה.
זה נקרא חישוב איטרטיבי באקסל.
להלן השלבים לאפשר ולהגדיר חישובים איטרטיביים ב- Excel:
- לחץ על הכרטיסייה קובץ
- לחץ על אפשרויות. פעולה זו תפתח את תיבת הדו -שיח אפשרויות Excel
- בחר נוסחה בחלונית השמאלית
- בסעיף אפשרויות החישוב, סמן את התיבה 'אפשר חישוב איטרטיבי'. כאן תוכל לציין את האיטרציות המרביות ואת ערך השינוי המרבי
זהו זה! השלבים שלעיל יאפשרו חישוב איטרטיבי ב- Excel.
תן לי גם להסביר במהירות את שתי האפשרויות בחישוב האיטרטיבי:
- מקסימום חזרות: זהו המספר המרבי של הפעמים שברצונך ש- Excel יחשב לפני שיתן לך את התוצאה הסופית. אז אם תציין זאת כ- 100, Excel יפעיל את הלולאה 100 פעמים לפני שתתן לך את התוצאה הסופית.
- שינוי מקסימלי: זהו השינוי המרבי, שאם לא יושג בין איטרציות, החישוב יופסק. כברירת מחדל, הערך הוא .001. ככל שערך זה נמוך יותר, כך התוצאה תהיה מדויקת יותר.
זכור שככל שמספר הפעמים שהאיטרציות עוברות, כך נדרש יותר זמן ומשאבים כדי שאקסל תעשה זאת. במקרה שאתה שומר את האיטרציות המרביות גבוהות, הדבר עלול להוביל להאטת הקצב של Excel או לקרוסתו.
הערה: כאשר חישובים איטרטיביים מופעלים, Excel לא יראה לך את הפקודת האזהרה המעגלית וגם יציג אותה כעת בשורת המצב.שימוש מכוון בהפניות מעגליות
ברוב המקרים, הימצאות הפניה מעגלית בגליון העבודה שלך תהיה שגיאה. ובגלל זה Excel מראה לך הודעה שאומרת - "נסה להסיר או לשנות הפניות אלה או להעביר את הנוסחאות לתאים שונים."
אך ייתכנו מקרים ספציפיים בהם אתה זקוק לעיון מעגלי על מנת שתוכל להשיג את התוצאה הרצויה.
מקרה אחד ספציפי שכזה כבר כתבתי על כך שהוא מקבל את חותמת הזמן בתא בתא ב- Excel.
לדוגמה, נניח שברצונך ליצור נוסחה כך שכל רשומה תתבצע בתא בעמודה A, חותמת הזמן תופיע בעמודה B (כפי שמוצג להלן):
אמנם ניתן להכניס בקלות חותמת זמן באמצעות הנוסחה שלהלן:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
הבעיה בנוסחה שלעיל היא שהיא תעדכן את כל חותמות הזמן ברגע שיבוצע שינוי כלשהו בגליון העבודה או אם גיליון העבודה ייפתח מחדש (מכיוון שנוסחת ה- NOW נדיפה)
כדי לעקוף בעיה זו, תוכל להשתמש בשיטת הפניה מעגלית. השתמש באותה נוסחה, אך הפעל חישוב איטרטיבי.
ישנם גם מקרים אחרים בהם רצויה היכולת להשתמש בהתייחסות מעגלית (תוכל למצוא דוגמא אחת כאן).
הערה: למרות שיכולים להיות מקרים שבהם תוכל להשתמש בהפניה מעגלית, אך לדעתי עדיף להימנע משימוש בה. הפניות חוזר יכולות גם הן לגרוע מהביצועים של חוברת העבודה שלך ולגרום להאט. במקרים נדירים שבהם אתה צריך את זה, אני תמיד מעדיף להשתמש בקודי VBA כדי לבצע את העבודה.אני מקווה שמצאת הדרכה שימושית!
הדרכות אקסל אחרות שעשויות להועיל לך:
- #REF! שגיאה ב- Excel; כיצד לתקן את שגיאת ההפניה!
- טיפול בשגיאות VBA של Excel
- השתמש ב- IFERROR עם VLOOKUP כדי להיפטר משגיאות מספר לא רלוונטי
- כיצד להפנות לגיליון אחר או לחוברת עבודה ב- Excel (עם דוגמאות)
- הפניות לתאים מוחלטים, יחסיים ומעורבים ב- Excel
