Pivot کردن داده ها 

یکی از بهترین ویژگی های 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 اکسل در مدیریت و کنترل پروژه

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 ها می توانند با خلاصه کردن داده ها و دادن امکان تغییرآن ها به روش های مختلف برای شما توانایی کنترل ورک شیت¬ها را افزایش می دهند. 

استفاده از PivotTable در کنترل پروژه

مثال زیر را در نظر بگیرید. 

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

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

 معرفی PivotTable

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

 معرفی PivotTable

 

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

معرفی PivotTable

 

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

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

مثال زیر را در نظر بگیرید. 

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

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

 معرفی PivotTable

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

 معرفی PivotTable

 

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

معرفی PivotTable

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

معرفی Conditional Formatting

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

Conditional Formatting یا قالب بندی شرطی چیست؟

Conditional Formatting به شما کمک می کند تا به صورت اتوماتیک قالب بندی مد نظرتان مانند رنگ ها، آیکن ها و دیتا بارها را برای یک یا چند سلول و بر اساس مقدار آن ها بکار ببرید. برای انجام این کار، باید از Conditional Formatting استفاده کنید. مثلا اگر مقادیر موجود در سلول کمتر از 2000$ باشد، سلول را قرمز کن. با بکار بردن این دستور، براحتی می توانید بفهمید که محتوای کدام سلول ها کمتر از 2000$ است. 

 ConditionalFormating

روش استفاده از Conditional Formatting

در این مثال، ورک شیتی داریم که در آن داده های مربوط به فروش وجود دارد و می خواهیم ببینیم که کدام فروشندگان به مقدار فروش هدفی که مد نظر بوده، رسیده اند. مقدار فروش هدف، 4000$ در ماه است. در نتیجه ما دستوری را از طریق Conditional Formatting ایجاد می کنیم سلول هایی را که حاوی مقادیری بیش از  4000$ هستند را برای ما مشخص کند. 

1. سلول های مد نظرتان برای دستور Conditional Formatting را انتخاب کنید. 

 ConditionalFormating

2. از تب Home، بر روی دستور Conditional Formatting کلیک کنید که یک لیست کشویی ظاهر خواهد شد. 

3. بر روی نوع قالب بندی شرطی که مد نظرتان است، کلیک کنید و سپس فرمان مورد نظرتان را از منویی که ظاهر می شود، انتخاب کنید. دراین مثال، ما میخواهیم سلول هایی که بزرگ تر از 4000$ است را هایلایت کنیم. 

ConditionalFormating 

4. یک کادر محاوره ای ظاهر خواهد شد. مقادیر مد نظرتان را در فیلد خالی وارد کنید. در این مثال، ما 4000 را وارد می کنیم. 

5. یک  formatting style را از منوی کشویی انتخاب کنید. در این مثال، ما Green Fill with Dark Green Text را انتخاب می کنیم . سپس بر روی OK کلیک کنید. 

 ConditionalFormating

6. قالب بندی شرطی بر روی سلول های انتخاب شده اعمال می شود. در این مثال، به سادگی می توانیم ببینیم که کدام فروشنده ها مقدار فروش هدف را که برابر با 4000$ در ماه است را داشته اند. 

 ConditionalFormating

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

 ConditionalFormating

روش حذف conditional formatting : 

1. بر روی فرمان conditional formatting کلیک کنید.  یک لیست کشویی ظاهر خواهد شد. 

2. ماوس را بر روی Clear Rules ببرید و انتخاب کنید که کدام دستورات را می خواهید حذف کنید. در این مثال، ما برای آنکه تمام قالب بندی های شرطی را در ورک شیت حذف کنیم، بر روی Clear Rules from Entire Sheet را انتخاب می کنیم. 

ConditionalFormating 

3. قالب بندی شرطی حذف خواهد شد. 

 

* برای ویرایش یا حذف دستورات به صورت تکی بر روی Manage Rules کلیک کنید. این کار به ویژه در زمانی مفید است که بیش از یک قالب بندی شرطی برروی ورک شیت اعمال شده باشد. 

 

 

قالب شرطی از پیش تعریف شده (Conditional formatting presets)

اکسل دارای چندین قالب بندی شرطی از پیش تعریف شده (Conditional formatting presets) است، که شما می توانید به وسیله آن ها به سرعت قالب بندی های شرطی را به داده هایتان اعمال کنید. این قالب بندی های از پیش تعیین شده در سه دسته قرار می گیرند: 

• Data Bars : دیتا بارها نمودارهای افقی هستند که به هر سلول اضافه می شوند و بسیار شبیه نمودارهای میله ای هستند. 

 

• Color Scales : Color Scale ها رنگ هر سلول را بر اساس مقدار آن تغییر می دهد. هر Color Scale از دو یا سه رنگ استفاده می کنند. مثلا بین رنگ های سبز، زرد و قرمز، بزرگترین مقادیر با سبز، مقادیر متوسط با زرد و کوچکترین مقادیر با قرمز نشان داده می شوند. 

 

 

 

• Icon Sets : به هریک از سلول ها بر اساس مقادیر آن ها تصویر خاصی را اضافه می کند. 

 

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

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

  

2. بر روی فرمان Conditional Formatting کلیک کنید. یک لیست کشویی ظاهر می شود.

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

 

 

4. قالب بندی شرطی بر روی سلول های انتخاب شده ظاهر می شود. 

 

 

 

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

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

 

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

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

4. ابزار Conditional Formatting را از مسير Home > Styles باز كنيد. 

 

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 استفاده كنيد)

 

 

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

تحلیل پارتو 

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

 

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

 

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

 

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

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

 

 

 

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

 

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

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

 

نتیجه زیر حاصل می‌شود.

 

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

 

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

 

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

 

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

 

 

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

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

تابع SUBTOTAL 

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

 

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

 

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

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

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

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

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

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

ساختار این تابع به شکل زیر است.

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 که برای میانگین است.

موفق باشید.

 

معمولا وقتی روی چندین شیتِ داده در اکسل کار می کنید، ممکن است این سوال برای شما پیش بیاید که یک آیتم در یک لیست چند بار در لیست دیگر تکرار شده است. به عبارت دیگر دو لیست دارید که میخواهید بدانید تعداد آیتم های لیست 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 ترفندی که شما نمی دانید!!

 

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

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

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

صفحه1 از2