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

شاید ایجاد یک لیست کشویی هوشمند در اکسل برای بعضی کاربران چالش‌برانگیز باشد. در این مطلب از مجله فرادرس به معرفی چهار روش برای ایجاد لیست کشویی می‌پردازیم. شاید این روش‌ها شاید پیچیده و طولانی به نظر بیایند، اما با استفاده از آرایه‌های دینامیک اکسل به راحتی انجام می‌شوند. دقت کنید در این مطلب از امکاناتی استفاده خواهیم کرد که در اکسل ۳۶۵ و اکسل ۲۰۲۱ به بعد معرفی شده‌اند. ابتدا نحوه ساخت یک لیست کشویی دینامیک و سپس چند لیست کشویی را آموزش خواهیم داد و در ادامه لیست کشویی گسترش‌پذیر و مرتب کردن لیست کشویی بر اساس حروف الفبا را توضیح می‌دهیم.

ایجاد لیست کشویی دینامیک در اکسل

مثال زیر روشی کلی را برای ایجاد یک لیست کشویی هوشمند در اکسل با استفاده از تابع آرایه پویا (دینامیک) جدید اکسل نشان می‌دهد. فرض کنید لیستی از میوه‌ها در ستون A و لیستی از صادرکنندگان در ستون B دارید. نام میوه‌ها نیز گروه‌بندی نشده‌اند و به طور پراکنده در ستون A قرار گرفته‌اند. هدف این است که در جدول سمت راست، سلول سمت چپ، نام میوه وارد شود و بسته به انتخاب کاربر، صادر کننده مرتبط در سلول سمت راست نمایش داده شود.

فیلم آموزش اکسل Microsoft Excel 2021 در فرادرس

کلیک کنید

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

=UNIQUE(A3:A15)

فرمول در سلول G3 نمایش داده می‌شود و با زدن روی Enter سلول‌های بعدی نیز به همین ترتیب پر می‌شوند.

حال برای ایجاد لیست کشویی، اعتبارسنجی داده (Data Validation) در اکسل را به ترتیب زیر اعمال کنید:

• سلول موردنظر خود را انتخاب کنید. در این مثال سلول D3 را انتخاب می‌کنیم.
• در سربرگ «Data» در بخش «Data Tools» روی «Data Validation» کلیک کنید
• در پنجره Data Validation زیر «Allow»، گزینه «List» را انتخاب کنید
• در بخش «Source» مرجع را با سلول‌هایی که نتیجه تابع Unique در آنها قرار دارد آدرس‌دهی کنید و در نهایت یک علامت هشتگ بگذارید. سپس روی Ok بزنید.

لیست کشویی شما آماده است.

حال می‌توانید منوی کشویی وابسته دومی را ایجاد کنید. نیاز است داده‌های مورد نیاز برای ایجاد این منو را تعیین کنید. برای این کار داده‌های موجود در ستون B را بر اساس مورد انتخاب شده در منوی کشویی اول فیلتر می‌کنیم. برای این کار می‌توانیم از تابع آرایه داینامیک دیگری به نام Filter استفاده کنیم.

=FILTER(B3:B15, A3:A15=D3)

عبارت B3:B15   آدرس داده‌های مرجع برای منوی کشویی دوم است. A3:A15   نیز آدرس داده‌های مرجع برای منوی کشویی اصلی هستند. D3 نیز آدرس سلول لیست کشویی اصلی است. برای اینکه مطمئن شوید فرمول به درستی کار می‌کند، می‌توانید مقداری را در منوی کشویی اول انتخاب کنید و نتیجه را در لیست کشوی دوم ببینید.

حال برای ایجاد لیست کشویی دوم مراحل Data Validation را دقیقاً طبق مراحلی که برای منوی اول انجام دادید تکرار کنید. فقط کافی‌ست این بار بخش Source را با خروجی تابع Filter آدرس‌دهی کنید و سپس در انتهای آدرس یک هشتگ ( =$H$3#   ) تایپ کنید.

لیست کشویی وابسته شما آماده است.

ایجاد چند لیست کشویی وابسته در اکسل

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

ابتدا نیاز است لیست کشویی اول را ایجاد کنیم. این کار دقیقاً طبق مراحل شرح داده شده در بخش قبلی انجام می‌شوند. تفاوت در آدرس وارد شده در بخش Source در پنجره Data Validation است. در مثال، این بخش تابع UNIQUE   را در سلول E8 وارد کردیم و منوی کشویی اصلی نیست در سلول E3 قرار دارد. از این رو روی سلول E3 کلیک کنید سپس این آدرس =$E$8#   را وارد کنید.

در مرحله بعدی منوی آبشاری دوم را ایجاد می‌کنیم. همان طور که قابل ملاحظه است، در ستون ‌B صادرکنندگان تکرار شده است. اما قصد نداریم در لیست آبشاری مان نامی تکرار شود از این رو تابع Unique و فیلتر را با یکدیگر مشابه فرمول زیر ترکیب می‌کنیم

=UNIQUE(FILTER(B3:B15, A3:A15=E3)) 

عبارت B3:B15  مرجع داده برای منوی آبشاری دوم و است و A3:A15   مرجع داده برای منوی آبشاری اول هستند. E3 نیز آدرس لیست آبشاری اول است. ایجاد این فرمول عبارت =$F$8#   را برای Source در Data Validation وارد کنید.

برای ایجاد لیست کشوی سوم نیاز است از تابع فیلتر چند شرطی استفاده کنیم. فرمول ابتدا لیست میوه‌ها را با توجه به مقدار انتخاب شده در منوی اول ( A3:A15=E3  ) بررسی می‌کند و شرط دوم لیست صادرکنندگان را با توجه به مقدار انتخاب شده در منوی دوم داخل پرانتز اعمال می‌کند. نتیجه فرمول نیز در سلول G8 نمایش داده می‌شود.

=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))

اگر قصد داشته باشید منوهای بیشتری ایجاد کنید، احتمالاً ستون C مقادیر تکراری خواهد داشت. برای جلوگیری از تکرار تنها کافی‌ست تابع فیلتر را با تابع Unique ترکیب کنید. مشابه آنچه در مرحله قبلی انجام داده‌ایم.

=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

نهایتاً نیز در بخش Data Validation منبع Source را G8 تعریف می‌کنیم.

لیست کشویی وابسته چندتایی شما آماده است.

ایجاد لیست کشویی گسترش پذیر در اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

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

تغییر فرمول برای لیست اصلی

در ستون میوه‌ها از A3:A15   ۵ سلول جدید اضافه می‌کنیم.تابع فیلتر را در تابع Unique قرار می‌دهیم به طوری که سلول‌های خالی را در نظر نگیرد. فرمول در سلول G3 به ترتیب زیر خواهد بود:

=UNIQUE(FILTER(A3:A20, A3:A20<>""))

تغییر فرمول برای لیست وابسته

فرمول موجود در سلول G3 تغییر چندانی نمی‌کند، تنها نیاز است آدرس وارد شده در آرگومان‌ها را به اندازه ۵ سلول اضافه شده تغییر دهیم.

=FILTER(B3:B20, A3:A20=D3)

نتیجه در تصویر زیر مشاهده می‌شود.

مرتب کردن لیست کشویی بر اساس حروف الفبا

برای مرتب کردن موارد منو به ترتیب حروف الفبا می‌توانیم به سادگی از تابع «Sort» در فرمول‌ها استفاده کنیم. تنظیمات Data Validation تغییری نخواهند کرد.

مرتب کردن از A تا Z

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

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

منوی وابسته نیست فرمول زیر استفاده خواهد شد.

=SORT(FILTER(B3:B20, A3:A20=D3))

موارد موجود در لیست‌ها به ترتیب حروف الفبا از A تا Z مرتب می‌شوند.

مرتب کردن از Z تا A

برای این کار نیاز است آرگومان سوم تابع Sort را روی منفی یک قرار دهیم تا ترتیب مرتب سازی از آخر به اول باشد. بر این اساس فرمول لیست اصلی به ترتیب زیر تغییر خواهد کرد.

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

فرمول تابع وابسته نیز مشابه فرمول زیر خواهد بود.

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

با استفاده از این روش‌ها می‌توانید به راحتی لیست کشویی هوشمند در اکسل ایجاد کنید. برخلاف روش‌های قدیمی‌تر، با استفاده از این روش مقادیر تک‌کلمه‌ای و چند‌کلمه‌ای بدون هیچ تغییری در مراحل انجام، در منو وارد می‌شوند.