کار با فایل های سنگین اکسل و افزایش سرعت
کاهش حجم و افزایش سرعت فایل اکسل
حتما تا حالا با این موضوع برخورد کردید که فایل اکسلی که باهاش کار میکنید خیلی سنگین شده و کار کردن باهاش سخت. فایل های سنگین به سختی ایمیل میشن، موقعی که میخوایم این فایل ها رو باز کنیم زمان طولانی باید صبر کنیم تا فایل باز بشه. حتی با یک تغییر کوچک در فایل، باید مدت زیادی رو صبر کنیم تا محاسبات فایل بروزرسانی بشه و مشکلاتی از این قبیل. در این مقاله سعی داریم راه های حل این مشکل رو برای افزایش سرعت فایل اکسل ارائه بدیم. راه حل ها در سه دسته اصلی تقسیم میشن:
- مستقیما حجم فایل رو کاهش بدیم
- فضاهای خالی در شیت حذف کنید.
- شیتهای غیر لازم و 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) باشن تا روند جستجو سریع تر باشه. شما با راه حل های زیادی آشنا شدید که میتونید در مورد فایل های سنگین، بسته به شرایط، از هر کدوم از این روش ها استفاده کنید. با این روش ها دیگه فایل هاتون دیر باز نمیشن و نیازی نیست زمان زیادی رو برای انجام محاسبات اتلاف کنید. آیا راه حل های ارائه شده برای افزایش سرعت فایل اکسل شما مفید بودن؟ یا هنوز با فایل های سنگین مشکل دارید؟ مسائل در رابطه با این موضوع رو در قالب کامنت در ادامه همین پست با ما در میون بذارید. راه حل های مایکروسافت برای بهبود سرعت محاسبات در اکسل رو هم نگاه کنید.
با سلام و عرض تشکر
در حال حاضر محاسبات بر روی حالت دستی (Manual) است و این مشکل (هنگ شدن در زمان Delete و یا Insert کردن) رو در این شرایط داریم.
با تشکر و سپاس فراوان
گزینه اخر که به ذهنم میرسه، عدم استفاده از ورژن ۲۰۱۳ هست
البته شرایط شیکه (احتمال) و توانایی سیستم و اینها هم باید در نظر گرفته بشه
با سلام و عرض ادب
یک فایل اکسل نسبتاً سنگین با تعدادی شیت و سلول های دارای فرمول داریم (حجم بیش از ۱۰ مگابایت)، مشکل آزار دهنده ای که وجود داره اینه که هر وقت بخواهیم کل سطر یا ستونی را حذف کنیم (کلیک راست روی سطر یا ستون های انتخاب شده و زدن Delelet) اکسل در حالت Not Responding قرار می گیره و ناچارا برنامه رو باید ببندیم. اگه راه حلی وجود داره لطفاً راهنمایی بفرمایید.
با تشکر و سپاس فراوان
درود بر شما
راه هایی که میدونستیم و در این مقاله نوشتیم.
اگر راهی برای سبک کردن فایل ندارید، محسبات رو روی حالت دستی بذارید و بعد سطر و ستون حذف کنید
با سلام
ابتدا اطلاعات رو انتخاب و با دکمه Delete کیبورد ، سلول ها رو خالی از اطلاعات کنید. بعد از خالی شدن سلول ها ، ردیف ها رو Delete کنید
درود یک سوال داشتم
من از تابع vlookup استفاده کردم خطای n/a میداد و دو رکورد متفاوت ثبت کردم یکی رکورد های مربوط به دو ها که رکوردها نزولی اند و دیگری پرش ها که رکورد ها افزایشی اند در رکورد های افزایشی مشکلی ندارم عددی که بین دو رکورد ثبت میشه نمره پایین تر رو بر میداره
اما در رکوردهای نزولی نمره بالاتر رو بر میداره
مثلا ۵.۴۵ امتیاز ۶۵ رو میگیره و ۵.۴۰ امتیازه ۶۴ و عددی که بین این دو تا می باشد ۵.۴۳ نمره ۶۴ رو میگیره که درسته ولی برای رکوردهای دو ها این فرمول عمل نمیکنه راه حلی دارید به من بدید ؟
میتونید برای این کار یک شاخص برعکس براش در نظر بگیرید که مثل صعودی ها درست عمل کنه.
درود بر شما بسیار عای و کاربردی بود
با سلام و خسته نباشید
من یه فایل اکسل با ۸شیت دارم همه شیت ها دارای جدول های ۳۰۰۰ردیفه هستن واطلاعات اولیه خود رو از شیت اول میگیرن.جدول شیت اول ، ۴۰ستون و ۳۰۰۰ردیف دارد.
۵ستون از۴۰ستون شیت اول اطلاعات خام وارد میشود که شامل اطلاعات هزینه ای و درآمدی ست.
در ابتدای هر سال اطلاعات این ۵ستون رو پاک میکنم و اطلاعات سال جدید رو وارد میکنم. هرسال که اینکار رو میکنم زمان ثبت هزینه ها در جدول بیشتر میشود مثلا زمان ثبت هزینه در سال۹۹ ، ۱۰ثانیه است در ابتدای سال۴۰۰که اطلاعات ۵ستون رو پاک کردم زمان ثبت هزینه ۲۲ثانیه شد.البته من ثبت هزینه رو بوسیله یک فرم وی بی ای انجام میدهم.
سوال
– چرا هر بار که اطلاعات سال قبل رو پاک میکنم زمان ثبت هزینه در جدول تقریبا دو برابر میشود؟
– دراثر پاک کردن اطلاعات چه اتفاقی رخ میده که جدول کند میشه؟
– حال برای رفع این کندی چکار میشه کرد؟ آیا نمیشه به یه نوعی جدول رو ریست کرد که به قبل از وارد نمودن اطلاعات و پاک کردن آنها برگرده ، یه چیزی شبیه Ctrl+z که تمام کارها و عملیاتی رو که انجام داده و باعث کندی اون شدن رو صفر کنه؟
تو اینترنت سرچ کردم و بحث سلول های فعال رو هم تا جایی که امکان داشت کاهش دادم.
همچنین نمیتونم محاسبات جدول رو از حالت auto خارج کنم چون یکسری اشکالات ممکنه رخ بده.
بی صبرانه منتظر راهنماییهای شما هستم.
با تشکر
سلام
اصولا کاری که دارید انجام میدید نباید تأثیری روی سرعت فایل بذاره.
مواردی که فرمودید درست هست، یک مورد دیگه رو هم چک کنید:
تعداد ستون های هر جدول شما در همه شیت ها
ممکنه که به دلایل غیر عمدی ستون های اضافی به جدولتون اضافه شده باشن.
همچنین فرمت ها رو یکبار کامل پاک کنید و اگر نتیجه ای نداشت مجددا برگردانید.
نهایتا اگر نشد میتونید فایل خالی رو در گروه تلگرامی ارسال کنید که دقیقتر بررسی بشه.
سلام من میخوام از select در vba استفاده نکنم از چه عملگری میتونم استفاده کنم؟
در کد زیر
Sheets(“Sheet2”).Select
Cells(1, 2).Select
Selection.Copy
Sheets(“Sheet1”).Select
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
سلام، کدها رو به صورت زیر بنویسید:
Sheets("Sheet2").Range("A2").copy Sheets("Sheet1").Range("A1').PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=Falseعالی بود
یه فایل اکسل دارم وقتی بازش میکنم زمان میبره ولی زیاد نیست
ولی وقتی بین شیت ها حرکت میکنم تمام رم سیستم رو اشغال میکنه
یا نزدیک به یک ربع طول میشکه
ممنون میشم کمکم کنید
همون موارد بالا رو باید چک کنید
چک کردم نشد
اگه امکان داره آدرس ایمیل بفرمایید براتون بفرستم خودتونم چک کنید نمیشه
داخل گروه تلگرامی فایل رو بذارید و موضوع رو مطرح بفرمایید
سلام
لطفا کمک کنید
چهار ستون دارم
می خواهم اگر در ستون اولی عدد ۱ وارد شدسه ستون بعدی از حالت قفل خارج شده و امکان وارد کردن داده وجود داشته باشد
درود بر شما
اگر بخواید حتما قفل بشه باید کدنویسی بشه حتما
اما این کار و میتونید با دیتا ولیدیشن انجام بدید و فرمول نویسی کنید.
بسیار کامل و عالی. فقط یه مشکلی هست که چون اطلاعات داینامیک تغییر میکنه بازه ها هم تغییر می کنن. توی پارامترهای فرمول ها از جمله Sumif که مثال زدید چطور می شه بازه رو به صورت داینامیک تعریف کرد که با تغییر اطلاعات بازه هم تغییر کنه
درود بر شما
بهتره از table استفاده کنید که خودش گسترش پیدا کنه
ممنون از اطلاعات خوبتون. عالیه!