در این آموزش با توابع پایه اکسل شامل SUM، AVERAGE، MIN، MAX، COUNT، COUNTA، ROUND و IF آشنا شوید و کاربرد هر تابع را با مثال یاد بگیرید.

مقدمه

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

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

برای مثال، اگر بخواهیم اعداد موجود در 100 سلول را با یکدیگر جمع کنیم، نیازی نیست آدرس تمام سلول‌ها را با علامت جمع بنویسیم. تابع SUM این محاسبه را با یک فرمول کوتاه انجام می‌دهد. به همین ترتیب، توابع دیگری برای محاسبه میانگین، پیدا کردن بزرگ‌ترین یا کوچک‌ترین عدد، شمارش سلول‌ها، گرد کردن اعداد و بررسی شرط‌ها وجود دارند.

در این مقاله با هشت تابع مهم و پرکاربرد شامل SUM، AVERAGE، MIN، MAX، COUNT، COUNTA، ROUND و IF آشنا می‌شویم. این توابع در بسیاری از فایل‌های مالی، اداری، آموزشی، فروشگاهی و شخصی استفاده می‌شوند و یادگیری آن‌ها مقدمه ورود به فرمول‌نویسی حرفه‌ای است.

تابع در اکسل چیست؟

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

ساختار کلی یک تابع به‌شکل زیر است:

content_copy text

=FUNCTION(arguments)

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

برای مثال، فرمول زیر اعداد موجود در محدوده B2:B10 را جمع می‌کند:

content_copy text

=SUM(B2:B10)

در این فرمول، SUM نام تابع و B2:B10 محدوده مورد محاسبه است. علامت دونقطه : نیز تمام سلول‌های میان B2 تا B10 را مشخص می‌کند.

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

۱. تابع SUM در اکسل

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

ساختار کلی این تابع به‌صورت زیر است:

content_copy text

=SUM(number1,number2,…)

فرض کنید مبلغ فروش روزانه در سلول‌های B2 تا B8 قرار دارد. برای محاسبه مجموع فروش هفت روز می‌توان نوشت:

content_copy text

=SUM(B2:B8)

تابع SUM تمام اعداد موجود در این محدوده را با یکدیگر جمع می‌کند. سلول‌های خالی و متن‌های معمولی موجود در محدوده نیز در محاسبه جمع تأثیری ندارند.

می‌توان چند محدوده جداگانه را نیز با یک تابع جمع کرد:

content_copy text

=SUM(B2:B8,D2:D8)

این فرمول مجموع اعداد موجود در دو محدوده B2:B8 و D2:D8 را محاسبه می‌کند.

همچنین می‌توان عدد ثابت، آدرس سلول و محدوده را هم‌زمان در تابع قرار داد:

content_copy text

=SUM(B2:B8,D2,100)

برای استفاده سریع از این تابع می‌توانید سلول زیر یک ستون عددی را انتخاب کرده و روی گزینه AutoSum در تب Home یا Formulas کلیک کنید. میانبر Alt + = نیز تابع SUM را به‌صورت خودکار درج می‌کند.

۲. تابع AVERAGE در اکسل

تابع AVERAGE برای محاسبه میانگین حسابی اعداد استفاده می‌شود. این تابع مجموع اعداد را محاسبه کرده و سپس آن را بر تعداد مقادیر عددی تقسیم می‌کند.

ساختار تابع AVERAGE به‌شکل زیر است:

content_copy text

=AVERAGE(number1,number2,…)

فرض کنید نمرات یک دانش‌آموز در سلول‌های C2 تا C7 نوشته شده است. برای محاسبه میانگین نمرات از فرمول زیر استفاده می‌کنیم:

content_copy text

=AVERAGE(C2:C7)

اگر اعداد موجود در این محدوده برابر 15، 18، 17، 20، 16 و 19 باشند، تابع ابتدا مجموع آن‌ها را محاسبه و سپس نتیجه را بر 6 تقسیم می‌کند.

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

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

• میانگین نمرات دانش‌آموزان
• میانگین فروش روزانه یا ماهانه
• میانگین هزینه‌ها
• میانگین ساعات کاری
• میانگین امتیاز مشتریان
• میانگین تولید یک مجموعه

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

۳. تابع MIN در اکسل

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

ساختار تابع MIN به‌صورت زیر است:

content_copy text

=MIN(number1,number2,…)

برای مثال، اگر قیمت چند محصول در محدوده D2:D20 قرار داشته باشد، فرمول زیر کمترین قیمت را نمایش می‌دهد:

content_copy text

=MIN(D2:D20)

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

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

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

۴. تابع MAX در اکسل

تابع MAX عملکردی برخلاف تابع MIN دارد و بزرگ‌ترین مقدار عددی را پیدا می‌کند. ساختار آن به‌صورت زیر است:

content_copy text

=MAX(number1,number2,…)

اگر مبلغ فروش کارمندان در محدوده E2:E15 قرار دارد، با فرمول زیر می‌توان بیشترین فروش را پیدا کرد:

content_copy text

=MAX(E2:E15)

کاربردهای متداول تابع MAX عبارت‌اند از:

• پیدا کردن بالاترین نمره کلاس
• شناسایی بیشترین فروش
• نمایش گران‌ترین محصول
• پیدا کردن بیشترین ساعت کاری
• مشخص کردن بالاترین امتیاز
• یافتن حداکثر مقدار تولید

توابع MIN و MAX معمولاً در کنار یکدیگر استفاده می‌شوند. برای مثال، در یک گزارش فروش می‌توان کمترین و بیشترین فروش ماهانه را با دو فرمول زیر نمایش داد:
content_copy text

=MIN(B2:B13)

content_copy text

=MAX(B2:B13)

به این ترتیب، کاربر می‌تواند دامنه تغییرات داده‌ها را سریع‌تر بررسی کند.

۵. تابع COUNT در اکسل

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

ساختار تابع COUNT به‌شکل زیر است:

content_copy text

=COUNT(value1,value2,…)

برای مثال، فرمول زیر تعداد سلول‌های عددی موجود در محدوده B2:B20 را محاسبه می‌کند:

content_copy text

=COUNT(B2:B20)

فرض کنید در این محدوده 15 سلول دارای عدد، دو سلول دارای متن و دو سلول خالی باشند. نتیجه تابع COUNT برابر 15 خواهد بود.

تاریخ‌ها و زمان‌های معتبر نیز در اکسل به‌صورت عدد ذخیره می‌شوند؛ بنابراین تابع COUNT معمولاً آن‌ها را هم می‌شمارد. در مقابل، عددی که به‌صورت متن ذخیره شده باشد، ممکن است توسط این تابع شمارش نشود.

از COUNT می‌توان برای شمارش تعداد نمرات ثبت‌شده، تعداد مبالغ واردشده، تعداد روزهای دارای فروش یا تعداد پاسخ‌های عددی استفاده کرد.

این تابع نباید با COUNTIF اشتباه گرفته شود. تابع COUNT فقط تعداد سلول‌های عددی را می‌شمارد، اما COUNTIF سلول‌هایی را شمارش می‌کند که یک شرط مشخص را داشته باشند.

۶. تابع COUNTA در اکسل

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

ساختار تابع COUNTA به‌صورت زیر است:

content_copy text

=COUNTA(value1,value2,…)

برای مثال، اگر نام کارمندان در محدوده A2:A30 ثبت شده باشد، می‌توان تعداد نام‌های واردشده را با فرمول زیر محاسبه کرد:

content_copy text

=COUNTA(A2:A30)

تفاوت COUNT و COUNTA را می‌توان به‌صورت ساده این‌گونه بیان کرد:

• تابع COUNT فقط سلول‌های دارای مقدار عددی را می‌شمارد.
• تابع COUNTA تمام سلول‌های غیرخالی را شمارش می‌کند.

فرض کنید محدوده‌ای شامل پنج عدد، سه نام و دو سلول خالی است. نتیجه COUNT برابر 5 و نتیجه COUNTA برابر 8 خواهد بود.

نکته مهم این است که اگر یک سلول ظاهراً خالی باشد اما در آن فرمولی وجود داشته باشد که رشته خالی “” تولید می‌کند، ممکن است COUNTA آن را غیرخالی در نظر بگیرد. وجود فاصله پنهان داخل سلول نیز باعث شمارش آن می‌شود.

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

۷. تابع ROUND در اکسل

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

ساختار تابع ROUND به‌شکل زیر است:

content_copy text

=ROUND(number,num_digits)

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

برای مثال، فرمول زیر عدد موجود در A1 را تا دو رقم اعشار گرد می‌کند:

content_copy text

=ROUND(A1,2)

اگر مقدار A1 برابر 12.3456 باشد، نتیجه برابر 12.35 خواهد شد.

برای گرد کردن عدد به نزدیک‌ترین عدد صحیح، آرگومان دوم را صفر قرار می‌دهیم:

content_copy text

=ROUND(A1,0)

اگر آرگومان دوم منفی باشد، گرد کردن در سمت چپ ممیز انجام می‌شود. برای مثال:

content_copy text

=ROUND(1267,-2)

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

تغییر تعداد رقم اعشار از طریق تنظیمات ظاهری سلول با استفاده از تابع ROUND یکسان نیست. کاهش رقم‌های اعشار از بخش Number Format فقط نحوه نمایش عدد را تغییر می‌دهد، اما ROUND مقدار استفاده‌شده در محاسبات را واقعاً گرد می‌کند.

دو تابع مرتبط نیز وجود دارند:

• ROUNDUP عدد را در جهت دور شدن از صفر گرد می‌کند.
• ROUNDDOWN عدد را در جهت نزدیک شدن به صفر گرد می‌کند.

۸. تابع IF مقدماتی در اکسل

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

ساختار تابع IF به‌صورت زیر است:

content_copy text

=IF(logical_test,value_if_true,value_if_false)

این تابع سه بخش اصلی دارد:

1. شرطی که باید بررسی شود.
2. نتیجه‌ای که در صورت درست بودن شرط نمایش داده می‌شود.
3. نتیجه‌ای که در صورت نادرست بودن شرط نمایش داده می‌شود.

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

content_copy text

=IF(B2>=10,”قبول”,”مردود”)

اگر مقدار B2 بزرگ‌تر یا مساوی 10 باشد، عبارت «قبول» نمایش داده می‌شود. در غیر این صورت، نتیجه «مردود» خواهد بود.

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

مثال دیگر، بررسی میزان فروش است:

content_copy text

=IF(C2>=10000000,”هدف محقق شد”,”نیاز به فروش بیشتر”)

تابع IF می‌تواند به‌جای متن، یک عدد یا محاسبه را نیز برگرداند. برای مثال، اگر کارکنانی با فروش بیشتر از 20 میلیون تومان مستحق دریافت 5 درصد پاداش باشند، می‌توان نوشت:

content_copy text

=IF(C2>=20000000,C2*5%,0)

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

عملگرهای قابل‌استفاده در تابع IF

برای ساخت شرط در تابع IF معمولاً از عملگرهای مقایسه‌ای استفاده می‌شود:

• = به معنای برابر بودن
• > به معنای بزرگ‌تر بودن
• < به معنای کوچک‌تر بودن
• >= به معنای بزرگ‌تر یا مساوی بودن
• <= به معنای کوچک‌تر یا مساوی بودن
• <> به معنای نابرابر بودن

برای مثال، فرمول زیر بررسی می‌کند که آیا مقدار سلول A2 برابر عبارت «تهران» است یا خیر:

content_copy text

=IF(A2=”تهران”,”ارسال رایگان”,”هزینه ارسال”)

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

برای شروع بهتر است از شرط‌های ساده استفاده کنید. تابع IF می‌تواند به‌صورت تودرتو و همراه با توابعی مانند AND و OR نیز استفاده شود، اما فرمول‌های پیچیده‌تر را در آموزش‌های آینده بررسی می‌کنیم.

ترکیب توابع پایه اکسل

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

content_copy text

=IF(AVERAGE(B2:F2)>=10,”قبول”,”مردود”)

اکسل ابتدا تابع داخلی AVERAGE را اجرا می‌کند. سپس تابع IF بررسی می‌کند که آیا میانگین به‌دست‌آمده بزرگ‌تر یا مساوی 10 است یا خیر.

مثال دیگر، گرد کردن میانگین تا دو رقم اعشار است:

content_copy text

=ROUND(AVERAGE(B2:B20),2)

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

برای گرد کردن مجموع فروش نیز می‌توان نوشت:

content_copy text

=ROUND(SUM(C2:C20),0)

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

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

فرض کنید جدولی برای ثبت نمرات دانش‌آموزان ساخته‌ایم. نام دانش‌آموزان در ستون A و نمرات آن‌ها در ستون B قرار دارد. اکنون می‌توانیم گزارش زیر را ایجاد کنیم:

مجموع نمرات:

content_copy text

=SUM(B2:B20)

میانگین نمرات:

content_copy text

=AVERAGE(B2:B20)

کمترین نمره:

content_copy text

=MIN(B2:B20)

بیشترین نمره:

content_copy text

=MAX(B2:B20)

تعداد نمرات عددی ثبت‌شده:

content_copy text

=COUNT(B2:B20)

تعداد نام‌های واردشده:

content_copy text

=COUNTA(A2:A20)

میانگین گرد‌شده تا دو رقم اعشار:

content_copy text

=ROUND(AVERAGE(B2:B20),2)

وضعیت قبولی هر دانش‌آموز:

content_copy text

=IF(B2>=10,”قبول”,”مردود”)

پس از نوشتن فرمول IF در اولین ردیف، می‌توانید Fill Handle را به پایین بکشید تا فرمول برای دانش‌آموزان دیگر نیز کپی شود.

خطاهای رایج هنگام استفاده از توابع

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

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

برای مثال، فرمول زیر به دلیل اشتباه بودن نام تابع اجرا نمی‌شود:

content_copy text

=SMU(A1:A10)

شکل صحیح آن به‌صورت زیر است:

content_copy text

=SUM(A1:A10)

همچنین اگر اکسل فرمول را به‌صورت متن نمایش دهد، فرمت سلول را از Text به General تغییر دهید، سپس با کلید F2 وارد حالت ویرایش شوید و Enter را فشار دهید.

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

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

1. همیشه فرمول و تابع را با علامت = شروع کنید.
2. نام توابع را با حروف انگلیسی بنویسید.
3. محدوده داده‌ها را با دقت انتخاب کنید.
4. تمام پرانتزهای باز را ببندید.
5. متن‌های داخل فرمول را بین ” ” قرار دهید.
6. تفاوت COUNT و COUNTA را به خاطر بسپارید.
7. سلول خالی را با مقدار صفر یکسان در نظر نگیرید.
8. هنگام گرد کردن واقعی مقدار از ROUND استفاده کنید.
9. نتیجه چند فرمول را با محاسبه دستی کنترل کنید.
10. از نام‌های واضح برای عنوان ستون‌ها استفاده کنید.
11. فرمول‌های ساده را پیش از ترکیب توابع آزمایش کنید.
12. پس از کپی فرمول‌ها، ارجاع سلول‌ها را بررسی کنید.

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

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

• نام محصول
• تعداد فروش
• قیمت واحد
• مبلغ فروش
• وضعیت فروش

حداقل 10 محصول فرضی وارد کنید. سپس فعالیت‌های زیر را انجام دهید:

1. مبلغ فروش هر محصول را از ضرب تعداد در قیمت واحد محاسبه کنید.
2. با تابع SUM مجموع مبلغ فروش را به دست آورید.
3. با AVERAGE میانگین مبلغ فروش را محاسبه کنید.
4. با MIN کمترین مبلغ فروش را پیدا کنید.
5. با MAX بیشترین مبلغ فروش را نمایش دهید.
6. با COUNT تعداد مبالغ عددی ثبت‌شده را محاسبه کنید.
7. با COUNTA تعداد نام محصولات را به دست آورید.
8. میانگین فروش را با ROUND تا عدد صحیح گرد کنید.
9. با IF فروش‌های بیشتر از پنج میلیون تومان را «مناسب» و سایر فروش‌ها را «کم» مشخص کنید.
10. یکی از سلول‌ها را خالی کنید و تغییر نتیجه توابع را بررسی کنید.

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

جمع‌بندی

در این قسمت از مجموعه آموزش صفر تا صد اکسل با مهم‌ترین توابع پایه اکسل آشنا شدیم. یاد گرفتیم تابع SUM برای محاسبه مجموع، AVERAGE برای به‌دست آوردن میانگین، MIN برای پیدا کردن کمترین مقدار و MAX برای پیدا کردن بیشترین مقدار استفاده می‌شود.

همچنین تفاوت توابع COUNT و COUNTA را بررسی کردیم. تابع COUNT فقط سلول‌های عددی را می‌شمارد، اما COUNTA تمام سلول‌های غیرخالی را شمارش می‌کند. سپس تابع ROUND را برای گرد کردن عددها و تابع IF را برای انجام محاسبات شرطی یاد گرفتیم.

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