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

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

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

آموزش توابع اکسل و فرمول نویسی در محیط اکسل برای مبتدی تا پیشرفته نهیه شده است. فرض بر این است پژوهشگر درس اول آشنایی با محیط اکسل را مطالعه کرده است. برای مطالعه بیشتر می‌توانید به آموزش افزونه Solver و افزونه Analysis ToolPak رجوع کنید.

چهار عمل اصلی در اکسل

عملگرهای مربوط به چهار عمل اصلی عبارتند از:

  • عملگر جمع +
  • عملگر منها –
  • عملگر ضرب .
  • عملگرتقسیم /

فرض کنید می خواهید جمع سلول A1 و B1 را در سلول C1 نشان دهید. در سلول C1 کلیک کنید سپس در کادر FX عبارت A1+ B1 = را وارد کنید ( ابتدا علامت مساوی را وارد کنید ) سپس Enter کنید تا نتیجه جمع را ببینید.

برای تفریق در اکسل نیز بعد از اجرای برنامه مطابق روش جمع دو عدد پیش می رویم. ولی  به جای علامت جمع بین A1 و B1 علامت تفریق می گذاریم و Enter می کنیم. فرمول تفریق در اکسل به شکل A1-B1= است.

اولویت عملگرهای ریاضی در فرمول‌  Operator Precedence

اکسل به طور پیش فرض محاسبات را با اولویت‌هایی به مانند اولویت‌های ریاضی انجام می‌دهد:

  • پرانتز
  • ضرب و تقسیم
  • جمع و تفریق

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

عملگرهای منطقی در اکسل

از یک عملگر منطقی در اکسل برای مقایسه دو مقدار استفاده میشود. عملگرهای مقایسه ای در بعضی مواقع عملگرهای BOOLEAN گفته میشوند زیرا نتیجه یک مقایسه در هر شرایطی مقادیر TRUE/FALSE میشود. در اکسل شش عملگر مقایسه ای وجود دارد. در جدول زیر کاری که هر کدام از این عملگرها انجام میدهند و فرمول هایی از هرکدام از آنها نشان داده است.

  • عملگر مساوی =
  • عملگر نامساوی <>
  • بزرگتر < کوچکتر > بزرگتر مساوی =< کوچکتر مساوی =>
عملگرهای منطقی اکسل

عملگرهای منطقی اکسل

تابع SUM و Product

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

برای جمع چند سلول متوالی ( دنبال هم ) برای فرمول نویسی از علامت ( : ) استفاده می شود. مثلا اگر از سلول B1 تا B4 را انتخاب کنیم بصورت (B1:B4)Sum= نوشته میشود و نشان دهنده انتخاب سلول های پشت سر هم برای فرمول می باشد.

اگر هدف انتخاب چند سلول تامتوالی باشد از علامت ( ; ) در بین نام سلول های انتخابی استفاده می شود. مثلا اگر فقط سلول B1 و D4 را برای فرمول نویسی انتخاب کنیم ، فرمول به شکل (B1;D4)= نوشته میشود.

برای ضرب از عملگر * و برای تقسیم از عملکرد / استفاده می شود.

فرض کنید می خواهید ضرب سلول A1 و B1 را در سلول C1 نشان دهید. در سلول C1 کلیک کنید سپس در کادر FX عبارت A1* B1 = را وارد کنید سپس Enter کنید تا نتیجه جمع را ببینید.

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

برای محاسبه میانگین موزون (معدل) از تابع SumProduct استفاده می شود. این تابع چند آرایه می پذیرد که ساده ترین و پرکاربردترین مورد استفاده آن به صورت زیر است.

تابع sumproduct در اکسل

تابع sumproduct در اکسل

توابع اکسل AND و OR

ابتدا باید بدانید عملگر اَمپِرسَند (Ampersand) یا & برای نمایش همزمان مقادیر چند سلول استفاده می شود و کاربرد آن با تابع AND تفاوت دارد. تابع CONCATENATE نیز نتایجی مشابه عملگر & دارد و برای ترکیب محتویات چندین سلول استفاده می شود.

تابع منطقی AND دو یا چند شرط را به صورت همزمان بررسی می کند و اگر همه برقرار باشند مقدار TRUE و اگر تنها یکی برقرار نباشد مقدار FALSE را بر می گرداند.

=AND ( شرط دوم , شرط اول, … )

= AND(A1>10,B1>10)

تابع منطقی ORدو یا چند شرط را به صورت همزمان بررسی می کند و اگر تنها یکی برقرار باشد مقدار TRUE و اگر هیچکدام برقرار نباشد مقدار FALSE را بر می گرداند.

تابع IF در اکسل

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

ساختار تابع if در اکسل به صورت زیر است:

=IF(logical_test, [value_if_true], [value_if_false])

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

فرض کنید میخواهید نشان دهید اگر عدد از ۷ بزرگتر است مطلوب است و اگر ۷ یا کوچکتر باشد نامطلوب است:

=IF (B1>7,”رد”,”پذیرش”)

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

=IF(AND(A1>0,A1<5), “Approved”, “Denied”)

تابع TRUNC

از تابع TRUNC برای انتخاب بخش صحیح یا Integer یک عدد استفاده می‌شود:

= TRUNC (number)

اگر بخواهید فقط بخش اعشاری یک عدد را انخاب کنید از تابع TRUNC استفاده کنید:

= number – TRUNC (number)

= B6 – TRUNC (B6)

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

تابع TRIM

تابع TRIM جزء توابع متنی بوده به منظور حذف فاصله زائد مورد استفاده قرار میگیرد. این تابع تنها یک ورودی دریافت میکند و تمامی فاصله های زائد در ورودی را حذف کرده و سایر کاراکتر ها را به همان ترتیب به عنوان خروجی ارائه میدهد.

=TRIM(B3)

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

تابع SUBSTITUTE

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

=SUBSTITUTE (text, old text, new text)

در این تابع به جای آزگومان text به خانه موردنظر رفرنس دهید. به جای old text آن بخش از متن که مایل هستید حذف شود را بنویسید و برای آنکه با رشته جدیدی جایگزین شود از آرگومان new text استفاده کنید. اگر هم نمی‌خواهید چیزی جایگزین شود در قسمت new text از “” استفاده کنید.

تابع SUBSTITUTE

تابع SUBSTITUTE در اکسل

این تابع دارای یک آرگومان دیگر به نام [Instance_Num] است. این آرگومان اختیاری و از جنس عدد هست. در صورتی که خالی بگذاریم، همه عبارت های معادل موردی که جستجو میکنید را با عبارت جدید جایگزین میکند. اما اگر عدد بذاریم، مثلا ۳، فقط سومین عبارت مورد نظر رو پیدا میکند و جایگزین میکند.

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

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

ابتدا روی اعداد مورد نظرتان (محورها یا سری‌ها) راست کلیک کرده و سپس Format Axis را انتخاب ‌کنید . سپس در پنجره باز شده منوی Number را انتخاب کرده و در قسمت Category روی گزینه Custom کلیک ‌کنید. بعد در کادر خالی، کد فرمت زیر را وارد کرده و Add’ گزینه Add را انتخاب کنید .

کد فرمت :

۰[$-۳۰۱۰۰۰۰]

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

۰.۰۰[$-۳۰۱۰۰۰۰]

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