مهم ترین فرمول های اکسل کدامند؟

تاریخ انتشار:
بازدید: 1,245 بازدید

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

۱. فرمول در اکسل چیست؟

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

در نهایت فرمول های اکسل همان دستوراتی هستند که در نرم افزار تعریف شده اند و نوع فعالیت ما را نسبت به داده های وارد شده تعیین می کنند. این دستورها می توانند از ساده ترین عملیات های ریاضی مانند جمع و تفرق تا پیچیده ترین منطق های شرطی را شامل شوند.

  • تفاوت Formula و Function

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

  • ساختار یک فرمول

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

شروع فرمول‌نویسی با مساوی است
شروع فرمول‌نویسی با مساوی است

  • نحوه شروع فرمول با علامت =

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

۲. فرمول های پایه و ضروری اکسل

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

  • SUM جمع اعداد

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

=SUM(A1:A10)

در این مثال به نرم افزار اکسل دستور داده شده تا مجموع اعداد تعریف شده در سلول های  A1تا A10 را محاسبه کند.

  • AVERAGE میانگین

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

= AVERAGE(B1:B5)

این تابع به نرم افزار این فرمان را می دهد تا میانگین اعداد ثبت شده در سلول های B1 تا B5 را محاسبه نماید. از این تابع می توان برای محاسبه میانگین فروش روزانه، میانگین دمای هوا در یک دوره و میانگین زمان صرف شده برای انجام وظایف بهره برد.

  • MAX بیشترین مقدار

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

=MAX (C1:C15)

در مثال بالا فرمول های اکسل اینگونه تعریف شده اند تا در بین سلول های C1 تا C156 بزرگترین عدد را تشخیص دهند.

  • MIN کمترین مقدار

تابع MIN نیز برای جستجوی کوچکترین عدد در مجموعه ای مشخص از اعداد استفاده می شود.

=MIN (D1:D15)

در واقع کاربر با استفاده از این تابع می تواند از بین اعداد تعریف شده در سلول های D1 تا D15 کمترین میزان را تعیین نماید.

  • COUNT شمارش سلول های عددی

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

=COUNT(E1:E30)

این تابع برای ما تعیین می نماید که چه تعداد سلول های عددی در بازه تعریف شده بین سلول های E1 تا E30 وجود دارد.

  • COUNTA شمارش سلول های غیرخالی

نقش تابع COUNTA در اکسل این است تا تعداد سلول هایی را که خالی نیستند ( حاوی اعداد و یا متن هستند) را مشخص نماید. برای مثال:

=COUNTA(F1:F25)

این تابع وظیفه دارد تا تعداد سلول های خالی در محدوده بین F1 تا F25 را مشخص کند.

۳. فرمول های منطقی در اکسل

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

  • IF شرط گذاری

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

=IF(logical _test, value _if _true, value _if _false)

برای درک این مورد، موقعیتی را در نظر بگیرید که وضعیت یک دانش آموز را در فرمول های اکسل بررسی می کنید. در این نمونه در سلول A1 نمره دانش آموز ثبت خواهد شد. در نتیجه در سلول B1 این مورد ثبت خواهد شد که اگر نمره ۱۰ یا بیشتر زمینه قبولی دانش آموز را فراهم می کند. در این شرایط این دانش آموز در دو موقعیت متغیر قبولی و یا مردودی قرار دارد.

بنابراین اگر نمره این دانش آموز در حدود ۱۵ باشد قبول است و اگر نمره ایشان در حدود ۸ باشد او مردود خواهد بود. از این فرمول غالبا برای دسته بندی مشتریان براساس دستیابی به اهداف و میزان خرید، همچنین برای ثبت موقعیت ارسال شده و یا تحویل داده شده سفارشات استفاده می کنند.

  • IFS چند شرطی

تابع IFS به کاربر این امکان را می دهد تا به طور همزمان و در امتداد یکدیگر چندین شرط را بررسی نماید. این تابع اولین شرطی که درست است را در نظر می گیرد و مقدار آن را باز می گرداند. در نهایت تابع IF توانایی بررسی تنها یک شرط را دارد در حالی که تابع IFS می تواند به طور همزمان چندین شرط را به صورت متوالی بررسی کند.

  • AND همه شروط برقرار باشند

تابع AND به کاربر این امکان را می دهد تا بررسی کند که آیا تمام آرگومان های منطقی درست (TRUE) و یا نادرست هستند. اگر تمام آرگومان ها درست TRUE  باشند تابع AND مقدار TRUE را بر می گرداند. در غیر این صورت تابع AND نسبت به بازگرداندن مقدار FALSE اقدام خواهد نمود.

=AND(logical1, [logical2],…)

فرض کنید شما در سلول A1 نمره آزمون اول و در سلول B1 نمره آزمون دوم را ثبت کرده اید. در این شرایط هدف از کاربرد این تابع این مورد است که آیا دانش آموز در هر دو آزمون نمره قبولی را کسب نموده و یا شرایط متفاوت می باشد.

=AND(A1 >=10, B1 >=10)

اگر A1=12 و B1=11 باشد نتیجه TRUE خواهد بود و در صورتی که A1=9 و B1=11 باشد نتیجه FALSE می شود.

  • OR حداقل یک شرط برقرار باشد

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

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

ساختار این تابع:

=OR(logical1, [logical2],…)

  • IFERROR مدیریت خطاها

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

ساختار:

=IFERROR(1/D2.”  ”)

۴. فرمول های جستجو و بازیابی اطلاعات در اکسل

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

  • XLOOKUP

XLOOKUP را می توانیم به عنوان یک تابع جستجو و ارجاع با عملکرد مدرن معرفی نماییم که از تمام مزایای تعریف شده در توابع VLOOKUP و HLOOKUP بهره مند می باشد. همچنین محدودیت های تعریف شده در این توابع در تابع XLOOKUP رفع شده است. این تابع می تواند در دامنه گسترده ای از سلول های چپ و راست جستجو نماید. بنابراین کاربر برای مدیریت فرایندها نیازی به مرتب نمودن جداول و داده ها ندارد. همین امر نیز سبب شده تا این تابع قابلیت بیشتری در مدیریت خطاها از خود به نمایش بگذارد. ساختار این تابع:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

در این ساختار lookup_value به معنی مقداری است که قصد جستجوی آن را دارید.  lookup_array نیز معرف محدوده ای می باشد که کاربر در آن به دنبال lookup_value می گردد. return_array نیز به محدوده ای اشاره دارد که مقدار مورد نظر از آن بازگردانده می شود. در این بین [if_not_found] نیز داده ای اختیاری به شمار می رود و اشاره به زمانی دارد که اگر [if_not_found] در محدوده تعیین شده یافت نشد برگردانده می شود. در نهایت می توانیم این عبارت را جایگزین IFERROR نیز به شمار آوریم.

[match_mode] نیز داده ای اختیاری است و در آن معمولا از اعداد ۰، -۱، ۱ و ۲ استفاده می گردد. در این عبارت صفر به معنی تطابق دقیق ( پیش فرض)، -۱ برای تطابق دقیق یا کوچکتر، ۱ برای تطابق دقیق و یا موارد بزرگتر و در نهایت ۲ نیز برای تطابق الگوها مورد استفاده قرار می گیرند. [search_mode] نیز عبارتی اختیاری به شمار می رود که در آن عدد ۱ برای جستجو از ابتدا و انتهای سلول ها ( پیش فرض)، -۱ برای جستجوی از انتها به ابتدا، ۲ برای جستجوی باینری صعودی و در نهایت -۲ نیز برای جستجوی باینری نزولی مورد استفاده قرار می گیرد.

مثال: فرض کنید ستون A کد مشتری و ستون B نیز نام مشتری باشد. در نهایت کد مشتری در سلول E1 چه خواهد بود. این فرمول به طور خودکار در محدوده های تعیین شده جستجو خواهد نمود تا مقدار E1 را پیدا نماید. در صورتی که تابع نتواند مقدار مورد نظر را بیاید از عبارت مشتری یافت نشد برای بیان این موضوع بهره می برد.

  • VLOOKUP

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

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

در این تابع عبارت lookup_value بیان کننده مقداری است که قصد دارید آن را جستجو نمایید. عبارت table_array به جدولی که در آن جستجو انجام می شود اشاره می نماید. به خاطر داشته باشید که ستون اول در عبارت table_array باید حاوی lookup_value باشد. در این بین عبارت col_index_num به شماره ستونی از table_array اشاره می نماید که شما قصد دارید مقدار آن را بیابید. [range_lookup] نیز داده ای اختیاری به شمار می رود که برای آن از دو اصطلاح TRUE به معنی جستجوی تقریبی و FALSE برای جستجوی دقیق استفاده می کنند.

برای مثال: فرض کنید جدولی از مشتریان دارید که در ستون A کد مشتری و در ستون B نام مشتری و ستونC  نیز حاوی شهر باشد. شما در این مثال کد مشتری را در سلول E1 قرار داده اید و می خواهید نام مشتری را سلول F1 نمایش دهید.

=VLOOKUP(E1, A1:C100,2,FALSE)

با استفاده از این فرمول کاربر می تواند کد مشتری E1 را در ستون A ( ستون اول جدول A1:C100) جستجو کند و سپس مقدار ستون دوم یا همان نام مشتری را برگرداند. در این تابع FALSE بر این امر تاکید می نماید که جستجو به صورت کاملا دقیق انجام شود.

  • HLOOKUP

از تابع HLOOKUP در اکسل برای جستجو در ردیف اول یک جدول و بازگرداندن مقدار تعریف شده در ردیف های پایین تر همان ستون بهره می برند. ساختار کلی این تابع به شکل زیر می باشد:

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

lookup_value در این تابع بیان کننده مقداری است که در ردیف اول جدول قصد پیدا نمودن آن را داریم. در این بین table_array نیز کل جدولی را که در آن جستجو صورت می گیرد را به نمایش می گذارد. برای مثال در این مورد A1:D4 در واقع بیان کننده عبارت table_array در تابع می باشد. نکته مهم در تابع HLOOKUP این است که فقط قادر به جستجو در ردیف اول هر محدوده است.

  • INDEX + MATCH

ترکیب INDEX + MATCH یک تابع قدرتمند و انعطاف پذیر برای جستجوی داده ها در اکسل می باشد. این تابع عموما محدودیت های شناخته شده در تابع VLOOKUP را ندارد. MATCH در این تابع موقعیت مقدار مورد نظر را پیدا می کند و INDEX از این موقعیت برای بازیابی مقدار از ستون دیگر استفاده می نمایند. در نهایت در عبارت row_index_num مشخص می شود که مقدار کدام ردیف بازگردانده می شود.

برای مثال مقدار بدهی در سه ردیف را در همان ستون ها به کمک این عبارت تعیین می نمایند. آرگومان range_lookup نیز دارای دو حالت اصلی FALSE یا همان جستجوی دقیق و TRUE برای جستجوی تقریبی می باشد. در نهایت اگر به جای این آرگومان چیزی درج نگردد تابع به طور خودکار عبارت جستجوی تقریبی را درج خواهد نمود.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

در این تابع عبارت lookup_value بیان کننده مقداری است که قصد داریم آن را جستجو کنیم. lookup_range نیز محدوده ای است که lookup_value در آن جستجو می گردد. بنابراین این محدوده حاوی یک سطر یا ستون می باشد.  return_range معرف محدوده ای است که مقدار مورد نظر شما از آن برگردانده می شود. این محدوده نیز فقط از یک سطر و ستون تشکیل شده و باید هم اندازه LOOKUP-range باشد.

مثال:

فرض کنید در ستون A کد محصول و در ستون C قیمت محصول درج شده باشد. در نهایت کد محصول مورد نظر شما در سلول E1 قرار دارد.

=INDEX(C1:C100, MATCH(E1, A1:A100, 0))

در این فرمول تابع MATCH موقعیت کد محصول در سلول E1 را در ستون A پیدا می نماید و سپس INDEX از این موقعیت بری بازیابی قیمت محصول از ستون C استفاده می کند. سرعت جستجو از طریق این تابع نسبت به توابع دیگر بسیار بیشتر می باشد و می تواند جستجو را از سلول های چپ نیز انجام دهد. همچنین این تابع انعطاف پذیری بیشتری برای انتخاب ستون های جستجو برای بازگشت آنها دارد.

۵. فرمول های متنی در اکسل

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

  • CONCAT

این تابع دو یا چند رشته از یک تابع متنی را به یکدیگر می چسباند. این تابع بسیار جدید و کارآمد می باشد و در نسخه های جدید اکسل پشتیبانی می گردد. تابع CONCAT می تواند دامنه های متنی را نیز ادغام می نماید. ساختار این تابع به شکل زیر می باشد:

=CONCAT(text1, [text2], …)

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

  • TEXTJOIN

از تابع TEXTJOIN برای بررسی ترکیب چند رشته با جدا کننده بهره می برند. در این تابع عموما سلول های خالی نادیده گرفته می شوند. ساختار این تابع شامل:

=TEXTJOIN(“-“, TRUE, A2:A10)

  • LEFT

تابع فوق وظیفه دارد تعدادی کاراکتر را از ابتدای یک دسته متنی و به ترتیب از سمت چپ استخراج نماید. ساختار این تابع نیز:

=LEFT(TEXT, [NUM_CHARS])

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

  • RIGHT

وظیفه این تابع این است تا تعدادی کاراکتر را از انتهای یک رشته متنی (از سمت راست) استخراج نماید. ساختار این تابع نیز به شکل زیر می باشد:

=RIGHT(text, [num_chars])

در این تابع text بیان کننده رشته متنی می باشد که قصد استخراج آن را دارید. [num_chars] نیز یک عبارت اختیاری است و تعداد کاراکترهایی که می خواهید از سمت راست استخراج نمایید را مشخص می نماید. در نهایت در صورت عدم ثبت این آرگومان توسط کاربر اکسل به صورت پیش فرض برای آن یک کاراکتر در نظر می گیرد. عموما از این تابع برای استخراج پسوند کد محصول و جدا کردن بخش آخر شماره شناسایی استفاده می کنند. در نظر داشته باشید در این تابع اگر [num_chars] بزرگتر از طول متن باشد کل متن توسط تابع برگردانده می شود.

  • MID

تابع MID  می تواند به استخراج تعدادی کاراکتر از میان یک رشته متنی در موقعیت مشخص کمک نماید. ساختار تابع شامل:

=MID(text, start_num, num_chars)

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

  • LEN

تابع LEN نیز در واقع به منظور بررسی تعداد کاراکترهای موجود در یک رشته متنی مورد استفاده قرار می گیرد. ساختار این تابع نیز شامل:

=LEN(text)

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

  • TRIM

از این تابع به منظور حذف فاصله های اضافه در متن ها بهره می برند و ساختار آن به شکل زیر می باشد.

=TRIM(A2)

  • TEXT

یکی از توابعی می باشد که به تبدیل عدد به متن در یک قالب مشخص کمک می نماید. ساختار این تابع نیز شامل زیر می باشد:

=TEXT(A2,”0.00″)

۶.فرمول های شرطی پرکاربرد

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

  • COUNTIF

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

=COUNTIF(range, criteria)

مثال: در عبارت زیر تعداد سلول ها در محدوده A1 تا A10 مقداری بزرگتر از عدد ۵۰ می باشد.

=COUNTIF(A1:A10, “>50 “)

  • COUNTIFS

این تابع نیز کاملا مشابه تابع COUNTIF عمل می نماید و تنها تفاوت آن با تابع قبل در این است که امکان بررسی چندین شرط را برای شمارش سلول ها فراهم می نماید. ساختار این تابع نیز به شکل زیر می باشد:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2], …)

  • SUMIF

تابع SUMIF نیز مجموعه ای از سلول ها را که در یک محدوده با موقعیت مشخص قرار دارند را محاسبه می کند. ساختار این تابع نیز به شکل زیر می باشد:

=SUMIF(range, criteria, [sum_range])

  • SUMIFS

این تابع عملکردی مشابه تابع SUMIF از خود نمایش می دهد و امکان جمع بر اساس چندین شرط را برای کاربر محقق می نماید ساختار این تابع نیز به شرح زیر می باشد:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)

  • AVERAGEIF

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

=AVERAGEIF(range, criteria, [average_range])

  • AVERAGEIFS

تابع AVERAGEIFS کاملا از نظر ساختاری مانند تابع AVERAGEIF عمل می نماید. اما نکته تمایز این دو تابع در این است که از تابع  AVERAGEIFS در شرایطی استفاده می کنند که چندین شرط منظور گردیده باشد. ساختار این تابع نیز به شرح زیر می باشد:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)

۷.فرمول های تاریخ و زمان

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

  • TODAY

در واقع این تابع وظیفه دارد تا تاریخ جاری در یک سیستم را به نمایش بگذارد. این تابع فاقد هرگونه آرگومانی می باشد و نمایش آن با تغییر تاریخ سیستم به روز می شود. از این تابع برای تعیین مهلت سر رسیدها و تاریخ ارائه خودکار گزارش ها بهره می برند.

  • NOW

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

  • YEAR

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

=YEAR(serial_number)

  • MONTH

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

=MONTH(serial_number)

  • DAY

این تابع نیز همانگونه که از نام آن مشخص است ابزاری کاربردی برای استخراج یک روز خاص از یک تاریخ مشخص به شمار می رود. ساختار این تابع نیز شامل:

=DAY(serial_number)

  • DATEDIF

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

=DATEDIF(A2,B2,”d”)

  • NETWORKDAYS

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

=NETWORKDAYS(A2,B2)

۸.توابع جدید اکسل ۳۶۵

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

  • FILTER

تابع FILTER  که در نسخه های جدید اکسل تعریف شده به شما این امکان را  می دهد تا بخشی از داده های یک محدوده را بر اساس شرط های مشخص فیلتر کرده و نتایج را در سلول های مجاور نمایش دهید. از این تابع می توانیم برای استخراج سطرهایی از یک جدول که معیارهای خاصی دارد بهره ببریم. مثال: فرض کنید جدولی از فروش در سلول های A1:C100 دارید که در آن A بیان کننده منطقه، B محصول و C مقدار فروش می باشد. ساختار این تابع نیز به شکل زیر می باشد:

=FILTER(A2:C100, B2:B100>10)

  • UNIQUE

این تابع (UNIQUE) لیستی از مقادیر منحصر به فرد ( بدون تکرار) را از یک محدوده داده استخراج می نماید. از این تابع عموما برای یافتن لیست تمام آیتم های یکتا در یک ستون ( مانند لیست مشتریان و یا محصولات) بهره می برند. ساختار تعریف شده برای این تابع نیز به شرح زیر می باشد:

=UNIQUE(array, [by_col], [exactly_once])

برای مثال از این تابع برای استخراج لیست تمام محصولات منحصر به فرد از ستون B در محدوده B1:B500 بهره می برند:

=UNIQUE(B1:B500)

  • SORT

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

=SORT(A2:A100, 1, TRUE)

  • SORTBY

این تابع (SORTBY) داده های یک محدوده را بر اساس مقادیر موجود در یک محدوده یا ستون مرتب می نماید. کاربرد اصلی این تابع نیز زمانی است که می خواهید جدولی را بر اساس ستونی که بخشی از جدول اصلی نیست مرتب نمایید. ساختار این تابع نیز به شرح زیر می باشد:

=SORTBY(array, by_array1, [sort_order1], [sort_order2], [by_array2])

برای مثال فرض کنید نام محصولات در ستون A و امتیاز آنها در ستون D درج شود. برای مرتب سازی این محصولات براساس امتیاز قید شده برای آنها به صورت نزولی به این شکل باید اقدام نمود:

=SORTBY(A1:A100, D1:D100, -1)

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

=SEQUENCE([rows], [columns], [start], [step])

  • XMATCH

این تابع نیز بسیار شبیه تابع MATCH می باشد تفاوت اصلی این دو تابع در این این است که تابع XMATCH از قابلیت های پیشرفته تر و انعطاف پذیری بیشتری برخوردار است. در نتیجه می توانیم این تابع را به عنوان یک مکمل اصلی برای تابع XLOOKUP در نظر بگیریم. از این تابع عموما برای یافتن موقعیت یک آیتم در یک آرایه بهره می برند. از مهم ترین مزایای این تابع نیز می توانیم به امکان جستجوی از آخر به اول در تابع اشاره نماییم. همچنین این تابع برای جستجوی دودویی برای داده های مرتب شده نیز تطابق پذیر می باشد. ساختار این تابع نیز به شرح زیر است:

=XMATCH([lookup_value, lookup_array, [match_mode], [search_mode])

مثال: برای یافتن موقعیت محصول H2 در لیست محصولات A1:A100 به این صورت می توان اقدام نمود. در این موقعیت برای تطابق دقیق از عدد صفر استفاده می نماییم.

=XMATCH(H2, A1:A100, 0, “محصول”)

۹.ده فرمولی که هر کاربر اکسل باید بلد باشد

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

  • SUM

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

  • IF

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

  • XLOOKUP

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

  • FILTER

تابع FILTER نیز یکی از کاربردی ترین توابع برای استخراج و نمایش داده ها براساس شرایط به شمار می رود. در نسخه های جدید این تابع وجود دارد و در دسترس کاربران می باشد.

  • COUNTIF

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

  • SUMIFS

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

  • تابع INDEX

این تابع را می توانیم به عنوان بخشی قدرتمند از ترکیب تابع INDEX/MATCH به شمار آوریم که از آن به منظور استخراج داده ها در موقعیت مشخص بهره می برند.

  • MATCH

این تابع نیز بخشی از ترکیب توابع INDEX/MATCH به شمار می رود که از آن به منظور یافتن موقعیت یک داده استفاده می کنند.

  • IFERROR

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

=IFERROR(value, value_if_error)

  • TEXTJOIN

از این تابع به منظور ترکیب متن همراه با جداکننده دلخواه و نادیده گرفتن سلول های خالی بهره می برند. تابع فوق یکی از فرمول های اکسل می باشد که در بین کاربران کارایی ویژه دارد.

۱۰.خطاهای رایج در فرمول نویسی اکسل

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

  • خطای #N/A

این خطا زمانی رخ می دهد که مقدار مورد نظر ما که قصد بررسی آن را در تابع های جستجو مانند تابع VLOOKUP و MATCH داریم یافت نمی شود.

  • خطای #VALUE!

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

  • خطای #DIV/0!

خطای # DIV/0 ! زمانی رخ می دهد که کاربر قصد دارد نسبت به تقسیم یک عدد بر صفر اقدام نماید. در این شرایط ما شاهد وقوع این ارور فوق خواهیم بود. بنابراین با توجه به میزان شناخت خود نسبت به فرمول های اکسل می توانیم از بروز این خطا پیشگیری کنیم.

  • خطای #REF!

این خطا نیز زمانی رخ می دهد که فرمول ما به یک سلول یا محدوده ارجاع داده می شود که فاقد اعتبار است. برای این شرایط می توانیم به زمانی اشاره کنیم که سلول مورد نظر ما حذف شده است.

  • خطای #NAME?

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

سوالات متداول ( FAQ )

  • مهم ترین فرمول های اکسل چیست؟

انتخاب مهم ترین فرمول اکسل می تواند امری سلیقه ای و وابسته به نوع فعالیت شما باشد. با این حال به طور معمول فرمول هایی مانند IF برای توابع منطقی و شرطی و تابع SUM برای اجرای محاسبات پایه و همچنین توابع XLOOKUP و VLOOKUP که در فرایند بازیابی اطلاعات مورد استفاده قرار می گیرند از مهم ترین توابع این نرم افزار البته از نگاه کاربران به شمار می روند.

  • برای جستجو در اکسل از VLOOKUP استفاده کنیم یا XLOOKUP ؟

فرمول XLOOKUP به طور کلی نسبت به فرمول VLOOKUP ارجحیت دارد. علت این امر نیز در این است که فرمول فوق عموما از انعطاف پذیری، قدرت و قابلیت کاربرد بهتری نسبت به فرمول VLOOKUP برخوردار می باشد. همچنین تابع XLOOKUP می تواند در جستجوی داده ها در سلول های چپ و راست مورد استفاده قرار گیرد. در نهایت مدیریت خطاها در این تابع بسیار ساده تر از مدل های دیگر می باشد.

  • بهترین فرمول های اکسل برای حسابداری کدامند؟

در زمان اجرای فرایند حسابداری بهتر است تا کاربران از مجموعه ای از فرمول های اکسل استفاده نمایند. توابع SUMIF و SUMIFS از جمله توابعی می باشند که از آنها برای جمع زدن مقادیر بر اساس چندین معیار بهره می برند. همچنین از توابع COUNTIFS / COUNTIF نیز می توانیم برای شمارش تراکنش ها یا تطبیق اقلام بهره ببریم. IFERROR تابعی است که برای مدیریت خطاهای محاسباتی و نمایش پیام های واضح مورد استفاده قرار می گیرد. توابع XLOOKUP / VLOOKUP نیز از جمله توابعی هستند که از آنها برای لیست تعداد مشتریان یا کدهای حسابداری بهره می برند.

  • فرمول های اکسل ۳۶۵ چه تفاوتی با نسخه های قدیمی دارند؟

تفاوت اصلی در فرمول های اکسل ۳۶۵ با نسخه های قدیمی در معرفی توابع پویا مانند FILTER، UNIQUE، SORT، XLOOKUP و SEQUENCE است. این توابع نتایج را در چندین سلول پخش می کنند. در نتیجه کارایی و انعطاف پذیری را به شدت افزایش می دهند. همچنین، بسیاری از توابع در نسخه های جدیدتر بهبود یافته اند و قابلیت های بیشتری دارند.

  • یادگیری فرمول های اکسل چقدر زمان می برد؟

مدت زمان یادگیری فرمول های اکسل به هدف و میزان تمرین شما بستگی دارد. بنابراین کاربران ممکن است فرمول های پایه را در ظرف چند ساعت بیاموزند. با این حال ممکن است شما برای یادگیری فرمول های پیشرفته تر به زمان بیشتری نیاز داشته باشید. در نهایت مهم ترین عنصر در فراگیری توابع تمرین مداوم و کاربردی کردن فرمول ها در پروژه های واقعی است.

مطالعه بیشتر

نوشته قبلی

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

ادغام متن در سلول: راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN (با مثال کاربردی)