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

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

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

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

تابع SUM در اکسل

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

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

تابع SUMIF در اکسل

در صورتی که بخواهیم مقادیری از یک ردیف که دارای شرط مشخصی هستند را با هم جمع بزنیم، می‌توانیم از تابع 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 در اکسل

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

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

تابع COUNTA در اکسل

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

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

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

تابع COUNTBLANK در اکسل

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

تابع COUNTIF در اکسل

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

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

تابع AVERAGE در اکسل

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

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

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

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

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

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

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

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

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

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

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

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

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

تابع IF در اکسل

تابع IF برای بررسی یک شرط منطقی در اکسل به کار می‌رود. خروجی هر شرط منطقی یا (درست) «TRUE» است و یا (غلط) «FALSE». با این تابع می‌توان در صورت درست یا غلط بودن به ازای هر سلول، یک عمل خاص را انجام داد. مثلا در تصویر زیر لیست اسامی شاگردان یک کلاس و نمره آنها آورده شده است. با تابع 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 و RIGHT برای جداسازی بخشی از متن به کار می‌روند. تابع 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 در اکسل

در مطالب قبلی از مجله فرادرس با روش‌های حذف فاصله در اکسل آشنا شدیم. تابع TRIM یکی از توابع اکسل برای حذف فاصله است که فواصل اضافی موجود در متن‌ها را از بین می‌برد. این فواصل شامل فواصل طبیعی بین کلمات نمی‌شود. به طور مثال در شکل زیر، در ابتدای اسامی یک یا چند فاصله وجود دارد که اکسل آن را جزو رشته متنی در نظر می‌گیرد و اگر فرضا این اسامی را بدون این فاصله جستجو کنیم اکسل آنها را پیدا نمی‌کند. برای از بین بردن خطاهای احتمالی در کار با این داده‌ها ابتدا با استفاده از تابع TRIM این فواصل اضافی را حذف می‌کنیم. تابع TRIM با ساختار =TRIM(text)   تنها یک آرگومان ورودی دارد که همان متن مورد نظر است.

تابع CONCATENATE در اکسل

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

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

تابع LEN در اکسل

تابع LEN به منظور شمارش تعداد کاراکترهای یک متن در اکسل به کار برده می‌شود. در مثال زیر، تعداد کاراکترهای رشته متنی موجود در ستون C با استفاده از تابع LEN در ستون D محاسبه شده است. این تابع نیز تنها یک ورودی می‌گیرد که همان متن مورد نظر برای شمارش تعداد کاراکترها است.

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

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

تابع VLOOKUP در اکسل

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

ساختار این تابع به صورت =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 در اکسل

تابع MATCH شماره موقعیت یک داده در یک محدوده ستونی را مشخص می‌کند. ساختار این تابع به صورت =MATCH(lookup_value, lookup_array, [match_type])   است. که آرگومان‌های آن عبارتند از:

• 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 که می‌توانند مقادیر دلخواه را در اکسل جستجو کرده و از جدول داده‌ها مقادیر متناظر با آنها را برگردانند.