در این مطلب با ساخت لیست های کشویی وابسته و چند سطحی اکسل آشنا میشویم. با استفاده از لیست های کشویی مقدار مربوط به یک سلول را بر اساس مقدار موجود در یک سلول دیگر محدود می کنیم. برای مثال، مقدار “Fruit” (میوه) را در سلول B2 انتخاب میکنید و مقادیر “apple, banana, peach” (سیب، موز و هلو) در لیست کشویی سلول C2 ظاهر میشود. یا از بین استان های کشور یکی مثلا «خراسان رضوی» یا «تهران» را انتخاب میکنید و شهرهای آن ها در سلول بعد نمایش داده میشود.

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

تصویر متحرک زیر نشان میدهد که یک لیست کشویی وابسته چگونه کار میکند:

• میوه به عنوان نوع تولید در ردیف سوم قرار گرفته است، بنابراین لیست کشویی لیست میوه ها را نشان میدهد.
• سبزیجات به عنوان نوع تولید در ردیف چهارم قرار گرفته است، بنابراین لیست کشویی لیست سبزیجات را نشان می دهد

شروع

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

1. ساخت یک ورک بوک جدید
2. تغییر نام شیت 1 به “DataEntry”
3. اضافه کردن یک شیت جدید در ورک بوک و نامگذاری آن به نام “Lists”

ساخت لیست های دارای نام

گام های زیر را طی کنید. این لیست ها آیتم های مورد نیاز لیست های کشویی وابسته را فراهم میکنند. در این مثال، سه لیست وجود خواهد داشت:

• نوع محصول
• نام میوه ها
• نام سبزیجات

در این مثال، لیست اول را produce نامگذاری میکنیم. این لیست دسته بندی های محصولات را نشان خواهد داد.

لیست را تایپ کنید

لیست اصلی آیتم های یک کلمه ای را در خود جای داده است(Fruit, Vegetable) . سپس این کلمات در یک محدوده به کار برده میشود.

1. در شیت list، در سلول B2 عنوان Produce List را تایپ کنید.
2. در سلول های B3 و B4 مقادیر fruit و vegetable را تایپ کنید.

ساخت یک جدول

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

1. یک سلول را در لیست انتخاب کنید. و در پنجره home، بر روی گزینه Format as Table کلیک کنید.
2. بر روی یکی از استایل های جدول کلیک کنید.
3. چک My table has headers را فعال کنید.
4. Ok را بزنید

ساخت یک محدوده دارای نام

گام های زیر را برای ساخت یک لیست دارای نام طی کنید. در آینده و زمان ساخت لیست کشویی از این نام استفاده خواهیم کرد.

1. آیتم های درون لیست را انتخاب کنید.ولی عنوان انتخاب نشود. در این مثال سلول های B3:B4 انتخاب شده است.
2. بر روی name box در قسمت سمت چپ formula bar کلیک کنید .
3. یک نام یک کلمه ای برای لیست تایپ کنید.
4. کلید enter را بزنید تا فرایند تکمیل شود.

ساخت لیست های دارای نام بیشتر

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

این لیست ها دارای نام هستند و نام ها می بایست دقیقا مطابق عناصر لیست محصولات باشند.

1. در شیت lists، در سلول d2، fruit list را تایپ کنید.

2. نام میوه ها را در سلول های d3:d6 تایپ کنید. Apple, Banana, Lemon, Peach

3. لیست را تبدیل به یک جدول دارای نام کنید.

4. نام میوه ها در سلول های d3:d6 را انتخاب کنید.

5. بر روی name box کلیک کنید.

6. یک نام یک کلمه ای برای آن تایپ کنید — fruit

7. کلید enter را فشار دهید. تا فرایند نام گذاری تکمیل شود.

8. همین فرایند را برای ایجاد لیست برای نام سبزیجات طی کنید. – – و آن لیست را vegetable نامگذاری کنید.

اضافه کردن لیست کشویی

سپس، عملیات data validation را به شیت DataEntry اعمال کنید.

• ستون Produce Type یک لیست ساده خواهد داشت.
• ستون item با استفاده از یک فرمول آیتم های مربوط به produce type را نشان خواهد داد.

شروع به کار شیت data entery

ابتدا، عناوین را در شیت data entery وارد میکنید. و یک جدول دارای نام ایجاد میکنید

الف. در شیت data entery عناوین را در سلول های B2:C2 تایپ کنید. Produce type و item

ب. سلول B2 را انتخاب کنید. و در پنجره home، Format as Table را انتخاب کنید.

ج. یکی از انواع جدول را انتخاب کنید.

د. چک مربوط به My table has headers را قرار دهید.

ه. ok را کلیک کنید.

2. ساخت کشوی اول

سپس میبایست یک لیست کشویی در ستون produce type ایجاد کنید.

الف. در شیت data entery سلول b3 را انتخاب کنید.

ب. در ریبون، پنجره data را انتخاب کنید سپس data validation  بروید

ج. در قسمت Source یک علامت مساوی تایپ کنید و نام لیست را قرار دهید:  Produce=

د. برای تکمیل ok را کلیک کنید

ه. برای تست لیست کشویی ، بر روی فلش کلیک کنید و fruit را انتخاب کنید.

ساخت لیست کشویی وابسته

سپس، در ستون item یک لیست کشویی وابسته ایجاد میکنیم. این سلول یک فرمول خواهد داشت که لیست کشویی وابسته را ایجاد خواهد کرد. برای اطلاعات بیشتر در مورد تابع indirect میتوانید به این مطلب مراجعه کنید.

الف. در شیت data entry سلول C3 را انتخاب کنید.

ب. در ریبون، پنجره data را باز کنید و گزینه data validation را انتخاب کنید.

ج. از لیست کشویی Allow، گزینه list را انتخاب کنید.

د. در قسمت source، یک علامت مساوی و تابع indirect را تایپ کنید و سلول داده اول در ستون data source را درون فرمول قرار دهید.

در این مثال، سلول c3 فعال است.در نتیجه فرمول، به سلول product type اشاره خواهد کرد که در ردیف سوم قرار دارد:

=INDIRECT(B3)

ه. ok را بزنید

نکته: اگر سلول B3 خالی باشد، پیام زیر را مشاهده خواهید کرد. برای ادامه yes را انتخاب کنید.

برای تست کردن لیست کشویی، فلش را کلیک کنید و یکی از انواع میوه ها را انتخاب کنید.

تست کردن لیست کشویی

برای تست دوباره لیست کشویی گام های زیر را طی کنید

1. سلول c3 را انتخاب کنید. سپس کلید tab را فشار دهید تا یک ردیف جدید در جدول data entry شروع شود.

2. در ستون produce type یکی از گزینه های میوه یا سبزیجات را انتخاب کنید.

3. کلید tabر ا فشار دهید، تا به ستون item در همان ردیف منتقل گردید.

4. در ستون item بر روی فلش کلیک کنید و یکی از آیتم ها را انتخاب کنید. لیست گزینه های fruit یا vegetable را نشان خواهد داد( بسته به اینکه در ستون قبل کدام گزینه را انتخاب کرده باشید.)

نکته: اگر produce type (نوع محصول) انتخاب نشده باشد، کشوی آن لیست کار نخواهد کرد.

اضافه کردن لیست وابسته سوم

در صورت نیاز میتوانید یک لیست کشویی دیگر انتخاب کنید که به انتخاب شما در دو لیست قبل وابسته باشد. برای مثال انتخاب کشور- منطقه – شهر

1. دو محدوده دارای نام و لیست کشویی به شکلی که در بالا توضیح داده شد ایجاد کنید.

2. مجموعه دیگری از محدوده های دارای نام بر اساس ترکیب دو لیست کشویی قبل ایجاد کنید. برای مثال میتوانید محدوده هایی با نام های CanadaOntario و USANewYork ایجاد کنید.

3. برای کشوی سوم، از گزینه Allow، list را انتخاب کنید و از فرمولی استفاده کنید که عناصر را در دو ستون ابتدایی با هم ترکیب کند و فاصله ها را از نام ها حذف کند. برای مثال، در سلول d2، فرمول میتواند به شکل زیر باشد:

=INDIRECT(SUBSTITUTE(B2&C2," ",""))

استفاده از آیتم های دو کلمه ای

در بعضی از ورک بوک ها، ممکن است نیاز باشد که آیتم های دو کلمه ای در اولین لیست کشویی خود داشته باشید. برای مثال، انتخاب شما “red fruit”  Green Fruit’ و ‘Yellow Fruit’ باشد،

1. محدوده دارای نام و لیست کشویی اول را به همان شکلی که دربالا توضیح داده شد بنویسید.
2. لیست های پشتیبان را با استفاده از نام های یک کلمه ای ایجاد کنید. برای مثال RedFruit, GreenFruit, YellowFruit
3. برای کشوی دوم، allow را بر روی list قرار دهید و با استفاده از یک فرمول، فاصله ها را از نام حذف کنید:

=INDIRECT(SUBSTITUTE(A2," ",""))

استفاده از آیتم ها همراه با کاراکترهای غیرمجاز

ممکن است در لیست کشوی اول آیتم هایی داشته باشید که نوشتن آن نیاز به استفاده از کاراکترهای غیرمجاز در نام محدوده مثل & داشته باشد.

برای مثال، گزینه های شما  ‘Red Fruit’, ‘Green Fruit’ and ‘Yellow & Orange Fruit’ باشد.برای لیست های وابسته، میتوانید محدوده هایی با نام های یک کلمه ای مثل YOFruit ایجاد کنید.سپس، یک جدول جستجو ایجاد کنید، که هر کدام از آیتم ها را در لیست کشویی اول قرار میدهد

برای شروع لیست آیتم ها و کشوی اول را ایجاد کنید.

1. محدوده دارای نام و لیست کشویی اول را به شکلی که در بالا توضیح داده شد ، ایجاد کنید. در این مثال، محدوده ProductList نامگذاری میشود و مقادیر آن در سلول های A6:A8 قرار میگیرند. لیست کشویی در سلول A2 از ProductList بعنوان منبع استفاده میکند.
2. لیست وابسته را ایجاد کنید. و یک نام یک کلمه ای برای آن قرار دهید. بهتر است در انتهای نام از list استفاده کنید برای مثال، RedFruitList, GreenFruitList, YOFruitList . در این مثال، RedFruitList در سلول های a1:a12، YOFruitList در سلول های A15:A16 و GreenFruitList در سلول های A19:20 قرار دارند.
3. یک آیتم را از لیست کشویی در سلول a2انتخاب کنید.

سپس، جدول جستجو یا lookup table را ایجاد کنید تا هر آیتم را با محدوده آیتم وابسته به آن مطابقت دهید.

1. در ستون سمت راست محدوده FruitList کد نام را برای هرکدام از آیتم های لیست وابسته وارد کنید.برای مثال، YOFruit  بعنوان کد Yellow & Orange Fruit. به کار گرفته میشود.
2. lookup table (جدول جستجو)را نامگذاری کنید.در این مثال، محدوده A6:B8 بعنوان ProductLookup نامگذاری شده است.
3. سلول b2 را انتخاب کنید. و از منوی data گزینه validation را انتخاب کنید.
4. Allow>list را انتخاب کنید.
5. در قسمت Source، فرمولی را وارد کنید که با استفاده از تابع vlookup نام محدوده لیست وابسته را پیدا کند – ترکیبی از کد نام و “list” برای مثال،

=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&”List”)

با انتخاب  Red Fruit در سلول a2، فرمول vlookup مقدار RedFruitList را بعنوان نام محدوده برای  لیست وابسته برگردانده میشود.

برای ایجاد لیست وابسته سوم، از همین تکنیک برای ایجاد lookup table و لیست آیتم ها استفاده کنید.

در تصویر زیر، جدول های جستجویی وجود دارد که RedFruitLookup، YOFruitLookup و GreenFruitLookup نام دارند. لیست آیتم ها که به صورت کد نام+ list هستند به ورک شیت اضافه شده است .

در پنجره data validation برای کشوی سوم از فرمول زیر استفاده کنید:

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

این فرمول lookup table را بر اساس نوع محصولی که در سلول A2 انتخاب شده است پیدا میکند. برای مثال با انتخاب RedFruit در سلول A2 کد محصول انتخاب شده را میگیرد – – mac سپس یک list به انتهای آن اضافه میکند و آیتم را در محدوده maclist نشان میدهد.

استفاده از محدوده های پویا

از آنجایی که فرمول indirect فقط با آدرس و نه فرمول  کار میکند، شیوه قبل که توضیح داده شده در مورد لیست هایی که از محدوده های دارای نام پویا بر اساس فرمول هایی مثل offset استفاده میکنند کارایی نخواهد داشت. در این موارد میتوانید از یکی از راه حل های زیر استفاده کنید.

جدول های دارای نام اکسل(بهترین گزینه)

راه حل نام پویا( در صورتی که نمیتوانید از جدول استفاده کنید)

جدول های دارای نام اکسل

برای جلوگیری از بروز مشکل، از جدول های دارای نام اکسل  به جای محدوده های پویای بر اساس فرمول ،استفاده کنید.

• یک جدول با آیتم های لیست خود ایجاد کنید.
• تمام آیتم ها در ستون جدول را انتخاب کنید(به جز عنوان)
• در قسمت name کلیک کنید و یک نام یک کلمه ای برای محدوده انتخاب نمائید. و کلید enter را بزنید.

سپس، یکی از توابع indirect که در بالا توضیح داده شد را برای ایجاد یک لیست کشویی وابسته مورد استفاده قرار دهید.

راه حل نام پویا

اگر قادر نیستید از جدول های دارای نام استفاده کنید، از شیوه زیر برای ایجاد لیست وابسته از محدوده های دارای نام پویا و بر اساس فرمول استفاده کنید.

1. به شیوه ای که در بالا توضیح داده شد محدوده دارای نام و لیست کشویی اول را ایجاد کنید.
2. لیست دارای نام پشتیبان را ایجاد کنید. و اولین سلول در هر محدوده را نامگذاری کنید. برای مثال، سلول b1 بعنوان fruit نامگذاری شده و سلول c1 ، vegetables قرار داده شده است.
3. هر ستون را که لیستی در آن قرار گرفته نامگذاری کنید. برای مثال ، ستون b ، FruitCol نامگذاری شده است و ستون c ، VegetablesCol قرار داده شده است.
4. برای کشوی دوم، Allow>list را انتخاب کنید و از فرمولی که محدوده جستجو را محاسبه کند ، استفاده کنید. برای مثال ، اگر اولین لیست کشویی در سلول e2 باشد :

=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1)

در صورتی که از آیتم های دو کلمه ای استفاده میکنید میتوانید از تابع substitute را درون فرمول خود قرار دهید:

=OFFSET(INDIRECT(SUBSTITUTE($F2,"","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)

منع تغییرات در اولین لیست کشو

برای اینکه مانع ایجاد تغییر در لیست اول گردید، میتوانید فرمول data validation خود را تغییر دهید. در نتیجه لیست تا زمانی که سلول دوم خالی نباشد ظاهر نمیشود.

منع تغییرات در لیست کشویی اول

زمانی که از لیست های وابسته استفاده میکنید . وقتی که کاربر به لیست اول برگردد و آن را تغییر دهد ممکن است مشکلاتی اتفاق بیفتد. در نتیجه گزینه انتخاب شده اول و دوم با هم مطابقت نداشته باشند. برای مثال در تصویر زیر، اولین انتخاب vegetable است و banana در ستون دوم انتخاب شده است . برای اینکه مانع ایجاد تغییر در لیست اول شوید، میتوانید فرمول data validation را تغییر دهید . در این صورت لیست کار نمیکند مگر اینکه سلول دوم خالی باشد.

به جای اشاره کردن به محدوده produce برای لیست محصول، فرمول وجود یک عنصر را در ستون item چک میکند.

• اگر سلول آیتم خالی باشد، لیست کشویی مربوط به نوع محصول ، لیست محصولات را نشان میدهد.
• اگر یک آیتم انتخاب شده باشد، لیست کشویی محدوده ای را که توسط تابع indirect ایجاد شده است نشان می دهد—- fake range . زیرا هیچ محدوده ای با این نام وجود ندارد نتیجه یک خطا خواهد بود و لیست کار نخواهد کرد.
• برای تغییر فرمول،:

1. سلول های data validation را در ستون اول انتخاب کنید.
2. در پنجره data، بر روی data validation کلیک کنید.
3. در قسمت Allow گزینه list را انتخاب کنید.
4. در قسمت فرمول، تایپ کنید:

=IF(C2="",Produce,INDIRECT("FakeRange"))

5. Ok را بزنید.

حال اگر آیتم در آن ردیف انتخاب شده باشد، لیست کشویی در ستون اول فعال نخواهد بود.

خالی کردن(clear) سلول وابسته بعد از انتخاب

در بخش قبل، یک فرمول ارائه شد که اگر سلول سمت راست یک مقدار داشت اجازه استفاده از انتخاب یک گزینه از لیست را نمی داد.

یک گزینه دیگر برای خالی کردن سلول پس از انتخاب یک گزینه از لیست، استفاده از ماکرو هاست. این کار از بروز انتخاب های غیر مطابق جلوگیری میکند.

در این مثال ستون B، یک لیست کشویی از مناطق را شامل میشود.پس از اینکه یک منطقه را انتخاب کردید، لیست موجود در ستون C مشتریان مربوط به آن منطقه را نشان میدهد.

برگردید و یک منطقه دیگر را انتخاب کنید، می بینید که نام مشتریان موجود پاک میشود.

استفاده از event code برای clear کردن سلول مشتریان

زمانی که یک منطقه را انتخاب میکنید، event code ها سلول های سمت راست را خالی میکنند. برای اضافه کردن کد ها بر روی پنجره sheet راست کلیک کنید و View Code را انتخاب کنید.

در sheet module کد های زیر را paste کنید. ممکن است نیاز باشد شماره ستون را تغییر دهید و آن را با ستون های کاربرگ خود جایگزین کنید.

نکته:

در ورک بوک نمونه کد هایی برای clear کردن چندین سلول وابسته نیز ارائه شده است.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Column = 2 Then

If Target.Validation.Type = 3 Then

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

End If

End If

exitHandler:

Application.EnableEvents = True

Exit Sub

End Sub

ساخت لیست های کشویی وابسته با استفاده از لیست دسته بندی شده

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

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

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

در این مثال، یک منطقه در ستون B انتخاب میشود، سپس فقط مشتریان مربوط به آن منطقه در ستون C نمایش داده میشود.

در این مطلب از تابع offset  برای استخراج آیتم ها از یک لیست دسته بندی شده، استفاده شده است.

یک شیوه دیگر این است که از محدوده های دارای نام و تابع indirect استفاده میکند. این شیوه زمانی کارایی دارد که تعداد کمی گزینه در ستون اول وجود داشته باشد.

آماده سازی ورک بوک

در ورک بوک نمونه دو ورک شیت وجود دارد.

1. عناصر داده های ورودی در شیت ValidationSample
2. لیست منطقه و مشتریان در شیت ValidationLists قرار دارد.

در شیت ValidationLists جداول، شامل لیست هایی هستند که در لیست های کشویی مورد استفاده قرار خواهند گرفت:

1. در سلول های A1:B20، لیستی از مناطق و مشتریان وجود دارد.

نکته: این لیست می بایست بر اساس مناطق دسته بندی شود.

2. در سلول های D1:D4، لیستی از مناطق وجود دارد.
3. هر دو لیست به صورت جداول دارای نام اکسل فرمت شده اند ( insert>table)

محدوده های زیر را نامگذاری کنید:

1. سلول A1 به نام RegionStart
2. سلول های A1:A20 به نام RegionColumn
3. سلول های B1:B20 به نام CustColumn
4. سلول های D2:D4 به نام RegionList

نکته:

اگر پس از انتخاب این نام ها، به قسمت name manager مراجعه کنید، مشاهده خواهید کرد که تمام این محدوده ها به صورت جدول ذخیره شده اند، به همین دلیل با اضافه کردن یا حذف ردیف ها به صورت اتوماتیک اصلاح خواهند شد.

ایجاد یک لیست کشویی منطقه ای

در شیت  ValidationSample، لیست های کشویی برای مناطق و ستون مشتریان ایجاد شده است.محدوده data entry نیز به صورت جدول است و به صورت اتوماتیک گسترش می یابد.

گام اول ، ایجاد لیست کشویی مناطق در سلول B2 است. در صورتی که هیچ مشتری انتخاب نشده باشد ، لیست به صورت کامل نشان داده میشود. اما، اگر یک مشتری در سلول کناری انتخاب شده باشد، فقط منطقه مربوط به آن مشتری در لیست مربوط به مناطق نشان داده میشود.

برای ایجاد لیست کشویی مناطق مراحل زیر را طی کنید:

• سلول B2 را انتخاب کنید، و پنجره data را باز کنید
• بر روی data validation کلیک کنید، تا قسمت مربوطه باز شود
• در لیست مربوط به allow، گزینه list را انتخاب کنید.
• در قسمت formula، فرمول زیر را تایپ کنید:

=IF(C2="",RegionList,INDEX(RegionColumn,MATCH(C2,CustColumn,0)))

• Ok را بزنید تا باکس مربوطه بسته شود.

یک منطقه را از لیست کشویی انتخاب کنید. هیچ مشتری در ستون C  انتخاب نشده است، بنابراین شما می بایست لیست مناطق را به صورت کامل مشاهده کنید.

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

گام بعدی، ایجاد لیست کشویی وابسته در ستون C است.

1. در سلول B2، گزینه east(شرق( را از لیست کشویی انتخاب کنید.( اگر سلول خالی گذاشته شود ، یک پیام خطا ارائه خواهد شد)
2. سلول C2 را انتخاب کنید، در ریبون، پنجره data را باز کنید.
3. قسمت data validation را باز کنید
4. از لیست allow، گزینه list را انتخاب کنید.
5. در قسمت formula، فرمول زیر را تایپ کنید:

=OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,B2),1)

6. تابع offset پارامتر های زیر را داراست:

ما از تابع offset برای برگرداندن آدرس سلول هایی استفاده میکنیم که شامل مشتریان منطقه east میشود.

Reference: در فرمول ما، این پارامتر، RegionStart یا همان سلول A1 در شیت ValidationLists است.

Row: محدوده ما از چند ردیف پایینتر از reference شروع میشود؟ تابع match اولین حالت east را در محدوده RegionColumn و در ردیف 8 پیدا میکند. ما 1 را از این عدد کم میکنیم ، زیرا سلول شروع، در ردیف 1 قرار دارد.

Cols: چند ستون بعد از نقطه شروع، در محدوده مورد نظر ما قرار میگیرد؟ 1 ستون بعد از reference تعیین شده،  مورد نظر ماست.

Height: محدوده مورد نظر ما می بایست چند ردیف داشته باشد؟ تابع countif تعداد تکرار منطقه مورد نظر در  RegionColumn را شمارش میکند. 6 مشتری در منطقه east وجود دارد.

Width: محدوده مورد نظر می بایست چند ستون داشته باشد؟ محدوده ما می بایست 1 ستون عرض داشته باشد.

7. Ok را بزنید .

آزمون اعتبار

1. سلول C2 را انتخاب کنید.
2. بر روی فلش لیست کلیک کنید
3. لیستی از مشتریان منطقه east نشان داده میشود.
4. یکی از مشتریان را انتخاب کنید.

5. گام بعد، سلول B2 را انتخاب کنید و بر روی فلش لیست کلیک کنید.

6. چون مشتری انتخاب شده است، در لیست فقط منطقه شرق است.

بررسی لیست مرتب سازی شده

این تکنیک بستگی دارد به لیست منبع که توسط ستون منطقه مرتب شده است. اگر لیست منطقه به صورت A-Z مرتب نشده باشد، یک فروشنده (customer) نادرست از لیست ممکن است نمایش داده شود. برای اینکه اطمینان حاصل کنید که به درستی مرتب شده است، فرمول های اختیاری زیر را دنبال کنید.

بررسی ترتیب مرتب سازی

در تیبل Region/customer، سه ستون با این فرمول، برای بررسی ترتیب مرتب سازی، اضافه شده است:

=IF(A3="",0,--(A3<A2))

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

مرتب سازی بررسی کل و پیام

دو سلول نام گذاری شده در شیت validationlists وجود دارد.

• Sortcheck: شامل یک فرمول برای محاسبه مجموع در ستون sortcheck است.

=SUM(tblRegCust[SortCheck])

• SortMsg: شامل یک پیام تایپ شده است که وقتی لیست به درستی مرتب نشده است، نشان داده میشود.

لیست کشویی مشتری

در شیت ValidationSample، فرمول اعتبار سنجی مشتری (customer data validation formula) کمی تغییر کرده است.

((IF(SortCheck>0, SortMsg, OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1=

• فرمول سلول SortCheck را بررسی میکند تا ببیند آیا آن بزرگتر از صفر است.
• اگر چنین است، به جای نام مشتریان، RangeMsg محدوده نشان داده میشود.

برای دیدن لیست به شیت ValidationList بروید و جدول را بر اساس ستون Regions مرتب کنید.

سپس بر روی پیکان کلیک کنید تا مشتریان منطقه مورد نظر را مشاهده کنید.

مطالب آموزشی دیگر در مورد Data Validation:

ایجاد لیست های کشویی در اکسل (Data Validaion)

ساخت لیست های کشویی وابسته و چند سطحی در اکسل

ساخت لیست های وابسته در اکسل با استفاده از index

بعنوان جایگزین استفاده از indirect برای ساخت لیست های کشویی وابسته، میتوان از تابع index برای این کار استفاده کرد.

• مقدمه
• ساخت لیست های دارای نام
• ساخت محدوده های پویا
• تنطیم data validation برای ستون A در شیت data entry
• تنطیم data validation برای ستون های دیگر در شیت data entry
• شیت data entry پایان یافته با داده های نمونه
• گسترش ستون های مربوط به لیست های شیت
• مطالب مرتبط

مقدمه

در این مثال، تنها 4 محدوده پویا مورد استفاده قرار گرفته است. از این 4 مورد، 3 مورد برای ساخت چارچوب اصلی  این شیوه مورد  استفاده قرار گرفته است.

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

ساخت لیست های دارای نام

1. در یک صفحه کاری (ورک بوک) جدید، شیت 1 را تغییر نام دهید و نام این شیت را dataentry قرار دهید.
2. نام شیت 2 را lists قرار دهید.
3. عنوان سلول های A1:D1 در شیت dataentry را به ترتیب Region، Country، Area و City قرار دهید.

Data Intry Sheet

4. در شیت lists، در سلول A1 عنوان regions را وارد کنید.
5. در سلول های A2:A5، عناوین APAC، MEA، Europe و Americas را وارد کنید.

List Sheet

ساخت محدوده های پویا

به شیت lists بروید و به صورتی که در زیر توضیح داده شده است 4 محدوده پویای دارای نام مورد نیاز را ایجاد کنید.

• Master: که برای validation در ستون A شیت data entry مورد استفاده قرار میگیرد.
• Valdata: محدوده ای را که در شیت lists، validation data را تعریف میکند، مشخص میکند
• Counter: برای مشخص کردن طول هر لیست مورد استفاده قرار میگیرد.
• Uselist: برای تمام ستون های شیت data entry مجزا از ستون a که می بایست در آنها از data validation استفاده شود، به کار گرفته میشود

ساخت اولین محدوده پویا

اولین لیست ما master نام دارد.

1. دستور Insert | Name | Define را وارد کنید.
2. یک نام برای محدوده انتخاب کنید – master
3. در باکس refer to، یک فرمول index وارد کنید که بر اساس شمارش آیتم ها در ستون A شیت lists،  اندازه محدوده را مشخص کند:

=Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))

 

4. با این کار لیستی ایجاد خواهد شد که از سلول A2 شروع میشود و تا جایی که ما عناصر جدید را وارد کنیم ، ادامه می یابد.

اضافه کردن عناصر و عنوان های بیشتر به لیست ها

نام تمام محدوده ها را بعنوان، عنوان در سلول های B1:E1 شیت lists وارد کنید.

سپس، در زیر هر عنوان ، یک یا چند کشور از آن منطقه را وارد کنید.(تصویر زیر را مشاهده کنید)

ایجاد محدوده پویای valdata

در شیت lists، محدوده داده ای وجود دارد که برای تمام validation های شیت data entry مورد استفاده قرار میگیرد. به جای اشاره به نام شیت بعنوان محدوده این داده ها، که در صورت اضافه کردن validation رشد میکند، یک محدوده پویا ایجاد کنید.

1. دستور Insert | Name | Define را وارد کنید.
2. یک نام برای محدوده انتخاب کنید— valdata
3. در باکس refers to، یک فرمول index وارد کنید که اندازه محدوده را مشخص میکند، برای مثال 100 ردیف و تعدادی ستون را انتخاب میکند:

=Lists!$A$2:INDEX(Lists!$1:$100,100,COUNTA(Lists!$1:$1))

 

4. محدوده ای پویا ایجاد خواهد شد که از سلول A2 شروع میشود و به اندازه 100 ردیف و هر تعداد عنصر که در ردیف 1 وارد کنیم، گسترش می یابد.
5. دکمه add را بزنید

یک شمارش کننده ایجاد کنید.

شمارش کننده متغیری است که طول هر لیست را مشخص میکند. این متغیر پویا است و بر اساس اینکه کدام ستون مورد استفاده قرار میگیرد،ستونی که توسط تابع match برگردانده میشود، تغییر میکند. یک محدوده پویا برای شمارش کننده ایجاد میشود.

نکته: محدوده ، نسبی است، و به سلول سمت چپ اشاره میکند(A2) تا مقدار آن را مشخص کند. بنابراین حتما می بایست در زمان تعریف محدوده، نشانه گر ماوس را در سلول B2 شیت dataentry قرار دهید.

1. در شیت data entry، در سلول A2 ، کلمه Europe را تایپ کنید (این یک مقدار موقتی است و میتوانید آن را بعداً حذف کنید)
2. در شیت data entry، سلول B2 را انتخاب کنید.
3. دستور Insert | Name | Define را انتخاب کنید.
4. یک نام برای محدوده انتخاب کنید—counter
5. در پنجره refers to، یک فرمول index وارد کنید، که اندازه محدوده را بر اساس تعداد اعداد در ستون مربوطه ، مشخص میکند:

=COUNTA(INDEX(ValData,,MATCH('Data Entry'!A2,Lists!$1:$1,0)))

 

6. دکمه add را بزنید.

ساخت محدوده پویای uselist

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

با اضافه شدن ستون های جدید به شیت lists، محدوده مربوط به آن ها، به صورت اتوماتیک و پویا ساخته میشود.و در تمام محدوده های validation، از uselist بعنوان منبع  داده استفاده میشود.

نکته: از آنجایی که محدوده نسبی است، و به سلول سمت چپ(A2) برای تعیین مقدار آن اشاره میکند می بایست حتما در زمان تعریف محدوده نشانه گر موس را در سلول B2 شیت data entry قرار دهید.

1. نشانه گر را در سلول B2 شیت dataentry قرار دهید
2. دستور Insert | Name | Define را انتخاب کنید.
3. یک نام برای محدوده انتخاب کنید—uselist
4. در باکس refers to، فرمول زیر را وارد کنید:

=INDEX(ValData,1,MATCH('Data Entry'!A2,Lists!$1:$1,0)): INDEX(ValData,Counter,MATCH('Data Entry'!A2,Lists!$1:$1,0))

 

5. دکمه Add را بزنید.

چرا مکان نشانه گر ماوس مهم است.

• قبلا، محدوده valdata بعنوان محدوده ای که از ردیف دو شیت lists شروع میشود، تعریف شده است.
• فرمول uselist، محدوده valdata را index میکند، به این صورت که یک مقدار ثابت برای عنصر ردیف شروع، در قسمت اول فرمول و یک مقدار پویا برای عنصر ردیف پایانی در قسمت دوم فرمول قرار میدهد.
• فرمول در هر دو قسمت خود، یک مقدار نسبی برای عنصر ستون(column) تابع index، دارد.
• در قسمت اول، ردیف بوسیله (Valdata,1, …) بعنوان 1 در نظر گرفته میشود، در نتیجه به ردیف 1 valdata اشاره خواهد کرد( در نتیجه ردیف 2 شیت lists)
• در قسمت دوم ردیف توسط counter(شمارش کننده) در (Valdata,Counter, …) مشخص میشود، و Counter قبلا تعریف شده است و مقدار آن بر اساس تعداد عناصر ورودی validation list برای ستون نسبی متفاوت است.
• Column توسط مقدار مطابقی(match) که در سلول سمت چپ سلول استفاده کننده از این محدوده پویا قرار دارد، مشخص میشود.MATCH(‘Data Entry’!A2,Lists!$1:$1,0) (همراه با عنوان لیست که در ردیف 1 شیت lists قرار دارد)

این، دلیل اهمیت قرار دادن نشانه گر در سلول شروع شونده صحیح، در زمان ایجاد محدوده های دارای نام است)

تنظیم validation cells، برای ستون A در شیت data entry

حال که محدوده های پویا را تعریف کردید، گام بعدی این است که محدوده های validation را در شیت data enrty مشخص کنید.

1. در شیت Data Entry، سلول های a2:A12 را انتخاب کنید( هر تعداد ردیف که نیاز دارید)
2. Data/validation را انتخاب کنید.

3. Allow/list را انتخاب کنید.

4. در قسمت source، =master را تایپ کنید

5. Ok را بزنید

مشخص کردن validation cells – دیگر ستون ها

گام بعدی مشخص کردن محدوده های validation باقیمانده در شیت data entry است

1. در شیت Data Entry ، سلول های B2:D12 را انتخاب کنید (یا هر تعداد ردیف که نیاز دارید)
2. Data/validation را انتخاب کنید.
3. Allow/list را انتخاب کنید.
4. در قسمت source، =uselist را تایپ کنید
5. Ok را بزنید

شیت dataentry کامل

گسترش ستون های شیت lists

شما میتوانید هرچقدر که مایل باشید به شیت lists ستون اضافه کنید. هر ستون می بایست یک عنوان داشته باشد که این عنوان می بایست عضوی از یکی از ستون های درون شیت باشد، تا در نتیجه ستون جدیدی که ایجاد میشود به آن عنوان وابسته باشد.

برای مثال، میتوانید بک عنوان us اضافه کنید، و لیستی از ایالت ها را در آن ستون قرار دهید. سپس ، میتوانید نام ایالت ها را بعنوان عنوان در ردیف 1 قرار دهید و برای هر ایالت یک لیست از شهرها اضافه کنید.

مطالب مرتبط

ایجاد لیست های کشویی در اکسل (data validation)

ساخت لیست های کشویی وابسته و چند سطحی اکسل

ساخت لیست های کشویی وابسته با استفاده از لیست دسته بندی شده

لیست های کشویی از طریق داده یک ورک بوک دیگر

چگونگی پنهان سازی آیتم های استفاده شده در لیست های کشویی

مثال هایی در مورد معیارهای data validation