در این مطلب با ایجاد لیست های کشویی در اکسل (data validation) آشنا میشویم. شما میتوانید با استفاده از ویژگی data validation اکسل لیست های کشویی ایجاد کنید. این کار ورود داده را آسانتر میکند و خطاهای املایی و نگارشی را کاهش میدهد. دستورالعمل گام به گام و ویدئوی آموزشی در زیر آمده است:

Data validation چیست؟

در اکسل، ویژگی Data validation به شما امکان میدهد چیزی را که در ورک شیت شما وارد میشود کنترل کنید.  برای مثال ، شما میتوانید کارهای زیر را انجام دهید :

• ایجاد یک لیست کشویی از آیتم ها در یک لیست
• محدودیت عناصر ورودی، برای مثال محدوده تاریخ یا ورود عدد کامل
• قوانینی را برای عناصری که می بایست وارد شود ایجاد کنید.

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

ایجاد یک لیست کشویی در یک سلول

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

نحوه ایجاد یک لیست کشویی

با استفاده از data validation میتوانید یک لیست در یک سلول ایجاد کنید. برای این کار می بایست سه مرحله ساده زیر را انجام دهید.

1. جدولی از آیتم ها را ایجاد کنید یا یک لیست ایجاد کنید.
2. یک نام برای لیست قرار دهید.
3. کشویی را ایجاد کنید

نکته:

Data validation محفوظ از خطا نیست. برای جلوگیری از خطا می توان داده ها را درون سلول paste کرد. یا در پنجره home ، دستور  Clear > Clear All را انتخاب کنید.

1. ایجاد یک جدول آیتم

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

نکته:

یک لیست data validation میتواند تا 32767 آیتم را از یک لیست درون ورک شیت فراخوانی کند.

1. یک عنوان برای لیست تایپ کنید. – در این مثال از Employeesاستفاده شده است.

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

3. یک سلول را در لیست انتخاب کنید و در پنجره insert بر روی table کلیک کنید.

4. چک مربوط به “My table has Headers” را فعال کنید و ok را بزنید

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

2. یک نام برای لیست قرار دهید.

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

استفاده از Name manager

1. یکی از سلول های لیست نام Employees را انتخاب کنید.

2. روی تب Formulas کلیک کنید و سپس از گروه Defined Names بر روی Defined Name کلیک کنید.

3. در پنجره ای که باز شد یک نام برای محدوده ای که انتخاب کرده اید انتخاب کنید. برای مثال: EmpNames

4. Scope را بگذارید همان workbook باشد.

5. در قسمت refers to کلیک کنید. (در ورک شیت، سلول (سلولهایی) که در کارد refers to ذکر شده است، هایلات میشود.)

6. برای انتخاب همه نام ها در لیست (برای انتخاب همه نام ها، به جز سلول عنوان)، به بالای سلول عنوان (B1) بروید، و وقتی یک فلش سیاه ظاهر شد، کلیک کنید (وقتی مطمئن شدید که فلش در سلول B1 است کلیک کنید، نه زمانی که در قسمت عنوان ستون B است.)

7. نام تیبل و نام ستون در قسمت refers to ظاهر میشود.

=Table1[Employees]

8. برای کامل شدن نام، بر روی OK کلیک کنید.

استفاده از Name box

این مورد راه سریعی برای نام گذاری محدوده ای از سلول ها است.

1. برای انتخاب تمام سلول های موجود در لیست، در بالای سلول عنوان کلیک کنید. (عنوان انتخاب نخواهد شد.). در تصویر دوم به علامت فلش دقت کنید.

2. روی Name box در سمت چپ formula bar کلیک کنید.

3. یک نام یک کلمه ای مثلاً « EmpNames» برای محدوده خود انتخاب کنید، سپس کلید Enter را فشار دهید تا نام کامل شود.

نکته: بعد از اینکه کلید Enter را فشار دهید، نام ناپدید میشود و نام Table در name box ظاهر میشود.

3. ایجاد لیست کشویی

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

1. سلول هایی را که میخواهید در لیست قرار بگیرند انتخاب کنید

2. در پنجره data بر روی data validion کلیک کنید.

3. از لیست Allow گزینه list را انتخاب کنید.

4. در قسمت source کلیک کنید و یک علامت مساوی تایپ کنید. برای مثال، =EmpNames

یا کلید f3 را فشار دهید تا لیستی از نام ها را مشاهده کنید. بر روی یک نام کلیک کنید و ok را بزنید.

5. ok را بزنید تا قسمت مربوط به data validation بسته شود.

6. بر روی یکی از سلول ها کلیک کنید و فلش کنار سلول را فشار دهید.

7. بر روی یکی از عناصر لیست کلیک کنید تا در سلول وارد شود.

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

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

به جای اشاره به یک سری از آیتم های موجود در ورک شیت میتوانید تمام عناصری را که می خواهید درون لیست قرار بگیرد به صورت دستی در درون قسمت Source تایپ کنید و بین هرکدام از عناصر یک کاما قرار دهید.برای مثال

Yes,No,Maybe

نکته ها:

1. این نوع از data validation نسبت به حروف بزرگ و کوچک حساس است . مثلاً اگر کاربر مقدار YES را در سلول تایپ کند با خطا مواجه خواهد شد.
2. میتوان قبل و بعد از آیتم ها فاصله قرار داد . برای مثال ” Yes   ” هیچ خطایی برگردانده نخواهد شد.

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

برای ایجاد امکان ورود داده هایی که در لیست وجود ندارد برای کاربر،چک مربوط “show error alert after invalid data is entered” را بردارید.

ساخت لیست های کشویی با استفاده از یک صفحه کاری دیگر

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

دستورالعمل مربوطه در زیر توضیح داده شده است.

لیست های کشویی با استفاده از لیست یک صفحه کاری دیگر

نکته مهم: برای اینکه data validation شما کار کند می بایست ورک بوکی که حاوی لیست آیتم هاست باز باشد.کاربران می بایست هر دو صفحه کاری را باز کنند – هم صفحه کاری مربوط به لیست کشویی و هم صفحه کاری مربوط به لیست آیتم ها.

ساخت لیست منبع

در این مثال، یک ورک که DataValWb.xlsx نام دارد شامل لیستی از مشتریان است. لیست مشتریان در محدوده ای قرار دارد که CustName نامیده شده است.

ساخت یک آدرس دارای نام برای لیست منبع

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

1. صفحه کاری را که شامل لیست منبع است باز کنید.
2. صفحه کاری را که میخواهید از لیست مورد نظردر آن استفاده کنید، باز کنید یا یک ورک بوک جدید ایجاد کنید.
3. در ریبون، پنجره formula را باز کنید و بر روی define name کلیک کنید.

4. یک نام برای لیست تایپ کنید. برای مثال، MyCustList

5. از لسیت کشویی scope گزینه workbook را انتخاب کنید.

6. داخل باکس refers to کلیک کنید- این همان جایی است که می بایست آدرس محدوده دارای نام را در ورک بوک منبع ذکر کنید.

7. یک علامت مساوی تایپ کنید و سپس نام محدوده دارای نام مورد نظر را قرار دهید. از براکت استفاده نکنید. برای مثال:

=DataValWb.xlsx

 

8. در صورتی که نام ورک بوک دارای فاصله است، در ابتدا و انتهای نام از علامت (‘) استفاده کنید. برای مثال،

‘DataVal May10.xlsx’

9. سپس، یک علامت تعجب قرار دهید — !
10. در آخر، نام محدوده را در ورک بوک منبع تایپ کنید.
11. آدرس کامل می بایست به شکل زیر باشد

=DataValWb.xlsx!CustName

 

یا

’DataVal May10.xlsx’!CustName

 

12. Ok را بزنید.

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

1. سلول هایی که میخواهید برای آنها از data validation استفاده کنید را انتخاب کنید.
2. در ریبون، پنجره data، را باز کنید و گزینه data validation را انتخاب کنید.
3. در باکس Allow، گزینه list را انتخاب کنید.
4. در قسمت source، یک علامت مساوی قرار دهید، سپس نام لیستی را که ایجاد کرده اید تایپ کنید – برای مثال، MyCustList=
5. Ok را بزنید.

استفاده از لیست data validation

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

1. هر دو ورک بوک را باز کنید.—ورک بوک شامل لیست اصلی و منبع و ورک بوکی که لیست کشویی در آن قرار گرفته است.
2. یک آیتم از لیست کشویی data validation استفاده کنید.

مطالب آموزشی دیگر پیرامون لیست های کشویی (DATA VALIDATION):

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

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

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

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

مقدمه

با تشکر از متخصص اکسل قبل، Peo Sjoblom که فرمول اصلی را طراحی کرد و همچنین تشکر میکنیم از متخصص فعلی اکسل، Daniel.M که فرمول را ارتقاء داد.

شما میتوانید با مخفی کردن گزینه هایی که قبلاً انتخاب شده است، انتخاب ها را در لیست های کشویی محدود کنید. برای مثال اگر کارکنان را به یک شیفت کاری تخصیص میدهید، نمیخواهید که یک کارمند را دوبار به یک شیفت اختصاص دهید.

در لیست کشویی که در زیر آمده است، fred و jooe تخصیص داده شده اند. در نتیجه نام آنها در لیست قرار نمیگیرد. دستورالعمل ساخت چنین لیستی در زیر توضیح داده شده است.

تنظیم جدول اصلی

برای ساخت ورک بوک مربوط به شیفت کاری کارکنان، ابتدا جدولی را آماده کنید که میخواهید از آن در data validation استفاده کنید. در این مثال، ورک شیت مورد نظر “schedule” نام دارد و محدوده استفاده شده A1:C7 است.

عملیات data validation در ستون B اعمال خواهد شد.

ساخت لیست آیتم ها

لیستی از آِتم هایی که میخواهید در لیست کشویی data validation مشاهده شود، را ایجاد کنید. در اینجا، نام کارکنان در سلول های A1:A16 در شیتی به نام “employees” قرار داده شده است.

مشخص کردن نام های استفاده شده

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

1. در شیت employees ، در سلول B1، فرمول زیر را وارد کنید:

=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())

 

2. فرمول را به سمت پایین و تا آخرین نام کپی کنید.

این فرمول وقوع “bert” را در سلول های B2:B7 شمارش میکند. اگر تعداد تکرار بیشتر یا برابر با 1 باشد، سلول خالی نمایش داده میشود. در غیر اینصورت شماره ردیف نشان داده میشود.

ساخت لیست نام های استفاده نشده

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

1. برای لیست های بلند، میتوانید از فرمول آرایه ای چند سلولی استفاده کنید.
2. برای محدوده های کوچکتر(کمتر از 200 سلول)، از فرمول تک سلولی استفاده کنید که اصلاح آن نیز ساده تر است.

راه حل 1: فرمول چند سلولی

1. سلول های C1:C6 را انتخاب کنید.
2. فرمول آرایه زیر را وارد کنید (فرمول طولانی است و می بایست تمام آن در یک خط قرار گیرد)

=IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"", INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6))))))

 

3. کلیدهای Ctrl+Shift+Enter را وارد کنید.

راه حل 2. فرمول تک سلولی

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

1. سلول C1 را انتخاب کنید.
2. فرمول زیر را وارد کنید (فرمول طولانی است و می بایست در یک خط نوشته شود)

=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

 

3. دکمه enter را فشار دهید.
4. فرمول را تا ردیف 6 به سمت پایین کپی کنید.

نام گذاری لیست نام های استفاده نشده

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

1. در پنجره formula، گزینه define name را انتخاب کنید.
2. در قسمت names، باکس workbook یک نام یک کلمه ای برای محدوده تایپ کنید برای مثال NameCheck
3. در باکس scope، Workbook را انتخاب کنید.
4. در باکس refers to، فرمول زیر را وارد کنید:

=OFFSET(Employees!$C$1,0,0,COUNT(Employees!$B$1:$B$6),1)

5. Ok را انتخاب کنید.

تابع offset محدوده ای را ایجاد میکند که از سلول C1 شروع میشود. تعداد ردیف در این محدوده بر اساس تعداد اعداد در سلول های B1:B6 است.

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

سپس، از ویژگی data validation اکسل استفاده میکنیم

1. در شیت schedule سلول های b2:b7 را که در آن نام کارکنان وارد شده است، انتخاب کنید.
2. در پنجره data، گزینه data validation را انتخاب کنید
3. از لیست کشویی Allow، گزینه list را انتخاب کنید.
4. در باکس source، یک علامت مساوی قرار دهید و نام لیست را تایپ کنید.
5. Ok را بزنید.

آزمون لیست کشویی

یک نام کارمند را از سلول B2 انتخاب کنید، یک نام دیگر را نیز از سلول B3 انتخاب کنید.

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

مثال های دیگر

مثال های دیگری نیز از این تکنیک وجود دارد.

بازیکنان بیس بال

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

در تصویر زیر، Mike بعنوان Pitcher (پرتاب کننده) انتخاب شده است. بنابراین نام او در لیست قرار نمیگیرد. بازیکنانی که برای بازی دوم انتخاب شده اند، هنوز در لیست مربوط به بازی اول قابل مشاهده و انتخاب هستند.

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

از این تکنیک برای کار با لیست های وابسته نیز میتوان استفاده کرد.در تصویر زیر، یک type میتواند چندین بار در ستون A انتخاب شود. با اینحال، در ستون B، هرکدام از آیتم ها را میتوان یکبار انتخاب کرد. در سلول B4، آیتم SI-02 ، در لیست si نیست، زیرا در سلول B2 انتخاب شده است.

انتخاب در یک ردیف

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

مخفی کردن گزینه های اصلی

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

در شیت lists، یک محدوده دارای نام وجود دارد —primarylist (سلول های سبز). یک فرمول در ستون notused وجود دارد که تمام پرینتر ها را شماره گذاری میکند به جز پرینتری که بعنوان پرینتر اصلی انتخاب شده است.

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

مطالب آموزشی دیگر پیرامون لیست های کشویی (DATA VALIDATION):

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

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

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

COUNTIF($l$4:$l$27,l4)=0=