آخرین به‌روزرسانی: 18 بهمن 1401

تابع LOOKUP

سوالاتی که در این فصل پاسخ داده می شود:

• چگونه می توانم فرمولی بنویسم که بر اساس درآمد نرخ مالیات را حساب کنم؟
• بر اساس کد یک محصول چگونه می توانم قیمت آن را استخراج کنم؟
• فرض کنید قیمت یک محصول مرتبا تغییر کند. من تاریخ فروش را می دانم ، چگونه می توانم فرمولی بنویسم که قیمت محصول را محاسبه کند؟

دستور زبان تابع  LOOKUP

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

دستور زبان VLOOKUP

دستور زبان استفاده از تابع VLOOKUP به شکل زیر است. آرگومانهای درون ([ ]) براکت اختیاری هستند.

VLOOKUP(lookup value ,table range ,column index,[range lookup])

• Lookup value یا مقدار جستجو: مقداری است که شما در اولین ستون جدول محدوده به دنبال آن می گردید.
• Table range یا جدول محدوده : محدوده ایست که کل جدول مورد جستجو را در بر دارد. جدول محدوده شامل ستون اولیست که شما می خواهید معادل مقدار مورد جستجو را در آن بیابید و بقیه ستونهایی که مایلید تابع، نتیجه را از آنها بازگرداند.
• Column index یا شاخص ستون: شماره ستون در جدول محدوده است که تابع ، مقدار را از آن بر می دارد.
• Range lookup یا محدوده جستجو: یک آرگومان اختیاریست. نکته: این پارامتری است که به شما امکان بازگرداندن معادل دقیق یا تقریبی را می دهد.

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

اگر این آرگومان False بوده و معادل دقیق هم در ستون اول یافت شود، اکسل بنای مقدار برگشتی را بر آن ردیف می گذارد. اما اگر معادلی یافت نشد اکسل پیام خطای #N/A (موجود نیست ) را برمی گرداند.

در فصل 12 توابع IF, IFERROR, IFS, CHOOSE, and SWITCH خواهیم آموخت که چگونه از تابع  IFERROR برای حذف این پیام خطا استفاده کنیم. توجه کنید که در این آرگومان عدد 1 معادل True و عدد 0 معادل False می باشد.

دستور زبان HLOOKUP

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

بیایید چند مثال جالب از توابع lookup را ببینیم.

پاسخ به سوالات فصل

چگونه می توانم فرمولی بنویسم که بر اساس درآمد نرخ مالیات را حساب کنم؟

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

برای دیدن اینکه چگونه فرمولی بنویسیم که نرخ مالیات برای هرسطح درآمدی را محاسبه کند ، فایل Lookup.xlsx را همانگونه که در تصویر 1-3 نشان داده شده باز کنید.

تصویر1-3 استفاده از تابع lookup برای محاسبه نرخ مالیات. اعداد در ستون اول جدول محدوده به صورت صعودی مرتب شده اند.

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

برای ترسیم اینکه تابع  lookup چگونه عمل می کند در محدوده D13:D17 داده هایی وارد کرده ام. با کپی فرمول VLOOKUP(D13,Lookup,2,True) به محدوده E13:E17 ، نرخ مالیاتی درآمدهای وارد شده در محدوده D13:D17 را محاسبه نموده ام.

بیائید ببینیم تابع lookup در سلولهای E13:E17 چگونه عمل کرده است. توجه کنید از آنجائیکه شاخص ستون در فرمول عدد 2 است، پاسخ همیشه از ستون دوم جدول محدوده خواهد آمد.

• در سلول D13 درآمد –$1,000 پاسخ خطای #N/A را دریافت خواهد کرد زیرا این مقدار کمتر از پائین ترین درآمد در ستون اول جدول محدوده است. اگر بخواهید برای چنین درآمدی مالیاتی 15 درصدی اختصاص دهید عدد صفر سلول D6 را با عدد  –$1,000   یا کوچکتر از آن جایگزین کنید.
• در سلول D14 درآمد $30,000 دقیقا با یکی از مقادیر ستون اول جدول محدوده مطابق است بنابراین تابع نرخ مالیات 34 درصد را بر می گرداند.
• در سلول D15 سطح درآمدی $29,000 با مقداری در ستون اول جدول محدوده مطابق نیست بنابراین تابع lookup بر بزرگترین رقم کمتر از$29,000   در ستون اول جدول محدوده متوقف می شود که در مثال ما  $10,000 است. در این حالت تابع از ستون 2 روبروی $10,000 مقدار 30 درصد را برمی گرداند.
• در سلول D16 سطح درآمدی $98,000 با مقداری در ستون اول جدول محدوده مطابق نیست بنابراین تابع lookup بر بزرگترین رقم کمتر از$98,000  در ستون اول جدول محدوده متوقف می شود. در این حالت تابع از ستون 2 روبروی $30,000 مقدار 34 درصد را برمی گرداند.
• در سلول D17 سطح درآمدی $104,000 با مقداری در ستون اول جدول محدوده مطابق نیست بنابراین تابع lookup بر بزرگترین رقم کمتر از$104,000 در ستون اول جدول محدوده متوقف می شود. در این حالت تابع از ستون 2 روبروی $100,000 مقدار 40 درصد را برمی گرداند.

در محدوده F13:F17 آرگومان محدوده جستجو را از True به False تغییر داده و فرمول VLOOKUP(D13,Lookup,2,False) را از F13 به محدودهF14:F17  کپی کرده ایم. سلول F14 همچنان مقدار34 درصد را برمی گرداند زیرا ستون اول جدول محدوده دقیقا مقدار$30,000  را درخود دارد. بقیه مقادیر در محدوده F13:F17 خطای #N/A را برمی گردانند زیرا هیچکدام ازاین درآمدها در محدوده D13:D17  معادلی در ستون اول جدول محدوده ندارند.در فصل 12 خواهید آموخت که چگونه مطمئن شوید که اکسل در چنین موقعیت هایی پیام خطای #N/A برنگرداند.

بر اساس کد یک محصول چگونه می توانم قیمت آن را استخراج کنم؟

اغلب اوقات ستون اول جدول محدوده حاوی اعداد مرتب شده صعودی نیست. برای مثال ستون اول جدول محدوده ممکن است فهرست کدهای محصول یا نام کارکنان باشد.در تجربه آموزش هزاران تحلیلگر مالی ، دریافته ام که افراد بسیاری وقتیکه ستون اول جدول محدوده بر اساس اعداد صعودی مرتب نیست ، نمی دانند چگونه با توابع lookup کار کنند. در چنین موقعیتهایی فقط یک قانون ساده را به یاد داشته باشید:از False برای مقدار آرگومان محدوده جستجو استفاده کنید.

یک مثال را ببینید. در فایل Lookup.xlsx (که در تصویر 2-3 نشان داده شده) می توانید قیمت 5 محصول و کد کالای هریک را ببینید.چگونه فرمولی بنویسیم که کد کالا را گرفته و قیمت آن را برگرداند؟

تصویر 2-3 جستجوی قیمت از طریق کد کالا. وقتیکه جدول محدوده به ترتیب صعودی مرتب نشده است ، False را به عنوان آخرین آرگومان در تابع lookup بنویسید.

برای کد کالای داده شده ، استفاده از False به عنوان آرگومان  محدوده جستجو اکسل را قادر می سازد قیمتی را که در ستون I با  کد واقع در ستون H مطابقت دارد بیابد. افراد بسیاری همین فرمول VLOOKUP(H18,Lookup2,2) را که من در سلول I18 وارد کرده ام خواهند نوشت. اما توجه داشته باشید وقتی چهارمین آرگومان را ننویسید (آرگومان  range lookup)  ، مقدار آن به صورت پیش فرض True در نظر گرفته خواهد شد. از آنجائیکه کد کالاها در جدول محدوده (H11:I15) مرتب نشده اند ، قیمتی نادرست ($3.50)  برگردانده می شود. اگر فرمول سلول I18 را به این صورت اصلاح کنید VLOOKUP(H18,Lookup2,2,False) ، قیمت صحیح ($5.20)  همانگونه که در سلول I19  نشان داده شده ، برگردانده می شود.

همچنین باید آرگومان False را در فرمولی که برای یافتن حقوق کارکنان از روی نام فامیل یا کد پرسنلی به کار می رود بگنجانید.

به هر حال همچنان که در تصویر 2-3 می بینید ستونهای B–G پنهان شده اند. برای پنهان کردن ستونها ، آنها را انتخاب کرده ، سربرگ Home را درنوارریبون کلیک کرده در گروه Cells گزینه Format را کلیک نموده و روی گزینه Hide & Unhide    واقع در زیر Visibility بروید و سپس از منوی ظاهر شده Hide Columns را کلیک کنید.

فرض کنید قیمت یک محصول مرتبا تغییر کند. من تاریخ فروش را می دانم ، چگونه می توانم فرمولی بنویسم که قیمت محصول را محاسبه کند؟

فرض کنید قیمت محصولی به روز فروش آن بستگی دارد. چگونه می توان از تابع lookup  برای نوشتن فرمولی که قیمت صحیح را بازگرداند استفاده کرد؟ روشن تر بیان کنم فرض کنید قیمت محصولی در جدول زیر نشان داده شده است.

بیائید فرمولی بنویسیم که قیمت درست را برای هر تاریخی که در سال 2005 محصول فروش رفته است محاسبه کند. برای تنوع از تابع HLOOKUP استفاده می کنیم. من تاریخ های تغییر قیمت را در سطر اول جدول محدوده نوشته ام . فایل Datelookup.xlsx را که در تصویر 3-3 نشان داده شده ببینید.

تصویر 3-3 استفاده از تابع HLOOKUP برای مشخص نمودن قیمتی که بسته به روز فروش تغییر می کند.

تابع HLOOKUP برای یافتن قیمت درست در سطر Dبه کاررفته شده است. قیمت از طریق تطابق تقریبی با تاریخ موجود در سطر 2 باعث می گردد تابع  HLOOKUP مقدار معادل تقریبی در سطر 3 درست زیر آن ، را برگرداند. من فرمول HLOOKUP(B8,lookup,2,TRUE) را از سلول C8  به محدوده C9:C11 کپی کرده ام . این فرمول تلاش می کند معادل تاریخ های ستون B را در اولین سطر از محدوده  B2:D3 بیابد. برای هر تاریخ بین 1/1/05  و 4/30/05 تابع  lookup  برروی 1/1/05  توقف می کند و قیمت موجود در سلول B3 را بر می گرداند.

برای هر تاریخ بین 5/01/05  و7/31/05 تابع lookup   برروی 5/1/05  توقف می کند و قیمت موجود در سلول C3 را بر می گرداند. برای هر تاریخ مساوی 8/01/05 یا بعد از آن تابع lookup  برروی 8/01/05  توقف می کند و قیمت موجود در سلول D3 را بر می گرداند.

مسائل:

– در فایل Hr.xlsx کد کارکنان ، حقوق و سابقه کاری آنها داده شده است.  فرمولی بنویسید که کد پرسنلی را گرفته و حقوق را اعلام کند. فرمول دیگری بنویسید که کد پرسنلی را گرفته و سابقه کار را اعلام نماید.

– فایل Assign.xlsx وظایف کاری کارکنان را در چهار گروه کاری تقسیم بندی کرده است.تناسب هر نفر از کارکنان برای هر وظیفه ( در مقیاس 0 تا 10 ) نیز داده شده است. فرمولی بنویسید که شایستگی هر کارمند را برای هر گروه کاری بدهد.

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

به عنوان مثال برای ۸ آگهی هر یک ۱۱ هزار دلار ولی برای چهارده آگهی هر یک ۱0 هزار دلار باید بپردازید فرمولی بنویسید که قیمت کل هر تعداد خرید را محاسبه کند.

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

برای مثال اگر  زمان برای پخش هشت آگهی بخرید ۱۲ هزار دلار برای هر یک از ۵ آگهی اول و ۱۱ هزار دلار برای هر یک از ۳ آگهی دیگر پرداخت خواهید کرد. اگر زمان برای پخش ۱۴ آگهی بخرید برای پنج عدد اول هرکدام ۱۲۰۰۰ و برای ۵ عدد دوم هر کدام ۱۱۰۰۰ و برای ۴ عدد آخر هر کدام ۱۰ هزار دلار پرداخت خواهید کرد. فرمولی بنویسید که جمع کل پرداختی برای هر تعداد آگهی را محاسبه کند.

راهنمایی : احتمالاً به سه ستون در جدول خود نیاز خواهید داشت و فرمول شما می بایست شامل دو تابع lookup باشد.

– بانک شما نرخ بهره برای وام های ۱ و ۵ و ۱۰ یا ۳۰ ساله را طبق جدول زیر داده است:

اگر برای زمانی بین ۱ تا ۳۰ سال از بانک وام بگیرید که در جدول موجود نباشد نرخ بهره وام شما از طریق درج مدت در میان طبقات مختلف محاسبه می گردد برای مثال فرض کنید بخواهید یک وام ۱۵ ساله بگیرید ازآنجاکه ۱۵ یک چهارم راه بین ۱۰ تا ۳۰ است نرخ بهره سالانه به این صورت محاسبه خواهد شد:  .75*.09 + .25*.10  فرمولی بنویسید که نرخ بهره سالیانه وام را در زمانهای بین ۱ تا ۳۰ سال محاسبه کند.

فاصله بین هر دو شهر امریکا (به جز ایالت های آلاسکا و هاوایی) به صورت تقریبی با فرمول زیر به دست می آید:

فایل Citydata.xlsx شامل طول و عرض جغرافیایی تعدادی از شهرهای امریکاست جدولی درست کنید که فاصله بین هر دو شهر موجود در لیست را بدهد.

– در فایل Pinevalley.xlsx اولین کاربرگ حاوی حقوق تعدادی از کارکنان در دانشگاه “پاین ولی” ، دومین کاربرگ حاوی سن و سومین کاربرگ حاوی سابقه کار آنهاست .کاربرگ چهارمی بسازید که شامل حقوق ، سن و سابقه کاری هر نفر باشد.

– کاربرگ Lookupmultiplecolumns.xlsx حاوی اطلاعات فروش یک فروشگاه لوازم الکترونیکی است. نام فروشنده در سلول B17  وارد خواهد شد فرمولی بنویسید که از سلول C17 به محدوده  D17:F17کپی شود و فروش های رادیو را در C17 ،تلویزیون را در D17 ،چاپگر را در E17 و سی‌دی را در F17 نشان دهد.

– فایل Grades.xlsx حاوی نمرات دانش آموزان در یک امتحان است. فرض کنید جدول تبدیل نمرات عددی به رتبه حرفی به شکل زیر باشد:

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

– فایل Employees.xlsx حاوی رتبه ۳۵ کارگر (از صفر تا ده) در مورد سه وظیفه کاری است و همچنین حاوی کاری است که به هر کارگر اختصاص داده شده است . فرمولی بنویسید که رتبه هر کارگر را در کاری که به او اختصاص داده شده محاسبه کند.

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

– فایل Qb2013.xlsx  آمار لیگ فوتبال حرفه ای امریکا را در سال ۲۰۱۳ نشان می دهد در سلول‌های J2 و K2 فرمولی بنویسید که TD و  Intاو را برای هر بازیکن داده شده ، برگرداند.

– فایل NBAplayers.xlsx  سن و حقوق بعضی از بازیکنان لیگ بسکتبال حرفه ای امریکا را نشان میدهد. فرمولی در سلول های J5:K50  بنویسید که سن و حقوق هر بازیکن را برگرداند.

ستون F در فایل Hardware.xlsx  کد محصول سخت افزاری و ستون G قیمت آن را در بر دارد. ستونهای M–O  حاوی مقادیر و قیمتی است که فروشگاه برای محصولات مختلف پرداخته است. قیمت کل خریدهای سخت افزار فروشگاه را محاسبه کنید.