کلاس های حضوری طراحی داشبورد و اکسل پیشرفته

نمایش اکسل به روش های مختلف

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

دیدن دو Sheet همزمان در اکسل

ممکن است کاربر نیاز داشته باشد دو Sheet از Workbook خود را در کنار هم ببیند، برای این کار می توان فایل اکسل موجود را دوباره باز کرد و دو فایل اکسل (که در واقع ی) را در کنار هم دید. به منظور انجام این کار وارد تب View شده و در گروه Window گزینه ی New Window را انتخاب می کنیم، حال برای تعیین نحوه نمایش دو فایل به صورت همزمان از گزینه ی Arrange All استفاده می کنیم.

Freeze کردن در اکسل

اگر بخواهید یک یا چند سطر از جدول شما مثلاً عناوین ستون های جدول خود را همواره در صفحه نمایش خود داشته باشید باید از دستور Freeze استفاده نمایید. برای استفاده از ابزار Freeze در تب View و در گروه Window بر روی گزینه ی Freeze کلیک نمایید.

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

Split در اکسل

اگر بخواهید Sheet موجود را به دوبخش تقسیم کنید می توانید از Split استفاده نمایید. استفاده از Split زمانی کاربرد دارد که در حال کار بر روی یک فایل اکسل طولانی میباشید، در این حالت ممکن است برای فرمول نویسی ها مجبور باشید قسمت های بالایی و پایینی شیت را در کنار یکدیگر ببینید، Split کردن شیت کار شما را بسیار راحت مینماید. ابزار Split هم در تب View و در گروه Window قرار گرفته است.

Costume View در اکسل

فرض کنید احتیاج دارید یک نمای خاص (View) از اکسل را دائماً کنترل کنید، به عنوان مثال شما در حال تکمیل بانک اطلاعات هستید و اطلاعاتی در Sheet اول وجود دارند که دائم باید داده های ورودی را با آنها چک کنید. در این حالت به جای اینکه هر دفعه به Sheet  اول رفته و دنبال آن اطلاعات باشید نمای مورد نظر در Custom View ذخیره نمایید. ابزار Custom View در تب View و در گروه Workbook View قرار گرفته است.

 

Watch Window در اکسل

حال اگر بخواهید بدون اینکه View خود را عوض کنید به یک سری اطلاعات در فایل اکسل خود دسترسی سریع داشته باشید می توانید از دستور Watch Window استفاده نمایید. با ایجاد پنجره نظارت می توانید محتوا و مقدار یک سلول خاص را ذخیره نمایید و اگر به آن احتیاج داشته باشید به جای گشتن به دنبال آن می توانید از طریق Watch Window به آن دسترسی داشته باشید. این ابزار در نسخه های اکسل ۲۰۱۰ به بعد از منو حذف شده است. در اکسل نسخه ۲۰۰۷ از مسیر زیر میتوانید به ابزار Watch Window دسترسی پیدا کنید:

صفحه نمایش اکسل

صفحه نمایش اکسل می تواند به صورت صفحه گسترده، که در اکسل به آن Normal گفته می شود و یا به صورت های Page Break یا Page Layout نمایش داده شود. در حالات Page Layout، اکسل مانند نرم افزار Word به صورت صفحه ای نمایش داده می شود و می توان برای صفحات Header و Footer هم تعیین نمود. در حالت Page Break تنها سلول های دارای اطلاعات صفحه بندی می شوند و فضاهای خالی صفحه بندی نمی شوند.

نمودار پویا در اکسل

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

 

ادامه نوشته

کابرد علامت $ در اکسل

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

زمانی که فرمول A1*B1 را به سمت ردیفهای پایین تر بسط دهید نتیجه بصورت A2*B2، A3*B3،.... تغییر خواهد کرد، همچنین اگر فرمول A1*A2 را به سمت راست (پیش فرض) بسط دهید نتیجه بصورت B1*B2, C1*C2, .... خواهد بود.

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

چند مثال:

A$1 (Down): A$1,A$1,A$1,A$1,....

$A1 (Down): $A2,$A3,$A4,....

A$1 (Right): B$1,C$1,D$1,....

$A1 (Right): $A1,$A1,$A1,$A1,...

$A$1 (Down or Right): $A$1,$A$1,$A$1,....

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

از این قابلیت، علاوه بر فرمول نویسی، در ثابت نگه داشتن یک محدوده نیز استفاده می شود. حین استفاده از فرمولهایی نظیر VLOOKUP، و امکاناتی نظیر Data Validation، باید توجه داشت که محدوده مورد نظر توسط $ ثابت نگه داشته شود در غیر اینصورت فرمول با مشکل مواجه خواهد شد.

فرمول‌نویسی آرایه‌ای در اکسل

                                                                                                                                            بااستفاده از فرمول‌نویسی آرایه‌ای (Array formulas) می‏‌توانید فرمول‏‌ها را به صورت کلی بنویسید و روی چندین سلول اعمال کنید. ساده‌ترین کاربرد فرمول نویسی آرایه‌ای، فرمول نویسی روی ستون‏‌ها است. به این صورت که دیگر به جای استفاده از نشانی یک سلول برای نگارش فرمول، شما می‏‌توانید محدوده‌ی یک ستون را در فرمول مورد استفاده قرار دهید. در ادامه مطلب باما همراه باشید با روش استفاده از فرمول نویسی آرایه‌ای آشنا شوید.

 

برای مثال مطابق شکل زیر، برای به دست آوردن حاصل‌ضرب اعداد ستون D در ستون E، در روش سنتی باید فرمول E1*D1= را در سلول F1 بنویسید و سپس فرمول را در سلول‏‌های متناظر مربوط به سطرهای دیگر کپی نمایید.
 

با استفاده از فرمول نویسی آرایه‌ای دراکسل شما می‌‏توانید به صورت یکجا محدوده‌ی (F1:F3) در ستون F، را انتخاب نموده و سپس عبارت D1:D3*E1:E3= را در آن بنویسید. برای راحتی کار می‌توانید به وسیله یروش نام‌گذاری محدوده‌، یک نام برای محدوده‌‌های D1:D3 و E1:E3 اختصاص دهید. 
  

البته تنها تفاوت فرمول نویسی آرایه‌ای با فرمول نویسی معمولی نحوه‌ی تائید فرمول می‌باشد. در واقع در فرمول نویسی آرایه‌ای بجای استفاده از کلید Enter، باید سه کلید Ctrl + Shift + Enter را همزمان فشار داد. به همین خاطر به فرمول نویسی آرایه‌ای، فرمول‌های سی.اس.ای (CSE Formulas) هم گفته می‏‌شود. پس از تائید مشاهده می‌کنید که در اطراف فرمول D1:D3*E1:E3= دو علامت کروشه {} ایجاد شده است. (دقت کنید که این دو کروشه بصورت خودکار پس از استفاده از سه کلید Ctrl + Shift + Enter ایجاد می‌شود بنابراین نباید بصورت دستی در ابتدا و انتهای فرمول کروشه بگذارید.)

 

حال اگر تصمیم داشته باشید که مجموع سلول های F1:F3 را محاسبه کنید می‌توانید با انتخاب سلول F4، فرمول (Sum(F1:F3= را بنویسید و تائید کنید. اگرچه اگر هدف اصلی محاسبه مجموع ضرب ستون D و E بود می‌توانستید همان ابتدا از فرمول نویسی آرایه‌ای استفاده کنید و در سلول F4 فرمول (Sum(D1:D3*E1:E3= را نوشته و برای تائید سه کلید Ctrl + Shift + Enter را همزمان فشار دهید.

* برای محاسبه مجموع ضرب ستون D و E، می‌توانید از تابع SUMPRODUCT نیز استفاده نمائید.

یک نمونه پرکاربرد از فرمول نویسی آرایه‌ای محاسبه خطا در محاسبات مهندسی است. بطور مثال برای مقایسه اعداد پیش‌بینی شده با اعداد تجربی از عبارت متوسط ​​درصد خطای مطلق ((Mean absolute percentage error (MAPE) یا (Mean absolute percentage deviation (MAPD استفاده می‌شود. MAPE را می‌توان با کمترین میزان محاسبات با استفاده از فرمول نویسی آرایه‌ای تعیین نمود.
به این صورت که با استفاده از روش نام‌گذاری محدوده‌، برای اعداد پیش‌بینی شده نام Forecasted_values و برای اعداد تجربی نام  Actual_values را انتخاب نمائید. سپس در سلولی که می‌خواهید خطا را محاسبه کنید فرمول زیر را وارد نموده و سپس برای تائید سه کلید Ctrl + Shift + Enter را همزمان فشار دهید.

=AVERAGE(ABS((actual_values-forecasted_values)/actual_values)))

رسم نمودار دایره ای و Doughnut در اکسل

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

نمودار Doughnut در اکسل

نمودار Doughnut در اکسل مانند نمودار دایره ای میباشد وتنها در ظاهر با آن تفاوت دارد، ضمن اینکه در ادامه با قابلیتی از نمودار Doughnut (دونات) آشنا میشویم که در نمودار دایره ای وجود ندارد،                           

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

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

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

قصد داریم نمودار دایره ای نشان دهنده سهم وزن آیتم های مختلف در وزن کل یک سازه را رسم نماییم، روش اول اندازه گیری وزن را در نظر بگیرید. برای رسم نمودار دایره ای در اکسل ابتدا از میسر زیر، نمودار Pie را انتخاب نمایید                                                                                                                                   نمودار دایره ای در اکسل خود اشکال متفاوتی دارد، میتوانید نمودار را به صورت دو بعدی یا سه بعدی رسم نمایید و یا از دو دایره برای نشان دادن داده ها استفاده نمایید، همینطور در صورت زیاد بودن تعداد داده ها و کم اهمیت بودن تعدادی از آنها میتوانید از نمودار دایره ای نوع Bar of Pie استفاده نمایید، امکان تغییر نوع نمودار و فرمت آن بعد از رسم نمودار در اکسل هم ممکن میباشد، پس میتوانید بعد از اتمام رسم نمودار در مورد شکل آن تصمیم بگیرید.

برای جلوگیری از دوباره کاری در ترسیم نمودار در اکسل، هنگام Insert کردن نمودار در اکسل و طی کردن مراحل فوق یک سلول خالی را در حالت انتخاب قرار دهید، در اینجا اگر دقت کنید میبینید سلول A1 درحالت انتخاب قرار گرفته، بدین ترتیب پس از انتخاب نمودار مورد نظر یک صفحه ی خالی برای شما نمایش داده میشود که میتوانید رسم نمودار در اکسل را از آن آغاز نمایید.                                                                                                                    کادر خالی ایجاد شده در صفحه ی اکسل شما آماده ی معرفی داده ها برای رسم نمودار دایره ای در اکسل میباشد، بر روی صفحه کلیک کرده و گزینه ی Select Data را انتخاب کنید، با انتخاب این گزینه پنجره ی انتخاب داده برای نمودار اکسل فعال میگردد. در اینجا شما میتوانید با کلیک بر روی Add نمودار دایره ای جدید خود را در اکسل ایجاد نمایید.                           

شکل ظاهری نمودار و نحوه ی نمایش دادن نام آیتم ها و سهم آنها را به راحتی میتوانید در تب Design و تب های دیگر تغییر دهید                                                                          در مورد نمودار Doughnut شما میتوانید چند سری داده را همزمان نشان دهید، برای این کار مراحل رسم نمودار را دنبال نمایید و یک سری جدید را اضافه نمایید،                                     حلقه داخلی روش اندازه گیری اول و حلقه بیرونی، روش اندازه گیری دوم را نشان میدهد. شکل ظاهری و نحوه نمایش داده ها را میتوانید به دلخواه خود ویرایش نمایید.

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

                                                                                                                                            فرمولهای Excel در دسته های مختلفی دسته بندی شده اند كه برای كارهای ریاضی، مالی ، آماری و كار با رشته های متنی و كار با داده های زمان و …. به كار می روند.
توابع ریاضی مشتمل است بر انواع و اقسام توابع مثلثاتی، قدر مطلق، تقسیم باقیمانده ، توان، مجموع مربعات و ….
توابع متنی شامل جستجو در یك متن، حذف و جایگزینی بخشی از یك متن و …. می باشد. توابع آماری برای محاسبه واریانس، انحراف از معیار و كلی چیزهای دیگر كه آرزویشان را داریم هم وجود دارد برای داشتن پیش زمینه ذهنی سعی كنید حتماً سری به لیست این فرمولهای بزنید تا در مواقع لزوم از امكانات تعبیه شده در خود Excel استفاده كنید. تابع نویسی از یك علامت = شروع می شود به محض وارد كردن یك علامت مساوی در یك خانه خالی Excel می فهمد مقدار این خانه یك داده تنها نیست بلكه باید آنرا حساب كند.
وقتی نیاز به یك تابع را احسای می كنید به عنوان نخستین سؤال این پرسش مطرح می شود كه از كجا تابع مورد نظر خود را بیاییم؟
ممكن است شما دقیقاً بدانید می خواهید چه كاری انجام دهید ولی اطلاعی نداشته باشید كه آیا در اكسل توابع مورد نیاز شما پیش بینی شده است یا خیر؟ از كجا بدانیم تابعی كه مورد نیاز ما است كجاست؟ شاید هم تابعی را چند ماه پیش به كار برده اید ولی هم اكنون آنرا فراموش كرده اید. فرض كنید می خواهید میزان بازپرداخت ماهانه برای یك وام 180 هزار دلاری را كه ظرف ده سال با بهره 6 درصد باید بازپرداخت شود محاسبه كنید. كافی است در خط فرمول قرار بگیرید و دكمه function insert را بفشارید و در آنجا عبارت مورد نظر خود را در بخش جستجو وارد كنید. یا حتی می توانید نوع تابع مورد نظر خود را از بخش category انتخاب نموده و در لیست مربوطه تك تك روی اسامی هر یك از توابع بایستید و تعریف آنرا بخوانید. مثلاً تابع PMT همان تابعی است كه ما بدنبال آن هستیم و در تعریف آن آورده شده است، محاسبه میزان باز پرداخت ماهانه برای وام با اقساط ثابت و بهره ثابت با انتخاب این تابع كادری باز می شود كه آرگومانهای تابع را از ما دریافت می نماید.
قبل از این كار با كلیك روی گزینه This Topic Help on می توانید نكات راهنما در مورد این تابع را بخوانید. در این راهنما syntax یا به عبارتی فرم دقیق تابع و مشخصات آرگومانهای ورودی آن آورده شده است و همچنین یك مثال كامل از یك نمونه كاربرد تابع ذكر شده است. كه می توانید این مثال را كپی كرده و در فایل خود بچسبانید و نحوه كار آن را ببینید.
پنجره ای كه آرگومانهای تابع را دریافت می كند به تفكیك مقادیر ورودی تابع ما را نشان داده است و منتظر می ماند شما مقدار هر یك از وارد نمایید. در اینجا می توانید به هر یك از این آرگومانها یك مقدار عددی یا آدرس یك خانه تخصیص دهید. آرگومانهایی كه ارائه مقدار آنها ضروری است با رنگ مشكی تیره مشخص شده اند و حتماً باید مقدارشان را مشخص كنید. بقیه آرگومانها مقدارشان اختیاری است و اگر معلوم نكنید به صورت پیش فرض به آنها مقدار داده می شود.
در كادر مقابل هر آرگومان كه قرار بگیرید در پایین ترین بخش صفحه تعریفی از ویژگیها و مشخصات این آرگومان آورده می شود.
تابع 5 PMT(Pate. Noper.Pv.type)‌آرگومان دریافت می دارد كه سه تای اول آن اجباری هستند و دو تای آخری اختیاری.
برای محاسبه اقساط ماهانه وام ده ساله 180 هزاردلاری با بهره 6 درصد در بخش Rate كه نشانگر بهره است مقدار 12/6% را باید وارد كنید. بهره 6 درصد سالیانه برای هر ماه معادل نیم درصد است.
توجه داشته باشید كه نرخ سود را باید ماهانه وارد نمود نه سالانه. آرگومان Noper مبین تعداد كل اقساط وام است كه در اینا معادل 12*10=120 خواهد بود. Pv هم نشانگر كل میزان وام دریافتی بدون در نظر گرفتن بهره و معادل 180000 است. با فشردن دكمه ok نتیجه كار در كاربرگ شما پدیدار می شود ($.79 . 19) یعنی شما هر ماه تقریباً هزار و هفتاد و نه دلار باید بپردازید. چون این مقدار را باید بپردازید و به اصطلاح حسابداری پرداختنی است در داخل پرانتز به عنوان یك عدد منفی – و با رنگ قرمز نشان داده می شود.
مراحلی كه در بالا توضیح دادیم برای وقتی است كه شما خود تابع و یا آرگومانهای آنرا به درستی نمی شناسید. ولی در دفعات بعدی كه نام تابع را به خاطر می آورید كافی است در داخل خود كاربرگ نام تابع را وارد نمایید. بدیهی است باید محتوای خانه ای كه قرار است از طریق یك تابع محاسبه شود با یك علامت مساوی شروع شود. اكسل با نشان دادن نوشته هایی با زمینه زرد رنگ به نام tips به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری كه در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی كه مقدار دادن به آنها اختیاری است در داخل كروشه نشان داده می شوند.

مفاهیم ماکرو نویسی در اکسل

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

ماکرو چیست؟

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

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

 

Procedure

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

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

در ماکرو نویسی Procedure ها میتوانند Sub، Function یا Property باشند. در ادامه به معرفی آن ها میپردازیم.

Sub

ساب یا همان Sub Routine یک نوع Procedure میباشد که با عبارت Sub شروع شده و با عبارت End Sub تمام میشود، کد های قرار گرفته در بین این دوعبارت عملیات خاصی را با هم انجام میدهند اما قرار نیست که خروجی مشخصی ارایه کنند، یعنی نمیتوان نوشت خروجی Sub برابر است با یک مقدار یا عبارت مشخص. Sub ها پرکاربردترین Procedure ها در ماکرو نویسی در اکسل میباشند. به این نوع پروسیژر ها به صورت مختصر روتین (Routine) هم میگویند، در مقاله ی Procedure ها در ماکرو نویسی این نوع پروسیژر مورد بررسی قرار گرفته است.

Property

پروپرتی یا همان Property Procedure هم مانند Sub یک نوع Procedure میباشد که با Property Set یا Property Get یا Property Let شروع شده و با End Property تمام میشود. کد های قرار گرفته در بین این دو عبارت برای Class Module ها خواص (Properties) تعریف و توصیف میکنند، به عبارت ساده تر کدها قرار است برخی خواص یک شیء خاص را تعریف و مورد بررسی قرار دهند، با مفهوم شیء آشنا خواهیم شد.

 

Function

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

نمیتوان Procedure ها را درون یکدیگر تعریف کرد به عبارت دیگر، خط اول و آخر هر Procedure یک Module Level Code (کدی که در سطح یک ماژول تعریف شده و شناخته شده است) میباشد.

ماژول

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

انواع ماژول ها

انواع ماژول ها عبارتند از:

• ماژول های نوع Standard Module یا همان Code Module که ماژول هایی هستند که میتوانند توسط سایر ماژول ها در یک پروژه مورد استفاده قرار گیرند.
• ماژول های نوع Class Module که برای تعریف یک Class خاص و مشخص تعریف و مورد استفاده قرار گرفته اند. این نوع ماژول ها یک کلاس (Class) را توصیف (Define) کرده و خواص (Properties) و روش های (Method Definitions) آن را تعریف میکنند.
• ماژول های نوع Form Module که حاوی کد های مربوط به یک فرم (Form) هستند. با فرم ها به تفصیل آشنا خواهیم شد. فرم ها خود دارای اشیا متعدد میباشند که کدهای مربوی به Properties هریک از آنها در این فضا قرار میگیرد.
• ماژول های نوع Object Modul که حاوی کد های مشخص برای یک شیء (Object) میباشند، ماژول ها نوع Class Module، Form Module و Document Module همگی جز این دسته میباشند به این علت که خود Form ها یا Document ها یا Class ها در ویژوال بیسیک اشیا محسوب میشوند.

 Declaration

کد های غیر اجرایی (یا غیر قابل اجرا Non Executable) میباشند که برای نام گذاری Procedure ها، ثابت ها (Constants)، متغیر ها (Variables) و مشخصات (Characteristics) آنها- مانند نوع داده ها- میباشند. هر ماژول در ابتدا دارای سطرهایی تحت عنوان Declarations میباشد که منظور چند سطر ابتدایی ماژول است که برخی تنظیمات مربوط به سراسر آن ماژول در قالب کد نوشته میشود. در آینده با این بخش اشنا خواهیم شد. یکی از دلایل نیاز به تعریف چند ماژول برای یک پروژه، متفاوت بودن Declaration ها ی آنها با توجه به کاربردشان میباشد.

میتوان ماژول را به صورت یک سری Declarations که به دنبال آن ها Procedure ها قرار دارند هم تعریف نمود. به کد ها و متغیرهایی که به صورت Decorations تعریف میشوند عبارت Module Level Codes و Module Level Variable اطلاق میگردد. متغیر های نوع Module Level در تمام Procedure های قرار گرفته در آن ماژول معتبر میباشند.

فرم

فرم ها یا Form ها اشیا تبادل اطلاعات با کاربر (Dialog Box) یا پنجره هایی میباشند که در برگیرنده ی کنترل ها (Controls) هستند. نوع تخصصی تر فرم ها Multiple Document Interface ها یا فرم های MDI میباشند که خود حاوی فرم های کوچکتر (Child Forms) در درون خود هستند. فرم ها از دو قسمت تشکیل شده اند: یکی خود شیء فرم و کنترل های قرار گرفته در آن و دیگری ماژول در برگیرنده ی کدهای اجرایی آن فرم و اشیا درون آن (که به آن Form Module میگوییم). با انتخاب گزینه های View Object و View Code (کد های فرم ها در فضای Form Module قرار دارند) که با کلیک راست بر روی فرم نمایش داده میشوند، میتوانیم هر قسمت را ببینیم یا ویرایش نماییم.

در واقع فرم ها پنجرهایی هستند که از طریق آنها برنامه نویسان با کاربران خود ارتباط برقرار میکنند، مانند تمام Dialog Box هایی که در ویندوز و نرم افزار های دیگر وجود دارند.

Object Box و Procedure Box

در فضای کد نویسی Form ها (Form Modules و سایر Module ها) در بالا و سمت چپ کادر Object Box وجود دارد که لیستی از تمام اشیا قرار گرفته در Form در آن قرار دارد، هر کدام از اشیا را که میخواهید انتخاب نمایید، حال در سمت راست کادر فوق کادری تحت عنوان Procedure Box قرار دارد، با باز کردن این کادر متوجه میشویم که مثلاً برای یک کمبوباکس چندین Procedure وجود دارد (برای سایر Module ها با باز کردن این قسمت لیستی از Procedure های موجود در آن ماژول نمایش داده میشود)، مثلاٌ Click، با انتخاب کلیک یک Private Sub ComboBox1_Click ایجاد میشود که در واقع یک Procedure میباشد که فقط در سطح همین ماژول اعتبار دارد (به دلیل وجود کلمه ی Privet) و حاوی کد هایی است که در صورت کلیک کردن بر روی این کمبوباکس اجرا میشوند، در واقع در اینجا میخواهید مشخص کنید که کلیک کردن بر روی کمبو باکس چه نتایجی ایجاد کند.

برای درک این قسمت حتماً باید مفهوم اشیا (Object) و پروپرتی (Properties) را درک کرده باشید.

Project

یک یا یک سری ماژول را یک Project مینامیم، مثلاً شما ممکن است برای انجام کاری ناچار به ایجاد چند ماژول متفاوت شوید (به علت وجود Declaration های مختلف) به این مجموعه یک Project میگوییم. وقتی ما با اکسل سر و کار داریم، Project به معنی فایل اکسلی هست که ماژول ها در آن قرار گرفته اند، در یک فایل اکسل میتوانید به تعداد بیشما ماژول بوجود بیاورید.

اشیاء

اشیا همان Object ها هستند که به صورت خلاصه میتوان آن ها را ترکیبی از کدها و داده ها فرض نمود که میتوان بر روی آنها به صورت یکجا عملیات انجام داد و یا آن ها را به صورت یکجا و یک واحد مورد استفاده قرار داد. به عنوان مثال Application یک شیء است، یا فرم ها یا کنترل ها همگی شیء میباشند. اشیا خواص (Properties) مربوط به خود را دارند که هرکدام روش(Method) استفاده ی مختص خود را دارا میباشند.

مفهوم شی (Object) در ماکرو نویسی در اکسل یکی از مهمترین مفاهیم است که برنامه نویسان باید درک درستی از آن بدست بیاورند، در واقع نرم افزار اکسل که در آن ماژول های خود را بوجود می آورید Application مورد خطاب ویژوال بیسیک میباشد، اگر دقت کنید VBA مخفف عبارت Visual Basic for Applications میباشد که در این سایت Application اکسل مورد بررسی قرار گرفته است، پس در هر جایی که عبارت Application به کار برده شده منظور اکسل میباشد.

زبان ویژوال بیسیک برای Application های دیگر هم مورد استفاده قرار میگیرد که فعلاً موضوع بحث این سایت نیست، خود Application اکسل یک شی (Object) است، اشیا در ویژوال بیسیک میتوانند در برگیرنده اشیا دیگر باشند، مثلاً شی Application در برگیرنده ی شی Workbook میباشد که به معنی فایل اکسلی که در آن کد نویسی میکنید میباشد که خود در برگیرنده ی شی (Object) شیت (Worksheet) میباشد و شیت در برگیرنده اشیا دیگر میباشد، این بحث در مقاله ای جداگانه به تفصیل بررسی خواهد شد.

هر شیء توسط یک کلاس توصیف و تعریف (Define) شده است.

Properties

پروپرتی یا Properties در مورد اشیا همانند تنظیمات آنها میباشند، مثلاً یک شی فونت (Font) دارای تنظیماتی مانند Bold بودن میباشد که یک پروپرتی Boolean (دو حالت دارد یا True و یا False) است، شی فونت زیر مجموعه شی Range است.

کلاس

کلاس ها یا Class ها در واقع توصیف هایی (Definition) برای Object ها میباشند، کلاس به عنوان یک Template که یک شیء را در زمان اجرای کد ها بوجود می آورند عمل میکنند. کلاس ها خواص (Properties) اشیا و روش های استفاده شده برای کنترل رفتار شی را توصیف میکنند. کلاس ها و ماژول ها خود جز اشیا هستند و خود آنها جزیی از شیء VBA میباشند.

 

پنجره کد نویسی

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

پنجره کد نویسی شیت ها

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

Container

یک شیء که در بر گیرنده ی اشیاء دیگر میباشد را Container میگوییم.

Method

متد ها (Methods) یا روش ها، Procedure هایی هستند که بر روی اشیا عمل میکنند. هر متد یک Procedure است که بر روی یک شیء عمل میکند.

Range

محدوده یا Range به یک یا چند سلول اکسل گفته میشود، هر Range یا از یک سلول تشکیل شده و یا از تعدادی سلول که در کنار یکدیگر قرار دارند. به عنوان مثال A1 یک Range است که دارای یک سلول میباشد و A1:B3 هم یک Range با تعداد شش سلول میباشد.

Cells

Cells هم مانند Range برای اشاره به سلول های اکسل در کدهای ویژوال بیسیک استفاده میشود، با این تفاوت که Cells تنها به یک سلول اشاره میکند.

ضبط ماکرو در اکسل

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

کاربرد ضبط ماکرو در اکسل

ضبط ماکرو در ا کسل به کاربر این امکان را میدهد که از انجام یه رشته عملیات تکراری در اکسل پرهیز نماید و انجام آن ها را بر عهده ی ویژوال بیسیک در اکسل قرار دهد.

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

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

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

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

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

در قسمت “Store Macro in” میتوانید تعیین نمایید که ماکرو شما در کجاها قابل دسترس باشد. اگر گزینه ی “Personal Macro Workbook” را انتخاب نمایید میتوانید به این ماکرو در تمام فایل های اکسل بر روی سیستم خودتان دسترسی داشته باشید. در صورت انتخاب گزینه ی “This Workbook” این ماکرو تنها در همین فایل اکسل در دسترس قرار میگیرد و قابل استفاده است و در صورت استفاده از گزینه ی “New Workbook” این ماکرو در فایل اکسل جدید که بر روی سیستم شما بلافاصله ایجاد میشود قابل مشاهده و کاربرد است.

نام گذاری ماکرو در اکسل

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

• نام به انتخاب شده نباید جز نام های عناصر یا اشیا اکسل باشد، مثلاٌ شما نمیتوانید نام Excel را برای ماکروی خود انتخاب کنید.
• در نامگذاری ماکرو در اکسل از فاصله بین کلمات نمیتوانید استفاده نمایید و تنها کاراکتر قابل قبول بین کلمات “Under Line” میباشد.
• نام ماکرو ها نباید با عدد شروع شود
• در صورتی که شما نام غیر قابل قبول برای ماکرو در اکسل انتخب کنید، اکسل به شما اخطار داده و نام وارد شده را قبول نمیکند.

ضبط ماکرو در اکسل

پس از پیمودن مراحل فوق، ضبط ماکرو در اکسل آغاز شده و از این به بعد تمام کارهای شما به صورت کد در برنامه ویژوال بیسیک ذخیره میگردد. در صورت اتمام عملیات خود بر روی آیکون ضبط ماکرو در اکسل که حال تبدیل به آیکون “Stop Recording” شده کلیک نمایید تا ضبط ماکرو در اکسل متوقف گردد.

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

برای اجرای ماکرو در اکسل در تب Developer گزینه ی ماکرو (Macros) را انتخاب نمایید تا به شما لیستی از ماکروهای موجود در فایل اکسل شما نمایش داده شود، از این مسیر می توانید ماکرو مورد نظر خود را  انتخاب نموده و یا با استفاده از گزینه ی Edit به ویرایش ماکرو در اکسل بپردازید. پنجره Macros در شکل زیر نشان داده شده است.

برای دسترسی ماکرو های ضبط شده در اکسل، علاوه بر مسیر فوق میتوانید با کلیک بر روی آیکون Visual Basic در تب Developer در اکسل وارد محیط ویژوال بیسیک شده و ماکرو مورد نظر را ویرایش یا اجرا نمایید.

 

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

ذخیره ماکرو در اکسل

ذخیره و انتقال ماکرو در اکسل در مقاله ای جداگانه مورد بررسی قرار خواهد گرفت.

در این مقاله به این نکته اکتفا میکنیم که برای ذخیره ی فایل های اکسل باید از گزینه ی Save As و Excel Macro-Enabled Workbook استفاده نمود، در غیر اینصورت کد های ماکرو شما از بین خواهند رفت و دیگر به آن ها دسترسی نخواهید داشت.

انواع داده در اکسل

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

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

اعداد در اکسل

اعداد در اکسل از اهمیت بسیاری برخوردارند، در مراتب حرفه ای تر کار با اکسل و مخصوصاً در یادگیری و استفاده از برنامه نویسی ویژوال بیسیک در اکسل، به اهمیت شناخت و تفکیک انواع داده در اکسل و کار با اعداد پی خواهیم برد. حتی اعداد نیز خود به چند دسته تعریف میشوند که در توصیف نوع متغیرها در ماکرو نویسی در اکسل بسیار مهم میباشد. داده های عددی در اکسل ممکن است حتی به فرمت متنی نیز ذخیره شوند که انجام اینکار گاهی لازم میباشد.  توابع ریاضی و مثلثاتی در اکسل (Math & Trig Functions) از جمله توابعی هستند که بر روی داده های عددی عملیات انجام میدهند.

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

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

مالی و حسابداری در اکسل

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

برای کار با توابع حسابداری و انجام امور مالی در اکسل لازم است کاربران با داده های نوع Currency آشنا باشند، لذا این نکته اهمیت این نوع داده ها را آشکارتر مینماید.

تاریخ و زمان در اکسل

از جمله پر کاربردترین ابزارهای اکسل در حوزه های امور اداری، حسابداری و مستند سازی، توابع تاریخ و زمان (Date & Time Functions) می باشند، برای یادگیری کار با توابع تاریخ و زمان و البته انجام عملیات بر روی سلول های حاوی تاریخ و زمان، لازم است کاربران درک درستی از داده های نوع تاریخ و زمان داشته باشند.

 متن در اکسل

استفاده از توابع و داده های متنی چه در محیط اکسل و چه در زبان برنامه نویسی ویژوال بیسیک در اکسل همواره در سطوح بالاتر و حرفه ای تر قرار گرفته و این موضوع برای فایل های اکسل با زبان فارسی یادگیری مهارت های بیشتری را می طلبد. توابع متنی در اکسل (Text Functions In Excel) از جمله توابع بسیار مهم و عمومی در اکسل به شمار میروند که ماموریت آنها انجام عملیات بر روی داده های متنی میباشد. لذا شناخت داده های متنی و روش تعریف آنها از اهمیت بسیاری برخوردار است.

داده های Boolean در اکسل

داده های Boolean در اکسل داده هایی هستند که صرفاً میتوانند یکی از دو حالت True و False باشند. لازم به ذکر است در اکسل و ویژوال بیسیک، همواره عبارت False برابر با عدد صفر و عبارت True برابر با عدد یک میباشد. پس داده های Boolean داده هایی با دو مقدار صفر یا یک میباشند. البته داده ی نوع Null هم وجود دارد که به معنی Not True و Not False است. در آینده در مورد این نوع داده ها بیشتر اطلاعات کسب خواهیم کرد.

توابع منطقی در اکسل (Logical Functions) که از ارکان اکسل به شمار میروند و توابع اطلاعات در کسل (Information Functions) از جمله توابعی هستند که با داده های نوع Boolean سر و کار دارند.