استفاده از نام در اکسل

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

استفاده از نام برای ثابت ها

بسیاری از کاربران اکسل نمی دانند که می توانند به گزینه هایی که در سلول نمایان نمیشوند، اما به شکلی مورد استفاده قرار گیرند که نام اختصاصی دهند. برای مثال اگر فرمولی در یک کاربرگ از نرخ مالیات فروش استفاده می کند کاربر در حالت معمولی، مقدار عددی نرخ مالیات را در سلولی درج کرده و در فرمول ارجاع آن سلول استفاده می کند. اما برای ساده تر شدن کارها می تواند نامی مانند  Sales را برای سلول مذکور انتخاب کنید. چگونگی تعیین نام برای مقداری که در سلول ظاهر نمی شود به صورت زیر است:

1- دستور Define Name Formulas→ Defined Name→ را اجرا کنید تا کادر محاوره ای New Name ظاهر شود.

2- نام دلخواه خود( به عنوان مثال Sales) را در فیلد Name درج کنید.

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

4- در کادر متنی Refers to کلیک کنید تا محتویات آن پاک شود و مقدار جدید(075/0) را به جای مقدار قبلی درج کنید.

5- در صورت تمایل می توانید در کادر Comment توضیحاتی درباره‌ی نام انتخاب شده درج کنید.

6- برای بستن کادر محاوره ای New Name روی دکمه OK کلیک کنید.

اکنون نامی ساخته اید که به جای ارجاع به یک سلول یا دامنه، به یک مقدار ثابت ارجاع می کند و از این پس اگر در سلولی که در حوزه ی مشخص شده برای نام قرار دارد، عبارت=Sales  را درج کنید، مقدار عددی 075/0 برگردانده خواهد شد که در واقع همان مقدار ثابتی است که در مراحل پیشین تعریف شده است. از این نام می توانید به صورت زیر در فرمول ها نیز استفاده کنید:

=Al*Sales

مقدار ثابت می تواند یک مقدار متنی نیز باشد. مثلا می توانید برای نام شرکت خود، یک مقدار ثابت تعریف کنید. توجه داشته باشید که ثابت های نامگذاری شده در کادر Name یا کادر محاوره ای Go To نمایان نمی شوند. این امر به این دلیل است که این ثابت ها در هیچ جای خاصی ذخیره نشده اند اما در هنگام درج فرمول، در فهرست بازشویی که نمایش داده می شوند حضور دارند.

استفاده از نام فرمول ها

همان طور که می توانید ثابت های نام گذاری ایجاد کنید، می توانید فرمول های نام گذاری شده نیز بسازید. فرمول های نام گذاری شده انند ثابت های نام گذاری شده، در کاربرگ ظاهر می شوند. فرمول های نام گذاری شده مانند ثابت های نام گذاری شده با استفاده از کادر محاوره ای New Name ساخته می شوند. برای مثال می توانید یک فرمول نام گذاری شده بسازید که نرخ سود ماهیانه را از روی نرخ سود سالانه محاسبه کند. تصویر زیر مثالی از این عملیات است. در این مثال، نام Monthly Rate به فرمول زیر ارجاع می شود:

=Sheet3!$B$1/12

استفاده از نام در اکسل

وقتی از نام Monthly Rate در یک فرمول استفاده کنید، در واقع ان سلول از مقادیر سلول B1 تقسیم بر 12استفاده می کند. دقت کنید که ارجاع سلولی در چنین حالتی از نوع ارجاع مطلق خواهد بود.

وقتی به جای ارجاع مطلق از ارجاع نسبی استفاده کنید، نام گذاری سلول بیشتر جلب توجه خواهد کرد. وقتی از کلیک موس برای تعیین سلول ها در فیلد Refers to از کادر محاوره ای New Name استفاده کنید، اکسل همیشه از ارجاع های سلولی طلق استفاده می کند که بر خلاف رفتار طبیعی آن در هتگام ساخت فرمول در یک سلول است.

برای مثال، سلولB1 در کاربرگ Sheet1 را فعال و نام Cubed را برای فرمول زیر انتخاب کنید:

=Sheet1!A1^3

در این مثال ارجاع نسبی به سلول سمت چپ که فرمول نامگذاری شده در آن به کار گرفته شده اشاره می کند. بنابر این بسیار مهم است که پیش از باز کردن کادر محاوره ای New Name مطمئن شوید که سلول B1  همان سلول فعال است. این فرمول حاوی یک ارجاع نسبی است و وقتی از این فرمول نام گذاری شده در یک کاربرگ استفاده کنید، ارجاع نسبی همیشه متناسب با سلولی خواهد بود که حاوی فرمول است. برای مثال اگر در سلول D12 عبارت=Cubed را درج کنید، از این پس محتویات سلول C12 به توان 3 در آن تنایش داده خواهد شد. دقت کنید که سلول C12 همان سلولی است که در سمت چپ سلول D12  قرار گرفته است.

استفاده از فصل مشترک دامنه ها

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

استفاده از نام در اکسل

در سلولB9 فرمول زیر قرار دارد:

=C1:C6  A3:E3

این فرمول عدد 107 که مقدار عددی سلول C3 است را برمی گرداند و این همان مقداری است که در فصل مشترک دو دامنه بالا قرار دارد.

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

عملگر

عملکرد

: (کارکتر دو نقطه)

یک دامنه را مشخص می کند.

, ( کارکتر ویرگول)

اجماع دو دامنه را مشخص می کند. این عملگر، ارجاع عای چند دامنه را باهم ترکیب و تبدیل به یک ارجاع می نماید.

(کارکتر فاصله)

فصل مشترک دو دامنه را مشخص می کند. این عملگر, سلول هایی را که در د. دامنه مشترک هستند را بر می گرداند.

 

استفاده از نام در اکسل

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

Formulas→ Defined Name→ Create from Selection

اکسل نام های زیر را ایجاد کرد:

North     =Sheet1! $B$2:$E$2     Quarter1     =Sheet1! $B$2:$B$5    

South     =Sheet1! $B$3:$E$3     Quarter2     =Sheet1! $C$2:$C$5   

West      =Sheet1! $B$4:$E$4     Quarter3     =Sheet1! $D$2:$D$5    

East       =Sheet1! $B$5:$E$5     Quarter4     =Sheet1! $E$2:$E$5    

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

=SUM(Quarter4)

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

=Quarter1  West

این فرمول قدار فصل اول (Quarter1) برای منطقه‌ی West را بر می گرداند. به عبارت دیگر این فرم.ل مفداری را که در محل تقاطع دامنه‌ی Quarter1 با دامنه‌ی West وجود دارد را بر می گرداند. نامگذاری دامنه‌ها به این شیوه می تواند در ساخت فرموا هایی که کاملا خوانا هستند کمک کنند.

به کارگیری نام برای ارجاع های موجود

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

=A1-A2

اگر برای سلول A1 نام Income و برای سلول A2  نام Expenses راتعریف کنید اکسل نمی تواند به صورت خودکار فرمول بالا را به Expenses = Income – تبدیل کند. البته جایگزینی نام سلول یا دامنه ها در ارجاع آنها کار ساده ای است. برای به کارگیری نام ها به جای ارجاع های سلولی در فرمول ها باید ابتدا دامنه‌ای که می خواهید تغییرات روی آن اعمال شود را انتخاب کنید. سپس دستور:

Define Name Formulas→ Defined Name→ Apply Names

را اجرا کنید تا کادر محاوره ای Apply Names نمایش داده شود. نام هایی که می خواهید به کار گرفته شوند را با کلیک روی آنها انتخاب و سپس روی دکمه OK کلیک کنید. اکسل، ارجاع عای دامنه را با نام های موجود در سلول های انتخاب شده عوض می کند.


نویسنده : --

تگ ها : آموزش اکسل
تاپ سایت 98
عضویت طلایی
محصولات آموزشی
ثبت نظر