تبلیغات
بیکران - مطالب ابر ماکرونویسی
منوی اصلی
بیکران
برای یادگیری و فهمیدن
  • این مطلب در مورد استفاده از ابزار Goal Seek و Solver در اکسل است. برای حل توابع ریاضی می‌توان از این ابزار استفاده کرد. از ابزار Solver به طور خاص می‌توان برای بهینه‌سازی و یافتن مقادیر بیشینه و کمینه‌ی توابع نیز استفاده کرد.

    یک تابع ریاضی f(x) = x + 3 را داریم. مقدار تابع وابسته به متغیر x است. می‌خواهیم بدانیم به ازای چه مقداری از x مقدار تابع برابر با عددی دلخواه مثل 0 یا 5 می‌شود. این مقدار دلخواه را هدف می‌نامیم.

    برای تابع بالا جواب واضح است. به ازای x = -3 مقدار تابع برابر با 0 خواهد شد. و به ازایx = 2 مقدار تابع برابر با 5 خواهد شد.

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

    برای مثال به ازای چه مقداری از x تابع زیر برابر با 24 می‌شود؟

    به چنین مسائلی targeting problems گفته می‌شود. چون می‌خواهیم مقداری را پیدا کنیم که به ازای آن مقدار تابع به مقدار مورد نظر ما یا مقدار هدف برسد.

    برای جواب دادن به چنین سوالی می‌توان از ابزار Goal Seek یا Solver در اکسل استفاده کرد.

    چطور از Goal Seek استفاده کنیم؟

    برای دستیابی به این ابزار باید از تَب Data وارد قسمت What-if Analysis بشیم وGoal Seek را انتخاب کنیم.

    فرض کنید مقدار مشخصی پول داریم. برای مثال 100 واحد پول داریم. می‌خواهیم ببینیم با چه سود سالیانه‌ای بعد از 5 سال به 325 واحد پول می‌رسیم؟

    برای محاسبه‌ی مقدار پول بر اساس سود سالیانه تابعی به فرم زیر وجود دارد که مقدار x در آن معادل سود سالیانه است.

    اگر فرض کنیم مقدار سود سالیانه 10 درصد است، بعد از 5 سال مقدار این پول برابر 161 خواهد شد. کافی است فرمول آن را در اکسل بنویسیم و مقدار به دست آمده را مشاهده کنیم.

    ولی می‌خواهیم بدانیم که به ازای چه سود سالیانه‌ای بعد از 5 سال مقدار این پول 325 واحد خواهد شد؟

    در اینجا می‌توان از Goal Seek استفاده کرد.

    وقتی روی Goal Seek کلیک کنیم پنجره‌ای باز می‌شود که باید در آن خروجی تابع، مقدار هدف، و ورودی تابع که به ترتیب با شماره‌های 1 تا 3 در تصویر زیر نشان داده شده‌اند را مشخص کنیم. خروجی تابع باید به صورت یک فرمول که تابعی از ورودی است باشد.

    وقتی روی گزینه Ok کلیک کنیم ابزار هدف‌یابی مقدار x را به صورتی تغییر می‌دهد تا خروجی تابع برابر با 325 شود.

    می‌بینید که با سود سالیانه‌ای در حدود 26 درصد می‌توان بعد از 5 سال به 325 واحد پول رسید.

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

    مثلا با سود 10 درصد بعد از چند سال می‌توان به مقدار 325 واحد پول رسید؟

    یا اینکه با چه مقدار پول اولیه‌ای می‌توان با سود 10 درصد  بعد از 5 سال به 325 واحد پول رسید؟

    به راحتی با ابزار Goal Seek می‌توان این مساله‌ها را حل کرد.

    نیاز به حدس اولیه:

    حتما برای استفاده از Goal Seek نیاز به یک حدس اولیه داریم. در مثال قبل، سود سالیانه 10 درصد همان حدس اولیه بود. چون که این ابزار ابتدا یک ورودی را دریافت می‌کند و مقدار خروجی تابع را با آن محاسبه می‌کند. سپس اختلاف خروجی به دست آمده با هدف را محاسبه می‌کند. بر اساس اینکه مقدار این اختلاف کم یا زیاد شود مقدار ورودی را تغییر می‌دهد تا زمانی که مقدار خروجی تابع برابر با مقدار هدف شود.

    پس برای اینکه چنین محاسباتی شروع شود لازم داریم که همیشه یک مقدار اولیه را به تابع پیشنهاد بدیم تا تابع با آن محاسبات را شروع کند.

    حالا می‌توان حساب کرد که به ازای چه مقدار از x مقدار تابع زیر برابر با 24 می‌شود.

    به ازای x برابر با 113.

    بعضی وقت‌ها هم جوابی نداریم.

    اگر مقدار تابع را برابر 0.1 قرار دهید می‌بینید که نمی‌تونید برای تابع جوابی رو پیدا کنید.

    ارسال دیدگاه نظرات ()
  • یوزرفرم‌ها منوهایی هستند که اجازه ارتباط ساده‌تر بین کاربر و اکسل را برقرار می‌کنند. برای ساختن یوزرفرم‌ها می‌توان از قسمت یوزرفرم در VBE استفاده کرد.

    باید وارد قسمت insert -> UserForm شویم تا بتوانیم یک فرم جدید را ایجاد کنیم.

    ماکرونویسی 11

    یوزرفرم UserForm‌ یک شی است که می‌تواند یک پنجره یا یک منویی باشد که برای ایجاد رابط‌های کاربری استفاده می‌شود.

    کالکشن UserForms هم وجود دارد. کالکشن‌ها مجموعه‌ای از اشیا مشابه را در خود جای می‌دهند. پس کالکشن UserForms دربرگیرنده‌ی UserFormهایی است که هم اکنون در اپلیکیشن در حال استفاده هستند.

    یکی از بخش‌های یوزرفرم پنجره‌ی یوزرفرم است.

    یوزرفرم

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

    کنترل‌‌ها در منویی با نام Toolbox قرار گرفته‌اند که بسته به نیاز می‌توان آن‌ها را به یوزرفرم اضافه کرد.

    پنجره یوزرفرم

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

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

    کنترل کننده‌ها می‌توانند استاتیک باشند یا اینکه پویا باشند. خصوصیت‌های کنترل‌کننده‌های استاتیک را فقط می‌توان با کدها تغییر داد. اما خصوصیت کنترل کننده‌های پویا بر اساس رفتار کاربر قابل تغییر هستند.

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

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

    منو کنترل ها در یوزرفرم

    همچنین می‌توانیم 3 کنترل Label هم ایجاد کنیم تا نام هر کدام از TextBox ها را مشخص کنیم.

     

    برای این‌ کار از منوی Toolbox باید کنترل دوم یعنی Label را انتخاب کنیم و به فرم اضافه کنیم.

    انتخاب کنترل TextBox در VBA

    هر کدام از کنترل‌ها دارای نامی هستند که برنامه آن‌ها را با آن نام می‌شناسد. این نام به مانند نام یک شی است که در برنامه تعریف می‌کنیم و با استفاده از این نام می‌توانیم خصوصیت‌های مربوط به آن‌ها را تغییر دهیم و مِتدهای مختلف را روی آن‌ها اعمال کنیم.

    در تصویر بالا نام خانه A که در تصویر نشان داده شده است Label2 است. پس در هنگام کدنویسی می‌توانیم با استفاده از Label2 به خصوصیات آن دسترسی پیدا کنیم.

    می‌خواهیم اینجا عملیات جمع را انجام دهیم.

    کافی است که روی خانه C یک دابل کلیک کنیم تا بتوانیم در آن‌جا کد مورد نظر خودمان را بنویسیم. یا اینکه می‌توانیم روی خانه C راست کلیک کنیم و گزینه View Code را انتخاب کنیم.

    ایجاد کنترل Label در منو UserForm

     

    وقتی وارد قسمت کدنویسی شدیم می‌توانیم از قسمت بالا شی مورد نظر خودمان را انتخاب کنیم. خانه‌ی C با نام TextBox3 شناخته شده است.

    از قسمت بالا سمت راست می‌توانیم یک رویداد خاص را انتخاب کنیم تا آن را با خانه‌ی C لینک کنیم.

    پس از اینکه رویداد مورد نظر را انتخاب کردیم می‌توانیم کدی را که می‌خواهیم در صورت رخ دادن رویداد اجرا شود را بنویسیم.

    در اینجا می‌خواهیم جمع دو عدد وارد شده در خانه‌های A و B را در خانه‌ی C نمایش دهیم.

    نوشتن کد در یوزرفرم&zwnj

    پس از اینکه کد را نوشتیم لازم است که دکمه Run یا کلید F5 کلیک کنیم تا فرم مورد نظر ایجاد شود. سپس با وارد کردن عدد در خانه‌های A و B می‌توانیم خروجی را در خانه‌ی C ببینیم.

    نوشتن کد در یوزرفرم&zwnj

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

    ارسال دیدگاه نظرات ()
  • ماکرونویسی 11

    برای کدنویسی در اکسل از ویژوال بیسیک اپلیکیشن VBA استفاده می‌کنیم.

    محیطی که در آن کدها نوشته می‌شوند ویژوال بیسیک اِدیتور VBE است که همان پنجره‌ای است که در آن ماژول‌های مختلف را ایجاد می‌کنیم و در آن کدها را می‌نویسیم.

    تا به حال ما همیشه از قسمت ماژول‌ها برای کدنویسی استفاده کردیم.

    اما در VBE قسمت‌های دیگری نیز وجود دارد.

    اگر وارد محیط ویژوال بیسیک با کلید Alt+F11 بشویم پنجره‌ای با نام Project – VBAProject وجود دارد.

    یعنی پنجره نشان داده شده با کادر قرمز در شکل زیر:

    پروژه اکسل در ویژوال بیسیک - ماکرونویسی 11

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

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

    در زیر هر پروژه‌ای محیط‌های مختلف بسته به ماهیتی که دارند در قسمت‌های مختلف قرار می‌گیرند.

    برای مثال شی‌های مربوط به صفحه‌ی کاری اکسل یعنی Sheet1 (Sheet1) و ThisWorkbook در قسمت Microsoft Excel Objects قرار گرفته‌اند که در تصویر بالا هم می‌توان آن‌ها را دید.

    با اضافه کردن صفحات جدید یا چارت‌های جدید به اکسل می‌توان اضافه‌ شدن شی مربوط به هر کدام از آن‌ها را در این قسمت مشاهده کرد.

    اشیا اکسل در ویژوال بیسیک - ماکرونویسی در اکسل 11

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

    مثلا وقتی یک ماژول جدید را اضافه می‌کنیم، قسمتی با نام Modules به پروژه اضافه می‌شود و تمام ماژول‌های دیگر هم که اضافه می‌شوند در زیر قسمت Modules قرار می‌گیرند. (برای اضافه کردن یک ماژول جدید باید در پنجره Project راست کلیک کنیم، گزینه Insert را انتخاب کنیم و یک ماژول جدید را بسازیم.)

    یا با اضافه کردن یک کلاس جدید قسمتی با عنوان Class Modules به پروژه اضافه می‌شود. تمام کلاس‌های بعدی هم به این قسمت اضافه می‌شوند.

    یا با اضافه کردن یوزرفرم‌ها قسمتی با عنوان Forms به پروژه اضافه می‌شود و تمام یوزرفرم‌ها ساخته شده در این قسمت قرار می‌گیرند.

    اشیا، ماژول، کلاس، فرم ها - ماکرونویسی در اکسل 11

    ما تا اینجای کار فقط از قسمت Modules برای کدنویسی استفاده کردیم.

    اما 3 قسمت دیگر مربوط به چه کاری هستند؟

    1. قسمت مربوط به شی‌های اکسل Microsoft Excel Objects در VBE برای برخی از فعالیت‌های خاص که مربوط به رویدادها Events می‌شوند مفید هستند.

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

    البته با استفاده از ماژول‌ها نیز می‌توان چنین کارهایی را انجام داد ولی در قسمت مربوط به شی‌های اکسل این کار ساده‌تر شده است.

    2. قسمت مربوط به فرم‌های کاربر Forms،  برای ایجاد کادری برای تبادل اطلاعات بین کاربر و اکسل می‌توانیم از فرم‌های کاربری استفاده کنیم. یوزرفرم‌ها کمک می‌کنند تا وارد کردن اطلاعات توسط کاربر ساده‌تر شود.

    3. قسمت مربوط به ایجاد کلاس‌ها Class Modules

    همانطور که می‌دونید اکسل برنامه‌ای شی‌گرا است و اهمیت برنامه‌ی شی‌گرا در استفاده از شی‌های آن است.

    معمولا در کدنویسی در ماژول‌ها از شی‌های پیش‌فرض برنامه مثل Range، Worksheet یا Workbook استفاده می‌کنیم.

    اما اگر بخواهیم خودمان اشیا جدیدی را به برنامه اضافه کنیم می‌توانیم ابتدا آن شی را در قسمت کلاس‌ها تعریف کنیم و سپس از شی مورد نظر در قسمت ماژول‌ها استفاده کنیم.

    VBA-Class-Modules

    چرا تا اینجای کار فقط از قسمت Modules استفاده کردیم؟

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

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

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

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

    برای مثال تعریف اشیا در برنامه در قسمت کلاس‌‌ها انجام می‌شود که گاهی اوقات می‌تواند خوانایی کد نوشته شده را بسیار بالا ببرد. اما بدون تعریف اشیا هم می‌توان کدی را در قسمت ماژول‌ها نوشت که همان کار را برای ما انجام دهد.

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

    برای همین هم برای یادگرفتن کدنویسی در اکسل استفاده از قسمت ماژول‌ها تا اینجای کار کفایت می‌کند.

    برای همین هم فقط از قسمت Modules استفاده شده است.

    ارسال دیدگاه نظرات ()
تعداد صفحات : 5 1 2 3 4 5