چرا تابع 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 بصورت عددی هست. پس علیرغم ظاهر مشابه، با هم تفاوت دارند. حالا باید نوع داده ها رو یکسان کنیم. یا متنی ها رو به عدد تبدیل کنیم یا عددی ها رو به متن.
برای این کار حتما آموزش چهار روش تبدیل متن به عدد رو مطالعه کنید.
سلام خانم خاکزاد محترم
من به تازگی دارم از این تابع استفاده میکنم
مشکلی که دارم اینه که نمیتونتم به پارامتر های دیگه برم
یعنی وقتی مقدار اول رو وارد میکنم و بعدش علامت کاما رو میزنم تو همون مقدار اول می مونه
تو تصویر زیر هم مشخصه
http://s3.picofile.com/file/8373319926/Untitled.jpg
سلام،
کارکتری که به عنوان جدا کننده در فرمول ها استفاده میشه در تنظیمات ویندوز شما روی کارکتر ؛ ( که ترکیب دکمه Ctrl + Y در کیبورد فارسی هست) تنظیم شده که برای تغییر این کارکتر از مسیر زیر میتونید به , یا ; تغییر بدید:
Control panel> Region>Additional Setting>List Seperator
سلام و خسته نباشید و ممنون از مطالب مفیدی که به اشتراک میذارید .
وقتی از تابع VLOOKUP استفاده میکنم ستونهایی که برگردانده می شن اعداد کاملا درست و در جای خودشون هستن با این تفاوت که متاسفانه پشت همه ی اعداد منفی گذاشته و اون اعداد منفی شدن و منجر به بروز خطا در محاسبات دیگه شده. ممنون میشم علت رو بفرمایید و راهنمایی کنید.
درود بر شما
همچین مسئله ای بصورت خودکار وجود نداره
احتمالا قبل از اینکه فرمول شروع بشه، – تایپ شده!
سلام خانم
من در یک فایل اکسلُ دو شیت دارم
میخواهم با دستور vlookup با مرجع قرار دادن یک سلول در شیت ۱ول اطلاعات دیگر مربوط به آن سلول را از شیت دوم فراخوانی کنم یغام غلط می دهد
آیا این دستور فقط در یک شیت کار میکند یا قابل استفاده در چند شیت هم هست ؟
لطفا با مثال راهنمایی فرمایید. ممنون
درود بر شما
هیچ تابعی همچین محدودیتی نداره
این لینک رو مطالعه کنید:
https://excelpedia.net/excel-external-link/
سلام جواد،
شما میتوانی تنها یک شیت دیگه رو مرجع قرار بدی بلکه یک فایل مجزای دیگر رو هم میتونی مرجع قرار بدی. که این نیاز مند این است که دو فایل همزمان باز باشند.
اگر با پیغام N/A# مواجه میشی، اون سلولی رو که این پیغام رو میدی انتخاب کن. در کنارش یک علامت تعجب در یک لوزی زرد نمایان میشه. روی اون بزن و گزینه Show Calculation Steps رو بزن که بهت نشون بده دقیقا کجای کار این فرمولت اشتباهه.
تصحیح:
* شما میتوانی نَه تنها
سلام خانم خاکزاد،
من از Vlookup استفاده کردم برای یه جدولی که شامل ۱۰۰ ردیف است.
Vlookup باید متنی را که در ستون شماره یک هست را برگشت دهد.
یعنی در A1:A100 جستجو میکند و از همان ستون گزینه ای را که پیدا کرد را برگشت میدهد.
برای ۹۰ درصد کلماتی که جستجو میکند اشتباهی نمیکند ولی در بعضی اسم ها، گزینه اشتباهی را پیدا میکند.
اسم هایی که در A1:A100 وجود دارند اسم های میوه ها و سبزی ها هستن.
مثلا وقتی سیب را میگردد همان سیب را بر میگرداند. پرتقال، گلابی، والک، شوید،… به خوبی کار میکنند.
ولی مثلا وقتی ذرت شیرین را میگردد، گزینه دارابی رو برمیگرداند. این دو کلمه حتا شبیه به هم هم نیستند.
بنظر شما چکار کنم؟
در بعضی موارد دیگر هم میبینم که گزینه دارابی را پس میدهد. نمیدانم چرا به این گزینه حساس است.
لطفا پیشنهاد خودتون رو بهم بدین.
میتونم حتا این فایل رو براتون ارسال کنم که خودتون ببینید.
ازتون تشکر میکنم.
درود بر شما
آرگومان آخر رو صفر بذارید
سلام
من همه موارد بالا رو چک کردم ولی مشکلم حل نشد.زیاد از اکسل سر درنمیارم،اینکه میگید در عدد ضرب کنم تغییر داده رو چه کنم؟به نظرتون هر دو ستون مورد نظرم رو در عدد یک ضرب کنم کفایت میکنه؟؟
درود بر شما
ضربدر یک بشه هیچ تغییری نمیکنه…
علت باید مشخص بشه اول. بعد یک یاز این راه ها جواب میده
سلام خانم خاکزاد
خیلی ممنون از جوابتان.
من آرگومان آخر رو با
۰
۱
۱-
امتحان کردم.
فکر کنم vlookup حرف دال را اشتباهی به جای ذال تشخیص میدهد.
به خاطر همین به جای ذرت
گزینه دارابی را انتخاب میکند.
من به جای vlookup از
Index match استفاده کردم که همون کار vlookup رو انجام میده و این مشکل من رو حل کرد.
نمیدونستم که index match دقیقا همون کار vlookup رو انجام میده.
با تشکر
علی
آرگومان آخر تابع Vlookup اصلا -۱ نداره!!
و وقتی صفر گذاشته بشه یعنی سرچ دقیق انجام بشه.
اگر توابع دیگه انجام بدن، نشون میده که فرقی بین د و ذ نیست (که منطقا و واقعا هم نیست)
بله ترکیب Match و Index خیلی قدرتمند هست و خیلی کارها میکنه. اما باز هم مسئله تفاوت د و ذ نیست.
به هر حال مهم اینه که الان مسئله حل شده. ولی خب حتما دنبال دلیل کار نکردن تابع Vlookup باشید.
سلام خانم مهندس ، یک سوال تخصصی دارم ، من هم مانند شما عاشق اکسل هستم و از آن در رشته مربوط به خودم یعنی دفتر فنی شرکت پیمانکاری استفاده می کنم و با آن کارهای زیادی از قبیل لیستوفر هوشمند و فایل صورتجلسات انجام دادم ، اما مشکلی که خیلی وقت با آن دست و پنجه نرم میکنم و نتوانستم برای آن راه حلی پیدا کنم این است که در vlookup اعداد ردیف که از ۹ بیشتر شود خطای NA# را میدهد و دست آخر تسلیم شده و از حروف انگلیسی بعد از ۹ استفاده کرده و مشکل خود را حل کردم . اما هنوز سوال تو ذهن من هست ، اگر امکان دارد کمک کنید.
درود بر شما
خیلی هم عالی
سوالتون خیلی عجیبه
یعنی چی بعد از ۹؟ ردیف چی؟
سلام مجدد
خانم خاکزاد تو فایل من دو تا شیت هست یکی ریز متره و یکی برگ روکش .
برای انتقال شماره آیتم و جمع مبلغ آیتم به برگه روکش من از این فرمول استفاده کرده ام به این ترتیب که برای شماره آیتم مثلاً ۱-۱ را تایپ میکنم و برای جمع هم ۲-۱ و در صفحه روکش هم همین شماره ردیف ها وجو دارد از ۱ تا هر چند ردیف شد . مثلاً ۱ و بطور سریالی تا ۹ و بالاتر و برای انتقال این فرمول رو مینویسم :
( ۳ ; A:E!” ریزمتره”;”۱-“& A8 )VLOOKUP
A8 در اینجا همان سلول انتخاب شده است . و برای اعداد ۱ تا ۹ هیچ مشکلی نیست ولی از ۹ که بالاتر میرود خطای NA# را میدهد
درود بر شما
اینطور ی نمیشه نظر داد
فرمولی هم که نوشتید اینجا بهم ریخته و معلوم نیست چیه
ولی این خطا مال پیدا نکردنه. دقت کنید روی اعداد ۹ به بالا. شاید اتفاقی براش میفته که پیدا نمیکنه. فرمت ها رو چک کنید. مبدا و مقصد باید عین هم باشن
سلام خانم خاکزاد
واقعا متوجه سوال من نشدید یا ما را گرفتید.
من سوال آقای سیامک را خواندم و وقتی گفت ۰ و ۱ آخر را میزند درست میشود . من هم اینکار را کردم و درست شد و بعد از آدرس نهایی خودم وقتی عدد صفر را تایپ کردم بعد از ۹ را هم برایم انتقال داد و مشکل حل شد. ولی شما باید در جواب این را می گفتید نه از رو سوال سیامک بفهمم. پس برای کس دیگری مشکل مشابه پیش آمد بگوئید در اولین قدم صفر و یک انتهایی را چک کند.
درود بر شما
اگه دقت کنید (که نمیکنید!) نوشتم براتون فرمولی که نوشتید قابل بررسی نیست چون بهم ریخته. اگر مرتب بود و مشخص میشد کامل ننوشتید بهتون میگفتم.
ضمن اینکه این آموزش، اولین موردش همین مسئله رو توضیح داده که آرگومان آخر باید صفر گذاشته بشه. پس بهتره قبل از اینکه طلبکار باشید و این ادبیات رو بکار ببرید، اول آموزش رو به خوبی مطالعه کنید بعد سوال مطرح کنید.
ضمنا وقتی سوالی مطرح میکنید، فرض بر این هست که حداقل، آموزش موجود رو به خوبی مطالعه کردید و هیچکدام پاسخگو نبوده و سوال مطرح کردید.
موفق باشید
سلام. منم یه مشکل عجیب با این فرمول دارم. توی ستون ۱ شیت اول، کد کالا ،برای مثال کدهای ۱۵۰ تا ۱۵۶ و در ستون دوم شرح کالا برای مثال ۸-۶۳ و ۸-۵۰ و ۶-۶۳ و ۶-۵۰ و۸-۹۰ و ۸-۱۲۵رو نوشتم در شیت دوم ستون ۱ کد رو که مینویسم توی ستون دوم که شرح کالا میباشد و فرمول vlookup رو قرار دادم بایستی شرح کالا رو بنویسه و این کار رو هم انجام میده (اینا رو برای تمرین انجام دادم)تا زمانیکه میخوام این کدها (۱۵۰ تا ۱۵۶ )رو تغییر بدم و به کد مورد نظر خودم که به ترتیب شامل کدهای ۳۰۸۰۰۰۸۰۱۷۰۶۳ و۳۰۸۰۰۰۸۰۱۷۰۵۰ و ۳۰۸۰۰۰۶۰۲۱۰۵۰ و ۳۰۸۰۰۰۶۰۲۱۰۶۳ و …. میباشد شرح کالای ردیف ۱ و ۲ قاطی میکنه و شرح ردیف ۴ شیت ۱ رو مینویسه!!!!!!!
بازم سلام . جواب مشکلم رو پیدا کردم. چون این تابع رو از یه ویدیو آموزشی یاد گرفته بودم و در مورد آرگومان آخری که true یا false هستش چیزی توضیح نداده بود و جاش رو خالی گذاشت ، این مشکل پیش میومد و من اون آرگومان رو false کردم و درست شد.
درود بر شما
بله در بالا هم توضیح داده شده که اگر خروجی نامرتبط هست، آرگومان آخر ۰ نیست.
پیشنهاد میکنم برای مطالعه کاربرد حالت True این آرگومان، پست زیر رو بخونید:
https://excelpedia.net/vlookup-interval-search/
سلام مطلب خوب و کاربردی بود . من یک مشکل در استفاده از این فرمول دارم اون هم اینه که وقتی که میخوام فایل رو در شبکه داخلی سازمان استفاده کنم آدرسی که میدم بعد از یه مدت به آدرس شبکه تبدیل میشه و مقدار رو برنمیگردونه. هر کاری که می شد کردم ولی وقتی آدرس شبکه میگره به مشکل برمیخوره. اگر میتونید بنده رو راهنمایی کنید.
با سلام
ترکیب تابع INDEX و تابع MATCH میتونه این مشکل رو هم برطرف کنه
سلام
بله دقیقا
اما اگه ورودی ها درست نباشه طبق مسائلی که بالا ارائه شد، خروجی Match هم نمیتونه درست باشه
موفق باشید
سلام
من برای نوشتن Vlookup با مشکل مواجه بودم
همه چیز هم درست بود؛ اما آخرین داده رو بر می گردوند
پروژه خیلی مهم و حیاتی هم بود
تمام راه های از جمله تغییر خاصیت متن از عدد به متن و… رو هم امتحان کردم
آخر سر مشکل من با یه حرکت خیلی ساده حل شد
تمام ستون اول رو در عدد یک ضرب کردم
و value اون ستون رو جایگزین کردم
با کمال تعجب مشکلم حل شد
سلام
بله دقیقا.
ممنون از اینکه تجربتون رو در اختیار گذاشتید.
سلام دوست عزیز
ممنون از تجربه ی عالیتون که مشکل من رو هم حل کرد.موفق باشین