כיצד ליצור הפניות מובנות ב- Excel?
הפניות מובנות מתחילות בטבלאות אקסל. ברגע שהטבלאות שנוצרו ב- excel זה יוצר עבורך אוטומטית הפניות מובנות.
עכשיו הסתכל בתמונה למטה.
- שלב 1: נתתי קישור לתא B3, במקום להציג את הקישור כ- B2 הוא מוצג כטבלה 1 [@ מכירות]. כאן טבלה 1 היא שם הטבלה ו- @ מכירות היא העמודה אליה אנו מתייחסים. כל התאים בעמודה זו מופנים בשם טבלה ואחריהם שם כותרת העמודה.
- שלב 2: כעת אשנה את שם הטבלה ל- Data_Table ואשנה את כותרת העמודה לסכום .
- שלב 3: על מנת לשנות את שם הטבלה הצב סמן בתוך הטבלה> עבור אל עיצוב> שם הטבלה.
- שלב 4: הזכר את שם הטבלה כ- Data_Table.
- שלב 5: עכשיו שנה תן התייחסות לתא B3.
אז הבנו שלפניה מובנית יש שני חלקים שם טבלה ושם עמודות.
דוגמאות
אתה יכול להוריד תבנית Excel זו להפניה מובנית כאן - תבנית Excel להפניה מובניתדוגמה מס '1
באמצעות הפניות מובנות תוכלו להפוך את הנוסחה שלכם לדינמית. בניגוד להפניות תאים רגילות, הוא מאפשר לנוסחה להיות פעילה במקרה של הוספה ומחיקה בטווח הנתונים.
תן לי להחיל את נוסחת ה- SUM גם לטווח הרגיל וגם לטבלת ה- Excel.
נוסחת SUM לטווח רגיל.
נוסחת SUM לטבלת Excel.
תן לי להוסיף כמה שורות לנתונים של טבלה רגילה וטבלה. הוספתי 2 פריטי שורה לנתונים, כעת ראו את ההבדל.
הפניה מובנית בטבלת ה- Excel מציגה את הערך המעודכן אך טווח הנתונים הרגיל אינו מציג את הערכים המעודכנים אלא אם כן מבצעים שינויים מסוימים בנוסחה באופן ידני.
דוגמה מס '2
עכשיו, הסתכלו בדוגמה נוספת. יש לי מידע על שם, כמות ומחיר. באמצעות מידע זה אני צריך להגיע לערך המכירה.
על מנת לקבל את ערך המכירה, הנוסחה היא כמות * מחיר . בואו נשתמש בנוסחה זו בטבלה.
פורמולה אומרת [@QTY] * [@PRICE]. זה מובן יותר מההתייחסות הרגילה של B2 * C2. אנו לא מקבלים את שם הטבלה אם אנו מכניסים את הנוסחה בתוך הטבלה.
בעיות עם הפניות מובנות של Excel
בעת שימוש בהפניות מובנות אנו מתמודדים עם כמה בעיות המפורטות להלן.
בעיה מס '1
להפניות מובנות יש גם בעיות משלהן. כולנו מכירים את יישום נוסחת האקסל והעתקה או גרירה לתאים האחרים שנותרו. זה לא אותו תהליך ברפרנסים מובנים, זה עובד קצת אחרת.
כעת, בדוק את הדוגמה הבאה. יישמתי נוסחת SUM ב- Excel לטווח הנורמלי.
אם אני רוצה לסכם מחיר וערך מכירה פשוט אעתיק ואדביק או גרור את הנוסחה הנוכחית לשני התאים האחרים והיא תיתן לי את הערך SUM של מחיר ומחיר מכירה.
כעת החל את אותה הנוסחה עבור טבלת האקסל בעמודה כמות.
עכשיו קיבלנו את סכום העמודה כמות. כמו טווח רגיל, הנוסחה מעתיקה את הנוסחה הנוכחית ומדביקה אותה בעמודה מחיר כדי לקבל את סך המחיר.
אלוהים אדירים!!! הוא אינו מציג את סך העמודה מחיר אלא עדיין מראה את סך העמודה כמות. לכן, איננו יכולים להעתיק ולהדביק נוסחה זו לתא הסמוך או לכל תא אחר כדי להתייחס לעמודה או לשורה היחסית.
גרור את הנוסחה כדי לשנות את הפניה
כעת אנו יודעים שמגבלותה איננו יכולים לבצע את עבודת הדבקת ההעתקה יותר באמצעות הפניות מובנות. אז איך נתגבר על המגבלה הזו?
הפיתרון הוא פשוט מאוד אנחנו רק צריכים לגרור את הנוסחה במקום להעתיק. בחר בתא הנוסחה והשתמש בידית המילוי וגרור אותה לשני התאים הנותרים כדי לשנות את הפניה לעמודה למחיר ולמכירה.
כעת עדכנו נוסחאות לקבלת הסכומים בהתאמה.
בעיה מס '2
ראינו בעיה אחת עם הפניות המבנה ומצאנו גם את הפיתרון, אך יש לנו בעיה אחת נוספת כאן, איננו יכולים לבצע את השיחה כהפניה מוחלטת אם אנו גוררים את הנוסחה לתאים אחרים.
בואו נסתכל על הדוגמה הבאה. יש לי טבלת מכירות עם מספר ערכים ואני רוצה לאחד את הנתונים באמצעות פונקציית SUMIF ב- Excel.
כעת אשתמש בפונקציית SUMIF כדי לקבל את ערכי המכירות המאוחדים עבור כל מוצר.
יישמתי את הנוסחה לחודש ינואר, מכיוון שהיא הפניה מובנית איננו יכולים להעתיק ולהדביק את הנוסחה בשתי העמודות הנותרות, היא לא תשנה את ההתייחסות לפברואר ומארס, אז אני גרור את הנוסחה.
אה !! לא קיבלתי שום ערכים בעמודה פברואר ומרץ. מה תהיה הבעיה ??? הסתכל מקרוב על הנוסחה.
גררנו את הנוסחה מחודש ינואר. בארגומנט הראשון של הפונקציה SUMIF הוא טווח קריטריונים מכירה_טבלה [מוצר] מכיוון שגררנו את הנוסחה שיש בה שינויים לטבלת מכירות _טבלה [ינואר].
אז איך נתמודד עם זה ?? עלינו להפוך את הטיעון הראשון כלומר עמודת המוצר כמוחלטת ועמודות אחרות כהתייחסות יחסית. שלא כמו התייחסות רגילה, אין לנו את המותרות להשתמש במקש F4 כדי לשנות את סוג ההפניה.
הפתרון הוא שעלינו לשכפל את עמודת ההפניה כפי שמוצג בתמונה למטה.
כעת אנו יכולים לגרור את הנוסחה לשתי עמודות אחרות. טווח הקריטריונים יהיה קבוע והפניות עמודות אחרות ישתנו בהתאם.
טיפ מקצוען: על מנת להפוך את ROW כהפניה מוחלטת עלינו להזין ROW כפול אך עלינו לשים את סמל @ לפני שם ROW.
= טבלת מכירות [@ [מוצר]: [מוצר]]
כיצד לבטל הפניה מובנית ב- Excel?
אם אינך חובב הפניות מובנות תוכל לכבות על ידי ביצוע השלבים הבאים.
- שלב 1: עבור אל FILE> אפשרויות.
- שלב 2: נוסחאות> בטל את הסימון השתמש בשמות טבלאות בנוסחאות.
דברים שיש לזכור
- על מנת לבצע את ההתייחסות המוחלטת בהתייחסות מובנית, עלינו להכפיל את שם העמודה.
- איננו יכולים להעתיק את הנוסחה של הפניה מובנית במקום זאת עלינו לגרור את הנוסחה.
- איננו יכולים לראות לאיזה תא אנו מתייחסים בהפניות מובנות.
- אם אינך מעוניין בהפניות מובנות תוכל לכבות אותן.