دوشنبه , ۷ فروردین ۱۳۹۶
صفحه نخست / توسعه مهارتهای اکسل / چگونه میتوان لیست های کشویی(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

پاسخ دهید

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

چهارده + 7 =

Time limit is exhausted. Please reload CAPTCHA.