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

داده‌های اکسل شما به صورت مداوم تغییر می‌کند و بد نیست با ایجاد بازه های پویا در آن شرایطی ایجاد کنید که بنا بر نیاز اندازه مد نظری که می‌خواهید را رعایت رعایت کنند. در این مطلب قصد داریم به توضیح چگونگی این کار با استفاده از مایکروسافت اکسل بپردازیم. با استفاده از بازه‌های پویا در اکسل، نیازی نخواهید داشت که به طور دستی بازه‌ی ستون‌هایی که دارای فرمول، جدول یا 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 در بخش‌های مختلف اکسل که نیاز به مرجع گذاری دارند استفاده کنید. این بازه حالا به صورت دو طرفه پویا خواهد بود.

مطالب مرتبط

با بهترین سایت‌های تشخیص فونت آشنا شوید

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

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

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

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

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

قابلیت‌های اختیاری ویندوز ۱۰ چیست و چگونه آن‌ها را فعال کنیم؟

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

چگونه در اکسل نام سری داده را تغییر دهیم؟

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

با کاربردی‌ترین کلیدهای میانبر ویرایش متن در مک او اس آشنا شوید

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

ویجیاتو

نظرات ۰

وارد شوید

برای گفتگو با کاربران، وارد حساب کاربری خود شوید.

ورود

رمزتان را گم کرده‌اید؟