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

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

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

۱-‌ جمع و تفریق در اکسل: جمع و تفریق دو فرمول اساسی و ضروری ریاضی در مایکروسافت اکسل است. برای محاسبات هزینه های کسب و کار در طول یک ماه اپراتورهای جمع و تفریق فوق العاده مفید هستند.
۲-‌ ضرب در نرم افزار اکسل: برای ضرب کردن مقادیر، از اپراتور ” * ” استفاده کنید. به جای افزودن یک آیتم چندین بار، از عملیات ضرب استفاده کنید.
۳-‌ تقسیم: عملیات تقسیم هنگام دوبخشی کردن یا شکافتن آیتم ها در یک گروه مفید است. از اپراتور ” / ” برای تقسیم اعداد یا مقادیر در سلول های صفحات گسترده استفاده کنید.

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

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

۱-‌ میانگین (Average): برای استفاده از فرمول میانگین در مایکروسافت اکسل، باید فرمول خود را به صورت Average () = بنوسید و به جای فاصله گذاشتن در بین اعداد از کاما استفاده کنید. وقتی که شما کلید Enter را در صفحه کلید خود فشار می دهید، مایکروسافت اکسل به صورت خودکار میانگین را محاسبه می کند و خروجی را نشان می دهد.

۲-‌ میانه (Median): حد وسط مجموعه ای از داده ها، مقداری است که در وسط قرار دارد. اگر شما مقادیر عددی را گرفته اید و آن را از بزرگ به کوچک چیده اید، حد وسط تکه ای است که در میان فهرست قرار می گیرد.

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

مطالعه نمائید: دانلود جزوه آموزشی توابع آماری در اکسل

۴-‌ ماکزیمم (Max): فرمول Max در مایکروسافت اکسل در واقع قطب مخالف فرمول Min است. این فرمول حداکثر مقدار مجموعه ای از داده ها را مشخص می کند. منبع: farazsoft.ir

پیشنهاد می کنیم حتما بخوانید: 175 کلید سریع در اکسل

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

1- تابع SUM: برای جمع کردن مقادیر است و عملکرد آن مشابه عملگر جمع است، اگر لازم باشد که تعداد زیادی عدد را با هم جمع بزنید، استفاده از عملگر جمع مشکل است، زیرا باید تک تک سلول‌ها را ارجاع دهید، ولی در تابع SUM می‌توانید محدوده تعریف کنید و این مسئله کارکرد را بسیار ساده می‌کند، به عنوان مثال (SUM(A1:A1000= هزار سلولی را که در فاصله A1 تا A1000 قرار دارند، جمع می‌زند.
2- تابع TRIM: اگر متنی را از برنامه‌های دیگر کپی کرده و در یکی از سلول‌های اکسل paste کنید، فضای خالی و سفید زیادی در آن سلول ایجاد می‌شود و باعث به هم ریختگی کل صفحه می‌شود. تابع TRIM کمک می‌کند این مشکل را حل کنید. این تابع را می‌توان در هر سلول به صورت جداگانه استفاده کرد.
3- تابع CLEAN: از این تابع میتوان برای حذف شکستگی خطوط در هر سلول استفاده کرد، به جای تابع TRIM،.
4- تابع () LEN= اگر می‌خواهید تعداد کاراکترهای موجود در یک سلول را بدست آورید، از تابع LEN استفاده کنید. برای مثال، برای تعیین تعداد کاراکترهای موجود در سلول A1 کافی است در هر سلول تایپ کنید (LEN(A1= و در همان لحظه پاسخ خود را دریافت کنید.

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

5- تابع () SQRT= برای تعیین ریشه دوم عدد ۱۷۶۴ چه راهی به ذهن شما می‌رسد؟ ما تابع SQRT را پیشنهاد می‌کنیم. به این منظور، عبارت (SQRT(1764= را در سلولی تایپ کنید و ENTER را بزنید. حالا پاسخ آماده است.
6- تابع () ROUND= همان‌طور که از نام این تابع مشخص است، با استفاده از آن می‌توانید اعداد را گرد کنید. برای کار با این تابع، به دو پارامتر نیاز دارید: عدد یا سلول، و عددی که می‌خواهید مقادیر براساس آن گرد شوند.

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

7- تابع SMALL: به‌منظور تعیین Kامین عدد کوچک در بین اعداد استفاده می‌شود. برخلاف تابع Min که همواره کوچکترین عدد یک مجموعه را خروجی می‌دهد، با استفاده از این تابع میتوان kامین عدد کوچک از مجموعه اعداد را پیدا نمود. منبع: http://wiki.sbmu.ac.ir
8- تابع MODE: شکل تابع (محدوده)=MODE - شرح تابع :داده با بیشترین فراوانی را در محدوده نمایش میدهد.
9- تابع ABS: شکل تابع (عدد)=ABS - شرح تابع :این تابع قدر مطلق عددمورد نظر را نمایش میدهد (بدون علامت)
10- تابع POWER: شکل تابع =POWER(A,B) - شرح تابع :این تابع عدد A را به توان B می‏رساند.
11- تابع OR: شکل تابع (شرط1و2)=OR - شرح تابع :این تابع حد‏اقل دوشرط رابرسی کرده واگر دو شرط نادرست باشند، نادرست را نمایش می دهد اگر حتی یکی از شرطها صحیح باشد خروجی تابع صحیح است.
12- تابع IF در اکسل: شکل تابع ( ;T ;Fشرط)=IF - شرح تابع : IFیک تابع شرطی ومنطقی است و ابتدا  شرط راچک کرده اگرشرط صحیح باشد عملیات  Tانجام می‏پذیرد واگر شرط نادرست باشد عملیات F. منبع: hadi-ac.com

مطالعه کنید: دانلود کتاب توابع آماری نرم افزار اکسل (Excel)

تعدادی دیگر از فرمول های مهم Excel

1- فرمول جمع زدن شرطی: تابع SUMIF که بسیاری از آن به عنوان مجموع شرطی یا جمع زدن شرطی یاد می‌کنند از جمله فرمول‌های بسیار مهم در مایکروسافت اکسل است که قطع به یقین به کار مهندس معمار یا عمران خواهد آمد. در اکسل فرمول جمع (SUM) را داریم که مقادیر موجود در یک محدوده را با هم جمع می‌کند. حال اگر قصد داشته باشیم که مقادیر موجود در یک محدوده در صورت برقرار بودن یک شرط خاص (IF) را با هم جمع بزنیم، باید از تابع SUMIF اکسل استفاده کنیم.
2- فرمول جستجو و فراخوانی: تابع VLOOKUP یک فرمول پرکاربرد اکسل برای مهندسان معمار و عمران است. آشنایی با این تابع می‌تواند خیلی از کارهای را ساده‌تر کند. با استفاده از فرمول VLOOKUP در اکسل می‌توان یک مقدار را در یک ستون جستجو کرد و عدد متناظر آن را در ستون‌های بعدی به دست آورد.
3- فرمول کف و سقف در اکسل: دو تابع کف یا جزء صحیح و سقف در واقع توابعی هستند که به ترتیب هر مقدار حقیقی که به آن‌ها داده شود را به بزرگ‌ترین عدد صحیح کوچک‌تر (یا مساوی) و کوچک‌ترین عدد صحیح بزرگ‌تر (یا مساوی) گرد می‌کنند. به عبارت دیگر تابع جز صحیح که به بزرگ‌ترین عدد صحیح قبل از آن گرد می‌کند یعنی به طور مثال ۲/۶ را به ۲ و ۳/۴- را به ۴- گرد می‌کند اما تابع سقف به کوچک‌ترین عدد صحیح بعد از آن گرد می‌کند یعنی ۲/۶ را به ۳ و ۳/۴- را به ۳- گرد می‌کند. این دو فرمول اکسل به صورت زیر بیان می‌شوند. =FLOOR(A1, 10) و =CEILING(A2, 0.25) منبع: ویکی ساختمون
4- فرمول TODAY: به جای این که در هر بار تاریخ را به صورت دستی در اکسل وارد کنید می توانید از تابع ()TODAY استفاده نمایید. به‌وسیله این تابع حتی نیازی به ارزش‌گذاری در پرانتز نیست. درواقع در هر بار که صفحه اکسل را باز می کنید می توانید با تاریخ فعلی و به روز مواجه شوید.

رایگان دانلود کنید: دانلود جزوه آموزش کاربردی نرم افزار اکسل (Excel)

5- فرمول PROPER: اکسل تنها بستر برای تجزیه و تحلیل داده ها نیست بلکه بستر مناسبی برای سازماندهی و مرتب سازی اطلاعات است. به هنگام وارد کردن مقادیر زیادی متن در اکسل، PROPER  فرمول بسیار خوبی برای سازماندهی اطلاعات است به این صورت که  حروف اول کلمات انگلیسی را به حروف بزرگ و بقیه را به حروف کوچک تبدیل می کند.
6- فرمول EVEN & ODD: تابع ODD، عدد مد نظر را صرف نظر از علامت به نزدیک ترین عدد فرد بزرگتر از آن عدد گرد می کند؛ بالعکس تابع EVEN عدد را به نزدیک ترین عدد زوج بزرگتر از آن عدد، گرد می کند.
7- فرمول CONCATENATE: این فرمول یکی از مهم ترین و کاربردی ترین فرمول اکسل می باشد. concatenate، اطلاعات متنی موجود در دو سلول را با یکدیگر ادغام کرده و در یک سلول نمایش می دهد. اگر در یک سلول نام و در سلول دیگر نام خانوادگی را داشته باشیم به وسیله این تابع می توان نام و نام خانوادگی را با یکدیگر ادغام کرده و در یک سلول نمایش داد. منبع: tahlilgary.com

حتما بخوانید: دانلود جزوه آموزشی توابع مالی در اکسل (Excel)

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

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

حتما بخوانید: دانلود جزوه آموزشی اکسل (microsoft excel)

در ادامه تعدادی دیگر از فرمول ها و دستورها و کلیدهای میانبر اکسل EXCEL را می توانید مشاهده کنید. منبع این دستورها جزوه (175 کلید سریع در اکسل) که در بالا معرفی شد و توسط آقای مرتضی نقدي گردآوری شده است می باشد. برای مشاهده دستورات بیشتر و کلیدهای میانبر و فرمول های کاملتر جزوه مذکور که در بالا معرفی شده است را دانلود نمائید. اساتید خصوصی اکسل هم می توانند در زمینه یادگیری این نرم افزار پرکاربرد به شما کمک نمایند.
فعال کردن / رفتن به کاربرگ بعدي (پیمایش سریع بین کاربرگ ها) Ctrl+PageDown
فعال کردن / رفتن به کاربرگ قبلی (پیمایش سریع بین کاربرگ ها) Ctrl+PageUP
انتقال به آخرین سلول/ محدوده که حاوي اطلاعات می باشد در جهت انتخابی Ctrl+  کلیدھای 4جھت
اشاره گر را به اندازه ي یک کلمه به راست منتقل می کند Ctrl+ راست
اشاره گر را به اندازه ي یک کلمه به چپ منتقل می کند Ctrl+ چپ
انتخاب تا آخرین سلول/ محدوده که حاوي اطلاعات می باشد در جهت انتخابی Ctrl+Shift+ کلیدھای 4جھت
محدوده انتخابی را در جهت نشان داده شده با کلیدها گسترش میدهد Shift+ کلیدھای 4جھت
A انتقال به اولین سلول کاربرگ یعنی 1 Ctrl+Home
کپی کردن محتوا یا فرمول سلول بالایی در سلول فعال Ctrl+'
تغییر دادن حالت نمایش از فرمول به مقدار و برعکس Ctrl+`
انتخاب کل یک محدوده / جدول / کاربرگ Ctrl+A
پس از نوشتن نام کامل تابع Function Arguments نمایش پنجره Ctrl+A

مهمترین دستورها و فرمول های اکسل مقدماتی و پیشرفته

Bold فعال/غیرفعال کردن فرمت فونت Ctrl+B
Italic فعال/غیرفعال کردن فرمت فونت Ctrl+I
Underline فعال/غیرفعال کردن فرمت فونت Ctrl+U
Fill Down دستور Ctrl+D
Fill Right دستور Ctrl+R
Print دستور Ctrl+P
Copy دستور Ctrl+C
Copy دستور Ctrl+Insert
Cut دستور Ctrl+X
Paste دستور Ctrl+V
Paste دستور Shift+Insert
Table تبدیل محدوه انتخاب شده به Ctrl+T
Table تبدیل محدوه انتخاب شده به Ctrl+L
Save دستور Ctrl+S
جدید workbook ایجاد یک Ctrl+N
Open دستور Ctrl+O
جاري workbook بستن Ctrl+W
Find and Replace جست وجو در صفحه گسترده - دستور Ctrl+F
Find and Replace جست وجو در صفحه گسترده - دستور Ctrl+H
Undo دستور Ctrl+Z
Redo دستور Ctrl+Y
Hyperlink دستور Ctrl+K
Go to دستور Ctrl+G
درج تاریخ جاري سیستم Ctrl+:
درج زمان جاري سیستم Ctrl+Shift+:

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

Help دستور F1
وارد شدن به / ویرایش سلول فعال F2
هنگام نوشتن فرمول. Paste Name نمایش کادر F3
تکرار دستور یا عمل قبلی F4
مطلق کردن آدرس فرمول ها(زمانی که روي آدرس فرمول کلیک کنیم) F5
Go To دستور F6
Spelling - بررسی املاي کلمات F7
گسترش ناحیه انتخاب شده F8
انجام محاسبات در کل کاربرگ ها (در صورت تنظیم محاسبات در حالت دستی) F9
Ribbon نمایش حروف فعال سازي دستورات F10
نمودار جدید (یا پیوت چارت جدید) (WorkSheet) افزودن یک کاربرگ F11
Save As دستور F12

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

در ادامه 25 تابع و فرمول مهم دیگر اکسل ارائه شده است. فرمول ها و توابع ارائه شده در ادامه بسیار کاربردی و مهم هستند.
1. SUM: تابع SUM همانطور که از نام آن پیداست، مجموع محدوده انتخاب شده از مقادیر سلول را نشان می دهد.
2. AVERAGE: تابع AVERAGE بر محاسبه میانگین محدوده انتخاب شده از مقادیر سلول تمرکز می کند.
3. COUNT: تابع COUNT تعداد کل سلول ها را در محدوده ای که شامل یک عدد است می شمارد.
4. SUBTOTAL: تابع SUBTOTAL مقدار فرعی را در پایگاه داده برمی گرداند. بسته به آنچه می خواهید، می توانید میانگین، تعداد، مجموع، حداقل، حداکثر، حداقل و موارد دیگر را انتخاب کنید.
5. MODULUS: تابع MOD روی برگرداندن باقی مانده زمانی که یک عدد خاص بر یک مقسوم علیه تقسیم می شود کار می کند.
6. POWER: تابع "Power()" نتیجه یک عدد افزایش یافته به یک توان خاص را برمی گرداند.
7. CEILING: تابع CEILING عددی را تا نزدیکترین مضرب یک عدد مشخص گرد می کند.
8. FLOOR: برخلاف تابع سقف، تابع کف عددی را تا نزدیکترین مضرب یک عدد مشخص گرد می کند.
9. CONCATENATE: این تابع چندین رشته متن را در یک رشته متن ادغام یا به هم می پیوندد.
10. LEN: تابع LEN تعداد کل کاراکترهای یک رشته را برمی گرداند. بنابراین، کاراکترهای کلی، از جمله فضاها و کاراکترهای خاص را شمارش می کند.
11. REPLACE: همانطور که از نام آن پیداست، تابع REPLACE روی جایگزینی بخشی از یک رشته متنی با یک رشته متن متفاوت کار می کند.
12. SUBSTITUTE: تابع SUBSTITUTE متن موجود را با یک متن جدید در یک رشته متن جایگزین می کند.
13. LEFT, RIGHT, MID: تابع LEFT تعداد کاراکترها را از ابتدای یک رشته متنی نشان می دهد. در همین حال، تابع MID کاراکترها را از وسط یک رشته متن، با توجه به موقعیت و طول شروع باز می گرداند. در نهایت، تابع right تعداد کاراکترهای انتهای یک رشته متن را برمی گرداند.
14. UPPER, LOWER, PROPER: تابع UPPER هر رشته متنی را به حروف بزرگ تبدیل می کند. در مقابل، تابع LOWER هر رشته متنی را به حروف کوچک تبدیل می کند. تابع PROPER هر رشته متنی را به حروف مناسب تبدیل می کند، یعنی حرف اول هر کلمه با حروف بزرگ و بقیه حروف کوچک خواهد بود.
15. NOW: تابع NOW در اکسل تاریخ و زمان فعلی سیستم را نشان می دهد.
16. TODAY: تابع TODAY در اکسل تاریخ فعلی سیستم را ارائه می دهد.
17. TIME: تابع TIME ساعت ها، دقیقه ها، ثانیه های داده شده به عنوان اعداد را به شماره سریال اکسل تبدیل می کند که با فرمت زمان فرمت شده است.
18. HOUR, MINUTE, SECOND: تابع HOUR ساعت را از یک مقدار زمانی به عنوان عددی از 0 تا 23 تولید می کند. در اینجا 0 به معنای 12 AM و 23 11 PM است.
19. DATEDIF: تابع DATEDIF تفاوت بین دو تاریخ را بر حسب سال، ماه یا روز ارائه می کند.
20. VLOOKUP: این تابع مخفف جستجوی عمودی است که مسئول جستجوی یک مقدار خاص در سمت چپ ترین ستون جدول است. سپس مقداری را در همان سطر از ستونی که شما مشخص کرده‌اید برمی‌گرداند.
21. HLOOKUP: تابع HLOOKUP به دنبال مقداری در ردیف بالای جدول یا آرایه ای از مزایا می گردد. این مقدار را در همان ستون از ردیفی که شما مشخص کرده اید می دهد.
22. IF Formula: این تابع یک شرط داده شده را بررسی می کند و اگر TRUE باشد مقدار خاصی را برمی گرداند. اگر شرط FALSE باشد مقدار دیگری را برمی گرداند.
23. INDEX-MATCH: تابع INDEX-MATCH برای برگرداندن یک مقدار در یک ستون در سمت چپ استفاده می شود. با VLOOKUP، شما در برگرداندن یک ارزیابی از یک ستون به سمت راست گیر کرده اید. دلیل دیگری برای استفاده از index-match به جای VLOOKUP این است که VLOOKUP به قدرت پردازش بیشتری از اکسل نیاز دارد. این به این دلیل است که باید کل آرایه جدولی را که انتخاب کرده اید ارزیابی کند. با INDEX-MATCH، اکسل فقط باید ستون جستجو و ستون بازگشت را در نظر بگیرد.
24. COUNTIF: تابع COUNTIF برای شمارش تعداد کل سلول‌ها در یک محدوده که شرایط داده شده را برآورده می‌کنند استفاده می‌شود.
25. SUMIF: تابع SUMIF سلول های مشخص شده توسط یک شرط یا معیار معین را اضافه می کند.

برای مشاهده مثال های عددی و واضح از این توابع به این صفحه مراجعه نمائید.

گردآوری: ایران مدرس