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

در این مقاله 14 فرمول و تابع ساده و کاربردی اکسل را با ذکر مثال بررسی می کنیم.

  • توابع UPPER , LOWER , PROPER

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

مثال:

میخواهیم اثر توابع فوق را روی رشته متنی “miCRoSoFt ExcEl SoftWare ” ببینیم

=UPPER(“miCRoSoFt ExcEl SoftWare ” )                          MICROSOFT EXCEL SOFTWARE

همانطور که می بینید تابع UPPER تمام حروف را به صورت حروف بزرگ نمایش می دهد

=LOWER(“miCRoSoFt ExcEl SoftWare ” )                          microsoft excel software

تابع LOWER تمام حروف را به صورت کوچک نمایش می دهد

=PROER(“miCRoSoFt ExcEl SoftWare ”  )                         Microsoft Excel Software

تابع PROPER حروف اول هر کلمه را به صورت بزرگ و بقیه حروف را به صورت کوچک نمایش می دهد.

  • حذف فاصله های اضافی توسط تابع Trim (با این تابع در اتوکد می توان قسمت های اضافی نقشه را حذف کرد)

اغلب هنگام کپی اطلاعات از منابع دیگر فضاهای خالی زیادی قبل و یا بعد از داده ها بوجود می آیند.

یک نمونه از این اتفاق هنگام سورت کردن اسامی دیده می شود، با وجود آنکه به ظاهر همه اسامی درست نوشته شده اند اما هنگام sort کردن می بینیم اسمی که باید در اول لیست قرار داشته باشد در جای درست خود قرار ندارد و ما فکر می کنیم تنظیمات اکسل به هم ریخته و باید آفیس را مجدد نصب کنیم!!

مثال:

رشته متنی ” Microsoft   Excel   Software     ” را در نظر بگیرید

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

 =trim(” Microsoft   Excel   Software     “)             Microsoft Excel Softwareنتیجه تابع

  • جداسازی قسمتی از یک متن از سمت راست، چپ یا وسط با استفاده از توابع RIGHt,LEFT,MID

مثال:

می خواهیم تعداد 5 کاراکتر (حرف) از رشته متنی Microsoft Excel Software  را جدا کنیم

=RIGHT(“Microsoft Excel Software”,5)                         tware نتیجه

=LEFT(“Microsoft Excel Software”,5)                            Micro نتیجه

=MID(“Microsoft Excel Software”,11,5)                         Excel نتیجه

تذکر: برای تابع MID باید مکان شروع جداسازی و تعداد کاراکترهای مورد نظر را انتخاب کنیم

در این مثال عدد 11 یعنی اینکه از مکان یازدهم شروع کن و تعداد 5 حرف را جدا کن. هر فاصله خالی نیز یک کاراکتر در نظر گرفته می شود.

برای توضیح کامل توابع متنی به لینک زیر مراجعه نمایید

توابع متنی اکسل

  • یافتن دومین، سومین، چهارمین و n امین عدد کوچک (بزرگ) در یک رشته از اعداد بدون مرتب سازی

می دانیم که برای یافتن کوچکترین و بزرگترین عدد در یک لیست می توانیم از توابع MIN() و MAX() استفاده کنیم اما اگر بخواهیم مثلاً سومین عدد کوچک را از لیست زیر پیدا کنیم از توابع SMALL() استفاده می کنیم

مثال:

سومین عدد کوچگ در لیست اعداد زیر کدام است؟

10,9,12,14,26,13,4,6,8

=SMALL({10,9,12,14,26,13,4,6,8},3)                     نتیجه عدد 8

برای یافتن  nامین عدد بزرگ از تابع LARGE استفاده کنید

شکل 1

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

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

 

  • نمایش تاریخ و یا ساعت فعلی در اکسل

از تابع  () today برای نمایش تاریخ فعلی (امروز) استفاده کنید.

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

از تابع NOW() برای نمایش تاریخ و زمان فعلی (امروز) استفاده کنید.

برای این کار از کلید میانبر Shift+Ctrl+; نیز می توانید استفاده کنید

  • تبدیل if های تو در تو پیچیده به تابع ساده CHOOSE

اگر تابعی دارای بیش از یک شرط باشد به جای استفاده از IF های تودر تو بهتر است از تابع CHOOSE استفاده کنیم

مثال:

فروردین, اردیبهشت, خرداد, تیر, مرداد, شهریور

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

با استفاده از تابع IF به شکل زیر عمل می کنیم

=IF(H1=1,”فروردین”,IF(H1=2,”اردیبهشت”,IF(H1=3,”خرداد”,IF(H1=4,”تیر”,IF(H1=5,”مرداد”,IF(H1=6,”شهریور”,””))))))

 

با استفاده از تابع CHOOSE به این صورت عمل می کنیم

=CHOOSE(3,فروردین,اردیبهشت,خرداد,تیر,مرداد,شهریور)

نتیجه تابع “خرداد” می باشد

اگر به جای 3 عدد 5 را قرار دهید پنجمین عنصر رشته یعنی “مرداد” را نمایش می دهد

  • تابع ()REPT تایپ یک کاراکتر به تعداد دلخواه در یک سلول

این تابع یک رشته متنی را به تعداد مشخص شده تکرار می کند

فرض کنید بخواهیم علامت “|” را به تعداد 15 مرتبه تکرار کنیم. برای این منظور از تابع ()REPT استفاده می کنیم

شکل زیر مثال ساده ای از کاربرد این تابع را نمایش می دهد.

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

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

 

  • تابع ()TYPE مشخص کردن نوع داده داخل سلول ها

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

مثال: TYPE(“Excel”)نتیجه این تابع عدد 2 خواهد بود و منظور این است که کلمه Excel متن است

مقادیر به شرح زیر هستند.

Number            1

Text                 2

Logical Value     4

Error Value       16

Array                64

  • گرد کردن یک عدد به نزدیکترین عدد زوج یا فرد توابع ()ODD() – EVEN و ()INT

 

مثال: =ODD(63.4)   نتیجه عدد 65 است

مثال: =EVEN(62.4) نتیجه عدد 64 است

مثال: (INT(62.99= نتیجه عدد 62 می باشد. (فقط قسمت صحیح عدد را نگه می دارد)

برای تبدیل عدد کسری به نزدیک ترین عدد صحیح از تابع ()ROUND استفاده کنید

مثال: (ROUND(63.4,0= عدد 63 می باشد

  • تابع ()RANDBETWEEN

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

مثال: برای تولید اعداد تصادفی بین 10 و 100  (RANDBETWEEN(10,100=

اگر مرتباً صفحه را Refresh کنید ممکن است به عدد 75 برسید!

  • تبدیل متر به فوت، پوند به کیلوگرم و …

برای تبدیل واحد نیازی نیست که به سراغ گوگل بروید!

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

مثال: برای تبدیل متر به فوت ( CONVERT(1,”m”,”ft”=

مثال (CONVERT(150,”lbm”,”kg” =  تبدیل 150 پوند به کیلوگرم. نتیجه عدد 68.038 خواهد بود

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

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

 

  • محاسبه قسط وام

مثال:

فرض کنید قصد خرید ماشین دلخواه خود را دارید و می خواهید یک وام به مبلغ 25 میلیون تومان با نرخ بهره 16% سالیانه و مدت بازپرداخت 3 سال (36 ماه) را دریافت کنید.

ماهانه باید چقدر قسط پرداخت کنید؟

برای محاسبه مبلغ اقساط از تابع ()PMT استفاده کنید

=PMT(rate,nper,pv,fv,type)

=PMT(0.16/12,3*12,25000000)

نتیجه تابع عدد 878926 می باشد.

مقادیر FV و Type را اگر خالی بگذارید به طور پیش فرض محاسبه می شوند

  • این هفته چندمین هفته سال است؟

برای آنکه بفهمیم امروز چندمین هفته سال است از این تابع استفاده می کنیم

=WEEKNUM(TODAY())

نتیجه این تابع در تاریخ نوشتن این مقاله عدد 3 می باشد

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

  • 30 روز دیگر از امروز چه تاریخی است؟

پیدا کردن جواب این سئوال بسیار آسان است. برای پیدا کردن آن اکسل تابعی دارد به نام ()WORKDAY

مثال:

=WORKDAY(TODAY(),30)

نتیجه تابع 43158 می باشد (مبنا تاریخ نوشتن این مقاله است). این عدد شاید نامفهوم باشد. فرمت سلول را به تاریخ تغییر دهید به تاریخ 2018/27/2 می رسید.

مثال:

278 روز دیگر از امروز چه تاریخ است؟

=WORKDAY(TODAY(),278)

جواب 2019/8/2

 

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