کار با فایل های سنگین اکسل و افزایش سرعت
کاهش حجم و افزایش سرعت فایل اکسل
حتما تا حالا با این موضوع برخورد کردید که فایل اکسلی که باهاش کار میکنید خیلی سنگین شده و کار کردن باهاش سخت. فایل های سنگین به سختی ایمیل میشن، موقعی که میخوایم این فایل ها رو باز کنیم زمان طولانی باید صبر کنیم تا فایل باز بشه. حتی با یک تغییر کوچک در فایل، باید مدت زیادی رو صبر کنیم تا محاسبات فایل بروزرسانی بشه و مشکلاتی از این قبیل. در این مقاله سعی داریم راه های حل این مشکل رو برای افزایش سرعت فایل اکسل ارائه بدیم. راه حل ها در سه دسته اصلی تقسیم میشن:
- مستقیما حجم فایل رو کاهش بدیم
- فضاهای خالی در شیت حذف کنید.
- شیتهای غیر لازم و Hide شدن رو حذف کنید.
- فایل رو با پسوند xlsb ذخیره کنید.
- فرمت هایی که روی سلول های خالی اعمال شدند رو حذف کنید.
- فرمت های شرطی Conditional Formatting رو چک کنید.
- زمان محاسبات رو کاهش بدیم
- حالت اتوماتیک محاسبات اکسل رو غیر فعال کنید.
- از Watch Window استفاده کنید تا همیشه سلول های مشخصی رو ببینید.
- فرمول های نوشته شده رو بهینه سازی کنیم
- از توابع Volatile استفاده نکنید.
- از Pivot Table و Table استفاده کنید.
- از ارجاع کل ردیف ها و ستون ها در فرمول ها اجتناب کنید.
- از محاسبات تکراری اجتناب کنید.
- داده ها رو موقع استفاده از توابع جستجو مرتب کنید.
دسته اول: کاهش حجم فایل جهت افزایش سرعت
اگر میخواید به داده های داخل فایل دست نزنید و به لحاظ محتوایی فایل تغییر نکنه، اول از این روش ها استفاده کنید. راه هایی که برای افزایش سرعت فایل از طریق کاهش حجم فایل وجود داره عبارتند از: فضاهای خالی در شیت حذف کنید. این موضوع یکی از مهم ترین علل افزایش حجم هست و حلش هم خیلی راحته. اکسل مفهومی داره به عنوان “رنج استفاده شده”. هر چقدر این رنج بزرگتر باشه حجم فایل هم بیشتر. حالا ببینیم مفهوم این عبارت چی هست؟ برای فایل های جدید که باز میکنیم، این محدوده استفاده شده، معادل سلول A1 هست. اما به محض اینکه شروع میکنیم به کار کردن، این محدوده گسترش پیدا میکنه و دورترین سلول (هم دورترین ستون و هم دورترین ردیف) از A1 رو که چیزی داخلش نوشته شده یا فرمتی داده شده رو در نظر میگیره و از A1 تا اونجا محدوده استفاده شده در نظر گرفته میشه.
برای اینکه ببینیم محدوده استفاده شده تا کجاست، کافیه کلید ترکیبی Ctrl+End رو بزنیم. به شکل ۱ نگاه کنید. طبق شکل انتظار داریم محدوده استفاده شده تا E9 (دورترین ستون E و دورترین ردیف ۹ هست) در نظر گرفته بشه. اما چون قبلا در سلول J18 مطلبی نوشته شده و پاک شده، با زدن کلید Ctrl+End سلول J18 انتخاب میشه و محدوده استفاده شده A1:J18 خواهد بود. 
شکل ۱- فایل های سنگین و افزایش سرعت فایل اکسل -محدوده استفاده شده
چطور این مشکل رو کنیم؟ باید فضای استفاده شده رو حذف کنیم. این کار رو از طریق انتخاب سطرها و ستون های مورد نظر و Delete کردن اونها انجام میدیم. شمل ۱ رو در نظر بگیرید. برای اینکه محدوده استفاده شده رو محدود کنیم، روی ستون F کلیک کرده و کلید ترکیبی Ctrl+Shift+Arrow Right رو میزنیم. بعد از انتخاب همه ستون ها، کلیک راست کرده و Delete رو میزنیم. برای ردیف هم به همین شکل عمل میکنیم. یعنی ردیف شماره ۱۰ رو انتخاب کرده و کلید ترکیبی Ctrl+Shift+Arrow Down رو شده، کلیک راست میکنیم و Delete. شیتهای غیر لازم و Hide شدن رو حذف کنید. ممکنه فایلی که به دستتون رسیده تعدادی شیت Hide شده داشته باشه. اول همه شیت ها رو Unhide کنید. بعد بررسی کنید که آیا همه این شیت ها لازم هستن یا نه. هر کدوم لازم نبود رو حذف کنید. 
شکل ۲- فایل های سنگین و افزایش سرعت فایل اکسل -حذف شیت
فایل رو با پسوند .xlsb ذخیره کنید. یکی از بهترین روش ها برای کاهش حجم فایل و افزایش سرعت فایل اکسل ، ذخیره کردن با پسوند Xlsb هست. اگر فایلی دارید که حجم زیادی داده و فرمول نویسی داره، با این فرمت ذخیره کنید. با این کار همه فرمول ها و کدهای VBA همچنان کار میکنن و نگرانی بابت کارکرد فایل نخواهید داشت.البته دقت داشته باشید که امکان بازیابی فایل در این حالت از بین میره. 
شکل ۳- فایل های سنگین و افزایش سرعت فایل اکسل -ذخیره با پسوند Binary
حجم اکثر فایل ها حدودا ۵۰% کاهش پیدا میکنه. اما این میزان بیشتر بستگی به محتوای فایل و نوع داده های موجود در اون داره. بعضی وقت ها حجم فایل به ۲۰% حجم فایل اصلی هم کاهش پیدا میکنه! فرمت هایی که روی سلول های خالی اعمال شدند رو حذف کنید. اگر داده های خام دارید و فرمت دهی کردید، حجم فایل شما بالا خواهد رفت. اگر فایل شما بصورتی هست که این داده ها صرفا برای انجام محاسبات استفاده میشن و قرار نیست مستقیما نمایش داده بشن، سعی کنید هیچ فرمتی نداشته باشه. فرمت رو فقط در شیت گزارشگیری و نتیجه اعمال کنید. به هیچ وجه روی بانک اطلاعاتی این کار رو انجام ندید. این موضوع ممکنه با مخالفت برخی مواجه بشه، چرا که برخی عادت دارند که داده های دیتابیس رو با رنگ و فرمت های مختلف از هم تفکیک کنن. در حالیکه باید سعی کنیم داده های دیتابیس در خام ترین حالت ممکن باشند. پس فرمت های از قبیل رنگ، کادر (Border) و هر چیزی که صرفا بخاطر زیبایی اعمال شده رو حذف کنید. دقت کنید که فرمت های تاریخ و واحد پول و … حذف نکنید. چون باعث ناخوانی داده ها میشه. فرمت های شرطی Conditional Formatting رو چک کنید. فرمت دهی شرطی یا Conditional Formatting یکی از راه های جذاب بصری سازی داده هاست. اما این ابزار باعث افزایش حجم فایل میشه. پس باید دقت داشته باشید که از این ابزار فقط به اندازه نیاز استفاده کنید. برخی کاربرها عادت دارند که فرمت رو برای کل شیت، یک ستون یا ردیف کامل اعمال کنند. خب این مسئله باعث افزایش حجم فایل خواهد شد. از قسمت Manage Rule Conditional Formatting> همه شرط ها رو چک کنید و مطمئن بشید که رنج ها فقط تا جایی که نیاز هست اعمال شده اند. اگر رنجی بیش از محدوده مورد نیاز تعیین شده بود، ویرایش کنید و محدود کنید به محدوده مورد نیاز.
دسته دوم: زمان محاسبات رو کاهش بدیم
حالت اتوماتیک محاسبات اکسل رو غیر فعال کنید. 
اگر محاسبه فرمول ها زمانبر هست، پس باید محاسبات رو از حالت خودکار خارج کنیم. بعبارتی از حالت Automatic به حالت Manual تغییر بدیم. با این کار به اکسل میگیم که “لازم نیست با هر بار تغییر، کل فایل رو محاسبه کنی، فقط زمانهایی که میخوایم این محاسبات رو انجام بده”. این کار از مسیر نشان داده شده در شکل ۴ انجام میشه: 
شکل ۴- فایل های سنگین و افزایش سرعت فایل اکسل – غیراتومات ساختن محاسبات
زمانی که محاسبات روی حالت دستی هست، با زدن کلید F9 محاسبات کل فایل انجام میشه و کلید Shift+F9 فقط شیت فعلی. به این نکته توجه داشته باشید که وقتی محاسبات روی حالت دستی هست، با اعمال تغییرات در صفحه، نتیجه فرمول ها آپدیت نمیشه. چون ما بهش گفتیم تا زمانی که F9 نزدیم، هیچ محاسبه ای انجام نده. پس اگر فایلی دیدید که با تغییر داده ها، نتایج تغییر نمیکنه، باید بدونید که محاسبات روی حالت دستی تنظیم شده.
از Watch Window استفاده کنید تا همیشه سلول های مشخصی رو ببینید. استفاده از Watch Window به کاهش حجم فایل کمکی نمیکنه. اما به افزایش سرعت در استفاده از فایل کمک میکنه. توضیحات کامل این ابزار در لینک زیر ارائه شده.
دسته سوم: فرمول های نوشته شده رو بهینه سازی کنیم
از توابع Volatile استفاده نکنید. 
یک لیست هشت تایی از توابع وجود داره که اصطلاحا Volatile نامیده میشن. در هر بار محاسبه، هر سلولی که حاوی این توابع باشه و همه سلولهای وابسته به این سلولها دوباره محاسبه میشن، که این مسئله باعث افزایش مدت محاسبه خواهد شد. این هفت تابع عبارتند از:
- Now
- Today
- Rand/Randbetween
- Offset
- Indirect
- Info (بسته به آرگومانهای استفاده شده)
- Cell (بسته به آرگومانهای استفاده شده)
- Sumif (بسته به آرگومانهای استفاده شده)
هر چقدر کمتر از این توابع استفاده کنید، به افزایش سرعت محاسبات فایل کمک کردید. از Pivottable و Table استفاده کنید. سعی کنید بجای استفاده از تعداد زیادی فرمول های پیچیده و طولانی، تا جای ممکن از Pivot Table برای تهیه گزارش استفاده کنید. همچنین استفاده از Table خیلی اثرگذار هست و کمک میکنه به کاهش حجم فایل. فرمول های نوشته شده در یک Table نسبت به فرمول های نوشته شده در رنج معمولی، به مراتب کمتر باعث حجم فایل میشن. از ارجاع کل ردیف ها و ستون ها در فرمول ها اجتناب کنید. دقت کنید زمان فرمول نویسی از انتخاب کل ستون ها و ردیف ها بجای تخصیص چند صد ردیف، اجتناب کنید. چرا که اکسل فقط محدوده تخصیص داده شده محاسبه میکنه نه کل ۱,۰۴۸,۵۷۶ ردیف رو! 
شکل ۵-فایل های سنگین و افزایش سرعت فایل اکسل -تخصیص درست محدوده ها در فرمول
از محاسبات تکراری اجتناب کنید. از تکرار محاسبات اجتناب کنید. اجازه بدید این موضوع رو با مثال توضیح بدم. فرض کنید فرمولی دارید بصورت “=$D$۵+$E$۵″، و این فرمول رو در صد سلول استفاده کردید. یعنی یعنی تعداد کل ارجاعات به سلول میشه دویست تا. اما اگه در یک سلول مثل A5 این فرمول رو بنویسیم و اون صد سلول رو به سلول A5 وصل کنیم، تعداد ارجاعات به سلول ها نصف میشه. در واقع =$D$۵+$E$۵ بجای صد بار، یکبار محاسبه میشه. پس اگه حجم زیادی فرمول با محاسبات یکسان دارید، مطابق مثال ارائه شده اونها رو به چند قسمت بشکنید. داده ها رو موقع استفاده از توابع جستجو مرتب کنید. وقتی داده ها مرتب باشن، زودتر و راحت تر پیدا میشن! این موضوع در خصوص توابعی مثل VLOOKUP, INDEX, MATCH, SUMIF و یا هر تابعی در اکسل که به دنبال داده بخصوصی میگرده صدق میکنه. پس بهتره زمانی که از این توابع استفاده میکنیم، داده هامون مرتب (Sort) باشن تا روند جستجو سریع تر باشه. شما با راه حل های زیادی آشنا شدید که میتونید در مورد فایل های سنگین، بسته به شرایط، از هر کدوم از این روش ها استفاده کنید. با این روش ها دیگه فایل هاتون دیر باز نمیشن و نیازی نیست زمان زیادی رو برای انجام محاسبات اتلاف کنید. آیا راه حل های ارائه شده برای افزایش سرعت فایل اکسل شما مفید بودن؟ یا هنوز با فایل های سنگین مشکل دارید؟ مسائل در رابطه با این موضوع رو در قالب کامنت در ادامه همین پست با ما در میون بذارید. راه حل های مایکروسافت برای بهبود سرعت محاسبات در اکسل رو هم نگاه کنید.
با این حلقه های تو در تو و اون حجم از فایل قطعا هنگ میکنه از کدهای فیلتر ستونها استفاده نمایید و سپس محدوده مربوطه را با کد در شیت دوم کپی نمایید سپس می توانید شیت قبلی راحذف نمایید
سلام.
ببخشید اکسل من به دلیل به کار بردن فرمول های بسیار سنگین شده است. با توجه به نوشته های شما به این نتیجه رسیدم که بهترین کار برای من این است که حالت محاسبات را از حالت خودکار (automatic)به دستی تغییر دهم. اما مشکل این جاست که اصلا اکسل در زمان باز شدن کار نمی کند و به من امکان تغییر نحوه محاسبات را نمی دهد. آیا راه حلی وجود دارد که قبل از باز کردن اکسل، حالت محاسبات خودکار آن را غیرفعال کنم؟
درود
قبلش کهممکن نیست
binary ذخیره کنید
شاید باز بشه
یا open & repair
باسلام واحترام
ضمن تشکرازآموزش های ارائه شده:
لطفا درخصوص اجتناب ازمحاسبات تکراری مجددتوضیح دهید.بنده متوجه توضیح فوق نشدم.شایدمرتب نبودن متن فرمول نوشته شده علت آن باشد.
ممنونم
درود
در این مقاله که کامنت گذاشتید، فمرولی وجود نداره که بخخواد بهم بریزه
احتمالا هدفتون مقاله دیگه ای بوده
اونو بفرمایید لطفا
سلام استاد ،،، یه اکسل دارم قبلا به راحتی می تونستم تغییرات را اعمال کنم و سیو کرده و بهره برداری کنم ،،، لیکن چند روزی هست هر کاری میکنم و اعمال تغییرات می کنم ولی بعد سیو کردن تغییر ات اعمال شده می پره یا جواب نهایی را بهم نمیده ،،،، علتش چیه ،،، سپاس فراوان از راهنمایی ،،، اکبر از بوشهر
درود
شاید read only باز میشه
ااگر محاسبات انجام نمیشه محاسبات manual نباشه. از تب formula/calculation رو چک کنید
سلام من حسابدار هستم و کار اصلی ام خرید و فروش سهام دقیقا از سال ۱۳۷۷ تاکنون . با اکسل و کلا آفیس از آن قدیمها که تعداد ۱۰ تا دیسکت برای نصبش بود آشنا بودم .
من جانباز ۷۰ درصد هستم و بسختی حرکت می کنم و بسختی درد دارم اما این نرم افزار موقعیت شغلی بسیار ایده الی برایم بوجود آورد و کمک بزرگی برای من شد در امر خرید و فروش سهم و محاسبات آن
دنیای شغلی ام با اکسل بسیار متحول شد . فکرش را بکنید کارت حسابداری و محاسباتی باشد و با ابزار اکسل هم تا حدودی آشنا باشید ( نه خیلی حرفه ایی ولی درحد کار راه انداز ) . در کار بورس و سهام واقعا کمک و عصای دستم می باشد ..
در این مدت استاندارم را حفظ کردم و نتیجه اش یک آمار و مستندات ۲۰ و چند ساله شده است …وقتی نگاه می کنم به انبوه اطلاعات که بطور روزانه برای خودم ثبت می کنم . متوجه شدم که خودم برای خودم شرکتی شدم …
الان که ۶۰ سالم شده ذره ایی از اکسل خسته نشدم … چند سال پیش درمورد اکسس مقداری تحقیق و یه کارهایی انجام دادم اما شخصا به این نتیجه رسیدم اسپرت شیت اکسل دیگر انگیزه ایی برای ورود به اکسس نمی دهد (شایدم اشتباه می کنم ) . البته خیلی دلم می خواست برنامه نویسی را را در اکسس جلو ببرم . اما دیگر حوصله و اشتیاق چند سال پیش را ندارم و سهل و ساده بودن اکسل نیز مزید علت شده است …
خواستم بگویم همینجا به روح و روان مبدع و سازنده این برنامه ساده و کارآمد درود می فرستم
درضمن عیدتان مبارک و از بلای کرونایی محفوظ و درامان خدا باشید
با سلام
علت اینکه نمودارها در اکسل بعضی وقتها اندازه زیادی دارن چیه مثلا من ۱۵ نمودار را در اکسل ساختم و به ورد منقل کردم حجمی معادل ۴۴۴مگابایت داشت. راه حلش چیه؟
با تشکر فراوان
سلام
تا حالا همچین موردی ندیده بودم. نحوه انتقالتون به ورد چطوری انجام میدید؟
با سلام خانم مهندس
من یک لیست ۵۰۰ ردیفی در ۱۵ ستونی دارم و این لیست هر روز با اعداد جدید تکرار می شود و من هر روز این لیست جدید به لیست روزهای قبلی اضافه می کنم و عملیاتی مثل جمع یا میانگین و … روی هر ستون در ردیف های مشابه اینها انجام میدهم.
حالا سوال من این است که چکار کنم که این عملیات هر روز تکرار نکنم یعنی یکبار فرمول عملیات را اعمال کنم و به محض اضافه شدن لیست جدید، گزینه های مشابه مورد نظر مرا شناسایی کرده و عملیات مثل جمع یا میانگین و … انجام دهد. چیزی شبیه consolidate در data .
سلام خانم مهندس
ممنون از انرژی که میزارید
من یه فایل اکسل دارم که حدود یک میلیون رکورد داخلش هست، بخش زیادی از محتوای این اکسل رو میخوام بر اساس چندین شرط از پیش تعین شده حذف کنم.
به کمک یکی از اساتید ماکرویی تهیه کردم که در حجم بسیار کم ( حود ۱۰۰۰ رکورد)این امکان رو داره فراهم میکنه ولی توی حجم دیتای سنگینی که من لازم دارم هنگ میکنه و جواب نمیده.
ممنون میشم مساعدت بفرمایید و بنده رو راهنمایی کنید.
کد:
Sub M_E()
Dim bdata As Boolean
Dim i, lr, lr2, h, chk As Long
Dim fDir As String
With Application
lr = Cells(Rows.Count, 1).End(3).Row
.ScreenUpdating = False
For i = 2 To lr
h = 2
Do While h < 8
If Cells(i, 10) = Sheets(2).Cells(h, 2) Then
Range("a" & i).EntireRow.Delete
i = i – 1
End If
h = h + 1
Loop
Next
lr2 = Cells(Rows.Count, 1).End(3).Row
For i = 2 To lr2
h = 2
bdata = True
Do While h < 16
If Cells(i, 6) Sheets(2).Cells(h, 1) And Cells(i, 6) Empty Then
For chk = 2 To 16
If Cells(i, 6) = Sheets(2).Cells(chk, 1) Then
bdata = False
Exit For
End If
Next chk
If bdata = True Then
Range(“a” & i).EntireRow.Delete
i = i – 1
End If
End If
h = h + 1
Loop
Next
.ScreenUpdating = True
.DisplayAlerts = False
fDir = ThisWorkbook.Path & “\new file.xlsx”
ActiveWorkbook.SaveAs Filename:=fDir, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
.DisplayAlerts = True
End With
End Sub
درود بر شما
برای این حجم دیتا، پیشنهاد میدم از پاور کوئری استفاده کنید
سرکار خانم مهندس خاکزاد
با سلام، آنطور که فرمودید برای افزایش سرعت محاسبات می بایست از توابع Volatile کمتر استفاده کرد. آیا توابعی هست که بتوان جایگزین دو تابع Indirect و Sumif کرد؟ (از ترکیب تابع Indirect و Address در Sumif استفاده شده است)
صمیمانه سپاسگزارم
درود بر شما
تابع sumif میتونه بسته به شرایط با Dsum جایگزین بشه
میشه در شرایط مناسب از ابزار table استفاده کرد
سرکار خانم مهندس خاکزاد
راهنمایی تون بسیار عالی و موثر بود. براتون آرزوی بهترین ها رو دارم.
با سپاس و تشکر فراوان