فهرست
این درس توضیح می دهد که چگونه می توان به سرعت با موقعیتی برخورد کرد که در آن یک تابع VPR (VLOOKUP) نمی خواهد در اکسل 2013، 2010، 2007 و 2003 و نحوه شناسایی و رفع خطاهای رایج و غلبه بر محدودیت ها کار کند. VPR.
در چندین مقاله قبلی، جنبه های مختلف عملکرد را بررسی کرده ایم VPR در اکسل اگر آنها را به دقت خوانده اید، اکنون باید در این زمینه متخصص باشید. با این حال، بی دلیل نیست که بسیاری از کارشناسان اکسل معتقدند VPR یکی از ویژگی های پیچیده تر مجموعه ای از محدودیت ها و ویژگی ها دارد که منشا بسیاری از مشکلات و خطاها می شود.
در این مقاله توضیحات ساده ای در مورد خطاها خواهید یافت #AT (#N/A) قطب مثبت باتری (#NAME؟) و #ارزش! (#VALUE!) که هنگام کار با تابع ظاهر می شوند VPRو همچنین تکنیک ها و روش های مقابله با آنها. ما با رایج ترین موارد و واضح ترین دلایل شروع می کنیم. VPR کار نمی کند، بنابراین بهتر است مثال ها را به ترتیبی که در مقاله آورده شده است، مطالعه کنید.
رفع خطای #N/A در تابع VLOOKUP در اکسل
در فرمول های با VPR پیغام خطا #AT (#N/A) یعنی در دسترس نیست (بدون داده) – زمانی ظاهر می شود که اکسل نتواند مقدار مورد نظر شما را پیدا کند. این ممکن است به دلایل مختلفی اتفاق بیفتد.
1. مقدار مورد نظر اشتباه نوشته شده است
ایده خوبی است که ابتدا این مورد را بررسی کنید! اشتباهات تایپی اغلب زمانی رخ می دهد که شما با حجم بسیار زیادی از داده ها، متشکل از هزاران خط کار می کنید، یا زمانی که مقداری که به دنبال آن هستید در یک فرمول نوشته می شود.
2. خطای #N/A هنگام جستجوی یک مطابقت تقریبی با VLOOKUP
اگر از فرمولی با شرایط جستجوی تقریبی مطابقت، یعنی آرگومان استفاده می کنید range_lookup (range_lookup) درست است یا مشخص نشده است، فرمول شما ممکن است یک خطا را گزارش کند # N / A در دو مورد:
- مقداری که باید جستجو شود کمتر از کوچکترین مقدار در آرایه ای است که جستجو می شود.
- ستون جستجو به ترتیب صعودی مرتب نشده است.
3. خطای #N/A هنگام جستجوی یک تطابق دقیق با VLOOKUP
اگر به دنبال تطبیق دقیق، یعنی استدلال هستید range_lookup (range_lookup) FALSE است و مقدار دقیق آن پیدا نشد، فرمول همچنین خطا را گزارش می کند # N / A. درباره نحوه جستجوی منطبقات دقیق و تقریبی با یک تابع بیشتر بیاموزید VPR.
4. ستون جستجو در سمت چپ نیست
همانطور که احتمالا می دانید یکی از مهم ترین محدودیت هاست VPR این است که نمی تواند به سمت چپ باشد، بنابراین ستون جستجو در جدول شما باید در سمت چپ باشد. در عمل، ما اغلب این موضوع را فراموش می کنیم که منجر به فرمول ناکارآمد و خطا می شود. # N / A.
تصمیم: اگر امکان تغییر ساختار داده به گونه ای وجود ندارد که ستون جستجو در سمت چپ باشد، می توانید از ترکیبی از توابع استفاده کنید شاخص (INDEX) و بیشتر در معرض (MATCH) به عنوان یک جایگزین انعطاف پذیرتر برای VPR.
5. اعداد به صورت متن قالب بندی می شوند
یکی دیگر از منابع خطا # N / A در فرمول های با VPR اعداد در قالب متن در جدول اصلی یا جدول جستجو هستند.
این معمولاً زمانی اتفاق می‌افتد که اطلاعات را از پایگاه‌های داده خارجی وارد می‌کنید، یا زمانی که یک آپوستروف را قبل از یک عدد تایپ می‌کنید تا صفر اول را حفظ کنید.
بارزترین علائم یک عدد در قالب متن در شکل زیر نشان داده شده است:
علاوه بر این، اعداد را می توان در قالب ذخیره کرد سوالات عمومی (عمومی). در این مورد، تنها یک ویژگی قابل توجه وجود دارد - اعداد در لبه چپ سلول تراز می شوند، در حالی که به طور پیش فرض در لبه سمت راست قرار می گیرند.
تصمیم: اگر یک مقدار است، فقط روی نماد خطا کلیک کنید و انتخاب کنید تبدیل به شماره (تبدیل به شماره) از منوی زمینه.
اگر این وضعیت با تعداد زیادی اعداد است، آنها را انتخاب کنید و روی ناحیه انتخاب شده کلیک راست کنید. در منوی زمینه که ظاهر می شود، را انتخاب کنید سلولها را قالب بندی کنید (Format Cells) > tab شماره (شماره) > قالب شماره (عددی) و فشار دهید OK.
6. در ابتدا یا در انتها یک فاصله وجود دارد
این کمترین دلیل برای خطا است. # N / A در عمل VPR، از آنجایی که دیدن این فضاهای اضافی از نظر بصری دشوار است، به خصوص هنگام کار با جداول بزرگ، زمانی که بیشتر داده ها خارج از صفحه هستند.
راه حل 1: فضاهای اضافی در جدول اصلی (جایی که تابع VLOOKUP است)
اگر فضاهای اضافی در جدول اصلی ظاهر می شوند، می توانید با قرار دادن آرگومان مطمئن شوید که فرمول ها به درستی کار می کنند lookup_value (lookup_value) در یک تابع TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
راه حل 2: فضاهای اضافی در جدول جستجو (در ستون جستجو)
اگر فضاهای اضافی در ستون جستجو وجود دارد - راه های ساده # N / A در فرمول با VPR نمی توان اجتناب کرد بجای VPR می توانید از یک فرمول آرایه با ترکیبی از توابع استفاده کنید شاخص (فهرست مطالب)، بیشتر در معرض (مسابقه) و TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
از آنجایی که این یک فرمول آرایه است، فراموش نکنید که فشار دهید Ctrl + Shift + Enter به جای معمول واردتا فرمول را به درستی وارد کنید.
خطای #VALUE! در فرمول ها با VLOOKUP
در بیشتر موارد، مایکروسافت اکسل یک خطا را گزارش می کند #ارزش! (#VALUE!) زمانی که مقدار استفاده شده در فرمول با نوع داده مطابقت ندارد. مربوط به VPR، پس معمولاً دو دلیل برای خطا وجود دارد #ارزش!.
1. مقدار مورد نظر شما بیشتر از 255 کاراکتر است
مراقب باشید: عملکرد VPR نمی تواند مقادیری را که بیش از 255 کاراکتر دارند جستجو کند. اگر مقدار مورد نظر شما از این حد بیشتر شود، یک پیغام خطا دریافت خواهید کرد. #ارزش!.
تصمیم: از یک سری ویژگی استفاده کنید INDEX + MATCH (INDEX + MATCH). در زیر فرمولی وجود دارد که برای این کار به خوبی انجام می شود:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. مسیر کامل کتاب کار جستجو مشخص نشده است
اگر داده‌ها را از کتاب کار دیگری بازیابی می‌کنید، باید مسیر کامل آن فایل را مشخص کنید. به طور خاص، شما باید نام کتاب کار (از جمله پسوند) را در کروشه مربع [ ]، به دنبال آن نام برگه و به دنبال آن علامت تعجب قرار دهید. در صورتی که نام کتاب یا برگه دارای فاصله باشد، تمام این ساختار باید در آپستروف ها محصور شود.
در اینجا ساختار کامل تابع آمده است VPR برای جستجو در کتاب دیگری:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
فرمول واقعی ممکن است شبیه به این باشد:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
این فرمول مقدار سلول را جستجو می کند A2 در یک ستون B روی ورق Sheet1 در کتاب کار قیمت های جدید و مقدار مربوطه را از ستون استخراج کنید D.
اگر هر بخشی از مسیر جدول حذف شود، تابع شماست VPR کار نمی کند و یک خطا را گزارش می دهد #ارزش! (حتی اگر کتاب کار با جدول جستجو در حال حاضر باز باشد).
برای اطلاعات بیشتر در مورد عملکرد VPRبا ارجاع به فایل اکسل دیگری، به درس: جستجوی کتاب کار دیگری با استفاده از VLOOKUP مراجعه کنید.
3. Argument Column_num کمتر از 1 است
تصور موقعیتی که کسی مقداری کمتر از آن را وارد کند دشوار است 1برای نشان دادن ستونی که از آن مقدار استخراج می شود. اگر چه این امکان وجود دارد که مقدار این آرگومان توسط یکی دیگر از تابع های اکسل تو در تو محاسبه شود VPR.
بنابراین، اگر آن اتفاق می افتد که برهان col_index_num (شماره_ستون) کمتر از 1تابع VPR همچنین یک خطا را گزارش خواهد کرد #ارزش!.
اگر استدلال col_index_num (column_number) از تعداد ستون های آرایه داده شده بیشتر است، VPR یک خطا گزارش خواهد کرد #مرجع! (#SSYL!).
خطای #NAME؟ در VLOOKUP
ساده ترین حالت یک اشتباه است قطب مثبت باتری (#NAME؟) - اگر به طور تصادفی نام تابع را با خطا بنویسید ظاهر می شود.
راه حل واضح است - املای خود را بررسی کنید!
VLOOKUP کار نمی کند (محدودیت ها، هشدارها و تصمیمات)
علاوه بر نحو نسبتاً پیچیده، VPR محدودیت های بیشتری نسبت به هر تابع اکسل دیگری دارد. به دلیل این محدودیت ها، فرمول های به ظاهر ساده با VPR اغلب منجر به نتایج غیرمنتظره می شود. در زیر راه حل هایی برای چندین سناریو رایج پیدا خواهید کرد VPR غلط است.
1. VLOOKUP به حروف کوچک و بزرگ حساس نیست
عملکرد VPR بین حروف بزرگ و کوچک تفاوت قائل نمی شود و حروف کوچک و بزرگ را یکسان می پذیرد. بنابراین، اگر چندین عنصر در جدول وجود داشته باشد که فقط در حروف کوچک و بزرگ با هم تفاوت داشته باشند، تابع VLOOKUP بدون در نظر گرفتن حروف کوچک، اولین عنصر یافت شده را برمی گرداند.
تصمیم: از یک تابع اکسل دیگر استفاده کنید که می تواند جستجوی عمودی (LOOKUP، SUMPRODUCT، INDEX و MATCH) را در ترکیب با دقیقA که مورد را متمایز می کند. برای جزئیات بیشتر، می توانید از درس یاد بگیرید - 4 روش برای حساس کردن VLOOKUP به حروف بزرگ و کوچک در اکسل.
2. VLOOKUP اولین مقدار یافت شده را برمی گرداند
همانطور که می دانید، VPR مقدار را از ستون داده شده مربوط به اولین تطابق یافت شده برمی گرداند. با این حال، می توانید آن را وادار کنید که 2، 3، 4 یا هر تکرار دیگری از مقدار مورد نظر خود را استخراج کند. اگر نیاز به استخراج همه مقادیر تکراری دارید، به ترکیبی از توابع نیاز خواهید داشت شاخص (فهرست مطالب)، کمترین (کوچک) و LINE (ROW).
3. ستونی به جدول اضافه یا حذف شد
متاسفانه فرمول ها VPR هر بار که یک ستون جدید به جدول جستجو اضافه یا حذف می شود، کار را متوقف کنید. این اتفاق می افتد زیرا نحو VPR از شما می خواهد که محدوده کامل جستجو و شماره ستون خاص را برای استخراج داده ها مشخص کنید. طبیعتاً وقتی ستونی را حذف می کنید یا ستون جدیدی را وارد می کنید، هم محدوده داده شده و هم شماره ستون تغییر می کند.
تصمیم: و دوباره توابع برای کمک عجله دارند شاخص (INDEX) و بیشتر در معرض (همخوانی داشتن). در فرمول INDEX + MATCH شما به طور جداگانه ستون های جستجو و بازیابی را تعریف می کنید، و در نتیجه، می توانید بدون نگرانی در مورد به روز رسانی تمام فرمول های جستجوی مرتبط، هر تعداد ستون را که می خواهید حذف یا وارد کنید.
4. هنگام کپی کردن فرمول، مراجع سلولی مخدوش می شوند
این عنوان ماهیت مشکل را به طور کامل توضیح می دهد، درست است؟
تصمیم: همیشه از مراجع سلولی مطلق (با علامت $) روی رکوردهای محدوده، برای مثال 2 دلار استرالیا: 100 دلار کانادا or $A:$C. در نوار فرمول، می توانید به سرعت با کلیک کردن، نوع پیوند را تغییر دهید F4.
VLOOKUP – کار با توابع IFERROR و ISERROR
اگر نمی خواهید کاربران را با پیام های خطا بترسانید # N / A, #ارزش! or قطب مثبت باتری، می توانید یک سلول خالی یا پیام خود را نشان دهید. با قرار دادن می توانید این کار را انجام دهید VPR به یک تابع IFERROR (IFERROR) در اکسل 2013، 2010 و 2007 یا از یک دسته توابع استفاده کنید IF+ISERROR (IF+ISERROR) در نسخه های قبلی.
VLOOKUP: کار با تابع IFERROR
نحو توابع IFERROR (IFERROR) ساده است و برای خودش صحبت می کند:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
یعنی برای آرگومان اول مقداری را وارد می‌کنید تا خطا بررسی شود و برای آرگومان دوم مشخص می‌کنید که در صورت یافتن خطا چه چیزی را برگردانید.
به عنوان مثال، اگر مقدار مورد نظر شما پیدا نشد، این فرمول یک سلول خالی را برمی گرداند:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
اگر می خواهید پیام خود را به جای پیام خطای استاندارد تابع نمایش دهید VPR، آن را در نقل قول قرار دهید، مانند:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: کار با تابع ISERROR
از آنجایی که تابع IFERROR در اکسل 2007 ظاهر شد، هنگام کار در نسخه های قبلی باید از ترکیب استفاده کنید IF (اگر) و EOSHIBKA (ISERROR) مانند این:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
به عنوان مثال، فرمول IF+ISERROR+VLOOKUP، مشابه فرمول IFERROR+VLOOKUPدر بالا نشان داده شده است:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
برای امروز کافی است. امیدوارم این آموزش کوتاه به شما در مقابله با تمام اشتباهات احتمالی کمک کند. VPR و فرمول های خود را به درستی کار کنید.