نکاتیدر مورد معانی خطاهای فرمول نویسی اکسل

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

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

!VALUE# : 
این خطا به این معنیاست که شما نوع اشتباهی از داده رو به عنوان ورودی فرمول استفاده کردید. به عنوان مثال ممکنن است د ر تابعی که پارامتری رو از نوع یک سلول میگیره یک محدوده رو وارد کرده باشید و یا در فرمول جایی که باید به عدد ارجاع داده شود متن به جای عدد وجود داشته باد. به عنوان مثال در فرمول A1/A2 شما مقدار موجود در سلول A2 یک متن باشه.  ?NAME# : 
این خطا زمانی رخ میده که شما نام تابعی رو در فرمولتون اشتباه نوشته باشید، مقادیر متنی استفاده شده در فرمول رو داخل علامت ” نگذاشته باشید و یا پرانتزهای خالی مربوط به تابع رو جا گذاشته باشید.

!NUM# : این خطا یعنی یکی از اعدادی که در فرمول شما وجود دار د مشکل دارد. به عنوان مثال عدد خیلی خیلی بزرگ یا خیلی خیلی کوچک هست.

DIV/0 # : این خطا نشان دهنده ی  این هست که  جایی از فرمولتون مقداری رابر صفر تقسیم کرده باشید. ممکنه مقدار یکی از سلول هایی که در فرمولتون استفاده کردید صفر باشه یا فاقد مقدار باشد. اکسل سلول هایی رو که فاقد مقدار هستند در محاسبات صفر در نظر میگیره و اگر جایی عددی رو بر اونها تقسیم کرده باشید این خطا نشون داده میشود.

!REF# : این خطا به معنی این است که شما در فرمول به سلولی اشاره کردید که وجود ندارد. این خطا معمولا زمانی رخ میدهد که شما سلول هایی رو پاک کنید یا به عنوان مثال فرمول را  که به صورت نسبی نوشته شده و در آن به سلول A3 اشاره شده، از سلول A4 اون شیت به سلول A2 شیت دیگه کپی کنید.

N/A# : این خطا معمولا زمانی نشان داده میشود که مقدار مورد نظر شما پیدا نشود. به عنوان مثال شما مقدار موردنظر شما در یک تابع LOOKUP در هیچ یک از سلول های محدوده ای که برای تابع تعریف کردید وجود نداشته باشه.

!NULL# : این خطا بیانگر این است که در فرمول جایی که باید از علایم ریاضی استفاده کنید از فاصل استفاده کردید . به عنوان مثال به جای فرمول  =A1+A2+A3  اشتباها مقدار  =A1+A2 A3 راوارد کردید. یکی از دلایل دیگری که باعث میشود  این فرمول نمایش داده شوند این است که  جایی در فرمول برای مشخص کردن یک محدوده بین دو سلول علامت : رو جا انداخته اید.

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

نکاتی پیرامون تغییر کاراکتر جداکننده در توابع اکسل

کاراکتر جدا کننده پارامترها در توابع اکسل                                                                                      به صورت پیش فرض کاراکتر “;” یا semicolon هست. بعضی مواقع  که در سیستم این کاراکتر چیزی غیر از semicolon تعریف شده. در این حالت چون به کاراکتر “;” عادت کردیم این مساله خیلی آزار دهنده میشود  این مشکل به خصوص زمانیهب وجد می آید که تنظیمات علائم در سیستم عامل روی زبان فارسی باشد.

به عنوان مثال در بعضی از سیستم ها  که هنگام نصب سیستم عامل تنظیم زبان  روی گزینه Persian بوده این کاراکتر به جای “;” روی کاراکتر “؛” تنظیم شده که خیلی باعث اشتباه میشود . برای تغییر کاراکتر جدا کننده پارامترها در توابع اکسل به کاراکتر دلخواه می توانیم  از روش زیر استفاده کنیم.

ابتدا کنترل پانل سیستم رر باز میکنیم و  روی گزینه Change date, time or number formats کلیک میکنیم.

 

در پنجره ای که باز میشود  روی گزینه Additional settings…‎ کلیک میکنیم.

 

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

 

با این کار کاراکتر جداکننده پارامترها به کاراکتری که ما تعریف کردیم تغییر میکند.

نکاتی در مورد چک لیست های مرتبط در اکسل

در نرم افزار اکسل  برای  یافتن چک باکس ها که به هم مرتبط هم هستند راهکاری زیادی وجود دارد    آموزش اکسل پیشرفته رابا یک مثال ادامه خواهیم داد  

در این مثال 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 باید نوشته شود.

نکاتی در مورد وارد کردن داده در Excel

 

در واقع درنرم افزار اکسل  هر سلول را با نام ستون و شماره سطر می شناسیم و در قسمت 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 را هم کلیک می کنیم.

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

 

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

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

 

مطالبی پیرامون یافتن مفاهیم تکراری‌ در Excel

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

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

۲- در سربرگ 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 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد ، که تکراری نشود.

چگونگی حذف سطرها وستون های خالی در اکسل

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

 

 

نکاتی پیرامون بهینه سازی ضرایب در اکسل

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

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

برای مثال فرض کنید از نتایج تجربی، نقاط زیر در مختصات (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 می باشد در نهایت پس از اتمام بهینه سازی مقادیر ضرایب مدل بهینه سازی می شود.

 

 

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

میان‌بر یا Shortcut نام کلیدهای ترکیبی صفحه‌کلید است که در نرم‌افزارهای مختلف کاربردها و عملکردهای متفاوتی دارند. این میان‌برها همان‌طور که از نامش پیداست از اتلاف وقت برای گشتن در منو‌ها و پیدا کردن عملکرد‌های مختلف جلوگریری می‌کند و در واقع سرعت کار شما را افزایش می‌دهد. اگر شما از کارمندان ادارات و دبیرخانه‌ها باشید، حتما به این کلید‌ها نیاز خواهید داشت و با استفاده از آن‌ها باعث صرفه جویی در وقت خود خواهید شد. در ادامهبا ما همراه باشسیدتا با میان بر های پر‌کاربرد Excel آشنا شوید                                                                                                                                        – Undo و Redo

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

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

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

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

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

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

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

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

 

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

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

 

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

۷– خانه (Home)

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

۸– کنترل (Crtl)

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

۹– Shift

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

۱۰– پایان (End)

 

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

 

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

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

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

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

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

نمودار خطی Line Chart

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

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

نکات جذاب در مورد اکسل

یکی از امکاناتی که این برنامه در اختیار کاربران قرار می‌دهد، برقراری ارتباط میان چند فایل اکسل، مقادیر موجود در سلول‌های ذخیره شده در هر فایل و دسترسی سریع به هر صفحه (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 کلیک کنید.

 

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

 

 

مطالبی در مورد تابع AVERAGE در اکسل

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

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

AVERAGE (مقدار ۳[اختیاری] , مقدار ۲[اختیاری] , مقدار۱ , …)

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

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

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

 

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

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

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

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

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

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

تکنیک های کاربردی در اکسل

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

بین فایل های مختلف اکسل (excel) جابجا شوید

وقتی چند صفحه باز شده دارید، جابجایی بین آن ها بسیار آزار دهنده است چرا که اگر گاهی در صفحه اشتباه کار کنید تمام پروژه نابود می شود. با استفاده از ctrl+tab می توانید بین چند فایل مختلف آزادانه جابچا شوید. این دستور در محیط ویندوز برای جابجایی بین صفحات ویندوز هم کاربرد دارد.

تبدیل ستون ها به سطرها (و بالعکس) در اکسل

گاه ممکن است بخواهید در نرم افزار اکسل (excel) داده ها را از فرمت ستون به فرمت سطر(یا بالعکس) تبدیل کنید. برای اینکار، سطر یا ستونی که میخواهید تبدیل در آن ها انجام دهید را کپی کنید، سلول مقصد را راست کلیک نمائید و Paste Special را انتخاب نمائید. سپس کادری با برچسب Transpose که در پائین پنجره ظاهر می شود را تیک بزنید و ok را کلیک نمائید. اکسل بقیه کارها را انجام می دهد.

PivotChart

PivotChart به شما امکان می دهد تا به روشی آسان و قابل فهم نگاهی سریع به مجموعه داده های پیچیده داشته باشید. PivotChartها متغییرهای مشابه متغییرهای چارت قدیمی یعنی PartPivotTable دارند، همان مجموعه داده ها، همان طبقه بندی ها ، اما در PivotChartها فیلترهای تعاملی نیز وجود دارند، بنابراین میتوانید گشت و گذاری در میان زیر مجموعه داده ها داشته باشید.
اکسل ۲۰۱۳، شامل بخش Recommended PivotCharts است که در زیر آیکن Recommended Charts در قسمت Charts تب Insert لیست شده اند. با چرخاندن اشاره گر ماوس روی آن گزینه میتوانید پیش نمایش یک چارت را ببینید. برای ساخت دستی PivotChart ، آیکن PivotChart را در تب Insert انتخاب نمائید.

فرمول IF and IF ERROR در اکسل

IF and IFERROR دو نوع از مفیدترین فرمول های IF در اکسل هستند. IF به شما امکان می دهد از فرمول های شرطی استفاده کنید که از یک سو صحیح (true) بودن یک چیز خاص را محاسبه می کند و از سوی دیگر غلط (false) بودن آن چیز خاص را. مثلاً میتوانید دانش آموزانی که امتیاز ۸۰ یا بالاتر را در یک امتحان گرفته اند شناسایی کنید. به این ترتیب که اگر امتیاز درون ستون C بالای ۸۰ باشد، سلول می باید ‘Pass’ را گزارش دهد و اگر امتیاز ۷۹ یا زیر ۷۹ باشد، باید «Fail» را گزارش نماید.
IFERROR نوع دیگری از فرمول استاندارد IF است و اگر فرمول مورد استفاده شما سعی در بازگرداندن خطا داشته باشد، به شما امکان می دهد تا یک مقدار خاص (یا مقدار خالی) را بازگردانید. مثلاً اگر Vlookup را در برگه یا جدول دیگری اجرا می کنید، فرمول IFERROR میتواند در هر نمونه که Vlookup قادر به پیدا کردن مرجع نیست، فیلد را خالی تحویل دهد.

انتخاب سریع یک محدوده به کمک کلید  Shiftدر اکسل

در اکسل اولین سلول را انتخاب کرده و با نگهداشتن کلید Shift و کلیک روی سلول انتهایی آن محدوده را انتخاب نمایید.

 

Vlookup

Vlookup به شما امکان می دهد داده هایی که در سراسر برگه های اکسل پراکنده شده اند را جمع آوری کنید و آن برگه ها را در یک محل جهت ساختن گزارشات و خلاصه ها، جمع آوری نمایید.
فرض کنید که دریک مغازه خرده فروشی کار می کنید، هر محصول معمولاً دارای شماره کالای مخصوص است و شما میتوانید از این شماره به عنوان نقطه ارجاع در Vlookup ها استفاده کنید. فرمول Vlookup این آیدی را با آیدی مربوط در برگه دیگر مطابقت می دهد، بنابراین میتوانید توضیح کالا، قیمت، میزان کالای موجود در انبار و اطلاعات دیگر که به کتاب کار فعلی شما اشاره دارند را استخراج نمایید.
فرمول Vlookup را در منوی فرمول ها فرابخوانید و سلولی که شامل شماره ارجاع شماست (۱) وارد کنید، سپس محدوده سلول های موجود در برگه یا کتاب کاری که لازم است داده ها را از آنها استخراج کنید، وارد نمایید (۲) شماره ستون مربوط به نقطه داده ای که بدنبالش هستید را وارد کنید (۳) و در صورتی که نزدیکترین آیتم به مرجع را می خواهید، True و در صورتی که آیتم دقیق را میخواهید، False را وارد نمائید.

ساخت چارت ها در اکسل

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(۱) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(۲).
اکسل ۲۰۱۳ شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید. پس از ساختن نسخه عمومی یک چارت، منوهای Chart Tools را برای اختصاصی کردن آن باز کنید. گزینه های جالبی را می بینید که می توانید برای کارتان از آن ها استفاده کنید.

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

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

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

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

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

 

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

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

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

 

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

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

مقادیر ثابت

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

آدرس سلول ها

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

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

عملگرها

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

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

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

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

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

به طور مثال نتیجه حاصل از عملیات 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 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

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

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

• توابع آماری

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

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

• توابع متنی

• توابع منطقی

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

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

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی ۳; ورودی ۲; ورودی ۱) نام تابع

مثال از یک تابع: برای اینکه با عملکرد یک تابع به صورت عملی آشنا شوید، بهتر است یک مثال را در اکسل اجرا کنید. برای اینکار از تابع 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 نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  ۱۲ و ۱۰ و ۸ را وارد می کنیم.

 

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

مطالبی ارزنده پیرامون قفل کردن سلول های در اکسل

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

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

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

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

 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 کلیک کنید .


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

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

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

با ذکر یک مثال این مسئله بیشتر روشن می شود.

فرض کنید در یک ستون تعدادی عدد را وارد کرده اید. اکنون می خواهیم فرمولی را بنویسیم که مشخص نماید آیا اعداد وارد شده تکراری دارند یا خیر. (توجه داشته باشید هدف این مثال فهمیدن منطق این فرمول ترکیبی نیست بلکه هدف فقط روش ترکیب و بدست آوردن یک فرمول پیچیده تر و حذف ستون های کمکی است)
برای انجام این کار آن را به چند مرحله تقسیم می کنیم.
اعداد را در ناحیه B1 تا B10 وارد می کنیم.

 

ناحیه داده ها را نامگذاری می کنیم.(در این مثال Data)
الف ) تابعی می نویسیم که در صورت وجود اعداد تکراری، اولین عدد تکراری را مشخص کند. Mode (Data) (این فرمول در سلول کمکی E2 نوشته شده است)

تابع شمارش شرطی در اکسل
ب)  تابع شمارش شرطی را می نویسیم تا مشخص نماید که آیا نتیجه اولین تابعی که نوشتیم true یا false است COUNTIF(Data,E2)>1 (این فرمول در سلول کمکی F2 نوشته شده است)

ج) تابع شرطی را می نویسیم که در صورت true بودن نتیجه شرط قسمت (ب) عبارت "لیست با تکرار" و در صورت false بودن نتیجه قسمت (ب) عبارت "لیست بدون تکرار" را نمایش دهد.
IF)F2,"لیست با تکرار","لیست بدون تکرار")
(این فرمول در سلول کمکی G2 نوشته شده است)
اکنون در سلول نهایی تابع ترکیبی را به صورت
IF(COUNTIF(Data,MODE(Data))>1(,"لیست با تکرار","لیست بدون تکرار"
می نویسیم که این تابع مستقل از سلول های کمکی است (یعنی آدرس های E2 و F2 در آن وجود ندارد) و با حذف آنها در نتیجه تغییری حاصل نمی شود.
ما در تابع نهایی به جای آدرس های مرجع E2 و F2 فرمول واقع در این سلول ها را نوشته ایم.                                                                 چگونگی استفاده از توابع در اکسل                                                                                    تابع یك فرمول از پیش نوشته شده و نهادین است كه در وقت شما صرفه جویی می كند.excel بیش از 300 تابع دارد بنابراین مطمئناً می توانید تابع ریاضی مورد نظر خود را پیدا كنید. برای استفاده از یك تابع، یك = ، اسم تابع و آرگومان های تابع را داخل پرانتز تایپ كنید. مانند = SUM (C4 : C22) . بین آرگومان ها یك كاما یا ویرگول بگذارید. وقتی كه اسم تابع را تایپ می كنید، آرگومان های آن در یك Screen Tip ظاهر می شود تا به شما كمك كند تا آنها را به ترتیب درست وارد كنید. اگر به كمك بیشتری نیاز دارید، كادر محاوره ای Insert Functions در اختیار شماست.

 

 روی دكمه Insert Functions كلیك كنید  

 

 روی سلولی كه می خواهید نتیجه تابع در آن ظاهر شود كلیك كنید. بعد روی دكمه Insert Functions در نوار فرمول كلیك كنید تا كادر محاوره ای Insert Functions باز شود.

یكی از توابع را انتخاب كنید  

 

این كادر محاوره ای دو راه برای پیدا كردن یك تابع در اختیار شما قرار می دهد: شرح تابع مورد نظر را تایپ كنید و روی Go كلیك كنید. توابع مربوطه در قسمت Select a Functions لیست می شوند. یا اینكه، از لیست كشویی، یك دسته یا گروه را انتخاب كنید. لیست توابع مربوط به این دسته در كادر پایین ظاهر می شوند. در این لیست اسكرول كنید و روی یك تابع كلیك كنید. شرح تابع انتخاب شده در پایین كادر محاوره ای ظاهر می شود. وقتی كه تابع مورد نظر را پیدا كردید، روی Ok كلیك كنید.

 

 آرگومان اول را وارد كنید  

 

كادر محاوره ای Functions Arguments مطابق با تابعی كه در مرحله 2 انتخاب كردید باز می شود. مراحل این كار عملی، مربوط به تابع IF می باشند. این تابع به سه آرگومان نیاز دارد: عبارتی كه باید ارزیابی شود، قسمتی كه در صورت درست بودن عبارت باید انجام شود و قسمتی كه در صورت نادرست بودن عبارت باید انجام شود. برای وارد كردن آرگومان ها، روی سلول های مورد نظر كلیك كنید (اگر لازم باشد كادر محاوره ای را به قسمتی بكشید كه بتوانید سلول ها را به راحتی انتخاب كنید).

 

 مقداری كه در صورت درست بودن عبارت باید وارد شود را مشخص كنید  

 

 در كادر آرگومان دوم كلیك كنید و مقداری كه می خواهید در صورت درست بودن عبارت ظاهر شود را تایپ كنید. من می خواهم كه اگر میزان فروش بیشتر از یا مساوی میزان هدف باشد كلمه Over نوشته شود. توجه داشته باشید كه متن باید در علامت كوتیشن قرار گیرد: ًOverً .

 

 مقداری كه در صورت نادرست بودن عبارت باید وارد شود را مشخص كنید  

 

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

 

 فرمول را به پایان برسانید  

 

 روی Ok كلیك كنید تا كادر محاوره ای Function Arguments بسته شود و فرمول تكمیل شود. نتیجه فرمول در برگه كاری ظاهر می شود و خود فرمول (تابع) در نوار فرمول نوشته می‌شود.

*توجه  *

تست منطقی

تست منطقی مورد استفاده، توسط توسط تابع IF، مقایسه ای بین دو مقدار است. می توانید دو سلول را با هم مقایسه كنید یا یك سلول را با یك مقدار یا نتیجه فرمول مقاسیه كنید. برای مقایسه ها می توانید از عملگرها مثلاً > (بزرگتر از)، < (كوچكتر از)، = (مساوی با)، < > (نامساوی)، > = (بزرگتر یا مساوی) و < = (كوچكتر یا مساوی) استفاده كنید.

عدم استفاده از كادر های محاور های

اگر با یك تابع آشنا هستید، لازم نیست كه از كادرهای محاوره‌ای نشان داده شده در اینجا استفاده كنید. و می توانید از تابع به همان راحتی فرمول، استفاده كنید. یك Screen Tip ، پایین سلول نتیجه ظاهر می شود تا آرگومان هایی كه ممكن است فراموش كرده باشید را به شما یادآوری كند.

 

نکاتی ارزنده پیرامون کاربرد تابع CONCATENATE و جداسازی یک ستون در اکسل

کاربرد تابع CONCATENATE

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

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

=A1&” “&B1

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

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

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

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

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

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

 

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

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

نکاتی پیرامون ایجاد اعداد تصادفی و استفاده ازتابع شرطی (if) در اکسل

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

---

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

 

---

2 پارامتر اول آن را با مقدار ابتدای بازه و پارامتر دوم آن را با مقدار انتهایی بازه مقدار دهی کنیم.به عنوان نمونه (RANDBETWEEN(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 همان طور که مشاهده می کنید دما پایین تر از ۳۰ درجه هست به همین دلیل در قسمت وضعیت  شرط “خنک” نشان داده شده.

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

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

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

داده ها	نتیجه تابع FREQUENCY	نتیجه تابع IF	نتیجه تابع SUM	توضیحات
152	1	152	2220	عدد 152 یک بار وارد شده است
725	3	725		عدد 725 سه بار تکرار شده است
326	1	326		عدد 326 یک بار وارد شده است
15	1	15		عدد 15 یک بار وارد شده است
45	2	45		عدد 45 دوبار تکرار شده است
725	0	0		این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود
147	1	147		عدد 147 یک بار وارد شده است
725	0	0		این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود
198	1	198		عدد 198 یک بار وارد شده است
45	0	0		این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود
612	1	612		عدد 612 یک بار وارد شده است

 

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

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

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

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

 

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

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

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

این لیست بر اساس اکسل 2016 تهیه شده است اما بیشتر آن‌ها بر روی اکسل 2013 نیز کار خواهد کرد.

1. دسترسی به منوی ابزار توسط صفحه کلید: اکسل مثل افزونه Vim-inspired در کروم و فایرفاکس قابلیتی به نام Key Tips دارند که وقتی کلید Alt را می‌فشارید، منوی Ribbon با یک سری حروف پر می‌شود. حرف مورد نظر بر روی صفحه کلید را بزنید تا آن گزینه‌ی مشخص فعال شود.
2. Ctrl + PgDn: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از چپ به راست.
3. Ctrl + PgUp: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از راست به چپ.
4. F12: نمایش کادر “Save As”
5. Ctrl + Shift + $: (اکسل 2016) تغییر فرمت سلول فعلی به پول با دو رقم اعشاری
6. Ctrl + Shift + %: (اکسل 2016) تغییر فرمت سلول فعلی به درصد بدون رقم اعشاری
7. Ctrl + Shift + #: (اکسل 2016) تغییر فرمت سلول فعلی به تاریخ با روز، ماه، سال
8. Ctrl + Shift + “:”: قراردادن زمان فعلی
9. Ctrl + Shift + “;”:قراردادن تاریخ فعلی
10. F4: تکرار آخرین فرمان و عمل، در صورت امکان
11. Shift + Arrow key: گسترش سلول انتخابی فعلی با یک سلول دیگر در جعت انتخابی
12. Ctrl + F1: نمایش یا مخفی کردن نوار ابزار
13. Alt + Shift + F1: ایجاد یک سربرگ صفحه گسترده جدید
14. Ctrl + F4: بستن صفحه گسترده فعلی
15. Ctrl + D: اجرای فرمان Fill Down برای سلول‌های انتخابی زیرین. Fill Down محتوا و فرمت بالاترین سلول را در کل ستون کپی می‌کند.

نکاتی در مورد تنظیمات سلول در اکسل

 در نرم افزار اکسل برای تنظیم سلول ها میتوان از روروش های زیر اسیتفاده نمود                          براي قالب بندي خانه هادر اکسل  مراحل زير را طي ميکنيم :
 روش اول : از تب Home  منو Number  را انتخاب کنید.
روش دوم : بر روي خانه هاي مورد نظرکليک راست کرده و گزينه Format Cells را انتخاب ميکنيم.
روش سوم : فشردن کليدهاي ctrl +

 

با اجراي يکي ازسه روش بالا پنجره اي باز ميشود که  Tab Number را انتخاب می کنیم :

در Tab ذکر شده  Sample  و Category مشاهده می شود که هر کدام به شرح زیر می باشد.

•  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 آن را به صورت متن نشان ميدهد و مقادير آنرا محاسبه نميکند. (کاردرکارگاه 7 تمرين 8 تا 13 )
11- Special : مقادير را با استفاده از قواعد قالب بندي خاص ( مانند كد پستي ، کد پستي به اضافه چهار رقم، يا شماره تلفن، شماره تامين اجتماعي) نشان ميدهد. مثلاً اگر در يک سلول يک شماره تلفن ( در حالت 10 رقمي) تايپ کنيم ، با انتخاب اين فرمت به صورت قالب بندي تلفن در مي آيد. مثلاً اگر شماره IT (3116681184) را وارد ميکنيم، به فرمت تلفن در آمده (1184-668 (311) ) و مي فهميم که 311 کد شهرستان

، 668 کد محله و 1184 شماره تلفن IT ميباشد.

مطالبی پیرامون تغییر پهنای ستون ها در اکسل

برای تغییر پهنای ستون به یکی از دو روش زیر عمل میکنیم :
روش اول :
کافی است به مرز بین ستون مورد نظر و ستون بعدی رفته تا مکان نما به صورت یک فلش دو سر در آید سپس مرز ستون را در جهت مورد نظر Drag میکنیم.
روش دوم :
در این روش پهنا را به صورت دقیق تنظیم میکنیم . برای اینکار مراحل زیر را طی میکنیم :
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Width
4-    در کادر باز شده مقدار دلخواه را وارد میکنیم.
نکته: به طور پیش فرض پهنای ستون 8.43میباشد.
 
تغییر پهنای استاندارد ستون :
میدانیم که پهنای استاندارد ستونها 8.43 میباشد اگر بخواهیم این پهنای استاندارد را کم یا زیاد کنیم به ترتیب زیر عمل میکنیم : ( فقط به یاد داشته باشید این تغییر بر روی ستونهایی اعمال میشود که پهنای آنها را تغییر نداده باشیم.)
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Standard Width
4-    در کادر باز شده پهنای مورد نظر را وارد میکنیم.
 
جستجوی داده ها:
اگر بخواهیم رشته کاراکترهای خاصی را در کاربرگ خود پیدا کنیم، مشاهده هر یک از سلولها کار سختی خواهد بود. پیدا کردن یك رشته از کاراکتر‌ها که باید تغییر یابند نیز مشکل تر میباشد. اکسل ویژگیهایی دارد که به ما در یافتن داده‌ها و تعویض آنها کمک میکند.
 
استفاده از Find : برای فعال كردن Find به یكی از دو روش زیر عمل میكنیم :
روش اول :
1- انتخاب منوی Edit
2- انتخاب find
روش دوم :
فشردن همزمان كلید‌های Ctrl F
جهت جستجوی داده‌ها مراحل زیر را انجام میدهیم :
1-        لغتها یا عددهایی را که میخواهیم مکان یابی کنیم در کادر متن Find What وارد میکنیم.
2-        دکمه Find Next را کلیک میکنیم و Excel اولین مورد از شرط جستجو را پیدا میکند و آن را انتخاب مینماید. کادر مکالمه Find بر روی صفحه نمایش باقی می ماند تا بدین ترتیب بتواند جستجو برای متن یا اعداد مشخص شده را ادامه دهیم.
3-        جهت جستجو برای مورد بعدی داده‌های مشخص شده Find Next را مجدداً کلیک میکنیم.
4-        جهت برگشت به محیط دكمه Esc را فشرده و یا بر روی دكمه Close كلیك میكنیم. تا کادر مکالمه Find بسته شود و سپس با داده‌های داخل سلول به کارمان ادامه میدهیم.
5-        جهت جستجو پس از بستن کادر مکالمه ، با فشار دادن Ctrl F کادر مکالمه را میتوانیم مجدداً باز نماییم.
نکته: اگر محدوده ای از سلول‌ها را در یک کاربرگ , قبل از باز نمودن کادر مکالمه Find را انتخاب کنیم ، جستجو را برای این سلولها محدودكرده ایم .
 
برقراری شروط بیشتر برای Find :
کادر مکالمه Find با ارائه گزینه‌های زیر جستجو را با تخصصی تر نمودن آن محدود میسازد.
ü      Search : میتوان جهت جستجو را مشخص کرد. جستجو به صورت سطری باشد (By Row) یا به صورت ستونی باشد
ü      By Column : با استفاده از این گزینه میتوانیم جستجو را به یک عنصر خاص محدود کنیم در این كادر گزینه‌های Formula , Values, Comments را خواهیم داشت.
ü      Match Case : با انتخاب این گزینه محیط به حروف بزرگ و کوچک حساس شده و کلماتی را پیدا میکند که از نظر حروف بزرگ و کوچک مطابق متن تایپ شده در کادر Find What باشد.
ü      Find Entire Cell Only : این گزینه از Excel میخواهد سلولهایی را که داده‌های اضافه بر رشته کاراکتر جستجو دارند نادیده بگیرد.
 
تعویض داد ه‌ها استفاده از Replace :
اگر بخواهیم داده پیدا شده توسط Find را با داده‌های دیگر جایگزین كنیم می توانیم پس از هر بار جستجو پنجره جستجو را بسته ، ویرایش را انجام داده ، دوباره پنجره Find را باز كنیم و عمل جستجو را ادامه دهیم كه این كار وقت گیر میباشد. Excel دستوری ساده برای انجام این كار در نظر گرفته است. با استفاده از Replace میتوانیم این كار را انجام دهیم. جهت انجام جایگزینی مراحل زیر را انجام میدهیم :
1-    متن جستجو را در کادر Find What وارد میکنیم.
2-    متن تعویضی را در کادر متن Replace With وارد میکنیم.
3-    جهت تایید هر یک از تعویض‌ها مراحل زیر را دنبال میکنیم.
الف)     Find Next را کلیک كرده، Excel به مورد بعدی داده جستجو حرکت میکند و سلول را انتخاب می کند.
ب)      در صورتی که بخواهیم تعویض را انجام دهد دکمه Replace را کلیک میکنیم .
ج)       Find Next را جهت حرکت به موردبعدی کلیک می کنیم.
د)       جهت تعویض تمام موارد داده‌های جستجو با با دادههای تعویضی ، Replace All را کلیک می‌کنیم.
 
پر کردن خودکار چند خانه در  excel
پر کردن خودکار چند خانه یکی از مفید ترین ویژگیهای برنامه Excel ، قابلیت پر کردن داده‌های متوالی بصورت خودکار میباشد. که به این عمل Auto fill گویند. داده‌های متوالی میتوانند مجموعه ای از داده‌ها بصورت 1و2و3و... یا 4و5و.... یا از حروف A و B و C و ... تشکیل شده باشند. همچنین میتواند تاریخ یا روزهای هفته یا ماههای سال باشند. حتی میتوان مجموعه ای از داده‌های خاص را تعریف کرد. برای این کار مراحل زیر را انجام میدهیم :
1-    چند نمونه اولیه از مقادیر متوالی مزبور را مینویسیم.
2-    این مقادیر را انتخاب میکنیم.
3-    مکان نمای ماوس را به گوشه پایین سمت راست قسمت انتخابی می آوریم تا به شکل ( )در آید. به اشاره گر ماوس در این حالت Fill Handle می گوییم.
4-    حال به طرف پایین یا راست Drag میکنیم.
5-    به این ترتیب با رها کردن کلید ماوس ، خانه‌های مورد نظر با مجموعه داده‌های متوالی پر میشوند.
 
ایجاد لیست جدید خودكار:
اگر برای حروف الفبا Auto fill را اجرا كنیم ، پر كردن خودكار انجام نمی شود. چون حروف الفبا جزء لیستهای آماده نیست پس باید ایجاد شود. برای ایجاد لیست جدید مراحل زیر را انجام میدهیم :
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list : Tab
4-    انتخاب New List در کادر Custom List
5-    تایپ اطلاعات در قسمت List Entries (بعد از وارد کردن هر اطلاعات Enter میکنیم.)
6-     انتخاب دکمه Add
7-    OK
ایجاد یك لیست خودكار بر اساس لیست موجود در صفحه در EXCEL :                                                 اگر لیستی بر روی صفحه داشته باشیم و بخواهیم بصورت لیست دائمی در آوریم باید مراحل زیر را انجام دهیم. :
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list : Tab
4-    روی دکمه Collapse dialog که در انتهای کادر فهرست Import List From Cells قرار دارد Click میکنیم. تا پنجره مینیمایز شود.
5-    انتخاب لیست‌ها در صفحه
6-     روی دکمه Collapse Dialog که در انتهای کادر فهرست Import list From Cell قرار دارد Click میکنیم. تا Resize شود.
7-    انتخاب دکمه Import
8-    OK
نکته: میتوانیم بجای انجام مراحل 4و 5و 6، در كادر Import ، آدرس سلولهایی كه لیست در آنها قرار دارد را تایپ كنیم. ویرایش لیست خودكار میتوانیم لیستهایی را كه ایجاد كرده ایم ویرایش كنیم . یعنی اطلاعاتی را حذف كنیم ، ویرایش كنیم یا اضافه كنیم. برای این كار مراحل زیر را انجام میدهیم :
1-    انتخاب منوی Tools
2-    انتخاب گزینه Option 3
3-    انتخاب :Custom list Tab
4-    انتخاب لیست مورد نظر در کادر Custom list
5-    کلیک در محل مورد نظر در کادر List Entries
6-     ویرایش متن
7-    OK
 
پاک کردن لیست خودكار در اکسل :
اگر بخواهیم یك لیست خودكار حذف كنیم تا دیگر پر كردن خودكار بر روی آن عمل نكند مراحل زیر را طی میكنیم :
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب :Custom list Tab
4-    انتخاب List مورد نظر در کادر Custom List
5-    انتخاب دکمه Delete
6-     OK

اصطلاحات کاربردی نمودار ها دئر اکسل

اصطلاحات پر کاربرد نمودارها در اکسل   :
1-         Gird Line : خطوطی هستند که در امتداد مقادیر محور X , Y ظاهر میشوند. این خطوط در تعیین مقدار دقیق یک نقطه به ما کمک میکنند.
2-         Tick : خطوط کوتاهی هستند که با محور متقاطع بوده و قسمتهایی از یک مقیاس سری با گروه را جدا میکنند.
3-         Chart Title : متن بکار برده شده برای شناسایی عنوان نمودار میباشد.
4-         Axis : به محور‌ها در نمودار‌ها گفته میشود.
5-         Legend : هر نمودار دارای راهنمایی است كه نشان دهنده این است كه هر رنگ مربوط به كدام سری است.
 
روشهای ایجاد نمودار  در  excel:
روش اول :
1-                انتخاب منوی Insert
2-                انتخاب گزینه Chart
روش دوم :
1-    انتخاب آیکون Chart Wizard
نکته: در زمان ایجاد نمودار با کلیک بر روی دکمه Next به صفحات بعدی میرویم و کلیک بر روی دکمه Back باعث برگشت به صفحه قبلی میشود و با کلیک بر روی Finish کادر نمودار‌ها بسته میشود. نکته میتوانیم اطلاعاتی را كه میخواهیم بر اساس آن نمودار رسم كنیم ابتدا انتخاب كنیم بعد به یكی از دو روش بالا عمل كنیم. میتوانیم هم در حین كار انتخاب را انجام دهیم.
با انجام یکی از دو راه بالا پنجرهای زیر به ترتیب باز میشود :
ü          پنجره Chart Type : این پنجره شامل دو Tab زیر میباشد :
1)         Standard Type : که شامل قسمتهای زیر است :
الف)  Chart Type : که در این قسمت نوع اصلی نمودار را انتخاب میکنیم.
ب)   Chart Sub-Type : در این قسمت نوع فرعی نمودار را مشخص میکنیم.
ج)    Press and Hold to View Sample : در این قسمت پیش نمایشی از نمودار با اطلاعاتی که خودمان داده ایم نشان میدهد. برای این کار کافی است روی این دکمه Click کرده و نگه داریم.
2)         Custom Type : در این قسمت میتوان از نمودارهای سفارشی استفاده کرد یا یك نوع نمودار ایجاد كرد.
 
ü          پنجره Chart Source Data : این پنجره شامل دو Tab زیر میباشد :
1)         Data Range : که شامل قسمتهای زیر است :
الف) Data Range : در این قسمت آدرس داده هایی را که قرار است نمودار برای آنها کشیده شود، میتوان تغییر داد همچنین میتوانیم آدرس را با كلیك بر روی Collapse Dialog و با Drag كردن وارد كنیم. و یا میتوانیم با استفاده از فرمت زیر آدرس را تایپ كنیم. شماره سطر پایان $ نام ستون پایان$ شماره سطر شروع$ نام ستون شروع$! نام Sheet =
ب) Series in : این قسمت جهت نمایش داده‌ها را مشخص میکند. یعنی کدام یک از این دو برچسب از داده‌ها در پایین نمودار قرار خواهد گرفت. اگر Row را انتخاب كنیم سطر‌ها به عنوان سری و اگر Column را انتخاب كنیم ستونها به عنوان سری قرار میگیرند.
2)         Series : که شامل قسمتهای زیر است :
الف) Series : در این قسمت نام سری‌های موجود نوشته شده است.
ب)   Name : توسط این قسمت میتوانیم نام سری را عوض کنیم.
ج)    Value : در این قسمت میتوانیم آدرس مقادیر هر سری را مشخص كنیم. همچنین میتوانیم مقادیر را تایپ كنیم. برای تایپ مقادیر باید مقدار سری را در علامت { } وارد كنیم.
د)    Category(x) Axis labels : در این قسمت میتوان آدرس خانه هایی را داد که محتوای آنها بر روی محور X نوشته میشوند.
ه)     دکمه Add : یک سری جدید ایجاد میکند.
و)     دکمه Remove : بر روی نام هر سری در قسمت Series کلیک کنیم و سپس این دکمه را بزنیم ، سری حذف میشود.
ü          پنجره Chart Option : این پنجره شامل 6 Tab زیر است :
1)             Titles : این قسمت شامل موارد زیر است :
الف) Chart Title : در این قسمت عنوان نمودار را مینویسیم.
ب)   Category(x) Axis labels : در این قسمت عنوان محور X را مینویسیم.
ج)    Value (Y) Axis : در این قسمت عنوان محور Y را مینویسیم.
2)             Axes : این قسمت شامل گزینه‌های زیر است :
الف)  Category(x) Axis اگر در کنار این کادر تیک خورده باشد ، مقادیر روی محور X نشان داده میشوند و اگر تیک نخورده باشد نشان داده نمیشوند.
ب)   Value (Y) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور Y نشان داده میشوند در غیر این صورت نمایش داده نمیشوند.
3)             Grid Line : که این Tab شامل قسمتهای زیر است :
الف)  Category(x) Axis که خود شامل دو قسمت است :
×      Major Gridline : اگر این قیمت تیک خورده باشد ، خطوط شبکه رسم میشوند ( تمامی خطوط رسم نمیشوند)
×      Minor Gridline : اگر این قسمت تیک خورده باشد خطوط دیگری بین خطوط بالا رسم میشوند. این دو قسمت برای خطوط شبکه موازی محور Y‌ها هستند.
ب)   Value (Y) Axis : که شامل دو قسمت زیر است :
×      - Major Gridline : خطوط شبکه را به موازات محور X رسم میکند.
×      - Minor Gridline : خطوط دیگری را به موازات محور X بین خطوط بالا رسم میکند.
4)             Legend : این Tab شامل قسمتهای زیر است :
الف)  Show Legend : اگر این گزینه تیک خورده باشد راهنما نمایش داده میشود.
ب)   Placement : مکان راهنما را نشان میدهد. Bottom : پایین ، Corner : گوشه بالا سمت راست، Top : بالا، Right : سمت راست، Left : سمت چپ
5)             Data Label : شامل قسمتهای زیر است :
الف) None : هیچ مقداری را بر روی ستونها نمایش نمیدهد.
ب)   Show Value : مقدار هر سری رابر روی ستون آن نشان میدهد.
ج)    Show Label : برچسب هر سری را روی آن نشان میدهد.
د)    Legend Key Next to Label : اگر یکی از حالتهای ب و ج را انتخاب میکنیم. این گزینه فعال شده و در کنار هر مقدار رنگ سری نمایش داده میدهد.
6)              Data Table : شامل قسمتهای زیر است :
الف)  Show Data Table : اگر این گزینه فعال شود ، جدولی مشابه آنچه که ما ، در Sheet کشیده ایم در زیر نمودار ظاهر میشود.
ب)   Show Legend Keys : اگر گزینه الف فعال باشد ، این گزینه نیز فعال میشود. و اگر در کادر آن تیک بزنیم، رنگ هر سری را در کنار اطلاعات آن سری در جدول نشان میدهد.
نکته:   باید توجه داشته باشیم كه كه با توجه به نوع نمودار گزینه‌های موجود در این پنجره میتوانند متفاوت باشند.
ü           پنجره Chart Location :
الف)  As New Sheet : این گزینه نمودار را در یک Sheet جدید با اسم دلخواه ( اسم پیش فرض Chart 1 است) ایجاد میکند. نموداری که در این حالت ایجاد میشود، قابل جابجا شدن و تغییر سایز نیست ولی هر گونه تغییرات بر روی اطلاعات اصلی بر روی نمودار تاثیر دارد.
ب)   As Object in : این گزینه نمودار را در هر Sheet که ما انتخاب میکنیم ، ( از Sheet‌های موجود ) رسم میکند و قابل جابجا کردن و تغییر سایز نیز میباشد.
 
تغییر بر روی نمودار ایجاد شده :
برای تغییر نمودار ایجاد شده باید ابتدا نمودار را انتخاب کرد. سپس مجدداً دکمه Chart Wizard راکلیک کرد. یا از روی Toolbar ، Chart که باز میشود، تغییرات را اعمال کرد.
 
نوار ابزار Chart در اکسل :
این نوار ابزار را میتوانیم در صورتی که فعال نبود با Right Click بر روی نوار ابزار‌ها و انتخاب گزینه Chart فعال کنیم این نوار ابزار دارای آیکونهای زیر است :
×      Chart Object : در این قسمت میتوانیم قسمتی از نمودار را كه میخواهیم بر روی آن تغییر دهیم انتخاب كنیم. با انتخاب هر گزینه قسمت مربوط به آن روی نمودار انتخاب میشود.
×      Format : با توجه به اینكه در Chart Object چه انتخاب شده باشد این آیكون پنجره Properties آن را باز میكند.
نکته: با Double كلیك كردن بر روی هر موضوع از نمودار نیز پنجره Properties آن باز میشود.
×      Chart Type : در این قسمت میتوانیم نوع نمودار انتخاب شده را تغییر دهیم.
×      legend : توسط این آیکون کادر راهنما را ظاهر یا پنهان میکنیم.
×      Data Table : توسط این آیکون میتوانیم جداول داده‌ها را فعال یا غیر فعال کنیم.
×      By Rows :با انتخاب این آیكون سطر‌ها به عنوان سری انتخاب میشوند.
×      By Column : با انتخاب این آیكون ستونها به عنوان سری انتخاب میشوند.
×      Angle Text up : جهت متن نمودار را تغییر میدهد. یعنی متن از بالا به پایین ، کج نوشته میشود. به شرطی این آیكون فعال است كه قسمتهای متنی نمودار انتخاب شده باشد.
×      Angle Text down : متن نمودار را تغییر جهت میدهد. یعنی متن از پایین به بالا و کج نوشته میشود. به شرطی این آیكون فعال است كه قسمتهای متنی نمودار انتخاب شده باشد.
تغییر اندازه :
1)         روی نمودار کلیک میکنیم تا انتخاب شود.
2)         روی یکی از مربع‌های سیاه اطراف نمودار کلیک میکنیم.
3)         مربع را تا زمانی که به اندازه دلخواه تغییر اندازه دهد Drag میکنیم.

نکاتی پیرامون حل مشکل فونت , تایپ فارسی کلمات و اعداد در اکسل

روش ساده تایپ عدد فارسی در اکسل که اکثر کاربران از آن استفاده میکنند ، استفاده از Reginoal & Language می باشد . برخی از کاربران بر این باورند که تنظیم اکسل همانند تنظیم Word می باشد . ولی با اندکی دقت متوجه میشویم که راه فارسی کردن اعداد در برنامه Excel متفاوت می باشد.
اما یک راه کار بسیار ساده برای تایپ اعداد به شکل فارسی اینست که فونت خانه ای که درون آن عبارت خود را تغییر میدهیم را به : B lotus B Nazanin تغییر دهیم. و یا کلا از فونتهایی استفاده کنیم که با حرف B آغاز می شود.

---

راه حل کاملتر برای تنظیم اعداد فارسی در اکسل

برای انجام اینکار به قسمت Format Cell بروید و گزینه ی Custom را انتخاب کنید و کد زیر را برای فارسی کردن اعداد وارد کنید :
[$-3020429]0  در این روش نیاز به نصب فونت نیست.با فونت arial هم کاملا خوانا میشود.

---

حل مشکل حرف ی در اکسل

ممکن است حرف "ی" بروی کیبوردتان مدل های مختلفی داشته باشد یا فایل های موجود شما دارای چند مدل از حرف "ی" باشد که هر کدام از این مدل "ی" ها دارای کد خاصی باشند.
این مشکل راه حلهای مختلفی دارد از راههای وقت گیر و زمان بر باید راه کدنویسی را در پیش بگیریم.
یکی از راههای به وجود آمدن این مشکل فایلهای فارسی ساز ویندوز است که نسخه های مختلفی دارند. برای حل این مشکل میتوانید از فونت های اصلی ویندوز مثل Tahoma استفاده کنید یا اینکه از فونت هایی که اول آنهاB است همچنین میتوانید از فونتهای B zar و یا B zar , B Lotus , B Nazanin استفاده کنید.
اگر با اینکار مشکل حل نشد باید ویندوز خود را عوض کنید و در طی مراحل نصب ویندوز زبان فارسی را انتخاب کنید خوب است بدانید در اکثر مواقع این مشکل زمانی به وجود می آید که زبان فارسی را از داخل محیط ویندوز انتخاب می کنید.

نکاتی در مورد کاربردهای Data Validation در Excel

به طور خلاصه میتوان کاربردهای Data Validation را بصورت زیر عنوان کرد:

• ایجاد لیستهای کشوئی یا Drop-Down که به واسطه آن کاربران داده های خود را سریعتر، دقیقتر و با اشتباه کمتر وارد می نمایند، در ضمن به واسطه آن کاربران ملزم به انتخاب یک داده مشخص و از پیش تعیین شده در لیست می باشند.
• ایجاد محدودیت برای ورود داده در سلولها بر اساس شروط مختلفی از قبیل (کوچکتر یا بزرگتر بودن از مقدار خاصی، دقیقاً برابر بودن با مقداری خاص، مابین دو مقدار خاص بودن و …)
• ایجاد راهنما برای ورود داده ها در سلولها
• نمایش پیغام خطا هنگام رعایت نکردن شروط مذکور و …                                                

  ---

  ایجاد لیستهای کشوئی یا Drop-Down دراکسل :
  برای تفیهم بهتر موضوع، کاربرگ یا Sheet زیر را در نظر بگیرید:

   

  در کاربرگ مذکور، ستونهائی از قبیل محل تولد، مدرک و رشته تحصیلی میتوانند مواردی از پیش تعیین شده و مشخص باشند که برای آنها از لیستهای کشوئی یا Drop-Down List استفاده می نمائیم. بدین منظور ابتدا می بایست داده هایی که در هر لیست هستند در ستونی جداگانه از کاربرگ جاری و یا در کاربرگی دیگر درج گردند. به عنوان نمونه همانند تصویر زیر، لیست محل تولد را که دربرگیرنده نام استانهای کشور است در کاربرگی دیگر ایجاد میکنیم:

  لهای مورد نظر را برای ایجاد لیست انتخاب می کنیم و بر روی گزینه Data Validation کلیک می کنیم:
   

  در ویزارد Data Validation در تب Setting همانگونه که ملاحظه می نمائید در قسمت Allow گزینه Any value بصورت پیش فرض انتخاب شده است یعنی ورود هر داده ای در سلولهای انتخاب شده مجاز می باشد.
  حال برای ایجاد لیست کشوئی در قسمت Allow همانند تصویر زیر گزینه list را انتخاب می کنیم، با انجام این کار فیلدی با عنوان Source نمایان می شود که برای آدرس دهی به همان لیست ایجاد شده از قبل (استانهای ذکر شده برای محل تولد) می بایست مورد استفاده قرار گیرد، برای این منظور شما می توانید آدرس دادهها را در کادر تایپ نمائید و یا با استفاده از آیکن مشخص شده در کنار کادر، با استفاده از اشاره گر موس ناحیه داده ها را به حالت انتخاب درآورده و طبق تصویر زیرین بر روی آیکن مشخص شده کلیک کنید:

   

   

  
  با انجام این کار آدرس ناحیه انتخاب شده همانند تصویر در کادر Source قرار می گیرد:

   

  حال بر روی OK کلیک کلیک کنید، زین پس طبق تصویر زیر در کاربرگ خود و در ستون محل تولد، در کنار هر سلول شکل مثلثی ظاهر می گردد که با کلیک کردن بر روی آن، قادر به انتخاب یک استان از لیست کشوئی می باشید:

   

  ---

  گزینه In-cell DropDown :
  حال در ستون مدرک نیز مطابق موارد مذکور در بالا، لیستی شامل موارد (زیر دیپلم-دیپلم-کاردانی-کارشناسی-ارشد-دکتری) را ایجاد می کنیم، اما این بار تیک گزینه In-cell DropDown را بر میداریم:

   

  با انجام این کار طبق تصویر زیر، شکل مثلث جهت باز شدن لیست کشوئی نمایش داده نمیشود و شما می بایست مقدار مورد نظر خود را تایپ نمائید. حال اگر مقدار وارد شده با یکی از موارد موجود در لیست مطابقت داشته باشد، داده ثبت می شود، اما اگر در کوچکترین حرفی مطابقت نداشته باشد، خطای (The value you entered is not valid ) ظاهر می گردد:

   

  نکته: پس از انجام تنظیمات فوق، در حالی که سلولهای مورد نظر در حالت انتخاب قرار دارند، با کلیک کردن مجدد بر روی گزینه Data Validation در Ribbon قادر به تغییر تنظیمات مذکور خواهید بود، به عنوان نمونه با فعال کردن گزینه In-cell DropDown در سلولهای ستون مدرک، مجدداً لیست کشوئی برای این سلولها همانند تصویر زیر نمایان خواهد شد:

   

   

  ---

  حال فرض کنید، ستونی با عنوان «کد ملی» دارید که می بایست در هر سلول دقیقاً ۱۰ رقم جهت کاهش خطا درج گردد، بدین منظور همانند تصویر زیر ابتدای سلولهای مورد نظر در ستون کد ملی را انتخاب میکنیم و پس از انتخاب گزینه Data Validation از منوی Data، در تب Setting ، گزینه Text Length را در قسمت Allow، گزینه Equal To را در قسمت Data و مقدار ۱۰ را در قسمت Length تنظیم مینمائیم:

   

  پس از انجام تنظیمات فوق، در هریک از سلولهای ستون کد ملی، صرفاً داده ای با طول ۱۰ کاراکتر می بایست وارد شود در غیر این صورت پیغام خطائی ظاهر می گردد:

   

  ---

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

   

  ---

  Circle Data Validation در اکسل :
  حال فرض کنید در برخی از ستونها از قبل داده هایی وارد شده داشته باشیم و پس از آن نسبت به اعمال Data Validation اقدام کنیم، به عنوان نمونه همانند تصویر زیر در ستون «رشته تحصیلی» از قبل داده هایی وارد می کنیم و سپس نسبت به اعمال محدودیت تعداد کاراکترهای مجاز بین ۶ تا ۲۰ اقدام میکنیم:

   

  همانگونه که ملاحظه می نمائید برخی از سلولها دارای شرط مذکور نمی باشند، برای مشخص شدن این سلولها کافیست طبق تصویر زیر گزینه Circle Data Validation را انتخاب کنید، با انجام این کار سلولهای فاقد شرط ذکر شده، با کادری قرمز رنگ مشخص میگردند:

   

  حال اگرزیر بر روی گزینه Clear Validation Circle کلیک کنید، این کادرها حذف خواهد گردید.

   

  علاوه بر موارد مذکور در این آموزش، همانند موارد مذکور، از طریق Data Validation قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ:

   

مطالبی یرامون ترفندهای ضروری در اکسل

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

۱. استفاده از میانبر Ctrl + PgDn و Ctrl + PgUp برای رفتن از یک کاربرگ (worksheet) به کاربرگ دیگردر اکسل

 

۲. با استفاده از کلیدهای جهت دار + Ctrl بین سلول‌ها جابه جا شوید

 

البته واضح است که تنها با کلیدهای جهت دار می‌توانید از یک سلول به سلول دیگری حرکت کنید. اما استفاده از کلیدهای جهت دار + Ctrl این روند را سریع‌تر می‌کند.

۳. برای انتخاب داده‌ها از کلید Shift استفاده کنید

 

کلیدهای ترکیبی Ctrl + Shift+ و کلیدهای جهت دار کمک می‌کند محدوده‌ی داده‌ها را تا آخرین سلول دارای داده انتخاب کنید.

۴. برای کپی دوبار کلیک کنید

 

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

۵. برای تغییر فرمت سلول از کلیدهای میانبر استفاده کنید

 

با استفاده از کلیدهای ترکیبی !+Ctrl + Shift می‌توانید فرمت عدد را به دو رقم اعشار تبدیل کنید. برای تبدیل به فرمت دلار کافی است از کلیدهای ترکیبی $+Ctrl + Shift و برای تبدیل به درصد از کلیدهای ترکیبی %+Ctrl + Shift استفاده کنید.

۶. برای قفل سلول‌ها از کلید F4 استفاده کنید

 

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

۷. خلاصه کردن داده‌ها با توابع CountIF و SumIFدر  Excel

 

تابع CountIF تعداد دفعات تکرار یک داده را در محدوده‌ی انتخاب شده مشخص می‌کند. اولین ورودی این تابع محدوده‌ی مورد نظر و دومین ورودی مربوط به شرط مورد نظر ما است. برای مثال در حالت زیر اسم چند نویسنده به نام‌های سانسا، اریا، راب، جان، ریکون و برن آورده شده است. هر کدام از آن‌ها چند داستان نوشته‌اند که هر داستانی تعدادی بازدیدکننده داشته است. برای مرتب کردن داده‌ها در یک سلول خالی دستور CountIF را نوشته تا از ستون اسم نویسنده‌ها اسم آریا را شمارش کند. پس اولین ورودی تابع ستون B4 تا B50 است و دومین ورودی آن سلول G4‌ است که اسم آریا در آن نوشته شده است.

 

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

۸. استخراج داده‌ها با تابع VLOOKUP

 

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

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

۹. استفاده از & برای ترکیب رشته‌های متنی

 

در اینجا دو ستون داریم که یکی شامل نام و دومی شامل نام خانوادگی است. با استفاده از علامت & می‌توان ستونی ایجاد کرد که نام و نام خانوادگی را در کنار هم داشته باشد. در نرم‌افزار اکسل با کمک & می‌توانید دو قسمت از متن را با هم ترکیب کنید. البته دقت کنید که بین نام و نام خانوادگی حتما فاصله قرار دهید. روش کار به این صورت است: [انتخاب سلول نام خانوادگی]& ” ” & [ّانتخاب سلول نام].

۱۰. پاک کردن متن با توابع LEFT، RIGHT و LEN  در اکسل

 

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

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

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

 

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

حالا با کمک تعداد کاراکترهای به دست آمده از تابع LEN می‌توانید از تابع RIGHT استفاده کنید.

 

از آن جا که ما می‌خواهیم حروف اختصاری و خط فاصله را حذف کنیم، وردی تابع RIGHT را عدد تابع LEN منهای ۳ قرار می‌دهیم.

۱۱. ایجاد اعداد تصادفی با تابع RAND

 

تابع ()RAND می‌تواند اعداد تصادفی بین صفر تا ۱ ایجاد کند. این تابع هیچ گونه ورودی لازم ندارد و داخل پرانتز خالی است. با هر بار فشردن کلید F9 این تابع عدد جدیدی تولید می‌کند. دقت داشته باشید که با ایجاد هر گونه تغییر در فضای کار، یک عدد جدید ایجاد خواهد شد.

 

نکاتی پیرامون انواع نمودار، کاربردها و روش ایجادآن‌ها در نرم افزار(Excel)

برنامه های متفاوتی می‌توانند برای رسم نمودار به کار برده شوند. پرکاربردترین، آسان ترین و جامع ترین برنامه موجود، برنامه‌ی Excel از مجموعه‌ی Microsoft Office می‌باشد. این نرم افزار هرچند حرفه‌ای نیست و خیلی از پژوهش‌گران از برنامه های دیگری مثل SPSS‌ یا msTAT استفاده می‌کنند، اما شروع خوبی برای یادگیری کاربرد نمودارها و رسم آن‌ها به کمک کامپیوتر می‌باشد.

 

ابتدای کشیدن نمودار، وارد کردن داده‌ها در یک کاربرگ (worksheet) از برنامه‌ی Excel می‌باشد. اسم متغیرها یا گروه داده‌ها را نیز بالای ستون مربوط به آن بنویسید. عموما متغیر یا گروه داده را در ستون اول و فراوانی یا مقدار هر گروه یا متغیر را در ستون بعد روبروی آن وارد می‌کنند. برنامه‌ی Excel متغیر یا گروه داده را به صورت اتوماتیک، روی محور افقی و فراوانی یا مقادیر را روی محور عمودی نمایش می‌دهد.

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

 

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

زمانی که داده ها یا متغیرهای گسسته یا دسته بندی شده داریم، نمودار میله‌ای یک نمودار واضح برای نمایش داده‌هاست. برای نمونه تعداد فرزندان خانواده یا تعداد افرادی که به یک سوال پاسخ درست داده‌اند، داده‌های گسسته‌اند. متغیر دما را می‌توان به عنوان نمونه به ۳ دسته‌ی ۳۴-۲۵، ۴۴-۳۵ و ۵۴-۴۵ طبقه بندی کرد. برای تحلیل خیلی از پرسش‌نامه‌ها نیز نمودار میله‌ای می‌تواند مناسب باشد.

برای کشیدن نمودار در Excel داده‌ها را وارد کنید:

 

 

پس از آن ستون فراوانی‌ها را انتخاب کنید.

آنگاه از منوی بالا سربرگ Insert را انتخاب کنید. انواع نمودار را در این جا می‌بینید. برای کشیدن نمودار میله‌ای از الگوی Column یا Bar استفاده کنید.

 

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

 

در وضعی که بیش‌تر از ۱ متغیر وجود داشته باشد نیز می‌توان از نمودار میله‌ای استفاده کرد. برای مثال اگر غلظت یک ماده را در سه دمای مختلف اندازه‌گیری کرده باشیم، برای رسم نمودار میله‌ای داده‌ها را وارد می‌کنیم:

 

متغیرهای مورد نظر را انتخاب کنید. دقت داشته باشید که متغیر دما را انتخاب نکنید زیرا احتیاج نیست و نباید درون نمودار کشیده شود. سپس در قسمت نام‌گزاری محورهای نمودار می‌توانید آن را در نظر بگیرید.

 

دوباره با رفتن به قسمت insert و انتخاب Column یا Bar نمودار میله‌ای مورد نظرتان را کلیک کنید. با انتخاب روی نمودار مورد نظر، نمودار میله‌ای کشیده می‌شود.

 

 

نمودار دایره‌ای در excel:

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

برای کشیدن نمودار در Excel ابتدا داده‌ها را وارد کنید:

 

با رفتن به برگه Insert و کلیک کردن Pie نمودار دایره‌ای مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار دایره ای کشیده می‌شود.

 

نمودار پراکنش:

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

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

 

بعد ستون‌های مورد نظر را انتخاب می‌کنیم.

 

با رفتن به برگه Insert و انتخاب الگوی Scatter نمودار مورد نظرتان را  با کلیک روی آن انتخاب کنید. سپس نمودار پراکنش نمایش داده می‌شود.

 

 

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

 

 

 

 

نمودار خطی:

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

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

این نمودار پرکاربردترین نمودار در Excel است.

برای تغییر مشخصات نمودار می‌توانید روی آن کلیک کنید. در بالای صفحه، قسمت منو، سربرگ‌های Design، Layout و Format نمایش داده می‌شوند. از آن‌ها برای تغییرات گوناگون در نمودارتان استفاده کنید.

 

 

مطالبی در مورد انجام محاسبات ریاضی با Excel

 

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

 

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

Long Lists: =SUM(B4:B13)

Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7)

یا ماوس را روی اولین سلول خالی در انتهای لیست‌تان بیاورید (یا هر سلول دیگر) و بر روی علامت «+» کلیک کنید، سپس روی B4 کلیک کنید، دوباره علامت «+» را بزنید و اینبار B5 را کلیک کنید و به همین صورت تا آخر؛ در نهایت کلید اینتر را فشار دهید. اکسل لیست شما را به شکل =+B4+B5+B6+B7 جمع خواهد زد.

 

۲) با کلیک بر روی کلید وارد کردن تابع

با اینکار که از شاخه (فرمول‌ها) قابل انجام است قادرید  یکی از توابع اکسل را از لیست انتخاب کنید:

=COUNT(B4:B13)

اعداد قرار گرفته در دامنه را می‌شمارد

=COUNTA(B3:B13)

تمامی کراکترهای موجود در دامنه را می‌شمارد
۳) تابعی را از ۱ گروه انتخاب کنید

با اینکار قادرید جستجوی خود را محدودتر کرده و تابع مورد نظرخود را از شاخه‌های اقتصادی، منطقی یا تاریخ/زمان انتخاب کنید، برای نمونه:

=TODAY()

وارد کردن تاریخ روز.
۴) دکمه استفاده‌های اخیر

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

=AVERAGE(B4:B13)

لیست را جمع می‌زند، میانگین را محاسبه می‌کند.
۵) توابع خودکار زیر دکمه جمع‌زدن خودکار در اکسل

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

=MAX(B4:B13)

بیشترین مقدار لیست را نشان می‌دهد،

=MIN(B4:B13)

کمترین مقدار لیست را نشان می‌دهد.

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

نکته : در میان فرمول‌های پایه، دکمه (AutoSum) «جمع‌زن خودکار» بهترین انتخاب است، زیرا با مشخص شدن خودکار دامنه سلول‌ها فقط کافی است دکمه اینتر را بزنید و نتایج را مشاهده کنید.

نکته : سریع‌ترین راه برای محاسبه مجموع یک لیست قرار دادن ماوس در انتهای لیست و فشار دادن کلیدهای ترکیبی ( Alt+) و سپس اینتر است. اکسل لیست را پررنگ کرده و مجموع را محاسبه می‌کند.

۵ فرمول مفید برای کارهای روزمره

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

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

۱) =DAYS

این فرمول برای محاسبه تعداد روزهای موجود بین ۲ تاریخ بکار می‌رود.

مثال: تاریخ اتمام ۱۲ اکتبر ۲۰۱۵ منهای تاریخ شروع ۳۱ مارچ ۲۰۱۵ = ۱۹۵ روز

=DAYS(A30,A29)

 

۲) =NETWORKDAYS

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

مثال: تاریخ شروع ۳۱ مارچ ۲۰۱۵ منهای تاریخ اتمام ۱۲ اکتبر ۲۰۱۵ = ۱۴۰ روز

=NETWORKDAYS(A33,A34)

 

۳)=TRIM

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

مثال: =TRIM به همراه آدرس سلول در داخل پارنتز

=TRIM(A39)

 

۴) =CONCATENATE

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

مثال: =CONCATENATE به همراه (ماه،”space”،روز،”comma space”،سال) بدین شکل موارد ترکیب شده با فاصله و کاما از هم جدا خواهند شد.

فرمول برای وارد کردن تاریخ:

=CONCATENATE(E33,” “,F33,”, “,G33)

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

=CONCATENATE(E37,”-“,F37,”-“,G37)

۵) =DATEVALUE

DATEVALUE تمامی فرمول‌های فوق را در غالب یک تاریخ اکسل در می‌آورد، در نتیجه اگر می‌خواهید از این تاریخ‌ها برای محاسبات بعدی استفاده کنید استفاده از این فرمول بسیار مفید خواهد بود. کار با آن آسان است، کافیست DATEVALUE را از لیست فرمول‌ها انتخاب کنید، بر روی فیلد «Date_Text» کلیک کنید، بر روی سلول مورد نظرتان کلیک کنید، روی OK کلیک کنید و کپی را انجام دهید. خروجی شماره سریال‌های اکسل خواهند بود پس باید به نحو مقابل فرمتی برای لیست‌تان انتخاب نمایید: Format>Format Cells>Number>Date

=DATEVALUE(H33)

۳ روش مفید اضافی برای کار با فرمول‌ها

• روش اول: برای تبدیل متن یا عدد به فرمول‌های اضافی نیاز ندارید. فقط کافیست آن‌ها را کپی کنید و به عنوان نوع‌ خاص (Special>Values) پیست‌شان کنید. چرا با استفاده از فرمول‌های تبدیل نوع خودتان را اذیت کنید؟ شاید با اینکار محتوای سلول‌ها شبیه به شماره تلفن‌ به نظر برسند اما در واقع فرمول هستند که نه می‌شود جا‌به‌جایشان کرد و نه می‌توان تغییرشان داد.
• روش دوم: اگر از روش فوق برای تبدیل نوع تاریخ‌ها استفاده کنید (Paste>Special>Values)، نتیجه متن شده و قابل تبدیل نوع تاریخ واقعی نخواهد بود. تاریخ‌ها به فرمول DATEVALUE نیاز دارند تا به صورت تاریخ‌های واقعی دربیایند.
• روش سوم: فرمول‌ها همیشه با حروف بزرگ نمایش داده می‌شوند، اما اگر آنها را با حروف کوچک تایپ کنید، اکسل به صورت خودکار آنها را به حروف بزرگ در‌می‌آورد. همچنین به یاد داشته باشید که هیچ فاصله‌ای در فرمول‌ها و توابع وجود ندارد. اگر فرمولی کار نکرد مطمئن شوید که فاصله‌ای بین نوشته‌ها وجود نداشته باشد

 

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

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

۱- عدم نمایش تبها و شیتها در قسمت پائینی اکسل

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

• روی گزینه file کلیک کنید و به قسمت option بروید.
• در پنجره باز شده به تب Advanced بروید.
• در قسمت Display option for this workbook تیک گزینه Show Sheets Tab را بزنید .

۲- حل مشکل اعدادی که به صورت متن وارد شده اند

فرض  کنید در ستون A1 الی A12 مجموعه ای از اعداد را وارد کرده اید و در A13 فرمولی می نویسید که A1:A12 را جمع کند و در کمال تعجب میبینید جواب اشتباه است و با کمی دقت متوجه می شوید که در گوشه بالا سمت راست برخی از سلولها رنگ سبزی جلب توجه می کند که می خواهد یادآوری کند که در این سلول داده عددی با قالب متن وارد شده و در نتیجه در حاصلجمع محاسبه نمی شود . راه حل چیست؟

• شما می توانید تک تک سلولهایی که این مشکل را دارند انتخاب کرده و گزینه Convert to number را بزنید که راه خسته کننده با درصد خطای بالایی است.
• راه دوم این است که در یک سلول که مطمئن هستید از نوع عددی است عدد ۱ را وارد کنید و روی سلول کلیک راست کرده و Copy را بزنید و سپس محدوده ای که مشکوک هستید در آن اعداد به صورت متنی ذخیره شده باشند راست کلیک کرده و گزینه Paste Special را بزنید و مانند تصویر زیر گزینه Multiply را انتخاب کرده و ok کنید.

۳- حذف فضای خالی ما بین نوشته ها در  اکسل

بعضا فاصله های ناخواسته ای بین نوشته در اول یا آخر و یا وسط آنها وجود دارد که شما می خواهید آنها را حذف کنید .راه حل:

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

=Trim(a1)

۴- حذف سلولهای خالی موجود در محدوده در اکسل و یا تغییر نام آنها

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

• کلیدهای ترکیبی Ctrl+G را بزنید
• روی گزینه Special کلیک کنید تا پنجره Go To Special باز شود .
• حال گزینه Blank را انتخاب کرده و OK کنید .
• می بینید که سلولهای خالی به حالت انتخاب در آمده است که می توانید آنها را حذف کنید .
• اگر خواستید آنها را تغییر نام دهید در همان حالت انتخاب متن مورد نظر خود را تایپ کرده و کلیدهای ترکیبی Ctrl+Enter را بزنید.

۵- جدا کردن نام و نام خانوادگی که در یک ستون وارد شده و تقسیم به دو ستون

فرض می کنیم ستونی داریم که در آن وارد شده    “نادر بحری تهران” و شما می خواهید این ستون به سه ستون   “نادر” “بحری” “تهران” تبدیل شود.

• ستون مورد نظر خود را انتخاب کرده و کلیدهای ترکیبی Alt+A+E بزیند
• پنجره Convert Text To Column نمایش داده خواهد که مانند فیلم آموزشی عمل کنید

۶- مشاهده داده های تکراری در محدوده

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

• محدوده مورد نظر را انتخاب نمائید و کلیدهای ترکیبی Alt+H+L+H+D را بزنید ( در تب Home روی Conditional Formatting کلیک کنید ) که منوی مربوط به Conditional Formatting باز میشود که باید Duplicate values را انتخاب کرده و نتیجه را ببینید.

۷- جایگزین کردن عدد صفر با سلول خالی

• محدوده مورد نظر را انتخاب نمائید روی گزینه File کلیک کنید
• به Option بروید  و از تب Advanced بروید
• در قسمت Display Option for this worksheet تیک گزینه Show a Zero in cells… را بزنید

۸- پاک کردن فرمت و تغییراتی که در شیت اعمال کرده اید .

• محدوده مورد نظر را انتخاب نمائید و به تب Home بروید
• در قسمت Editing روی Clear کلیک کنید و گزینه Clear Formatting را انتخاب کنید.

نکاتی در مورد ایجاد یک گزارش جدول محور در اکسل

 

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

Microsoft Excel را باز کرده و تب Data را از نوار منو انتخاب کنید.  روی From Other Sources کلیک کرده و گزینه ی From Other Sources را ، انتخاب کنید.

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

 

اگر توانستید به نمونه ی SSAS تعیین شده با موفقیت متصل شوید، در مرحله ی بعد باید قادر به انتخاب دیتابیس باشید و SSAS "Sales" بوده و مکعب Sales را پیدا کنید. مکعب Sales را انتخاب کنید و به مرحله ی بعدی بروید.

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

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

 
پس از انتخاب “PivotTable Report”با گزینه هایی برای انتخاب بعد، ویژگی ها و اندازه گیری ها برای قرار گرفتن در جدول محور، باز خواهد شد. همانطور که در تصویر زیر نمایش داده شده، مقادیر را انتخاب کنید. هدف ما نمایش رده بندی ایجاد شده در بعد Sales Territory  روی ستون های محور، مجموعه ی نام گذاری شده ی Internet Sales Top 25 روی ردیف های محور و اندازه گیری محاسبه ی شده ی Total Sales  در محدوده ی مقادیر می باشد.

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

 

 

 

 

راهکارهای مدیریت و تغییر Ribbon در اکسل

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

در ابتدا که اکسل را باز می کنید، ریبون بر روی تب (tab) اصلی یعنی Home قرار دارد،در این آموزش  اکسل هدف ما یادگیری  چکونگی بستن  ریبون  یا به عبارتی آن را مینیمایز minimize  کنید یا چگونه عناصر دلخواه را به آن اضافه یا کم کنیم به عبارتی آن را کاستومایز کنیم

تب ها در ریبون

تب های  excelبه صورت زیر هستند:

 

File, Home, Insert, Page layout, Formulas, Data, Review و View.

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

 

 

کوچک کردن نوار ریبون در اکسل (Minimize the Ribbon)

شما میتوانید این نوار را کوچک کنید تا فضای بیشتری داشته باشید، برای این کار هر جایی از ریون کلیک راست کنید و روی Minimize the Ribbon کلیک کنید.

البته از کلید های Ctrl+F1 هم می توانید استفاده کنید.

 

 

نتیجه به صورت زیر است:

 

تغییر در نوار ریبون (Customize the Ribbon) در اکسل

در این قسمت شما می آموزید که چکونه ریبون را تغییر دهید، چیزاهایی به آن کم و زیاد کنید و یا اصلا تبی اضافه یا کم کنید.

 کلیک راست کنید و گزینه Customize را انتخاب کنید

 

در این پنجره دو ستون دارید، ستون سمت چپ امکاناتی که اکسل دارد، البته این امکانات دسته بندی شده اند، همانطور که در شکل زیر می بینید الان دسته Popular Commands انتخاب است که ابزار های پرکاربرد را به شما نشان می دهد، میتوانید انرا تغییر دهید تا تمامی ابار ها و کامند ها به شما نشان داده شوند و در کنار این ستون، ستون سمت راست نمایانگر ریبون هست به همراه دسته بندی تب ها، خب میتوانید تعیین کنید کدام امکان در کدام تب قرار بگیرد یا با کلیک بر روی دکمه New Tab میتوانید تبی جدید ایجاد کنید:

 

حال تب جدید ساخته شد میتوانید به آن امکاناتی اضافه کنید:

 

مثلا Quickprint را در سمت چپ انتخاب کرده و new group را در سمت راست انتخاب کنید و با زدن کلید add گزینه QuickPrint به سمت راست در زیر مجموعه new Group اضافه می شود.

البته درون تب ها را میتوان گروه بندی کرد در شکل زیر تب new Tab  یک گروه زیر مجموعه خودش دارد که new group است . نام این ها را نیز میتوان تغییر دارد ، هر کدام را خواستید انتخاب کنید و rename را بزنید ، برای ساخت گروه جدید هم هر تبی را خواستید انتخاب کنید و روی new group کلیک کنید.

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