فرمول های تخصصی جست وجو

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

جست وجو برای یافتن مقداری دقیق

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

برای جست وجوی یک مقدار دقیق باید از تابع VLOOKUP  یا HLOOKUP  که مقدار آرگومان اختیاری چهارم آن، FALSE است استفاده کنید. تصویر14-8، کاربرگی با یک جدول LOOKUP را نشان می دهد که در ستون C، شماره ی پرسنلی و در ستون D، نام کارمندان درج شده و نام جدول جست وجو، EmpList انتخاب شده است. فرمول سلول B2 که در پایین مشاهده می کنید به دنبال شماره پرسنلی ای که در سلول B1 درج می گردد، نام کارمند متناظر با آن را برمی گرداند:

=VLOOKUP ( B1 , EmpList , 2 , FALSE )

فرمول های تخصصی جست وجو

چون مقدار آخرین آرگومان تابع VLOOKUP ، FALSE قرار داده شده است، تابع تنها در صورتی یک مقدار را به عنوان پاسخ برمی گرداند که کاملا با عبارت جست وجو تطبیق داشته باشد واگر چنین مقداری پیدا نشود عبارت خطای #N/A  را به عنوان پاسخ برمی گرداند. البته این نتیجه دقیقا همان چیزی است که کاربر انتظار دارد، چون برگرداندن یک تطبیق نسبی برای شماره ی پرسنلی بی فایده است. همچنین دقت کنید که مقادیر ستون C به صورت صعودی تنظیم نشده اند. اگر مقدار آخرین آرگومان تابع VLOOKUP  برابر با FALSE باشد باید مقادیر را  به صورت صعودی تنظیم کنید.

 

اگر ترجیح می دهید در هنگامی که شماره پرسنلی پیدا نمی شود، پیغامی غیر از #N/A  نمایش داده شود، می توانید از تابع IFERROR برای تعویض عبارت پیغام با یک متن دیگر استفاده کنید. فرمول زیر به جای #N/A  عبارت Not Fount را نمایش می دهد:

=IFERROR ( VLOOKUP (B1 , EmpList , 2 , FALSE) , “Not Fount” )

تابع IFERROR تنها با نسخه ی اکسل 2007 به بعد پاسخ می دهد و برای نسخه های قدیمی تر اکسل باید از فرمول زیر استفاده کنید:

=IF (ISNA (VLOOKUP (B1 , EmpList , 2 , FALSE) ) , “Not Fount”, VLOOKUP (B1, EmpList, 2, FALSE) )

 

جست وجوی یک مقدار در سمت چپ جداول

تابع  VLOOKUP همیشه جست وجوی مقادیر را از نخستین ستون دامنه ی جست وجو شروع می کند، اما اگر بخواهید مقداری را در ستونی غیر از ستون نخست مورد جست وجو قرار دهید باید چه کار کنید؟

در چنین مواقعی اگر بتوانید برای آرگومان سوم تابع LOOKUP از یک مقدار منفی استفاده کنید نتیجه مورد نظر اصل می شود، اما متاسفانه اکسل اجازه ی چنین کاری را به کاربران نمی دهد.

تصویر زیر، این مشکل را نشان می دهد. فرض کنید می خواهید میانگین روپایی (ستون B در دامنه ای به نام Averages ) بازیکنی که نام او در ستون C (در دامنه ای به نام Players) درج شده را جست وجو کنید.

نام بازیکنی که به دنبال داده های مربوط به او می گردید در سلولی به نام Lookup Value قرار دارد. تابع VLOOKUP پاسخ درست را به شما نشان نمی دهد چون داده ها به صورت صحیح مرتب نشده اند. یک راه برای به دست آوردن پاسخ صحیح، مرتب کردن دوباره ی داده ها به صورت صحیح است اما گاهی اوقات مانند زمانی که تعداد داده ها خیلی زیاد باشد، امکان انجام چنین کاری وجود ندارد.

فرمول های تخصصی جست وجو

راه حل این مشکل، استفاده از تابع LOOKUP بوده که نیازمند دو آرگومان است. فرمول زیر که در سلول F3 قرار دارد میانگین روپایی را از ستون B برای بازیکنی که نامش در سلولی به نام Lookup Value درج شده برمی گرداند:

=LOOKUP ( Lookup Value , Players , Averages )

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

یک راه حل بهتر برای این مساله، استفاده از توابع MATCH  و INDEX  است. فرمول زیر درست مانندفرمول پیش عمل می کند، با این تفاوت که اگر نام بازیکنی پیدا نشود پیغام #N/A  را برمی گرداند. مزیت دیگر این فرمول در آن است که لازم نیست نام بازیکنان را مرتب کرده باشید.

=INDEX (Averages , MATCH (LookupValue , Players , 0 ) )

 

انجام جست وجوی حساس نسبت به بزرگ و کوچک بودن حروف

توابع جست وجو در اکسل (LOOKUP، VLOOKUP و HLOOKUP ) نسبت به بزرگ و کوچک بودن حروف حساس نیستند. برای مثال اگر یک فرمول LOOKUP را برای جست وجوی عبارت budget بنویسید، فرمول مورد نظر هر کدام از عبارت های BUDGET ، Budget یا BuDgEt را به عنوان پاسخ برمی گرداند.

تصویر زیر، یک مثال ساده را نمایش می دهد. دامنه ی D2:D7 را Range1 و دامنه ی E2:E7 را Range2 نامیده ایم. کلمه ای که قرار است جست وجو شود در سلول B1 (که Value نام دارد) درج شده است. فرمول آرایه ای زیر، در سلول B2 قرار دارد و یک جست وجوی حساس به بزرگ و کوچک بودن حروف در Range1  را انجام می دهد و مقدار متناظر در Range2  را برمی گرداند:

{= INDEX (Range2 , MATCH ( TRUE , EXACT ( Value , Range1 ) , 0) ) }

فرمول های تخصصی جست وجو

این فرمول به دنبال کلمه ی DOG (با حروف بزرگ) می گردد و 300 را به عنوان پاسخ برمی گرداند. فرمول جست وجوی استاندارد زیر (که نسبت به بزرگ و کوچک بودن حروف حساس نیست)، 400 را به عنوان پاسخ برمی گرداند:

=LOOKUP ( Value , Range1 , Range2 )

فراموش نکنید در هنگام درج فرمول های آرایه ای باید کلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید.

جست وجوی مقداری در چند جدول جست وجو

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

فرمول های تخصصی جست وجو

این کارپوشه، مقدار کمیسیون فروش را محاسبه می‌کند و شامل دو جدول جست‌وجو به نام‌های CommTable1در دامنه ی G3:G9  و CommTableدر دامنه ی J3:K8  است. درصد کمیسیون برای هر نماینده ی فروش وابسته به دو فاکتور است: سابقه ی کاری نماینده‌ی فروش و میزان فروش وی. ستون D هم شامل فرمول هایی است که به جست وجوی درصد کمیسیون از جدول مربوطه می پردازد. برای مثال، فرمول سلول D2 به صورت زیر است:

=VLOOKUP (C2 , IF ( B2<3 , CommTable1 , CommTable2 ) , 2 )

 

آرگومان دوم برای تابع VLOOKUP  شامل یک فرمول IF است که از مقدار موجود در ستون B برای تعیین اینکه کدام جدول جست وجو به کار گرفته شود، استفاده می کند. فرمول ستون E، مقدار فروش (ستونC) را در درصد کمیسیون( ستونD) ضرب می کند. برای مثال، فرمول سلول E2 به صورت زیر است:

=C2*D2

 

تعیین رتبه ی حرفی برای نمرات آزمون

یکی از کاربردهای متداول جدول جست وجو اختصاص دادن حروف (که مشخص کننده ی رتبه ی دانش آموز هستند) به نمرات آزمون است. تصویر زیر، کاربرگی که نمرات دانش آموزان روی آن درج شده است را نشان می دهد. دامنه‌ی E2:F6 (که GradeList نامیده می شود)، جدول جست وجوی به کار گرفته شده برای اختصاص یک رتبه ی حرفی به نمرات آزمون را نشان می دهد.

ستون C حاوی فرمول هایی است که از تابع VLOOKUP  و جدول  LOOKUP برای اختصاص دادن رتبه بر مبنای نمره ای که در ستون B درج شده استفاده می کند. برای مثال، فرمول سلول C2 به صورت زیر است:

=VLOOKUP (B2 , GradeList , 2)

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

=VLOOKUP (B2 , { 0, “F” ;40 , “D” ;70 , “C” ;80 , “B” ;90 , “A” } , 2 )

 

شیوه ی دیگری که در آن از فرمولی خواناتر استفاده می کنیم، استفاده از تابع LOOKUP  با دو آرگومان آرایه ای است:

=LOOKUP (B2 , { 0, 40, 70, 80, 90 } , { “F” , “D” , “C” , “B” , “A” } )

 

فرمول های تخصصی جست وجو

 


نویسنده : --

تگ ها :
عضویت طلایی
ثبت نظر