اکتشاف VLOOKUP اکسل را با چندین فیلد داده پیدا کنید

با ترکیب عملکرد VLOOKUP اکسل با عملکرد COLUMN ما می توانیم یک فرمول جستجو را ایجاد کنیم که به شما اجازه می دهد مقدار چندگانه را از یک ردیف یک پایگاه داده یا جدول داده ها بازگردانید.

در نمونه ای که در تصویر بالا نشان داده شده است، فرمول جستجو به راحتی می توان تمام مقادیر مانند قیمت، شماره بخشی و تامین کننده را به قسمت های مختلف سخت افزاری بازگرداند.

01 از 10

ارقام چندگانه با اکسل VLOOKUP

پس از مراحل ذکر شده در زیر فرمول جستجو را که در تصویر بالا دیده می شود ایجاد می کند، مقادیر چندگانه را از یک رکورد داده ی یکسان به نمایش می گذارد.

فرمول جستجوی نیازمند آن است که تابع COLUMN داخل VLOOKUP قرار گیرد.

قرار دادن یک تابع شامل ورود به تابع دوم به عنوان یکی از استدلال برای اولین تابع است.

در این آموزش، تابع COLUMN به عنوان شماره استدلال شماره ستون برای VLOOKUP وارد می شود.

آخرین مرحله در آموزش شامل کپی فرمول جستجو به ستون های اضافی برای بازیابی مقادیر اضافی برای بخش انتخاب شده است.

مطالب آموزشی

• وارد کردن داده های آموزشی
• ایجاد دامنه نامشخص برای جدول داده ها
• شروع عملکرد VLOOKUP
• وارد کردن Argument Value Array با استفاده از مرجع کامل سلول
• وارد کردن Argument جدول آرایه
• وارد کردن تابع COLUMN مشتق شده
• تکمیل عملکرد VLOOKUP
• کپی فرمول گرین کارت با استفاده از قلم پر
• بازیابی اطلاعات با فرمول گرین کارت

02 از 10

اطلاعات آموزشی را وارد کنید

اولین گام در آموزش این است که داده ها را به یک صفحه اکسل وارد کنید.

به منظور پیروی از مراحل آموزش، داده های نشان داده شده در تصویر بالا را به سلول های زیر وارد کنید.

• طیف بالا داده ها را به سلول های D1 تا G1 وارد کنید
• دامنه دوم را به سلول های D4 تا G10 وارد کنید

معیارهای جستجو و فرمول جستجو، ایجاد شده در این آموزش، به ردیف 2 برگه وارد می شوند.

آموزش قالببندی دیده شده در تصویر را شامل نمی شود، اما این کار بر نحوه کارکرد فرم جستجو تاثیر نمی گذارد.

اطلاعات در مورد گزینه های قالب بندی مشابه آنچه که در بالا دیده می شود در این Basic Excel Formatting Tutorial موجود است .

مراحل آموزش

1. داده ها را در تصویر بالا به سلول های D1 تا G10 وارد کنید

03 از 10

ایجاد دامنه نامشخص برای جدول داده ها

محدوده نامی یک راه آسان برای اشاره به طیف وسیعی از داده ها در یک فرمول است. به جای تایپ کردن در مراجع سلول برای داده ها، می توانید فقط نام دامنه را تایپ کنید.

مزیت دوم برای استفاده از یک محدوده نامیده شده این است که مرجع سلول برای این محدوده هرگز تغییر نمی کند حتی زمانی که فرمول به سلول های دیگر در صفحه کار داده شود.

بنابراین نام دامنه جایگزین استفاده از مراجع مطلق سلول برای جلوگیری از خطا در هنگام کپی کردن فرمولها است.

نکته: نام دامنه شامل عنوان ها یا نام های فیلد برای داده ها نیست (ردیف 4) اما فقط داده های خود.

مراحل آموزش

1. سلولهای D5 تا G10 را در برگه انتخاب کنید تا آنها را انتخاب کنید
2. روی جعبه نام واقع در ستون A کلیک کنید
3. "جدول" (بدون نقل قول) را در کادر نام تایپ کنید
4. کلید ENTER بر روی صفحه کلید را فشار دهید
5. سلول های D5 تا G10 نام دامنه "جدول" دارند. ما از نام اسم آرایه جدول آرایه VLOOKUP بعد از آموزش استفاده خواهیم کرد

04 از 10

باز کردن جعبه محاوره VLOOKUP

اگرچه ممکن است فرمول جستجوی خود را فقط به یک سلول در یک صفحه برگردانده شود، بسیاری از افراد دشوار نگه داشتن آن را به طور مستقیم - به ویژه برای یک فرمول پیچیده مانند همان که ما در این آموزش استفاده می کنیم دشوار است.

یک جایگزین، در این مورد، استفاده از کادر محاوره VLOOKUP است. تقریبا تمام توابع اکسل یک جعبه محاوره ای دارند که به شما اجازه می دهد تا هر یک از استدلال های عملکرد را در یک خط جداگانه وارد کنید.

مراحل آموزش

1. بر روی سلول E2 از برگه کلیک کنید - محل که در آن نتایج فرمول جستجوی دو بعدی بعدی نمایش داده خواهد شد
2. روی برگه فرمول روبان کلیک کنید
3. روی گزینه Lookup & Reference در نوار کلیک کنید تا لیست کشویی تابع باز شود
4. بر روی VLOOKUP در لیست کلیک کنید تا کادر محاوره ای تابع باز شود

05 از 10

وارد کردن Argument Value Array با استفاده از مرجع کامل سلول

به طور معمول، مقدار جستجو با یک فیلد داده در ستون اول جدول داده منطبق است.

در مثال ما، مقدار جستجو به نام بخش سخت افزاری اشاره دارد که ما می خواهیم اطلاعات را پیدا کنیم.

نوع داده های مجاز برای مقدار جستجو عبارتند از:

• داده های متنی
• یک مقدار منطقی (فقط TRUE یا FALSE)
• یک عدد
• یک مرجع سلول به یک مقدار در برگه

در این مثال، مرجع سلول را به جایی که نام قسمت در آن قرار دارد وارد کنید - D2 سلول.

مرجع مطلق سلولی

در یک مرحله بعد در آموزش، فرمول جستجو در سلول E2 را به سلولهای F2 و G2 کپی خواهیم کرد.

به طور معمول وقتی که فرمول ها در اکسل کپی می شوند، مراجع سلول به تغییر محل جدید خود تغییر می کنند.

اگر این اتفاق می افتد، D2 - مرجع سلول برای مقدار جستجو - به عنوان فرمول کپی شده ایجاد اشتباهات در سلول های F2 و G2 تغییر خواهد کرد.

برای جلوگیری از خطا، مرجع سلول D2 را به مرجع سلولی مطلق تبدیل می کنیم.

مراجع سلول مطلق هنگامی که فرمول ها کپی می شوند تغییر نمی کنند.

مراجع سلولی مطلق با فشار دادن کلید F4 روی صفحه کلید ایجاد می شوند. این کار باعث می شود نشانه های دلار در اطراف مرجع سلولی مثل $ D $ 2 اضافه شود

مراحل آموزش

1. روی خط lookup_value در جعبه محاوره کلیک کنید
2. روی سلول D2 کلیک کنید تا این مرجع سلولی به خط lookup_value اضافه شود. این سلول است که ما نام بخشی را که ما به دنبال آن هستیم تایپ می کنیم
3. بدون حرکت نقطه ورود، کلید F4 را روی صفحه کلید فشار دهید تا D2 را به مرجع مطلق $ D $ 2 تبدیل کند
4. کادر محاوره ای VLOOKUP را برای مرحله بعدی آموزش باز کنید

06 از 10

وارد کردن Argument جدول آرایه

آرایه جدول، جدول داده هایی است که فرمول جستجو برای یافتن اطلاعاتی که می خواهیم جستجو می کند.

آرایه جدول باید حداقل دو ستون داده داشته باشد.

• ستون اول شامل آرگومان مقدار جستجو (مرحله قبل در آموزش)
• دومین و هر ستون اضافی با فرمول جستجو برای جستجوی اطلاعاتی که مشخص می کنیم جستجو می شود.

استدلال آرایه جدول باید به عنوان یک محدوده حاوی مراجع سلولی برای جدول داده یا به عنوان نام دامنه وارد شود .

برای این مثال، از نام دامنه ایجاد شده در مرحله 3 آموزش استفاده خواهیم کرد.

مراحل آموزش

1. روی خط table_array در کادر محاوره ای کلیک کنید
2. تایپ "جدول" (بدون نقلقول) برای وارد کردن نام دامنه برای این آرگومان
3. کادر محاوره ای VLOOKUP را برای مرحله بعدی آموزش باز کنید

07 از 10

درج تابع COLUMN

به طور معمول VLOOKUP فقط داده ها را از یک ستون یک جدول داده باز می کند و این ستون توسط استدلال شماره ستون تعیین می شود .

در این مثال، با این حال، ما سه ستون داریم که می خواهیم داده ها را به آنها بازگردانیم، بنابراین ما نیازمند یک راه ساده برای تغییر عدد ستون بدون تغییر فرمول جستجوی ما هستیم.

این جایی است که تابع COLUMN وارد می شود. با وارد کردن آن به عنوان استدلال شماره ستون شماره ، آن تغییر خواهد کرد زیرا فرمول جستجو از سلول D2 به سلول E2 و F2 بعدا در آموزش کپی می شود.

توابع لاین

بنابراین، عملکرد تابع COLUMN به عنوان عدد شماره ستون VLOOKUP عمل می کند .

این کار با قرار دادن تابع COLUMN داخل VLOOKUP در خط Col_index_num کادر محاوره ای انجام می شود.

وارد کردن تابع COLUMN به صورت دستی

هنگامی که توابع توابع، اکسل ما را مجاز به باز کردن کادر محاوره ای دوم برای وارد کردن استدلال های آن نمی کند.

بنابراين تابع COLUMN بايد بصورت دستی در خط Col_index_num وارد شود.

تابع COLUMN تنها یک استدلال دارد - استدلال مرجع که مرجع سلولی است.

انتخاب پاراگراف ارجاع تابع COLUMN

کار تابع COLUMN این است که تعداد ستون داده شده به عنوان استدلال ارجاع را بازگردانیم.

به عبارت دیگر، حرف ستون را به یک عدد تبدیل می کند که ستون A اولین ستون است، ستون B دوم و غیره.

از آنجا که اولین فیلد اطلاعاتی که ما میخواهیم باز میگردیم، قیمت آیتم است - که در ستون دو جدول داده است - ما می توانیم مرجع سلولی برای هر سلول در ستون B را به عنوان Argument ارجاع برای انتخاب شماره 2 برای استدلال Col_index_num .

مراحل آموزش

1. در جعبه محاوره VLOOKUP، روی خط Col_index_num کلیک کنید
2. ستون نام تابع را بعد از یک ستون باز باز کنید " ( "
3. روی سلول B1 در برگه کلیک کنید تا مرجع سلولی را به عنوان استدلال ارجاع وارد کنید
4. براى تکمیل تابع COLUMN یک براى ضمیمه را وارد کنید "" )
5. کادر محاوره ای VLOOKUP را برای مرحله بعدی آموزش باز کنید

08 از 10

ورود به VLOOKUP محدوده گرامر Argument

Argument range_lookup VLOOKUP یک مقدار منطقی (درست یا نادرست) است که نشان می دهد که آیا می خواهید VLOOKUP برای پیدا کردن دقیق یا تقریبی مطابقت با Lookup_value باشد.

• اگر TRUE یا اگر این استدلال حذف شده باشد، VLOOKUP یک مقدار دقیق را به Lookup_value ارسال می کند، یا اگر یک جستجوی دقیق پیدا نشد، VLOOKUP مقدار بزرگ بعدی را برمی گرداند. برای فرمول برای انجام این کار، داده ها در ستون اول Table_array باید به ترتیب صعودی مرتب شوند .
• اگر FALSE، VLOOKUP فقط از یک Match دقیق به Lookup_value استفاده خواهد کرد. اگر دو ستون یا بیشتر در ستون اول Table_array وجود داشته باشد که با مقدار lookup مطابقت دارد، مقدار اولی که استفاده می شود استفاده می شود. اگر یک جستجوی دقیق پیدا نشود، خطای # N / A بازگشت می شود.

در این آموزش، از آنجا که ما به دنبال اطلاعات خاص در مورد یک مورد خاص سخت افزار هستیم، Range_lookup را برابر False تنظیم می کنیم.

مراحل آموزش

1. روی خط Range_lookup در کادر محاوره ای کلیک کنید
2. کلمه False را در این خط تایپ کنید تا نشان دهد که ما VLOOKUP را می خواهیم تا یک داده دقیق را برای داده هایی که ما دنبال می کنیم را بازگردانیم
3. برای تکمیل فرمول جستجو و بستن کادر محاوره ای روی OK کلیک کنید
4. از آنجا که ما هنوز معیارهای مراجعه به سلول D2 را وارد نکرده ایم، خطای # N / A در سلول E2 وجود خواهد داشت
5. این خطا زمانی اصلاح خواهد شد که معیارهای مراجعه را در آخرین مرحله آموزش اضافه کنیم

09 از 10

کپی فرمول گرین کارت با استفاده از قلم پر

فرمول جستجو برای بازیابی داده ها از چندین ستون جدول داده در یک زمان در نظر گرفته شده است.

برای انجام این کار، فرمول جستجو باید در تمام زمینه هایی باشد که از آن ما می خواهیم اطلاعاتی داشته باشیم.

در این آموزش ما می خواهیم داده ها را از ستون های 2، 3 و 4 جدول داده ها، یعنی قیمت، شماره بخشی و نام تامین کننده، زمانی که نام بخشی را به عنوان Lookup_value وارد می کنیم، بازیابی کنیم.

از آنجاییکه داده ها در یک الگوی منظم در برگه قرار داده شده است ، ما می توانیم فرمول جستجو در سلول E2 را به سلول های F2 و G2 کپی کنیم.

همانطور که فرمول کپی شده است، اکسل مرجع نسبی نسبی در عملکرد COLUMN (B1) برای نشان دادن محل جدید فرمول به روز می شود.

همچنین، اکسل مرجع مطلق سلول $ D $ 2 و جدول محدوده نامیده می شود به عنوان فرمول کپی می شود.

بیش از یک راه برای کپی کردن داده ها در اکسل وجود دارد، اما احتمالا ساده ترین راه این است که با استفاده از Fill Handle .

مراحل آموزش

1. با کلیک بر روی سلول E2 - که در آن فرمول جستجو قرار گرفته است - آن را به سلول فعال است
2. نشانگر ماوس را روی مربع سیاه در گوشه پایین سمت راست قرار دهید. نشانگر به یک علامت + " + " تغییر خواهد کرد - این قسمت پر شدن است
3. با کلیک بر روی دکمه سمت چپ ماوس و جمع آوری دسته کامل را به سلول G2 بکشید
4. دکمه ماوس را آزاد کنید و سلول F3 باید فرمول جستجوی دو بعدی را داشته باشد
5. اگر به درستی انجام شود، سلولهای F2 و G2 باید حاوی خطای # N / A موجود در سلول E2 باشند

10 از 10

ورود به معیار گرید

هنگامی که فرمول جستجو به سلول های مورد نیاز کپی می شود می توان از آن برای بازیابی اطلاعات از جدول داده استفاده کرد.

برای انجام این کار، نام موردی که میخواهید در سلول Lookup_value (D2) بازیابی کنید را تایپ کنید و کلید ENTER را در صفحه کلید فشار دهید.

پس از انجام، هر سلول حاوی فرمول جستجو باید یک قطعه متفاوت از اطلاعات مربوط به مورد سخت افزاری مورد نظر شما را داشته باشد.

مراحل آموزش

1. بر روی سلول D2 در برگه کلیک کنید
2. نوع ویجت را به سلول D2 و کلید ENTER را در صفحه کلید فشار دهید
3. اطلاعات زیر باید در سلول های E2 تا G2 نمایش داده شود:
   • E2 - 14.76 دلار - قیمت ویجت
   • F2 - PN-98769 - شماره بخش برای ویجت
   • G2 - Widgets Inc. - نام فروشنده برای ویدجت
4. فرمول آرایه VLOOKUP را با تایپ کردن نام قطعات دیگر به سلول D2 و مشاهده نتایج در سلول های E2 تا G2، آزمایش کنید.

اگر یک پیام خطا مانند #REF! در سلول های E2، F2 یا G2 ظاهر می شود، این لیست پیام های خطا VLOOKUP ممکن است به شما در تعیین مشکل کجا کمک کند.