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

۱۶۱۴ بازدید
آخرین به‌روزرسانی: ۱۶ بهمن ۱۴۰۲
زمان مطالعه: ۱۵ دقیقه
فرمول های اکسل مهم که باید بلد باشید – معرفی ۳۵ تابع

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

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

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

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

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

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

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

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

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

مثال محاسبه مجموع اعداد با استفاده از تابع SUM

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

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

مثال تابع PRODUCT

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

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

مثال محاسبه میانگین اعداد در اکسل- EXCEL

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

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

مثال برای استفاده از فرمول COUNT در اکسل- EXCEL

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

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

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

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

مثال فرمول SUBTOTAL

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

مثال فرمول SUBTOTAL برای محاسبه حداکثر مقدار- EXCEL

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

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

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

مثال استفاده از تابع MOD-فرمول های اکسل

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

مثال استفاده از تابع MOD و باقیمانده صفر- فرمول های اکسل

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

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

مثال تابع POWER در اکسل- فرمول های اکسل

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

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

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

مثال تابع CEILING NV در اکسل- فرمول های اکسل

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

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

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

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

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

مثال استفاده از تابع Concatenate در اکسل- فرمول های اکسل

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

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

مثال تابع LEN در اکسل- فرمول های اکسل

۱۲. تابع 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 است، قسمت آخر دستور آن را تایپ می‌کنیم.

مثالی از کاربرد تابع REPLACE- فرمول های اکسل

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

مثالی از جایگزینی دو کاراکتر با تابع REPLACE-فرمول های اکسل

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

مثالی از جایگزینی کاراکترها با تابع REPLACE- فرمول های اکسل

۱۳. تابع 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")  خواهد بود.

مثال کاربرد تابع SUBSTITUTE در اکسل- فرمول های اکسل

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

مثال تابع SUBSTITUTE در اکسل با عبارات تکرار شونده- فرمول های اکسل

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

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

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

مثال کاربرد تابع LEFT در اکسل- فرمول های اکسل

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

مثال تابع MID در اکسل-فرمول های اکسل

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

مثال تابع RIGHT در اکسل-فرمول های اکسل

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

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

مثال تابع UPPER در اکسل-فرمول های اکسل

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

مثال تابع LOWER در اکسل-فرمول های اکسل

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

مثال تابع PROPER در اکسل-فرمول های اکسل

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

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

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

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

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

مثال تابع VALUE

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

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

مثال پیدا کردن دومین عدد بزرگ در اکسل

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

مثال سومین عدد کوچک در اکسل- EXCEL

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

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

مثال تابع NOW در اکسل

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

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

مثال تابع TODAY در اکسل- EXCEL

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

مثال تابع DAY در اکسل- EXCEL

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

مثال تابع MONTH در اکسل- EXCEL

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

تابع YEAR در اکسل- EXCEL

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

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

مثال تابع TIME در اکسل- EXCEL

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

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

مثال تابع HOUR در اکسل- EXCEL

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

مثال تابع MINUTE در اکسل- excel

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

تابع SECOND در اکسل

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

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

مثال تابع DATEIF

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

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

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

مثال تابع TRIM

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

نتیجه استفاده از تابع TRIM

۲۵. تابع 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 در اکسل- EXCEL

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

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

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

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

مثال تابع SEARCH

۲۷. تابع 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 تایپ شده است.

مثال تابع VLOOKUP

۲۸. تابع 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)  خواهد بود.

مثال فرمول HLOOKUP-

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

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

مثال تابع IF

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

مثال تابع IFERROR

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

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

مثال تابع INDEX

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

مثال تابع INDEX حالت دو بعدی- EXCEL

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

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

مثال تابع MATCH

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

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

مثال تابع CHOOSE در اکسل

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

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

داده‌های ابتلا به کرونا افغانستان- EXCEL

تابع COUNTIF برای یک سری داده مشخص-EXCEL

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

داده‌های ابتلا به کرونا هند- فرمول های EXCEL

مثال تابع COUNTIFS

۳۴. تابع 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» به شکل عدد معمولی منفی با نشانه بدهی مشخص است.

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

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

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

بر اساس رای ۶ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
simplilearnablebits1ablebits2
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *