یکی از توابع مهم در اکسل تابع vlookup در اکسل میباشد که این تابع برای آن که داده ها در اکسل با هم ادغام شوند به کار میروند. همان طور که میدانید داده های حسابداری همیشه در یک شیت و یک فایل موجود نیستند و برای ان که بتوانیم اطلاعات صحیحی از داده های آماری به دست آوریم نیاز است اطلاعات پراکنده را با هم ادغام کنیم.
تابع vlookup در اکسل در این حوزه ما را کمک میکند و شما به راحتی میتوانید با این تابع داده های پراکنده خود را ادغام کنید.
شاید برای شما این سوال پیش آمده باشد که اصلا چرا باید از تابع vlookup در دو شیت اکسل استفاده کنیم و این تابع چه کاری انجام میدهد.
تابع vlookup به دنبال یک معلوم در بین داده ها میگردد و تعیین میکند که مقدار مجهول مورد نظر ما در این شیت ها چیست.برای مثال فرض کنید در شکل زیر شما باید پیدا کنید که نمره فارسی انش آموز با کد داشن آموزی 2000 چند است.
شما به راحتی در ستون کد دانش آموزی به دنبال کد 2000 میگردید و و مقداری که جلوی آن مربوط به نمره فارسی اوست را تعیین کردید.
این دقیقا کاری است که تابع vlookup اکسل انجام میدهد یعنی یک معلوم را پیدا میکند (کد دانش آموزی 2000) و به دنبال مقدار مجهول مورد نظر در جدول میگردد.
اگر بخواهیم تعریف تابع vlookup در اکسل را به زبان ساده به شما بگوییم این است که:
تابع vlookup در اکسل یک چیزی را پیدا میکند و به دنبال آن میگردد که جلوی آن چه چیزی است.
برای آن که بهتر آموزش تابع vlookup را داشته باشیم با یک مثال آن را به شما توضیح میدهیم.
=VLOOKUP(A, B:E , column , 0)
مقداری است که مشخص است و ما آن را در اکسل پیدا کرده ایم و بعد به دنبال داده ای هستیم که بعد از ادغام شدن با هم جلوی داده معلوم نوشته میشود و میتوان آن را به عنوان جواب بیرون کشید.
این محدوده ای را تعیین میکند که در آن باید به دنبال جواب بگردید. این محدوده میتواند بیشتر از مقدار مکشخص باشد و هیچ اشکالی ند ارد اگر شما محدوده بزرگتری را برای بررسی تعیین کنید.
در این جا باید شماره ستونی که به دنبال جواب در آن هستید را تعیین کنید.
تمرین تابع vlookup
سوال : فرض کنید میخواهیم بفهمیم در سومین ستون نمره bahar چقدر شده است؟
مقدار مشخص ما اسم baharمیباشد که باید آن را به دلیل آن که به صورت متنی است در “” بنویسید. VLOOKUP(“BAHAR”=
در مرحله بعد باید محدوده تعیین شود. اسم بهار در ستون E نوشته شده است و سومین ستون در صورتی که از این ستون شروع شود ستون E میباشد. پس محدوده مورد نظر ما C:E میباشد و جواب تا اینجا به اسن صورت نوشته میشود :
VLOOKUP(“BAHAR” , C:E=
ستون مجهول ما ستون 3 میباشد پس متغیر سوم 3 میباشد و جواب به این صورت نوشته میشود : VLOOKUP(“BAHAR” , C:E , 3=
ورودی چهارم 0 میباشد که میتوان به جای آن FALSE و یا 1 (TRUE) قرار داد. این ورودی اختیاری است. در صورتی که ورودی آخر 0 باشد به این معنی است که این تابع باید به دنبال دقیقا ورودی اول بگردد و آن را پیدا کند و بعد به دنبال مجهول باشد. اما اگر ورودی آخر 1 باشد به این معنی است که میتواند ورودی مشابه را به عنوان ورودی اول در نظر بگیرد که این مشابه دقیقا نزدیک ترین عدد به عدد مورد نظر میباشد.
ما در این مسئله به دنبال آن هستیم که دقیقا نمره قرآن بهار را مشاهده کنیم پس ورودی چهارم را 0 در نظر میگیریم.
جواب
VLOOKUP(“BAHAR” , C:E , 3 , 0)=
در ادامه ما تعدادی تمرین تابع vlookup را برای شما قرار داده ایم که میتانید از طریق آن ها بیشتر با این تابع پرکاربرد در اکسل آشنا شوید.
یکی از مقالات پربازدید ما در مسائل اکسل تبدیل تاریخ میلادی به شمسی در اکسل میباشد که حتما پیشنهاد میکنیم این مقاله را مطالعه کنید و اطلاعات لازم را کسب کنید.
همان طور که اشاره کردیم از این تابع برای ادغام کردن اطلاعات در شیت ها و فایل های مختلف میباشد. شاید درک این کاربرد از تابع vlookup در اکسل با ذکر یک مثال برای شما راحت تر باشد.
فرض کنید که شما مدیریت حسابداری یک مجموعه بزرگ را به عهده دارید که قرار است اطلاعات مالی از بخش های مختلف بازرگانی و انبار و غیره برای شما ارسال شود.
مثلا در بخش بازرگانی شما میتوانید کالایی که سفارش گرفته اید را مشاهده کنید و در بخش انبار میتوانید تعداد کالاهای موجود را لیست کنید.
برای آن که اطلاعات به درستی تحلیل و بررسی شوند نیاز است این اطلاعات با هم ادغام شوند که این کار با تابع vlookup امکان پذیر است.
با استفاده از این تابع میتوانید لیست کمبود انبار را در بیاورید و مشخص کنید برای آن که تعداد سفارشات به درستی به دست مشتریان برسد نیاز است چه تعداد کالا تولید گردد.
یکی دیگر از کاربردهای تابع vlookup برای محاسبه مالیات حقوق میباشد. برای این محاسبات شما در یک شیت لیست حقوقی افراد را مشاهده خواهید کرد و در لیست دیگر جدول مالیاتی وجود خواه داشت که با استفاده از این دو شیت میتوانید به راحتی مالیات حقوق کارمندان مختلف را محاسبه کنید.
شما با استفاده از تابع vlookup در اکسل 2016 و تابع vlookup در اکسل 2019 و در تمامی ورژن ها میتوانید داده ها را با هم ادغام کنید.
برای آن که بتوانیداز این تابع به درستی استفاده کنید نیاز است که یک سری اطلاعات و نکات را درباره این تابع بدانید. در این قسمت نکات تابع vlookup را با هم بررسی میکنیم.
برای آن که بتوانید از این تابع در دو شیت مختلف بهره بگیرید نیاز است از فرمول زیر استفاده کنید:
VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])=
به طور کلی میتوان گفت از این تابع برای جستجو بین شیت اهی مختلف و فایل های متفاوت استفاده میشود. برای جستجو بین شیت های مختلف شما میتوانید شما باید نام شیت را در فرمول بالا تعیین کنید.
تصور کنید محصول 1 در یک شیت و محصول 2 در شیت دیگر قرار دارد و ما میخواهیم فروش محصول 1 در اردیبهشت ماه را مورد بررسی قرار دهیم.در این حالت جواب به صورت زیر میباشد:
(A2 ,’محصول ۱′!A1:B13 , 2,0)VLOOKUP=
وقتی شما بخواهید شیت محصول 1 را انتخاب کنید باید در هنگام نوشتن فرمول و وقتی میخواهید آرگومان دوم را انتخب کنید روی شیت محصول ۱ کلیک کنید و Table_Array رو محدوده A1:B13 انتخاب نمایید.
این تابعدر اکسل دارای مشکلاتی است که ما در این قسمت تلاش میکنیم مشکل vlookup در اکسل را با هم بررسی کنیم. گاهی ممکن است شما با استفاده از این تابع در اکسل با ارور هایی رو به رو شوید که این ارور ها دلایل خاصی میتوانند داشته باشند.
ارور N/A
یکی از مهمترین مشکلات vlookup در اکسل ارور N/A میباشد. اگر شما در محدوده آرگومان LOOKUP_VALUE که آرگومتن اول است مقداری وارد نکرده باشید و یا مقادیری که در محدوده مورد جستجو وجود ندارد را وارد کرده باشید در این صورت با این ارور رو به رو خواهید شد.
بهتر است ورودی های خود را بررسی کنید و مطمئن شوید این ورودی در جدول وجود دارند و به درستی آن ها را وارد کره اید.
مشکل وارد شدن داده عددی به صورت متنی
یکی از مشکلاتی که ممکن است در حین کار کردن با این تابع با ان رو به رو شوید این است که موقع وارد کردن داده های عددی به اشتباه از “” استفاده کنید. در این صورت اکسل آن را داه متنی در نظر میگیرد و همین امر باعث میشود تابع اجرا نشود.
در این حال یک علامت زرد رنگ نمایش داده میشود که با کلیک روی آن میتوانید بررسی کنید که عدد به صورت متن نوشته شده است و همین ایراد باعث شده تابع به درستی کار نکند.
مشکل vlookup در اکسل VALUE#
یکی دیگر از مشکلاتی که در این تابع وجود دارد خطای VALUE# میباشد که این خطا زمانی رخ میدهد که طول داده بیشتر از 255 کاراکتر باشد.
برای رفع این ایراد باید داده های خود را کمتر از 255 کاراکتر تعریف کنید.
خطای NAME# مشکل vlookup
در صورتی که شما در تایپ این تابع مشکل تایپی داشته باشید با این ارور رو به رو میشوید. بری رفع این ارور باید تابع تان را مجدد بررسی کنید و به دنبال یافتن غلط های املایی در تایپ تابع باشید.
برای ان که شما بتوانید به درستی از این تابع استفاده کنید ما چند تمرین تابع vlookup را در این قسمت برای شما آورده ایم. با انجام این تمارین میتوانید بیشتر با این تابع آشنا شوید و راحت تر از آن برای ادغام داده ها و اطلاعات شیت ها استفاده کنید.
VLOOKUP(H2 , B3:E9 , 4 , 0)=
خروجی این تابع 58,339$ خواهد بود.
جواب این سوال VLOOKUP(H2 , B3:E9 , 3 , FALSE)= میباشد و خروجی آن LOPEZ است.
یکی از کاربرد های تابع vlookup در فاکتور فروش میباشد. با استفاده از این تابع میتوانید لیست کالا ها را در یک لیست داشته باشید و فاکتور فروش کالا ها را در شیت دیگر نگهداری کنید. با اعمال این تابع میتوانید شرایطی را فراهم کنید که با زدن روی هر کالا مشخصات کالا در فاکتور فروش نمایش داده شود و در جایگاه خود قرار گیرند.
در این حالت lookup_value = کد کالا میباشد. شما میتوانید برای آن که اطلاعات کالای خاصی نمایش داده شود به صورت مستقیم کد کالا را به صورت عددی وارد کنید.
اطلاعات ما در شیت 2 قرار دارد که ما باید در زمان وارد کردن آرگومان دوم شیت دوم را انتخاب کنیم و محدوده ای که قرار است جستجو در آن محدوده صورت گیرد را تعیین کنیم. که در این بخش A3:C7 محدوده مورد نظر ما در شیت دوم میباشد.
ما در این مثال میخواهیم نام کالا برای شما فراخوانی شود پس باید به دومین ستون را انتخاب کنیم و 2 = col_index_num میباشد.
در نهایت جواب به صورت زیر خواهد بود:
(VLOOKUP(B4,A3:C7,2,0=
سوالات متداولتابع vlookup در اکسل چیست؟
این تابع برای ادغام داده های مختلف در شیت ها و فایل های مختلف استفاده میشود. تابع vlookup در اکسل یک چیزی را پیدا میکند و به دنبال آن میگردد که جلوی آن چه چیزی است.کاربرد تابع vlookup اکسل چیست؟
کاربردهای این تابع در اکسل زیاد است که در این مقاله به بررسی این کابردها پرداخته ایم برای مثال در حسابداری فروش یک مجموعه بزرگ
دیدگاه شما