vlookupدر اکسل

با سلام و عرض ادب خدمت شما دوست عزیز,
 
لطفا اموزش زیر را مطالعه کرده مجددا تست بفرمایید درصورتی که مجدد به مشکل خوردید در قسمت کامنت برای ما بیان دارید . 
آموزش فرمول VLOOKUP 
تابع VLOOKUP از شما ۴ ورودی می­خواهد و سپس به شما پاسخ را خواهد داد. بگذارید با حل قدم به قدم پرسش الف شروع کنیم.
 در جلوی اسم بهار در سومین ستون جدول چه مقداری نوشته شده است؟
 

 
ورودی ۱)
 در ابتدا VLOOKUP از شما مقدار معلوم را می­خواهد، پس خواهیم نوشت:
=VLOOKUP(“BAHAR”
یادآوری: هر گاه در فرمولی یک متن نوشتیم باید آن متن در داخل علامت (دبل کوت) یعنی ” ” قرار گیرد.
 
ورودی ۲)
 سپس VLOOKUP از شما می­خواهد که جدولی را برای جستجو مشخص کنید. (در واقع کاملا حق هم دارد، از کجا بدانید که در کدام شیت و یا ستون­ها باید جستجو را انجام دهد) . دقت داشته باشید که حتما باید این جدول دو شرط زیر را داشته باشد:
الف) اولین ستون جدول، ستونی باشد که مقادیر معلوم در آن تایپ شده است.
با توجه به تصویر چون نام دانش آموزان در ستون C وارد شده است، باید جدول ما از ستون C شروع شود.
ب) جدول شما شامل ستون مجهول ها هم باشد.
چون سومین ستون جدول (یعنی نمره قرآن) را باید پیدا می­کردیم، حتما باید جدول ما حداقل تا ستون E باشد. بنابراین ادامه تابع ما می­شود
=VLOOKUP(“BAHAR” , C:E
یادآوری: علامت «:» در اکسل به معنای «تا» است. بنابراین C:E را باید بخوانیم ستون C تا E.
نکته: اگر جدول را بیشتر انتخاب کردیم مثلا نوشتیم C:Z، هیچ اشکالی ندارد.
 
ورودی ۳)
 خوب حالا که معلوم و جدول را مشخص کردیم، نوبت می­رسد که به VLOOKUP بگوییم که مجهول ما کجاست. کافی است که جای ستون مجهول را به آن بگوییم. یعنی بگوییم در این جدول (یعنی C:E) سومین ستون حاوی مقداری است که ما آنرا لازم داریم. بنابراین باید بنویسیم:
=VLOOKUP(“BAHAR” , C:E , ۳
توجه ۱: حتما باید سومین ورودی یک عدد باشد. در واقع شما نمی توانید نام ستون مجهول را بدهید.
یادآوری: مجدد تاکید می­شود که شمارش ستون ها باید نسبت به «جدول» باشد.
 
ورودی ۴)
این ورودی را فعلا طوطی وارد عدد ۰ بگذارید. دلیلش را فعلا نپرسید. در این مقاله به صورت مفصل توضیح داده‌ام.
بنابراین شکل نهایی تابع VLOOKUP شما این خواهد شد:
=VLOOKUP(“BAHAR” , C:E , 3 , ۰)
و در پاسخ عدد ۱۴.۵ را خواهید داشت.
 
حل مساله ب)
سوال) در جلوی کد دانش آموزی ۲۰۰۰ در دومین ستون جدول چه مقداری نوشته شده است؟ فرمولش را بنویسید؟
فرمول شما به شکل زیر خواهد بود:
=VLOOKUP(2000 , B:F , 2 , 0)
حالا بگذارید من چند تا سوال بپرسم و مثلا شما پاسخ بدهید:
۱) چرا ۲۰۰۰ را داخل علامت ” ”  قرار ندادید؟
پاسخ شما : چون یک عدد است و فقط متن ها را داخل علامت ” ”  می گذاریم.
 
۲) چرا از ستون B، جدول را شروع کردید؟
پاسخ شما: چون معلوم ما در این مساله کد دانش آموزی است و این کدها در داخل ستون B قرار دارند و حتما باید ستون معلوم ها اولین ستون جدول ما باشد.
 
۳) چرا جدول را B:C ننوشتید؟
پاسخ شما: اشکالی ندارید که ستون های بیشتری را در جدول انتخاب کنیم.
 
۴) عدد ۲ در فرمول بالا چیست؟
پاسخ شما: دومین ستون منطقه B:F است و مجهول های ما در این ستون قرار دارند.
 
۵) عدد ۰ در فرمول بالا چیست؟
پاسخ شما: استاد گفته ۰ بگذارید و من گوش کردم. (برای دانستن معنای ۰ مقاله «محاسبه مالیات حقوق در اکسل با تابع VLOOKUP» را مشاهده نمایید)
 
۶) اگر VLOOKUP نتوانست چیزی را که مطمئن هستیم وجود دارد را بیاید، دلیلش چیست؟
پاسخ:  احتمال‌های زیر را بررسی کنید:
الف) احتمالا یک یا چند Space در داخل سلول معلوم وجود دارد با تابع trim این فاصله های اضافه را حذف کنید.
ب)   باید بدانید که عدد ۱ با متن “۱” برابر نیست. ممکن است شما در تابع عدد ۱ را نوشته باشید اما در جدول ، این اعداد با Format Cells TEXT ، وجود داشته باشند. 
ج) حرف “ی” و “ک” را بررسی کنید.
نکات نهایی تابع VLOOKUP
نکته ۱: حروف بزرگ و کوچک انگلیسی متفاوت نیستند. یعنی BAHAR = baHaR است.
نکته ۲: عدد و متن متفاوتند. یعنی ۲ = “۲” نیست. (در یک مقاله دیگر باید اینرا دقیق توضیح دهیم اما به صورت خلاصه مربوط می شود به Format Cell Text .)
نکته ۳: همواره فرض ما این است که یک مقدار معلوم وجود دارد. اگر مقادیر معلوم ها تکراری بودند، تابع vlookup اولین مقدار را می­یابد.
نکته ۴: تابع vlookup از wildcard ها پشتیبانی می کند یعنی می­توانید به جای “bahar” بنویسید “*aha*” و یعنی سلولی که در آن aha وجود دارد.
نکته ۵: توجه داشته باشید که دو حرف “ی” و “ک” در عربی و فارسی متفاوت هستند. (بازهم در مقاله دیگری باید به موضوع چرا VLOOKUP یک مقدار را نمی یابد، بپردازیم)
نکته ۶: تابع vlookup فقط می تواند رو به جلو جستجو کند. به همین دلیل است که همواره ستون معلوم ها را در ابتدای یک لیست می نویسیم. (برای یافتن مقادیر پشت سر مقدار معلوم باید از ترکیب دو تابع index  و match استفاده کنیم که در یک مقاله دیگر باید به آن پرداخت).
نکته ۷: اگر اطلاعات شما در شیت دیگری بود مثلا شیتی به نام DATA، فرمول اینگونه خواهد شد:
=VLOOKUP(2000 , DATA!B:F , ۲ , ۰)
برای ارجاع به یک فایل دیگر هم، می توانید با موس محدوده داده‌های آن فایل را انتخاب کنید و فرمول شما چیزی شبیه زیر می شود. (داده‌ها در فایل به نامه MyFile در شیتی به نام Data هستند):
=VLOOKUP(2000 , [MyFile]DATA!B:F , ۲ , ۰)
 
نکته ۸: اگر VLOOKUP مقدار یا نیابد به شما خطای N/A# می‌دهد. معمولا ما همواره در اکسل خطاها را مدیریت می‌کنیم و به جای خطا، یک چیز دیگری مثلا عدد ۰ را می‌گذاریم. برای اینکار از فرمول IFERROR مانند نمونه زیر استفاده می‌کنیم:
=IFERROR(  VLOOKUP(2000 , DATA!B:F , 2 , 0)   , ۰)
 

همچنین شما میتوانید از قابلیت جدید وب سایت استفاده کنید , به صورتی که اگر قصد دارید سوال شما در سریع ترین زمان ممکن پاسخ داده شود میتوانید بسته زیر را تهیه کنید .
بسته پاسخگویی سریع
با تشکر,
تیم پشتیبانی.