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

برخلاف مایکروسافت اکسل ، هیچ جادوگری VLOOKUP وجود ندارد که بتواند در Google Sheets به شما کمک کند ، بنابراین باید فرمول را به صورت دستی تایپ کنید.

نحوه کار VLOOKUP در Google Sheets

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

اولین مقدار کلید جستجوی مورد نظر شماست ، و دوم دامنه سلولی که جستجو می کنید (به عنوان مثال ، A1 تا D10). استدلال سوم ، شماره فهرست ستون از دامنه شما برای جستجو است ، جایی که اولین ستون در دامنه شما عدد 1 است ، ستون بعدی شماره 2 و غیره.

استدلال چهارم این است که آیا ستون جستجو مرتب شده است؟ یا نه.

آرگومان نهایی تنها در صورتی مهم است که به دنبال نزدیکترین مسابقه به مقدار کلید جستجوی خود باشید. اگر ترجیح می دهید مطابقت دقیق را به کلید جستجوی خود برگردانید ، این آرگومان را روی FALSE قرار دهید.

در اینجا مثالی از نحوه استفاده از VLOOKUP آورده شده است. یک صفحه گسترده شرکت ممکن است دو برگ داشته باشد: یکی با لیستی از محصولات (هرکدام با شماره شناسه و قیمت) و دوم با لیستی از سفارشات.

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

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

با استفاده از VLOOKUP روی یک صفحه واحد

برای این مثال ، بیایید به شما بگوییم. دو جدول با داده های روی یک صفحه واحد داشته باشید. جدول اول لیستی از اسامی کارمندان ، شماره شناسه ها و روزهای تولد است.

در جدول دوم ، می توانید از VLOOKUP برای جستجوی داده هایی استفاده کنید که از جدول اول (نام ، شماره شناسه یا تولد) استفاده می کند. در این مثال ، ما از VLOOKUP برای تهیه شماره شناسه کارمند خاص استفاده خواهیم کرد.

فرمول VLOOKUP مناسب برای این [ = VLOOKUP (F4، A3: D9، 4، FALSE) است .

برای تجزیه کردن این ، VLOOKUP از مقدار سلول F4 (123) به عنوان کلید جستجو استفاده می کند و جستجو می کند. دامنه سلولهای A3 تا D9. این داده ها را از ستون شماره 4 در این محدوده باز می گرداند (ستون D ، "تولد") ، و همانطور که می خواهیم یک مسابقه دقیق داشته باشیم ، آرگومان نهایی FALSE است.

در این حالت ، برای شناسه شماره 123 ، VLOOKUP یک تولد را برمی گرداند. از 19/12/1971 (با استفاده از قالب DD / MM / YY). ما این مثال را با اضافه کردن ستونی به جدول B برای نام خانوادگی ، بیشتر می کنیم و تاریخ تولد را به افراد واقعی پیوند می دهیم.

این فقط به یک تغییر ساده در فرمول نیاز دارد. به عنوان مثال ، در سلول H4 ، = VLOOKUP (F4، A3: D9، 3، FALSE) نام خانوادگی را که با شناسه شماره 123 مطابقت دارد جستجو می کند.

به جای بازگشت تولد ، داده ها را از ستون شماره 3 ("نام خانوادگی") مطابق با مقدار شناسه واقع در ستون شماره 1 ("ID") برمی گرداند.

از VLOOKUP با چند برگ استفاده کنید

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

به جای استفاده از یک محدوده سلولی معمولی مانند A3: D9 ، می توانید کلیک کنید روی یک سلول خالی ، و سپس تایپ کنید: = VLOOKUP (A4 ، کارمندان! A3: D9 ، 4 ، FALSE) .

وقتی نام صفحه را به ابتدای دامنه سلول اضافه می کنید (کارمندان! A3: D9) ، فرمول VLOOKUP می تواند از داده های یک برگ جداگانه در جستجوی خود استفاده کند.

با استفاده از Wildcards با VLOOKUP

نمونه های فوق از مقادیر اصلی کلید جستجو برای یافتن داده های تطبیق استفاده می کنند. اگر یک مقدار کلید اصلی جستجوی دقیق ندارید ، می توانید از VCOOKUP نیز مانند کارتهای علامت سؤال یا ستاره استفاده کنید.

برای این مثال ، ما از همان مجموعه داده ها در مثالهای فوق استفاده خواهیم کرد ، اما اگر ستون «نام» را به ستون A منتقل کنیم ، می توانیم برای جستجوی نام خانوادگی کارمندان از یک اسم جزئی و یک کارت علامت ستاره استفاده کنیم.

فرمول VLOOKUP برای جستجوی نام خانوادگی با استفاده از یک اسم جزئی جزئی است. = VLOOKUP (B12، A3: D9، 2، FALSE)؛ مقدار کلید جستجوی شما در سلول B12 می رود.

در مثال زیر ، "Chr *" در سلول B12 با نام خانوادگی "Geek" در نمونه جدول جستجو.

در جستجوی نزدیکترین مسابقه با VLOOKUP

می توانید از آرگومان نهایی یک فرمول VLOOKUP استفاده کنید. یا یک مسابقه دقیق یا نزدیکترین را با مقدار کلید جستجوی خود جستجو کنید. در مثالهای قبلی ما یک جستجوی دقیق را جستجو کردیم ، بنابراین این مقدار را برای FALSE تنظیم کردیم.

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

در جدول زیر ، ما لیست مواردی را برای خرید قرار داده ایم (A3 تا B9 ) همراه با نام و قیمت کالا طبقه بندی شده اند با قیمت از پایین ترین به بالاترین. بودجه کل ما برای هزینه کردن برای یک مورد تنها 17 دلار است (سلول D4). ما از فرمول VLOOKUP برای یافتن مقرون به صرفه ترین مورد در لیست استفاده کردیم.

فرمول VLOOKUP مناسب برای این مثال = VLOOKUP (D4، A4: B9، 2، TRUE) . از آنجا که این فرمول VLOOKUP تنظیم شده است که نزدیکترین مسابقه را پایین تر از مقدار جستجو پیدا کند ، فقط می تواند به دنبال کالاهایی ارزان تر از بودجه تعیین شده 17 دلار باشد.

در این مثال ، ارزان ترین مورد زیر 17 دلار کیف است که هزینه آن 15 دلار ، و این موردی است که فرمول VLOOKUP به عنوان نتیجه D5 برگشت داده است.