העתק נוסחאות ללא שינוי קישור

בעיה

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

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

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

שיטה 1. קישורים מוחלטים

שיטה 2: השבת באופן זמני נוסחאות

כדי למנוע מנוסחאות להשתנות בעת העתקה, עליך לוודא (זמנית) ש-Excel מפסיק להתייחס אליהן כנוסחאות. ניתן לעשות זאת על ידי החלפת סימן השוויון (=) בכל תו אחר שלא נמצא בדרך כלל בנוסחאות, כגון סימן גיבוב (#) או זוג סימני אמפרסנד (&&) עבור זמן ההעתקה. לזה:

1. בחר את הטווח עם נוסחאות (בדוגמה שלנו D2:D8)
2. נְקִישָׁה Ctrl + H במקלדת או בכרטיסייה בית - מצא ובחר - החלף (בית - מצא ובחר - החלף)

   

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

   

5. חלף # on = חזרה באמצעות אותו חלון, החזרת פונקציונליות לנוסחאות.

שיטה 3: העתק דרך פנקס רשימות

שיטה זו היא הרבה יותר מהירה וקלה.

לחץ על מקש הקיצור Ctrl+Ё או כפתור הצג נוסחאות כרטיסייה נוסחה (נוסחאות - הצג נוסחאות), כדי להפעיל את מצב בדיקת הנוסחה - במקום התוצאות, התאים יציגו את הנוסחאות לפיהן הן מחושבות:

העתק את הטווח שלנו D2:D8 והדבק אותו בתקן מחברת:

כעת בחר את כל מה שהודבק (Ctrl + A), העתק אותו שוב ללוח (Ctrl + C) והדבק אותו על הגיליון במקום שאתה צריך:

נותר רק ללחוץ על הכפתור הצג נוסחאות (הצג נוסחאות)כדי להחזיר את Excel למצב רגיל.

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

שיטה 4. מאקרו

אם לעתים קרובות אתה צריך לעשות העתקה כזו של נוסחאות מבלי לשנות הפניות, אז זה הגיוני להשתמש במאקרו בשביל זה. הקש על קיצור המקלדת Alt + F11 או כפתור Visual Basic כרטיסייה מפתחים (מפתח), הכנס מודול חדש דרך התפריט הכנס - מודול  והעתק לשם את הטקסט של המאקרו הזה:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("בחר תאים עם נוסחאות להעתקה.", _ "העתק נוסחאות בדיוק", Default:=Selection.Address, Type := 8) If copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("עכשיו בחר את טווח ההדבקה." & vbCrLf & vbCrLf & _ "הטווח חייב להיות שווה בגודלו לטווח התאים המקורי " & vbCrLf & _ " להעתיק." , "העתק נוסחאות בדיוק", _ ברירת מחדל:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "טווחי העתקה והדבקה משתנים בגודלם!", vbExclamation, "Copy error " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

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

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