صفحات گسترده اکسل اغلب شامل کشویی سلول برای ساده سازی و / یا استاندارد کردن ورود اطلاعات است. این کرکره ها با استفاده از ویژگی اعتبارسنجی داده ها برای مشخص کردن لیستی از ورودی های مجاز ایجاد می شوند.
برای تنظیم لیست کشویی ساده ، سلولی را انتخاب کنید که داده ها در آن وارد می شوند ، سپس روی اعتبار سنجی داده(در برگه داده) ، اعتبارسنجی داده را انتخاب کنید ، لیسترا انتخاب کنید (در قسمت مجاز :) ، و سپس موارد <لیست شده (جدا شده با کاما) را در وارد کنید. >منبع: قسمت (شکل 1 را ببینید).
در این نوع بازشوی اصلی ، لیست ورودی های مجاز مشخص شده است در خود اعتبار داده ها. بنابراین ، برای ایجاد تغییرات در لیست ، کاربر باید اعتبار سنجی داده را باز و ویرایش کند. این ممکن است برای کاربرهای بی تجربه یا در مواردی که لیست انتخابها طولانی باشد دشوار باشد.
گزینه دیگر این است که لیست را در نام محدوده در صفحه گسترده قرار دهید و سپس مشخص کنید این نام دامنه (با یک علامت مساوی) در قسمت منبع: اعتبار سنجی داده ها (مطابق شکل 2) نشان داده شده است.
این روش دوم ویرایش گزینه های موجود در لیست را آسان تر می کند ، اما اضافه کردن یا حذف موارد می تواند مشکل ساز باشد. از آنجا که دامنه نامگذاری شده (در مثال ما FruitChoices) به محدوده ثابت سلول (H $ 3 $: $ H 10 $ همانطور که نشان داده شده است) اشاره می کند ، اگر انتخاب های بیشتری به سلول های H11 یا زیر اضافه شود ، در کشویی ظاهر نمی شوند. (از آنجا که آن سلول ها جزئی از محدوده FruitChoices نیستند.). به همین ترتیب اگر به عنوان مثال ، ورودی های Pears and Strawberry پاک شوند ، دیگر در کشویی ظاهر نمی شوند ، اما در عوض ، بازشو شامل دو گزینه های "خالی" از زمان بازشونده هنوز به کل محدوده FruitChoices ، از جمله سلول های خالی H9 و H10 اشاره می کند.
به همین دلایل ، هنگام استفاده از یک محدوده معمولی با نام محدود به عنوان منبع لیست برای کشویی ، محدوده نامگذاری شده را نشان می دهد. در صورت اضافه شدن یا حذف شدن از لیست ، تعداد سلولهای کمتری را باید ویرایش کرد.
راه حلی برای این مشکل استفاده از پویااست >نام دامنه به عنوان منبع انتخاب های کشویی. نام دامنه پویا نامی است که بطور خودکار (یا قراردادها) گسترش می یابد تا دقیقاً با اندازه یک بلوک از داده ها مطابقت داشته باشد زیرا ورودی ها اضافه می شوند یا حذف می شوند. برای این کار ، به جای محدوده مشخصی از آدرسهای سلولی ، ازformulaبرای تعریف دامنه مشخص استفاده می کنید.
نحوه راه اندازی پویا Range in Excel
یک نام محدوده معمولی (ایستا) به طیف مشخصی از سلول ها اشاره دارد ($ H $ 3: $ H $ 10 در مثال ما ، در زیر مشاهده کنید):
اما یک محدوده پویا با استفاده از یک فرمول تعریف شده است (نگاه کنید به زیر ، از یک صفحه گسترده جداگانه که از نامهای دامنه پویا استفاده می کند ، مشاهده شود):
4s>
قبل از شروع کار ، حتماً فایل نمونه اکسل (مرتب کردن ماکروها غیرفعال شده است) را بارگیری کنید.
بیایید این فرمول را با جزئیات بررسی کنیم. گزینه های میوه ها در یک مجموعه سلول مستقیماً زیر عنوان قرار دارند (میوه ها). به آن عنوان نیز یک اسم اختصاص داده شده است: FruitsHeading:
کل فرمول مورد استفاده برای تعریف دامنه پویا برای انتخاب میوه ها عبارتند از:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingبه عنوان مربوط به عنوانی است که یک ردیف بالاتر از اولین ورودی لیست است. عدد 20 (در فرمول دو بار استفاده شده است) حداکثر اندازه (تعداد ردیف) برای لیست است (می توان آن را مطابق دلخواه تنظیم کرد).
توجه داشته باشید که در این مثال ، فقط 8 مدخل وجود دارد در لیست وجود دارد ، اما سلولهای خالی نیز در زیر این موارد وجود دارند که می توانند ورودی های اضافی اضافه شوند. عدد 20 به کل بلوکی که می توان در آن نوشته شد اشاره دارد نه به تعداد واقعی ورودی.
حالا بیایید فرمول را به صورت قطعات (کدگذاری رنگ هر قطعه) تقسیم کنیم ، تا بدانیم که چگونه کار می کند :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
قطعه "پایین ترین" OFFSET (FruitsHeading ، 1،0،20،1)است. این گروه شامل 20 سلول (در زیر سلول FruitsHeading) است که در آن می توانید انتخاب کنید. این عملکرد OffSET اساساً می گوید: از سلول FruitsHeadingشروع کنید ، به ترتیب 1 ردیف و بیش از 0 ستون پایین بیایید ، سپس ناحیه ای را انتخاب کنید که دارای طول 20 ردیف و عرض 1 ستون باشد. بدین ترتیب بلوک 20 سطحی را که انتخاب میوه ها در آن وارد شده به ما می دهد.
قطعه بعدی فرمول تابع ISBLANKاست:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
در اینجا ، عملکرد OFFSET (توضیح فوق) با "موارد فوق" جایگزین شده است (برای آسان تر خواندن چیزها). اما عملکرد ISBLANK بر روی سلولهای ردیف 20 ردیفی که عملکرد OFFSET تعریف می کند ، کار می کند. محدوده ردیف ارجاع شده توسط عملکرد OFFSET خالی است (خالی) یا خیر. در این مثال ، 8 مقادیر اول در مجموعه FALSE خواهند بود زیرا 8 سلول اول خالی نیستند و 12 مقدار آخر TRUE خواهند بود.
قطعه بعدی فرمول عملکرد INDEX است:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
باز هم ، "موارد فوق" به عملکردهای ISBLANK و OFFSET که در بالا توضیح داده شده است ، اشاره دارد. عملکرد INDEX آرایه ای را شامل می شود که حاوی 20 مقدار TRUE / FALSE ایجاد شده توسط عملکرد ISBLANK است. یک بلوک از داده ها ، با مشخص کردن یک ردیف و ستون خاص (در آن بلوک). اما تنظیم ورودی های سطر و ستون به صفر (همانطور که در اینجا انجام می شود) باعث می شود INDEX یک آرایه حاوی کل بلوک داده ها را برگرداند.
قطعه بعدی فرمول تابع MATCH است:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
تابع MATCHموقعیت اولین مقدار TRUE را ، در آرایه ای که توسط عملکرد INDEX برگردانده شده ، برمی گرداند. از آنجا که 8 ورودی اول در لیست خالی نیست ، 8 مقدار اول در آرایه FALSE و مقدار نهم TRUE خواهد بود (از آنجا که ردیف 9 thدر دامنه خالی است).
بنابراین تابع MATCH مقدار 9را برمی گرداند. در این حالت ، با این حال ، ما واقعاً می خواهیم بدانیم که تعداد ورودی ها در لیست وجود دارد ، بنابراین فرمول 1 را از مقدار MATCH (که موقعیت آخرین ورودی را دارد) کم می کند. بنابراین در نهایت ، MATCH (TRUE ، موارد فوق ، 0) -1 مقدار8/8 را برمی گرداند.
قطعه بعدی فرمول عملکرد IFERROR است:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
اگر اولین مقدار مشخص شده منجر به خطا شود ، عملکرد IFERROR یک مقدار متناوب را برمی گرداند. این عملکرد شامل می شود زیرا ، اگر کل بخش سلول (هر 20 سطر) با ورودی پر شود ، عملکرد MATCH خطایی را برمی گرداند.
این به این دلیل است که ما به عملکرد MATCH می گوییم که جستجو کنیم. مقدار اول TRUE (در آرایه ای از مقادیر ISBLANK) ، اما اگر هیچ یک از سلول ها خالی نباشد ، کل آرایه با مقادیر FALSE پر خواهد شد. اگر MATCH نتواند مقدار هدف (TRUE) را در آرایه جستجو کند ، خطایی را برمی گرداند.
بنابراین ، اگر کل لیست کامل باشد (و بنابراین ، MATCH خطایی را برمی گرداند) ، عملکرد IFERROR خواهد شد. در عوض ، مقدار 20 را برگردانید (دانستن اینکه باید 20 ورودی در لیست وجود داشته باشد).
سرانجام ، OFFSET (FruitsHeading ، 1،0 ، موارد فوق ، 1)دامنه مورد نظر ما به دنبال این هستیم: از سلول FruitsHeading شروع کنید ، 1 ردیف و بیش از 0 ستون را پایین بیاورید ، سپس منطقه ای را انتخاب کنید که با این وجود تعداد زیادی ردیف داشته باشد ، مگر اینکه در لیست (و 1 ستون گسترده) وجود داشته باشد. بنابراین کل فرمول با هم دامنه ای را شامل می شود که فقط شامل ورودی های واقعی (پایین به اولین سلول خالی) باشد.
استفاده از این فرمول برای تعریف دامنه که منبع بازشو است ، به این معنی است که می توانید آزادانه ویرایش کنید لیست (افزودن یا حذف ورودی ها ، مادامی که ورودی های باقی مانده در سلول بالا شروع شده و به هم پیوسته باشند) و بازشونده همیشه لیست فعلی را منعکس می کند (به شکل 6 مراجعه کنید).
پرونده به عنوان مثال (لیست های پویا) که در اینجا استفاده شده است از این وب سایت قابل بارگیری است. با این حال ماکروها کار نمی کنند ، زیرا WordPress کتابهای اکسل را که دارای ماکرو هستند در آنها دوست ندارد.
به عنوان یک گزینه جایگزین برای تعیین تعداد ردیف های موجود در لیست ، بلوک لیست را می توان اختصاص داد نام دامنه خاص خود را ، که می تواند در فرمول اصلاح شده استفاده شود. در پرونده مثال ، یک لیست دوم (نام) از این روش استفاده می کند. در اینجا ، به کل فهرست لیست (در زیر عنوان "NAMES" ، 40 ردیف در پرونده مثال) نام محدوده NameBlockاختصاص داده شده است. فرمول جایگزین برای تعریف NamesList عبارت است از:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
که NamesBlockجایگزین OFFSET می شود (FruitsHeading ، 1،0،20،1) و <قوی >ROWS (NamesBlock)20 (تعداد ردیف ها) را در فرمول قبلی جایگزین می کند.
بنابراین ، برای لیست های کشویی که به راحتی ویرایش می شوند (از جمله توسط کاربران دیگری که ممکن است بی تجربه باشند) ، سعی کنید از نام های محدوده پویا استفاده کنید! و توجه داشته باشید که اگرچه این مقاله به لیست های بازشونده متمرکز شده است ، اما در هر مکانی که بخواهید می توانید یک محدوده یا لیستی را که می تواند در اندازه متفاوت باشد ، از نام دامنه پویا استفاده می کنید. لذت ببرید!