بایگانی برچسب برای: 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 را به موارد مذکور تغییر دهیم.

فرمول نویسی ترکیبی در اکسل (Excel)

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

 

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

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

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

میخواهیم مبلغ بهره قابل دریافت از بابت هر کدام از اوراق را در ستون 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 تغییر مییابد. در واقع علامت $ قبل از نام ستون و یا شماره سطر آن باعث ثابت شدن آن در فرمول میشود.

 

پر کردن خودکار در نرم افزار اکسل

در ادامه آموزش فرمول نویسی در اکسل همانند تطبیق و کپی فرمولها در اکسل با استفاده از مربع کوچک گوشه سلولها میتوان متن و اعداد را نیز تطبیق یا ادامه داد. فرض کنید میخواهیم در سلول 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= نمایش داده میشد.

 

 

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

 

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