01 از 03
با استفاده از VLOOKUP اکسل، مقادیر تقریبی داده را پیدا کنید
عملکرد VLOOKUP چگونه کار می کند
تابع VLOOKUP اکسل، که برای جستجو عمودی است ، می تواند مورد استفاده قرار گیرد تا اطلاعات خاصی را که در جدول داده یا پایگاه داده قرار دارد، جستجو کنند.
VLOOKUP به طور معمول یک فیلد داده را به عنوان خروجی آن باز می کند. چطور این کار را می کند:
- شما یک نام یا مقدار lookup_value می دهید که به VLOOKUP می گوید که در آن ردیف یا رکورد جدول داده برای جستجوی اطلاعات مورد نظر
- شما شماره ستون را که به نام col_index_num شناخته می شود، از اطلاعاتی که می خواهید جستجو می کنید
- این تابع به دنبال lookup_value در ستون اول جدول داده میگردد
- VLOOKUP سپس اطلاعاتی را که از یک فیلد دیگر از همان رکورد استفاده می کنید را با استفاده از شماره ستون ارائه شده پیدا می کند
مرتب سازی داده ها برای اولین بار
اگر چه همیشه مورد نیاز نیست، بهتر است ابتدا مرتب سازی محدوده داده هایی را که VLOOKUP در جستجوی صعودی انجام می دهد با استفاده از اولین ستون محدوده کلید مرتب سازی مرتب کنید.
اگر داده ها مرتب نشده باشند، VLOOKUP ممکن است یک نتیجه نادرست را بازگرداند.
نحو و Argument های تابع VLOOKUP
نحو تابع به طرح تابع اشاره دارد و شامل نام تابع، براکت و استدلال است .
نحو عملکرد VLOOKUP:
= VLOOKUP (lookup_value، table_array، col_index_num، range_lookup)
جستجو _value - (مورد نیاز) مقدار برای جستجو - مانند مقدار فروخته شده در تصویر بالا
table_array - (مورد نیاز) این جدول داده هایی است که VLOOKUP به دنبال یافتن اطلاعاتی است که بعد از آن جستجو می کنید.
- table_array باید حداقل دو ستون داده داشته باشد
- ستون اول به طور معمول شامل lookup_value می باشد
col_index_num - (مورد نیاز) شماره ستون از مقدار که می خواهید پیدا کنید.
- شماره گذاری با ستون search_key به عنوان ستون 1 آغاز می شود
- اگر col_index_num به یک عدد بزرگتر از تعداد ستونهای انتخاب شده در استدلال table_array یک #REF! خطا توسط تابع بازگردانده می شود
range_lookup - (اختیاری) نشان می دهد که آیا طیف به ترتیب صعودی مرتب شده است یا خیر.
- داده ها در ستون اول به عنوان کلید مرتب سازی استفاده می شود
- ارزش بولی - TRUE یا FALSE تنها مقادیر قابل قبول هستند
- اگر حذف شود، مقدار به طور پیش فرض TRUE است
- اگر به TRUE یا حذف شده و ستون اول محدوده به ترتیب صعودی طبقه بندی نشده باشد، یک نتیجه نادرست ممکن است رخ دهد
- اگر به درست یا غلط تنظیم شده و یک جستجوی دقیق برای جستجوی _value یافت نشد، نزدیک ترین مطابقت که در اندازه یا مقدار کوچکتر است به عنوان search_key استفاده می شود
- اگر به FALSE تنظیم شده باشد، VLOOKUP فقط یک جستجوی دقیق برای جستجوی _value را می پذیرد . اگر مقادیر تطبیق چندگانه وجود دارد، اول مقدار تطبیق بازگردانده می شود
- اگر به FALSE تنظیم شده و هیچ مقدار مطابق برای کلید جستجو یافت نشد، خطای # N / A توسط تابع بازگشت می یابد
مثال: نرخ تخفیف را برای مقدار خریداری کنید
مثال در تصویر بالا از تابع VLOOKUP برای پیدا کردن نرخ تخفیف استفاده می کند که بسته به تعداد اقلام خرید شده متفاوت است.
مثال نشان می دهد که تخفیف برای خرید 19 مورد 2٪ است. این به این دلیل است که ستون مقدار شامل محدوده مقادیر است. در نتیجه، VLOOKUP نمیتواند یک بازی دقیق پیدا کند. در عوض، برای رسیدن به نرخ صحیح اختیاری، یک بازی تقریبی باید پیدا شود.
برای پیدا کردن مقادیر تقریبی:
- مرتب سازی داده ها در table_array به ترتیب صعودی؛
- تنظیم argument_ range_lookup را به TRUE تنظیم کنید
در مثال، فرمول زیر حاوی تابع VLOOKUP برای پیدا کردن تخفیف برای مقادیر کالاهای خریداری شده استفاده می شود.
= VLOOKUP (C2، $ C $ 5: $ D $ 8،2، TRUE)
حتی اگر این فرمول فقط می تواند به یک سلول برگه تایپ شود، گزینه دیگری، همانطور که در مراحل ذکر شده در زیر استفاده می شود، استفاده از کادر محاوره ای تابع برای ورود به استدلال های آن است.
- استفاده از کادر محاوره اغلب باعث می شود تا به درستی وارد بحث های تابع شود.
باز کردن جعبه محاوره VLOOKUP
مراحل استفاده شده برای ورود به VLOOKUP تابع نشان داده شده در تصویر بالا به سلول B2 عبارتند از:
- با کلیک بر روی سلول B2 آن را فعال سلول - محل که در آن نتایج عملکرد VLOOKUP نمایش داده می شود
- روی برگه Formulas کلیک کنید.
- گزینش و مرجع را از روبان را انتخاب کنید تا لیست کشویی تابع باز شود
- بر روی VLOOKUP در لیست کلیک کنید تا کادر محاوره ای تابع ظاهر شود
02 از 03
وارد کردن اشکال Argument VLOOKUP Function
اشاره به منابع سلولی
استدلال برای عملکرد VLOOKUP به خطوط جداگانه کادر محاوره وارد می شود همانطور که در تصویر بالا نشان داده شده است.
مراجع سلولی که به عنوان استدلال استفاده می شود می توانند به خط صحیح تایپ شوند، و یا همانطور که در مراحل زیر انجام می شود، اشاره کردن، که شامل محدوده مورد نظر سلول ها با اشاره گر ماوس را برجسته می کند، می تواند برای ورود آنها به کادر محاوره ای .
مزایای استفاده از اشاره گر عبارتند از:
- سریعتر از تایپ کردن است
- اشتباهات کمتر وارد شده به مراجع سلول صحیح است.
با استفاده از ارجاعات سلولی نسبی و مطلق با Arguments
استفاده از کپی چندگانه VLOOKUP غیر معمول نیست برای بازگشت اطلاعات متفاوتی از همان جدول داده ها. برای انجام این کار راحت تر، اغلب VLOOKUP را می توان از یک سلول به دیگری کپی کرد. هنگامی که توابع به سایر سلول ها کپی می شوند، باید اطمینان حاصل شود که مرجع های حاصل از سلول با توجه به مکان جدید تابع، صحت دارند.
در تصویر بالا، نشانه های دلار ( $ ) مراجع سلولی را برای argument argument table_array محاسبه می کنند که نشان می دهد که آنها سلول های مطلق هستند، به این معنی که اگر تابع به یک سلول دیگر کپی شود، تغییر نخواهد کرد. این مطلوب است به عنوان نسخه های متعدد از VLOOKUP همه مرجع همان جدول داده ها به عنوان منبع اطلاعات است.
مرجع سلولی مورد استفاده برای lookup_value، از سوی دیگر ، با نشانه های دلار محاصره نمی شود، که مرجع نسبی نسبی آن است. مراجع سلولی نسبی وقتی که آنها را برای نشان دادن موقعیت جدید خود نسبت به موقعیت داده هایی که به آن مراجعه می کنند، تغییر می کند.
وارد کردن پارامترهای تابع
- بر روی خط گرینفون _value در جعبه محاوره VLOOKUP کلیک کنید
- روی سلول C2 در برگه کلیک کنید تا وارد این مرجع سلولی به عنوان argument_key شوید
- روی خط Table_array کادر محاوره ای کلیک کنید
- برجسته کردن سلولهای C5 تا D8 در برگه برای وارد کردن این محدوده به عنوان Table_array آرگومان - عنوانهای جدول شامل نیست
- کلید F4 را روی صفحه کلید فشار دهید تا محدوده را به مرجعهای سلولی مطلق تغییر دهید
- روی خط Col_index_num کادر محاوره ای کلیک کنید
- نوع 2 را در این خط به عنوان متضاد Col_index_num تایپ کنید ، زیرا نرخ های تخفیف در ستون 2 آرگومان table_array قرار دارند
- روی خط Range_lookup کادر محاوره ای کلیک کنید
- کلمه True را به عنوان Argument Range_lookup تایپ کنید
- دکمه Enter را در صفحه کلید فشار دهید تا کادر محاوره ای بسته شود و به برگه برگردد
- پاسخ 2٪ (میزان تخفیف برای مقدار خریداری شده) باید در D2 سلول برگه ظاهر شود
- هنگامی که شما بر روی سلول D2 کلیک می کنید، تابع کامل = VLOOKUP (C2، $ C $ 5: $ D $ 8،2، TRUE) در نوار فرمول بالای برگه ظاهر می شود
چرا VLOOKUP 2٪ را به عنوان یک نتیجه به دست آورد
- در مثال، ستون Column یک مقدار دقیق برای مقدار search_key 19 را نشان نمی دهد.
- از آنجا که استدلال is_sorted به TRUE تنظیم شده است، VLOOKUP یک مقایسه تقریبی با مقدار search_key پیدا خواهد کرد.
- نزدیکترین مقدار در اندازه کوچکتر از مقدار search_key 19 است 11.
- VLOOKUP به این ترتیب درصد تخفیف در ردیف حاوی 11 را می بیند و در نتیجه یک نرخ تخفیف 2٪ را ارائه می دهد.
03 از 03
اکسل VLOOKUP کار نمی کند: # N / A و #REF خطاها
پیام های خطا VLOOKUP
پیام های خطا زیر با VLOOKUP مرتبط هستند.
A # N / A ("value not available") خطا نمایش داده می شود اگر:
- _value جستجو در ستون اول آرگومان محدوده یافت نشد
- استدلال Table_array نادرست است برای مثال، استدلال ممکن است شامل ستون های خالی در سمت چپ محدوده باشد
- استدلال Range_lookup به FALSE تنظیم شده و یک مسابقه دقیق برای استدلال جستجو_key در ستون اول محدوده یافت نشد
- استدلال Range_lookup به TRUE تنظیم شده و تمام مقادیر در ستون اول محدوده بزرگتر از کلید search_key است
#REF! ("مرجع خارج از محدوده") خطا نمایش داده می شود اگر:
- استدلال Col_index_num بیشتر از تعداد ستون ها در Table_array است