چگونه در اکسل بازه های پویا و قابل تغییر برای سلول ها ایجاد کنیم؟

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

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

ایجاد بازه های پویا در اکسل

برای مثال اول در این مطلب یک جدول از داده‌ها داریم که دارای تنها یک ستون است و می‌توانید آن را در تصویر زیر مشاهده کنید.

می‌خواهیم این لیست پویا باشد چرا که ممکن است بخواهیم کشور‌های دیگری را به این سلول اضافه کنیم یا برخی از کشور‌ها را حذف نماییم. به همین دلیل باید بازه مورد توجه در این ستون به صورت پویا باشد. در این مثال، می‌خواهیم از سلول‌های Header یا تیتر‌ها اجتناب کنیم. می‌خواهیم بازه A2 : A6 را در نظر بگیریم به این شرط که بازه یاد شده پویا باشد. برای این کار وارد Formula و سپس Define Name خواهیم شد.

در فیلد Name عبارت countries را تایپ کنید و سپس فرمول زیر را در بخش refers to وارد نمایید.

برای افزایش سرعت می‌توانید ابتدا فرمول بالا را در یکی از سلول‌های اسپریدشیت خود وارد کرده و سپس آن را در Refers to  کپی نمایید.

چگونه کار می‌کند؟

بخش اول فرمول یاد شده مشخص می‌کند که شروع سلول‌ها در بازه های پویا کجا خواهد بود (در مثال ما A2) و سپس اوپراتور بازه یعنی : در ادامه آن قرار خواهد گرفت. استفاده از این اوپراتور باعث می‌شود که تابع INDEX به جای مقدار درون یک سلول، یک بازه را برگرداند. تابع INDEX سپس با استفاده از COUNTA تعداد سلول‌های غیر خالی در ستون A را شمارش می‌کند (در مثال ما شش).

این فرمول از تابع INDEX می‌خواهد که آدرس دقیق آخرین سلول غیر خالی در ستون A را اعلام کند (A6). نتیجه نهایی A2 : A6 است و به خاطر استفاده از COUNTA، بازه پویا خواهد بود. حالا این امکان را دارید که از نام مشخص شده Countries در جداول، فرمول‌ها و هرجای دیگری که نیاز به مرجع گذاری دارید استفاده کنید.

ایجاد یک بازه پویای دو طرفه

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

در مثال زیر می‌خواهیم جدول زیر را تبدیل به یک بازه پویای دو طرفه کنیم.

این بار باید تیتر‌ها یا Header را هم در نظر بگیریم. پس از انتخاب سلول‌های مد نظر خود، به مسیر Formulas و سپس Define Name بروید.

در بخش Name عبارت sales را وارد کنید و در بخش Refers To فرمول زیر را بنویسید:

در این فرمول از سلول A1 برای شروع استفاده خواهیم کرد. تابع INDEX پس از این از بازه کلی ورک‌شیت استفاده خواهد کرد (یعنی از 1 تا 10485786). یکی از توابع COUNTA برای شمارش سلول‌های غیر خالی در ردیف‌ها مورد استفاده قرار گرفته و دیگری کار مشابه را برای ستون‌ها انجام خواهد داد. اگرچه این فرمول از A1 شروع می‌شود، شما می‌توانید شروع آن را هر کجا از پرونده اکسل خود که می‌خواهید بگیرید.

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