داده‌هاي اكسل شما به صورت مداوم تغيير مي‌كند و بد نيست با ايجاد بازه هاي پويا در آن شرايطي ايجاد كنيد كه بنا بر نياز اندازه مد نظري كه مي‌خواهيد را رعايت رعايت كنند. در اين مطلب قصد داريم به توضيح چگونگي اين كار با استفاده از مايكروسافت اكسل بپردازيم. با استفاده از بازه‌هاي پويا در اكسل، نيازي نخواهيد داشت كه به طور دستي بازه‌ي ستون‌هايي كه داراي فرمول، جدول يا 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 در بخش‌هاي مختلف اكسل كه نياز به مرجع گذاري دارند استفاده كنيد. اين بازه حالا به صورت دو طرفه پويا خواهد بود.
منبع:
- چهارشنبه ۳۰ بهمن ۹۸ ۱۰:۲۲
- ۷۸ بازديد
- ۰ نظر