راهکارهای برنامه ریزی تولید

تابع VLOOKUP چیست؟ برای شروع، این یک تابع اکسل است :) چه کاری انجام می دهد؟ آن را برای مقدار تعیین شده جستجو می کند و یک مقدار تطبیقی ​​را از ستون دیگر باز می گرداند. از لحاظ فنی، تابع VLOOKUP یک مقدار در ستون اول محدوده مشخص سلول ها را نشان می دهد و یک مقدار را در همان ردیف از ستون دیگر باز می گرداند.

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

کاراکتر "V" در VLOOKUP به معنای "عمودی" است. این استفاده شده است که VLOOKUP را از تابع HLOOKUP متمایز کند که در ردیف بالا یک آرایه به کار می رود (H به معنای "افقی" است).

نحو اکسل VLOOKUP

فرمول تابع VLOOKUP به شرح زیر است:

VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

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

• lookup_value  مقدار مورد جستجو است.
• این می تواند یک مقدار (شماره، تاریخ یا متن) یا یک مرجع سلولی (مرجع به سلول حاوی یک مقدار) یا مقدار بازگشتی توسط برخی دیگر از عملکرد یا فرمول های اکسل باشد. مثلا:

• جستجوی شماره : 

=VLOOKUP(40, A2:B15, 2) 

• برای شماره 40 جستجو خواهد کرد.

• جستجوی متن : 

• فرمول متن "سیب" را جستجو می کند. لطفا توجه داشته باشید که شما همیشه مقادیر متن را در «نقل قول دوتایی» قرار می دهید.

• جستجو بر اساس ارزش در سلول دیگری:

• فرمول برای مقدار C2 در سلول جستجو می کند.

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

•  بنابراین، فرمول ما 

• برای "40" در سلولهای A2 تا A15 جستجو خواهد شد، زیرا A اولین ستون table_array A2: B15 است. امیدوارم، تا اینجا همه چیز روشن باشد! 

• col_index_num شماره ستون در table_array است که مقدار آن در سطر مربوطه باید بازگردانده شود.

• توالی ستون سمت چپ در table_array مشخص شده، ستون اول 1، ستون دوم 2، ستون سوم 3 و غیره است.

  خوب، حالا شما می توانید تمام فرمول 

• را بخوانید. فرمول جستجو برای "40" در سلول های A2 تا A15 و مقدار مطابق از ستون B را برمی گرداند (زیرا B ستون دوم در table_array A2: B15 مشخص شده است). 

• range_lookup تعیین می کند که آیا شما به دنبال یک جستجوی دقیق (FALSE) یا جستجوی تقریبی هستید (TRUE یا حذف شده).این پارامتر نهایی اختیاری است اما بسیار مهم است. علاوه بر این در این آموزش VLOOKUP، چندین مثال در ارتباط با این بخش در پایین آورده خواهد شد.

چند نمونه از اکسل VLOOKUP

نحوه انجام vlookup در اکسل از یک برگه دیگر

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

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

فرمول که در تصویر زیر مشاهده می کنید، جستجو در متن "محصول 1" در ستون A (ستون 1 ستون محدوده جستجو A2: B9) در صفحه برگزیده قیمت ها :

 نکته: ایده خوبی است که همیشه در آرایه table_array از منابع مطلق سلولی (با علامت $) استفاده کنید. در صورتی که فرمول Vlookup خود را به سایر سلول ها کپی می کنید، از تغییر محدوده جستجو جلوگیری می شود.

نحوه انجام vlookup از یک کتاب کار (فایل) متفاوت

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

به عنوان مثال، فرمول زیر در Sheet2 از کتاب Workbook Numbers.xlsx ارزش "40" را جستجو می کند :

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

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

1. هر دو کتاب را باز کنید این مرحله واجب نیست اما فرمول را برای شما آسان تر می کند زیرا شما نباید نام کتاب کار را به صورت دستی تایپ کنید، بنابراین از اشتباهات جلوگیری کنید.
2. شروع به تایپ کردن فرمول VLOOKUP کنید و برای argument_ table_array، به کتاب دیگر بروید و دامنه جستجو را در آنجا با موس انتخاب کنید.

نحوه استفاده از محدوده یا جدول نامی در فرمول VLOOKUP

اگر شما قرار است از یک محدوده جستجو در چندین فرمول VLOOKUP استفاده کنید، می توانید برای آن محدوده نامی ایجاد کرده و نام را مستقیما در argument_ table of array فرمول Vlookup خود وارد کنید.

=VLOOKUP("Product 1",Products,2)

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

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

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

فرمول کامل ممکن است شبیه به این باشد:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

همچنين به عنوان محدودههاي نامي، نام ستونها ثابت است و مرجع سلول شما بدون توجه به جايگزين فرمول VLOOKUP در همان كتاب، تغيير نخواهد كرد.

استفاده از کاراکترهای جستجو (wildcard) در فرمول VLOOKUP

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

• علامت سوال (؟) برای مطابقت با فقط یکی از کاراکترها، و
• ستاره (*) برای مطابقت با هر دنباله ای از کاراکترها.

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

• هنگامی که متن دقیقی را که دنبال می کنید به یاد نمی آورید.
• وقتی می خواهید یک کلمه را به عنوان بخشی از محتویات سلول پیدا کنید. توجه داشته باشید که تابع VLOOKUP با تمام محتوای یک سلول جستجو می شود، همانطور که اگر گزینه "Match the entire cell content" را در کادر استاندارد جستجوی اکسل انتخاب کنید.
• هنگامی که یک ستون جستجو شامل فضاهای خالی در ابتدا یا انتهای مقدار درج شده در داخل سلول باشد، ممکن است مغز شما در تلاش برای کشف کردن اینکه چرا فرمول عادی کار نمی کند، منفجر شود!

مثال 1. نگاهی به متن شروع یا پایان دادن با کاراکترهای خاص

فرض کنید شما می خواهید یک مشتری خاص را در پایگاه داده زیر پیدا کنید. شما نمیتوانید نام خانوادگی خود را به خاطر بسپارید، اما شما می دانید که با " ack " شروع می شود. بنابراین، فرمول زیر VLOOKUP راه چاره خواهد بود:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

پس از اینکه مطمئن شوید نام صحیح را پیدا کرده اید، می توانید از فرمول مشابه VLOOKUP برای دریافت مبلغی که توسط آن مشتری پرداخت می شود استفاده کنید. شما فقط باید پارامتر 3 را در فرمول به شماره ستون مناسب، ستون C (3) در مورد ما تغییر دهید:

=VLOOKUP("ack*",$A$2:$C$11, 3 ,FALSE)

در اینجا چند مثال دیگر از فرمولهای VLOOKUP با کاراکترهای کلمات کلیدی:

پیدا کردن نام پایان دادن به با " man ":

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

مثال 2. فرمول های wildcard VLOOKUP بر اساس مقدار سلول

و اکنون بیایید نمونه ای کمی پیچیده تر از نحوه انجام یک vlookup بر روی مقدار در برخی از سلول ها بحث کنیم. فرض کنید که شما دارای کلید مجوز در ستون A و نام مجوز در ستون B هستید. شما همچنین دارای چند (کاراکتر) از برخی از کلید مجوز در سلول C1 و شما می خواهید یک نام مجوز مربوطه را پیدا کنید.

این را می توان با استفاده از فرمول VLOOKUP مانند این انجام می شود:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

این فرمول برای مقادیر C1 از طریق محدوده مشخص شده جستجو می کند و مقدار مربوطه را در ستون B باز می گرداند. لطفا توجه داشته باشید که ما از ampersand (&) قبل و بعد از یک مرجع سلولی در پارامتر 1 استفاده می کنیم تا یک رشته متن را پیوند دهیم.

همچنین، لطفا به استدلال table_array در تصویر بالا مراجعه کنید. همانطور که در مثال بالا مورد بحث قرار گرفتیم، این شامل نام جدول ("Table7") به جای محدوده سلولی است.

اکسل VLOOKUP با مقدار دقیق یا تقریبی

و در نهایت، بیایید نگاه دقیق تر به آخرین بحث از عملکرد VLOOKUP اکسل، range_lookup . اگرچه اختیاری است، این پارامتر بسیار مهم است، زیرا شما احتمالا نتایج مختلفی را به دست می آورید بسته به این که آیا TRUE یا FALSE را به همان فرمول ارائه می کنید.

برای شروع، بیایید ببینیم که منظور مایکروسافت اکسل از معنای "مطابقت دقیق" و "تقریبی" چیست.

اکسل Vlookup با مقدار دقیق

اگر range_lookup به FALSE تنظیم شده باشد، فرمول برای یک مقدار دقیقا همان مقدار lookup_value را جستجو می کند. اگر دو یا بیشتر مقادیری که دقیقا مطابق با ارزش جستجو هستند در لیست وجود داشته باشد، اولین مقدار پیدا شده بازگردانده می شود. اگر یک جستجوی دقیق پیدا نشد، خطای # N / A بازگردانده می شود.

اکسل Vlookup با بازی تقریبی

اگر range_lookup به TRUE یا حذف شده (خالی رها شده) باشد، فرمول نزدیکترین مسابقه را جستجو خواهد کرد. دقیق تر، یک فرمول VLOOKUP برای اولین بار یک جستجوی دقیق را جستجو می کند، اما اگر یک جستجوی دقیق پیدا نشود، نزدیک ترین مقدار را که بزرگترین مقدار بعدی است که کمتر از lookup_value است، بازگرداند.

یادداشت مهم! Vlookup با بازی تقریبی نیاز به مقادیر در ستون اول table_array دارد که به ترتیب صعودی مرتب می شوند، یعنی از کوچکترین تا بزرگترین. در غیر این صورت، عملکرد VLOOKUP اکسل ممکن است ارزش صحیح را پیدا نکند.

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

مثال 1: نحوه انجام یک vlookup در اکسل با استفاده از روش دقیق

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

بگذارید جدول سرعت حیوانات را از اولین مثال دوباره ببنیم و بفهمیم کدام حیوان می تواند 50 مایل در ساعت را اجرا کند. من معتقدم که با شناخت فرمول هیچ مشکلی نخواهید داشت.

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=VLOOKUP(E1,$A$2:$B$15,2,FALSE)

جایی که E1 ارزش جستجو است.

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

مثال 2. Vlookup با مقدار تقریبی

هنگام استفاده از فرمول VLOOKUP با مقدار تقریبی ، یعنی با range_lookup تنظیم شده به TRUE یا حذف شده، اولین کاری که باید انجام دهید مرتب سازی ستون اول در table_array شما به صورت صعودی است .

و اکنون می توانید با یکی از فرمول های زیر بروید:

=VLOOKUP(69,$A$2:$B$15,2,TRUE)

=VLOOKUP(E1,$A$2:$B$15,2,TRUE)

=VLOOKUP(E1,$A$2:$B$15,2)

جایی که E1 ارزش جستجو است.

به عنوان مثال، بیایید حیوان را پیدا کنیم که سرعت آن نزدیک به 69 مایل در ساعت باشد. و در اینجا فرمول VLOOKUP آمده است: 

همانطور که می بینید، فرمول Antelope را که سرعت 61 مایل در ساعت است، در حالی که ما همچنین یوزپلنگ 70 مایل در ساعت را در لیست داریم، 70 درصد بسیار نزدیکتر به 69 است تا مقدار 61. بنابراین، چرا Vlookup مقدار  "Antelope" را برمیگرداند؟ 

از آنجا که VLOOKUP با بازی تقریبی، نزدیک ترین مقدار را که کمتر از مقدار جستجو است، بازیابی می کند.

امیدوارم که این مثالها سبب درک صحیح شما در استفاده از VLOOKUP در اکسل شوند و این کار دیگر برای شما بیگانه نیست. اکنون ممکن است ذکر یک مبحث ضروری باشد تا نکات کلیدی را به خاطر داشته باشید.

اکسل VLOOKUP - همه چیز به یاد داشته باشید!

1. تابع VLOOKUP اکسل نمی تواند به چپ آن نگاه کند. به عبارتی همیشه مقدار جستجو در ستون سمت چپ محدوده جستجو (table_array) جستجو می شود.
2. در فرمول های VLOOKUP، تمام مقادیر حساس به حروف نیستند ، به این معنی که کاراکترهای بزرگ و کوچک به صورت معادل به کار می روند.
3. اگر مقدار جستجو کوچکتر از کوچکترین مقدار در ستون اول table_array باشد، تابع VLOOKUP خطای # N / A را باز می کند.
4. اگر پارامتر 3 (col_index_num) کمتر از 1 باشد، فرمول VLOOKUP #VALUE را باز میگرداند! خطا در صورتی که بیشتر از تعداد ستونها در table_array باشد، #REF! خواهد بود.
5. همیشه با استفاده از آرگومان table_array از سلولهای مطلق سلولی (با علامت $) برای جلوگیری از تغییر آن هنگام کپی کردن فرمول Vlookup خود به سایر سلول ها استفاده کنید.
6. هنگام جستجو با مقدار تقریبی (range_lookup تنظیم به TRUE یا حذف شده)، همیشه داده ها را در ستون اول table_array مرتب سازی کنید به ترتیب صعودی .
7. و در نهایت، در مورد اهمیت آخرین پارامتر به یاد داشته باشید. برای تحویل TRUE تقریبی یا FALSE برای مقدار دقیق، میتواند استفاده درست از آنها بسیار کار راه انداز باشد.