קישורים באקסל - מוחלטים, יחסיים ומעורבים. שגיאות בעבודה עם קישורים יחסיים באקסל

כדי להקל על חישובים באמצעות נוסחאות במצב אוטומטי, נעשה שימוש בהפניות לתאים. בהתאם לסוג הכתיבה, הם מחולקים לשלושה סוגים עיקריים:

1. קישורים יחסית. משמש לחישובים פשוטים. העתקת נוסחה כרוכה בשינוי הקואורדינטות.
2. קישורים מוחלטים. אם אתה צריך לייצר חישובים מורכבים יותר, אפשרות זו מתאימה. לתיקון השתמש בסמל "$". דוגמה: $A$1.
3. קישורים מעורבים. סוג זה של כתובת משמש בחישובים כאשר יש צורך לתקן עמודה או שורה בנפרד. דוגמה: $A1 או A$1.

אם יש צורך להעתיק את הנתונים של הנוסחה שהוזנה, נעשה שימוש בהפניות עם כתובת מוחלטת ומעורבת. המאמר יחשוף עם דוגמאות כיצד מתבצעים חישובים באמצעות סוגים שונים של קישורים.

הפניה יחסית לתא באקסל

זוהי קבוצה של תווים המגדירים את מיקומו של תא. קישורים בתוכנית נכתבים אוטומטית עם כתובת יחסית. לדוגמה: A1, A2, B1, B2. מעבר לשורה או לעמודה אחרת משנה את התווים בנוסחה. לדוגמה, עמדת מוצא A1. תנועה אופקית משנה את האות ל-B1, C1, D1 וכו'. באותו אופן, שינויים מתרחשים בעת תנועה לאורך קו אנכי, רק במקרה זה המספר משתנה – A2, A3, A4 וכו'. אם יש צורך לשכפל חישוב מאותו סוג בתא סמוך, חישוב מתבצע באמצעות התייחסות יחסית. כדי להשתמש בתכונה זו, בצע מספר שלבים:

1. ברגע שהנתונים מוכנסים לתא, הזז את הסמן ולחץ עם העכבר. הדגשה במלבן ירוק מעידה על הפעלת התא ועל המוכנות לעבודה נוספת.
2. על ידי לחיצה על צירוף המקשים Ctrl + C, אנו מעתיקים את התוכן ללוח.
3. אנו מפעילים את התא שאליו ברצונך להעביר נתונים או נוסחה שנכתבה בעבר.
4. על ידי לחיצה על השילוב Ctrl + V נעביר את הנתונים השמורים בלוח המערכת.

עצת מומחה! כדי לבצע את אותו סוג של חישובים בטבלה, השתמש בפריצת חיים. בחר את התא המכיל את הנוסחה שהוזנה קודם לכן. העברת הסמן מעל ריבוע קטן המופיע בפינה הימנית התחתונה, והחזקת לחצן העכבר השמאלי, גרור לשורה התחתונה או לעמודה הקיצונית, בהתאם לפעולה שבוצעה. בשחרור כפתור העכבר החישוב יתבצע באופן אוטומטי. כלי זה נקרא סמן המילוי האוטומטי.

דוגמה לקישור יחסי

כדי להבהיר זאת, שקול דוגמה לחישוב באמצעות נוסחה עם התייחסות יחסית. נניח שבעל חנות ספורט לאחר שנה של עבודה צריך לחשב את הרווח מהמכירות.

סדר הפעולות:

1. הדוגמה מראה שעמודות B ו-C שימשו למילוי כמות הסחורה הנמכרת ומחירה. בהתאם לכך, כדי לכתוב את הנוסחה ולקבל את התשובה, בחר בעמודה D. הנוסחה נראית כך: = B2 *C

שים לב! כדי להקל על תהליך כתיבת נוסחה, השתמש בטריק קטן. שים את הסימן "=", לחץ על כמות הסחורה הנמכרת, הגדר את הסימן "*" ולחץ על מחיר המוצר. הנוסחה שאחרי סימן השוויון תיכתב אוטומטית.

2. לתשובה הסופית, הקש "Enter". לאחר מכן, עליך לחשב את הסכום הכולל של הרווח שהתקבל מסוגי מוצרים אחרים. ובכן, אם מספר השורות אינו גדול, ניתן לבצע את כל המניפולציות באופן ידני. למילוי מספר רב של שורות בו-זמנית באקסל, ישנה פונקציה שימושית אחת המאפשרת להעביר את הנוסחה לתאים אחרים.
3. העבר את הסמן מעל הפינה הימנית התחתונה של המלבן עם הנוסחה או התוצאה המוגמרת. המראה של צלב שחור משמש כאות שניתן לגרור את הסמן כלפי מטה. כך, מתבצע חישוב אוטומטי של הרווח המתקבל עבור כל מוצר בנפרד.
4. על ידי שחרור כפתור העכבר לחוץ, אנו מקבלים את התוצאות הנכונות בכל השורות.

בלחיצה על תא D3, אתה יכול לראות שהקואורדינטות של התא שונו אוטומטית, ועכשיו נראות כך: =B3 *C3. מכאן נובע שהקישורים היו יחסיים.

שגיאות אפשריות בעבודה עם קישורים יחסיים

ללא ספק, פונקציית אקסל זו מפשטת מאוד את החישובים, אך במקרים מסוימים עלולים להתעורר קשיים. הבה נשקול דוגמה פשוטה לחישוב מקדם הרווח של כל פריט סחורה:

1. צור טבלה ומלא: א' - שם המוצר; ב' - כמות שנמכרה; ג - עלות; D הוא הסכום שהתקבל. נניח שיש רק 11 פריטים במבחר. לכן, בהתחשב בתיאור העמודות, ממלאות 12 שורות וסכום הרווח הכולל הוא D
2. לחץ על תא E2 והיכנס =D2 /D13.
3. לאחר לחיצה על כפתור "Enter", מופיע מקדם החלק היחסי במכירות של הפריט הראשון.
4. מותח את העמודה כלפי מטה ומחכה לתוצאה. עם זאת, המערכת נותנת את השגיאה "#DIV/0!"

הסיבה לטעות היא שימוש בהתייחסות יחסית לחישובים. כתוצאה מהעתקת הנוסחה, הקואורדינטות משתנות. כלומר, עבור E3, הנוסחה תיראה כך =D3 /D13. מכיוון שתא D13 אינו מלא ובאופן תיאורטי יש לו ערך אפס, התוכנית תיתן שגיאה עם המידע שחלוקה באפס היא בלתי אפשרית.

חשוב! כדי לתקן את השגיאה, יש צורך לכתוב את הנוסחה בצורה כזו שהקואורדינטות D13 קבועות. לפניות יחסית אין פונקציונליות כזו. לשם כך, יש סוג אחר של קישורים - מוחלטים. 

איך עושים קישור מוחלט באקסל

הודות לשימוש בסמל $, ניתן היה לתקן את קואורדינטות התא. איך זה עובד, נשקול עוד. מכיוון שהתוכנית משתמשת בכתובת יחסית כברירת מחדל, בהתאם, כדי להפוך אותה למוחלטת, תצטרך לבצע מספר פעולות. בואו ננתח את הפתרון לשגיאה "כיצד למצוא את המקדם ממכירת מספר פריטי סחורה", ביצוע החישוב באמצעות כתובת מוחלטת:

1. לחץ על E2 והזן את הקואורדינטות של הקישור =D2 /D13. מכיוון שהקישור הוא יחסי, יש להגדיר סמל לתיקון הנתונים.
2. תקן את הקואורדינטות של תא D כדי לבצע פעולה זו, הקדימו את האות המציינת את העמודה ומספר השורה על ידי הצבת סימן "$".

עצת מומחה! כדי להקל על משימת הכניסה, מספיק להפעיל את התא לעריכת הנוסחה וללחוץ על מקש F4 מספר פעמים. עד שתקבל ערכים מספקים. הנוסחה הנכונה היא כדלקמן: =D2/$D$13.

3. לחץ על כפתור "Enter". כתוצאה מהפעולות שבוצעו, התוצאה הנכונה אמורה להופיע.
4. גרור את הסמן לשורה התחתונה כדי להעתיק את הנוסחה.

עקב שימוש בכתובת מוחלטת בחישובים, התוצאות הסופיות בשורות הנותרות יהיו נכונות.

כיצד לשים קישור מעורב באקסל

עבור חישובי נוסחאות, לא רק הפניות יחסיות ומוחלטות משמשות, אלא גם מעורבות. התכונה הייחודית שלהם היא שהם מתקנים את אחת הקואורדינטות.

• לדוגמה, כדי לשנות את המיקום של קו, עליך לכתוב את הסימן $ לפני ייעוד האות.
• להיפך, אם סימן הדולר נכתב לאחר ייעוד האות, אז האינדיקטורים בשורה יישארו ללא שינוי.

מכאן נובע שכדי לפתור את הבעיה הקודמת בקביעת מקדם מכירות הסחורה באמצעות כתובת מעורבת, יש צורך לתקן את מספר הקו. כלומר, הסימן $ ממוקם אחרי אות העמודה, כי הקואורדינטות שלו לא משתנות אפילו בהתייחסות יחסית. ניקח דוגמה:

1. לחישובים מדויקים, הזן =D1/$D$3 ולחץ על "Enter". התוכנית נותנת את התשובה המדויקת.
2. כדי להעביר את הנוסחה לתאים הבאים במורד העמודה ולקבל תוצאות מדויקות, גרור את הידית לתא התחתון.
3. כתוצאה מכך, התוכנית תיתן את החישובים הנכונים.

שימו לב! אם תגדיר את הסימן $ לפני האות, אז Excel ייתן שגיאה "#DIV/0!", מה שיגיד שלא ניתן לבצע את הפעולה הזו.

כתובת "סופר אבסולוטית".

בסופו של דבר, הבה נסתכל על דוגמה נוספת לקישור מוחלט - התייחסות "SuperAbsolute". מהן התכונות וההבדלים שלה. קח את המספר המשוער 30 והזן אותו בתא B2. זה המספר הזה שיהיה העיקרי, יש צורך לבצע איתו סדרה של פעולות, למשל, להעלות אותו לעוצמה.

1. לביצוע נכון של כל הפעולות, הזן את הנוסחה הבאה בעמודה C: =$B$2^$D2. בעמודה D נזין את ערך המעלות.

2. לאחר לחיצה על כפתור "Enter" והפעלת הנוסחה, אנו מותחים את הסמן במורד העמודה.
3. אנחנו מקבלים את התוצאות הנכונות.

השורה התחתונה היא שכל הפעולות שבוצעו התייחסו לתא קבוע אחד B2, לכן:

• העתקת נוסחה מתא C3 לתא E3, F3 או H3 לא תשנה את התוצאה. זה יישאר ללא שינוי - 900.
• אם צריך להוסיף עמודה חדשה, הקואורדינטות של התא עם הנוסחה ישתנו, אך גם התוצאה תישאר ללא שינוי.

זו המוזרות של הקישור "SuperAbsolute": אם תצטרך להזיז, התוצאה לא תשתנה. עם זאת, ישנם מצבים שבהם נתונים מוכנסים ממקורות צד שלישי. כך, העמודות מוזזות הצידה, והנתונים נקבעים בצורה הישנה בעמודה B2. מה קורה במקרה הזה? בערבוב הנוסחה משתנה בהתאם לפעולה שבוצעה, כלומר היא כבר לא תצביע על B2, אלא על C2. אבל מכיוון שההכנסה בוצעה ב-B2, התוצאה הסופית תהיה שגויה.

התייחסות! כדי להיות מסוגל להכניס פקודות מאקרו ממקורות צד שלישי, עליך להפעיל את הגדרות המפתחים (הן מושבתות כברירת מחדל). כדי לעשות זאת, עבור אל אפשרויות, פתח את הגדרות הסרט וסמן את התיבה בעמודה הימנית מול "מפתח". לאחר מכן, תיפתח גישה לפונקציות רבות שהיו נסתרות בעבר מעיני המשתמש הממוצע.

זה מעלה את השאלה: האם ניתן לשנות את הנוסחה מתא C2 כך שהמספר המקורי ייאסף מתא B, למרות הוספת עמודות נתונים חדשות? כדי להבטיח ששינויים בטבלה לא ישפיעו על קביעת הסכום הכולל בעת התקנת נתונים ממקורות צד שלישי, עליך לבצע את השלבים הבאים:

1. במקום הקואורדינטות של תא B2, הזן את האינדיקטורים הבאים: =INDIRECT("B2"). כתוצאה מכך, לאחר הזזת הרכב הגיבוש ייראה כך: =INDIRECT(“B2”)^$E2.
2. הודות לפונקציה זו, הקישור תמיד מצביע על הריבוע עם הקואורדינטות B2, ללא קשר אם הוסיפו או הוסרו עמודות בטבלה.

יש להבין שתא שאינו מכיל נתונים כלשהם מציג תמיד את הערך "0".

סיכום

הודות לשימוש בשלושה סוגים של הקישורים המתוארים, מופיעות הזדמנויות רבות המקלות על העבודה עם חישובים באקסל. לכן, לפני שתתחיל לעבוד עם נוסחאות, קרא תחילה את הקישורים ואת הכללים להתקנתם.