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

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

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

  • پرکردن خودکار(بطور کامل)
  • فرمول نویسی بین کاربرگ ها

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

پرکردن خودکار

در ادامه آموزش فرمول نویسی در اکسل همانند تطبیق و کپی فرمولها در اکسل با استفاده از مربع کوچک گوشه سلولها میتوان متن و اعداد را نیز تطبیق یا ادامه داد. فرض کنید میخواهیم در سلول A1 تا A10 اعداد ۱ تا ۱۰ را بنویسیم. برای این کار میتوانیم تک تک در سلولها این اعداد را نوشت که کاری وقتگیر است. اما راه سریعتری برای این کار وجود دارد. برای این کار میتوان در سلول A1 عدد ۱ و در سلول A2 عدد ۲ را نوشت، سپس این دو سلول را با هم انتخاب کرد ،بصورت زیر.

حال اگر ماوس را روی مربع کوچک گوشه سلول A2 ببریم خواهیم دید شکل ماوس به صورت + تغییر مییابد. در این زمان باید کلیک کنیم و کلیک را نگه داریم و ماوس را تا سلول A10 بکشیم و رها کنیم.

 

مثال

جدول ارزش مرکب یک ریال را برای یک تا ده سال و با نرخ بهره سالانه ۱ تا ۵ درصد در سال را تشکیل دهید. نتیجه حاصل به شکل زیر خواهد بود.

در ابتدا شماره دورهها را مینویسیم. برای این کار در سلول A1 عبارت “دوره”، در سلول A2 عدد ۱ و در سلول A3 عدد ۲ را مینویسیم. حالا دو سلول A2 و A3 را با هم انتخاب میکنیم و سپس روی مربع کوچک سلول A3 کلیک کرده و تا سلول A11 میکشیم و رها میکنیم.

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

 

حالا برای نوشتن درصد ها در سطر ۱ ابتدا در سلول B1 عدد ۰٫۰۱ و در سلول C1 عدد ۰٫۰۱ را مینویسیم و سپس دو سلول B1 و C1 را با هم انتخاب کرده و سپس روی مربع کوچک گوشه سلول C1 کلیک کرده و نگه داشته و تا سلول F1 میکشیم و رها میکنیم.

 

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

 

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

حالا نوبت نوشتن فرمول ارزش مرکب است. فرمول ارزش مرکب بصورت زیر است:

(۱+i)ˆn

در سلول B2 پس از علامت “=” پرانتز باز کرده و عبارت “(۱+B1)” را مینویسیم و روی نام سلول B1 یک بار کلید F4 را فشار میدهیم تا بصورت =(۱+B$1) تغییر یابد و در بیرون پرانتز بعد از علامت توان “^” روی سلول A2 کلیک کرده و سه بار کلید F4 را بفشارید تا فرمول بصورت

=(۱+B$1)^$A2

تغییر یابد و در پایان کلید ENTER را بفشارید. تا اینجای کار شکل زیر حاصل شده است.

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

 

حالا یکبار روی مربع کوچک سلول B2 کلیک کرده و نگه داشته و تا سلول F2 میکشیم و کلیک را رها میکنیم.

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

و حالا در حالی که ۵ سلول B2 تا F2 با هم انتخاب شدهاند، روی مربع سلول F2 کلیک کرده و نگه میداریم و تا سطر ۱۱ میکشیم و رها میکنیم. در پایان شکل حاصل بصورت زیر است.

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

 

مثال
فروش فصلی یک شرکت برای سه سال متوالی به شرح زیر است.

 

در ادامه آموزش فرمول نویسی در اکسل میخواهیم جمع فروش سالانه را در سطر ۶ و میانگین فروش هر فصل را در ستون E محاسبه کنیم. برای جمع فروش هر سال ابتدا برای سال ۱۳۸۹ روی سلول B6 کلیک میکنیم و فرمول B2+B3+B4+B5= را مینویسیم و کلید ENTER را میفشاریم.

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

 

حالا برای محاسبه جمع فروش دو سال دیگر کافیست روی مربع کوچک گوشه سلول B6 کلیک کنیم و نگهداریم و تا سلول D6 بکشیم و کلیک را رها کنیم. اعداد حاصل بصورت زیر نمایش داده میشوند.

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

 

برای محاسبه میانگین فروش فصل بهار در سه سال گذشته در سلول E2 فرمول (B1+C1+D1)/3)= را مینویسیم و کلید ENTER را میفشاریم.

حالا با کلیک بر مربع کوچک گوشه سلول E2 و نگهداشتن آن و کشیدن تا سلول E5 میانگین فروش سایر فصول نیز محاسبه خواهد شد.

 

فرمول موجود در این سلولها بصورت زیر است:

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

مثال

جدول استهلاک یک دارایی به روش خط مستقیم با بهای تمام شده ۵۰۰۰۰۰۰ ریال و عمر مفید ۴ سال با ارزش اسقاط ۲۰۰۰۰۰۰ ریال را تشکیل دهید. دقت کنید جدول حاصل بصورت زیر خواهد بود.

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

 

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

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

 

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

 

در ادامه آموزش فرمول نویسی در اکسل بنابراین فرمول محاسبه استهلاک سال اول بصورت  ۴/(۲۰۰۰۰۰۰۰-۵۰۰۰۰۰۰)= که اگر به جای عددها آدرس سلول آنها را قرار دهیم (فرمول را بصورت نسبی بنویسیم)، فرمول این سلول بصورت B1-B3)/B2)= خواهد بود. چون فرمول محاسبه استهلاک برای همه سالها یکسان است، پس میتوان این فرمول را با ماوس به پایین کشید تا اکسل بطور خودکار این فرمول را برای سایر سالها نیز تطبیق دهد. اما قبل از این کار باید عناصر این فرمول را ثابت کرد تا با کپی فرمول به سلولهای پایینی، فرمول تغییر نکند. برای این کار به این صورت فرمول سلول B6 را مینویسیم.

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

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

حالا علامت “-” را قرار داده و روی سلول B3 کلیک کرده و سپس کلید F4 را برای ثابت شدن آن میفشاریم و پرانتز را میبندیم.

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

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

تا اینجا تنها استهلاک سال اول را محاسبه کردهایم. برای کپی این فرمول به سالها بعد کافیست روی مربع کوچک کادر سلول B6 دبل کلیک کنیم.

برای محاسبه استهلاک انباشته دارایی در سلول C6 چون استهلاک انباشته سال اول برابر با هزینه استهلاک همین سال است فرمول B6= را قرار داده و کلید ENTER را میفشاریم.

برای استهلاک انباشته سالهای بعد در سلول C7 فرمول C6+B7= را نوشته و کلید ENTER را میفشاریم .فرمول محاسبه استهلاک انباشته هر سال برابر است با هزینه استهلاک همان سال بعلاوه استهلاک انباشته سالهای قبل. بنابراین در سلول C7 هزینه استهلاک سال دوم (B7) و استهلاک انباشته سالهای قبل (C6) را با هم جمع کرده ایم.

حال اگر روی مربع کوچک کادر سلول C7 دبل کلیک کنیم خواهیم دید بصورت خودکار استهلاک انباشته سالهای بعد نیز محاسبه شده است.

برای نمونه فرمول موجود در سلول C9 بصورت C8+B9= است که به معنای جمع هزینه استهلاک سال چهارم (B9) و استهلاک انباشته سه سال گذشته(C8)میباشد.

حال برای محاسبه ارزش دفتری هر سال در سول D6 فرمول

=$B$1-C6

را مینویسیم و کلید ENTER را میفشاریم. ارزش دفتری هر سال برابر است با بهای تمام شده دارایی(B1، چون در همه سالها باید ثابت باشد روی آن کلید F4 را فشردهایم و بصورت $B$1 تغییر پیدا کرده است) منهای استهلاک انباشته همان سال (برای سال اول سلول C6 و چون هر سال باید تغییر کند آن را ثابت نمیکنیم.)

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

حالا با یک دبل کلیک روی مربع کوچک سلول C6 جدول ما کامل میشود.

مثال

میخواهیم جدول ارزش مرکب برای یک ریال با نرخهای بهره ۱ تا ۱۰ درصد و در دورهای ۱ تا ۱۵ سال را تشکیل دهیم. برای این کار ابتدا عناوین سطر و ستون جدول را بصورت زیر در اکسل تشکیل میدهیم.

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

حالا در سلول B2 برای محاسبه ارزش مرکب سال اول و با نرخ بهره ۱ درصد در سال، باید فرمول

=(۱+B1)^A2

را بنویسیم. اما میخواهیم این فرمول را برای همه سلولهای این جدول کپی کنیم. برای اینکه در هنگام جابجایی فرمول آدرس سلولها بهم نریزد باید دو سلول A2 و B1 را ثابت کرد. اما این بار بجای اینکه یک بار کلید F4 را روی هر کدام از آنها فشار دهیم، برای سلول B1 دوبار کلید F4 را فشار دهید تا آدرس این سلول بصورت B$1 تغییر یابد. این کار باعث میشود در هنگام کپی فرمول به پایین برای دورههای دیگر، همواره این قسمت سطر یک را نشان دهد و همچنین در هنگام کپی فرمول به سمت راست برای نرخهای بهره دیگر، ستونها تغییر کند.

برای سلول A2 سه بار کلید F4 را بفشارید تا به شکل A2$ تغییر یابد. این کار باعث میشود تا در هنگام کپی فرمول به سمت راست، همواره فرمول در قسمت توان در ستون A ثابت باشد و در هنگام کپی به پایین، بدلیل ثابت نبودن عدد این سلول، اعداد توان تغییر کند.

در پایان کلید ENTER را بفشارید. حالا در سلول B2 ارزش مرکب یک ریال در یک سال بعد با نرخ بهره ۱ درصد در سال نمایش داده شده است.

حالا برای محاسبه سایر سلولها، روی مربع سلول B2 کلیک کرده و نگه میداریم و تا سلول K2 میکشیم و رها میکنیم.

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

 

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

مثال

در یک شرکت تولیدی، در دایره مشترک آن که دایره اول تولید است، سه محصول اصلی با نامهای الف، ب و ج تولید میشود. تعداد تولید هر محصول بترتیب ۲۵۰۰، ۳۵۰۰ و ۴۵۰۰ واحد و هزینه این دایره مشترک ۱۲۰۰۰۰۰۰۰ ریال است. سایر اطلاعات بشرح زیر در اکسل موجود است.

 

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

=B2/($B$2+$B$3+$B$4)

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

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

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

حالا در ستون “سهم از هزینه مشترک” باید برای هر محصول عدد ۱۲۰۰۰۰۰۰ را در نسبت تولید هر محصول ضرب کنیم. برای این کار در سلول E2 فرمول =۱۲۰۰۰۰۰۰*D2 را نوشته و کلید ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر گوشه سلول E2 سهم هر محصول از هزینه مشترک محاسبه میشود.

برای محاسبه بهای تمام شده هر محصول باید “مبلغ پرداخت اضافی” و “سهم از هزینه مشترک” هر محصول را جمع کرده و در ستون F وارد کنیم.

برای محاسبه بهای تمام شده هر واحد کافیست بهای تمام شده کل محصول را بر تعداد تولید آن تقسیم کرد. برای این کار در سلول G2 فرمول F2/B2= را نوشته و ENTER را میفشاریم. سپس با دبل کلیک بر مربع توپر سلول G2 بهای تمام شده همه محصولات محاسبه میشود.

فرمول موجود در این سلولها بصورت زیر است.

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

فرمول نویسی بین کاربرگ ها

در ادامه آموزش فرمول نویسی در اکسل گاهی اوقات میخواهیم اطلاعات و دادهها در اکسل در یک کاربرگ و تجزیه تحلیلها، محاسبات و فرمولها در کاربرگی دیگر وارد شوند. فرض کنید مبلغ ۵۰۰۰۰۰۰ ریال را در حسابی در بانک با نرخ بهره سالانه ۷ درصد سپردهگذاری کردهاید. میخواهید بدانید در پایان دوره ۴ ساله سرمایه گذاری موجودی حساب شما با احتساب بهره متعلقه به آن، چه مقدار خواهد بود. اطلاعات سوال را در کاربرگ اول (SHEET1) بصورت زیر وارد کرده ایم.

حالا میخواهیم فرمول محاسبه ارزش آتی این سرمایه گذاری در سلول A1 در SHEET2 وارد شود و نتیجه در کاربرگ دوم نمایش داده شود. برای این کار ابتدا به کاربرگ دوم میرویم و روی سلول A1 کلیک میکنیم تا فعال شود. سپس علامت “=” را قرار میدهیم. حالا برای کلیک بر سلول حاوی مبلغ وام (سلول B1 در کاربرگ اول) ابتدا روی نام کاربرگ اول در پایین صفحه کلیک میکنیم تا به کاربرگ اول منتقل شویم، سپس روی سلول B1 کلیک میکنیم.

مشاهده میکنید نام سلول B1 در کاربرگ اول بصورت SHEET1!B1 نمایش داده شده است. حالا ادامه فرمول را در نوار فرمول مینویسیم. فرمول نهایی بصورت زیر است.

=Sheet1!B1*(1+Sheet1!B2)^Sheet1!B3

اگر این فرمول را در کاربرگ اول مینوشتیم بصورت B1*(1+B2)^B3= نمایش داده میشد.

 

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

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

 

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

احمد جعفری

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

0 پاسخ

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

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

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