درج فرمول به صورت دستی

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

درج فرمول به صورت دستی به معنای تایپ فرمول به وسیله ی کاربر است. برای این کار، ابتدا سلولی را انتخاب کرده، سپس کارکتر مساوی و به دنبال آن فرمول مورد نظر را تایپ کنید. به موارات این کار، کارکتر ها در سلول و نوار فرمول ظاهر می شوند.

درج سلول به صورت ارجاع سلولی

اگر چه می توانید فرمول ها را تایپ کنید اما اکسل روش دیگری تیز برای درج فرمول ها ارایه می کند که بسیار ساده تر، سریع تر و کم خطاتر است. هرچند دراین روش هم بخشی از کار به صورت دستی انجام می‌شود اما می توانید به جای تایپ مقادیر به وسیله ی کاربر از ارجاع های سلولی استفاده کنید. برای مثال برای درج فرمول =A1=A2 در سلولA3 می‌توانید مراحل بعدی را اجرا کنید.

1- اشاره گر سلول را به سلولA1 انتقال دهید.

2- علامت مساوی را به عنوان شروع فرمول تایپ کنید و دقت کنید که با این کار، عبارت Enter در نوار وضعیت ظاهر می شود.

3- دوباره کلید هدایت گر رو به بالا را فشار دهید. وقتی این کلید را فشار دهید، یک کادر چشمک زن متحرک دور سلول A1 و ارجاع سلولی در سلول A3 ونوار فرمول نمایان می شود، به علاوه در نوار وضعیت نیز عبارت Point ظاهر می شود.

4- با تایپ کارکتر علامت جمع، کادر چشمک طن و متحرک دور سلول A1 تبدیل به کادری با رنگ ثابت و غیر متحرک شده و دوباره عبارت Enter در نوار وضعیت ظاهر میشود.

5- دوباره کلید هدایت گر رو به بالا را فشار دهید تا مرز چشمک زن و متحرک دور سلول A2 قرار گیرد و آدرس آن سلول به فرمول اضافه شود.

6- در پایان کلید اینتر را فشار دهید.

درج نام دامنه ها در فرمول

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

نام مورد نظر را از یک فهرست باز شو اتخاب کنید: برای استفاده از این روش باید دست کم اولین کارکتر آن را بدانید. در هنگام درج فرمول، کارکتر نخست نام را تایپ کرده و سپس از بین گزینه‌های فهرست بازشو، نام مورد نظر را انتخاب کنید.

فشردن کلیدF3: با این کار، کادر محاوره ای Paste name نمایش داده می شود. می توانید نام مورد نظرتان را از فهرست موجود انتخاب کرده و سپس روی دکمه یok کلیک کنید یا روی نام مذکور کلیک دوبل کنید. با این کار، اکسل نام مورد نظرتان را در فرمول درج می کند.

درج توابع در فرمول‌ها

ساده ترین روش درج یک تابع در فرمول، استفاده از خصوصیت formula autocomplete است. البته برای استفاده از این روش باید دست کم کارکتر نخست نام تابع را بدانید.روش دیگر برای درج تابع،استفاده از گروه function library روی زبانه‌ی formulas در نوار ریبون است. این روش به خصوص وقتی فراموش کردید به کدام تابع نیاز دارید، مفید خواهد بود. در هنگام درج فرمول، روی دسته تبع مورد نظر مانند financial، logical، text و دیگر موارد کلیک کنید تا فهرستی از توابع دسته ی مربوط ظاهر شود. سپس روی تابع مورد نظر کلیک کنید تا اکسل، کادر محاوره‌ای function arguments را نمایش دهد. در این کادر آرگومان‌های تابع را وارد می‌کنید. به علاوه می‌توانید روی لینک help on this function کلیک کنید تا درباره ی تابع انتخاب شده، شرح مختصری را ببیند.

روش دیگر درج تابع در فرمول، استفاده از کادر محاوره‌ای insert function است. به چند روش می توان این کادر محاوره ای را فعال کرد:

1- اجرای دستور:

Formulas→ function library→ insert function

2- استفاده از دستور insert function  که در پایین هر فهرست باز شو در گروه Formulas→ function library ظاهر می شود.

3- کلیک روی آیکون insert function که در سمت چپ نوار Formulas قرار دارد. روی دکمه ی این یکون عبارت fx درج شده است.

4- فشردن کلید ترکیبی shift+F3

 

درج فرمول به صورت دستی

کادر محاوره‌ای insert function  فهرست باز کشویی را نشان می دهد. دسته ی مورد نظر را انتخاب کنید تا توابع موجود در ان در یک کادر فهرستی به نمایش در آید. برای دسترسی به توابعی که اخیرا به کار گرفته شده اند باید فهرست باز شو، گزینه‌ی most recently used را انتخاب کنید.

اگر مطمئن نیستید کدام که کدام تابع مورد نیازتان است، می توانید با استفاده از فیلد search for a function در بالای کادر محاوره ای به دنبال تابع متناسب بگردید. روال کار به صورت زیر است:

1- عبارت جست و جو را وارد و روی دکمه‌ی Go کلیک کنید تا فهرستی از توابع مرتبط با عبارت جست و جوی شما نمایش داده شود. وقتی تابع را از فهرست select a function انتخاب کنید اکسل، آن تابع و نام آرگومان‌هایش را همراه با شرح مختصری از عملکرد آن تابع در یک کادر محاوره ای نمایش می‌دهد.

2- وقتی محل مورد نظرتان را پیدا کردید روی آن و سپس روی دکمه OK کلیک کنید تا کادر محاوره ای function arguments که در تصویر زیر نشان داده شده است به نمایش در آید.

3- ارگومان‌های تابع را مشخص کنید. کادر محاوره ای function arguments بسته به نوع تابعی که درج می کنید، تغییر کرده و برای هر کدام از آرگومان های تابع بک کادر متنی در کادر محاوره ای نمایش می دهد. برای اینکه از یک ارجاع سلولی یا دامه به عنوان آرگومان تابع استفاده کنید می توانید آن آدرس سلول یا دامنه را به صورت دستی درج یا در کادر متنی آرگومان کلیک کنید و سپس سلول یا دامنه‌ی مورد نظر را در کاربرگ انتخاب کنید.

4- پس از مشخص کردن تمام آرگومان‌های تابع باید روی دکمه OK کلیک کنید.

درج فرمول به صورت دستی

ویرایش فرمول ها

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

1- روش سلول مورد نظر کلیک دوبل کنید تا امکان ویرایش مستقیم محتوی آن ممکن شود.

2- کلید F2 را فشار دهید تا امکان ویرایش مستقیم محتوی سلول فعال ممکن شود.

3- سلولی که قرار است ویرایش شود را انتخاب کنید و سپس در نوار Formula کلیک کنید. با این کار می توانید محتویات سلول را در نوار Formula ویرایش کنید.

4- اگر سلول دارای فرمولی است که پاسخ ان خطاست اکسل در گوشه‌ی بالای سمت چپ سلول یک مثلث کوچک نمایش می دهد. با فعال کردن سلول، یک Smart tag ظاهر می شود که در صورت کلیک روی آن می توانید یکی از گزینه های تصحیح فرمول را انتخاب کنید. دقت کنید که این گزینه‌ها بسته به نوع خطایی که رخ می‌دهد تغییر می کنند.

در هنگام ویرایش فرمول می توانید با درگ کردن مکان نمای ماوس روی چندین کارکتر یا نگه‌داشتن کلید Shift و استفاده از کلید‌های هدایتی، کارکترهای مورد نظر را انتخاب کنید. اگر فرمولی داشتید که ویرایش آن ممکن نبود یا تغییر آن به درستی انجام نمی‌شد، می‌توانید با برداشتن کارکتر مساوی از ابتدای آن، فرمول را تبدیل به یک عبارت متنی ساده کرده و پس از پایان ویرایش، کارکتر مساوی را دوباره به ابتدای آن اضافه کنید تا دوباره تبدیل به فرمول شود.

استفاده از ارجاع‌های سلولی در فرمول‌ها

بیشتر فرمول هایی که می سازید شامل ارجاع‌هایی به سلول ها یا دامنه‌ها هستند که به فرمول،امکان ارتباط پویا با داده‌های موجود در آن سلول یا دامنه‌ها را می دهند. برای مثال اگر فرمول به سلول A1 را تغییر دهید، نتیجه‌ی فرمول نیز با توجه به مقدار جدید سلول A1 تغییر خواهد کرد. البته اگر در فرمول از ارجاع های سلولی استفاده نکرده باشد. برای تغییر مقادیر به کار گرفته شده در فرمول‌ها باید خود فرمول را ویرایش کنید.

استفاده از ارجاع‌های نسبی، مطلق و مختلط

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

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

2- مطلق: در این حالت وقتی فرمولی را در سلول جدیدی کپی کنید، ارجاع‌های ردیف و ستون در آن تغییر نمی کنند چون ارجاع در واقع در واقع آدرس واقعی یک سلول است. در آدرس ارجاع مطلق، باید از کارکتر$ برای حروف مربوط به ستون و شماره ردیف استفاده کنید. مثلا $A$5یک ارجاع مطلق به سلول  A5  است.

3- مختلط: در این حالت ارجاع به ردیف یا ستون نسبی و به دیگری مطلق بوده و در واقع تنها نیمی از آدرس، مطلق خواهد بود که در صورت کپی کردن به آدرس جدید، بخش نسبی تغییر کرده و بخش مطلق ثابت می ماند. مثلا در آدرس $A4، آدرس ستون مطلق و ردیف نسبی هستند.

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

در تصویر زیر یک کاربرگ ساده نشان داده شده است که در سلول D2، فرمولی است که کمیت را در قیمت ضرب می کند:

=B2*C2

این فرمول از ارجاع سلولی نسبی استفاده می کند. بنابراین وقتی فرمول را در سلول‌هایی زیر آن کپی کنید ارجاع‌ها نیز به شیوه‌ی نسبی تغییر خواهند یافت. برای مثال شکل فرمول بالا در سلول D3  به صورت زیر در می اید:

=B3*C3

درج فرمول به صورت دستی

اما اگر ارجاع‌های سلولی در D2، اجاع‌های مطلق باشند چه اتفاقی رخ می دهد؟ فرض کنید فرمول بالا به صورت زیر نوشته شده باشد:

=$B$2*$C$2

در این حالت با کپی کردن فرمول بالا در سلول‌های زیر آن، نتایج درستی به دست می آید چون فرمول سلول D3 نیز دقیقا مانند فرمول سلول D2 خواهد بود.

اکنون مثال بالا را تغییر دهید تا مالیات فروش که مقدار آن در سلول B7 ذخیره شده نیز محاسبه شود. در این وضعیت، فرمول سلول D2 به صورت زیر خواهد بود:

=(B2*C2)*$B$7

که در آن، کمیت در قیمت و سپس نتیجه در نرخ مالیت فروش که در سلول B7 ذخیره شده ضرب خواهد شد. دقت کنید چون ارجاع سلولB7، یک ارجاع مطلق است وقتی سلول D2 به سلول D3 کپی شود، شکل فرمول به صورت زر در می آید:

=(B3*C3)*$B$7

درج فرمول به صورت دستی

در اینجا به نسبت محل جدید، ارجاع سلول‌های B2 وC2 تغییر یافته اند اما ارجاع به سلول B7 بدون تغییر مانده و این همان چیزی است که مد نظر ماست چون مقدار مالیات فروش هیچ گاه تغییر نمی کند.

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

=$B3*C$2

دقت کنید که هر دو ارجاع سلول از نوع مختلط هستند. در ارجاع به سلول B3، از یک ارجاع مطلق بای ستون($B) و ارجاع به سلول C2، از یک ارجاع مطلق برای ردیف ($2) استفاده شده است. در نتیجه می توان این فرمول را بدون اینکه نتیجه یمحاسبات نادرست از آب در بیاید، در سلول های دیگر کپی کرد. برای مثال فرمول بالا ئر سلول B7 به صورت زیر در می‌آید:

=$B7*F$2

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

درج فرمول به صورت دستی

تغییر نوع ارجاع

می توان ارجاع‌های غیر نسبی( یعنی ارجاع مطلق با مختلط) را به صورت دستی و با درج کارکتر دلار در محل مناسب آدرس سلول ایجاد کرد یا اینکه می توانید از یک را میانبرکلیدF4  استفاده کنید. قتی یک ارجاع سلولی را درج می کنید می توانید چند بار کلید F4 را فشار دهید تا اکسل به ترتیب، چهار حالت مختلف ارجاع را فعال کند.

برای مثال اگر =A1 را برای شروع فرمول درج کنید، فشردن کلید F4 باعث می شود ارجاع سلولی به =$A$1 تبدیل شود. اگر کلید F4  را دوباره فشار دهید، ارجاع سلولی به =A$1 تبدیل می شود. فشردن دوباره‌ی این کلید، ارجاع سلولی را به =$A1 تبدیل می کند. چهرمین مرتبه‌ی فشردن کلید F4 بعث می شود دوباره ارجاع=A1 درج شود.

وقتی سلول یا دامنه‌ای را نام‌گذاری کنید، اکسل به صورت پیش فرض از ارجاع مطلق برای نام گذاری استفاده می کند. برای مثال اگر دامنه‌ی B1:B12 را SalesForecast بنامید، گزینه‌ی Refers to در کادر محاوره‌ای New Name، ارجاع را به صورت:

$b$1:$b$12

نمایش می دهد. اگر سلولی را کپی کنید که در فرمول آن، ارجاعی نامگذاری شده وجود داشته باشد، فرمول کپی شده شامل ارجاعی به نام(دامنه) اصلی خواهد بود.

ارجاع به سلول های خارج از کاربرگ فعال

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

ارجاع به سلول دیگر کاربرگ

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

Sheetname!celladdress

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

=A1*Sheet2!A1

این فرمول مقدار سلول A1 در کاربرگ فعلی را در مقدار سلول A1 از کاربرگSheet2 ضرب می‌کند. اگر نام کاربرگی در عملیات ارجاع به آن استناد می شود دارای یک یا چند کارکتر فاصله باشد، باید نام کاربرگ را در بین دو کارکتر علامت نقل قول تکی(‘) قرار دهید. باری مثال، فرمول زیر به سلول روی کاربرگی به نام All Depts ارجاع می کند:

=A1*’All Depts’!Al

ارجاع به سلول‌های کار پوشه‌های دیگر

برای ارجاع به سلولی که در یک کارپوشه دیگر قرار دارد از قالب بندی زیر استفاده کنید:

= [workbookname]SheetName!Celladdress

در این حالت ابتدا نام پوشه در بین دو کروشه قرارمی گیرد، سپس نام کاربرگ همراه با کارکتر علامت تعجب و در پایان آدرس سلول نوشته می‌شود. مثال زیر، فرمولی است که در آن از ارجاع سلولی به کاربرگ Sheet1 در کار پوشه‌ای به نام Budget استفاده شده است:

= [Budget.xlsx] Sheet1! A1

اگر نام کارپوشه که در عملیات ارجاع به آن استناد می شود دارای یک یا چند کارکتر فاصله باشد، باید نام کارپوشه را در بین دو کارکتر نقل قول تکی قرار دهید. برای مثال فرمولی که به سلولی در کاربرگ Sheet1  از کار پوشه‌ای به نام Budget For 2016 ارجاع می گردد به صورت زیر نوشته می شود:

=A1*’[Budget For 2016.xlsx] Sheet’!Al

نیازی نیست کارپوشه‌ای که از فرمولی در کارپوشه‌ی دیگر به سلول‌های آن ارجاع می شود حتما باز باشد. البته اگر کارپوشه بسته باشد باید مسیر کامل آن کارپوشه را در فرمولی که به ان ارجاع  می شود ذکر کنید تا اکسل بتواند آن کارپوشه را پیدا کند. به مثال زیر دقت کنید:

=A1*’C: \My Documents\ [Budget For 2016.xlsx]Sheet1’!A1

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

فایل مورد نظر شما ممکن است در سیستم دیگری از طریق شبکه قابل دسترس قرار داشته باشد. به عنوان مثال، فرمول زیر به سلولی در کارپوشه‌ای که در پ.شه‌ی Files از کامپوتری به نام DataServer قرار دارد ارجاع می کند:

=’\\Dataserver\files\[budget.xlsx]Sheet’!$D$7

 


نویسنده : --

تگ ها : آموزش اکسل
ثبت نظر