فرمول های اکسل قدرتمند هستند ، اما محدودیت های خود را دارند. من قبلاً به فرمول های پیچیده تو در تو اعتماد می کردم که برای ساختن آن برای همیشه انجام می شود و عیب یابی آن دشوار است ، اما به جای آن شروع به تکیه بیشتر به ماکروها کردم. اینگونه نیست که فرمول ها بد هستند – آنها برای محاسبات دقیق و دستکاری داده ها عالی هستند. اما هنگامی که با کارهای تکراری که شامل چندین مرحله ، قالب بندی تغییرات یا اقداماتی است که باید در برگه های مختلف اتفاق بیفتد ، ماکروها فقط بیشتر حس می کنند.
فرمول های اکسل قدرتمند هستند ، اما محدودیت های خود را دارند. من قبلاً به فرمول های پیچیده تو در تو اعتماد می کردم که برای ساختن آن برای همیشه انجام می شود و عیب یابی آن دشوار است ، اما به جای آن شروع به تکیه بیشتر به ماکروها کردم. اینگونه نیست که فرمول ها بد هستند – آنها برای محاسبات دقیق و دستکاری داده ها عالی هستند. اما هنگامی که با کارهای تکراری که شامل چندین مرحله ، قالب بندی تغییرات یا اقداماتی است که باید در برگه های مختلف اتفاق بیفتد ، ماکروها فقط بیشتر حس می کنند.
بهترین بخش این است که ماکروها فرمول های چیزهایی را کنترل نمی کنند. در حالی که یک فرمول ممکن است مقادیر را محاسبه کند ، یک ماکرو می تواند داده ها را در یک برگه دیگر محاسبه ، قالب بندی کند ، یک ایمیل ارسال کند و یک نمودار را به صورت یک بار ایجاد کند. بنابراین ، هنگامی که متوجه شدید وقت خود را در کارهای دستی که می تواند خودکار باشد ، هدر می دهید ، تعویض آشکار می شود.
چگونه اولین کلان خود را در اکسل ضبط کنیم
با ضبط کلان داخلی اکسل شروع کنید
ضبط یک کلان در اکسل ساده است – شما اساساً به اکسل می گویید که چه کاری انجام می دهید و بعداً آن را به یاد می آورید. ضبط کننده ماکرو هر کلیک ، کلید و اقدامی را که انجام می دهید ضبط می کند ، سپس آن را به کد Visual Basic برای برنامه های (VBA) تبدیل می کند که هر زمان که به آن نیاز دارید قابل پخش است.
قبل از شروع ضبط ، مهم است که دقیقاً کاری را که می خواهید کلان انجام دهید ، برنامه ریزی کنید. در هر مرحله فکر کنید زیرا ضبط کننده همه چیز را ضبط می کند ، از جمله اشتباهات و کلیک های غیر ضروری.
در حالی که می توانید از طریق برگه View به ضبط ماکرو دسترسی پیدا کنید ، ارزش دسترسی آسان تر به ابزارهای کلان را دارد. برگه توسعه دهنده به طور پیش فرض غیرفعال است ، اما روشن کردن آن دکمه های کلان اختصاصی و گزینه های پیشرفته تر را به شما می دهد.
برای فعال کردن برگه Developer ، به File> Options> Customize Ribbon بروید ، سپس کادر توسعه دهنده را در پنل سمت راست بررسی کرده و روی OK کلیک کنید. پس از فعال کردن ، دکمه های ماکرو را ضبط کرده و به همراه ابزارهایی برای ویرایش و مدیریت ماکروهای خود ، دکمه های ضبط را درست در برگه توسعه دهنده متوقف می کنید.
در اینجا نحوه شروع کار آورده شده است:
- به برگه توسعه دهنده روی روبان بروید و روی Record Macro کلیک کنید.
- در کادر گفتگو که ظاهر می شود ، یک نام توصیفی به ماکرو خود بدهید. از فضاها خودداری کنید – از آنها از این موارد تأکید کنید.
- اگر بعداً دسترسی سریع دارید ، یک کلید میانبر اختصاص دهید. چیزی مانند Ctrl + Shift + Q به خوبی کار می کند.
- انتخاب کنید که ماکرو را ذخیره کنید. اگر فقط برای پرونده فعلی به آن احتیاج دارید ، این کتاب کار را انتخاب کنید.
- شرح مختصری از آنچه ماکرو در قسمت توضیحات انجام می دهد اضافه کنید.
- برای شروع ضبط ، OK را کلیک کنید.
اکنون مراحل دقیقی را که می خواهید اکسل به خاطر بسپارید ، انجام دهید. پس از اتمام ، به برگه توسعه دهنده برگردید و روی Stop Recording کلیک کنید. این است
هنگام یادگیری با کارهای ساده شروع کنید. ضبط یک کلان که سلول ها را قالب بندی می کند یا داده ها را بین ورق ها کپی می کند ، بسیار ساده تر از تلاش برای اتوماسیون محاسبات پیچیده است.
ماکرو اکنون ذخیره شده و آماده استفاده است. می توانید آن را با استفاده از کلید میانبر اختصاص داده شده یا با پیمایش به Developer> Macros و انتخاب آن از لیست اجرا کنید.
ماکروهای رفتن من که جایگزین فرمول ها شد
سه ماکرو اساسی که تکراری ترین کارها را بر عهده دارند
با گذشت زمان ، من مجموعه ای از ماکروها را ساخته ام که وسایل تکراری را که قبلاً با آنها مبارزه می کردم ، اداره می کنند. اینها فانتزی یا پیچیده نیستند – آنها فقط راه حل های عملی هستند که مرا از انجام همان کار خسته کننده بارها و بارها نجات می دهند.
بیشترین استفاده کلان من به طور خودکار داده های وارداتی را تمیز می کند. من از آن برای حذف فضاهای اضافی و تمیز کردن داده ها با عملکرد trimrange ، تبدیل متن به مورد مناسب و استاندارد سازی قالب های تاریخ در چندین ستون استفاده می کنم.
یک کلان دیگر که من به داده های چند برگه در یک گزارش خلاصه تکیه می کنم. این محدوده های خاص را از برگه های مختلف به دست می آورد ، از قالب بندی مداوم استفاده می کند و یک مرور کلی تمیز ایجاد می کند. این یکی هنگام برخورد با گزارش های ماهانه که هر بار به همان ساختار احتیاج دارند ، مفید است.
من همچنین یک کلان دارم که قوانین قالب بندی مشروط را بر اساس معیارهای مختلف اعمال می کند – چیزی که هنگام تلاش برای ساختن آن با فرمول های تو در تو ، کثیف می شود. ماکرو می تواند به چندین ستون نگاه کند ، از طرح های مختلف رنگی استفاده کند و حتی بسته به مقادیری که پیدا می کند ، میله های داده یا نمادها را اضافه کند.
بهترین بخش در مورد این ماکروها این است که پس از راه اندازی ، هر کسی که در این تیم قرار دارد می تواند از آنها استفاده کند. لازم نیست منطق اساسی را درک کنید – فقط ماکرو را اجرا کنید و هر بار نتایج مداوم کسب کنید.
کمی VBA ماکرو را قدرتمندتر می کند
کدهای ساده که ماکروهای ضبط شده اساسی را تغییر می دهند
در حالی که ماکروهای ضبط شده از جعبه عالی کار می کنند ، یادگیری کمی برنامه نویسی Excel VBA آنها را به سطح بعدی می برد. شما نیازی به تبدیل شدن به یک متخصص برنامه نویسی ندارید – فقط درک چند مفهوم اساسی می تواند ماکروهای شما را باهوش تر و انعطاف پذیر تر کند.
ضبط کننده کلان کد عملکردی را ایجاد می کند ، اما اغلب ناکارآمد و انعطاف پذیر است. این مراجعات مطلق سلولی و بعضی اوقات دستورات اضافی را که لازم نیست ، ضبط می کند ، که می تواند همه چیز را کاهش دهد. با کمی دانش VBA ، می توانید ماکروهای خود را تغییر دهید تا دامنه های مختلف را به صورت پویا کنترل کنید و سریعتر اجرا کنید.
در اینجا نحوه شروع ویرایش ماکروهای ضبط شده خود آورده شده است:
- به توسعه دهنده> ماکرو بروید و کلان خود را انتخاب کنید.
- برای باز کردن ویرایشگر VBA ، روی ویرایش کلیک کنید.
- به دنبال منابع سلول های سخت مانند دامنه (“A1: C10”) باشید.
- آنها را با منابع پویا با استفاده از جریان region یا پایان (XLDown) جایگزین کنید.
- اضافه کردن خطای ساده را با رزومه خطا در مرحله بعدی اضافه کنید.
یکی از بزرگترین پیشرفت هایی که می توانید انجام دهید اضافه کردن ورودی کاربر است. به جای داشتن ماکروهای جداگانه برای سناریوهای مختلف ، از inputbox استفاده کنید تا کاربران محدوده یا معیارها را مشخص کنند. این یک کلان ضبط شده سفت و سخت به یک ابزار انعطاف پذیر تبدیل می شود.
اضافه کردن حلقه های اساسی همچنین ماکروها را قدرتمندتر می کند. یک ساده برای هر حلقه می تواند چندین برگه یا محدوده کار را بدون نیاز به ضبط اقدامات جداگانه برای هر یک پردازش کند.
ویرایشگر VBA ممکن است در ابتدا ارعاب کننده به نظر برسد ، اما کوچک شروع می شود. یک مرجع سلول را در اینجا تغییر دهید ، یک جعبه پیام را در آنجا اضافه کنید. هنگامی که می بینید چگونه این تغییرات کوچک ماکروهای شما را بهبود می بخشد ، می خواهید اطلاعات بیشتری کسب کنید.
جایی که ماکروها کوتاه می شوند
وقتی فرمول ها هنوز انتخاب بهتری هستند
ماکروها کامل نیستند ، و قطعاً موقعیت هایی وجود دارد که فرمول ها یا راه حل های دیگر بهتر کار می کنند. دانستن این محدودیت ها به شما کمک می کند تا ابزار مناسب برای هر کار را انتخاب کنید.
امنیت احتمالاً بزرگترین سردرد با ماکرو است. از آنجا که ماکرو ها می توانند کد مخرب را حمل کنند ، بسیاری از شرکت ها به طور پیش فرض آنها را غیرفعال می کنند تا امور ایمن باشند. این بدان معناست که اتوماسیون کلان محور شما ممکن است برای همه اجرا نشود ، مگر اینکه آنها بدانند که چگونه ماکروها را فعال کنند و بیایید با آن روبرو شویم ، همه نمی خواهند یا احساس راحتی کنند. وقتی پرونده ها را به اشتراک می گذارید ، ایده خوبی است که دستورالعمل های واضح در مورد چگونگی ایمن ماکرو را با خیال راحت درج کنید ، اما برای برخی از کاربران آماده شوید تا به یک دیوار ضربه بزنید.
ماکروها نمی توانند محاسبات در زمان واقعی را جایگزین کنند زیرا آنها فقط هنگامی اجرا می شوند که صریحاً آنها را شروع کنید. از طرف دیگر ، فرمول ها هر زمان که داده های شما تغییر می کند ، فوراً به روز کنید ، به همین دلیل وقتی می خواهید به زندگی ، و نتایج دوم ، بهترین انتخاب آنها باشند. بنابراین ، ماکروها را به عنوان راهی برای خودکارسازی وظایف فکر کنید ، نه جایگزین محاسبات روزمره خود.
عیب یابی ماکروهای شکسته می توانند ناامید کننده باشند ، به خصوص اگر با VBA راحت نیستید. وقتی فرمول شکسته می شود ، خطا معمولاً آشکار است. هنگامی که یک کلان کار را متوقف می کند ، می فهمید که چرا اغلب نیاز به حفر کد و آزمایش سناریوهای مختلف دارد.
عملکرد با مجموعه داده های بزرگ نیز به مسئله تبدیل می شود. یک کلان ضعیف نوشته شده می تواند اکسل را پایین بیاورد و برای همیشه تکمیل شود ، اما فرمول های خوب طراحی شده یا سایر ابزارهای مانند Power Query اغلب داده های مشابه را به راحتی انجام می دهند. اگر سرعت اولویت شماست ، از ترکیب ماکرو با این گزینه ها برای بهترین های هر دو جهان نترسید.
ماکروها جادویی نیستند ، اما نزدیک هستند
تعادل مناسب بین اتوماسیون و فرمولها را پیدا کنید
ماکروها جایگزین هر فرمول موجود در ابزارهای اکسل شما نمی شوند ، اما آنها برای انجام کارهای تکراری و چند مرحله ای که در غیر این صورت وقت شما را می خورد ، مفید هستند. کلید این است که بدانید چه موقع از هر ابزار استفاده کنید. فرمول ها برای محاسباتی که نیاز به بروزرسانی خودکار دارند مناسب هستند. ماکرو در گردش کار پیچیده ای که شامل چندین اقدامات در بخش های مختلف کتاب کار شما است ، برتری دارد.
با ضبط ماکرو ساده را شروع کنید ، سپس به تدریج ترفندهای VBA را اضافه کنید تا راحت تر شوید. مدتهاست که مجموعه ای از ابزارهای اتوماسیون سفارشی را خواهید داشت که باعث می شود اکسل دقیقاً به همان روشی که به آن نیاز دارید کار کند.