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

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

במקום להשתמש בהפניות מפורשות לתאים	Excel משתמש בשם טבלה ובשמות של עמודות
‎=Sum(C2:C7)‎	‎=SUM(DeptSales[Sales Amount])‎

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

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

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

Sales Person	Region	Sales[Amount]‎	‎% Commission	Commission Amount
Joe	North	260	10%
Robert	South	660	15%
Michelle	East	940	15%
Erich	West	410	12%
Dafna	North	800	15%
Rob	South	900	15%

1. העתק את הנתונים לדוגמה מהטבלה לעיל, כולל כותרות העמודות, והדבק אותם בתא A1 בגליון עבודה חדש של Excel.
2. כדי ליצור את הטבלה, בחר תא כלשהו בטווח הנתונים והקש Ctrl+T.
3. ודא שהתיבה לטבלה שלי יש כותרות מסומנת ובחר אישור.
4. בתא E2, הקלד סימן שוויון (=) ובחר בתא C2.
   בשורת הנוסחאות, ההפניה המובנית ‎[@[Sales Amount]]‎ מופיעה לאחר סימן השוויון.
5. הקלד כוכבית (*) מיד לאחר תו הסוגריים הסוגר ובחר בתא D2.
   בשורת הנוסחאות, ההפניה המובנית ‎[@[% Commission]]‎ מופיעה לאחר הכוכבית.
6. הקש Enter.
   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 (@)
• קו נטוי הפוך (\)
• סימן קריאה (!)
• סוגריים שמאליים ()
• סוגריים ימניים ())
• סימן אחוז (%)
• סימן שאלה (?)
• גשר הפוך (')
• נקודה-פסיק (;)
• טילדה (~)
• קו תחתון (_)

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

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

• תו סוגריים מרובעים פותח ([)
• סוגר מרובע ימני (])
• סולמית (#)
• גרש בודד (')
• סימן at (@)

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

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

• אחרי הסוגר השמאלי הראשון ([)
• לפני הסוגר הימני האחרון (]).
• אחרי פסיק.

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

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

הפניה מובנית זו:	מפנה אל:	באמצעות:	שהוא טווח התאים:
‎=DeptSales[[Sales Person]:[Region]]‎	כל התאים בשתי עמודות סמוכות או יותר	: (נקודתיים) אופרטור טווח	A2:B7
‎=DeptSales[Sales Amount],DeptSales[Commission Amount]‎	שילוב של שתי עמודות או יותר	, (פסיק) אופרטור איחוד	C2:C7, ‏E2:E7
‎=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]‎	ההצטלבות של שתי עמודות או יותר	(רווח) אופרטור הצטלבות	B2:C7

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

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

מציין פריט מיוחד זה:	מפנה אל:
‎#All	הטבלה בשלמותה, כולל כותרות עמודה, נתונים וסיכומים (אם קיימים).
‎#Data	רק שורות הנתונים.
‎#Headers	שורת הכותרת בלבד.
‎#Totals	שורת הסיכום בלבד. אם שורה זו אינה קיימת, החזרת הערך Null.
‎#This Row או @ או ‎@[Column Name]‎	רק התאים באותה שורה כשל הנוסחה. לא ניתן לשלב מציינים אלה עם מצייני פריטים מיוחדים אחרים. השתמש בהם כדי לאלץ התנהגות של הצטלבות עקיפה עבור ההפניה או כדי לעקוף התנהגות של הצטלבות עקיפה ולהפנות לערכים בודדים מעמודה. Excel משנה באופן אוטומטי את מצייני ‎#This Row למציין @ הקצר יותר בטבלאות הכוללות יותר משורת נתונים אחת. אך אם הטבלה שלך מכילה שורה אחת בלבד, Excel אינו מחליף את מציין השורה #This, דבר שעלול לגרום לתוצאות חישוב בלתי צפויות בעת הוספת שורות נוספות. כדי להימנע מבעיות חישוב, הקפד להזין שורות מרובות בטבלה שלך לפני שתזין נוסחאות הפניה מובנית.

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

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

סוג הפניה מובנית	דוגמה	הערה
לא מלאה	‎=[Sales Amount]*[% Commission]‎	הכפלת הערכים התואמים מהשורה הנוכחית.
מלאה	‎=DeptSales[Sales Amount]*DeptSales[% Commission]‎	הכפלת הערכים התואמים עבור כל שורה של שתי העמודות.

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

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

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

הפניה מובנית זו:	מפנה אל:	שהוא טווח התאים:
‎=DeptSales[[#All],[Sales Amount]]‎	כל התאים בעמודה Sales Amount.	C1:C8
‎=DeptSales[[#Headers],[% Commission]]‎	כותרת העמודה ‎% Commission.	D1
‎=DeptSales[[#Totals],[Region]]‎	סיכום העמודה Region. אם השורה 'סכומים' אינה קיימת, החזרת הערך Null.	B8
‎=DeptSales[[#All],[Sales Amount]:[% Commission]]‎	כל התאים ב- Sales Amount ו- ‎% Commission.	C1:D8
‎=DeptSales[[#Data],[% Commission]:[Commission Amount]]‎	רק הנתונים של העמודות ‎% Commission ו- Commission Amount.	D2:E7
‎=DeptSales[[#Headers],[Region]:[Commission Amount]]‎	רק הכותרות של העמודות בין Region ו- Commission Amount.	B1:E1
‎=DeptSales[[#Totals],[Sales Amount]:[Commission Amount]]‎	סיכומי העמודות Sales Amount עד Commission Amount . אם השורה 'סכומים' אינה קיימת, החזרת הערך Null.	C8:E8
‎=DeptSales[[#Headers],[#Data],[% Commission]]‎	רק הכותרת והנתונים של ‎% Commission.	D1:D7
‎=DeptSales[[#This Row], [Commission Amount]]‎ או ‎=DeptSales[@Commission Amount]‎	התא בהצטלבות של השורה הנוכחית והעמודה Commission Amount. אם נעשה שימוש באותה שורה שבה משתמשת שורת הכותרת או שורת הסיכום, הפונקציה תחזיר שגיאת #VALUE! . אם אתה מקליד את הצורה הארוכה יותר של הפניה מובנית זו (‎#This Row) בטבלה עם שורות נתונים מרובות, Excel מחליף אותה באופן אוטומטי בצורה הקצרה יותר (@). שתיהן פועלות באותה צורה.	E5 (אם השורה הנוכחית היא 5)

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

חשוב על הפרטים הבאים בעת עבודה עם הפניות מובנות.

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

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

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

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

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

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

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

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

  הערה

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

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

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

תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.

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

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