نحوه ایجاد لیست های وابسته در اکسل
ایجاد لیست کشویی وابسته در اکسل
حتما تا حالا شده که برای طراحی یک فرم، دیتابیس و … لیست هایی داشته باشید که بخواید به هم وابسته باشن. مثلا توی یک سلول، از یک لیست اسم استان و انتخاب میکنید و میخواید داخل سلول بعدی، شهرهای مربوط به همون استان رو ببینید. به این مسئله، لیست های به هم وابسته یا related list گفته میشه. بعبارتی، محتوای لیست دوم، کاملا به انتخاب لیست اول وابسته است. تو این آموزش میخوام در مورد ایجاد لیست کشویی وابسته در اکسل صحبت کنیم.
فرض کنید دو ستون داریم (H و I) از شهر و استان و میخوایم طوری فرمول نویسی کنیم که با انتخاب هر استان، سلول روبروش، شهرهای فقط مربوط به همون استان نمایش داده بشه.
شکل 1- ایجاد لیست کشویی وابسته در اکسل -لیست های بهم وابسته
مرحله اول: ایجاد لیست اول (نام استان)
روی سلول 2H قرار گرفته و از قسمت Data Validation/ List محدوده A1:E1 رو به عنوان نام استان ها تخصیص میدیم.
مرحله دوم: نامگذاری محدوده ها
محدوده زیر هر استان رو انتخاب کرده و نامگذاری میکنیم. نام هر محدوده رو معادل نام استان میذاریم. به شکل 2 توجه کنید. محدوده A2:A5 انتخاب شده و نام “تهران” تخصیص داده شده. برای آشنایی با نحوه نامگذاری محدوده ها پست مربوط به نامگذاری محدوده ها رو مطالعه کنید.
شکل 2- نامگذاری محدوده های مربوط به هر استان بصورت جداگانه
بعد از اینکه همه محدوده ها رو نامگذاری کردیم. باید مطابق شکل 3، پنج محدوده نامگذاری شده (برای هر استان) داشته باشیم.
شکل 3- محدوده های نامگذاری شده شهرهای هر استان
مرحله سوم: ارجاع به محدوده ها
حالا کافیه با استفاده از تابع Indirect بصورت غیر مستقیم به محدوده های نامگذاری شده ارجاع بدیم. روی سلول I1 قرار گرفته و از تب Data/ Data Validation/ List رو انتخاب میکنیم و مطابق شکل4 در قسمت Source فرمول زیر رو می نویسیم. Validation رو به سلول های دیگه هم انتقال میدیم.
=Indirect($H2)
شکل 4- ایجاد لیست کشویی وابسته در اکسل – فرمول نوشته شده در سلول I2
شرح راه حل این مسئله
تابع Indirect بصورت غیر مستقیم به محدوده یا نام محدوده اشاره میکنه. الان تابع Indirect نگاه میکنه ببینه در سلول H2 چی نوشته. H2 هم که شامل نام یک محدوده در اکسل هست. پس اون محدوده رو در لیست قرار میده. دقت کنید که داده ای که در لیست اول قرار میگیره باید عینا مطابق باشه با اسم محدوده های نامگذاری شده. برای همین اسم محدوده ها رو معادل استان ها گذاشتیم که براحتی بتونیم استفاده کنیم.
حالا ممکنه این سوال پی بیاد که چطور میشه لیست رو داینامیک کرد؟؟ یعنی به محض اضافه کردن یک شهر برای هر استان، محدوده اون خود به خود آپدیت بشه؟
برای این کار باید از طریق Table محدوده ها رو نامگذاری کنیم. یعنی محدوده شهرهای هر استان رو انتخاب کنیم و به Table تبدیل کنیم. بعد نام اون Table رو عوض کنیم. برای آشنایی با Table پست ایجاد جدول در اکسل رو مطالعه کنید.
در واقع در نهایت پنج Table داریم که نام آنها به نام استان ها تغییر کرده.
دانلود فایل این آموزش
برای دانلود این فایل از لینک زیر استفاده کنید:
با عضویت در سایت به صورت مستقیم دانلود کنید
-
ارسال به ایمیل
Des
ممکن است همچنین دوست داشته باشید
رنگی کردن سلول های پروتکت شده
لیست قابل جستجو در اکسل
59 نظر
سلام وقت بخیر من تو اکسل به یه مشکل خوردم میخواستم راهنماییم کنید با تشکر.
جدولی طراحی کردم که درون 3 تا فرمول جدا جدا تعریف شده برای 3 مشخصات جدا میخواستم ببینم میشهطوری تعریف کرد که هر 3 تا فرمول و مشخصات خالص داخل یه جدول تعریف شه ؟
به طور مثال مثلا دمای 14 و وقتی انتخاب می کنیم فرمول مخصوصش محاسباتشو انجام بده و تو ردیف خودش نمایش بده و وقتی یه دما دیگه بدیم جداگانه عدد فرمول جدید و نشون بده ….
درود بر شما
سوال خیلی واضح نیست
اما بصورت کلی میتونید با توجه به تغییر هر سلول با استفاده از if و یا choose میتونید نوع محاسبات رو تغییر بدید
با سلام
من یک لیست کشویی وابسته بهم درست کردم ولی مشکلی که هست اینه که وقتی مقادیر لیست اول فارسی نوشته میشه تابع indirect برای انتخاب لیست دوم کار نمیکنه و مجبور میشم انگلیسی بنویسم تا کار کنه. این چطور حل میشه؟
درود بر شما
مشکل فارسی یا انگلیسی نیست
احتمالا اسپیس داره یا اینکه خطایی وجود داره در نام مورد نظر
در همین آموزش هم مشاهده میکنید که اسم ها فارسی هستن
سلام من تو محیط vba یه userform دارم که داخلش دوتا کمبوباکس هست چطور میتونم اینارو وابسته بهم کنم…مثلا استان رو انتخاب کنم و شهر های همان استان رو نشون بده
سلام، در رویداد Change کمبوباکس اول با استفاده از حلقه For اطلاعات مربوط به مقدار انتخاب شده در کمبوباکس اول رو به کمبوباکس دوم Add Item کنید و قبلش کمبوباکس دوم رو Clear کنید که اطلاعات قبلیش پاک بشه.
سلام.
بنده برای یک دسته اطلاعات (که در قسمتی از سطر اول شیت قرار داره) میخوام لیست کشویی درست کنم و اجبارا بین اون سلولهایی که میخوام برای Data validation استفاده کنم،یکی در میان سلول خالی وجود داره که متاسفانه نباید حذفشون کنم.یا استفاده از انتخاب چندگانه (Ctrl+ Select) هم امتحان کردم،ولی بخاطر اینکه بنده 27 سلول برای انتخاب دارم،فقط اجازه انتخاب محدوده سلولها (از ابتدا تا شماره 27) رو بهم میده که در اینصورت اون سلولهای خالی که مورد نیاز نیست هم به Data validation اضافه میکنه.چه راه حلی برای این مشکل پیشنهاد میکنید بنده اون سلولهای خالی رو از لیست کشویی حذف کنم و نمایش داده نشه؟ ممنون
درود بر شما
باید یک ممحدوده جداگانه ایجاد کنید و داده ها رو بدون خالی و پشت سر هم بیارید و بعد از لیست جدید به عنوان ورودی لیست دیتا ولیدیشن استفاده کنید
سلام ممنون از آموزشتون اگر بخواهیم این کشوها را به 3 یا 4 کشور وابسته به هم تغییر بدیم چکار کنیم مثلاً
نام شهر: آمل -بابل
نام واحد: دبیرخانه-آموزش-کارگزینی
نام کارمند: احمدی-رجبی-بشیری
سلام، به هر تعداد لیست وابسته که بخواید ایجاد کنید باید از تکنیک بالا که نامگذاری انجام میده و در Data Validation قرار داده میشه استفاده کرد.
سلام
من میخوام یه لیست کشویی درست کنم که وقتی یکی از داده های لیست رو انتخاب میکنم .یک جدول رو از جایی که میخوام تو شیت پر کنه.
داده بعدی از یه جا دیگه پر کنه؟
درود بر شما
سوالتون اصلا واضح نیست
بصورت کلی برای فراخوانی داده ها، بسته به ساختار و نیاز از توابع index/ lookup و . .. باید استفاده کنید
میخواستم یه جدول داشته باشم که با انتخاب یکی از سه حالت 1 و 2 و 3 داده های مربوط به اون حالت ها در یه جدول جایگذاری بشن
از یه لیست کشویی و if استفاده کردم و کارم انجام شد . ممنون
باسلام
موقع نوشتن فرمول زیر در مکان مربوط به آن با خطا مواجه میشم لطف میکنید راهنمایی کنید.
میخوام برنامه ای بنویسم که موقع تغیرر سلول اول سلول وابسته به آن ( لیست کشویی) تغییر رنگ پیدا کنه
=ISERROR(VLOOKUP(B4,INDEX(I6:J7,,MATCH($A$4,I5:J5)),1,0
باتشکر
لطفا در صورت امکان جواب را ایمیل کنید.
درود بر شما
برای تغییر رنگ باید از Conditional formatting استفاده کنید
حالا با توجه به سوال و منطق، باید فرمول مناسب رو بنویسید
با سلام
خیلی ممنون از آموزش مفید شما
اگر بخواهیم بعد از نام شهر نام بخش یا روستا رو اضافه کنیم چکار باید انجام بدیم؟
درود بر شما
اسامی نمیتونن با فاصله باشن. باید در نام انتخاب دشه با _ به هم وصل بشن و این منطق روی داده ها پیاده بشه.
میشه هم در حین فرمول نویسی فاصله رو با _ جایگزین کرد با استفاده از توابع متنی
سلام
خیلی ممنون از مطالبتون . عالی هستن
فقط یه سوال آیا امکان داره در حال ثبت مطلبی همواره کنار مطلب به فاطله مثلا یک ستون بتونیم داده هایی که ثبت میکنیمو با تغییرات ثبتی ببینیم و همواره با ثبت ها حرکت کند . مثلا ما 100 تا داده ثبت کردیم اما تیتر همه ی 100 تا فقط 25 مورده که داده ها از این 25 مورد کسر می شن. همچین کشویی یا ریلی داریم که بتونیم موقع ثبت از آخرین تغییرات مربوز به اون ثبت هم باخبر بشیم
درود بر شما
سوال خیلی مبهم هست
درود بر شما
این مطلب آموزشیتون خیلی عالی بود و از اونجا که من DVD آموزشی شما رو قبلا خریداری کردم، برام یه جورایی جنبه مرور رو داشت.
در نظر بگیرید که میخوام لیستی شامل استانها داشته باشم که با کلیک بر روی سلول کناریش، اسم شهرهای اون استان مورد نظر رو بهم نشون بده.
من 30 تا ستون در نظر گرفتم که در سطرهای آن اسامی شهرهاشونو نوشتم.
مشکل من اینه که میخوام بجای اینکه از حالت Region (محدوده انتخاب داده) استفاده کنم، از قابلیت های Table استفاده کنم. (چون ممکنه بعدها اسم یه شهر به Table اضافه بشه و نمیخوام که دوباره کاری بشه و برم دوباره از اول محدوده ها رو دستکاری کنم)
هر کاری که می کنم، نمیتونم دو تا لیست ها رو به همدیگه وابسته کنم.
فکر می کنم یه نکته خیلی ظریفی این وسط باشه که من تو همونجاش مشکل دارم.
اگه امکانش هست، همین آموزش رو در حالت استفاده از Table بزارین؟
از توجه و از زحمات شما بی نهایت سپاسگزارم.
درود بر شما
ساختار به این صورت خواهد بود:
عرض ادب خانم مهندس
در ایجاد لیست کشویی من ده گروه دارم که باید زیر گروه آن در کشو نمایش داده شود .
مثلا گروه 61 که خود 500 کد ده رقمی دارد وزیر گروه آن است مثل 6142162523 چطور میشه دریک سل قرار داد وهنگام نیاز و گزارش به مدیریت آن را نمایش داد
با تشکر —دلجو
درود بر شما
روش توی همین آموزش شرح داده شده.
مشکل کجاست؟
با سلام خدمت شما وسایت خوبتون
یه سوال داشتم ممنون میشم کمکم کنید
من یه فایل اکسل به عنوان دیتابیس دارم که تشکیل شده از نام و نام خانوادگی فقط.
خب من در یک جای دیگه یه فایل اکسل دارم که میخام این اسامی که من در بانک اطلاعاتیم دارم رو ببینه و با انتخاب هر کدوم از اسامی بیاد و در فایل جدید من بشینه بدون تایپ کردن (این اسامی در حال تغییر هستن و هر روز ابدیت میشن ) اگر میشه راهنماییم کنید ممنون میشم ازتون
درود بر شما
میتونید از تابع vlookup استفاده کنید
ولی مسئله اصلی اینه که اسم و فامیل به تنهایی کافی نیست چون ممکنه اسم و فامیل تکراری داشته باشید
باید یک شاخص دیگه مثل کد ملی باشه که تعیین کنه کدام مورد مد نظر شماست
سلام ممنونم از توجه شما.حق با شماست شاید من سوالم را درست بیان نکردم.
شما تصور کنید یک ردیف با سه سلول داریم.
سلول اول یک لیست کشویی با نام چهار کارمند است
سلول دوم یک لیست کشویی با نام ۱۲ ماه است
حالا میخواهم اتوماتیک در سلول سوم مبلغ کارکرد کارمند سلول اول در ماه سلول دوم نمایش داده شود.
کلا چه روشی رو پیشنهاد میکنید
درود بر شما
بستگی به ساختار فایلتون داره
مبلغ کارکرد کجا ذخیره شده؟
بصورت کلی فراخوانی رو با فرمول های جستجو میتونید انجام بدید.
سلام من یک لیست کشویی در دیت ولیدشن درستکردم ولی فقط تا هشت عبارت ان را نشان می دهد لیست من ۱۳ردیفه
درود بر شما
اسکرول کنید
بقیه رو میبینید
سلام
ببخشید چطور میتونم 3 لیست کشویی متصل به هم درست کنم .از vlookup نمیتونم استفاده کنم چون نام ها تکراری است.
شرح کار:برای چهار نفر از پرسنل میخواهم مبلغ کارکرد ماهانه را از طریق منوی کشویی داشته باشم – مثلا منو اول نام کارمند – منو دوم ماه (از فروردین تا اسفند) منو سوم مبلغ
مشکل ساختن جدول دیتا هست نمیدونم چطور 12 ماه رو برای هر چهار کارمند نام گذاری کنم
درود بر شما
توضیحاتتون که نشون نمیده این لیست ها به هم وابسته هستن
یکی اسم کارمند، یکی ماه و یکی ساعت کارکرد…. اینها که هر کدوم مستقل هستن
سوال ررو واضح تر توضیح بدید
سلام.روزتون بخیر.
می خواستم بدونم چطور می تونم در یک سلول با انتخاب گزینه ی موردنظر از لیست کشویی که معرفی کردم ، در سلول کنار آن کد مرتبط با آن گزینه انتخاب شود.
ممنون میشم راهنماییم کنید.
درود بر شما
از تابع Vlookup استفاده کنید
https://excelpedia.net/vlookup-function/
سلام
من برای نام گذاری گروهبندی ها مشکل دارم .
وقتی اسم از چند کلمه تشکیل میشه باید بین شون نقطه گذاشت ک ظاهر جالبی درست نمیکنه.
روش دیگه ای هست براش؟؟
درود بر شما
چون د رنامگذاری مجاز به ثبت فاصله نیستید این مسئله هست
که میتونید در حین فرمول نویسی و استفاده از نام، با استفاده از تابع substitute این فاصله رو با _ یا نقطه جایگزین کنید.
سلام وقت بخیر
من 2 تا ستون دارم – ستون اول نام آزمایشگاه و ستون دوم کد اقتصادی آزمایشگاه
برای ستون اول یک لیست درست کردم که بتونم از داخل اون لیست نام آزمایشگاه رو بدون تایپ کردن انتخاب کنم
حالا می خوام وقتی نام آزمایشگاه از ستون اول انتخاب میشه، در ستون دوم به صورت اتومات کد اقتصادی همون آزمایشگاه درج بشه
میشه راهنماییم کنید؟
سلام
از تابع Vlookup استفاده کنید.
آموزش کار با تابع Vlookup
سلام
ممنونم از آموزش مفید تون
من میخوام تنظیمات لیست کشویی که برای سه سلول پشت هم تعریف کردم به ستون زیرشون کپی کنم اما نتونستم.
لطفا راهنمایی کنید
درود بر شما
Copy/Paste Special/Validation
با سلام و خسته نباشید.
در این فایل می خواهم یک کوئری داشته باشم که مثلا
یک حلقه درست کند که از شیت 1 مقادیر (B:G) قسمت انتخاب شده هر کد کالا را کپی کرده و به شیت کد کالای مورد نظر در آخرین قسمت فعالش منتقل نماید.
شروع کوئری با یک کلید در شیت 1
فایل مربوطه را برایتان ارسال نمودم
ممنون از راهنمایی های خوبتان
پیروز و سر بلند باشید.
درود بر شما
برای این کار ماکرو ضبط کنید. و کدها رو ببینید.
برای پیدا کردن آخرین ردیف پر شده هم در حین ضبط کردن کد، از کلید ctrl و جهت به سمت پایین، استفاده کنید و کدش رو ببینید.
با سلام
سوالی داشتم خدمتتون… یه واحد درخواست دهنده دارم که شامل چهار بخش مختلف است: واحد انبار، واحد تولید، واحد عمران، واحد مکانیک. به طوریکه برای هر کدام از این واحد ها یه جدول با سر ستون های مشابه تعریف شده که داده ها وارد بشن…. حالا من می خوام این چهار واحد لیستی از واحد درخواست کننده باشن بطوریکه روی هر واحد که کلیک می شه به جدول مربوط به خودش وارد بشه….
ممنون می شم راهنمایی بفرمایید.
درود بر شما
اگر جداو ل جداگانه هستن که براحتی میتونید با if این مسئله رو حل کنید
اما اگر جداول در یک شیت هستن و ترکیبی، باید کد بنویسید که بسته به انتخاب هر واحد، فیلدهای مربوطه نمایش داده بشن.
از custom view هم شاید بتونید استفاده کنید.
سلام
من چندین مجموعه و زیر مجموعه درست کردم با ترکیب توابع VLOOKUP و INDIRECT و در نهایت خروجی یک کد چند رقمی است منتها میخام نتیجه انتخابهای قبلیم از لیستها در انتخاب جدید از لیست کشویی نیاد مثلا از مجموعه یک و زیر مجموعه یک کالایی انتخاب شده و خروجی یک کد است حال اگر یک مجموعه جدید انتخاب کنم که زیر مجموعه ای نداشته یاشد ارقام کد زیر مجموعه قبلی در کد جدید می اید. که مایلم عدد صفر را بیندازد.
لطفا راهنمایی فرمایید.
درود بر شما
سوالتون واضح نیست
ولی بصورت کلی با if میتونید کنترل کنید شرایط مختلف رو
سلام
ببخشید میشه در بین دو شیت مختلف که اطلاعات استان و شهر در یک شیت هست و آیکن ها یا آبشارها در شیت دیگر باشد را توضیح بدهید
هرکاری میکنم نمیتوانم به این شکل جواب بگیرم
ممنون بابت آموزش و سایت خوبتون
درود بر شما
بله میشه
فقط باید در آدرس ایجاد شده، اسم شیت رو هم ترکیب کنید. یعنی مثلا اگه داده ها در sheet1 هستن، آدرس A1:A10 بشه : Sheet1!A1:A10
بعد در شیت دیگه این آدرس رو فراخوانی کنید
سلام. من میخوام یک جدول داشته باشم که کاربر در ردیف های مختلف از لیست کشویی استفاده کند. در واقع لیست کشویی باید در ردیف های مختلف تکرار بشه و ایتکه نوع جدول کشویی من وابسته هست (dependent drop down list)
ولی مشکلی که الان هست اینه که توی ردیف های پایینی دوباره از همان سلول اول لود میشن که قبلا پر شده بودن. باید بیاد از ردیف خودش بخونه
درود
احتمالا بحث $ یا آدرس دهی رعایت نمیشه. اگه درست آدرس داده باشید از ردیف خودش میخونه
موفق باشید
سلام
اگر لیست کشوئی دوم را بوسیله data validation باز کنید و علامت $ را از کنار آدرس سل بردارید مثلا =INDIRECT($C$2) را به =INDIRECT(C2) تبدیل کنید میتونید با کپی کردن این سل و انتخاب تعداد سل های مورد نظر و Paste کردن مشکلتون را حل کنید
با سلام و احترام
باز هم مطالبتون قدیمیه . سایت شما هم مثل سایت های دیگه مطالب رو از سایت های خارجی کپی برداری میکنید و برای مردم ارائه میدین .
سعی کنید مطالبی ارائه بدین که لااقل توی سایت های دیگه نباشه . همش تکراریه
سلام
قطعا سایت های خارجی منابع بسیار مفیدی هستند که ترجمه آنها به تنهایی کاری ارزشمند هست و برای بسیاری از علاقمندان که با زبان های خارجی مشکل دارند راهگشا خواهد بود.
اما مطالب اکسل پدیا ترجمه نیستن و تالیف هستن (ممکنه موضوع مشترک باشه، اما متن و توضیحات تالیفی هستن) . ضمن اینکه تجربیات ارزشمند اساتید هم در این آموزشها مشهود هست. ضمن اینکه هدف این نیست که چیزی گفته بشه که هیچ جا نیست. هدف شفاف و واضح گفتن و مفید بودن هست. چون به هر حال همیشه در همه سطوح مراجعه کننده وجود خواهد داشت.
پس صرف اینکه یک مطلب قدیمی هست، نباید از قلم بیفته.
موفق باشید
سلام
بسیار ممنون از آموزش و فایل شما
همه چی عالی و حرفه ای…
سپاس
خانم مهندس من اومدم روش بالا رو انجام دادم ولی برای استانهای دو بخشی مثل خراسان رضوی و خراسان شمالی اسم شهرها رو نشون نمی‌داد و فقط اسم استانهای یک قسمتی رو نشون میداد
محدوده های نامگذاریتون رو چک کنید
احتمالا ناقص انتخاب شده
اگر درست نشد بیاید توی گروه تلگرامی مطرح کنید و فایل بذارید تا بررسی بشه
فاصله بین کلمات مجاز نیست توی لیستهای وابسته. بجای فاصله بین خراسان و رضوی نقطه یا خط فاصله بگذارید مشکلتون حل میشه .
عالی بود ممنون
واقعا عالی و کاربردی. خیلی ممنون از این مطلب. واقعا جالب بود.مخصوصا Table