توابع پایه اکسل - کار با توابع در فرمول نویسی

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

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

 فرمول هر عبارتی است که با علامت تساوی (=) شروع می شود.

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

در بیشتر موارد ، توابع دارای نام‌هایی هستند که کاربرد مورد نظر آنها را نشان می‌دهد. برای مثال ، احتمالا تابع SUM را می‌شناسید ، که مجموع مقادیر آدرس‌های داده شده را برمی‌گرداند:

= SUM (1;2;3)   //  مقدار 6 را بر می‌گرداند

= SUM (A1:A3)   // را باز می‌گرداند A1+A2+A3حاصل جمع 

تابع AVERAGE  همانطور که انتظار دارید، میانگین آدرس‌های داده شده را برمی گرداند:

= AVERAGE (1;2;3)   //   مقدار 2 را باز می‌گرداند

تابعMIN و تابع MAX به ترتیب مقادیر حداقل و حداکثر را بر می‌گردانند:

= MIN (1;2;3)   //  مقدار1 را باز می‌گرداند 

= MAX (1;2;3)   // مقدار 3 را باز می‌گرداند

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

دسته‌بندی توابع

Most Recently Used: لیست توابع پر کاربرد اکسل و توابعی که در دفعات گذشته از آنها استفاده شده است.

All: این دسته شامل تمامی توابع اکسل می‌باشد.

Financial: لیست تمامی توابع مربوط به محاسبات مالی و حسابداری ، شامل تابع محاسبه نرخ بهره استهلاک باز پرداخت‌های وام و … 

Data & Time: توابع تاریخ و ساعت ، همانطور که از نام آن مشخص است ، جهت ارزیابی داده‌های تاریخ و ساعت ، از جمله محاسبه اختلاف دو تاریخ و... مورد استفاده قرار می‌گیرند. 

Math & Trig: یکی از بزرگترین دسته بندی توابع اکسل است که به محاسبات و توابع ریاضی مربوط می‌شود.

Statistical: توابع آماری جهت ارزیابی توزیعهای آماری.

Lookup & Refrence: توابع این دسته برای جستجوی یک مقدار در برگه اکسل و بازگرداندن آدرس یک سلول و یا محدوده ، مورد استفاده قرار می‌گیرد.

Data Base: توابع ارزیابی مجموعه‎ای از اطلاعات در فرمت دیتا‌بیس (هر رکورد در یک سطر و هر ویژگی در یک ستون)

Text: توابع این دسته برای کار با رشته‌های متنی مورد استفاده قرار می‌گیرند.

Logical: توابع منطقی ، که شرطی را بررسی می‌کنند و خروجی آنها عمدتا به صورت True و False می‌باشد.

Information: توابعی که در مورد برگه اکسل ، فایل اکسل و ویژگی‌های سلول‌ها  (شناسایی محتویات سلول و تعیین هرگونه خطا یا ...) اطلاعاتی به کاربر می‌دهد.

User Defined: لیست توابعی که توسط کاربر در محیط VBA (محیط برنامه‌نویسی اکسل) نوشته می‌شود. در حالت عادی ، تا زمانی که کاربر تابع جدیدی تعریف نکند این لیست وجود ندارد.

Engineering: گزیده‌ای از توابع معروف و پرکاربرد که در رشته‌های مهندسی مورد استفاده قرار می گیرد.

Cube: توابعی که در تحلیل‌های تحت شبکه مورد استفاده قرار می‌گیرد.

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

Web: تعدادی از توابع که جهت به اشتراک گذاری اطلاعات در شبکه مناسب است.

مولفه‌های تابع

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

(مولفه‌ها) نام تابع

به عنوان مثال ، تابع COUNTIF تعداد سلول‌هایی را می‌شمارد که معیاری را برآورده می‌کنند و دو مولفه محدوده (range) و معیار (criteria) را به عنوان ورودی دریافت می‌کند: 

= COUNTIF (range;criteria)   //   دو مولفه دارد

در مثال زیر، محدوده A1:A5 و معیار "قرمز" است. فرمول سلول C1 به این صورت است:

= COUNTIF (A1:A5;"قرمز")   //   مقدار 2 را باز می‌گرداند

توجه داشته باشید که درج مقدار برای اکثر مولفه‌ها اجباری است ، اما برخی مولفه‌ها نیز اختیاری هستند. مولفه‌های اختیاری با کروشه ([ ]) مشخص می‌شوند.

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

= YEARFRAC (start_date;end_date;[basis])

مولفه‌های Start_date  و end_date مولفه‌های اجباری هستند، مولفه basis مولفه‌ای اختیاری است.

در ادامه مثالی از نحوه محاسبه سن با استفاده از تابع YERFRAC را می‌توانید مشاهده کنید.

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

به عنوان مثال ، تابع COUNTIFS جفت‌های " محدوده و معیار " چندگانه و اختیاری می‌پذیرد که ترکیب تابع به صورت زیر است:

= COUNTIFS (range1 ; criterial1 ; [range2 ; criterial2] ; … )

این بدان معنی است که می‌توانید به صورت اختیاری مولفه‌های اضافی را به صورت جفت اضافه کنید: range4 ; criteria4 / range3 ; criteria3 و غیره.

برخی از توابع نیازی به مولفه ندارند. برای مثال ، تابع TODAY تاریخ میلادی روز جاری را بدون هیچ ورودی بر می‌گرداند:

= TODAY()

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

= TODAY( ) + 30

جداکننده مولفه‌های تابع

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

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

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

به عنوان مثال تابع SUM در ایالات متحده به این صورت وارد می شود:

= SUM ( A1 , C1 , E1)

و در ایتالیا نیز به صورت زیر درج می‌شود:

= SUM ( A1 ; C1 ; E1)

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

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

پنجره پیام خطای فرمول ، با جداکننده اشتباه

اگر بخواهید فرمولی را با جداکننده اشتباه وارد کنید ، با پنجره خطایی مواجه می‌شوید که می‌گوید "مشکلی با این فرمول وجود دارد" و یا پیامی شبیه به این.

توجه داشته باشید که در این پنجره پیام ، چیزی در مورد جداکننده اشاره نمی‌شود :)

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

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

از کنترل پنل ( Control Panel) ویندوز بخش Region and Language را باز کنید.

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

در پنجره باز شده زبانه‌ای به نام Numbers وجود دارد. در این زبانه سراغ List separator بروید.

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

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

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

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

در اکسل به این تنظیمات از مسیر زیر می‌توانید دسترسی داشته باشید:

Options > Advanced > Use System separators 

نحوه وارد کردن تابع در فرمول‌نویسی

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

ا) نام تابع و ترکیب صحیح مولفه‌های آن را بدانید و مستقیما در فرمول تایپ کنید.

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

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

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

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

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

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

محدوده مورد نظر را برای مولفه اول انتخاب و پس از درج جداکننده سراغ مولفه دوم بروید.

پس از تکمیل مولفه‌ها Enter را برای تأیید فرمول فشار دهید.

2) روش دیگر برای فراخوانی تابع ، چنانچه بدانیم تابعی که به دنبالش هستیم جزء کدام دسته از توابع است استفاده از کنترل‌های موجود در زبانه Formulas روبان اکسل است.

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

هنگامی که تابعی را به این روش وارد کردید ، اکسل پنجره محاوره‌ای Function Arguments را نمایش می‌دهد.

این پنجره شامل توضیحی در مورد تابع و مولفه‌هایی است که می‌گیرد. به عنوان مثال ، اگر بخواهیم ریشه دوم عددی را با استفاده از تابعSQRT  محاسبه کنیم به این شکل عمل می‌کنیم: 

از آنجایی که تابع یک تابع ریاضی است ، به دسته توابع Math & Trig بروید. در لیست توابع این دسته پیمایش و تابع را پیدا کنید.

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

همانطور که مشاهده می‌کنید این تابع تنها یک مولفه دارد (number).

هنگامی که تابع در فرمول درج شد ، می‌توانید هر زمان که بخواهید با کلیک روی دکمه Insert Function در سمت چپ نوار فرمول ، دوباره به پنجره محاوره‌ای Function Arguments بازگردید.

3) همچنین می‌توانید از دکمه Insert Function موجود در زبانه Formulas روبان اکسل ، برای فراخوانی تابع استفاده کنید.

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

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

در این پنجره با انتخاب دسته مربوطه از قسمت Or Select a Category می‌توانید تنها توابع این گروه را مشاهده کنید.

درج تابع با تایپ نام تابع ، سریعترین راه برای کار با توابع است.

ترکیب توابع ( توابع تودرتو)

بسیاری از فرمول‌های اکسل بیش از یک تابع استفاده می‌کنند و توابع را می‌توان به صورت "تودرتو" استفاده کرد. برای نمونه ، در مثال زیر براساس تاریخ تولد (میلادی) درج شده در سلول B1 می‌خواهیم میزان سن را براساس تاریخ روز جاری در سلول B2 محاسبه کنیم:

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

می‌توانیم از سلول B1 برای تاریخ شروع و سپس از تابع TODAY برای ارائه تاریخ پایان استفاده کنیم.

= YEARFRAC (B1 ;TODAY( ))

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

توجه داشته باشید که از تابع TODAY برای تغذیه تاریخ پایان در تابع YERFRAC استفاده می‌کنیم.

به عبارت دیگر ، تابع TODAY را می‌توان داخل تابع YEARFRAC قرار داد تا مولفه تاریخ پایان را ارائه دهد.

می‌توانیم فرمول را یک قدم جلوتر ببریم و از تابع INT برای حذف مقدار اعشاری استفاده کنیم:

= INT (YEARFRAC (B1;TODAY()))

در اینجا، فرمول اصلی YEARFRAC مقدار 45/43 را به تابع INT برمی‌گرداند و تابع INT نتیجه نهایی 45 را باز می‌گرداند.

یادداشت: تصاویر فوق در 21 ژانویه 2024 ایجاد شده‌اند.

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

بررسی مرحله به مرحله محاسبات فرمول‌ها

با استفاده از ویژگی Evaluate Formula می‌توانید محاسبات مرحله به مرحله فرمول‌ها در اکسل را مشاهده کنید.

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

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

می‌توانید Evaluate Formula را در زبانه Formulas روبان اکسل ، در گروه Formula Auditing پیدا کنید.

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

زیر قسمتی از فرمول خط کشیده خواهد شد ، این بخشی است که در حال حاضر "در حال ارزیابی" است. در این مثال ، خروجی تابع TODAY اولین گام در حل این فرمول است.
وقتی روی Evaluate کلیک می‌کنیم ، تابع TODAY ارزیابی می‌شود و تاریخ را در قالب شماره سریال اکسل بر می‌گرداند و زیر تابع YEARFRAC به عنوان مرحله بعدی در فرآیند ارزیابی خط کشیده می‌شود.

در کلیک بعدی ، تابع YEARFRAC ارزیابی می‌شود و زیر تابع INT خط کشیده می‌شود. با آخرین کلیک ، فرمول با نتیجه 45 حل می‌شود.

توجه داشته باشید که دکمه Evaluate در پایان فرآیند ارزیابی به Restart تغییر می‌کند. برای ارزیابی مجدد فرمول ، روی Restart کلیک کنید.

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

دکمه‌های Step In و Step Out زمانی در دسترس و فعال هستند که در فرمول اصلی به فرمول‌های دیگر یا به آدرس سلولی ارجاع می‌شود. برای ارزیابی جداگانه فرمول‌های داخل فرمول اصلی می‌توانید از این دکمه‌ها استفاده کنید.

فرآیند مشابه ارزیابی عادی است. پس از اتمام ، برای ادامه ارزیابی فرمول اصلی روی Step Out کلیک کنید.

امکان ورود به فرمول‌های داخل فرمول اصلی یک ویژگی اختیاری است.

میانبرهای مرتبط

لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

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