جمع کردن اعداد غیر تکراری در اکسل

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

برای این منظور از ترکیب سه تابع sum، IF،Frequncey  در اکسل  استفاده می کنیم.

فرض کنید در ناحیه A2:A12 تعدادی عدد وارد کرده ایم. فرمول را به شکل زیر می نویسیم:

=SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0))

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

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

شکل کلی تابع FREQUENCY به این صورت است.

FREQUENCY(data_array,bins_array)

همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است. توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید.

در این مثال ما تعداد 11 عدد را در ناحیه A2:A12 وارد کرده ایم. اگر بخواهیم با استفاده از تابع FREQUENCY تعداد تکرار هر عدد را حساب کنیم باید ابتدا تعداد 11 سلول را انتخاب نموده و سپس فرمول  (FREQUENCY(A2:A12,A2:A120 را در اولین سلول نوشته و کلیدهای ترکیبی ctrl+shift+enter را فشار دهیم تا فرمول به صورت آرایه ای ثبت شود.

تذکر: در فرمول های آرایه ای علامت های {    } در ابتدا و انتهای فرمول به صورت اتوماتیک نوشته می شوند.

در فرمول =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) اگر تعداد تکرار در تابع FREQUENCY بیشتر از عدد صفر باشد آن عدد یک باردر محاسبه منظور می شود و اگر صفر بود یعنی تکرار آن عدد بیش از یک بار بوده و در جمع در نظر گرفته نمی شود.

در ستون A اعداد تکراری مشخص شده اند

فرمول ستون B به صورت آرایه ای ثبت شده است

در ستون C اگر مقدار ستون B از 0 بیشتر شود عدد واقع در ستون A قرار می گیرد و در غیر این صورت نتیجه تابع IF عدد 0 می باشد

در ستون D جمع نتیجه تابع IF محاسبه شده است.

یکی از توابع سودمند تابع “RANDBETWEEN” در اکسل  است. این تابع به ما امکان تولید اعداد تصادفی را می‌دهد. مقدار بازگشتی این تابع یک عدد دربازه انتخابی  است. برای اینکه ما بتوانبم عدد مورد نظر را در بازه مورد نظر تولید کنیم کافیست:

1 بر روی خانه مورد نظر کلیک کرده و عبارت “RANDBETWEEN=” را درج می نماییم.

2 پارامتر اول آن را با مقدار ابتدای بازه و پارامتر دوم آن را با مقدار انتهایی بازه مقدار دهی کنیم.به عنوان نمونه (RANDBETWEEN(1,100) که عددی تصادفی بین 1 تا 100 برای ما تولید می‌کند.

3 عدد به دست آمده را مشاهده می کنید.                                                                                     استفاده ازتابع  شرطی (if) در اکسل                                                                    

یکی از توابع منطقی سودمند دراکسل تابع “if” است. این تابع شرط مورد نظر را بررسی و با توجه به نتیجه شرط مقادیر مورد نظر را درج می‌کند. استفاده از تابع شرطی به ما امکان تصمیم گیری پویا بر اساس مقادیر ورودی را می‌دهد. برای استفاده از “if” موارد زیررا انجام می‌دهیم:

1 تایپ مساوی “=” در خانه مورد نظر و نوشتن عنوان “)if”.

2 با استفاده از دکمه “insert function” و باز کردن پنجره “function arguments”.

3 درج شرط در قسمت “Logical_test” (به عنوان مثال B3>30  که بررسی مقدار خانه B3 است).

4 درج مقداری که در صورت درستی شرط می‌خواهیم درج شود در قسمت “value_if_true”(مثلاً مقدار:گرم).

5 درج مقداری که در صورت نادرستی شرط می‌خوهیم درج شود در قسمت “value_if_false” (مثلاً: خنک).

6 همان طور که مشاهده می کنید دما پایین تر از 30 درجه هست به همین دلیل در قسمت وضعیت  شرط “خنک” نشان داده شده.

کاربرد تابع CONCATENATE

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

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

=A1&” “&B1

این فرمول به راحتی برای ما مقادیر موجود توی سلول A1 و B1 را با در نظر گرفتن یک فاصله بین اونها به ما نمایش میده. ولی باید بدونید که برای این کار یک تابع مخصوص وجود دارد به نام CONCATENATE

کاربرد تابع  CONCATENATE در اکسل

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

جدا کردن نوشته های یک ستون و تقسیم آن به دو ستون در excel

فرض کنید در سلولی  ستون A1:A10 تعداد 10 تا نام نام خانوادگی وارد کردید که نام با فاصله از نام خانوادگی جدا شده اند ولی همراه سلول قرار دارند و هدف ما این است که  کاری کنیم که نامها در ستون B1:B10 قرار بگیرn و نام خانوادگی در ستون C1:C10 نوشته بشه برای این کار اکسل یک قابلیتی در نظر گرفته به نام Text To Columns که متن  را از ستونها جدا می کنه.

برای استفاده از این قابلیت باید به تب Data برید و گزینه Text To Columns رو انتخاب کنید  گزینه Next رو بزنید.

در قسمت بعدی بالا سمت چپ از شما میخواهد که جدا کننده متن راانتخاب کنید که باید Space را انتخاب کنیدو Next رو بزنید.در پنجره بعدی از شما میخواهد که فرمت جدید و محل و دیتای جدا شده را مشخص کنید کهدر این مثال ما فرمت General و سلول B1 را انتخاب می کنیم که اکسل به صورت خودکار نامها را در  ستون B و نام خانوادگی ها را در ستون C قرار میده.

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

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

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

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

 1- ابتدا با استفاده از Ctrl+A تمام سلول های کاربرگ را انتخاب کنید.

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

3- در پنجره Format Cells  بر روی سربرگ Protection کلیک کنید و تیک گزینه Locked را غیر فعال کنید و بر روی OK کلیک نمایید.

4- سلولهایی که حاوی فرمول هستند را انتخاب کنید و دوباره مرحله 3 را انجام دهید. ولی در این مرحله تیک گزینه Locked  را فعال کنید.

5- بر روی تب Review کلیک کنید. و بر روی گزینه Protect Sheet کلیک کنید.

6- در پنجره Protect sheet به جز گزینه ُSelect Lock Cells تیک تمام گزینه ها را فعال کنید و رمز عبور خود را وارد نمایید.

7- در پنجره بعدی هم رمز عبور خود را دوباره وارد کنید و بر روی Ok کلیک کنید.


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

 (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 می باشد در نهایت پس از اتمام بهینه سازی مقادیر ضرایب مدل بهینه سازی می شود.