כיצד ליצור טווח שמות דינמי ב-Excel?

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

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

צור טווח שמות דינמי ב-Excel עם פונקציה

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

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

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

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

1. ראשית, אגדיר שמות טווח עבור טווח זה. בחרו את הטווח A1:A6 והכניסו את השם Date לתיבת השם Name Box, לאחר מכן לחצו על מקש Enter. כדי להגדיר שם עבור הטווח B1:B6 כ-Saleprice באותה הדרך. באותו הזמן, אני יוצר נוסחה =sum(Saleprice) בתא ריק, ראו צילום מסך:

2. בחרו את הטווח ולחצו על הוספה > טבלה, ראו צילום מסך:

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

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

הערות:

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

2. בטבלה, תוכלו להכניס נתונים בין הערכים הקיימים.

צור טווח שמות דינמי ב-Excel עם פונקציה

ב-Excel 2003 או גרסאות מוקדמות יותר, השיטה הראשונה לא תהיה זמינה, אז הנה דרך נוספת בשבילכם. הOFFSET( ) הבאה יכולה לעשות זאת עבורכם, אך היא מעט מסובכת. נניח שיש לי טווח נתונים שמכיל את שמות הטווחים שהגדרתי, למשל, A1: A6 שם הטווח הוא Date, ו-B1:B6 שם הטווח הוא Saleprice, באותו הזמן, אני יוצר נוסחה עבור Saleprice. ראו צילום מסך:

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

1. עברו ללחיצה על נוסחאות > מנהל שמות, ראו צילום מסך:

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

3. בתיבת הדו-שיח עריכה שקפצה החוצה, הזינו את הנוסחה הזו =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) לתיבת הטקסט מתייחס ל, ראו צילום מסך:

4. לאחר מכן לחצו על אישור, ולאחר מכן חזרו על שלב 2 ושלב 3 כדי להעתיק את הנוסחה הזו =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) לתיבת הטקסט מתייחס ל עבור שם הטווח Saleprice.

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

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

עצה: הסבר עבור הנוסחה הזו:

• =OFFSET(התייחסות,שורות,עמודות,[גובה],[רוחב])
• 
• =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)

• התייחסות מתאימה למיקום התא ההתחלתי, בדוגמה זו Sheet1!$A$1;
• שורות מתייחסת למספר השורות שאתם עומדים לזוז כלפי מטה, ביחס לתא ההתחלתי (או כלפי מעלה, אם אתם משתמשים בערך שלילי.), בדוגמה זו, 0 מצביע שהרשימה תתחיל מהשורה הראשונה כלפי מטה.
• עמודות מתאימות למספר העמודות שאתם עומדים לזוז לימין, ביחס לתא ההתחלתי (או לשמאל, תוך שימוש בערך שלילי.), בנוסחה שלמעלה, 0 מצביע להרחבה של 0 עמודות לימין.
• [גובה] מתאים לגובה (או מספר השורות) של הטווח שמתחיל בנקודה המתוקנת. $A:$A, זה יספור את כל הפריטים שהוכנסו בעמודה A.
• [רוחב] מתאים לרוחב (או מספר העמודות) של הטווח שמתחיל בנקודה המתוקנת. בנוסחה שלמעלה, הרשימה תהיה ברוחב של עמודה אחת.

תוכלו לשנות את הארגומנטים הללו לפי הצורך שלכם.

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

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

1. הפעילו את גליון העבודה שלכם.

2. החזיקו את המקשים ALT + F11, וזה יפתח את חלון Microsoft Visual Basic for Applications.

3. לחצו על הוספה > מודול, והדביקו את הקוד הבא בחלון המודול.

קוד VBA: צור טווח שמות דינמי

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

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

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

הערות:

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

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

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

מאמר קשור:

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