قالب بندی شرطی (5)
لیست ها و محدوده ها (5)
ماکروها (روش های VBA) (63)
متفرقه (39)
اشکالات و اشکالات اکسل (3)

نحوه افزایش سرعت و بهینه سازی کد VBA

دیر یا زود کسانی که می نویسند ویژوال بیسیکبرای برنامه‌ها، مشکلی پیش می‌آید - اگرچه کد زندگی را آسان‌تر می‌کند و همه چیز را به‌طور خودکار انجام می‌دهد، اما زمان زیادی طول می‌کشد. در این مقاله تصمیم گرفتم چند مورد را جمع آوری کنم توصیه های ساده، که به سرعت بخشیدن به کار کد VBA کمک می کند ، در حالی که در برخی موارد بسیار چشمگیر است - ده ها بار یا حتی بیشتر. تاکید اصلی در مقاله بر مبتدیان است، بنابراین، در ابتدای مقاله، بیشترین روش های سادهبهينه سازي. راه حل های "عمیق" بیشتر برای بهینه سازی کد در انتهای مقاله آورده شده است، زیرا برای اعمال این راه حل ها، به تجربه کافی در VB نیاز دارید و ممکن است بلافاصله چنین روش های بهینه سازی برای کسی روشن نباشد.

1. اگر تعداد زیادی Activate و Select در کد وجود دارد، به خصوص در چرخه ها، باید بلافاصله از شر آنها خلاص شوید. چگونه این کار را انجام دهیم، در مقاله نوشتم: انتخاب و فعال سازی - چرا آنها مورد نیاز هستند و آیا مورد نیاز هستند؟
2. در حین اجرای کد حتما غیر فعال کنید:
   • محاسبه مجدد خودکار فرمول ها به طوری که با هر دستکاری روی برگه در هنگام اجرای کد، فرمول ها دوباره محاسبه نمی شوند - اگر تعداد زیادی فرمول وجود داشته باشد، می تواند سرعت کد را به شدت کاهش دهد:

     Application.ScreenUpdating = نادرست

   • در هر صورت، ردیابی رویداد را غیرفعال کنید. لازم است تا اکسل رویه‌های رویدادی را که ممکن است در برگه‌ای که تغییرات در آن ایجاد شده باشد، اجرا نکند. به عنوان یک قاعده، اینها رویدادهای تغییر سلول ها، فعال کردن صفحات و غیره هستند:

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

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

   • در هر صورت، می توانید نمایش اطلاعات را در نوار وضعیت اکسل خاموش کنید (در چه مواردی اصلاً اطلاعات در آنجا نمایش داده می شود و دلیل آن را می توانید در مقاله: نمایش پیشرفت ها متوجه شوید). اگرچه این کار منابع زیادی را مصرف نمی کند، اما گاهی اوقات می تواند سرعت کد را افزایش دهد:

     Application.StatusBar = نادرست

     Application.StatusBar = نادرست

   نکته اصلی برای یادآوری - همه این ویژگی ها باید پس از اجرای کد دوباره فعال شوند. در غیر این صورت، ممکن است مشکلاتی برای کار در اکسل وجود داشته باشد. به عنوان مثال، اگر فراموش کردید که محاسبه مجدد خودکار فرمول ها را فعال کنید - اکثر فرمول ها منحصراً با روش اجباری دوباره محاسبه می شوند - تغییر مکان+F9. و اگر فراموش کردید به روز رسانی صفحه را خاموش کنید - یعنی این شانس وجود دارد که خود را از توانایی کار بر روی برگه ها و کتاب ها مسدود کنید. اگرچه به طور پیش‌فرض خاصیت ScreenUpdating باید به True برگردد، اگر در داخل رویه غیرفعال شده باشد - بهتر است به این اعتماد نکنید و به بازگرداندن اجباری همه ویژگی‌ها به مکان‌هایشان عادت کنید. در واقع، همه چیز به چند خط خلاصه می شود:

   "بازگرداندن به روز رسانی صفحه Application.ScreenUpdating = True "برگرداندن محاسبه مجدد خودکار فرمول ها Application.Calculation = xlCalculationAutomatic "فعال کردن ردیابی رویداد Application.EnableEvents = True

   این کد در عمل چگونه به نظر می رسد؟ فرض کنید باید در یک حلقه از 10000 خط مقدار بنویسید:

   Sub TestOptimize() "غیرفعال کردن به روز رسانی صفحه Application.ScreenUpdating = False "غیرفعال کردن محاسبه مجدد خودکار فرمول ها Application.Calculation = xlCalculationManual "غیرفعال کردن ردیابی رویداد Application.EnableEvents = نادرست "غیرفعال کردن صفحه بندی =FalpplayageEctiveWorkbook.Diakt. lr تا زمانی که lr = 1 تا 10000 سلول (lr, 1). مقدار = lr "برای مثال، فقط خطوط را شماره گذاری کنید بعد "به روز رسانی صفحه را برگردانید Application.ScreenUpdating = True "برگرداندن محاسبه مجدد خودکار فرمول ها Application.Calculation = xlCalculationAu "برنامه ردیابی رویداد را فعال کنید. EnableEvents = True EndSub

   می شکند صفحات چاپ شدهشما نمی توانید برگردید - آنها در هر صورت کار را کند می کنند.

3. از حلقه هایی مانند Do while برای یافتن آخرین سلول باید اجتناب شود. افراد مبتدی اغلب این اشتباه را مرتکب می شوند. محاسبه آخرین سلول در کل صفحه یا در یک ستون خاص بدون این حلقه ترمز Do while بسیار کارآمدتر و سریعتر است. من معمولا استفاده میکنم

   lLastRow = سلول ها (Rows.Count,1).End(xlUp).Row

   lLastRow = سلول ها (Rows.Count,1).End(xlUp).Row

   من گزینه های دیگری را برای تعیین آخرین سلول در مقاله شرح دادم:

برای بیشتر کاربران با تجربه VBA، من چندین راه حل برای بهینه سازی کدها در شرایط مختلف ارائه می دهم:

• بیشترین بهینه سازی خوبکد، اگر باید مستقیماً با سلول‌های شیت کار کنید، آنها را پردازش کنید و احتمالاً مقادیر را تغییر دهید، سریع‌تر است که تمام پردازش‌ها را در یک آرایه انجام دهید و آن را به یکباره روی صفحه خالی کنید. به عنوان مثال، کد بالا برای پر کردن سلول ها با اعداد در این مورد به صورت زیر خواهد بود:

  Sub TestOptimize_Array() "مستقیم کد برای پر کردن سلول هاکم نور، lr به مدت طولانی "در یک آرایه یکباره تمام مقادیر 10000 ردیف از ستون اول را به خاطر بسپارید. arr = سلول ها (1، 1). تغییر اندازه (10000). مقدار "اگر برای دو یا چند ستون نیاز به بالشتک دارید "arr = سلول‌ها(1، 1). تغییر اندازه (10000، 2). مقدار"یا "arr = محدوده (سلول‌ها(1، 1)، سلول‌ها (10000، 2)). مقدار یا به طور خودکار آخرین سلول را محاسبه کرده و داده ها را از سلول A3 وارد آرایه کنید "llastr = سلولها (ردیف. تعداد، 1). پایان (xlUp). ردیف "آخرین سلول ستون A "arr = محدوده(سلول‌ها(3، 1)، سلول‌ها(llastr، 2)). مقداربرای lr = 1 تا 10000 arr(lr,1) = lr "آرایه را با شماره سریال پر کنیدبعد "آرایه پردازش شده را دوباره به صفحه در همان سلول ها آپلود کنیدسلول ها (1، 1). تغییر اندازه (10000). مقدار = arr پایان زیر

  Sub TestOptimize_Array() "مستقیم کد برای پر کردن سلول ها Dim arr, lr تا زمانی که "در یک آرایه یکباره تمام مقادیر 10000 ردیف از ستون اول arr = Cells(1,1) را به خاطر بسپارید. تغییر اندازه (10000) ).Value " اگر باید دو یا چند ستون را پر کنید"arr = Cells(1, 1).Resize(10000, 2).Value "یا"arr = Range(Cells(1, 1),Cells(10000, 2)).Value "یا به طور خودکار آخرین سلول را محاسبه کرده و داده ها را از سلول A3 در آرایه وارد کنید"llastr = Cells(Rows.Count, 1).End(xlUp).Row "последняя ячейка столбца А "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "заполняем массив порядковыми номерами Next "Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub!}

  اما در اینجا باید این واقعیت را نیز در نظر گرفت که آرایه های بزرگ می توانند به سادگی باعث سرریز حافظه شوند. این بیشتر مربوط به سیستم های 32 بیتی است، جایی که حافظه کمتری برای VBA و Excel نسبت به سیستم های 64 بیتی اختصاص داده می شود.

• اگر از IF-IIF سریع استفاده می کنید، آن را با IF ... سپس ... Else جایگزین کنید
• همچنین بهتر است به جای Switch() و Shoose() از همان IF ... سپس ... Else استفاده کنید.
• در بیشتر موارد، بررسی یک رشته برای "خالی نیست" با Len() بهتر از مقایسه مستقیم با خالی بودن است: Len(s)=0 به جای s = "" . این به این دلیل است که کار با رشته ها بسیار کندتر از کار با داده های عددی است.
• الحاق رشته ها را بیهوده اعمال نکنید. برای مثال، s = "AB" سریعتر از: s = "A" و "B" خواهد بود.
• مقادیر متن را مستقیماً مقایسه نکنید. بهتر است از تابع داخلی StrComp استفاده کنید:
  اگر s s1 سپس کندتر از
  اگر StrComp(s، s1، vbBinaryCompare) = 0
  و حتی بیشتر از آن اگر مقایسه نیاز به حروف کوچک و بزرگ داشته باشد:
  اگر LCase(s) LCase(s1) آنگاه کندتر از آن خواهد بود
  اگر StrComp(s، s1، vbTextCompare) = 0
• برای... حلقه های بعدی معمولا سریعتر از حلقه های Do...Loop هستند.
• از تصاحب بپرهیزید متغیرها را تایپ کنیدگونه. اگرچه وسوسه عالی است، اما این نوع حافظه زیادی می گیرد و کد را بیشتر کند می کند. همچنین، برای متغیرهای شی، در صورت امکان، باید از بدون چهره اجتناب کنید نوع جهانینوع بتن را مورد اعتراض قرار دهید و اعمال کنید:

  Dim rRange به عنوان Range، wsSh به عنوان Worksheet

• اگر با آرایه‌ها کار می‌کنید، می‌توانید این را به صراحت هنگام اعلان مشخص کنید:

  Dim arr() به عنوان رشته، arr2() به عنوان طولانی

  اما این تنها در صورتی است که اطمینان حاصل شود که انواع داده های کاملاً مشخص شده در آرایه وارد می شوند

البته اینها همه ترفندها و راه حل های بهینه سازی نیستند. اما زوج اول باید کافی باشد. به علاوه، همیشه باید از عقل سلیم استفاده کنید. به عنوان مثال، اگر کد در 2 ثانیه اجرا شود، احتمالاً هیچ فایده ای برای بهینه سازی بیشتر آن وجود ندارد. البته اگر این کد جزو کدهایی نباشد که به سادگی مقدار یک یا دو سلول را تغییر می دهد.

آیا مقاله کمکی کرد؟ لینک را با دوستان خود به اشتراک بگذارید دروس تصویری

("نوار پایین":("textstyle":"ایستا"،"textpositionstatic":"پایین"،"textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"پایین سمت چپ"،"textpositionmarginleft":24," textpositionmarginright":24"textpositionmargintop":24"textpositionmarginbottom":24"texteffect":"slide"، "texteffecteasing":"easeOutCubic"، "texteffectduration":600، "texteffectslidedirection": "leftlid"، "texteffects" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"اسلاید"،"texteffectslidedirection1":"راست"،"texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:601"ext "texteffectdelay1":1000,"texteffect2":"slide", "texteffectslidedirection2":"راست"،"texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic"،"texteffectduration2":600""ext textcss":"display:block; padding:12px; text-align:left;""textbgcss":"display:block; position:absolute; top:0px; left:0px; عرض:100%؛ ارتفاع:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);""titlecss":"display:block; موقعیت:نسبی; فونت:پررنگ 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; موقعیت:نسبی; فونت: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; موقعیت:نسبی; margin-top:8px;""texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;,""descriptioncssresponsive":"display:none !important;""buttoncssresponsive" """"addgooglefonts":false,""googlefonts":""، "textleftrightpercentforstatic":40))

دارای 32767 کاراکتر بنابراین، تابع تغییر سریععرض ستون با محتوای آن همیشه راحت نیست. :) اگرچه در بیشتر موارد کمک می کند. نشانگر ماوس را روی مرز سمت راست عنوان ستون ببرید و روی آن دوبار کلیک کنید تا برنامه پهنای ستون بهینه را انتخاب کند. این ترفند برای چندین ستون و همچنین کل کاربرگ کار می کند.

2. نحوه انتخاب سریع کل سطر یا ستون

هنگام کار با حجم زیاد داده، بدون آن غیرممکن است میانبرهای صفحه کلید، که با آن می توان به راحتی در برگه حرکت کرد و ستون ها یا ردیف ها را انتخاب کرد. ترکیبات Ctrl+Spacebarو Shift + Spacebarفقط مسئول انتخاب سریعستون یا ردیف فعلی

یک بار یک کودک چهار ساله را تماشا کردم که از کلیدهای میانبر کپی و پیست برای وارد کردن یک کلید بازی استفاده می کرد. به نظر می رسد یک تثلیث جدایی ناپذیر است ctrl+c, Ctrl+Vو Ctrl+Aهمراه با شیر مادر توسط نسل جوان جذب می شود. به هر حال، دستور "انتخاب همه" در اکسل یک ویژگی کوچک دارد: یک کلیک Ctrl+Aمحدوده فعلی را انتخاب می کند و double کل جدول را انتخاب می کند.

4. نحوه ایجاد تغییرات در چندین شیت به طور همزمان

شلغم بخار پز، راه شماست! به عنوان مثال، برای تغییر اندازه فونت در تمام برگه ها به طور همزمان، باید آنها را در یک گروه با کلیک بر روی زبانه های برگه ها و در حالی که کلید Ctrl را نگه دارید، جمع آوری کنید.

5. نحوه جابجایی یک خط در سلول فعلی

اگر می‌خواهید یک تازه‌کار را مسخره کنید - از او بخواهید که داده‌های داخل سلول را در چند خط وارد کند. البته، یک ترفند مدرسه، اما با صدای بلند با نسل قدیمی کار می کند. ترکیبی Alt+Enterاز حرکت به سلول دیگر جلوگیری می کند و مکان نما را به آن حرکت می دهد خط جدیددر داخل سلول فعلی

درج کنید خط اضافییا ستون سه یا چهار کلیک ماوس می گیرد. این مقدار زیادی نیست، اما فقط تا زمانی که برای مثال به یک دوجین ستون جدید نیاز داشته باشید. با این حال، با انتخاب قبلی تعداد مورد نیاز سلول های موجود، می توانید با همان تعداد کلیک پیاده شوید.

7. نحوه درج سریع تاریخ جاری

ظاهرا کلیدهای میانبر Ctrl + ;و Ctrl + Shift + ;به جای سرود صبح باید از رادیو پخش شود. این ترکیب ها باید تکرار شوند تا زمانی که هیچ کارگر سختی روی کره زمین باقی نماند که تاریخ و زمان را به صورت دستی رانندگی کند.

• آموزش

پیشگفتار

این اتفاق افتاد که امروزه بسیاری از افراد مجبور به کار (نوشتن ماکرو) در VBA در اکسل هستند. برخی از ماکروها حاوی صدها خط کد هستند که باید هر روز (هفته، ماه، سه ماهه و غیره) اجرا شوند و در عین حال، زمان زیادی را صرف می کنند. به نظر می رسد که این فرآیند خودکار است و نیازی به دخالت انسانی نیست، اما زمان لازم برای اجرای یک ماکرو می تواند ده ها دقیقه یا حتی چندین ساعت طول بکشد. زمان همانطور که می گویند پول است و در این پست سعی می کنم سرعت اجرای کلان شما را به میزان قابل توجهی افزایش دهم و شاید این کار تاثیر مثبتی بر تجارت شما و در نهایت پول داشته باشد.

قبل از شروع کار

قبل از اینکه مستقیماً به اصل مطلب بپردازم، می خواهم توجه شما را به این پست جلب کنم: چند نکته برای کار با VBA در اکسل. به طور خاص، در بلوک "شتاب ماکرو" وجود دارد مثال های مفیدکدی که باید همراه با نکات من برای سرعت بخشیدن به کار برای دستیابی به حداکثر نتیجه استفاده شود.

افزایش سرعت ماکرو

بنابراین، به نقطه ... به منظور واقعا سرعت بخشیدن به شغل VBAدر اکسل، باید بدانید که دسترسی به سلول روی یک برگه زمان زیادی می برد. اگر می خواهید یک مقدار را در یک سلول بنویسید، زمان زیادی طول نمی کشد، اما اگر نیاز به نوشتن (خواندن، ارجاع) به هزاران سلول داشته باشید، زمان بسیار بیشتری طول خواهد کشید. در چنین مواقعی چه باید کرد؟ آرایه ها به کمک می آیند. آرایه ها در حافظه ذخیره می شوند و VBA عملیات را در حافظه صدها یا حتی هزاران بار سریعتر انجام می دهد. بنابراین، اگر هزاران، صدها هزار مقدار در داده های خود داشته باشید، زمان اجرای ماکرو می تواند از چند دقیقه تا چند ساعت طول بکشد و اگر این داده ها را به یک آرایه منتقل کنید، اجرای ماکرو را می توان به کاهش داد. چند ثانیه (دقیقه).

من یک کد نمونه ارائه می کنم و در نظرات توضیح می دهم که چیست، واضح تر خواهد بود. علاوه بر این، برخی از خطوط کد که مستقیماً به فرآیند شتاب مرتبط نیستند ممکن است مفید باشند.

مثال

فرض کنید ما داده هایی را در "Sheet1" ("Sheet1") داریم. داده ها در 50 ستون (ستون ها حاوی عناوین) و 10000 ردیف هستند. به عنوان مثال، باید مقداری را در آخرین ستون وارد کنیم که برابر با مقدار ستون دوم تقسیم بر مقدار ستون سوم باشد (از خط دوم شروع می شود، زیرا اولین شامل عنوان است). سپس 10 ستون اول را می گیریم و آنها را برای پردازش بیشتر (برای سایر نیازها) در "Sheet2" ("Sheet2") کپی می کنیم. اگرچه مثال پیش پا افتاده است، اما به نظر من می تواند تمام ماهیت این پست را منعکس کند.

برای تنظیم اولیه متغیرها، این گزینه را فعال کنید. ) Dim i As Long" آرایه ای که داده های ما در آن ذخیره می شود Dim R_data As Variant "متغیرهای آخرین سطر و ستون Dim FinalRow, FinalColumn As Long "شما می توانید برگه را نه با نام، بلکه با شماره سریال مقداردهی اولیه کنید" Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) رشته خالیدر ستون اول "لازم است که داده ها فیلتر نشوند، در غیر این صورت آخرین ردیف آخرین ردیف در فیلتر خواهد بود" همچنین در ردیف آخر، در ستون اول، سلول خالی نباشد. البته اگر اصلا داده ای در این خط وجود داشته باشد. در غیر این صورت، آخرین ردیف آخرین سلول غیر خالی خواهد بود. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row "=10,000 "آخرین ستون غیر خالی را در سطر اول بیابید FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft ستون "=50 "محدوده داده در برگه 1 را به آرایه R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) اختصاص دهید برای i = 2 به FinalRow" عملیات داده ای که ما نیاز داریم. "بررسی کنید که تقسیم بر صفر وجود ندارد. فرض بر این است که ستون‌های 2 و 3 داده‌های عددی هستند.<>0 سپس R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i "داده‌ها را از آرایه به Sheet1 کپی کنید قبل از آن، داده‌های روی برگه را پاک کنید (اگر وجود دارد قالب بندی یا فرمول ها، پس بهتر است Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn) "Copy first_Column) 10 ستون Sheet2_WS.Range( Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data "کتاب کار را ببندید و آن را ذخیره کنید Workbooks(Application.ThisWorkbook.Name). End SaveChanges را ببندید:=TruChanges:=TruChanges

که در این مثالآرایه با محدوده مشخص شده پر شده است. اگر صریح داشته باشیم آرایه دو بعدی، سپس می توانید مقدار آن را به این ترتیب در برگه کپی کنید:

Dim R_new() As Variant ............................................ ... " به صراحت اندازه آرایه ReDim R_new(1 به FinalRow، 1 تا 50) را به عنوان متغیر مشخص کنید .......................... ...... ........... Sheet1_WS.Range(Sheet1_WS.Cells(1,1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()

نتیجه

اکثر عملیات روی داده ها را می توان در یک آرایه انجام داد، در حالی که فقط نتیجه را روی صفحه نمایش می دهد. گاهی اوقات منطقی است که نتیجه را در یک برگه نمایش دهید، سپس برخی از اقدامات (مانند مرتب سازی) را انجام دهید و دوباره داده ها را در یک آرایه بارگذاری کنید.

برای من، سرعت بخشیدن به کار ماکرو به دلیل آرایه ها، شگفتی بزرگی بود، زیرا داده های روی برگه ها، در واقع، از قبل یک آرایه دو بعدی هستند. اما معلوم شد که دسترسی به حافظه بسیار سریعتر از دسترسی به سلول های یک صفحه است.

در آینده قصد دارم نکاتی (نمونه‌هایی) را بنویسم جستجوی سریعداده های موجود در برگه، اما این یک پست دیگر خواهد بود. اگر سوالی دارید، نظری دارید، لطفا بنویسید.

با تشکر از توجه شما. توسعه مبارک.

اقتصاددانان معمولاً حدود نیمی از زمان کار خود را در آنجا می گذرانند برنامه اکسل. در عین حال، برای بسیاری از ما، ارتباط با برنامه دردسرهای زیادی ایجاد می کند، زیرا. کارایی عملیات روتینزمان زیادی می برد بیایید در مورد نحوه کار سریعتر در اکسل صحبت کنیم.

عملا هیچ جایگزینی برای Excel وجود ندارد. جای تعجب نیست، زیرا اکسل به شما امکان می دهد تقریباً همه چیز را انجام دهید. برای یک هنرمند مانند سه پایه خالی است. شما روی آن می نشینید و شروع به کشیدن نقاشی خود می کنید.

یک اسمیر، یک اسمیر دیگر... چند ساعت می گذرد و... چند صفحه کوچک و یک نمودار با طرح استاندارد. غم انگیز نیست؟

و برای من اتفاق افتاد. شما کار می کنید، شما کار می کنید. در حال حاضر خسته شده است، و کار تازه شروع شده است. با آن چه کار باید کرد؟

یک خروجی وجود دارد! درست است که همه در مورد او نمی دانند و بنابراین باید در محل کار خود بمانند، گردهمایی با دوستان، قرار ملاقات با عزیزان را به تعویق بیندازند و فقط در محل کار خسته شوند. من شما را نمی دانم، اما برای من مناسب نیست، و امروز اسرار سرعت کار در اکسل را به شما می گویم.

جالب هست؟ پس بیا بریم!

اولین کاری که باید انجام دهید این است که ماوس را بیرون بیندازید!

اینطوری که درست خواندید. برای کار سریعتر در اکسل، باید ماوس را حذف کنید. فقط برای بستن برگه عجله نکنید، اکنون همه چیز روشن خواهد شد.

من می فهمم که موش برای شما بسیار عزیز است. شما نمی توانید بدون او زندگی کنید، اما باور کنید، این اوست که شما را متوقف می کند. من دیوانه نیستم، فقط می دانم خیلی راحت تر و راه سریعحرکت صفحات اکسلو به سادگی از این ابزار فوق العاده استفاده نکنید.

همه چیز در مورد کلیدهای داغ است که به شما امکان می دهد به سادگی بین صفحات با سرعت رعد و برق جابه جا شوید، ساختمان ها / ستون ها را انتخاب کنید، اضافه یا حذف کنید. به طور کلی، دانش به معنای واقعی کلمه 5-7 ترکیب، کار را با یک مرتبه بزرگی سرعت می بخشد. بسیاری از عملیات ناوبری ماوس 3-5 یا حتی 10 برابر بیشتر طول می کشد.

من برای شما یک مثال می زنم. برای افزودن چند ردیف در اکسل، سه گزینه وجود دارد:

1. انتخاب 1.ماوس را بردارید، آن را روی ستون مربوطه یا چند ستون، بسته به تعداد ستون هایی که باید وارد کنید، نگه دارید. کلیک کلیک راسترا در کادر بازشوی Paste انتخاب کنید.
2. گزینه 2.با استفاده از دکمه های تب Home در گروه Cells. دوباره انتخاب کنید، به تب Home بروید. برای دریافت نتیجه روی دکمه های مربوطه کلیک کنید.
3. گزینه 3.برای برجسته کردن یک ستون فشار دهید. Ctrl + 'Space'، برای افزودن یک ستون کلیک کنید Ctrl+'+'.

می توان گفت که پس انداز زیاد نیست. در یک راه من آن را در 15-20 ثانیه انجام خواهم داد، در دیگری در 3-5. و با 12-15 ثانیه آزاد شده چه کار خواهم کرد؟ و به طور کلی، برای احساس تفاوت به چند کلید داغ نیاز دارید؟

موافق! زمان ذخیره شده توسط یک کلید داغعالی نیست، اما تمام کارهای ما در اکسل تکرار یکنواخت عملیات مشابه صدها بار در توالی های مختلف است. یعنی اگر صرفه جویی مشروط در یک عملیات 10 ثانیه فرض کنیم. هنگامی که یک عملیات را 20-30 بار در روز تکرار می کنید، صرفه جویی در دانستن یک ترکیب کلیدهای داغ 3-5 دقیقه است. پس دانستن 10 کلید داغ 30-50 دقیقه در زمان صرفه جویی می کند!!! حالا که باحاله!!!

نکته شماره دو از نکته اول پیروی می کند - کلیدهای میانبر را یاد بگیرید. کلیدهای داغ چندان مفیدی وجود ندارد، بنابراین پس از یادگیری 10-20 ترکیب، به سرعت تفاوت سرعت را احساس خواهید کرد.

"نصیحت مفید!" - اعتراض می کنی "و چگونه به آنها آموزش می دهید؟ روی چهارپایه جلوی همکاران بایستید و مثل شعر بگویید؟ 🙂

خیر البته اینطور نیست. سعی کنید راه خود را برای یادگیری کلیدهای فوری پیدا کنید. برای این چه کاری می توان انجام داد؟

برای مثال، می‌توانید فهرستی از کلیدهای میانبر تهیه کنید و 3 تا 5 عملکرد پرکاربرد را انتخاب کنید. سعی کنید آنها را حل کنید، نحوه کار آنها را تمرین کنید. عادت کنید وقتی به آنها نیاز دارید فشار دهید. با گذشت زمان، انگشتان خود روی صفحه کلید قرار می گیرند تا بتوانید سریع و راحت بین برگه ها جابه جا شوید. اینها را یاد گرفت - به بعدی بروید، و سپس دیگری و دیگری.

و البته، در اینجا لیستی از میانبرها و کلیدهای میانبر مفید وجود دارد:

کلیدهای فوری به شما کمک می کنند تا در اکسل سریعتر کار کنید. در استفاده از آنها آموزش دهید و خیلی سریع سرعت کار شما به میزان قابل توجهی افزایش می یابد. اما این همه ماجرا نیست!

من چند نکته دیگر در انبار دارم. آماده؟ برو!

نکته اکسل شماره سه. توابع و ترکیبات آنها که باید به آنها تسلط داشته باشید

ممکن است پیش پا افتاده به نظر برسد، اما برای کار سریعتر در اکسل باید عملکردها را بدانید. من مطمئن هستم که شما خواننده عزیز به عملکرد اکسل مسلط هستید و می توانید گزارش ها و محاسبات بسیار جالبی را در اکسل انجام دهید. با این حال، ممکن است که همه عملکردها تابع شما نباشند، و چیزی برای تلاش وجود دارد.

همچنین، هر تابع می تواند ویژگی های استفاده زیادی داشته باشد. بنابراین، برای مثال، آیا می دانید که تابع SUM می تواند مقادیر را از آن جمع کند ورق های مختلفکتاب شما و نیازی نیست که هر کدام را جداگانه برجسته کنید؟ آن ها

به جای فرمول

=SUM(Sheet1!A1; Sheet2!A1; Sheet3!A1; Sheet4!A1; Sheet5!A1; Sheet6!A1; Sheet7!A1; ... SheetN!A1)

فرمول شبیه خواهد بود

=SUM(Sheet1:SheetN!A1)

این همه برای چیست؟ علاوه بر قابلیت های غنی اکسل که در آن وجود دارد فرمول های استاندارد، ترکیبات زیادی وجود دارد که دانستن آنها به شخص اجازه حل می دهد و نه وظایف استاندارد. بنابراین، اکسل اصلا تابع MINESLI ندارد. بله، SUMMESLI، COUNTIF وجود دارد، اما MINESLI وجود ندارد. همچنین MAXIF، MEDIANIF و غیره، اما همه اینها با استفاده از توابع ناحیه حل می شود. ممکن است دیده باشید که یک فرمول در بریس های مجعد پیچیده می شود.

برخی از توابع فقط در ارتباط خوب کار می کنند. این چیزی است که من اکنون در مورد INDEX و SEARCH صحبت می کنم. به نظر می رسد که عملکردهای احمقانه به صورت جداگانه انجام می شود، اما به صورت جفت آنها عملکرد عالی را ارائه می دهند.

چه چیزی برای این مورد نیاز است؟ به عنوان مثال، در گروه ما مشترک شوید فیس بوکو منتظر پست های جدید باشید 😉

همچنین لیست فرمول هایی را که توصیه می کنم به آنها توجه کنید را مشاهده کنید:

توابع بسیار بیشتری در اکسل وجود دارد، اما فعلاً می‌توانید با آنها مقابله کنید. من مطمئن هستم که اکثر آنها به شما کمک می کنند تا در اکسل سریعتر کار کنید و حداقل به طور قابل توجهی کارآمدتر باشید.

نکته اکسل شماره چهار. ساختار و قالب بندی در فایل ها

اگر با اصول مدل‌سازی برخورد کرده‌اید، پس واضح است که می‌دانید برای جلوگیری از خطا، کافی است همه چیز را در محاسبات خود مرتب کنید.

محاسبات گیج کننده، انباشته شدن و ناوبری ناخوشایند منجر به این واقعیت می شود که حتی خود نویسنده شروع به گمراهی در پرونده می کند.

چطور می توان از این اجتناب کرد؟ من به شما توصیه هایی می کنم:

• فایل را فرمت کنید. به شما اجازه می دهد تعداد ارقام اعشاری یکسان، فونت یکسان در سراسر سند و محدود داشته باشید پالت رنگ. من اکنون دوست دارم از پالت هایی با همان رنگ اما تن های مختلف (آبی روشن، آبی و آبی تیره) استفاده کنم. بسیار شیک به نظر می رسد.
• داده های ورودی، محاسبات و نتایج را جدا کنید. این همیشه ضروری نیست، اما زمانی که داده های زیادی وجود دارد، وجود یک صفحه خلاصه به سادگی باعث صرفه جویی می شود.
• همان فرمول ها را با ستون یا سطر انجام دهید. موافقم که وقتی همان شاخص وارد می شود خیلی درست نیست دوره های مختلفمتفاوت در نظر گرفته شود. در این مورد، معمولاً تا زمانی که به فرمول نگاه نکنید، این قابل مشاهده نیست. و گاهی اوقات می توانید فراموش کنید
• سعی کنید اجتناب کنید مراجع دایره ایو لینک به فایل های خارجی. معمولا نتایج متفاوتی می دهند کامپیوترهای مختلف. موردی وجود داشت که خطای به روز رسانی رخ داد لینک های خارجیبیش از 7 صفر بود. ای!
• ساده سازی و صرفه جویی در فضا. اکسل به شما این امکان را می دهد که هر تعداد سطر و ستون بسازید. در 10 سال کار با این برنامه، هرگز از یک برگه به ​​طور کامل استفاده نکردم. بعید است که موفق شوید، بنابراین فرمول های پیچیده را حصار نکنید. بهتر است محاسبه را در چند مرحله انجام دهید.

در اینجا چند نکته وجود دارد. باور کنید زندگی را آسان تر می کنند. خطاهای کمتری وجود دارد، محاسبات دقیق تر و واضح تر هستند. اگر نکته دیگری دارید در نظرات بنویسید.

در این توصیه من اصلا اصل نیستم. از قضا، دستیابی به تسلط به شما بستگی دارد و اینکه چقدر و چقدر تمرین می کنید.

من می خواهم یک دوره فشرده 28 دقیقه ای بگذارم که بعد از آن همه بدانند اکسل بهترهاز آنچه توسعه دهندگان آن می دانند، اما افسوس. این اتفاق نمی افتد. کار مداوم و سخت منجر به نتایج واقعاً چشمگیر می شود. به همین دلیل است که به زودی آموزش اکسل را راه اندازی می کنیم که می تواند به صورت رایگان در وب سایت ما تکمیل شود (برای حداقل 1000 کاربر اول).

پاولوف نیکولای

در این مقاله، من می خواهم بیشتر به شما ارائه دهم تکنیک های موثرکار در مایکروسافت اکسل، جمع آوری شده توسط من در طول 10 سال گذشته کار بر روی پروژه ها و انجام آموزش های مربوط به این برنامه فوق العاده. در اینجا هیچ توضیحی در مورد فن آوری های فوق پیچیده وجود ندارد، اما برای هر روز ترفندهایی وجود دارد - ساده و موثر، بدون "آب" توصیف شده است - فقط "بقایای خشک". بیشتر این مثال‌ها بیش از یک یا دو دقیقه طول نمی‌کشد تا تسلط پیدا کنید، اما شما را بسیار بیشتر نجات می‌دهند.

پرش سریع به برگه مورد نظر

آیا اتفاقاً با آن کار می کنید کتاب های اکسل، شامل تعداد زیادیورق؟ اگر بیش از ده مورد از آنها وجود داشته باشد، هر انتقال به صفحه مورد نظر بعدی به خودی خود یک مشکل کوچک می شود. یک راه حل ساده و ظریف برای این مشکل این است که در گوشه سمت چپ پایین پنجره روی دکمه های پیمایش برگه ها نه با سمت چپ، بلکه با دکمه سمت راست ماوس کلیک کنید - فهرست مطالب کتاب با ظاهر می شود. لیست کاملتمام برگه ها و می توانید در یک حرکت به برگه مورد نظر بروید:

این بسیار سریعتر از پیمایش در برگه های برگه با همان دکمه ها برای جستجوی آنچه نیاز دارید است.

بدون آسیب به قالب کپی کنید

چند صدها (هزاران؟) بار این تصویر را دیده‌ام که در طول آموزش پشت سر شنوندگانم ایستاده است: کاربر فرمول را در سلول اول وارد می‌کند و سپس آن را به کل ستون "کشش" می‌کند و قالب‌بندی ردیف‌های زیر را می‌شکند. زیرا این روش نه تنها فرمول، بلکه قالب سلول را نیز کپی می کند. بر این اساس، در ادامه لازم است آسیب به صورت دستی اصلاح شود. یک ثانیه برای کپی و سپس 30 - برای تعمیر طرح خراب شده توسط کپی.

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

فقط سلول های قابل مشاهده را کپی کنید

اگر بیش از یک هفته است که در مایکروسافت اکسل کار می کنید، حتما قبلاً با آن مواجه شده اید مشکل مشابه: در برخی موارد، هنگام کپی پیست سلول‌ها، تعداد سلول‌هایی که در نگاه اول کپی شده‌اند بیشتر می‌شود. اگر محدوده کپی شده شامل سطرها/ستون‌های پنهان، گروه‌بندی، جمع‌های فرعی یا فیلتر باشد، این اتفاق می‌افتد. بیایید یکی از این موارد را به عنوان مثال در نظر بگیریم:

در این جدول، مجموع‌های فرعی محاسبه می‌شوند و ردیف‌ها بر اساس شهر گروه‌بندی می‌شوند - این موضوع با دکمه‌های مثبت و منفی در سمت چپ جدول و با شکستن شماره‌گذاری ردیف‌های قابل مشاهده آسان است. در صورت انتخاب، داده ها را از این جدول کپی و جایگذاری کنید به روش معمول، سپس 24 خط اضافی دریافت می کنیم. ما می خواهیم فقط مجموع ها را کپی و جایگذاری کنیم!

می توانید با برجسته کردن دقیق هر ردیف از مجموع در حالی که نگه دارید، مشکل را حل کنید کلید CTRL- در مورد برجسته کردن محدوده های غیر پیوسته. اما اگر سه یا پنج خط وجود نداشته باشد، بلکه چند صد یا هزاران خط وجود داشته باشد، چه؟ راه دیگری، سریعتر و راحت تر وجود دارد:

محدوده مورد نظر برای کپی را انتخاب کنید (در مثال ما A1:C29 است)

کلید F5 روی کیبورد و سپس دکمه Special را در پنجره باز شده فشار دهید.
پنجره ای ظاهر می شود که به کاربر امکان می دهد نه همه چیز، بلکه فقط سلول های لازم را انتخاب کند:

در این پنجره گزینه Only Visible Cells (Visiblecell only) را انتخاب کرده و OK کنید.

انتخاب به دست آمده اکنون می تواند با خیال راحت کپی و جایگذاری شود. در نتیجه دقیقاً یک کپی از سلول های قابل مشاهده دریافت می کنیم و به جای 29 خط غیر ضروری، فقط 5 خط مورد نیاز خود را وارد می کنیم.

اگر این ظن وجود دارد که مجبور خواهید بود چنین عملیاتی را اغلب انجام دهید، پس منطقی است که به پانل اضافه کنید ابزارهای مایکروسافت دکمه اکسلبرای میانبرچنین عملکردی این کار را می توان از طریق منوی Tools> Customize انجام داد، سپس به تب Commands بروید، در دسته Edit، دکمه انتخاب سلول های قابل مشاهده را پیدا کنید و آن را با ماوس به نوار ابزار منتقل کنید:

تبدیل ردیف ها به ستون و بالعکس

یک عملیات ساده، اما اگر نمی دانید چگونه آن را به درستی انجام دهید، می توانید نیم روز را صرف کشیدن و رها کردن سلول های جداگانه کنید:

در واقع همه چیز ساده است. در آن بخش از ریاضیات عالی که ماتریس ها را توصیف می کند، مفهوم جابجایی وجود دارد - عملی که ردیف ها و ستون ها را در یک ماتریس با یکدیگر تعویض می کند. در مایکروسافت اکسل این کار در سه مرحله انجام می شود: جدول را کپی کنید

روی یک سلول خالی کلیک راست کرده و Paste Special را انتخاب کنید

در پنجره ای که باز می شود، پرچم Transpose (Transpose) را تنظیم کرده و روی OK کلیک کنید:

به سرعت داده ها را به نمودار اضافه کنید

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

راه دیگر - ساده، سریع و زیبا - این است که سلول‌هایی را با داده‌های جدید انتخاب کنید، آنها را کپی کنید (CTRL+C) و (CTRL+V) را مستقیماً در نمودار جای‌گذاری کنید. اکسل 2003، بر خلاف بیشتر نسخه های بعدی، حتی از قابلیت کشیدن یک محدوده انتخاب شده از سلول ها با داده و رها کردن مستقیم آن در نمودار با ماوس پشتیبانی می کند!

اگر می خواهید تمام تفاوت های ظریف و ظرافت ها را کنترل کنید، می توانید از معمول استفاده نکنید، اما درج ویژهبا انتخاب Edit> Paste Special از منو. در این مورد، مایکروسافت اکسل کادر محاوره ای را نمایش می دهد که به شما امکان می دهد مکان و نحوه دقیق اضافه شدن داده های جدید را پیکربندی کنید:

به طور مشابه، می توانید به راحتی نموداری را با استفاده از داده های موجود ایجاد کنید جداول مختلفاز ورق های مختلف برای همین کار به روش کلاسیکزمان و تلاش بسیار بیشتری را می طلبد.

پر کردن سلول های خالی

پس از تخلیه گزارشات از برخی برنامه ها در فرمت اکسلیا هنگام ایجاد جداول محوریکاربران اغلب جدول هایی با سلول های خالی در برخی از ستون ها دریافت می کنند. این حذفیات اجازه معمول و ابزارهای مفیدنوع فیلتر خودکار و مرتب سازی به طور طبیعی، نیاز به پر کردن فضای خالی با مقادیر سلول های بالاتر وجود دارد:

البته با حجم کم داده می توان این کار را به راحتی انجام داد. کپی ساده- با کشیدن دستی هر سلول بزرگ در ستون A به سمت پایین سلول های خالی. و اگر جدول دارای چند صد یا هزار ردیف و چند ده شهر باشد؟

راهی برای حل سریع و زیبا این مشکل با یک فرمول وجود دارد:

تمام سلول های ستون خالی را انتخاب کنید (یعنی محدوده A1:A12 در مورد ما)

برای اینکه فقط سلول های خالی در انتخاب باقی بماند، کلید F5 را فشار دهید و در پنجره ناوبری که باز می شود، دکمه انتخاب را بزنید. پنجره ای خواهید دید که به شما امکان می دهد سلول هایی را که می خواهیم برجسته کنیم را انتخاب کنید:

دکمه رادیویی را روی Blank قرار دهید و روی OK کلیک کنید. اکنون فقط سلول های خالی باید در انتخاب باقی بمانند:

بدون تغییر انتخاب، i.e. بدون لمس ماوس، فرمول را در اولین سلول انتخاب شده (A2) وارد کنید. علامت تساوی و سپس فلش رو به بالا را روی صفحه کلید خود فشار دهید. فرمولی دریافت می کنیم که به سلول قبلی اشاره دارد:

برای وارد کردن فرمول ایجاد شده در تمام سلول های خالی انتخاب شده به طور همزمان، فشار دهید کلید ورود، آ ترکیبی CTRL+ENTER. فرمول تمام سلول های خالی را پر می کند:

اکنون تنها جایگزینی فرمول ها با مقادیر برای رفع نتایج باقی مانده است. محدوده A1:A12 را انتخاب کنید، آن را کپی کنید و با استفاده از Paste Special در سلول ها با مقادیر آنها پیست کنید.

لیست کشویی در یک سلول

ترفندی که بدون اغراق همه کسانی که در اکسل کار می کنند باید بدانند. استفاده از آن می تواند تقریباً هر جدولی را بدون توجه به هدف آن بهبود بخشد. در تمام تمرینات سعی می کنم روز اول آن را به شنوندگانم نشان دهم.

این ایده بسیار ساده است - در همه مواردی که باید داده‌ها را از مجموعه‌ای به جای وارد کنید ورودی دستیبه یک سلول با استفاده از صفحه کلید برای انتخاب مقدار مورد نظرماوس از لیست کشویی:

انتخاب یک محصول از لیست قیمت، نام مشتری از پایگاه مشتری، نام کامل کارمند از لیست کارکنان و غیره. کاربردهای زیادی برای این ویژگی وجود دارد.

برای ایجاد یک لیست کشویی در یک سلول:

سلول هایی را که می خواهید یک لیست کشویی ایجاد کنید را انتخاب کنید.

اگر اکسل 2003 یا بالاتر دارید، سپس از منو Data> Validation را انتخاب کنید. اگر اکسل 2007/2010 دارید، به تب Data بروید و روی دکمه اعتبارسنجی داده کلیک کنید.

در پنجره باز شده گزینه List را از لیست کشویی انتخاب کنید.

در قسمت Source، باید مقادیری را که باید در لیست باشد، مشخص کنید. در اینجا گزینه های احتمالی وجود دارد:

گزینه های متنی را در این قسمت که با نقطه ویرگول از هم جدا شده اند وارد کنید

اگر محدوده سلول ها با مقادیر اولیه در صفحه فعلی باشد، کافی است آن را با ماوس انتخاب کنید.

اگر او در برگه دیگری از این کتاب باشد، باید از قبل یک نام بگذارد (سلول ها را انتخاب کنید، CTRL + F3 را فشار دهید، نام محدوده را بدون فاصله وارد کنید) و سپس این نام را در فیلد بنویسید.