• مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
  ورود
گروه تحلیلگری
  • مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
0
ثبت نام / ورود

وبلاگ

کاربرد range_lookup در فرمول vlookup برای جستجوی بازه ای در اکسل

توابع جستجوی اکسل مانند Vlookup ، Hlookup و Match با منطق جستجوی مشابه کار می کنند. ایده اصلی این توابع، جستجوی یک مقدار در لیست است.این توابع جستجوی خود را به دو صورت تطابق دقیق (exact match) و تطابق بازه ای (approximate match) انجام می دهند. این مطلب نحوه انجام تطابق بازه ای در اکسل را نشان می دهد.

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

آموزش فرمول vlookup در اکسل

بیایید این آموزش را با یک مثال ساده دنبال کنیم. داده های جدول های زیر را در نظر بگیرید.

کاربرد range_lookup در فرمول vlookup برای جستجوی بازه ای در اکسل

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

برای انجام این کار تابع Vlookup از ما می خواهد که یک مقدار معلوم در جدول اول را به آن ارائه دهیم (آرگومان اول) تا این مقدار را در جدول دوم (آرگومان دوم) جستجو کند. سپس موقعیت “گروه شغلی” در جدول دوم را از ما می خواهد (آرگومان سوم).

کاربرد range_lookup در فرمول vlookup برای جستجوی بازه ای در اکسل

در آخر به چهارمین آرگومان تابع Vlookup می رسیم که Range_Lookup نامگذاری شده است و می توانید مقدار TRUE یا FALSE یا هر بازنمایی دیگری از TRUE یا FALSE را به آن ارائه دهید. اکسل مقدار TRUE را به عنوان “تطبیق بازه ای” و FALSE را به عنوان “تطبیق دقیق” توصیف می کند. در این نمونه منطقی است که با انتخاب TRUE به دنبال جستجوی بازه ای باشیم.

بنابراین فرمول به صورت زیر در می آید.

=VLOOKUP(C4;F:G;2;1)

خروجی تابع در جدول زیر قابل مشاهده است.

کاربرد range_lookup در فرمول vlookup برای جستجوی بازه ای در اکسل

عملکرد تابع به صورت زیر است:

زمانی که از approximate match استفاده می کنید، اکسل در بین داده ها جستجو می کند تا زمانیکه مقداری بزرگتر از مقدار معلوم (آرگومان اول) یافت شود. به عنوان مثال مقدار ۶۳۰۰۰۰۰  را در جدول “تعرفه”، ردیف به ردیف بررسی می کند و سعی می کند تعیین کند که در کدام ردیف متوقف شود. این حرکت ادامه می یابد تا زمانی که ردیفی را پیدا کند که کمتر از این مقدار باشد و ردیف بعدی آن بزرگتر باشد. سپس مقدار جلوی آن را بر می گرداند. پس چون ۵۰۰۰۰۰۰ کمتر از ۶۳۰۰۰۰۰ است و ۱۰۰۰۰۰۰۰ بیشتر است پس مقدار جلوی ۵۰۰۰۰۰۰ را در سلول D7 برمی گرداند.

به همین دلیل حتما باید محدوده جستجو به صورت صعودی (ascending) مرتب سازی شده باشد. در غیر اینصورت ممکن است نتایجی کاملا اشتباه برگردانده شود.

 از این روش برای جستجو روی متن ها استفاده نکنید چون نتایج برگشتی از vlookup در این حالت قابل درک یا توضیح نیست. 

درباره آمنه نوروزیان

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

نوشته های بیشتر از آمنه نوروزیان
در تلگرام
کانال ما را دنبال کنید!
در اینستاگرام
ما را دنبال کنید!
مطالب زیر را حتما بخوانید
  • ایجاد فرمت سفارشی در اکسل

    2.08k بازدید

  • کتاب مرجع اکسل پیشرفته ۲۰۱۹

    2.06k بازدید

  • جستجوی چند شرطی در اکسل

    5.2k بازدید

  • مقایسه دو ستون در اکسل

    3.9k بازدید

  • کاربرد تابع offset در اکسل

    1.42k بازدید

  • کاربرد علامت دلار در اکسل

    3.95k بازدید

guest
تعداد ماههای سال به عدد
guest
13 نظر
قدیمی ها
جدید ها بیشترین رای
Inline Feedbacks
View all comments
انتخاب
انتخاب

با تشکر از آموزش خوبتون approximate match به معنای تطابق تقریبیه نه تطابق بازه ای، تابع براساس بهترین و نزدیک ترین تقریب خروجی از بین داده ها انتخاب میکند و اساسا بازه ای نیست و برای آرگومان‌‌ بازه ای تعریف نشده است.

1
پاسخ
آمنه نوروزیان
آمنه نوروزیان
نویسنده
در پاسخ به  انتخاب

متشکر از همراهیتون، اگر مطلب رو مطالعه کنید متوجه میشید که اساسا بازه ای است و در واقع این ترجمه مربوط به range-lookup هست.

1
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

با تشکر از دقت شما واژه approximate match را ما کاربران روی این حالت از فرمول vlookup گذاشته ایم. اما در واقع ترجمه صحیح ورودی آخر vlookup که range_lookup نام دارد و با دادن True تایید می کنیم که می خواهیم از آن استفاده کنیم به معنای جستجوی بازه ای هست. این واژه های بهترین و نزدیکترین اتفاقا اصلا صحیح نیست و vlookup در این حالت در بازه ها جستجو می کند و نزدیک ترین را بدست نمی آورد. بطور کلی وقتی از exact match یعنی تطابق دقیق استفاده نمی کنیم می گوییم جستجوی تقریبی که لفظ خوبی نیست. بطور… ادامه...

1
پاسخ
انتخاب
انتخاب
در پاسخ به  پوریا بغدادی

دقیقا حرف شما درسته حتی اسم تابع جستجوی بازه ای هم لفظیه، بطور مثال گروه شغلی ۱-۵ از نظر منطق ماشینی یه اسم و کرکتره نه بازه و معنای بخصوصی برای تابع ندارد و هر اسمی می تواند باشد مثال گروه A، خروجی تابع دسته بندی مقادیر داده های خامه و بر اساس نزدیک ترین تقریب به داده مرجع انجام میشه، اگه بخواهیم بحث مقادیر بازه ای بصورت ریاضی بسط بدیم کاملا متفاوته ولی چون خروجی تابع دسته بندی و کاربرد بازه ای داره شاید کاربردی ترین تعریف همین باشه لازم به ذکره بخاطر همینه که تابع بر اساس مقادیر… ادامه...

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

نه دوست عزیز، بازه ستون اول هست یعنی حقوق ۰ تا ۵ میلیون، ۵ تا ۱۰ میلیون و … ما از خودمون کلمه ای اختراع نمی کنیم. جستجوی بازه ای ترجمه Range_Lookup هست. من پیشنهاد می کنم یکبار کامل و با دقت مطلب را مطالعه کنید.

0
پاسخ
انتخاب
انتخاب
در پاسخ به  پوریا بغدادی

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 )
ستون اول متشکل از داده های صحیحه که بصورت افزایشی نوشته شدن میتونن لزوما معنای بازه رو ندن، خروجی تابع بصورت دسته بندی منطقیه و محدود سازی و نسبت دادن داده های ستون ۳ به ۲ براساس نزدیک ترین داده به مقدار ستون ۱ که منطقیه میتونه بازه برداشت بشه، که تعریف خود مایکروسافتم همینه

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

با مثال میگم: وقتی حقوق یک نفر بین ۰ تا ۵ میلیون است میشه گروه شغلی اول که اون ۰ تا ۵ میلیون در واقع بازه هست. بخاطر همین گفته میشه جستجوی بازه ای (که بیان دیگری از جستجوی غیر دقیق هست.) در حقیقت فرمول vlookup در ورودی آخر از شما میپرسه که Range_Lookup (جستجوی بازه ای) نیاز دارید یا خیر؟ که در این مطلب آموزشی ما حالتی که پاسخ آن مثبت (True) است را آموزش داده ایم. در مطلب دیگری هم حالت خیر که ورودی آخر False می شود را توضیح داده ایم. ستون اول متشکل از داده های… ادامه...

0
پاسخ
انتخاب
انتخاب
در پاسخ به  پوریا بغدادی

حرف شما درسته ولی مثال زیرو توجه کنید ، از صد نفر خواسته میشه عدد رندومی بگن از صد نفر دیگه خواسته میشه همین سوالو پاسخ بدن، جداول دو عدد و اسامی نوشته میشه و داده های گروه دوم بصورت صعودی با اسامی نوشته ثبت میشه به عنوان ارگومان دوم، اگه ازین تابع استفاده کنیم خروجی تابع نزدیکترین قرابت دوعدد حدس زده شده توسط دونفر میشه لزوما معنای بازه در این مثال نداره، منطقیه اگه کاربرد مد نظر باشه بازه بندی منطقی ترین کاربرد تابع range lookup

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

نه، فرض کنید عدد ۶ را بین اعداد ۱ و ۳ و ۷ جستجو کنیم. در این حالت پاسخ ۳ خواهد بود در حالیکه ۶ به ۷ نزدیک تر هست. یعنی نتیجه نه تقریب زدن هست و نه نزدیکی. بازه هست. یه تست بکنید متوجه منظورم میشید.

0
پاسخ
انتخاب
انتخاب
در پاسخ به  پوریا بغدادی

تست کردم پاسخ ۷، خروجی فانکشن تقریب به بالا نه تقریب به پائین، مثال نقض حرف من اینه فرض کنی عدد ۶ رابخواهیم از سه عدد ۱ ۵ ۵۲ تقریب نزدیک ترین عدد بزنیم بطور قطع ۵ و حتی ۱ به ۶ نزدیک تره ولی چون خروج تقریب با شرط بزرگتری تنها عدد موجوده ۵۲ انتخاب میشه که شرط بزرگتری در نظر بگیرم متناقض نیست، کل این تابع یه برنامه ده خطیه تقریب با شرط بزرگتر بودن و آرگومان ورودی بازه نیست مفهوم بازه ای میشه براش قائل شد. با تشکر از جوابتون

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

شرطِ اینکه ورودی آخر را True بزنید این هست که داده ها بصورت صعودی مرتب شده باشه (این شرط در راهنمای خود پنجره VLookup هم نوشته شده) بنابراین تست شما اشتباه هست و جواب ۳ خواهد بود. خروجی تابع تقریب نیست. بازه هست. آرگومان ورودی Range_Lookup هست که ترجمه دقیقش جستجوی بازه ای هست.

0
پاسخ
انتخاب
انتخاب
در پاسخ به  پوریا بغدادی

بله ممنون

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  انتخاب

خواهش می کنم.

0
پاسخ
جستجو
جستجو برای:
دسته بندی ها
  • Charts
  • DAX
  • Excel
  • Power BI
  • Power Query
  • هوش تجاری
بسته های آموزشی
  • Excel
  • Power BI
  • SQL
درباره گروه تحلیلگری

معتقدیم آموزش شروع یک تعهد بلند مدت است. این را از مشتریان ما سوال کنید.

اطلاعات تماس
  • تهران، خیابان مطهری، ابتدای خیابان سنایی، کوچه بیست و یکم
  • 543 30 910 - 021
پیوندها
  • آموزش Power BI
  • آموزش SQL
  • آموزش اکسل
دسته بندی محصولات
Excel Power BI SQL
نماد الکترونیک
wpDiscuz

ورود

رمز عبور را فراموش کرده اید؟

هنوز عضو نشده اید؟ عضویت در سایت