چرا تابع Vlookup درست کار نمیکنه؟
چرا تابع Vlookup درست کار نمیکنه؟
این سوالیه که خیلی وقت ها از من پرسیده شده. در واقع مشکل اینجاست که فرد داره داده مورد نظرشو توی داده ها می بینه، اما تابع vlookup اونو نمیتونه پیدا کنه و خطای N/A# رو نشون میده یا اشتباها داده دیگه ای رو بر میگردونه و از نظر ما درست نیست. حالا میخوایم بررسی کنیم ببینیم مشکل تابع Vlookup از کجاست. (این شرایط برای تابع Hlookup و Match هم برقرار هست).
این مشکل میتونه دو حالت داشته باشه:
۱- خروجی فرمول درست نیست و در واقع داده غیر مرتبط رو نشون میده.
این به دلیل اینه که آرگومان آخر این تابع ۰ یا False گذاشته نشده . وقتی این آرگومان تعیین نمیشه و خالی گذاشته میشه، جستجو بصورت دقیق نیست و داده نا مرتبط برگردانده میشه. برای آشنایی بیشتر با این آرگومان آخر، این پست رو مطالعه کنید. پس برای حل این مشکل، آرگومان آخر تابع رو ۰ قرار بدید. توجه کنید که گاهی اوقات در این حالت ممکن است خروجی،خطای #N/A نیز باشد.
۲- خروجی فرمول خطای N/A# است.
وقتی خروجی فرمول خطای N/A# است، علاوه بر مورد شماره ۱ (برسی آرگومان آخر فرمول)، باید دو مورد زیر رو هم بررسی کنیم:
مرحله اول: بررسی کنیم ببینیم آیا واقعا دو داده با هم برابرند یا نه؟ برای این کار از = استفاده میکنیم.
اگر خروجی True بود یعنی دقیقا با هم برابرند و اینجا باید چک کنیم ببینیم موردی که در Lookup استفاده شده درست هست یا نه؟ همچنین Match Type رو بررسی کنیم که صفر گذاشته شده باشه.
این موضوع در خصوص حروف و کلمات فارسی که “ی” و “ک” دارند هم زیاد اتفاق میفته. اینجور مواقع Lookup value رو حتما از سلول بگیریم که دچار این اشتباه نشیم.
شکل ۱-مشکل تابع Vlookup – بررسی مساوی بودن دو داده مورد نظر
اگه خروجی False بود باید رو نکته زیر رو بررسی کنیم:
علت اول:
احتمالا کاراکترهایی که قابل مشاهده نیستند (مثل فاصله Space) در یکی از سلول ها (یا در سلول Lookup value یا در محدوده جستجو table Array) وجود داره.
راه حل:
از تابع Trim استفاده میکنیم. این تابع Space های اضافی (فاصله اول و آخر یک سلول) رو حذف میکنه. برای اینکار مراحل رو طبق تصویر زیر انجام میدیم:
تابع Trim فقط فاصله (Space) رو حذف میکنه. اگر کاراکترهای غیرقابل مشاهده دیگه ای در سلول وجود داشته باشه باید از تابع Clean استفاده کنید. در واقع همه کارهایی که برای تابع Trim کردیم رو در مورد تابع Clean هم انجام میدیم.
علت دوم:
داده هایی که از نظر ما یکسان هستند، ممکنه نوع داده (Data Type) متفاوتی داشته باشند. مثلا یکی به عنوان متن ذخیره شده باشه و یکی بصورت عدد. در اینصورت علیرغم تساوی ظاهری، با هم برابر نیستند. یک راه ساده برای اینکه تشخیص بدیم داده بصورت عدد ذخیره شده یا متن، اینه که از تابع IsText یا IsNumber استفاده کنیم.
شکل ۲- مشکل تابع Vlookup – بررسی نوع داده ذخیره شده
همونطور که در شکل ۲ می بینید سلول ۲A بصورت متنی ذخیره شده ولی سلول D3 بصورت عددی هست. پس علیرغم ظاهر مشابه، با هم تفاوت دارند. حالا باید نوع داده ها رو یکسان کنیم. یا متنی ها رو به عدد تبدیل کنیم یا عددی ها رو به متن.
برای این کار حتما آموزش چهار روش تبدیل متن به عدد رو مطالعه کنید.
سلام خانم مهندس
در اکسل چطور میتونم تشخیص بدم یک خانه ارور #N/A داده ؟
از تابع Vlookup استفاده میکنم و میخوام در صورتی که Vlookup پاسخ #N/A برگدوند من عدد دیگری جایگزین کنم .
سلام
از تابع IFERROR یا ISERROR استفاده کنید.
باسلام
بنده از فرمول vlookup استفاده کردم، ولی در ستون جستجو اعداد حاصل از فرمول می باشند و اخطار n/a را میده
می خواستم کمکم کنید
سلام
اینکه اطلاعات شما در ستون اول جدول با استفاده از فرمول محاسبه میشه مشکل ساز نیست. مطمئن بشید که دقیقا عددی که حاصل از فرمول هست با عددی که جستجو میکنید کاملا یکسان باشد.
با سلام و خسته باشید ..من در یک اکسل چند ستون دارم که مشخصات واریزی درس های دانشجویان در یک دانشگاه در آن نوشته شده است . در این قسمت من میخواهم میزان پرداختی هر دانشجو را طبق کد ملی آن ها پیدا کنم ..ولی یک دانشجو که چند واریزی در چند تاریخ مختلف انجام داده رو بعد از Vlookup گیری کد ملی رو شناسایی میکنه و میزان پرداختی رو میاره ولی واریزی های دیگه رو همان واریزی اولی شناسایی نمیکنه چون کد ملی یکی هستند.من چیکار باید بکنم .میشه راهنماییم کنید.
سلام
برای انجام این کار از دو روش میشه استفاده کرد:
فرمول نویسی (که مقداری پیشرفته هست و در این کامنت نمیشه آموزش داد)
پیوت تیبل که بهترین ابزار برای این کار هست.
اولا خیلی ممنون از انرژی که میذارید. مشکلم اینه که از دو منبع مختلف لیست نمادهای بورسی آمار معاملات رو میگیرم. وقتی میخوام مچ کنم به خاطر اینکه فونت بورس عربی هست و فونت فایلی که میخوام به روزش کنم فارسی هست در vlookup با خطای na مواجه میشم. مثال اینکه نماد ونیرو تو فایل بورس با ی عربی نوشته میشه و در فایل مرجعم فارسی هست. اینو مشکل رو چطوری می تونم حل کنم؟
ممنون، دو راه دارید:
۱- هر بار که اطلاعات رو از سایت بارگذاری میکنید با Find & Replace ی عربی با ی فارسی جابجا بشه و فرمول ها جواب درست رو میدن که این کار زحمت انجام کار تکراری داره که مغایر با هدف استفاده از اکسل هست.
۲- به جای اینکه کل جدول نمادهای بورسی به همراه نام نماد رو منتقل کنید، فقط قیمت های لحظه ای رو بارگذاری کنید و نام نمادها رو (که قبلا ی عربی رو با فارسی جابجا کردید) دست نزنید، اینطوری دیگه نیازی به انجام Find & Replace در هر بار نیست. منابع مختلفی هستند که در هر بار به روزرسانی نماد ها در جدول جابجا نمیشن و تنها با گرفتن عدد اونا میتونید ازشون استفاده کنید.
سلام سرکارخانم خاکزاد
من از دستور TRIM استفاده کردم ومشکل ی که داشتم حل شد
بسیار ممنونم
عالی بود ممنونم
سلام.بین دو فایل تابع vlookup نوشتم.الان یکی از فایلها رو جابه جا کردم به یک درایو دیگه.فرمولها الان نمیخونه.میشه بفرمایید راه حلو؟
درود بر شما
از قسمت Edit link آدرس جدید فایل و بدید
با سلام
در استفاده از تابع vlookup برای آرگومان اول، وقتی یک سلول(سلول اول معلوم) رو وارد میکنم و میخوام فرمول رو به کل ستون تعمیم بدم، در همه سطرها، در فرمول، آرگومان اول همان سلول می افتد. چرا یکی به آن اضافه نمیکند؟؟؟
البته وقتی کل ستون معلوم را بعنوان آرگومان اول معلوم انتخاب میکنم، درست میشود.
میخوام بدونم مشکلی نیست؟ جوابها درستند؟
با تشکر
درود بر شما
برای اطمینان به نتیجه فمرول نویسی، حتما حتما باید $ ها درست باشه
این مقاله رو بخونید
http://excelpedia.net/cell-address/
باعرض سلام خدمت خانم خاکزاد
من یک فایل اکسل در SHEET 1 دارم
این اطلاعات رو میخواستم در قالب یک فرم در SHEET 2 به مدیرم ارایه بدم
مثلا
در قسمت بالای شیت کد محصول رو بزنم و بقیه طلاعات به اون محصول خودکار در فرم نوشته و چاپ بشه .
تمام اطلاعاتم یک شکل و یک قالب داره ولی زمان و نام و نوع هرکدام مشابه نیست
ممنون میشم کمکم کنید
درود بر شما
اگر میخواید با زدن کد محصول سایر اطلاعات داخل فرم بشینه، بهترین راحش vlookup هست
چون کد محصول غیر تکراری هست
https://excelpedia.net/vlookup-function/
ممنون از لطف شما
درست شد
اگر بخواهیم عکس را انتقال بدهیم که ضمیمه باشد ایا راهی دارد یا خیر ؟
یک راه استفاده از تابع hyperlink هست
داخل سایت سرچ کنید و مقاله رو ببینید
سپاس
با سلام و احرام چگونه عدد ۱۳۶ را در فرمول زیر بعد از درگ کردن در سلول ستون بعد به ۱۳۷ تبدبل کنم چون تبدیل نممیشه متاسفانه
درود بر شما
بجاش باید تابع بذارید
اینطور مواقع یا از match استفاده میکنید که خودش شماه ستون رو تشخیص بده
یا اینکه اگر میخواید به ترتیب پیش بره و ۱۳۶ بشه ۱۳۷ و الی آخر. با فرض اینکه فرمول رو بصورت افقی درگ میکنید، میتونید از تابع( column(EF1 استفاده کنید. (شماره ستون EFمعادل ۱۳۶ هست که وقتی درگ میشه به سمت راست، به ۱۳۷ و … تبدیل میشه)