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

01 از 03

دادههای به سمت چپ را پیدا کنید

مرور اجمال فرمول اکسل چپ گرین کارت

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

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

با ترکیب VLOOKUP با عملکرد CHOOSE ؛ با این حال، یک فرمول جستجو چپ می تواند ایجاد شود که:

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

مثال: با استفاده از VLOOKUP و انتخاب توابع در یک فرمول چپ گراب

مراحل زیر شرح دهید فرمول جستجو چپ دیده شده در تصویر بالا.

فرمول

= VLOOKUP ($ D $ 2، CHOOSE {1،2}، $ F: $ F، $ D: $ D)، 2، FALSE)

این امکان را فراهم می کند که بخشی ارائه شده توسط شرکت های مختلف ذکر شده در ستون 3 جدول داده ها را پیدا کند.

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

مراحل آموزش - ورود به داده های آموزشی

1. مقادیر زیر را به سلول های ذکر شده وارد کنید: D1 - Supplier E1 - Part
2. جدول داده های دیده شده در تصویر بالا را به سلول های D4 تا F9 وارد کنید
3. ردیف 2 و 3 به منظور تطابق با معیارهای جستجو و فرمول جستجوی چپ ایجاد شده در این درس خالی باقی می مانند

شروع فرمول چپ گرین کارت - باز کردن جعبه محاوره VLOOKUP

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

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

مراحل آموزش

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

02 از 03

وارد کردن Arguments به جعبه محاوره VLOOKUP - برای دیدن تصویر بزرگتر کلیک کنید

Arguments VLOOKUP

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

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

مقادیر زیر را برای هر استدلال VLOOKUP در خط صحیح کادر محاوره ای همانطور که در تصویر بالا نشان داده شده وارد کنید.

ارزش ارقام

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

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

مراحل آموزش

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

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

آرایه جدول: ورود به عملکرد CHOOSE

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

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

در این فرم، عملکرد CHOOSE دو کار انجام می دهد:

1. این یک آرایه جدول ایجاد می کند که تنها دو ستون بزرگ است - ستون های D و F
2. آن را به سمت راست به سمت چپ ستون ها در آرایه جدول تغییر می دهد به طوری که ستون F می آید اول و ستون D دوم است

جزئیات نحوه عملکرد CHOOSE انجام این کارها را می توان در صفحه 3 آموزش یافت .

مراحل آموزش

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

1. در کادر محاوره ای VLOOKUP روی خط Table_array کلیک کنید
2. عملکرد CHOOSE زیر را وارد کنید
3. CHOOSE ({1،2}، $ F: $ F، $ D: $ D)

عدد ستون شماره

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

تابع CHOOSE یک آرایه جدول ایجاد می کند که دو ستون عرض است و ستون F اولین ستون D است. از آنجا که اطلاعات مورد نظر - نام قسمت - در ستون D است، مقدار آرگومان index column باید 2 باشد.

مراحل آموزش

1. روی خط Col_index_num در کادر محاوره ای کلیک کنید
2. 2 در این خط تایپ کنید

جستجوی محدوده

Argument Range_lookup VLOOKUP یک مقدار منطقی (درست یا نادرست) است که نشان می دهد که آیا می خواهید VLOOKUP برای پیدا کردن دقیق یا یک تقریبی با ارزش مراجعه کنید.

• اگر 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 باشد

03 از 03

آزمایش فرمول چپ گرین کارت

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

برای پیدا کردن شرکت هایی که قطعات را عرضه می کنند، یک نام شرکت را به سلول D2 وارد کنید و کلید ENTER را در صفحه کلید فشار دهید.

نام بخش در سلول E2 نمایش داده خواهد شد.

مراحل آموزش

1. بر روی سلول D2 در صفحه کار خود کلیک کنید
2. گجت ها را به D2 سلول وارد کنید و کلید ENTER را در صفحه کلید فشار دهید
3. Gadgets متن - بخش عرضه شده توسط شرکت Gadgets Plus - باید در سلول E2 نمایش داده شود
4. فرمول جستجو را با تایپ کردن نامهای دیگر شرکت به سلول D2، تست کنید و نام مربوط به قسمت آن باید در سلول E2 ظاهر شود

پیام های خطا VLOOKUP

اگر یک پیام خطا مثل # N / A در سلول E2 ظاهر شود، ابتدا برای خطاهای املایی در سلول D2 بررسی شود.

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

شکستن کار انتخاب شغل

همانطور که ذکر شد، در این فرمول، عملکرد CHOOSE دارای دو شغل است:

• این یک آرایه جدول ایجاد می کند که تنها دو ستون بزرگ است - ستون های D و F
• آن را به سمت راست به سمت چپ ستون ها در آرایه جدول تغییر می دهد به طوری که ستون F می آید اول و ستون D دوم است

ایجاد یک آرایه جدول دو ستون

نحو عملکرد CHOOSE:

= CHOOSE (Index_number، Value1، Value2، ... Value254)

عملکرد CHOOSE به طور معمول یک مقدار از لیست مقادیر (Value1 تا Value254) بر اساس شماره شاخص وارد شده است.

اگر شماره شاخص 1 باشد، تابع value1 را از لیست بازمی گرداند؛ اگر شماره شاخص 2 باشد، تابع value2 را از لیست و غیره باز می گرداند.

با وارد کردن شماره های متعدد متعدد؛ با این وجود، تابع مقادیر چندگانه را در هر مورد دلخواه به شما نشان می دهد. انتخاب CHOOSE برای بازگشت مقادیر چندگانه با ایجاد یک آرایه انجام می شود .

وارد کردن آرایه توسط اطراف اعداد وارد شده با braces یا براکت های پیچیده انجام می شود. دو عدد برای شماره فهرست وارد می شوند: {1،2} .

لازم به ذکر است که CHOOSE به ایجاد یک جدول ستون محدود نمی شود. با اضافه کردن عدد اضافی در آرایه - مانند {1،2،3} - و محدوده دیگری در استدلال ارزش، می توان یک جدول سه ستون ایجاد کرد.

ستون های اضافی به شما این امکان را می دهند که اطلاعات متفاوتی را با فرمول جستجوی چپ به سادگی با تغییر عدد ستون عدد ستون VLOOKUP به شماره ستون حاوی اطلاعات مورد نظر به شما نشان دهید.

تغییر سفارش ستون ها با عملکرد CHOOSE

در عملکرد CHOOSE استفاده شده در این فرمول: CHOOSE ({1،2}، $ F: $ F، $ D: $ D) ، محدوده برای ستون F قبل از ستون D ذکر شده است.

از آنجا که تابع CHOOSE مجموعه آرایه جدول VLOOKUP - منبع داده برای این تابع - تعویض نظم ستون ها در عملکرد CHOOSE به VLOOKUP منتقل می شود.

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

در نتیجه VLOOKUP قادر به استفاده از نام شرکت برای پیدا کردن بخشی است که عرضه می کنند.