כיצד ליצור טווחים בעלי שם ב- Excel (מדריך שלב אחר שלב)

מה יש בשם?

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

במדריך זה תלמד כיצד ליצור טווחי שמות ב- Excel וכיצד להשתמש בו כדי לחסוך זמן.

טווחי שמות באקסל - מבוא

אם מישהו צריך להתקשר אליי או להתייחס אליי, הוא ישתמש בשמי (במקום לומר שזכר שוהה כך וכך עם כל כך גובה ומשקל).

ימין?

באופן דומה, ב- Excel, אתה יכול לתת שם לתא או לטווח תאים.

כעת, במקום להשתמש בהתייחסות לתא (כגון A1 או A1: A10), תוכל פשוט להשתמש בשם שהקצבת לו.

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

במערך נתונים זה, אם עליך להתייחס לטווח המכיל את התאריך, יהיה עליך להשתמש ב- A2: A11 בנוסחאות. באופן דומה, עבור נציג מכירות ומכירות, יהיה עליך להשתמש ב- B2: B11 ו- C2: C11.

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

טווחי שמות של Excel מקל להתייחס למערכות נתונים ב- Excel.

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

תוכל גם ליצור שם לתא בודד. לדוגמה, אם יש לך את אחוז עמלת המכירה בתא, תוכל לקרוא לתא זה כ'עמלה '.

היתרונות של יצירת טווחים בעלי שם ב- Excel

להלן היתרונות של שימוש בטווחים בשם ב- Excel.

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

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

לדוגמה, תוכל להשתמש = SUM (SALES) במקום = SUM (C2: C11) עבור מערך הנתונים הנ"ל.

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

• מספר מכירות בעלות יותר מ- 500: = COUNTIF (מכירות, "> 500 ″)
• סכום כל המכירות שביצע טום: = SUMIF (SalesRep, "טום", מכירות)
• העמלה שהרוויחה על ידי ג'ו (המכירות בג'ו מוכפלות באחוז העמלה):
  = SUMIF (SalesRep, "ג'ו", מכירות)*עמלה

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

אין צורך לחזור למערך הנתונים כדי לבחור תאים

יתרון משמעותי נוסף בשימוש בטווחי שמות ב- Excel הוא שאינך צריך לחזור ולבחור את טווחי התאים.

אתה יכול פשוט להקליד כמה אלפביתים מאותו טווח בשם ו- Excel יציג את הטווחים המתואמים בשם (כפי שמוצג להלן):

טווחי שמות הופכים את הנוסחאות לדינאמיות

על ידי שימוש בטווחי שמות ב- Excel, תוכל להפוך את נוסחאות Excel לדינאמיות.

לדוגמה, במקרה של עמלת מכירה, במקום להשתמש בערך 2.5%, תוכל להשתמש בטווח בשם.

כעת, אם החברה שלך תחליט מאוחר יותר להגדיל את העמלה ל -3%, תוכל פשוט לעדכן את הטווח בשם, וכל החישוב יתעדכן אוטומטית כך שישקף את העמלה החדשה.

כיצד ליצור טווחים בעלי שם ב- Excel

להלן שלוש דרכים ליצירת טווחי שמות ב- Excel:

שיטה מס '1 - שימוש בהגדרת שם

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

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

פעולה זו תיצור שם SALESREP בשם.

שיטה מס '2: שימוש בתיבת השמות

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

שיטה מס '3: שימוש באפשרות צור מתוך בחירה

זו הדרך המומלצת כשיש לך נתונים בטבלה ותרצה ליצור טווח שם לכל עמודה/שורה.

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

להלן השלבים ליצירה מהירה של טווחי שם ממערך נתונים:

• בחר את מערך הנתונים כולו (כולל הכותרות).
• עבור אל נוסחאות -> צור מבחירה (קיצור מקשים - Control + Shift + F3). הוא יפתח את תיבת הדו -שיח 'צור שמות מתוך בחירה'.
• בתיבת הדו -שיח צור שמות מבחירה, בדוק את האפשרויות שבהן יש לך את הכותרות. במקרה זה, אנו בוחרים בשורה העליונה רק מכיוון שהכותרת נמצאת בשורה העליונה. אם יש לך כותרות בשורה העליונה ובעמודה השמאלית, תוכל לבחור בשתיהן. באופן דומה, אם הנתונים שלך מסודרים כשהכותרות נמצאות בעמודה השמאלית בלבד, אתה בודק רק את האפשרות עמודה שמאלית.

פעולה זו תיצור שלושה טווחים בשם - תאריך, Sales_Rep ומכירות.

שים לב שהוא מרים שמות מהכותרות באופן אוטומטי. אם יש רווח כלשהו בין מילים, הוא מוסיף קו תחתון (מכיוון שלא תוכל להיות רווח בטווחים בשם).

אמנת שמות לטווחי שמות ב- Excel

ישנם כללי שמות מסוימים שעליך לדעת בעת יצירת טווחי שמות ב- Excel:

• התו הראשון של טווח שמות צריך להיות אות ותו קו (_), או קו נטוי לאחור (\). אם זה משהו אחר, זה יראה שגיאה. התווים הנותרים יכולים להיות אותיות, מספרים, תווים מיוחדים, נקודה או קו תחתון.
• אינך יכול להשתמש בשמות המייצגים גם הפניות לתאים ב- Excel. לדוגמה, אינך יכול להשתמש ב- AB1 מכיוון שהוא גם הפניה לתא.
• אינך יכול להשתמש ברווחים בעת יצירת טווחים בשם. לדוגמה, לא תוכל לקבל את נציג המכירות כטווח בשם. אם ברצונך לשלב שתי מילים וליצור טווח עם שם, השתמש בתווי קו תחתון, נקודה או רישיות כדי ליצור אותו. לדוגמה, תוכל לקבל Sales_Rep, SalesRep או SalesRep.
  • בעת יצירת טווחים בשם, Excel מתייחס לאותיות קטנות וקטנות באותה צורה. לדוגמה, אם תיצור SALES טווח בשם, לא תוכל ליצור טווח אחר בשם כגון 'מכירות' או 'מכירות'.
• טווח עם שם יכול להיות באורך של עד 255 תווים.

יותר מדי טווחי שמות ב- Excel? אל תדאג

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

מה אם אתה לא זוכר את שם הטווח בשם שיצרת?

אל תדאג - הנה כמה עצות שימושיות.

קבלת השמות של כל הטווחים ששמם

להלן השלבים לקבלת רשימה של כל הטווחים בשם שיצרת:

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

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

הצגת טווחי שמות תואמים

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

כיצד לערוך טווחי שמות ב- Excel

אם כבר יצרת טווח בשם, תוכל לערוך אותו באמצעות השלבים הבאים:

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

קיצורי דרך לטווח שימושי בשם (הכוח של F3)

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

• כדי לקבל רשימה של כל הטווחים הנקראים והדבקתה בפורמולה: F3
• כדי ליצור שם חדש באמצעות תיבת דו -שיח מנהל שמות: שליטה + F3
• כדי ליצור טווחי שמות מתוך מבחר: Control + Shift + F3

יצירת טווחי שמות דינמיים ב- Excel

עד כה במדריך זה יצרנו טווחי שמות סטטיים.

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

לדוגמה, אם A1: A10 נקרא בשם 'מכירות', הוא תמיד יתייחס ל- A1: A10.

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

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

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

לדוגמה, לדוגמה, אם אוסיף שתי נקודות נתוני מכירות נוספות, טווח בשם דינאמי יתייחס אוטומטית ל- A1: A12.

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

בואו נראה כיצד ליצור טווחי שמות דינמיים ב- Excel.

נניח שיש לנו את נתוני המכירות בתא A2: A11.

להלן השלבים ליצירת טווחי שמות דינמיים ב- Excel:

1. 1. עבור לכרטיסייה נוסחה ולחץ על הגדר שם.
   2. בתיבת הדו -שיח שם חדש הקלד את הדברים הבאים:
      • שם: מכירות
      • היקף: חוברת עבודה
      • הכוונה ל: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))
   3. לחץ על אישור.

בוצע!

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

כיצד פועל טווחי שמות דינמיים?

כדי להסביר כיצד זה עובד, עליך לדעת קצת יותר על פונקציית Excel INDEX.

רוב האנשים משתמשים ב- INDEX כדי להחזיר ערך מרשימה המבוססת על השורה ומספר העמודות.

אך לפונקציית INDEX יש גם צד אחר.

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

לדוגמה, הנה הנוסחה בה השתמשנו ליצירת טווח בשם דינמי:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & "") -> חלק זה של הנוסחה צפוי להחזיר ערך (שיהיה הערך העשירי מהרשימה, בהתחשב בכך שיש עשרה פריטים).

עם זאת, בעת שימוש מול הפניה (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))) הוא מחזיר את ההפניה לתא במקום לערך.

מכאן שכאן הוא חוזר = $ A $ 2: $ A $ 11

אם נוסיף לעמודת המכירות שני ערכים נוספים, הוא יחזיר = $ A $ 2: $ A $ 13

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

הערה:

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

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

אתה עשוי גם לאהוב את משאבי Excel הבאים:

• תבניות אקסל בחינם.
• הדרכת אקסל מקוונת בחינם (קורס וידאו מקוון בן 7 חלקים).
• דוגמאות שימושיות לקוד מאקרו של Excel.
• 10 דוגמאות מתקדמות ל- Excel VLOOKUP.
• יצירת רשימה נפתחת ב- Excel.
• יצירת טווח עם שם ב- Google Sheets.
• כיצד להפנות לגיליון אחר או חוברת עבודה ב- Excel