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

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

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

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

Excel משתמש בשם טבלה ובשמות של עמודות

‎=Sum(C2:C7)‎

‎=SUM(DeptSales[Sales Amount])‎

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

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

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

מכירות אדם

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, מקף או רבעון.

  • אין להשתמש ביותר מ- 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

  • הזנת שורה

  • החזרת גררה

  • פסיק (,)

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

  • נקודה (.)

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

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

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

  • גרש בודד (')

  • מרכאות כפולות (")

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

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

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

  • תו קארה (^)

  • אמפרסנד (&)

  • כוכבית (*)

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

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

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

  • סמל גדול מ (<)

  • סמל קטן מ (>)

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

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

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

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

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

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

  • גרש בודד (')

השתמש בתו רווח לשיפור הקריאות בהפניה מובנית    באפשרותך להשתמש בתווי רווח כדי לשפר את קריאותה של הפניה מובנית. לדוגמה: ‎=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 Row שעשוי לגרום לתוצאות חישוב לא צפויות כאשר אתה מוסיף שורות. כדי להימנע מבעיות חישוב, הקפד להזין שורות מרובות בטבלה שלך לפני שתזין נוסחאות הפניה מובנית.

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

בעת יצירת עמודה מחושבת, נעשה שימוש נפוץ בהפניה מובנית כדי ליצור את הנוסחה. הפניה מובנית זו יכולה להיות לא מלאה או מלאה. לדוגמה, כדי ליצור את העמודה המחושבת הנקראת 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 Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

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

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

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×