کاربرد range_lookup در فرمول vlookup برای جستجوی بازه ای در اکسل
توابع جستجوی اکسل مانند Vlookup ، Hlookup و Match با منطق جستجوی مشابه کار می کنند. ایده اصلی این توابع، جستجوی یک مقدار در لیست است.این توابع جستجوی خود را به دو صورت تطابق دقیق (exact match) و تطابق بازه ای (approximate match) انجام می دهند. این مطلب نحوه انجام تطابق بازه ای در اکسل را نشان می دهد.
از آنجایی که تابع Vlookup احتمالاً پرکاربردترین و آشناترین تابع جستجو است، برای ساده کردن این مطلب، از این تابع استفاده خواهیم کرد.
بنابراین قبل از مطالعه این مطلب، باید به کارگیری فرمول Vlookup را به خوبی فرا گرفته باشید. پس ابتدا سعی کنید مطلب زیر را به دقت مطالعه و درک کنید.
بیایید این آموزش را با یک مثال ساده دنبال کنیم. داده های جدول های زیر را در نظر بگیرید.
در تصویر بالا دو جدول مشاهده می کنید. یکی شامل نام افراد و میزان حقوق دریافتی آن هاست و دیگری گروه شغلی نسبت به حقوق دریافتی را نشان می دهد. می خواهیم با استفاده از فرمول Vlookup گروه شغلی هر فرد را با توجه به میزان حقوق دریافتی او، مشخص کنیم.
برای انجام این کار تابع Vlookup از ما می خواهد که یک مقدار معلوم در جدول اول را به آن ارائه دهیم (آرگومان اول) تا این مقدار را در جدول دوم (آرگومان دوم) جستجو کند. سپس موقعیت “گروه شغلی” در جدول دوم را از ما می خواهد (آرگومان سوم).
در آخر به چهارمین آرگومان تابع Vlookup می رسیم که Range_Lookup نامگذاری شده است و می توانید مقدار TRUE یا FALSE یا هر بازنمایی دیگری از TRUE یا FALSE را به آن ارائه دهید. اکسل مقدار TRUE را به عنوان “تطبیق بازه ای” و FALSE را به عنوان “تطبیق دقیق” توصیف می کند. در این نمونه منطقی است که با انتخاب TRUE به دنبال جستجوی بازه ای باشیم.
بنابراین فرمول به صورت زیر در می آید.
خروجی تابع در جدول زیر قابل مشاهده است.
عملکرد تابع به صورت زیر است:
زمانی که از approximate match استفاده می کنید، اکسل در بین داده ها جستجو می کند تا زمانیکه مقداری بزرگتر از مقدار معلوم (آرگومان اول) یافت شود. به عنوان مثال مقدار ۶۳۰۰۰۰۰ را در جدول “تعرفه”، ردیف به ردیف بررسی می کند و سعی می کند تعیین کند که در کدام ردیف متوقف شود. این حرکت ادامه می یابد تا زمانی که ردیفی را پیدا کند که کمتر از این مقدار باشد و ردیف بعدی آن بزرگتر باشد. سپس مقدار جلوی آن را بر می گرداند. پس چون ۵۰۰۰۰۰۰ کمتر از ۶۳۰۰۰۰۰ است و ۱۰۰۰۰۰۰۰ بیشتر است پس مقدار جلوی ۵۰۰۰۰۰۰ را در سلول D7 برمی گرداند.
به همین دلیل حتما باید محدوده جستجو به صورت صعودی (ascending) مرتب سازی شده باشد. در غیر اینصورت ممکن است نتایجی کاملا اشتباه برگردانده شود.
از این روش برای جستجو روی متن ها استفاده نکنید چون نتایج برگشتی از vlookup در این حالت قابل درک یا توضیح نیست.
با تشکر از آموزش خوبتون approximate match به معنای تطابق تقریبیه نه تطابق بازه ای، تابع براساس بهترین و نزدیک ترین تقریب خروجی از بین داده ها انتخاب میکند و اساسا بازه ای نیست و برای آرگومان بازه ای تعریف نشده است.
متشکر از همراهیتون، اگر مطلب رو مطالعه کنید متوجه میشید که اساسا بازه ای است و در واقع این ترجمه مربوط به range-lookup هست.
با تشکر از دقت شما واژه approximate match را ما کاربران روی این حالت از فرمول vlookup گذاشته ایم. اما در واقع ترجمه صحیح ورودی آخر vlookup که range_lookup نام دارد و با دادن True تایید می کنیم که می خواهیم از آن استفاده کنیم به معنای جستجوی بازه ای هست. این واژه های بهترین و نزدیکترین اتفاقا اصلا صحیح نیست و vlookup در این حالت در بازه ها جستجو می کند و نزدیک ترین را بدست نمی آورد. بطور کلی وقتی از exact match یعنی تطابق دقیق استفاده نمی کنیم می گوییم جستجوی تقریبی که لفظ خوبی نیست. بطور… ادامه...
دقیقا حرف شما درسته حتی اسم تابع جستجوی بازه ای هم لفظیه، بطور مثال گروه شغلی ۱-۵ از نظر منطق ماشینی یه اسم و کرکتره نه بازه و معنای بخصوصی برای تابع ندارد و هر اسمی می تواند باشد مثال گروه A، خروجی تابع دسته بندی مقادیر داده های خامه و بر اساس نزدیک ترین تقریب به داده مرجع انجام میشه، اگه بخواهیم بحث مقادیر بازه ای بصورت ریاضی بسط بدیم کاملا متفاوته ولی چون خروجی تابع دسته بندی و کاربرد بازه ای داره شاید کاربردی ترین تعریف همین باشه لازم به ذکره بخاطر همینه که تابع بر اساس مقادیر… ادامه...
نه دوست عزیز، بازه ستون اول هست یعنی حقوق ۰ تا ۵ میلیون، ۵ تا ۱۰ میلیون و … ما از خودمون کلمه ای اختراع نمی کنیم. جستجوی بازه ای ترجمه Range_Lookup هست. من پیشنهاد می کنم یکبار کامل و با دقت مطلب را مطالعه کنید.
An optional logical argument, that describes what the
function should return in the event that it does not find an exact match to the lookup_value. (range lookup )
ستون اول متشکل از داده های صحیحه که بصورت افزایشی نوشته شدن میتونن لزوما معنای بازه رو ندن، خروجی تابع بصورت دسته بندی منطقیه و محدود سازی و نسبت دادن داده های ستون ۳ به ۲ براساس نزدیک ترین داده به مقدار ستون ۱ که منطقیه میتونه بازه برداشت بشه، که تعریف خود مایکروسافتم همینه
با مثال میگم: وقتی حقوق یک نفر بین ۰ تا ۵ میلیون است میشه گروه شغلی اول که اون ۰ تا ۵ میلیون در واقع بازه هست. بخاطر همین گفته میشه جستجوی بازه ای (که بیان دیگری از جستجوی غیر دقیق هست.) در حقیقت فرمول vlookup در ورودی آخر از شما میپرسه که Range_Lookup (جستجوی بازه ای) نیاز دارید یا خیر؟ که در این مطلب آموزشی ما حالتی که پاسخ آن مثبت (True) است را آموزش داده ایم. در مطلب دیگری هم حالت خیر که ورودی آخر False می شود را توضیح داده ایم. ستون اول متشکل از داده های… ادامه...
حرف شما درسته ولی مثال زیرو توجه کنید ، از صد نفر خواسته میشه عدد رندومی بگن از صد نفر دیگه خواسته میشه همین سوالو پاسخ بدن، جداول دو عدد و اسامی نوشته میشه و داده های گروه دوم بصورت صعودی با اسامی نوشته ثبت میشه به عنوان ارگومان دوم، اگه ازین تابع استفاده کنیم خروجی تابع نزدیکترین قرابت دوعدد حدس زده شده توسط دونفر میشه لزوما معنای بازه در این مثال نداره، منطقیه اگه کاربرد مد نظر باشه بازه بندی منطقی ترین کاربرد تابع range lookup
نه، فرض کنید عدد ۶ را بین اعداد ۱ و ۳ و ۷ جستجو کنیم. در این حالت پاسخ ۳ خواهد بود در حالیکه ۶ به ۷ نزدیک تر هست. یعنی نتیجه نه تقریب زدن هست و نه نزدیکی. بازه هست. یه تست بکنید متوجه منظورم میشید.
تست کردم پاسخ ۷، خروجی فانکشن تقریب به بالا نه تقریب به پائین، مثال نقض حرف من اینه فرض کنی عدد ۶ رابخواهیم از سه عدد ۱ ۵ ۵۲ تقریب نزدیک ترین عدد بزنیم بطور قطع ۵ و حتی ۱ به ۶ نزدیک تره ولی چون خروج تقریب با شرط بزرگتری تنها عدد موجوده ۵۲ انتخاب میشه که شرط بزرگتری در نظر بگیرم متناقض نیست، کل این تابع یه برنامه ده خطیه تقریب با شرط بزرگتر بودن و آرگومان ورودی بازه نیست مفهوم بازه ای میشه براش قائل شد. با تشکر از جوابتون
شرطِ اینکه ورودی آخر را True بزنید این هست که داده ها بصورت صعودی مرتب شده باشه (این شرط در راهنمای خود پنجره VLookup هم نوشته شده) بنابراین تست شما اشتباه هست و جواب ۳ خواهد بود. خروجی تابع تقریب نیست. بازه هست. آرگومان ورودی Range_Lookup هست که ترجمه دقیقش جستجوی بازه ای هست.
بله ممنون
خواهش می کنم.