Excel: از یک لیست انتخاب کنید. لیست کشویی در یک سلول کاربرگ

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

این چگونه می تواند برای ما مفید باشد؟

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

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

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

1 - سریعترین راه

ساده ترین راه برای افزودن لیست کشویی چیست؟ فقط یکبار روی سلول خالی زیر ستون داده کلیک راست کنید، سپس روی فرمان منوی زمینه "انتخاب از لیست کشویی" کلیک کنید. یا فقط می توانید در جای مناسب بایستید و کلید ترکیبی را فشار دهید Alt + فلش رو به پایین. یک لیست مرتب شده از مقادیر منحصر به فرد قبلاً وارد شده ظاهر می شود.
اگر سلول و ستون ما با رکوردها با حداقل یک خط خالی از هم جدا شوند یا بخواهید چیزی را وارد کنید که هنوز در بالا وارد نشده است، این روش کار نمی کند. این به وضوح در مثال ما قابل مشاهده است.

2 - از منو استفاده کنید.

بیایید به یک مثال کوچک نگاه کنیم که در آن باید دائماً همان نام محصولات را در جدول وارد کنیم. داده هایی را که استفاده خواهیم کرد (مثلاً نام محصول) را در یک ستون بنویسید. در مثال ما - در محدوده G2:G7.

یک سلول جدول را انتخاب کنید (شما می توانید چندین سلول را همزمان داشته باشید) که در آن می خواهید از ورودی یک لیست از پیش تعیین شده استفاده کنید. سپس در منوی اصلی، تب Data – Validation را انتخاب کنید. سپس روی Data Type (Allow) کلیک کرده و گزینه List را انتخاب کنید. مکان نما را در قسمت Source قرار دهید و آدرس ها را با مقادیر مرجع عناصر - در مورد ما G2:G7 - وارد کنید. همچنین توصیه می شود در اینجا از پیوندهای مطلق استفاده کنید (برای تنظیم آنها، F4 را فشار دهید).

اگر می‌خواهید مقدار درج شده خودکار را به صورت دستی تغییر دهید، یک امتیاز در اینجا امکان تنظیم یک اشاره و یک پیام خطا است. برای انجام این کار، تب های Input Message و Error Alert وجود دارد.

همچنین می توانید از یک محدوده نامگذاری شده به عنوان منبع استفاده کنید.

به عنوان مثال، محدوده I2:I13 که شامل نام ماه ها است، می تواند به نام ماه ها باشد. سپس نام را می توان در قسمت Source وارد کرد.

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

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

برای جدا کردن مقادیر از یک نقطه ویرگول، کاما یا کاراکتر دیگری که به عنوان جداکننده عنصر تنظیم کرده اید استفاده کنید. (نگاه کنید به کنترل پنل - ساعت و منطقه - فرمت ها - پیشرفته - اعداد.)

3 - یک عنصر کنترل ایجاد کنید.

بیایید یک شی جدید را در برگه وارد کنیم - یک عنصر کنترل "جعبه ترکیبی" و سپس آن را به داده های صفحه اکسل متصل کنیم. ما انجام می دهیم:

1. تب Developer را باز کنید. اگر قابل مشاهده نیست، در اکسل 2007 باید روی دکمه آفیس - گزینه‌ها - گزینه Show Developer Tab در Ribbon (دکمه Office - گزینه‌ها - Show Developer Tab in the Ribbon) یا در نسخه‌های 2010-2013، راست کلیک کنید- روی نوار کلیک کنید، دستور Customize Ribbon را انتخاب کنید و با استفاده از چک باکس نمایش تب Developer را فعال کنید.
2. نماد مورد نظر را در میان کنترل ها پیدا کنید (تصویر زیر را ببینید).

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

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

همانطور که در شکل نشان داده شده است فرمول را در F3 می نویسیم:

INDEX(F5:F11,F2)

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

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

4 - عنصر ActiveX

ما مشابه روش قبلی پیش می رویم، اما نماد را دقیقاً در زیر - از بخش "EctiveX Elements" انتخاب کنید.

ما لیستی از مقادیر قابل قبول را تعریف می کنیم (1). لطفاً توجه داشته باشید که در اینجا می توانید چندین ستون را برای نمایش همزمان انتخاب کنید. سپس آدرسی را انتخاب می کنیم که در آن موقعیت مورد نظر از لیست درج می شود (2) تعداد ستون هایی را که به عنوان داده های منبع استفاده می شود (3) و تعداد ستونی که انتخاب از آن انجام می شود را نشان می دهیم. برای درج در ورق (4). اگر ستون شماره 2 را نشان دهید، نه نام خانوادگی، بلکه موقعیت در A5 درج می شود. همچنین می توانید تعداد خطوطی که در لیست نمایش داده می شود را مشخص کنید. پیش فرض 8 است. بقیه را می توان با ماوس پیمایش کرد (5).

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

5 - لیست با تکمیل خودکار

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

تکمیل خودکار در یک مثال ساده به این صورت است:

روش 1. یک منبع بزرگ شناخته شده را مشخص کنید.

ساده ترین و بی عارضه ترین ترفند. ابتدا الگوریتم معمول اقدامات را دنبال می کنیم: در منو، برگه Data – Validation را انتخاب کنید. از لیست Data Type (Allow) گزینه List را انتخاب کنید. مکان نما را در قسمت Source قرار دهید. بیایید یک مجموعه را در لیست با یک حاشیه بزرگ رزرو کنیم: به عنوان مثال، تا خط 55، اگر چه ما فقط 7 را اشغال کرده ایم، حتماً به یاد داشته باشید که چک باکس "نادیده گرفتن خالی ..." را علامت بزنید. سپس "ذخیره" مقادیر خالی شما شما را آزار نخواهد داد.

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

البته می توانید کل ستون را نیز به عنوان منبع مشخص کنید:

اما پردازش چنین تعداد سلولی می تواند محاسبات را تا حدودی کند کند.

روش 2: از یک محدوده با نام استفاده کنید.

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

ستون A شامل اسامی کارکنانی است که وارد خواهیم کرد. لیست می تواند تا زمانی که شما دوست دارید طولانی باشد. ما می خواهیم که هر ورودی جدید بدون هیچ اقدام اضافی در لیست کشویی قرار گیرد.

اجازه دهید لیست اسامی را که در اختیار داریم A2:A10 برجسته کنیم. سپس با پر کردن فیلد "Name" واقع در سمت چپ نوار فرمول، نامی برای آن بگذارید. بیایید لیستی از مقادیر در C2 ایجاد کنیم. به عنوان منبعی برای آن عبارت را نشان می دهیم

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

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

ناراحتی اصلی استفاده از چنین لیستی این است که محدوده ای که استفاده می کنیم ثابت است. اندازه آن به طور خودکار قابل تغییر نیست. موافقم، این روش خیلی راحت و از نظر تکنولوژیکی پیشرفته نیست. مراحل دستی بسیار زیاد است.

روش 3. یک جدول "هوشمند" به ما کمک خواهد کرد.

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

هر مجموعه ای از مقادیر در جدول را می توان به این روش تبدیل کرد. به عنوان مثال، A1: A8. آنها را با ماوس انتخاب کنید. سپس با استفاده از منو به جدول تبدیل کنید صفحه اصلی - قالب بندی به عنوان جدول. مشخص کنید که خط اول حاوی نام ستون است. این سرفصل جدول شما خواهد بود. ظاهر می تواند هر چیزی باشد: چیزی بیش از یک طراحی خارجی نیست و بر هیچ چیز دیگری تأثیر نمی گذارد.

همانطور که در بالا ذکر شد، یک جدول "هوشمند" برای ما خوب است زیرا زمانی که اطلاعات به آن اضافه می شود، اندازه آن به صورت پویا تغییر می کند. اگر چیزی را در خط زیر آن وارد کنید، بلافاصله آن را به خودش متصل می کند. بنابراین، مقادیر جدید را می توان به سادگی اضافه کرد. به عنوان مثال، کلمه "Coconut" را در A9 وارد کنید، و جدول بلافاصله به 9 خط افزایش می یابد.

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

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

جدول 1 [ستون 1]

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

برای استفاده از "میز هوشمند" به عنوان منبع، باید از یک ترفند کوچک استفاده کنیم و از تابع INDIRECT استفاده کنیم. این تابع یک متغیر متنی را به یک لینک معمولی تبدیل می کند.

فرمول اکنون به شکل زیر خواهد بود:

INDIRECT("جدول5[محصول]")

Table5 نامی است که به طور خودکار به "جدول هوشمند" اختصاص داده می شود. ممکن است برای شما متفاوت باشد. در تب منوی Design، می توانید نام استاندارد را به نام خود تغییر دهید (اما بدون فاصله!). با استفاده از آن، سپس می‌توانیم به جدول خود در هر صفحه از کتاب دسترسی داشته باشیم.

"محصول" نام اولین و تنها ستون ما است که با عنوان آن اختصاص داده شده است.

مطمئن شوید که کل عبارت را نیز در علامت نقل قول قرار دهید تا آن را به عنوان یک متغیر متنی نشان دهید.

حالا اگر میوه دیگری (مثلاً نارگیل) را به A9 اضافه کنید، به طور خودکار در لیست ما ظاهر می شود. اگر چیزی را حذف کنیم همینطور می شود. مشکل افزایش خودکار لیست کشویی مقادیر حل شده است.

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

در اینجا اطلاعات مفید دیگری برای شما آورده شده است:

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

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

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

چگونه با استفاده از یک دستور در نوار ابزار یک لیست کشویی در اکسل 2010 یا 2016 ایجاد کنیم؟ در برگه «داده»، در بخش «کار با داده»، دکمه «تأیید اعتبار داده» را پیدا کنید. روی آن کلیک کنید و اولین مورد را انتخاب کنید.

پنجره ای باز خواهد شد. در برگه «گزینه‌ها»، در بخش کشویی «نوع داده»، «فهرست» را انتخاب کنید.


یک خط در پایین ظاهر می شود تا منابع را نشان دهد.


شما می توانید اطلاعات را به روش های مختلف ارائه دهید.

ابتدا بیایید یک نام اختصاص دهیم. برای انجام این کار، چنین جدولی را در هر صفحه ایجاد کنید.

آن را انتخاب کرده و راست کلیک کنید. بر روی دستور "Assign a name" کلیک کنید.

نام خود را در خط بالا وارد کنید.

پنجره «بررسی داده ها» را فراخوانی کنید و در قسمت «منبع» نام را با قرار دادن علامت «=» در مقابل آن مشخص کنید.


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

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

جایگزینی داده های پویا اکسل

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

آن را انتخاب کنید و در تب "Home"، هر سبک جدول را انتخاب کنید.


حتما کادر زیر را علامت بزنید.

این طرح را دریافت خواهید کرد.

همانطور که در بالا توضیح داده شد یک عنصر فعال ایجاد کنید. برای منبع، فرمول را وارد کنید

INDIRECT("جدول1[شهرها]")

برای اطلاع از نام جدول، به تب Design رفته و آن را نگاه کنید. شما می توانید نام را به هر نام دیگری تغییر دهید.


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

بیایید سعی کنیم تعداد شهرها را افزایش دهیم.


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

آدرس_سلول

به عنوان مثال، اگر لیست داده ها در سلول D1 است، سپس در سلولی که نتایج انتخاب شده نمایش داده می شود، فرمول را وارد کنید.

نحوه حذف (حذف) یک لیست کشویی در اکسل

پنجره تنظیمات لیست کشویی را باز کنید و در بخش "نوع داده" "Any value" را انتخاب کنید.

عنصر غیر ضروری ناپدید می شود.

اقلام وابسته

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


در این صورت به هر ستون یک نام بدهید. بدون اولین سلول (عنوان) انتخاب کنید و راست کلیک کنید. "نام" را انتخاب کنید.

این نام شهر خواهد بود.


هنگام نامگذاری سن پترزبورگ و نیژنی نووگورود، یک خطا دریافت خواهید کرد، زیرا نام نمی تواند شامل فاصله، زیرخط، کاراکترهای خاص و غیره باشد.


بنابراین نام این شهرها را با خط خط تغییر می دهیم.


اولین عنصر را در سلول A9 به روش معمول ایجاد می کنیم.


و در دومی فرمول را می نویسیم:

INDIRECT(A9)

ابتدا یک پیغام خطا مشاهده خواهید کرد. موافقم

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

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

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


برای لیست دوم باید فرمول را وارد کنید:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH تعداد سلول را با شهر انتخاب شده در لیست اول (E6) در ناحیه مشخص SA:$A برمی گرداند.
COUNTIF تعداد موارد منطبق را در یک محدوده با مقدار موجود در سلول مشخص شده (E6) می شمارد.

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

چند انتخابی

اغلب ما باید چندین مقدار را از یک مجموعه داده دریافت کنیم. شما می توانید آنها را در سلول های مختلف نمایش دهید، یا می توانید آنها را در یکی ترکیب کنید. در هر صورت، ماکرو مورد نیاز است.
روی برچسب برگه در پایین کلیک راست کرده و View Code را انتخاب کنید.


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

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) هیچ است و Target.Cells.Count = 1 سپس Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 سپس Target.Offset(1,0) = Target Else Target.End(xlDown).Offset(1,0) = Target End If Target.ClearContents Application.EnableEvents = پایان واقعی اگر پایان فرعی

لطفا توجه داشته باشید که در خط

If Not Intersect(Target, Range("E7")) هیچ چیز نیست و Target.Cells.Count = 1 سپس

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

به کاربرگ اکسل خود برگردید و یک لیست در سلول E7 ایجاد کنید.

پس از انتخاب، مقادیر زیر آن ظاهر می شوند.

کد زیر به شما امکان می دهد مقادیر را در یک سلول جمع آوری کنید.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) هیچ چیز و Target.Cells.Count = 1 سپس Application.EnableEvents = False newVal = Target Application.Undo old = هدف اگر لن (الدوال)<>0 و اولدوال<>newVal سپس Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 سپس Target.ClearContents Application.EnableEvents = پایان واقعی If End Sub

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


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

روز خوبی داشته باشید!

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

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

مرحله 1.به برگه بروید "داده ها"، که در پانل بالایی و سپس در بلوک قرار دارد "کار با داده ها"ابزار تأیید داده را انتخاب کنید (تصویر صفحه نشان می دهد که با چه نمادی نشان داده شده است).

مرحله 2.حالا همان تب اول را باز کنید "گزینه ها"و نصب کنید "فهرست"در لیست نوع داده

مرحله 3.اکنون در قسمت ورود اطلاعات "منبع"می توانید مقادیری را که لیست کشویی را تشکیل می دهند را مشخص کنید. شما می توانید این کار را به روش های زیر انجام دهید:

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

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

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

ویدئو - ایجاد لیست های کشویی در اکسل

لیست کشویی با جایگزینی داده ها

اگر جدول شما دستخوش تغییرات دائمی است و نمی‌خواهید بر محتوای لیست کشویی شما تأثیر بگذارد، روش زیر به شما کمک می‌کند. از آن استفاده کنید و سپس ویرایش داده های سلولی به طور خودکار در لیست کشویی نمایش داده می شود. برای این کار موارد زیر را انجام دهید:

1. محدوده لیست را با دکمه سمت چپ ماوس انتخاب کنید (در این مثال لیستی از درختان خواهد بود)، سپس برگه را باز کنید "خانه"و منو را انتخاب کنید "قالب کردن به عنوان جدول".

   

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

   

3. در مرحله بعد، یک پنجره تأیید ظاهر می شود که هدف از آن تأیید صحت محدوده وارد شده است. مهم است که کادر کناری را علامت بزنید "جدول با سرصفحه"، از آنجایی که وجود عنوان در این مورد نقش اساسی دارد.

   

4. پس از تکمیل مراحل، نوع محدوده زیر را دریافت خواهید کرد.

   

5. حالا روی سلولی که لیست کشویی در آن قرار دارد کلیک چپ کرده و به تب بروید "داده ها"(روش قبلی به شما می گوید که چگونه این کار را انجام دهید).

   

6. در قسمت ورودی "منبع"باید تابعی را با سینتکس وارد کنید "=INDIRECT("TableName[Header]")". اسکرین شات یک مثال خاص تر را نشان می دهد.

   

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

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

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

اگر یک سلول را حذف کنید (در این مورد ما "توس" را حذف کردیم)، این نیز در محتوای لیست کشویی منعکس می شود.

توجه داشته باشید!در این روش به اصطلاح «میز هوشمند» می پردازیم. به راحتی قابل گسترش است و این ویژگی برای بسیاری از دستکاری ها با جداول اکسل، از جمله ایجاد یک لیست کشویی مفید است.

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

برای ایجاد لیست های کشویی وابسته، این دستورالعمل های گام به گام را دنبال کنید:

1. ابتدا باید یک محدوده با نام ایجاد کنید. به برگه بروید "فرمول ها"، سپس انتخاب کنید "نام مدیر"و "ایجاد".

   

   

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

   

3. با استفاده از همین تکنیک، به تعداد وابستگی های منطقی که می خواهید ایجاد کنید، محدوده های نامگذاری شده ایجاد کنید. در این مثال، این دو محدوده دیگر هستند: "بوته ها"و "گیاهان".

   

4. برگه را باز کنید "داده ها"(در روش اول مسیر رسیدن به آن مشخص شده است) و نام محدوده های نامگذاری شده را در منبع نشان دهید، همانطور که در تصویر نشان داده شده است.

   

   

5. اکنون باید با استفاده از همان طرح یک کشویی اضافی ایجاد کنید. این فهرست کلماتی را که با عنوان مطابقت دارند منعکس می کند. به عنوان مثال، اگر "درخت" را انتخاب کرده باشید، "توس"، "آهنگ"، "افرا" و غیره خواهد بود. برای این کار مراحل بالا را تکرار کنید اما در قسمت ورودی "منبع"تابع را وارد کنید "=INDIRECT(E1)" . در این مورد "E1"– این آدرس سلول با نام محدوده اول است. با استفاده از همین روش، می توانید هر تعداد لیست به هم پیوسته که نیاز دارید ایجاد کنید.

   

   

اکنون چندین روش برای ایجاد یک منوی کشویی در می دانید. این ممکن است در تعدادی از موارد و به ویژه در تأیید داده ها برای شما مفید باشد. انتخاب روش مناسب باید به نوع جدولی که با آن کار می کنید بستگی دارد. اگر این یک جدول "یک بار مصرف" است، روش اول مناسب است - سریع و آسان است. اگر جدول به تغییرات ثابت نیاز دارد، بهتر است از روشی استفاده کنید که شامل ایجاد «جدول هوشمند» و محدوده‌های نام‌گذاری شده باشد.

ویدئو - لیست های کشویی مرتبط: آسان و سریع

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

بنابراین، برای ایجاد یک لیست کشویی نیاز دارید:

1. فهرستی از مقادیری ایجاد کنید که برای انتخاب در اختیار کاربر قرار می گیرد (در مثال ما این یک محدوده است M1: M3، سپس سلولی را انتخاب کنید که لیست کشویی در آن قرار دارد (در مثال ما این سلول است K1، سپس به برگه " بروید داده ها"، گروه" کار با داده ها"، دکمه" تایید داده ها"

2. انتخاب کنید " نوع داده" -"فهرست کنید"و محدوده لیست را مشخص کنید

3. اگر می خواهید از کاربر در مورد اقدامات خود مطلع شوید، به برگه "" بروید پیامی که باید وارد شود"و عنوان و متن پیام را پر کنید

که با انتخاب یک سلول با لیست کشویی ظاهر می شود

4. همچنین می‌توانید به‌صورت اختیاری پیامی ایجاد کنید که وقتی می‌خواهید داده‌های نادرست وارد کنید ظاهر شود

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

5. اگر لیست مقادیر در برگه دیگری باشد، نمی توانید یک لیست کشویی با استفاده از روش توصیف شده در بالا (تا اکسل 2010) ایجاد کنید. برای انجام این کار، باید به لیست یک نام بدهید. این را می توان به چند روش انجام داد. اول: لیست را انتخاب کنید و راست کلیک کنید، در منوی زمینه، " را انتخاب کنید یک نام اختصاص دهید"

برای نسخه های اکسل زیر 2007، مراحل مشابه به این صورت است:

دوم: استفاده کنید مدیر نام(نسخه های اکسل بالای 2003 - تب " فرمول ها"-گروه" اسامی خاص")، که در هر نسخه اکسل با یک میانبر صفحه کلید فراخوانی می شود Ctrl+F3.
هر روشی که انتخاب کنید، در پایان باید یک نام وارد کنید (من محدوده را با لیست نامگذاری کردم فهرست) و آدرس خود محدوده (در مثال ما این است "2"!$A$1:$A$3)

6. اکنون در سلول با لیست کشویی، نام محدوده را در قسمت "منبع" وارد کنید

7. آماده!

برای تکمیل تصویر، اضافه می کنم که لیست مقادیر را می توان مستقیماً بدون استفاده از قرار دادن مقادیر در یک برگه وارد بررسی داده ها کرد (این به شما امکان می دهد با لیست در هر برگه کار کنید. ). این کار به این صورت انجام می شود:

یعنی به صورت دستی از طریق ; (نقطه ویرگول) لیست را در فیلد وارد کنید منبع"، به ترتیبی که می خواهیم آن را ببینیم (مقادیر وارد شده از چپ به راست در سلول از بالا به پایین نمایش داده می شود).

با تمام مزایای آن، لیست کشویی ایجاد شده به روشی که در بالا توضیح داده شد، یک نقطه ضعف دارد، اما بسیار "پررنگ": تأیید داده ها فقط زمانی کار می کند که مستقیماً مقادیر را از صفحه کلید وارد کنید. اگر سعی می کنید به یک سلول بچسبانید با تایید داده هامقادیر را از کلیپ بورد، یعنی قبلاً به هر شکلی کپی کرده اید، سپس موفق خواهید شد. علاوه بر این، مقدار چسبانده شده از بافر، فهرست چک کردن و حذف داده ها را از سلولی که مقدار کپی شده قبلی در آن جایگذاری شده است، حذف می کند. با استفاده از ابزارهای استاندارد اکسل نمی توان از این امر اجتناب کرد.

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

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

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

برای این کار در سلول های A1:A7 داده هایی را که در لیست نمایش داده می شود وارد می کنیم. حالا بیایید سلولی را انتخاب کنیم که در آن یک لیست کشویی ایجاد می کنیم - B2.

به تب "داده ها" بروید و روی دکمه "بررسی داده ها" کلیک کنید.

در برگه «پارامترها»، در قسمت «نوع داده»، «فهرست» را انتخاب کنید. می توانید مقادیر را در قسمت Source به روش های مختلف وارد کنید:

1 - مقادیر لیست را به صورت دستی وارد کنید که با نقطه ویرگول از هم جدا شده اند.

2 - محدوده سلول هایی را که داده های لیست کشویی در آنها وارد می شود را مشخص کنید.

3 – سلول های دارای نام را انتخاب کرده، روی آنها کلیک راست کرده و از منو گزینه Assign a name را انتخاب کنید.

سلول B2 را انتخاب کنید و "=" را در قسمت "Source" قرار دهید، سپس نام ایجاد شده را بنویسید.

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

اگر یک عنوان برای یک ستون دارید، و باید هر ردیف را با مقادیر پر کنید، نه یک سلول، بلکه محدوده ای از سلول ها - B2:B9 را انتخاب کنید. سپس می توانید مقدار مورد نظر را در هر سلول از لیست کشویی انتخاب کنید.

افزودن مقادیر به یک لیست کشویی - لیست پویا

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

محدوده سلول ها را انتخاب کنید – D1:D8، سپس در تب “Home”، روی “Format as table” کلیک کنید و هر سبکی را انتخاب کنید.

مکان داده ها را تأیید کنید و کادر "جدول با سرصفحه" را علامت بزنید.

در بالا عنوان جدول را می نویسیم - "کارمندان" و آن را با داده ها پر می کنیم.

سلولی که لیست کشویی در آن قرار دارد را انتخاب کنید و روی دکمه "بررسی داده ها" کلیک کنید. در پنجره بعدی، در قسمت "Source"، موارد زیر را بنویسید: =INDIRECT("Table1"). من یک جدول روی یک برگه دارم، بنابراین می نویسم "جدول 1"، اگر جدول دوم وجود دارد - "جدول2" و غیره.

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

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

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

در برگه 2، یک سلول یا محدوده ای از سلول ها را انتخاب کنید، سپس بر روی دکمه "Data Validation" کلیک کنید.

به برگه 1 بروید، مکان نما را در قسمت "منبع" قرار دهید و محدوده مورد نظر سلول ها را انتخاب کنید.

اکنون می توانید نام ها را در برگه 1 اضافه کنید، آنها به لیست های کشویی در برگه 2 اضافه خواهند شد.

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

بیایید فرض کنیم سه محدوده داریم: نام، نام خانوادگی و نام خانوادگی کارکنان. برای هر کدام، باید یک نام اختصاص دهید. ما سلول های این محدوده را انتخاب می کنیم، همچنین می توانید آنها را خالی کنید - با گذشت زمان می توانید داده هایی را به آنها اضافه کنید که در لیست کشویی ظاهر می شود. روی آنها کلیک راست کرده و از لیست گزینه "Assign a name" را انتخاب کنید.

ما اولی را "نام"، دومی "نام خانوادگی"، سومی را "پدر" می نامیم.

بیایید یک محدوده دیگر بسازیم که نام های اختصاص داده شده در آن نوشته شود. بیایید آن را "کارمندان" بنامیم.

ما اولین لیست کشویی را ایجاد می کنیم که شامل نام محدوده ها می شود. سلول E1 را انتخاب کنید و در برگه "داده" "Data Validation" را انتخاب کنید.

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

اولین لیست کشویی ایجاد شده است. اکنون در سلول F2 یک لیست دوم ایجاد می کنیم که باید به لیست اول بستگی داشته باشد. اگر در مورد اول "Name" را انتخاب کنیم، در مورد دوم لیستی از نام ها نمایش داده می شود.

سلول را انتخاب کنید و روی دکمه "بررسی داده ها" کلیک کنید. در قسمت "نوع داده"، "List" را در قسمت منبع انتخاب کنید، موارد زیر را وارد کنید: =INDIRECT($E$1). در اینجا E1 سلول با اولین لیست کشویی است.

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

اگر در آینده، باید مقادیر را در محدوده ای وارد کنید که نامی به آن داده می شود، به عنوان مثال، "نام خانوادگی". به تب Formulas رفته و روی Name Manager کلیک کنید. اکنون در نام محدوده "Last Name" را انتخاب کنید و در زیر به جای آخرین سلول C3، C10 را بنویسید. روی علامت چک کلیک کنید. پس از این، محدوده افزایش می یابد و می توانید داده هایی را به آن اضافه کنید که به طور خودکار در لیست کشویی ظاهر می شود.

اکنون می دانید که چگونه یک لیست کشویی در اکسل ایجاد کنید.

نحوه ایجاد یک لیست کشویی متشکل از چندین سلول به طور همزمان (به عنوان مثال، به طوری که نام هزینه داشته باشد)

ممنون، همه چیز درست شد

یک لیست کشویی با مقادیر از یک برگه دیگر کار نمی کند، زیرا پنجره ای که تأیید داده ها باز است اجازه کار با پنجره های دیگر، به خصوص با برگه دیگر را نمی دهد!

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

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

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

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

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

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

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

فهرست دسته ها و زیرمجموعه ها در لیست کشویی وابسته به اکسل

اعتراف می کنم که در نسخه بودجه خانه پیشنهادی من ، خود را فقط به یک دسته محدود می کنم ، زیرا برای من چنین تقسیم بندی هزینه ها کاملاً کافی است (نام هزینه / درآمد به عنوان یک زیر مجموعه در نظر گرفته می شود). با این حال، اگر شما نیاز به تفکیک آنها به زیر شاخه ها دارید، روشی که در زیر توضیح می دهم ایده آل است. با خیال راحت از آن استفاده کنید!

و نتیجه نهایی به این صورت است:

لیست کشویی وابسته از زیر مجموعه ها

برای رسیدن به این هدف، باید یک جدول داده کمی متفاوت از زمانی که یک لیست کشویی ایجاد می‌کردیم ایجاد کنیم. جدول باید به این شکل باشد (محدوده G2:H15):

صفحه گسترده منبع اکسل کار

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

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

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

1. نام های محدوده سلولی

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

بیایید به دو محدوده نام اختصاص دهیم. لیست همه دسته ها و لیست کاری دسته ها. این محدوده‌ها A3:A5 (فهرست دسته‌ها در کاربرگ سبز در تصویر اول) و G3:G15 (لیست دسته‌های تکراری در کاربرگ بنفش) خواهند بود.

برای نام‌گذاری فهرستی از دسته‌ها:

1. محدوده A3:A5 را انتخاب کنید.
2. در کادر Name (کادر سمت چپ نوار فرمول)، نام "Category" را وارد کنید.
3. با کلید Enter تایید کنید.

همین عمل را برای محدوده فهرست کار دسته‌بندی G3:G15 انجام دهید، که می‌توانید آن را «Work_List» بنامید. ما از این محدوده در فرمول استفاده خواهیم کرد.

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

ساده خواهد بود:

1. سلولی را که می خواهید لیست را در آن قرار دهید انتخاب کنید. در مورد من A12 است.
2. از منوی DATA ابزار Data Validation را انتخاب کنید. پنجره "بررسی مقادیر ورودی" ظاهر می شود.
3. لیست را به عنوان نوع داده انتخاب کنید.
4. به عنوان منبع، وارد کنید: =Category (تصویر زیر).
5. با OK تایید کنید.

نتیجه به شرح زیر است:

لیست کشویی برای دسته.

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

حالا سرگرم کننده خواهد بود. ما می دانیم که چگونه لیست ایجاد کنیم - ما این کار را فقط برای یک دسته انجام دادیم. فقط یک سوال: "چگونه به اکسل بگویم فقط مقادیری را انتخاب کند که برای یک دسته خاص در نظر گرفته شده است؟" همانطور که احتمالاً می توانید حدس بزنید، من در اینجا از یک کاربرگ و البته فرمول استفاده خواهم کرد.

بیایید با چیزی که قبلاً می دانیم شروع کنیم، یعنی با ایجاد یک لیست کشویی در سلول B12. بنابراین آن سلول را انتخاب کنید و روی Data/Data Validation کلیک کنید و Data Type لیست است.

در منبع لیست، فرمول زیر را وارد کنید:

نمای پنجره "بررسی مقادیر ورودی":

اعتبارسنجی مقادیر ورودی برای یک زیر مجموعه در یک لیست کشویی وابسته

همانطور که می بینید، کل ترفند یک لیست وابسته استفاده از تابع OFFSET است. خوب، تقریباً همه چیز. توابع MATCH و COUNTIF به او کمک می کنند. تابع OFFSET به شما اجازه می دهد تا محدوده ها را به صورت پویا تعریف کنید. ابتدا سلولی را که تغییر محدوده باید از آن شروع شود و در آرگومان های بعدی اندازه آن را مشخص می کنیم.

در مثال ما، محدوده در ستون Subcategory در کاربرگ حرکت می کند (G2:H15). از سلول H2 شروع به حرکت می کنیم که اولین آرگومان تابع ما نیز هست. در فرمول، سلول H2 به عنوان یک مرجع مطلق نوشته شده است زیرا من فرض می کنم که ما از لیست کشویی در بسیاری از سلول ها استفاده خواهیم کرد.

از آنجایی که کاربرگ بر اساس دسته بندی مرتب شده است، محدوده ای که باید منبع فهرست کشویی باشد از جایی شروع می شود که دسته انتخابی برای اولین بار ظاهر می شود. به عنوان مثال، برای دسته غذا می خواهیم محدوده H6:H11 را نمایش دهیم، برای حمل و نقل - محدوده H12:H15 و غیره. توجه کنید که ما همیشه در امتداد ستون H حرکت می کنیم و تنها چیزی که تغییر می کند شروع است. محدوده و ارتفاع آن (یعنی تعداد عناصر موجود در لیست).

ابتدای محدوده نسبت به سلول H2 به تعداد سلول‌ها (به تعداد) به اندازه شماره موقعیت اولین دسته در ستون Category به پایین منتقل می‌شود. درک آن با یک مثال ساده تر خواهد بود: محدوده برای دسته غذا 4 سلول به پایین نسبت به سلول H2 منتقل شده است (از 4 سلول از H2 شروع می شود). در سلول 4 ستون Subcategory (بدون احتساب عنوان، چون در مورد محدوده ای به نام Work_List صحبت می کنیم)، کلمه Nutrition (اولین ظاهر آن) وجود دارد. ما از این واقعیت برای تعیین شروع محدوده استفاده می کنیم. تابع MATCH (که به عنوان آرگومان دوم تابع OFFSET معرفی شده است) برای این منظور به ما کمک می کند:

ارتفاع محدوده توسط تابع COUNTIF تعیین می شود. او همه تکرارها را در دسته بندی می شمارد، یعنی کلمه تغذیه. تعداد دفعاتی که این کلمه رخ می دهد، تعداد موقعیت هایی که در محدوده ما وجود دارد خواهد بود. تعداد موقعیت ها در یک محدوده ارتفاع آن است. در اینجا تابع است:

البته هر دو تابع قبلاً در تابع OFFSET که در بالا توضیح داده شد گنجانده شده اند. همچنین توجه داشته باشید که در هر دو تابع MATCH و COUNTIF به محدوده ای به نام WorkList اشاره می شود. همانطور که قبلاً اشاره کردم، لازم نیست از نام محدوده استفاده کنید، فقط می توانید $H3:$H15 را وارد کنید. با این حال، استفاده از نام های محدوده در فرمول، خواندن آن را ساده تر و آسان تر می کند.

همین:

نمونه ای از یک لیست کشویی وابسته را در اکسل دانلود کنید

یک فرمول، خوب، نه چندان ساده، اما کار را آسان‌تر می‌کند و هنگام وارد کردن داده‌ها از خطا محافظت می‌کند!