سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

فرمول Vlookup در اکسل
۴.۶/۵ - (۱۰ امتیاز)

کاربردی جالب از تابع Vlookup برای جستجوی حدودی

آیا تاکنون در اکسل به این مسئله برخورد کرده اید که بازه های عددی زیادی داشته باشید و بخواهید اعداد مختلفی را در آن ها جستجو کنید؟ (شکل ۱) مثلا میخواهیم بدانیم که عدد ۳۲۰ در کدام گروه قرار می گیرد؟ در واقع قصد داشته باشید جستجوی بازه ای یا جستجو حدودی انجام بدید. حل این مسئله روش های مختلفی دارد که در این آموزش قصد داریم کاربردی جالب از فرمول Vlookup در اکسل را برای حل این موضوع شرح بدیم.

تابع Vlookup - جستجو بازه ای

شکل ۱- دیتابیس بازه ای

شاید راه حلی که در نگاه اول به ذهن برسد استفاده از if متداخل یا nested if باشد. به این صورت که برای هر بازه، یک if در نظر گرفته شود. اما همانطور که مشخص است، علاوه بر اینکه کار سخت و زمانبری به نظر می رسد، ویرایش آن بسیار دشوار خواهد بود. مثلا اگر ابتدا و انتهای بازه، تغییر کند، کل فرمول را باید اصلاح کرد.

در اینجا راه حلی را با استفاده از فرمول Vlookup در اکسل ارائه خواهیم کرد که بسیار سریع، راحت و انعطاف پذیر در صورت تغییر خواهد بود.

همانطور که قبلا در آموزش تابع Vlookup توضیح داده ایم، آرگومان چهارم این تابع می تواند دو مقدار ۰ یا ۱ را بگیرد. در این آموزش توضیح داده شد که این آرگومان اصولا ۰ قرار داده می شود که بتواند جستجوی دقیق یا Exact Match را انجام دهد. اما چه مواقعی این آرگومان رو ۱ یعنی Approximate Match میگذاریم؟ یکی از کاربردهای این حالت، همین جستجوی بازه ای (تخمینی) هست. با یک مثال بیشتر توضیح میدیم:

مثلا می خواهیم ببینیم عدد ۴۹۹ در کدام گروه قرار میگیرد؟ با توجه به اینکه عدد ۴۹۹ در هیچ یک از این سلول ها وجود ندارد، پس نمیتوانیم به صورت دقیق جستجو انجام بدیم و میخواهیم کاری کنیم که جستجوی بازه ای صورت گیرد.

برای این کار اول باید جدولی رو آماده کنیم که به عنوان Table Array مورد استفاده قرار گیرد. پس بازه های مورد نظر را تایپ کرده و جدول را ایجاد میکنیم. در شکل ۲ ستون A ابتدای بازه و ستون B انتهای بازه و ستون C گروه بندی وارد شده. دقت داشته باشید که این داده ها باید مرتب و از کوچک به بزرگ در جدول چیده شوند که جستجوی تخمینی بتواند صورت گیرد.

مطابق شکل ۲، در سلول F6 تابع Vlookup را می نویسیم و آرگومان آخر را ۱ قرار می دهیم. مقدار Lookup Value یا چیزی که مورد جستجو قرار گرفته، همان عدد ۴۹۹ هست. جدول جستجو هم محدوده A1:C8 هست. میخواهیم بصورت تخمینی جستجو کند و عدد ۴۹۹ را در ستون اول پیدا کند و داده مرتبط با آن را در ستون سوم نمایش دهد. پس آرگومان Col_index عدد ۳ خواهد بود. آرگومان آخر هم طبق توضیحاتی که ارائه شد، عدد ۱ یا مقدار True قرار داده می شود.

توجه داشته باشید که می توانیم ستون B را حذف کنیم (برای درک بهتر مفهوم بازه، جدول را به این صورت نمایش دادیم). در واقع تابع Vlookup جستجو را در ستون اول انجام می دهد و ملاک فقط ستون اول هست.

به این ترتیب این تابع با جستجوی تخمینی، اعداد بین بازه ها را تشخیص خواهد داد و داده مرتبط با ستون سوم را به عنوان خروجی نمایش می دهد. عدد ۴۹۹ در بازه ۳۵۰-۵۰۰ قرار میگیرد و گروه بندی D به عنوان خروجی نمایش داده خواهد شد.

تابع Vlookup - مثالی از جستجوی بازه ای

شکل ۲- فراخوانی عدد مورد نظر در بازه های موجود

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

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

نکته خیلی مهم: باز و بسته بودن بازه ها به صورت (A B] خواهد بود. برای مثال، عدد ۵۰۰ در گروه بندی E قرار خواهد گرفت. پس انتخاب ابتدا و انتهای بازه ها (با توجه به مفهوم و خروجی مورد انتظار) از اهمیت بسیار بالایی برخوردار است.

کلیدواژه : پیشرفتهتابع Vlookup
آواتار
131

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • Keivan ۹ آبان ۱۳۹۹ / ۱۰:۵۷ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۷ آذر ۱۳۹۹ / ۱۲:۱۵ ب٫ظ

      درود بر شما
      از countifs استفاده کنید

  • نیما ۹ خرداد ۱۳۹۹ / ۱۲:۴۵ ب٫ظ

    اصلا معلوم نیست چی گفتیید

    • آواتار
      حسنا خاکزاد ۱۱ خرداد ۱۳۹۹ / ۱۰:۳۵ ق٫ظ

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

  • حانیه نظامی فر ۲۳ فروردین ۱۳۹۹ / ۹:۰۴ ب٫ظ

    سلام و خداقوت
    من ستون اکسلی دارم که اطلاعات آن به شرح زیر می باشد
    نام کد
    علی ۱۱
    علی ۱۲۳
    علی ۱۱۴
    حانیه ۱۵۹
    حانیه ۲۰۰
    حانیه ۱۰
    حانیه ۱۰۲
    حانیه ۵
    و می خواهم در یک شیت دیگر فقط اطلاعات حانیه و کد مربوط به هر یک نمایش داده شود.ممنون راهنماییم کنید

    • آواتار
      حسنا خاکزاد ۲۴ فروردین ۱۳۹۹ / ۵:۵۷ ب٫ظ

      درود
      هم با پیوت تیبل میشه
      هم فمرول نویسی. میتونید اسم ها رو از اعداد تفکیک کنید و بعد با استفاده از روش جستجوی موارد تکررای، فرمول نویسی انجام بدید که در مقاله زیر توضیح داده شده است
      https://excelpedia.net/search-duplicates/

  • ساسان ۱۳ بهمن ۱۳۹۸ / ۱:۴۷ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۴ بهمن ۱۳۹۸ / ۱۱:۲۲ ق٫ظ

      درود
      با تابع vlookup میتونید اعداد رو جستجو کنید و ستون دومش رو فراخوانی کنید

  • آلما ۷ دی ۱۳۹۸ / ۹:۵۱ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۸ دی ۱۳۹۸ / ۱۲:۳۹ ب٫ظ

      درود بر شما
      چی از جمع و میانگین بیشتر باشه؟
      بصورت کلی باید برید سراغ تابع ifs (اگر ۲۰۱۹ دارید)
      یا nested if

  • nima ۵ خرداد ۱۳۹۸ / ۳:۰۵ ب٫ظ

    با سلام
    یه مشکلی دارم با اکسل ممنون میشم راهنمایی بفرمایید
    برای پرسش های پایان نامه در اکس میخواهم فرمول بدم که بجای گزینه بسیار مهم بزنه عدد ۵ برای مهم بزنه ۴ برای متوسط۳ برای کم ۲ و برای بسیار کم ۱

    • سامان چراغی ۵ خرداد ۱۳۹۸ / ۹:۰۳ ب٫ظ

      سلام
      از ابزار Find & Replace میتویند استفاده کنید و برای هر کدام Replace All رو بزنید.

      • nima ۶ خرداد ۱۳۹۸ / ۳:۳۶ ق٫ظ

        سپاس فراوان از هر دو عزیز و ممنون بابت پاسخگویتان 🌻🍀🌼🌹🌹

    • آواتار
      حسنا خاکزاد ۵ خرداد ۱۳۹۸ / ۹:۴۳ ب٫ظ

      درود بر شما
      با هعمین vlookup میتونید
      با match میشه
      با index هم

      اموزش همه اینها هم داخل سایت هست

      همین اموزش و بخونید
      جدولتون و تشکیل بدید که هر کلمه معادل چه عددی هست. بعد vlookup کنید

  • صمد تبریزی جم ۱۱ اردیبهشت ۱۳۹۸ / ۳:۰۳ ب٫ظ

    باسلام و خسته نباشید من میخواهم فرمولی بنویسم که عدد مورد نظر مرا از داخل چند بازه پیدا کرده بعد با عدد متناظر با آن بازه جمع کند . با تشکر.

    • آواتار
      حسنا خاکزاد ۱۱ اردیبهشت ۱۳۹۸ / ۶:۲۵ ب٫ظ

      درود بر شما
      خروجی همین آموزشی که ارائه شده، رو باید در عدد مورد نظر ضرب کنید.
      جستجوی بازه ای رو خوب مطالعه کنید.

  • MEYSAM ۱۵ اسفند ۱۳۹۷ / ۳:۰۸ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۵ اسفند ۱۳۹۷ / ۹:۳۶ ق٫ظ

      درود بر شما
      فرمول نویسی آرایه ای لازمه برای این مورد

      در دوره نینجا، فرمول نویسی آرایه ای کامل تشریح شده.
      داخل گروه پرسش و پاسخ هم نمونه فایل هست. به نام array-index میتونید دانلود کنید. خودتون تحلیل کنید و یاد بگیرید

      • MEYSAM ۱۵ اسفند ۱۳۹۷ / ۷:۳۸ ب٫ظ

        سپاس از راهنماییتون….
        گروه پرسش و پاسخ رو از کجا پیدا کنم؟ اگه ممکنه این فایل array-index که میفرمایین رو برام بفرستین…. کمک بزرگی میکنید….
        [email protected]

        • آواتار
          حسنا خاکزاد ۱۸ اسفند ۱۳۹۷ / ۲:۲۴ ب٫ظ

          خواهش میکنم
          لینک گروه در فوتر سایت هست

  • ریحانه ۱۳ اسفند ۱۳۹۷ / ۸:۳۹ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۳ اسفند ۱۳۹۷ / ۱۲:۰۰ ب٫ظ

      درود بر شما
      فرقی نمیکنه
      مثلا من این بازه ها رو میتونم استفاده کنم.
      ۵ ۱
      ۱۵ ۱۰
      ۲۵ ۲۰

      که اصلا پشت سر هم نیستن

  • حمید ۱۲ آبان ۱۳۹۷ / ۴:۴۷ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۳ آبان ۱۳۹۷ / ۹:۴۶ ق٫ظ

      درود بر شما
      کلمات سطر و ستون رو جابجا استفاده کردید و این اثر میذاره روی صورت مسئله.
      اگر اطلاعات بر سااس کد پرسنلی هست و در هر ردیف یک کد داردی و جلوش اطلاعات مربوط به هر نفر، این براحتی با vlookup شدنی هست و هر بار col index جداگانه میذارید.
      اما اگر هر کد در ردیف های جداگانه هست، و تککرار شده، باید فرمول نویسی آرایه ای انجام بدید و با index, small و … ترکیب کنید

  • مسلم ۷ مهر ۱۳۹۷ / ۸:۳۱ ق٫ظ

    سلام
    من یک فایل اکسل دارم که ۳۰۰۰۰۰ ردیف است و در یکی از ستون ها متنی شامل شماره حساب و شماره چک و … می باشد.
    چطور میتونم ۵ شماره چک را در کمترین زمان در این ستون سرچ کنم؟ یعنی نمی خواهم تک تک شماره چک بزنم و جستجو کنم. بیشتر جستجو دسته جمعی نیازم هست.
    ممنون

    • آواتار
      حسنا خاکزاد ۷ مهر ۱۳۹۷ / ۹:۲۱ ق٫ظ

      درود بر شما
      بهتره از ابزار find استفاده کنید
      سریعترین راهه

  • امید ۲۸ خرداد ۱۳۹۷ / ۴:۱۱ ق٫ظ

    سلام مطالب خوبی ارایه میدین تشکر میکنم
    من میخوام یک جدول داشته باشم که اسم محصول که زدم قیمت و دیگر مشخصاتش(که درهمون ردیف قرار داره) استخراج کنم
    مشکلم اینجاست که اسم محصول طولانیه ولی من میخوام با زدن قسمتی از عبارت مثلا پرینتر M102a بتونم قیمت رو از شیت قبلی بگیرم
    با فرمول Vlookup امتحان کردم درصورتی جواب میده که عین عبارت تایپ بشه.
    ممنون میشم راهنمایی بفرمایید..

    • سامان چراغی ۲۸ خرداد ۱۳۹۷ / ۸:۴۴ ق٫ظ

      سلام، تشکر
      کافیه علامت * به انتهای قسمتی از نام محصول که تایپ میکنید اضافه بشه و در آرگومان اول Vlookup قرار داده بشه:

      • امید ۱ تیر ۱۳۹۷ / ۰:۲۵ ق٫ظ

        hp m102a
        hp n130fn
        hp m130fw
        hp n400d
        اول تشکر میکنم بابت راهنمایی جناب مهندس عزیز و بزرگوار. دوم اینکه من خیلی متوجه نشدم کاربرد ستاره تو فرمان Vlookup به چه صورته کلی هم سرچ کردم اما متاسفانه پیدا نکردم و دوباره مزاحم شما شدم.
        اقلام بالا بعنوان مثال نوشتم که چطور میشه خلاصه ای از نام این محصولات رو با vlookup جستجو کرد.
        =VLOOKUP(A2,sheet10!$A$2:$E$15,3,FALSE) چیزی که خودم نوشتم اینه

      • امید ۱ تیر ۱۳۹۷ / ۴:۱۹ ب٫ظ

        سلام مجدد – من بلاخره با کمک شما تونستم کاربرد ستاره رو تو فرمان vlookup بفهمم و استفاده کنم
        باز هم سپاسگزارم

      • زهرا ۲۴ مرداد ۱۳۹۷ / ۱۰:۳۳ ق٫ظ

        سلام وقتتون بخیر ،من هرچی میخوام از این فرمول استفاده کنم نمیشه . ( ارورN/A# میده )
        ببینید نمونه جدولم اینه:
        ۲۲۰۵۲۳
        ۲۲۰۵۲۴
        ۲۲۰۵۲۵
        وتوی ستون دوم کد مورد نظر مثلا از aتا c
        مثلا میخوام ۵۲۳ رو جست جو کنم این فرمولو میزنم:
        VLOOKUP(D1&”*”;A1:B3;2;1)
        “۵۲۳توی سلولd1 تایپ شده”

        • آواتار
          حسنا خاکزاد ۲۷ مرداد ۱۳۹۷ / ۹:۰۶ ق٫ظ

          درود بر شما
          خواسته شما با آموزش بالا متفاوت هست
          اینکه نوشتید جستجوی بازه ای نیست.

          برای این کار باید داده های جدولتون رو متنی تبدیل کنید با استفاده text to column یا هر روش دیگه.بعد این فرمول رو بنویسید و آرگومان آخر رو هم صفر بذارید.

          یا از این فرمول که آرایه ای هست استفاده کنید. با ctrl+shift+enter ثبت کنید:

  • حامد ۲ اردیبهشت ۱۳۹۷ / ۱۱:۵۴ ب٫ظ

    با سلام و عرض خسته نباشید
    از مطالب علمی و آموزشی که در سایت قرار دادین تشکر میکنم واقعا عالی و کاربردی هستند.
    یه سوال در مورد اکسل داشتم ممنون میشم اگر راهنمایی کنید
    بنده یه فایل دارم یک حدود ۲۲ هزار سطر در یک ستون است که شامل عدد از یک تا ۱۰۰ هست میخواستم اعداد رو طبقه بندی کنم مثلا در ۵ گروه دسته بندی بشه و فروانی هر دسته رو نشون بده. مثلا دسته ۰ الی ۲۰ که نشون بده چند رکورد در این دسته قرار دارد.
    اگر فرمول یا روشی هست که بتوان اینکارو کرد ممنون میشم راهنمایی کنید.
    با تشکر

    • آواتار
      حسنا خاکزاد ۳ اردیبهشت ۱۳۹۷ / ۹:۳۵ ق٫ظ

      درود بر شما
      خوشحالیم که اینو میشنویم

      ۲ تا روش داره
      یکی از استفاده از تابع Frequency
      دیگری استفاده از Countifs

  • پیمان و علی ۶ شهریور ۱۳۹۶ / ۲:۲۸ ب٫ظ

    دمت گرم، عالی بود

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان