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

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

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

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

در این پنجره ی جدید، می توانید انتخاب کنید که داده ها چگونه مرتب شوند. از آنجایی که قصد داریم داده های یک ردیف مرتب شوند لذا گزینه ی Sort left to right را انتخاب کرده و سپس بر روی دکمه ی OK کلیک کرده تا به پنجره قبلی بازگردانده شوید. 

در پنجره قبلی سه ستون به نام های ردیف (Row)، مرتب سازی بر اساس (Sort On)، ترتیب (Order) را مشاهده خواهید کرد. منوی کشویی اول یعنی Row را باز کرده و ردیفی که قصد دارید داده های آن را مرتب کنید انتخاب نمایید. اکنون با استفاده از دو منوی کشویی دیگر یعنی Sort On و Order نوع مرتب سازی را مشخص کنید. شما می توانید داده ها را بر اساس ارزش، رنگ فونت، رنگ سلول و آیکون سلول مرتب نمایید

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

ترکیب کردن رشته های متنی یکی از رایجترین کارها برای کاربران اکسل است اما تا به امروز اگر شما می خواستید رشته های متنی موجود در یک محدوده ای از سلول ها را ترکیب کنید مجبور بودید تک تک سلول ها را به تنهایی انتخاب کنید. توابع جدید CONCAT و TEXTJOIN به شما اجازه می دهند رشته های متنی موجود در محدوده ای از سلول ها (چه با استفاده از جداگرها همانند کاما و چه بدون استفاده از آنها) را با یکدیگر ترکیب کنید. شما می توانید به سادگی اشاره کنید به محدوده تان و جداگرتان را یکبار انتخاب کرده و به اکسل اجازه دهید تا همه­ ی این بار سنگین را بلند کند.

با استفاده از جداگرها

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  بدون استفاده از جداگرها

CONCAT(text1, [text2],…)                                                                                                                                                                

• IFS
• SWITCH

 توابع IFS و SWITCH در اکسل جایگزینی برای فرمول نویس های تو در تو یا همان IF  تو IF  هستند. تابع IF  یکی از رایجترین توابع در اکسل است و استفاده ی تو در تو از این تابع یکی از کارهای متداول کاربران است که در برخی موارد بسیار خسته کننده و گیج کننده است.

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

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

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

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

• MAXIFS
• MINIFS

اگر شما با COUNTIFS, SUMIFS و AVERAGEIFS آشنا هستید پس MAXIFS و MINIFS نیاز به هیچ توضیحی ندارند. توابع MAX و MIN، ماکزیمم و مینیمم مقادیر را درون یک محدوده ای از سلول ها مشخص می کنند ولی اگر شما نیاز داشته باشید که این محاسبات در یک شرایط خاص انجام بشود باید چه کنید؟ این دقیقا همان کاری است که MAXIFS و MINIFS انجام می دهند. شما می توانید شرایطتان را قبل از این که محاسبات انجام شود مشخص کنید.

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

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

ابزار Goal Seek در اکسل

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

روش استفاده از ابزار Goal Seek در اکسل:

فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)

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



➊ سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است:

=AVERAGE(B2:B6)

➋ در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

➍ وقتی این کارها را انجام دادید ok کنید.



➎ اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.



➏ نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

روش استفاده از ابزار Goal Seek در اکسل :

فرض کنیم  مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2+B3*B4

➊ ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.



➋ در تب Data، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

➍ وقتی کارتان تمام شد ok کنید.



➎ اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر می دهد. ok کنید.

➏ نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

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

سایر انواع what-if analysis در اکسل

در پروژه های خیلی پیشرفته تر، شما ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

• Scenarios: این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
  
  
• Data tables: این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  
  

فیلترها (Filters) در PivotTable اکسل

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

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

➊ یک فیلد را از قسمت فیلدها بکشید و در ناحیه فیلترها رها کنید. در این مثال ما از فیلد Salesperson استفاده می کنیم.



➋ فیلتر در بالای قسمت PivotTable ها نمایان می شود. ابتدا کادر مربوط به فیلتر را باز کنید و سپس گزینه Select Multiple Items را تیک بزنید.



➌ حالا تیک مواردی را که نمی خواهید در PivotTable مشاهده شوند را بردارید. در این مثال ما چند تا از فروشندگان را از حالت انتخاب خارج کرده ایم، در پایان OK کنید.



➍ جدول PivotTable خودش را تنظیم می کند و تغییرات ایجاد شده را منعکس می کند.

برش دهنده ها (Slicers) درexcel

برش دهنده ها (Slicers) فیلتر کردن داده ها در PivotTables را از این هم ساده تر می کنند. برش دهنده ها در واقع همان فیلترها هستند، اما ساده تر هستند و استفاده از آنها سریعتر است، و به شما امکان می دهند تا فورا داده های خود را بچرخانید. اگر شما مکررا داده های PivotTables را فیلتر می کنید، ممکن است بخواهید که از برش دهنده ها استفاده کنید.

روش افزودن یک slicer (برش دهنده) در اکسل

➊ ابتدا یک سلول را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Analyze بر روی دستور Insert Slicer کلیک کنید.


➌ یک کادر باز می شود. فیلدهای مورد نظرتان را تیک بزنید و سپس ok کنید. در این مثال ما فیلد Salesperson را تیک می زنیم.



➍ برش دهنده (slicer) در کنار PivotTable نمایان می شود. مواردی را که انتخاب کرده اید به رنگ آبی نمایش داده می شوند. در این مثال، برش دهنده شامل تمامی 8 فروشنده ما می باشد، اما 5 تایی که در حالت انتخاب هستند با رنگ آبی متمایز شده اند.



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

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

نمودارهای چرخشی (PivotCharts) در اکسل

نموارهای چرخشی (PivotCharts) دقیقا مانند نمودارهای معمولی هستند، با این استثنا که آنها داده های یک PivotTable را نمایش می دهند. درست مانند نمودارهای معمولی، شما می توانید از ویژگیهای نوع نمودار، سبک نمایش نمودار و... بهره مند گردید.

روش ایجاد یک PivotChart در اکسل

➊ ابتدا سلولی را به دلخواه در PivotTable انتخاب کنید.

➋ در تب Insert بر روی دستور PivotChart کلیک کنید.



➌ صفحه Insert Chart نمایان می شود. ابتدا نوع نموارد (chart type) و همینطور سبک نمایش (layout) مورد نظرتان را انتخاب کنید.



➍ نمودار PivotChart نمایان می شود.

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

ابزار Goal Seek در اکسل

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

 استفاده از ابزار Goal Seek در اکسل

فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

در تصویر زیر شما نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)

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



➊ سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است:

=AVERAGE(B2:B6)

➋ در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

➍ وقتی این کارها را انجام دادید ok کنید.



➎ اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.



➏ نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

روش استفاده از ابزار Goal Seek در اکسل

فرض کنیم شما مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2 B3*B4

➊ ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.



➋ در تب Data، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.



➌ یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

➍ وقتی کارتان تمام شد ok کنید.



➎ اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر می دهد. ok کنید.

➏ نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

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

 انواع what-if analysis در اکسل

در پروژه های خیلی پیشرفته تر ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

• Scenarios: این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
  
• Data tables: این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.
  

پایگاه داده یک سیستم کامل دیتابیس است که توسط شرکت مایکروسافت ارائه می گردد.  SQL Server یک پایگاه داده است که شا می توانید به عنوان یک مخزن داده از آن برای وب سایت، برنامه و … استفاده کنید.

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

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

کپی کردن جدول SQL در Excel

1- ابتدا SQL Server Management را باز کنید و به سرور دیتابیس خود متصل شوید.

2- سپس بر روی جدول مورد نظر کلیک راست کرده و گزینه Select Top 1000 Rows را انتخاب نمایید.

3- با کلیک بر روی گوشه سمت چپ جدول تمام داده‌ها را انتخاب نمایید.

4- بر روی جدول کلیک راست کرده و گزینه Copy را انتخاب نمایید.

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

5- سپس یک سند اکسل ایجاد نمایید.

6- بر روی آن کلیک راست کرده و گزینه Paste را همانند تصویر انتخاب نمایید.

برای Paste کردن در فایل اکسل، می توانید از کلیدهای ترکیبی Ctrl + V استفاده نمایید.

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

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

امیدواریم آموزش برای شما مفید واقع شده باشد.

CTRL+A

کل worksheet ای که در آن فعالیت می‌کنید را انتخاب می‌نماید.

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

CTRL+B

فرمت ضخیم شدن متن یا همان بولد شدن را اعمال کرده یا حذف می‌کند.

CTRL+C

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

CTRL+D

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

CTRL+F

دایالاگ باکس Find and Replace را برای جستجو و یا جایگزین کردن سلول‌ها نمایش می‌دهد. در حالت پیش فرض تب Find فعال است.

SHIFT+F5 نیز عمل CTRL+F را انجام می‌دهد. همچنین با ترکیب SHIFT+F4 می‌توان به جستجوی آخرین کلمه یا حروفی که در قسمت Find Wath  وارد کرده بودیم، بدون ظاهر شدن دایالاگ باکس Find and Replace، پرداخت.

CTRL+SHIFT+F نیز دایالاگ باکس فرمت سلول‌ها را باز می‌کند که تب پیش‌فرض Font در آن باز می‌باشد.

CTRL+G

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

F5 نیز عمل CTRL+G را نمایش می‌دهد.

CTRL+H

دایالاگ باکس Find and Replace، با تب پیش‌فرض Replace،  نمایش می‌دهد.

CTRL+I

فرمت کج نوشتن حروف یا ایتالیک را اعمال یا حذف می‌نماید.

CTRL+K

دایالاگ باکس Insert Hyperlink را جهت قرار دادن لینک در فایل نمایش می‌دهد.

CTRL+N

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

CTRL+O

دایالاگ باکس Open را جهت باز کردن فایل ایجاد می‌کند.

با ترکیب CTRL+SHIFT+O نیز تمام سلول‌هایی که کامنت دارند انتخاب می‌شوند.

CTRL+P

دایلوگ باکس Print را جهت چاپ پرونده، نمایش می‌دهد.

CTRL+SHIFT+P نیز دایالاگ باکس Format Cells، با تب پیش‌فرض Font جهت تغییر فونت سلول‌ها باز می‌شود.

CTRL+R

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

CTRL+S

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

CTRL+T

دایالاگ باکس Create Table جهت ایجاد جدول نمایش داده می‌شود.

CTRL+U

توسط این شرتکات تعیین می‌کنید که زیر متن خط کشیده شود یا خیر.

CTRL+SHIFT+U نیز Formula Bar که همان نوار بالای صفحه و در مقابل fx است را جهت نمایش بهتر فرمول‌ها بزرگ و کوچک می‌کند.

CTRL+V

هنگامی که قصد paste کردن داده‌های مبدأ را داریم، از این شرتکات استفاده می‌شود.

با ترکیب CTRL+ALT+V دایالاگ paste Special برایتان باز می‌شود که توسط آن می‌توانید روش Paste شدن داده‌ها را معین کنید. مثلا با زدن Column Width می‌گویید عرض ستون‌ها هنگام Paste،  همانند مبدأ باشد.

CTRL+W

در یک لحظه احتمالاً چندین محیط کاری یا همان Sheet در اکسل باز است. با زدن CTRL+W می‌توانید محیط کاری فعلی را ببندید.

CTRL+X

ناحیه‌‌ی انتخابی کات می‌شود تا برای paste  شدن در مقصد آماده باشد. در این حالت بر خلاف Copy در مبدأ چیزی باقی نمی‌ماند.

CTRL+Y

آخرین عملی که انجام داده‌ایم تکرار می‌‌شود (البته اگر ممکن باشد). فرض کنید در لحظه‌ای که فایلی جدید تشکیل می‌دهید کاری روی پرونده انجام نداده‌اید و آخرین عمل‌های خود را با undo کردن یا ترکیب CTRL+Z پاک نکرده‌اید. در این صورت تکرار عمل آخر معنی نخواهد داشت.

 CTRL+Z

اثر آخرین عمل انجام شده از بین می‌رود.در واقع یک مرحله به عقب باز می‌گردد.

یکی از قابلیتهای قوی نرم افزار اکسل قابلیت جستجو و جایگزین نمودن متن است. با استفاده از این قابلیت شما به آسانی می توانید متن یا عددی را در یک صفحه یا بین چندین صفحه از فایل اکسل را جستجو می نماید یا می توانیم یک متن یا عدد و... را جستجو نماییم و بعد آن را با متن یا... دلخواه جایگزین نماییم.
با استفاده از ترکیب کلید Ctrl+F نیز می توانید وارد Find & Replace گردید.
در قسمت Find what موضوعی را که می خواهیم جستجو کنیم، تایپ می کنیم.
در صورتیکه برروی Format کلیک کنید پنجره Format Cell باز خواهد شد. و در این پنجره هرنوع فرمتی که شما قصد دارید جستجو کنید را مشخص می کنید. حتی در این قسمت شما قادر خواهید رنگ سلول را نیز جستجو کنید.
در قسمت Within نوع جستجو را تعیین می کنیم اگر Sheet را انتخاب کنیم جستجو فقط در همان کاربرگ صورت می گیرد ولی اگر Workbook را انتخاب کنیم جستجو در کلیه کاربرگهای اکسل صورت می گیرد.
در قسمت Search مشخص می کنید که عملیات جستجو در ردیفها صورت گیرد یا در ستونهای اکسل. به عبارت دیگر وقتی By Rows را فعال کنید جستجو به ترتیب ردیفها صورت می گیرد و هنگامی که جستجو در ردیف اول صورت گرفت، اکسل به دنبال ردیف دوم می رود ولی هنگامی که By Columns را فعال کنید عملیات جستجو ستون به ستون صورت می گیرد.
در قسمت Look in مشخص می کنید که عملیات جستجو در فرمولها، مقادیر عددی و یا در کامنتهای اکسل صورت گیرد.
قسمت Match case به کوچک و بزرگ بودن حروف حساس است.
اگر قسمت Match entire cell contents را فعال نکنیم جستجو در موارد مشابه نیز صورت می گیرد ولی اگر فعال کنیم دقیقاً همان چیزی را جستجو می کنیم پیدا خواهد کرد.
به عنوان مثال اگر کلمه "حساب" را جستجو کنید اگر گزینه فوق را فعال نکنید اکسل کلماتی از قبیل "حسابداری"، حسابرسی" و "حساب" را پیدا خواهد کرد اما در صورت فعال نمودن گزینه گفته شده فقط کلمه "حساب" پیدا خواهد شد.
و اما چند نکته هنگام جستجو:
جهت جستجو می توانید از از 2 علامت (*) و (؟) استفاده کنید. ستاره به معنی هر تعداد کاراکتر و علامت سؤال به معنی یک کاراکتر می باشد.
مثال:
حساب* در این حالت اکسل کلماتی که با حساب شروع می شود و ادامه آن هرتعداد کاراکتر باشد را پیدا خواهد کرد.
حساب؟ در این حالت اکسل کلماتی که با حساب شروع می شود و ادامه آن فقط دو کاراکتر دیگر خواهد بود را پیدا خواهد کرد این کلمه می تواند کلمه حسابرس باشد.
کلیه موارد Replace شبیه به Find عمل می کند.

قالب بندی خانه ها در Excel

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

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

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

مثال برای این موارد زیاد می باشد اما بهتر است به سراغ تنظیمات قالب بندی خانه ها برویم

. برای قالب بندی خانه ها در Excel مراحل زیر را طی می کنیم:

ابتدا محدوده مورد نظر خود را  که دارای یک فرمت یکسان هستند در اکسل انتخاب می  نماییم به طور مثال در یک کاربرگ فاکتور محدوده تعداد کالا را انتخاب نموده ایم.

سپس با یکی از روش های زیر عمل می نماییم.

روش اول:

1- انتخاب منوی Format از تب home
2- انتخاب گزینه Cells format

روش دوم:

بر روی خانه های مورد نظرکلیک راست کرده و از منوی باز شده گزینه Format Cells را انتخاب می کنیم.

روش سوم:

فشردن کلیدهای ctrl +1

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

و به نسبت داده درون سلول یکی از فرمت های عددی ذیل را انتخاب و تنظیم می نماییم.

• General

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

• Number 

   

  یا عدد مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود. همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم:

  1. – Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. – Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود.یا به رنگ قرمز.یا سیاه با علامت منفی. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)

  3. – Use 1000 Separator: با انتخاب این گزینه اعداد وارد شده از سمت راست سه رقم سه رقم با علامت کاما (,) جدا می نماید 

      

• Currency: مقادیر را همراه با سمبل پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد 

  1. Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.

  2. Symbol: نوع واحد پول را مشخص می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.

  3. – Negative Number: اعداد منفی چطور نمایش داده شوند

      

• Accounting: مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته می شود. تفاوت عمده آن با currency در این است که بخش Negative Number در این فرمت وجود ندارد چون در حسابداری ماهیت اعداد هستند که باعث کاهندگی آن ها می شود نه منفی بودن آنها به طور مثال هزینه ها اعداد مثبتی هستند که بخاطر ماهیت آنها از درآمد کسر می شوند نه بخاطر منفی بودن آنها

   

• Date: تاریخ را با قالب بندی خاص تاریخ نشان می دهد. و ما می توانیم انواع قالب بندی های تاریخ را دیده و انتخاب کنیم.

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

• انتخاب منطقه جغرافیایی یا location وجود دارد و با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردد

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت تاریخی در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید 

   

6. Time: زمان را با قالب بندی های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد

   در این قالب بندی نیز 2 امکان در دسترس می باشد.

• انتخاب منطقه جغرافیایی یا location  که با انتخاب منطقه جغرافیایی مورد نظر فرمتهای مختلف در بخش بعدی فعال می گردند

• در بخش TYPE با انتخاب منطقه جغرافیایی در بخش location انواع فرمت زمان  در این بخش فعال شده و به دلخواه می توانید یکی از آنها را انتخاب نمایید 

  
  7- Percentage: عددها را همراه با علامت درصد نشان می دهد.

باامکان انتخاب تعداد اعشار در بخش decimal places

نکته

در تمام قالب بندی ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.

7. Fraction: مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.

    

8. Scientific: مقادیر را با قالب بندی علمی نشان می دهد

   باامکان انتخاب تعداد اعشار در بخش decimal places

    

8. Text: مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.

    

10. Special

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

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

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

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

در حالت پایه، صفحات اکسل از سمت چپ به راست است. یعنی اگر به ستون های اکسل توجه کنید، حروف A و B و C و … از سمت چپ به راست است(1). اعداد سطرهای شما در سمت چپ صفحه قرار دارند (2). صفحات کاری شما در سمت چپ صفحه قرار دارند و ترتیب آنها از چپ به راست است(3). همچنین اگر در صفحه کاری از دکمه Tab استفاده کنید سلول  انتخابی شما سمت چپ به راست حرکت می کند.

اما نکته مهم در این است که زبان فارسی از راست به چپ است و ما عادت داریم صفحات را از سمت راست به چپ ببینیم. برای اینکه جهت صفحات کاری خود در اکسل را تغییر بدهید کافی است به ریبون page layout بروید و گزینه sheet right to left را انتخاب کنید. در این صورت صفحات کاری شما در اکسل از راست به چپ خواهید شد.

بعد از ایجاد این تغییر در اکسل ستونی های شما از راست به چپ خواهد شد (1). اعداد سطرهای اکسل شما در سمت راست صفحه قرار می گیرد (2). صفحات کاری اکسل شما در سمت راست صفحه قرار دارند و ترتیب آنها از راست به چپ است(3). همچنین اگر از Tab استفاده کنید حالت انتخاب سلول از سمت راست به چپ حرکت خواهد کرد.
 

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

تغییر تنظیمات پیش فرض برای تنظیمات راست به چپ شدن برگه ها و سندهای جدید در اکسل

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

File –> option–> advance –> display –> right to left

در اکسل خود به ریبون File بروید (1). گزینه option را انتخاب کنید (2). از منوی سمت چپ گزینه advance را انتخاب کنید (3). در سمت راست پنجره به بخش display بروید و گزینه right to left را انتخاب کنید (4).

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

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