ارجاعات در اکسل

 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 را بزنید اگر از جدولتان پرینت بگیرید خواهید دید که در تمام صفحات سطر اول یکسان است.

اطلاعات در Excel اطلاعات در محیط Excel میتوانند اعداد ، حروف ، تاریخ ، زمان و یادداشت باشند که در زیر به شرح تک تک آنها میپردازیم :

( توجه داشته باشید که با فرمت اطلاعات در آینده بیشتر آشنا میشوید و این قسمت فقط برای آشنایی با انواع اطلاعات میباشد. )

1- اطلاعات عددی کار کردن با اعداد : Excel تمام اعداد را یکسان فرض میکند. بنابراین دقت زیادی در نمایش اعداد به صورت مبلغ ، تاریخ و کمیت یا هر شکل دیگری از اعداد ندارد. در Excel اعداد را به دو روش میتوان وارد نمود :

1- با استفاده از کلید های عددی موجود در بالای حروف

 2- با استفاده از کلید های موجود در سمت راست صفحه کلید در مد Num Lock. ( در صورتی که اعداد این قسمت کار میکنند که کلید Num Lock روشن باشد .) علاوه بر ارقام 0 تا 9 میتوان نمادهای خاص + و – و ، و . و $ و % و E و e را وارد نمود. نکته Excel به طور صحیح علائم کاما و دلار را قبول نموده و اعداد را در نماد علمی نیز دریافت کند. نکته هنگامی که از علائم دلار ، درصد یا کاما استفاده میشود، Excel قالب بندی اعداد را تغییر میدهد.

برخوردExcel با اعداد :

 1- اگر طول عدد از سلول کوچکتر باشد تغییری در سلول داده نمیشود.

 2- اگر طول عدد به اندازه چند کاراکتر بزرگتر از سلول باشد اندازه سلول بزرگ شده تا عدد در آن بگنجد.

 3- اگر طول اعداد برای خانه مورد نظر بزرگ باشد یکی از اتفاقات زیر رخ میدهد :

 الف- اعداد در نماد علمی نمایش داده میشوند.

 ب- عدد گرد میشود.

 ج- سلول با یکسری از علائم # پر میشود.

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

 2- اطلاعات متنی : میتوانیم در سلولهای Excel هر متن دلخواهی را چه به فارسی و چه انگلیسی تایپ کنیم.

 توانایی Excel برای در نظر گرفتن اعداد مثل متن : اگر عددی مثل 2/6 را وارد کنیم ، پس از Enter کردن ، Excel، 6 مارس را نمایش میدهد. یعنی این اعداد را به تاریخ در نظر میگیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (") آپستروف قرار میدهیم. یعنی بنویسیم ( "6/2 )

 3- اطلاعات از نوع تاریخ : اگر در سلول Excel یک تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد کنیم به طور اتوماتیک قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به ( / ) شده و سال در چهار رقم نمایش داده می شود.

4- اطلاعات از نوع زمان : میتوانیم در سلول Excel یک زمان را با فرمت H:M:S وارد کنیم که قالب سلول به طور اتوماتیک زمان میشود.

 5- اطلاعات از نوع یادداشت : این نوع اطلاعات توضیحات یا یادداشتهایی هستند که بر روی سلول ظاهر شده و در مورد آن توضیح می دهند. فرمول نویسی در Excel عملگر های Excel •عملگر های محاسباتی : عملگر های محاسباتی به ترتیب تقدم ها عبارتند از : ( ^ ) توان و ( % ) درصد و ( * , /) ضرب و تقسیم و ( + و -) جمع و تفریق •عملگر های رشته ای : تنها عملگر رشته ای ( & ) است. از این عملگر برای ترکیب رشته ها استفاده میشود. • عملگر های آدرس : از این عملگر زمانی استفاده میشود که بخواهیم محدوده ای را مشخص کنیم که ابتدا آدرس ابتدای محدوده را نوشته و سپس علامت ( : ) را میگذاریم و بعد آدرس انتهای محدوده را مینویسیم.

 ( از این عملگر در درسهای آتی استفاده میکنیم ) ایجاد فرمول و مشاهده نتیجه : در Excel به چند روش میتوان فرمولها را نوشت :

1- در خط فرمول ( یا در سلول ) قبل از فرمول علامت = را تایپ کرده سپس فرمول را تایپ میکنیم و بعد Enter میزنیم.

 2- فرمول را در خط فرمول یا و بعد علامت = در خط فرمول سلول نوشته ( بدون علامت = ) سپس بر روی علامت میکنیم.

 پنجره ای باز میشود?، Click که نتیجه را نمایش می دهد. در صورتی که بخواهیم نتیجه نمایش داده شود ، OK را میزنیم.

3- در خانه ای که میخواهیم فرمول نوشته شود، Click میکنیم ، علامت تساوی را تایپ کرده ، سپس با ماوس بر روی خانه ای که میخواهیم عمل بر روی آن انجام شود Click میکنیم ، سپس عملگر را تایپ کرده و سپس بر روی عملوند بعدی Click میکنیم. فرض کنید در سلول A1 عدد 2 و در سلول A2 عدد 5 را نوشتیم و میخواهیم در سلول A3 حاصل جمع این دو را بدست آوریم ، برای این کار ابتدا در سلول A3 علامت = را تایپ کرده سپس بر روی سلول A1 ( اولین عملوند ) کلیک کرده تا آدرس آن در سلول A3 نوشته شود.

 سپس علامت + ( عملگر ) را تایپ کرده و بعد بر روی سلول A2 ( دومین عملوند) کلیک می کنیم و در انتها Enter می کنیم.

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

 روش اول : بر روی سلول کلیک کرده و سپس در نوار فرمول در محل مورد نظر کلیک کرده ، ویرایش را انجام می دهیم.

 روش دوم : بر روی سلول double Click کرده و ویرایش را انجام می دهیم.

 انتخاب یک یا چند خانه

 1- توسط صفحه کلید : - برای انتخاب یک خانه کافی است با جهت نما (Arrow key)بر روی آن برویم.

 - برای انتخاب تعدادی خانه مجاورکافی است دکمه Shift را پایین نگه داشته و با کلید های جهت نما ( Arrow Key)بر روی آن حرکت کنیم.

 - برای انتخاب سطر جاری کافی است کلید های Shift + Spacebar را بفشاریم.

 - برای انتخاب یک ستون کافی است کلید های Ctrl+ Spacebar را بزنیم.

 - برای انتخاب کاربرگ جاری کافی است کلیدهای Ctrl + Shift+ Spacebar را بزنیم.

 2- توسط ماوس : - برای انتخاب یک خانه کافی است با ماوس بر روی آن کلیک کنیم.

 - برای انتخاب تعدادی خانه مجاور کافی است دکمه سمت چپ ماوس را پایین نگه داشته و روی خانه ها Drag کنیم.

 - برای انتخاب یک سطر کافی است روی شماره سطر Click کنیم.

 - برای انتخاب یک ستون کافی است روی حرف ستون Click کنیم.

 - برای انتخاب یک کاربرگ کافی است از منوی Edit ، گزینه Select All را انتخاب کنیم.

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

 دستور برگشت : میتوانیم عمل انجام داده شده را برگردانیم . فرض کنید در سلول A1 عدد 10 را می نویسیم و سپس آنرا پاک می کنیم . اگر در این زمان ازدستور Undo استفاده کنیم . عدد 10 بر میگردد . برای استفاده از Undo به یکی از روشهای زیر عمل می کنیم.

روش اول: 1- انتخاب منوی Edit 2- انتخاب Undo

 روش دوم : فشردن همزمان کلیدهای ctrl+z

 روش سوم : استفاده از آیکون Undo در نوار ابزار Standard ( ) دستور Redo : با این دستور می توانیم عمل Undo را برگردانیم .

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

 روش اول : 1- انتخاب منوی Edit 2- انتخاب Redo

 روش دوم : فشردن همزمان کلیدهای ctrl+y

 روش سوم : استفاده از آیکون Redo در نوار ابزار نسخه برداری و انتقال سلولها نسخه برداری از سلولها : 1- موضوعات مورد نظر را انتخاب میکنیم.

 2- به یکی از روشهای زیر Copy را انتخاب میکنیم :

الف- استفاده از آیکون Copy ( )

 ب- انتخاب منوی Edit، گزینه Copy

ج- Right Click بر روی موضوع و انتخاب گزینه Copy

د- فشردن همزمان کلیدهای Ctrl+C

 3- Click بر روی مکانی که میخواهیم اطلاعات اضافه شوند.

 4- با یکی از روشهای زیر Paste را انتخاب میکنیم :

 الف- استفاده از آیکون Paste ( )

 ب- انتخاب منوی Edit ، گزینه Paste

 ج- Right Click بر روی موضوع و انتخاب گزینه Paste

 د- فشردن همزمان کلید های Ctrl+V انتقال سلولها : 1- انتخاب موضوعات مورد نظر 2- به یکی از روشهای زیر Cut را انتخاب میکنیم : الف- استفاده از آیکون Cut ( ) ب- انتخاب منوی Edit، گزینه Cut ج- Right Click بر روی موضوع مورد نظر و انتخاب گزینه Cut د- فشردن همزمان کلیدهای Ctrl +X 3- Click بر روی محل انتقال موضوعات

 4- به یکی از روشهای گفته شده در حالت قبل گزینه Paste را انتخاب حذف سلول وقتی یک سلول را حذف میکنیم محتویات آن به همراه خود سلول حذف میشود که جای این سلول یک فضای خالی ایجاد میشود. این فضای خالی باید توسط سلولهای مجاور پر شود.

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

 روش اول : 1- انتخاب موضوعات 2- انتخاب منوی Edit 3- انتخاب گزینه Delete

 روش دوم : 1- انتخاب موضوعات 2- Right Click بر روی خانه 3- انتخاب گزینه Delete با انتخاب گزینه Delete پنجره ای ظاهر میشود که شامل چهار گزینه زیر است ( در این پنجره میتوانیم تعیین کنیم که محل خالی سلول حذف شده با کدام یک از سلولهای مجاور پر شود. ) :

 الف- Shift Cell Left : باعث انتقال خانه سمت راست خانه پاک شده به جای آن میشود.

 ب- Shift Cells Up : خانه زیرین خانه پاک شده را بجای آن منتقل میکند.

 ج- Entire Row : سطر زیرین خانه پاک شده را به جای سطری که خانه پاک شده در آن قرار دارد منتقل میکند.

 د- Entire Column : ستون سمت راست خانه پاک شده را به جای ستونی که خانه پاک شده در آن قرار دارد منتقل میکند.

 نکته اگر محیط فارسی باشد بجای ستون سمت راست , ستون سمت چپ جایگزین میشود.

 پاک کردن سلول : در این حالت خود سلول حذف نمیشود و فقط محتویات داخل آن یا قالب بندی آن یا توضیحات آن و یا هر سه پاک میشوند.

 1- خانه های مورد نظر را انتخاب میکنیم.

 2- از منوی Edit ، گزینه Clear را انتخاب میکنیم.

 3- زیر منویی باز میشود که شامل گزینه های زیر است :

 الف- All : هم محتوای خانه ، هم Format خانه و هم توضیحات را پاک میکند.

 ب- Format : فقط Format خانه های انتخابی را پاک میکند.

 ج- Comment : فقط توضیحات را پاک میکند.

 د- Content : فقط محتوای خانه های انتخابی را پاک میکند .

بهار بهترین بهانه برای آغاز و آغاز بهترین بهانه برای زیستن است.

 سال نو مبارک

گل ناز تقدیم به شما

 شکوه شکوفایی شکوفه ها بر شما مبارک