روش ایجاد و استفاده از PivotTableها ( قسمت دوم )

Pivot کردن داده ها
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
یکی از بهترین ویژگی های PivotTable ها این است که به سرعت می توانند Pivot ( یا سازمان یافته ) شوند تا بتوانید ورک شیت خود را به روش های مختلف امتحان کنید. Pivot کردن داده ها میتواند برای پاسخ به سوال های مختلف به شما کمک کند و می توانید با امتحان کردن روش های مختلف، روندها و الگوهای مختلف داده ها را کشف کنید.
اضافه کردن ستون ها در PivotTable

تا کنون PivotTable ما تنها یک ستون را نشان میدهد. اما برای داشتن ستون های مختلف باید یک ستون را به قسمت Columns اضافه کنید.

1. یک فیلد را از Field List به قسمت Columns اضافه کنید. در این مثال ما از فیلد Month استفاده می کنیم.

 روش ایجاد و استفاده از PivotTable

 

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

روش ایجاد و استفاده از PivotTableها 

 
تغییر یک سطر یا ستون در PivotTable

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

1. فیلدی را که می خواهید حذف کنید از قسمتی که در آن قرار دارد درگ کنید. همچنین می توانید در Field List تیک کادر مقابل آن فیلد را بردارید. در این مثال ما فیلدهای Month و Salesperson را حذف می کنیم.

 روش ایجاد و استفاده از PivotTableها

2. یک فیلد جدید را به قسمت های مد نظرتان اضافه کنید. در این مثال ما Region را در قسمت Rows قرار داده ایم.

روش ایجاد و استفاده از PivotTableها 

3. PivotTable برای نشان دادن داده های جدید تنظیم خواهد شد. در این مثال، مقدار فروش در هرناحیه نشان داده می شود.

روش ایجاد و استفاده از PivotTableها

 
ادامه مطلب...
روش ایجاد و استفاده از PivotTableها ( قسمت اول )
In وبلاگ
استفاده از ( روش ایجاد یک ) PivotTable اکسل در مدیریت و کنترل پروژه
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
1. یک جدول یا تعدادی سلول (شامل عناوین ستون ها) را که میخواهید در PivotTable شما باشند را انتخاب کنید.

 روش ایجاد PivotTable

2. ازتب Insert بر روی فرمان PivotTable کلیک کنید.

 

3. کادر محاوره ای Create PivotTable ظاهر خواهد شد. تنظیمات مد نظرتان را انتخاب کنید و بر روی OK کلیک کنید. در اینجا ما از نام Table1 به عنوان منبع داده هایمان استفاده می کنیم و PivotTable را با کلیک بر روی new worksheet در یک ورک شیت جدید قرار می دهیم .

 

4. یک PivotTable و یک Field List بر روی ورک شیت جدید ظاهر خواهند شد.

 روش ایجاد PivotTable

5. پس از آنکه یک PivotTable را انتخاب کردید باید تصمیم بگیرید که قصد دارید کدام فیلدها را اضافه کنید. هر فیلد (field) عنوان یکی از ستون های منبع داده ها است. در PivotTable Field List، کادری را که میخواهید فیلد آن به PivotTable شما اضافه شود را تیک بزنید. در این مثال ما می خواهیم مقدار کل فروش هر فروشنده را پیدا کنیم در نتیجه کادرهای مربوط به فیلدهای Salesperson و Order Amount را تیک بزنید.

 

6. این فیلدها به یکی از چهار قسمت زیر اضافه خواهند شد. در این مثال  می خواهیم  فروشنده (Salesperson) به قسمت سطرها اضافه کنیم و مقدار سفارش را به قسمت مقادیر اضافه کنیم . در نتیجه Salesperson را به Rows و Order Amount را به Values می کشیم و رها (درگ اند دراپ) می کنیم.

روش ایجاد PivotTable

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

 روش ایجاد PivotTable

 

*دقیقا مانند صفحه گسترده های معمولی می توانید با استفاده از فرمان Sort & Filter بر روی تب Home داده هارا دسته بندی کنید. همچنین می توانید ازهرنوع قالب عددی استفاده کنید. مثلا می توانید فرمت (قالب) اعداد را به Currency تبدیل کنید. با این حال در نظر داشته باشید که بعضی از انواع قالب بندی ها ممکن است در زمان تغییر PivotTable ها ناپدید شوند.

 روش ایجاد PivotTable

 

* اگر در ورک شیت منبع داده ها را تغییر دهید PivotTable بصورت خودکار آپدیت نمی شود و برای آپدیت کردن آن PivotTable را انتخاب کنید و سپس به Analyze Refresh بروید.

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

 
ادامه مطلب...
معرفی PivotTable ( قسمت دوم )
In وبلاگ

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

مثال زیر را در نظر بگیرید.
ما می خواهیم جواب این سوال را پیدا کنیم: مقدار جنس خریداری شده ( فروخته شده ) از هر یک از فروشندگان چقدر است ؟

پاسخ به این سوال ممکن است مشکل و زمانبر باشد زیرا نام هر فروشنده در بیش از یک سطر وجود دارد و ما باید تک تک آن ها را با هم  جمع کنیم. برای پیدا کردن جواب سوال می توانیم از فرمان Subtotal استفاده کنیم اما باز هم تعداد داده های زیادی داریم که باید با آن ها کار کنیم.
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
 معرفی PivotTable

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

 معرفی PivotTable

 

پس از ایجاد PivotTable می توانید از آن با تغییرآرایش داده ها برای پاسخ به سوالات مختلف استفاده کنید. مثلا اگر سوال دیگری هم داشته باشیم مثل مقدار جنس خریداری شده ( فروخته شده ) در هر ماه چقدر است؟ آنگاه میتوانیم PivotTable را بصورت زیر تغییر دهیم:

معرفی PivotTable

 
ادامه مطلب...
معرفی PivotTable ( قسمت اول )
In وبلاگ

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

استفاده از PivotTable برای جواب دادن به سؤالات

مثال زیر را در نظر بگیرید.
ما می خواهیم جواب این سوال را پیدا کنیم: مقدار جنس خریداری شده ( فروخته شده ) از هر یک از فروشندگان چقدر است ؟

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

 معرفی PivotTable

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

 معرفی PivotTable

 

پس از ایجاد PivotTable می توانید از آن با تغییرآرایش داده ها برای پاسخ به سوالات مختلف استفاده کنید. مثلا اگر سوال دیگری هم داشته باشیم مثل مقدار جنس خریداری شده ( فروخته شده ) در هر ماه چقدر است؟ آنگاه میتوانیم PivotTable را بصورت زیر تغییر دهیم:

معرفی PivotTable

 برای رفتن به قسمت دوم اینجا کلیک کنید.
ادامه مطلب...
تغییر رنگ ردیف در جداول اکسل با Conditional Formatting
In وبلاگ

شاید برای شما نیز این مورد پیش آمده باشد که بخواهید در اکسل اطلاعات لیست شده را پس از مدتی بازبینی و تصحیح نموده و پس از این کار ردیف مورد نظر در اکسل را از مابقی ردیف‌ها با استفاده از رنگ‌ها متمایز کنید. البته این کار با استفاده از انتخاب سل‌های مورد نظر و استفاده از Fill Color در Home > Font در اکسل 2007 به راحتی انجام می‌شود. ولی نکته اینجاست که چنانچه سل‌های مورد نظر زیاد باشد و نیاز به وقت زیاد برای این کار باشد تکلیف چیست؟ راهکار ساده‌ای در اکسل با استفاده از قابلیت Conditional Formattingوجود دارد که در ذیل با طرح یک مثال به آن اشاره شده است.

1. فرض کنید جدولی در اکسل به صورت ذیل دارید.

 

2. در یک سمت اطلاعات جدول ستونی را برای استفاده از قابلیت Conditional Formatting به نام” وضعیت” ایجاد کنید.

3. محدوده اطلاعات جدول ( به استثنای عناوین ستون‌ها) را انتخاب کنید.

4. ابزار Conditional Formatting را از مسیر Home > Styles باز کنید.

 09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

5. گزینه … New Rule  را انتخاب کنید.

 

6. در قسمت Select a Rule Type گزینه Use a formula to determine which cells to format را انتخاب کرده و در بخش پائین آن در قسمت Edit the Rule Description در باکس Format values where this formula is true عبارت ذیل را وارد نمائید. 

“B3=”F$= 

 

7. سپس بر روی گزینه Format کلیک کرده و در پنجره Format Cell تب Fill را انتخاب کرده و رنگ مورد نظر را برای حالت اول انتخاب می‌کنیم ( به طور مثال رنگ آبی را انتخاب می‌کنیم)

 

8. سپس برای تغییر رنگ در حالت دوم همان کارهای مرحله 6 را انجام داده و فقط به جای حرف F حرف D را استفاده می‌کنیم و در آخر مجدداً‌ مرحله 7 را این بار برای حالت D انجام دهید. ( رنگ مورد نظر برای حالت D ،‌ را زرد انتخاب می‌کنیم)

“B3=”D$=

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

 

نکات مهم :

• در مثال فوق فرض کردیم F مخفف Final و D مخفف Draft می‌باشد.

• حرف B نشانگر ستونی است که مبنای تغییر رنگ ردیف است.

• عدد 3 نشانگر اولین ردیفی است که باید تغییر رنگ ردیف از آنجا شروع شود.

• حرف F و D نشانگر وضعیت آیتم مورد نظر است. ( استفاده از عناوین برای این قسمت کاملاً‌ سلیقه‌ای است چنانچه در ستون وضعیت از عناوین خاصی استفاده می‌کنید بهتر است برای اطمینان از صحت انجام کار عنوان مورد نظر را کپی کرده و به جای حرف F و D استفاده کنید)

 

 
ادامه مطلب...
تحلیل پارتو با اکسل (Pareto Analysis with Excel)
In وبلاگ

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

تحلیل پارتو

حال واریانس و واریانس وزنی فعالیتها را به طریق زیر محاسبه می نماییم.

 

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

 

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

 

تا اینجا، کار تحلیل پارتوی ما به پایان می‌رسد. همانگونه که ملاحظه می کنید فعالیتهای3، 4، 10 و 1 حدود 81 درصد تاخیرات پروژه را شامل می شوند. شما به عنوان مدیر پروژه اگر بخواهید پروژه را از حالت بحران خارج کنید باید به سرعت به سراغ این 4 فعالیت، مخصوصا سه فعالیت اول بروید تا 80 درصد تاخیر را جبران نمایید. حال می خواهم با 5 گام بسیار ساده نمودار پارتو را با استفاده از اکسل رسم نمایم. برای این منظور مراحل زیر را با من دنبال کنید.

1. یک نمودار ستونی از درصد وزنی تاخیرات رسم کنید.

 

 

 

2. درصد تجمعی را به صورت نمودار خطی به آن اضافه کنید. یک روش خیلی سریع برای انجام این کار را به شما آموزش می دهم. کل ستون درصد تجمعی (cumulative shares) را انتخاب کنید. از روی آن کپی کنید. بر روی نمودار کلیک راست کنید و آن را Past نمایید. نمودار جدید به چارت شما اضافه شد! به همین راحتی

 09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

حالا کافیست بر روی نمودار جدید کلیک راست کنید و در Change series chart type آن را به Line تبدیل نمایید.

3. طبق روشی که در پست ترکیب نمودارها- اضافه کردن یک محور عمودی اشاره نموده بودم باید نمودار دوم را به محور جدید انتقال دهیم. بر روی نمودار دوم کلیک راست می‌کنیم و Format Data Series را کلیک می نماییم. در Series Option، Secondary Axis را انتخاب می کنیم.


 

4. برای بهتر شدن چارت می توانید یک نمودار برای 80% اضافه کنید تا تقاطع آن مشخص کننده آخرین ستونی باشد که بیشترین تاخیرات را شامل می‌شود. بدین منظور در کنار ستون جمع تجمعی یک ستون دیگر با مقدار 0.8 ایجاد کنید. آنرا کپی نموده و روی نمودار Past نمایید.

 

5. اگر شما هم کمی ذوق هنری دارید بهتر است که دستی به سر نمودار بکشیم. هرچقدر زیباتر باشد نظر مدیران را بهتر جلب می کند.

 

فکر می کنم اینطوری زیباتره. نظر شما چیه؟

 

تحلیل پارتو (Pareto Analysis)

 

 
ادامه مطلب...
تابع SUBTOTAL
In وبلاگ

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

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

تابع SUBTOTAL

برای اینکار به راحتی از از تابع (SUM(D2:D15=  و (SUM(C2:C15= استفاده می کنیم. نتیجه مشخص است!

 

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

 

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

راه حل کار ساده است!

اصلا نگران نباشید. راه حل کار بسیار ساده است. برای انجام این کار در Excel تابع SUBTOTAL قرار داده شده است. تابع SUBTOTAL چند خاصیت مهم دارد.

• در هنگام فیلتر کردن قسمتهای فیلتر شده را محاسبه نمی کند

• در هنگام Hide کردن قسمتهای Hide شده را محاسبه نمی کند

• در هنگامی که از چند تابع SUBTOTAL در یک ستون استفاده کرده باشید، سایر SUBTOTALها را در محاسبه نهایی محاسبه نمی کند.

ساختار این تابع به شکل زیر است.
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
SUBTOTAL (function_num, ref1, ref2=

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

برای داشتن جمع کل داده هایی که فیلتر می شوند تابع  (SUM(D2:D15= و (SUM(C2:C15= را پاک کنید و بجای آن از (SUBTOTAL(109;D2:D15= و (SUBTOTAL(109;C2:C15= استفاده کنید.

همان نتیجه ای که می خواستیم حاصل شد!  

بجای کد 109 -که برای جمع است- کدهای دیگر را نیز امتحان کنید. مثلا کد 101 که برای میانگین است.

موفق باشید.

 
ادامه مطلب...
( 3-6 ) توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!
In وبلاگ

معمولا وقتی روی چندین شیتِ داده در اکسل کار می کنید، ممکن است این سوال برای شما پیش بیاید که یک آیتم در یک لیست چند بار در لیست دیگر تکرار شده است. به عبارت دیگر دو لیست دارید که میخواهید بدانید تعداد آیتم های لیست A در لیست B و برعکس چقدر می باشد.
3. تابع COUNTIF
این کار با استفاده از یک تابع قدیمی و طلایی (!!!) به نام COUNTIF  قابل انجام است. تابع COUNTIF  تعداد داده هایی را که دارای ویژگی های خاصی هستند، شمارش می کند. به مثال زیر توجه کنید:
مدیر پروژه از شما خواسته است  که تعداد مهندسین، تکنسین ها و سرکارگرهایی را که در پروژه‌های شرکت مشغول به کار می باشند به وی اعلام کنید. به شما لیستی به شرح زیر ارجاع می گردد که باید تعداد هر یک از پست‌های سازمانی را شمرده و نتیجه را اعلام کنید.

در این مثال من با استفاده از تابع COUNTIF  این کار را انجام خواهم داد. به عبارت دیگر لیست 1 حاوی مشخصات پستهای سازمانی می باشد و لیست 2 مشخصات کارکنان. فرمول COUNTIF   بدین شکل عمل می کند: =COUNTIF(range, criteria)
 
بسیار ساده بود، نه؟ در قسمت range، ستون پست‌های سازمانی در لیست 2 و در قسمت criteria، سلول حاوی  نام “مهندس” را انتخاب کردیم. البته فراموش نکنید که قسمت پستِ سازمانی به صورت مطلق یا absolute انتخاب شده است. به عبارت دیگر اگر دقت کنید در داخل آدرس ها از علامت $ استفاده شده که باعث می شود در صورتی که این فرمول در ستون بعد کپی شود، این آدرس ثابت بماند اما آدرس کلمه “مهندس” اینگونه نیست. یعنی اگر فرمول در سلول بعدی کپی شود، این بار نام “تکنسین” برگزیده می شود.
خب، حالا ممکن است مدیر شما از شما درخواست دیگری داشته باشد… L
“لطفا تعداد مهندسین را در پروژه 1 فورا به من اعلام کنید!!! > :(

( 1-6 ) توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!

( 2-6 ) توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!

 
ادامه مطلب...
( 2-6 )توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!
In وبلاگ
 توابع شرطی در اکسل،6 ترفندی که شما نمی دانید!


 


در پست قبل در ارتباط با تابع SUMIF صحبت کردیم و نهایتا به مشکلی برخوردیم که ظاهراً تابع SUMIF از عهده آن بر نمی آمد. مساله اصلی در اینجا بود که مدیر پروژه از شما خواسته بود در فاصله بین 30م جولای تا 30م سپتامبر، هزینه های پروژه را اعلام کنید. اینجا کمی مساله پیچیده می شود، زیرا تابع SUMIF فقط قادر به بررسی یک محدود است. برای حل این مشکل باید باز به سراغ تابع SUM رفت.
2. استفاده از تابع SUM بجای SUMIF
برای انجام این کار باید از یک فرمول Array (Array Formula) استفاده کنیم (از این نوع فرمول با عنوان CSE Formula هم نام برده می شود. اگر فرصتی پیش بیاید حتما در مورد این نوع فرمول، به طور کامل برایتان خواهم گفت). تنها نکته ای که فعلا باید در مورد این نوع فرمول بدانید این است که پس از تایپ آن باید حتما از کلیدهای Ctrl+Shift+Enter بجای Enter استفاده کنید.
خب بر می گردیم به مساله قبل. طبق شکل زیر می خواهیم فاصله بین 30م جولای تا 30م سپتامبر را محاسبه کنیم. فرمول زیر را روبروی بازه زمانی مورد نظر بنویسید.
=SUM((B2:B10<=F4)*(B2:B10>=E4)*C2:C10)
سپس دکمه های Ctrl+Shift+Enter را بزنید.
SUM
مقدار 702 که مقدار هزینه کرد بین تاریخ مد نظر است ظاهر شد. به همین سادگی!
اگر دقیقا به قسمت فرمول (Formula Bar) نگاه کنید می بینید که با زدن کلیدهای Ctrl+Shift+Enter دو علامت  {} در ابتدا و انتهای فرمول ظاهر شده که نشان دهنده Array Formula می باشد. روی فرمول فکر کنید تا بتوانید منطق آن را بیشتر درک کنید. اگر نیاز به تحقیق بیشتر در این مورد داشتید در گوکل با موضوع ” SUMIF Using Multiple Criteria in Microsoft Excel” تحقیق کنید. نتایج بسیار جالب و مثال های جالبتری پیدا خواهید کرد.
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
( 1-6 )توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!

( 3-6 )توابع شرطی در اکسل، 6 ترفندی که شما نمی دانید!!
ادامه مطلب...
(1-6)توابع شرطی در اکسل، 6ترفندی که شما نمی دانید!!
In وبلاگ

  اکسل توابع مختلفی دارد. اما اکثر ما (از جمله خود من) از بیشتر از 5-6 تابع، برای انجام کارهای خوداستفاده نمی کنیم.و در این میان فرمول های شرطی (IF Formulas) بیشترین سهم را دارند. با این اوصاف دانستن چند نکته جالب در مورد کارهایی که می توانید با توابع IF اکسل انجام دهید، خالی از لطف نیست. من هر هفته یکی از این توابع را به شما معرفی خواهم کرد.


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


SUMIF
بازه های زمانی طبق جدول زیر از شما خواسته شده است.
 SUMIF
راه حل بسیار ساده و سریع آن استفاده از تابع SUMIF() است. تابع SUMIF() جمع مجموعه ای از اعداد را با محدوده ی مشخص شده به شما می دهد. در مثال ما محدوده مشخص شده یک بازه زمانی است که در آن هزینه ها باید جمع شوند.
 SUMIF
فکر می کنم همه چیز در شکل بالا واضح است و نیاز به توضیح بیشتر نیست. از تابع SUMIF می خواهم جمع تمامی هزینه کردهایی که قبل از تاریخ دهم جولای انجام شده را به من بدهد. در قسمت range مجموعه ای که باید این بررسی در آن صورت گیرد را معرفی می کنیم. در قسمت Criteria باید محدوده را مشخص کنیم. مثلا زمانهای کوچکتر از دهم جولای “<10-jul-10” .

SUMIF
و نهایتا در قسمت sum range محدوده ای را که جمع باید در آن انجام شود معرفی می کنیم.
برای محدوده بعدی یعنی از اول ژانویه 2012 تا انتهای پروژه نیز خیلی ساده است.
(SUMIF(B2:B10;”>01-Jan-12”;C2:C10= یا (SUMIF(B2:B10;”>”&E5;C2:C10=

SUMIF
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

فقط یک قسمت دیگر باقی مانده. فاصله بین 30 ام جولای تا 30 ام سپتامبر 2011. به نظر شما برای این محدوده چکار باید کرد؟ در تابع SUMIF امکان مشخص کردن چند محدوده (Multiple Criteria) وجود ندارد. پس خوب است که به فکر راه حل بهتری برای آن باشیم. قبل از خواندن پست بعدی روی آن فکر کنید.

انجام پروژه حسابداری

• انجام پروژه حسابداری مالی

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

• انجام پروژه حسابداری صنعتی
• انجام پروژه حسابداری دولتی
• انجام پروژه حسابداری مدیریت
• انجام پروژه حسابداری بازرگانی
• انجام پروژه با استفاده از نرم افزارهای حسابدار
• 09367292276
  [e-mail]
  azsoft.ir
  [e-mail]
  azsoft.ir
  09367292276
  [e-mail]
  azsoft.ir
  09367292276
  [e-mail]
• 
  
• ی
• انجام پروژه های مالی و حسابداری
• تجزیه و تحلیل صورتهای مالی شرکتهای پذیرفته شده در بورس
• پروژه های مدیریت مالی
• انجام انواع تحقیقات دانشجویی در حوزه حسابداری و مدیریت
• تحلیل آماری در نرم افزار Spss – Eviews – Smartpls
• انجام پروژه های دانشجویی در محیط Excel و
• 09367292276
  [e-mail]
  azsoft.ir
  [e-mail]
  azsoft.ir
  09367292276
  [e-mail]
  azsoft.ir
  09367292276
  [e-mail]
• 
  

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

کسانی که پروژه های دانشجویی و تجاری با نرم افزار های آفیس داشتید تیم ما در خدمت شماست.
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

• جمع آوری و دسته بندی دیتا (قیمت ها و حساب های مشتری و ...) در Word, Excel
• تجزیه و تحلیل آماری (اطلاعات سایت ها و فروشگاه ها و ... ) در Excel
• ساخت فایل های آموزشی و ... در PowerPoint, Word
• تراکت و کارت ویزیت و ... در Publisher
09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]
تمامی خدمات ما به صورت ریموت (دورکاری) از طریق ایمیل و تلگرام و ... می باشد.
در ضمن در صورت همکاری مداوم، دستمزد رو فقط به صورت هفتگی دریافت می کنیم.

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

با این نرم افزار می توانیم انواع محاسبات از ساده تا پیچیده را انجام دهیم و انواع نمودارها و منحنی ها را رسم کنیم. این نرم افزار با صفحه گسترده ها سروکار دارد که در زبان انگلیسی به آنها (Spread Sheet) می گویند. نیاز به این داریم که در ابتدا بعضی از تعاریف و معانی را بدانیم:

1- Sheet: اصولاً هر صفحه که بصورت یک کلیشه تعریف و طراحی شده باشد، که بتوانیم در سلولهایی از آن داده هایی را وارد کنیم به آن     Sheet می گویند.

2- Spread Sheet: شیت های بسیار بزرگی هستند که اگر در مقابل آنها قرار گیریم، در آن واحد فقط می توانیم بخشی از آن را ببینیم و برای دسترسی به نقاط دیگر باید آن را پیمایش کنیم.

3- Work Sheet  (کاربرگ):

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

تعداد سلولهای هر کاربرگ:

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

256 × 65536 = 2⁸ × 2¹⁶ = 2²⁴ = 2⁴ × 2²⁰ = 16 × 1048576 = 16777216

یعنی بیش از 16 میلیون سلول.

4- Work Book (کارپوشه):

معمولاً تعدادی کاربرگ را با هم در یک مجموعه بصورت ورقهایی از کتاب نگهداری می کنیم که به آن کارپوشه می گویند. هر کارپوشه می تواند 1 الی 256 کابرگ داشته باشد. اصولاً هر فایلی که با Excel می سازیم یک کارپوشه است که در آن 1 الی 256 کابرگ می تواند وجود داشته باشد.

تعداد سلولهای هر کارپوشه:

256 × 16777216 = 4096000000

 یعنی بیش از 4 میلیلرد سلول

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

راههای فعال کردن Excel :

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

روش دوم: اگر نام این برنامه در منوی استارت باشد بر روی آن کلیک می کنیم تا باز شود. اگر برنامه در منوی استارت نباشد می توانیم آن را پیدا کنیم، بر روی آن کلیک راست کنیم و بر روی فرمان Pin to start menu    کلیک  می کنیم.

روش سوم: از مسیر All program  و Microsoft Office   نشانگر موس را بر روی برنامه می بریم و کلیک می کنیم.

روش چهارم: باید بدانیم Excel فایل های خود را بصورت پیش فرض در My Document  می سازد. لوگوی این فایل ها X است. اگر بر روی یکی از این فایل ها دابل کلیک کنیم، اولاً برنامه Excel فعال می شود. ثانیاً: آن فایل نیز باز خواهد شد.

آشنایی با محیط Excel :

این محیط شباهت هایی با محیط Word دارد، یعنی نوار عنوان داریم، نوار منو داریم، از گزینه های منوها برای انجام کارها استفاده می کنیم. نوارهای Standard وFormat داریم که از دکمه های آنها بعنوان ابزار برای انجام کارها استفاده می کنیم. نوارهای ابزارهای دیگری نیز داریم که بر حسب ضرورت می توانیم آنها را فعال یا غیر فعال نمائیم. در اینجا مشابه Word، Task Pane داریم. اگر فایل هایی را باز کنیم، اسامی فایل ها در اینجا قرار می گیرد که با یک کلیک می توانیم هر کدام را در اختیار بگیریم. فعال یا غیر فعال کردن آنها و همچنین کم یا زیاد کردن دکمه های نوارها، مشابه محیط Word است.

تفاوت های دو محیط عبارتند از:

الف- یک نوار مخصوص فرمول بنام Formula Bar (fx)  داریم که از آن برای وارد کردن فرمول در سلولها و همچنین اصلاح فرمولها استفاده می کنیم که در Word چنین چیزی را نداریم.  

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

ج- در پایین کاربرگ جاری اسامی کاربرگها را می بینیم که این اسامی عبارتند از:

- Sheet 1

- Sheet 2

- Sheet 309367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

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

نکته: اگر نوار فرمول غیر فعال باشد، می توانیم وارد منوی View شویم و آن را فعال کنیم.

برچسب های ستونها:

هر ستون دارای یک برچسب منحصر به فرد است. برچسبها عبارتند از:

A,B,C,D,…,AA,AB,AC,…,BA,BB,BC,…,IV  

آخرین برچسب (برچسب 256) IV می باشد.

ردیفها هر کدام شماره مستقل دارند و عبارتند از 1 الی 65536 . هر سلول یک آدرس منحصر به فرد دارد که از ترکیب برچسب ستون و شماره ردیف ایجاد می شود. (اولین ستون A1 و آخرین ستون IV65536 می باشد.)

نکته: آدرس سلول جاری را در بالای ستون A می بینیم. بالعکس اگر در آنجا آدرس را تایپ کنیم مستقیماً به آنجا خواهیم رفت.

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

ذخیره کردن کارپوشه:

پس از وارد کردن داده ها و فرمولهای مورد نظر در سلول های کاربرگها می توانیم کل کاربرگها را به عنوان یک کارپوشه بصورت یک فایل ذخیره کنیم.برای این منظور وارد منوی فایل می شویم و از فرمان Save As استفاده می نمائیم. پنجره ای ظاهر می شود. سیستم پیشنهاد می کند آن را در پوشه My Document ذخیره نمائیم. نامی را انتخاب کرده و بر روی گزینه Save کلیک می نمائیم. پسوندهای این فایل ها XLS می باشد. بدیهی است که می توان از دکمه Close استفاده کرده و کارپوشه را ببندیم.

برای باز کردن کارپوشه وارد منوی فایل می شویم و از فرمان Open استفاده می کنیم. ما را به آدرس My Document هدایت می کند. از آنجا می توانیم فایل مورد نظر را انتخاب کنیم و بر روی دکمه Open کلیک نمائیم. البته با دابل کلیک نیز می توان آن را باز کرد.

نکته: برای تغییر دادن محتوای یک سلول، می توانیم بر روی آن دابِل کلیک کنیم و محتوای آن را تغییر دهیم.

اگر بخواهیم آخرین وضعیت ذخیره شود، باید از فرمان Save استفاده نمائیم. (از فرمان Save در منوی فایل یا از دکمه Save در نوار استاندارد یا با استفاده از Ctrl + S).

اگر بخواهیم کارپوشه جدید باز کنیم، می بایست از New استفاده کنیم. (اولین گزینه از نوار استاندارد یا فرمان New از منوی فایل یا Ctrl + N).

اگر بخواهیم جهت کاربرگ عوض شود از دکمه Sheet Right to Left استفاده می کنیم. با استفاده از دکمه Zoom می توان بزرگنمایی کاربرگ جاری را تغییر داد.

انجام محاسبات ساده با اکسِل:

اگر قبلاً در بعضی سلولها اعدادی را تایپ کرده باشیم، می توانیم اعمال ریاضی ساده از قبیل جمع، تفریق، ضرب، تقسیم و توان را انجام دهیم. برای این منظور می توانیم در سلولهای جدیدی فرمول تایپ کنیم. برای این کار باید با یک مساوی شروع کنیم و از عملگرهای بعلاوه +، منها -، ستاره * (برای عمل ضرب)، اِسلَش / (برای عمل تقسیم)، کَرِت ^ (برای عمل توان) استفاده نمائیم.

فرمولها را می توان بصورت طولانی نوشت؛

مثال:

 A1+A2+A3+A4+…+A100=

تذکر: اگر بخواهیم جمع تعدادی عدد در سلولهای یک ستون یا در سلولهای یک ردیف را پیدا کنیم، می توانیم از ابزار سیگما ( ) واقع در نوار استاندارد استفاده کنیم.

مساله:

طول و عرض تعدادی مستطیل را داریم، می خواهیم آنها را در یک جدول وارد کنیم. مساحت و محیط مستطیل ها را محاسبه نمائید؟

محاسبه مساحت و محیط مستطیل ها
طول	عرض	مساحت	محیط	رابطه مساحت = طول × عرض
54	32	1728	172
97	75	7275	344
75	53	3975	256
112	86	9632	396	رابطه محیط = 2 × (طول+ عرض)
230	190	43700	840
320	275	88000	1190
430	395	169850	1650

اگر بخواهیم چند سلول همجوار را با هم ادغام کنیم آنها را انتخاب کرده و در نوار Format بر روی دکمه Merge and center کلیک می کنیم. چنانچه بخواهیم وضعیت عادی شود، بار دیگر محل ادغام شده را انتخاب کرده و بر روی آن (Merge and center) کلیک می کنیم.

Auto Fill: (به معنی پُرکردن خودکار است.)

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

عبارت محاسباتی:

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

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

در اِکسِل هر عبارت محاسباتی با تقلید از عبارتهای محاسباتی ریاضی از عناصر زیر تشکیل می شود:

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

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

1- قرینه سازی -                2- به توان رساندن  ^ 

(3- ضرب و تقسیم  /  و  *   4- جمع و تفریق -  و + ) چهار عمل اصلی در یک سطح قرار دارند.

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

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

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

مساله:09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

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

استفاده از AutoFormat برای زیباسازی جدولهای محاسباتی:

پس از ایجاد جدولهای محاسباتی و تکمیل آن، کل جدول را انتخاب می کنیم و در نوار Format بر روی ابزار AutoFormat کلیک می کنیم. چنانچه این ابزار وجود نداشت، وارد منوی Format شده و از فرمان AutoFormat استفاده می کنیم. پنجره ای ظاهر می شود که جدول هایی با ظواهر مختلف در آن وجود دارد. یک مورد را بَر می گزینیم و بر روی Ok کلیک می کنیم.

مساله:

اگر بخواهیم نمره کلی دانشجویان کلاس را بر اساس زیر محاسبه نمائیم؛

نمره اول 20%، نمره دوم 30% و نمره سوم 50% موثر باشند، مساله قبل را حل نمائید.

البته در محاسبات زیر می توان به جای 20% عدد 2/0 و به همین ترتیب 3/0 و 5/0 را به جای 30% و 50% قرار داد.

توابع یا Functions:

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

تابع SUM:

این تابع می تواند 1 الی 30 پارامتر بپذیرد و حاصل جمع سلول های موجود در پارامترهایش را در اختیار ما قرار دهد.      

                          (پارامتر 30;…; پارامتر2; پارامتر1) =SUM

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

;   یا    ,

اگر یک بار با یک تابع SUM تست کنیم که جداکننده ویرگول(,) است یا سیمی کالوم(;)، همیشه در تابع ها برای جداکننده از آن نوع جداکننده باید استفاده نمود.

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

مساله:

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

فرمول محاسبه مجموع هزینه ها به صورت ذیل نوشته می شود که پس از نوشتن آن و فشردن کلید Enter عدد 1707000 در سلول مربوطه (مانند جدول بالا) نمایش داده خواهد شد.

تابع Average:

مشابه تابع قبل در این تابع هم می توانیم حداکثر تا 30 پارامتر معرفی کنیم:

                          (پارامتر 30;…; پارامتر2; پارامتر1) =Average

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

مساله:

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

تابع Count:

                          (پارامتر 30;…; پارامتر2; پارامتر1) =Count

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

تابع Count blank:

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

تابع Counta:

                          (پارامتر 30;…; پارامتر2; پارامتر1) =Counta

این تابع تعداد سلول های غیرِخالی را می شمارد و به ما می دهد.

مساله:

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

تابع Max:

                          (پارامتر 30;…; پارامتر2; پارامتر1) =Max

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

تابع Min:

                          (پارامتر 30;…; پارامتر2; پارامتر1) =Min

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

مساله:

جزئیات هزینه های روزانه را برای یک هفته در اختیار داریم. محاسبه کنید که در هر روز جمعاً چقدر هزینه کرده ایم و ضمناً در طول هفته بیشترین هزینه های روزانه و کمترین هزینه های روزانه چه مبلغ بوده است؟

تابع های شرطی (Conditional Functions):

بعضی از تابع ها می توانند شرط بپذیرند و نتایج مشروط بدست آورند. یعنی بعضی از پارامترهای آنها می تواند از نوع شرطی باشد. اصولاً شرط ها دو نوع هستند:

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

شرط ساده (Simple Condition):

اگر عبارتها را با عملگرهای شرطی ترکیب کنیم، شرط بوجود می آید. حاصل هر شرط یا درست (True) است یا نادرست (False).

عملگرهای شرطی (مقایسه ای):

= برابر بودن

< بزرگتر بودن

> کوچکتر بودن

= < بزرگتر یا مساوی بودن

= > کوچکتر یا مساوی بودن

<> نابرابر بودن

مثال هایی از شرط های ساده:

A1+B1=A2+B2

A1>1000

A1+A2+A3+A4>1000

A1>=1000

A1+B1+C1+D1>=7500000

A1+B1<>10000000

شرط مرکب:

اگر شرط های ساده را با عملگرهای منطقی (Logical Operators) یعنی And، Or، Not ترکیب کنیم، شرط های مرکب ایجاد خواهند شد. حاصل این شرط ها نیز یکی از دو حالت درست (True) یا نادرست (False) خواهد بود.

تابع شرطی IF:

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

(هر نوع مقدار یا عبارت; هر نوع مقدار یا عبارت; شرط) =IF

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

چند مثال:

=IF(A1>1000;5;7)

اگر A1 بزرگتر از 1000 بود نتیجه 5 و در غیر این صورت 7 خواهد بود.

("ضعیف";"عالی"IF(A1>1000; =

اگر A1 بزرگتر از 1000 بود نتیجه عالی و در غیر این صورت ضعیف خواهد بود.

سوال:

فرض می کنیم در سلول B1 نمره یک دانشجو قرار دارد. می خواهیم در سلول C1 فرمولی قرار دهیم که نمره را ارزیابی کند، چنانچه نمره 12 یا بزرگتر از آن بود بنویسد قبول و گرنه بنویسد مردود. فرمول را بنویسید.

("مردود";"قبول"IF(B1>=12; =

مساله:

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

ضمناً می خواهیم در مقابل اشخاصی که نمرات بالاتر از 15 آورده اند، کلمه ممتاز نوشته شود و در مقابل بقیه چیزی نوشته نشود.

تابع Countif:

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

(شرط; محدوده)Countif =

سلولهایی از محدوده را شمارش می کند که شرط در مورد آنها صدق می کند.

مساله:

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

تابع Sumif:

)شرط; محدوده1) =Sumif(

)محدوده برای جمع زدن;شرط; محدوده2) =Sumif(

این تابع به دو صورت (دو شکل) بکار می رود:

1- نوع اول را هنگامی استفاده می کنیم که سلول های محدوده، اعداد باشند. در این صورت مجموع سلول هایی را پیدا می کند که شرط برای آنها برقرار است. به عنوان مثال، اگر در محدوده A1 الی A100 اعدادی قرار داده باشیم و بخواهیم مجموع اعدادی را پیدا کنیم که همگیبزرگتر از 1000 هستند، بصورت زیر عمل می کنیم:

=Sumif(A1:A100;">1000")

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

سوال: در مساله قبل جمع مبلغ پرتقال، گلابی و گیلاس را بدست آورید.

مساله:

لیستی از نام و نام خانوادگی و جنسیت و حقوق کارکنان یک شرکت کوچک را در یک کاربَرگ ایجاد کرده و محاسباتزیر را انجام دهید:

الف- جمع حقوق آقایان   ب- جمع حقوق خانم ها   ج- تعداد آقایان   د- تعداد خانم ها

ه- میانگین حقوق آقایان   و- میانگین حقوق خانم ها

اضافه کردن کاربَرگ جدید:

بر روی نام یکی از کاربرگ ها، کلیک راست می کنیم. منوی فوری ظاهر می شود. بر روی فرمان Insert کلیک می نمائیم. پنجره ای ظاهر می شود، بر روی Ok کلیک می کنیم. کاربرگ جدید قبل از کاربرگی که بر روی آن کلیک راست کرده بودیم، ایجاد می شود.

جابِجا کردن اسامی کاربرگ ها:

می توان نام هر کاربرگ را انتخاب کرد و آن را با موس Drag کرده و به جای دیگر برد.

تعویض نام کاربرگ ها:

بر روی نام کاربرگ کلیک راست نموده و از منوی فوری ظاهر شده، از فرمان Rename استفاده می نمائیم.

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

برروی نام کاربرگ کلیک راست نموده و از منوی فوری ظاهر شده، از فرمان Tab Color استفاده می نمائیم. یک رنگ را انتخاب نموده و بر روی OK کلیک می کنیم. اگر بخواهیم رنگ را از آن برداریم باید از No Color استفاده نمائیم.

حذف کردن کاربرگ:

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

کُپی کردن یا انتقال دادن یک کاربرگ از یک کارپوشه به کارپوشه دیگر:

باید ابتدا کارپوشه ها باز باشند. حالا می توانیم از کارپوشه ی جاری بر روی یکی از کاربرگ ها که می خواهیم آن را به کارپوشه ی دیگر کپی کنیم یا انتقال دهیم، کلیک راست می کنیم. از منوی فوری ظاهر شده بر روی فرمان Move or Copy کلیک می نمائیم.

 در دریچه اول می توانیم کارپوشه ی مقصد را انتخاب کنیم. حالا باید جایی را انتخاب کنیم که قرار است کاربرگ مورد نظر به آنجا کپی شود یا انتقال یابد. اصولاً می توانیم اعلام نمائیم که کاربرگ جدیدی باشد یا انتهای کاربرگ ها (Move to End).

نکته مهم اینجاست که اگر بخواهیم کپی انجام شود باید کنار Create Copy تیک بزنیم. اگر تیک نزنیم انتقال (Cut) انجام خواهد شد.

تذکر: چنانچه بخواهیم تعدادی از کاربرگ ها را همزمان به یک مقصد جدید کپی کرده یا انتقال دهیم باید ابتدا آنها را انتخاب نموده و سپس با همان روشی که یک کاربرگ را کپی یا انتقال دادیم، عمل نمائیم. البته برای انتخاب چند کاربرگ ابتدا یکی از آنها را انتخاب می کنیم، سپس با نگه داشتن کلید کنترل (Ctrl) بقیه را نیز انتخاب می کنیم.

 چنانچه بخواهیم تمام کاربرگ ها را سریعتر انتخاب کنیم، بر روی یکی از آنها کلیک راست کرده و از گزینه Select All Sheets استفاده می کنیم.

درج کردن ردیف های جدید در کاربرگ ها:

وارد منوی Insert شده و بر روی فرمان Rows کلیک می کنیم. یک ردیف جدید بالای ردیف جاری ایجاد می شود.

درج کردن ستون جدید در کاربرگ:

وارد منوی Insert شده و بر روی فرمان Column کلیک می کنیم. یک سلول جدید قبل از سلول جاری ایجاد می شود.

درج کردن سلول جدید:

وارد منوی Insert شده و از فرمان Cells استفاده می کنیم. پنجره کوچکی باز می شود که 4 گزینه دارد.

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

عوض کردن مشخصات ردیف ها:

پس از انتخاب ردیف مربوطه، وارد منوی Format می شویم، نشانگر موس را بر روی فرمان Row می بریم، 4 گزینه ظاهر می شود:

1- Height: (یعنی بلندی)، با این گزینه می توانیم ارتفاع ردیف را به میزان دلخواه تغییر دهیم.

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

2- AutoFit: باعث می شود که سلولها به اندازه نیاز بلندی داشته باشند. یعنی طوری که داده های داخل ستونها در آنها جا شوند و فضا کم باشند نه زیاد.

3- Hide: ردیف های انتخاب شده مخفی می شوند.

4- Unhide: ردیف های مخفی شده آشکار می گردند.

تغییر دادن مشخصات ستونها:

ستونهای مورد نظر را انتخاب می کنیم. وارد منوی Format شده و نشانگر موس را بر روی فرمان Column می بَریم. 5 گزینه ظاهر می شود:

1- Width: می توانیم پهنای ستونها را به میزان دلخواه تنظیم کنیم.  

2- AutoFit Selection: پهنای ستونها به میزان نیاز تنظیم می شود طوری که داده های داخل آنها در سلول ها جا شده و فضا نه زیاد باشد و نه کم.

3- Hide: ستون های انتخاب شده مخفی می شوند.

4- Unhide: ستون های مخفی شده آشکار می گردند.

5- Standard Width: با این گزینه می توانیم پهنای استاندارد را تعریف نمائیم. باید توجه داشت که پهنای ستون های انتخاب شده به عدد استاندارد تغییر می یابند.

تغییر دادن مشخصات کاربرگ:

وارد منوی Format شده و نشانگر موس را بر روی فرمان Sheet می بَریم. 5 گزینه ظاهر می شود:

1- Rename: نام کاربرگ جاری را عوض می کند.

2- Hide: کاربرگ جاری را مخفی می کند.

3- Unhide: یک پنجره ظاهر می کند که اسامی کاربرگ های مخفی شده را در آن می بینیم. می توانیم هر مورد را انتخاب کنیم و بر روی OK کلیک نمائیم تا آشکار شوند.

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

4- Background: با این گزینه می توانیم پَس زمینه کاربرگ را عوض نمائیم. ما را به پنجره My Pictures هدایت می کند. می توانیم تصویری را انتخاب نمائیم.

5- Tab Color: با این گزینه رنگِ نامِ کاربرگ را عوض می نمائیم.

معرفی فُرمت برای سلولها:

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

ابتدا کاربر باید بداند، چگونه سلول ها را انتخاب می کند. آنگاه سلول های مورد نظر را انتخاب نموده و و وارد منوی Format می شود و از فرمان Cells استفاده می نماید. پنجره ای ظاعهر می شود که برگه هایی دارد:

می بایست از برگه Number استفاده شود و نوع داده و خصوصیات آن تعیین گردد.

در برگه Number تعدادی گزینه داریم که مهمترین آنها به شرح زیر می باشند:

1- General (عمومی): اگر این گزینه را انتخاب و تائید نمائیم، می توانیم در سلول های انتخاب شده، هر نوع داده ای قرار دهیم ولی باید بدانیم که اگر بار اول نوعی از داده را در سلول وارد کنیم، آن سلول از آن نوع خواهد شد.

2- Number (شماره): انتخاب این گزینه به این معنی خواهد بود که در سلول های انتخاب شده، فقط باید عدد وارد کنیم و در غیر این صورت در پشت صحنه، اِکسِل دچار مشکلاتی خواهد شد.

3- Currency (اَرز): با این گزینه می توانیم در سلول ها عدد وارد کنیم و سیستم واحد پول را در کنار عدد می نویسد.

4- Accounting (حسابداری): این گزینه شبیه اَرز است با این تفاوت که کلمه ریال را در سمت چپ عدد می نویسد.

5- Date (تاریخ): با این گزینه اعلام می کنیم که در سلول های انتخاب شده، تاریخ وارد خواهیم کرد. (مشکل تاریخ را می توانیم تعریف کنیم.)

6- Time (زمان): با این گزینه اعلام می نمائیم که در سلول های انتخاب شده فقط باید زمان را وارد کنیم. (شکل ظاهری زمان را باید تعریف کنیم.)

7- Percentage (درصد): اگر این گزینه انتخاب شود، اعدادی که در سلول های انتخاب شده وارد می نمائیم همراه با درصد خواهند بود. تعداد رَقَم های بعد از ممیّز را نیز می توان انتخاب کرد.

8- Fraction (کَسر): این گزینه باعث می شود اعداد وارد شده در سلول های انتخاب شده، بصورت کسرس ظاهر شوند. می توانیم تعداد رقم های صورت و مخرج را تعیین نمائیم.

9- Scientific (علمی): باعث می شود اعداد در سلول های انتخاب شده با نماد علمی ظاهر شوند. (جهت اعداد نجومی و میکرونی یعنی اعداد خیلی بزرگ و خیلی کوچک).

10- Text (متن): این گزینه باعث می شود که در سلول های انتخاب شده، متن وارد کنیم.

مثال (از 10 مورد ذکر شده در بالا):

چیدمان سلول های انتخاب شده:

ابتدا سلول های مورد نظر را انتخاب می نمائیم. سپس وارد منوی Format شده و از فرمان Cells استفاده می کنیم. پنجره ای باز می شود. از برگه دوم یعنی Alignment استفاده می نمائیم.در حقیقت می خواهیم تعیین کنیم که اگر داده هایی را در سلول های انتخاب شده تایپ کنیم، چیدمان آنها در سلول ها چگونه باشد.

دو دریچه داریم: افقی (Horizontal) و عمودی (Vertical). از این دریچه ها استفاده نموده و مشخص می نمائیم که چیدمان افقی و چیدمان عمودی چگونه باشد.

Degrees: برای نشان دادن نوشته ها به صورت زاویه دار از این گزینه استفاده می شود.

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

اگر کنار Wrap text تیک بزنیم، باعث می شود نوشته های طولانی در دو یا چند سطر ظاهر شوند.  

اگر کنار Shrink to fit تیک بزنیم، باعث می شود که اگر نوشته ها در یک سطر جا نشدند، آنها را کوچک کند تا جا شوند.

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

دریچه Text direction (جهت متن):

در این دریچه3 گزینه داریم:

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

برگه Font:

این برگه به کاربر امکان می دهد نوع قلم، استیل قلم و سایز آن را تعیین کند. همچنین می توانیم اعلام نمائیم که نوشته ها زیرخط داشته باشند یا نه، رنگ زیرخط چه باشد و نیز می توانیم جلوه هایی از قبیل خط کشیدن بر روی نوشته ها، بالانویس کردن یا پایین نویس کردن را تعیین نمائیم. اگر کنار Normal font تیک بزنیم، قلم به حالت نُرمال بر می گردد.

برگه Border (مرز):

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

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

برگه Patterns:

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

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

آدرس نسبی (Relative Address) و آدرس مطلق (Absolute Address):

در اِکسِل دو نوع آدرس داریم. آدرس نسبی و آدرس مطلق.

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

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

قرارداد: برای معرفی آدرس مطلق می بایست علامت دلار را قبل از برچسب ستون و قبل از شماره ردیف قرار دهیم.

مثال:

مساله:

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

اگر پس از محاسبه ناگهان اعلام شود که به حقوق پرسنل بلا استثناء چهل هزار تومان حق سرویس و سی هزار تومان حق نهار نیز تعلق می گیرد، حقوق آنها را محاسبه نمائید.

ایجاد نمودار:

جدول هایی که ستون های آنها با هم مرتبط باشند را می توان انتخاب کرد و از داده های موجود در سلول های آنها نمودار رسم کرد. باید دو یا چند ستون را انتخاب کرد و از یک ویزارد (Wizard) برای ترسیم نمودار استفاده نمود. طی چندین مرحله می توان نمودار را رسم کرد.

   برای فعال کردن ویزارد در نوار Standard بر روی دکمه Chart Wizard کلیک می کنیم.

 (اگر این دکمه نبود، وارد منوی Insert می شویم و بر روی فرمان Chart کلیک می کنیم. این ویزارد فعال می شود.) در مرحله اول باید نوع نمودار را انتخاب کنیم، پس از انتخاب بر روی Next کلیک می کنیم.

وارد مرحله دوم می شویم. در این مرحله اگر مهارت داشته باشیم، می توانیم محدوده داده ها و سری داده ها را تغییر دهیم. در نهایت بر روی Next کلیک می کنیم.

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

وارد مرحله چهارم می شویم. در این مرحله دو گزینه داریم:

گزینه اول باعث می شود که نمودار در یک کاربرگ جدید ظاهر شود. می توانیم برای کاربرگ نام تعریف نمائیم.

با گزینه دوم می توانیم، اعلام کنیم نمودار در کدام یک از کاربرگ های موجود قرار گیرد. در نهایت بر روی Finish کلیک می نمائیم.

مساله:

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

آمارهای سازمان هواشناسی کشور
نام استان	فروردین	اردیبهشت	خرداد	تیر	مرداد	شهریور
تهران	100	75	60	8	7	65
قزوین	90	85	70	10	8	75
گیلان	250	270	150	80	100	150
مازندران	220	230	140	60	88	100
سمنان	8009367292276 [e-mail] azsoft.ir [e-mail] azsoft.ir 09367292276 [e-mail] azsoft.ir 09367292276 [e-mail]	60	45	0	0	20

 

 با توجه به میزان بارش درج گردیده در شش ماهه اول سال مطابق جدول بالا، نمودارهای مختلفی به شرح زیر رسم نمائید:

1- نموداری که نشان دهنده میزان بارش در شش ماهه اول سال در تهران باشد.

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

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

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

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

اعمال تغییرات در نمودار ایجاد شده:

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

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

مرتب سازی داده ها در اِکسِل:

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

مرتب سازی ساده را بر روی داده های موجود در سلول های متوالی یک ستون یا یک ردیف انجام می دهیم. داده ها به صورت صعودی یا نزولی قابل مرتب شدن هستند. هر نوع داده را می توان مرتب کرد. پس از انتخاب یک ستون یا یک ردیف بر روی گزینه های Ascending یا Descending در نوار Standard کلیک می نمائیم تا بنا به درخواست ما، اطلاعات به صورت صعودی یا نزولی مرتب شوند.

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

برای انجام این کار ستون ها یا ردیف های مورد نظر را انتخاب نموده و از منوی Data بر روی اولین گزینه یعنی Sort کلیک می کنیم. پنجره ای باز می شود که سه دریچه دارد. به ترتیب از بالا به پایین (از دریچه اول تا سوم) می توانیم اولویت مرتب سازی را مشخص نمائیم. همچنین با تیک زدن در سمت راست پنجره، کنار گزینه های Ascending یا Descending می توانیم صعودی یا نزولی بودن مرتب سازی را مشخص نمائیم.

Header and Footer:

وارد منوی View می شویم. از فرمان Header and Footer استفاده می نمائیم. پنجره ای باز می شود که دو دریچه ( Header و Footer ) دارد. می توانیم دریچه هارا باز کنیم و اطلاعات مورد نظر خود را در آنها وارد نمائیم.

در دریچه های Header و Footerانواع اطلاعات وجود دارد که به ترتیب عبارتند از:

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

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

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

   در برگه Sheet می توانیم تنظیماتی را انجام دهیم:

در ناحیه اول می توانیم در داخل دریچه Print area کلیک کنیم و اگر خواستیم ناحیه خاصی را برای چاپ در نظر بگیریم، با موس آن ناحیه را انتخاب می کنیم تا آدرس آن ناحیه در این دریچه قرار گیرد.

اگر بخواهیم ردیفی در بالای تمام صفحات در چاپ تکرار شود، در داخل دریچه Rows to repeat at top کلیک می کنیم و ردیف مورد نظر را با یک کلیک انتخاب می نمائیم. آدرس مطلق آن ردیف در دریچه می آید.

اگر بخواهیم نوشته های یک ستون درابتدای اولین ستون هر صفحه پرینت شود، می توانیم در دریچه Columns to repeat at left کلیک کنیم و سپس با موس آن را انتخاب نمائیم.

 درناحیه پرینت چهار جا برای تیک زدن داریم:

1- خطوط شبکه ای (Gridlines): اگر این گزینه را تیک بزنیم خطوط افقی و عمودی چاپ خواهند شد و در غیر این صورت چاپ نمی شوند.

2- Black and white: اگر این گزینه را تیک بزنیم باعث خواهد شد که اطلاعات بصورت سیاه و سفید چاپ شوند.

3- Draft quality: در صورت تیک زدن این گزینه، کیفیت چاپ کَم رنگ و چِک نویس خواهد بود. (معمولاً برای صرفه جویی در مصرف جوهر یا تونر ریبون از آن استفاده می شود.)

4- Row and column headings: اگر تیک بزنیم، برچسب ستون ها یعنی A , B , C , … و همچنین شماره های ردیف ها یعنی 4 و 3 و 2 و 1 چاپ می شوند و در غیر این صورت چاپ نخواهند شد.

در دریچه Comments سه گزینه وجود دارد:

گزینه اول یعنی ملاحضات چاپ نشوند. (None)

گزینه دوم یعنی ملاحضات در انتهای کاربرگ چاپ شوند. (At end of sheet)

گزینه دوم یعنی ملاحضات در جای خودشان چاپ شوند. (As displayed on sheet)

در دریچه Cell errors as تعدادی گزینه داریم:

گزینه اول یعنی اگر خطایی هنگام اجرای فرمول های موجود در سلول ها پیش بیاید، خطا نشان داده شود. (Displayed)

گزینه دوم یعنی خطا ظاهر نشود. (Blank)

گزینه سوم یعنی به جای خطا، دو خط تیره ظاهر شود. (--)

گزینه چهارم یعنی علامت #N/A ظاهر شود. (#N/A)

در ناحیه Page order (ترتیب صفحات) باید به اِکسِل بگوییم که اگر کاربرگ ما اطلاعات خیلی زیادی در بعد افقی و بعد عمودی دارد چگونه اطلاعات را برای صفحات متوالی برگزیند و آنها را چاپ کند. آیا از بالا به پایین برود و دوباره از بالا به پایین و ... یا اینکه افقی برود و مجدداً افقی و ... .

پس از تنظیمات وارد منوی File می شویم و از فرمان Print Preview استفاده می نمائیم.

پس از نهایی کردن وارد منوی File شده و از فرمان Print استفاده می کنیم. پنجره ای ظاهر می شود. دردریچه اول این پنجره چاپگر را برای چاپ انتخاب می کنیم. در ناحیه Print range (محدوده چاپ)، دو گزینه داریم:

- All یعنی همه صفحاتی که مد نظر ما می باشد، چاپ شود.

- Pages یعنی صفحاتی که ما مشخص می نمائیم، چاپ شود. (محدوده ای از صفحات از ... تا ... .)

در ناحیه Print what باید تعریف کنیم که چه چیزی چاپ شود:

- Selection یعنی جایی که انتخاب کرده ایم.

- Active sheet(s) یعنی کاربر جاری چاپ شود. (نکته: اگر بخواهیم تعدادی کاربرگ را به عنوان کاربرگ فعال در نظر بگیریم، اولی را عادی انتخاب نموده و بقیه را با استفاده از کلید کنترل)

- Entire workbook یعنی کلیه کاربرگ های کارپوشه ی جاری.

- List یعنی فقط می خواهیم لیست را چاپ کنیم ولاغیر.

در ناحیه Copies می توانیم تعیین کنیم چند کپی چاپ شود.

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276

انجام محاسبات رگرسیون با نرم افزار اکسل

عنوان: انجام محاسبات رگرسیون با نرم افزار اکسل

مقدمه:

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

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

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

شرح:

برای انجام رگرسیون بوسیله اکسل ابتدا ، داده های متغیر مستقل را در یک ستون و در سمت راست آن ستون داده های متغیر وابسته را وارد میکنیم ،داده ها را انتخاب کرده و

 سپس از منوی insert chart  ،چارت از نوع scatter را انتخاب میکنیم ، وچارت مورد نظر را رسم میکنیم.

در نمودار اگر بر روی نقاط کلیک کنیم به شکلی که کلیه نقاط درون نمودار انتخاب شوند ،با کلیک راست کردن در این حالت گزینه add trendline را انتخاب میکنیم تا پنجره format trendline ظاهر شود ،

1)     در این پنجره در ابتدا در قسمت trend/regression type     نوع رگرسیون را انتخاب کنبد ،

Exponential :  رگرسیونی است که در تابع تخمین رگرسیون از یک تابع e^x استفاده شده است.

Linear: رگرسیون خطی (مدلی است که بیشتر در مطالب درسی آموزش داده میشود )

Logarithmic: رگرسیونی است که در تابع تخمین رگرسیون از یک تابع log x استفاده شده است

Polynomial: رگرسیونی است که در تابع تخمین رگرسیون از یک تابع چند جمله ای درجه n ام x استفاده شده است.که n  را در گزینه period وارد نمایید.

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]

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

Moving average: این گزینه متفاوت با بقیه گزینه هاست ،برای محاسبه سری های زمانی و متغیرهایی که سری زمانی هستند از این گزینه استفاده می شود.

2)    یکی از کاربردهای رگرسیون پیش بینی است ، به وسیله اکسل ما میتوانیم با توجه به یک روند گذشته و یا آینده آن روند را پیش بینی نماییم. در پنجره forecast با انتخاب forward آینده وگزینه Backward گذشته را پیش بینی میکنیم.

3)    در نهایت هم برای دیدن فرمول رگرسیون علاوه بر نمودار آن گزینه display equation on chart را انتخاب نمایید وdisplay r-squared value on chart میزان تطبیق نمودار پیش بینی را با اطلاعات واقعی نشان میدهد.

09367292276
[e-mail]
azsoft.ir
[e-mail]
azsoft.ir
09367292276
[e-mail]
azsoft.ir
09367292276
[e-mail]