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

آموزش فرمول های کاربردی اکسل

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

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

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

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

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

فرمول های ریاضی و مثلثاتی در اکسل (Math & Trigonometry)

از جمله دسته فرمول های کاربردی اکسل دسته فرمول های ریاضی و مثلثاتی در اکسل است. این دسته فرمول ها برای انجام محاسبات عددی و ریاضی پایه یا پیشرفته استفاده می‌شود.
مثال: SUM, ROUND, INT, MOD, RAND, SIN, PI

فرمول های آماری در اکسل (Statistical)

دسته دیگر فرمول های کاربردی در اکسل دسته فرمول های آماری در اکسل هستند. این فرمول ها برای تحلیل داده‌ها و محاسبه میانگین، انحراف معیار و …استفاده خواهد شد.
مثال: AVERAGE, MEDIAN, COUNT, STDEV, VAR, MAX, MIN

فرمول های مالی در اکسل (Financial)

دسته دیگر فرمول های کاربردی در اکسل، دسته فرمول های مالی در اکسل است.این دسته برای محاسبات مالی، اقساط، استهلاک و ارزش فعلی/آتی کاربرد دارد.
مثال: PMT, FV, NPV, SLN, DB, DDB, IRR

فرمول های متنی در اکسل (Text)

فرمول های متنی در اکسل ، دسته بسیار کاربردی از فرمول ها هستند که برای کار بر روی متون و پردازش و تغییر رشته‌های متنی بسیار کاربرد دارند.

مثال: LEFT, RIGHT, MID, LEN, TEXT, CONCAT, TRIM

فرمول های تاریخ و زمان در اکسل (Date & Time)

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

مثال: TODAY, NOW, DATE, TIME, DATEDIF, EDATE, EOMONTH

فرمول‎های منطقی در اکسل (Logical)

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

مثال: IF, AND, OR, NOT, IFS, SWITCH

فرمول های جست‌وجو و مرجع در اکسل (Lookup & Reference)

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

مثال: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, CHOOSE

فرمول‌ آرایه‌ای و ماتریسی در اکسل (Array & Matrix)

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

مثال: TRANSPOSE, MMULT, SEQUENCE, FILTER, UNIQUE, SORT

1. فرمول های ریاضی و مثلثاتی در اکسل (Math & Trigonometry)

1.1. فرمول SUM در اکسل

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

ساختار کلی فرمول SUM در اکسل

=SUM(محدوده موردنظر)

مثال:فرض کنید در اکسل یک لیست از اعداد مربوط به فروش ماهانه در ستون A داریم و می‌خواهیم مجموع آن‌ها را محاسبه کنیم.

1.2. فرمول Average در اکسل

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

1.3. ساختار کلی فرمول Average در اکسل

=AVERAGE(محدوده موردنظر)

مثال:

فرض کنید داده‌های زیر را داریم و می‌خواهیم میانگین فروش ماهانه را محاسبه کنیم:

1.4. فرمول Round در اکسل

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

1.5. ساختار فرمول Round در اکسل

ROUND(number, num_digits)

•  Number: عددی که می‌خواهید گرد کنید.
•  num_digits: تعداد رقم‌های اعشار مورد نظر (اگر مقدار 0 باشد، به نزدیک‌ترین عدد صحیح گرد می‌شود).
  مثال:

4.1 فرمول MOD در اکسل

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

ساختار فرمول MOD در اکسل

=MOD(number, divisor)

Number: عددی که می‌خواهید تقسیم کنید.

Divisor: عددی که می‌خواهید بر آن تقسیم کنید.
مثال:

•  number: عدد پایه‌ای که می‌خواهید به توان برسانید.
•  Power: مقدار توان موردنظر.

از آنجا که تعداد داده‌ها زوج است (4 عدد)، میانه میانگین دو عدد وسطی است. در اینجا این دو عدد 10 و 15 هستند، بنابراین میانه برابر با:

(10 + 15) / 2 = 12.5

2.4. فرمول MODE در اکسل

فرمول MODE در اکسل برای پیدا کردن مد (Mode) یک مجموعه داده استفاده می‌شود. مد به عددی اطلاق می‌شود که بیشترین فراوانی را در داده‌ها دارد. اگر بیشتر از یک عدد با بیشترین فراوانی وجود داشته باشد، اکسل اولین مد را برمی‌گرداند.

ساختار فرمول MODE در اکسل

=MODE(number1, [number2], …)

مثال:
فرض کنید داده‌های شما در ستون A به صورت زیر است:

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

=MODE(A1:A5)

2.5. فرمول STDEV.P در اکسل

فرمول STDEV.P در اکسل برای محاسبه انحراف معیار یک جامعه (population) استفاده می‌شود. انحراف معیار معیاری است که نشان می‌دهد داده‌ها چقدر از میانگین خود فاصله دارند. این فرمول که از فرمول های کاربردی در اکسل است، زمانی استفاده می‌شود که داده‌ها شامل کل جامعه آماری (تمام داده‌های موجود) باشند، نه یک نمونه از جامعه.

ساختار فرمول STDEV.P در اکسل

=STDEV.P(number1, [number2], …)

مثال:
فرض کنید داده‌های شما در ستون A به صورت زیر است:

برای محاسبه انحراف معیار این مجموعه داده‌ها با استفاده از STDEV.P، از فرمول زیر استفاده می‌کنید:

=STDEV.P(A1:A5)

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

نکته:

اگر شما فقط یک نمونه از داده‌ها (یک نمونه آماری) داشته باشید، باید از فرمول STDEV.S به جای STDEV.P استفاده کنید.

2.6. فرمول STDEV.S در اکسل

فرمول STDEV.S در اکسل برای محاسبه انحراف معیار یک نمونه (sample) استفاده می‌شود. این فرمول که از فرمول های کاربردی در اکسل است، زمانی کاربرد دارد که شما فقط بخشی از داده‌ها (نمونه‌ای از جامعه آماری) را دارید و می‌خواهید انحراف معیار آن نمونه را محاسبه کنید.

ساختار فرمول STDEV.S در اکسل:

=STDEV.S(number1, [number2], …)

•  number1, number2, … مجموعه‌ای از اعداد یا محدوده‌ای از داده‌ها که می‌خواهید انحراف معیار نمونه آن‌ها را محاسبه کنید.
  تفاوت STDEV.S با STDEV.P:
•  STDEV.P برای محاسبه انحراف معیار جامعه استفاده می‌شود (زمانی که تمامی داده‌های موجود را دارید).
•  STDEV.S برای محاسبه انحراف معیار یک نمونه استفاده می‌شود (زمانی که فقط بخشی از داده‌ها را دارید).
  مثال:
  فرض کنید داده‌های شما در ستون A به صورت زیر است:

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

=STDEV.S(A1:A5)

نتیجه این فرمول، انحراف معیار داده‌های نمونه را محاسبه می‌کند. برای این داده‌ها، نتیجه انحراف معیار تقریبا برابر با 7.905 خواهد بود.

نکته:

اگر داده‌های شما شامل تمام جامعه آماری هستند، باید از STDEV.P استفاده کنید.

2.7. فرمول VAR.P در اکسل

فرمول VAR.P در اکسل برای محاسبه واریانس یک جامعه (population variance) استفاده می‌شود. واریانس یک معیار است که نشان می‌دهد داده‌ها چقدر از میانگین خود فاصله دارند. این فرمول که از دسته فرمول های کاربردی در اکسل است زمانی استفاده می‌شود که داده‌ها شامل کل جامعه آماری (تمام داده‌های موجود) باشند، نه فقط یک نمونه از جامعه.

ساختار فرمول VAR.P در اکسل

=VAR.P(number1, [number2], …)

number1, number2, … مجموعه‌ای از اعداد یا محدوده‌ای از داده‌ها که می‌خواهید واریانس آن‌ها را محاسبه کنید.

تفاوت با VAR.S:

•  VAR.P برای محاسبه واریانس جامعه استفاده می‌شود (زمانی که تمامی داده‌های موجود را دارید).
•  VAR.S برای محاسبه واریانس یک نمونه استفاده می‌شود (زمانی که فقط بخشی از داده‌ها را دارید).
  مثال:
  فرض کنید داده‌های شما در ستون A به صورت زیر است:

برای محاسبه واریانس جامعه این داده‌ها با استفاده از VAR.P، از فرمول زیر استفاده می‌کنید:

=VAR.P(A1:A5)

نتیجه این فرمول، واریانس داده‌ها را محاسبه می‌کند. برای این داده‌ها، واریانس تقریبا برابر با 62.5 خواهد بود.

نکته:

اگر شما فقط یک نمونه از داده‌ها (یک نمونه آماری) دارید، باید از فرمول VAR.S به جای VAR.P استفاده کنید.

2.8. فرمول VAR.S در اکسل

فرمول VAR.S در اکسل برای محاسبه واریانس یک نمونه (sample variance) استفاده می‌شود. واریانس به‌طور کلی معیاری است برای اندازه‌گیری پراکندگی داده‌ها حول میانگین، و در این فرمول زمانی که داده‌ها فقط یک نمونه از جامعه آماری هستند (نه تمام داده‌ها)، استفاده می‌شود.

ساختار فرمول VAR.S در اکسل

=VAR.S(number1, [number2], …)

•  number1, number2, … مجموعه‌ای از اعداد یا محدوده‌ای از داده‌ها که می‌خواهید واریانس نمونه آن‌ها را محاسبه کنید.

تفاوت با VAR.P:

•  VAR.P برای محاسبه واریانس جامعه استفاده می‌شود (زمانی که تمامی داده‌های موجود را دارید).
•  VAR.S برای محاسبه واریانس یک نمونه استفاده می‌شود (زمانی که فقط بخشی از داده‌ها را دارید).

مثال:
فرض کنید داده‌های شما در ستون A به صورت زیر است:

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

=VAR.S(A1:A5)

نتیجه این فرمول، واریانس نمونه داده‌ها را محاسبه می‌کند. برای این داده‌ها، واریانس تقریبا برابر با 62.5 خواهد بود.

نکته:

اگر داده‌های شما شامل تمام جامعه آماری هستند، باید از فرمول VAR.P به جای VAR.S استفاده کنید.

نتیجه گیری

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

در کنار فرمول های ریاضی، فرمول های آماری در اکسل نقش مهمی در تجزیه و تحلیل داده‌ها ایفا می‌کنند. فرمول هایی مانند AVERAGE، MEDIAN، STDEV و COUNTIFS به کاربران کمک می‌کنند تا ویژگی‌های آماری مجموعه داده‌ها را درک کرده و تصمیم‌گیری‌های دقیق‌تری داشته باشند. به‌کارگیری این فرمول ها در پروژه‌های پژوهشی، مالی، مدیریتی و صنعتی باعث می‌شود که داده‌ها به شکل معنادار تفسیر شوند. در نتیجه، آشنایی و تسلط بر این دو دسته فرمول های کاربردی در اکسل، نه‌تنها مهارت کاربر در کار با نرم‌افزار را افزایش می‌دهد، بلکه او را به فردی توانمند در تحلیل داده‌ها تبدیل می‌کند.