نرم افزار مایکروسافت اکسل (Excel) قابلیت های زیادی را برای شما فراهم می کند. اگر بخواهید به صورت حرفه ای و کامل از این برنامه استفاده کنید، کمک گرفتن از تایع ها و فرمول ها لازم است. امروز در انزل وب نحوه کار با فرمول و تابع VLOOKUP اکسل 🔎 را به شما آموزش خواهیم داد.
کاربرد و آموزش کار با دستور VLOOKUP در Excel
How to Use VLOOKUP in Excel
به صورت کلی VLOOKUP یک تابع در اکسل است که برای جستجو داده ها در یک جدول که به صورت عمودی سازمان یافته است، کاربرد دارد. در ادامه ضمن معرفی بیشتر این تابع، روش استفاده از آن را بررسی می کنیم. همچنین تکنیک ها و نکته هایی را راجب استفاده از تابع را معرفی می کنیم.
VLOOKUP اکسل چیست؟
همانطور که اشاره کردیم، هدف تابع VLOOKUP به دست آوردن اطلاعات از یک جدول به شکل زیر است.
عبارت LOOKUP در اکسل دستور VLOOKUP به معنی جستجو می باشد و حرف اول V تابع نیز مخفف Vertical می باشد. در مثال زیر، می خواهیم با استفاده از شماره Order موجود در ستون B با کمک این تابع سایر اطلاعات آن شناسه را به دست آوریم. برای این کار از فرمول زیر استفاده می کنیم.
= VLOOKUP(1004,B5:F9,4,FALSE)
همچنین اگر با نحوه ساخت جدول آشنا نیستید، پیشنهاد می کنیم مطلب ساده ترین شیوه رسم جدول در اکسل را هم مطالعه نمایید.
ساختار فرمول VLOOKUP اکسل
در این تابع به ترتیب از سمت چپ به راست بعد از VLOOKUP از آرگومان های زیر استفاده می شود.
- انتخاب کلید جستجو مورد نظر (اطلاعاتی که می خواهید آن را بازیابی کنید)
- انتخاب آرایه جدولی که می خواهید نتیحه را بازیابی کنید.
- انتخاب شماره ستون داده های مورد نظر
- تعیین حالت تطبیق جستجو
نحوه کار تابع VLOOKUP
هنگامی که از تابع VLOOKUP اکسل استفاده می کنید، باید بدانید که این تابع بر اساس شماره ستون کار می کند. تصور کنید که هر ستون جدول شماره گذاری شده باشد و از سمت چپ شروع می شود. برای به دست آوردن مقداری از یک ستون خاص، عدد مناسب را به عتنوان شاخص ستون ارائه دهید. به عنوان مثال شاخص ستون برای بازیابی first name در جدول زیر، ۲ است.
در این مثال می توان last name را با ستون ۳ و email را با ۴ باز خوانی کرد.
= VLOOKUP(H3,B4:E13,2,FALSE) // first name
= VLOOKUP(H3,B4:E13,3,FALSE) // last name
= VLOOKUP(H3,B4:E13,4,FALSE) // email address
نکته توابع VLOOKUP اکسل
این تابع فقط می تواند به سمت راست نگاه کند. بنابراین داده هایی که می خواهید بازیابی شوند (مقادیر نتیجه) باید در هر ستونی در سمت راست مقادیر جستجو باشند.
حالت تطبیق دقیق و تقریبی در فرمول VLOOKUP در Excel
این تابع دارای دو حالت تطبیق دقیق و تقریبی است. نام آرگومان کنترل کننده تطبیق این تابع range_lookup می باشد. هنگامی که وضعیت آرگومان TRUE باشد، تابع به جای یک مقدار دقیق با یک دامنه مقادیر مطابقت دارد. به طور مثال برای محاسبات نمره می توان از آن استفاده کرد.
مهم است بدانید که تابع به طور پیش فرض از مقدار تقریبی استفاده می کند. برای اینکه مطابقت دقیق داشته باشید، مقدار را روی FALSE و یا ۰ تنظیم کنید.
= VLOOKUP(value, table, col_index) // approximate match (default)
= VLOOKUP(value, table, col_index, TRUE) // approximate match
= VLOOKUP(value, table, col_index, FALSE) // exact match
مطابقت کامل در دستور VLOOKUP در Excel
در اکثر موارد شما احتمالا از حالت تطبیق دقیق استفاده کنید. در این صورت شما یک کلید منحصر به فرد برای جستجو دارید.
در این مثال از H6 برای یافتن دقیق سال استفاده می شود.
= VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match
آموزش دستور VLOOKUP در اکسل، مطابقت تقریبی برای آموزش VLOOKUP در حسابداری
در مواردی که شما نیاز به یک تطابق تقریبی دارید، می توانید از این حالت استافده کنید. به عنوان مثال در نمونه زیر می خواهیم نرخ کمیسیون را در جدول G5:H10 جستجو کنید.
مقادیر جستجو از ستون C می آیند. برای استفاده از این تابع با حالت مطابقت تقریبی از فرمول زیر استفاده می کنیم.
= VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match
در نظر داشته باشید وقتی که از حالت تقریبی استفاده می کنید، داده ها باید به ترتیب صعودی مرتب شوند.
طرز کار تابع VLOOKUP در Excel، پیدا کردن اولین مقدار (تابع VLOOKUP در فاکتور فروش و..)
در مواردی که مقادیر تکراری وجود دارد، ممکن است بخواهید اولین نتیجه برگشت داده شود. در مثال زیر می خواهیم اولین قیمت برای رنگ Green در جدول را پیدا کنیم. در مثال ما ۳ قیمت برای این رنگ وجود دارند که می خواهیم اولین مقدار نمایش داده شود. برای این کار از فرمول زیر استفاده می کنیم.
= VLOOKUP(E5,B5:C11,2,FALSE) // returns 17
کاربرد دستور VLOOKUP در اکسل، تطابق Wildcard
این تابع از حالت تطابق Wildcard هم پشتیبانی می کند. با این حالت شما می توانید با وارد کردن بخشی از مقدار جستجو، نتیجه را بیابید. برای استفاده از این حالت باید حالت جستجو FALSE باشد. به طور مثال در جدول زیر، ما با تایپ حروف Aya در خانه H4 نتیجه Michael را در خانه H7 و کامل شدن فیلد را خواهیم دید.
= VLOOKUP($H$4&”*”,$B$5:$E$104,2,FALSE)
جستجو دو طرفه در اجرای دستور VLOOKUP در اکسل
با کمک این ویژگی می توانید یک جستجو پویا دوطرفه ایجاد کنید. به طور مثال می خواهیم جستجویی انجام دهیم که هم در سطر و هم ستون به موارد مورد نظر مطابقت داشته باشد.
= VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)
در صورتی که با رند شدن اعداد هم دچار مشکل شدید، مطالعه مطلب آموزش ۳ روش حل مشکل رند شدن اعداد در اکسل نیز برای شما کاربردی و مفید خواهد بود.
کاربرد VLOOKUP در excel با جستجو چند معیاره، چگونه در اکسل از VLOOKUP استفاده کنیم؟
تابع VLOOKUP به طور پیش‌فرض از جستجو چند معیاره پشتیبانی نمی کند. با این حال می توانید از یک ستون کمکی استفاده کنید. در مثال زیر ستون B یک ستون کمکی است که نام و نام خانوادگی را ترکیب می کند.
=C5&D5 // helper column
سپس می توانید از تابع VLOOKUP شرطی زیر استفاده کنید.
= VLOOKUP(H4&H5,B5:E13,4,0)
مشکل VLOOKUP در اکسل، ارور های #N/A در تابع VLOOKUP اکسل
اگر از تابع VLOOKUP استفاده کنید، به ناچار با ارور های #N/A مواجه می شوید. این ارور به معنای یافت نشدن است. به عنوان مثال در صفحه زیر مقدار جستجو Toy Story 2 در جدول وجود ندارد. بنابراین فرمول نتیجه #N/A را بر می گرداند.
یکی از راه های پوشش دادن این ارور، استفاده از تابع IFNA به شکل زیر است. (فرمول if= VLOOKUP)
فرمول این مثال در H6 به شکل زیر است.
=IFNA( VLOOKUP(H4,B5:E9,2,FALSE),”Not found”)
پیام را می توان به دلخواه سفارشی کرد. به طور مثال برای نمایش رشته خالی می توان از فرمول زیر استفاده کرد.
=IFNA( VLOOKUP(H4,B5:E9,2,FALSE),””) // no message
البته دلایل دیگری برای بروز این خطا در VLOOKUP اکسل وجود دارد که می توان به موارد زیر اشاره کرد.
- وجود نداشتن مقدار مورد نظر در جدول
- وجود غلط املایی یا فضای اضافی در کلید جستجو
- تنظیم بودن حالت تطبیق دقیق در حالی که باید تقریبی باشد
- درست وارد نشدن محدوده جدول
- کپی کردن تابع بدون قفل کردن مرجع
در پایان در صورت مواجه با ارور VALUE، مطالعه مطلب ۶ روش رفع مشکل VALUE و نوشتن فرمول در اکسل نیز کاربردی و مفید خواهد بود.
نظرات و پیشنهادات شما
در مطلب امروز آموزش تصویری VLOOKUP در اکسل را به شما ارائه دادیم. می توانید نظرات، پیشنهادات و تجربیات خود در زمینه آموزش VLOOKUP در اکسل را در دیدگاه این پست با ما به اشتراک بگذارید.
سلام
در تابع ولوک آپ من میخواهم در مورد مقادیر تکراری جمع آنها را نشان بده چکار باید بکنم
با سپاس