این تابع برای رند کردن اعداد کاربرد دارد .برای مثال فرض کنید عدد ۵۵۳۴٫۶۵۲ را داریم. می خواهیم این عدد را تا دو رقم اعشار رند کنیم. برای این کار تابع ROUND را فرامیخوانیم و در کادر NUMBER این عدد را می نویسیم. در کادر NUM_DIGITS، اگر عدد مثبت وارد شود، تعداد ارقام اعشار را مشخص می کند و اگر عدد منفی نوشته شود اعداد قبل از اعشار را رند میکند (عدد ۱- آخرین رقم قبل از اعشار عدد را صفر میکند، عدد ۲- دو عدد آخر عدد را صفر میکند و.)…
در این مثال برای رند کردن عدد به دو رقم اعشار، عدد ۲ را در این کادر وارد میکنیم.
?
عدد وارد شده پس از کلیک بر گزینه OK بصورت زیر نمایش داده میشود.
?
حال اگر در کادر NUM_DIGITS عدد ۲- قرار دهیم، عدد وارد شده بصورت زیر نمایش داده میشود.
?
پس از انجام محاسبات مربوطه، تعداد فروش در نقطه سربهسر یک شرکت در سلول A2 عدد ۲۱۵۰٫۸۴ نمایش داده شده است. قیمت فروش هر محصول این شرکت ۵۰۰۰ ریال است. میخواهیم در سلول C2 مبلغ فروش در نقطه سربه سر را محاسبه کنیم.
?
چون تعداد ۲۱۵۰٫۸۴ واحد غیر ممکن است و تعداد فروش محصول باید یک عدد صحیح باشد، تصمیم گرفته شده که این عدد به نزدیکترین عدد صحیح گرد شود .اگر روی سلول A2 کلیک کنیم و سپس از زبانه HOME در بخش NUMBER دوبار روی گزینه DECREASE DECIMAL کلیک کنیم، عدد موجود در سلول A2 به ۲۱۵۱ تغییر پیدا میکند.
?
?
حال اگر در سلول C2 فرمول محاسبه مبلغ فروش کل در نقطه سربهسر(A2*B2=) را بنویسیم، خواهیم دید که عدد ۱۰,۷۵۴,۲۰۰ نمایش داده شده است.
?
اما باید توجه شما را به این نکته جلب کنیم که این عدد حاصلضرب از فرمول زیر به دست آمده است:
=۲۱۵۰٫۸۴×۵۰۰۰=۱۰,۷۵۴,۲۰۰
مشاهده میکنید، با اینکه تعداد محصول در نقطه سربهسر ۲۱۵۱ نشان داده میشود، اما در واقع این عدد همان ۸۴/۲۱۵۱ میباشد که فقط ظاهر آن ۲۱۵۱ است. برای اینکه این عدد واقعا به ۲۱۵۱ تغییر پیدا کند و در محاسبات نیز ۲۱۵۱ حساب شود، باید از تابع ROUND استفاده کنیم. برای این کار دوباره به عقب بازمیگردیم.
?
حالا در سلول C2 تابع ROUND را فرامیخوانیم و در کادر NUMBER بجای نوشتن عدد، آدرس سلول A2 را قرار میدهیم و از آنجایی که این عدد باید عددی صحیح باشد، در کادر NUM_DIGITS عدد صفر )به معنی عدد بدون اعشار( را وارد و روی گزینه OK کلیک میکنیم.
?
حالا در سلول C2 بخش اول ضرب را خواهید داشت.
?
اما عدد بدست آمده در سلول C2 باید در مبلغ ۵۰۰۰ ریال ضرب شود. برای ادامه نوشته فرمول در سلول C2 ابتدا آن را فعال کرده و سپس در نوار فرمول با قرار دادن علامت ضرب “*” ادامه فرمول را مینویسیم.
?
حالا با فشردن کلید ENTER نتیجه را مشاهده میکنید.
?
با کمک این تابع میتوان تعداد سلولهای دارای عدد (عددی) در یک منطقه از سلول ها را شمرد. فرض کنید در اکسل همانند تصویر زیر سلولهایی حاوی متن و عدد وجود دارد.
?
میخواهیم در سلول A6 تعداد سلولهای حاوی عدد در این محدوده را شمارش کنیم. برای این کار در این سلول تابع COUNT را از بخش توابع آماری( STATISTICAL) فرامیخوانیم. در کادر VALUE1 محدوده مورد نظر(A1:C4) را با ماوس انتخاب (و یا مینویسیم) و در پایان روی OK کلیک میکنیم.
?
پس از کلیک روی OK خواهید دید در این محدوده ۸ سلول حاوی عدد وجود دارد. همچنین تابع COUNTA تعداد سلولهای غیر خالی را میشمارد.
تفاوتی که این تابع با تابع COUNT دارد این است که این تابع میتواند عمل شمارش را با شرطی که شما به آن میدهید انجام دهد. این تابع دارای دو کادر ورودی اطلاعات به نام RANGE(آدرس منطقه مورد نظر برای شمارش) و CRITERIA (شرط شمارش) است. برای مثال اگر بخواهید در یک منطقه از سلول ها اعداد بزرگتر از ۵ را بشمارید، باید در کادر CRITERIA عبارت “۵<” را وارد کنید.
مبلغ فروش ماهانه یک شرکت در یک سال بصورت زیر است.
?
میخواهیم تعداد ماههایی را که فروش بیشتر از ۶۱ ریال بود را بیابیم. برای این کار تابع COUNTIF را فرامیخوانیم و ورودیهای آن را بصورت زیر وارد میکنیم.
?
مشاهده میکنید فروش ۶ ماه بیش از ۶۰ ریال است. حال برای شمارش تعداد ماههایی که فروش ۶۰ ریال و بیشتر از ۶۰ ریال بود، در کادر CRITERIA این تابع عبارت “۰۶=<” را قرار میدهیم. همچنین تابع COUNTIFS برای شمارش سلولها با چند شرط بکار میرود.
مطلب مفید برای شما : آموزش و لیست توابع مالی در اکسل + مثال و تمرین
این بخش دومین بخش از زبانه FORMULAS است. در این بخش ابزارهایی برای نامگذاری سلول ها و محدوده ها وجود دارد. در تصویر زیر این بخش و ابزارهای درون آن را مشاهده میکنید.
?
اگر بخواهیم برای یک منطقه از سلول ها نامی را انتخاب و در فرمولها از آن نام استفاده کنیم، از ابزار DEFINE NAME (? ) بهره میگیریم .فرض کنید میخواهید منطقه سلولهای A1 تا ۴D را به نام “AAA” نامگذاری کنید. برای این کار روی ابزار DEFINE NAME کلیک کرده و از لیست باز شده گزینه DEFINE NAME را انتخاب میکنیم.
?
در کادر باز شده این ابزار همانند تصویر زیر در قسمت NAME عبارت “AAA” را وارد میکنیم.
?
سپس در قسمت REFERS TO با ماوس منطقه مورد نظر را انتخاب میکنیم.
?
در نهایت گزینه OK را انتخاب میکنیم.
مثال
در ادامه مثال قبل فرض کنید منطقه A1:D4 به نام AAA نامگذاری شده است. اگر در این منطقه اعداد زیر وجود داشته باشند، در سلول F2 جمع این محدوده را محاسبه کنید.
?
برای این کار در سلول F2 تابع SUM را فرامیخوانیم و در بین پرانتز این فرمول عبارت AAA را مینویسیم.
?
با کلیک بر کلد ENTER حاصل جمع این اعداد در سلول F2 نمایش داده میشود.
?
آموزش مرتبط : آموزش تب های Number, Styles, Conditional formatting
فرض کنید در ادامه مثال قبل محدوده AAA یک ماتریس باشد. میخواهیم در سلول F2 دترمینان این ماتریس را محاسبه کنیم. برای این کار ابتدا سلول F2 را فعال کرده و سپس تابع MDETERM را فرامیخوانیم و در کادر ARRAY بجای انتخاب محدوده ماتریس، نام AAA را وارد و گزینه OK را انتخاب میکنیم.
?
?
این ابزار برای مدیریت نامگذاری سلولها بکار میرود. با کلیک بر ابزار NAME MANAGER (? ) پنجره این ابزار بصورت زیر باز میشود. در این کادر تمام محدودههای نامگذاری شده مشاهده میشوند. همچنین گزینه هایی برای حذف و ویرایش آنها وجود دارد.
?
اگر بخواهیم محدوده AAA را ویرایش کنیم و آدرس آن را تغییر دهیم روی گزینه EDIT کلیک میکنیم تا پنجره جدیدی بصورت زیر باز شود.
?
در این پنجره میتوان نام و آدرس محدوده را تغییر داد.
برای حذف یک محدوده نامگذاری شده کافیست ابتدا روی نام محدوده کلیک کرده و سپس گزینه DELETE را انتخاب کنید. در کادر جدید از شما خواسته میشود در صورت اطمینان از حذف محدوده گزینه OK و در غیر اینصورت گزینه CANCEL را انتخاب کنید.
?
برای نام گذاری یک منطقه جدید از گزینه NEW استفاده کنید.
این بخش برای ابزارهایی را برای مشاهده فرمول ها در اختیار ما قرار می دهد. فرض کنید فرمول محاسبه مبلغ قسط وام و اصل و فرع قسط اول را بصورت زیر در اکسل داشته باشید.
?
برای اینکه اکسل به ما نشان دهد در فرمول سلول E1 از چه سلولهای دیگری استفاده شده، گزینه TRACE PRECEDENTS(?) را انتخاب می کنیم.
?
مشاهده میشود که برای محاسبه مبلغ هر قسط در سلول E1 از سلولهای B2 ،B1 و B3 استفاده شده است.
برای پاک کردن این خطوط از گزینه REMOVE ARROWS(? ) استفاده میکنیم.
حال برای اینکه ببینیم سلول E3 در کدام سلولها بکار رفته است، ابتدا روی آن کلیک میکنیم و سپس گزینه TRACE DEPENDENTS(?)استفاده می کنیم.
?
این ابزار برای نمایش فرمول درون سلول ها کاربرد دارد. فرض کنید در مثال قبل میخواهیم فرمول درون سلول ها نمایش داده شود. برای این کار روی گزینه SHOW FORMULAS (?) کلیک می کنیم
برای لغو این حالت کافیست دوباره روی همین گزینه کلیک کنیم.
?
مثال جامع
شرکت گلبرگ قصد سرمایه گذاری در یکی از سه پروژه زیر را دارد. نرخ بازده مورد انتظار شرکت برای این سرمایهگذاریها ۱۲ درصد است. سرمایهگذاری اولیه و عواید پنج ساله این سه پروژه در محیط اکسل همانند زیر در دسترس است.
?
میخواهیم در سلولهای C10 ،B10 و D10 نرخ بازده داخلی این سه پروژه را محاسبه کنیم. برای این کار در سلول B11 تابع IRR را فرامیخوانیم و در کادر VALUES، محدوده اطلاعات سرمایهگذاری پروژه الف (B4:B9) را با ماوس انتخاب و روی OK کلیک میکنیم.
?
با کلیک بر گزینه OK نرخ بازده داخلی پروژه الف در سلول B11 نمایش داده میشود.
?
حالا اگر با ماوس روی مربع توپر این سلول کلیک کنیم و نگهداریم و تا سلول D11 بکشیم و رها کنیم، نرخ بازده داخلی سایر پروژهها نیز محاسبه میشود.
?
?
حالا میخواهیم در زیر سلول نرخ بازده داخلی، در مقابل سلول وضعیت سوددهی شرطی بنویسیم که در صورتی که نرخ بازده داخلی کمتر از نرخ بازده مورد انتظار مدیریت(B1) باشد، عبارت “سودده”، در صورتی که نرخ بازده داخلی بیشتر از نرخ بازده مورد انتظار مدیریت باشد عبارت “زیانده” و در صورتی که نرخ بازده داخلی دقیقا برابر با نرخ بازده مورد انتظار مدیریت باشد، عبارت “بیتفاوت” را نمایش دهد.
برای این کار در سلول B12 تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST باید قسمت اول شرط را بنویسیم (اگر سلول B11 کوچکتر از B1 باشد). این شرط در اکسل بصورت B11<B1 نوشته میشود. اما چون قصد داریم فرمول این سلول را به سلولهای کناری برای سایر پروژهها منتقل کنیم و سلول نرخ بازده مدیریت در هر پروژه یکسان است، سلول B1 را با فشردن کلید F4 ثابت میکنیم.
?
در کادر صحت شرط عبارت “سودده” را مینویسیم.
?
در کادر عدم صحت شرط(VALUE_IF_FALSE) چون دو حالت دیگر از شرط باقی مانده است، یک تابع IF جدید باز میکنیم.
?
در تابع جدید IF در قسمت شرط، حالت دوم را مینویسیم. اگر نرخ بازده داخلی از نرخ بازده مورد انتظار مدیدیت بزرگتر بود را بصورت B11>B1 و چون B1 باید ثابت باشد، روی آن کلید F4 را میفشاریم.
?
در کادر صحت شرط عبارت “زیانده” و در کادر عدم صحت شرط، حالت آخر یعنی “بیتفاوت” را وارد و روی گزینه OK کلیک میکنیم.
?
فرمول موجود در سلول B12 بصورت زیر است.
=IF(B11<$B$1,”سود ده”,IF(B11>$B$1,”زیان ده”۰″بی تفاوت”))
حالا اگر روی مربع توپر کادر سلول B12 کلیک کرده و نگهداریم و تا سلول D12 بکشیم و رها کنیم ،این شرط برای سایر پروژهها هم نمایش داده میشود.
?
حال میخواهیم در سلول B14 نرخ بازده داخلی پروژه منتخب نمایش داده شود. برای این کار باید پروژه دارای کمترین نرخ بازده داخلی از میان پروژههای سودده انتخاب شود. برای این کار ابتدا سلول B14 را فعال کرده و تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST میخواهیم بررسی کنیم که آیا در کل پروژهها، پروژه سود دهی وجود دارد یا نه. برای این کار در این کادر تابع OR را فرامیخوانیم(این تابع بررسی میکند که حداقل یکی از شروط وارده صحت دارند یا نه). در کادر پنجره OR همانند تصویر زیر شروط را وارد میکنیم.
?
در تابع OR در حال بررسی وجود حداقل یک گزینه سودده در میان پروژهها هستیم. حال قبل از کلیک بر OK در نوار فرمول روی تابع IF کلیک میکنیم تا به تابع IF بازگردیم.
?
حالا در ادامه تابع IF در کادر VALUE_IF_TRUE برای پیدا کردن کمترین نرخ بازده داخلی، تابع MIN را فرامیخوانیم. در کادر NUMBER1 این تابع منطقه هر سه نرخ بازده داخلی(B11 تا D11) را با ماوس انتخاب میکنیم.
?
حالا قبل از کلیک بر گزینه OK روی نوار فرمول روی تابع IF کلیک میکنیم تا دوباره به تابع IF بازگردیم.
?
حالا در کادر VALUE_IF_FALSE باید عبارتی را که در هنگام زیانده بودن همه پروژهها نمایش داده شود را بنویسیم. در این کادر عبارت “همه پروژها زیانده هستند” را مینویسیم و روی OK کلیک میکنیم.
?
فرمول موجود در سلول B14 بصورت زیر است.
=IF(OR(B12=”سود ده”,C12=”سود ده”,D12=”سود ده”),MIN(B11:D11),”همه پروژها زیانده هستند”)
?
امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش رند کردن اعداد، عمل شمارش، عمل شمارش شرطی بود، لذت برده باشید. نظرات خودتون رو برای ما کامنت کنید تا ما بتونیم هر چه بیشتر از اونها واسه بهتر شدنه تیممون استفاده کنیم.
