1- ارجاع به همان کاربرگ فعال
برای این منظور علامت مساوی را به عنوان شروع فرمول تایپ می کنیم و سپس به روی سلول مورد نظر کلیک می کنیم و کلید اینتر را فشار می دهیم.
مثال: می خواهیم سلول B1 را به سلول A1 در همان کاربرگ ارجاع بدیم با این کار ما اگر هر چی در سلول A1 بنویسیم به طور اتوماتیک در سلول B1 هم نوشته خواهد شد. برای این کار در سلول B1 کلیک می کنیم و کلید مساوی را فشار می دهیم و بعد به سلول A1 می رویم و در آنجا کلیکمی کنیم و اینتر را می زنیم.
B1=A1
2- ارجاع به دیگر کاربرگهای یک کاربرگ دان
برای این منظور علامت مساوی را به عنوان شروع فرمول تایپ می کنیم و سپس کاربرگ دیگری را انتخاب و بوسیله موس سلول مورد نظر را انتخاب و کلید اینتر را می زنیم.
مثال: فرض کنید شما در sheet1 مانده حسابهای دفتر کل را نوشته اید و در sheet2 می خواهید ترازنامه را طراحی کنید برای این منظور مثلاً در sheet2 روبروی سلول صندوق کلیک می کنیم و کلید مساوی را فشار می دهیم و به sheet1 می رویم و در مانده حسابها در سلول مانده صندوق کلیک می کنیم و اینتر می زنیم با این کار هر رقمی در سلول مانده صندوق باشد در ترازنامه منعکس خواهد شد.
A1=Aheet2!A1
تمرین: یک ترازنامه در sheet1 و یک ترازنامه در sheet2 با ارقام فرضی ایجاد کنید و در sheet3 این دو تا ترازنامه را با هم با استفاده از ارجاعات لینک کرده و جمع بزنید.
تابع ارزش آتی (FV)
غالباً تابع FV دارای 5 آرگومان بصورت زیر می باشد:
(FV(Rate,Nper,Pmt,Pv,Type
Rate: نرخ بهره هر دوره می باشد.
Nper: تعداد کل دوره های پرداخت سالیانه است.
توجه شود چنانچه پرداخت ها ماهیانه باشند می بایست نرخ بهره سالیانه نیز ماهیانه در نظر گرفته شود.
Pmt: مبلغی ثابت است که هر دوره سالیانه یا ماهیانه جهت پس انداز توسط فرد علاوه بر مبلغ اولیه به حساب واریز می شود چنانچه رقمی وجود نداشته باشد این آرگومان صفر در نظر گرفته می شود قابل ذکر است آرگومان مذکور بصورت منفی می باشد.
Pv: مبلغ جاری یا ارزش حال پرداخت های آینده است اگر مقدار آن مشخص نگردد صفر در نظر گرفته می شود این آرگومان نیز همانند آرگومان های قبلی می بایست منفی وارد شود.
Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.
مثال1: نرخ بهره سالیانه 6% چنانچه یک میلیون سپرده گذاری نماییم بعد از یکسال چقدر پس انداز نمودیم؟
1060000=(0;1000000-;0;1;6%)=FV
مثال2: یک حساب پس انداز با مقدار 200000 تومان باز می کنیم هر ماه نیز مبلغ 50000 تومان به این حساب اضافه می کنیم نرخ بهره سالیانه 12% می باشد پس از 10 ماه چقدر پس انداز شده است؟ (پرداخت ها ابتدای هر دوره می باشند.)
749266=(1;200000-;50000-;10;1%)=Fv
· در مثال فوق نرخ بهره سالیانه می بایست به نرخ بهره مامانه تبدیل شود.
تابع ارزش فعلی (Pv)
ارزش فعلی یعنی ارزش وجوهی است که قرار است در آینده دریافت نمایید.
قالب تابع بصورت زیر است:
(PV(Rate,Nper,Pmt,Fv,Type
Rate: نرخ بهره هر دوره می باشد.
Nper: تعداد کل اقساط هر دوره.
توجه شود چنانچه پرداخت ها ماهیانه باشند می بایست نرخ بهره سالیانه نیز ماهیانه در نظر گرفته شود.
Pmt: اقساط هر دوره که در طول دوره ثابت است و تغییر نمی کند.
Fv: ارزش آتی یا میزان سرمایه بعد از آخرین پرداخت.
Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.
تذکر1: واحد Nper و Rate باید یکسان باشد هر دو ماهانه یا هر دوسالیانه.
تذکر2: مقدارهایی که پرداخت می شوند با اعداد منفی نمایش داده می شود.
مثال1: قرار است 20000000 ریال 2 سال دیگر دریافت گردد با نرخ 10% ارزش فعلی مبلغ مذکور چقدر است؟
62/16528925= (0;20000000-;0;2;10%)=Pv
بالعکس عبارت مذکور را می توان چنین بیان کرد مبلغ 62/16528925 را با نرخ 10% سپرده گذاری گردد 2 سال بعد چقدر پس انداز نمودیم؟
2000000= (0; 62/16528925 -;0;2;10%)=Fv
تابع محاسبه اقساط وام (PMT)
این تابع اقساط متناوب سالانه را براساس پرداخت های ثابت و نرخ بهره ثابت محاسبه می نماید دقت کنید که واحد نرخ بهره و دوره بازپرداخت با هم یکسان باشد.
قالب تابع بصورت زیر است:
(PMT(Rate,Nper,Pv,Fv,Type
Rate: نرخ بهره هر دوره می باشد.
Nper: تعداد کل اقساط در یک دوره یکساله.
Pv: مقدار ارزش فعلی (جاری) کل پرداخت های آتی
Fv: مقدار ارزش آتی از پرداخت آخرین قسط در صورتی که مقدار آن مشخص نباشد از مقدار پیش فرض صفر استفاده می شود.
Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.
مثال: قسط ماهانه یک وام 100000 ریالی با نرخ بهره سالیانه 8% برای یک دوره 10 ماهه چقدر است؟
10370= (0;.;100000-;0;10; )=Pmt
انجام محاسبات با ایجاد ارجاعات در آرگومان ها
برای مدلسازی و حل مسائل در اکسل راه های گوناگون وجود دارد در مثال های قبل مقادیر نرخ بهره، تعداد پرداخت ها، مقدار واریز ماهیانه، مقدار سپرده اولیه و نحوه موعد پرداخت، تعداد اقساط وام بطور مستقیم در آرگومان های توابع بکار گرفته شده است می توان مقادیر مذکور را در سلول های مختلف وارد نمود و سپس آرگومان های توابع را به آدرس هر سلول ارجاع نمود مزیت عمل مذکور این است که محاسبات با وارد نمودن مقادیر متغیر بدون نیاز به دست زدن به فرمول بطور خودکار انجام می پذیرد و در مدلسازیهای پیچیده می توان با قفل گذری (Lock) بر روی سلولهای فرمول از تغییر در فرمولهای داده شده جلوگیری نمود.
مثال: اطلاعات زبر در یک کاربرگ وارد گردد:
A
9%
1
60
2
0
3
300000
4
0
5
=PMT(A1/12;A2;A3;A4;A5)
6
با استفاده از ارجاعات می توانیم از دوباره نویسی فرمول ها جلوگیری کنیم در مثال بالا می توانیم ستون A را مثلاً نرخ بهره یا مبلغ وام را عوض کنیم و با فشار دادن یک اینتر بطور اتوماتیک در ستون A6 مبلغ قسط وام مشخص می شود.
تمرین:
1- سهامی به ارزش 7000000 ریال خریداری کرده ایم چنانچه به مدت 20 سال در انتهای هر ماه 600000 ریال پرداخت نماییم با نرخ 12% آیا این یک سرمایه گذاری مناسبی بوده است یا خیر؟
2- جدولی در اکسل طراحی کنید و با استفاده از ارجاعات انواع وام با نرخ بهره متفاوت را در آن بطور اتوماتیک محاسبه نمائید؟
دوستان در صورت وجود مشکل یا سوال می توانید در قسمت پایین این کامنت سؤالات خود را بپرسید تا توضیحات بیشتری برایتان ارئه کنم.
در اکسل می توان با استفاده از کلید F4 فرمول را ثابت نگه داشت
به عنوان مثال فرض کنید می خواهیم مالیات حقوق پرسنل یک شرکت را محاسبه کنیم برای این منظور ما به چند پارامتر نیاز داریم 1
- حقوق پایه کارمند
2- میزان معافیت مالیاتی هر ماه و
3- نرخ مالیات ،
برای حل این مسئله ساده ما می توانیم میزان معافیت مالیاتی را که ماهانه 227000 تومان می باشد در خانه A1 تایپ کنیم و جدول حقوق را نیز به این صورت تنظیم نماییم:
در (ستون A به جز خانه (A1 خانه A2 میزان حقوق پایه ماهانه
در خانهB2 فرمول را به این صورت وارد کنید:
(A2-$A$1)*0.1 =
همان طور که می بینید A2 را می نوسیسم اما چون خانه A1 باید در همه خانه ثابت باشد در دوطرف A علامت دلار گذاشته شده است تا خانه A1 در همه فرمول ها یکسان باشد بعد شما می توانید عمل درگ کردن و رها کردن را برای خانه هایی که میخواهید مالیات را حساب کنید انجام بدهید.
نحوه گزارش گیری از نرم افزار اکسل
فرض کنید لیست پرسنل را در یک جدول طراحی نموده اید و تعداد پرسنل شما 300 نفر می باشد برای اینکه بتوانید از این افراد یک لیست بیمه یا لیست حقوق بدست آورید باید به چند نکته دقت کنید:
1- اکسل صفحه گسترده است و شما باید محدوده ای را که میخواهید از آن گزارش بگیرید باید تعیین کنید. برای این منظور شما از نوار ابزار ابزار Print Preview را بزنید و یا در منوی فایل گزینه Print Preview را انتخاب نمایید. صفحه ای باز خواهد شد شما می توانید با استفاده از دکمه Break Preview Page را بزنید شما دوباره به صفحه اکسل باز خواهید گشت اما تغییراتی را درصفحه خواهید دید که قسمتی از صفحه که چیزی در آن ننوشته اید های لایت شده است و قسمتی که در آن نوشته وجود دارد داخل کادری که دور آن با خط آبی پوشیده شده است و اگر شما در داخل این خطوط آبی ممتد خط آبی نقطه چین می بینید باید آن خط ها را از بغلها تنظیم کنید تا از بین بروند و کل صفحه در کاغذ بیفتد.
2- گاهی اوقات لیست شما در یک صفحه جا نمی شود و می خواهید درهر صفحه مثلاً یک سطر (سطر اول) صفحه عنوان یا تیتر بیاید مانند نام و نام خانوادگی پرسنل که در هر صفحه در سطر اول ثابت بیاید برای این منظور شما می توانید فقط یکبار در سطر اول جدولتان که عناوین را نوشته اید با استفاده از منوی فایل گزینPage Setup را انتخاب نمایید در صفحه ای که ظاهر خواهد شد در بالای صفحه دکمه Setup را بزنید در اینحالت پنجره کوچکی باز خواهد شد در این صفحه شما از گزینه هایی که در بالای پنجره قرار دارد گزینه آخری Sheet را انتخاب نمایید در این پنجره در محل Print Titles سط اول را Row to Repeat at up: در این گزینه سطری را که می خواهید در تمام صفحه ها بیفتد را انتخاب کنید و دکمه Ok را بزنید اگر از جدولتان پرینت بگیرید خواهید دید که در تمام صفحات سطر اول یکسان است.
