چگونه با محدوده‌های دارای نام در اکسل کار کنیم؟ (+ دانلود فیلم آموزش گام به گام)

فرمول‌های اکسل غالباً از داده‌ها و مقادیر موجود در سلول‌های دیگر به وسیله ارجاع (references) به آن سلول‌ها استفاده می‌کنند. اگر فرمول‌های زیادی در یک فایل اکسل وجود داشته باشند، ارجاع سلولی ممکن است باعث سردرگمی شود و درک فرمول‌ها را دشوارتر نماید. برای سهولت کار در چنین مواردی می‌توان از محدوده‌های دارای نام در اکسل استفاده کرد.

نام‌های تعریف شده (Defined Names) در اکسل باعث می‌شوند که فرمول‌ها و دیگر داده‌ها پیچیدگی کمتری داشته باشند و درک آن‌ها آسان‌تر شود. به جای ارجاع به یک سلول که حاوی یک مقدار یا یک فرمول یا محدوده‌ای از سلول‌ها بر اساس عنوان ردیف یا ستون (A1, B2 و ...) است؛ می‌توان از نام‌های انتساب داده شده به آن سلول یا محدوده سلول‌ها استفاده نمود.

در این نوشته روش کار با محدوده‌های دارای نام از سلول‌ها را در اکسل بررسی می‌کنیم که شامل تعریف نام، قواعد و حوزه عمل نام‌ها، و روش ویرایش، حذف، مشاهده و استفاده از نام‌ها است.

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

ابتدا باید بگوییم که نباید نام‌های تعریف شده را با نام جدول‌ها اشتباه بگیرید. یک جدول اکسل مجموعه‌ای از داده‌ها در رکوردها (ردیف) و فیلدها (ستون) است. اکسل نام‌های پیش‌فرضی (Table1, Table2 و ...) برای هر جدول اختصاص می‌دهد؛ اما می‌توانید نام‌ها را تغییر دهید. برای مطالعه ادامه این نوشته باید اطلاعات مقدماتی در مورد جداول اکسل و روش کار با آن‌ها داشته باشید. برای یادگیری بیشتر در این خصوص می‌توانید به گنجینه آموزش‌های اکسل مراجعه نمایید.

قواعد نام‌های تعریف شده در اکسل

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

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

کلیک کنید

برای مشاهده موارد بیشتر در خصوص قواعد نام‌های تعریف شده می‌توانید از این راهنمای مایکروسافت استفاده کنید.

حوزه عمل نام‌های تعریف شده در اکسل

حوزه عمل نام‌های تعریف شده در اکسل یعنی یک نام تعریف شده در کدام بخش از فایل اکسل بدون پیش‌نیازی همچون اشاره به نام برگه یا نام فایل می‌تواند شناسایی شود. هر نام می‌تواند یک حوزه عمل در سطح کاربرگ (worksheet) یا حوزه عمل در سطح فایل یا کارپوشه (workbook) داشته باشد.

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

ایجاد یک محدوده دارای نام در اکسل

چند گزینه مختلف برای ایجاد محدوده دارای نام در اکسل وجود دارند. می‌توان نام‌ها را با استفاده از کادر Name در نوار فرمول ایجاد کرد. همچنین می‌توان از متن سلول عنوان برای گروهی از سلول‌های منتخب استفاده کرد و یا کادر محاوره‌ای New Name را به این منظور مورد استفاده قرار داد.

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

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

تعریف کردن یک نام با استفاده از کادر Name

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

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

برای استفاده از کادر Name برای تعریف کردن نام کارهای زیر را انجام دهید:

1.  یک سلول یا محدوده‌ای از سلول‌ها را انتخاب کنید.
2. نامی که می‌خواهید را در کادر Name در سمت چپ نوار فرمول وارد کرده و دکمه اینتر را بزنید.

اطمینان پیدا کنید که قواعد نامگذاری که قبلاً اشاره کردیم را رعایت کرده‌اید.

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

اگر سطر یا ستون عنوان را برای داده‌های خود تعریف کرده‌اید، می‌توانید از این عنوان‌ها به عنوان نام تعریف شده نیز استفاده کنید.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

برای تعریف نام از عنوان، کارهای زیر را انجام دهید:

1. سلول‌هایی که می‌خواهید شامل برچسب ردیف یا ستون باشند را انتخاب کنید.
2. در نوار Formula و در بخش Defined Names بر روی Create from Selection کلیک کنید و یا دکمه‌های میانبر Ctrl + Shift + F3 را بزنید.

اگر برچسبی شامل فاصله (Space) یا دیگر کاراکترهای نامعتبر مانند علامت (&) باشد، با یک علامت زیرخط جایگزین می‌شوند.

در کادر محاوره‌ای Create Names from Selection موقعیت برچسب عنوان را که می‌خواهید به عنوان نام استفاده شود وارد کنید.

برای مثال، برچسب عنوان در تصویر فوق در بخش فوقانی ستون انتخاب شده است. بنابراین کادر Top row را بررسی کرده و بر روی OK کلیک می‌کنیم.

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

تعریف کردن یک نام با استفاده از کادر محاوره‌ای New Name

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

با استفاده از کادر محاوره‌ای New Name می‌توان یک سلول یا محدوده‌ای از سلول‌ها را به صورت زیر تعریف کرد:

1. اگر می‌خواهید نامی برای یک سلول یا محدوده‌ای از سلول‌ها تعریف کنید، ابتدا سلول‌هایی که قصد نامگذاری دارید را انتخاب کنید. در صورتی که نام را برای یک مقدار یا فرمول تعیین می‌کنید، مهم نیست که کدام سلول‌ها در حالت انتخاب هستند.
2. در نوار Formula در بخش Defined Names بر روی Define Name کلیک کنید.

در کادر محاوره‌ای New Name اطلاعات زیر را تعیین کنید:

• Name: یک نام را با استفاده از قواعد پیش‌گفته برای نام تعریف شده وارد کنید.
• Scope: به طور پیش‌فرض حوزه عمل فایل برای نام تعریف شده انتخاب گشته است. اگر می‌خواهید حوزه عمل در سطح کاربرگ داشته باشید، این گزینه را از فهرست باز شونده Scope انتخاب کنید.
• Comment: هر توضیحی که می‌خواهید در مورد نام داشته باشید را در این بخش وارد کنید.
• Refers to: نام سلول‌های انتخابی فعلی به همراه نام کاربرگ فعال به طور خودکار در این کادر وارد شده است. اگر نام را برای یک مقدار یا فرمول تعریف می‌کنید، می‌بایست در این کادر ابتدا یک علامت (=) و سپس مقدار یا فرمول مورد نظرتان را وارد نمایید.

دکمه سمت راست کادر Refers to امکان پایین فرستادن کادر محاوره‌ای New Name و انتخاب سلول‌ها بر روی کاربرگ جهت محدوده سلولی مورد ارجاع نام تعریف شده را فراهم می‌کند. در مورد شیوه استفاده از این دکمه در بخش بعدی «ویرایش یک محدوده دارای نام» بیشتر توضیح داده‌ایم.

زمانی که وارد کردن اطلاعات مربوط به نام را به پایان بردید، می‌توانید بر روی OK کلیک کرده و به کاربرگ بازگردید.

ویرایش یک محدوده دارای نام

زمانی که نامی را تعریف کردید، می‌توانید آن را تغییر دهید. همچنین می‌توانید توضیح، و چیزی که به آن ارجاع می‌کند را ویرایش کنید.

برای ویرایش یک نام تعریف شده یا محدوده دارای نام در نوار Formula در بخش Defined Name بر روی Name Manager کلیک کنید.

اگر فهرست بلندی از نام‌ها دارید، می‌توانید آن‌ها را برای یافتن مواردی که قصد ویرایش دارید فیلتر کنید.

در گوشه سمت راست-بالای کادر محاوره‌ای Name Manager بر روی Filter کلیک کنید. سپس انواعی از نام‌ها که می‌خواهید نمایش یابند را تعیین نمایید. می‌توانید چند نوع فیلتر را با کلیک مجدد بر روی Filter و انتخاب گزینه‌های دیگر انتخاب کنید.

برای پاک کردن فیلترها و نمایش مجدد همه نام‌ها بر روی Filter کلیک کرده و گزینه Clear Filter را انتخاب نمایید.

برای تغییر دادن نام یا توضیح، نامی که می‌خواهید تغییر دهید را انتخاب کرده و بر روی Edit کلیک کنید.

حوزه عمل (Scope) یک نام تعریف شده را نمی‌توان تغییر داد. اگر چنین قصدی دارید باید آن را حذف کرده و مجدداً با حوزه عمل جدید تعریف کنید.

می‌توان سلول‌های مورد ارجاع یک نام را در بخش Refers to در کادر Edit Name ویرایش کرد. اما می‌توان این کار را به طور مستقیم در کادر محاوره‌ای Name Manager نیز انجام داد. این روش برای تغییر سلول‌های مورد ارجاع یک نام در هر دو کادر محاوره‌ای یکسان است.

برای تغییر سلول یا محدوده سلول‌های مورد ارجاع یک نام در کادر محاوره‌ای Name Manager، ابتدا باید نامی که قصد تغییر آن را داریم انتخاب کنیم. سپس بر روی دکمه فلش رو به بالا در سمت راست کادر Refers to کلیک می‌کنیم.

کادر محاوره‌ای Name Manager طوری جمع می‌شود که تنها کادر Refers to در آن مشاهده می‌شود. برای تغییر ارجاع کارهای زیر را انجام می‌دهیم:

1. سلول یا محدوده‌ای از سلول‌ها را روی کاربرگ انتخاب می‌کنیم.
2. بر روی دکمه سمت راست کادر Refers to کلیک می‌کنیم

اینک ارجاع سلولی جدید را در کادر Refers to می‌بینید و کادر محاوره‌ای Name Manager به حالت عادی خود باز می‌گردد.

برای پذیرش ارجاع سلولی جدید باید بر روی دکمه با علامت تیک سبز رنگ کلیک کنید. همچنین برای لغو تغییرات و بازگشت به حالت قبل می‌توانید بر روی دکمه ضربدر (×) کلیک کنید. برای بستن کادر محاوره‌ای Name Manager بر روی Close کلیک کنید.

مشاهده همه نام‌های تعریف شده در یک فایل اکسل

کادر محاوره‌ای Name Manager همه نام‌های در سطح کاربرگ و فایل را که تعریف کرده‌اید، صرف‌نظر از این که در حال حاضر در کدام کاربرگ قرار دارید، نمایش می‌دهد،. اما زمانی که این کادر محاوره‌ای باز است نمی‌توانید بر روی کاربرگ فعالیت کنید.

داشتن فهرستی از نام‌های تعریف شده به طور مستقیم در کاربرگ گزینه مفیدی است. بدین ترتیب می‌توان به بخش‌های مختلف کاربرگ که در آنجا نام‌ها نیازمند ویرایش هستند مراجعه کرد و همچنین نام‌هایی که باید حذف شوند را مشخص نمود.

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

برای ایجاد چنین فهرستی از نام‌ها باید کارهای زیر را انجام دهید:

1. یک بخش خالی بر روی کاربرگ جاری بیابید که دو ستون وجود داشته باشد و سپس سلولی که گوشه سمت چپ-بالای فهرست خواهد بود را انتخاب کنید.
2. در نوار Formula در بخش Defined Names بر روی Use in Formula کلیک کرده و گزینه Paste Names را انتخاب کنید. همچنین می‌توانید از کلید میانبر F3 استفاده کنید.

در کادر محاوره‌ای Paste Names بر روی Paste List کلیک کنید.

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

اینک می‌توانید فهرست را بررسی کنید و هر کاری که دوست دارید را با نام‌های تعریف شده انجام دهید. زمانی که دیگر نیاز به این فهرست نداشتید، می‌توانید آن را از کاربرگ حذف کنید.

حذف یک محدوده دارای نام در اکسل

اگر نام‌هایی دارید که دیگر نیازی به آن‌ها ندارید، بهتر است آن‌ها را حذف کنید و در غیر این صورت فهرست نام‌ها شلوغ و مدیریت آن دشوارتر می‌شود.

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables + گواهینامه در فرادرس

کلیک کنید

برای باز کردن Name Manager، در نوار Formula و در بخش Defined Names بر روی Name Manager کلیک کنید.

در کادر محاوره‌ای Name Manager نامی که می‌خواهید حذف کنید را انتخاب کرده و بر روی Delete کلیک کنید. در کادر محاوره‌ای تأیید، بر روی Ok کلیک نمایید.

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

اگر یک نام به محدوده‌ای از سلول‌ها ارجاع داشته باشد، می‌توانید از آن برای حرکت سریع به آن محدوده و انتخاب همه آن سلول‌ها استفاده کنید. برای پرش به یک محدوده دارای نام بر روی فلش رو به پایین کادر Name در سمت چپ نوار Formula کلیک کرده و نامی که می‌خواهید را انتخاب کنید.

نام‌های در سطح کاربرگ تنها در صورتی در کادر بازشونده Name نمایش می‌یابند که در کاربرگ جاری تعریف شده باشند. همچنین می‌توانید نام یک محدوده از سلول‌ها که قصد حرکت به آنجا دارید را وارد کرده و اینتر را بزنید. اما ابتدا مطمئن شوید که قبلاً این نام را تعریف کرده باشید. اگر چنین نباشد در واقع نامی که وارد کرده‌اید بر روی سلول‌های انتخاب شده کنونی اعمال خواهد شد.

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

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

چند روش برای وارد کردن یک نام در فرمول وجود دارد. اگر دقیقاً می‌دانید که از چه نامی می‌خواهید در فرمول استفاده کنید می‌توانید آن را مستقیماً در فرمول وارد نمایید.

همچنین می‌توانید از امکان تکمیل سازی خودکار فرمول استفاده کنید. به محض این که فرمولی را تایپ می‌کنید، اکسل فهرستی از نام‌های معتبر را ارائه می‌کند و می‌توانید از میان آن‌ها نام مورد نظر خود را انتخاب کرده و اینتر را بزنید تا وارد فرمول شود. برای مثال در برگه زیر به دلیل انتخاب محدوده سلول‌هایی که نام Total داشتند چنین نامی در فرمول پیشنهاد شده است.

روش دیگر این است که شروع به نوشتن فرمول بکنید و سپس بر روی Use in Formula در بخش Defined Names در برگه Formula کلیک کنید. در این زمان نام مورد نظر خود را برای استفاده در فرمول از فهرست بازشونده انتخاب نمایید.

روش آخر برای استفاده از نام در فرمول این است که شروع به نوشتن فرمول بکنید و سپس F3 را بزنید. در این زمان نامی که می‌خواهید استفاده کنید را در کادر محاوره‌ای Paste Names انتخاب کرده و بر روی OK کلیک کرده‌اید اینتر را بزنید.

نام‌ها باعث درک بهتر کاربرگ‌های اکسل می‌شوند

نام‌های تعریف شده در اکسل به منظم‌تر شدن و درک آسان‌تر کاربرگ‌ها کمک می‌کنند. از نام‌ها نه تنها در محدوده‌های سلولی؛ بلکه برای اشاره به مقادیر و فرمول‌های مختلف نیز می‌توان استفاده کرد.

اگر این نوشته مورد توجه‌تان واقع شده است، موارد زیر نیز احتمالاً برای شما مفید خواهند بود:

• آموزش‌های مجموعه نرم‌افزاری آفیس
• ۱۶ فرمول اکسل که به حل مشکلات روزمره کمک می‌کنند
• آموزش نرم‌افزار مایکروسافت اکسل
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• مجموعه آموزش‌های علوم کامپیوتر

==