نحوه ایجاد لیست های وابسته در اکسل
ایجاد لیست کشویی وابسته در اکسل
حتما تا حالا شده که برای طراحی یک فرم، دیتابیس و … لیست هایی داشته باشید که بخواید به هم وابسته باشن. مثلا توی یک سلول، از یک لیست اسم استان و انتخاب میکنید و میخواید داخل سلول بعدی، شهرهای مربوط به همون استان رو ببینید. به این مسئله، لیست های به هم وابسته یا 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 داریم که نام آنها به نام استان ها تغییر کرده.
دانلود فایل این آموزش
برای دانلود این فایل از لینک زیر استفاده کنید:
سلام
اگر بخوایم این لیست رو به چند سطر تعمیم بدیم باید چه کاری انجام بدیم
به عنوان مثال
در یک شرکت ساختمانی
مواد اولیه به چند دسته اصلی و فرعی تقسیم میشن
و اگر بخوایم این دسته بندی ها مثلا در هزار سطر باشه و وابسته به هم باید چه کاری انجام بدیم
کاری که من کردم باعث میشه تابع indirect فقط از سلول اولیه داده میگیره و بعد از تعمیم دادن به سطر های دیگه به صورت داینامیک به روز نمیشه
درود
$ در فرمول ها رو چک کنید
سلام. ممنونم بابت آموزش. من این لیست وابسته رو برای حداقل ۱۰۰۰ ردیف باید تعریف کنم. لیست اول مشکلی نداره کپی میشه , ولی اگر فرمول رو برای لیست دوم کپی کنم ، به اولین ردیف نگاه میکنه و لیست پویا نیست.روشی وجود داره که یکی یکی آرگومان indicator رو تغییر ندیم؟(از این مسیر کپی می کنم: Copy/Paste Special/Validation)
درود
متوجه نشدم درست مشکل کجاست
اگر مسئله درگ کردنه باید $ رو در فرمول اصلاح کنید
درود مهندس–لیست کشویی که مثلا واحد آن متر باشد کل اعداد وارد شده به متر تبدیل بشوند و در صورت انتخاب فوت اعداد تبدیل به واحد فوت شوند فرمول آن چیست –ممنون می شوم کمک کنید.
درود
میتونید یک ستون نهایی درست کنید و با استفاده از IF مقدار انتخاب شده در سلول حاوی لیست رو بررسی کنید و متناسب با مورد انتخاب شده، مقدار درون سلول اولیه رو تبدیل کنه و نمایش بده.
راه دیگه استفاده از VBA هست.
سلام
وقتی نام آذربایجان شرقی یا استان های دو سه کلمه ای رو میخام شهرهاشو نمایش بدم اونا رو نشون نمیده ولی استانهای تک کلمه رو نشون میده!
حتی وقتی به صورت آندرلاین دارمثل آذربایجان_غربی می نویسم؟؟؟؟؟
درود
در مورد دو قسمتی ها مقاله زیر رو مطالعه کنید:
https://excelpedia.net/picture-vlookup/
سلام
من سه تا ستون دارم
که به توالی به هم وابسته هستند
حالا میخوام از هر ستون مقادیر یکتا رو بگیرم و با هر کدومشون یدونه لیست بسازم که در نهایت به هم وابسته هستند.
مقادیر تکرری توشون زیاده
بهم میشه بگی مقادیر یکتا رو چطور باید به هم متصل کنم؟
ببینین من بلدم چطوری مقادیر غیر تکراری رو توی یک لیست بیارم
مقادیر یکتا رو هم بلدم پیدا کنم
ولی نمیدونم چطوری وابسته کنمشون!!
میشه بهم ایمیل بزنین؟؟
درود بر شما
منظورتون از وابسته کردن رو متوجه نشدم
همین مقاله که کامنت گذاشتید مربوط به لیست های وابسته است
فکر کنم مثال بزنید بهتر متوجه بشید
سلام.
من میخوام چند نمودار رو دسترسی سریع براشون ایجاد کنم و به صکرت لیست کشویی یا روشی بهتر اگه مدنظرتون هست در بیارم.
چیکار باید کنم؟ممنون
درود بر شما
اگر منظور اینه با تغییر لیست کشویی نمودار تغییر کنه یا باید برید سراغ pivot chart یا نمودارهای پویا
من دو تا فایل دارم می خوام حدول اطلاعات را از فایل دو با انتخاب از فایل کشویی بخونه مثلا صدیقی را انتخاب کنم در جدول ۱ و فایل یک تمام مشخصات صدیقی را جایگزین فرم کنه با indirect وقتی ارجاع میدم خطای #ref را میده من با ترکیب فرمول ها ادرس را ایجاد می کنم ولی وقتی میدم indirect ارجاع نمیشه لطفا راهنمایی کنید indirect(a2)
لیست کشویی>صدیقی
A1=(لیست کشویی) >صدیقی
A2=( “‘”& a1&”‘!c5”)
سلام خیلی ممنون بابت این اموزش
من میخوام وقتی یکی از موارد داخل لیست رو انتخاب میکنم ستون های اکسل مربوط به اون مورد نمایش داده بشه و باقی ستون ها هاید بشه میشه لطفا راهنمایی کنید
درود بر شما
کدنویسی VBA باید انجام بدید
سلام
میخام برای محصولاتی که به طول و عرض مشخص میشن، لیست کشویی تعریف کنم.
بدین صورت که مثلا عرض ۸۰، سه تا طول میتونه داشته باشه،عرض ۱۰۰، چهار تا طول مختلف و … همینطور.
برای تعریف کشویی وابسته، در سلول اول عرض محصول رو انتخاب میکنم و میخوام در سلول دوم، کشویی طول های قابل ساخت برای اون عرض رو بیاره.
وقتی میخوام نام گذاری کنم که INDIRECT بدم به اون سلول، چون در سلول اول فقط عدد نوشته شده، نمیتونم نامگذاری کنم که ریفر بدم بهش.
ممنون میشم راهنمایی کنید.
درود وقتی عدد نوشته شده و نامکذاری میکنید یک _ در اول نام ها ظاهر میشه که از حالت عددی خارج کنه.
پس داخل فرمول ولیدیشن، این _ رو ایجاد کنید
مثلا با این تابع:
=INDIRECT(REPLACE($E$1,1,0,”_”))
سلام
فرض کنید دو ستون “استان” و “شهر” داریم و می خواهیم وقتی در ستون استان، استانی انتخاب میشه شهرهای همون استان در ستون شهرها نمایش داده بشه و قابل انتخاب باشه.
برای این مورد از Table استفاده کردیم و Table ها را نامگذاری کردیم. (مطابق این آموزش: https://excelpedia.net/excel-table/)
برای ایجاد لیست و فراخوانی table های نامگذاری شده چه باید کرد؟
از چه فرمولی باید استفاده کرد؟
درود
این مقاله ای که انتهاش کامنت گذاشتید دقیقا پاسخ سوال شماست!
فایلش هم قابل دانلود هست
ببینید سوالتون همینه؟
همه چیز اوکی شد ولی می خوام لیست استان رو که میزنم، شهرهای همون استان باز بشه.
نمیدونم چه دستوری رو کجا باید درج کنم؟؟؟
مشکلم این هست.
این مقاله دقیقا راجع به همینه که شهرهای هر استان رو نشون بده
مجدد مطالعه کنید
یکجای کار رو اختمالا اشتباه رفتید
سلام من لیست کشویی درست کردم با دیتا ولیدشن . مشکل اینه وقتی لیست اسامی خیلی طولانی باشه باید با سکروا کردن بینشون انتخاب کنیم که خیلی وقت گیره . چطور میشه با نوشتن مثلا سه حرف اسامی که این سه حرف رو دارند لیست کنه که سریع تر جستجو به نتیجه برسه . ممنون
درود
این مقاله رو بخونید
https://excelpedia.net/searchable-list/