داده‌های اکسل شما به‌صورت مرتب تغییر می‌کنند. بنابراین اگر بتوانید یک محدوده تعریف‌شده پویا را که بسته به اطلاعات شما منبسط و منقبض می‌شود، ایجاد کنید، فعالیت کاری شما روند بهتری را در پیش خواهد گرفت. در ادامه شیوه ایجاد یک محدوده تعریف‌شده پویا در اکسل را به شما آموزش می‌دهیم.
با استفاده از محدوده تعریف‌شده پویا، دیگر شما در هنگام تغییر اطلاعات نیاز نخواهید داشت که به‌صورت دستی محدوده فرمول‌ها، نمودارها و جداول محوری خود را ویرایش کنید. این کار به‌صورت خودکار انجام خواهد شد. از دو فرمول جهت ایجاد محدوده‌های پویا استفاده می‌شود: OFFSET و INDEX. ازآنجایی‌که INDEX یک روش بهتر است، این مقاله نیز بر روی استفاده از آن متمرکز خواهد بود. OFFSET یک عملکرد فرار بوده و می‌تواند سرعت صفحات بزرگ را با کاهش مواجه کند.
ایجاد یک محدوده تعریف‌شده پویا در اکسل
در اولین مثال، داده‌هایی تک-ستونی به شرح زیر را در اختیار داریم.
ما می‌خواهیم این ستون تبدیل به ستونی پویا شود، به‌طوری‌که اگر کشورهایی را اضافه و یا کم کردیم، این محدوده نیز به‌صورت خودکار به‌روزرسانی شود.
در این مثال، از سلول سر تیتر صرف‌نظر می‌کنیم. ما می‌خواهیم که محدوده 6$A$2:$A$ را تبدیل به محدوده‌ای پویا کنیم. این کار با کلیک بر روی Formulas > Define Name انجام می‌شود.
کلمه «countries» را در قسمت «Name» نوشته و سپس باید فرمول زیر را در قسمت «Refers to» وارد کنید.
گاهی اوقات، نوشتن این معادله در یک سلول و سپس کپی کردن آن در قسمت «New Name» آسان‌تر و سریع‌تر است.
این روش چگونه کار می‌کند؟
اولین بخش این فرمول، سلول شروع را (در این مثال: A2) مشخص کرده و سپس عامل محدوده (:) نیز به دنبال آن می‌آید.
استفاده از عامل محدوده باعث می‌شود تا عملکرد INDEX به‌جای مقدار یک سلول، به یک محدوده مراجعه کند. سپس عملکرد INDEX به همراه عملکرد COUNTA استفاده می‌شود. COUNTA تعداد سلول‌های غیرخالی در ستون A را (در مثال ما، 6 عدد) محاسبه می‌کند.
این فرمول از عملکرد INDEX می‌خواهد که به محدوده آخرین سلول غیرخالی در ستون 6$A$ مراجعه کند.
نتیجه نهایی 6$A$2:$A$ بوده و به دلیل عملکرد COUNTA و ازآنجایی‌که آخرین ردیف در نظر گرفته می‌شود، این محدوده یک محدوده پویا است. حال شما می‌توانید از محدوده «countries» در داخل یک Data Validation، نمودار و یا هرجای دیگری که در آن لازم باشد نام تمامی کشورها ارجاع داده شود، استفاده کنید.
ایجاد یک محدوده تعریف‌شده پویای دوراهی
مثال پیشین فقط در عرض پویا بود. به‌هرحال با تغییری کوچک و یک عملکرد COUNTA دیگر شما می‌توانید محدوده‌ای را ایجاد کنید که در آن هم طول و هم عرض پویا هستند.
در این مثال، ما از داده‌های زیر استفاده می‌کنیم.
این بار یک محدوده تعریف‌شده پویا را ایجاد خواهیم کرد که سرتیتر را نیز در بر می‌گیرد. بر روی Formulas > Define Name کلیک کنید.
کلمه «sales» را در قسمت «Name» نوشته و سپس فرمول زیر را در قسمت «Refers To» وارد کنید.
این فرمول از 1$A$ به‌عنوان سلول آغازین استفاده می‌کند. سپس عملکرد INDEX از محدوده تمامی صفحه (1048576$:1$) جهت جست‌و‌جو و ارجاع استفاده می‌کند.
استفاده از دو عملکرد COUNTA، یکی جهت شمارش ردیف‌های غیرخالی و دیگری جهت شمارش ستون‌های غیرخالی باعث می‌شود تا این عملکرد در هر دو جهت (عمودی و افقی) عملکردی پویا داشته باشد. اگرچه که فرمول مثال ما از سلول A1 شروع شد، اما شما می‌توانید سلول موردنظر خود را به‌عنوان سلول آغازین در نظر بگیرید.
اکنون شما می‌توانید که از این اسم‌های تعریف‌شده (sales) در فرمول‌های خود و یا مجموعه داده‌های نموداری استفاده کرده و آن‌ها را پویاسازی کنید.
