فرمول vlookup در اکسل / بخش هجدهم
فرمول vlookup در اکسل
فرمول vlookup در اکسل : در درس قبلی به آموزش فرمول vlookup در اکسل پرداختیم. در این مطلب نحوه ی ویرایش فرمول vlookup در اکسل و روش هایی جهت بهبود عملکرد این تابع خواهیم آموخت.
آموزش فرمول vlookup در اکسل
با نگاهی به تصویر بالا متوجه خواهید شد تابع VLOOKUP در سطر ۲ به درستی کار می کند اما در سطر ۳ با مشکل روبرو است و از آنجا که فرمول های ستون E به مقادیر سطر ۳ بستگی دارند، آنها نیز قادر به محاسبه درست نیستند.
چه مشکلی را می خواهیم برطرف کنیم؟
همانگونه که از درس قبلی به خاطر دارید، در مثال یک برگه برای لیست محصولات داشتیم. در حال حاضر برخی از محصولات جدید به برگه محصولات افزوده شده است، اما با تغییرات انجام شده VLOOKUP به درستی کار نمی کند. اگر ما به برگه محصولات نگاه کنیم، می بینیم که چند محصول جدید در محدوده سلولی A2: C16 لیست شده است.
اما اگر به تابع در سلول B3 نگاه کنیم می بینیم که تابع VLOOKUP فقط قادر به جستجو در اطلاعات محدوده سلولی A2: C13 است.
همانطور که می بینید، تابع VLOOKUP در لیست کامل محصولات جستجو نمی کند؛ این جستجو تا سطر ۱۳ انجام می شود. بنابراین مشکلی در پیدا کردن Ramekin وجود ندارد، اما تابع نمیتواند Zester ،Grater یا Wire Whisk را پیدا کند.
برای حل این مسئله، ما بایستی دومین آرگومان را در VLOOKUP تغییر دهیم تا کل لیست محصول را جستجو کند. دو راه برای انجام این کار وجود دارد:
محدوده سلول را به روز کنید
استفاده از یک جدول (فقط مایکروسافت اکسل)
محدوده سلولی را به روز کنید.
یکی از راه های حل این مشکل این است که به سادگی محدوده های سلولی فرمول vlookup در اکسل را به روزرسانی کنیم. باید تابع سلول B3 را ویرایش کنیم.
ما تابع را جهت شامل شدن تمام سلول ها در برگه محصولات ویرایش می کنیم و برای مواردی که بعدا به لیست اضافه می شوند، تعداد ردیف ها در محدوده سلولی را به ۱۰۰ افزایش می دهیم. این بدان معنی است که ما از تعداد زیادی سلول خالی استفاده می کنیم. تابع VLOOKUP همچنان به طور صحیح عمل خواهد کرد. بنابراین در اینجا تابع ما به صورت زیر خواهد بود:
(VLOOKUP(A3, Products!$A$2:$C$100, 2, FALSE=
تابع دوباره عمل خواهد کرد و نام محصول صحیح (Zester) باید ظاهر شود. ما همچنین اصلاح مشابهی را با سایر توابع VLOOKUP در صفحه گسترده انجام خواهیم داد:
در حال حاضر صفحه گسترده ما به درستی کار می کند.
از یک جدول استفاده کنید.
در روش اول، ما تابع VLOOKUP را با گسترش محدوده سلولی به ۱۰۰ سطر اصلاح کردیم. اگر نیاز به اضافه کردن محصولات بیشتری داشته باشیم، تابع VLOOKUP مجددا با اشکال روبرو خواهد شد. بنابراین این روش، یک راه حل کامل و مناسب نیست. اگر از مایکروسافت اکسل استفاده می کنید، روش بهتر، استفاده از یک جدول است. با استفاده از جدول با اضافه شدن موارد جدید تابع به درستی عمل خواهد کرد.
جداول در تمام نسخه های اخیر Excel کار می کنند اما در Google Sheets در حال حاضر موجود نیستند.
آموزش فرمول vlookup در اکسل با استفاده از جدول :
در مرحله اول، اطلاعات محصول را انتخاب می کنیم و سپس آن را به یک جدول تبدیل می کنیم. توجه داشته باشید که این کار با توجه به نسخه اکسلی که استفاده می کنید متفاوت است:
برای اکسل ۲۰۰۷ تا ۲۰۱۶: به Table < Insert بروید.
برای اکسل ۲۰۰۳ و قبل از آن: به Data> List> Create List بروید (جداول به عنوان لیست در Excel 2003 و قبل شناخته شدند).
در مرحله بعدی، جدول را نامگذاری خواهیم کرد. در این مثال، نام آن را ProductList انتخاب می کنیم. توجه داشته باشید که نام جدول نمی تواند کاراکتر فاصله داشته باشد.
در تابع VLOOKUP به جای تعریف کردن یک محدوده خاص برای جستجو در برگه محصولات (Products!$A$2:$C$100), ما می توانیم به سادگی نام جدول (ProductsList) را تایپ کنیم.
بنابراین در اینجا تابع جدید ما به این صورت خواهد بود:
(VLOOKUP(A3, ProductsList, 2, FALSE =
در این صورت اگر اطلاعات جدید به جدول اضافه شود، تابع VLOOKUP آن را به صورت خودکار شناسایی می کند. مجددا لازم است همه توابع VLOOKUP را در صفحه گسترده به منظور ارجاع به جدول به جای محدوده سلولی، به روزرسانی کنید.
فرمول vlookup در اکسل
ترجمه شده در سایت خلایق
منبع:
gcflearnfree.org
