ساده‌ترین روش جستجو در Excel، فشار دادن کلید ترکیبی Ctrl + F است که مثل اغلب نرم‌افزارها، پنجره‌ی Find یا سرچ را باز می‌کند و می‌توانید عبارتی را تایپ کرده و پیدا کنید. اما برای امور پیچیده‌تر و در حالتی که دیتابیس عظیمی از اطلاعات در فایل اکسل موجود باشد، این روش مناسب نیست. بهتر است از توابعی مثل VLOOKUP و HLOOKUP و MATCH و INDEX استفاده کنید.
در این مقاله به معرفی چهار تابع جستجو در اکسل مجموعه‌ی آفیس مایکروسافت و روش استفاده از این توابع می‌پردازیم.
استفاده از VLOOKUP در اکسل
کار تابع VLOOKUP در نرم‌افزار Excel این است که سلولی حاوی مقدار مشخص شده را در ستونی پیدا می‌کند و مقدار دیگری که در همان ردیف قرار گرفته را برمی‌گرداند. در واقع با VLOOKUP می‌توان به این سوال پاسخ داد که: روبروی سلولی با مقداری خاص، چه مقداری درج شده است؟
اما با دو مثال ساده شیوه‌ی استفاده از تابع VLOOKUP را بررسی می‌کنیم. البته برای اطلاعات بیشتر می‌توانید مقاله‌ای که در خصوص کار با این تابع منتشر کردیم را مطالعه کنید:
فرض کنید می‌خواهیم با وارد کردن کد کارمند، نام خانوادگی آن را به سرعت در جدول پیدا کنیم یا حالت برعکس، با وارد کردن نام خانوادگی، شماره تلفن کارمند را در جدولی عظیم و طولانی پیدا کنیم.
سینتکس کلی استفاده از تابع VLOOKUP به این صورت است:
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
اما توضیحی در مورد چهار آرگومان تابع VLOOKUP:
- [lookup_value] یا اولین آرگومان، ویژگی‌ای است که در مورد آن اطلاع داریم و آن را جستجو می‌کنیم.
- [table_array] یا دومین آرگومان، محدوده‌ای است که برای یافتن آرگومان اول جستجو می‌شود. دقت کنید که اولین ستون از محدوده‌ی وارد شده، ستونی است که داده‌ی موردنظر شما در آن قرار دارد.
- [col_index_num] یا آرگومان سوم، شماره‌ی ستونی است که اطلاعات برگشتی از آن استخراج می‌شود.
- [range_lookup] یا آرگومان چهارم که وارد کردن آن ضروری نیست، دو حالت ۰ و ۱ دارد. عدد 0 به معنی جستجوی دقیق است در حالی که عدد ۱ یا وارد نکردن آرگومان چهارم، موجب جستجوی تقریبی و یافتن نزدیک‌ترین نتیجه می‌شود.
اما توضیح بیشتر در مورد آرگومان چهارم: اگر در جدول زیر و ستون امتیاز SAT، عدد ۶۵۲ را با VLOOKUP جستجو کنید و آرگومان چهارم را ۰ وارد کنید، نتیجه‌ای در کار نیست چرا که این عدد وجود ندارد. اما اگر از ۱ استفاده کنید یا آرمان آخر را تایپ نکنید، عدد ۶۴۶ به عنوان نتیجه‌ی جستجو در ستون SAT در نظر گرفته می‌شود.
اما مثالی دیگر: فرض کنید می‌خواهید امتیاز شخصی که نام خانوادگی وی، Winters است را پیدا کنید. آرگومان اول همین عنوان است و آرگومان دوم، سلول‌های C2 الی F101 است که کل جدول را تشکیل می‌دهد. آرگومان سوم یعنی شماره‌ی ستون داده‌ی مقصد، عدد ۴ است چرا که اگر ستون C حاوی نام خانوادگی را ستون شماره ۱ در نظر بگیرید، ستون SAT شماره‌ی ۴ می‌شود. در نهایت عدد ۰ را برای آرگومان آخر تایپ می‌کنیم که جستجوی دقیق انجام شود.
و فرمول نهایی در پاسخ به این سوال ساده:
=VLOOKUP("Winters", C2:F101, 4, 0)
نتیجه را مشاهده می‌کنید که امتیاز 651 است، امتیازی که در ردیف مربوط به شخص موردنظر و در ستون SAT موجود بوده است.
بار دیگر یادآوری می‌کنیم که در مورد آرگومان دوم که رنج یا محدوده‌ی سلول‌ها است، از ستونی شروع کنید که در آن جستجو انجام می‌شود چرا که در غیر این صورت ممکن است تابع VLOOKUP نتایج اشتباهی را گزارش کند!
نکته‌ی بعدی این است که VLOOKUP فقط یک عدد را برمی‌گرداند. اگر در ستون جستجو شده، چند سلول حاوی مقدار موردنظر باشند، تنها مقداری که روبروی اولین سلول است، برگردانده می‌شود.
استفاده از HLOOKUP در اکسل
تابع HLOOKUP عملکردی مشابه VLOOKUP دارد با این تفاوت که مقداری در یکی از سلول‌های زیر سلول موردنظر را برمی‌گرداند.
سینتکس استفاده از تابع HLOOKUP به صورت زیر است:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
چهار آرگومان این تابع درست شبیه به تابع VLOOKUP است. بنابراین به ترتیب مقداری که باید در اولین ردیف جستجو شود، محدوده‌ی سلول‌ها، شماره‌ی ردیف سلول مقصد و اینکه آیا جستجوی تقریبی انجام شود یا خیر، چهار آرگومان این تابع خواهند بود.
و یک مثال ساده:
اگر بخواهیم در جدولی حاوی امتیاز متوسط هر شهر در سال‌های مختلف، امتیاز شهری که در ردیف ۲۴ قرار گرفته را در سال ۲۰۱۳ پیدا کنیم، دستور زیر مناسب است:
=HLOOKUP(2013, A1:P51, 24)
نتیجه نمایش امتیاز ذکر شده در ستون ۲۰۱۳ و ردیف ۲۴ است.
البته برای این مثال خاص که تعداد ردیف‌ها بسیار کم است، می‌توانید از VLOOKUP استفاده کنید که حتی ساده‌تر است چرا که نیازی به پیدا کردن ردیف مربوط به شهر موردنظر نیست. اما اگر تعداد ستون‌ها و در این مثال خاص، تعداد سال‌ها بسیار زیاد باشد، پیدا کردن ستون مربوط به سال ۲۰۱۳ وقت‌گیر می‌شود و لذا کار با HLOOKUP گزینه‌ی بهتری خواهد بود.
نکته‌ی دیگر این است که شماره‌ی سال در این مثال به صورت "2013" ذکر نشده چرا که رشته نیست بلکه عدد است.
در استفاده از HLOOKUP هم محدوده‌ی سلول‌ها می‌بایست با اولین ردیف که ردیف جستجو است، آغاز شود تا نتیجه‌ی صحیحی به دست آید. به علاوه اولین سلول از ردیفی که جستجو می‌شود، برای یافتن داده‌ی مقصد به کار می‌رود و سلول‌های بعدی که مقدار مشابهی دارند، لحاظ نمی‌شوند.
The INDEX and MATCH Functions
توابع INDEX و MATCH در اکسل نیز برای جستجو کردن در جداول عظیم داده، بسیار مفید هستند به خصوص اگر در فرمول‌نویسی، این دو تابع را به صورت ترکیبی استفاده کنید.
قبلاً با تابعی به اسم INDEX آشنا شدیم:
سینتکس کلی استفاده از تابع INDEX به صورت زیر است:
=INDEX([array], [row_number], [column_number])
و معنی سه آرگومان این تابع:
- [array] یا آرگومان اول رنج سلول‌ها است که جستجو در آن انجام می‌شود.
- [row_number] و [column_number] نیز شماره‌ی ردیف و شماره‌ی ستون است و برای محدود کردن نتایج استفاده می‌شود.
سینتکس کار با تابع MATCH مشابه است:
=MATCH([lookup_value], [lookup_array], [match_type])
و توضیح آرگومان‌های سه‌گانه‌ی MATCH:
- [lookup_value] یا آرگومان اول همان مقدار و عبارتی است که موردسوال است.
- [lookup_array] یا دومین آرگومان، محدوده‌ی سلول‌هایی است که جستجو می‌شود.
- [match_type] یا آرگومان سوم می‌تواند ۱ یا ۰ یا منفی ۱ باشد و البته می‌توانید آن را وارد نکنید! عدد ۱ برای برگرداندن بزرگ‌ترین نتیجه که کوچک‌تر یا مساوی عبارت سرچ شده است، عدد ۰ برای برگرداندن نتیجه‌ی دقیق و عدد منفی ۱ برای برگرداندن کوچک‌ترین نتیجه که بزرگ‌تر یا مساوی عبارت سرچ شده است، به کار می‌رود.
دو مثال ساده از کاربرد این توابع به صورت مجزا:
همان‌طور که در تصویر زیر مشاهده می‌کنید، با استفاده از تابع MATCH، شماره‌ی ردیفی که حاوی عدد ۶۴۶ است، مشخص شده که ردیف ۴ است.
و مثالی از کاربرد تابع INDEX: همان‌طور که در تصویر زیر مشاهده می‌کنید، تابع INDEX شماره‌ی ردیفی که نام شهر Anchorage در آن قرار گرفته را عدد ۶ برمی‌گرداند:
اما چطور از ترکیبی این توابع استفاده کنیم و اساساً ترکیب MATCH و INDEX چه کاربردی دارد؟
با تابع MATCH عبارتی را جستجو می‌کنیم و نتیجه که شماره‌ی ردیف است را به تابع INDEX می‌دهیم تا این تابع مقدار موجود در ستونی دیگر در همان ردیف را برگرداند.
به عنوان مثال با فرمول زیر، ردیفی که کلمه‌ی Waters ستون C آن موجود است، به تابع INDEX داده می‌شود و تابع ایندکس در همین ردیف و ستون F، سلولی را پیدا کرده و مقدار آن که نمره‌ی شخصی با فامیلی Waters است را برمی‌گرداند:
=INDEX(F:F, MATCH("Waters", C:C, 0))
در واقع این دو تابع در صورت ترکیب، کاری شبیه VLOOKUP و HLOOKUP را انجام می‌دهند.
makeuseofسیاره‌ی آی‌تی