چگونه کارهای روتین را در اکسل با ماکروها خودکار کنیم

• ماکرو چیست؟
• ایجاد یک ماکرو - یک مثال عملی
• اجرای ماکرو در اکسل
• بیایید به زیر کاپوت نگاه کنیم: ماکرو چگونه کار می کند؟
• بیایید یک مرحله دیگر به وظیفه خود اضافه کنیم…

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

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

ماکرو چیست؟

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

ماکروها می توانند تقریباً هر کاری را که در یک سند می خواهید انجام دهند. در اینجا برخی از آنها (بخش بسیار کوچک) وجود دارد:

• اعمال سبک ها و قالب بندی
• انجام عملیات های مختلف با داده های عددی و متنی.
• استفاده از منابع داده خارجی (فایل های پایگاه داده، اسناد متنی و غیره)
• سند جدیدی ایجاد کنید.
• تمام موارد بالا را در هر ترکیبی انجام دهید.

ایجاد یک ماکرو - یک مثال عملی

به عنوان مثال، بیایید رایج ترین فایل را در نظر بگیریم CSV. این یک جدول ساده 10×20 است که با اعداد از 0 تا 100 با عنوان ستون ها و سطرها پر شده است. وظیفه ما این است که این مجموعه داده را به یک جدول با فرمت فعلی تبدیل کنیم و مجموعات را در هر ردیف ایجاد کنیم.

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

برای ایجاد یک ماکرو، باز کنید چشم انداز (تایپ کنید) > ماکرو (ماکرو) > ضبط ماکرو (ضبط ماکرو…)

ماکرو خود را یک نام بگذارید (بدون فاصله) و کلیک کنید OK.

از این لحظه، تمام اقدامات شما با سند ثبت می شود: تغییرات در سلول ها، پیمایش در جدول، حتی تغییر اندازه پنجره.

اکسل سیگنال می دهد که حالت ضبط ماکرو در دو مکان فعال است. ابتدا در منو ماکرو (ماکروها) - به جای یک رشته ضبط ماکرو (در حال ضبط یک ماکرو…) خط ظاهر شد توقف ضبط (ضبط را متوقف کنید).

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

اکنون که حالت ضبط ماکرو فعال است، بیایید به وظیفه خود برسیم. اول از همه، اجازه دهید هدر برای داده های خلاصه اضافه کنیم.

در مرحله بعد، فرمول ها را مطابق با نام سرفصل ها در سلول ها وارد کنید (انواع فرمول های انگلیسی و نسخه های Excel داده شده است، آدرس سلول ها همیشه حروف و اعداد لاتین هستند):

• =SUM(B2:K2) or =SUM(B2:K2)
• =متوسط ​​(B2:K2) or =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) or =MIN(B2:K2)
• =MAX(B2:K2) or =MAX(B2:K2)
• =MEDIAN(B2:K2) or =MEDIAN(B2:K2)

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

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

در مرحله بعد، نتایج را برای کل جدول خلاصه می کنیم، برای این کار چند عملیات ریاضی دیگر انجام می دهیم:

به ترتیب:

• =SUM(L2:L21) or =SUM(L2:L21)
• =متوسط ​​(B2:K21) or =СРЗНАЧ(B2:K21) – برای محاسبه این مقدار باید دقیقاً داده های اولیه جدول گرفته شود. اگر میانگین میانگین ها را برای ردیف های جداگانه بگیرید، نتیجه متفاوت خواهد بود.
• =MIN(N2:N21) or =MIN(N2:N21)
• =MAX(O2:O21) or =MAX(O2:O21)
• =MEDIAN(B2:K21) or =MEDIAN(B2:K21) - به دلیل ذکر شده در بالا، از داده های اولیه جدول استفاده می کنیم.

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

بعد، ظاهر سرصفحه های ستون و ردیف را تغییر دهید:

• سبک فونت پررنگ
• تراز وسط.
• پر رنگ.

و در نهایت، اجازه دهید قالب کل را تنظیم کنیم.

در پایان باید اینگونه باشد:

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

تبریک می گویم! شما به تازگی اولین ماکرو خود را در اکسل ثبت کرده اید.

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

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

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

یک نکته مهم! اگر فایل را با پسوند ذخیره کنید XLTX، پس ماکرو در آن کار نخواهد کرد. ضمناً می توانید کتاب کار را به عنوان یک قالب اکسل 97-2003 که دارای فرمت است ذخیره کنید. XLT، از ماکروها نیز پشتیبانی می کند.

هنگامی که الگو ذخیره می شود، می توانید با خیال راحت اکسل را ببندید.

اجرای ماکرو در اکسل

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

• ماکروها می توانند مضر باشند.
• پاراگراف قبلی را دوباره بخوانید.

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

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

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

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

وقتی وارد کردن تمام شد، به منو بروید ماکرو برگه (ماکروها). چشم انداز (مشاهده) و یک فرمان را انتخاب کنید مشاهده ماکروها (ماکرو).

در کادر محاوره ای که باز می شود، خطی با نام ماکرو خود مشاهده می کنیم FormatData. آن را انتخاب کرده و کلیک کنید دویدن (اجرا کردن).

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

بیایید به زیر کاپوت نگاه کنیم: ماکرو چگونه کار می کند؟

همانطور که بارها گفته شد، ماکرو کد برنامه در یک زبان برنامه نویسی است. ویژوال بیسیک برای برنامه های کاربردی (VBA). هنگامی که حالت ضبط ماکرو را روشن می کنید، اکسل در واقع هر اقدامی را که انجام می دهید در قالب دستورالعمل های VBA ثبت می کند. به عبارت ساده، اکسل کد را برای شما می نویسد.

برای دیدن این کد برنامه، باید در منو ماکرو برگه (ماکروها). چشم انداز (مشاهده) کلیک کنید مشاهده ماکروها (ماکروها) و در کادر محاوره ای باز شده کلیک کنید ویرایش (تغییر دادن).

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

بیایید یک مرحله دیگر به وظیفه خود اضافه کنیم…

تصور کنید که فایل داده اصلی ما data.csv به طور خودکار توسط برخی فرآیندها ایجاد می شود و همیشه روی دیسک در همان مکان ذخیره می شود. مثلا، C:Datadata.csv – مسیر فایل با داده های به روز شده روند باز کردن این فایل و وارد کردن داده ها از آن را می توان در یک ماکرو نیز ثبت کرد:

1. فایل قالب را که ماکرو − را در آن ذخیره کرده بودیم باز کنید FormatData.
2. یک ماکرو جدید با نام ایجاد کنید LoadData.
3. هنگام ضبط ماکرو LoadData وارد کردن داده ها از فایل data.csv – همانطور که در قسمت قبلی درس انجام دادیم.
4. وقتی وارد کردن کامل شد، ضبط ماکرو را متوقف کنید.
5. تمام داده ها را از سلول ها حذف کنید.
6. فایل را به عنوان یک الگوی اکسل با قابلیت ماکرو (پسوند XLTM) ذخیره کنید.

بنابراین، با اجرای این الگو، شما به دو ماکرو دسترسی خواهید داشت – یکی داده ها را بارگذاری می کند، دیگری آنها را قالب بندی می کند.

اگر می‌خواهید وارد برنامه‌نویسی شوید، می‌توانید اقدامات این دو ماکرو را با هم ترکیب کنید - به سادگی با کپی کردن کد از LoadData به ابتدای کد FormatData.