کار با فایل های سنگین اکسل و افزایش سرعت
کاهش حجم و افزایش سرعت فایل اکسل
حتما تا حالا با این موضوع برخورد کردید که فایل اکسلی که باهاش کار میکنید خیلی سنگین شده و کار کردن باهاش سخت. فایل های سنگین به سختی ایمیل میشن، موقعی که میخوایم این فایل ها رو باز کنیم زمان طولانی باید صبر کنیم تا فایل باز بشه. حتی با یک تغییر کوچک در فایل، باید مدت زیادی رو صبر کنیم تا محاسبات فایل بروزرسانی بشه و مشکلاتی از این قبیل. در این مقاله سعی داریم راه های حل این مشکل رو برای افزایش سرعت فایل اکسل ارائه بدیم. راه حل ها در سه دسته اصلی تقسیم میشن:
- مستقیما حجم فایل رو کاهش بدیم
- فضاهای خالی در شیت حذف کنید.
- شیتهای غیر لازم و 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$5+$E$5″، و این فرمول رو در صد سلول استفاده کردید. یعنی یعنی تعداد کل ارجاعات به سلول میشه دویست تا. اما اگه در یک سلول مثل A5 این فرمول رو بنویسیم و اون صد سلول رو به سلول A5 وصل کنیم، تعداد ارجاعات به سلول ها نصف میشه. در واقع =$D$5+$E$5 بجای صد بار، یکبار محاسبه میشه. پس اگه حجم زیادی فرمول با محاسبات یکسان دارید، مطابق مثال ارائه شده اونها رو به چند قسمت بشکنید. داده ها رو موقع استفاده از توابع جستجو مرتب کنید. وقتی داده ها مرتب باشن، زودتر و راحت تر پیدا میشن! این موضوع در خصوص توابعی مثل VLOOKUP, INDEX, MATCH, SUMIF و یا هر تابعی در اکسل که به دنبال داده بخصوصی میگرده صدق میکنه. پس بهتره زمانی که از این توابع استفاده میکنیم، داده هامون مرتب (Sort) باشن تا روند جستجو سریع تر باشه. شما با راه حل های زیادی آشنا شدید که میتونید در مورد فایل های سنگین، بسته به شرایط، از هر کدوم از این روش ها استفاده کنید. با این روش ها دیگه فایل هاتون دیر باز نمیشن و نیازی نیست زمان زیادی رو برای انجام محاسبات اتلاف کنید. آیا راه حل های ارائه شده برای افزایش سرعت فایل اکسل شما مفید بودن؟ یا هنوز با فایل های سنگین مشکل دارید؟ مسائل در رابطه با این موضوع رو در قالب کامنت در ادامه همین پست با ما در میون بذارید. راه حل های مایکروسافت برای بهبود سرعت محاسبات در اکسل رو هم نگاه کنید.
با سلام و احترام
یک فایل اکسل با بیش از نیم میلیون سطر دارم که می‌خواهم رابطه رگرسیون بین این دو ستون برقرار کنم. مشکل محدودیت تعداد سطر در اکسل را چطور برطرف کنم؟
پیشاپیش ممنون از راهنمایی شما
درود
دیتا را با استفاده از Power Query وارد Data Model کنید و با استفاده از DAX، محاسبات رو ایجاد کنید.
بسیار عالی و آموزنده. موفق و پاینده باشید…
دست مریزاد به این زحمت و دقت. مقاله کامل و جالبی بود و خیلی زود من رو به نتیجه لازم رسوند. ذخیره با فرمت excell binary خیلی عالی بود.
با سلام و خسته نباشید
من یه فایل اکسل با ۸شیت دارم همه شیت ها دارای جدول های ۳۰۰۰ردیفه هستن واطلاعات اولیه خود رو از شیت اول میگیرن.جدول شیت اول ۴۰ستون و ۳۰۰۰ردیف دارد.
5ستون از۴۰ستون شیت اول اطلاعات خام وارد میشود که شامل اطلاعات هزینه ای و درآمدی ست
در ابتدای هر سال اطلاعات این ۵ستون رو پاک میکنم و اطلاعات سال جدید رو وارد میکنم. هرسال که اینکار رو میکنم زمان ثبت هزینه ها در جدول بیشتر میشود مثلا زمان ثبت هزینه در سال۹۹ ، ۱۰ثانیه است در ابتدای سال۴۰۰که اطلاعات ۵ستون رو پاک کردم زمان ثبت هزینه ۲۲ثانیه شد.البته من ثبت هزینه رو بوسیله یک فرم وی بی ای انجام میدهم.
سوال چرا هر بار که اطلاعات سال قبل رو پاک میکنم زمان ثبت هزینه در جدول تقریبا دو برابر میشود؟
تو اینترنت سرچ کردم و بحث سلول های فعال رو هم تا جایی که امکان داشت کاهش دادم
سلام
باید کدی که برای فرم زده شده بررسی بشه.
میتونید سوالتون رو به همراه فایل در گروه تلگرامی اکسل پدیا ارسال کنید.
سلام
اگه داده‌های یکی شیت رو توسعه بدیم بهتره یا اینکه یک شیت جدید باز کنیم؟
تعداد سلولها در کند شدن اثرش بیشتره یا اضافه شدن شیت؟
سلام
بستگی داره که قصد دارید چه داده ای رو میخواید اضافه کنید.
اگر ورود داده به صورت صحیح وارد شده باشه میتونید شیت رو توسعه بدید و نباید خیلی تأثیری روی سرعت بذاره.
اما یک راه دیگه ایجاد جداول مختلف و تعریف Relation بین آنها هست که هم حرفه ای تر هست هم امکان ایجاد گزارش های تحلیلی بهتره به شما خواهد داد که خب نیازمند پیاده سازی صحیح هست.
سلام. روز خوش
من میخوام سطر و ستونهای اضافی رو با دیلیت کردن حذف کنم ولی در یک سری از شیت ها خطا میده که حافظه کافی نداری و نمیشه دیلیت کنی و در شیت های دیگر که خطا نمیده بعد از حذف کردن دوباره سلول آخرم همون قبلی میشه. ممکنه راهنمایی کنین؟
ممنونم
اگه فایلتون محاسبات زیادی داشته باشه این مشکل پیش میاد
محاسبات شیت رو روی Manual بذارید و از شیت های سبک تر شروع به حذف کنید و کم کم سطر و ستون های اضافی رو حذف کنید.
با عرض سلام و وقت بخیر
من حسابدار هستم و کارم با فایل های حجیم اکسل هست که فرمول های زیادی رو شامل میشه. تا چندی پیش که موقع ورود به برنامه و درج اطلاعات برنامه کلا هنگ میکرد که توسط واحد it و با پاک کردن حساب کاربری و تخصیص مجدد حساب ورود به ویندوز مشکل حل شد. ولی در حال حاظر موقع باز کردن و بستن فایل زمان زیادی صرف میشه. البته اینم بگم در زمان کار با همون فایل هیچ مشکل یا کندی وجود نداره.
میخواستم بدونم مشکل کجاست؟
با تشکر
درود
مسائل اکسلیش رو که در همین مقاله شرح دادیم
مسائل IT و توانایی سیستم رو هم که باید جداگانه بررسی کنید
سلام وقت بخیر
من در طول روز از اکسل زیاد استفاده میکنم و اطلاعاتی که در اکسل وارد میکنم متنی و عددی هست و از فرمول کمتر استفاده میکنم. اکسل قبلی من ورژن ۲۰۱۶ بود که هیچ مشکلی نداشتم ولی الان که ورژن ۲۰۱۹ رو نصب کردم برای باز کردن فایل اکسل که حجمش از ۳ مگابایت بیشتر باشه مشکل دارم. و حتی مواقعی شده که حدود ۱۰ دقیقه صبر کردم تا اکسل باز بشه و موقع کار کردن باهاش هم با کند شدن و هنگی مواجه میشم. (ویندوز و مشخصات سیستمم هم بالاست و از لحاظ ویندوز و سیستم و آنتی ویروس هیچ مشکلی ندارم. اتفاقا دوباره ورژن ۲۰۱۶ رو نصب کردم ولی بازم این مشکل رو دارم هنوز)
روش هایی که توی سایتتون گفتید مثل باینری کردن فایل هم انجام دادم. هنگام باز کردن فایل سرعتش یه خورده بهتر شد ولی بازم در کار کردن باهاش با کندی سرعت و هنگی مواجه میشم.
میشه لطفا راهنماییم کنید؟
درود
شاید نسخه ای که نصب کردید نسخه کاملی نیست
نسخه اش کامله
وقتی با ۲۰۱۶ مشکلی نداشتید، ۲۰۱۹ اینطوره باید داخل نرم افزار دنبال مشکل بگردید
مخصوصا که فرمول نویسی خاصی ندارید و اصولا دیتا، حجمی نداره
بعضی نسخه ها باگ دارن.
در هر صورت همین موارد به ذهنم میرسه
سلام و درود ، وقتون بخیر و خداقوت ، یه دیتابیس بزرگ که حاوی کللی فرمول و محاسبات هست درست شده که حجمش ۵۲ مگابایته شاید حدود ۷۰ هزار آیتم ثبت شده و به چندیل فایل اکسل دیگه لینک هستش ، در شبکه داخلی ورک گروپ اشتراک گذاری شده ، مشکلی که داریم اینه که سرعت ذخیره سازی و تغییرات یا اضافه کردن آیتم خیلی کند انجام میشه ، مخصوصا موقع ذخیره کردن که باید صبر کنیم تا درصدش پر بشه چند دقیقه ، آیا راهی هست که بشه سرعتش رو افزایش داد؟؟؟ راههای بالارو هم تا حدودی انجام دادیم ، گاهی به این نتیجه میرسیم که از اکسل خارج بشیم و برنامه نویسی تحت شبکه رو انجام بدیم برای کارمون
راهنمایی کنید ممنون میشم
درود
خیلی بستگی به کاری که میکنید داره. شاید روش های بهتری برای انجام اون کار وجود داشته باشه.
ولی به هر حال اکثر راه های کار با فایل های سنگین در بالاارائه شده.
بسته به شرایط، وی بی ای و پاورها و یا ترکیب اینها رو هم امتحان میتونید کنید
با این حلقه های تو در تو و اون حجم از فایل قطعا هنگ میکنه از کدهای فیلتر ستونها استفاده نمایید و سپس محدوده مربوطه را با کد در شیت دوم کپی نمایید سپس می توانید شیت قبلی راحذف نمایید
سلام.
ببخشید اکسل من به دلیل به کار بردن فرمول های بسیار سنگین شده است. با توجه به نوشته های شما به این نتیجه رسیدم که بهترین کار برای من این است که حالت محاسبات را از حالت خودکار (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 استفاده کرد
سرکار خانم مهندس خاکزاد
راهنمایی تون بسیار عالی و موثر بود. براتون آرزوی بهترین ها رو دارم.
با سپاس و تشکر فراوان
با سلام و عرض تشکر
در حال حاضر محاسبات بر روی حالت دستی (Manual) است و این مشکل (هنگ شدن در زمان Delete و یا Insert کردن) رو در این شرایط داریم.
با تشکر و سپاس فراوان
گزینه اخر که به ذهنم میرسه، عدم استفاده از ورژن ۲۰۱۳ هست
البته شرایط شیکه (احتمال) و توانایی سیستم و اینها هم باید در نظر گرفته بشه
با سلام و عرض ادب
یک فایل اکسل نسبتاً سنگین با تعدادی شیت و سلول های دارای فرمول داریم (حجم بیش از ۱۰ مگابایت)، مشکل آزار دهنده ای که وجود داره اینه که هر وقت بخواهیم کل سطر یا ستونی را حذف کنیم (کلیک راست روی سطر یا ستون های انتخاب شده و زدن Delelet) اکسل در حالت Not Responding قرار می گیره و ناچارا برنامه رو باید ببندیم. اگه راه حلی وجود داره لطفاً راهنمایی بفرمایید.
با تشکر و سپاس فراوان
درود بر شما
راه هایی که میدونستیم و در این مقاله نوشتیم.
اگر راهی برای سبک کردن فایل ندارید، محسبات رو روی حالت دستی بذارید و بعد سطر و ستون حذف کنید
با سلام
ابتدا اطلاعات رو انتخاب و با دکمه Delete کیبورد ، سلول ها رو خالی از اطلاعات کنید. بعد از خالی شدن سلول ها ، ردیف ها رو Delete کنید
درود یک سوال داشتم
من از تابع vlookup استفاده کردم خطای n/a میداد و دو رکورد متفاوت ثبت کردم یکی رکورد های مربوط به دو ها که رکوردها نزولی اند و دیگری پرش ها که رکورد ها افزایشی اند در رکورد های افزایشی مشکلی ندارم عددی که بین دو رکورد ثبت میشه نمره پایین تر رو بر میداره
اما در رکوردهای نزولی نمره بالاتر رو بر میداره
مثلا ۵.۴۵ امتیاز ۶۵ رو میگیره و ۵.۴۰ امتیازه ۶۴ و عددی که بین این دو تا می باشد ۵.۴۳ نمره ۶۴ رو میگیره که درسته ولی برای رکوردهای دو ها این فرمول عمل نمیکنه راه حلی دارید به من بدید ؟
میتونید برای این کار یک شاخص برعکس براش در نظر بگیرید که مثل صعودی ها درست عمل کنه.
درود بر شما بسیار عای و کاربردی بود
با سلام و خسته نباشید
من یه فایل اکسل با ۸شیت دارم همه شیت ها دارای جدول های ۳۰۰۰ردیفه هستن واطلاعات اولیه خود رو از شیت اول میگیرن.جدول شیت اول ، ۴۰ستون و ۳۰۰۰ردیف دارد.
5ستون از۴۰ستون شیت اول اطلاعات خام وارد میشود که شامل اطلاعات هزینه ای و درآمدی ست.
در ابتدای هر سال اطلاعات این ۵ستون رو پاک میکنم و اطلاعات سال جدید رو وارد میکنم. هرسال که اینکار رو میکنم زمان ثبت هزینه ها در جدول بیشتر میشود مثلا زمان ثبت هزینه در سال۹۹ ، ۱۰ثانیه است در ابتدای سال۴۰۰که اطلاعات ۵ستون رو پاک کردم زمان ثبت هزینه ۲۲ثانیه شد.البته من ثبت هزینه رو بوسیله یک فرم وی بی ای انجام میدهم.
سوال
– چرا هر بار که اطلاعات سال قبل رو پاک میکنم زمان ثبت هزینه در جدول تقریبا دو برابر میشود؟
– دراثر پاک کردن اطلاعات چه اتفاقی رخ میده که جدول کند میشه؟
– حال برای رفع این کندی چکار میشه کرد؟ آیا نمیشه به یه نوعی جدول رو ریست کرد که به قبل از وارد نمودن اطلاعات و پاک کردن آنها برگرده ، یه چیزی شبیه 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
سلام، کدها رو به صورت زیر بنویسید:
عالی بود
یه فایل اکسل دارم وقتی بازش میکنم زمان میبره ولی زیاد نیست
ولی وقتی بین شیت ها حرکت میکنم تمام رم سیستم رو اشغال میکنه
یا نزدیک به یک ربع طول میشکه
ممنون میشم کمکم کنید
همون موارد بالا رو باید چک کنید
چک کردم نشد
اگه امکان داره آدرس ایمیل بفرمایید براتون بفرستم خودتونم چک کنید نمیشه
داخل گروه تلگرامی فایل رو بذارید و موضوع رو مطرح بفرمایید
سلام
لطفا کمک کنید
چهار ستون دارم
می خواهم اگر در ستون اولی عدد ۱ وارد شدسه ستون بعدی از حالت قفل خارج شده و امکان وارد کردن داده وجود داشته باشد
درود بر شما
اگر بخواید حتما قفل بشه باید کدنویسی بشه حتما
اما این کار و میتونید با دیتا ولیدیشن انجام بدید و فرمول نویسی کنید.
بسیار کامل و عالی. فقط یه مشکلی هست که چون اطلاعات داینامیک تغییر میکنه بازه ها هم تغییر می کنن. توی پارامترهای فرمول ها از جمله Sumif که مثال زدید چطور می شه بازه رو به صورت داینامیک تعریف کرد که با تغییر اطلاعات بازه هم تغییر کنه
درود بر شما
بهتره از table استفاده کنید که خودش گسترش پیدا کنه
ممنون از اطلاعات خوبتون. عالیه!