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

فهرست مطالب و زمان بندی ویدیو

اگر می‌خواهید ورود اطلاعات در فایل‌های اکسل سریع‌تر، دقیق‌تر و بدون خطا انجام شود، «آموزش لیست کشویی و بازشو در اکسل» دقیقا همان چیزی است که نیاز دارید. در این مقاله (همراه با ویدیو) ساخت dropdown list در اکسل را از مدل ساده شروع می‌کنیم و بعد سراغ لیست کشویی داینامیک و لیست کشویی وابسته در اکسل می‌رویم تا بتوانید فرم‌ها و داشبوردهای حرفه‌ای‌تر طراحی کنید.

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

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

به‌طور کلی، لیست کشویی در اکسل چند مدل رایج دارد: لیست کشویی ساده (برای انتخاب از چند گزینه ثابت)، لیست کشویی داینامیک یا پویا (وقتی گزینه‌ها با تغییر داده‌ها خودکار به‌روز می‌شوند) و لیست کشویی وابسته (وقتی گزینه‌های لیست دوم به انتخاب لیست اول وابسته است).

انتخاب مدل مناسب به سناریوی شما بستگی دارد. مثلا برای فرم ثبت اطلاعات یکسان، لیست ساده کافی است؛ اما برای دسته‌بندی‌های چندمرحله‌ای مثل «استان → شهر»، حتما به لیست کشویی وابسته در اکسل نیاز دارید. اگر هم داده‌های شما دائما در حال افزایش است، مدل داینامیک انتخاب بهتری خواهد بود.

ساخت لیست کشویی ساده در اکسل (Dropdown List) با Data Validation

ساده‌ترین روش ساخت لیست بازشو در اکسل استفاده از ابزار Data Validation است. ابتدا گزینه‌های لیست را در یک ستون (مثلا A1:A10) وارد کنید. سپس سلولی را که قرار است لیست کشویی داخل آن نمایش داده شود انتخاب کنید و از مسیر Data > Data Validation، نوع را روی List بگذارید و محدوده گزینه‌ها را به‌عنوان Source معرفی کنید.

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

ساخت لیست کشویی داینامیک و پویا در اکسل

در لیست کشویی ساده، اگر گزینه جدیدی به انتهای لیست اضافه کنید، ممکن است لازم باشد محدوده Source را دستی تغییر دهید. اما در لیست کشویی داینامیک در اکسل، محدوده گزینه‌ها خودکار با افزایش/کاهش داده‌ها به‌روزرسانی می‌شود.

برای ساخت حالت پویا، یکی از روش‌های رایج استفاده از Table اکسل است: لیست گزینه‌ها را تبدیل به Table کنید (Insert > Table). سپس در Data Validation، Source را به ستون همان Table ارجاع دهید. در این حالت، هر گزینه جدیدی که به Table اضافه کنید، به‌صورت خودکار وارد dropdown می‌شود و نیازی به ویرایش دستی ندارید.

لیست کشویی وابسته در اکسل (Dependent Dropdown) چیست و کجا استفاده می‌شود؟

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

برای پیاده‌سازی Dependent Dropdown معمولا داده‌ها باید ساختارمند باشند (مثلا هر دسته، محدوده مخصوص به خودش را داشته باشد). در بسیاری از روش‌ها از Name Manager و تعریف نام برای محدوده‌ها کمک می‌گیریم تا اکسل بتواند بر اساس انتخاب کاربر، محدوده درست را به‌عنوان Source تشخیص دهد.

ساخت لیست کشویی داینامیک وابسته در اکسل (Dynamic Dependent Dropdown)

وقتی وابستگی با «داینامیک بودن» ترکیب شود، نتیجه یک سیستم حرفه‌ای‌تر است: هم لیست‌ها وابسته‌اند و هم اگر داده‌های پایه تغییر کند، dropdownها بدون نیاز به اصلاح دستی به‌روزرسانی می‌شوند. در این سناریو معمولا داده‌ها به شکل جدول‌های مرتب ذخیره می‌شوند و از روش‌های منعطف‌تر برای ارجاع‌دهی استفاده می‌کنیم.

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

لیست کشویی وابسته برای داده‌های تکراری: راه‌حل‌های عملی

یکی از چالش‌های رایج در Dependent Dropdown در اکسل زمانی است که داده‌های تکراری داریم؛ مثلا نام یک محصول در چند دسته تکرار شده یا یک عنوان مشابه در چند بخش دیده می‌شود. در این حالت، اگر فقط بر اساس «متن» بخواهید وابستگی بسازید، ممکن است اکسل به محدوده اشتباه ارجاع دهد یا لیست دوم خروجی دقیقی ندهد.

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

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

آنچه در ویدیو گفته ام

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