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

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

حتما تا حالا شده که برای طراحی یک فرم، دیتابیس و … لیست هایی داشته باشید که بخواید به هم وابسته باشن. مثلا توی یک سلول، از یک لیست اسم استان و انتخاب میکنید و میخواید داخل سلول بعدی، شهرهای مربوط به همون استان رو ببینید. به این مسئله، لیست های به هم وابسته یا related list گفته میشه. بعبارتی، محتوای لیست دوم، کاملا به انتخاب لیست اول وابسته است. تو این آموزش میخوام در مورد ایجاد لیست کشویی وابسته در اکسل صحبت کنیم.

فرض کنید دو ستون داریم (H و I) از شهر و استان و میخوایم طوری فرمول نویسی کنیم که با انتخاب هر استان، سلول روبروش، شهرهای فقط مربوط به همون استان نمایش داده بشه.

شکل ۱- ایجاد لیست کشویی وابسته در اکسل -لیست های بهم وابسته

مرحله اول: ایجاد لیست اول (نام استان)

روی سلول 2H قرار گرفته و از قسمت Data Validation/ List محدوده A1:E1 رو به عنوان نام استان ها تخصیص میدیم.

مرحله دوم: نامگذاری محدوده ها

محدوده زیر هر استان رو انتخاب کرده و نامگذاری میکنیم. نام هر محدوده رو معادل نام استان میذاریم. به شکل ۲ توجه کنید. محدوده A2:A5 انتخاب شده و نام “تهران” تخصیص داده شده. برای آشنایی با نحوه نامگذاری محدوده ها پست مربوط به نامگذاری محدوده ها رو مطالعه کنید.

شکل ۲- نامگذاری محدوده های مربوط به هر استان بصورت جداگانه

بعد از اینکه همه محدوده ها رو نامگذاری کردیم. باید مطابق شکل ۳، پنج محدوده نامگذاری شده (برای هر استان) داشته باشیم.

شکل ۳- محدوده های نامگذاری شده شهرهای هر استان

مرحله سوم: ارجاع به محدوده ها

حالا کافیه با استفاده از تابع Indirect بصورت غیر مستقیم به محدوده های نامگذاری شده ارجاع بدیم. روی سلول I1 قرار گرفته و از تب Data/ Data Validation/ List رو انتخاب میکنیم و مطابق شکل4 در قسمت Source فرمول زیر رو می نویسیم. Validation رو به سلول های دیگه هم انتقال میدیم.

=Indirect($H2)

شکل ۴- ایجاد لیست کشویی وابسته در اکسل – فرمول نوشته شده در سلول I2

شرح راه حل مسئله ایجاد لیست کشویی وابسته

تابع Indirect بصورت غیر مستقیم به محدوده یا نام محدوده اشاره میکنه. الان تابع Indirect نگاه میکنه ببینه در سلول H2 چی نوشته. H2 هم که شامل نام یک محدوده در اکسل هست. پس اون محدوده رو در لیست قرار میده. دقت کنید که داده ای که در لیست اول قرار میگیره باید عینا مطابق باشه با اسم محدوده های نامگذاری شده. برای همین اسم محدوده ها رو معادل استان ها گذاشتیم که براحتی بتونیم استفاده کنیم.

حالا ممکنه این سوال پی بیاد که چطور میشه لیست رو داینامیک کرد؟؟ یعنی به محض اضافه کردن یک شهر برای هر استان، محدوده اون خود به خود آپدیت بشه؟

برای این کار باید از طریق Table محدوده ها رو نامگذاری کنیم. یعنی محدوده شهرهای هر استان رو انتخاب کنیم و به Table تبدیل کنیم. بعد نام اون Table رو عوض کنیم. برای آشنایی با Table پست ایجاد جدول در اکسل رو مطالعه کنید.

در واقع در نهایت پنج Table داریم که نام آنها به نام استان ها تغییر کرده.

دانلود فایل این آموزش

برای دانلود این فایل از لینک زیر استفاده کنید: