שימוש בהפניות מובנות עם טבלאות Excel

מה קורה כאשר אני משתמש בהפניות מפורשות לתאים?

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

1. בגליון העבודה לדוגמה, לחץ על תא E2

2. בשורת הנוסחאות, הזן =C2*D2 והקש Enter.

שים לב כי למרות ש- Excel מעתיק את הנוסחה לכל אורך העמודה, הוא אינו משתמש בהפניות מובנות. אם, לדוגמה, אתה מוסיף עמודה בין העמודות הקיימות C ו- D, עליך לשנות את הנוסחה.

כיצד ניתן לשנות שם טבלה?

כאשר אתה יוצר טבלת Excel‏, Excel יוצר שם טבלה המוגדר כברירת מחדל (טבלה1, טבלה2 וכן הלאה), אבל באפשרותך לשנות את שם הטבלה כדי לתת לה משמעות.

1. בחר תא כלשהו בטבלה כדי להציג את הכרטיסיה 'כלי > ' ברצועת הכלים.

2. הקלד את השם הרצוי בתיבה שם טבלה והקש Enter.

בנתונים לדוגמה, השתמשנו בשם DeptSales.

השתמש בכללים הבאים עבור שמות טבלאות:

• השתמש בתווים חוקיים  התחל תמיד שם עם אות, תו קו תחתון (_) או קו נטוי הפוך (\). השתמש באותיות, מספרים, תקופות ות תווי מקף תחתון עבור שאר השם. לא ניתן להשתמש ב- "c" ,"C",‏ "R" או "r" עבור השם מכיוון שהם מוקצים כבר כקיצור דרך לבחירת העמודה או השורה עבור התא הפעיל כאשר אתה מזין אותם בתיבה שם או עבור אל .

• אל תשתמש בהפניות לתאים  שמות אינם יכולים להיות זהים להפניה לתא, כגון Z$100 או R1C1.

• אל תשתמש ברווח כדי להפריד בין מילים  לא ניתן להשתמש ברווחים בשם. באפשרותך להשתמש בתו מקף תחתון (_) ובנקודה (.) כמפרידי מילים. לדוגמה, DeptSales, Sales_Tax First.Quarter.

• שימוש ב- 255 תווים לכל היותר שם טבלה יכול להכיל עד 255 תווים.

• שימוש בשמות טבלאות ייחודיים שמות כפולים אינם מותרים. Excel אינו מבחין בין תווים רישיים לאותיות קטנות בשמות, כך שאם תזין "Sales" אך כבר יש לך שם אחר בשם "SALES" באותה חוברת עבודה, תתבקש לבחור שם ייחודי.

• שימוש במזהה אובייקט  אם בכוונתך לשלב בין טבלאות, טבלאות PivotTable ותרשימים, מומלץ ליצור קידומת עבור השמות שלך עם סוג האובייקט. לדוגמה: tbl_Sales עבור טבלת מכירות, pt_Sales עבור PivotTable של מכירות ו- chrt_Sales עבור תרשים מכירות, או ptchrt_Sales עבור PivotChart של מכירות. פעולה זו שומרת את כל השמות ברשימה מסודרת במנהל השמות.

כללי תחביר של הפניות מובנות

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

‎=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])‎

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

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

• מציין עמודה:    [Sales Amount] ו - [Commission Amount] הם מצייני עמודות המשתמשים בשמות העמודות שהן מייצגות. הם מפנים לנתוני העמודה, ללא כותרת עליונה או שורת סיכום. הקף תמיד את המציינים בסוגריים מרובעים, כפי שמוצג.

• מציין פריט:    [#Totals] ו- [#Data] הם מצייני פריטים מיוחדים המתייחסים לחלקים ספציפיים של הטבלה, כגון שורת הסכום.

• מציין הטבלה:    ‎[[#Totals],[Sales Amount]]‎ ו- ‎[[#Data],[Commission Amount]]‎ הם מצייני טבלה שמייצגים את החלקים החיצוניים של ההפניה המובנית. הפניות חיצוניות מגיעות לאחר שם הטבלה, ועליך לתחום אותן בסוגריים מרובעים.

• הפניה מובנית:    (DeptSales[[#Totals],[Sales Amount]] ו- DeptSales[[#Data],[Commission Amount]] הן הפניות מובנות, המיוצגים על-ידי מחרוזת המתחילה בשם הטבלה ומסתיימת במציין העמודה.

כדי ליצור או לערוך הפניות מובנות באופן ידני, השתמש בכללי התחביר הבאים:

• השתמש בסוגריים מרובעים מסביב למציינים    כל מצייני הטבלה, העמודה והפריט המיוחד חייבים להיות מוקפים בסוגריים מרובעים תואמים ([ ]). במציין המכיל מציינים אחרים נדרשים סוגריים תואמים חיצוניים כדי להקיף את הסוגריים התואמים הפנימיים של המציינים האחרים. לדוגמה: =DeptSales[[Sales Person]:[Region]]

• כל כותרות העמודה הן מחרוזות טקסט    אך הם אינם דורשים ציטוטים כאשר נעשה בהם שימוש בהפניה מובנית. מספרים או תאריכים, כגון 2014 או 1/1/2014, נחשבים גם הם למחרוזות טקסט. לא ניתן להשתמש בביטויים עם כותרות עמודות. לדוגמה, הביטוי DeptSalesFYSummary[[2014]:[2012]]‎ לא יפעל.

השתמש בסוגריים מרובעים מסביב לכותרות עמודה עם תווים מיוחדים    אם קיימים תווים מיוחדים, יש לתחום את כותרת העמודה המלאה בסוגריים מרובעים, כך שסוגריים מרובעים כפולים דרושים במציין עמודה. לדוגמה: ‎=DeptSalesFYSummary[[Total $ Amount]]‎

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

• Tab

• הזנת שורה

• החזרת גררה

• פסיק (,)

• נקודתיים (:)

• נקודה (.)

• סוגר מרובע שמאלי ([)

• סוגר מרובע ימני (])

• סימן סולמית (#)

• גרש בודד (')

• גרש כפול (")

• סוגר מסולסל שמאלי ({)

• סוגר מסולסל ימני (})

• סימן דולר ($)

• תו ^^

• אמפרסנד (&)

• כוכבית (*)

• סימן חיבור (+)

• סימן שוויון (=)

• סימן חיסור (-)

• סימן גדול >)

• סימן קטן מ- (<)

• סימן חילוק (/)

• סימן At (@)

• קו נטוי הפוך (\)

• סימן קריאה (!)

• סוגריים שמאליים (()

• סוגריים ימניים ())

• סימן אחוז (%)

• סימן שאלה (?)

• Backtick (')

• נקודה-פסיק (;)

• Tilde (~)

• קו תחתון (_)

• השתמש בתו ביטול עבור תווים מיוחדים מסוימים בכותרות עמודות    לתווים מסוימים יש משמעות מיוחדת, והם דורשים שימוש בגרש (') כתו ביטול. לדוגמה: ‎=DeptSalesFYSummary['#OfItems]‎

להלן רשימת התווים המיוחדים הדרושים לתו ביטול (') בנוסחה:

• סוגר מרובע שמאלי ([)

• סוגר מרובע ימני (])

• סימן סולמית(#)

• גרש בודד (')

• סימן At (@)

השתמש בתו רווח לשיפור הקריאות בהפניה מובנית    באפשרותך להשתמש בתווי רווח כדי לשפר את קריאותה של הפניה מובנית. לדוגמה: ‎=DeptSales[ [Sales Person]:[Region] ]‎ או ‎=DeptSales[[#Headers], [#Data], [% Commission]]‎

מומלץ להשתמש ברווח אחד:

• אחרי הסוגריים המרובעים השמאליים ([)

• לפני הסוגריים המרובעים האחרון (]).

• אחרי פסיק.

אופרטורים של הפניה

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

מצייני פריט מיוחד

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

הכשרת הפניות מובנות בעמודות מחושבות

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

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

דוגמאות לשימוש בהפניות מובנות

להלן מספר דרכים שבהן תוכל להשתמש בהפניות מובנות.

אסטרטגיות עבודה עם הפניות מובנות

שקול לבצע את הפעולות הבאות בעת עבודה עם הפניות מובנות.

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

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

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

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

• ביטול כותרות עמודות    באפשרותך להפעיל ולכבות את כותרות עמודות הטבלה מהכרטיסיה 'עיצוב' > שורת כותרת. אם תבטל את כותרות העמודות בטבלה, הפניות מובנות המשתמשות בשמות עמודות לא יושפעו, ועדיין תוכל להשתמש בהן בנוסחאות. הפניות מובנות המפנות ישירות לכותרות הטבלה (לדוגמה = DeptSales[[#Headers],[%Commission]]) ייתכנו #REF.

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

• שנה שם טבלה או עמודה    אם אתה משנה שם של טבלה או עמודה, Excel ישנה באופן אוטומטי את השימוש בכותרת הטבלה והעמודה בכל ההפניות המובנות הנמצאות בשימוש בחוברת העבודה.

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

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

זקוק לעזרה נוספת?

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

נושאים קשורים

מבט כולל על טבלאות Excel
וידאו: יצירה ועיצוב של טבלת Excel
סיכום הנתונים בטבלת Excel
עיצוב טבלת Excel
שינוי גודל של טבלה על-ידי הוספה או הסרה של שורות ועמודות
סינון נתונים בטווח או בטבלה
המרת טבלה לטווח
בעיות תאימות בטבלת Excel
ייצוא טבלת Excel ל- SharePoint
מבט כולל על נוסחאות ב- Excel