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

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

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

تابع SUM در اکسل

آرگومان‌های این تابع اعدادی هستند که باید با یکدیگر جمع شوند. این اعداد هم مانند تصویر بالا می‌توانند آدرس سلول‌ها باشند و هم می‌توانند به صورت عدد به عنوان ورودی این تابع وارد شوند. به طور مثال خروجی عبارت =SUM(10,20,30)  عدد ۶۰ خواهد بود.

تابع SUMIF در اکسل

مطابق تصویر، فرمول نوشته شده در خانه «C7»، ابتدا یک شرط را بررسی کرده و در صورت برقرار بودن آن شرط، مقادیر قیمت را جمع می‌زند. شرط در این تابع در آرگومان اول آن نوشته می‌شود. در اینجا شرط لازم این است که مقادیر موجود در سلول های B2 تا B6 برابر عبارت (قرمز) باشد و اگر اینگونه بود مقادیر متناظر آن در خانه های C2 تا C6 جمع زده شود. آرگومان‌های تابع SUMIF به صورت زیر هستند:

=SUMIF(range,criteria,[sum_range])

• range : محدوده‌ای که شرط در آن باید بررسی شود
• criteria : شرط مورد نظر
•  [sum_range] : محدوده‌ای که می‌خواهیم جمع زده شود

تابع SUMIFS در اکسل

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

آرگومان‌های تابع SUMIFS عبارتند از:

=SUMIFS(sum_range,criteria_range1,criteria1,...)

•  sum_range : محدوده‌ای که می‌خواهیم جمع زده شود
• criteria_range1 : محدوده اولی که شرط باید در آن بررسی شود
• criteria1 : شرطی که باید در محدوده اول بررسی شود

وجود سه نقطه در ساختار تابع SUMIFS به این معناست که می‌توان به تعداد دلخواه شرط به این تابع اضافه کرد.

فیلم آموزش توابع پرکاربرد اکسل در فرادرس

کلیک کنید

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

تابع COUNT در اکسل

این تابع در واقع آرگومان‌های ورودی به خود را می‌شمارد. مطابق تصویر بالا، با وجود اینکه از C2 تا C7، شش ردیف وجود دارد، به علت خالی بودن سلول C5 خروجی تابع ۵ شده است.

تابع COUNTA در اکسل

=COUNTA(value1, [value2], ...)

آرگومان‌های ورودی این تابع (value )، سلول‌های دارای مقدار عددی و غیرعددی هستند. تصویر زیر، کاربرد COUNTA را برای شمارش ورودی‌های متنی نشان می‌دهد.

تابع COUNTBLANK در اکسل

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

تابع COUNTIF در اکسل

ساختار این تابع به صورت =COUNTIF(range,criteria)  که آرگومان اول محدوده مورد نظر برای شمارش و آرگومان دوم شرطی است که باید برای شمارش بررسی شود.

تابع AVERAGE در اکسل

توابع MIN و MAX در اکسل

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

در واقع این دو تابع کوچکترین و بزرگترین عدد را از بین گستره انتخابی برمی‌گردانند.

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

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

تابع TODAY و تابع NOW در اکسل

این دو تابع که از معروف‌ترین توابع اکسل به شمار می‌روند، زمان فعلی را با فرمت DATE بر می‌گردادنند. تابع TODAY تاریخ امروز به میلادی و تابع NOW تاریخ و ساعت همان لحظه را بر می‌گرداند. لازم به ذکر است که با هر بار باز کردن مجدد و یا ایجاد یک تغییر در فایل اکسل این زمان بروزرسانی می‌شود. هر دوی این توابع نیاز به هیچ آرگومان ورودی ندارند. تابع TODAY به صورت =TODAY()  و تابع NOW به صورت =NOW()  استفاده می‌شوند.

توابع YEAR، MONTH و DAY در اکسل

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

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

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

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

کلیک کنید

تابع IF در اکسل

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

=IF(logical_test,[value_if_true],[value_if_false])

• logical_test : شرط مورد نظری که باید بررسی شود
• [value_if_true] : مقداری که در صورت درست بودن شرط باید اعمال شود
• [value_if_false] : مقداری که در صورت غلط بودن شرط باید اعمال شود

توابع AND و OR در اکسل

این دو تابع نیز جزو توابع اکسل از نوع منطقی هستند و مطابق آنچه گفته شد خروجی آنها TRUE و یا FALSE است. فرض کنید لیستی از مشتریان شرکت و میزان خریدی که تا به حال از شرکت داشته‌اند و همچنین مدت زمانی که از شرکت خرید کرده‌اند را در اختیار داریم. می‌خواهیم بدانیم کدام یک از آنها تا بحال بالای ۵۰ میلیون تومان از ما خریده کرده‌اند و بیش از ۳ سال است که مشتری ما هستند. چون می‌خواهیم هر دوی این شرط‌ها برقرار باشد، مطابق شکل زیر از تابع AND استفاده می‌کنیم.

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

مطابق تصویر هر جا فقط یکی از دو شرط خرید بالای ۵۰ میلیون تومان و یا سابقه بالای ۳ سال درست باشد، نتیجه درست یا «TRUE» نشان داده شده و فقط مواردی که هر دو شرط برقرار نبوده‌اند خروجی FALSE برگردانده شده است. ساختار آرگومان‌های این دو تابع کاملا شبیه هم هستند و کافی است شرط‌های مورد نظر به ترتیب وارد شوند.

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

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

توابع LEFT و RIGHT در اکسل

مطابق شکل با استفاده از تابع LEFT و مشخص کردن تعداد ۴ کاراکتر جدا شونده از سلول مورد نظر، کد هر شهر در ستون جدید برگردانده شده است. ساختار این تابع به صورت =LEFT(text,[num_chars])  است که در آن آرگومان اول متن مورد نظر و آرگومان دوم تعداد کاراکتری است که می‌خواهیم جدا شود. همچنین با استفاده از تابع RIGHT و مطابق شکل زیر می‌توان ۴ رقم آخر شماره تلفن ها را جدا کرد.

ساختار تابع RIGHT نیز مشابه ساختار تابع LEFT یعنی به صورت =RIGHT(text,[num_chars]  است.

توابع UPPER، LOWER و PROPER در اکسل

این توابع در کار با متن‌های انگلیسی کاربرد دارند. تابع UPPER با ساختار =UPPER(text)  کل متن را تبدیل به حروف بزرگ و تابع LOWER با ساختار =LOWER(text)  کل متن را تبدیل به حروف کوچک می‌کند. همچنین تابع PROPER با ساختار =PROPER(text)  متن ورودی را گرفته و حرف اول آن را تبدیل به حروف بزرگ و بقیه حروف متن را تبدیل به حروف کوچک می‌کند. در جدول تصویر زیر تاثیر هر کدام از این توابع بر متن موجود در ستون A مشاهده می‌شود.

تابع TRIM در اکسل

تابع CONCATENATE در اکسل

ساختار این تابع به صورت =CONCATENATE(text1,[text2],...)  است و آرگومان‌های این تابع همان رشته‌های متنی هستند که باید به هم متصل شوند. در این مثال بین نام و نام خانوادگی یک کاراکتر (فاصله) یا « » آورده شده تا به هم متصل نشوند.

تابع LEN در اکسل

توابع جستجو در اکسل

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

فیلم آموزش داشبورد مدیریتی و گزارش‌گری در Excel در فرادرس

کلیک کنید

تابع VLOOKUP در اکسل

ساختار این تابع به صورت =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])  است که آرگومان‌ها به صورت زیر تعریف شده‌اند:

• lookup_value : مقداری است که می‌خواهیم جستجو شود
• table_array : محدوده مورد نظر برای جستجو است
• col_index_num : شماره ستون مورد نظر برای برگرداندن مقدار
• [range_lookup] : تعیین‌کننده انطباق دقیق یا تقریبی عبارت مورد جستجو است.

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

تابع INDEX در اکسل

تابع INDEX یک محدوده را دریافت کرده و تقاطع شماره ستون و شماره ردیف مشخصی را برمی‌گرداند. ساختار این تابع به صورت =INDEX(array, row_num, [col_num], [area_num])  نمایش داده می‌شود. آرگومان‌ اول در INDEX، محدوده مورد نظر، آرگومان دوم شماره ردیف در آن محدوده و آرگومان سوم شماره ستون است. مثلا در تصویر زیر، در محدوده A1 تا C9 ردیف سوم و ستون دوم عبارت "نارنجی" به درستی برگردانده شده است.

تابع MATCH در اکسل

• lookup_value : مقداری که باید جستجو شود
• lookup_array : محدوده‌ای که جستجو باید در آن صورت بگیرد
• [match_type] : نوع انطباق را مشخص می‌کند

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

• 0  : اگر این آرگومان صفر وارد شود، به معنی انطباق کامل است و اولین مقداری که با عبارت مورد جستجو انطباق کامل داشته باشد در نظر گرفته می‌شود.
• 1  : به معنی انطباق تقریبی است به طوریکه بزرگترین مقداری که کوچکتر یا مساوی عبارت مورد جستجو باشد در نظر گرفته می‌شود.
• -1 : به معنی انطباق تقریبی است به طوریکه کوچکترین مقداری که بزرگتر یا مساوی عبارت مورد جستجو باشد در نظر گرفته می‌شود.

مثلا اگر بخواهیم بدانیم عبارت (نارنجی) چندمین عنصر در محدوده A1 تا A9 است، فرمول بالا را می‌نویسیم. نتیجه به دست آمده ۷ خواهد بود. یکی از مهم‌ترین کاربردهای این تابع، ترکیبش با تابع INDEX و یافتن مقادیر متناظر است. در واقع ترکیب این دو تابع کاربردی مانند تابع VLOOKUP دارد، با این تفاوت که محدودیت‌های تابع VLOOKUP وجود نخواهد داشت. در ادامه با نحوه ترکیب دو تابع INDEX و MATCH آشنا می‌شویم.

ترکیب دو تابع INDEX و MATCH در اکسل

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

مطابق تصویر به جای نوشتن عدد در آرگومان دوم تابع INDEX، از تابع MATCH برای پیدا کردن عدد موقعیت عبارت (جلیقه) استفاده شده و در آرگومان دوم تابع INDEX وارد شده است.

به این صورت به راحتی قیمت جلیقه از جدول استخراج شده و در جدول جدید قرار گرفته است.

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

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

مهم‌ترین توابع آماری در اکسل کدامند؟

توابع جمع از جمله SUM، SUMIF و SUMIFS، توابع شمارش مانند COUNT و COUNTA تابع میانگین یا AVERAGE و توابع یافتن ماکزیمم و مینیمم شامل MIN و MAX از جمله مهم‌ترین توابع آماری در توابع اکسل به شمار می‌روند.

مهم‌ترین توابع زمانی در اکسل کدامند؟

از جمله مهم‌ترین توابعی که با زمان در اکسل کار می‌کنند می‌توان از توابع NOW ،YEAR ،MONTH ،DAY و TODAY نام برد.

توابع منطقی در اکسل چه هستند؟

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

مهم‌ترین توابع جستجو در اکسل کدامند؟

مهم‌ترین توابع جستجو در اکسل عبارتند از VLOOKUP، INDEX و MATCH که می‌توانند مقادیر دلخواه را در اکسل جستجو کرده و از جدول داده‌ها مقادیر متناظر با آنها را برگردانند.