شرکت Jans اخیراً به Office 365 ProPlus ارتقاء داده است. با آن یک #نشت شد! خطا در هنگام استفاده از VLOOKUP، که او از آن بسیار استفاده می کند. او اکنون نمی تواند از VLOOKUP با فیلتر استفاده کند. جان می داند که مرتب کردن و/یا حذف #N/A که از VLOOKUP قبلی آمده است آسان است، اما این خطا باعث می شود زمان و تلاش زیادی برای او به همراه داشته باشد. او فکر می کند که آیا راهی برای خاموش کردن این "ویژگی" وجود دارد؟
در گذشته بسیار نزدیک، مایکروسافت نحوه محاسبه کاربرگ ها را تغییر داد. این یک تغییر بزرگ بود، و ممکن است در مورد آن در جای دیگری خوانده باشید. در اینجا یک مقاله وجود دارد که اطلاعات خوبی در مورد این تغییر ارائه می دهد:
https://exceljet.net/dynamic-array-formulas-in-excel
مقاله نسبتا طولانی است. شما می خواهید زمانی را برای هضم اطلاعات موجود در آن اختصاص دهید. اگر فرمول های زیادی در اکسل ایجاد می کنید، می خواهید این کار را انجام دهید - تغییر در برنامه به این معنی است که در نهایت باید آن را درک کنید.
اساساً، مایکروسافت مفهوم توابع آرایه را کنار گذاشت (اگرچه آنها همچنان کار خواهند کرد)، در عوض تقریباً به همه توابع، از جمله VLOOKUP، اجازه داد تا آرایه ای از مقادیر را برگردانند. اگر آرایه مقادیر برگشتی در فضای موجود جا نگیرد، #SPILL جدید را دریافت می کنید! خطا
صادقانه بگویم، پاسخ غیرفعال کردن #SPILL نیست! خطاها؛ واقعا هیچ راهی برای انجام این کار وجود ندارد. پاسخ این است که بدانید اکسل اکنون در حال محاسبه چه کاری انجام می دهد و سپس فرمول های خود را بر اساس آن اصلاح کنید.
بیایید به یک مثال نگاه کنیم. فرض کنید یک کاربرگ دارید که اقلام و قیمت آنها را در یک مجموعه دو ستونی ساده از داده ها فهرست می کند. سپس، در سمت راست، تعدادی آیتم را وارد کرده و از یک تابع VLOOKUP برای برگرداندن قیمت های مربوط به هر یک از آن آیتم ها استفاده می کنید. هنگامی که این ورک بوک را در نسخه قدیمی اکسل (2019 یا قبل از آن) باز می کنید، نتایج بسیار خوبی دریافت می کنید. (شکل 1 را ببینید.)
شکل 1. یک فرمول VLOOKUP ساده در اکسل 2010.
این اسکرین شات با استفاده از سیستم اکسل 2010 گرفته شده است، اما اگر در اکسل 2016 یا حتی اکسل 2019 به آن نگاه کنید، به همین شکل کار می کند. در این مثال توجه داشته باشید که فرمول VLOOKUP در سلول F2 (در نوار فرمول به دلیل سلول F2 نشان داده شده است. انتخاب شده است) در محدوده F2:F8 کپی می شود. شما نتایج دلخواه را دریافت می کنید زیرا تابع VLOOKUP یک مقدار را از جدول برمی گرداند.
حال، اجازه دهید ببینیم چه اتفاقی می افتد اگر همان ورک بوک را با استفاده از همان فرمول ها، در نسخه اکسل ارائه شده با Office 365 ایجاد کنید. در این صورت خطاهایی را مشاهده خواهید کرد. (شکل 2 را ببینید.)
شکل 2. همان فرمول ساده VLOOKUP در آخرین نسخه اکسل.
به #نشتی توجه کنید! خطاها این خطا به این دلیل رخ می دهد که فرمول VLOOKUP اکنون می تواند بیش از یک مقدار را برگرداند. در واقع، زمانی که از محدوده سلولی در اولین پارامتر برای VLOOKUP استفاده می کنید، اکنون برای هر سلول در آن محدوده یک مقدار برمی گرداند. بنابراین، استفاده از محدوده E2:E8 برای پارامتر اول به این معنی است که VLOOKUP 7 مقدار را برمی گرداند. به عبارت دیگر، به طور خودکار یک آرایه از مقادیر را برمی گرداند. اگر این مقادیر نتوانند همه نمایش داده شوند، #SPILL را دریافت می کنید! خطا به همین دلیل است که #نشت را می بینید! خطا در سلول های F2:F7. مواردی در زیر آنها وجود دارد که از نمایش تمام مقادیر بازگشتی در آن فرمول ها جلوگیری می کند. شما خطا را در سلول F8 مشاهده نمی کنید زیرا چیزی زیر آن سلول وجود ندارد که نمایشگر را متوقف کند.
بنابراین، چگونه این را برطرف می کنید؟ در واقع سه راه برای رفع آن وجود دارد. در این مثال خاص، ساده ترین راه این است که به سادگی همه چیز را در سلول های F3:F8 حذف کنید. این اجازه می دهد تا فرمول موجود در سلول F2 به درستی به بقیه سلول های زیر آن بریزد.
روش دوم تغییر فرمول در سلول F2 است، بنابراین به نظر می رسد این است:
=VLOOKUP(@E$2:E$8,A$2:B$19,2)
به استفاده از نماد @ درست قبل از اولین پارامتر توجه کنید. این به اکسل می گوید که می خواهید فرمول VLOOKUP فقط یک مقدار را برگرداند. راه دیگر برای تنظیم فرمول این است که در سلول F2 به شکل زیر باشد:
=VLOOKUP(E2,A$2:B$19,2)
اکنون می توانید هر یک از فرمول ها را از سلول F2 در محدوده کامل F2:F8 کپی کنید و مشکلی نخواهید داشت. چرا؟ زیرا (دوباره) VLOOKUP در این نمونه ها فقط یک مقدار را برمی گرداند، نه یک آرایه از مقادیر.
نتیجه این است که بهترین راه برای تغییر فرمول های خود این است که (1) مطمئن شوید که هیچ چیزی مانع نمایش آرایه کامل مقادیری که از VLOOKUP برای برگرداندن آن درخواست می کنید وجود ندارد یا (2) پارامتر اول را تغییر دهید تا فقط به آن ارجاع دهد. یک سلول واحد
