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

جستجوی چند شرطی با تابع VLOOKUP در اکسل، یکی از انواع روش های یافتن داده ها با بیش از یک شرط (Multiple Criteria Lookup) می باشد. در این حالت، شما عمل جستجو را بر اساس یک سری معیارها انجام می دهید. جستجوی چند شرطی در اکسل به اشکال مختلفی قابل انجام است که در این مقاله، به روش انجام آن با تابع VLOOKUP می پردازیم.

منظور از جستجوی چند شرطی و تابع VLOOKUP چیست؟

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

اگر کاربر برنامه اکسل هستید، حتما برای شما هم پیش آمده است که بخواهید جستجو را به صورت ستونی در اکسل انجام دهید. سپس مقداری که آن ستون دارد را در همان ردیف اما ستونی دیگر یافته و مشاهده کنید. برای انجام این کار، یک تابع متمایز (VLOOKUP) در نظر گرفته شده که با عنوان کامل Vertical Lookup شناخته می شود. در یک تعریف ساده می توان راجع به چیستی این تابع گفت:

• جستجوی چند شرطی با تابع VLOOKUP در اکسل به شما این امکان را می دهد که فرآیند سرچ کردن را به صورت عمودی انجام داده و مقدار مورد نظر را در همان ردیف بیابید. البته لازم است ستون هایی که پارامتر و مقداردهی در آنها قرار گرفته اند را به صورت مجزا مشخص کنید.

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

آموزش استفاده از تابع VLOOKUP در اکسل + ساختار فرمول و مثال ساده

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

تابع VLOOKUP (a,b,c,d) به عنوان قابلیت جستجوی چند شرطی با تابع VLOOKUP در اکسل، دارای 4 بخش برای مقدار گیری است که فرمول آن بدین شرح است:

• a: این پارامتر که به آن lookup_value نیز گفته می شود، مقداری است که می خواهید در ستون اول جستجوی شما یافت شود. این مقدار می تواند یک عدد، یک متن یا یک سلول باشد که انتخاب شده است.
• b: این پارامتر که table_array نام دارد، یک بازه را مشخص می کند تا جستجو در آن بازه انجام گردد. مثلا می توان به A5:D2 اشاره کرد که نشان می دهد شما از اولین سلول یعنی A5 تا سلول انتهایی یعنی D2 قصد جستجوی داده مدنظرتان را دارید.
• c: در این بخش با عنوان number of the column، شما می توانید عدد ستونی که داده در آن قرار گرفته است را تعیین کنید. این کار می تواند به تابع بفهماند که داده مد نظر شما در بازه ای که انتخاب کردید، مربوط به کدام یک از ستون ها می شود.
• d: این مورد از تابع جستجوی چند شرطی با تابع VLOOKUP در اکسل، اختیاری بوده و شما می توانید از طریق آن، نسبت به دقیق یا تقریبی بودن جستجوی خود اقدام نمایید. توجه داشته باشید که مقدار دهی این پارامتر فقط در 2 حالت قابل تعریف است که عبارتند از FALSE و TRUE که می توانید آن را به صورت همان کلمه یا 0 و 1 درج نمایید.

مثال ساده از جستجوی چند شرطی با تابع VLOOKUP در اکسل

حال یک مثال ساده تر را برای درک جستجوی چند شرطی در اکسل با VLOOKUP بیان می کنیم. فرض نمایید ما می خواهیم بودجه “Airfare” که مقدار آن “$800” است را بدست آوریم (مطابق تصویر زیر). برای این کار، مراحل زیر را انجام دهید:

1. یک سلول را انتخاب کنید تا تابع نویسی و نتیجه را در آن مشاهده کنید.
2. حال فرمول روبرو را در آن قرار دهید: VLOOKUP (a,b,c,d).
   1. به جای مقدار a، سلول Airfare را انتخاب نمایید تا مقدار آن به جای a قرار گیرد.
   2. به جای مقدار b، جدولی که می خواهید در آن جستجو انجام شود را انتخاب نمایید. در اینجا ما از سلول Airfare تا سلول $150 را انتخاب می کنیم.
   3. در قسمت سوم، عدد ستونی که داده در آن قرار گرفته است را مشخص می نمایید. در این مثال، ستون دوم حاوی داده مدنظر ما است که عدد 2 جایگذاری می شود.
   4. به جای مقدار d، عبارت FALSE را قرار دهید تا جستجو دقیق باشد.
3. حال که فرمول نویسی تکمیل شد، پرانتز را بسته و گزینه Enter را بزنید. اکنون مشاهده می کنید که مقدار مدنظر ما یعنی عدد “$800” در سلول مرحله اول نمایش داده شده است.

آموزش جستجوی چند شرطی با تابع VLOOKUP در اکسل با مثال های بیشتر

یکی دیگر از مثال ها مربوط به زمانی است که چندین معیار مدنظر ما باشند. در این حالت، استفاده از این تابع پیچیده تر خواهد شد. در این مثال، می خواهیم میزان فروش هر فرد را براساس تاریخ و نوع محصول بیابیم.

همانطور که در تصویر مثال برای جستجوی چند شرطی با تابع VLOOKUP در اکسل مشاهده می‌کنید، میزان فروش، تاریخ و محصول 3 معیار ما هستند. آنها را برای فروشندگان در نظر گرفته ایم. برای آنکه کار ما راحت تر شود، چند شرط مدنظرمان را در یک شرط (داخل یک ستون) ادغام می کنیم:

1. در تصویر اول، چارت را که حاوی نام فروشندگان و 3 معیار ما هستند، مشاهده می نمایید.
2. ابتدا یک ستون به نام “Helper” در نظر گرفته می شود تا معیارها با هم ترکیب گردند.
3. برای ترکیب 3 ستون دیگر، از فرمول B2&”|”&C2&”|”&D2 استفاده می گردد تا داده ها با یک جداکننده “|” کنار هم بیایند.
4. برای آنکه مشکل نمایش تاریخ به شکل درست آن حل گردد، از یک تابع TEXT استفاده می کنیم. این فانکشن دارای 2 مقدار است که اولی، همان داده ای است که می خواهید به درستی نمایش داده شود و دومی، فرمتی است که می خواهید داده خود را به آن تغییر دهید. پس فرمول ما در ستون Helper بدین شکل جایگزین می شود: =B2&”|”&C2&”|”&TEXT(D2, “DD-MM-YY”)
5. برای اینکه فرمول روی همه سلول ها اعمال شود، از قسمت گوشه سمت راست پایین، نقطه را به سمت پایین، روی سلول های بعدی می کشیم.
6. حال ستون دیگری را برای نمایش نتایج VLOOKUP در نظر گرفته و فرمول =VLOOKUP(G6&”|”&H6&”|”&TEXT(I6, “DD-MM-YY”),$A$2:$E$11, 5,0) را در آن قرار دهید.
7. اکنون مشاهده می کنید که مقدار فروش هر فرد با در نظر گرفتن نوع محصول و بازه زمانی کارکرد آنها، به صورت دقیق نمایش داده می شود. مثلا فردی به نام John، تنها 100 عدد گوشی هوشمند را در تاریخ 1 جولای 2024 فروخته است. در اینجا ما فاکتور نوع محصول، تاریخ و نام فروشنده را همزمان در نظر گرفته ایم.

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

برخی از پرسش های شما کاربران راجع به جستجوی چند شرطی با تابع VLOOKUP در اکسل عبارتند از:

• چطور چندین مقدار را با تابع VLOOKUP برگردانیم؟

برای انجام این کار، باید از چند تابع مثل SMALL، INDEX و نیز ROW کمک بگیرید.

• منظور از جستجوی دوطرفه در اکسل چیست؟

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

• چگونه برای جستجوی دوطرفه (هم ستون و هم ردیف) اقدام کنیم؟

برای انجام این کار، لازم است دو تابع را با هم ترکیب نمایید که عبارتند از VLOOKUP و MATCH. در این حالت، شما می توانید موقعیت ستونی که مدنظر شما است را در تابع MATCH که داخل VLOOKUP قرار می گیرد، جایگذاری کنید مثل:

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