قبلا آموزش کامل تابع VLOOKUP را داشته ایم و در این مثال کاربردی قرار است از ویلوکاپ با دو شرط استفاده کنیم ، یعنی با انتخاب نام شیت و وارد کردن کد پرسنلی اطلاعات آن نمایش داده شود.
فهرست محتوا
استفاده از ویلوکاپ با دو شرط
در این مثال که تصویر آن را در ادامه خواهید دید ما یک فایل اکسل داریم که دارای 4 شیت با نامهای “فروردین ماه” و “اردیبهشت” و “خرداد” و شیت ” گزارش” داریم.
داده ها در شیتهایی که به نام ماههای سال است به صورت یک شکل می باشد یعنی نوع و تعداد ستونها یکی است ولی تعداد ردیفها و داداهای در آنها احتمال دارد متفاوت باشد.
حالا ما میخواهیم وقتی کاربر در شیت گزارش کد پرسنلی را انتخاب کرد و نام ماه را هم وارد کرد، پاداش مربوط به آن پرسنل در ماه انتخاب شده نمایش داده شود.
لیست پائین افتادنی از نام شیتها
برای این که در شیت “گزارش” بتوانیم در سلول مربوط به ماهها از بیست پائین افتادنی از نام شیتها استفاده کنیم و ویلوکاپ با دو شرط اجرا نمائیم ، باید نام شیتها را به صورت داینامیک داشته باشیم که اگر شیتی اضافه یا حذف شد و یا تغیی رنام پیدا کرد، لیست هم آپدیت شود.
- فایل را ابتدا ذخیره میکنیم
- به تب DATA می رویم و از گزینه Get Data گزینه From File و گزینه from Excel را انتخاب می کنیم
- در پنجره باز شده ، فایلی یکه ذخیره کرده ایم را انتخاب می کینم.
- منتظر می شویم تا پنجره پاور کوئری باز شود .
- نام فایل را انتخاب کرده و گزینه Save Load را می زنیم.
- یک شیت به فایل ما اضافه می شود ، ان را به List تغییر نام می دهیم.
- فایل را ذخیریه میکنیم
- کوری را با کلیک روی آیکون آن رفرش می کنیم .
- می بینید که جدول تغییر می کند .
- روی نام آن در کوئری دابل کلییک می کنیم تا پنجره پاور کوئری مجدا باز شود.
- از قسمت Kind تیک Sheet را میزنیم
- روی ستون اول که نام شیتها هست ، راست کلیک می کنیم
- گزینه Remove Other Columns را میزنیم و
- روی گزینه Save Load کلیک میکنیم.
خواهید دی که نام شیتها را در یک جدول در شیت List داریم می توانیم از آن استفاده نمائیم.
نمایش نتیجه توسط ویلوکاپ با دو شرط
خب حالا ما لیست پائین افتادنی از نام شیتها داریم و هر وقت اضافه یا کم شد کافیست فایل را ذخیره کنیم و کوئری را رفرش نمائیم همانطور که در ویدیو گفته شده .
اما برای استفاده از VLOOKUP به شکل گفته شده کافیست مراحل زیر را انجام دهیم.
- در شیت گزارش به سلول مورد نظر می رویم.
- فرمول زیر را داخل آن می نویسیم.
=VLOOKUP(B3,INDIRECT("'"&C3&"'!$B$3:$D$120"),3,0)ما برای اینکه به نتیجه برسیم مجبوریم از تابه INDIRECT به همرا تابع VLOOKUP استفاده نمنائیم تا بتوانیم ، نام شیت را هم در شرط ذخیل کنیم.
دقت داشته باشید که اگر خواستید از فرمول بالا در برنامه اکسل خود استفاده نمائید ، به انم سلولها دقت نمائید شاید برای شما متفاوت باشد.
پرسشهای پرتکرار
چرا حتما باید فایل را ذخیره کنیم
برای اینکه نام شیتها اگر تغییر کند و یا اضافه و یا کم شود در لیست پائین افتادنی به صورت خودکار آپدیت شود.
آیا نیازی به کدنویسی و ماکرو است؟
خیر هیچ نیازی به کدنویسی و ماکرو ندارین ولی از آنجا از پاورکوئری در بخشی از آموزش استفاده شده باید از اکسل 2013 به بالا استفاده کنید.
نیاز به چه توابعی به غیر از VLOOKUP داریم؟
از تابع INDIRECT و همچنین OFFSET هم باید استفاده کنیم که استفاده OFFSET اختیاری می باشد و میتوانیم نام شیتها را خودمان تایپ کنیم که در این صورت با آفیس 2010 هم کار انجام می شود و نیاز به پاور کوئری نیست.
