تابع Vlookup از توابع جستجو
آموزش تابع VLookup
تابع Vlookup اکسل یکی از پرکاربردترین توابع این نرم افزار هست که آشنایی و استفاده درست از اون میتونه خیلی کارها رو راحت تر کنه. با استفاده از تابع Vlookup اکسل میتونیم یک مقدار رو در یک ستون جستجو کنیم و عدد متناظر اون رو در ستون های بعدی بدست بیاریم. در این مطلب با نحوه استفاده از این تابع آشنا میشیم.
نکته:
میتونیم از ترکیب دو تابع Index و Match که آموزشش در مطالب سایت وجود داره به عنوان جایگزین تابع Vlookup استفاده کنیم. زمانی که فایل اکسل ما سنگین هست و محاسبات زیادی داره این کار میتونه باعث بهبود سرعت محاسبات بشه.
قاعده کلی تابع Vlookup اکسل به صورت زیر هست:
(جستجوی تقریبی [اختیاری]، شماره ستون، محدوده جستجو، مقدار موردنظر برای جستجو) VLOOKUP
فرض کنید ما یک Workbook داشته باشیم که دو تا شیت حاوی اطلاعات زیر داشته باشه:
Sheet1:
D C B A
۱ کد محصول نام محصول تعداد برنامه تولید تعداد تولید شده
۲ ۱۴۱۱ ماوس ۳۰۰۰
۳ ۲۷۱۲ کیبورد ۲۰۰۰
۴ ۷۴۱۵ مانیتور ۱۰۰۰
۵ ۱۳۵۲ فلش ۱۰۰۰
Sheet2:
C B A
۱ کد محصول نام محصول تعداد تولید شده
۲ ۱۴۱۱ ماوس ۲۰۰۰
۳ ۲۷۱۲ کیبورد ۱۵۰۰
۴ ۷۴۱۵ مانیتور ۳۵۰
۵ ۱۳۵۲ فلش ۷۰۰
۶ ۱۳۵۲ فلش ۸۰۰
حالا اگر بخوایم تعداد تولید شده متناظر هر محصول رو از Sheet2 برداریم و در ستون D در Sheet1 قرار بدیم میتونیم از تابع Vlookup اکسل استفاده کنیم. ما میخوایم در سلول D2 در Sheet1 تعداد تولید شده مربوط به محصول ماوس از Sheet2 قرار بگیره. برای اینکار پارامترهای تابع Vlookup رو به ایتصورت مقداردهی میکنیم.
مقدار موردنظر برای جستجو: این پارامتر مقداری رو که قراره در محدوده موردنظر ما جستجو بشه تعیین میکنه. در واقع ما با این پارامتر به تابع میگیم که از بین اعداد موجود در ستون تعداد تولید شده Sheet2، عددی رو که در سطر متناظر این پارامتر قرار داره برگردونه. ما در این مثال میخوایم تعداد تولید شده رو برای محصولات مختلف بدست بیاریم. بنابراین مقداری که ما میخوایم بر اساس اون جستجو رو انجام بدیم کد هر محصول هست که در ستون A قرار داره. پس پارامتر اول ما برابر A:A (ستون A مربوط به Sheet1) هست.
محدوده جستجو: در این پارامتر ما بازه ای رو که در اون قراره جستجو رو انجام بدیم تعریف میکنیم. این بازه باید از ستونی که ما قراره در اون جستجو رو انجام بدیم (در مثال ما ستون کد محصول) شروع بشه و ستونی که به دنبال نتایج متناطر جستجو از اون هستیم (ستون مربوط به تعداد تولید شده) رو هم شامل بشه. ما میخوایم کد هر محصول (A2) رو در ستون A در Sheet2 جستجو کنیم و بعد از پیدا کردن شماره سطری که مقدار A2 در اون قرار داره، مقدار متناظرش رو از ستون C (تعداد تولید شده) برگردونیم. پس پارامتر دوم ما برابر Sheet2!A:C (ستون های A تا C مربوط به Sheet2) هست.
شماره ستون: این پارامتر تعیین میکنه که پس از پیدا شدن مقدار مورد نظر جواب تابع از چه ستون متناظری در محدوده جستجو باید استخراج بشه و به عنوان نتیجه تابع نشون داده بشه. در این مثال ما جستجو رو در ستون یک (ستون مربوط به کد محصول) انجام میدیم اما هدف ما رسیدن به تعداد تولید شده مربوط به کد محصول مورد نظر (سومین ستون محدوده جستجو) هست پس باید شماره ستون رو برابر ۳ قرار بدیم. این باعث میشه که هنگامی که کد محصول مورد نظر ما (پارامتر مقدار موردنظر برای جستجو) در ستون اول پیدا شد، نتیجه تابع از ستون سوم که نشان دهنده تعداد تولید شده مربوط به اون کد محصول هست استخراج بشه.
جستجوی تقریبی: این پارامتر در تابع VLOOKUP اختیاری هست. اگر این مقدار رو برابر TRUE قرار بدید به این معنی هست که اگر مقدار موردنظر برای جستجو در محدوده جستجو وجود نداشت بزرگترین مقدار موجود در محدوده جستجو که کوچکتر از مقدار موردنظر برای جستجو هست به عنوان نتیجه جستجو انتخاب بشه. به عنوان مثال در جدول بالا بزرگترین کد محصول موجود در Sheet2 برابر ۷۴۱۵ هست. حالا اگر جستجوی تقریبی رو برابر TRUE قرار بدیم و پارامتر مقدار موردنظر برای جستجو رو ۸۰۰۰ تعیین کنیم، تابع VLOOKUP نتیجه رو برای بزرگترین مقدار موجود در محدوده جستجو که که کوچکتر از ۸۰۰۰ هست (۷۴۱۵) نشون میده. اگر در این حالت پارامتر جستجوی تقریبی رو برابر FALSE قرار بدیم تابع خطای !REF# رو برمیگردنه. ما میخوایم فقط تعداد تولید شده هر محصول در مقابلش قرار بگیره بنابراین جستجوی تقریبی رو False قرار میدیم.
بنابراین فرمول ما در نهایت به شکل زیر کامل میشه:
(VLOOKUP(A:A;Sheet2!A:C;3;False
حالا فرمول رو در سلول های D3 تا D5 کپی میکنیم تا تعداد تولید شده هر محصول در سلول متناظرش قرار بگیره. در نهایت Sheet1 ما به شکل زیر در میاد.
D C B A
۱ کد محصول نام محصول تعداد برنامه تولید تعداد تولید شده
۲ ۱۴۱۱ ماوس ۳۰۰۰ ۲۰۰۰
۳ ۲۷۱۲ کیبورد ۲۰۰۰ ۱۵۰۰
۴ ۷۴۱۵ مانیتور ۱۰۰۰ ۳۵۰
۵ ۱۳۵۲ فلش ۱۰۰۰ ۷۰۰
نکته ای که در مورد تابع Vlookup اکسل وجود داره اینه که این تابع هنگام جستجو اولین مقدار پیدا شده رو بر میگردونه. مثلاً در مورد محصول فلش که در Sheet2 دو سطر با مقادیر ۷۰۰ و ۸۰۰ وجود داره فقط اولین مقدار یعنی عدد ۷۰۰ به عنوان نتیجه تابع برگردونده میشه. اگر بخوایم در این حالت تمام اعداد موجود مربوط به اون کد محصول با هم جمع بشه و عدد ۱۵۰۰ به عنوان تعداد تولید شده فلش برگردونده بشه باید از تابع SUMIF استفاده کنیم.
نوشته: معراج زارع
مطالب زیر را حتما مطالعه کنید
فایل حقوق دستمزد 98 و اکسل محاسبه مالیات حقوق 98
معرفی توابع جدید در اکسل ۲۰۱۹
همانطور که می دانید مجموعه آفیس هر سه سال یک بار نسخه جدید خود را عرضه می کند و در ورژن های جدید امکانات جدیدی را قرار می دهند که کار با نرم افزارها را برای کاربران راحت تر کند
در اکسل ۲۰۱۹ چندین ابزار کاربردی اضافه شده است که در این قسمت مب خواهیم به معرفی چند تابع جالب و بسیار کاربردی آن بپردازیم. توابعی که در این بخش یاد خواهیم گرفت در نسخه OFFICE 365 وجود داشتند اما چون این نسخه به صورت غیررایگان عرضه شده بود بسیاری از کاربران امکان پرداخت هزینه آن را نداشته و از این توابع محروم بودند
توابعی که به معرفی آنها می پردازیم شامل موارد زیر می باشند:
CONCAT
TEXTJOIN
MINIFS
MAXIFS
IFS
SWITCH
محاسبه مالیات حقوق تحت اکسل
ورود سریع تر اطلاعات
ورود سریعتر داده ها در اکسل به کمک قابلیت Auto Correctشاید برای شما پیش آمده باشد که در یک زمان ورود اطلاعات یک فهرست، به صورت مداوم مجبور به تکرار یک کلمه یا جمله هستید. بهترین راه برای این کار استفاده از ویژگی Auto Correct است. به عنوان نمونه ما در ورود اطلاعات یک جدول کلمه @talant_ir را دائما تکرار می کنیم، و تصمیم داریم با ورود عبارت @t به صورت خودکار کلمه @talant_ir تکرار شود. برای اینکار از مسیر ذیل استفاده می کنیم.
محاسبه بهای تمام شده به روش FIFO
بسیاری از حسابداران محاسبات مربوط به بهای تمام شده را به صورت دستی و به صورت کاردکس بالا انجام می دهند که این کار زمانبر و پر از اشتباه خواهد بوددر این آموزش می خواهیم محاسبات را با استفاده از نرم افزار اکسل انجام دهیم و دقت و سرعت محاسبات را بالا ببریم.
دیدگاهتان را بنویسید