معرفی what if analyse در اکسل

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

معرفی سناریوها:

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

1- goal seek
2- data table
3- scenario mannager

کاربرد Goal Seek:

مثال:

برای اینکه سود 100 ریالی بدست آوریم چند واحد محصول باید بفروشیم؟

این ابزار از منوی Data زیر مجموعه Whats If Analysis می باشد
با این ابزار می توان بهترین حالت را برای یک مجهول تک متغیره به کار برد. در واقع در سوال مجهول ما یک مورد است و آن تعداد فروش واحد کالای مورد نیاز جهت بدست آوردن سود 100ریالی.
نکته لازم به ذکر در استفاده از این ابزار ها این است که می بایست داده هایی که وارد می کنیم و نتیجه ای را انتظار داریم سلولها با فرمول با یکدیگر مرتبط باشند بدین منظور که نمی توان عددی را بدون فرمول وارد کرد و انتظار پاسخ داشت. یعنی باید با تغییر سلول مورد نظر ما بصورت خودکار سلول هدف ما نیز تغییر کند

فرضا قیمت محصول در سلول a2 برابر 50 ریال باشد سپس در a3 که تعداد محصول مورد نیاز جهت فروش می باشد عددی را وارد کنیم یا صفر قرار دهیم و در خانهa4 خانه های a2 و a3 را در یکدیگر ضرب کنیم

حال از whats if analysis مورد Goal Seek را انتخاب می کنیم
در قسمت set cell خانهa4 را قرار میدهیم یعنی تنظیم شود با سود من.
در قسمت to value ارزش سود را قرار میدهیم در مثال ما 100 ریال سود بود.
و By Changing cell خانه ای که میخواهیم تنظیم شود یعنی a2 تعداد فروش
مس از تایید مشاهده می نمایم در a2 جواب می نشیند.
لازم به ذکر است که در Goal Seek کلمهcell وجود دارد یعنی یک خانه لذا نمی توان انتظار ترکیبی از خانه ها یا چند متغیر را داشت. صرفا یک خانه و یک متغیر مجهول.

کاربرد Data Table

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

چنانچه در متن های بالاتر تشریح شده یکی از ابرازها در منوی Data از مجموعه Whats If Analysis می باشد.

با استفاده از این ابزار می توان به دو مجهول پاسخ داد لذا در دو متغیره مجهول (در سوال فوق، حجم فروش و هزینه) کاربرد می یابد که با ایجاد جدولی از طریق Data Table می توان سود مزبور را مشخص کرد.

در واقع ایجاد سناریو می کنیم.

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

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

What-if Analysis شامل سه ابزار 

1- Data Table
2- Scenario Manager
3- Goal Seek
می باشد که در ادامه با یک مثال کاربردی به شرح هریک از آنها خواهیم پرداخت.

1- Data Table
فرض کنید با توجه به نیاز مالی خود خواهان دریافت وامی به مبلغ 100،0000،000 ریال هستیم. اکنون باید تصمیم بگیریم با توجه به شرایط خود از بین انبوهی از بانک ها، موسسات مالی و اعتباری و موسسات قرض الحسنه باشرایط متفاوت کدام یک را انتخاب کنیم در اینجا به یکباره با همان سردرگمی که به آن اشاره شد مواجه می شویم و بدلیل کمبود زمان نیاز به تصمیم گیری سریع پیدا خواهیم کرد و Data Table کوتاه ترین مسیر برای تصمیم گیری ما خواهد بود.
بسیار خوب شروع به حل مسئله می کنیم. برای حل این مسئلهexcel تابع کلیدی PMTرا پیشنهاد می دهد که باتوجه به مبلغ وام، نرخ بهره و مدت بازپرداخت، مبلغ قسط ماهیانه را برای ما محاسبه می کند.

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

حال نوبت به استفاده از تکنیک What-if Analysis می باشد. قبل از اینکار از ابتدای بازه نرخ بهره تا انتهای بازه دوره بازپرداخت را درگ می کنیم.  سپس از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Data Table را انتخاب می کنیم.

در پنجره Data Table در قسمت Row input cell آدرس سلول نرخ بهره سالیانه (E4) و در قسمت Column input cell آدرس سلول دوره بازپرداخت (E5) را داده و بر روی  OK کلیک می کنیم.             

در ادامه  آموزش اکسل تکنیک  قدرتمند What-if Analysis  امروز به دو ابزار   Scenario Manager و Goal Seek خواهیم پرداخت.

  Scenario Manager:

یکی از محدویت هایی که در ماکروسافت اکسل وجود دارد دو بعدی بودن این نرم افزار است بصورتی که هنگام استفاده از ابزار Data Table تنها می توان تغییرات را بروی دو متغیر تابع اعمال کرد.

 فرض کنید علاوه بر نرخ بهره و دوره باز پرداخت ما می خواهیم بدانیم چه می شود اگر مبلغ وام نیز کم و زیاد شود و تاثیر آن در جواب مسئله یعنی مبلغ قسط ماهانه چه خواهد شد. در این حالت نیز شرکت ماکروسافت پیش بینی های لازم را انجام داده و  ابزار سودمند Scenario Manager را معرفی کرده است.

برای حل مسئله ابتدا از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Scenario Manager را انتخاب می کنیم.

در پنجره باز شده ناحیه ای به نام Scenarios برای ایجاد سناریو با حالت های مختلف وجود دارد و تعدادی ابزار نیز در سمت راست پنجره برای مدیریت سناریوها قرار داده شده است. برای اضافه کردن سناریو گزینه Add… را انتخاب می کنیم.

 در پنجره Add Scenario بعد از انتخاب نام برای سناریو در قسمت Changing cells آدرس سلول هایی که برای تغییر مد نظر داریم را وارد می کنیم در این مثال ما آدرس سلول های E3 , E4 , E5 که در تابع ما بترتیب به متغیرهای نرخ بهره سالانه، دوره بازپرداخت و مبلغ وام اشاره دارد را وارد کرده ایم.

 بعد از آدرس دهی سلول های مورد نظر و تایید، در پنجره بعد نوبت به دادن مقادیر دلخواه به متغیر های انتخاب شده می رسد.

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

در Scenario Manager مانند Data Table محدودیت تعداد متغیر وجود ندارد ولی در عوض گزارشی بصورت ماتریسی نیز دریافت نخواهید کرد.

برای گزارش گیری از پنجره Scenario Manager بر روی گزینه Summery…  کلیک می کنیم در اینجا حق انتخاب دو نوع گزارش Scenario summery  و Scenario PivotTable Report  را داریم که اکسل هر گزارش را در شیتی جداگانه به ما می دهد.

در قسمت Result cells آدرس سلول پاسخ یعنی مبلغ قسط ماهانه را می دهیم.

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

 Scenario Summery:

 Scenario PivotTable :

 Goal Seek :

اگر شما بخواهید با توجه به توان بازپرداخت خود وام بگیرید مثلا ماهیانه 3,000,000 ریال وبخواهید که متغیرها طوری تغییر کنند که دقیقا این مبلغ را هرماه بپردازید ماکروسافت اکسل ابزار Goal Seek را به شما پیشنهاد می کند.

شما کافیست مبلغ هدف را مشخص کرده و یک متغیر را اعلام کنید و Goal Seek متغیر را تا جایی که شما به مقدار هدفتان برسید تغییر می دهد. (ابزار Goal Seek تغییرات را تنها بر روی یک متغیر می تواند اعمال کند.) ما دراینجا مبلغ وام را به عنوان متغیر تعریف کرده ایم یعنی چه مقدار وام بگیریم تا هر ماه تنها مبلغ 3,000,000 ریال قسط بپردازیم.

سلول هدف (مبلغ قسط ماهانه) را در قسمت  Set cell، مقدار هدف را در قسمت  To value و متغیری که قرار است تغییر کند (مبلغ وام) را در قسمت By changing cell وارد می کنیم و با زدن Ok نتیجه دلخواه،

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

در excelبا استفاده از تابع Match می‌توان موقعیت یک مقدار را در یک محدوه مشخص، تعیین نمود. این محدوده باید بصورت برداری، یعنی تنها دارای یک سطر یا یک ستون باشد. البته امکان جستجو در مجموعه نیز وجود دارد. مثلا فرمول زیر حرف b را در مجموعه {a,b,c} جستجو می‌کند و موقعیت آن‌ یعنی مقدار 2 را برمی‌گرداند، چون حرف b دومین حرف مجموعه فوق است.

=MATCH("b",{"a","b","c"},0)

ساختار تابع Match به شکل زیر است:

=MATCH (lookup_value, lookup_array,[match_type])

آرگومان اول (lookup_value): اجباری است و بیانگر عبارتی است که باید جستجو شود. این آرگومان می‌تواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم (lookup_array): اجباری است و بیانگر محدوده‌ای است که عملیات جستجو باید در آن انجام شود. یعنی آرگومان اول قرار است با این آرگومان مقایسه شود تا در صورت تطابق، موقعیت آن بازگردانده شود.
آرگومان سوم (match_type): نوع جستجو را مشخص می‌کند، که می‌تواند یکی از حالات زیر باشد:

• اگر در این قسمت چیزی نوشته نشود یا عدد 1 درج شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از کوچک به بزرگ مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین عبارت کوچک‌تر از آرگومان اول را باز می‌گرداند.
• اگر عدد 0 نوشته شود، تابع شماره سطری را که اولین تطابق رخ دهد را بر می‌گرداند. در صورتیکه مقدار موردنظر در محدوده تعیین شده یافت نشود خطای N/A# نمایش داده می‌شود.
• اگر 1- نوشته شود، اعدادی که در آن‌ها جستجو انجام می‌شود باید از بزرگ به کوچک مرتب شده باشد. اگر در محدوده مورد جستجو، آرگومان اول وجود نداشته باشد، تابع، شماره سطر نزدیک‌ترین مقدار بزرگ‌تر از آرگومان اول را باز می‌گرداند.

نکته 1: مرتب شدن داده‌ها بصورت صعودی یعنی از کوچک به بزرگ باید مطابق ترتیب زیر باشد:

..., -2, -1, 0, 1, 2,..., A-Z, FALSE, TRUE.

حالت نزولی برعکس ترتیب بالا است.

نکته 2: اگر آرگومان سوم برابر با 0 باشد و آرگومان دوم از نوع متن باشد، می‌توانید از کاراکترهای جایگزین شونده استاندارد در آرگومان اول استفاده کنید. علامت? را می‌توان جایگزین یک کاراکتر و علامت * را می‌توان جایگزین چندین کاراکتر دانست.                                                         

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

برای این کار می توانید ابتدا بازه سلولهای مورد نظر را انتخاب و در منوی insert>chart نوع چارت را تعیین کنید تا اکسل با توجه به داده های انتخابی، نمودار مورد نیاز را ترسیم نماید. همچنین می توانید بعد از ایجاد نمودار از منوی insert>chart، با کلیک راست روی نمودار و انتخاب گزینه select data، مجموعه سلولهای مورد نظر را جهت استفاده در نمودار انتخاب نمایید.

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

                                                          
برای تعریف بازه نام گذاری شده، از منوی formulas،name manager را انتخاب نموده و در پنجره باز شده دکمه new را کلیک می کنیم.

در پنجره جدید در کادر name نام مورد نظر را وارد کنید (در مثال شکل زیر، نام  sales_data درج شده است)، در کادر refers to می توانید بازه ای از سلولها را وارد نمایید(F5:G11 در شکل زیر) و یا می توانید در این کادر از فرمول برای مشخص کردن بازه داده های named range استفاده نمایید، فرق این دو روش در این است که در حالت اول خروجی ثابتی خواهیم داشت و در حالتی که از فرمول، در کادر refers to استفاده نماییم، خروجی ما متغییر خواهد بود و در صورتی که از این داده های نامگذاری شده به عنوان ورودی داده های نمودار (data ranges) استفاده کنیم، نمودار ما می تواند نسبت به تغییرات عددی و حتی تعداد داده ها پویا باشد و در واقع یک نمودار داینامیک        خواهیم داشت.

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

باید مقادیر محورهای عمودی و افقی با عناوین year و sales مشخص شود بنابراین یک محدوده نامگذاری جهت محور عمودی به نام Sales_data و یک نام جهت محور افقی به نام sales_lable تعریف می نماییم و در قسمت refers to به برای نامهای فوق به ترتیب فرمولهای زیر را تعریف می کنیم:

در نهایت نام اختصاص داده شده را به روش زیر در تنظیمات چارت وارد می کنیم:

همانطور که گفته شد با راست کلیک روی نمودار،select data را کلیک می کنیم و در منویی که به شکل زیر باز می شود همانطور که مشخص شده است، دکمه Edit  را برای تنظیم ورودی هر یک از محورهای عمودی و افقی کلیک می نماییم.

با کلیک روی Edit در قسمت (series) منویی  باز می شود

در قسمت Series values نام Sales_data که قبلا تعریف کردیم (توضیحات شکل 5) درج شده است. به نحوه درج نام در این کادر توجه کنید که نام فایل اکسل یعنی dynamic_range با پسوند (.xlsx) و علامت (!) قبل از نام، آورده شده است.همینطور در قسمت Categoryبا کلیک روی Edit منو  باز می شود:

 برای داده های مربوط به محور افقی یعنی عناوین نمودار (Axis Lable)، نامی را که قبلا تعریف کرده اید با عنوان Sales_lable را وارد می کنیم.

 با استفاده از فرمول در قسمت data range نمودار به گونه ای طراحی شده که درصورت اضافه یا کم کردن داده ها در ستونهایyear و sales بلافاصله نمودار، با ورودی های جدید ترسیم می گردد
خروجی فرمولی که در روش فوق جهت معرفی بازه سلولها در نمودار نوشته می شود باید بازه ای از سلولها باشد. پرکاربردترین روش در این خصوص، ترکیب توابع OFFSETو COUNTA می باشد. همچنین می توانیم از فرمولهای آرایه ای برای این منظور استفاده کنیم.                                                                              امید واریم از این آموزش اکسل لذت برده با شید

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

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

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

توجه:همچنین شما می توانید در نوار سربرگ صفحه اصلی Excel در بخش Cells، دکمه Format را انتخاب کنید. سپس، از منوی کشویی که باز می شود از قسمت Organize Sheets گزینه Move or Copy Sheet… را انتخاب کنید.

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

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

برای کپی کردن برگه به تنهایی روی آن کلیک کنید تا باکس Move or Copy نمایش داده شود و تیک گزینه Create a copy را بزنید و سپس Ok کنید.

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

برای کپی کردن یا انتقال کاربرگ انتخاب به کارنامه جدید روی آن کلیک کنید تا باکس Move or Copy ظاهر شود در فیلد To book  بر روی new book کلیک کنید و در آخر تیک گزینه Create a copy را بزنید و سپس Ok کنید.

حالا کاربرگ جدید ایجاد می شود و می توانید کاربرگ انتخاب شده را در ان نتقال یا کپی کنید.

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

فرض کنید معلمی هستید که می خواهید با استفاده از کاربرگ های برنامه اکسل، نمرات دانش آموزان را کنترل کنید. به این صورت که نمرات خوب از نمرات متوسط و ضعیف به راحتی قابل تفکیک باشند. باید دید برای این امر چه کار باید بکنید؟  با این اموزش اکسل می توانید از قالب بندی شرطی یا conditional formatting در excelاستفاده نموده و تغییرات مختلفی را در ظاهر یک سلول,g (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سلول، سه شرط تعیین کنید که با فرمت فعلی آن سلول، مجموعاً چهار شرط می شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه گذاری (shading) هر سلول را تعیین کنید که البته این تغییرات، شامل فرمت عددی و نوع چیدمان سلول نمی شود. برای استفاده از قالب بندی شرطی، باید شروطی را انتخاب کنید که با مقادیر false و true قابل ارزیابی باشند.                                                                                                                                                    مثلا جمله "آیا عدد بزرگ تر از 10 است؟" را می توان با false یا true ارزیابی کرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سلول  انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یک سل تعیین کرد که اگر شرط اول، ارزش true داشت، تغییرات انجام شده و بقیه شرط ها نادیده گرفته خواهند شد.
حال می خواهید رنگ هر سلول  را بر اساس نمره موجود در آن، تعیین کنید. بدین صورت که خانه های دارای نمره کمتر از 50 به رنگ قرمز، بین 50 و 65 به رنگ سبز، بین 65 و 75 به رنگ بنفش، و نمرات بالاتر از 75، به رنگ آبی درآیند.
برای این کار، ابتدا رنگ فونت کلیه سل های حاوی نمرات را آبی کنید. سپس آن ها را انتخاب نموده و در منوی Format، روی Conditional Formating کلیک کنید. در پنجره باز شده، کادر اول را در حالت Cell Value Is باقی بگذارید. از کادر دوم، گزینه less than را انتخاب و در کادر روبه روی آن، عدد 50 را تایپ کنید. سپس دکمه Format واقع در سمت راست را کلیک کرده ودر زبانه Font، رنگ قرمز را برای اعداد کمتر از 50 انتخاب نمایید. با زدن OK، به کادر قبلی برگشته ودکمه Add در پایین صفحه را کلیک کنید.
می توانید تا سه شرط را برای هر سل تعیین کنید.
حالا برای تعیین شرط دوم، کادر مقابل Cell Value Is را به less than، و در کادر روبه روی آن، عدد 65 را بنویسید. سپس دکمه Format را زده و در کادر باز شده، رنگ سبز را انتخاب کنید. یک بار دیگر بر روی Add کلیک کنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در کادر اول Cell Value Is، در کادر دوم less than، و در کادر روبه روی آن، عدد 75 را تایپ کنید. سپس دکمه Format را کلیک کرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت کنید، می بینید که دکمه Add بعد از اعمال سومین شرط، غیر فعال می شود. چرا که شما نمی توانید بیش از سه شرط برای یک سل تعیین کنید. OK را بزنید و نتیجه را ملاحظه نمایید.
راه ساده تر آن است که قالب بندی های مورد نظرتان را به صورت یکجا بر تمامی سل ها اعمال کنید. اما اگر خواستید، می توانید بااستفاده از دکمه FormatPainter، قالب های شرطی و فرمت اولیه یک سلول را به سلول های دیگر کپی نمایید.
در ضمن برای این که در آینده بدانید قالب بندی های شرطی بر روی کدام یک از سلول های کاربرگتان اعمال شده است، از منوی Edit، گزینه Go To را کلیک و در کادر باز شده، دکمه Special را کلیک کنید. در پنجره باز شده، دکمه رادیویی Conditional Formats را علا مت زده و OK کنید. با این کار کلیه سلول  های دارای قالب بندی شرطی، با رنگ جداگانه نمایش داده می شوند.
می توانید فرمتی را روی یک سطر با توجه به تعداد یک سلول خاص، اعمال نمایید.
همچنین می توان فرمت یک ردیف را بر اساس داده های یک سلول در آن ردیف تعیین کرد. فرض کنید شماره ستون ها را در ردیف یک می نویسید، نام دانش آموزان را در ستون A، و نمره آن ها را نیز در ستونB. کلیه سلول ها از خانه 2 A تا آخرین آن ها در ستون B را انتخاب کنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه Formula Is و در لیست دوم، شرط 50>2= را نوشته و رنگ متن را نیز تنظیم کنید. با استفاده از دکمه Add، شروط دوم و سوم را به ترتیب 65> B2 $= و 75 > 2 B $= تعیین نمایید. برای کنترل مجدد تغییرات هر سلول، ابتدا سل A2 را انتخاب کنید و به مسیر Format / Conditional Formatting بروید.
این سلول بر اساس مقدار موجود در سلول B2 تغییر خواهد کرد. فرمت سل A3 نیز براساس سلول B3 تغییر خواهد نمود و به همین ترتیب تا آخر. بخش B$ فرمول برای مقایسه دوبه دو بین ستون ها تنظیم می شود تا بتواند تغییرات هر ردیف را به طور جداگانه انجام دهد. همچنین می توانید از شرط Formula Is برای دیگر ردیف ها نیز استفاده کنید. مثلاً بعد از انتخاب ردیف های موردنظر از فرمول های (Mod(Row(),2= و ((Not(Mod(Row(),2= استفاده نمایید. برای این که به جای ردیف ها، در ستون ها تغییرات ایجاد کنید، در فرمول آن ها به جای () Row از () Column استفاده نمایید. اگر هم خواستید، می توانید در شروط ایجاد شده در ردیف های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده کنید.

استفاده از فرمول های پیچیده تر در ایجاد تغییرات شرطی، امکانات قوی تری را در اختیار شما قرار می دهد. مثلاً برای این که در محدوده B2:B50 اعدادی که بیش از یک بار آمده اند مشخص شوند، می توان در جلو لیست Formula Is فرمول 1

• تابع INDEX

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

=INDE شماره ستون، شماره سطر، محدوده)

=INDE شماره محدوده، شماره ستون، شماره سطر، یک یا چند محدوده)

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

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

در حالت اول که تابع INDEX سه پارامتر دارد اگر فرمول زیر را در سلولی بنویسیم مقدار سلول موجود در سطر سوم و ستون چهارم جدول فوق که عدد 40 هست نمایش داده می‌شود.

‎=INDE A1 5,3,4)‎

در حالت دوم اگر در سلولی فرمول زیر ‎ را قرار دهیم چون پارامتر آخر ما عدد 2 هست سلول موجود در سطر دوم و ستون دوم مربوط به آرایه دوم یعنی محدوده A4 5 انتخاب می‌شود و در نتیجه قهوه در آن سلول نمایش داده می‌شود.

=INDE (A1 3,A4 5),2,2,2)

اگر پارامتر آخر را به یک تغییر دهیم محدوده ما از A4 5 به A1 3 تغییر می‌کند و در نتیجه تابع به جای کلمه قهوه کلمه پرتقال را برمی‌گرداند.                                                                                                                                             

نکته:

1- اگر در تابع INDEX پارامتر شماره سطر را برابر صفر قرار دهیم تابع آرایه‌ای شامل تمام سلول‌های شماره ستون مشخص‌شده را برمی‌گرداند. همینطور اگر شماره ستون را برابر صفر قرار دهیم تابع آرایه‌ای شامل تمام سلول‌های شماره سطر مشخص شده را به عنوان نتیجه برمی‌گرداند. به عنوان مثال اگر ما در سلولی فرمول زیر را در سلولی قرار دهیم، جمع تمام سلول‌های موجود در ستون سوم محدوده B1 5 یعنی عدد 200 توسط فرمول محاسبه می‌شود.

‎=SUM(INDE B1 5,0,3))‎

2- اگر محدوده موردنظر ما دارای فقط یک سطر یا یک ستون باشد تعریف شماره سطر یا ستون برای آن آرایه اختیاری هست. به عنوان مثال اگر محدود موردنظر ما A1 1 باشد، از آنجایی‌که این محدوده فقط یک سطر دارد نیازی به تعیین پارامتر شماره سطر نیست و می‌توانیم جای این پارامتر را در فرمول خالی بگذاریم.

تابع Column

در حالت کلی تابع Column درExcelبرای نمایش شماره ستون سلولی که به آن داده می‌شود، استفاده می‌شود که به صورت زیر نوشته می‌شود:

=Column(مرجع یا سلول مورد نظر)

مثلاً نتیجه دستور زیر عدد 6 می‌باشد.

=COLUMN(F12)

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

• تابع Columns

تابع COLUMNS، تعداد ستون‌های موجود در محدوده انتخابی را نشان می‌دهد. ساختار این تابع به صورت زیر است:

=COLUMNS(محدوده)

بطور مثال، فرمول زیر تعداد ستون‌های موجود در محدوده انتخابی A3:C10 را نشان می‌دهد، که پاسخ آن برابر با 3 است.

=COLUMNS(A3:C10)

تابع Row

در حالت کلی تابع Row اکسل برای نمایش شماره سطر سلولی که به آن داده می‌شود، استفاده می‌شود که به صورت زیر نوشته می‌شود:

=ROW(مرجع یا سلول مورد نظر)

مثلاً نتیجه دستور زیر عدد 12 می‌باشد.

=ROW(F12)

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

• تابع Rows

تابع ROWS، تعداد سطرهای موجود در محدوده انتخابی را نشان می‌دهد. ساختار این تابع به صورت زیر است:

=ROWS(محدوده)

بطور مثال، فرمول زیر تعداد سطرهای موجود در محدوده انتخابی A3:C10 را نشان می‌دهد، که پاسخ آن برابر با 8 است.

=ROWS(A3:C10)

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

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

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

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

 ستون مربوط به نام‌ها را انتخاب کنید و با استفاده از گزینه فیلتر برای این ستون‌ها فیلتر در نظر بگیرید. برای این کار کافیست بر روی یک سلول کلیک کرده و گزینه Filter را انتخاب کنید.

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

• گزینه Filter by Color که فیلتر بر اساس رنگ می‌باشد و با حرکت فلش ماوس بر روی این گزینه کادر زیر مجموعه‌های آن باز خواهد شد. چون از نام این فیلتر پیداست که چه کاری انجام خواهد داد مثالی از آن نخواهیم زد.
• گزینه Text Filter که برای فیلتر نمودن نوشته‌ها بکار می‌رود.

 بر اساس حرف (س) نام‌ها را فیلتر کنید. برای این کار ابتدا به روی آرم فیلتر که در ابتدای ستون قرار دارد کلیک کرده و از کادر باز شده آن، فلش ماوس را به روی گزینه Text Filter می‌بریم تا کادر کشویی دیگری در کنار کادر فیلتر باز شود. از کادر دوم انتخاب می‌کنیم که این ستون بر چه اساسی باید فیلتر شود.

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

…Equals (برابر با…)

…ِDoes Not Equals (برابر نیست با…)

…Begins With  (شروع شدن با…)

…Ends With  (تمام شدن با…)

…Contains (شامل … می‌شود)

…Does Not Contain  (شامل … نمی‌شود)

…Custom Filter (فیلتر سلیقه‌ای)

در این بخش از آموزش  میخواهیم فیلتر بر اساس حرف (س) را اعمال کنیم بنابراین به گزینه …Begins With (شروع می‌شود با…) نیاز خواهیم داشت. با انتخاب این گزینه پنجره‌ای باز خواهد شد. در این پنجره و در کادر مشخص شده در تصویر زیر حرف (س) را وارد کنید و سپس با کلیک بر گزینه Ok فیلتر روی ستون نام خریدار اعمال می شود.

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

حال با نگاه به برگه (Sheet) مورد نظر، نتیجه کار را مشاهده خواهید کرد.

در جدول قبل می‌خواهیم بجز نام‌هایی با حرف اول (س و الف) آغاز می‌گردند، مابقی را فیلتر کنیم. برای این کار مانند قبل روی آرم Filter روی ستون مورد نظر (ستون نام‌ها) کلیک کرده و با باز شدن کادر آن گزینه Custom Filter را انتخاب کرده و در پنجره باز شده  گزینه Or را انتخاب کرده و در کادرهای سمت راست بالا و پایین حرف‌های (س) و (الف) را وارد می‌کنیم.

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

حال اگر بخواهیم بجز قیمت‌های کل بین (50000) تا (100000) را فیلتر کنیم، یا به عبارتی دیگر فیلتر عددی را اعمال کنیم:

در ابتدا بر روی آرم فیلتر درون ستون قیمت کل کلیک می‌کنیم. از کادر باز شده گزینه Number Filters را انتخاب کرده و باز هم کادری در کنار آن باز خواهد شد. کادر زیر مجموعه شامل گزینه های زیر می‌باشد:

…Equals (برابر با…)

…Does not Equals (برابر نبودن با…)

…Greater Than (بزرگتر از…)

…Greater Than  or Equal To  (بزرگتر یا مساوی با…)

…Less Than (کوچکتر از…)

…Less Than or Equal To (کوچکتر یا مساوی با…)

…Between (بین…)

…Top 10 (ده مورد اول…)

…Above Average (بالاتر از میانگین…)

…Below Average (پایین‌تر از میانگین…)

…Custom Filter (فیلتر سلیقه‌ای…)

برای فیلتر کردن اعداد بزرگ‌تر از 50000 تا کوچک‌تر از 100000 از گزینه …Between در پنجره مربوطه مانند تصویر زیر عمل می‌کنیم:

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

اگر در ستونی  فیلترینگ را اعمال کرده‌اید و حال می‌خواهید این فیلتر‌ها را از بین ببرید دو راه پیش رو دارید:

روش اول این است که از سربرگ Home به بخش Sort & Filter رفته و با کلیک بر روی این گزینه، از کادر باز شده گزینه Clear را انتخاب کنید.

روش دوم این است که بر روی آرم فیلتر که در بالای ستون فیلتر شده قرار دارد کلیک کرده و از کادر باز شده گزینه… Clear Filter From را انتخاب نمایید.

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

پس از انتخاب ستون‌های فیلتر شده، به سربرگ Data مراجعه کرده و در کادر گروه Sort & Filter بر روی گزینه Clear کلیک کنید.

برای نمایش ممیز (/) به جای نقطه در اعداد اعشاری در اکسل سه روش وجود دارد.

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

روش اول: تغییر نمایش علامت اعشار فقط در نمودارهای اکسل 

روش دوم: تغییر نمایش علامت اعشار در کل اکسل

روش اول: تغییر نمایش علامت اعشار فقط در نمودارهای اکسل

مجموعا در فارسی کردن اعداد در نمودارهای اکسل (لینک 1 و لینک 2)، چهار نوع کد برای فارسی کردن اعداد در نمودارهای اکسل ارائه شده است. که دو مورد آن‌ها دارای عدد 3 و دو مورد دیگر دارای عدد 2 هستند.

[$-3010000]0.00
[$-3000401]0.00

[$-2000401]0.00
[$-2000000]0.00

اعداد نمودار اگر صحیح باشند و اعشاری نباشند تفاوتی ندارد که کدامیک از این کدها استفاده شوند ولی برای اعداد اعشاری باید از کدهایی استفاده کنید که دارای عدد 3 باشند. با استفاده از کدهای شامل عدد 3، چنانچه یک سری از فونت‌ها را انتخاب کنید مثلا Arial، نقطه به ممیز / تبدیل می‌شود ولی اگر از فونت‌هایی مثل Calibri یا فونت‌های فارسی سری B استفاده کنید نقطه به ویرگول (,) تبدیل می‌شود. پس اگر می‌خواهید بجای نقطه، ممیز نشان داده شود فونت اعداد باید Arial باشد. البته از فونت‌‌های دیگری نیز می‌توانید استفاده کنید که خودتان می‌توانید بررسی کنید.

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

در نرم افزار اکسل (نسخه 2007 به بعد) به مسیر زیر بروید:

File menu –> Options –> Advanced

در قسمت Editing options تیک گزینه Use system separators را بردارید و علامت / را در قسمت Decimal separator تایپ کنید.

روش سوم: تغییر نمایش علامت اعشار در کل ویندوز

در ویندوز به مسیر زیر بروید.

Control Panel –> Clock, Language and Region –> Change the date, time, or number format

در پنجره باز شده گزینه Additional settings را انتخاب کنید.

در پنجره Customize Format علامت اعشار در قسمت Decimal Symbol نشان داده شده است که می توانید علامت / را در آن تایپ کنید.

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

فرض کنید که شرکتی 300 کارمند داره و به هر کدوم از اونها ماهیانه 5 میلیون ریال حقوق می دهد. هزینه مواد اولیه به ازای هر کالا برابر 60 هزار ریال هست و قیمت فروش محصول در بازار هم 80 هزار ریال هست. تعداد تولید  ماهانه محصول شرکت در حال حاضر 100 هزار واحد در هر ماه هست و تمام محصولات تولید شده در بازار فروخته خواهند شد. حالا فرض کنیم که هزینه تمام شده هر واحد کالا و سود شرکت از روابط ساده زیر بدست میاد:

 قیمت تمام شده هر کالا = هزینه مواد اولیه به ازای هر کالا + ((تعداد پرسنل *  حقوق ماهانه) / تعداد تولید ماهانه)

سود ماهانه شرکت = تعداد تولید ماهانه * (قیمت فروش هر کالا – قیمت تمام شده هر کالا)

حالا اگر بنا به دلایلی حقوق پرداختی پرسنل افزایش پیدا کند، طبیعتا سود ماهانه شرکت کاهش پیدا خواهد کرد. فرض کنید در سال جدید حقوق کارکنان از 5 میلیون ریال به 6 میلیون و 200 هزار ریال افزایش پیدا کرده باشه و بقیه متغیرها ثابت باشد.

حالا اگر تنها متغیر قابل تعیین توسط شرکت، افزایش تولید ماهانه باشد و بخواهیم ببینیم باید چه مقدار تولید ماهانه شرکت رو افزایش دهیم تا با وجود افزایش حقوق پرسنل سود شرکت مانند حالت قبل برابر 500 میلیون ریال باشه  میتونیم از ابزار Goal Seek اکسل استفاده کنیم. برای اینکار مطابق شکل زیر از تب دیتا گزینه What-If Analysis رو انتخاب می کنیم و در منوی باز شده گزینه Goal Seek رو کلیک می کنیم.

حالا در excelپنجره Goal Seek باز میشه و ما میتونیم مساله خودمون رو در اون تعریف کنیم. گزینه های این پنجره شامل موارد زیر هستند:

Set cell:  در این بخش متغیر وابسته خود راکه به دنبال رسیدن به مقدار مشخصی برای آن هستیم  تعریف می کنیم. در مثال ما این بخش سلول B7 هست که سود ماهانه شرکت رو نشون میده.

To value: در این بخش مقدار مورد نظر رو برای متغیر وابسته تعریف می کنیم. از آنجایی که هدف ما رسیدن به سود ماهانه دوره قبل یعنی 500 میلیون ریال است مقدار این بخش را برابر 500 میلیون قرار میدهیم.

By changing cell: در این بخش متغیری که میخواهیم با تغییر آنن مقدار متغیر وابسته را به عدد موردنظر برسانیم تعریف می کنیم. در این مثال چون ما میخواهیم با تغییر میزان تولید ماهانه سود ماهانه را افزایش دهیم مقدار این گزینه رو برابر سلول B6 قرار میدهیم.

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

اگر حقوق پرسنل افزایش پیدا کنه و مابقی متغیرها ثابت باشه، شرکت می تواند با افزایش تولید و فروش ماهانه از 100 هزار کالا به 118 هزار  کالا، سودی معادل دوره قبل یعنی 500 میلیون ریال  داشته باشد.                  امیدواریم ازاین آموزش اکسل بهره برد باشید