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

فهرست‌های کشویی یا آبشاری (Drop-Down Lists) یکی از پرکاربردترین ابزارها در اکسل هستند. با ساختن این لیست‌ها، کاربران مجبور نیستند اطلاعات مورد نظر خود را دستی وارد کنند و می‌توانند آن را از یک لیست از پیش تعریف شده انتخاب نمایند.

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

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

آنچه در این آموزش می‌خوانید:

آموزش ساخت لیست کشویی در اکسل

چگونه یک لیست کشویی ساده بسازیم؟

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

1. ابتدا و قبل از هر کار دیگری اکسل را باز کنید. دو شیت بسازید. نام یکی را wks و نام دیگری را lists بگذارید.

2. نام 10 دکتر (یا هر چیزی که مد تظر خودتان هست) را در سلول‌های A1 تا A10 بنویسید.

3. لیست را آنطور که دوست دارید بنویسید؛ اگر دوست دارید نام و نام خانوادگی به‌ترتیب باشند، به همین شکل وارد کنید و اگر دوست دارید ابتدا نام خانوادگی و سپس نام نمایش داده شوند، با همین فرمت لیست خود را بنویسید.

4. سلول‌های A1 تا A10 را گرفته (هایلات کنید) و با فشردن کلید ترکیبی Ctrl+T آن را به یک جدول تبدیل کنید. اکسل، نام جدول‌ها را به‌ترتیب شماره‌گذاری می‌کند (Table 1, 2, 3). حتما توجه کنید که پیام My Table Has Header در کنار جدول شما نمایش داده شود.

اما چرا ما لیست خود را به صورت جدول درآوردیم؟ پاسخ ساده است؛ اگر منبع لیست کشویی شما یک جدول باشد، با هر گونه تغییر در جدول، لیست شما هم آپدیت می‌شود و از تغییرات انجام شده جا نمی‌ماند.

5. به شیت 1 که نام آن را wks گذاشتید بروید و یک لیست دیگر از چیزی که می‌خواهید (مثلا تاریخ حضور دکتر‌ها، دستیاران دکترها و نوع تخصص دکترها و هر چیز دیگری) را وارد کنید.

6. سلول و یا سلول‌هایی را که دوست دارید لیست آبشاری در آن‌ها نمایش داده شود را انتخاب کنید. مثلا در فایل ما می‌توانید سلول D2 را به تنهایی و یا سلول‌های D2 تا D11 را با هم انتخاب کنید.

7. در تب Data، گزینه Data Validation  را انتخاب کنید.

8. در پنجره Data Validation، به تب Settings بروید و در فیلد Allow گزینه List را انتخاب کنید.

9. در باکس Source آن سلول‌هایی که قصد دارید لیستی از آن‌ها بسازید را وارد کنید. توجه کنید که شما قصد دارید منبع خود را از یک شیت دیگر انتخاب کنید. برای این کار، لازم نیست فرمول خاصی بنویسید، تنها کافی است نشانگر ماوس خود را به سمت شیت‌ها ببرید و منبع لیست خود را از آن شیت انتخاب کنید. با انتخاب این سلول‌ها، در باکس Source شما lists!$A$2:$A$11 درج می‌شود. کلمه lists در ابتدای این رشته حروف، به معنای این است که شما سلول‌های A2 تا A11 را از یک شیت به نام lists انتخاب کرده‌اید.

10. در تب Input Message می‌توانید پیامی را که دوست دارید در این لیست به کاربر نشان داده شود، وارد کنید.

11. در تب Error Alert هم می‌توانید یک پیام بنویسید تا در مواقعی که کاربر داده‌ای غیر از لیست تعریف شده شما در سلول وارد می‌کند، به او نشان داده شود. روی OK کلیک کنید تا لیست شما کامل نمایش داده شود.

12. به شیت wks برگردید و نشانگر ماوس خود را روی سلول D2 قرار دهید. پیامی را که در مرحله 10 وارد کردید، اینجا مشاهده می‌کنید. شما می‌توانید با کلیک روی علامت فلش کوچکی که در کنار هر سلول وجود دارد، یکی از دکتر‌هایی را که به عنوان لیست تعریف کردیم، انتخاب کنید.

13. اگر کاربری نامی غیر از نام این دکتر‌ها را وارد کند و یا نام یکی از این دکتر‌ها را به اشتباه در سلول‌ها بنویسد، پیغام خطایی که در مرحله 9 تعریف کردید، برای او نمایش داده می‌شود.

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

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

لیست اصلی ما شامل سه بیمارستان است که آن‌ها را بر اساس محل قرارگیری‌شان انتخاب می‌کنیم. لیست بعدی به شما دکتر‌هایی را نشان می‌دهد که در بیمارستان انتخاب شده شما، حضور دارند. این سه درمانگاه را با مکان آن‌ها یعنی East Side و West Side و Midtown انتخاب می‌شوند.

الف) لیست‌ها را بسازید

1. ابتدا یک شیت دیگر به نام lists2 بسازید.

2. در این شیت، در ردیف A مکان درمانگاه‌ها را بنویسید. یعنی در A1 بنویسید: Hospital Locations و در سلول‌های A2 تا A4 بنویسید: EastSide و WestSide و Midtown. حتما ترتیب را رعایت کرده و واژه‌ها را به صورت تک کلمه‌ای و بدون فاصله وارد کنید.

3. نشانگر ماوس خود را روی سلول A2 برده و از تب Home گزینه Format As Table را انتخاب کنید و یک مدل برای جدول خود برگزینید.

4. حال سه گزینه که در سلول‌های A2 تا A4 نوشتید را انتخاب کرده و در باکس نام (در بالای ستون A) کلمه Locations را تایپ کنید. پس از انتخاب این سه سلول از کلید ترکیبی Ctrl + T برای تبدیل آن‌ها به یک جدول استفاده کنید. باز هم چک کنید که پیام My Table Has Headers در کنار سلول‌هایتان وجود داشته باشد.

5. برای عوض کردن نام جدول خود، می‌توانید در بخش Formulas از Name Manager نام تمام جداول خود را تغییر دهید (با کلیک روی گزینه Edit که در کنار هر جدول قرار دارد.)

6. نام جدولی که در مرحله قبل تغییر داده‌اید را می‌توانید به نام Locations تغییر دهید.

نکته: در نام‌گذاری جداول، وارد کردن فاصله و کاراکتر‌های خاص ممنوع است. نام‌ها باید با یک حرف کوچک شروع شوند و نامی که استفاده می‌کنید نباید تکراری باشد (مثلا نمی‌توانید دو جدول را با یک نام داشته باشد، ولو اینکه در دو شیت مختلف باشند.)

7. اکنون باید برای هر مکانی که تعریف کرده‌اید، یک جدول جداگانه ایجاد کنید. در شیت lists2 عناوین East Side برای ستون B و West Side ستون C و Midtown برای ستون وارد کنید.

8. به دلخواه نام چند دکتر را در زیر مکان‌های بالا وارد کنید.

9. همانند مرحله 3، هر سه ستون ایجاد شده را به صورت جدول درآورید. می‌توانید طبق مرحله 5 نام جداول خود را تغییر دهید (بهتر است تغییر دهید.)

نکته: اگر جداول زیادی می‌سازید، بهتر است نام تمام آن‌ها را به عنوان آن‌ها تغییر دهید.

ب) ساختن منوی کشویی

1. ابتدا به شیت wks برگردید و منو آبشاری را که برای اسامی دکتر‌ها ساختیم حذف کنید. در ستون D و در سلول D1 عنوان Location و در سلول E1 عنوان Surgeons را وارد کنید.

2. سلول‌های D1 تا E11 را انتخاب کنید. مثل قبل، با گزینه Format As Table آن را به جدول تبدیل کنید.

3. حال سلول‌های D2 تا D11 را برای لیستی کردن آن‌ها انتخاب کنید. به منو Data Validation بروید.

4. تنظیمات این بخش را مثل قبل اعمال کنید (در Allow گزینه list را انتخاب کنید و …)

5. در باکس Source سلول‌های A2 تا A4 را علامت بزنید و OK کنید.

6. نشانگر ماوس خود را روی سلول E2 نگه دارید.

7. برای سلول E2، این بار در باکس Source این فرمول را بنویسید: INDIRECT($D$2

نکته: اگر در هنگام OK کردن این بخش، خطایی را مشاهده کردید، گزینه Yes را علامت بزنید چرا که معمولا این پیغام هشدار داده می‌شود.

8. در سلول D2 بنویسید: INDIRECT($D2. توجه کنید که علامت $ این بار تنها در کنار ستون درج شده و کاری به ردیف نداریم.

9. فرمول بالا را که در سلول D2 نوشتید، با Autofill کردن در سلول‌های D3 تا D11 کپی کنید (بخاطر این بود که تنها در کنار ستون $ گذاشتیم.)

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

ج) امتحان کردن

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

د) تمرین بیشتر

اگر نمی‌خواهید تمام کلمه‌ها را به‌صورت تک کلمه‌ای بنویسید، مثلا EastSide را به‌صورت East Side بنویسید. باید فرمول را به صورت INDIRECT(SUBSTITUTE(D2,” “,””)) وارد کنید. معنای این فرمول یعنی: سلول D2 را که فاصله دارد با سلول D2 بدون فاصله جایگزین کن. می‌توانید این حالت را نیز امتحان کنید.

فایل کامل شده این آموزش را از اینجا دانلود کنید.

کلام آخر

برای اطلای از آموزش های بیشتر با پلازا همراه بمانید، شما همچنین می‌توانید در بخش نظرات، دیدگاه خود را با ما به اشتراک بگذارید.