در اکسل حاصل فرمول ها را نگه دارید اما داده های اولیه را حذف کنید
این اغلب برای حذف فرمول‌هایی که دیگر به آن نیاز ندارید و صرفه جویی در قدرت پردازش انجام می‌شود (زیرا فرمول‌ها هر بار که تغییری در کاربرگ ایجاد می‌کنید دوباره محاسبه می‌شوند).
در این مطلب، چند راه ساده برای حذف فرمول‌ها اما نگه داشتن داده‌ها در اکسل به شما نشان می‌دهم. می‌توانید این کار را به راحتی با استفاده از میانبرهای صفحه کلید، گزینه‌های Paste Special داخلی یا حتی VBA انجام دهید.
فهرست مطالب:
استفاده از میانبرهای صفحه کلید
سریع ترین و ساده ترین راه برای حذف فرمول‌ها و تبدیل نتیجه این فرمول‌ها به مقادیر استفاده از میانبرهای صفحه کلید است.
در این بخش، دو کلید میانبر صفحه کلید برای حذف فرمول‌ها و نگهداری داده‌ها در اکسل را مورد بحث قرار می‌دهیم.
[میانبر جدید] Ctrl+ Shift + V
اگر از Excel برای Microsoft 365 یا Excel برای Web استفاده می‌کنید، می‌توانید از این میانبر جدید استفاده کنید.
این میانبر در اکتبر 2022 منتشر شد و فقط برای نسخه‌ها/ساخت‌های اکسل برای مایکروسافت 365 پس از اکتبر 2022 در دسترس است. همه کاربران اکسل مایکروسافت 365 به آن دسترسی خواهند داشت، اما نسخه‌های قدیمی‌تر اکسل این امکان را ندارند.
در زیر، ما یک مجموعه داده نمونه داریم که در آن فرمول محاسبه کمیسیون هر نماینده فروش در ستون D را دارم و می‌خواهیم فرمول‌ها را حذف کنم اما نتایج حاصل را حفظ کنم.
در اینجا مراحل انجام این کار آمده است:
- محدوده سلولی حاوی فرمول‌هایی را که می‌خواهید حذف کنید انتخاب کنید. در این مورد، D2:D11 را انتخاب می‌کنم
- برای کپی کردن سلول‌های انتخاب شده، Control + C را فشار دهید.
- همان محدوده را انتخاب کنید و سپس از میانبر زیر استفاده کنید
Ctrl + Shift + V
باید کلیدهای Control و Shift را نگه دارید و سپس کلید V را فشار دهید.
منظور از میانبر صفحه کلید بالا، قرار دادن داده‌های کپی شده به عنوان مقادیر متن ساده در اکسل است.
همچنین می‌توانید از این برای کپی داده‌ها از هر منبعی مانند یک صفحه وب یا سایر برنامه‌های آفیس مانند MS Word یا PowerPoint استفاده کنید و آن را به‌عنوان مقادیر بدون فرمت یا فرمول در Excel جای‌گذاری کنید.
توجه: طبق یادداشت‌های انتشار مایکروسافت، این عملکرد برای اکسل برای مایکروسافت 365 در دسترس است و همچنین برای اکسل برای مک نیز منتشر خواهد شد.
میانبر برای همه نسخه‌ها
اگر از نسخه قدیمی اکسل استفاده می‌کنید و میانبر Control + Shift + V برای شما کار نمی‌کند، در زیر میانبری وجود دارد که در هر نسخه اکسل کار می‌کند.
ALT + E + S + V + Enter
برای استفاده از این میانبر:
- محدوده سلولی را با فرمول‌هایی که می‌خواهید حذف کنید انتخاب کنید.
- برای کپی کردن سلول ها، Control + C را فشار دهید.
- در حالی که محدوده هنوز انتخاب شده است، ALT + E + S (یکی پس از دیگری) را فشار دهید تا کادر گفتگوی Paste Special باز شود.
- برای انتخاب گزینه Values کلید V را فشار دهید.
- برای تایید و پیست کردن (Paste) مقادیر، کلید Enter را فشار دهید و به طور موثر فرمول‌ها را حذف کنید.
این میانبر از گزینه پیست کردن (Paste) برای چسباندن سلول‌های کپی شده به عنوان مقادیر استفاده می‌کند.
بنابراین، اگر سلول‌هایی که انتخاب کرده‌اید دارای فرمول هستند، این میانبر فرمول را حذف می‌کند اما داده‌ها را حفظ می‌کند.
با استفاده از هر یک از این میانبرهای صفحه کلید، می‌توانید فرمول‌ها را در اکسل حذف کنید و در عین حال مقادیر/داده‌ها را حفظ کنید.
استفاده از گزینه Paste Special
اگر طرفدار یادگیری میانبرهای صفحه کلید جدید نیستید یا به میانبر Control + Shift + V دسترسی ندارید، هنوز هم راه‌های ساده‌ای برای خلاص شدن از شر فرمول‌ها و حفظ داده‌ها وجود دارد.
منوی Paste Special
در زیر، من یک مجموعه داده دارم که در آن یک فرمول در ستون D دارم و می‌خواهم فرمول را حذف کنم در حالی که نتیجه فرمول را به عنوان مقادیر نگه می‌دارم.
در اینجا مراحل انجام این کار با استفاده از کادر گفتگوی Paste Special آمده است:
- محدوده‌ای را انتخاب کنید که دارای فرمول‌هایی است که می‌خواهید حذف کنید.
- محدوده را کپی کنید می‌توانید این کار را با کلیک راست روی محدوده و سپس انتخاب گزینه Copy انجام دهید یا از میانبر Control + C استفاده کنید.
- با همان محدوده انتخاب شده، کلیک راست کرده و سپس روی گزینه Paste Special کلیک کنید. با این کار کادر محاوره‌ای Paste Special باز می‌شود.
- گزینه Values را انتخاب کنید.
- Ok را کلیک کنید
مراحل بالا سلول‌های کپی شده را در محدوده انتخاب شده جای‌گذاری می‌کند، اما از آنجایی که ما گزینه‌های Value را در مرحله 4 انتخاب کرده‌ایم، فقط مقادیر را جای‌گذاری می‌کند.
از آنجایی که سلول‌هایی که انتخاب کردیم در ابتدا دارای فرمول بودند و سپس مقادیری را روی آنها چسباندیم(کپی کردیم)، در حالی که مقادیر باقی می‌ماندند، فرمول‌ها حذف می‌شدند (به عنوان مقادیر ثابت).
paste as values در روبان
راه دیگر برای حذف فرمول‌ها و فقط حفظ مقادیر، استفاده از گزینه paste as values در منوی کلیک راست است.
بیایید از مجموعه داده زیر استفاده کنیم، جایی که من یک فرمول در ستون D دارم و می‌خواهم فرمول را حذف کنم و مقادیر را حفظ کنم.
بیشتر بخوانید: چگونه منوی روبان را در اکسل بزرک یا کوچک کنیم؟
در اینجا مراحل انجام این کار آمده است:
- محدوده‌ای را انتخاب کنید که دارای فرمول‌هایی است که می‌خواهید حذف کنید.
- محدوده را کپی کنید می‌توانید این کار را با کلیک راست روی محدوده و سپس انتخاب گزینه Copy انجام دهید یا از میانبر Control + C استفاده کنید.
- حالا روی تب Home کلیک کنید.
- در گروه Clipboard بر روی آیکون فلش کوچک زیر گزینه Paste کلیک کنید.
- گزینه Paste Values را انتخاب کنید.
مراحل بالا داده‌های کپی شده را به‌عنوان مقادیر در محدوده انتخاب‌شده سلول‌ها جای‌گذاری می‌کند که در مثال ما به این معنی است که فرمول‌ها با مقادیر ثابت حذف می‌شوند.
همچنین بخوانید: نحوه قالب بندی شرطی در اکسل
ترفند حرکت مکان نما
این یک ترفند واقعا جالب است که بسیاری از مردم آن را نمی‌دانند و شما را شبیه یک شعبده باز اکسل می‌کند.
در حالی که این روش هیچ مزیتی نسبت به روش‌های قبلی ندارد، اما شما را یک متخصص جلوه می‌دهد.
در زیر مجموعه داده‌ای وجود دارد که در آن فرمول‌هایی در ستون D دارم که می‌خواهم آنها را حذف کنم و فقط نتیجه فرمول‌ها را حفظ کنم.
در اینجا روش‌های استفاده از این ترفند حرکت مکان نما آورده شده است:
- سلول‌هایی را انتخاب کنید که فرمولی دارند که می‌خواهید حذف کنید
- نشانگر ماوس خود را در لبه سمت راست انتخاب قرار دهید. متوجه خواهید شد که مکان نما شما به یک نماد چهار فلش تبدیل می‌شود (مانند شکل زیر)
- کلید سمت چپ ماوس را نگه دارید (آن را نگه دارید)، سپس مکان نما را کمی به سمت راست حرکت دهید. می‌بینید که طرح سبز رنگ در ستون مجاور سمت راست ظاهر می‌شود.
- کلید سمت چپ ماوس را نگه دارید و مکان نما را به جایی که قبلاً داده‌ها را دارید برگردانید.
- کلید ماوس را رها کنید. به محض انجام این کار، منویی با چند گزینه ظاهر می‌شود.
- گزینه ‘Copy Here as Values Only’ را انتخاب کنید.
به محض کلیک بر روی گزینه “Copy Here as Values Only” در مرحله 6، فرمول حذف می‌شود و شما فقط داده‌های موجود در سلول را به عنوان مقادیر ثابت خواهید داشت.
توجه: من شخصاً این روش را سریعتر از استفاده از گزینه‌های Paste Special داخلی می‌بینم
با استفاده از VBA
اگر حذف فرمول‌ها و تبدیل آنها به مقدار حاصل از آنها کاری است که باید اغلب انجام دهید، یا باید آن را برای چندین کاربرگ در یک کتاب کار یا چندین کتاب کار انجام دهید، می‌توانید از VBA استفاده کنید.
همچنین بخوانید: نحوه ایجاد ماکرو در اکسل
در زیر برخی از کدهای VBA وجود دارد که می‌توانید در سناریوهای مختلف استفاده کنید:
حذف فرمول‌ها از یک محدوده خاص
Sub RemoveFormulasInRange()
Dim rng As Range
Set rng = ActiveSheet.Range("D2:D11")
rng.Value = rng.Value
End Subدر کد بالا محدوده را D2:D11 مشخص کرده ام. شما می‌توانید محدوده را با توجه به نیاز خود تغییر دهید.
حذف فرمول‌ها از کل کاربرگ
Sub RemoveFormulasButKeepData() Dim ws As Worksheet Set ws = ActiveSheet ws.UsedRange.Value = ws.UsedRange.Value End Sub
کد بالا فرمول‌ها را از تمام سلول‌های «محدوده استفاده‌شده» کاربرگ فعال حذف می‌کند و مقادیر محاسبه‌شده‌شان را جایگزین آنها می‌کند.
حذف فرمول‌ها از تمام برگه‌های کتاب کار
Sub RemoveFormulasButKeepData()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Subکد بالا از یک حلقه For Every برای عبور از هر کاربرگ در کتاب کار فعال استفاده می‌کند و سپس محدوده استفاده شده را با مقادیر موجود در سلول‌ها جایگزین می‌کند. این به حذف تمام فرمول‌ها و در عین حال دست نخورده نگه داشتن داده‌ها منجر می‌شود.
کجا این کد VBA را قرار دهیم
مراحل زیر را دنبال کنید تا ویرایشگر VB را باز کنید، کد VBA را قرار داده و آن را اجرا کنید:
- به تب Developer در Ribbon بروید. اگر تب Developer را نمی‌بینید، باید آن را فعال کنید. در تب Developer، روی نماد “Visual Basic” کلیک کنید. یا می‌توانید ALT + F11 را فشار دهید تا ویرایشگر VBA باز شود.
- در ویرایشگر VBA بر روی گزینه Insert در منو کلیک کنید و سپس بر روی Module کلیک کنید. این یک ماژول جدید را به پروژه VBA شما وارد می‌کند.
- کد VBA را که می‌خواهید در پنجره کد ماژول جدیدی که در ویرایشگر ظاهر می‌شود، کپی کنید.
- ماژول VBA را با کلیک کردن روی نماد ذخیره یا فشار دادن Ctrl + S ذخیره کنید.
- ویرایشگر VBA را ببندید
برای اجرای کد ماکرو مراحل زیر را دنبال کنید:
- کلید ALT را نگه داشته و کلید F8 را فشار دهید. با این کار کادر محاوره‌ای ماکرو باز می‌شود.
- ماکرو مورد نظر برای اجرا را انتخاب کرده و روی دکمه Run کلیک کنید.
هشدار : به یاد داشته باشید که تغییرات انجام شده توسط یک کد VBA غیر قابل برگشت هستند. بنابراین مطمئن شوید که یک نسخه پشتیبان از اطلاعات خود داشته باشید فقط در صورتی که مشکلی با کد VBA پیش بیاید یا بعداً به داده‌های اصلی نیاز داشته باشید.
اگر می‌خواهید دوباره از کد VBA استفاده کنید، باید فایل خود را به عنوان یک فایل اکسل فعال شده ماکرو (با پسوند .XLSM) ذخیره کنید.
نکته حرفه‌ای : اگر می‌خواهید از هر یک از این کدهای VBA در همه کتاب‌های کار اکسل روی سیستم خود استفاده کنید، باید کد را در کتاب کار شخصی ماکرو ذخیره کنید. هنگامی که کد در کتاب کار شخصی ماکرو قرار گرفت، می‌توان آن را با استفاده از مراحلی که در بالا ذکر کردم، در هر کتاب کار استفاده کرد.
بنابراین، این چند روش است که می‌توانید برای حذف فرمول‌ها استفاده کنید اما داده‌ها را در اکسل نگه دارید. اگر این کاری است که باید اغلب انجام دهید، بهتر است میانبرهای صفحه کلید را برای انجام این کار یاد بگیرید.
در غیر این صورت، می‌توانید از گزینه Paste Special داخلی برای خلاص شدن از شر فرمول و جایگزینی آن با مقدار آن استفاده کنید.
امیدوارم این آموزش اکسل برای شما مفید بوده باشد.