فرادرساستفاده از VLOOKUP روی بازه ای از مقادیر در اکسل (+ دانلود فیلم آموزش گام به گام)
VLOOKUP یکی از مشهورترین تابع‌های اکسل به حساب می‌آید. از این تابع به طور معمول برای بررسی مطابقت‌های کامل دو مقدار مانند ID محصول‌ها یا مشتریان استفاده می‌شود؛ اما در این مقاله، شیوه استفاده از VLOOKUP روی بازه‌ای از مقادیر را مورد بررسی قرار می‌دهیم.
فیلم آموزشی آشنایی با نحوه استفاده از VLookup در اکسل
مثال اول: استفاده از VLOOKUP برای انتساب حروف به نمرات یک آزمون
به عنوان یک مثال در این بخش تصور می‌کنیم، فهرستی از نمرات یک آزمون را در اختیار داریم و می‌خواهیم به هر نمره یک حرف از الفبا را انتساب دهیم.
بدین ترتیب در جدول ما ستون A نشان دهنده نمره‌های واقعی آزمون و ستون B برای نمایش نمرات حروفی محاسبه شده است. همچنین یک جدول در سمت راست (ستون‌های D و E) ایجاد می‌کنیم که برای نمایش نمرات مورد نیاز برای رسیدن به درجات الفبایی استفاده می‌شود.
با استفاده از VLOOKUP می‌توان از بازه‌ای از مقادیر در ستون D برای انتساب نمره حرفی در ستون E به همه مقادیر نتایج آزمون استفاده کرد.
فرمول VLOOKUP
پیش از آن که فرمول مورد نظر را روی مثال خود به کار بگیریم، باید یک یادآوری سریع در مورد ساختار VLOOKUP داشته باشیم:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
در این فرمول، متغیرها به صورت زیر عمل می‌کنند:
- lookup_value: این همان مقداری است که به دنبالش هستیم. در این مورد این مقدار نمره موجود در ستون A است که از سلول A2 آغاز می‌شود.
- table_array: این مقداری است که به طور غیر رسمی به عنوان جدول lookup مورد استناد قرار می‌گیرد. در این مثال، این همان جدولی است که شامل نمرات و درجات حروفی (بازه D2:E7) است.
- col_index_num: این همان شماره ستونی است که نتایج در آن قرار خواهند گرفت. در مثال ما این ستون B است؛ اما از آنجا که دستور VLOOKUP نیازمند مقدار عددی است نام آن را ستون 2 می‌گذاریم.
- <range_lookup: این یک سؤال منطقی است و از این رو پاسخ آن True یا False است. اگر مشغول کار روی lookup بازه‌ای هستید، پاسخ بله است که به صورت TRUE تعیین می‌شود.
بنابراین فرمول تکمیل شده در این مثال به صورت زیر خواهد بود:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
آرایه جدول طوری تثبیت شده است که وقتی فرمول روی سلول‌های ستون B کپی می‌شود تغییری پیدا نکند.
نکته مهم
زمانی که از VLOOKUP برای بررسی بازه‌هایی از مقادیر استفاده می‌کنید، حتماً باید ستون نخست آرایه جدول (در این فرض، ستون D) با ترتیب نزولی مرتب‌سازی شده باشد. این فرمول برای کارکرد صحیح و قرار دادن مقدار مورد نظر در بازه درست به این پیش‌فرض نیازمند است.
در ادامه تصویر نتایجی را مشاهده می‌کنید که در صورتی که آرایه جدول به جای نمره عددی با درجه حرفی مرتب‌سازی شده باشد.
یک بار دیگر تأکید می‌کنیم که ترتیب در بررسی‌های VLOOKUP بازه‌ای حائز اهمیت است؛ اما زمانی که در انتهای تابع VLOOKUP از مقدار false استفاده کنید، ترتیب دیگر اهمیت چندانی ندارند.
مثال دوم: ارائه تخفیف بر مبنای میزان خرید مشتری
در این مثال، با برخی داده‌های فروش سر و کار داریم. ما قصد داریم که بر اساس میزان خرید مشتری، مقداری تخفیف به وی بدهیم و درصد این تخفیف به حجم خرید مشتری وابسته است.
جدول lookup (ستون‌های D و E) شامل مقادیر تخفیف برای هر سبد خرید است.
فرمول VLOOKUP زیر می‌تواند برای بازگشت دادن مقدار تخفیف صحیح برای جدول فوق استفاده شود:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
این مثال بسیار جالب است، زیرا می‌توانیم از آن برای یک فرمول جهت کسر تخفیف نیز استفاده کنیم. اغلب می‌بینیم که کاربران اکسل برای چنین محاسباتی از فرمول‌های پیچیده استفاده می‌کنند؛ اما فرمول VLOOKUP روشی منسجم و سرراست برای این محاسبه ارائه می‌کند.
در ادامه VLOOKUP به فرمول اضافه شده تا مقدار تخفیف را از مبلغ مورد نظر کسر کند و مقدار خرید را در ستون A بازگشت دهد.
=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)
فواید تابع VLOOKUP صرفاً محدود به مواردی که به دنبال رکوردهای خاصی مانند کارمندان یا محصولات می‌گردیم نمی‌شود. کاربردهای این فرمول بسیار متنوع‌تر از آن چیزی است که اغلب افراد تصور می‌کنند و به‌کارگیری آن روی یک بازه از مقادیر صرفاً نمونه‌ای از آن است. همچنین می‌توانید از آن به عنوان جایگزینی برای فرمول‌های پیچیده دیگر نیز استفاده کنید.
اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:
- مجموعه آموزش‌های نرمافزار مایکروسافت اکسل
- آموزش اکسل (Microsoft Office Excel 2016)
- آموزش‌های مجموعه نرم‌افزاری آفیس
- گنجینه آموزش های اکسل (Microsoft Excel)
- آموزش مقدماتی اکسل (Excel) — به زبان ساده
==
