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

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

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

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

در ویدئوی زیر یاد می گیرید چگونه یک لیست کشویی وابسته در اکسل ایجاد کنید . لیست های وابسته به صورت کاملا عملی در پکیج اکسل پیشرفته آموزش داده شده است .

پیش نیاز این آموزش مشاهده آموزش ترکیب تابع offset و match در اکسل است که پیشنهاد می کنیم حتما این آموزش را نیز مشاهده بفرمایید.

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

در ادامه با یک مثال ساده به ساخت لیست کشویی وابسته با تابع offset می پردازیم. در مثال زیر فرض کنید ما می خواهیم بر اساس انتخاب هر ستون در لیست کشویی اول ، داده های لیست کشویی دوم تغییر کند و در واقع یک لیست کشویی وابسته در اکسل در اکسل بسازیم.

در این مثال ما دو عدد لیست کشویی ایجاد می کنیم. اولین لیست کشویی در سلول G3 قرار دارد و باید نام سر ستون های ما را داشته باشد. در سلول G4 باید یک لست وابسته ایجاد کنیم. لیست وابسته ما به سلول G3 وابستگی دارد یعنی اگر سلول G3 داده “ستون A” را داشته باشد باید لیست در سلول G4 تمام محتوای ستون A را نمایش دهد.

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

ساخت فرمول Offset برای لیست وابسته

برای ایجاد تابع Offset ابتدا باید مسئله را بیان کنیم. می خواهیم با توجه به محتوای سلول G3 محدوده مورد نظر را برای ما برگرداند . نکته مهم این است که هر محدوده دارای تعدا مشخصی سطر است و ممکن است سطر ها کم و یا زیاد شوند . پس باید محدوده ما داینامیک یا پویا نیز باشد و نکته بسیار مهم تر این است که نباید در فرمول ما آدرسی به صورت محدوده Table وجود داشته باشد چون Data validation فرمول را از Table نمی خواند.

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

خیلی خب! به سراغ نوشتن تابع offset دراکسل برویم.

مرحله اول: اولین آرگومان تابع Offset یک نقطه از ما میخواهد که قدم زدن را از این نقطه آغاز کند . فرقی نمی کند کدام سلول در اکسل را انتخاب می کنید . انتخاب این سلول فقط در تعداد قدم ها در سطر و ستون تفاوت ایجاد می کند. من در این مثال سلول A3 را انتخاب می کنم.

مرحله دوم : آرگومان دوم فرمول Offset آرگومان Rows است که مفهوم آن این است چند سلول به سمت پایین و یا بالا حرکت کند. من در این مورد عدد 1 را قرار میدهم که فرمول از سلول A3 حرکت کند و یکی به سمت پایین بیاید و در سلول A4 قرار بگیرد.

مرحله سوم : در آرگومان سوم (cols) باید مشخص کنید چند سطر به سمت چپ و یا راست حرکت کند. این مورد بستگی دارد. گزینه ای که در ایجاد لیست کشویی وابسته در اکسل بسیار مورد اهمیت است همین مورد است . که سه حالت دارد:

• اگر سلول G3 کلمه ستون A را نشان میداد عدد 0 است.
• اگر سلول G3 کلمه ستون B را نشان میداد عدد 1 است.
• اگر سلول G3 کلمه ستون C را نشان میداد عدد 2 است.

اکنون کمی فکر کنیم. کدام فرمول است که یک داده را می گیرد و یک عدد بر می گرداند. تابع Match در اکسل این کار را انجام میدهد. پس در این آرگومان در تابع Offset از تابع Match کمک می گیریم.

تابع Match : فرمول Match را می نویسیم ، در آرگومان اول ( Lookup value ) داده ای که قرار است جستجو شود را می نویسیم در این مثال محتوای سلول G3 برای ما مهم است پس سلول G3 را می نویسیم. در ادامه یک محدوده می خواهد که محدوده ما جایی است که Lookup value در آن وجود دارد . در این مثال می شود A3:C3 و آرگومان آخر را روی 0 قرار می دهیم که جستجو دقیق انجام شود.

برای یادگیری کامل تابع Match میتوانید به مقاله ترکیب دو تابع index و match در اکسل مراجعه کنید.

در این جا فرمول Match کلمه درون سلول G3 را در هدر جدول جستجو می کند و عدد مربوط به آن را بر میگرداند . مثلا اگر سلول G3 کلمه ستون A باشد عدد 1 را بر میگرداند و باعث می شود فرمول Offset یکی به سمت چپ برود و ستون B را برگرداند. یک اشتباه در لیست کشویی وابسته در اکسل باعث می شود لیست وابسته ما محتوای اشتباه برگرداند. پس در ادامه فرمول را منهای عدد 1 می کنیم تا عدد درست را برگرداند.

تا اینجا فرمول ما به صورت زیر است.

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

COUNTA(OFFSET(A3,1,MATCH(G3,A3:C3,0)-1,50,1)

در فرمول بالا تابع Offset محدوده ای به اندازه 50 سلول ارتفاع و 1 سلول عرض را بر میگرداند و فرمول Counta داده های پر این محدوده را می شمارد و عدد سلول های پر را فراخوانی می کند.

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

=OFFSET(A3,1,MATCH(G3,A3:C3,0)-1,COUNTA(OFFSET(A3,1,MATCH(G3,A3:C3,0)-1,50,1)),1)

و نتیجه کار ما به صورت زیر خواهد بود

در ادامه مسیر ساخت لیست کشویی هوشمند در اکسل باید فرمول را درون Data validation کپی کنیم.

ایجاد لیست کشویی پیشرفته در اکسل

فرمول ساخته شده را به همراه مساوی کپی می کنیم سپس بر روی سلولی که می خواهیم در آن لیست کشویی وابسته در اکسل ایجاد کنیم کلیک می کنیم،  به منوی Data می رویم و یر روی ابزار Data validation کلیک می کنیم. در پنجره باز شده گزینه Allow را بر روی List قرار میدهیم و در Source فرمول را جایگذاری می کنیم.

با کلیک بر روی OK مشاهده می کنید که در سلول G4 یک لیست کشویی وابسته در اکسل ایجاد شده است که با تغییر داده درون سلول G3 محتوای داخل لیست کشویی G4 هم تغییر می کند . این لیست داینامیک است و با اضافه شدن با هر ستون ، لیست شما نیز آپدیت می شود.

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

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

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

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

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

سوالات متداول