کپی فرمول ها بدون تغییر لینک

مشکل

فرض کنید یک جدول ساده مانند این داریم که در آن مبالغ برای هر ماه در دو شهر محاسبه می شود و سپس کل به یورو با نرخ سلول زرد J2 تبدیل می شود.

مشکل این است که اگر محدوده D2:D8 را با فرمول‌ها در جای دیگری روی برگه کپی کنید، مایکروسافت اکسل به طور خودکار پیوندهای این فرمول‌ها را تصحیح می‌کند و آنها را به مکان جدیدی منتقل می‌کند و شمارش را متوقف می‌کند:

وظیفه: محدوده را با فرمول ها کپی کنید تا فرمول ها تغییر نکنند و ثابت بمانند و نتایج محاسبات را حفظ کنید.

روش 1. پیوندهای مطلق

روش 2: فرمول ها را به طور موقت غیرفعال کنید

برای جلوگیری از تغییر فرمول ها هنگام کپی کردن، باید (به طور موقت) مطمئن شوید که اکسل آنها را به عنوان فرمول تلقی نمی کند. این کار را می توان با جایگزین کردن علامت مساوی (=) با هر کاراکتر دیگری که معمولاً در فرمول ها یافت نمی شود، مانند یک علامت هش (#) یا یک جفت علامت (&&) برای زمان کپی انجام داد. برای این:

1. محدوده را با فرمول ها انتخاب کنید (در مثال ما D2:D8)
2. کلیک کنید Ctrl + H روی صفحه کلید یا روی یک برگه صفحه اصلی – یافتن و انتخاب – جایگزینی (صفحه اصلی - یافتن و انتخاب - جایگزینی)

   

3. در کادر محاوره‌ای که ظاهر می‌شود، آنچه را که به دنبال آن هستیم و با چه چیزی جایگزین می‌کنیم، وارد کنید پارامترهای (گزینه ها) توضیح را فراموش نکنید محدوده جستجو - فرمول ها. فشار می دهیم همه را جایگزین کنید (همه را جایگزین کنید).
4. محدوده حاصل را با فرمول های غیرفعال شده در جای مناسب کپی کنید:

   

5. جایگزین کردن # on = با استفاده از همان پنجره، عملکرد را به فرمول ها برمی گرداند.

روش 3: از طریق Notepad کپی کنید

این روش بسیار سریعتر و راحت تر است.

میانبر صفحه کلید را فشار دهید Ctrl+Ё یا دکمه نمایش فرمول ها برگ فرمول (فرمول ها - نمایش فرمول ها), برای روشن کردن حالت بررسی فرمول - به جای نتایج، سلول ها فرمول هایی را نشان می دهند که با آنها محاسبه می شوند:

محدوده ما D2:D8 را کپی کرده و آن را در استاندارد قرار دهید دفتر یادداشت:

اکنون هر چیزی که چسبانده شده است (Ctrl + A) را انتخاب کنید، دوباره آن را در کلیپ بورد کپی کنید (Ctrl + C) و آن را در محل مورد نیاز روی برگه قرار دهید:

فقط فشار دادن دکمه باقی می ماند نمایش فرمول ها (نمایش فرمول ها)برای برگرداندن اکسل به حالت عادی

توجه: این روش گاهی اوقات در جداول پیچیده با سلول های ادغام شده با شکست مواجه می شود، اما در اکثر موارد به خوبی کار می کند.

روش 4. ماکرو

اگر اغلب مجبور هستید چنین کپی برداری از فرمول ها را بدون تغییر مراجع انجام دهید، پس منطقی است که از یک ماکرو برای این کار استفاده کنید. میانبر صفحه کلید را فشار دهید Alt + F11 یا دکمه ویژوال بیسیک برگ توسعه دهنده (توسعه دهنده)، یک ماژول جدید را از طریق منو وارد کنید درج - ماژول  و متن این ماکرو را در آنجا کپی کنید:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange as Range On Error Resume Next Set copyRange = Application.InputBox("سلول هایی را با فرمول ها برای کپی انتخاب کنید."، _ "کپی فرمول ها دقیقا"، پیش فرض:=Selection.Address، Type := 8) اگر copyRange چیزی نیست، از Sub Set خارج شوید pasteRange = Application.InputBox ("اکنون محدوده چسباندن را انتخاب کنید." & vbCrLf & vbCrLf & _ "محدوده باید از نظر اندازه با محدوده سلول های اصلی " & vbCrLf & _ " برابر باشد. برای کپی کردن." , "کپی فرمول ها دقیقا"، _ پیش فرض:=Selection.Address، Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count سپس MsgBox "محدوده های کپی و چسباندن در اندازه متفاوت است!"، vbExclamation، "خطای کپی" از Sub End خارج شوید اگر PasteRange چیزی نیست، سپس از Sub Else خارج شوید pasteRange.Formula = copyRange.Formula End If End Sub

برای اجرای ماکرو می توانید از دکمه استفاده کنید. ماکرو برگ توسعه دهنده (توسعه دهنده - ماکروها) یا میانبر صفحه کلید Alt + F8. پس از اجرای ماکرو، از شما می خواهد که محدوده را با فرمول های اصلی و محدوده درج انتخاب کنید و فرمول ها را به طور خودکار کپی می کند:

• مشاهده راحت فرمول ها و نتایج به طور همزمان
• چرا سبک مرجع R1C1 در فرمول های اکسل مورد نیاز است
• چگونه به سرعت همه سلول ها را با فرمول پیدا کنیم
• ابزاری برای کپی فرمول های دقیق از افزونه PLEX