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

ازجمله روش‌ها جهت انتخاب و ویرایش ساده داده‌ها، می‌توان به موضوع لیست کردن اطلاعات اشاره کرد که نقش بسزایی در مدیریت و استفاده از اطلاعات ذخیره‌شده دارد.

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

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

مطالبی که در طول این پست آموزش می‌بینیم:

• ایجاد لیست کرکره‌ای یا آبشاری
• کار با ابزار Name Manager
• کار با ابزار Conditional Formatting
• کار با تابع AND
• کار با تابع IF
• کار با تابع INDIRECT
• ترفند استفاده از فونت‌های بصری یا آیکونی

نکات کلیدی این آموزش:

• تابع INDIRECT
• نام‌گذاری محدوده و تکنیک آدرس‌دهی سلول

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

مانند شکل ۱:

شکل ۱- نمونه نهایی لیست کشویی وابسته

گام اول: لیست کردن نام کلاس‌ها

سلول C2 را انتخاب کرده سپس به مسیر زیر بروید:

Data Tab > Data Tools Group > Data Validation

که پنجره زیر ظاهر می‌شود

شکل ۲- پنجره Data Validation برای انتخاب لیست کشویی کلاس‌ها

بر اساس شکل فوق، تنظیمات را انجام دهید به این صورت که محدوده $B$6:$E$6 (محدوده عنوان کلاس‌ها) را جهت لیست شدن در سلول C2 آدرس‌دهی کنید.

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

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

نام گذاري محدوده ها در این روش به دو صورت امکان پذیر است:

روش اول: برای نام گذاری محدوده کلاس‌ها مسیر زیر را بروید:

Formulas Tab > Defined Names Group > Name Manager

یا کلید ترکیبی Ctrl+F3 را فشار دهید تا پنجره Name Manager ظاهر شود.

شکل ۳- پنجره Name Manager

دکمه New را بزنید تا پنجره New Name ظاهر شود.

شکل ۴- پنجره New Name برای ایجاد یک نام به اسم کلاس A

A: مطابق شکل دقیقا نام کلاسی را که در لیستتان نوشته‌اید را در این کادر بنویسید.توجه کنید که بین نام از خط فاصله استفاده نکنید.

B: در این کادر محدوده‌ی لیستی که مربوط به کلاس A می‌باشد را انتخاب کنید که اینجا محدوده ما B7:B17 می‌باشد.

C: بعد از زدن OK دوباره به پنجره Name Manager هدایت می‌شوید. حال می‌بینید که نامگذاری محدوده شما به لیست اضافه شده است.

حال دوباره با زدن دکمه New دقیقا همین کار را برای سه کلاس B,C,D هم انجام دهید با این تفاوت که اینبار در قسمت B وA نام و محدوده مربوط به همان کلاس را انتخاب کنید.

بعد از اتمام این کار شما باید چهار کلاس با نام‌های کلاسA ، کلاسB ، کلاسC و کلاسD در لیستتان داشته باشید.

مطابق شکل ۵:

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

Formulas Tab > Defined Names Group > Create From Selection

در فرم ظاهر شده تنها تیک گزینه Top Row را زده و OK می‌کنیم.

خواسته شما انجام شد، می‌توانید لیست محدوده‌های نام‌گذاری شده را از قسمت Name Manager مشاهده کنید. (شکل ۵)

گام سوم: ایجاد لیست وابسته به کلاس برای دانش‌آموزان

جهت اتصال لیست دانش‌آموزان هر کلاس به کلاس خود، ابتدا مسیر زیر را بروید:

Data Tab > Data Tools Group > Data Validation

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

=INDIRECT($C$2)

شکل ۶- ایجاد لیست کشویی وابسته به نام کلاس

سپس OK را می‌زنیم.

حال اگر شما نام کلاس را تغییر دهید خواهید دید که لیست دانش‌آموزان نیز متناسب با آن تغییر خواهد کرد.

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

حال قصد داریم زمانی که مثلا نام کلاسC از لیست انتخاب شد فقط دانش‌آموزان همان کلاس بصورت هایلایت نشان داده شوند.

برای اینکار ابتدا محدوده B7:E7 را انتخاب و مسیر زیر را بروید:

Home tab > Styles Group > Conditional Formatting > Manage Rules…

در پنجره باز شده گزینه New Rule را بزنید.

شکل ۷- استفاده از Conditional Formatting برای رنگی کردن ستون کلاس با انتخاب کلاس از لیست

مطابق شکل مرحله 1 و 2 را انجام داده و گزینه Format را فشار دهید تا پنجره Format Cells باز شود. به تب Fill رفته و یک رنگی به دلخواه انتخاب کنید که ما اینجا رنگ سبز را انتخاب کردیم. در آخر OK را بزنید. دوباره OK را بزنید.

مطابق شکل ۸:

شکل ۸- انتخاب رنگ برای Conditional Formatting

حال شما می‌بینید که رنگ‌بندی شرطی شما به لیست اضافه شده است. (شکل ۹)

شکل ۹- اضافه شدن یک شرط Conditional Formatting به

OK را بزنید تا کلا پنجره بسته شود.

اکنون بروید و نام کلاس را تغییر دهید.خواهید دید که محدوده مربوط به آن کلاس به رنگ سبز درخواهد آمد. (شکل ۱۰)

شکل ۱۰- نتیجه خروجی بعد از اعمال اولین Conditional Formatting

گام پنجم: رنگی کردن نام دانش‌آموزان بصورت داینامیک

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

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

برای اینکار ابتدا محدوده B7:E7 را انتخاب و مسیر زیر را بروید:

Home tab > Styles Group > Conditional Formatting > Manage Rules…

حال مطابق شکل ۱۱ مراحل را به ترتیب انجام داده و فرمول ذیل را نوشته و در آخر OK را بزنید.

=AND(B$6=$C$2,B7=$C$4)

شکل ۱۱- ایجاد شرط دوم در Conditional Formatting برای رنگی کردن اسم دانش آموز

اکنون مشاهده می‌کنید که رنگ‌بندی شرطی شما به لیست اضافه شده است.

شکل ۱۲- نمونه نهایی Conditional Formatting

دوباره OK را بزنید.

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

شکل ۱۳- محل قرار گرفتن هر شرط در Conditional Formatting

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

جهت ایجاد لیست‌های کشویی وابسته روش‌های متنوعی وجود دارد. ما در اين پست يكي از روش‌هاي ايجاد ليست‌كشويي وابسته بصورت غير پويا (استاتيك) را بررسي كرديم.
سعي داريم در پست هاي آينده روش‌هاي ديگري نيز به شما آموزش دهيم. شما نیز می‌توانید با معرفی روش‌هایی که در این مورد می‌دانید در با ارسال کامنت ما را در این مسیر همراهی کنید.

منابع

[1] exceljet

[2] ablebits