כיצד להחליף שמות טווחים בהפניות תאים ב-Excel?

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

החלף שמות טווחים בהפניות תאים באמצעות קוד VBA

להחליף במהירות ובקלות שמות טווחים בהפניות תאים על ידי Kutools for Excel

לדוגמה, בגיליון זה, אני אקרא לטווח A2:A6 בשם saleprice, ול-B2 בשם discount, ואז להשתמש בשני שמות הטווח בנוסחה לחישוב המחיר החדש. ראה צילומי מסך:

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

החלף שמות טווחים בהפניות תאים באמצעות קוד VBA

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

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

2. לחצו על Alt + F11 כדי לפתוח חלון חדש של Microsoft Visual Basic עבור יישומים, ולאחר מכן לחצו על Insert > Module, והעתק והדבק את הקודים הבאים במודול:

קוד VBA: החלף שמות טווחים בהתייחסויות מוחלטות

Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
        End If
    Next
Next
End Sub

קוד VBA: החלף שמות טווחים בהתייחסויות יחסיות

Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
        End If
    Next
Next
End Sub

3. לאחר מכן לחצו על F5 כדי להריץ את הקוד, תופיע חלונית לבחירת טווח להחלפה. ראה צילום מסך:

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

להחליף במהירות ובקלות שמות טווחים בהפניות תאים על ידי Kutools for Excel

בעוד שעשייה ידנית או דרך VBA יכולה להיות עתירת זמן וחשופה לשגיאות, Kutools for Excel מספק פתרון לחיצה אחת כדי לפשט את כל התהליך. אם יש לך את Kutools for Excel, כלי Convert Name to Reference Range שלו יעזור לך להחליף שמות טווחים בהפניות תאים במהירות ובקלות.

1. אנא השתמשו בכלי זה על ידי לחיצה על Kutools > Name Tools > Convert Name to Reference Range.

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

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

🔚 מסקנה

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

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

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

מאמר קשור:

שינוי הפניית תא בנוסחאות לשמות טווחים ב-Excel