ייחוס מוחלט ב-Excel (כיצד ליצור ולהשתמש)

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

• מהו ייחוס מוחלט
• כיצד ליצור ייחוסים מוחלטים
• שימוש בייחוס מוחלט עם דוגמאות
• חישוב אחוז מסך הכול
• חיפוש ערך ומחזירה את ערך ההתאמה המתאים
• 2 לחיצות כדי להפוך באופן מרוכז את הייחוסים של תאים לייחוסים מוחלטים עם Kutools
• ייחוס יחסי וייחוס מעורב
• דברים שיש לזכור

הורדה חינמית של קובץ לדוגמה

וידאו: ייחוס מוחלט

מהו ייחוס מוחלט

ייחוס מוחלט הוא סוג של ייחוס תא ב-Excel.

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

ייחוס מוחלט נוצר על ידי הוספת סימן דולר ($) לפני הייחוס לעמודה ולשורה בנוסחה. למשל, כדי ליצור ייחוס מוחלט עבור התא A1, עליך לייצגו כ-$A$1.

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

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

אם אתה משתמש בייחוס יחסי בנוסחה כמו "=B5*B2", תוצאות שגויות יוחזרו כאשר תגרור את ידית האוטו-מילוי כלפי מטה כדי להחיל את הנוסחה. מכיוון שייחוס לתא B2 ישתנה בהתאם לתאים בנוסחה. כעת, הנוסחה בתא C6 היא "=B6*B3", והנוסחה בתא C7 היא "=B7*B4".

אבל אם תשתמש בייחוס מוחלט לתא B2 עם הנוסחה "=B5*$B$2", זה יבטיח שהשיעור המס ישאר זהה לכל התאים כאשר הנוסחה מוזזת כלפי מטה באמצעות ידית האוטו-מילוי, והתוצאות יהיו נכונות.

שימוש בייחוס יחסי		שימוש בייחוס מוחלט

כיצד ליצור ייחוסים מוחלטים

כדי ליצור ייחוס מוחלט ב-Excel, הוסף סימני דולר ($) לפני הייחוס לעמודה ולשורה בנוסחה. ישנם שני דרכים ליצור ייחוס מוחלט:

הוספה ידנית של סימני דולר לייחוס התא

אתה יכול להוסיף ידנית סימני דולר ($) לפני הייחוס לעמודה ולשורה שאתה רוצה להפוך למוחלט בזמן הקלדת הנוסחה בתא.

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

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

שימוש בקיצור F4 להמרת ייחוס יחסי לייחוס מוחלט

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

מקש F4 עובר בין הייחוסים: יחסי, מוחלט ומעורב.

A1 → $A$1 → A$1 → $A1 → A1

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

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

שימוש בייחוס מוחלט עם דוגמאות

חלק זה מספק שתי דוגמאות מתי ואיך להשתמש בייחוסים מוחלטים בנוסחה ב-Excel.

דוגמה 1: חישוב אחוז מסך הכול

נניח שיש לך טווח נתונים (A3:B7) שמכיל את המכירות של כל פרי, והתא B8 מכיל את סך המכירות של הפרי, כעת ברצונך לחשב את האחוז של מכירת כל פרי מסך הכול.

הנוסחה הכללית לחישוב אחוז מסך הכול:

Percentage = Sale/Amount

השתמש בייחוס יחסי בנוסחה כדי לקבל את האחוז של הפרי הראשון כך:

=B4/B8

כאשר גררים את ידית האוטו-מילוי כלפי מטה כדי לחשב את האחוז של שאר הפרי, תוחזרנה שגיאות #DIV/0!.

מכיוון שכשאתה מגרר את ידית המילוי האוטומטי כדי להעתיק את הנוסחה לתאים מתחת, הייחוס היחסי B8 מותאם אוטומטית לייחוסים אחרים (B9, B10, B11) בהתבסס על מיקומם היחסי. והخلايا B9, B10 ו-B11 ריקות (אפס), כאשר המחלק הוא אפס, הנוסחה מחזירה שגיאה.

כדי לתקן את השגיאות, במקרה זה, עליך להפוך את הייחוס לתא B8 לייחוס מוחלט ($B$8) בנוסחה כדי למנוע ממנו להשתנות כאשר אתה מזיז או מעתיק את הנוסחה לאזור אחר. כעת הנוסחה עודכנה כך:

=B4/$B$8

לאחר מכן גרור את ידית האוטו-מילוי כלפי מטה כדי לחשב את האחוז של שאר הפרי.

דוגמה 2: חיפוש ערך וחזרה לערך התאמה מתאים

נניח שאתה רוצה לחפש את רשימת השמות ב-D4:D5 ולהחזיר את המשכורות המתאימות להם בהתבסס על שמות הצוות והמשכורות השנתיות המתאימות שניתנו בטווח (A4:B8).

הנוסחה הכללית לחיפוש היא:

=VLOOKUP(lookup_value, table_range, column_index, logical)

אם אתה משתמש בייחוס יחסי בנוסחה כדי לחפש ערך ולהחזיר את ערך ההתאמה המתאים כך:

=VLOOKUP(D4,A4:B8,2,FALSE)

לאחר מכן גרור את ידית האוטו-מילוי כלפי מטה כדי לחפש את הערך הבא, תוחזר שגיאה.

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

כדי למנוע שגיאות, השתמש בייחוס מוחלט $A$4:$B$8 במקום הייחוס היחסי A4:B8 בנוסחה:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

לאחר מכן גרור את ידית האוטו-מילוי כלפי מטה כדי לקבל את המשכורת של ליסה.

2 לחיצות כדי להפוך באופן מרוכז את הייחוסים של תאים לייחוסים מוחלטים עם Kutools

בין אם תבחר להקליד ידנית או להשתמש בקיצור F4, תוכל לשנות נוסחה אחת בלבד בכל פעם ב-Excel. אם ברצונך להפוך את הייחוסים לתאים במאות נוסחאות ב-Excel למוחלטות, כלי "המרת הפניות" של "Kutools for Excel" יכול לעזור לך לבצע את המשימה בשתי לחיצות.

כדי להפוך את הייחוסים לתאים מוחלטים בכמה נוסחאות, בחר את תאי הנוסחה ולחץ על "Kutools" > "עוד" > "המרת הפניות". לאחר מכן בחר את האפשרות "למוחלט" ולחץ על "אישור" או "החל". כעת כל הייחוסים לתאים של הנוסחאות שנבחרו הומרו למוחלטים.

ייחוס יחסי וייחוס מעורב

פרט לייחוס מוחלט, ישנם שני סוגי ייחוס נוספים: ייחוס יחסי וייחוס מעורב.

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

לדוגמה, כאשר אתה מקליד נוסחה בתא כגון "=A1+1", ולאחר מכן מגרר את ידית האוטו-מילוי כלפי מטה כדי למלא את הנוסחה לתא הבא, הנוסחה תשתנה אוטומטית ל-"=A2+1".

ייחוס מעורב מורכב משילוב של ייחוס מוחלט וייחוס יחסי. במילים אחרות, ייחוס מעורב משתמש בסימן הדולר ($) כדי לקבע את השורה או העמודה כאשר נוסחה מועתקת או ממולאת.

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

כנקודת פתיחה, תוכל להשתמש בנוסחה "=B3*C2" בתא C3 כדי להכפיל את 1 בתא B3 במספר (1) בעמודה הראשונה. עם זאת, כאשר אתה מגרר את ידית האוטו-מילוי לימין כדי למלא את התאים האחרים, תבחין שכל התוצאות שגויות למעט הראשונה.

זה קורה מכיוון שכשאתה מעתיק את הנוסחה לימין, מיקום השורה לא ישתנה, אך מיקום העמודה משתנה מ-B3 ל-C3, D3 וכו'. כתוצאה מכך, הנוסחאות בתאים מצד ימין (D3, E3 וכו') משתנות ל-"=C3*D2", "=D3*E2", וכן הלאה, בעוד שבפועל אתה רוצה אותן להיות "=B3*D2", "=B3*E2", וכך הלאה.

במקרה זה, עליך להוסיף סימן דולר ($) כדי לנעול את הייחוס לעמודה של “B3”. השתמש בנוסחה הבאה:

=$B3*C2

כעת, כאשר אתה מגרר את הנוסחה לימין, התוצאות נכונות.

לאחר מכן עליך להכפיל את המספר 1 בתא C2 במספרים בשורות שמתחת.

כאשר אתה מעתיק את הנוסחה כלפי מטה, מיקום העמודה של תא C2 לא ישתנה, אך מיקום השורה משתנה מ-C2 ל-C3, C4 וכו'. כתוצאה מכך, הנוסחאות בתאים שמתחת משתנות ל-"=$B4C3", "=$B5C4", וכו', מה שיגרום לתוצאות שגויות.

כדי לפתור את הבעיה הזו, שנה את “C2” ל-“C$2” כדי לשמור על הייחוס לשורה קבוע כאשר אתה מגרר את ידית האוטו-מילוי כלפי מטה כדי למלא את הנוסחאות.

=$B3*C$2

כעת תוכל לגרור את ידית האוטו-מילוי ימינה או כלפי מטה כדי לקבל את כל התוצאות.

דברים שיש לזכור

• סיכום של ייחוסי תאים

  סוג	דוגמה	סיכום
  ייחוס מוחלט	$A$1	לעולם לא משתנה כאשר נוסחה מועתקת לתאים אחרים
  ייחוס יחסי	A1	גם הייחוס לשורה וגם הייחוס לעמודה משתנים בהתבסס על מיקומם היחסי כאשר נוסחה מועתקת לתאים אחרים
  ייחוס מעורב	$A1/A$1	הייחוס לשורה משתנה כאשר נוסחה מועתקת לתאים אחרים אך הייחוס לעמודה קבוע/הייחוס לעמודה משתנה כאשר נוסחה מועתקת לתאים אחרים אך הייחוס לשורה קבוע;

• באופן כללי, ייחוסים מוחלטים לעולם לא משתנים כאשר נוסחה מועתקת. עם זאת, ייחוסים מוחלטים ישתנו אוטומטית כאשר שורה או עמודה מוספות או מוסרות מלמעלה או מצד שמאל בגליון העבודה. לדוגמה, בנוסחה "=$A$1+1", כאשר אתה מוסיף שורה בראש הגיליון, הנוסחה תשתנה אוטומטית ל-"=$A$2+1".

  

• מקש "F4" יכול לעבור בין ייחוס יחסי, מוחלט ומעורב.