تئو از یک کاربرگ اکسل برای پیگیری رزروهای شرکت خود استفاده می کند. داده ها فقط از سه ستون تشکیل شده است. اولی نام افراد، دومی شماره هفته اول (1-52) رزرو و سومی شماره هفته آخر رزرو است. افراد می توانند برای چندین هفته رزرو شوند (یعنی هفته شروع 15 و هفته پایانی 19 است). تئو به راهی برای وارد کردن یک عدد هفته نیاز دارد و سپس فرمولی برای تعیین نام (ستون A) با آن شماره هفته دارد. داده ها به ترتیب خاصی مرتب نشده اند و شرکت اجازه نمی دهد که تئو از یک ماکرو برای به دست آوردن نتیجه استفاده کند (این باید یک فرمول باشد).
موقعیت Theos به اندازه کافی ساده به نظر می رسد، اما هنگام ابداع راه حل، پر از مشکلات است. نگاه کردن به داده های بالقوه (همانطور که در شکل زیر نشان داده شده است) به سرعت دلیل این موضوع را نشان می دهد. (شکل 1 را ببینید.)
شکل 1. داده های بالقوه برای مسئله Theos.
توجه داشته باشید که داده ها (همانطور که تئو گفت) ترتیب خاصی ندارند. همچنین توجه داشته باشید که برخی هفته‌ها هستند که هیچ رزروی وجود ندارد (مانند هفته 5 یا 6)، هفته‌هایی که چندین نفر وجود دارند (مانند هفته 11 یا 16)، و هفته‌هایی هستند که فردی رزرو شده است، اما هفته عدد در ستون B یا C نمایش داده نمی شود (مانند هفته 12 یا 17).
قبل از شروع به بررسی راه حل های بالقوه، اجازه دهید فرض کنیم هفته ای که می خواهید در مورد آن بدانید سلول E1 است. شما باید این محدوده را Query نامگذاری کنید. بعلاوه، محدوده ای که شامل نام افراد است (در این مثال، سلول های A2:A10) را به عنوان ResNames، هفته های شروع (B2:B10) را به عنوان StartWeeks و هفته های پایانی (C2:C10) را به عنوان EndWeeks نام ببرید. در نهایت یک نام برای کل جدول (A2:C10) مانند MyData تعریف کنید. این نامگذاری، اگرچه به شدت ضروری نیست، درک فرمول ها را بسیار آسان تر می کند.
یک راه حل بالقوه اضافه کردن چیزی است که معمولاً به عنوان "ستون کمکی" نامیده می شود. موارد زیر را به سلول D2 اضافه کنید:
=IF(AND(Query>=B2,Query
فرمول را برای تعداد سلول هایی که در جدول وجود دارد کپی کنید. (به عنوان مثال، آن را از طریق سلول D10 کپی کنید.) هنگامی که یک شماره هفته را در سلول E1 قرار می دهید، کلمه "RESERVED" در سمت راست هر رزروی که شامل آن شماره هفته می شود ظاهر می شود. همچنین به راحتی می توان فهمید که آیا چندین نفر برای آن هفته رزرو شده اند یا اینکه هیچ فردی برای آن هفته رزرو نشده است. شما حتی می توانید یک AutoFilter اعمال کنید و انتخاب کنید که فقط آن رکوردها با کلمه "Reserved" در ستون D نشان داده شود.
در صورت تمایل می توانید از ستون کمکی صرف نظر کنید و از قالب بندی شرطی برای نمایش افرادی که برای یک هفته مورد نظر رزرو شده اند استفاده کنید. به سادگی نام های ستون A را انتخاب کنید و یک قانون قالب بندی شرطی اضافه کنید که از فرمول زیر استفاده می کند:
=AND(Query>=B2,Query
(نحوه وارد کردن قوانین قالب بندی شرطی به طور گسترده در شماره های دیگر ExcelTips توضیح داده شده است . ) قانون را به گونه ای تنظیم کنید که سایه (الگوی) اعمال شده در سلول را تغییر دهد و به راحتی می توانید ببینید که کدام رزرو برای هفته ای که هستید اعمال می شود. علاقه مند به
روش دیگر استفاده از فرمول آرایه است. چند سلول بیشتر از تعداد رزروهای همپوشانی که انتظار دارید انتخاب کنید و سپس با فشار دادن Ctrl+Shift+Enter موارد زیر را در آن سلول ها وارد کنید :
=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)*(EndWeeks>=Query)*(ROW(ResNames)),ROW()-1)-1),"")
هنگام انتخاب تعداد سلول‌ها، می‌خواهید این فرمول آرایه را اشغال کند، به عنوان مثال، به تعداد افرادی که ممکن است در هفته 11 رزرو شوند نگاه کنید. در مثال نشان داده شده در این نکته، 2 نفر است. بیش از این تعداد سلول را انتخاب کنید و سپس فرمول آرایه را در آن سلول ها قرار دهید. اگر انتظار دارید که احتمالاً 20 نفر برای یک هفته رزرو شده باشند، باید تعداد بیشتری سلول مانند 20 یا 30 سلول را انتخاب کنید. فقط سلول ها را انتخاب کنید، فرمول را در نوار فرمول قرار دهید و سپس Ctrl را فشار دهید . +Shift+Enter .
در نهایت، شما واقعاً باید در مورد نحوه چیدمان داده های خود تجدید نظر کنید. می توانید یک کاربرگ ایجاد کنید که دارای شماره های هفته در ستون A (1 تا 52 یا 53) و سپس نام مکان ها در ستون B باشد. اگر فردی برای دو هفته رزرو شده بود، نام او دو بار در ستون B ظاهر می شود، یک بار در کنار هر یک از موارد دو هفته ای که رزرو کردند.
با داده‌های خود در این قالب، می‌توانید به راحتی داده‌ها را اسکن کنید تا ببینید کدام هفته‌ها در دسترس هستند، کدام‌ها گرفته شده‌اند و توسط چه کسانی گرفته شده‌اند. اگر می‌خواهید نوعی جستجو انجام دهید، استفاده از تابع VLOOKUP بر اساس شماره هفته آسان است، زیرا اولین ستون داده‌ها به ترتیب مرتب شده است.
