نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، گرافیک و فناوری اطلاعات فعالیت دارم.

  • ایجاد لیست کشویی وابسته توسط ابزار Data Validation

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

فرض کنید می‌خواهیم به کمک فرمول‌نویسی با انتخاب هر استان، در سلول مجاور آن فقط شهرستان‌های مربوط به آن استان نمایش داده شود. برای این‌کار در شیت Sheet2 نام استان‌ها و شهرستان‌های مورد نظر را مطابق با تصویر 1 ایجاد می‌کنیم.

محدوده نام استان‌ها و شهرستان‌ها در شیت Sheet2

محدوده نام استان‌ها و شهرستان‌ها در شیت Sheet2

نام‌گذاری محدوده‌ها

اولین گام برای ایجاد لیست کشویی وابسته نام‌گذاری محدوده‌ها می‌باشد. می‌خواهیم یک نام برای استان‌ها و برای شهرستان‌های هر استان یک نام تعریف کنیم. محدوده A1:D1 را انتخاب کرده و به مسیر زیر می‌رویم. در قسمت Name یک نام به دلخواه برای آن در نظر گرفته و به روی دکمه Ok کلیک می‌کنیم.

  • Formulas
  • Defined names
  • Define Name

نام‌گذاری محدوده نام استان‌ها

نام‌گذاری محدوده نام استان‌ها

بیشتر بخوانید: رده‌بندی مقادیر عددی

برای نام‌گذاری شهرستان‌های هر استان محدوده A1:D6 را انتخاب کرده و به مسیر زیر می‌رویم. گزینه Top row را انتخاب کرده و به روی دکمه Ok کلیک می‌‌کنیم. با این روش همه شهرستان‌ها به تفکیک استان نام‌گذاری می‌شوند.

نام‌گذاری شهرستان‌ها به تفکیک استان

نام‌گذاری شهرستان‌ها به تفکیک استان

بنابراین در پنجره Name Manager باید یک نام برای استان‌ها و 4 نام برای شهرستان‌های هر استان داشته باشیم.

محدوده‌های نام‌گذاری شده در Name Manager

محدوده‌های نام‌گذاری شده در Name Manager

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

می‌خواهیم یک لیست کشویی به کمک ابزار Data Validation برای استان‌ها در نظر بگیریم. برای این‌کار در شیت Sheet1 سلول A2 را انتخاب کرده و به مسیر زیر می‌رویم. در قسمت Allow گزینه List را انتخاب کرده و در قسمت Source نام محدوده نام‌گذاری شده برای استان‌ها را بعد از یک = وارد کرده و به روی دکمه Ok کلیک می‌کنیم.

  • Data
  • Data Tools
  • Data Validation
  • Settings

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

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

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

=INDIRECT(A2)

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

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

بیشتر بخوانید: خطاهای فرمول‌نویسی

تابع Indirect به صورت غیرمستقیم به محدوده یا نام محدوده اشاره می‌کند. مثلا این تابع بررسی می‌کند در سلول B2 چه مقداری قرار دارد. چون این سلول شامل یک محدوده نام‌گذاری شده است، بنابراین این محدوده را در لیست قرار می‌دهد.

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

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

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

می‌توان لیست‌ها را به صورت پویا (Dynamic) بروزرسانی کرد. داینامیک‌سازی لیست کشویی به این معنی است که مثلا به محض اضافه کردن یک شهر جدید برای هر استان، لیست مورد نظر به صورت خودکار بروزرسانی شود. برای این‌کار باید بوسیله جدول محدوده‌ها را نام‌گذاری کنیم. ابتدا محدوده شهرهای هر استان را تبدیل به جدول کرده و نام هر جدول را برابر با نام هر استان قرار می‌دهیم.