فرمول های اکسل مهم که باید بلد باشید – معرفی ۳۵ تابع

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

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

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

• انتخاب یک سلول خالی در صفحه اکسل
• وارد کردن علامت مساوی «=» در سلول خالی
• انتخاب سلولی که در نظر داریم عملیات محاسباتی بر روی آن انجام دهیم
• وارد کردن عملگر محاسباتی
• وارد کردن نشانی سلول بعدی برای ادامه عملیات محاسبات و فشار دادن دکمه «Enter»

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

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

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

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

تابع SUM در اکسل برای محاسبه مجموع داده‌های موجود در محدوده انتخاب شده از سلول‌ها بکار می‌رود. به عنوان مثال در جدول اکسل تصویر زیر، برای پیدا کردن مجموع فروش (Total Sales)، در ستون سوم، از فرمول =SUM(C2:C4)  استفاده می‌کنیم. در این صورت به شکل خودکار حاصل جمع سه عدد ۳۰۰، ۳۸۵ و ۴۸۰ برابر ۱۱۶۵ در سلول C5 محاسبه می‌شود. لازم به‌ذکر است اگر بخواهیم حاصل عملیات جمع در سلول دیگری به نمایش درآید، باید از ابتدا فرمول مربوط را در همان سلول وارد کنیم.

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

تابع PRODUCT در اکسل با ساختار =PRODUCT(number1, [number2],...)  حاصل‌ضرب اعداد در محدوده مورد نظر از سلول‌های جدول اکسل را نشان می‌دهد. به‌عنوان مثال برای ضرب مقادیر سلول‌های A2 تا C2 در تصویر زیر با تعریف فرمول =PRODUCT(A2:C2)  نتیجه مورد نظر به‌دست می‌آید. همچنین اگر بخواهیم حاصل ضرب این اعداد در ۳ را نیز محاسبه کنیم، فرمول با تغییری کوچک به شکل =PRODUCT(A2:C2, 3)  در می‌آید.

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

تابع AVERAGE در اکسل برای محاسبه میانگین محدوده ارقام مشخص شده در سلول‌های اکسل به‌کار می‌رود. به‌عنوان مثال اگر بخواهیم میانگین اعداد بین سلول‌های C2 تا C4 در جدول زیر را حساب کنیم فرمول به صورت =AVERAGE(C2,C3,C4) خواهد بود. همان‌طور که در تصویر مشخص است، این میانگین در سلول C6 ذخیره شده است.

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

تابع COUNT در اکسل تعداد سلول‌های دارای رقم در محدوده مشخصی از جدول را می‌شمارد. توجه داشته باشید که این تابع سلول‌های خالی یا سلول‌هایی که ساختار آن‌ها چیزی به‌جز عدد است را شمارش نمی‌کند. به‌عنوان مثال در جدول زیر برای شمارش سلول‌ها در محدوده C1 تا C4 و استفاده از فرمول به شکل =COUNT(C1:C4)  تعداد سلول‌های دارای عدد برابر ۳ شمارش می‌شوند. این در حالی است که در این محدوده چهار سلول وجود دارد که یکی از آن‌ها در ردیف اول شامل حروف است و در شمارش به حساب نمی‌آید.

برای شمارش همه سلول‌ها با ساختار عدد و متن می‌توان از تابع COUNTA()  استفاده کرد که البته در این فرمول هم سلول‌های خالی قابل شمارش نیستند. برای شمارش تعداد سلول‌های خالی در یک محدوده مشخص فرمول COUNTBLANK()  به کار می‌رود.

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

تابع SUBTOTAL در اکسل برای انجام عملیات مختلفی مانند جمع، شمارش، میانگین، تعیین حداقل و حداکثر در محدوده سلول‌های جدول اکسل کاربرد دارد. فرمول کلی آن به شکل =SUBTOTAL(function_num,ref1,[ref2],…)  است که در آن آرگومان function_num  شماره از پیش تعیین شده فرمول زیرمجموعه و 2,ref1  محدوده مشخص سلول‌ها در جدول است. به صورت پیش فرض در لیست این تابع برای هر عملیات یک رقم مشخص شده است که در بخش function_num  درج می‌شود. به عنوان مثال عدد ۱ نماینده عملیات AVERAGE یا عدد ۴ نماینده عملیات MAX است.

برای نمونه، در جدول زیر با استفاده از فرمول =SUBTOTAL(1,A2:A4)  میانگین اعداد در محدوده سلول A2 تا A4 برابر عدد ۱۱ محاسبه شده‌اند.

به همین ترتیب با تعریف =SUBTOTAL(4,A2:A4)  در این محدوده سلول، حداکثر مقدار که عدد ۱۲ است به‌دست می‌آید.

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

تابع MODULUS در اکسل برای تعیین باقیمانده حاصل تقسیم مقدار مشخص در سلول انتخابی بر یک عدد دلخواه، مورد استفاده قرار می‌گیرد. به‌عنوان مثال در تصویر زیر، اگر بخواهیم باقیمانده تقسیم عدد سلول A2 را بر ۳ به‌دست آوریم، تابع به صورت =MOD(A2,3)  در می‌آید. در اینجا باقیمانده تقسیم عدد ۱۰ بر ۳ در سلول B2 با عدد ۱ نشان داده شده است.

البته می‌توان مستقیما از فرمول =MOD(10,3)  هم استفاده کرد که در هر دو صورت نتیجه یکسان است.

به‌شکل مشابه در همین مثال با استفاده از فرمول =MOD(12,4)  باقیمانده تقسیم عدد ۱۲ بر ۴ در سلول B3 با عدد صفر مشخص است.

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

تابع POWER()  در اکسل برای محاسبه حاصل عدد مورد نظر در جدول به توان مشخص مورد استفاده قرار می‌گیرد. به‌عنوان مثال اگر بخواهیم عدد سلول A2 را به توان ۳ برسانیم، تابع به شکل =POWER(A2,3)  در می‌آید. در تصویر زیر، حاصل عملیات در سلول B1 با عدد ۱۰۰۰ مشخص است.

به همین ترتیب برای به‌دست آوردن حاصل عدد موجود در سلول A3 به توان ۳ از فرمول =POWER(A2,3)  استفاده می‌کنیم. نتیجه در سلول B2 مشخص است.

۸. تابع CEILING در اکسل

تابع CEILING()  در اکسل برای گرد کردن مقدار سلول به بیشترین و نزدیک‌ترین مضرب عددی که در فرمول تعیین می‌شود، استفاده می‌شود. به‌عنوان مثال در فرمول =CEILING(A2,5)  جدول زیر، بیشترین مضرب نزدیک ۵ برای عدد ۳۵/۳۱۶ برابر ۴۰ است.

۹. تابع FLOOR در اکسل

تابع FLOOR در اکسل بر خلاف CEILING، یک عدد را به نزدیکترین و کمترین مضرب خود گرد می‌کند. در مثال مورد بحث، کمترین مضرب نزدیک ۵ عدد ۳۵/۱۳۶ که با فرمول =FLOOR(A2,5)  تعریف می‌شود برابر ۳۵ است.

۱۰. تابع CONCATENATE در اکسل

تابع CONCATENATE در اکسل دو یا چند متن در جدول اکسل را با یکدیگر ترکیب می‌کند. به‌عنوان مثال با به‌کار بردن یکی از فرمول‌ های اکسل به صورت =CONCATENATE(A25, " ",B25)  متن سلول A25 یا عبارت «Hello» با متن سلول B25 یعنی «World» ترکیب می‌شود. نتیجه با عبارت «Hello World» مشخص است. در این دستور علامت " "  یک فاصله بین متن‌ها ایجاد می‌کند. به همین ترتیب دستور =CONCATENATE(A27&" "&B27)  متن‌های دو سلول A27 و B27 را ترکیب می‌کند.

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

تابع LEN در اکسل تعداد کل کاراکترهای یک رشته متنی را مشخص می‌کند. این تعداد شامل فضاهای خالی و همچنین کاراکترهای خاص هستند. در تصویر مثال زیر، فرمول =LEN(A7)  تعداد کاراکترهای سلول A7 را با در نظر گرفتن فضای خالی، برابر ۵ شمارش کرده است.

۱۲. تابع REPLACE در اکسل

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

=REPLACE(old_text,start_num,num_chars,new_text)

در رابطه بالا، هر بخش به صورت زیر تعریف می‌شود.

• آرگومان old_text  : متنی که در نظر دارید آن‌ را جایگزین کنید.
• آرگومان start_num  : موقعیت مکانی کاراکتر در عبارت که می‌خواهید جایگزینی کاراکترها را با آن آغاز کنید.
•  آرگومان num_chars  : تعداد کاراکترهای مورد نظر جهت جایگزینی است.
• آرگومان new_text  : عبارت متنی جدید

به‌عنوان مثال اگر در جدول زیر بخواهیم حرف A در عبارت «A101» سلول A15 را با حرف B جایگزین کنیم، دستور مربوط به شکل =REPLACE(A15,1,1,"B")  خواهد بود. از آنجا که حرف A در جایگاه اول عبارت قرار دارد در قسمت بعدی دستور عدد ۱ را می‌نویسیم. در مرحله بعد هدف جایگزینی تنها یک حرف است، بنابراین جایگاه بعدی در فرمول باز هم عدد ۱ خواهد بود. در نهایت نیز از آنجا که حرف جایگزین B است، قسمت آخر دستور آن را تایپ می‌کنیم.

به همین ترتیب برای تبدیل عبارت متنی «A102» به «A2102» دستور مربوط =REPLACE(A16,2,1,"A2")  و نتیجه به شکل تصویر زیر است.

یا برای جایگزینی متن «Saam» به‌جای «Adam» همان‌طور که مشخص است، این کلمه در سلول A17 قرار دارد و جایگزینی از اولین حرف به تعداد ۲ کاراکتر انجام می‌گیرد. بنابراین دستور به شکل =REPLACE(A17,1,2,"Sa")  خواهد بود.

۱۳. تابع SUBSTITUTE در اکسل

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

=SUBSTITUTE(text, old_text, new_text, [instance_num])

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

• آرگومان text  : شماره سلول مورد نظر در جدول اکسل برای جایگزینی
• آرگومان old_txt  : عبارت متنی که می‌خواهیم جایگزین کنیم.
• آرگومان new_text  : عبارت متنی جدیدی که می‌خواهیم جایگزین شود.
• آرگومان [instance_num]  : موقعیت مکانی متن‌ها با تکرار بیش از یک مرتبه

برای روشن‌تر شدن بحث، دو مثال مختلف را در این زمینه توضیح می‌دهیم. فرض کنید می‌خواهیم در تصویر زیر عبارت داخل سلول A20 یعنی «I like Excel» را با متن «He likes Excel» جایگزین کنیم.

در این حالت دستور اجرای کار =SUBSTITUTE(A20,"I like","He likes")  خواهد بود.

در مثال بعد می‌خواهیم دومین عدد ۲۰۱۰ در عبارت متنی سلول A21 را با ۲۰۱۶ جایگزین کنیم. بنابراین دستور مورد نظر =SUBSTITUTE(A21,2010,2016,2)  خواهد بود. همان‌طور که مشخص است، عدد ۲ به دومین ۲۰۱۰ تکرار شونده در متن اشاره می‌کند.

۱۴. توابع LEFT, RIGHT, MID در اکسل

تابع LEFT در اکسل کاراکترهای رشته متنی در یک سلول را از سمت چپ نشان می‌دهد. به همین ترتیب تابع RIGHT کاراکترهای متن از سمت راست را نمایش می‌دهد. تابع MID نیز برای نمایش کاراکترها از وسط متن به بعد بر اساس نقطه آغاز معین در دستور و به تعداد مشخص تعیین شده به‌کار می‌رود. در مثال‌های زیر این موضوع بیشتر مورد بررسی قرار می‌گیرد.

ابتدا می‌خواهیم با استفاده از تابع LEFT پنج کاراکتر متن سلول A5 را از سمت چپ مشخص کنیم. در این حالت دستور مربوط به شکل =LEFT(A5,5)  خواهد بود. نتیجه در تصویر زیر به شکل کادر سبز رنگ نشان داده شده است.

در مثال دوم با استفاده از دستور =MID(A5,7,6)  شش کاراکتر متن سلول A5 از محل کاراکتر ۷ به بعد در سلول C5 جدا شده‌اند. لازم به‌ذکر است که هنگام شمارش، کلیه فاصله‌ها هم به‌حساب می‌آیند.

و در مثال آخر برای جدا کردن ۸ کاراکتر از سمت راست در متن سلول A5 از دستور =RIGHT(A5,8)  استفاده می‌کنیم. نتیجه در سلول D5 مشخص است.

۱۵. توابع UPPER, LOWER, PROPER در اکسل

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

در مثال بعدی حروف سلول A6 با دستور =LOWER(A6)  همانند تصویر به شکل کوچک در می‌آیند.

و در نهایت از طریق دستور =PROPER(A6)  متن نادرست سلول A6 که به شکل حروف مخلوط بزرگ و کوچک است، به عبارت درست در سلول A7 تبدیل شدند.

۱۶. تابع TEXT در اکسل

تابع TEXT در اکسل برای تبدیل عدد به متن بر اساس ساختار فرمولی =TEXT(value, format_text)  است. در اینجا آرگومان اول یا value  مقدار عددی مورد نظر جهت تبدیل به متن و آرگومان format_text  قالب‌بندی مورد نظر برای اعمال بر متن است. به‌عنوان مثال اگر بخواهیم تاریخ موجود در جدول تصویر زیر را به حالت نوشتاری تبدیل کنیم، این کار با استفاده از فرمول =TEXT(A2,"mmmm d, yyy")  انجام می‌گیرد.

۱۷. تابع VALUE در اکسل

تابع VALUE در اکسل با ساختار ساده =VALUE(text)  عبارات متنی را به فرمت عددی تبدیل می‌کند. در تصویر زیر مثال‌های مختلفی از آن ارائه شده‌اند. همان‌طور که مشخص است در سلول‌های A3 و A4 که مقدارها همراه با واحد پولی است، پس از تعریف تابع، تنها بخش عددی آن در جدول درج می‌شود. در مورد آخر نیز که فرمت عبارت در قالب ساعت ۱۲ بعد از ظهر است، به شکل عدد ۰/۵ نمایش داده می‌شود. زیرا زمان در اکسل به صورت پیش‌فرض عددی بین ۰ تا ۱ است، یعنی اینکه ساعت «۰۰:۰۰:۰۰» ارزش ۰، ساعت «۱۲:۰۰:۰۰» ارزش ۰/۵، ساعت «۰۶:۰۰:۰۰» ارزش ۰/۲۵ دارد و به همین ترتیب بقیه اعداد بین این دو مقدار ارزش‌گذاری می‌شوند.

۱۸. توابع LARGE,SMALL در اکسل

توابع SMALL و LARGE در اکسل به ترتیب برای پیدا کردن کوچکترین و بزرگترین عدد در محدوده‌ای از داده‌های جدول استفاده می‌شوند. در اینجا ساختار فرمول های اکسل به شکل =SMALL (array, k)  و =LARGE(array, k)  خواهد بود. که در آن آرگومان array  نشان‌دهنده محدوده سلول مورد نظر برای جستجوی عدد و k  رتبه عدد در بین داده‌ها است. به‌عنوان مثال در تصویر زیر دومین عدد بزرگ در بین سلول‌های B2 تا B10 با استفاده از فرمول =LARGE(B2:B10,2)  برابر عدد ۲۸۰ خواهد بود.

به همین ترتیب سومین عدد کوچک در محدوده سلول B2 تا B10 در جدول زیر با استفاده از فرمول =SMALL(B2:B10,3)  برابر ۵۸۸۶۰ محاسبه می‌شود.

۱۹. تابع NOW در اکسل

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

۲۰. تابع TODAY در اکسل

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

با تعریف تابع =DAY(TODAY())  می‌توان روز میلادی ماهی که در آن قرار داریم را در سلول دلخواه خود مشخص کنیم.

کاربرد ترکیبی فرمول های اکسل بالا و تابع جدید MONTH به شکل =MONTH(TODAY())  ماه میلادی را در سلول مشخص می‌کند.

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

۲۱. تابع TIME در اکسل

تابع TIME در اکسل ساعت، دقیقه و ثانیه داده شده را به ساختار زمانی قابل خواندن در ساعت‌های دیجیتالی تبدیل می‌کند. به عنوان مثال ساعت ۸ و ۴۰ دقیقه و ۲۰ ثانیه شب که با تابع =TIME(20,40,20)  مشخص می‌شود، به شکل زیر نمایش داده خواهد شد.

۲۲. توابع HOUR, MINUTE, SECOND در اکسل

تابع HOUR در اکسل در ترکیب با تابع NOW طبق دستور =HOUR(NOW())  ساعتی که در آن قرار داریم را بر حسب اعدادی از ۰ تا ۲۳ نمایش می‌دهد. در این حالت صفر نماینده ساعت ۱۲ نیمه شب، ۲۳ نماینده ساعت ۱۱ شب است و به همین ترتیب سایر ساعت‌های شبانه‌روز مشخص می‌شوند. به‌عنوان مثال در تصویر زیر ساعت ۱۶ یا ۴ بعد از ظهر است.

همچنین تابع =MINUTE(NOW())  دقیقه‌ای که در آن قرار داریم را نشان می‌دهد.

و در نهایت دستور =SECOND(NOW())  نشان‌دهنده ثانیه زمانی است که در آن قرار داریم.

۲۳. تابع DATEIF در اکسل

با تابع DATEIF در اکسل و استفاده از دستورهای مختلف، تفاوت بین دو تاریخ از نظر سال، ماه یا روز را تعیین می‌کنیم. به عنوان مثال با استفاده از تعریف دستور =DATEIF(B12,B13,"Y")  تفاوت بین دو تاریخ میلادی درج شده در سلول‌های B12 و B13 بر حسب سال در تصویر زیر مشخص است.

اگر بخواهیم تفاوت این دو تاریخ را از لحاظ ماه و روز بدانیم، در این صورت به جای «Y» به ترتیب از «M» و «D» استفاده می‌کنیم.

۲۴. تابع TRIM در اکسل

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

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

۲۵. تابع FIND در اکسل

تابع FIND در اکسل به شکلی ساده مشخص می‌کند که یک کاراکتر در سلول اکسل، در موقعیت چندم از متن قرار دارد. به‌عنوان یکی از کاربردی‌ترین فرمول های اکسل ساختار آن به شکل =FIND(find_text,within_text,[start_num])  است. در این فرمول آرگومان‌ها به شکل زیر تعریف می‌شوند.

•  آرگومان find_text  کاراکتر مورد نظر
•  آرگومان within_text  : متن مورد نظر
•  آرگومان start_num  : جستجو از چندمین کاراکتر آغاز شود (آرگومان اختیاری)

به‌عنوان مثال در تصویر زیر برای مشخص کردن موقعیت حرف d در عبارت Find که در سلول A1 قرار دارد، فرمول به صورت =FIND("d", A1)  و نتیجه عدد ۴ خواهد بود. در صورتی‌که با تعریف فرمول =FIND("a", A1)  پیغام خطا را مشاهده می‌کنیم.

لازم به‌ذکر است که تابع FIND به حروف بزرگ و کوچک لاتین حساس است و هنگام تعریف فرمول باید به این موضوع توجه کرد.

۲۶. تابع SEARCH در اکسل

تابع SEARCH در اکسل ساختار فرمولی مشابه تابع FIND به شکل =SEARCH(find_text,within_text,[start_num])   دارد. این تابع نیز برای مشخص کردن موقعیت حروف یا بخشی از متن در یک رشته متنی به‌کار می‌رود. با این تفاوت که در اینجا بزرگ یا کوچک بودن حروف در ساختار فرمول تفاوتی ندارد.

به‌عنوان مثال برای تعیین موقعیت مکانی حرف «e» در عبارت «Excel» و کلمه «market» در رشته متنی «supermarket» از فرمول‌های مشخص شده در تصویر زیر استفاده می‌شود. نتیجه نهایی به ترتیب عدد ۱ و ۶ خواهد بود.

۲۷. تابع VLOOKUP در اکسل

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

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

در این ساختار هر آرگومان به شرح زیر تعریف می‌شود:

•  آرگومان lookup-value  : مقدار یا عبارت مورد نظر برای جستجو
•  آرگومان table_array  : محدوده جستجو در جدول داده اکسل
•  آرگومان col_index_num  : شماره ستونی که می‌خواهیم داده مد نظر را از آنجا جستجو کنیم
• آرگومان range_lookup  : در اینجا مشخص می‌کنیم که در جستجو به تطابق تقریبی یا دقیق نیاز داریم. برای تطابق دقیق کلمه «FALSE» یا عدد صفر و تطابق تقریبی کلمه «TRUE» یا عدد یک وارد می‌شود.

برای روشن شدن بحث به این مثال توجه کنید. می‌خواهیم بدانیم در دومین ستون سمت چپ عبارت «Lion» محدوده داده‌های جدول زیر، با تطابق دقیق عبارت، چه عددی نوشته شده است. بنابراین ساختار فرمول به‌صورت =VLOOKUP(E1,A2:B11,2,FALSE)  خواهد بود. در این فرمول برای راحتی کار کلمه Lion در ستونی خارجی از جدول داده یعنی E1 تایپ شده است.

۲۸. تابع HLOOKUP در اکسل

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

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

در این ساختار نیز هر آرگومان به شرح زیر تعریف می‌شود:

•  آرگومان lookup_value  : مقدار یا عبارت مورد نظر برای جستجو
•  آرگومان table_array  : محدوده جستجو در جدول داده اکسل
•  آرگومان row_index_num  : شماره ردیفی که می‌خواهیم داده مد نظر را از آنجا جستجو کنیم
•  آرگومان range_lookup  : نشان‌دهنده این است که در جستجو به تطابق تقریبی نیاز داریم یا دقیق.

به‌عنوان مثال در جدول داده‌های تصویر زیر به‌دنبال پیدا کردن «Diameter» یا قطر سیاره زمین یا «Earth» که در سلول B5 آن را درج کرده‌ایم، هستیم. در ساختار فرمول، آرگومان اول عبارت موجود در سلول B5، آرگومان دوم یا محدوده جستجو سلول B1 تا I3 و مقدار مورد نظر ما نیز در ردیف دوم قرار دارد. بنابراین فرمول ما =HLOOKUP(B5,B1:I3,2,FALSE)  خواهد بود.

۲۹. توابع IF, IFERROR در اکسل

تابع IF در اکسل بعد از بررسی شرط تعریف شده در صورت درست یا نادرست بودن آن، مقدار یا عبارت معینی را در جدول نمایش می‌دهد. به‌عنوان مثال، ابتدا می‌خواهیم بدانیم که آیا مقدار سلول A2 در تصویر زیر بزرگتر از ۵ است یا خیر. سپس در صورت اینکه بیشتر از ۵ است، جواب را با عبارت «Yes 4 is greater»، یا «بله ۴ بزرگتر است» و در غیر این صورت با پاسخ «No» مشخص کند. بنابراین تابع به شکل =IF(A2.5, "Yes 4 is greater", "NO")  در خواهد آمد.

البته تابع دیگری هم به نام IFERROR هم وجود دارد که در صورت تشخیص عملیات خطا پیغام تعیین شده را نشان می‌دهد. به‌عنوان مثال اگر بخواهیم عدد ۱۰ را بر صفر تقسیم کنیم، با این تابع پیغام دلخواه «Cannot divide» یا «غیر قابل تقسیم» در جدول نمایش داده خواهد شد.

۳۰. تابع INDEX در اکسل

تابع INDEX در اکسل برای جستجوی یک عدد در محدوده مورد نظر بر اساس شماره ردیف و ستون استفاده می‌شود. در ساختار ساده و یک بعدی این فرمول، بعد از مشخص کردن محدوده و سطر یا ستون، عددی که به‌دنبال آن هستیم را پیدا می‌کنیم. به‌عنوان مثال با تعریف فرمول =INDEX(C3:C7,5)  می‌خواهیم بدانیم در سطر پنجم محدوده سلول‌های C3 تا C7 چه عددی وجود دارد. همان‌طور که مشخص است، نتیجه عدد ۴ را نشان می‌دهد.

اما در ساختار دو بعدی این تابع، دو عدد به نشانه ردیف و ستونی که ما در آن به‌دنبال پیدا کردن مقدار مورد نظر خود هستیم، فرمول کمی تغییر می‌کند. به‌عنوان مثال در جدول زیر اگر عددی در تقاطع ردیف ۳، ستون ۴ و محدوده سلول‌های B3 تا F7 را جستجو می‌کنیم، با تعریف فرمول =INDEX(B3:F7,3,4)  نتیجه ۶۲۹ به‌دست خواهد آمد.

۳۱. تابع MATCH در اکسل

تابع MATCH در اکسل برای تعیین موقعیت مکانی مقداری معین در ردیف یا ستون جدولی از داده‌های اکسل مورد استفاده قرار می‌گیرد. به‌عنوان مثال فرمول =MATCH( "Laura", A2:A8,0) برای تعیین موقعیت مکانی کلمه «Laura» در جدول تصویر زیر به‌کار می‌رود. در اینجا عدد صفر نشان‌دهنده این است که می‌خواهیم کلمه کاملا به‌صورت دقیق با عبارت تطابق داشته باشد. در ضمن می‌توانیم برای راحتی جستجو، کلمه Laura را در سلولی دلخواه مانند E1 هم نوشت و در فرمول به‌جای کلمه استفاده کرد. همان‌طور که مشخص است موقعیت مکانی ۵ برای این عبارت به‌دست می‌آید که کاملا مطابق با جدول داده‌ها است.

۳۲. تابع CHOOSE در اکسل

تابع CHOOSE در اکسل برای انتخاب مقدار یا عبارتی در میان چندین داده در جدول اکسل بر اساس مقدار مشخص شده به‌کار می‌رود. به عنوان مثال فرمول =CHOOSE(3,"Mike", :Sally", "Amy", "Neal")  برای انتخاب سومین عبارت در جدول تصویر زیر از میان سایر کلمات موجود به‌کار می‌رود. همان‌طور که مشخص است، نتیجه «Amy» خواهد بود.

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

تابع COUNTIF در اکسل برای شمارش تعداد ارقام یا عبارت در محدوده‌ای از سلول جدول اکسل با شرطی معین به‌کار می‌رود. بحث را با مثالی در این خصوص پیش می‌بریم. جدول تصویر زیر داده‌های مربوط به تعداد ابتلا و مرگ ناشی از ویروس کرونا در کشورهای مختلف را نشان می‌دهد. اگر بخواهیم تعداد دفعات تکرار عبارت «Afghanistan» یا افغانستان در محدوده سلول G1 تا G21129 را محاسبه کنیم، فرمول به شکل =COUNTIF(G1:G21129, "Afghanistan")  و نتیجه عدد ۱۴۵ خواهد بود.

تابع مشابه دیگر برای شمارش، COUNTIFS است. با این تفاوت که در آن تعداد سلول‌های دارای بیش از یک شرط مشخص نمایش داده می‌شوند. به‌عنوان مثال در جدول قبل اگر بخواهیم روزهایی که در آن تعداد مبتلایان کشور هند بیشتر از ۱۰۰ نفر بوده است را مشخص کنیم، از فرمول زیر استفاده می‌کنیم. در اینجا، کل ستون G برای عبارت «India» و کل ستون E برای اعمال شرط اعداد بیشتر از ۱۰۰ جستجو می‌شود.

۳۴. تابع SUMIF در اکسل

تابع SUMIF در اکسل حاصل جمع اعداد موجود در محدوده سلول‌های مشخص اکسل با شرط معین را به‌دست می‌آورد. به‌عنوان مثال، جدول زیر بخشی از داده‌های تعداد مبتلایان به کرونا ویروس در کشورهای مختلف است. حال می‌خواهیم جمع کل افراد مبتلا در کشور هند تا تاریخ سوم ژوئن ۲۰۲۰ را محاسبه کنیم.

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

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

که در آن هر یک از پارامترها به شرح زیر است.

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

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

۳۵. تابع PMT در اکسل

تابع PMT در اکسل یکی از توابع مالی است که برای محاسبه مبلغ قسط وام دریافتی بر اساس نرخ بهره، تعداد اقساط و مبلغ وام مورد استفاده قرار می‌گیرد. ساختار تابع به شکل =PMT(rate, nper, pv, [fv], [type])  تعریف می‌شود که در آن هر آرگومان به شرح زیر است.

•  آرگومان rate  : نرخ بهره (به صورت درصد یا اعشار)
•  آرگومان nper  : تعداد اقساط وام
•  آرگومان pv  : کل مبلغ وام دریافتی
•  آرگومان fv  : مقدار پول نقدی که به‌عنوان مانده وام بعد از آخرین قسط وام پرداخت می‌شود.
•  آرگومان type  : زمان پرداخت وام

به‌عنوان مثال ساختار تابع برای محاسبه مبلغ سالانه قسط وام ۵ ساله‌ای به مبلغ ۱۰۰ هزار دلار با نرخ بهره ۷٪ به شکل =PMT(7%,5,100000)  خواهد بود. در تصویر زیر تابع PMT را در دو شکل برای همین مثال بر روی سلول‌های B1، B2،B3 اعمال می‌کنیم. تنها نکته، نحوه نمایش نتیجه نهایی است. در حالت «Currency» حاصل با فرمت واحد پول با دو رقم اعشار و در حالت «General» به شکل عدد معمولی منفی با نشانه بدهی مشخص است.

جمع‌بندی پایانی

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