לְהִצטַיֵן

טווחים בשם ב- Excel

Named Ranges Excel

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

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



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



היסודות של טווחים בעלי שם ב- Excel

מהו טווח בשם?

טווח בשם הוא רק שם קריא לאדם עבור טווח תאים ב- Excel. לדוגמה, אם אני שם את הטווח A1: A100 ל'נתונים ', אני יכול להשתמש ב- MAX כדי לקבל את הערך המרבי בנוסחה פשוטה:

 
 = MAX (data) // max value

נקרא טווח פשוט בשם



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

 
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value

וִידֵאוֹ: כיצד ליצור טווח בשם

קל ליצור טווח בשם

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



צור טווח בשם במהירות עם תיבת שם

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

Excel יכול ליצור שמות באופן אוטומטי (ctrl + shift + F3)

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

צור שמות מפקודת הבחירה ברצועת הכלים

ניתן גם להשתמש בבקרת קיצור המקשים + shift + F3.

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

כיצד להכין רשימת צ'ק לאקסל

צור שמות מתוך בחירה עם נתונים ותוויות שנבחרו

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

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

עם שמות שנוצרו, אתה יכול להשתמש בהם בנוסחאות כאלה

 
= SUM (MN,WI,MI)

עדכן טווחים בעלי שם במנהל השמות (Control + F3)

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

טווחי שם עודכנו עם מנהל השמות

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

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

ראה את כל הטווחים שצוינו (control + F3)

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

אם ברצונך לראות פרטים נוספים, פתח את מנהל השמות (Control + F3), המפרט את כל השמות עם הפניות ומספק גם מסנן:

מנהל השמות מציג את כל השמות שנוצרו לאחרונה

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

העתק והדבק את כל הטווחים שצוינו (F3)

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

הדבק את תיבת הדו-שיח

כשתלחץ על כפתור הדבק רשימת תראה את השמות וההפניות שהודבקו בגליון העבודה:

לאחר הדבקת טווחים בשם לדף העבודה

ראה שמות ישירות בגליון העבודה

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

ברמת זום <40%, Excel יציג שמות טווחים

תודה על הטיפ הזה, פליפה!

לשמות יש חוקים

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

  1. שמות חייבים להתחיל באות, קו תחתון (_) או קו נטוי אחורי ()
  2. שמות אינם יכולים להכיל רווחים ורוב תווי הפיסוק.
  3. שמות אינם יכולים להתנגש עם הפניות לתאים - אינך יכול לתת שם לטווח 'A1' או 'Z100'.
  4. אותיות בודדות תקינות לשמות ('a', 'b', 'c' וכו '), אך האותיות' r 'ו-' c 'שמורות.
  5. שמות אינם תלויי אותיות רישיות - 'בית', 'HOME' ו- 'HoMe' זהים ל- Excel.

טווחים בשם בשם בנוסחאות

טווחים בעלי שם קלים לשימוש בנוסחאות

לדוגמא, נניח שאתה שם לתא בחוברת העבודה שלך 'מעודכן'. הרעיון הוא שאתה יכול להכניס את התאריך הנוכחי לתא (Ctrl +) ולהתייחס לתאריך במקום אחר בחוברת העבודה.

שימוש בטווח בשם בתוך נוסחת טקסט

הנוסחה ב- B8 נראית כך:

 
='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

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

טווחים בעלי שם מופיעים בעת הקלדת נוסחה

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

טווחים בעלי שם מופיעים בעת הזנת נוסחאות

טווחים בעלי שם יכולים לעבוד כמו קבועים

מכיוון שטווחים עם שם נוצרים במיקום מרכזי, אתה יכול להשתמש בהם כמו קבועים ללא התייחסות לתאים. לדוגמה, אתה יכול ליצור שמות כמו 'MPG' (מיילים לליטר) ו- 'CPG' (עלות לליטר) עם ולהקצות ערכים קבועים:

טווחים בשם יכולים לעבוד כמו קבועים, ללא התייחסות לתאים

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

שימוש בטווח בשם כמו קבוע בנוסחה

טווחים עם שם הם מוחלטים כברירת מחדל

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

 
=C5/$D

נוסחה סטנדרטית עם כתובת מוחלטת

ההתייחסות ל- D2 היא מוחלטת (נעולה), כך שניתן להעתיק את הנוסחה מבלי לשנות את D2.

אם נקרא D2 'MPG' הנוסחה הופכת להיות:

 
=C5/MPG

שימוש בטווח בשם כמו קבוע בנוסחה

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

טווחים בעלי שם יכולים להיות גם יחסית

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

לדוגמה, אתה יכול ליצור טווח כללי 'CellAbove' בשם כזה:

  1. בחר תא A2
  2. Control + F3 כדי לפתוח את מנהל השמות
  3. לחץ על הכרטיסייה אל החלק 'מתייחס' והקלד: = A1

CellAbove ישיג כעת את הערך מהתא שלמעלה בכל מקום בו הוא משמש.

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

החל טווחים בעלי שם על נוסחאות קיימות

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

תיבת הדו-שיח החל שמות

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

ניתן גם להחיל שמות באמצעות חיפוש והחלפה:

יישום שמות נע בין חיפוש והחלפה

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

היתרונות העיקריים של טווחים עם שם

טווחים בעלי שם מקלים על קריאת נוסחאות

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

 
= VLOOKUP ($H,$B:$E,2,0)

ללא טווחים נקובים נוסחאות יכולות להיות סתומות

איך לעשות סכום כולל באקסל

עם זאת, עם B3: E11 בשם 'נתונים' ו- H4 בשם 'כוכב לכת', אנו יכולים לכתוב נוסחאות כאלה:

 
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites

בטווחים עם שם, נוסחאות יכולות להיות פשוטות

במבט חטוף, אתה יכול לראות את ההבדל היחיד בנוסחאות אלה באינדקס העמודות.

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

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

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

 
= SUM (--( FREQUENCY (data,data)>0))

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

טיפ: אני ממליץ ליצור תחילה את שמות הטווחים הדרושים * בחוברת העבודה של היעד, ואז להעתיק בנוסחה כטקסט בלבד (כלומר לא להעתיק את התא שמכיל את הנוסחה בגליון עבודה אחר, פשוט להעתיק את הטקסט של הנוסחה ). זה מונע מ- Excel ליצור שמות תוך כדי טיסה ו- l מאפשר לך לשלוט באופן מלא בתהליך יצירת השמות. להעתקת טקסט נוסחא בלבד, העתק טקסט משורת הנוסחה, או העתק באמצעות יישום אחר (כלומר דפדפן, עורך טקסט וכו ').

ניתן להשתמש בטווחים בעלי שם לשם ניווט

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

טווחים בעלי שם מאפשרים ניווט פשוט

טווחים עם שם עובדים היטב עם היפר-קישורים

טווחים ששמם הופכים את הקישורים לקלים יותר. לדוגמא, אם אתה שם A1 ב- Sheet1 'בית', אתה יכול ליצור היפר-קישור במקום אחר שיקח אותך לשם.

יצירת היפר-קישור לטווח בשם

דוגמה להיפר-קישור של טווח בשם בגליון העבודה

כדי להשתמש בטווח בעל שם בתוך הפונקציה HYPERLINK, הוסף סמל פאונד לפני הטווח שצוין:

 
= HYPERLINK ('#home','take me home')

הערה: באופן מוזר, אינך יכול לקשר קישור לטבלה כמו שאתה יכול שם טווח רגיל. עם זאת, אתה יכול להגדיר שם השווה לטבלה (כלומר = טבלה 1) והיפר-קישור לזה. אם מישהו יודע דרך לקשר ישירות שולחן, פעמון פנימה!

טווחים בעלי שם לאימות נתונים

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

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

התוצאה היא תפריט נפתח בעמודה E המאפשרת רק ערכים בטווח הנקוב:

אימות נתונים עם דוגמא לטווח שמו

טווחי שם דינמיים

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

טווח בשם דינמי עם טבלה

טבלה היא הדרך הקלה ביותר ליצור טווח דינמי בשם. בחר תא כלשהו בנתונים, ואז השתמש בקיצור הדרך Control + T:

יצירת am Excel טבלה

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

הטבלאות יתרחבו אוטומטית וניתן לשנות את שמם

טווח בשם דינמי עם נוסחה

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

  • דוגמה לנוסחת טווח דינמי עם INDEX
  • דוגמה לנוסחת טווח דינמי עם OFFSET

שמות טבלאות באימות נתונים

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

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

וִידֵאוֹ: כיצד להשתמש בטווחים עם טבלאות

מחיקת טווחים עם שם

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

טווחים בעלי שם מותאמים בעת מחיקה והוספה של תאים

כאשר אתה מוחק * חלק * מטווח בשם, או אם אתה מכניס תאים / שורות / עמודות בתוך טווח בשם, הפניה לטווח תסתגל בהתאם ותישאר תקפה. עם זאת, אם תמחק את כל התאים שתוחמים טווח בשם, הטווח הנקוב יאבד את ההפניה ויציג שגיאת # REF. לדוגמא, אם אני שם את השם A1 'מבחן' ואז מוחק את העמודה A, מנהל השמות יראה 'מתייחס ל' כ:

 
=Sheet1!#REF!

מחק שמות באמצעות מנהל השמות

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

מחק שמות עם שגיאות

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

תפריט המסנן של מנהל השמות

ואז העבר + לחץ כדי לבחור את כל השמות ולמחוק.

טווחים והיקף בשם

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

מטרת ההיקף

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

היקף מקומי

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

כדי להתייחס לשם בעל היקף מקומי, תוכל להקדים את שם הגיליון לשם הטווח:

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

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

 
Sheet3!my_new_name

ראיה גלובלית

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

 
=last_update

שמות גלובליים חייבים להיות ייחודיים בתוך חוברת עבודה.

היקף מקומי

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

ניהול היקף טווח בשם

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

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

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

המחבר דייב ברונס


^