یکی از توابع مهم در اکسل تابع vlookup در اکسل میباشد که این تابع برای آن که داده ها در اکسل با هم ادغام شوند به کار میروند. همان طور که میدانید داده های حسابداری همیشه در یک شیت و یک فایل موجود نیستند و برای ان که بتوانیم اطلاعات صحیحی از داده های آماری به دست آوریم نیاز است اطلاعات پراکنده را با هم ادغام کنیم.

تابع vlookup در اکسل در این حوزه ما را کمک میکند و شما به راحتی میتوانید با این تابع داده های پراکنده خود را ادغام کنید.

آموزش تابع vlookup در اکسل

شاید برای شما این سوال پیش آمده باشد که اصلا چرا باید از تابع vlookup در دو شیت اکسل استفاده کنیم و این تابع چه کاری انجام میدهد.

تابع vlookup به دنبال یک معلوم در بین داده ها میگردد و تعیین میکند که مقدار مجهول مورد نظر ما در این شیت ها چیست.برای مثال فرض کنید در شکل زیر شما باید پیدا کنید که نمره فارسی انش آموز با کد داشن آموزی 2000 چند است.

شما به راحتی در ستون کد دانش آموزی به دنبال کد 2000 میگردید و و مقداری که جلوی آن مربوط به نمره فارسی اوست را تعیین کردید.

این دقیقا کاری است که تابع vlookup اکسل انجام میدهد یعنی یک معلوم را پیدا میکند (کد دانش آموزی 2000) و به دنبال مقدار مجهول مورد نظر در جدول میگردد.

اگر بخواهیم تعریف تابع vlookup در اکسل را به زبان ساده به شما بگوییم این است که:

تابع vlookup در اکسل یک چیزی را پیدا میکند و به دنبال آن میگردد که جلوی آن چه چیزی است.

نحوه نوشتن تابع VLOOKUP

برای آن که بهتر آموزش تابع vlookup را داشته باشیم با یک مثال آن را به شما توضیح میدهیم.

=VLOOKUP(A, B:E , column , 0)

• A :

مقداری است که مشخص است و ما آن را در اکسل پیدا کرده ایم و بعد به دنبال داده ای هستیم که بعد از ادغام شدن با هم جلوی داده معلوم نوشته میشود و میتوان آن را به عنوان جواب بیرون کشید.

• B:E

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

• column

در این جا باید شماره ستونی که به دنبال جواب در آن هستید را تعیین کنید.

تمرین تابع vlookup

سوال : فرض کنید میخواهیم بفهمیم در سومین ستون نمره bahar چقدر شده است؟

مقدار مشخص ما اسم baharمیباشد که باید آن را به دلیل آن که به صورت متنی است در “” بنویسید. VLOOKUP(“BAHAR”=

در مرحله بعد باید محدوده تعیین شود. اسم بهار در ستون E نوشته شده است و سومین ستون در صورتی که از این ستون شروع شود ستون E میباشد. پس محدوده مورد نظر ما C:E میباشد و جواب تا اینجا به اسن صورت نوشته میشود :

VLOOKUP(“BAHAR” , C:E=

ستون مجهول ما ستون 3 میباشد پس متغیر سوم 3 میباشد و جواب به این صورت نوشته میشود : VLOOKUP(“BAHAR” , C:E , 3=

• ورودی چهارم 0 یا 1

ورودی چهارم 0 میباشد که میتوان به جای آن FALSE و یا 1 (TRUE) قرار داد. این ورودی اختیاری است. در صورتی که ورودی آخر 0 باشد به این معنی است که این تابع باید به دنبال دقیقا ورودی اول بگردد و آن را پیدا کند و بعد به دنبال مجهول باشد. اما اگر ورودی آخر 1 باشد به این معنی است که میتواند ورودی مشابه را به عنوان ورودی اول در نظر بگیرد که این مشابه دقیقا نزدیک ترین عدد به عدد مورد نظر میباشد.

ما در این مسئله به دنبال آن هستیم که دقیقا نمره قرآن بهار را مشاهده کنیم پس ورودی چهارم را 0 در نظر میگیریم.

جواب

VLOOKUP(“BAHAR” , C:E , 3 , 0)=

در ادامه ما تعدادی تمرین تابع vlookup را برای شما قرار داده ایم که میتانید از طریق آن ها بیشتر با این تابع پرکاربرد در اکسل آشنا شوید.

یکی از مقالات پربازدید ما در مسائل اکسل تبدیل تاریخ میلادی به شمسی در اکسل میباشد که حتما پیشنهاد میکنیم این مقاله را مطالعه کنید و اطلاعات لازم را کسب کنید.

کاربرد تابع vlookup

همان طور که اشاره کردیم از این تابع برای ادغام کردن اطلاعات در شیت ها و فایل های مختلف میباشد. شاید درک این کاربرد از تابع vlookup در اکسل با ذکر یک مثال برای شما راحت تر باشد.

فرض کنید که شما مدیریت حسابداری یک مجموعه بزرگ را به عهده دارید که قرار است اطلاعات مالی از بخش های مختلف بازرگانی و انبار و غیره برای شما ارسال شود.

مثلا در بخش بازرگانی شما میتوانید کالایی که سفارش گرفته اید را مشاهده کنید و در بخش انبار میتوانید تعداد کالاهای موجود را لیست کنید.

برای آن که اطلاعات به درستی تحلیل و بررسی شوند نیاز است این اطلاعات با هم ادغام شوند که این کار با تابع vlookup امکان پذیر است.

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

یکی دیگر از کاربردهای تابع vlookup برای محاسبه مالیات حقوق میباشد. برای این محاسبات شما در یک شیت لیست حقوقی افراد را مشاهده خواهید کرد و در لیست دیگر جدول مالیاتی وجود خواه داشت که با استفاده از این دو شیت میتوانید به راحتی مالیات حقوق کارمندان مختلف را محاسبه کنید.

شما با استفاده از تابع vlookup در اکسل 2016 و تابع vlookup در اکسل 2019 و در تمامی ورژن ها میتوانید داده ها را با هم ادغام کنید.

نکات تابع vlookup اکسل

برای آن که بتوانیداز این تابع به درستی استفاده کنید نیاز است که یک سری اطلاعات و نکات را درباره این تابع بدانید. در این قسمت نکات تابع vlookup را با هم بررسی میکنیم.

• تابع vlookup برای آن که بتوان داده ای در سمت را جستجو کرد مورد استفاده قرار میگیرد. اگر بخواهید با استفاده از این تابع به دنبال داده ای در سمت چپ بگردید با ارور رو به رو میشوید. به همین دلیل توابع XLOOKUP و INDEX و MATCH مطرح شدند که میتوانید با استفاده از این توابع داده هایی را که در سمت چپ قرار دارند را نیز پیدا کنید.
• برای آن که بتوانید از این تابع استفاده کنید باید داده هایی که در یک ستون قرار میگیرند با هم مرتبط باشند اما میتوانند اشکال مختلفی داشته باشند.
• در این تابع میتوان مشخص کرد دقیقا همان چیزی که به دنبال آن هستیم را به ما برگرداند که در این صورت باید ورودی چهارم را 0 وارد کنیم و یا میتوان مشخص کرد مقدار تقریبی که نزدیک ترین مقدار به حالت مشص شده است را برگرداند که در این صورت باید ورودی چهارم را 1 در نظر گرفت.
• در این تابع اولین مطابقتی که صورت گیرد را به عنوان جواب خروجی میدهد به همین دلیل باید برای استفاده از این تابع داده های جدول را مرتب سازی کرد.

تابع 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 در اکسل

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

ارور N/A

یکی از مهمترین مشکلات vlookup در اکسل ارور N/A میباشد. اگر شما در محدوده آرگومان LOOKUP_VALUE که آرگومتن اول است مقداری وارد نکرده باشید و یا مقادیری که در محدوده مورد جستجو وجود ندارد را وارد کرده باشید در این صورت با این ارور رو به رو خواهید شد.

بهتر است ورودی های خود را بررسی کنید و مطمئن شوید این ورودی در جدول وجود دارند و به درستی آن ها را وارد کره اید.

مشکل وارد شدن داده عددی به صورت متنی

یکی از مشکلاتی که ممکن است در حین کار کردن با این تابع با ان رو به رو شوید این است که موقع وارد کردن داده های عددی به اشتباه از “” استفاده کنید. در این صورت اکسل آن را داه متنی در نظر میگیرد و همین امر باعث میشود تابع اجرا نشود.

در این حال یک علامت زرد رنگ نمایش داده میشود که با کلیک روی آن میتوانید بررسی کنید که عدد به صورت متن نوشته شده است و همین ایراد باعث شده تابع به درستی کار نکند.

مشکل vlookup در اکسل VALUE#

یکی دیگر از مشکلاتی که در این تابع وجود دارد خطای  VALUE# میباشد که این خطا زمانی رخ میدهد که طول داده بیشتر از 255 کاراکتر باشد.

برای رفع این ایراد باید داده های خود را کمتر از 255 کاراکتر تعریف کنید.

خطای NAME# مشکل vlookup

در صورتی که شما در تایپ این تابع مشکل تایپی داشته باشید با این ارور رو به رو میشوید. بری رفع این ارور باید تابع تان را مجدد بررسی کنید و به دنبال یافتن غلط های املایی در تایپ تابع باشید.

تمرین تابع vlookup

برای ان که شما بتوانید به درستی از این تابع استفاده کنید ما چند تمرین تابع vlookup را در این قسمت برای شما آورده ایم. با انجام این تمارین میتوانید بیشتر با این تابع آشنا شوید و راحت تر از آن برای ادغام داده ها و اطلاعات شیت ها استفاده کنید.

• میخواهیم۴ امین مقدار از ۴امین ستون را برگرداند. در مثال شکل پایین میتوان فرمول زیر را نوشت:

VLOOKUP(H2 , B3:E9 , 4 , 0)=

خروجی این تابع 58,339$ خواهد بود.

• میخواهیم در دومین ستون از H2 مقدار را برگرداند و به دنبال مقدار سومین ستون متناظر با این مقدار باشد؟

جواب این سوال VLOOKUP(H2 , B3:E9 , 3 , FALSE)= میباشد و خروجی آن LOPEZ است.

تابع vlookup در فاکتور فروش

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

در این حالت lookup_value = کد کالا میباشد. شما میتوانید برای آن که اطلاعات کالای خاصی نمایش داده شود به صورت مستقیم کد کالا را به صورت عددی وارد کنید.

اطلاعات ما در شیت 2 قرار دارد که ما باید در زمان وارد کردن آرگومان دوم شیت دوم را انتخاب کنیم و محدوده ای که قرار است جستجو در آن محدوده صورت گیرد را تعیین کنیم. که در این بخش  A3:C7 محدوده مورد نظر ما در شیت دوم میباشد.

ما در این مثال میخواهیم نام کالا برای شما فراخوانی شود پس باید به دومین ستون را انتخاب کنیم و 2 = col_index_num میباشد.

در نهایت جواب به صورت زیر خواهد بود:

(VLOOKUP(B4,A3:C7,2,0=

آموزش vlookup در اکسل pdfبرای آن که شما بتوانید بهتر با تابع vlookup اکسل آشنا شوید ما پی دی افی در این زمنیه آماده کرده ایم که شما میتوانید آموزش vlookup در اکسل pdf ما را دانلود کنید و بار ها از مثال های گفته شده درباره این تابع استفاده کنید.

سوالات متداولتابع vlookup در اکسل چیست؟

این تابع برای ادغام داده های مختلف در شیت ها و فایل های مختلف استفاده میشود. تابع vlookup در اکسل یک چیزی را پیدا میکند و به دنبال آن میگردد که جلوی آن چه چیزی است.کاربرد تابع vlookup اکسل چیست؟

کاربردهای این تابع در اکسل زیاد است که در این مقاله به بررسی این کابردها پرداخته ایم برای مثال در حسابداری فروش یک مجموعه بزرگ