VLOOKUP - زمان و نحوه استفاده از آن

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

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

در ساده ترین شکل، تابع VLOOKUP می گوید:

=VLOOKUP (آنچه را می‌خواهید جستجو کنید، جایی که می‌خواهید آن را جستجو کنید، شماره ستون در محدوده حاوی مقداری که باید برگردانید، مطابقت تقریبی یا دقیق را برمی‌گرداند – نشان داده شده به عنوان ۱/TRUE یا ۰/FALSE).

نکته: راز VLOOKUP این است که داده های خود را سازماندهی کنید تا مقداری که جستجو می کنید (Fruit) در سمت چپ مقدار بازگشتی (مقدار) مورد نظر شما باشد.

جزییات فنی

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

نحو

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

مثلا:

=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP(“Fontana”,B2:E7,2,FALSE)
=VLOOKUP(A2،’جزئیات مشتری’!A:F,3,FALSE)

نام آرگومان	شرح
lookup_value (الزامی)	ارزشی که می خواهید جستجو کنید. مقداری که می خواهید جستجو کنید باید در ستون اول محدوده سلول هایی باشد که در آرگومان *table_array* مشخص می کنید . به عنوان مثال، اگر *آرایه جدول،* سلول های B2:D7 را در بر می گیرد، پس lookup_value شما باید در ستون B باشد. *Lookup_value* می تواند یک مقدار یا یک مرجع به یک سلول باشد.
table_array (الزامی)	محدوده سلول هایی که VLOOKUP در آن ها lookup_value و مقدار بازگشتی را جستجو می کند. می توانید از یک محدوده نامگذاری شده یا یک جدول استفاده کنید و می توانید از نام ها در آرگومان به جای ارجاعات سلولی استفاده کنید. ستون اول در محدوده سلول باید حاوی *lookup_value* باشد. محدوده سلولی همچنین باید شامل مقدار بازگشتی باشد که می خواهید پیدا کنید. نحوه انتخاب محدوده ها در یک کاربرگ را بیاموزید.
col_index_num (الزامی)	شماره ستون (با ۱ برای سمت چپ ترین ستون table_array شروع می شود ) که حاوی مقدار بازگشتی است.
range_lookup (اختیاری)	یک مقدار منطقی که مشخص می کند آیا می خواهید VLOOKUP یک مطابقت تقریبی یا دقیق را پیدا کند: مطابقت تقریبی – ۱/TRUE فرض می کند که ستون اول جدول به صورت عددی یا حروف الفبا مرتب شده است و سپس نزدیکترین مقدار را جستجو می کند. این روش پیش فرض است اگر یکی را مشخص نکرده باشید. به عنوان مثال، =VLOOKUP(90,A1:B100,2,TRUE). تطابق دقیق – ۰/FALSE مقدار دقیق را در ستون اول جستجو می کند. برای مثال =VLOOKUP(“Smith”,A1:B100,2,FALSE).

چه طور باید شروع کرد

برای ساختن نحو VLOOKUP به چهار اطلاعات نیاز دارید:

مقداری را که می خواهید جستجو کنید، مقدار جستجو نیز نامیده می شود.
محدوده ای که مقدار جستجو در آن قرار دارد. به یاد داشته باشید که مقدار جستجو همیشه باید در اولین ستون در محدوده باشد تا VLOOKUP به درستی کار کند. به عنوان مثال، اگر مقدار جستجوی شما در سلول C2 است، محدوده شما باید با C شروع شود.
شماره ستون در محدوده ای که حاوی مقدار بازگشتی است. به عنوان مثال، اگر B2:D11 را به عنوان محدوده مشخص کنید، باید B را به عنوان ستون اول، C را به عنوان دومین و غیره بشمارید.
به صورت اختیاری، اگر می‌خواهید مطابقت تقریبی داشته باشید، می‌توانید TRUE یا اگر می‌خواهید تطابق دقیق با مقدار بازگشتی داشته باشید، FALSE را تعیین کنید. اگر چیزی را مشخص نکنید، مقدار پیش‌فرض همیشه TRUE یا مطابقت تقریبی خواهد بود.

حالا تمام موارد بالا را به صورت زیر کنار هم قرار دهید:

=VLOOKUP(مقدار جستجو، محدوده حاوی مقدار جستجو، شماره ستون در محدوده حاوی مقدار بازگشتی، تطابق تقریبی (TRUE) یا تطابق دقیق (FALSE)).

مثال ها

در اینجا چند نمونه از VLOOKUP آورده شده است:

مثال ۱

مثال ۲

مثال ۳

مثال ۴

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

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

در اینجا، ستون‌های AF و H دارای مقادیر یا فرمول‌هایی هستند که فقط از مقادیر در کاربرگ استفاده می‌کنند و بقیه ستون‌ها از VLOOKUP و مقادیر ستون A (کد مشتری) و ستون B (Attorney) برای دریافت داده‌ها از جداول دیگر استفاده می‌کنند.

جدولی که دارای فیلدهای مشترک است را در یک کاربرگ جدید کپی کنید و نامی برای آن بگذارید.
روی Data > Data Tools > Relationships کلیک کنید تا کادر گفتگوی Manage Relationships باز شود.

برای هر رابطه فهرست شده، به موارد زیر توجه کنید:
- فیلدی که جداول را پیوند می دهد (در پرانتز در کادر محاوره ای فهرست شده است). این مقدار lookup_value برای فرمول VLOOKUP شما است.
- نام جدول جستجوی مرتبط. این جدول_آرایه در فرمول VLOOKUP شما است.
- فیلد (ستون) در جدول جستجوی مرتبط که داده های مورد نظر شما را در ستون جدید شما دارد. این اطلاعات در گفتگوی Manage Relationships نشان داده نمی شود – باید به جدول جستجوی مرتبط نگاه کنید تا ببینید کدام قسمت را می خواهید بازیابی کنید. می خواهید شماره ستون (A=1) را یادداشت کنید – این col_index_num در فرمول شما است.
برای افزودن یک فیلد به جدول جدید، فرمول VLOOKUP خود را در اولین ستون خالی با استفاده از اطلاعاتی که در مرحله ۳ جمع آوری کردید، وارد کنید.در مثال ما، ستون G از Attorney ( مقدار_ lookup_value ) برای دریافت داده‌های نرخ صورت‌حساب از ستون چهارم (col_index_num = ۴) از جدول کاربرگ Attorneys، tblAttorneys (table_array) ، با فرمول =VLOOKUP([@Attorney]، استفاده می‌کند. tbl_Attorneys,4,FALSE) .این فرمول همچنین می تواند از یک مرجع سلولی و یک مرجع محدوده استفاده کند. در مثال ما، =VLOOKUP(A2،’Attorneys’!A:D,4,FALSE ) خواهد بود .
به افزودن فیلدها ادامه دهید تا زمانی که تمام فیلدهای مورد نیاز خود را داشته باشید. اگر می‌خواهید یک کتاب کار حاوی ویژگی‌های داده تهیه کنید که از چندین جدول استفاده می‌کند، منبع داده ویژگی داده را به جدول جدید تغییر دهید.

مشکلات رایج

مسئله	چه چیزی اشتباه پیش رفت
مقدار اشتباه برگردانده شد	اگر *range_lookup* درست باشد یا کنار گذاشته شود، ستون اول باید بر اساس حروف الفبا یا عدد مرتب شود. اگر ستون اول مرتب نشده باشد، مقدار بازگشتی ممکن است چیزی باشد که انتظارش را ندارید. یا ستون اول را مرتب کنید یا از FALSE برای مطابقت دقیق استفاده کنید.
#N/A در سلول	اگر *range_lookup* درست است، اگر مقدار در *lookup_value* کوچکتر از کوچکترین مقدار در ستون اول جدول_آرایه باشد ، مقدار خطای #N/A را دریافت خواهید کرد. اگر *range_lookup* FALSE باشد، مقدار خطای #N/A نشان می‌دهد که عدد دقیق پیدا نشده است. برای اطلاعات بیشتر در مورد رفع خطاهای #N/A در VLOOKUP، به نحوه تصحیح خطای #N/A در تابع VLOOKUP مراجعه کنید.
#مرجع! در سلول	اگر *col_index_num* بیشتر از تعداد ستون‌های *آرایه جدول* باشد ، #REF را دریافت خواهید کرد! مقدار خطا برای اطلاعات بیشتر در مورد حل و فصل #REF! خطاها در VLOOKUP، ببینید چگونه یک #REF را اصلاح کنیم! خطا.
#ارزش! در سلول	اگر *آرایه_جدول* کمتر از ۱ باشد، #VALUE را دریافت خواهید کرد! مقدار خطا برای اطلاعات بیشتر در مورد حل و فصل #VALUE! خطاها در VLOOKUP، ببینید چگونه یک #VALUE را اصلاح کنیم! خطا در تابع VLOOKUP .
#نام؟ در سلول	نام؟ مقدار خطا معمولاً به این معنی است که فرمول دارای نقل قول است. برای جستجوی نام یک فرد، مطمئن شوید که از نقل قول در اطراف نام در فرمول استفاده می کنید. برای مثال، نام را به صورت “Fontana” در =VLOOKUP(“Fontana”,B2:E7,2,FALSE) وارد کنید. برای اطلاعات بیشتر، نحوه تصحیح #NAME را ببینید! خطا .
#ریختن! در سلول	این #نشتی خاص! خطا معمولاً به این معنی است که فرمول شما برای مقدار جستجو بر تقاطع ضمنی تکیه می کند و از یک ستون کامل به عنوان مرجع استفاده می کند. به عنوان مثال، =VLOOKUP( A:A ,A:C,2,FALSE). می توانید مشکل را با لنگر انداختن مرجع جستجو با عملگر @ مانند این حل کنید: =VLOOKUP( @A:A ,A:C,2,FALSE). یا می توانید از روش سنتی VLOOKUP استفاده کنید و به جای کل ستون به یک سلول واحد مراجعه کنید: =VLOOKUP( A2 ,A:C,2,FALSE).

بهترین شیوه ها

این کار را انجام دهید	چرا
از مراجع مطلق برای range_lookup استفاده کنید	استفاده از مراجع مطلق به شما امکان می دهد فرمول را پر کنید تا همیشه به همان محدوده جستجوی دقیق نگاه کند. نحوه استفاده از مراجع سلول مطلق را بیاموزید .
مقادیر شماره یا تاریخ را به عنوان متن ذخیره نکنید.	هنگام جستجوی مقادیر عدد یا تاریخ، مطمئن شوید که داده‌های ستون اول *table_array* به عنوان مقادیر متنی ذخیره نمی‌شوند. در غیر این صورت، VLOOKUP ممکن است مقدار نادرست یا غیرمنتظره ای را برگرداند.
ستون اول را مرتب کنید	اولین ستون جدول_آرایه را *قبل* از استفاده از VLOOKUP مرتب کنید وقتی range_lookup درست است.
از کاراکترهای wildcard استفاده کنید	اگر *range_lookup* FALSE و *lookup_value* متن است، می‌توانید از کاراکترهای wildcard – علامت سوال (؟) و ستاره () – در lookup_value* استفاده کنید . علامت سوال با هر کاراکتری مطابقت دارد. یک ستاره با هر دنباله ای از کاراکترها مطابقت دارد. اگر می خواهید یک علامت سوال یا ستاره واقعی پیدا کنید، یک علامت (~) در جلوی کاراکتر تایپ کنید. به عنوان مثال، =VLOOKUP(“Fontan?”,B2:E7,2,FALSE) تمام نمونه های فونتانا را با حرف آخر جستجو می کند که می تواند متفاوت باشد.
اطمینان حاصل کنید که داده های شما حاوی نویسه های اشتباه نیست.	هنگام جستجوی مقادیر متن در ستون اول، مطمئن شوید که داده‌های ستون اول دارای فاصله‌های اصلی، فاصله‌های انتهایی، استفاده ناسازگار از علامت‌های نقل قول مستقیم ( ‘ یا ” ) و مجعد ( ‘ یا “) یا نویسه‌های غیرچاپی نباشد. در این موارد، VLOOKUP ممکن است مقدار غیرمنتظره ای را برگرداند. برای به دست آوردن نتایج دقیق، سعی کنید از تابع CLEAN یا تابع TRIM برای حذف فضاهای انتهایی پس از مقادیر جدول در یک سلول استفاده کنید.