گاهی اوقات پس از ایجاد لیست کشویی در اکسل، گزینه‌های جدیدی به موارد اضافه می‌شود و ویرایش لیست کشویی در اکسل ضروری می‌شود که وقت‌گیر است. در ساخت لیست کشویی در اکسل می‌توانید به شکلی فرمول نویسی کنید که در صورت پر کردن سلول جدیدی در یک ستون یا ردیف، گزینه مربوطه در لیست کشویی اضافه شود و نیازی به ویرایش کردن لیست کشویی وجود نداشته باشد.
در این مقاله آموزش Excel با روش ساخت لیست کشویی پیشرفته در اکسل آشنا می‌شویم که نیازی به ویرایش کردن ندارد و به صورت خودکار آپدیت می‌شود. با اینتوتک همراه باشید.
آموزش ساخت لیست کشویی با آپدیت شدن خودکار در Excel
برای ایجاد لیست کشویی در اکسل به سلول‌های حاوی گزینه‌های لیست ارجاع می‌دهیم. اگر ارجاع به شکل ثابت و معمولی باشد، طبعاً با اضافه شدن سلولی جدید، باید لیست کشویی را ویرایش کرد و سلول جدید را نیز به موارد قابل انتخاب در لیست کشویی اضافه کرد. اما اگر در ارجاع داده، از تابع Count برای شمارش کردن سلول‌های پر شده استفاده کنید و آدرس سلول را به کمک تابع Indirect بدهید، مشکل حل می‌شود.
برای توضیح تصویری مراحل کار، یک مثال ساده را بررسی می‌کنیم که جدولی حاوی نام بازیکنان و ملیت و امتیاز بازیکنان است. می‌خواهیم در سلول H2 از شیت زیر در اکسل، لیست کشویی برای انتخاب کردن ملیت ایجاد کنیم. ملیت بازیکنان در ستون B جدول درج شده است.
برای ساخت لیست کشویی با قابلیت آپدیت شدن خودکار در اکسل به صورت زیر عمل کنید:
- ابتدا روی سلول موردنظر برای ساخت لیست کشویی کلیک کنید.
- در بالای صفحه روی تب Data کلیک کنید.
- در گروه Data Tools روی منوی Data Validation کلیک کنید.
- در منوی باز شده روی Data Validation کلیک کنید.
- پنجره‌ی Data Validation ظاهر می‌شود. از منوی Allow گزینه‌ی List را انتخاب کنید.
- برای ایجاد لیست کشویی در سلول، تیک In-cell dropdown را بزنید.
- در کادر Source کلیک کنید.
- نماد = را تایپ کنید.
- ابتدا از تابع Indirect استفاده کنید.
- داخل پرانتز نماد " را وارد کنید.
- آدرس اولین سلول از محدوده‌ی سلول‌ها در ستون موردنظر را وارد کنید که در مثال ما B2 است.
- نماد : را وارد کنید.
- نام ستون را وارد کنید و نماد " و سپس & را وارد کنید.
- برای اشاره به شماره آخرین ردیف پر شده، تابع COUNTA که سلول‌های پر شده را شمارش می‌کند وارد کنید.
- به کل ستون موردنظر ارجاع بدهید که در مثال ستون B است و لذا داخل پرانتز B:B تایپ می‌شود.
- پرانتز مربوط به تابع Indirect را ببندید.
- روی OK کلیک کنید.
فرمول موردبحث برای ارجاع به سلول‌های پر شده در ستون B مثال ما به صورت زیر می‌شود:
=INDIRECT("B2:B"&COUNTA(B:B))
اگر سلول جدیدی در ستون B با وارد کردن نام یک کشور جدید پر شود، خروجی تابع COUNTA یک عدد بیشتر می‌شود و آدرس سلول‌های حاوی داده تغییر می‌کند و سلول جدید را شامل می‌شود. در نتیجه گزینه‌ی مربوط به لیست کشویی اضافه می‌شود.
با این روش ویرایش کردن یا حذف لیست کشویی در اکسل و ساخت لیست جدید با سلول‌های جدید ضروری نیست چرا که هر ردیف جدیدی که در شیت اکسل اضافه کنید، حروف اختصاری کشور مربوطه در لیست کشویی اضافه می‌شود.
برای اطمینان از درست کار کردن فرمولی که برای ارجاع به سلول‌ها نوشته‌اید، می‌توانید پنجره‌ی Data Validation را باز کنید و روی کادر Source کلیک کنید تا سلول‌هایی که به آن ارجاع شده، با خط‌چین متمایز شود.
howtogeekاینتوتک