آموزش فرمول نویسی اکسل (Excel) برای مهندسان صنایع | (قدم به قدم)

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

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

فرمول همانند متن، عدد و سایر دادهها به سلول وارد میشود با این تفاوت که قبل از نوشتن فرمول حتما باید علامت “=” قرار داده شود. در واقع ورود این علامت در ابتدای یک عبارت به اکسل میفهماند که عبارت وارد شده یک دستور یا فرمول است.

علائم ریاضی در اکسل بصورت زیر هستند:

عمل جمع ⇐ +  عمل تفریق ⇐ –  توان ⇐ ^  یا SHIFT+6 عمل تقسیم ⇐  /عمل ضرب ⇐ * یا SHIFT+8

 

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

فهرست این آموزش به صورت زیر است :

  • فرمول نویسی مطلق
  • فرمول نویسی نسبی
  • فرمول نویسی ترکیبی
  • خاصیت تطبیق فرمول ها
  • ثابت کردن بخشی از فرمول

فرمول نویسی مطلق

در ادامه آموزش فرمول نویسی اکسل فرمول نویسی مطلق یعنی فرمول متشکل از عدد و علائم ریاضی باشد. برای مثال فرمول ۲+۵= یک فرمول مطلق است زیرا در آن فقط علامت ریاضی و عدد وجود دارد. فرمول نویسی مطلق در سلولها کاری همانند ماشین حساب ساده انجام میدهد. فرض کنید میخواهیم در سلول A1 حاصل ضرب دو عدد ۵ و ۷ را محاسبه کنیم. برای این کار ابتدا این سلول را فعال میکنیم.

سپس برای نوشتن فرمول ابتدا علامت “=” را قرار میدهیم و بلافاصله بعد از آن عبارت ۵*۷ را مینویسیم و در پایان کلید ENTER را میفشاریم.

پس از فشردن کلید ENTER حاصل فرمول در سلول A1 نمایش داده میشود.

همچنین زمانی که روی سلول A1 کلیک کنیم، در نوار فرمول، فرمول موجود در این سلول نمایش داده میشود.

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

 

مثال

بهای تمام شده یک دارایی ۱۰۰۰۰۰۰ ریال و عمر مفید آن ۵ سال میباشد. این دارایی در پایان عمر مفید خود بیارزش تلقی میشود. استهلاک این دارایی را به روش خط مستقیم در سلول A1 محاسبه کنید؟

برای این کار ابتدا سلول A1 را فعال میکنیم و سپس عبارت ۱۰۰۰۰۰۰/۵= را در آن نوشته و کلید ENTER را میفشاریم.

 

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

مثال

میزان بهره دریافتنی بابت یک ورق قرضه ۵۰۰۰۰۰۰ ریالی با نرخ بهره اسمی ۱۵ درصد در سال را در پایان سال در سلول K3 محاسبه کنید؟

برای این کار ابتدا سلول K3 را فعال میکنیم و سپس عبارت ۰٫۱۵*۵۰۰۰۰۰۰= را در آن نوشته و کلید ENTER را میفشاریم.

 

 

خواهیم دید در سلول K3 مبلغ بهره قابل دریافت در پایان یک سال و در نوار فرمول، فرمول این محاسبه نمایش داده شده است.

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

 

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

فرمول نویسی نسبی

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

میخواهیم مبلغ استهلاک سالانه دارایی را به روش خط مستقیم در سلول B5 محاسبه کنیم. اگر از فرمول نویسی مطلق استفاده کنیم، در سلول B5 باید عبارت ۱۰۰۰۰۰۰/۵= را بنویسیم و کلید ENTER را بفشاریم. اما میخواهیم از فرمول نویسی نسبی برای این مثال استفاده کنیم.

این فرمول بصورت خطی به شکل  ۵/۰-۱۰۰۰۰۰۰ نوشته میشود. اگر همین فرمول را با یک علامت “=” در سلول B5 بصورت بنویسیم، همان نتیجه حاصل میشود. اما این نوع فرمول نویسی مطلق است و مدنظر ما نیست.

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

اگر بجای اعداد، آدرس سلول آنها را در فرمول بنویسیم، در واقع فرمول را بصورت نسبی نوشتهایم. فرمول نسبی این مثال بصورت =B1-B3)/B2) میباشد. حال اگر همین فرمول را در سلول B5 بنویسیم و کلید ENTER را فشار دهیم همان نتیجه حاصل میشود.

نوشتن نام سلولها در فرمول وقتگیر است و احتمال اشتباه را افزایش میدهد. برای سهولت در امر نوشتن فرمول نسبی ،میتوان بجای نوشتن آدرس سلول، با ماوس روی آن کلیک کرد. برای نمونه در همین مثال برای نوشتن فرمول ابتدا علامت “=” را قرار میدهیم و سپس پرانتز باز میکنیم و روی سلول B1 که مبلغ بهای تمام شده دارایی است کلیک میکنیم.

سپس علامت “-” قرار میدهیم و روی سلول B3 که ارزش اسقاط دارایی است کلیک میکنیم و سپس پرانتز را میبندیم.

و پس از آن علامت “/” را قرار داده و سپس روی سلول B2 که معرف عمر مفید دارایی است کلیک میکنیم و در پایان روی کلید ENTER کلیک میکنیم.

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

خواهیم دید استهلاک سالانه این دارایی در سلول B5 نمایش داده شده است.

حال اگر در سلول B1 مبلغ دارایی را تغییر دهیم و بجای ۱۰۰۰۰۰۰ ریال مبلغ ۱۵۰۰۰۰۰ ریال را وارد کنیم ،خواهیم دید که مبلغ استهلاک جدید محاسبه و نمایش داده میشود.

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

 

مثال

با استفاده از فرمول نوشته شده در مثال فوق استهلاک یک دارایی با بهای تمام شده ۶۰۰۰۰۰۰ ریال و عمر مفید ۱۵ سال و ارزش اسقاط ۱۵۰۰۰۰۰ ریال را به روش خط مستقیم محاسبه کنید.

برای این کار تنها کافیست مبالغ و عمر مفید را تغییر دهیم. بهای تمام شده را به ۶۰۰۰۰۰۰ ریال و ارزش اسقاط را به ۱۵۰۰۰۰۰ ریال و عمر مفید را به ۱۵ تغیر میدهیم.

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

مثال

همانند تصویر زیر اطلاعاتی در مورد یک ورقه قرضه به مبلغ اسمی ۲۵۰۰۰۰۰ ریال و نرخ بهره سالانه ۱۲ درصد را در اختیار داریم. بهره این اوراق یک بار در سال و در پایان سال پرداخت میشود. مطلوبست محاسبه بهره قابل دریافت در پایان سال در سلول B3.

برای این کار پس از قراردادن علامت “=” در سلول B3 روی سلول B1 کلیک کرده و علامت “*” را قرار میدهیم.

در ادامه روی سلول B2 کلیک میکنیم و در پایان کلید ENTER را میفشاریم.

در سلول B3 شاهد بهره قابل دریافت در پایان سال خواهیم بود.

حال اگر بخواهیم بهره قابل دریافت ورقه قرضه ۱۰۰۰۰۰۰ ریالی با نرخ بهره اسمی ۱۱ درصد را محاسبه کنیم کافیست سلولهای B1 و B2 را به موارد مذکور تغییر دهیم.

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

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

 

در واقع میتوان فرمول خطی این محاسبه را بصورت (۰٫۱۵/۲)*۴۰۰۰۰۰۰= نوشت. حال اگر بجای عدد ۴۰۰۰۰۰۰ ریال، آدرس سلول B1 و بجای نرخ بهره ۱۵ درصد، آدرس سلول B2 را در فرمول قرار دهیم، فرمولی ترکیبی بصورت =(B1*(B2/2 بدست میآید. اگر این فرمول را در سلول B3 بنویسیم نتیجه زیر را مشاهده خواهیم کرد.

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

خاصیت تطبیق فرمول ها

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

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

میخواهیم مبلغ بهره قابل دریافت از بابت هر کدام از اوراق را در ستون D محاسبه کنیم. برای این کار ابتدا در سلول D2 بهره قابل دریافت از اوراق قرضه شرکت الف را محاسبه میکنیم. برای این کار در این سلول پس از علامت “=” ابتدا روی سلول B2 کلیک کرده و علامت “*” قرار میدهیم.

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

سپس روی سلول C2 کلیک کرده و در پایان کلید ENTER را میفشاریم.

حاصل بصورت زیر است.

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

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

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

با این کار اکسل بصورت خودکار در سلول D3 فرمول =B3*C3، در سلول D4 فرمول =B4*C4 و در سلول D5 فرمول =B5*C5 را نوشته است. با کلیک بر هر کدام از این سلولها، فرمول آن را در نوار فرمول مشاهده میکنید.

حالا میخواهیم در سلول B6 جمع درآمد بهره از اوراق قرضه این شرکت را محاسبه کنیم. برای این کار میتوانیم روی سلول B6 کلیک کرده و فرمول =D2+D3+D4+D5 را نوشته و در پایان کلید ENTER فشرد. همچنین میتوان بجای نوشتن نام سلولها با ماوس روی آنها کلیک کرد.

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

ثابت کردن بخشی از فرمول

در ادامه آموزش فرمول نویسی اکسل در مثال فوق با پایین کشیدن فرمول موجود در سلول D2، اکسل بصورت خودکار به ازای هر سلولی که به پایین کشیدیم، یک شماره به آدرس سلول اضافه کرد و فرمول جدید تولید کرد. فرمول سلول D2 برابر با =B2*C2 بود و زمانیکه این فرمول را با استفاده از خاصیت تطبیق اکسل یک سلول به پایین کشیدیم اکسل فرمول =B3*C3 را در سلول D3 نمایش داد.

گاهی لازم است در صورت انتقال و تطبیق یک فرمول بخشی از آن همیشه ثابت باشد و تغییر نکند .فرض کنید اوراق قرضهای که یک شرکت در اختیار دارد بصورت زیر است و نرخ بهره اسمی همه اوراق یکسان و ۱۵ درصد باشد.

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

میخواهیم مبلغ بهره قابل دریافت از هر اوراق را در ستون C محاسبه کنیم. برای این کار همانند مثال قبل ابتدا در سلول C2 فرمول مربوط به این سلول را مینویسیم. فرمول محاسبه بهره قابل دریافت از شرکت الف بصورت B2*B7= است.

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

خواهیم دید که اعداد بدست آمده در سلولهای C3 تا C5 اشتباه محاسبه شده است. با نگاهی به فرمول موجود در این سلولها مشاهده میکنیم که برای مثال در سلول C3 فرمول =B3*B8 نوشته شده (با انتقال یک سلول به پایین، شماره سطر عناصر فرمول هم یک واحد اضافه شد). اما چون سلول B8 خالی است، صفر در نظر گرفته میشود و حاصل ضرب عدد ۳۰۰۰۰۰۰ ضربدر صفر عدد صفر میباشد. برای دو سلول دیگر نیز همین اتفاق رخ داده است.

برای حل این مشکل دوباره به سلول C2 بازمیگردیم و فرمول آن را با فشردن کلید DELETE پاک میکنیم و فرمول جدید =B2*B7 را مینویسیم، اما قبل از فشردن کلید ENTER یک بار کلید F4 را فشار میدهیم تا شکل آدرس سلول B7 تبدیل به $B$7 شود و سپس کلید ENTER را میفشاریم.

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

 

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

اگر فرمول سلولها را مشاهده کنیم خواهیم دید که قسمت اول فرمولها که مربوط به مبلغ اسمی اوراق است تغییر میکند اما بخش دوم همه فرمولها ثابت و یکسان است. برای مثال فرمول سلول C3 بصورت B3*$B$7= و فرمول سلول C5 بصورت B5*$B$7= میباشد.

حالتهای دیگر ثابت کردن به این صورت است که فرضا اگر روی سلول G12 در یک فرمول دوبار کلید F4 را فشار دهیم آدرس سلول بصورت G$12 و اگر سه بار کلید F4 را فشار دهیم آدرس سلول بصورت $G12 تغییر مییابد. در واقع علامت $ قبل از نام ستون و یا شماره سطر آن باعث ثابت شدن آن در فرمول میشود.

 

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

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

 

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

احمد جعفری

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

 

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

پاسخی بگذارید