استفاده از تابع VLOOKUP برای جایگزینی مقادیر

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

فرمول بندی مسئله

بنابراین، ما دو جدول داریم - جدول سفارش и لیست قیمت:

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

راه حل

در مجموعه تابع اکسل، زیر دسته ارجاعات و آرایه ها (جستجو و مرجع) یک تابع وجود دارد VPR (VLOOKUP).این تابع در ستون سمت چپ جدول مشخص شده (لیست قیمت) که از بالا به پایین حرکت می کند، به دنبال یک مقدار معین می گردد (در مثال ما، این کلمه "Apples" است) و با یافتن آن، محتویات سلول مجاور را نمایش می دهد. (23 روبل) .به صورت شماتیک، عملکرد این تابع را می توان نشان داد بنابراین:

برای سهولت استفاده بیشتر از عملکرد، یک کار را همزمان انجام دهید - محدوده سلول های لیست قیمت را نام خود را وارد کنید. برای انجام این کار، تمام سلول های لیست قیمت به جز "هدر" (G3: H19) را انتخاب کنید، از منو انتخاب کنید. درج - نام - اختصاص (درج - نام - تعریف) یا مطبوعات CTRL + F3 و هر نامی مانند (بدون فاصله) را وارد کنید قیمت... اکنون در آینده می توانید از این نام برای لینک دادن به لیست قیمت استفاده کنید.

اکنون از تابع استفاده می کنیم VPR… سلولی که در آن وارد می شود (D3) را انتخاب کنید و برگه را باز کنید فرمول ها - درج تابع (فرمول ها - درج تابع)… در دسته بندی ارجاعات و آرایه ها (جستجو و مرجع) تابع را پیدا کنید VPR (VLOOKUP) و مطبوعات OK... پنجره ای برای وارد کردن آرگومان های تابع ظاهر می شود:

استفاده از تابع VLOOKUP برای جایگزینی مقادیر

آنها را به نوبه خود پر می کنیم:

  • ارزش مورد نظر (مقدار جستجو) – نام محصولی که تابع باید در سمت چپ ترین ستون لیست قیمت پیدا کند. در مورد ما، کلمه "سیب" از سلول B3 است.
  • جدول (آرایه جدولی) – جدولی که مقادیر مورد نظر uXNUMXbuXNUMXبرگرفته شده است، یعنی لیست قیمت ما. برای مرجع، ما از نام خودمان "قیمت" که قبلاً داده شد استفاده می کنیم. اگر نامی نیاوردید، فقط می توانید جدول را انتخاب کنید، اما فراموش نکنید که دکمه را فشار دهید F4برای پین کردن پیوند با علائم دلار، زیرا در غیر این صورت، هنگام کپی کردن فرمول ما به بقیه سلول های ستون D3:D30 به پایین می لغزد.
  • شماره_ستون (شماره نمایه ستون) – شماره سریال (نه یک حرف!) ستونی در لیست قیمت که از آن مقادیر قیمت را می گیریم. ستون اول لیست قیمت با نام ها به شماره 1 است، بنابراین به قیمت از ستون شماره 2 نیاز داریم.
  • interval_lookup (جستجوی محدوده) - فقط دو مقدار را می توان در این قسمت وارد کرد: FALSE یا TRUE:
      • اگر مقداری وارد شود 0 or دروغ گویی (نادرست)، در واقع این بدان معنی است که فقط جستجو مجاز است مطابقت کامل، یعنی اگر تابع مورد غیر استاندارد مشخص شده در جدول سفارش را در لیست قیمت پیدا نکند (اگر مثلاً "Coconut" وارد شده باشد)، خطای #N/A (بدون داده) ایجاد می کند.
      • اگر مقداری وارد شود 1 or TRUE (درست است، واقعی)، پس این بدان معنی است که شما اجازه جستجو را نه برای دقیق، بلکه مطابقت تقریبی، یعنی در مورد "نارگیل"، تابع سعی می کند محصولی را با نامی که تا حد امکان نزدیک به "Coconut" باشد پیدا کند و قیمت این نام را برگرداند. در بیشتر موارد، چنین جایگزینی تقریبی می تواند با جایگزین کردن ارزش محصول اشتباهی که در واقع وجود داشت، کاربر را فریب دهد! بنابراین برای اکثر مشکلات واقعی تجاری، جستجوی تقریبی بهتر است اجازه داده نشود. استثنا زمانی است که ما به دنبال اعداد و نه متن هستیم - برای مثال، هنگام محاسبه تخفیف های مرحله ای.

همه چيز! فشار دادن باقی مانده است OK و تابع وارد شده را در کل ستون کپی کنید.

# N / A خطاها و سرکوب آنها

عملکرد VPR (VLOOKUP) خطای #N/A را برمی‌گرداند (#N/A) اگر یک:

  • جستجوی دقیق فعال شد (استدلال نمای فاصله = 0) و نام مورد نظر در نیست جدول.
  • جستجوی درشت شامل (نمای فاصله = 1)، ولی جدول، که جستجو در آن انجام می شود به ترتیب صعودی اسامی مرتب نشده است.
  • قالب سلولی که مقدار مورد نیاز نام از آن می آید (مثلاً B3 در مورد ما) و قالب خانه های ستون اول (F3: F19) جدول متفاوت است (به عنوان مثال عددی و متنی). ). این مورد به ویژه در هنگام استفاده از کدهای عددی (شماره حساب، شناسه، تاریخ و غیره) به جای نام متنی معمول است. در این حالت می توانید از توابع استفاده کنید Ч и TEXT برای تبدیل فرمت های داده چیزی شبیه به این خواهد بود:

    =VLOOKUP(TEXT(B3)،قیمت،0)

    شما می توانید بیشتر در مورد این در اینجا بخوانید.

  • تابع نمی تواند مقدار مورد نیاز را پیدا کند زیرا کد حاوی فاصله یا کاراکترهای غیرقابل چاپ نامرئی (شکست خط و غیره) است. در این حالت می توانید از توابع متنی استفاده کنید TRIM (TRIM) и چاپ(تمیز) برای حذف آنها:

    =VLOOKUP(TRIMSPACES(CLEAN(B3))، قیمت، 0)

    =VLOOKUP(TRIM(CLEAN(B3));قیمت;0)

برای سرکوب پیام خطا # N / A (#N/A) در مواردی که تابع نمی تواند مطابقت دقیق پیدا کند، می توانید از تابع استفاده کنید IFERROR (IFERROR)بنابراین، برای مثال، این ساختار هر گونه خطای ایجاد شده توسط VLOOKUP را قطع می کند و آنها را با صفر جایگزین می کند:

= IFERROR (VLOOKUP (B3، قیمت، 2، 0)، 0)

= IFERROR (VLOOKUP (B3؛ قیمت؛ 2؛ 0؛ 0)

PS

اگر لازم است نه یک مقدار، بلکه کل مجموعه را به طور همزمان استخراج کنید (اگر چندین مورد مختلف وجود دارد)، باید با فرمول آرایه شمن کنید. یا از ویژگی جدید XLOOKUP از Office 365 استفاده کنید.

 

  • یک نسخه بهبود یافته از عملکرد VLOOKUP (VLOOKUP 2).
  • محاسبه سریع تخفیف های مرحله (محدوده) با استفاده از تابع VLOOKUP.
  • نحوه ایجاد "VLOOKUP سمت چپ" با استفاده از توابع INDEX و MATCH
  • نحوه استفاده از تابع VLOOKUP برای پر کردن فرم ها با داده های لیست
  • چگونه می توان نه اولین، بلکه همه مقادیر را به یکباره از جدول بیرون کشید
  • عملکردهای VLOOKUP2 و VLOOKUP3 از افزونه PLEX

 

پاسخ دهید