چگونه یک ماکرو آماده را در کتاب کار قرار دهیم؟ خودکار کردن وظایف با ضبط ماکرو - اکسل

اشیایی مانند ماکروها در اکسل به سازماندهی صحیح فرآیند کار در اکسل کمک می کنند.

اجازه دهید تمام ویژگی های کار با این اشیاء را در بسته نرم افزاری MS Office با جزئیات بیشتری در نظر بگیریم.

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

ماکروها چیست و چرا به آنها نیاز است

شما می توانید با ماکروها در هر یک از برنامه های MS Office کار کنید. اول از همه، آنها برای سازماندهی شایسته کار کاربر در برنامه مورد نیاز هستند.

آنها برای اینکه یک نوع کارها و اقدامات را چندین بار انجام ندهند ضروری هستند.

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

مهم!به لطف ماکرو ایجاد شده، می توانید به سادگی کار را فوراً انجام دهید، در حالی که انجام آن به صورت دستی زمان زیادی می برد.

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

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

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

ما دو مقاله دیگر را یادداشت می کنیم که می توانندشما نیاز خواهید داشت:

ایجاد ماکروهای خود در اکسل 2010، 2007، 2013

بیایید نمونه‌هایی از ایجاد در Excel 2007، 2013 را با جزئیات بیشتری در نظر بگیریم:

• سندی را که با آن کار می کنید و می خواهید برای آن ماکرو ایجاد کنید باز کنید. به هر حال، هر سلولی که یک عمل روی آن انجام می شود باید کار شود.
• تب توسعه دهنده را در نوار نمایش دهید. برای انجام این کار، آیتم منوی "File" را باز کنید و گزینه ها را باز کنید، همانطور که در شکل نشان داده شده است.

• سپس سفارشی سازی ریبون را انتخاب کنید و پنجره توسعه دهنده را مانند شکل زیر به لیست پنجره های اصلی اضافه کنید.

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

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

• سپس روی OK کلیک کنید. پنجره بسته می شود و فرآیند ضبط شروع می شود. برای توقف ضبط، کلید مربوطه را در کنترل پنل فشار دهید.

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

نحوه فعال کردن و کار با ماکروها در اکسل

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

• در برگه توسعه دهنده، به دنبال دکمه ای به نام "Macros" بگردید. روی آن کلیک کنید؛

• ماکرو مورد نیاز خود را از لیست انتخاب کنید و روی دکمه "Run" کلیک کنید.

• همچنین می توانید با استفاده از میانبر صفحه کلید که توسط کاربر مشخص شده است، ماکرو لازم را اجرا کنید مرحله اولیهایجاد آن؛
• پس از کلیک بر روی دکمه execute، کلیه اقداماتی که در حین ضبط انجام شده است دوباره انجام می شود.

زمانی که یک سلول خاص باید چندین بار کپی شود، ماکرو بهترین استفاده است.

ماکرو برای برجسته کردن روی هر صفحه در کتاب کار فعال سلول A1. همچنین صفحه نمایش را حرکت می دهد.

زیر A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False برای i = 1 To Sheets.Count Sheets(i).انتخاب ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1"). برگه های بعدی را انتخاب کنید(1) Application.ScreenUpdating = True End Sub را انتخاب کنید

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

Sub SimpleCopy() Dim i به عنوان عدد صحیح، j به عنوان عدد صحیح i = Application.InputBox("تعداد کپی های برگه فعلی را وارد کنید") Application.ScreenUpdating = False برای j = 1 به i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Copy" & j بعدی j Application.ScreenUpdating = True End Sub Sub

برگه هایی با عناوین از یک محدوده مشخص در یک برگه ایجاد کنید

زیر () CreateFromList سلول کم نور به عنوان محدوده برای هر سلول در برگه های انتخابی.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value سلول بعدی پایان زیر

مارکروس ایمیل ها را با تاخیر ارسال می کند. ماکرو اصلاح شده از کتاب John Walkenbach Professional VBA Programming

Sub SendMail() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon در خطا رفتن به پاکسازی Set OutMail = OutApp.CreateItem(0) در خطا از سرگیری بعد با OutMail .To = " [ایمیل محافظت شده]" .موضوع = "گزارش فروش" .پیوست ها.افزودن "C:\Test.txt" .Body = "متن پیام" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00 " .send ".نمایش دهید تا ایمیل شکل بگیرد و آن را باز کنید پایان با خطای روشن برو به 0 تنظیم OutMail = پاکسازی هیچی: تنظیم OutApp = هیچ چیز پایان فرعی

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

صفحه زیر TableOfContent() کم نور به عنوان کاربرگ سلول کم نور به عنوان محدوده کم نور پاسخ به عنوان عدد صحیح Application.ScreenUpdating = False با ActiveWorkbook برای هر کاربرگ در ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" سپس پاسخ Moxets در کتاب کاری با نام Table of contents. حذف آن؟ (1)).Select Sheets.Add Sheets(1).Name = "Table of Contents" With ActiveWorkbook for Her sheet In ActiveWorkbook.Worksheets If sheet.Name<>"Table of Contents" سپس سلول را تنظیم کنید = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell، Address:=""، SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = sheet.Name End If Sheet Next End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub Sub

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

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") "وضعیت دید هر برگه را به خاطر بسپارید و انجام دهید همه چیز قابل مشاهده برای هر iSht در ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " مرتب سازی برگه های قابل مشاهده برای i = 1 به .Sheets.Count - 1 برای j = i + 1 به .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) سپس .Sheets(j).Move Before:=.Sheets(i) بعدی j بعدی i پایان با "بازیابی حالت اولیهقابلیت مشاهده هر برگه برای هر iSht در ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) بعدی Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

وارد کردن ستون های "Field1" و "Field2" از صفحه "Sheet1" فایل اکسل"C:\Manager.xls" از طریق اتصال ADODB و چسباندن محتوا از سلول A1 صفحه فعلی

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

VBA چیست

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

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

نقطه ضعف برنامه مشکلات مرتبط با سازگاری است نسخه های مختلف. آنها به دلیل این واقعیت هستند که کد برنامه VBA به آن اشاره دارد عملکرد، که در نسخه جدیدمحصول، اما در قدیمی وجود ندارد. همچنین از معایب آن می توان به باز بودن بیش از حد کد برای تغییرات اشاره کرد. توسط یک خارجی. با این اوصاف مایکروسافت آفیسو همچنین IBM Lotus Symphony به کاربر این امکان را می دهد که کد اولیه را رمزگذاری کرده و برای مشاهده آن رمز عبور تعیین کند.

اشیاء، مجموعه ها، ویژگی ها و روش ها

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

اصلی ترین برنامه، مربوط به بیشتر است برنامه اکسل. سپس Workbook ها، Worksheets و همچنین Range می آیند. به عنوان مثال، برای دسترسی به سلول A1 در یک کاربرگ خاص، باید مسیری را مشخص کنید که سلسله مراتب را در نظر بگیرد.

در مورد مفهوم "مجموعه"، این گروهی از اشیاء از همان کلاس است که در رکورد دارای شکل ChartObjects است. او عناصر منفردنیز اشیاء هستند.

مفهوم بعدی خواص است. آنها ویژگی ضروری هر شی هستند. به عنوان مثال، برای Range مقدار یا Formula است.

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

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

چگونه شروع کنیم

سپس باید به برنامه VB بروید که برای آن کافی است از کلیدهای ترکیبی "Alt" و "F11" استفاده کنید. به علاوه:

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

به نظر می رسد این است:

زیربرنامه()

"کد ما

توجه داشته باشید که خط "کد ما" با رنگ دیگری (سبز) هایلایت می شود. دلیل آن آپستروف در ابتدای خط است که نشان می دهد یک نظر دنبال می شود.

اکنون می توانید هر کدی را بنویسید و برای خود ایجاد کنید ابزار جدیددر VBA Excel (نمونه های برنامه را در زیر ببینید). البته برای کسانی که با اصول اولیه ویژوال بیسیک آشنا هستند، کار بسیار راحت تر خواهد بود. با این حال، حتی کسانی که آنها را ندارند، در صورت تمایل، می توانند به سرعت به راحتی راحت شوند.

ماکروها در اکسل

این نام برنامه های نوشته شده به زبان Visual Basic for Application را پنهان می کند. بنابراین، برنامه نویسی در اکسل ایجاد ماکروها با کد مناسب. با این قابلیت، صفحه گسترده مایکروسافت خود تکامل یافته و با نیازهای یک کاربر خاص سازگار است. پس از فهمیدن نحوه ایجاد ماژول برای نوشتن ماکرو، می توانید شروع به بررسی کنید نمونه های عینی برنامه های VBAبرتری داشتن. بهتر است با ابتدایی ترین کدها شروع کنید.

مثال 1

وظیفه: برنامه ای بنویسید که مقدار محتویات یک سلول را کپی کرده و سپس در سلول دیگر بنویسد.

برای این:

• برگه "View" را باز کنید؛
• به نماد "Macros" بروید؛
• روی "ضبط ماکرو" کلیک کنید؛
• فرم باز را پر کنید

برای سادگی، "Macro1" را در قسمت "Macro Name" رها کنید، و برای مثال، hh را در قسمت "Keyboard Shortcut" وارد کنید (به این معنی که می توانید برنامه را با دستور blitz "Ctrl + h" اجرا کنید). Enter را فشار دهید.

اکنون که ضبط ماکرو از قبل شروع شده است، محتویات یک سلول در سلول دیگر کپی می شود. به نماد اصلی برگردید. روی "Record macro" کلیک کنید. این عمل نشان دهنده پایان برنامه است.

• دوباره به خط "Macros" بروید.
• "Macro 1" را از لیست انتخاب کنید.
• روی "Run" کلیک کنید (همان عمل با شروع میانبر صفحه کلید "Ctrl + hh" آغاز می شود).

در نتیجه، عملی که در حین ضبط ماکرو انجام شد رخ می دهد.

منطقی است که ببینید کد چگونه به نظر می رسد. برای انجام این کار، به خط "Macros" برگردید و روی "Edit" یا "Enter" کلیک کنید. در نتیجه، آنها خود را در یک محیط VBA می بینند. در واقع، خود کد ماکرو بین خطوط Sub Macro1() و End Sub قرار دارد.

اگر به عنوان مثال، کپی کردن از سلول A1 به سلول C1 انجام شده باشد، یکی از خطوط کد مانند Range ("C1") خواهد بود. انتخاب کنید. در ترجمه، به نظر می رسد "محدوده ("C1"). انتخاب کنید، به عبارت دیگر، به VBA Excel، به سلول C1 می پرد.

دستور ActiveSheet.Paste قسمت فعال کد را تکمیل می کند. به معنی ضبط محتویات سلول انتخاب شده (in این مورد A1) در سلول C1 برجسته شده است.

مثال 2

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

حلقه های VBA به شما در ایجاد ماکروهای مختلف کمک می کند. فرض کنید یک تابع y=x + x 2 + 3x 3 - cos(x) وجود دارد. برای دریافت زمان بندی آن باید یک ماکرو ایجاد کنید. این کار فقط با استفاده از حلقه های VBA قابل انجام است.

x1=0 و x2=10 را برای مقادیر اولیه و نهایی آرگومان تابع در نظر بگیرید. علاوه بر این، باید یک ثابت وارد کنید - مقدار مرحله تغییر آرگومان و مقدار اولیه برای شمارنده.

تمام نمونه‌های ماکرو VBA اکسل با همان رویه بالا ایجاد می‌شوند. در این مورد خاص، کد به نظر می رسد:

زیربرنامه()

مرحله = 0.1

انجام while x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

سلول ها (i، 1). مقدار = x1 (مقدار x1 با مختصات (i،1) روی سلول نوشته می شود)

سلول ها (i، 2). مقدار = y (مقدار y با مختصات (i،2) روی سلول نوشته می شود)

i = i + 1 (کنترل فعال)؛

x1 = x1 + shag (آگومان با مقدار step تغییر می کند).

انتهای زیر

در نتیجه اجرای این ماکرو در اکسل، دو ستون دریافت می کنیم که ستون اول حاوی مقادیر x و ستون دوم برای y است.

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

مثال 3

برای پیاده‌سازی حلقه‌ها در VBA Excel 2010، مانند سایر نسخه‌ها، همراه با ساختار Do while از قبل، از For استفاده می‌شود.

برنامه ای را در نظر بگیرید که یک ستون ایجاد می کند. در هر یک از خانه های آن، مربع های شماره خط مربوطه نوشته می شود. استفاده از ساختار For به شما این امکان را می دهد که بدون استفاده از شمارنده، آن را بسیار کوتاه بنویسید.

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

برای i = 1 تا 10 بعد

این دستور به زبان "انسانی" به صورت "تکرار از 1 تا 10 با افزایش یک" ترجمه می شود.

اگر کار این است که یک ستون با مربع، به عنوان مثال، همه اعداد فرد از محدوده 1 تا 11 را بدست آوریم، می نویسیم:

برای i = 1 تا 10 مرحله 1 بعد.

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

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

به طور کلی، کد به صورت زیر خواهد بود:

زیربرنامه()

برای i = 1 به 10 مرحله 1 (شما می توانید به سادگی برای i = 1 تا 10 بنویسید)

سلول ها (i، 1). مقدار = i ^ 2

بعد (به یک معنا، به عنوان یک شمارنده عمل می کند و به معنای یک شروع دیگر از حلقه است)

انتهای زیر

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

مثال 4

در زندگی روزمره، اغلب نیاز به تصمیم گیری بسته به شرایطی وجود دارد. در VBA Excel نمی توانید بدون آنها کار کنید. نمونه‌هایی از برنامه‌هایی که در آنها مسیر بعدی الگوریتم انتخاب شده است، و در ابتدا از پیش تعیین نشده است، اغلب از ساختار If ... سپس استفاده می‌کنند (برای موارد دشوار) اگر … سپس … پایان اگر.

در نظر گرفتن مورد خاص. فرض کنید باید یک ماکرو برای "اکسل" ایجاد کنید تا سلول با مختصات (1،1) نوشته شود:

1 اگر استدلال مثبت باشد.

0 اگر آرگومان صفر باشد.

-1 اگر استدلال منفی باشد.

ایجاد چنین ماکرو برای اکسل آغاز می شود به صورت استاندارد، از طریق استفاده از "گرم" کلیدهای Altو F11. کد زیر در ادامه نوشته شده است:

زیربرنامه()

x= Cells(1, 1).Value (این دستور x مقدار محتویات سلول را در مختصات (1، 1) اختصاص می دهد)

اگر x>0 باشد، سلول ها (1، 1). مقدار = 1

اگر x=0 سپس سلول ها (1، 1). مقدار = 0

اگر x<0 Then Cells(1, 1).Value = -1

انتهای زیر

باقی مانده است که ماکرو را اجرا کنید و مقدار مورد نظر را برای آرگومان در اکسل بدست آورید.

توابع VBA

همانطور که ممکن است متوجه شده باشید، برنامه نویسی در معروف ترین صفحه گسترده مایکروسافت چندان دشوار نیست. به خصوص اگر یاد بگیرید که چگونه از توابع VBA استفاده کنید. در مجموع، این زبان برنامه نویسی که به طور خاص برای نوشتن برنامه های کاربردی در Excel و Word ایجاد شده است، حدود 160 عملکرد دارد. آنها را می توان به چند گروه بزرگ تقسیم کرد. این:

• توابع ریاضی با اعمال آنها بر استدلال، مقدار کسینوس، لگاریتم طبیعی، قسمت صحیح و غیره را دریافت می کنند.
• توابع مالی به لطف حضور آنها و استفاده از برنامه نویسی در اکسل می توانید ابزارهای موثری برای حسابداری و محاسبات مالی بدست آورید.
• توابع پردازش آرایه اینها عبارتند از Array، IsArray. lBound; UBound.
• توابع VBA Excel برای ردیف. این یک گروه نسبتاً بزرگ است. برای مثال شامل توابع Space برای ایجاد رشته ای با تعداد فاصله های برابر با یک آرگومان صحیح یا Asc برای تبدیل کاراکترها به کد ANSI می شود. همه آنها به طور گسترده استفاده می شوند و به شما امکان می دهند با ردیف ها در اکسل کار کنید و برنامه هایی ایجاد کنید که کار با این جداول را بسیار آسان تر می کند.
• توابع تبدیل نوع داده برای مثال، CVar مقدار آرگومان Expression را با تبدیل آن به نوع داده Variant برمی‌گرداند.
• توابع کار با تاریخ آنها به طور قابل توجهی موارد استاندارد را گسترش می دهند بنابراین، تابع WeekdayName نام (کامل یا جزئی) روز هفته را با تعداد آن برمی گرداند. حتی مفیدتر تایمر است. تعداد ثانیه هایی را که از نیمه شب تا یک نقطه خاص در روز سپری شده است را نشان می دهد.
• توابعی برای تبدیل یک آرگومان عددی به سیستم های اعداد مختلف. به عنوان مثال، Oct نمایش هشتگانه یک عدد را می دهد.
• توابع قالب بندی مهمترین آنها Format است. مقداری از نوع Variant را با یک عبارت فرمت شده بر اساس دستورالعمل های داده شده در اعلان فرمت برمی گرداند.
• و غیره.

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

مثال 5

بیایید سعی کنیم به سمت حل مشکلات پیچیده تر حرکت کنیم. مثلا:

یک سند کاغذی از گزارش سطح واقعی هزینه های شرکت ارائه شده است. ضروری:

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

بیایید یکی از راه حل ها را در نظر بگیریم.

یک قالب ایجاد کنید

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

متغیرها

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

• NN - شماره ردیف فعلی جدول.
• TP و TF - گردش مالی برنامه ریزی شده و واقعی؛
• SF و SP - مقدار واقعی و برنامه ریزی شده هزینه ها.
• IP و IF - سطح برنامه ریزی شده و واقعی هزینه ها.

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

حل مسئله با استفاده از برنامه نویسی VBA

با استفاده از نماد معرفی شده، فرمول هایی برای انحراف به دست می آوریم. اگر نیاز به محاسبه در٪ باشد، (F - P) / P * 100 و به مقدار - (F - P) داریم.

نتایج این محاسبات را می توان بلافاصله در سلول های مناسب صفحه گسترده اکسل وارد کرد.

برای مجموع واقعی و پیش بینی شده، با استفاده از فرمول های ItogP=ItogP + P و ItogF=ItogF+ F به دست می آید.

برای انحرافات، اگر محاسبه به صورت درصد انجام شود، از = (ItogF - ItogP) / ItogP * 100 استفاده کنید، و در مورد مقدار کل - (ItogF - ItogP).

نتایج دوباره بلافاصله در سلول های مناسب نوشته می شوند، بنابراین نیازی به اختصاص دادن آنها به متغیرها نیست.

قبل از اجرای برنامه ایجاد شده، باید کتاب کار را به عنوان مثال با نام "Report1.xls" ذخیره کنید.

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

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

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

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

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

Sub Makros1() Application.ScreenUpdating = 0 "کد شما Application.ScreenUpdating = 1 End Sub

فراموش نکنید که تابع را در انتهای ماکرو قرار دهید

چگونه پس از اجرای ماکرو انتخاب کپی را حذف کنیم؟

اگر محدوده ای را برای کپی و چسباندن در ماکرو خود اختصاص دهید. فراموش نکنید که انتخاب را در انتهای ماکرو غیرفعال کنید.

Sub Makros1() "کد شما Application.CutCopyMode = 0 End Sub

ماکروهای مفید چگونه آخرین سطر یا ستون یک محدوده را پیدا کنیم

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

Sub makros1() Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "تعداد آخرین سطر یک محدوده پر شده را پیدا می کند nLastCol= Cells(1, Columns.Count .End(xlToLeft ).Column "آخرین شماره ستون پر شده محدوده End Sub را پیدا می کند

علاوه بر این، من بلافاصله متغیر را به عنوان Long (طول 2 147 483 647) اعلام می کنم تا در موقعیتی قرار نگیرم که ممکن است عدد صحیح محبوب (32 767) برای جداول بزرگ کافی نباشد.

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

برای حلقه و وضعیت بررسی در حلقه

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

Sub makros1() Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Count Variable Dim i As Long "Loop Variable mLastRow = Cells(Rows.Count, 1).End(xlUp).Row Kol = 0 For i = 1 به mLastRow If Cells(i, 1).Value = "" سپس Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

در اینجا از Msgbox نیز استفاده می شود؛ با این ویژگی می توانید داده ها را در یک پنجره جداگانه نمایش دهید. برای مثال من به این شکل خواهد بود:

شمارش زمان اجرای ماکرو

MsgBox خروجی زیر را می دهد:

ردیابی زمان اجرای ماکروها برای بهینه سازی بعدی بسیار راحت است.

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

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

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

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

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

ضبط ماکرو

روی زبانه توسعه دهندهکلیک ماکروهابرای مشاهده ماکروهای مرتبط با کتاب کار. همچنین می توانید کلیدها را فشار دهید ALT+F8. با این کار یک کادر محاوره ای باز می شود. ماکرو.

توجه:

کار با کد نوشته شده در ویرایشگر ویژوال بیسیک (VBE)

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

نمونه ای از اصلاح کدهای ضبط شده را می توان در مقاله شروع با VBA در اکسل مشاهده کرد.

ضبط ماکرو

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

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

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

یک ماکرو همچنین می تواند شامل وظایف غیر اکسل باشد. فرآیند ماکرو می‌تواند سایر برنامه‌های آفیس و سایر برنامه‌هایی را که از Visual Basic for Applications (VBA) پشتیبانی می‌کنند، دربر گیرد. به عنوان مثال، می توانید یک ماکرو ضبط کنید که ابتدا صفحه گسترده ای را در اکسل به روز می کند و سپس Outlook را برای ایمیل کردن آن باز می کند.

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

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

برای ضبط یک ماکرو، دستورالعمل های زیر را دنبال کنید.

کار با ماکروهای ضبط شده در اکسل

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

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

در زیر آمده است اطلاعات اضافیدر مورد کار با ماکروها در اکسل

اطلاعات اضافی

همیشه می‌توانید از انجمن فناوری اکسل سؤال بپرسید، در انجمن پاسخ‌ها کمک بخواهید و همچنین پیشنهاد دهید. خصوصیت جدیدیا بهبود در وب سایت