تابع VLOOKUP یکی از توابع جستجو کردن در Google Sheets و نرم‌افزارهای مشابه نظیر Excel است که کاربردهای زیادی دارد اما برای مبتدیان کمی ناآشنا و پیچیده به نظر می‌رسد. تابع VLOOKUP عبارتی که جستجو شده را در سلول‌های محدوده‌ی سرچ پیدا می‌کند و مقداری که روبروی آن در یکی از ستون‌های بعدی قرار گرفته را برمی‌گرداند.
در ادامه با نحوه استفاده کردن از تابع VLOOKUP در گوگل شیتس آشنا می‌شویم که البته درست شبیه به کاربرد آن در اکسل آفیس مایکروسافت است.
در نرم‌افزار اکسل زمانی که می‌خواهید از VLOOKUP استفاده کنید، تایپ کردن فرمول به صورت دستی یک روش کار است و روش ساده‌تر این است که از راهنمای مرحله به مرحله‌ی اکسل استفاده کنید. در گوگل شیتس می‌بایست فرمول را به صورت دستی تایپ کنید که شاید در ابتدا کمی پیچیده به نظر برسد اما پس از چند مرتبه فرمول‌نویسی متوجه می‌شوید که استفاده از VLOOKUP در Google Sheets نیز بسیار ساده است.
تابع VLOOKUP چطور کار می‌کند؟
قبل از معرفی آرگومان‌های این تابع، بهتر است نحوه‌ی عملکرد آن را بررسی کنیم تا کاربرد هر یک از آرگومان‌های آن روشن شود. تابع VLOOKUP عبارت سرچ شده را در محدوده‌ای از سلول‌ها جستجو می‌کند و در صورت یافتن عبارت، مقدار موجود در سلولی از همان ردیف را برمی‌گرداند.
با یک مثال ساده موضوع روشن‌تر می‌شود: فرض کنید که فایلی مربوط به کارمندان یک شرکت در اختیار دارید که در آن جدولی حاوی شناسه، نام و نام خانوادگی و تاریخ تولد هر شخص ذکر شده است. بنا به نیاز می‌خواهید با وارد کردن شناسه‌ی کارمندی، تاریخ تولد کارمند مشخص شود. برای این منظور می‌توانید از تابع VLOOKUP استفاده کنید به این صورت که شناسه‌ی کارمند را در جدول جستجو کنید و سپس تاریخ تولد کارمند را از همان ردیف استخراج نمایید.
و اما ۴ آرگومان تابع VLOOKUP:
- آرگومان اول Search_Key یا داده‌ی معلوم و در واقع همان عبارتی است که در جدول جستجو می‌شود.
- آرگومان دوم Range یا محدوده‌ای از سلول‌ها است که با مشخص کردن سلول ابتدا و انتها و علامت : بینشان تعریف می‌شود.
- سومین آرگومان Index یا شماره ستون است. اولین ستون از محدوده‌ی سلول‌ها، شماره ۱ است و ستون‌های بعدی با اندیس ۲ و اندیس ۳ و ... مشخص می‌شوند.
- آرگومان آخر Is_sorted است که اگر آن را FALSE وارد کنید، نتیجه‌ی دقیق جستجو برگردانده می‌شود و اگر TRUE وارد کنید، نزدیک‌ترین مقدار به عبارت جستجو شده برگردانده می‌شود. در حالت جستجوی نزدیک‌ترین نتیجه، می‌بایست داده‌ها مرتب باشد تا VLOOKUP درست عمل کند.
نکته‌ی مهم این است که تابع VLOOKUP نمی‌تواند در ستون‌های سمت چپ شماره ستون جستجو کند. به عبارت دیگر می‌بایست معمولاً عبارتی که سرچ می‌کنید در اولین ستون از محدوده‌ی سلول‌ها موجود باشد.
استفاده از VLOOUP در یک جدول از Google Sheets
با یک مثال روش کار را توضیح می‌دهیم: فرض کنید دو جدول A و B دارید که در اولین جدول، اطلاعات کارمندان شامل شناسه و تاریخ تولد قرار گرفته است. در دومین جدول شناسه کارمندان و تاریخ تولد موجود است و می‌خواهید روبروی هر شناسه، تاریخ تولد کارمند مربوطه قرار بگیرد.
آرگومان اول F4 است و در واقع اگر در سلول F4 شناسه‌ی کارمندی تایپ شد، تابع VLOOKUP آن را در محدوده‌ی A3:D9 سرچ می‌کند و زمانی که آن را در سلولی پیدا کرد، سراغ ستون چهارم می‌رود. ستون ۱ در این مثال ستون حاوی شناسه کارمندی است و ستون ۲ و ۳ و ۴ به ترتیب حاوی نام، نام خانوادگی و تاریخ تولد است. در نهایت با توجه به اینکه می‌خواهید نتایج دقیق باشد و تقریب موردنظر شما نیست، آرگومان آخر FALSE خواهد بود. بنابراین فرمولی که در سلول G4 موردنیاز است، فرمول زیر است:
=VLOOKUP(F4, A3:D9, 4, FALSE)
پس از تایپ کردن این فرمول و زدن Enter، خروجی چاپ می‌شود.
فراموش نکنید که سلول G4 را انتخاب کنید و روی مربع کوچکی که در گوشه‌ی پایین آن ظاهر شده کلیک کرده و آن را به پایین بکشید. به این ترتیب فرمول سلول G4 در سلول‌های بعدی کپی می‌شود و ریفرنس‌ها تغییر می‌کند. به عبارت دیگر محدوده‌ی سلول‌ها از A3:D9 به A4:D10 و A5:D11 تغییر خواهد کرد که مطلوب نیست! لذا می‌بایست از ریفرنس‌دهی مطلق استفاده کنید و در واقع محدوده‌ی سلول‌ها را به صورت $A$3:$D$9 تایپ کنید. برای اطلاعات بیشتر به مقاله‌ی زیر توجه فرمایید:
به همین ترتیب اگر بخواهید روبروی شناسه‌ی کارمند، نام خانوادگی آن چاپ شود، می‌توانید از فرمول زیر استفاده کنید که صرفاً شماره ستون تغییر کرده است:
=VLOOKUP(F4, A3:D9, 3, FALSE)
نتیجه به صورت زیر خواهد بود:
استفاده از VLOOKUP در دو Sheet
در مثال قبلی همه‌ی اطلاعات در یک صفحه تعریف شده بود اما می‌توانید از VLOOKUP در جستجوی داده‌ها از صفحات مختلف نیز استفاده کنید. به عنوان مثال می‌توانید عبارتی که سرچ می‌شود را از شیت اول و محدوده‌ی سلول‌ها را از شیت دوم انتخاب کنید و خروجی را در شیت سوم چاپ کنید!
با یک مثال ساده موضوع روشن می‌شود: فرض کنید جدول اطلاعات کارمندان در اولین صفحه تعریف شده است و می‌خواهید جدول حاوی شناسه و تاریخ تولد کارمند را در شیت بعدی داشته باشید. بنابراین قبل از ذکر محدوده که A3:D9 است، می‌بایست نام شیت و علامت ! را تایپ کنید. به عنوان مثال اگر شیت اول Employees نام دارد، فرمول موردبحث به صورت زیر خواهد شد:
=VLOOKUP(A4, Employees!A3:D9, 4, FALSE)
استفاده از Wildcards در سرچ به کمک VLOOKUP
تابع VLOOKUP هم از عبارت‌های منظم یا به اصطلاح Wildcards پشتیبانی می‌کند و می‌توانید بخشی از عبارت موردنظر را سرچ کنید. به عنوان مثال اگر بخواهید سلول حاوی عبارت 12345 با سرچ کردن عبارت 234 که بخشی از آن است، به عنوان نتیجه شناسایی شود، می‌بایست از Wildcards استفاده کنید و *234* را جستجو کنید. یا مثال دیگر این است که اگر بخواهید سلولی که در ابتدای آن، عبارت abc موجود است را پیدا کنید، می‌بایست abc* را سرچ کنید. abc* به این معنی است که پس از abc ممکن است هر عبارت دلخواهی با طول نامشخص تایپ شده باشد.
اما به مثال قبلی برگردیم: فرض کنید که چند حرف از ابتدای نام کارمند مشخص است و می‌خواهید نام خانوادگی آن را با VLOOKUP پیدا کنید. اگر ابتدای نام را در سلول B12 تایپ کرده باشید، فرمول موردنیاز به صورت زیر است:
=VLOOKUP(B12, A3:D9, 2, FALSE)
حال اگر Chr* را در سلول B12 تایپ کنید، نامی که با Chr آغاز شده، به عنوان نتیجه شناسایی می‌شود و نام خانوادگی مربوطه از ستون ۲ استخراج شده و نمایش داده می‌شود.
پیدا کردن نزدیک‌ترین نتیجه با VLOOKUP
همان‌طور که در ابتدای مقاله اشاره کردیم، آرگومان آخر تابع VLOOKUP مشخص می‌کند که آیا به دنبال نتیجه‌ی دقیق هستید یا می‌خواهید نزدیک‌ترین نتیجه را پیدا کنید. در مثال‌های قبلی از FALSE استفاده کردیم که برای یافتن نتیجه‌ی دقیق کاربرد دارد.
حالت تقریبی را با یک مثال ساده‌ی دیگر توضیح می‌دهیم: فرض کنید جدولی حاوی قیمت چند کالا دارید و می‌خواهید با وارد کردن قیمت در سلولی، یک کالا را پیدا کنید. ممکن است قیمت وارد شده دقیقاً با قیمت یکی از کالاها برابر نباشد! بنابراین نتیجه‌ی تقریبی مطلوب است.
نکته بسیار مهم در پیدا کردن نزدیک‌ترین نتیجه این است که می‌بایست قیمت کالاها به صورت مرتب‌شده باشد تا VLOOKUP درست عمل کند.
پس از مرتب‌سازی قیمت‌ها از کم به زیاد، فرمول موردنیاز به این صورت است:
=VLOOKUP(D4, A4:B9, 2, TRUE)
به عبارت دیگر فرمول تابع VLOOKUP مقداری که در سلول D4 تایپ شده را در محدوده‌ی A4:B9 سرچ می‌کند و پس از یافتن نزدیک‌ترین عدد به آن، آنچه در سلول روبرو یا ستون دوم ذکر شده را برمی‌گرداند.
حال اگر عدد ۱۷ دلار را تایپ کنید که در سلول‌های ستون اول موجود نیست، نزدیک‌ترین رقم به آن که ۱۵ دلار است، انتخاب می‌شود و نام کالای روبروی آن که Bag است، برگردانده می‌شود:
howtogeekسیاره‌ی آی‌تی