سبد خرید
0

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

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

تابع XLookup جایگزین تابع Vlookup

تابع xlookup
۳/۵ - (۲ امتیاز)

تابع XLOOKUP در اکسل

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

همونطور که میدونیم تابع Vlookup در حالت عادی بصورت رو به جلو عمل میکنه و اصطلاحا بهش میگیم Forward Vlookup. در واقع این موضوع به این معنی هست که داده ای رو که جستجو میکنیم باید در اولین ستون از جدول جستجو قرار بدیم و با این کار دیگه امکان فراخوانی داده های موجود در قبل از ستون مورد نظر وجود نداره. این موضوع رو با یک مثال شرح میدهیم.

تابع Vlookup و عملکرد Forward

شکل ۱- تابع Vlookup و عملکرد Forward

در شکل ۱ مشاهده میکنید که اگر بخوایم شعبه رو جستجو کنیم، دیگه امکان فراخوانی منطقه مرتبط با اون شعبه وجود نداره؛ چرا که باید جدول جستجو طوری انتخاب بشه که شعبه مورد نظر، ستون اول جدول مورد نظر باشه. برای اینکه بتونیم منطقه رو هم جستجو کنیم، باید بریم سراغ روش های دیگه ای از قبیل جابجایی ستون ها، تکنیک Backward vlookup ، ترکیب Index , Match و …

در مورد روش های بالا قبلا مقالاتی ارائه شده، در این مقاله قصد داریم به تابع جدید Xlookup که در ورژن ۲۰۲۱ به اکسل اضافه شده بپردازیم. این تابع کاملا شبیه Vlookup عمل میکنه در حالیکه کامل تر شده و موضوع مطرح شده (Backward Vlookup) رو برطرف کرده و یک سری آرگومان های تکمیلی هم بهش اضافه شده.

این تابع جزو یکی از توابع Dynamic Array در ورژن ۲۰۲۱ بشمار میره که در ادامه آرگومان های این تابع رو شرح میدیم:

Lookup_Value: داده ای که داریم جستجو میکنیم.

Lookup_Array: محدوده ای که در آن به دنبال داده مورد نظر میگردیم.

Return_Array: محدوده جوابی که میخوایم فراخوانی بشه.

نکته:
در واقع آرگومان دوم و سوم در تابع Vlookup تبدیل شده به این دو آرگومان (Lookup_array و Return_array) [If_not_found]: آرگومان اختیاری برای اینکه اگر داده مورد نظر پیدا نشد، چی نمایش داده بشه. (جایگزین ترکیب Iferror)

[Match_Mode]: آرگومان آخر در تابع Vlookup با این آرگومان جایگزین شده که به مراتب کامل تر و کاربردی تر هست. اینکه سرچ دقیق یا تخمینی انجام بده (در مورد سرچ تخمینی مقاله جستجوی بازه ای رو مطالعه کنید) و حالت چهارم زمان جستجو با Wildcard استفاده میشه.

[Search_Mode]: در این آرگومان میتونیم تعیین کنیم که جستجو از اول به آخر انجام بشه یا برعکس، همینطور میتونیم مشخص کنیم که داده ها بصورت مرتب شده (صعودی و نزولی) مورد جستجو قرار بگیرن.

نکته:
این تابع همچنان موارد تکراری رو جستجو نمیکنه. برای جستجوی موارد تکراری در ورژن ۲۰۲۱ می تونید از تابع Filter استفاده کنید و در ورژن های قبلی از فرمول نویسی آرایه ای استافده کنید. (برای هر دو مقالات مرتبط ارائه شده است)

حالا در ادامه این تابع رو با مثال شرح خواهیم داد:

همون داده های شکل ۱ رو در نظر بگیرید، میخواهیم شعبه رو جستجو کنیم و منطقه رو به عنوان خروجی فراخوانی کنیم. مطابق شکل ۲، داده مورد جستجو (شعبه ۷) رو از سلول B13 میگیریم و آرگومان دوم رو ستون شعبه (یعنی جایی که باید دنبال شعبه بگردیم) تعیین میکنیم و آرگومان سوم رو ستون منطقه (یعنی محدوده ای که میخواهیم به عنوان خروجی نمایش داده بشه) تعیین میکنیم.

=XLOOKUP(B13,B2:B11,A2:A11)

حالت ساده تابع Xlookup

شکل ۲- حالت ساده تابع Xlookup

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

=XLOOKUP(B13,B2:B11,C2:E11)

فراخوانی یک محدوده جواب

شکل ۳- فراخوانی یک محدوده جواب

مدیریت خطا در Xlookup

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

=XLOOKUP(B13,B2:B11,C2:E11,”یافت نشد”)

جستجو از آخر جدول

در تابع Xlookup این امکان رو داریم که از طریق آرگومان آخر بتونیم جهت جستجو رو تعیین کنیم. در حالت عادی وقتی آرگومان آخر تابع رو تعیین نکنیم، گزینه اول یعنی جستجو از ابتدای جدول انجام میشه  اگر این آرگومان رو ۱- بذاریم، جستجو از انتهای محدوده مورد نظر انجام میشه (یعنی از آخر به اول). مثلا فرض کنید دیتاهای موجود در جدول به ترتیب ثبت میشن و آخرین ردیف داده ها نشان دهنده آخرین داده موجود است. مثلا اقلام فروش در یک فروشگاه به ترتیب ثبت میشن و حالا میخوایم ببینیم آخرین فروش مربوط به محصول X توسط چه کسی بوده است؟

برای حل این مسئله باید بتونیم آخرین محصولی که در جدول داده ها ثبت شده پیدا کنیم. پس برای این کار آرگومان آخر رو ۱- میذاریم که بتونیم از انتهای ستون A شروع به جستجو کنیم.

=XLOOKUP(D1,A2:A15,B2:B15,,,-1)

جستجو از آخر به اول

شکل ۴- جستجو از آخر به اول

وقتی اینکار رو انجام بدیم، جستجو از انتها انجام میشه و به اولین محصول ۳ که برسه، فروشنده مربوطه رو به عنوان خروجی بهمون میده. یعنی “دایی”

با توجه به توضیحاتی که راجع به این تابع ارائه شد، مسئله Backward Vlookup در این تابع حل شده و جستجو هم رو به جلو و هم رو به عقب قابل انجام هست. اما همچنان این تابع موارد تکراری رو جستجو نمیکنه و برای جستجوی موارد تکراری باید بریم سراغ تابع Filter در نسخه ۲۰۲۱ که قبلا در مقاله مربوط به تابع Filter کاملا به آموزش این تابع پرداختیم.

دانلود فایل تابع Xlookup در اکسل

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

مشاهد ویدئو تابع Xlookup در اکسل

در حال بارگذاری پخش کننده...
آواتار
144

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

دیدگاه کاربران
  • مهدی ۱۳ بهمن ۱۴۰۲ / ۱۰:۲۶ ب٫ظ

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

  • mohammadkazem62 ۱۱ آذر ۱۴۰۲ / ۳:۲۳ ب٫ظ

    درود بر شما…
    استاد برای جستجوی در یک بازه شامل سطر و ستون چه روشی هست؟
    مثلا حالت زیر رو بصورت جدول در نظر بگیرید
    آبی سبز زرد ۱۰
    قرمز نارنجی بنفش ۱۵
    صورتی سیاه سفید ۲۰
    حالا در یک سلول یک رنگ وارد بشه و کد مربوطه نمایش داده بشه… یعنی طوری باشه که جستجو شامل چند سطر و ستون باشه
    ممنون از راهنمایی تون…

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

      درود بر شما
      با فرمول نویسی ارایه ای ج میگیرید
      با if شرط رو بنویسید و ستون و ردیفشو استخراج کنید و بذارید توی تابع address

      • mohammadkazem62 ۱۲ آذر ۱۴۰۲ / ۱۰:۵۲ ق٫ظ

        درود استاد…
        ممنون از شما… ممکنه براتون هر دو روش رو توضیح بدین. بازم ممنون

      • mohammadkazem62 ۱۲ آذر ۱۴۰۲ / ۱:۵۷ ب٫ظ

        =IF(OR(L6=J2,L6=K2,L6=L2),M2,IF(OR(L6=J3,L6=K3,L6=L3),M3,IF(OR(L6=J4,L6=K4,L6=L4),M4,””)))
        استاد این فرمول IF
        تا اینجاشو رفتم بقیه رو لطفن کمک کنید ( :

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

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

  • محمد امین ۲۹ آذر ۱۴۰۱ / ۷:۵۹ ب٫ظ

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

ارسال دیدگاه

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

توسط
تومان