לְהִצטַיֵן

שמות טווחים ב- 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

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

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

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

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

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

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

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

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

תוכל גם להשתמש בפקד קיצור המקשים + shift + F3.

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

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

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

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

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

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

 
= SUM (MN,WI,MI)

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

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

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

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

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

הצג את כל הטווחים הנקראים (שליטה + 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')

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

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

לאחר שתיצור טווח עם שם, הוא יופיע באופן אוטומטי בנוסחאות בעת הקלדת האות הראשונה של השם. הקש על מקש Tab כדי להזין את השם כאשר יש לך התאמה וברצונך ש- 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 ets לך לשלוט באופן מלא על תהליך יצירת השמות. כדי להעתיק טקסט נוסחה בלבד, העתק טקסט משורת הנוסחאות, או העתק באמצעות יישום אחר (כלומר דפדפן, עורך טקסט וכו ').

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

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

טווחי שם מאפשרים ניווט פשוט

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

יצירת טבלת אקסל

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

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

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

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

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

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

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

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

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

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

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

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

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

 
=Sheet1!#REF!

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

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

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

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

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

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

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

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

מטרת ההיקף

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

היקף מקומי

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

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

 
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue

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

 
Sheet3!my_new_name

ראיה גלובלית

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

 
=last_update

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

היקף מקומי

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

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

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

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

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

הסופר דייב בראנס


^