دوشنبه , ۱ مهر ۱۳۹۸
صفحه نخست / توسعه مهارتهای اکسل / چگونه میتوان لیست های کشویی(ComboBox) وابسته بهم ایجاد کرد؟

چگونه میتوان لیست های کشویی(ComboBox) وابسته بهم ایجاد کرد؟

ساخت لیست کشویی وابسته در اکسل-Comboboxکمبوباکس

بسیار پیش می آید که مجبور هستید چندین لیست کشویی یا آبشاری یا همان کمبوباکس ComboBox درون سلولی ایجاد نمایید. برای مثال نام شهرهای مختلف و نام مشتریان مرتبط با هر شهر. در حالت عادی لیستها شامل تمامی مواردی هستند که شما تعیین میکنید و هیچ گونه ارتباطی با یکدیگر ندارند. اما بهتر است وقتی انتخابی از لیست اول انجام میدهید لیست دوم که وابسته به لیست اول می باشد خودبخود فیلتر شود و موارد مرتبط با انتخاب لیست اول را نمایش دهد.

روش اول: با استفاده از فرمول Offset

۱. روی سلول B3 کلیک کنید.

۲.  به آدرس  data > validation بروید.

۳. اسم دفاتر فروش رو تایپ کنید.

۴.روی سلول D3 کلیک کنید.

۵.کل معجزه اینجاست! فرمول OFFSET:

 

=OFFSET($C$6,MATCH($B$3,$B$7:$B$13,0),0,COUNTIF($B$7:$B$13,$B$3),1)

۶. حتما لیستی که انتخاب می کنید باید مرتب شده باشد یعنی تمام داده های مرتبط با یک مرکز فروش پشت سرهم بیاید.

توضیح فرمول:

=Offset(Reference,Rows,Cols,height,width)

میخواهیم یک لیست بسازیم. یعنی میخواهیم در نهایت یک ستون شامل چند ردیف مختلف نمایش داده شود. بطور مثال اگر در لیست اول اصفهان انتخاب شد در لیست دوم یک منوی سه ردیفه شامل نامهای مشتریان اصفهان نمایش داده شود. برای اینکار باید از بالای ستون مشتریها شروع به حرکت کنیم:

Reference: سلولی است که فرمول از آنجا شروع به حرکت می کند در اینجا C6

Rows: تعداد ردیفی که باید از محل سلول رفرنس جابا شویم در اینجا (Match(B3,B7:B13,0

تابع Match موقعیت B3 را در محدوده B7:B13 نشان میدهد. در اینجا مثلا اگر اصفهان را انتخاب کرده باشیم نتیجه فرمول عدد ۳ می باشد.

Cols: تعداد ستونهایی که باید بعد از مرحله قبل جابجا شویم در اینجا ۰ است یعنی نمیخواهیم از ستونی که در آن هستیم جابجا شویم.

خب تا اینجا یعنی از سلول C6 سه ردیف برو پایین و هیچ ستونی جابجا نشو. تا اینجا در سلول C10 هستیم.

Height: ارتفاع محدوده ای که میخواهیم برایمان نمایش داده شود. در اینجا میخواهیم مثلا مشتریهای اصفهان نمایش داده شوند. تا الان به اولین مشتری اصفهان یعنی “مهدی” رسیده ایم-سلول C10- حالا باید از فرمولی استفاده کنیم که بواسطه آن تعداد کل مشتریها اصفهان رابیاورد. طبیعتا باید تعداد مشتریهای اصفهان را بشماریم و بجای height بگذاریم.

(COUNTIF($B$7:$B$13,$B$3 نتیجه عدد سه است.

خب پس تا اینجا از سلول C6 سه ردیف پایین آمدیم تا به اولین مشتری اصفهان رسیدیم سپس هیچ ستونی جابجا نشدیم حالا باید مشخص میکردیم چه محدوده ای را میخواهیم نمایش دهیم. چون میخواهیم یک لیست نمایش داده شود پس باید ابتدا تعداد موارد لیست را با فرمول Countif حساب میکردیم و در نهایت چون میخواهیم لیست نمایش داده شود طبیعتا باید بجای Width عدد یک بگذاریم.

 

روش دوم: با استفاده از فرمول Indirect و Vlookup

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

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

حال کافیست روی سلول لیست دوم کلیک کرده و به تب Data منوی Data Validation بروید و در قسمت Source از فرمول زیر استفاده نمایید:

=Indirect(vlookup(B3,B7:F9,2,0))

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

تابع Indirect همیشه بعنوان یک واسطه عمل میکند. در اینجا سعی میکنیم با استفاده از فرمول VLOOKUP محدوده مرتبط با هر شهر را ابتداً پیدا کنیم و سپس با کمک فرمول INDIRECT مقادیر موجود در آن محدوده را فراخوانی کنیم.

روش سوم: با استفاده از فرمول Indirect و Hlookup

نحوه چدمان داده ها در این روش برعکس روش دوم است و مابقی مسیر مانند همدیگر می باشد.

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

 

دانلود فایل اکسل هر سه روش (نحوه ساخت لیستهای کشویی (آبشاری یا کمبوباکس) وابسته در اکسل)

 

 

۸ دیدگاه

  1. با سلام و وقت بخیر

    اگر بخواهیم داخل لیست کشویی بجز مطالبی که داخل سورس مشخص شده،مطالب دیگری تایپ کنیم چه راه حلی دارید. در هنگام تایپ کردن مواردی که در لیست نباشد ارور ذیل را میدهد:

    this value doesn’t match the data validation restrictions defined for this cell

    برگرفته شده از excelhouse.blog.ir

  2. سلام. من فرمول زیر را تو Date validation میدم. ولی هی خطا میگیره و میگه باید قبل از = علامت ‘ را بزارید. و وقتی این کار را میکنم، فرمول کار نمی کنه 🙁 :'(

    =Offset(C6:C10,0,0),CountC(C:C)-1

  3. سلام
    یک ستون داریم که نام افراد را بصورت تکراری و نامرتب در آن وارد کرده ایم
    ستون دیگری متناظر ستون اول داریم که نام خانوادگی و بصورت تکراری در آن وارد کرده ایم
    حال تصمیم داریم که با نوشتن یک نام از ستون اول در یک سلول خالی ، جدول تک ستونی ایجاد شود که نام خانوادگی های مرتبط با آن نام را نمایش دهد.

    من تصمیم دارم این جدول ایجاد شده را کمبو باکس قرار دهم
    لطفا راهنمایی کنید
    سپاس

  4. سلام
    تعداد داده های من زیاد هست ولی در هر لیست آبشاری فقط ۸ تا از اون رو نشون میده و اسکرول هم نداره
    البته این مشکل فقط در صفحاتی که راست چین هستند وجود داره.
    راهی هست که بدون چپ چین کردن صفحه مشکل رو حل کنم؟

  5. سلام وقت شما بخیر
    ما فایل اکسلی داریم که خروجیش از طریق نرم افزار همکاران سیستم هست یعنی فایل اکسل ما آنلاین به data base نرم افزار همکاران متصله(این مهم نیست برای اطلاع عرض کردم) توی این فایل فیلترهایی وجود داره که هر فیلتر یک لیست کشویی داره مشکل ما اینه که وقتی میخواهیم هر کدوم از فیلتر ها یکی از موارد لیست کشویی رو انتخاب کنیم با انتخاب لیست،لیست کشویی زود می پره یعنی بسته میشه زود و نمیشه چیزی رو انتخاب کرد،اینم بگم خدمتتون که آفیس رو حذف و نصب هم کردم بازم جواب نداد یعنی یه مدت خیلی کوتاهی جواب میده بعد به حالت قبل بر میگرده با پشتیبانی همکاران هم تماس گرفتیم گفتن مشکل از آفیستونه
    (آفیسمون ۲۰۱۶ هست)یعنی عملا اونها هم نتونستن مشکل رو پیدا کنن.
    لطفا اگه راهی هست ممنون میشم راهنماییم کنید.
    با تشکر

  6. با سلام. اگر پاسخ این مشکلتان را پیدا کردید حتما در اینجا بنویسید چون من هم همین مشکل را دارم

  7. سلام من در یک شیت اکسل برای بعضی از سلول ها کمبوباکس ایجاد کردم به این طریق که روی سل کلیک میکنم بعد data validation را میزنم و بعد در پنجره آن لیست را انتخاب میکنم و برای منبع آن نیز به شیت دوم اکسل میروم و در آنجا مقادیری که ستونی نوشتم را انتخاب میکنم و کمبوباکس ایجاد میشود ولی برای هر سل اکسل باید جداگانه انجام دهم و سیو کنم و فایل را ببندم وگرنه همه باهم سیو نمیشود و فقط آخری را سیو میکند ولی بعد از اتمام کار باز هم میبینم کمبوباکس ها کار نمیکنند و علامت فلش مشخص است ولی روی آن که میروم نشان نمی دهد.مشکل کار من کجاست؟البته موقع سیو هم ارور زیر را میدهد ولی Continue را میزنم سیو میشود.خیلی ممنون.
    some cells or styles in this workbook contain formting that is not supported by the selected file format.these format will be converted to the closet format available.

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

دوازده − 2 =

Time limit is exhausted. Please reload CAPTCHA.