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

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

برای نام گذاری سلول‌‌ها و محدوده‌ها در اکسل، دو روش وجود دارد:

نام گذاری محدوده در اکسل با استفاده از Name Box

اگر بر روی یک سلول کلیک کنید در گوشه سمت چپ Formula bar  نام آن دیده می‌شود. این قسمت که Name Bax نام دارد به طور پیش فرض آدرس هر سلول یا تعداد سطر و ستون انتخابی یک محدوده را نشان می‌دهد.

ساده ترین روش نام گذاری محدوده در اکسل با استفاده از این ابزار است. برای این منظور ابتدا محدوده یا سلول مورد نظر را انتخاب کرده و سپس به قسمت Name Bax بروید، در این قسمت نامی که می‌خواهید اختصاص بدهید را نوشته و کلید Enter را بزنید.

برای نام گذاری در این روش همواره باید ابتدا محدوه و یا سلول مورد نظر را انتخاب کرده و سپس به قسمت نام گذاری بروید.

نام گذاری محدوده در اکسل با استفاده از  Define Name

به منوی FORMULAS  رفته و در زیر منوی Defined Names در بخش Define Name کلیک نمایید. قسمتهای مختلف این بخش را مطابق شکل تکمیل نمایید. در این روش نام گذاری، می‌توان از دو ترتیب کاری استفاده کرد:

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

در قسمت Scope از شکل بالا دو انتخاب پیش روی شماست، این قسمت به صورت پیش فرض بر روی Workbook تنظیم شده و اگر این گزینه انتخاب شود، نامی که به محدوده خاص اختصاص می‌دهید در تمامی برگه های این فایل (Workbook) قابل استفاده خواهد بود. گزینه دیگری که می‌توانید انتخاب کنید عنوان برگه‌ها است. بدیهی است که با انتخاب عنوان هر یک از برگه های موجود در فایلتان، نام اختصاصی برای محدوده، فقط در برگه انتخاب شده قابل استفاده خواهد بود. در بخش Comment می‌توانید توضیحی برای محدوده و نامش بنویسید که هم راهنمای خودتان در مراجعات بعدی خواهد بود و هم به دیگر استفاده کنندگان فایل شما، کمک می‌کند. علامت‌های دلار در کادر مربوط به Refers to، نشان دهنده مطلق بودن آدرس دهی در اکسل برای نام گذاری محدوده ها است.

قسمت‌های مختلف زیر منوی Defined Names

• Use in Formula

اگر در بخش Use in Formula در زیر منوی Defined Names کلیک نمایید، نام های اختصاص داده شده و گزینه‌‌ای با عنوان Paste Names دیده می‌شود. با کلیک بر روی هر کدام از نام های اختصاص داده شده این نام به فرمول شما اضافه خواهد شد، این قسمت زمانی مفید خواهد بود که شما تعداد زیادی نام تعریف شده دارید و املای دقیق نام مورد نظر را فراموش کرده‌اید. اکسل به شما کمک خواهد کرد تا سریعاً محدوده مورد نظر را فراخوانی نمایید. قسمت Paste Names، تمامی نام های اختصاص داده شده را به صورت ستونی، برای شما الحاق خواهد کرد.

• Create from Selection

شما می‌توانید جدولی مانند آنچه در شکل می‌بینید تنظیم کرده و با استفاده از گزینه‌های موجود در بخش Create from Selection در زیر منوی Defined Names،  از بخش‌های مختلف جدولتان برای نام گذاری محدوه‌هایتان استفاده کنید. برای اینکار لازم است تمامی جدولتان (که شامل عنوان ستون‌ها نیز است) را انتخاب کنید. اگر تیک کنار Top Row را بزنید، اکسل از عنوان های موجود در سطر بالایی جدولتان برای نام گذاری هر یک از ستون‌ها استفاده می‌کند. در ادامه نتیجه‌ای به مانند شکل زیر خواهید داشت. با زدن تیک دیگر گزینه‌ها، اکسل از دیگر بخشهای جدولتان برای نام گذاری محدوده استفاده خواهد کرد.

• Name Manager

در بخش Name Manager می‌توانید از قسمت New نام جدیدی ایجاد کنید. از قسمت Edit نام های موجود را انتخاب و ویرایش کنید. از قسمت Delete نامی را حذف کنید. اگر تعداد نام‌هایی که ایجاد کرده‌اید زیاد است و قصد انجام عملیات جدیدی بر روی این نام ها را دارید قسمت فیلتر شما را در این امر یاری خواهد کرد. با استفاده از گزینه‌های مختلف فیلتر کردن نام مورد نظر خود را پیدا کنید.

نام گذاری محدوده در اکسل و استفاده در ایجاد ضریب ثابت نامدار  Named Constant

علاوه بر نام گذاری محدوده در اکسل ضرایب ثابت هم می‌توانند دارای نام باشند و در فرمول‌ها استفاده شوند. به منظور ایجاد یک ضریب ثابت نامدار، مطابق شکل زیر در بخش Define Name از زیر منوی Defined Names یا بخش New از Name Manager اقدام به نام گذاری جدید نمایید. پس از نوشتن نام دلخواه در کادر مربوطه، در قسمت Refers to مقدار ضریب ثابت را وارد نمایید.

برای مثال در محاسبه مقدار کل مصرف سیمان در کارهای ساختمانی، علاوه بر آنچه که مستقیماً در اعضا و بخشهای سازه‌ای (فونداسیون، ستون، تیر، سقف و …) و غیر سازه‌ای (آجرچینی، کاشی و سرامیک، کف‌سازی و …) استفاده می‌شود، باید مقدار سیمانی را که به هدر می‌رود نیز، در نظر گرفت. در جدول زیر مقدار مصرف سیمان در بخش‌های سازه‌ای، با استفاده از احجام کاری و عیار سیمان مصرفی در کارهای ساختمانی محاسبه شده است. در این جدول، با بررسی مراجع (فهرست بهاء) مقدار ۶% برای هدر رفت (پرت) سیمان در نظر گرفته شده است. پرت سیمان به عنوان ضریب ثابت نامدار، در جمع کل مقدار سیمان مصرفی هر ماه ضرب شده و در ستون مربوطه نوشته شده است. مقدار پرت شده سیمان بعلاوه  آنچه که در بخش‌های سازه‌ای استفاده شده است، مقدار کل سیمان مصرفی است.

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

اعداد نمی‌توانند در نام گذاری محدوده در اکسل استفاده شوند. در جایی که نیاز به استفاده از نام های دو یا چند سیلابی دارید، از “_” (آندرلاین)، نقطه و نیم فاصله برای جداسازی کلمات استفاده کنید. چرا که در نام گذاری محدوده ها از فاصله نیز نمی‌توان استفاده کرد. در کل یک نام می‌تواند تا ۲۵۵ کاراکتر طول داشته باشد.

کاربرد ثانویه نام گذاری محدوده در اکسل

پس از نام گذاری محدوده در اکسل، از نام هایی که تعریف کرده‌اید، می‌توانید برای لینک سازی نیز استفاده کنید. برای این منظور ابتدا سلولی که می‌خواهید به عنوان لینک عمل کند را انتخاب کنید. متنی که می‌خواهید به عنوان لینک باشد را در این سلول بنویسید. (برای متنی که می‌خواهید لینک شود از قسمت Text to display پنجره باز شده نیز می‌توانید استفاده کنید) از منوی INSERT زیر منوی Links را انتخاب کنید. چون نام گذاری انجام شده در فایل حاضر قرار دارد، در پنجره باز شده ابتدا از ستون سمت چپ گزینه Place in This Document را انتخاب کنید و سپس روی نام محدوده مورد نظر کلیک کرده و در پایان عملیات را OK کنید!

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