نحوه استفاده از تابع SUBTOTAL اکسل

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

از تابع SUBTOTAL اکسل برای انجام عملیاتی مانند جمع کردن، میانگین گرفتن و … روی یک محدوده خاص استفاده می کنیم. بر خلاف توابعی مانند SUM، AVERAGE و … که تنها یک عملیات مشخص رو روی یک محدوده یا RANGE انجام می دهند، تابع SUBTOTAL اکسل این قابلیت را دارد  که با استفاده از پارامتری که به آن  میدهیم عملیات های مختلفی ا روی محدوده مدنظر انجام می دهد

قاعده کلی تابع SUBTOTAL اکسل:

=SUBTOTAL(محدوده مدنظر; شماره تابع)

شماره تابع: این پارامتر عملیاتی راکه میخواهیم روی محدوده مد نظر انجام انجام دهیم. این عملیات ها شامل موارد زیر  ا ست. دقت کنید که در شماره تابع های 1 تا 11، تنها سلول هایی که فیلتر شده باشند لحاظ خواهند شد. شماره های 101 تا 111 دقیقا مشابه توابع 1 تا 11 هستند با این تفاوت که در این شماره توابع، سلول هایی که HIDE شده باشند نیز لحاظ نخواهند شد:

شماره تابع	تابع	توضیحات
1	AVERAGE	میانگین سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
2	COUNT	تعداد سلول های محدوده مدنظر رو شمارش می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
3	COUNTA	تعداد سلول های محدوده مدنظر رو شمارش می کنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
4	MAX	ماکزیمم سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
5	MIN	مینیمم سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
6	PRODUCT	حاصلضرب سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
7	STDEV.S	انحراف معیار نمونه سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
8	STDEV.P	انحراف معیار سلول های محدوده مدنظر رو حساب می کنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد

محدوده مدنظر: این محدوده سلولهایی که قراراست  عملیات روی آنها  انجام شود رانشان می دهد.        

تعیین شماره تابع SUBTOTAL اکسل:

همانطور که میدانیم تابع جمع در اکسل SUM است. پس کافیست  شماره تابع را  برابر 9 قرار بدیم. (در صورتی که بخواهیم سلول های HIDE شده هم در جمع لحاظ شوند  می توانیم بجای 9 از شماره 109 استفاده کنیم).

تعیین محدوده مدنظر:

 اگر تعداد محصولات در سلول های D5 تا D14 قرار دارد  پس محدوده مدنظر ما برای جمع، محدوده D5 14 است.

در نهایت فرمول تابع SUBTOTAL اکسل ما به شکل زیر کامل میشه:

=SUBTOTAL(9;D5 15)

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

 

نکته:

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

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

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

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

گام اول: با اطلاعات زیر شروع می کنم.

 

ترکیب چارت های مختلف در اکسل

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

گام دوم: داده های گام اول را انتخاب کنید. از زبانه Insert در ریبون بالا insert a column chart را انتخاب نمایید.

 

حالا یک نمودار ستونی با دو سری داریم. که هر دو با یک محور عمودی (Axis) نمایش داده شده است. تمام داده های ما همین است اما این چارت برای ما اطلاعات خیلی معناداری را نشان نمی دهد. زیرا مقیاس در گردش مالی (Transactions) و مقدار فروش (Units Sold) متفاوت است. گام بعدی تغییر نوع چارت گردش مالی (Total Transactions) از ستونی به خطی است.

گام 3: یکی از سری ها را انتخاب نمایید. که در اینجا ما سری (Total Transactions) را انتخاب می کنیم. شما از راههای متعدد موجود می توانید یکی را برای انتخاب سری مورد نظر انتخاب کنید.

گزینه 1: بر روی سریها روی چارت کلیک نمایید. (لطفا روی legend کلیک نکنید. روی ستونهای قرمز روی چارت کلیک کنید).

گزینه 2: بر روی ریبون روی زبانه Chart Layout کلیک نمایید. (دقت نمایید که این زبانه فقط در هنگام انتخاب چارت فعال می شود و در بقیه موارد قابل رویت نیست. پس اگر این زبانه را نمی بینید روی چارت دوباره کلیک کنید). در قسمت انتهایی چپ ریبون Current Selection را می بینید. که در آن یک منو dropdown به نام  Chart Area وجود دارد.                                                                                                           

از داخل این منو سریی که می خواهید در آن تغییر ایجاد کنید انتخاب نمایید. (در اینجا (Total Transactions.

گام 4: نوع چارت برای سری انتخاب شده را تغییر دهید. برای این کار ابتدا بر روی ریبون به زبانه Design بروید. سپس در قسمت انتهایی سمت چپ روی Change Chart Type کلیک نمایید.

این کار باعث رویت پنجره Change Chart Type خواهد شد. در این حالت نوع چارت دلخواه را انتخاب نمایید مثلات چارت خطی را انتخاب می کنیم.

 

اکنون چارتی داریم با دو نوع نمودار! می توانیم هر تعداد که بخواهیم نمودارهای مختلف را در این چارت با هم ترکیب کنیم.

 

اضافه نمودن یک محور دیگر (Secondary Axis) در نمودار در اکسل 

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

گام 5: در این مرحله سری داده هایی که می خواهید در محور دوم قرار گیرد را انتخاب نمایید. در اینجا ما سری Total Transactions را انتخاب می کنیم. این کار را مانند گام 3 انجام دهید.

گام 6: دوباره به زبانه Layout بروید. و در Current Selection گزینه Format Selection را انتخاب نمایید.

گام 7: با کلیک Format Selection پنجره زیر باز خواهد شد. در این پنجره Primary Axis انتخاب شده است. شما Secondary Axis را انتخاب کنید و پنجره را ببندید.

فوق العاده ست! با موفقیت توانستید محور دوم را در چارت ایجاد کنید!

 تنظیم فرمت چارت در اکسل

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

گام 8: در قسمت Layout روی ریبون، بر Show Legend at Bottom روی قسمت Legend کلیک کنید.

 

گام 9: محور عمودی را تغییر دهید تا علامت دلار را نمایش دهد. برای این کار بر روی لیبل محور عمودی کلیک راست کنید و Format Axis را انتخاب کنید.

 

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

 

گام 10: اضافه کردن عنوان برای محورها. دوباره به زبانه Layout بروید و Axis Titles را انتخاب کنید. از لیست dropdown، Primary Vertical Axis را انتخاب نمایید. هر کدام را می خواهید انتخاب کنید. من Rotated Title را انتخاب کردم.

 

گام 11: به چارتمان یک عنوان هم اضافه می کنیم. بازهم گزینه Layout را در ریبون انتخاب می کنیم. سپس بر روی گزینه Chart Title کلیک می نماییم. هر عنوانی که می خواهید انتخاب کنید. نتیجه نهایی کار بدست آمد!

 

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

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

برای این کار ابتدا در ستون A از Sheet1 مقادیز زاویه ها را به فاصله 10 تا 360 وارد می کنیم. (می توانید به دلخواه فواصل زاویه ها را کم یا زیاد کنید) 

در حالت پیش فرض مقدار زاویه در اکسل در واحد رادیان محاسبه می شود. مثلاً می دانیم که سینیوس زاویه 30 درجه برابر 0٫5 می باشد اما اگر فرمول را به صورت =sin(30) بنویسیم نتیجه عدد -0٫98803 خواهد شد. این مقدار برابر سینیوس زاویه 30 رادیان می باشد.

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

برای تبدیل رادیان به درجه به این شکل عمل می کنیم. R*pi()/180=D یعنی مقدار رادیان را در عدد پی که تقریباً برابر (3٫14) ضرب کرده و بر 180 تقسیم می کنیم. (اگر از مقدار 3٫14 به جای عدد پی استفاده کنیم مقادیر بدست آمده با مقادیر واقعی اختلاف اندکی خواهند داشت)

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

برای این منظور از سربرگ Developer یک کامبوباکس را در یک سلول دلخواه قرار می دهیم.

تذکر: سربرگ Developer در حالت پیش فرض فعال نیست، برای قرار دادن آن در نوار ابزار مطابق تصویرهای زیر عمل نمایید

 

 

برای قرار دادن کامبوباکس، پس از انتخاب بر روی قسمتی از صفحه کلیک کرده و کلید چپ موس را نگهداشته و به یک سمت بکشید. بر روی کامبو باکس راست کلیک کرده و Format control را انتخاب می کنیم. در پنجره تنظیمات و در قسمت Input range  محدوده  C2:C3 که در این مثال در آنها عبارت "سینوس و کسینوس " نوشته شده است را انتخاب می کنیم. و در قسمت Cell link یک سلول مثل C4 را انتخاب می کنیم. (سلول لینک با انتخاب سینوس عدد 1 و با انتخاب کسینوس عدد 2 را نمایش می د

اکنون با استفاده از اولین دستور IF که در سلول مقابل اولین زاویه یعنی سلول B2 نوشته می شود، اگر عدد سلول لینک شده به کامبوباکس 1 باشد مقدار سینوس و اگر عدد 2 باشد مقدار کسینوس زاویه محاسبه می گردد و نمودار بر اساس این مقدار رسم می گردد.

فرمول نوشته شده به این شکل می باشد.  

((IF($C$4=1,SIN(A2*3.14/180),COS(A2*3.14/180=

برای بقیه زوایا این فرمول را تا مقابل زاویه 360 رادیان کپی می کنیم

برای رسم نمودار، ابتدا محدوده داده های جدول (مقادیر ستون B که براساس درجه هستند) را انتخاب کرده و از سربرگ Insert از گروه Chart یکی از انوع نمودار Scatter را انتخاب می کنیم.

می توانید ظاهر نمودار را به دلخواه تغییر دهید

 

 

 

فرمولهای 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 به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری که در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی که مقدار دادن به آنها اختیاری است در داخل کروشه نشان داده می شوند.                                                                                                 امید واریم از آموزش اکسل امروز نیز استفادهکنید.

برای مرتب کردن داده‌ها (Sort)  می توان از ابزار Sort در تب Data استفاده کرد که شامل دو حالت مرتب سازی از کوچک به بزرگ و بزرگ به کوچک (یا به ترتیب حروف الفبا و عکس حروف الفبا) می‌باشد. ولی در برخورد با داده های کیفی ترتیبی هیچ یک از این دو گزینه مد نظر ما نیست.                                                                                                                                    با آموزش اکسل امروزهمره باشید

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

 

در این حالت، ابتدا باید ترتیب داده ها برای اکسل مشخص شود (این عمل یکبار صورت می گیرد و در تمامی فایلها قابل استفاده می‌باشد) به این صورت که ابتدا لیست تمامی ماه‌ها را به ترتیب در یک ستون وارد می‌نماییم و از تب File، قسمت Option، حالت Advanced را انتخاب کرده و در بخش General بر روی گزینه Edit Custom Listدراکسل  کلیک نمایید.                                                                                       سپس در قسمت پایین ادرس سلولهایی که ماههای شمسی در آنها ثبت شده است را وارد نموده و بر روی گزینه Import کلیک کرده تا لیست ماهها در قسمت Custom List مشاهده شود. از این مرحله به بعد در تمامی فایلهای اکسل، لیست و ترتیب ماههای شمسی تعریف شده است و می توان با نوشتن اسم یک ماه و اعمال کردن برای سلول های دیگر، لیست ماهها را به ترتیب داشته باشیم.

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

 

 

 

بعد از تعریف ترتیب ماههای شمسی، به منظور مرتب سازی اطلاعات بر اساس ماههای شمسی، کافی است که از تب Data، بر روی گزینه Sort کلیک کرده و در صفحه باز شده، در قسمت Column نام ستونی که اسامی ماهها در آن ثبت شده است را انتخاب نماییم و در قسمت Order گزینه Custom List را و در پنجره باز شده، از قسمت Custom List حالت ماههای شمسی را بر گزیده تا با Ok نمودن تمامی پنجره ها، داده ها به ترتیب ماههای شمسی مرتب شوند.

 

 

 

 اگر در یک ستون شماره سال و در ستون دیگر نام ماه شمسی ثبت شده باشد و بخواهیم داده ها به ترتیب سال مرتب شوند و داده های هر سال به ترتیب ماه، کافی است در پنجره Sort بر روی گزینه Add Level کلیک کرده تا دو قسمت برای تعریف معیار مرتب سازی ایجاد شود، سپس در قسمت بالایی ستون سال و در قسمت پایینی ستون ماه به صورت Custom List انتخاب شود.

 

 

                                                                                          

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

 

تعریف سابروتین در برنامه نویسی

 

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

 

نامهای procedure, method, function و routine همه نامهای دیگری برای سابروتین هستند و معادل آن در زبان فارسی "رویه" است.

 

در زبان برنامه نویسی ویژوال بیسیک اکسل اگر بخواهید دستوری را بنویسید که مثلا عدد 2+2 را محاسبه کنید، باید این دستور را در داخل یک سابروتین قرار دهید:

 

Sub Test()

 

a = 2 + 2

 

End Sub

 

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

توابع جدید به چه کاری می‌آیند

 

البته این سوال ممکن است به ذهن شما متبادر شود که چرا باید تابع جدیدی اضافه کرد.

 

شاید دلایل زیر بتواند گوشه‌ای از ارزش تابع را برای ما بیان کند:

• جلوگیری از کارهای تکراری در اکسل

 

• انجام محاسبات پیچیده

 

• دسترسی به کلیه امکانات یک زبان برنامه نویسی مانند ویژوال بیسک

 

• به اشتراک گذاشتن توابع با سایر کاربران

 

• استفاده سریعتر از نرم افزار

 

• جلوگیری از اشتباهات کاربران

آشنایی با ویژوال بیسیک

 

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

 

لازم به ذکر است که نسخه جدید نرم افزار VB به نام VB.NET نیز وجود دارد که امکان استفاده آن در OFFICE 12 که نسخه بعدی افیس است گنجانده شده و بد نیست بدانیم که VB.NET در واقع قابلیتهای زبان برنامه نویسی C# را دارد.

 

 

 

شما برای استفاده از VB در اکسل نیاز ندارید که نرم افزار VISUAL BASIC را نصب کنید، همراه با نصب افیس خود این نرم افزار نیز نصب می‌شود.

در ضمن VBA همان زبان برنامه نویسی ویژوال بیسک می‌باشد که در آن قابیلیتهای ویژه‌ای برای کار با اکسل،اکسس، ورد و... گنجانده شده است.

 

 

 

گام اول ورود به محیط ویژوال بیسیک

 

ابتدا بایستی وارد محیط VB شویم. برای اینکار چندین راه وجود دارد که عبارتند از:

• زدن کلید ALT+F11 

 

• از منوها: Tools à Macro à Visual Basic Editor

 

• از Toolbar  :

 

 

محیط ویژوال بیسک

 

 

 

 

 

گام دوم ایجاد یک ماژول

 

شما باید دستورات تابع خود را در یک Module (ماژول) بنویسید،  از منوی Insert گزینه Module را بزنید. و اگر به project explorer نگاه کنید متوجه خواهید شد که یک ماژول جدید ایجاد شده است.

 

 

نام ماژول جدید

اگر این پنجره نیست کلید Ctrl+R را بزنید

گام سوم ایجاد یک تابع درماژول

 

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

 

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

 

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

 

قانون سوم، نوع داده ورودیها (و خود تابع) باید مشخص شود.

 

این دستورات عبارتند از:

 

Private Function Test(Num As Integer) as Double

 

 

 

End Function

 

نام تابع ما test است و عبارت داخل پرانتز می‌گوید که این تابع یک ورودی دارد که نام آن ورودی Num است و integer  بیانگر آن است که این ورودی عددی صحیح است. (-32,768  تا 32,767)

 

خروجی تابع از نوع double است و البته گذاشتن آن در همه موارد الزامی نیست، گرچه بهتر است که مشخص شود. (برای اطلاع بیشتر به کتابهای برنامه نویسیم مراجعه کنید.)

 

عبارت Private Function نشانگر شروع تابع و End Function برای پایان تابع است.

 

 

 

 

 

گام چهارم – نوشتن تابع در محیط VBA

 

فرض کنید می‌خواهیم تابعی بنویسیم که یک عدد را بگیرد و آنرا در 10 ضرب کند!

 

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

 

خوب اسم آنرا Test می‌گذاریم و می‌دانیم که این تابع باید یک ورودی داشته باشد و خوب چون به تازگی با نوع عدد Integer  آشنا شدیم (عدد صحیح) نوع این ورودی را هم Integer می‌گذاریم.

 

باید نامی برای این ورودی در نظر بگیریم، این نام نباید یک نام آشنا! برای VB باشد و بهتر است نامی با مسما در نظر بگیریم، اینجا اسم این ورودی را Num می‌گذاریم.

                         

 

 

 

تعریف ماژول:

 

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

 

درج یک ماژول و سابروتین در VBA:

 

1- در ابتدا با زدن کلید Alt+F11 وارد محیط VBA اکسل شوید.

 

از منوی Insert گزینه Module را انتخاب کنید تا یک ماژول ایجاد شود. در سمت راست صفحه شما قاب Project Explorer را اگر مشاهده کنید خواهید دید که ماژول شما با نام Module1 نمایش داده می شود. (اگر Project Explorer را نمی بینید از کلید Ctrl+R استفاده نمایید)

 

3- سابروتین خود را بنویسید.

 

 

 

ذخیره فایل اکسل حاوی برنامه

 

در اکسل 2007 اگر فایل خود را با پسوند XLSX ذخیره نمایید، تمامی برنامه های (ماکروها) یا به عبارت صحیح تر تمامی ماژولها پاک می شوند. در واقع این اقدام امنیتی اکسل 2007 است که فایل حاوی یک برنامه وِیژوال بیسیک را با پسوند XLSA ذخیره می کند.

 

شما در اکسل 2003 نمی توانستید تعیین کنید که آیا فایل حاوی ماکرو - ماژول - برنامه (همه این اصطلاحات دارای یک معنی هستند) است یا نه و حتما باید فایل را باز می کردید تا متوجه این موضوع می شدید.

 

 

 

در شکل زیر تفاوت Icon فایلهای اکسل 2007 که حاوی ماکرو (برنامه) است با Icon یک فایل عادی اکسل 2007 را مشاهده می نمایید.

 

 

 

باز کردن فایل حاوی برنامه ویژوال بیسیک (ماکرو)

 

اکسل به صورت پیش فرض برنامه موجود در یک فایل را غیر فعال (Disable) می کند زیرا ممکن است که این برنامه به نوعی یک ویروس باشد و این موضوع یک اقدام پیشگرانه است که در تمامی نرم افزارهای خانواده آفیس از جمله اکسل وجود دارد.

 

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

 

سپس گزینه Enable this Content بعد از باز کردن فایل در اکسل2007 بر روی دکمه را بزنید

 

 

 

 

 

اجرای یک برنامه نوشته شده در محیط VBA  اکسل

 

همانطور که در بالا اشاره شد کوچکترین واحد یک برنامه سابروتین است و برای اجرای یک سابروتین کافیست در اکسل کلید Alt+F8 را بزنید تا پنجره زیر که لیستی از تمامی سابروتین ها در آن است را مشاهده نمایید و برای اجرای یک سابروتین، اول آنرا انتخاب کنید و سپس کلیدکنید.  

 

اگر مایل باشید می توانید برای اجرای ماکرو (سابروتین) در اکسل یک Shortcut key (یا Hotkey) نیز تعریف                                                                                                                                                 کلیک کنید

ایجاد دکمه جدید در صفحه اکسل:

 

از ابزار های DESIGN MODE  در منوی ماکر COMMAND BUTTON  را انتخاب می کنیم دکمه جدید را انتخاب کرده و بر روی آن دابل کلیک می کنیم تا صفحه ویژوال بیسیک باز شود.

 

در صفحه ایجاد شده دستور زیر را وارد می کنیم:

 

Range("A1:A10").Value = 10

 

 

 

با این دستور به اکسل گفته شده که A1 تا A10   را گرفته و به آن مقدار 10 را بدهد.

 

از عبارت  RANGE("@") برای مشخص کردن ناحیه ای در اکسل و از عبارت VALUE=.  برای تغییر مقدار سلولها استفاده می کنیم.

 

حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE  را می زنیم.

 

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

 

حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE  را می زنیم.

 

وقتی که از حالت DESIGN خارج شدیم حال بر روی دکمه کلیک کرده و مشاهده می کنیم که درسلهای مشخص شده ارزش عددی 10 را قرار می دهد.

 

می توان به جای 10 یک عبارت را در داخل "   "  قرار داد و در این حالت به جای 10 آن عبارت نوشته می شود.

 

توجه: در صورتی که از علامت '  در ابتدای یک خط برنامه نویسی در محیط VB استفاده شود آن خط سبز شده و عملیاتی بر روی آن خط اجرا نخواهد شد. این خط اصطلاحا Comment (توضیحات)‌نامیده می شود. Comment حالت اجرایی ندارد و از آن برای ارایه توضیحات استفاده می شود. Comment ها به خوانایی برنامه بسیار کمک می کنند.

 

 

 

دستور زیر را می نویسیم:

 

Range("D1").Value = Range("B1") + Range("C1")

 

دستور زیر را در یک سط دیگر می نویسیم:

 

Range("E1").Value = "=A1+A2"

 

ملاحظه می شود که در سطر 1E ارزشهای موجود در A1 و A2 جمع زده می شود.

 

نکته: به علامت = در داخل کوتیشن توجه شود.

 

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

 

دستور SELECT

 

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

 

دستور زیر را وارد کنید:

 

Range("F1:F10").Select

 

و دکمه را RUN  می کنیم.ملاحظه می شود که سطرهای مورد اشاره های انتخاب شد.

 

می توان به جای: از علامت ,  استفاده نمود که در این حالت به معنی AND می باشد.

 

مواردی که در بالا اشاره گردید خلاصه ای از سه دستور RANGE VALUE SELECT بود که اشاره ای مختصر به آن گردید.

 

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

 

 

 

 

 

1. شروع برنامه نویسی

 

روشهای مختلفی برای شروع  و ایجاد یک برنامه با VBA وجود دارد. روش اصلی و مستقیم  نوشتن کدها در داخل  ویرایشگر VB  یا همان     ‌VBE  می‌باشد. یک روش ساده دیگر نیز وجود دارد و آن ایجاد یک command button بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی  آن است. کار را با روش اول شروع می‌کنیم

 

1.1        روش اصلی: ایجاد و اجرای یک برنامه در VBE

 

برای ایجاد یک برنامه در محیط برنامه نویسی اکسل مراحل زیرانجام می‌شود:

 

1ورود به محیط برنامه نویسی یا همان Visual Basic Editor

 

 برای انجام اینکار چند روش وجود دارد:

 

·        استفاده از کلیدهای میانبر:  F11  + ALT ‌

 

·        استفاده از منوی اکسل:  Tools-> Macro-> Visual Basic Editor

 

·        استفاده از ToolBar یا همان نوار ابزار Visual Basic و فشردن آیکون Visual Basic Editor

 

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

 

2- ایجاد Module: ورود به  منوی  Insert  و انتخاب گزینه Module  

 

3- ایجاد یک رویه یا در اکسل

 

نوشتن فرمان (کلمه کلیدی)  Sub ‌ و سپس نام برنامه

 

فشردن دگمه  Enter   

 

بلافاصله بطور اتوماتیک فرمان End Sub  در یک خط جدید اضافه می‌شود.

 

4- نوشتن کدهای برنامه VBA: کدهای برنامه را خط به خط  بین فرمانهای Sub  و End Sub می‌نویسیم.

 

 

 

 به عنوان مثال:

 

Sub MyProgram()

 

Range("A1:A10").Value = "Visual Basic For Applications"

 

Range("A11")=10

 

Range("B11").Value = 20

 

Range("C11").Value = "=A11+B11"

 

End Sub

 

5- اجرای برنامه: برای اجرای برنامه چندین روش وجود دارد

 

·         فشردن کلید F5

 

·         فشردن آیکن Rub Sub/User Form از   Toolbar

 

·         بازگشت به محیط   Excel و استفاده از F11  + ALT 1.2        روش دیگر: ایجاد یک برنامه با استفاده از command button

 

در این روش مراحل زیر را انجام می دهیم

 

 6- ایجاد command button

 

برای قرار دادن  command button بر روی محیط اکسل مسیر زیر را طی می‌کنیم:

 

View منوی  àToolbaràControl ToolBox

 

(البته اگر قبلا نوار ابزار Visual Basic Editor نیز فعال شده باشد می توانید Control ToolBox را بطورمستقیم از آن انتخاب کنید.)

 

بعد از طی مسیر فوق Control ToolBox ظاهر می شود. command button را انتخاب و آنرا بر روی صفحه گسترده محیط اکسل اکسل قرار میدهیم. یک دگمه در محیط اکسل ظاهر می شود.

 

 

 

  نوشتن کدهای VBA:

 

 با کلیک کردن بر روی command button  بلافاصله  Visual Basic Editor یا  همان  VBE  ظاهر می‌شود. بلافاصله می توانید دستورات خود را همانند شکل تایپ کنید. (شکل بعدی)

 

 

 

با دقت در این کدهای VBA ملاحظه می‌شود که: دستور اول مقدار(Value) سلولهای A1 تا  A10را با عبارت "Visual Basic for Application" پر می‌کند. دستور بعدی نیز حاصل جمع سلولهای  A11  و B11 را در سلول C11 قرار می دهد. دستور آخر نیز مقدار سلول D11 را نیز با روش دیگری برابر با حاصلجمع A11  و B11 قرار می دهد. با اندکی دقت متوجه می‌شویم که این دستور با دستور قبلی متفاوت است.  خروجی و رابط برنامه

 

اجرای برنامه:

 

 برای اجرای این برنامه به محیط اکسل برمی‌گردیم. (می توان از F11  +   ALT ‌نیز استفاده کرد). با فشردن آیکن Exit Design Mode   از    Control Toolbox از حالت طراحی خارج می‌شویم. پس از آن با کلیک بر روی  command button برنامه اجرا می‌شود که خروجی آن در شکل نشان داده شده است.

 

دستور CELLS:

 

این دستور با استفاده از مختصات سلولها آنها را مشخص می کند. به عنوان مثال  CELLS(2,5)  سطر 2 و ستون 5 یعنی E2را مشخص می کند.

 

در دستور: 'Cells(2,5).Value = 15 در سل 2و5 ارزش 15 را قرار می دهد

 

 

 

 

 

دو دستور زیر یک مفهوم را در بر دارند:

 

Cells(2,5).Select

 

RANGE("E2").SELECT

 

در دستور زیر

 

Range(Cells(2, 5), Cells(1, 5)).Value = 10

 

 در سلهای مشخص شده ارزش 10 را می گذارد

 

دستور:

 

Range(("A1"), Cells(1, 5)).Value = 10

 

یک دستور ترکیبی می باشد. که هم از Range استفاده شده و هم از دستور CELLS

 

ملاحظه شد که به 2 صورت می توان سلها را از دو دستور  دستورRANGE  و دستور CELLS  انتخاب کرد و از علامتهای با علامتهای ":" یا  " ," استفاده نمود.

 

 

 

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

 

 

 

 

 

 

 

انتخاب سطر و ستون

 

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

 

Range("A:F").Value = 100

 

Range("1:5").Value = 100

 

که مشخص شده در آنها مقدار100 گذارده شود.در این دستورها تمامی ستونهای مابین و تمامی سطهای ما بین نیز ارزش مشخص شده را می گیرند.

 

 

 

دستور زیر:

 

Range("A:A,F:F").Value = 100

 

دستوری است که به برنامه می گوید ستون A و ستون F را به تنهایی ارزش 100 بگذار.

 

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

 

 

 

دستور زیر:

 

Range("A1:A5,C1:C5,D1 5").Value = 4

 

را وارد کنید و نتیجه را با RUN  نمودن ببینید.

 

معرفی سلولها با استفاده از متغیرها 

 

دستور زیر را واد کنید:

 

A = 1

 

B = 2

 

C = 3

 

Range(Cells(A, A), Cells(B, B)).Value = 6

 

که در این صورت متغیرهای A B C  را می توان برای برنامه تعریف کرد و در دستور CELLS  ا

 

 

 

 

 

RExcel  آنالیز آماری در Excel

 

در نرم افزار Excel  توانایی کار با ریاضیات و آمار در حد متوسطی قرار داده شده است و برای حل مسائل آمار پیشرفته و جوابهای قابل اطمینان تر به Add-in هایی نیازمندیم. یکی از بهترین این Add-in ها R است.

 

R بسته ای قابل توسعه (برنامه نویسی) برای کار با داده ها، انجام آنالیزهای آماری و نمایش داده ها به صورت نموداری.

 

نرم افزار RExcel محیط R را بیش از هزار قابلیت بر روی نرم افزار Excel به صورت یک Addins آماده استفاده برای کاربران این نرم افزار می نماید.

 

بنابراین R نرم افزاری برای آنالیز آماری و نمایش گرافیکی داده ها.

 

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

 

توسط این نرم افزار می توان کارهای زیر را نیز انجام داد:

 

* مدیریت داد ها و ابزارهای ذخیره آنها.

 

* ابزاری برای کار با ماتریس ها

 

* قابلیت توسعه و برنامه نویسی قدرتمند با زبانی که به آن S می گویند، که با در این زبان می توان حلقه ها، شرطها و انواع توابع را برنامه نویسی کرد.

 

 

 

RExcel یک Interface – رابطه گرافیکی با کاربر – می باشد که نرم افزار R را به شکل یک Add-in در اکسل نصب می کند.

 

توسط RExcel  می توان داده ها را بین Excel   و R رد و بدل نمود و در Excel می توان از توابع R استفاده نمود. سایر امکاناتی که RExcel در اختیار ما قرار می دهد:

 

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

 

* کنترل Missing Data

 

* نصب خط فرمان R Commander و همچنین Menu در اکسل

 .  Ctrl+R، برای اینکار در شکل بالا روی گزینه Options کلیک کنید.

ll بطور خودکار فعال است. جهت غیر فعال نمودن آن مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب منوی Options
3-    انتخاب: Edit Tab
4-    کادر انتخاب Allow Cell Drag and Drop را غیر فعال می کنیم
 
آدرس دهی در اکسل:
آدرس دهی نسبی:
فرض کنید سلولهای Excel را به صورت زیر پر کرده ایم:
در سلول B1 فرمول A1 A2 را می نویسیم. اگر این فرمول را copy کرده و در سلول B2. Paste کنیم و یا با استفاده از Auto fill محتوای سلولهای B2 تا B4 را پر کنیم.اعداد 5و7و4 به ترتیب برای سلولهای B2 تا B4 ظاهر می شوند. حال میخواهیم بدانیم این اعداد از کجا بدست آمده اند. وقتی ما در سلول B1 فرمول A1 A2 را تایپ می کنیم، در حقیقت سلول سمت چپی و یک سلول پایین آن با هم جمع می شوند. پس برای سلول B2، سلول سمت چپی (A2) و سلول پایینی آن (A3) با هم جمع می شوند که جواب 5 می شود. برای سلولهای بعدی هم به همین ترتیب محاسبات انجام می شود. به این نوع آدرس دهی، آدرس دهی نسبی گفته می شود چون نسبت به مکان هر سلول، فرمول سلول عوض می شود و در حقیقت فرمول درون سلول B2، A2 A3 می شود.
 
 
آدرس دهی مطلق:
فرض کنید میخواهیم حقوق افراد یک اداره را حساب کنیم. حقوق با استفاده از فرمول زیر حساب می شود: حقوق پایه * 7% - حقوق پایه = حقوق حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق می توانیم در سلول C1 فرمول B1-B1*7% را بنویسیم و برای تمام افراد Auto fill کنیم. در این حالت در حقیقت از آدرس دهی نسبی استفاده کرده ایم.
ولی فرض کنید که درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill کنیم. برای اینکه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض کنیم می توانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده کنیم. پس مثلاً در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1*A1 را مینویسیم حال Auto fill می کنیم. ولی می بینیم که برای بقیه سلولها جواب همان حقوق پایه می شود. چرا؟ چون با آدرس دهی نسبی میخواهد عمل کند و چون در سلول C1 فرمول B1-B1*A1 بوده، برای C2 فرمول B2-B2*A2 می شود که مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه می شود. برای رفع این مشکل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این حالت از آدرس دهی مطلق استفاده می کنیم. برای ثابت کردن سطر یا ستون در کنار حرف ستون یا عدد سطر علامت $ می گذاریم. اگر بخواهیم سلول را ثابت کنیم در کنار حرف ستون و عدد سطر هر دو علامت $ می گذاریم. یعنی در سلول C1 مینویسیم: B1-B1*$A$1
 
قالب بندی خانه‌ها در Excel
در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین می توانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم.
برای قالب بندی خانه‌ها در Excel مراحل زیر را طی می کنیم:
روش اول:
1-    انتخاب منوی Format
2-    انتخاب گزینه Cells
روش دوم:
بر روی خانه‌های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم:
فشردن کلیدهای ctrl 1
با اجرای یکی از سه روش بالا پنجره ای باز می شود که دارای Tab‌های زیر است:
 
الف - Number:
توسط این Tab می توانیم نوع اطلاعات ورودی را تعیین کنیم. این Tab شامل گزینه‌های زیر می باشد:
Sample: هر فرمتی را که انتخاب کنیم، بر روی محتوای سلول انتخابی نمایش می دهد.
 
الف) Category: در این قسمت نوع داده را مشخص می کنیم که شامل انواع زیر می باشد:
1- General:       این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان می دهد.
2- Number:       مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود. همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم:
-      Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.
-      Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)
-      Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست این گزینه می گذارد.
3- Currency:     مقادیر را همراه با سمبل جدا کرده و علامت (،) پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد:
-      Symbol: نوع واحد پول را مشخص می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.
-      Negative Number: اعداد منفی چطور نمایش داده شوند.
4- Accounting:    مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در انتهای سمت چپ آن نوشته می شود.
5- Date:           تاریخ را با قالب بندی خاص تاریخ نشان می دهد. و ما می توانیم انواع قالب بندی‌های تاریخ را دیده و انتخاب کنیم. مثلاً مدلی را انتخاب کنیم که فقط روز و ماه را نشان دهد. و یا مدلی را انتخاب کنیم که روز را به عدد و ماه را به حروف نشان دهد.
6- Time:           زمان را با قالب بندی‌های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا....
7- Percentage:   عددها را همراه با علامت درصد نشان می دهد.
 
نکته: در تمام قالب بندی‌ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.
8- Fraction:       مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک‌ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.
9- Scientific:      مقادیر را با قالب بندی علمی نشان می دهد.
10- Text:         مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.
11- Special:      مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT می باشد.
12- Custom:     برای ایجاد یک قالب بندی جدید از این گزینه استفاده می کنیم که خارج از بحث ما می باشد.
 
ب) Alignment: توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین کنیم. این Tab شامل قسمتهای زیر است:
1-     Horizontal: محل قرار گرفتن افقی متن را تعیین می کند. این قسمت شامل گزینه‌های زیر است:
-      General: هم ترازی پیش فرض
-      Left: هم ترازی داده‌ها را به سمت چپ (استفاده برای ارقام)
-      Center: هم ترازی داده‌ها در مرکز
-      Right: هم ترازی داده‌ها به سمت راست (برای متون)
-      Fill: تمام سلول را با متنی که در آن نوشته شده است، پر می کند.
-      Justify: یک تراز مناسب برای سلول در نظر می گیرد. معمولاً برای زمانی است که اطلاعات در سلول نگنجد.
-      Center Across Selection: وسط متن را در وسط سلولهای انتخابی می گذارد.
2-     Vertical: محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می کند. این گزینه شامل قسمتهای زیر است:
-      Bottom: هم ترازی داده‌ها در پایین سلول
-      Top: هم ترازی داده‌ها در بالای سلول
-      Center: هم ترازی داده‌ها در وسط یا مرکز سلول
-      Justify: داده‌ها در داخل سلول هم تراز می شوند. (بدین معنی که داده‌ها در داخل سلول بصورت مساوی جاسازی می شوند. مانند متون موجود در روزنامه ها)
 
3-     Orientation: شامل قسمتهای زیر است:
-      زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین کرد.
-      Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید‌های Increase و Decrease تعیین کرد.
 
4-     Text Control: شامل قسمتهای زیر است:
-      Wrap Text: اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
-      Shrink to Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
-      Merge Cells: اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث می شود که این سلول‌ها بهم پیوسته و یک سلول شوند.

- تصور کنید که چقدر بالا انداختن و کنترل دوجین توپ به‌طور همزمان دشوار است!

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

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

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

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

در این آموزش  نگاهی به این موضوع خواهیم داشت که چرا گانت چارت‌ها سودمند هستند و اینکه چطور می‌توانید آنها را به کار گیرید تا پروژه‌های خود را سازماندهی کنید.

تاریخچه این ابزار

در اواخر سال 1800، یک مهندس لهستانی به نام کارل آدامیک نمودار گردش کار تصویری ایجاد کرد که نام آن «هارمونوگرام» بود.

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

 

چرا باید از گانت چارت استفاده کنیم؟ 

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

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

ساخت یک گانت چارت درEXCEL

برای ساخت گانت چارت برای پروژه خود، مراحل زیر را انجام دهید: 

قدم اول: وظایف ضروری را شناسایی کنید. 

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

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

مثال:

سازمان شما مزایده ساخت «نرم‌افزار خدماتی» جدیدی را برنده شده است و شما مسوول پروژه هستید. شما تصمیم می‌گیرید که از گانت چارت برای ساماندهی فعالیت‌های ضروری استفاده کنید و زمان کلی تقریبی برای تحویل پروژه را نیز محاسبه کنید. 

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

* قدم دوم: شناسایی ارتباط میان وظایف 

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

این وظایف، وظایف «زنجیری» یا «خطی» نامیده می‌شوند. 

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

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

گانت چارت را آغاز کنید.

نکته:

در گانت چارت سه رابطه اصلی بین وظایف زنجیره‌ای وجود دارد:

* پایان به شروع: وظایف نمی‌توانند قبل از به پایان رسیدن وظایف قبلی (مرتبط) به پایان برسند. اگرچه، می‌توانند بعدا شروع شوند. 

* شروع به شروع: وظایف نمی‌توانند قبل از شروع وظیفه قبلی شروع شوند. اگرچه می‌توانند بعدا شروع شوند. 

* پایان به پایان: وظایف نمی‌توانند قبل از به پایان رسیدن وظیفه قبلی به پایان برسند.

* نوع چهارم، شروع به پایان، بسیار نادر است.

تذکر 1: 

وظایف می‌توانند بطور همزمان زنجیره‌ای و موازی باشد- برای مثال، دو وظیف (ب و پ) وابسته و وظیفه‌ای دیگر (الف) است و ممکن است بطور همزمان به پایان برسند. وظیفه (ب) که به‌دنبال وظیفه (الف) انجام می‌شود، زنجیره‌ای و با توجه به وظیفه (ت) موازی است.

تذکر 2:

برای کاهش زمان تحویل، باید تا آنجایی که ممکن است به‌صورت موازی کار کنید. همچنین باید گستره پروژه را تا حد ممکن کوچک نگه دارید. (جدول شماره 2) 

* قدم سوم: وارد کردن اطلاعات

می‌توانید به‌صورت دستی گانت چارت بکشید یا از نرم‌افزارهای تخصصی مانند Gantto، Matchware و Microsoft Project استفاده کنید. برخی از این نرم‌افزارها قابلیت این را دارند که شما و همکارانتان بطور همزمان و از هر نقطه‌ای به آنها دسترسی پیدا کنید. 

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

* قدم چهارم: پیشرفت چارت 

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

گانت چارت را برای انعکاس تغییرات به محض وقوع به روز‌رسانی کنید. این به شما کمک می‌کند تا برنامه‌ها، گروه و اسپانسر خود را به‌روز نگاه دارید.                                                                   

نکات کلیدی

 

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

 

 

خانه‌های اکسل مثل آدمها و سکه‌ اند، یعنی دو رو دارند. یک ظاهر و و یک باطن.  مانند خانه‌ای که تاریخ 7/27/2006 را نشان می‌دهد اما باطن آن عدد 38925 است.

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

 

          

در مثال زیر این امر به وضوح مشخص است که در خانه عدد 123.1 نشان داده می‌شود اما در نوار فرمول مقدار واقعی این خانه که 123.1233 است را می‌بینیم.

 

 

 

منظور از روند کردن یک عدد، یعنی تغییر دائمی در رقمهای آن عدد و می‌توان آنرا به انواع زیر دسته بندی کرد:

• روند کردن قسمت اعشار یک عدد
• روند کردن قسمت صحیح یک عدد

 

تابع  Roundدر اکسل 

برای گرد کردن تا تعداد رقم دلخواه اعشار بکار می‌رود، بدین صورت که:

ROUNDUP(Number,Num_digits)

Number: عددی که می‌خواهیم آنرا روند کنیم.

Num_digits: دقت اعشار

 

اگر پارامتر دوم عدد مثبت باشد، رقم داده شده را با آن دقت اعشار داده شده گرد می‌کند (عدد 5 بستگی به رقم بعدش دارد، اگر عدد بعد از 5 از 5 بیشتر باشد 5 به 6 گرد می‌شود)

• اگر صفر باشد فقط قسمت صحیح عدد را می‌دهد
• اگر عدد منفی باشد از سمت چپ ممیز شروع به گرد کردن می‌کند.

 

=ROUND(2.15, 1)	عدد 2.15 را تا یک رقم اعشار گرد می‌کند که می‌شود 2.1
=ROUND(2.149, 1)	Rounds 2.149 to one decimal place (2.1)
=ROUND(-1.475, 2)	Rounds -1.475 to two decimal places (-1.48)
=ROUND(21.5, -1)	Rounds 21.5 to one decimal place to the left of the decimal point (20)

 

 

ROUNDDOWN	گرد کردن اعداد به سمت صفر	=ROUNDDOWN(2.578;2)   ⇒   2.57
ROUNDUP	گرد کردن اعداد دور از صفر	=ROUNDUP(2.578;2)  ⇒   2.58
CEILING	عدد 2.4 را به نزدیکترین مضرب 2 که از خودش بیشتر است گرد می‌کند.	=CEILING(2.4;2)   ⇒   4
FLOOR	عدد 24 را به نزدیکترین مضرب صحیح 7 که از 24 کمتر است گرد می‌کند	=FLOOR(24;7)  ⇒   21
EVEN	عددی را به نزدیکترین عدد زوج بعداز خودش گرد می کند.	=EVEN(2.5)   ⇒   4
ODD	عددی را به نزدیکترین عدد فرد بعد از خودش گرد می‌کند.	=ODD(1.5)  ⇒   3
TRUNC	قسمت اعشاری را حذف می‌کند.	=TRUNC(8.9)   ⇒   8
MROUND	عددی را به مضربی دلخواه از عدد دیگر، گرد می‌کند.	=MROUND(10;3)  ⇒   9
INT	جزء صحیح یک عدد را می‌دهد.	=INT(5.4)  ⇒   5

 

 

تابع ROUNDUP 

فرض کنید که قرار است امتیاز افرادی را پس از یک سری محاسبات بدست آورید، اگر شما از تابع ROUND استفاده کنید، چون اعداد 12.44 را تا یک رقم اعشار می‌کند 12.4  ، ممکن است فردی اعتراض کند که امتیاز او نادیده گرفته شده است! آنهم در حد یکصدم! بنابراین بهتر است از ROUNDUP استفاده کنیم که در هر حال امتیازی بیشتر را محاسبه می‌کند.

 

تابع CEILING 

این تابع کار مثل راننده تاکسی‌ها عمل می‌کند، یعنی اگر کرایه شما 118 تومان شده باشد، می‌گویند 125 تومان یا مثلا اگر کرایه شما شده باشد 233 تومان می‌گویند 250 تومان. البته این به این دلیل نیست که می‌خواهند پول بیشتری بگیرند! بلکه به خاطر این است که در سیستم پولی ما 25 تومانی داریم اما 33 تومان نه. در واقع آنها عدد را به اولیل مضرب 25 بالاتر از کرایه گرد می‌کنند و ما در اکسل می‌نویسیم:

=CEILING(کرایه , 25)    

توجه: عکس این عمل یعنی راننده تاکسی منصف تر (عدد کمتر) را تابع Floor انجام می دهد.

 

تابع TRUNCدر اکسل

این یکی از توابع بسیار مهمی اکسل است که اکثر کاربران آنرا نمی شناسند. این تابع اعشار را حذف می کند و کار گرد کردن (رند کردن) را انجام نمی دهد. البته در اکثر مواقع کاربران از تابع INT برای حذف تمامی اعشار یک عدد مثبت استفاده می کنند. این تابع این قابلیت را دارد که مشخص نماییم که چند رقم اعشار باقی بماند.

 

گرد کردن قسمت صحیح یک عدد نه قسمت اعشاری!

هر گاه بخواهید که از قسمت اعداد صحیح یک عدد، گرد کردن را انجام دهید! می توانید عدد منفی استفاده نمایید.

فرمول	خروجی
=ROUND(13570517,-3)	13571000
=TRUNC(13570517,-3)	13570000

به اعداد صورت‌های مالی دست نزنید

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

 یک راه بسیار ساده برای اینکار است. اصلا به اصل اعداد دست نزنید.

آنها را به همان صورت اصلیشان رها کنید.

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

 

 

ما می توانیم لیست موجود را بر اساس هر یک از ستونهای آن به صورت صعودی یا نزولی مرتب کنیم. برای این کار کافی است به یکی از دو روش زیر عمل کنیم:
روش اول:
1- یک سلول از ستونی را که میخواهیم لیست بر اساس آن مرتب شود انتخاب می کنیم.
2- برای مرتب سازی صعودی از آیکون Sort Ascending  و برای مرتب سازی نزولی از آیکون Sort Descending در نوار ابزار Standard استفاده می کنیم.
نکته
باید توجه داشته باشیم که در این حالت نباید یک ستون انتخاب شود بلکه فقط یک سلول از ستونی که میخواهیم بر اساس آن لیست مرتب شود را انتخاب می کنیم.
روش دوم:
1- کل جدول یا یک سلول از جدول را انتخاب می کنیم.
2- منوی Data را انتخاب می کنیم.
3- گزینه Sort را انتخاب می کنیم.
4- پنجره ای باز می شود که باید قسمتهای زیر را با توجه به نیاز در آن پر کنیم:
الف- Sort By: در این کادر ستونی را که میخواهیم لیست بر اساس آن مرتب شود، انتخاب می کنیم.
ب- Then By: در این کادر ستونی را تعیین می کنیم که اگر اطلاعات ستونی که در کادر اول مشخص شده برای مرتب سازی درexcel  مانند هم بود لیست بر اساس این ستون مرتب شود.
ج- Ascending: با انتخاب این گزینه لیست به صورت صعودی مرتب می شود.
د- Descending: با انتخاب این گزینه لیست به صورت نزولی مرتب می شود.
ه- My List Has: در این قسمت دو گزینه Header Row، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort کل لیست به غیر از سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort By , Then By عناوین قرار می گیرند. ولی اگر No Header Row را انتخاب کنیم هنگام باز شدن پنجره Sort کل جدول حتی سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort by , Then By بجای عناوین, نام ستونها قرار می گیرند. در حقیقت در این حالت عناوین جزء اطلاعات در نظر گرفته شده و در مرتب سازی شرکت داده می شوند.ما می توانیم لیست موجود را بر اساس هر یک از ستونهای آن به صورت صعودی یا نزولی مرتب کنیم. برای این کار کافی است به یکی از دو روش زیر عمل کنیم:
روش اول:
1- یک سلول از ستونی را که میخواهیم لیست بر اساس آن مرتب شود انتخاب می کنیم.
2- برای مرتب سازی صعودی از آیکون Sort Ascending  و برای مرتب سازی نزولی از آیکون Sort Descending   در نوار ابزار Standard استفاده می کنیم.
نکته
باید توجه داشته باشیم که در این حالت نباید یک ستون انتخاب شود بلکه فقط یک سلول از ستونی که میخواهیم بر اساس آن لیست مرتب شود را انتخاب می کنیم.
روش دوم:
1- کل جدول یا یک سلول از جدول را انتخاب می کنیم.
2- منوی Data را انتخاب می کنیم.
3- گزینه Sort را انتخاب می کنیم.
4- پنجره ای باز می شود که باید قسمتهای زیر را با توجه به نیاز در آن پر کنیم:
الف- Sort By: در این کادر ستونی را که میخواهیم لیست بر اساس آن مرتب شود، انتخاب می کنیم.
ب- Then By: در این کادر ستونی را تعیین می کنیم که اگر اطلاعات ستونی که در کادر اول مشخص شده برای مرتب سازی مانند هم بود لیست بر اساس این ستون مرتب شود.
ج- Ascending: با انتخاب این گزینه لیست به صورت صعودی مرتب می شود.
د- Descending: با انتخاب این گزینه لیست به صورت نزولی مرتب می شود.
ه- My List Has: در این قسمت دو گزینه Header Row، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort کل لیست به غیر از سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort By , Then By عناوین قرار می گیرند. ولی اگر No Header Row را انتخاب کنیم هنگام باز شدن پنجره Sort کل جدول حتی سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort by , Then By بجای عناوین, نام ستونها قرار می گیرند. در حقیقت در این حالت عناوین جزء اطلاعات در نظر گرفته شده و در مرتب سازی شرکت داده می شوند.                                                                                  داده ها و عملگرهادر اکسل

برای اینکه داده ای را در سلولی وارد کنیم ابتدا باید روی آن سلول کلیک کرده و سپس داده مورد نظر را تایپ کنیم، یادتان باشد که هنوز این داده وارد شده پذیرفته نشده است برای پذیرفته شدن این داده پس از تایپ آن یکی از اعمال زیر را انجام می دهیم:
1- Enter: داده پذیرفته شده و سلول جاری به طور پیش فرض سلول زیرین می شود.

2- Tab: داده پذیرفته شده و سلول جاری سلول بعدی می شود.

نکته:

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

Arrow key: داده پذیرفته شده و سلول با توجه به جهت دکمه فشرده شده یکی از سلولهای اطراف می شود.

داده ها در Excel

داده ها در محیطExcel می توانند اعداد، حروف، تاریخ، زمان و یادداشت باشند که در زیر به شرح تک تک آنها می پردازیم:

(توجه داشته باشید که با فرمت اطلاعات در آینده بیشتر آشنا می شوید و این قسمت فقط برای آشنایی با انواع اطلاعات می باشد.)

انواع داده ها:

1- اطلاعات عددی در EXCEL

کارکردن با اعداد:

Excel تمام اعداد را یکسان فرض می کند. بنابراین دقت زیادی در نمایش اعداد به صورت مبلغ، تاریخ و کمیت یا هر شکل دیگری از اعداد ندارد. در Excel اعداد را به دو روش می توان وارد نمود:

1- با استفاده از کلیدهای عددی موجود در بالای حروف

2- با استفاده از کلیدهای موجود در سمت راست صفحه کلید (در صورتی که اعداد این قسمت کار می کنند که کلید Num Lock روشن باشد.)

 

علاوه بر ارقام 0 تا 9 نمادهای خاص + و – و، و. و $ و % و E و e را وارد نمود. که در آینده با مفهوم هر یک از این علائم بیشتر آشنا شویم.

عکس العمل Excel نسبت به عدد تایپ شده:

1- اگر طول عدد از سلول کوچکتر باشد تغییری در سلول داده نمی شود.

2- اگر طول عدد به اندازه چند کاراکتر بزرگتر از سلول باشد اندازه سلول بزرگ شده تا عدد در آن بگنجد.

3- اگر طول اعداد برای خانه مورد نظر بزرگ باشد ظاهراً عدد سلولهای بعدی را اشغال می کند ولی بعد از Enter کردن یکی از اتفاقات زیر روی می دهد:

الف – اعداد در نماد علمی نمایش داده می شوند.

ب- عدد گرد می شود.

ج- سلول با علامت # پر می شود. این حالت زمانی اتفاق می افتد که فرمت سلول General نباشد.

تبدیل داده عددی به متنی:

اگر اعدادی که رقم اول آنها صفر است مثل (01234) تایپ کنیم Excel، صفر را حذف می کند زیرا صفر قبل از عدد معنی ندارد و یا اگر عدد مثبت را به همراه آن تایپ کنیم (مثل 23 +)، Excel، + آن را حذف می کند. همچنین اگر 2/6 را تایپ کنیم سلول 6 مارس را نمایش نمی دهد یعنی آن را تبدیل به فرمت تاریخ می کند برای رفع این مشکلات باید عدد را تبدیل به متن کنیم برای این کار می توانیم یکی از علائم زیر را قبل از عدد بنویسیم:

‘ (آپستروف): این علامت باعث می شود عدد به همان صورت و با تراز چپ نوشته شود.

”: این علامت باعث می شود عدد به همان صورت و با تراز راست نوشته شود.

^: این علامت باعث می شود عدد به همان صورت و با تراز وسط نوشته شود.

=: این علامت باعث می شود سلول با عدد تایپ شده پر شود.

1- داده های متنی: می توانیم هر نوع داده متنی را در سلولهای Excel وارد کنیم.

2- داده های نوع تاریخ: می توانیم بدون تنظیم خاصی داده های نوع تاریخ در سلولهای Excel وارد کنیم، کافی است در سلول تاریخی با فرمت yy/mm/dd یا yy-mm-dd وارد کنیم، در آینده با قالب بندی های تاریخ آشنا می شویم. با انجام این کار (-) تبدیل به (/) شده و سال به صورت چهار رقمی (19yy) نمایش داده می شود.

3- داده های نوع زمانی: می توانیم بدون تنظیم خاصی داده های نوع زمان در سلولهای Excel وارد کنیم، کافی است در سلول تاریخی با فرمت hh:mm:ss وارد کنیم، در آینده با قالب بندی های زمان آشنا می شویم.

2- اطلاعات متنی:

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

توانایی Excel برای در نظر گرفتن اعداد مثل متن:

اگر عددی مثل 2/6 را وارد کنیم، پس از Enter کردن، Excel، 6 مارس را نمایش می دهد. یعنی این اعداد را به تاریخ در نظر می گیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (‘) آپستروف قرار میدهیم. یعنی بنویسیم (‘6/2)

3- اطلاعات از نوع تاریخ:

اگر در سلول Excel یک تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد کنیم به طور اتوماتیک قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به (/) شده و سال در چهار رقم نمایش داده می شود.

4- اطلاعات از نوع زمان:

می توانیم در سلول Excel یک زمان را با فرمت H:M:S وارد کنیم که قالب سلول به طور اتوماتیک زمان می شود.

5- اطلاعات از نوع یادداشت:

این نوع اطلاعات توضیحات یا یادداشتهایی هستند که بر روی سلول ظاهر شده و در مورد آن توضیح می دهند. در مباحث بعدی با نحوه ایجاد توضیحات آشنا می شویم.

توجه:

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

فرمول نویسی در Excel

عملگرها در Excel:
1- عملگرهای محاسباتی: از عملگرهای ریاضی + (برای جمع)، – (برای تفریق)، * (برای ضرب) و / (برای تقسیم)، ^(برای توان) و % (برای درصد) در Excel استفاده کنیم.

ایجاد یک فرمول ریاضی

برای ایجاد یک فرمول ریاضی به یکی از روشهای زیر عمل می کنیم.

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

روش دوم: در سلولی که می خواهیم نتیجه نوشته شود علامت = را تایپ کرده، سپس بر روی سلولی که حاوی اولین عملوند است کلیک کرده، سپس عملگرد را تایپ کرده و بعد بر روی سلول حاوی در بین عملوند کلیک می کنیم و به همین ترتیب ادامه می دهیم.

* تقدم عملگرهای ریاضی:

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

()

^

%

* و /

+ و –

می بینیم که * و / در یک خط نوشته شده اند زیرا این دو دارای تقدم یکسان هستند و اگر در فرمولی هر دو عملگر بودند از سمت چپ اولین عملگر انجام می شود.

2- عملگر متنی: برای عبارتهای متنی می توانیم از عملگر & (الحاق) استفاده کنید. با قرار دادن این عملگر بین دو متن آن دو را به هم پیوند می دهید.

3- عملگر آدرس: برای بیان محدوه ای از آدرس می توانیم از عملگر: استفاده کنیم. کافی است آدرس ابتدای محدوده را نوشته، سپس عملگر آدرس را تایپ کنیم و بعد آدرس انتهای محدوده.