چگونه در اکسل فرمول نویسی کنیم : (آموزش تضمینی و جامع فرمول نویسی در اکسل)|آکادمي استاد آموز

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

نکته : قبل از نوشتن فرمول باید حتما از = استفاده کنیم تا اکسل متوجه فرمول شود و  محاسبات را انجام دهد ، در غیر این صورت بعد ار فشردن اینتر  عبارت 12+10+5 نمایش داده می شود .

چند نمونه فرمول ساده در اکسل

فرمول ضرب در اکسل

فرمول توان در اکسل

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

تغییر و ویرایش فرمول در اکسل

• برای تغییر و ویرایش فرمول کافی است خانه ای که فرمولی در آن نوشتیم ، انتخاب کنیم .
• روی نوار فرمول در بالای صفحه کلیک می کنیم .
• و فرمول را تغییر می دهیم.
• سپس در پایان روی دکمه اینتر(ٍEnter) از نوار فرمول یا کی بورد کلیک می کنیم .

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

1. *(عملگرضرب )
2. /(عملگر تقسیم)
3. +(عملگر جمع)
4. -(عملگر تفریق)
5. ^(عملگر توان )

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

برای محاسبه معدل ابتدا نمرات را با هم جمع کنیم و حاصل جمع را بر تعداد نمرات تقسیم می کنیم.

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

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

4/(15.25+16.5+12+10.5)=

حاصل معدل نمرات ، عدد  13.5625 می شود.

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

1. مثلا اگر نمره 12 به عد 14.5 تغییر پیدا کند باید فرمول را تغییر دهیم و عدد 12 را پاک و عدد 14.5 را جایگزین کنیم .
2. ممکنه نمره که تغییر کرده را فراموش کنیم در محاسبات هم تغییر دهیم و احتمال خطا و اشتباه بالا  رود .

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

• پس ابتدا در خانه  I8 علامت = را تایپ می کنیم و پرانتز را باز می کنیم.
•  روی نمره اول ، که نمره علی می باشد و برابر 15.25 است کلیک می کنیم آدرس خانه که I3 می باشد در فرمول قرار می گیرد.
• عملگر + را از کی بورد تایپ می کنیم سپس روی نمره بعدی 16.5 که متعلق به حمید می باشد کلیک می کنیم و آدرس آن I4 در فرمول نوشته می شود .
• به ترتیب تا نمره آخر جلو می رویم و پرانتز را می بندیم .
• و عملگر / را تایپ می کنیم سپس عدد 4 را می نویسیم .
• با فشردن اینتر از کی بورد معدل 13.5625  بدست می آید.

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

• اما در فرمول نویسی با آدرس خانه اگر نمره ای را تغییر دهیم ، معدل هم سریع بعد از فشردن اینتر از کی بورد تغییر می کند .
• برای مثال اگر نمره فرهاد که 10.5 می باشد را به 20 تغییر دهیم و Enter از کی بورد را بفشاریم.
• معدل هم بعد از اینتر به عدد 15.9375 تغییر می کند و لازم نیست مانند روش قبلی نمره تغییر یافته را دوباره در فرمول بنویسیم تا محاسبات جدید انجام شود .
  

برای مثال  قیمت واحد کتاب ها و تعداد آنها را داریم می خواهیم  قیمت کل تمام کتاب ها را با فرمول نویسی در اکسل بدست آوریم؟

ابتدا قیمت کل هر کتاب ، که حاصل قیمت کتاب در تعداد آن است را محاسبه می کند.

• ابتدا در ستون مبلغ پرداختی E2  کلیک می کنیم ، علامت = را تایپ می کنیم .
•  و روی قیمت کتاب اول که 45000 تومان است کلیک می کنیم تا آدرس خانه C2  درج شود.
• سپس عملگر *  را تایپ می کنیم.
• سپس روی خانه تعداد کتاب ( D2 )کلیک می کنیم.
• و دکمه  اینتر از کی بورد را می فشاریم .حاصضرب این دو مقدار مطابق شکل بالا 90000 تومان می شود و فرمول آن هم (D2*C2=) ااست.
  
• در ستون پرداختی ردیف بعدی سلول E3 کلیک می کنیم و علامت = را تایپ می کنیم
•  قیمت کتاب را در تعداد آن ضرب می کنیم (D3*C3) 
• برای ردیف های دیگر هم ستون مبلغ پرداختی که شامل ضرب قیمت در تعداد کتاب است را پر می کنیم.
  
  

حالا می خواهیم قیمت کل تمام کتاب ها را بدست آوریم:

• برای محاسبه مبلغ کل پرداختی علامت = را تایپ می کنیم .
• روی خانه E2 کلیک می کنیم تا آدرس خانه درج شود.
• علامت + را تایپ می کنیم.
  روی خانه E3 کلیک می کنیم سپس + را تایپ می کنیم .
  و به ترتیب تا جایی که عدد برای جمع داریم ادامه می دهیم .
• در پایان روی دکمه اینتر از کی بورد کلیک می کنیم تا قیمت کل کتاب ها  566500 نشان داده شود  .

اولویت عملگرها در اکسل

1. ()
2. ^
3. / و * (اولویت برابر)
4. + و – (اولویت برابر)

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

برای مثال در خانه A2 فرمول 5*2^3+(5+6)*3-10  را می نویسیم می خواهیم ببینیم طبق اولویت عملگرهایی که در قسمت بالا توضیح دادیم ترتیب اجرای عملگرها به چه صورت می باشد .

• ابتدا عملگر پرانتز که در بالا ترین اولویت می باشد اجرا می شود و 6+5 می شود و نتیجه 11 حاصل می گردد.
•   عملگر بعدی که اولویت بالایی دارد 2^3 (عدد سه به توان دو) که نتیجه آن عدد 9 می شود .
  
• اکنون عبارت بالا  بصورت 5*9+(11)*3-10 می گردد.
• اولویت بعدی عمگر * است که به ترتیب از چپ به راست اجرا می شوند پس اول عدد 3 در عدد 11 ضرب می شود و نتیجه 33 حاصل می شود و بعد عدد 9 در عدد 5 ضرب می گردد و حاصل 45 بدست می آید .
•  عبارت بالا به صورت 45+33-10  می شود .
•  عملگر تفریق و جمع هم اولویت برابر دارند و به ترتیب از چپ به راست اجرا می شوند اول عدد 10 منهای  عدد 33 می شود بعد نتیجه آن با عدد 45 جمع بسته می شود و حاصل عدد 68 می گردد  و نمایش داده می شود .

اگر تعداد خانه هایی که می خواهیم برای آنها فرمولی را بنویسیم زیاد باشد  ممکن است مشکلاتی را ایجاد کند .برای مثال اعداد زیادی(1000 خانه یا بیشتر) داریم که می خواهیم مجموع یا حاصل ضرب آنها و… را محاسبه کنیم.

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

1. وقت گیر
2. خسته کننده 
3. احتمال اشتباه در وارد کردن آدرس خانه ها بالا می رود زیرا ممکن است که آدرس خانه ای را اشتباها اصلا انتخاب نکنیم و بجای آن روی آدرس یک خانه دوبار کلیک کنیم.

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

توابع در اکسل

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

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

1. توابع ریاضیات ( Math & trig  )
2. توابع جستجو ( Lookup and reference)
3. توابع متنی ( Text)
4. توابع منطقی ( Logical)
5. توابع مالی (Financial)
6. توابع آماری (Statistical)
7.  توابع پایگاه داده ( Database)
8. توابع مهندسی( Engineering)
9.  توابع Information

توابع ریاضیات ( Math & trig  )

این مجموعه از توابع شامل توابع مربوط به ریاضیات می باشد مانند توابع

• Mod: با قی مانده تقسیم را بر می گرداند.
• Sin : سینوس زاویه ای را محاسبه می کند .
  
•  Tan : این تابع تانژانت مربوط به ورودی ها را محاسبه می کند.
  
• Sum : مجموع اعداد را محاسبه می کند .
  
• Sumif: مجموع اعدادی که دارای یک شرط خاصی هستند را بدست می آورد .
  
• Sumifs: مجموع اعداد که دارای شرایط مختلفی هستند را محاسبه می کند .

توابع جستجو ( Lookup and reference)

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

• Hlookup: امکان جستجوی افقی سلول ها در اکسل را فراهم می آورد.
• Vlookup: امکان جستجوی عمودی سلول ها را فراهم می کند .
• Index: مقدار موجود در شماره سطر و ستون مشخص را بر می گرداند .
• Match:مقدار مورد جستجو در اکسل را می یابد سپس شماره سطر و ستون آن را بر می گرداند .
• Offset:
• Row:شماره ردیف سلول یی که تعیین کرده اید باز می گرداند.
  
• column:شماره ستون خانه ای که از اکسل انتخاب کرده ایم باز می گرداند.
  

توابع متنی ( Text)

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

بعضی از توابع مهم مربوط به این مجموعه:

• Right: تعدادی از کاراکتر های متن را از سمت راست جدا و بر می گرداند .
• left: تعدادی از کاراکتر های متن را از قسمت سمت چپ جدا می کند و نمایش می دهد .
• mid: از مکان تعیین شده ، بطول مشخص کاراکترهای مربوط به متن را جدا می کند.
• Upper:تمام حروف متون انگلیسی را با حروف بزرگ نشان می دهد.
• Lower: تمام حروف متون انگلیسی را با حروف کوچک نمایش می دهد.
• len: تعداد کاراکتر های متون را می شمارد و بر می گرداند .

توابع منطقی ( Logical)

در این گروه توابعی منطقی هستند که بسیار پرکاربرد می باشندمانند

• تابع If: اگر در محاسبات به شرط نیاز داشتیم از این تابع استفاده می کنیم .
• تابع And: تابعی که اگر ورودی های آن درست باشد جواب True و در غیر این صورت نتیجه False برمی گرداند.
• تابع Or:اگر یکی از ورودی ها درست باشد جواب true بر می گرداند .
• تابع IFError: هر خطایی را در اکسل شناسایی می کند و پیام خاص مه ما تعین می کنیم را نمایش می دهد.

توابع مالی (Financial)

این مجموعه شامل توابع مالی و بانکی است برای مثال میزان سود اقساط بانکی و…

توابع آماری (Statistical)

این توابع برای کارهای آماری کاربرد دارد.

• تابع  Average
• تابع AverageA
• تابع Averageif
• تابع  Averageifs
• تابع Count
• تابعCountA, Countblank
• و…..

 توابع پایگاه داده ( Database)

توابع مربوط به پایگاه داده و دیتابیس است.

توابع مهندسی( Engineering)

این توابع مربوط به مهندسی و Engineering است.

 توابع Information