نحوه ایجاد لیست های وابسته در اکسل
ایجاد لیست کشویی وابسته در اکسل
حتما تا حالا شده که برای طراحی یک فرم، دیتابیس و … لیست هایی داشته باشید که بخواید به هم وابسته باشن. مثلا توی یک سلول، از یک لیست اسم استان و انتخاب میکنید و میخواید داخل سلول بعدی، شهرهای مربوط به همون استان رو ببینید. به این مسئله، لیست های به هم وابسته یا 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 داریم که نام آنها به نام استان ها تغییر کرده.
دانلود فایل این آموزش
برای دانلود این فایل از لینک زیر استفاده کنید:
سلام وقت بخیر من تو اکسل به یه مشکل خوردم میخواستم راهنماییم کنید با تشکر.
جدولی طراحی کردم که درون ۳ تا فرمول جدا جدا تعریف شده برای ۳ مشخصات جدا میخواستم ببینم میشهطوری تعریف کرد که هر ۳ تا فرمول و مشخصات خالص داخل یه جدول تعریف شه ؟
به طور مثال مثلا دمای ۱۴ و وقتی انتخاب می کنیم فرمول مخصوصش محاسباتشو انجام بده و تو ردیف خودش نمایش بده و وقتی یه دما دیگه بدیم جداگانه عدد فرمول جدید و نشون بده ….
درود بر شما
سوال خیلی واضح نیست
اما بصورت کلی میتونید با توجه به تغییر هر سلول با استفاده از if و یا choose میتونید نوع محاسبات رو تغییر بدید
با سلام
من یک لیست کشویی وابسته بهم درست کردم ولی مشکلی که هست اینه که وقتی مقادیر لیست اول فارسی نوشته میشه تابع indirect برای انتخاب لیست دوم کار نمیکنه و مجبور میشم انگلیسی بنویسم تا کار کنه. این چطور حل میشه؟
درود بر شما
مشکل فارسی یا انگلیسی نیست
احتمالا اسپیس داره یا اینکه خطایی وجود داره در نام مورد نظر
در همین آموزش هم مشاهده میکنید که اسم ها فارسی هستن
سلام و وقت بخیر بجای فاصله بین کلمات از _ استفاده کنید .
سلام من تو محیط vba یه userform دارم که داخلش دوتا کمبوباکس هست چطور میتونم اینارو وابسته بهم کنم…مثلا استان رو انتخاب کنم و شهر های همان استان رو نشون بده
سلام، در رویداد Change کمبوباکس اول با استفاده از حلقه For اطلاعات مربوط به مقدار انتخاب شده در کمبوباکس اول رو به کمبوباکس دوم Add Item کنید و قبلش کمبوباکس دوم رو Clear کنید که اطلاعات قبلیش پاک بشه.
سلام.
بنده برای یک دسته اطلاعات (که در قسمتی از سطر اول شیت قرار داره) میخوام لیست کشویی درست کنم و اجبارا بین اون سلولهایی که میخوام برای Data validation استفاده کنم،یکی در میان سلول خالی وجود داره که متاسفانه نباید حذفشون کنم.یا استفاده از انتخاب چندگانه (Ctrl+ Select) هم امتحان کردم،ولی بخاطر اینکه بنده ۲۷ سلول برای انتخاب دارم،فقط اجازه انتخاب محدوده سلولها (از ابتدا تا شماره ۲۷) رو بهم میده که در اینصورت اون سلولهای خالی که مورد نیاز نیست هم به Data validation اضافه میکنه.چه راه حلی برای این مشکل پیشنهاد میکنید بنده اون سلولهای خالی رو از لیست کشویی حذف کنم و نمایش داده نشه؟ ممنون
درود بر شما
باید یک ممحدوده جداگانه ایجاد کنید و داده ها رو بدون خالی و پشت سر هم بیارید و بعد از لیست جدید به عنوان ورودی لیست دیتا ولیدیشن استفاده کنید
سلام ممنون از آموزشتون اگر بخواهیم این کشوها را به ۳ یا ۴ کشور وابسته به هم تغییر بدیم چکار کنیم مثلاً
نام شهر: آمل -بابل
نام واحد: دبیرخانه-آموزش-کارگزینی
نام کارمند: احمدی-رجبی-بشیری
سلام، به هر تعداد لیست وابسته که بخواید ایجاد کنید باید از تکنیک بالا که نامگذاری انجام میده و در Data Validation قرار داده میشه استفاده کرد.
سلام
من میخوام یه لیست کشویی درست کنم که وقتی یکی از داده های لیست رو انتخاب میکنم .یک جدول رو از جایی که میخوام تو شیت پر کنه.
داده بعدی از یه جا دیگه پر کنه؟
درود بر شما
سوالتون اصلا واضح نیست
بصورت کلی برای فراخوانی داده ها، بسته به ساختار و نیاز از توابع index/ lookup و . .. باید استفاده کنید
میخواستم یه جدول داشته باشم که با انتخاب یکی از سه حالت ۱ و ۲ و ۳ داده های مربوط به اون حالت ها در یه جدول جایگذاری بشن
از یه لیست کشویی و if استفاده کردم و کارم انجام شد . ممنون
باسلام
موقع نوشتن فرمول زیر در مکان مربوط به آن با خطا مواجه میشم لطف میکنید راهنمایی کنید.
میخوام برنامه ای بنویسم که موقع تغیرر سلول اول سلول وابسته به آن ( لیست کشویی) تغییر رنگ پیدا کنه
=ISERROR(VLOOKUP(B4,INDEX(I6:J7,,MATCH($A$4,I5:J5)),1,0
باتشکر
لطفا در صورت امکان جواب را ایمیل کنید.
درود بر شما
برای تغییر رنگ باید از Conditional formatting استفاده کنید
حالا با توجه به سوال و منطق، باید فرمول مناسب رو بنویسید
با سلام
خیلی ممنون از آموزش مفید شما
اگر بخواهیم بعد از نام شهر نام بخش یا روستا رو اضافه کنیم چکار باید انجام بدیم؟
درود بر شما
اسامی نمیتونن با فاصله باشن. باید در نام انتخاب دشه با _ به هم وصل بشن و این منطق روی داده ها پیاده بشه.
میشه هم در حین فرمول نویسی فاصله رو با _ جایگزین کرد با استفاده از توابع متنی
سلام
خیلی ممنون از مطالبتون . عالی هستن
فقط یه سوال آیا امکان داره در حال ثبت مطلبی همواره کنار مطلب به فاطله مثلا یک ستون بتونیم داده هایی که ثبت میکنیمو با تغییرات ثبتی ببینیم و همواره با ثبت ها حرکت کند . مثلا ما ۱۰۰ تا داده ثبت کردیم اما تیتر همه ی ۱۰۰ تا فقط ۲۵ مورده که داده ها از این ۲۵ مورد کسر می شن. همچین کشویی یا ریلی داریم که بتونیم موقع ثبت از آخرین تغییرات مربوز به اون ثبت هم باخبر بشیم
درود بر شما
سوال خیلی مبهم هست
درود بر شما
این مطلب آموزشیتون خیلی عالی بود و از اونجا که من DVD آموزشی شما رو قبلا خریداری کردم، برام یه جورایی جنبه مرور رو داشت.
در نظر بگیرید که میخوام لیستی شامل استانها داشته باشم که با کلیک بر روی سلول کناریش، اسم شهرهای اون استان مورد نظر رو بهم نشون بده.
من ۳۰ تا ستون در نظر گرفتم که در سطرهای آن اسامی شهرهاشونو نوشتم.
مشکل من اینه که میخوام بجای اینکه از حالت Region (محدوده انتخاب داده) استفاده کنم، از قابلیت های Table استفاده کنم. (چون ممکنه بعدها اسم یه شهر به Table اضافه بشه و نمیخوام که دوباره کاری بشه و برم دوباره از اول محدوده ها رو دستکاری کنم)
هر کاری که می کنم، نمیتونم دو تا لیست ها رو به همدیگه وابسته کنم.
فکر می کنم یه نکته خیلی ظریفی این وسط باشه که من تو همونجاش مشکل دارم.
اگه امکانش هست، همین آموزش رو در حالت استفاده از Table بزارین؟
از توجه و از زحمات شما بی نهایت سپاسگزارم.
درود بر شما
ساختار به این صورت خواهد بود:
=INDIRECT("Table1[Column1]")