برای درک بهتر و استفاده بهینه از تابع INDIRECT بهترین راه بررسی مثالهای مختلف از ایندایرکت است که در این مطلب به آنها پرداخته شده است.
مثال اول تابع INDIRECT استفاده از نام ماهها
فرض می کنیم داده هایی مشابه تصویر زیر داریم و میخواهیم یک لیست پائین افتادنی از نام ماههای سال داشته باشیم و با انتخاب نام ماه ، جمع فروش در آن ماه نمایش داده شود .
در مثال بالا باید ابتدا محدوده نام ماههای سال ساخته شود که مراحل ساخت آن در ویدیو توضیح داده است و در ادامه هم به صورت متنی آورده شده است .
- محدوده شامل اعداد مربوط به داده های فروردین را انتخاب می کنیم.
- در NameBox اکسل جایی که آدرس محدوده یا سلول فعال نمایش داده میشود.
- کلمه فروردین را می نویسیم و اینتر را می زینم
- با این کار یک محدوده داده با نام فروردین ایجاد کرده ایم
- می توانیم کلیدهای ترکیبی Ctrl + F3 را بزنیم تا محدوه نامهای ایجاد شده را ببینیم.
- برای اردیبهشت و خرداد هم همین کار می کنیم.
اجیاد لیست باکس و مقدار دهی به ایندایرکت
حالا باید یک لیست پائین افتادنی یا همان لیست کشویی ایحاد کنیم که ماههای سال در آن قابل انتخاب باشند و بعد که این کا را انجام دادیم ، کافیست در سلول کناری نام ماههای سال فرمول را زیر بنویسم.
=INDIRECT(B21)
توجه داشته باشید که آدرس B21 در کد بالا به عنوان نمونه وارد شده و شما باید آدرس سلول را وارد کنید که در آن می توانید ماههای سال را انتخاب کنید تا بتوانید به درستی از تابع INDIRECT استفاده نمائید.
حال خواهید دید که با انتخاب مثلا فروردین ، داده های مربوط به فروردین نمایش داده می شود .
کار تمام است و کافیست که تابع SUM را قبل از نتیجه استفاده نمائید که فرمول نهایی مانند تصویر زیر خواهد بود.
=SUM(INDIRECT(B21))
همانطور که در کد بالا مشاهده می کنید ، تابع INDIRECT را با تابع SUM ترکیب کرده ایم که در واقع نتیجه تابع را جمع میکند.
در ضمن توجه داشته باشید که اگر ورودی تابع INDIRECT خالی باشد خطای #REF! می دهد.
لیست کشویی با تابع INDIRECT اکسل
یکی از کاربردهای جالب ایندایرکت در ساخت لیست کشویی وابسته است ، یعنی شما با انتخاب یک گزینه از لیست اول ، لیست دوم را آپدیت خواهید کرد.
برای درک این موضوع نگاهی به تصویر زیر داشته باشد.
همانطور که در تصویر بالا می بینید ، با انتخاب شهر ، شهرستانهای مربوط به آن شهر نمایش داده می شود . و در این راه چالشی هم وجود دارد که در ادامه به آن خواهیم پرداخت.
قبلا گفتیم که تابع INDIRECT نام یک محدوده داده را به عنوان یک ورودی در نظر می گیرد و داده های داخل آن را نمایش می دهد.
پس در این مورد به عنوان قدم اول باید محدوده نامها را ایجاد کنیم که شامل نام استانها به عنوان نام محدوده نام و نام شهرستانها به عنوان داده های داخل محدوده نامها باشد.
بهترین و سریع ترین راه این است که محدوده مورد نظر را انتخاب کنیم و کلیدهای ترکیبی Ctrl + Shift + F3 را بزنیم.
با این کار پنجره ای باز می شود ، مشابه تصویر زیر و این امکان را می دهد که که خیلی سریع محدوه نام را ایجاد کنیم ، فقط دقت داشته باشد که در محدوده انتخابی ، ردیف اول شامل داده های باشد که میخواهیم یه عنوان نام محدوده از آنها استفاده کنیم.
همانطور که در تصویر بالا می بینید ، ردیف اول به عنوان نام محدوده لحاظ خواهد شد.
بعد از اینکه مراحل بالا را انجام دادیم و نامها ساخته شد ، کافیست که فرمول زیر را در لیست دیتاولیدیشن وارد کنیم و سلول کناری نام استانها را به عنوان لیست کشویی در نظر بگیریم که در ویدیو آموزشی به آن اشاره شده است.
=SUBSTITUTE(INDIRECT(B1)," ","_")
این که چرا از تابع SUBSTITUTE هم استفاده شده به این دلیل است که در نامگذاری محدوده های اکسل مجاز به استفاده از فاصله خالی نیستیم و با این تابع فاصله خالی را با _ جایگزین کرده ایم.
