با ترکیب تابع VLOOKUP اکسل با تابع COLUMN می توانید فرمول جستجویی ایجاد کنید که چندین مقدار را از یک ردیف از یک پایگاه داده یا جدول داده ها برمی گرداند. یاد بگیرید که چگونه یک فرمول جستجو ایجاد کنید که چندین مقدار را از یک رکورد داده برمی گرداند.
دستورالعمل های این مقاله برای Excel 2019، 2016، 2013، 2010 اعمال می شود. و اکسل برای مایکروسافت 365.
خط پایین
فرمول جستجو نیاز به تو در تو بودن تابع COLUMN در VLOOKUP دارد. تودرتو کردن یک تابع شامل وارد کردن تابع دوم به عنوان یکی از آرگومان های تابع اول است.
اطلاعات آموزشی را وارد کنید
در این آموزش، تابع COLUMN به عنوان آرگومان شماره فهرست ستون برای VLOOKUP وارد می شود. آخرین مرحله در آموزش شامل کپی کردن فرمول جستجو در ستون های اضافی برای بازیابی مقادیر اضافی برای قسمت انتخاب شده است.
اولین قدم در این آموزش وارد کردن داده ها در یک کاربرگ اکسل است. برای انجام مراحل این آموزش، داده های نشان داده شده در تصویر زیر را در سلول های زیر وارد کنید:
- گستره بالای داده ها را در سلول های D1 تا G1 وارد کنید.
- بازه دوم را در سلول‌های D4 تا G10 وارد کنید.
معیارهای جستجو و فرمول جستجو ایجاد شده در این آموزش در ردیف 2 کاربرگ وارد شده است.
این آموزش شامل قالب بندی اولیه اکسل نشان داده شده در تصویر نیست، اما بر نحوه کار فرمول جستجو تأثیری نمی گذارد.
یک محدوده نامگذاری شده برای جدول داده ایجاد کنید
یک محدوده نامگذاری شده راه آسانی برای ارجاع به طیفی از داده ها در یک فرمول است. به جای تایپ مراجع سلولی برای داده، نام محدوده را تایپ کنید.
دومین مزیت استفاده از یک محدوده نامگذاری شده این است که مراجع سلولی برای این محدوده هرگز تغییر نمی کند حتی زمانی که فرمول در سلول های دیگر در کاربرگ کپی می شود. نام‌های محدوده جایگزینی برای استفاده از ارجاعات سلول مطلق برای جلوگیری از خطا در هنگام کپی کردن فرمول‌ها هستند.
نام محدوده شامل عناوین یا نام فیلدها برای داده ها (همانطور که در ردیف 4 نشان داده شده است) نیست، فقط شامل داده ها می شود.
-
سلول های D5 تا G10 را در کاربرگ برجسته کنید.
Image -
مکان نما را در کادر نام واقع در بالای ستون A قرار دهید، Table را تایپ کنید، سپس Enter را فشار دهید. سلول‌های D5 تا G10 دارای نام محدوده جدول هستند.
Image - نام محدوده آرگومان آرایه جدول VLOOKUP بعداً در این آموزش استفاده می شود.
باز کردن کادر گفتگوی VLOOKUP
اگرچه می توان فرمول جستجو را مستقیماً در یک سلول در یک کاربرگ تایپ کرد، بسیاری از مردم به سختی می توانند نحو را مستقیم نگه دارند - به ویژه برای فرمول پیچیده ای مانند فرمول مورد استفاده در این آموزش.
به عنوان جایگزین، از کادر محاوره ای VLOOKUP Function Arguments استفاده کنید. تقریباً همه توابع اکسل دارای یک کادر محاوره ای هستند که در آن هر یک از آرگومان های تابع در یک خط جداگانه وارد می شود.
-
سلول E2 از کاربرگ را انتخاب کنید. این مکانی است که نتایج فرمول جستجوی دو بعدی نمایش داده می شود.
Image -
روی روبان، به برگه Formulas بروید و Lookup & Reference را انتخاب کنید.
Image -
VLOOKUP را انتخاب کنید تا کادر محاوره ای Function Arguments باز شود.
Image - کادر محاوره ای Function Arguments جایی است که پارامترهای تابع VLOOKUP وارد می شوند.
Argument Value Lookup را وارد کنید
معمولاً، مقدار جستجو با یک فیلد داده در ستون اول جدول داده مطابقت دارد. در این مثال، مقدار جستجو به نام بخشی که می‌خواهید اطلاعاتی را پیدا کنید، اشاره دارد. انواع مجاز داده ها برای مقدار جستجو عبارتند از داده های متنی، مقادیر منطقی، اعداد و مراجع سلولی.
مرجع مطلق سلول
هنگامی که فرمول ها در اکسل کپی می شوند، مراجع سلولی تغییر می کنند تا مکان جدید را منعکس کنند. اگر این اتفاق بیفتد، D2، مرجع سلول برای مقدار جستجو، تغییر می‌کند و در سلول‌های F2 و G2 خطاهایی ایجاد می‌کند.
مرجع سلول مطلق هنگام کپی کردن فرمول ها تغییر نمی کند.
برای جلوگیری از خطاها، مرجع سلول D2 را به مرجع سلول مطلق تبدیل کنید. برای ایجاد یک مرجع سلول مطلق، کلید F4 را فشار دهید. این علائم دلار را در اطراف مرجع سلول مانند $D$2 اضافه می کند.
-
در کادر محاوره ای Function Arguments، مکان نما را در کادر متنی lookup_value قرار دهید. سپس، در کاربرگ، cell D2 را انتخاب کنید تا این مرجع سلولی را به lookup_value اضافه کنید. سلول D2 جایی است که نام قطعه وارد می شود.
Image -
بدون حرکت دادن نقطه درج، کلید F4 را فشار دهید تا D2 به مرجع سلول مطلق $D$2 تبدیل شود.
Image - کادر محاوره ای تابع VLOOKUP را برای مرحله بعدی آموزش باز بگذارید.
Argument آرایه جدول را وارد کنید
آرایه جدول جدول داده هایی است که فرمول جستجو برای یافتن اطلاعات مورد نظر شما جستجو می کند. آرایه جدول باید حداقل شامل دو ستون داده باشد.
ستون اول حاوی آرگومان مقدار جستجو (که در بخش قبلی تنظیم شد) است، در حالی که ستون دوم با فرمول جستجو جستجو می شود تا اطلاعاتی را که مشخص کرده اید پیدا کنید.
آگومان آرایه جدول باید به عنوان یک محدوده حاوی ارجاعات سلولی برای جدول داده یا به عنوان نام محدوده وارد شود.
برای افزودن جدول داده ها به تابع VLOOKUP، مکان نما را در کادر متنی table_array در کادر محاوره ای قرار دهید و Table را تایپ کنید.برای وارد کردن نام محدوده برای این آرگومان.
Nest the COLUMN Function
به طور معمول، VLOOKUP فقط داده ها را از یک ستون از جدول داده برمی گرداند.این ستون توسط آرگومان شماره شاخص ستون تنظیم می شود. در این مثال، با این حال، سه ستون وجود دارد، و شماره فهرست ستون باید بدون ویرایش فرمول جستجو تغییر کند. برای انجام این کار، تابع COLUMN را در داخل تابع VLOOKUP به عنوان آرگومان Col_index_num قرار دهید.
هنگامی که توابع تو در تو قرار می گیرند، اکسل کادر محاوره ای تابع دوم را برای وارد کردن آرگومان های آن باز نمی کند. تابع COLUMN باید به صورت دستی وارد شود. تابع COLUMN فقط یک آرگومان دارد، آرگومان Reference، که یک مرجع سلولی است.
تابع COLUMN تعداد ستون ارائه شده به عنوان آرگومان مرجع را برمی گرداند. این حرف ستون را به عدد تبدیل می کند.
برای یافتن قیمت یک کالا، از داده های ستون 2 جدول داده ها استفاده کنید. این مثال از ستون B به عنوان مرجع برای درج 2 در آرگومان Col_index_num استفاده می کند.
-
در کادر محاوره ای Function Arguments ، مکان نما را در کادر متنی Col_index_num قرار دهید و COLUMN(را تایپ کنید.. (حتماً براکت باز را اضافه کنید.)
Image -
در کاربرگ، سلول B1 را انتخاب کنید تا آن مرجع سلول را به عنوان آرگومان مرجع وارد کنید.
Image - براکت دور بستن را برای تکمیل تابع COLUMN تایپ کنید.
Argument جستجوی محدوده VLOOKUP را وارد کنید
آگومان Range_lookup VLOOKUP یک مقدار منطقی است (TRUE یا FALSE) که نشان می دهد VLOOKUP باید مطابقت دقیق یا تقریبی با Lookup_value پیدا کند.
- درست یا حذف شده: VLOOKUP یک تطابق نزدیک به Lookup_value برمی‌گرداند. اگر مطابقت دقیقی پیدا نشد، VLOOKUP بزرگترین مقدار بعدی را برمی گرداند. داده های ستون اول Table_array باید به ترتیب صعودی مرتب شوند.
- FALSE: VLOOKUP از تطبیق دقیقی با Lookup_value استفاده می کند. اگر دو یا چند مقدار در ستون اول Table_array وجود داشته باشد که با مقدار جستجو مطابقت دارد، اولین مقدار یافت شده استفاده می شود. اگر مطابقت دقیقی پیدا نشد، یک خطای N/A برگردانده می‌شود.
در این آموزش، اطلاعات خاصی در مورد یک مورد سخت افزاری خاص جستجو می شود، بنابراین Range_lookup روی FALSE تنظیم شده است.
در کادر محاوره‌ای Function Arguments، مکان‌نما را در کادر متنی Range_lookup قرار دهید و False را تایپ کنید تا به VLOOKUP بگویید تا داده‌ها را دقیقاً مطابقت بدهد.
OK را برای تکمیل فرمول جستجو و بستن کادر محاوره ای انتخاب کنید. سلول E2 حاوی یک خطای N/A خواهد بود زیرا معیار جستجو در سلول D2 وارد نشده است. این خطا موقتی است. هنگامی که معیارهای جستجو در آخرین مرحله این آموزش اضافه شود، اصلاح خواهد شد.
فرمول جستجو را کپی کنید و معیارها را وارد کنید
فرمول جستجو داده ها را از چندین ستون جدول داده ها در یک زمان بازیابی می کند. برای انجام این کار، فرمول جستجو باید در تمام فیلدهایی باشد که از آنها اطلاعات می خواهید.
برای بازیابی داده‌ها از ستون‌های 2، 3، و 4 جدول داده‌ها (قیمت، شماره قطعه و نام تأمین‌کننده)، یک نام جزئی را به عنوان Lookup_value وارد کنید.
از آنجایی که داده ها در یک الگوی منظم در کاربرگ قرار داده شده اند، فرمول جستجو را در سلول E2 در سلول های F2 کپی کنید و G2 همانطور که فرمول کپی می شود، اکسل مرجع سلول نسبی را در تابع COLUMN (سلول B1) به روز می کند تا مکان جدید فرمول را منعکس کند. اکسل مرجع سلول مطلق (مانند $D$2) و محدوده نامگذاری شده (جدول) را با کپی کردن فرمول تغییر نمی دهد.
بیش از یک راه برای کپی داده ها در اکسل وجود دارد، اما ساده ترین راه استفاده از Fill Handle است.
-
سلول E2 را، جایی که فرمول جستجو در آن قرار دارد، انتخاب کنید تا آن را به سلول فعال تبدیل کنید.
Image -
دستگیره پر را روی سلول G2 بکشید. سلول‌های F2 و G2 خطای N/A را که در سلول E2 وجود دارد نمایش می‌دهند.
Image -
برای استفاده از فرمول های جستجو برای بازیابی اطلاعات از جدول داده ها، در کاربرگ cell D2 را انتخاب کنید، Widget را تایپ کنید و فشار دهید را وارد کنید.
Image اطلاعات زیر در سلول های E2 تا G2 نمایش داده می شود.
- E2: 14.76 دلار - قیمت یک ویجت
- F2: PN-98769 - شماره قطعه برای یک ویجت
- G2: Widgets Inc. - نام تأمین‌کننده ابزارک‌ها
-
برای آزمایش فرمول آرایه VLOOKUP، نام سایر قسمت‌ها را در سلول D2 تایپ کنید و نتایج را در سلول‌های E2 تا G2 مشاهده کنید.
Image - هر سلول حاوی فرمول جستجو حاوی یک قطعه داده متفاوت در مورد مورد سخت افزاری است که جستجو کرده اید.
عملکرد VLOOKUP با توابع تو در تو مانند COLUMN روشی قدرتمند برای جستجوی داده ها در داخل جدول با استفاده از داده های دیگر به عنوان مرجع جستجو ارائه می دهد.
