הפניה מוחלטת משאירה תא קבוע כשמעתיקים נוסחה. ב-=B2*$E$1, סימני הדולר נועלים את E1: העתיקו את הנוסחה למטה וכל שורה עדיין כופלת ב-E1, ואילו B2 זזה ל-B3, ל-B4 וכן הלאה. כדי להוסיף את סימני הדולר, לחצו על ההפניה בנוסחה ולחצו F4 (במק Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
C2 הוקלד פעם אחת והועתק למטה. לחצו על C4: הנוסחה שלו היא =B4*$E$1. תא המכירות זז לשורה 4, השיעור נשאר ב-E1. שנו את השיעור ב-E1 ל-8% וכל עמלה מתעדכנת.
הפניה יחסית מול הפניה מוחלטת
| הפניה | שם | הועתקה שורה אחת למטה ועמודה אחת ימינה |
|---|---|---|
A1 | יחסית | B2 |
$A$1 | מוחלטת | $A$1 |
A$1 | מעורבת: השורה נעולה | B$1 |
$A1 | מעורבת: העמודה נעולה | $A2 |
הפניה רגילה כמו B2 היא יחסית: אקסל שומר אותה כ"התא שנמצא במיקום הזה ממני", ולכן העתק שנמצא שורה אחת נמוך יותר מצביע שורה אחת נמוך יותר. זה בדיוק מה שרוצים לנתונים של כל שורה, וזו ברירת המחדל. ה-$ שלפני אות עמודה או מספר שורה נועל את החלק הזה.
הטעות הקלאסית: העתקה למטה בלי $
הנה שוב גיליון העמלות, עם =B2*E1 ב-C2 ובלי סימני דולר. השורה הראשונה נכונה. כל השאר הן 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
ב-C3 יש =B3*E2: ההפניה לשיעור זזה למטה ל-E2, שהוא ריק, ותא ריק נחשב 0. תקנו את זה כאן: לחצו על C2, שנו את הנוסחה ל-=B2*$E$1 ולחצו Enter. כל העמודה מתעדכנת, כי C3:C6 הם העתקים של C2. כשהתא הקבוע הוא מחלק, כמו ב-=B2/B7 לחלק מתוך סכום כולל, אותה טעות מציגה #DIV/0! במקום 0 (אחוז מתוך סכום כולל הוא המקרה הנפוץ).
לחצו F4 כדי להוסיף את סימני הדולר
בזמן הקלדה או עריכה של נוסחה, שימו את הסמן בתוך הפניה (או ממש אחריה) ולחצו F4. כל לחיצה עוברת לצורה הבאה:
E1 -> $E$1 -> E$1 -> $E1 -> E1
בהרבה מחשבים ניידים F4 שולט במסך או בשמע, אז לחצו Fn+F4. במק, השתמשו ב-Cmd+T, או ב-Fn+F4. אפשר גם להקליד את סימני ה-$ בעצמכם.
הפניות מעורבות: נעילה של השורה בלבד או של העמודה בלבד
להפניה מעורבת יש סימן דולר אחד. $A2 תמיד קוראת את עמודה A אבל נותנת לשורה לזוז; B$1 תמיד קוראת את שורה 1 אבל נותנת לעמודה לזוז. כששתיהן באותה נוסחה, נוסחה אחת שמועתקת על פני רשת בונה לוח כפל:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
ב-B2 יש =$A2*B$1. לחצו על F6: יש בו =$A6*F$1, מספר השורה מעמודה A כפול מספר העמודה משורה 1, ולכן הוא מציג 25. הסירו סימן דולר אחד ב-B2 והלוח מתפרק, כי ההעתקים מתחילים לכפול תאים שכנים במקום כותרות.
אותה תבנית מתמחרת רשימה בכמה הנחות: =$A2*(1-B$1) עם מחירים לאורך עמודה A ושיעורי הנחה לרוחב שורה 1.
סכום מצטבר עם טווח נעול בחצי
אפשר לנעול טווח רק בקצה אחד. =SUM($B$2:B2) מתחילה תמיד ב-B2, ואילו הסוף שלה זז למטה כשהנוסחה מועתקת, כך שכל שורה מחברת את כל מה שעד אליה:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
ב-C6 יש =SUM($B$2:B6) והוא מציג 2230, הסכום של כל חמשת החודשים. אותו טווח נעול בחצי גורם ל-=COUNTIF($A$2:A2,A2) לספור כמה פעמים ערך הופיע עד כה, וכך מסמנים כפילויות שאחרי ההופעה הראשונה.
תרגול: נוסחה אחת לכל הטבלה
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
תורכם: ב-B2, כתבו את המחיר של המוצר הראשון אחרי ההנחה שב-B1. השתמשו ב-$ כך שאותה נוסחה, מועתקת לרוחב ולמטה עד D5, תיתן כל מחיר בטבלה.
הגיליון מעתיק את הנוסחה שלכם לכל תא ב-B2:D5, כמו שידית המילוי הייתה עושה, והבדיקה קוראת את כל שתים עשרה התוצאות. בלי סימני הדולר הנכונים, ההעתקים בשורה 3 או בעמודה C קוראים את המחיר הלא נכון או את ההנחה הלא נכונה.
הפניות מוחלטות לגיליון אחר או לטבלת חיפוש
סימני הדולר עובדים באותה דרך עם שם גיליון: =B2*Settings!$B$1. הם הכי חשובים בחיפושים, שבהם הטבלה צריכה להישאר במקומה בזמן שערך החיפוש זז: =VLOOKUP(A2,$E$2:$F$10,2,FALSE) שמועתקת למטה ממשיכה לחפש ב-E2:F10, ואילו =VLOOKUP(A2,E2:F10,2,FALSE) מזיזה את הטבלה שורה אחת למטה בכל העתק ומתחילה לפספס את השורות הראשונות (VLOOKUP). אם תא קבוע משמש בהרבה נוסחאות, אפשר גם לתת לו שם דרך נוסחאות > הגדר שם ולכתוב =B2*Rate; שם שמוגדר כך מצביע על אותו תא מכל נוסחה, כמו $E$1.
שאלות נפוצות
מה אומר הסימן $ בנוסחת אקסל?
הוא נועל את החלק של ההפניה שבא אחריו. ב-$E$1 נעולות גם העמודה E וגם השורה 1, ולכן ההפניה נשארת E1 לאן שלא תועתק הנוסחה. E$1 נועלת רק את השורה ו-$E1 רק את העמודה.
מה קיצור המקלדת להפניה מוחלטת באקסל?
לחצו בתוך ההפניה בזמן עריכת הנוסחה ולחצו F4 (Fn+F4 בהרבה מחשבים ניידים). כל לחיצה עוברת בין $A$1, A$1, $A1 ו-A1. במק, לחצו Cmd+T, או Fn+F4.
מה ההבדל בין הפניה יחסית להפניה מוחלטת?
הפניה יחסית כמו B2 זזה כשמעתיקים את הנוסחה: שורה אחת למטה היא הופכת ל-B3. הפניה מוחלטת כמו $B$2 נשארת $B$2. השתמשו בהפניות מוחלטות לתא יחיד שכל שורה צריכה, כמו שיעור או סכום כולל.
מה זו הפניה מעורבת באקסל?
הפניה עם סימן דולר אחד: $A2 שומרת את העמודה ונותנת לשורה לזוז, B$1 שומרת את השורה ונותנת לעמודה לזוז. =$A2*B$1 שמועתקת לרוחב ולאורך של רשת בונה לוח כפל.
למה הנוסחה שלי מציגה 0 או #DIV/0! אחרי שגררתי אותה למטה?
הפניה שהייתה צריכה להישאר קבועה זזה עם ההעתקה. אם בשורה 2 יש =B2/B7, שורה 3 מקבלת =B3/B8, ו-B8 ריק. נעלו את הסכום הכולל עם =B2/$B$7 והעתיקו שוב.
