تابع vlookup شرطی |جستجوی چند شرطی در اکسل با vlookup

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

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

قبل از هر چیزی بهتر است کمی درباره ی تابع vlookup و ویژگی های آن صحبت کنیم :

معرفی تابع vlookup

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

ساختمان تابع vlookup

VLOOKUP(lookup_value , table_array , col_index_num , [range_lookup])

 lookup_value : مقدار مورد نظر خود را وارد می کنیم . به فرض مثال کد کالا ی خاص

table_array : همانطور که مشخص است محدوده جدول مورد نظر خود را وارد می کنید . مثلا جدول اطلاعات شما در  sheet1 قرار دارد . پس در این صورت مشخصات آن را وارد می کنید .

col_index_num : شماره ستون مورد نظر خود را که می خواهیم مقدار آن را به ما نمایش دهد ، وارد می کنیم .

col_index_num : این قسمت اختیاری و در عین حال بسیار مهم است !

اگر این قسمت را خالی رها کنید به طور پیش فرض True ( یا اگر عدد 1 را وارد کنید ) در نظر گرفته و اگر 0 وارد کنید ، آن را False در نظر می گیرد . منظور از  Trueیعنی این که اگر مقدار مورد نظر شما را نیافت ، نزدیک ترین مقدار به آن را نمایش بدهد . و منظور از False یعنی این که اگر مقدار دقیق تعیین شده را پیدا نکند به شما #N/A را نمایش بدهد .

نکته مهم در مورد تابع vlookup شرطی

چون در ستون های مختلف ، مقدا های مختلفی وجود دارد تابع vlookup نمی تواند مقدار آن ها را به طور دقیق تشخیص بدهد . اما راه حل چیست ؟

در ادامه با ذکر یک مثال به طور کامل تابع vlookup  شرطی را توضییح می دهیم .

یک مثال واقعی برای استفاده از تابع vlookup شرطی

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

همانطور که می بینید ما اطلاعات مورد نیاز خود را در یک sheet جداگانه وارد کردیم تا بتوانیم با استفاده از تابع vlookup  شرطی مقدار ” فی ” را محاسبه کنیم . با استفاده از vlookup & xlookup ما نمی توانیم ” فی ” را محاسبه کنیم . چون همانطور که گفتیم ، چند سلول داریم با مقدار های مختلفِ تکراری. خب راه حل چیست؟

راه حل :

ما باید یک پارامتری را  تعریف کنیم که هر ردیف را منحصر به فرد به کند ؛ تا بتوانیم از تابع vlookup  شرطی بدون هیچ مشکلی استفاده کنیم  . در ابتدا باید در جدول اصلی یک ستون ایجاد کنیم و تمام آیتم هایی که در sheet بعدی هست ( تاریخ برگه ، نام طرف حساب ، کد کالا ، نام کالا ، تعداد و فی ) را در این ستون بنویسیم .

در سلول اول این ستون ، به ترتیب با قرار دادن مساوی و & بین تک تک پارامتر هایی که باید  وارد کنیم ، آن ها را ثبت می کنیم . ( به تصویر زیر دقت کنید ) :

با ایجاد این ردیف منحصر به فرد برای هر آیتم ، آن را برای همه ی اطلاعات وارد شده sort می کنیم . حال از این ستون می توانیم به راحتی ، تابع vlookup  شرطی بگیریم . ( به تصویر زیر دقت کنید ) :

در آرگمان اول تابع vlookup ابتدا به همان ترتیبی که اطلاعات را در ستون جدید ایجاد شده در جدول اولمان ایجاد کرده ایم ، دوباره به همان روش می نویسیم . سپس در آرگمان دوم آن ، جایگاه جدول خود را معرفی می کنیم ( که در این مثال sheet1 است ) . در آرگمان سوم ، تمام این اطلاعات را تعریف می کنیم. سپس در آرگمان بعدی عدد 3 را وارد می کنیم ( دلیل آن هم به خاطر این است که  چون در جدول اطلاعات اولیه خود ” فی ” را سومین ستون به ترتیب به نوشتاری ، ثبت کرده ایم . )

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