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

📚 آشنایی با توابع در اکسل؛ قلب فرمول‌نویسی حرفه‌ای: اگر از چهار عمل اصلی عبور کرده‌اید و نیاز به محاسبات پیشرفته‌تر دارید، توابع اکسل ابزار شما هستند. در این مقاله از صفر تا صد کار با توابع – از نحوه ورود و جداکننده‌ها تا توابع تودرتو و عیب‌یابی – را به زبان ساده و با مثال یاد بگیرید.

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

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

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

در بیشتر موارد ، توابع دارای نام‌هایی هستند که کاربرد مورد نظر آنها را نشان می‌دهد. برای مثال ، احتمالا تابع 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 را بر می‌گرداند

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

📊 مثال عملی از تابع COUNTIF که تعداد خانه‌های دارای مقدار "قرمز" را در محدوده A1 تا A5 شمارش می‌کند.

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

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

(برخی دسته‌های بسیار تخصصی مانند Cube یا Web که کاربری عمومی ندارند، در اینجا توضیح داده نمی‌شوند).

• Most Recently Used: لیست توابع پرکاربردی که اخیراً از آن‌ها استفاده کرده‌اید.
• All: نمایش تمام توابع موجود در اکسل.
• Financial: توابع مالی برای محاسبه سود وام، استهلاک، نرخ بازگشت سرمایه و ... (مانند PMT، FV).
• Date & Time: توابع مربوط به تاریخ و ساعت (مانند TODAY، NOW، DATEDIF).
• Math & Trig: توابع ریاضی و مثلثاتی (مانند SUM، ROUND، SQRT، SIN).
• Statistical: توابع آماری (مانند AVERAGE، COUNT، STDEV).
• Lookup & Reference: توابع جستجو و آدرس‌دهی (مانند VLOOKUP، XLOOKUP، INDEX، MATCH).
• Text: توابع کار با رشته‌های متنی (مانند LEFT، FIND، CONCATENATE).
• Logical: توابع شرطی و منطقی (مانند IF، AND، OR).
• Information: توابع بررسی محتوای سلول (مانند ISBLANK، ISERROR، TYPE).
• Engineering: توابع مهندسی (مانند CONVERT برای تبدیل واحدها، BESSELI).

نکته: دسته‌های Cube، Compatibility و Web مربوط به تحلیل‌های آنلاین، سازگاری با نسخه‌های قدیمی و ارتباط با وب هستند که معمولاً کاربران عمومی نیازی به آن‌ها ندارند.

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

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

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

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

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

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

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

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

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

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

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

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

= SUM ( A1 , C1 , E1)

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

= SUM ( A1 ; C1 ; E1)

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

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

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

⚠️ پیام خطای اکسل هنگام وارد کردن فرمول با جداکننده نادرست؛ مشکل در این پیام مستقیماً ذکر نمی‌شود.

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

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

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

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

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

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

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

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

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

⚙️ تنظیمات جداکننده فرمول در کنترل پنل ویندوز (بخش Region و Additional settings).

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

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

Options > Advanced > Use System separators 

🖥️ تنظیمات مرتبط با جداکننده در گزینه‌های پیشرفته اکسل.

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

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

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

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

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

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

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

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

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

نکته: می‌توانید این جعبه راهنما را جا به جا کنید.

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

📌 جعبه راهنمای مولفه‌های تابع - قابل جابه‌جایی در صفحه.

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

🎯 انتخاب محدوده به عنوان اولین مولفه تابع COUNTIF.

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

✅ تکمیل مولفه معیار (criteria) و تأیید فرمول با کلید Enter.

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

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

📁 دسته‌بندی توابع در زبانه Formulas روبان اکسل.

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

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

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

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

🔢 پنجره محاوره‌ای Function Arguments برای تابع SQRT.

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

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

🔘 دکمه Insert Function در کنار نوار فرمول.

۳) استفاده از دکمه Insert Function (مناسب برای کاربرانی که نام دقیق تابع را نمی‌دانند)

اگر نام تابع را به خاطر ندارید اما می‌دانید چه کاری باید انجام دهد، از این روش استفاده کنید:

1- روی دکمه fx در کنار نوار فرمول (یا از زبانه Formulas > Insert Function) کلیک کنید.

🔍 پنجره Insert Function برای جستجوی تابع بر اساس کاربرد مورد نظر.

2- در پنجره باز شده، در کادر Search for a function، به زبان ساده و حتی غیرحرفه‌ای توضیح دهید که چه می‌خواهید.

📋 نتایج جستجوی توابع مرتبط با میانگین.

نیازی به انگلیسی حرفه‌ای نیست، اما بهتر است از حروف لاتین استفاده کنید. چند مثال:

• برای جمع زدن بنویسید: sum numbers
• برای میانگین گرفتن بنویسید: average
• برای شمارش سلول‌های دارای شرط بنویسید: count if
• برای گرد کردن عدد بنویسید: round

3- روی دکمه Go کلیک کنید. اکسل توابع مرتبط را به شما پیشنهاد می‌دهد.

4- تابع مورد نظر را انتخاب کرده و روی OK کلیک کنید.

✅ راهکار عملی برای کاربرانی که با انگلیسی آشنا نیستند:

اگر نوشتن توضیح به لاتین برایتان دشوار است، می‌توانید از این روش جایگزین استفاده کنید:

در کادر Search for a function، یک کلمه کلیدی ساده و کوتاه به لاتین بنویسید، مثلاً:

• به جای «میانگین» بنویسید: avg
• به جای «بزرگترین» بنویسید: max
• به جای «شمارش» بنویسید: count

یا مستقیماً از قسمت Or select a category دسته مورد نظر (مثلاً Statistical یا Math & Trig) را انتخاب کنید و لیست توابع آن دسته را یکی یکی ببینید.

نام توابع معمولاً به کاربردشان اشاره دارد (مثلاً SUM برای جمع، AVERAGE برای میانگین).

نکته مهم: سریع‌ترین روش برای حرفه‌ای‌ها، همان تایپ مستقیم نام تابع است. روش Insert Function بیشتر برای زمانی مفید است که نام تابع را فراموش کرده‌اید.

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

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

📆 نمونه دیتا برای محاسبه سن: تاریخ تولد در B1.

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

📝 نوشتن فرمول ترکیبی YEARFRAC و TODAY برای محاسبه سن.

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

= YEARFRAC (B1 ;TODAY( ))

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

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

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

🔢 خروجی فرمول YEARFRAC به صورت عدد اعشاری.

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

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

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

🎯 فرمول نهایی با تابع INT برای حذف اعشار و نمایش سن کامل.

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

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

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

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

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

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

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

🛠️ ابزار Evaluate Formula در گروه Formula Auditing از زبانه Formulas.

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

🔍 مرحله اول ارزیابی فرمول با ابزار Evaluate Formula.

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

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

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

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

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

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

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

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

⌨️ میانبرهای کاربردی برای کار با توابع در اکسل.

#10جمع‌بندی: آنچه در این مطلب یاد گرفتید

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

• تفاوت فرمول و تابع: هر فرمول با «=» شروع می‌شود، اما تابع یک فرمول از پیش تعریف‌شده با نام و هدف مشخص است (مانند SUM یا AVERAGE).
• ساختار تابع: به صورت (مولفه‌ها)نام تابع است. مولفه‌ها ورودی‌های تابع هستند و می‌توانند اجباری یا اختیاری باشند.
• جداکننده مولفه‌ها (نقطه ویرگول یا کاما): این موضوع یکی از شایع‌ترین منابع خطاست. جداکننده به تنظیمات منطقه‌ای ویندوز شما بستگی دارد. در ایران معمولاً از نقطه ویرگول (;) استفاده می‌شود. اگر فرمولی را از وب کپی می‌کنید و با خطا مواجه شدید، اولین قدم بررسی و تغییر جداکننده‌هاست.
• روش‌های وارد کردن تابع: سریع‌ترین روش، تایپ مستقیم نام تابع بعد از علامت «=» است. اما برای مرور و آشنایی با توابع جدید، از زبانه Formulas در روبان اکسل یا دکمه Insert Function استفاده کنید.
• ترکیب توابع (توابع تودرتو): قدرت اصلی اکسل در اینجاست. می‌توانید خروجی یک تابع را مستقیما به عنوان ورودی تابع دیگر بدهید. مثلا INT(YEARFRAC(B1;TODAY()))= برای محاسبه سن.
• ابزار Evaluate Formula: وقتی فرمول شما پیچیده شد و جواب درست را نمی‌داد، این ابزار را از زبانه Formulas > Formula Auditing اجرا کنید. قدم به قدم نشان می‌دهد که اکسل چگونه فرمول شما را محاسبه می‌کند – بهترین راه برای پیدا کردن محل خطا.

 قدم بعدی شما

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

1. با تابع SUM جمع چند عدد را محاسبه کنید.
2. با تابع AVERAGE میانگین سه عدد را به دست آورید.
3. یک تابع COUNTIF بنویسید که تعداد سلول‌های بزرگتر از ۱۰ را در یک محدوده بشمارد.
4. یک تابع تودرتو با IF و AND بسازید (مثلاً اگر نمره بین ۱۲ تا ۲۰ بود، «قبول» بنویسد).

💡 یادتان باشد: اشتباه کردن در فرمول‌نویسی نه تنها طبیعی است، بلکه بهترین راه برای یادگیری عمیق اکسل است. هر بار که با خطای #VALUE! یا #NAME? مواجه شدید، از ابزار Evaluate Formula استفاده کنید.

💡 اگر در اجرای هر کدام از توابع به مشکل خوردید یا سوالی دارید، در بخش دیدگاه مطرح کنید. پاسخ شما ظرف ۲۴ ساعت داده می‌شود.

مهراج محمدی

مدیر

...