چگونگی کارکردن با توابع ریاضی و فرمول های اکسل

 (excel) برای انجام کارهای محاسباتی ریاضی نیازمند استفاده از فرمول نویسی در سلول ها هستند. در این آموزش اکسل  قصد داریم تا حدودی نحوه کار کردن با امکانات محاسباتی اکسل و کار کردن با فرمول های آن آموزش داده شود.

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

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

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

نحوه درج فرمول در سلول ها

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

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

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

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

مقادیر ثابت

مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت ”  قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

آدرس سلول ها

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

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

عملگرها

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

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از + و – و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود.

این عملگرها عبارتند از =،، => و. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند.

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

به طور مثال نتیجه حاصل از عملیات 5=4 یک نتیجه «نادرست» است چرا که عدد 5 مساوی با عدد 4 نیست.

عملگرهای رشته ای: از این عملگر برای چسباندن دو رشته به هم استفاده می شود. استفاده از عملگر & در سلول ها، برای اتصال یا الحاق داده های متنی است.

به طور مثال اگر مقدار سلول B3  =”سلام” باشد و سلول C3 =”جهان”، می توانیم در سلول D3 عبارت B3&C3 را به عنوان فرمول یعنی پس از تساوی وارد کنیم تا در این سلول عبارت “سلام جهان” درج شود.

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

به طور مثال نتیجه عبارت A1:A10 سلول های A1 تا A10 است اما برای اشاره به سلول های خاص از عبارت A1;A5;A7 استفاده می شود.

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

()، ٪، ^، * /، + -، &

مثال: فرض کنید می خواهیم عبارت 4*2+10 را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم.

در حالت اول حاصل عبارت 4*2+10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است.

اما اگر این عبارت را به صورت (10+2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

• توابع تاریخ و ساعت

• توابع ریاضی و مثلثاتی

• توابع آماری

• توابع جستجو و مرجع

• توابع پایگاه داده

• توابع متنی

• توابع منطقی

• توابع اطلاعاتی

• توابع مهندسی

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی 3; ورودی 2; ورودی 1) نام تابع

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

در تصویر زیر، نام تابع SUM است و ورودی های تابع نیز سلول های A1 و A2 هستند.

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

روش درج فرمول با استفاده از Function Wizard در اکسل

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

برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید.

در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید.

این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.

با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند.

اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.

حال فرض کنید می خواهیم در کادر Insert Function تابع جمع را درج کنیم. همانطور که در مثال درج فرمول از طریق وارد کردن علامت = نیز گفتیم، این تابع با نام SUM شناخته می شود.

پس از انتخاب تابع SUM بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  12 و 10 و 8 را وارد می کنیم.

اکنون با کلیک بر روی OK نتیجه این تابع که عدد 30 است در سلول انتخاب شده نمایش داده می شود.

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

قاعده کلی تابع AVERAGE اکسل

AVERAGE (مقدار 3[اختیاری] , مقدار 2[اختیاری] , مقدار1 , …)

 تابع AVERAGE اکسل میانگین پارامترهایی که به آن داده شده را  محاسبه می کند.

تابع AVERAGE اکسل در واقع مترادف فرمول (SUM(X) / COUNT(X هست.

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

اگر بخوایم در سلول B10 معدل دانش آموز رو نمایش بدیم می تونیم از فرمول (AVERAGE(B3:B9 استفاده کنیم. که با قرار دادن این فرمول در سلول B10 مقدار این سلول برابر 16٫43 خواهد بود.

چند نکته در خصوص تایع AVERAGE:

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

نکته: در صورتی که بخوایم سلول های متنی که متن اونها قابل تبدیل به عدد هست یا سلول هایی که دارای مقادیر منطقی مثل TRUE هستند هم در میانگین  می توانیم از تابع AVERAGEA استفاده کنیم.

نکته: اگر بخواهیم برای گرفتن میانگین اعداد شرط خاصی تعیین کنیم می توانیم از تابع AVERAGEIF استفاده کنیم.

نکته: در صورتی که بخواهیم بیش از یک شرط برای میانگین گرفتن تعیین کنیم هم می توانیم از تابع AVERAGEIFS استفاده کنیم.                                                                                                   

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

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

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

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

لینک کردن در اکسل
1- ابتدا عبارت موردنظر به‌عنوان گزینه‌ فهرست را در یکی از سلول‌های دلخواه وارد کنید. برای مثال می‌توانید بنویسید: عملکرد سه ماهه اول سال 1393

2- سلول بالا را برگزیده و روی آن کلیک راست کنید سپس گزینه‌ hyperlink را انتخاب کنید.

3- اکنون می‌توانید به روش‌های مختلفی عملیات لینک‌دهی را انجام دهید.

برخی از این روش‌ها عبارت است از:
لینک به سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document بروید و در کادر Type the Cell reference نام سلول موردنظر را وارد و از فهرست صفحات در کادر زیرین آن، همان صفحه‌ فعلی را انتخاب کنید.

لینک به صفحه‌ دیگر و سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document مراجعه و در کادر Type the Cell reference نام سلول موردنظر را وارد کرده و از فهرست صفحات در کادر زیرین آن، هریک از صفحات دیگر را که قصد دارید به آنها لینک شوید، انتخاب کنید.

لینک به فایل دیگر: در پنجره‌ افزودن لینک، گزینه‌ Existing File and Web page را انتخاب کرده و با استفاده از ابزار مسیردهی، مسیر فایل اکسل موردنظر را برگزیده و در پایان روی Ok کلیک کنید.

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

نمودار ستونی Column Chart

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

نمودار ستونی خود به دو حالت عمودی (Column Chart) و افقی (Bar Chart) تقسیم می‌شود که هر   یک از آنها شامل حالات تجمعی (Stacked Chart) و خوشه‌ای (Clustered Chart) می‌باشند.             

نمودار خطی Line Chart

در صورتی که نقاط ماکسیمم هر میله در نمودار میله ای را با یک خط به یکدیگر متصل کنیم، نمودار میله ای حاصل می‌شود که زمینه های کاربردی همچون نمودار ستونی دارد. در زمانهایی که بازه زمان مورد بررسی مربوط به داده ها بسیار زیاد می‌باشد (مثلا میزان نرخ تورم از سال 1350 تا 1390) این نمودار به عنوان بهترین گزینه می‌باشد.

در محیط اکسل حالتی از نمودار خطی وجود دارد که در آن، مساحت زیر خط به صورت رنگی نمایش داده می‌شود که به آن نمودار سطحی (Area Chart) می‌گویند.

نمودار دایره‌ایPie Chart در اکسل

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

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

نمودار پراکندگی Scatter Plot

این نمودار جهت رسم نقاط بر روی محورهای مختصات و رسم معادلات ریاضیاتی می‌باشد. در واقع در این نمودار با وارد کردن زوج مرتب های x و y مقادیر هر زوج مرتب به صورت یک نقطه بر روی محورهای مختصات رسم می گردد.

نمودار حبابی Bubble Chart

این نمودار همانند نمودار پراکندگی‌می باشد، با این تفاوت که توانایی نمایش سه ویژگی به صورت همزمان را دارا می‌باشد به گونه‌ای که دو ویژگی X و Y بر روی محورها مشخص می‌گردد و ویژگی سوم با اندازه حباب مشخص می‌شود.

نمودار رادار Radar Chart

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

نمودار شمعی در  excel

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

نمودار جعبه ای Box and whisker

این نمودار به منظور بررسی و مقایسه وضعیت دو یا چند جامعه (گروه) متفاوت از داده‌ها از نظر پارامترهای مکانی (میانگین، میانه) و پراکندگی (واریانس) مورد استفاده قرا می گیرد. در نسخه های قبلی اکسل این نمودار به صورت آماده وجود ندارد ولی می توان با افزودن Error Bar به نمودارهای ستونی آن را ایجاد نمود ولی در اکسل 16 (اکسل 2016) این نمودار به صورت آماده در تب Insert و قسمت نمودارها اضافه شده است.

نمودار هیستوگرام Histogram Chart

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

نمودار نقشه درختی   در excel

این نمودار که در نسخه 2016 اکسل، به آن اضافه شده است و جزء نمودارهای نمایش سلسله مراتب میان داده‌ها می‌باشد، جهت نمایش درصد مشارکت هر گروه و زیر گروهها در یک رویداد مناسب می‌باشد، برای مثال اگر بخواهیم مشخص کنیم که کدام گروه از کالاها بیشترین فروش را داشته و در هر گروه کدام کالا سهم بیشتری از فروش را دارد، این نمودار بسیار مناسب می‌باشد.

نمودار خورشیدی SunBurst Chart

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

نمودار آبشاری Waterfall Chart

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

امید واریم از آموزش اکسل امروز نیز لذت برده باشید

قطعا همه ما در هنگام تایپ غلط‌های املایی داریم و یا شده در  Excel اشتباهاتی انجام دهیم. دکمه‌های ترکیبی Ctrl+Z و Ctrl+Y کار شما را از این بابت راحت می‌کنند. این کلیدها که کاربرد بازگشت به تنظیم قبل و تنظیم بعد را دارند از طریق دو فلش خمیده که به سمت چپ و راست هستند نیز در بالای صفحه نرم‌افزار در دسترس است.

2– یافتن و جایگزین کردن (Find & Replace) در اکسل

برای یافت یک کلمه خاص در متن‌های طولانی نیازی نیست کل متن را بگردید کافی است کلید میان‌بر Ctrl+F استفاده‌ کنید و کلمه مورد نظر را جستجو نمایید.
اگر بخواهید به طور اتوماتیک یک کلمه که به کرات در نوشته تکرار شده است را با کلمه دیگری جایگزین کنید، نیازی به پیدا کردن و نوشتن کلمه جدید نیست، کافی است از میان‌بر Ctrl+H استفاده نموده و کلمه قدیمی و کلمه‌ای که می‌خواهید در کل متن جایگزینش شود، بنویسید. سپس نرم‌افزار به صورت خودکار این کار را انجام خواهد داد.

3– برش، کپی و جای‌گذاری (Cut, Copy, Paste)

قطعا ساده‌ترین و پر استفاده‌ترین قابلیت در نرم‌افزارها کپی و کنترل بر روی متن‌هاست که به سادگی انتخاب متن و راست کلیک بر روی آن و انتخاب گزینه مورد نظر برای ویرایش است. اما راه حل ساده‌تر آن استفاده از کلید‌های ترکیبی Ctrl+X برای برش، Ctrl+C برای کپی و Ctrl+V برای جایگذاری متن است. شما با این میان‌بر‌ها می‌توانید به سادگی و سرعت بیشتر کار‌های خود را انجام دهید.

4– انتخاب یک قسمت یا انتخاب تمام متن (Select or Select All) در EXCEL

میان‌بر کاربردی دیگری که بیشترین کاربرد را بعد از کپی و چسباندن دارد، میان‌بر انتخاب کل متن است. دیگر نیازی نیست برای انتخاب کل متن ماوس را بگیرید و از بالا تا پایین را انتخاب نمایید. برای این کار از کلید ترکیبی Ctrl+A استفاده کنید. این کار باعث می‌شود تمام متن به یک‌باره انتخاب شود تا شما عملیات مورد نظر را بر روی آن انجام دهید.

5– چاپ و پیش‌نمایش چاپ

چاپ کردن خیلی آسان‌تر از آن چیزی است که بسیاری از مردم فکرش را می‌کنند. به راحتی در محیط Word یا Excel از کلید Ctrl+P برای پرینت کردن استفاده کنید. اما قبل از آن پیشنهاد می‌کنیم از گزینه پیش‌نمایش پرینت استفاده کنید تا مشکلات احتمالی را مشاهده و در مصرف کاغذ نیز صرفه جویی نمایید. برای پیش‌نمایش پرینت از کلید‌های ترکیبی Ctrl+F2 استفاده کنید.

6– برو به… (Go To)

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

7– خانه (Home)

کاربرد این میان‌بر در Word و Excel تا حدودی متفاوت است. در Word با استفاده از کلید ترکیبی Ctrl+Home مکان‌نما به ابتدای متن تایپ شده انتقال می‌یابد.
اما در اکسل با استفاده از همان کلید ترکیبی (Ctrl+Home) مکان‌نما به خانه A1 یا همان اولین خانه جداول اکسل منتقل می‌شود.

8– کنترل (Crtl)

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

9– Shift

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

10– پایان (End)

این کلید بر عکس کلید خانه (Home) عمل می‌کند. به این صورت که مکان‌نما را به انتهای متن Word یا آخرین خانه اکسل منتقل می‌کند. برای استفاده از این قابلیت از کلید ترکیبی Ctrl+End استفاده نمایید.

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

برای مثال فرض کنید از نتایج تجربی، نقاط زیر در مختصات (x,y)  بدست آمده است.

برای مثال در مبانی نظری پژوهش، تابع منحنی زیر می تواند بر روی داده ها منطبق شود:

برای بهینه سازی ضرایب ابتدا نرم افزار اکسل نسخه 32 بیتی را باز کنید. از منوی File گزینه Option  را انتخاب کنید. از منوی سمت چپ بر روی گزینه Add-Ins کلیک کنید. در پنجره سمت راست از منوی کرکره ای Excel Add-Ins را انتخاب کنید و بر روی دکمه Go کلیک کنید.

در پنجره باز شده افزونه Solver Add-In را تیک بزنید و بر روی دکمه Ok کلیک کنید.

سپس بر روی آیکن نرم افزار Nonlinear Parameter Estimation بر روی دسکتاپ کلیک کنید تا نرم افزار باز شود.

در ستون مربوط به x و y مختصات نقاط را کپی کنید تا مختصات نقاط بر روی نمودار ترسیم شود. در مرحله بعد می بایست تابع منحنی را در سلول زرد رنگ داخل نرم افزار بنویسید. در هنگام نوشتن فرمول منحنی باید علائم استفاده شده منطبق با استانداردهای نرم افزار اکسل باشد یعنی علامت ضرب با * علامت تقسیم با /  علامت توان با ^ علامت جذر با sqrt()  … نوشته شود. کسانی که با اکسل آشنایی مقدماتی دارند به راحتی می توانند هرگونه تابع را در نرم افزار اکسل نگارش کنند.

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

همانطور که مشاهده می شود تابع منحنی بر روی نقاط تجربی قرار گرفته است و مقدار بهینه ضرایب a و b در جدول مربوط محاسبه شده است. می توانید به ضرایب مقادیر اولیه متفاوتی بدهید و دوباره بر روی دکمه بهینه سازی کلیک کنید در هر حالت که کمترین مقدار برای مجموع مربعات خطا(Sum of Square) در سلول سبز رنگ بدست امده نشان می دهد که آن ضرایب در بهینه ترین حالت ممکن قرار دارد.

اگر منحنی شما بسیار پیچیده باشد می توانید از نرم افزار GA Solver که در پکیج آموزشی تحلیل آماری قرار دارد نیز استفاده کنید و ضرایب منحنی را به کمک الگوریتم ژنتیک بهینه سازی کنید.  برای این منظور ابتدا نرم افزار GA Solver  را باز کنید سپس نرم افزار Nonlinear Parameter Estimation را باز کنید به این ترتیب نرم افزار الگوریتم ژنتیک در‌منوی Add-In نرم افزار اکسل اضافه می شود و به راحتی می توانید از ترکیب هر دو نرم افزار استفاده کنید.

در نرم افزار الگوریتم ژنتیک مقدار سلول سبز رنگ(Sum of Square) به عنوان تابع هدف و مقادیر سلول های a و b به عنوان متغیرهای تصمیم انتخاب می شود و نوع تابع هدف نیز از نوع min می باشد در نهایت پس از اتمام بهینه سازی مقادیر ضرایب مدل بهینه سازی می شود.

نر م افزار اکسل یکی از نرم افزارهای کاربردی مجموعه آفیس است. اگر با این نرم افزار کار کرده باشید ممکن است در سطر و ستون های مختلف شیت های خالی بوجود آورده باشید.  اگر در صفحه‌ی شیت اکسل تعداد زیادی سطر خالی یا Blank وجود داشته باشد، می‌توان بر روی آن‌ها جداگانه کلیک راست کرده و با انتخاب گزینه Delete، آن‌ها را حذف کرد. با این وجود راهی سریع و آسان‌تر برای پاک کردن سطر و ستون‌های خالی در نرم‌افزار Excel 2016 وجود دارد.در اداغمه با ما همراه باشید تا با چگونگی حذف سلول های خالی بیشتر آشنا شویم.

1. محدوده‌ای از Spreadsheet  را که می‌خواهید سطرهای خالی آن  پاک شود، انتخاب نمایید. مطمئن شوید که سطر بالای اولین سطر خالی و سطر پایین آخرین سطر خالی را نیز انتخاب کرده باشید.
بر روی گزینه Find & Select در قسمت Editing از تب Home کلیک کرده و گزینه Go To Special  را از منوی آن انتخاب کنید.

2. کمی صبر کنید تا پنجره تنظیمات باز شود. در پنجره Go To Special گزینه Black را انتخاب کنید و بر روی OK کلیک نمایید.

3. به سندتان نگاه کنید. مشاهده می‌شود که در محدوده انتخابی در Sheet تمامی سلول‌های غیر خالی از حالت انتخاب خارج شده‌اند و تنها سلول‌های خالی در حالت Select هستند.

4.در مرحله بعدی در بخش Cell در تب Home بر روی گزینه Delete کلیک کرده و از منوی باز شده گزینه Delete Sheet Rows را انتخاب کنید.

5. تمامی سطرهای خالی حذف می‌شوند و سطرهای دارای مقدار پشت سر هم قرار خواهند گرفت.

6. همچنین شما می‌توانید به همین صورت ستون‌های خالی را نیز حذف نمایید. برای انجام این کار ابتدا محدوده‌ای را که در آن ستون‌های Black وجود دارند، انتخاب کنید. از انتخاب ستون سمت چپ اولین ستون خالی و ستون سمت راست آخرین ستون خالی مطمئن شوید. مجددا بر روی، Find & Select در قسمت Editing از تب Home کلیک کرده و گزینه Go To Special را انتخاب کنید.
مجددا گزینه Blank را در پنجره Go To Special انتخاب و تایید نمایید.

حذف سطر و ستون های خالی در excel

پنجره Go To نمایش داده می شود. بر روی کلید OK کلیک کنید. در پنجره باز شده چک باکس Blanks را انتخاب کرده و بر روی کلید OK کلیک کنید.

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

1- ابتدا ستون یا محدوده مورد نظر خود را انتخاب کنید.

2- در سربرگ Home از  Excel  (به این نوار در Excel 2007 به بعد Ribbon می‌گویند) گزینه Conditiona Formatting را بزنید.

3- در لیست باز شده، گزینه Highlight Cell Ruls را بزنید.

4- در این لیست گزینه Duplicate Value  را بزنید.

5 – در پنجره آخر، از شما پرسیده می‌شود که سلولهای تکراری چه رنگی شوند.

چگونه در Excel از ورود اعداد یا مقادیر تکراری توسط کاربر جلوگیری کنیم؟

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

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

ابتدا ستون مورد نظر را انتخاب نمایید.

1- دقت نماید که آدرس سلول فعال شما چیست، مثلاسلول فعال A1 است.

2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را انتخاب کنید.

3-تنظمات را انجام دهید.

تابع  COUNTIF در اکسل مشخص می کند که در ستون A، چند بار مقادر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد، که تکراری نشود.

در واقع درنرم افزار اکسل  هر سلول را با نام ستون و شماره سطر می شناسیم و در قسمت name box اسم هر سلول انتخاب شده را می نویسد.

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

مثلا جمله زیر را تایپ می کنیم: آموزش ورود اطلاعات در نرم افزار اکسل.

برای تأیید آن می توان کلید Enter را زده و یا در نوار ابزار بالا از این قسمت طبق شکل زیر آن را تأیید کرد.

البته اگر از نوشتن آن منصرف شده باشیم می توانیم، کلید X یا Cancel و یا کیبورد، کلید ESC را فشار دهی

ویرایش متن در excel

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

حال به سراغ نوار فرمول می آییم. توسط این نوار می توانیم به راحتی اطلاعات درون سلول ها را مدیریت کنیم. پس با کمک درگ کردن، کلمه نرم افزار را در نوار فرمول انتخاب کرده و با زدن کلید Deleteآن را حذف می کنیم و سپس کلید Enter را می زنیم.

و یا می توان درون سلول 2 بار کلیک کرده و هر قسمتی را که بخواهیم از متن مورد نظر حذف کنیم.

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

جا به جایی بین سلول ها

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

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

یک فاکتور فروش برای فروش سیستم کامپیوتر درست می کنیم. با ساختن این فاکتور فروش اطلاعات قبلی را تا حدی مرور می کنیم.

1. در سلول A1 2 بار کلیک می کنیم و می نویسیم فاکتور فروش.
2. حالا با کلیک و درگ تا سلول F1 آن را می آوریم و با استفاده از گزینه Merge & Center این سلول ها را یکی می کند.

3. حال به سراغ سلول A2 می رویم. اکنون می توانم با موس روی آن کلیک کنم و یا در کیبورد، کلید Enter را فشار دهم.

در حالی که روی سلول A1 قرار گرفته اید با زدن کلید Enter به A2 می آیید. البته می توانید از کلید جهت دار  Arrow key down هم استفاده کنید. (منظور کلید جهت دار پایین روی کیبورد است.)

در سلول A2 ، شماره فاکتور را تایپ کنید. دوباره کلید Enter را زده و می نویسیم: نام مشتری.

و دوباره Enter را زده و کلمه ردیف را تایپ می کنیم.

حال می خواهیم به سراغ سلول B4 برویم. این بار از کلید Tab استفاده می کنیم و یا از Arrow Key (کلید جهت دار سمت چپ) و اکنون درون سلول (لیست قطعات) را تایپ می کنیم.

حالا در سلول C4 می نویسیم: مدل قطعه

به همین ترتیب قیمت هر قطعه، تعداد و جمع را وارد می کنیم.

به سلول B5 آمده و در این سلول می نویسیم: 1

به سلول B5 رفته و می نویسیم: مادربرد.

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

اگر بخواهیم به سطر اول اطلاعاتمان دسترسی پیدا کنیم کافی است از کلید ترکیبی Ctrl+Home استفاده کنیم. و اگر بخواهیم به سلول آخری که اطلاعات در آن وارد شده است یعنی به سطر 24 برویم باید از کلید Ctrl + End استفاده کنیم.

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

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

اگر بخواهیم مستقیم به سلول C4 برویم و تک تک این خانه های خالی را انتخاب نکنیم، می توانیم همراه با زدن این فلش رو به بالای دکمه Arrow key کلید کنترل را نگه داریم. حالا دیگر سلول های خالی انتخاب نمی شوند و مستقیما به سلول C4 می رویم. و با زدن کلید +Ctrl جهتدار رو به پایین می توانیم دوباره به سلول C21 برویم.

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

اگر اطلاعاتی که وارد می کنیم بسیار زیاد باشد و مجبور باشیم آنها را صفحه به صفحه ببینیم می توانیم از کلید Page+Up و page down هم استفاده کنیم. با زدن این دو کلید می توانیم تمام اطلاعاتمان را در اکسل، به صورت صفحه به صفحه ببینیم.

ذخیره  کردن فایل اکسل

حالا می خواهیم با هم فاکتوری که ساخته ایم را ذخیره کنیم. به منوی Office  رفته و روی save کلیک می کنیم. بلافاصله پنجره save as برای ما باز می شود. در این قسمت ما به تمام قسمت های هارددیسک دسترسی داریم و می توانیم محل ذخیره سازی را مشخص کنیم. در قسمت File name می توانیم نام مورد نظر را وارد نماییم.

نام این فایل را فاکتور می گذاریم و با کلیک روی دکمه  Save، این فایل را ذخیره می کنیم و پنجره save as هم بسته می شود.

حال شما نام فایل را می توانید در قسمت Title bar اکسل (نوار عنوان) آن مشاهده کنید

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

در اکسل داده بسیار مهم داریم. داده های عددی و داده های متنی.

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

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

در سلول D5 می خواهیم قیمت یک قطعه را وارد کنیم. مثلا قیمت 200000 تومان (2000000  ریال) را در اینجا وارد می کنیم.

و سپس دکمه Enter را می زنیم.

این دقیقا مانند یک عدد معمولی وارد می شود. در سلول D6، آمده و روی علامت $ در زبانه Home و ریبون Number کلیک می کنیم.                                                               

حالا دوباره روی D6 رفته، عدد 2000000  را وارد می کنیم و در نهایت بدین شکل در خواهد آمد.

الان این عدد را به صورت یک مبلغ یا واحد پول به اکسل معرفی کردیم که واحد آن دلار است و البته 2 تا صفر اضافه شده است که توسط شکل زیر آن را حذف می کنیم.

حال روی سلول کلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.

البته راه های دیگری هم برای دسترسی به این پنجره وجود دارد. برای مثال روی سلول کلیک کرده و به ریبون Number رفته و روی $ کلیک کرده و سپس گزینه More Accounting Formatsرا انتخاب می کنیم و وارد پنجره Format Cells می شویم.

و یا در ریبون Number ، روی فلش کنار آن طبق شکل زیر کلیک کرده و در Format Cells گزینه Currency را انتخاب می کنیم.

سپس در قسمت Symbol ، Persian ریال را انتخاب کرده به شکل زیر و Ok می کنیم.

راستی باید حواسمان به Decimal Places باشد که اعشار را مشخص می کند.من آن را روی صفر می گذاریم. البته می توانیم ان را روی 1 قرار دهیم و در سلول اکسل که وارد می کنیم آن را به تومان بنویسیم.

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

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

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

سپس روی مثلا سلول G6 رفته و گزینه paste را انتخاب می کنیم. به صورت زیر:

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

با دقت نگاه کنید.

خط چین شکل 75  هنوز از دور سلول حذف نشده است. ما می توانیم هر جای دیگری که بخواهیم برویم و آن را paste کنیم.

و یا می توانیم به جای یک سلول، به وسیله درگ کردن چندین سلول را انتخاب کنیم. و سپس دستور Paste را صادر کنیم.

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

کلید ترکیبی ctrl+Z را می زنیم تا کپی هایی که گرفتیم از بین بروند.

نکته: لازم نیست که کلید Paste را انتخاب کنیم.  روی گوشه سمت چپ سلول ایستاده و دکمه Enter را زده و می بینیم که اطلاعات در اینجا کپی شده و دیگر از دور سلول D6 هم خبری نیست.

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

هنگامی که Copy کرده و دستور  Paste (Ctrl+V) را انتخاب می کنیم، یک آیکون به نام Paste Option کنار آن به شکل زیر باز می شود.

حالا روی آیکون paste option کلیک می کنیم.

ولی در حالتی که سلول را Enter می کردیم این آیکون ظاهر نمی شد.

کاربردهای آن:

1. Keep Source Formatting: محتویات و اطلاعات سلول کپی شده را به سلول انتخابی انتقال می دهد.

2. Match Destination Formatting: فقط داده کپی می شود و فرمت آن سلول کپی نمی شود.

3. Formatting Only:  این بار به جای محتویات فقط فرمت آن سلول کپی شده در اینجا منتقل می شود.

  Paste کردن مواجه نشویم، این بار وقتی که در سلولی دستور Pasteنکته: برای اینکه با گزینه ای مثل # هنگام 

را صادر می کنیم، گزینه Keep Source Columns With را انتخاب می کنیم.

عرض این سلول با عرض سلول کپی شده در واقع یکی شد.

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

حالا این بار گزینه Link Cells را انتخاب می کنیم.

به ظاهر که تغییری دیده نشد. حالا روی سلول D6 آمده و روی آن کلیک می کنیم و کلید Enter را زده و محتویات آن را تغییر می دهیم.

به محض زدن Enter محتویات آن سلول ها نیز تغییر می کند.

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

Format Cells را انتخاب کرده و General در قسمت Category را انتخاب می کنیم و آن را به فرمت یک سلول معمولی تبدیل می کند.

برای انتقال سلول ها از سلولی به سلول دیگر هم می توان از دستور Cut استفاده کرد و هنگامی که اطلاعات را در سلول مورد نظر Paste کنیم، از سلول اول به سلول مورد نظر انتقال پیدا می کند.

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

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

خاصیت ویژه سلول ها در پر کردن داده 

یک صفحه جدید ایجاد می کنیم.

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

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

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

در واقع اکسل خودش برای وارد کردن داده به ما کمک می کند.

راه دیگر کپی کردن داده های یک یا چند سلول استفده از Fill handle است.

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

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

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

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

به این قابلیت Autofill گفته می شود.

به محض اینکه موس را رها کنیم یک آیکون کوچک به نام Autofill Options ظاهر می شود.

این آیکون 3 گزینه دارد:

1. Copy Cells: یعنی کل محتویات سلول کپی شود.

2. Fill Formatting Only: تنها فرمت آن در سلول کناری اش کپی می شود.

3. Fill Without Formatting: فقط محتویات سلول کپی می شود و دیگر فرمت آن سلول کپی نمی شود.

ما با کمک Autofill می توانیم چند تا سلول هم کپی کنیم. هیچ فرقی نمی کند.

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

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

Mon را در یک سلول تایپ می کنیم و به سمت پایین درگ کرده و خواهیم دید که یک گزینه به نام   Fill Series، اضافه می شود.

اعداد به ترتیب و پشت سر هم قرار می گیرند.

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

جواب: Autofill در مورد داده های عددی عمل می کند و آنها را به ترتیب قرار می دهد.

اما در مورد داده های دیگر ما باید آن داده ها را به ترتیب به اکسل معرفی کنیم.

یا در واقع یک سری را خودمان در اکسل درست کنیم.

ساختن یک سری یا لیست در اکسل یسیار راحت است.

برای ساختن یک لیست من به سراغ منوی Office آمده و طبق شکل زیر ادامه می دهیم.

پنجره Excel Options باز می شود.

سپس سراغ کلید Edit Custom lists آمده و روی آن کلیک می کنیم تا پنجره Custom Lists برایمان باز شود.

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

سپس دکمه Add را زده و به قسمت Custom Lists اضافه می کنیم. و سپس ok را می زنیم.

و پنجره excel option را هم کلیک می کنیم.

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

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

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

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

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

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

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

برای ساخت چک باکس در اکسل  ابتدا باید تب DEVELOPER را در تب های اصلی اکسل فعال کنیم. برای این کار در اکسل 2013 از تب FILE بر روی Options کلیک کرده و در پنجره  پیشرو از منوی سمت چپ گزینه Customize Ribbon را انتخاب می کنیم. در سمت چپ در منوی آبشاری با زدن تیک گزینه Developer را فعال می کنیم.

به شیت گزارش باز می گردیم، از تب Developer قسمت Controls و از جعبه ابزار Insert قسمت Form Controls گزینه Check Box را انتخاب می کنیم.

در قسمتی که چک لیست هارا ایجاد کردیم، در کنار جدول ها و درست مقابل هر یک از ردیف های آن، یک چک باکس رسم می کنیم. پس از رسم چک باکس بر روی آن کلیک راست کرده و از منو، قسمت Edit Text، عبارت Check را از جلوی چک باکس حذف می کنیم. سپس از همان منو، پنجره Format Controls … را باز کرده و از تب Control (مطابق شکل زیر) در قسمت Cell link آدرس سلول سمت راست چک باکس را برای لینک کردن (F4) می دهیم. این کار را برای تمامی چک باکس ها انجام می دهیم. در صورت صحیح انجام شدن موارد ذکر شده در صورت تیک دار کردن چک باس، سلول مجاور TRUE را نشان داده و در صورت برداشتن تیک FALSE می شود.

تا اینجای کار، مقدمات انجام شده است. حال به قسمت سخت کار یعنی نوشتن فرمول محاسبه تعداد، در سلول های ستون D می رسیم.
با توجه به پیچیدگی فرمول در ادامه تا جای امکان فرمول نوشته شده را شرح خواهیم داد. برای این منظور فرمول نوشته شده در سلول D4 را تا رسیدن به نتیجه فرمول؛ یعنی عدد 2، شرح می دهیم. باید توجه داشت که فرمول نوشته شده آرایه ای یک بعدی و نوشته شده در یک سلول می باشد. این فرمول را می توان تنها در سلول های ستون D برای یک چک لیست کپی کرد و فرمول نوشته شده در چک لیست های دیگر از لحاظ آدرس دهی کمی تفاوت دارند و نمی توان به طور کامل از فرمول های چک لیست های دیگر در آن استفاده کرد.

در فرمول بالا از ترکیب توابع IF، SUMPRODUCT، ISNUMBER، MATCH به صورت توابع آرایه ای استفاده شده است. برای راحتی کار فرمول را 3 تکه کرده ایم.
1) DATABASE!$B$3:$B$12=C4:
فرمول بالا قصد شمردن تعداد گروه سنی در هر ردیف چک لیست را از روی دیتا بیس را دارد.

در این فرمول؛ گروه سنی 24- 20 سال واقع در سلول C4، با گروه های سنی واقع در ستون B در شیت دیتا بیس مقایسه شده و در صورت مطابق بودن، جواب TRUE دریافت می شود. تنها بدلیل آرایه ای بودن فرمول، قابلیت چک کردن یک سلول (C4) را با سلول های بازه دیگر(ستون B دیتابیس) داریم. در صورتی که اگر فرمول، معمولی نوشته می شد تنها اجازه مقایسه دو سلول را داشتیم. در روش آرایه ای این مقایسه در حافظه موقت برنامه، سلول به سلول (مطابق شکل بالا) انجام شده و تعداد تطابق ها بدست می آید (در شکل بالا 2 سلول می باشد) و در محاسبات فرمول اصلی استفاده می شود. (در فرمول مسئله ما تابع SUMPRODUCT از این 2 تطابق پیدا شده استفاده می کند.)

2) ((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

این فرمول در مسئله ما نقش شمردن تعداد مشاغل در هر ردیف چک لیست، از روی دیتابیس را دارد.
تابع کلیدی در فرمول بالا MATCH می باشد، (MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

"یادآوری: تابع   ([MATCH(lookup_value, lookup_array, [match_type از سه قسمت تشکیل شده؛ قسمت اول که سلول مورد نظر ما برای تطابق است، قسمت دوم بازه ای که سلول مورد نظر برای تطابق، در آن جستجو می شود و قسمت سوم که اختیاریست، برای دقت جستجو بکار می رود که با سه عدد 0 و 1 و 1- اجرا می شود؛ اگر صفر بگذاریم دقیقا سلول مورد نظر را پیدا می کند، اگر 1 بگذاریم، درصورتی که سلول ما در بازه جستجو موجود نباشد، بزرگترین عددی که به رقم مورد نظر ما نزدیک تر و از آن کوچکتر است را پیدا می کند و در صورت استفاده از 1-،  اگر سلول ما در بازه جستجو موجود نباشد، کوچکترین عددی که به رقم مورد نظر ما نزدیک تر و از آن بزرگتر است را پیدا می کند. در صورت یافتن تطابق در بازه جستجو، تابع MATCH جایگاه ترتیبی سلول مورد نظر را در بازه جستجو نشان می دهد. (به طور مثال سلول مورد نظر، پنجمین سلول در بازه جستجو می باشد.)

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

اگر بخوایم یک بازه یا آرایه را در بازه ای دیگر جستجو کنیم کار کمی پیچیده شده و باید فرمول را به صورت آرایه ای بنویسیم. همانطور که در شکل زیر می بینید برای یافتن جایگاه اعداد ستون Iدر ستون G هر سه خانه ستون K را انتخاب کرده و فرمول شکل زیر را بصورت آرایه ای می نویسیم و در نتیجه با یک فرمول آرایه ای جایگاه هر سه عدد را در بازه جستجو بدست می آوریم. ایرادی که در بالا ذکر شد در این مثال نیز صادق است و تنها اولین جایگاه 2 را در جواب به ما می دهد یعنی جایگاه اول بازه جستجو این در حالیست که 2 در جایگاه پنجم نیز تکرار شده است. "

ابتدا برای باز شدن گره فرمول بالا به سراغ فرمول (IF($F$10:$F$13,$C$10:$C$13می رویم. این فرمول در قسمت دوم تابع MATCH و به عنوان بازه جستجو استفاده می شود. ولی با توجه به شرطی که گذاشته شده هر شغلی نمی تواند جزء سلول های بازه جستجو باشد و تنها شغل هایی که در چک لیست مشاغل تیک بخورند شرط بالا را رعایت کرده و در بازه جستجو قرار می گیرند.

همانطور که در شکل مشاهده می کنید تمامی سلول های بازه F10 تا F13 شرط را رعایت کرده و TRUE می باشند. پس سلول های نظیر آنها در ستون C جزء بازه جستجو می باشند و درصورتی که تیک هریک از چک باکس های بازه F10 تا F13 را برداریم، سلول نظیر آن از بازه جستجو  حذف می گردد.

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

نتیجه فرمول MATCH همانطور که مشاهده می کنید به صورت بازه بالا در فرمول اصلی استفاده می شود. بطور مثال مطابق شکل بالا، وکیل جایگاه دوم را در بازه جستجو در اختیار دارد.
چک باکس در اینجا نقش فیلتر را بازی کرده و در صورت برداشتن آن شرط فرمول IF در مورد سلول نظیر چک باکس، برقرار نشده و آن سلول از بازه جستجو حذف می شود. و در بازه نتیجه فرمول MATCH چون سلولی که برای تطابق جستجو می شود در بازه جستجو موجود نمی باشد، اثر آن در بازه نتیجه به صورت ارور #N/A نمایش داده خواهد شد و در شمارش فرمول اصلی بحساب نمی آید. در قسمت سوم فرمول MATCH هم از صفر استفاده شده تا فقط جایگاه انطباق های دقیق در بازه نتیجه ظاهر شود.

((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

حالا از فرمول شماره 2 تنها تابع ISNUMBER باقی مانده است. کار این تابع؛ تبدیل نتایج بدست آمده در بازه نتیجه MATCH به صورتTRUE و FALSE می باشد. در صورتی که در بازه نتیجه MATCH، ارور #N/A نباشد ویا به عبارتی عدد باشد، ISNUMBER به ما نتیجه TRUE یا 1 و در صورت ظاهر شدن ارور به ما نتیجه FALSE ویا 0 می دهد.
در کل می توان از فرمول 2 نتیجه گرفت که اگر در چک لیست گزینه ای تیک دار نباشد، فرمول اصلی برای آن صفر منظور کرده و در محاسبات استفاده نمی کند و در صورت تیک دار بودن، 1 منظور شده و در محاسبات استفاده می شود.

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

3) ((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0

این فرمول دقیقا مانند فرمول شماره 2 عمل کرده در مسئله ما نقش شمردن تعداد علاقه مندی در هر ردیف چک لیست از روی دیتابیس را دارد. فقط در آدرس دهی باید آدرس های مربوط به علاقه مندی را منظور کرد در نهایت با ترکیب سه فرمول فوق، سه نتیجه به صورت سه بازه 0 و 1 یا TRUE و FALSE بدست می آید که با قرار دادن آنها در تابعSUMPRODUCT، نتیجه نهایی (برای چک باکس اول 2 می شود) بدست می آید

*(((SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

(((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

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

*(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0=

("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

این فرمول را می توان در تمام سطر های یک چک لیست کپی کرد ولی برای چک لیست های دیگر اندکی باید در آدرس دهی تغییراتی داد. در مثال ما این فرمول برای چک لیست گروه سنی کاربرد دارد و می توان در سلول های D4 تا D8 کپی کرد.
باتوجه به سه زیر فرمولی که داشتیم، به ترتیب فرمول یک آدرس دهی مربوط به عنوان چک لیستی است که فرمول را برای آن می نویسیم و به دو فرمول دیگر آدرس اطلاعات مربوط به دو چک لیست دیگر را می دهیم.

1) چک لیست گروه سنی = کپی فرمول در خانه های D4 تا D8
(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

2) چک لیست شغل = کپی فرمول در خانه های D10 تا D14

(((IF(F10,SUMPRODUCT((DATABASE!$C$3:$C$12=C10)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

3) چک لیست علاقه مندی = کپی فرمول در خانه های D16 تا D18
(((IF(F16,SUMPRODUCT((DATABASE!$D$3:$D$12=C16)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13,""),0)

همانطور که ملاحظه می کنید؛ این فرمول برای تمام چک لیست ها، مشابه می باشد و تنها در آدرس دهی تفاوت دارد. این فرمول را می توان برای هر تعداد چک لیست، با هر تعداد ردیف، بسط داد و تنها باید دقت داشت که به ازای هر چک لیست یک فرمول MATCH باید نوشته شود.